"""
資料庫初始化腳本
用於在 Supabase 中建立必要的資料表
"""

from supabase import create_client
from backend.config import settings
import logging

logger = logging.getLogger(__name__)

# SQL 建表語句
CREATE_TABLES_SQL = {
    "users": """
    CREATE TABLE IF NOT EXISTS users (
        user_id VARCHAR(255) PRIMARY KEY,
        name VARCHAR(255),
        email VARCHAR(255),
        display_name VARCHAR(255),
        picture_url TEXT,
        status_message TEXT,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );
    """,
    
    "products": """
    CREATE TABLE IF NOT EXISTS products (
        product_id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT,
        price DECIMAL(10,2) NOT NULL,
        stock INTEGER DEFAULT 0,
        category VARCHAR(100),
        image_url TEXT,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );
    """,
    
    "orders": """
    CREATE TABLE IF NOT EXISTS orders (
        order_id VARCHAR(255) PRIMARY KEY,
        user_id VARCHAR(255) REFERENCES users(user_id),
        total_amount DECIMAL(10,2) NOT NULL,
        status VARCHAR(50) DEFAULT 'pending',
        shipping_address TEXT,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );
    """,
    
    "order_items": """
    CREATE TABLE IF NOT EXISTS order_items (
        id SERIAL PRIMARY KEY,
        order_id VARCHAR(255) REFERENCES orders(order_id),
        product_id INTEGER REFERENCES products(product_id),
        quantity INTEGER NOT NULL,
        unit_price DECIMAL(10,2) NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );
    """,
    
    "line_messages": """
    CREATE TABLE IF NOT EXISTS line_messages (
        id SERIAL PRIMARY KEY,
        user_id VARCHAR(255),
        message TEXT NOT NULL,
        message_type VARCHAR(50) DEFAULT 'text',
        timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        processed BOOLEAN DEFAULT FALSE
    );
    """,
    
    "user_sessions": """
    CREATE TABLE IF NOT EXISTS user_sessions (
        id SERIAL PRIMARY KEY,
        user_id VARCHAR(255) REFERENCES users(user_id),
        session_data JSONB,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );
    """
}

# 範例資料
SAMPLE_DATA = {
    "products": [
        {
            "name": "iPhone 15 Pro",
            "description": "最新款 iPhone，配備 A17 Pro 晶片",
            "price": 35900,
            "stock": 50,
            "category": "手機"
        },
        {
            "name": "MacBook Air M2",
            "description": "輕薄筆記型電腦，搭載 M2 晶片",
            "price": 37900,
            "stock": 30,
            "category": "筆電"
        },
        {
            "name": "AirPods Pro",
            "description": "主動降噪無線耳機",
            "price": 7490,
            "stock": 100,
            "category": "耳機"
        }
    ]
}

def init_database():
    """初始化資料庫"""
    try:
        supabase = create_client(settings.SUPABASE_URL, settings.SUPABASE_KEY)
        
        logger.info("開始初始化資料庫...")
        
        # 建立資料表
        for table_name, sql in CREATE_TABLES_SQL.items():
            try:
                logger.info(f"建立資料表: {table_name}")
                # 注意：Supabase 的 Python 客戶端可能不直接支援 SQL 執行
                # 您需要在 Supabase Dashboard 的 SQL Editor 中執行這些 SQL
                print(f"\n=== {table_name.upper()} TABLE SQL ===")
                print(sql)
                
            except Exception as e:
                logger.error(f"建立資料表 {table_name} 失敗: {str(e)}")
        
        # 插入範例資料
        logger.info("插入範例資料...")
        for table_name, data in SAMPLE_DATA.items():
            try:
                result = supabase.table(table_name).insert(data).execute()
                logger.info(f"成功插入 {len(result.data)} 筆資料到 {table_name}")
            except Exception as e:
                logger.warning(f"插入範例資料到 {table_name} 失敗: {str(e)}")
        
        logger.info("資料庫初始化完成！")
        return True
        
    except Exception as e:
        logger.error(f"資料庫初始化失敗: {str(e)}")
        return False

if __name__ == "__main__":
    init_database()