import os import mysql.connector DB_CONFIG = { "host": os.environ.get("DB_HOST", "officialsite.kr"), "port": int(os.environ.get("DB_PORT", "23306")), "user": os.environ.get("DB_USER", "admin"), "password": os.environ.get("DB_PASSWORD", "dlgks~123"), "database": os.environ.get("DB_NAME", "app_master"), } CREATE_TABLE_QUERY = """ CREATE TABLE IF NOT EXISTS babynote_together_care_items ( id VARCHAR(36) PRIMARY KEY, family_id VARCHAR(36) NOT NULL, created_by_user_id VARCHAR(36) NOT NULL, assigned_user_id VARCHAR(36) NULL, item_type VARCHAR(20) NOT NULL DEFAULT 'todo' COMMENT '항목 타입 (schedule, todo)', title VARCHAR(200) NOT NULL, target_date DATE NOT NULL, start_time TIME NULL, end_time TIME NULL, repeat_type VARCHAR(20) NOT NULL DEFAULT 'none' COMMENT '반복 타입 (none, daily, weekly, weekdays, monthly, yearly)', repeat_days VARCHAR(50) NULL COMMENT '주간 반복 요일(JSON 또는 쉼표 구분)', repeat_until DATE NULL COMMENT '반복 종료일', reminder_minutes INT NULL COMMENT '시작 전 알림 시간(분)', reminder_revision INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '알림 설정 변경 버전', share_with_family BOOLEAN NOT NULL DEFAULT TRUE COMMENT '가족 공유 여부', notify_on_completion BOOLEAN NOT NULL DEFAULT FALSE COMMENT '완료 시 공유 가족 알림 여부', notes TEXT NULL, is_completed BOOLEAN NOT NULL DEFAULT FALSE, completed_by_user_id VARCHAR(36) NULL, completed_at DATETIME NULL, display_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (family_id) REFERENCES babynote_families(id) ON DELETE CASCADE, INDEX idx_family_target_date (family_id, target_date), INDEX idx_family_date_completed (family_id, target_date, is_completed), INDEX idx_assigned_user_date (assigned_user_id, target_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """ ALTER_COLUMNS = [ "ADD COLUMN repeat_type VARCHAR(20) NOT NULL DEFAULT 'none' COMMENT '반복 타입 (none, daily, weekly, weekdays, monthly, yearly)' AFTER end_time", "ADD COLUMN repeat_days VARCHAR(50) NULL COMMENT '주간 반복 요일(JSON 또는 쉼표 구분)' AFTER repeat_type", "ADD COLUMN repeat_until DATE NULL COMMENT '반복 종료일' AFTER repeat_days", "ADD COLUMN reminder_minutes INT NULL COMMENT '시작 전 알림 시간(분)' AFTER repeat_until", "ADD COLUMN reminder_revision INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '알림 설정 변경 버전' AFTER reminder_minutes", "ADD COLUMN share_with_family BOOLEAN NOT NULL DEFAULT TRUE COMMENT '가족 공유 여부' AFTER reminder_revision", "ADD COLUMN notify_on_completion BOOLEAN NOT NULL DEFAULT FALSE COMMENT '완료 시 공유 가족 알림 여부' AFTER share_with_family", ] CREATE_SHARES_TABLE_QUERY = """ CREATE TABLE IF NOT EXISTS babynote_together_care_shares ( id VARCHAR(36) PRIMARY KEY, item_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, permission_level VARCHAR(20) NOT NULL DEFAULT 'check' COMMENT '공유 권한 (check, view)', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (item_id) REFERENCES babynote_together_care_items(id) ON DELETE CASCADE, UNIQUE KEY unique_together_care_share (item_id, user_id), INDEX idx_together_care_share_user (user_id, item_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """ CREATE_ASSIGNEES_TABLE_QUERY = """ CREATE TABLE IF NOT EXISTS babynote_together_care_assignees ( id VARCHAR(36) PRIMARY KEY, item_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (item_id) REFERENCES babynote_together_care_items(id) ON DELETE CASCADE, UNIQUE KEY unique_together_care_assignee (item_id, user_id), INDEX idx_together_care_assignee_user (user_id, item_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """ BACKFILL_ASSIGNEES_QUERY = """ INSERT IGNORE INTO babynote_together_care_assignees (id, item_id, user_id) SELECT UUID(), id, assigned_user_id FROM babynote_together_care_items WHERE assigned_user_id IS NOT NULL AND assigned_user_id != '' """ CREATE_COMPLETIONS_TABLE_QUERY = """ CREATE TABLE IF NOT EXISTS babynote_together_care_completions ( id VARCHAR(36) PRIMARY KEY, item_id VARCHAR(36) NOT NULL, occurrence_date DATE NOT NULL, completed_by_user_id VARCHAR(36) NOT NULL, completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (item_id) REFERENCES babynote_together_care_items(id) ON DELETE CASCADE, UNIQUE KEY unique_together_care_completion (item_id, occurrence_date), INDEX idx_together_care_completion_date (occurrence_date, item_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """ CREATE_REMINDER_LOGS_TABLE_QUERY = """ CREATE TABLE IF NOT EXISTS babynote_together_care_reminder_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, item_id VARCHAR(36) NOT NULL, occurrence_date DATE NOT NULL, recipient_user_id VARCHAR(36) NOT NULL, reminder_minutes INT NOT NULL, reminder_revision INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '알림 설정 변경 버전', notification_id INT NULL, push_status VARCHAR(20) NOT NULL DEFAULT 'processing', push_error TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (item_id) REFERENCES babynote_together_care_items(id) ON DELETE CASCADE, UNIQUE KEY unique_together_care_reminder_revision (item_id, occurrence_date, recipient_user_id, reminder_revision), INDEX idx_together_care_reminder_recipient (recipient_user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """ REMINDER_LOG_COLUMNS = [ "ADD COLUMN reminder_revision INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '알림 설정 변경 버전' AFTER reminder_minutes", ] NOTIFICATION_COLUMNS = [ "ADD COLUMN together_care_item_id VARCHAR(36) NULL COMMENT '관련 함께 챙기기 항목 ID' AFTER kidsnote_preview_id", "ADD COLUMN together_care_occurrence_date DATE NULL COMMENT '관련 함께 챙기기 발생일' AFTER together_care_item_id", ] def run(): connection = None cursor = None try: connection = mysql.connector.connect(**DB_CONFIG) cursor = connection.cursor() print("DB Connected") cursor.execute(CREATE_TABLE_QUERY) for column_sql in ALTER_COLUMNS: try: cursor.execute(f"ALTER TABLE babynote_together_care_items {column_sql}") except mysql.connector.Error as error: if error.errno != 1060: raise cursor.execute(CREATE_SHARES_TABLE_QUERY) cursor.execute(CREATE_ASSIGNEES_TABLE_QUERY) cursor.execute(BACKFILL_ASSIGNEES_QUERY) cursor.execute(CREATE_COMPLETIONS_TABLE_QUERY) cursor.execute(CREATE_REMINDER_LOGS_TABLE_QUERY) for column_sql in REMINDER_LOG_COLUMNS: try: cursor.execute( f"ALTER TABLE babynote_together_care_reminder_logs {column_sql}" ) except mysql.connector.Error as error: if error.errno != 1060: raise cursor.execute( "UPDATE babynote_together_care_items item " "SET item.reminder_revision = 1 " "WHERE item.reminder_revision = 0 " "AND EXISTS (" "SELECT 1 FROM babynote_together_care_reminder_logs reminder " "WHERE reminder.item_id = item.id" ")" ) cursor.execute( "SHOW INDEX FROM babynote_together_care_reminder_logs " "WHERE Key_name = 'unique_together_care_reminder_revision'" ) reminder_revision_indexes = cursor.fetchall() if not reminder_revision_indexes: cursor.execute( "ALTER TABLE babynote_together_care_reminder_logs " "ADD UNIQUE KEY unique_together_care_reminder_revision " "(item_id, occurrence_date, recipient_user_id, reminder_revision)" ) cursor.execute( "SHOW INDEX FROM babynote_together_care_reminder_logs " "WHERE Key_name = 'unique_together_care_reminder'" ) old_reminder_indexes = cursor.fetchall() if old_reminder_indexes: cursor.execute( "ALTER TABLE babynote_together_care_reminder_logs " "DROP INDEX unique_together_care_reminder" ) for column_sql in NOTIFICATION_COLUMNS: try: cursor.execute(f"ALTER TABLE notifications {column_sql}") except mysql.connector.Error as error: if error.errno != 1060: raise try: cursor.execute( "ALTER TABLE notifications " "ADD INDEX idx_together_care_item_id (together_care_item_id)" ) except mysql.connector.Error as error: if error.errno != 1061: raise connection.commit() print("OK: together care items, assignees, shares, completions, reminders, notifications") finally: if cursor is not None: cursor.close() if connection is not None and connection.is_connected(): connection.close() print("DB connection closed") if __name__ == "__main__": run()