import pymysql
import uuid
import sys
import io
from datetime import datetime

# Fix Windows encoding issue
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

# Database configuration
DB_CONFIG = {
    'host': 'officialsite.kr',
    'user': 'admin',
    'password': 'dlgks~123',
    'port': 23306,
    'database': 'app_master',
    'charset': 'utf8mb4'
}

def create_tables():
    connection = None
    try:
        print('Connecting to database...')
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()

        # Disable foreign key checks
        cursor.execute('SET FOREIGN_KEY_CHECKS = 0')

        # 1. Create tapcounter_purchases table
        print('Creating tapcounter_purchases table...')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tapcounter_purchases (
                id VARCHAR(36) PRIMARY KEY,
                user_id VARCHAR(255) NOT NULL,
                product_id VARCHAR(100) NOT NULL COMMENT '상품 ID (tapcounter_ad_removal, tapcounter_ad_removal_yearly)',
                product_type ENUM('non_consumable', 'subscription') NOT NULL,
                platform ENUM('android', 'ios') NOT NULL,
                transaction_id VARCHAR(255) NOT NULL COMMENT '스토어 트랜잭션 ID',
                original_transaction_id VARCHAR(255) COMMENT '원본 트랜잭션 ID (구독용)',
                purchase_token TEXT COMMENT 'Android 구매 토큰',
                receipt_data LONGTEXT COMMENT 'iOS 영수증 데이터',
                price INT DEFAULT 0,
                currency VARCHAR(10) DEFAULT 'KRW',
                status ENUM('pending', 'completed', 'refunded', 'cancelled', 'expired') DEFAULT 'pending',
                purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                expires_at TIMESTAMP NULL COMMENT '구독 만료일',
                refunded_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_user_id (user_id),
                INDEX idx_product_id (product_id),
                INDEX idx_transaction_id (transaction_id),
                INDEX idx_status (status),
                INDEX idx_expires_at (expires_at),
                UNIQUE KEY unique_transaction (transaction_id, platform)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ''')
        print('✅ tapcounter_purchases table created')

        # 2. Create tapcounter_ad_removal table
        print('Creating tapcounter_ad_removal table...')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tapcounter_ad_removal (
                id VARCHAR(36) PRIMARY KEY,
                user_id VARCHAR(255) NOT NULL UNIQUE,
                purchase_id VARCHAR(36),
                is_active BOOLEAN DEFAULT FALSE,
                subscription_type ENUM('lifetime', 'yearly') COMMENT '구독 타입',
                expires_at TIMESTAMP NULL COMMENT '만료일 (yearly인 경우)',
                activated_at TIMESTAMP NULL,
                deactivated_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_user_id (user_id),
                INDEX idx_is_active (is_active),
                INDEX idx_expires_at (expires_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ''')
        print('✅ tapcounter_ad_removal table created')

        # 3. Create tapcounter_purchase_logs table
        print('Creating tapcounter_purchase_logs table...')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS tapcounter_purchase_logs (
                id VARCHAR(36) PRIMARY KEY,
                purchase_id VARCHAR(36),
                user_id VARCHAR(255),
                action VARCHAR(50) NOT NULL COMMENT 'purchase, verify, refund, expire, renew, restore',
                product_id VARCHAR(100),
                platform VARCHAR(20),
                request_data LONGTEXT,
                response_data LONGTEXT,
                error_message TEXT,
                ip_address VARCHAR(45),
                user_agent TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_purchase_id (purchase_id),
                INDEX idx_user_id (user_id),
                INDEX idx_action (action),
                INDEX idx_created_at (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ''')
        print('✅ tapcounter_purchase_logs table created')

        # 4. Insert app_settings for tapcounter
        print('Inserting tapcounter app_settings...')
        settings = [
            ('tapcounter', 'ad_removal_price', '4900', '광고 제거 (평생) 가격'),
            ('tapcounter', 'ad_removal_yearly_price', '2900', '광고 제거 (1년) 가격')
        ]

        for setting in settings:
            cursor.execute('''
                INSERT IGNORE INTO app_settings (app_name, setting_key, setting_value, description)
                VALUES (%s, %s, %s, %s)
            ''', setting)
        print('✅ tapcounter app_settings inserted')

        # Re-enable foreign key checks
        cursor.execute('SET FOREIGN_KEY_CHECKS = 1')

        connection.commit()
        print('\n🚀 All TapCounter tables created successfully!')

        # Verify tables were created
        print('\nVerifying tables...')
        cursor.execute("SHOW TABLES LIKE 'tapcounter%'")
        tables = cursor.fetchall()
        print(f'Found {len(tables)} tapcounter tables:')
        for table in tables:
            print(f'  - {table[0]}')

    except Exception as e:
        print(f'❌ Error: {e}')
        if connection:
            connection.rollback()
    finally:
        if connection:
            connection.close()
            print('\nDatabase connection closed.')

if __name__ == '__main__':
    create_tables()