import json
import os
from pathlib import Path

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 named_card_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  template_key VARCHAR(100) NOT NULL,
  name VARCHAR(255) NOT NULL,
  side ENUM('front', 'back') NOT NULL,
  thumbnail_path TEXT,
  background_color BIGINT UNSIGNED NOT NULL DEFAULT 4294967295,
  border_radius DECIMAL(10, 2) NOT NULL DEFAULT 12.00,
  category VARCHAR(100) NOT NULL DEFAULT 'basic',
  default_elements LONGTEXT NOT NULL,
  data_field_ids LONGTEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_named_card_templates_template_key (template_key),
  KEY idx_named_card_templates_side_active_sort (side, is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


def load_seed_templates():
    seed_path = Path(__file__).with_name("seed_named_templates.json")
    with seed_path.open("r", encoding="utf-8") as file:
        return json.load(file)


def run():
    connection = mysql.connector.connect(**DB_CONFIG)
    cursor = connection.cursor()

    try:
        print("DB Connected")
        cursor.execute(CREATE_TABLE_SQL)
        connection.commit()
        print("OK: create named_card_templates")

        seed_data = load_seed_templates()
        front_templates = seed_data.get("front", [])
        back_templates = seed_data.get("back", [])

        insert_sql = """
        INSERT IGNORE INTO named_card_templates (
          template_key,
          name,
          side,
          thumbnail_path,
          background_color,
          border_radius,
          category,
          default_elements,
          data_field_ids,
          sort_order,
          is_active
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 1)
        """

        total_seeded = 0
        for side, templates in (("front", front_templates), ("back", back_templates)):
            for index, template in enumerate(templates):
                cursor.execute(
                    insert_sql,
                    (
                        template["id"],
                        template["name"],
                        side,
                        template.get("thumbnailPath", ""),
                        int(template.get("backgroundColor", 4294967295)),
                        float(template.get("borderRadius", 12)),
                        template.get("category", "basic"),
                        json.dumps(template.get("defaultElements", []), ensure_ascii=False),
                        json.dumps(template.get("dataFieldIds", []), ensure_ascii=False),
                        index + 1,
                    ),
                )
                total_seeded += cursor.rowcount

        connection.commit()
        print(f"OK: seeded {total_seeded} named card templates")
        print("=== named card templates migration completed ===")
    finally:
        cursor.close()
        connection.close()
        print("DB connection closed")


if __name__ == "__main__":
    run()