import os

import mysql.connector
from mysql.connector import Error


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", ""),
    "database": os.environ.get("DB_NAME", "app_master"),
}


CREATE_TABLE = """
CREATE TABLE IF NOT EXISTS babynote_temperament_result_cards (
  id VARCHAR(36) PRIMARY KEY,
  type_id VARCHAR(36) NOT NULL,
  title VARCHAR(150) NULL,
  description TEXT NOT NULL,
  image_url TEXT NULL,
  score_axis VARCHAR(32) NULL,
  min_score DECIMAL(5,2) NULL,
  max_score DECIMAL(5,2) NULL,
  priority INT DEFAULT 0,
  order_index INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_result_cards_type_order (type_id, is_active, order_index),
  INDEX idx_result_cards_selection (type_id, is_active, priority, order_index),
  CONSTRAINT fk_temperament_result_cards_type
    FOREIGN KEY (type_id) REFERENCES babynote_temperament_types(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


ALTERS = [
    "ALTER TABLE babynote_temperament_result_cards ADD COLUMN score_axis VARCHAR(32) NULL AFTER image_url",
    "ALTER TABLE babynote_temperament_result_cards ADD COLUMN min_score DECIMAL(5,2) NULL AFTER score_axis",
    "ALTER TABLE babynote_temperament_result_cards ADD COLUMN max_score DECIMAL(5,2) NULL AFTER min_score",
    "ALTER TABLE babynote_temperament_result_cards ADD COLUMN priority INT DEFAULT 0 AFTER max_score",
    "CREATE INDEX idx_result_cards_selection ON babynote_temperament_result_cards (type_id, is_active, priority, order_index)",
]


def run():
    if not DB_CONFIG["password"]:
        raise RuntimeError("DB_PASSWORD environment variable is required")

    connection = mysql.connector.connect(**DB_CONFIG)
    cursor = connection.cursor()
    try:
        cursor.execute(CREATE_TABLE)
        connection.commit()
        print("OK: result behavior cards table ready")

        for query in ALTERS:
            try:
                cursor.execute(query)
                connection.commit()
                print("OK:", query)
            except Error as error:
                if error.errno in (1060, 1061):
                    print("SKIP (already exists):", query)
                else:
                    raise

        print("OK: temperament behavior score rules migration completed")
    finally:
        cursor.close()
        connection.close()


if __name__ == "__main__":
    run()