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


CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS ghost_running_ad_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ad_mode ENUM('test', 'release') DEFAULT 'test',
  ios_rewarded_ad_id VARCHAR(255),
  android_rewarded_ad_id VARCHAR(255),
  test_ios_rewarded_ad_id VARCHAR(255),
  test_android_rewarded_ad_id VARCHAR(255),
  voice_assist_reward_enabled TINYINT(1) NOT NULL DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


ALTERS = [
    "ALTER TABLE ghost_running_ad_settings ADD COLUMN ios_rewarded_ad_id VARCHAR(255) NULL AFTER ad_mode",
    "ALTER TABLE ghost_running_ad_settings ADD COLUMN android_rewarded_ad_id VARCHAR(255) NULL AFTER ios_rewarded_ad_id",
    "ALTER TABLE ghost_running_ad_settings ADD COLUMN test_ios_rewarded_ad_id VARCHAR(255) NULL AFTER android_rewarded_ad_id",
    "ALTER TABLE ghost_running_ad_settings ADD COLUMN test_android_rewarded_ad_id VARCHAR(255) NULL AFTER test_ios_rewarded_ad_id",
    "ALTER TABLE ghost_running_ad_settings ADD COLUMN voice_assist_reward_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER test_android_rewarded_ad_id",
]


INSERT_DEFAULTS_SQL = """
INSERT INTO ghost_running_ad_settings (
  ad_mode,
  ios_rewarded_ad_id,
  android_rewarded_ad_id,
  test_ios_rewarded_ad_id,
  test_android_rewarded_ad_id,
  voice_assist_reward_enabled
) VALUES (%s, %s, %s, %s, %s, %s)
"""


BACKFILL_SQL = """
UPDATE ghost_running_ad_settings
SET
  ios_rewarded_ad_id = COALESCE(NULLIF(ios_rewarded_ad_id, ''), %s),
  android_rewarded_ad_id = COALESCE(NULLIF(android_rewarded_ad_id, ''), %s),
  test_ios_rewarded_ad_id = COALESCE(NULLIF(test_ios_rewarded_ad_id, ''), %s),
  test_android_rewarded_ad_id = COALESCE(NULLIF(test_android_rewarded_ad_id, ''), %s),
  voice_assist_reward_enabled = COALESCE(voice_assist_reward_enabled, 1)
"""


DEFAULT_ROW = (
    "test",
    "ca-app-pub-1472588829285826/3632839466",
    "ca-app-pub-1472588829285826/1932744982",
    "ca-app-pub-3940256099942544/1712485313",
    "ca-app-pub-3940256099942544/5224354917",
    1,
)


BACKFILL_VALUES = (
    "ca-app-pub-1472588829285826/3632839466",
    "ca-app-pub-1472588829285826/1932744982",
    "ca-app-pub-3940256099942544/1712485313",
    "ca-app-pub-3940256099942544/5224354917",
)


def run():
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        print("DB Connected")

        cursor.execute(CREATE_TABLE_SQL)
        conn.commit()
        print("OK: create ghost_running_ad_settings")

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

        cursor.execute("SELECT id FROM ghost_running_ad_settings LIMIT 1")
        row = cursor.fetchone()
        if row is None:
            cursor.execute(INSERT_DEFAULTS_SQL, DEFAULT_ROW)
            conn.commit()
            print("OK: inserted default ghost_running_ad_settings row")

        cursor.execute(BACKFILL_SQL, BACKFILL_VALUES)
        conn.commit()
        print("OK: backfilled rewarded defaults")

        print("\n=== ghost_running_ad_settings rewarded migration completed ===")
    finally:
        if cursor is not None:
            cursor.close()
        if conn is not None and conn.is_connected():
            conn.close()
            print("DB connection closed")


if __name__ == "__main__":
    run()