#!/usr/bin/env python3
import os
import sys
from pathlib import Path


def load_env(env_path: Path) -> dict:
    data = {}
    if not env_path.exists():
        return data
    for line in env_path.read_text(encoding='utf-8').splitlines():
        line = line.strip()
        if not line or line.startswith('#') or '=' not in line:
            continue
        key, value = line.split('=', 1)
        data[key.strip()] = value.strip()
    return data


def get_mysql_connector():
    try:
        import mysql.connector  # type: ignore
        return mysql.connector
    except Exception:
        print('mysql-connector-python is required. Install with: pip install mysql-connector-python', file=sys.stderr)
        sys.exit(1)


def load_all_env():
    env_files = [
        Path(__file__).with_name('.env'),
        Path(__file__).resolve().parent.parent / '.env',
    ]
    loaded = {}
    for env_file in env_files:
        loaded.update(load_env(env_file))
    return {**os.environ, **loaded}


def main():
    named_android_banner_ad_id = 'ca-app-pub-1472588829285826/3548945533'
    env = load_all_env()
    required = ['DB_HOST', 'DB_USER', 'DB_PASSWORD', 'DB_NAME']
    if not all(env.get(key) for key in required):
        print('Missing DB_* settings. Check your .env or environment variables.', file=sys.stderr)
        sys.exit(1)

    mysql = get_mysql_connector()
    conn = mysql.connect(
        host=env['DB_HOST'],
        user=env['DB_USER'],
        password=env['DB_PASSWORD'],
        port=int(env.get('DB_PORT', '23306')),
        database=env['DB_NAME'],
        charset='utf8mb4',
    )
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS named_ad_settings (
          id INT PRIMARY KEY AUTO_INCREMENT,
          ad_mode ENUM('test', 'release') DEFAULT 'test',
          ios_banner_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-1472588829285826/7536356614',
          android_banner_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-1472588829285826/3548945533',
          ios_rewarded_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-1472588829285826/4365516376',
          android_rewarded_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-1472588829285826/7388329297',
          test_ios_banner_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-3940256099942544/2934735716',
          test_android_banner_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-3940256099942544/6300978111',
          test_ios_rewarded_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-3940256099942544/1712485313',
          test_android_rewarded_ad_id VARCHAR(100) DEFAULT 'ca-app-pub-3940256099942544/5224354917',
          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
    """)

    cursor.execute("""
        INSERT IGNORE INTO named_ad_settings (
          id, ad_mode, ios_banner_ad_id, android_banner_ad_id, ios_rewarded_ad_id, android_rewarded_ad_id,
          test_ios_banner_ad_id, test_android_banner_ad_id, test_ios_rewarded_ad_id, test_android_rewarded_ad_id
        ) VALUES (
          1, 'test', 'ca-app-pub-1472588829285826/7536356614', 'ca-app-pub-1472588829285826/3548945533', 'ca-app-pub-1472588829285826/4365516376', 'ca-app-pub-1472588829285826/7388329297',
          'ca-app-pub-3940256099942544/2934735716',
          'ca-app-pub-3940256099942544/6300978111',
          'ca-app-pub-3940256099942544/1712485313',
          'ca-app-pub-3940256099942544/5224354917'
        )
    """)

    cursor.execute("""
        UPDATE named_ad_settings
        SET android_banner_ad_id = %s
        WHERE id = 1
          AND (
            android_banner_ad_id IS NULL
            OR android_banner_ad_id = ''
            OR android_banner_ad_id = 'ca-app-pub-3940256099942544/6300978111'
          )
    """, (named_android_banner_ad_id,))

    conn.commit()
    cursor.close()
    conn.close()
    print('Named ad settings synced without overwriting existing values.')


if __name__ == '__main__':
    main()