<?php
/**
 * SQLite Database Initialization Script for FlashCore
 * This script sets up the SQLite database file and seeds it with initial data.
 */

$dbPath = __DIR__ . '/../database.db';

// Check if database exists and has been initialized (has tables)
if (file_exists($dbPath)) {
    try {
        $checkPdo = new PDO("sqlite:" . $dbPath);
        $checkPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Check if users table exists (indicates database is initialized)
        $stmt = $checkPdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='users' LIMIT 1");
        $tableExists = $stmt->fetch();
        
        if ($tableExists) {
            echo "Database already exists and is initialized at $dbPath\n";
            exit;
        } else {
            // Database file exists but is empty/uninitialized - remove it and reinitialize
            echo "Database file exists but is not initialized. Reinitializing...\n";
            unlink($dbPath);
        }
    } catch (PDOException $e) {
        // If we can't check, assume it's corrupted and remove it
        echo "Database file exists but appears corrupted. Reinitializing...\n";
        unlink($dbPath);
    }
}

try {
    $pdo = new PDO("sqlite:" . $dbPath);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Creating SQLite database at $dbPath...\n";

    // SQL Schema (converted for SQLite compatibility)
    $schema = "
    -- Enum-like tables
    CREATE TABLE IF NOT EXISTS subscription_tiers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS license_status (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS user_roles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    -- Main tables
    CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        email TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        first_name TEXT,
        last_name TEXT,
        is_approved INTEGER DEFAULT 0,
        email_verified INTEGER DEFAULT 0,
        verification_token TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_login DATETIME NULL
    );

    CREATE TABLE IF NOT EXISTS profiles (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        first_name TEXT,
        last_name TEXT,
        role_id INTEGER DEFAULT 2,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (role_id) REFERENCES user_roles(id)
    );

    CREATE TABLE IF NOT EXISTS subscribers (
        id TEXT PRIMARY KEY,
        user_id TEXT,
        email TEXT NOT NULL UNIQUE,
        stripe_customer_id TEXT,
        subscribed INTEGER NOT NULL DEFAULT 0,
        subscription_tier_id INTEGER,
        subscription_end DATETIME NULL,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id)
    );

    CREATE TABLE IF NOT EXISTS license_keys (
        id TEXT PRIMARY KEY,
        key_value TEXT NOT NULL UNIQUE,
        user_id TEXT,
        subscription_tier_id INTEGER NOT NULL,
        status_id INTEGER DEFAULT 4,
        expires_at DATETIME NULL,
        activated_at DATETIME NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id),
        FOREIGN KEY (status_id) REFERENCES license_status(id)
    );

    CREATE TABLE IF NOT EXISTS referrals (
        id TEXT PRIMARY KEY,
        referrer_id TEXT NOT NULL,
        referred_id TEXT NOT NULL,
        referred_name TEXT,
        status TEXT NOT NULL DEFAULT 'Pending',
        reward DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS user_progress (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL UNIQUE,
        current_rank TEXT NOT NULL DEFAULT 'Bronze',
        mails_sent INTEGER NOT NULL DEFAULT 0,
        progress_percentage INTEGER NOT NULL DEFAULT 0,
        referral_count INTEGER NOT NULL DEFAULT 0,
        referral_earnings DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        total_earnings DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS smtp_settings (
        id TEXT PRIMARY KEY,
        provider TEXT NOT NULL,
        host TEXT,
        port INTEGER,
        username TEXT,
        password TEXT,
        from_email TEXT NOT NULL,
        from_name TEXT,
        is_active INTEGER NOT NULL DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS user_profiles (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL UNIQUE,
        username TEXT,
        first_name TEXT,
        last_name TEXT,
        phone TEXT,
        avatar_url TEXT,
        bio TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS sessions (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        session_token TEXT NOT NULL UNIQUE,
        expires_at DATETIME NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );
    ";

    $pdo->exec($schema);
    echo "Schema created successfully.\n";

    // Seed Initial Data
    $pdo->exec("INSERT OR IGNORE INTO subscription_tiers (id, name) VALUES (1, 'basic'), (2, 'pro'), (3, 'enterprise')");
    $pdo->exec("INSERT OR IGNORE INTO license_status (id, name) VALUES (1, 'active'), (2, 'expired'), (3, 'suspended'), (4, 'pending')");
    $pdo->exec("INSERT OR IGNORE INTO user_roles (id, name) VALUES (1, 'admin'), (2, 'user')");

    echo "Initial data seeded successfully.\n";
    echo "SQLite initialization complete.\n";
    echo "\n";
    echo "⚠️  SECURITY: No default admin user created.\n";
    echo "📝 Next steps:\n";
    echo "   1. Create admin user using: php scripts/create-admin.php\n";
    echo "   2. Or use the web interface: /setup-admin\n";
    echo "   3. Or set environment variables: ADMIN_EMAIL and ADMIN_PASSWORD\n";

} catch (PDOException $e) {
    die("Error initializing SQLite database: " . $e->getMessage() . "\n");
}
