# -*- coding: utf-8 -*-
import pymysql

# 데이터베이스 연결 설정
DB_CONFIG = {
    'host': 'officialsite.kr',
    'user': 'admin',
    'password': 'dlgks~123',
    'port': 23306,
    'database': 'app_master',
    'charset': 'utf8mb4'
}

def init_calmoa_tables():
    connection = None
    try:
        print('[INFO] MySQL connecting...')
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()
        print('[OK] MySQL connected')

        # 외래 키 체크 일시 비활성화
        cursor.execute('SET FOREIGN_KEY_CHECKS = 0')

        # 1. 약속 테이블 생성
        create_appointments_table = '''
            CREATE TABLE IF NOT EXISTS calmoa_appointments (
                id VARCHAR(36) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                start_date DATE NOT NULL,
                end_date DATE NOT NULL,
                selected_dates TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                device_serial VARCHAR(255),
                INDEX idx_device_serial (device_serial),
                INDEX idx_created_at (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        '''
        cursor.execute(create_appointments_table)
        cursor.execute("""
            ALTER TABLE calmoa_appointments
            ADD COLUMN IF NOT EXISTS selected_dates TEXT NULL AFTER end_date
        """)
        print('[OK] calmoa_appointments table created')

        # 2. 응답 테이블 생성
        create_responses_table = '''
            CREATE TABLE IF NOT EXISTS calmoa_responses (
                id VARCHAR(36) PRIMARY KEY,
                appointment_id VARCHAR(36) NOT NULL,
                responder_name VARCHAR(255) NOT NULL,
                submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_appointment_id (appointment_id),
                INDEX idx_submitted_at (submitted_at),
                FOREIGN KEY (appointment_id) REFERENCES calmoa_appointments(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        '''
        cursor.execute(create_responses_table)
        print('[OK] calmoa_responses table created')

        # 3. 날짜별 응답 테이블 생성
        create_date_responses_table = '''
            CREATE TABLE IF NOT EXISTS calmoa_date_responses (
                id VARCHAR(36) PRIMARY KEY,
                response_id VARCHAR(36) NOT NULL,
                date DATE NOT NULL,
                status TINYINT NOT NULL COMMENT '0=available, 1=maybe, 2=unavailable',
                memo TEXT,
                INDEX idx_response_id (response_id),
                INDEX idx_date (date),
                FOREIGN KEY (response_id) REFERENCES calmoa_responses(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        '''
        cursor.execute(create_date_responses_table)
        print('[OK] calmoa_date_responses table created')

        # 외래 키 체크 다시 활성화
        cursor.execute('SET FOREIGN_KEY_CHECKS = 1')

        connection.commit()
        print('[DONE] Calmoa database initialized!')

        # 테이블 확인
        cursor.execute("SHOW TABLES LIKE 'calmoa_%'")
        tables = cursor.fetchall()
        print('\n[Tables]:')
        for table in tables:
            print(f'   - {table[0]}')

    except pymysql.Error as e:
        print(f'[ERROR] Database error: {e}')
    finally:
        if connection:
            connection.close()
            print('\n[INFO] Database connection closed')

if __name__ == '__main__':
    init_calmoa_tables()