<?php
$dbPath = __DIR__ . '/wedding_data.db';

try {
    $pdo = new PDO("sqlite:$dbPath");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("PRAGMA foreign_keys = ON");
    
    $sqls = [
        "CREATE TABLE IF NOT EXISTS video_assets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            video_id VARCHAR(50) NOT NULL UNIQUE,
            video_name VARCHAR(200) NOT NULL,
            video_type VARCHAR(20) DEFAULT '',
            storage_path VARCHAR(500) DEFAULT '',
            file_name VARCHAR(200) DEFAULT '',
            file_size INTEGER DEFAULT 0,
            duration INTEGER DEFAULT 0,
            format VARCHAR(10) DEFAULT '',
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            email VARCHAR(100) DEFAULT '',
            phone VARCHAR(20) DEFAULT '',
            login_attempts INTEGER DEFAULT 0,
            locked_until TIMESTAMP DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS testimonials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            author VARCHAR(50) NOT NULL,
            role VARCHAR(50) DEFAULT '新人',
            content TEXT NOT NULL,
            avatar VARCHAR(255) DEFAULT '',
            rating DECIMAL(2,1) DEFAULT 5.0,
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS custom_categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            icon VARCHAR(10) DEFAULT '📌',
            name VARCHAR(100) NOT NULL,
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS testimonials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category_id INTEGER NOT NULL,
            title VARCHAR(100) NOT NULL,
            content TEXT NOT NULL,
            tips VARCHAR(255) DEFAULT '',
            image_url VARCHAR(255) DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (category_id) REFERENCES custom_categories(id) ON DELETE CASCADE
        )",
        "CREATE TABLE IF NOT EXISTS budget_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category VARCHAR(50) NOT NULL,
            item_name VARCHAR(100) NOT NULL,
            estimated_amount DECIMAL(10,2) DEFAULT 0.00,
            actual_amount DECIMAL(10,2) DEFAULT 0.00,
            paid_amount DECIMAL(10,2) DEFAULT 0.00,
            is_essential INTEGER DEFAULT 0,
            priority INTEGER DEFAULT 0,
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS wedding_checklist (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category VARCHAR(50) NOT NULL,
            task_name VARCHAR(200) NOT NULL,
            priority VARCHAR(10) DEFAULT '中',
            deadline DATE DEFAULT NULL,
            is_completed INTEGER DEFAULT 0,
            completed_date DATE DEFAULT NULL,
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS vendors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(100) NOT NULL,
            type VARCHAR(20) NOT NULL,
            rating DECIMAL(2,1) DEFAULT 4.5,
            address VARCHAR(200) DEFAULT '',
            phone VARCHAR(30) DEFAULT '',
            description TEXT,
            website VARCHAR(200) DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS wedding_gallery (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title VARCHAR(100) NOT NULL,
            groom_name VARCHAR(30) DEFAULT '',
            bride_name VARCHAR(30) DEFAULT '',
            wedding_date DATE DEFAULT NULL,
            type VARCHAR(20) NOT NULL,
            description TEXT,
            gradient_start VARCHAR(20) DEFAULT '#e74c3c',
            gradient_end VARCHAR(20) DEFAULT '#c0392b',
            icon VARCHAR(10) DEFAULT '🏮',
            image_url VARCHAR(255) DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            view_count INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS wedding_day_schedule (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            time_slot VARCHAR(20) NOT NULL,
            segment VARCHAR(100) NOT NULL,
            responsible VARCHAR(100) DEFAULT '',
            helpers VARCHAR(200) DEFAULT '',
            notes TEXT,
            period VARCHAR(20) NOT NULL DEFAULT 'morning',
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS wedding_phase_videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            phase_code VARCHAR(50) NOT NULL,
            phase_name VARCHAR(100) NOT NULL,
            phase_order INTEGER NOT NULL DEFAULT 0,
            video_asset_id VARCHAR(50) DEFAULT NULL,
            video_url VARCHAR(500) DEFAULT NULL,
            start_time INTEGER NOT NULL DEFAULT 0,
            end_time INTEGER DEFAULT NULL,
            duration INTEGER DEFAULT NULL,
            description TEXT DEFAULT NULL,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (phase_code)
        )",
        "CREATE TABLE IF NOT EXISTS video_play_progress (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id VARCHAR(50) DEFAULT NULL,
            phase_code VARCHAR(50) NOT NULL,
            video_asset_id VARCHAR(50) DEFAULT NULL,
            current_time INTEGER NOT NULL DEFAULT 0,
            duration INTEGER DEFAULT NULL,
            is_completed INTEGER NOT NULL DEFAULT 0,
            last_played_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (user_id, phase_code)
        )",
        "CREATE TABLE IF NOT EXISTS video_play_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            video_id VARCHAR(50) NOT NULL,
            duration INTEGER DEFAULT 0,
            status VARCHAR(20) DEFAULT '',
            client_ip VARCHAR(50) DEFAULT '',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS agent_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            from_agent VARCHAR(50) NOT NULL,
            to_agent VARCHAR(50) NOT NULL,
            event_type VARCHAR(50) NOT NULL,
            payload TEXT,
            is_read INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS invitations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER DEFAULT NULL,
            bride_name VARCHAR(50) NOT NULL DEFAULT '',
            groom_name VARCHAR(50) NOT NULL DEFAULT '',
            wedding_date DATE DEFAULT NULL,
            wedding_time VARCHAR(20) DEFAULT '',
            venue VARCHAR(200) DEFAULT '',
            venue_address VARCHAR(300) DEFAULT '',
            message TEXT,
            theme VARCHAR(20) DEFAULT 'pink',
            cover_image VARCHAR(255) DEFAULT '',
            share_code VARCHAR(32) NOT NULL UNIQUE,
            view_count INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )",
        "CREATE TABLE IF NOT EXISTS remember_tokens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            token_hash VARCHAR(64) NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )",
        "CREATE TABLE IF NOT EXISTS invitation_templates (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category VARCHAR(50) DEFAULT '温馨',
            title VARCHAR(100) NOT NULL,
            content TEXT NOT NULL,
            tags VARCHAR(200) DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )"
    ];
    
    $count = 0;
    foreach ($sqls as $sql) {
        $pdo->exec($sql);
        $count++;
    }
    
    echo "✓ 创建了 $count 个表\n\n";
    
    $tables = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
    echo "数据库中的表：\n";
    while ($row = $tables->fetch(PDO::FETCH_ASSOC)) {
        echo "  - {$row['name']}\n";
    }
    
} catch (PDOException $e) {
    die("✗ 创建表失败: " . $e->getMessage() . "\n");
}
?>