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_SQL = """
CREATE TABLE IF NOT EXISTS babynote_kidsnote_requests (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  family_id VARCHAR(36) DEFAULT NULL,
  design_code VARCHAR(50) NOT NULL,
  design_name VARCHAR(100) NOT NULL,
  period_type VARCHAR(20) DEFAULT NULL,
  period_label VARCHAR(100) DEFAULT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  diary_count INT NOT NULL DEFAULT 0,
  image_count INT NOT NULL DEFAULT 0,
  selected_diary_ids LONGTEXT DEFAULT NULL,
  selected_preview_urls LONGTEXT DEFAULT NULL,
  estimated_pages INT NOT NULL DEFAULT 0,
  cover_type ENUM('hard', 'soft') NOT NULL DEFAULT 'hard',
  pdf_addon BOOLEAN NOT NULL DEFAULT FALSE,
  total_price INT NOT NULL DEFAULT 0,
  status ENUM('requested', 'reviewing', 'completed', 'cancelled') NOT NULL DEFAULT 'requested',
  admin_memo TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_family_id (family_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

CREATE_DESIGNS_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS babynote_kidsnote_designs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  title VARCHAR(100) NOT NULL,
  base_price INT NOT NULL DEFAULT 0,
  description TEXT DEFAULT NULL,
  thumbnail_url TEXT DEFAULT NULL,
  asset_path VARCHAR(255) DEFAULT NULL,
  badge VARCHAR(30) DEFAULT NULL,
  accent_color VARCHAR(20) NOT NULL DEFAULT '#FF7A72',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

DEFAULT_DESIGNS = [
    ("basic_white", "베이직 화이트", 21800, "차분하고 깨끗한 무드로 기록을 담아내는 기본형 디자인", "assets/images/kidsnote/design_basic_white.png", "BEST", "#2D2D2D", 10),
    ("cozy_coral", "포근한 코랄", 24800, "따뜻한 코랄 톤으로 사랑스러운 순간을 부드럽게 담는 디자인", "assets/images/kidsnote/design_cozy_coral.png", "NEW", "#FF7E7A", 20),
    ("growth_theme", "성장 기록 테마", 27800, "성장 과정이 또렷하게 드러나도록 구성한 기록 중심 디자인", "assets/images/kidsnote/design_growth_theme.png", "추천", "#93B86A", 30),
    ("dreamy_blue", "드리미 블루", 24800, "맑고 편안한 블루 톤으로 잔잔한 추억을 담아내는 디자인", "assets/images/kidsnote/design_dreamy_blue.png", "", "#7C8DCC", 40),
    ("natural_sage", "내추럴 세이지", 24800, "자연스러운 세이지 컬러로 담백하고 포근한 분위기를 전하는 디자인", "assets/images/kidsnote/design_natural_sage.png", "", "#7A8F6A", 50),
    ("minimal_modo", "미니멀 모노", 21800, "심플한 레이아웃으로 사진과 기록에 집중할 수 있는 미니멀 디자인", "assets/images/kidsnote/design_minimal_modo.png", "", "#FF7A72", 60),
]

INSERT_DESIGN_SQL = """
INSERT IGNORE INTO babynote_kidsnote_designs
  (code, title, base_price, description, thumbnail_url, asset_path, badge, accent_color, is_active, sort_order)
VALUES (%s, %s, %s, %s, '', %s, %s, %s, TRUE, %s)
"""

ALTER_DESCRIPTION_SQL = """
ALTER TABLE babynote_kidsnote_designs
ADD COLUMN description TEXT DEFAULT NULL AFTER base_price
"""

INSERT_SETTING_SQL = """
INSERT IGNORE INTO app_settings (app_name, setting_key, setting_value, setting_type, description)
VALUES 
  ('babynote', 'kidsnote_home_visible', 'false', 'boolean', '홈 화면 키즈노트 신청 배너 노출 여부'),
  ('babynote', 'kidsnote_home_banner_url', '', 'string', '홈 화면 키즈노트 신청 배너 이미지 URL')
"""


def run():
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()
    try:
        print("DB Connected")
        cursor.execute(CREATE_TABLE_SQL)
        conn.commit()
        print("OK: create babynote_kidsnote_requests")

        cursor.execute(CREATE_DESIGNS_TABLE_SQL)
        try:
            cursor.execute(ALTER_DESCRIPTION_SQL)
            conn.commit()
            print("OK: add description column to babynote_kidsnote_designs")
        except mysql.connector.Error as error:
            if error.errno != 1060:
                raise

        cursor.executemany(INSERT_DESIGN_SQL, DEFAULT_DESIGNS)
        for design in DEFAULT_DESIGNS:
            cursor.execute(
                """
                UPDATE babynote_kidsnote_designs
                   SET description = COALESCE(NULLIF(description, ''), %s)
                 WHERE code = %s
                """,
                (design[3], design[0]),
            )
        conn.commit()
        print("OK: create and seed babynote_kidsnote_designs")

        cursor.execute(INSERT_SETTING_SQL)
        conn.commit()
        print("OK: ensure kidsnote_home_visible setting")
        print("\n=== babynote kidsnote migration completed ===")
    finally:
        cursor.close()
        conn.close()
        print("DB connection closed")


if __name__ == "__main__":
    run()