import mysql.connector

DDL_LIST = [
    '''
    CREATE TABLE IF NOT EXISTS plant_reminder_settings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        notifications_enabled BOOLEAN DEFAULT TRUE,
        notification_hour INT DEFAULT 9,
        notification_minute INT DEFAULT 0,
        default_warning_message VARCHAR(255) DEFAULT '오늘 물줘야 할 식물이 있어요.',
        overdue_warning_message VARCHAR(255) DEFAULT '오래 물을 주지 않은 식물이 있어요.',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ''',
    '''
    CREATE TABLE IF NOT EXISTS plant_reminder_presets (
        id INT PRIMARY KEY AUTO_INCREMENT,
        type_name VARCHAR(100) NOT NULL,
        type_name_ko VARCHAR(100) DEFAULT NULL,
        type_name_en VARCHAR(100) DEFAULT NULL,
        type_name_ja VARCHAR(100) DEFAULT NULL,
        type_name_zh VARCHAR(100) DEFAULT NULL,
        watering_cycle_days INT NOT NULL DEFAULT 7,
        sunlight VARCHAR(100) DEFAULT '',
        sunlight_ko VARCHAR(255) DEFAULT NULL,
        sunlight_en VARCHAR(255) DEFAULT NULL,
        sunlight_ja VARCHAR(255) DEFAULT NULL,
        sunlight_zh VARCHAR(255) DEFAULT NULL,
        tip VARCHAR(255) DEFAULT '',
        tip_ko TEXT DEFAULT NULL,
        tip_en TEXT DEFAULT NULL,
        tip_ja TEXT DEFAULT NULL,
        tip_zh TEXT DEFAULT NULL,
        image_url VARCHAR(500) DEFAULT NULL,
        image_path VARCHAR(500) DEFAULT NULL,
        is_active BOOLEAN DEFAULT TRUE,
        sort_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_plant_reminder_presets_type_name (type_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ''',
    '''
    CREATE TABLE IF NOT EXISTS plant_reminder_push_history (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(255) NOT NULL,
        body TEXT NOT NULL,
        target_type VARCHAR(50) DEFAULT 'all',
        target_value VARCHAR(255) DEFAULT NULL,
        sent_by VARCHAR(100) DEFAULT NULL,
        status VARCHAR(50) DEFAULT 'queued',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ''',
    '''
    CREATE TABLE IF NOT EXISTS plant_reminder_ad_settings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        ad_mode ENUM('test', 'release') DEFAULT 'test',
        ios_banner_ad_id VARCHAR(255) DEFAULT 'ca-app-pub-1472588829285826/2199935347',
        ios_interstitial_ad_id VARCHAR(255) DEFAULT 'ca-app-pub-1472588829285826/2016102510',
        android_banner_ad_id VARCHAR(255) DEFAULT '',
        android_interstitial_ad_id VARCHAR(255) DEFAULT '',
        test_ios_banner_ad_id VARCHAR(255) DEFAULT 'ca-app-pub-3940256099942544/2934735716',
        test_ios_interstitial_ad_id VARCHAR(255) DEFAULT 'ca-app-pub-3940256099942544/4411468910',
        test_android_banner_ad_id VARCHAR(255) DEFAULT 'ca-app-pub-3940256099942544/6300978111',
        test_android_interstitial_ad_id VARCHAR(255) DEFAULT 'ca-app-pub-3940256099942544/1033173712',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    '''
]

SEED_SQL = [
    '''
    INSERT INTO plant_reminder_settings (
        notifications_enabled,
        notification_hour,
        notification_minute,
        default_warning_message,
        overdue_warning_message
    )
    SELECT TRUE, 9, 0, '오늘 물줘야 할 식물이 있어요.', '오래 물을 주지 않은 식물이 있어요.'
    WHERE NOT EXISTS (SELECT 1 FROM plant_reminder_settings)
    ''',
    '''
    INSERT INTO plant_reminder_ad_settings (
        ad_mode,
        ios_banner_ad_id,
        ios_interstitial_ad_id,
        test_ios_banner_ad_id,
        test_ios_interstitial_ad_id,
        test_android_banner_ad_id,
        test_android_interstitial_ad_id
    )
    SELECT 'test',
           'ca-app-pub-1472588829285826/2199935347',
           'ca-app-pub-1472588829285826/2016102510',
           'ca-app-pub-3940256099942544/2934735716',
           'ca-app-pub-3940256099942544/4411468910',
           'ca-app-pub-3940256099942544/6300978111',
           'ca-app-pub-3940256099942544/1033173712'
    WHERE NOT EXISTS (SELECT 1 FROM plant_reminder_ad_settings)
    ''',
]

PRESET_ROWS = [
    ('몬스테라', 5, '밝은 간접광', '흙 표면이 마르면 물주기', True, 1),
    ('스투키', 14, '밝은 곳', '과습 주의, 자주 주지 않기', True, 2),
    ('포토스', 6, '간접광', '잎이 축 처지기 전에 확인', True, 3),
    ('선인장', 21, '직사광 가능', '완전히 마른 뒤 물주기', True, 4),
    ('허브', 3, '햇빛 필요', '자주 확인하고 너무 마르지 않게', True, 5),
    ('고무나무', 7, '밝은 간접광', '통풍 좋은 곳에 두기', True, 6),
]

conn = mysql.connector.connect(
    host='officialsite.kr',
    port=23306,
    user='admin',
    password='dlgks~123',
    database='app_master',
    charset='utf8mb4',
)
cur = conn.cursor()

for ddl in DDL_LIST:
    cur.execute(ddl)

for sql in SEED_SQL:
    cur.execute(sql)

for row in PRESET_ROWS:
    cur.execute(
        '''
        INSERT INTO plant_reminder_presets (
            type_name, watering_cycle_days, sunlight, tip, is_active, sort_order
        )
        SELECT %s, %s, %s, %s, %s, %s
        WHERE NOT EXISTS (
            SELECT 1 FROM plant_reminder_presets WHERE type_name = %s
        )
        ''',
        (*row, row[0]),
    )

conn.commit()

for table in [
    'plant_reminder_settings',
    'plant_reminder_presets',
    'plant_reminder_push_history',
    'plant_reminder_ad_settings',
]:
    cur.execute(f'SELECT COUNT(*) FROM {table}')
    count = cur.fetchone()[0]
    print(f'{table}: {count}')

cur.close()
conn.close()
print('plant_reminder tables setup complete')