import os
from dotenv import load_dotenv
import mysql.connector

load_dotenv()

DB_HOST = os.getenv("DB_HOST", "officialsite.kr")
DB_PORT = int(os.getenv("DB_PORT", "23306"))
DB_USER = os.getenv("DB_USER", "kssoft_ad_mgr")
DB_PASSWORD = os.getenv("DB_PASSWORD", "")
DB_NAME = os.getenv("DB_NAME", "kssoft_v2")

TABLES = [
    """
    CREATE TABLE IF NOT EXISTS ftp (
        programname VARCHAR(100) PRIMARY KEY,
        ver VARCHAR(20) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS ad_master_user (
        id INT AUTO_INCREMENT PRIMARY KEY,
        c VARCHAR(50) NOT NULL DEFAULT '0',
        hardnum VARCHAR(64) NOT NULL,
        black TINYINT(1) NOT NULL DEFAULT 0,
        date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        useable TINYINT(1) NOT NULL DEFAULT 0,
        code TEXT,
        UNIQUE KEY uq_ad_master_user_hardnum (hardnum)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS ad_master_platform (
        id INT AUTO_INCREMENT PRIMARY KEY,
        platform_kor VARCHAR(100) NOT NULL,
        icon LONGBLOB
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS ad_master (
        id INT AUTO_INCREMENT PRIMARY KEY,
        platform_id INT NOT NULL,
        c VARCHAR(50) NOT NULL,
        name VARCHAR(150) NOT NULL,
        icon LONGBLOB,
        INDEX idx_ad_master_platform_id (platform_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS ad_master_user_match (
        id INT AUTO_INCREMENT PRIMARY KEY,
        hardnum_idx VARCHAR(50) NOT NULL,
        admin_idx INT NOT NULL,
        INDEX idx_ad_master_user_match_hardnum (hardnum_idx),
        INDEX idx_ad_master_user_match_admin (admin_idx)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS ad_master_member (
        idx INT PRIMARY KEY,
        credit_balance DECIMAL(12,2) NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS module_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        module_id VARCHAR(100) NOT NULL,
        hwid VARCHAR(64) NOT NULL,
        settings_json TEXT NOT NULL,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_module_settings (module_id, hwid)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS adpick_offers (
        offer_code VARCHAR(64) NOT NULL PRIMARY KEY,
        app_title VARCHAR(255) NOT NULL,
        headline TEXT NULL,
        promo_text MEDIUMTEXT NULL,
        video_url TEXT NULL,
        tracking_link TEXT NOT NULL,
        icon_url TEXT NULL,
        banner_url TEXT NULL,
        square_image_url TEXT NULL,
        wide_image_url TEXT NULL,
        offer_type VARCHAR(32) NULL,
        category VARCHAR(32) NULL,
        os VARCHAR(32) NULL,
        remain_count INT NOT NULL DEFAULT 0,
        daily_cap INT NOT NULL DEFAULT 0,
        partner TEXT NULL,
        reward_amount DECIMAL(12,2) NULL,
        raw_json LONGTEXT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_adpick_offers_active (is_active),
        INDEX idx_adpick_offers_sort (reward_amount, remain_count, synced_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,
    """
    CREATE TABLE IF NOT EXISTS module_daily_usage (
        id INT AUTO_INCREMENT PRIMARY KEY,
        module_id VARCHAR(100) NOT NULL,
        hwid VARCHAR(64) NOT NULL,
        usage_date DATE NOT NULL,
        free_starts INT NOT NULL DEFAULT 0,
        unlocked_starts INT NOT NULL DEFAULT 0,
        ad_clicks INT NOT NULL DEFAULT 0,
        last_ad_offer_code VARCHAR(64) NULL,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_module_daily_usage (module_id, hwid, usage_date),
        INDEX idx_module_daily_usage_hwid (hwid, usage_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
]


def main():
    conn = mysql.connector.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME,
    )
    cursor = conn.cursor()

    for stmt in TABLES:
        cursor.execute(stmt)

    conn.commit()
    cursor.close()
    conn.close()

    print("[OK] Tables created/verified in", DB_NAME)


if __name__ == "__main__":
    main()