<?php
// Include CORS headers from config.php but catch local errors
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// Read credentials from config.php
require_once __DIR__ . '/config.php';

try {
    // 1. Connect directly to the configured database
    $conn = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8", $db_user, $db_pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 2. Drop existing tables safely to allow clean re-initialization
    $conn->exec("SET FOREIGN_KEY_CHECKS = 0;");
    $tablesToDrop = [
        'contestants',
        'influencers',
        'broadcasts',
        'user_withdrawal_profiles',
        'quiz_attempts',
        'user_question_history',
        'quiz_questions',
        'wallet_transactions',
        'rate_limits',
        'leaderboard',
        'quizzes',
        'wallets',
        'user_otps',
        'users'
    ];
    foreach ($tablesToDrop as $table) {
        $conn->exec("DROP TABLE IF EXISTS $table");
    }
    $conn->exec("SET FOREIGN_KEY_CHECKS = 1;");

    // 4. Create Users Table
    $conn->exec("CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        full_name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        phone VARCHAR(30) NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        is_verified TINYINT DEFAULT 0,
        avatar MEDIUMTEXT DEFAULT NULL,
        last_profile_edit DATETIME DEFAULT NULL,
        points INT DEFAULT 0,
        points_quiz INT DEFAULT 0,
        points_challenges INT DEFAULT 0,
        points_referrals INT DEFAULT 0,
        points_other INT DEFAULT 0,
        `rank` INT DEFAULT 12,
        win_streak INT DEFAULT 7,
        accuracy_rate INT DEFAULT 92,
        fastest_answer FLOAT DEFAULT 5.8,
        today_points INT DEFAULT 18,
        referred_by VARCHAR(100) DEFAULT NULL,
        temp_pass_expires_at DATETIME DEFAULT NULL,
        temp_pass_active TINYINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;");

    // 5. Create User OTPs Table
    $conn->exec("CREATE TABLE user_otps (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(100) NOT NULL,
        code VARCHAR(6) NOT NULL,
        expires_at DATETIME NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;");

    // 6. Create Wallets Table
    $conn->exec("CREATE TABLE wallets (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT UNIQUE NOT NULL,
        balance DECIMAL(15, 2) DEFAULT 0.00,
        today_winnings DECIMAL(15, 2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;");

    // 7. Create Quizzes Table
    $conn->exec("CREATE TABLE quizzes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100) NOT NULL,
        category VARCHAR(50) NOT NULL,
        reward_amount DECIMAL(15, 2) NOT NULL,
        start_time VARCHAR(20) NOT NULL,
        total_players INT DEFAULT 0,
        is_active TINYINT DEFAULT 1
    ) ENGINE=InnoDB;");

    // 8. Create Leaderboard Table
    $conn->exec("CREATE TABLE leaderboard (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        score VARCHAR(30) NOT NULL,
        `rank` INT NOT NULL,
        avatar VARCHAR(255) NOT NULL
    ) ENGINE=InnoDB;");

    // 8b. Create Rate Limits Table
    $conn->exec("CREATE TABLE rate_limits (
        ip_address VARCHAR(45) NOT NULL,
        endpoint VARCHAR(255) NOT NULL,
        attempts INT DEFAULT 1,
        last_request TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (ip_address, endpoint)
    ) ENGINE=InnoDB;");

    // 8c. Create Wallet Transactions Table (Immutable Financial Ledger)
    $conn->exec("CREATE TABLE wallet_transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        action VARCHAR(50) NOT NULL,
        amount DECIMAL(15, 2) NOT NULL,
        balance_before DECIMAL(15, 2) NOT NULL,
        balance_after DECIMAL(15, 2) NOT NULL,
        reference VARCHAR(100) UNIQUE NOT NULL,
        ip_address VARCHAR(45) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;");

    // 8d. Create Quiz Questions Table
    $conn->exec("CREATE TABLE quiz_questions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        quiz_id INT NOT NULL,
        question_text TEXT NOT NULL,
        option_a VARCHAR(255) NOT NULL,
        option_b VARCHAR(255) NOT NULL,
        option_c VARCHAR(255) NOT NULL,
        option_d VARCHAR(255) NOT NULL,
        correct_option TINYINT NOT NULL,
        difficulty VARCHAR(20) NOT NULL,
        FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
        KEY idx_quiz (quiz_id),
        KEY idx_difficulty (difficulty)
    ) ENGINE=InnoDB;");

    // 8e. Create User Question History Table
    $conn->exec("CREATE TABLE user_question_history (
        user_id INT NOT NULL,
        question_id INT NOT NULL,
        PRIMARY KEY (user_id, question_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;");

    // 8f. Create Quiz Attempts Table
    $conn->exec("CREATE TABLE quiz_attempts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        quiz_id INT NOT NULL,
        score INT NOT NULL,
        reward_earned DECIMAL(15, 2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        KEY idx_user_quiz (user_id, quiz_id),
        KEY idx_created (created_at)
    ) ENGINE=InnoDB;");

    // 8g. Create User Withdrawal Profiles Table
    $conn->exec("CREATE TABLE user_withdrawal_profiles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        type VARCHAR(20) NOT NULL,
        bank_name VARCHAR(100) NOT NULL,
        account_number VARCHAR(100) NOT NULL,
        label VARCHAR(255) NOT NULL,
        owner_name VARCHAR(255) NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;");

    // 8h. Create Broadcasts Table
    $conn->exec("CREATE TABLE broadcasts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        category VARCHAR(50) DEFAULT 'general',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;");

    // 8i. Create Influencers Table
    $conn->exec("CREATE TABLE influencers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        handle VARCHAR(100) NOT NULL,
        platform VARCHAR(50) NOT NULL,
        followers VARCHAR(50) NOT NULL,
        pitch TEXT NOT NULL,
        status VARCHAR(20) DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;");

    // 8j. Create Contestants Table
    $conn->exec("CREATE TABLE contestants (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        pitch TEXT NOT NULL,
        status VARCHAR(20) DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;");

    // 9. Populate Seed Data: Quizzes
    $quizzes = [
        [1, 'General Knowledge', 'General Knowledge', 100000.00, '09:00', 1250, 1],
        [2, 'Sports', 'Sports', 100000.00, '18:15', 1420, 1],
        [3, 'Entertainment', 'Entertainment', 100000.00, '20:00', 3120, 1],
        [4, 'Business & Finance', 'Business & Finance', 100000.00, '10:00', 890, 1],
        [5, 'Current Affairs', 'Current Affairs', 100000.00, '15:00', 740, 1],
        [6, 'Science & Tech', 'Science & Tech', 100000.00, '12:00', 950, 1]
    ];
    $stmt = $conn->prepare("INSERT INTO quizzes (id, title, category, reward_amount, start_time, total_players, is_active) VALUES (?, ?, ?, ?, ?, ?, ?)");
    foreach ($quizzes as $quiz) {
        $stmt->execute($quiz);
    }

    // 10. Populate Seed Data: Leaderboard
    $leaderboard = [
        ['Zainab K.', '98,750', 1, 'https://i.pravatar.cc/150?img=32'],
        ['Tunde O.', '96,400', 2, 'https://i.pravatar.cc/150?img=11'],
        ['Chiamaka N.', '94,200', 3, 'https://i.pravatar.cc/150?img=44']
    ];
    $stmt = $conn->prepare("INSERT INTO leaderboard (name, score, `rank`, avatar) VALUES (?, ?, ?, ?)");
    foreach ($leaderboard as $thinker) {
        $stmt->execute($thinker);
    }

    // 10b. Seed Quiz Questions (500 per category)
    $stmtQ = $conn->prepare("INSERT INTO quiz_questions (quiz_id, question_text, option_a, option_b, option_c, option_d, correct_option, difficulty) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

    for ($quiz_id = 1; $quiz_id <= 6; $quiz_id++) {
        for ($i = 1; $i <= 500; $i++) {
            $question = "";
            $options = [];
            $correct = 0;

            switch ($quiz_id) {
                case 1: // General Knowledge
                    if ($i % 5 == 0) {
                        $n1 = 5 + $i;
                        $n2 = 3 + ($i % 10);
                        $ans = $n1 + $n2;
                        $question = "What is the sum of $n1 and $n2?";
                        $options = [strval($ans - 2), strval($ans + 1), strval($ans), strval($ans - 1)];
                        $correct = 2;
                    } else if ($i % 5 == 1) {
                        $countries = ['France', 'Germany', 'Italy', 'Spain', 'Japan', 'Canada', 'Nigeria', 'Ghana', 'Egypt', 'Kenya'];
                        $capitals = ['Paris', 'Berlin', 'Rome', 'Madrid', 'Tokyo', 'Ottawa', 'Abuja', 'Accra', 'Cairo', 'Nairobi'];
                        $idx = $i % count($countries);
                        $country = $countries[$idx];
                        $capital = $capitals[$idx];
                        $question = "What is the capital city of $country?";
                        $options = [$capital, $capitals[($idx + 1) % count($capitals)], $capitals[($idx + 2) % count($capitals)], $capitals[($idx + 3) % count($capitals)]];
                        $correct = 0;
                    } else if ($i % 5 == 2) {
                        $elements = ['Oxygen', 'Hydrogen', 'Carbon', 'Nitrogen', 'Helium', 'Gold', 'Silver', 'Iron'];
                        $symbols = ['O', 'H', 'C', 'N', 'He', 'Au', 'Ag', 'Fe'];
                        $idx = $i % count($elements);
                        $question = "Which chemical element is represented by the symbol '" . $symbols[$idx] . "'?";
                        $options = [$elements[($idx + 1) % count($elements)], $elements[($idx + 2) % count($elements)], $elements[$idx], $elements[($idx + 3) % count($elements)]];
                        $correct = 2;
                    } else if ($i % 5 == 3) {
                        $colors = ['red and yellow', 'blue and yellow', 'red and blue', 'black and white'];
                        $results = ['orange', 'green', 'purple', 'gray'];
                        $idx = $i % count($colors);
                        $question = "What color do you get when you mix " . $colors[$idx] . "?";
                        $options = [$results[($idx + 1) % count($results)], $results[$idx], $results[($idx + 2) % count($results)], $results[($idx + 3) % count($results)]];
                        $correct = 1;
                    } else {
                        $planets = ['Mercury', 'Venus', 'Earth', 'Mars', 'Jupiter', 'Saturn', 'Uranus', 'Neptune'];
                        $pIdx = $i % 8;
                        $ans = $planets[$pIdx];
                        $question = "Which planet is the " . ($pIdx + 1) . (($pIdx == 0) ? "st" : (($pIdx == 1) ? "nd" : (($pIdx == 2) ? "rd" : "th"))) . " planet from the Sun?";
                        $options = [$planets[($pIdx + 4) % 8], $planets[($pIdx + 2) % 8], $planets[($pIdx + 6) % 8], $ans];
                        $correct = 3;
                    }
                    break;

                case 2: // Sports
                    if ($i % 4 == 0) {
                        $sports = ['soccer', 'basketball', 'cricket', 'rugby league', 'baseball', 'handball'];
                        $players = ['11', '5', '11', '13', '9', '7'];
                        $idx = $i % count($sports);
                        $question = "How many players from one team are on the field/court in a standard game of " . $sports[$idx] . "?";
                        $options = [$players[($idx + 1) % count($players)], $players[$idx], $players[($idx + 2) % count($players)], $players[($idx + 3) % count($players)]];
                        $correct = 1;
                    } else if ($i % 4 == 1) {
                        $years = ['1998', '2002', '2006', '2010', '2014', '2018', '2022'];
                        $winners = ['France', 'Brazil', 'Italy', 'Spain', 'Germany', 'France', 'Argentina'];
                        $idx = $i % count($years);
                        $question = "Which country won the Men's FIFA World Cup in the year " . $years[$idx] . "?";
                        $options = [$winners[($idx + 2) % count($winners)], $winners[($idx + 1) % count($winners)], $winners[$idx], $winners[($idx + 3) % count($winners)]];
                        $correct = 2;
                    } else if ($i % 4 == 2) {
                        $terms = ['Love', 'Strike', 'Birdie', 'Touchdown', 'Home Run', 'Checkmate'];
                        $sports = ['Tennis', 'Bowling', 'Golf', 'American Football', 'Baseball', 'Chess'];
                        $idx = $i % count($terms);
                        $question = "In sports, which game is the term '" . $terms[$idx] . "' associated with?";
                        $options = [$sports[($idx + 1) % count($sports)], $sports[($idx + 2) % count($sports)], $sports[($idx + 3) % count($sports)], $sports[$idx]];
                        $correct = 3;
                    } else {
                        $dist = 100 * (($i % 4) + 1);
                        $question = "How many meters is a standard track sprint of " . $dist . " meters?";
                        $options = [($dist - 50) . "m", $dist . "m", ($dist + 50) . "m", ($dist * 2) . "m"];
                        $correct = 1;
                    }
                    break;

                case 3: // Entertainment
                    if ($i % 4 == 0) {
                        $movies = ['A Tribe Called Judah', 'The Wedding Party', 'Omo Ghetto', 'Battle on Buka Street', 'King of Boys'];
                        $directors = ['Funke Akindele', 'Kemi Adetiba', 'Funke Akindele', 'Funke Akindele', 'Kemi Adetiba'];
                        $idx = $i % count($movies);
                        $question = "Who directed the Nollywood movie '" . $movies[$idx] . "'?";
                        $options = ['Kunle Afolayan', $directors[$idx], 'Genevieve Nnaji', 'Mo Abudu'];
                        $correct = 1;
                    } else if ($i % 4 == 1) {
                        $artists = ['Burna Boy', 'Wizkid', 'Davido', 'Asake', 'Rema', 'Tems'];
                        $albums = ['Twice as Tall', 'Made in Lagos', 'Timeless', 'Mr. Money with the Vibe', 'Rave & Roses', 'For Broken Ears'];
                        $idx = $i % count($artists);
                        $question = "Which famous Nigerian music artist released the album/EP '" . $albums[$idx] . "'?";
                        $options = [$artists[($idx + 1) % count($artists)], $artists[($idx + 2) % count($artists)], $artists[$idx], $artists[($idx + 3) % count($artists)]];
                        $correct = 2;
                    } else if ($i % 4 == 2) {
                        $shows = ['Big Brother Naija', 'Nigerian Idol', 'The Voice Nigeria', 'MTN Project Fame'];
                        $seasons = [5 + ($i % 4), 3 + ($i % 3), 2 + ($i % 2), 4 + ($i % 3)];
                        $idx = $i % count($shows);
                        $question = "In the Nigerian entertainment industry, which reality TV show features season " . $seasons[$idx] . "?";
                        $options = ['Who Wants to Be a Millionaire', $shows[$idx], 'Gulder Ultimate Search', 'Maltina Dance All'];
                        $correct = 1;
                    } else {
                        $songs = ['Calm Down', 'Essence', 'Last Last', 'Unavailable', 'Buga'];
                        $singers = ['Rema', 'Wizkid', 'Burna Boy', 'Davido', 'Kizz Daniel'];
                        $idx = $i % count($songs);
                        $question = "Which artist sang the global hit song '" . $songs[$idx] . "'?";
                        $options = [$singers[$idx], $singers[($idx + 1) % count($singers)], $singers[($idx + 2) % count($singers)], $singers[($idx + 3) % count($singers)]];
                        $correct = 0;
                    }
                    break;

                case 4: // Business & Finance
                    if ($i % 4 == 0) {
                        $terms = ['ROI', 'GDP', 'CEO', 'IPO', 'ETF', 'APR'];
                        $meanings = ['Return on Investment', 'Gross Domestic Product', 'Chief Executive Officer', 'Initial Public Offering', 'Exchange-Traded Fund', 'Annual Percentage Rate'];
                        $idx = $i % count($terms);
                        $question = "In business and finance, what does the abbreviation '" . $terms[$idx] . "' stand for?";
                        $options = [$meanings[($idx + 1) % count($meanings)], $meanings[$idx], $meanings[($idx + 2) % count($meanings)], $meanings[($idx + 3) % count($meanings)]];
                        $correct = 1;
                    } else if ($i % 4 == 1) {
                        $p = 1000 * (($i % 10) + 1);
                        $r = 5 + ($i % 6);
                        $t = 2 + ($i % 4);
                        $interest = ($p * $r * $t) / 100;
                        $question = "What is the simple interest on a principal of ₦" . number_format($p) . " at a rate of $r% per annum for $t years?";
                        $options = ["₦" . number_format($interest - 50), "₦" . number_format($interest + 100), "₦" . number_format($interest), "₦" . number_format($interest - 100)];
                        $correct = 2;
                    } else if ($i % 4 == 2) {
                        $companies = ['Apple', 'Microsoft', 'Tesla', 'Amazon', 'Meta', 'Google'];
                        $founders = ['Steve Jobs', 'Bill Gates', 'Elon Musk', 'Jeff Bezos', 'Mark Zuckerberg', 'Larry Page'];
                        $idx = $i % count($companies);
                        $question = "Who is recognized as a co-founder of the tech giant " . $companies[$idx] . "?";
                        $options = [$founders[($idx + 1) % count($founders)], $founders[($idx + 2) % count($founders)], $founders[($idx + 3) % count($founders)], $founders[$idx]];
                        $correct = 3;
                    } else {
                        $banks = ['Access Bank', 'Zenith Bank', 'Guaranty Trust Bank', 'United Bank for Africa', 'First Bank of Nigeria'];
                        $idx = $i % count($banks);
                        $question = "In which major city is the corporate headquarters of " . $banks[$idx] . " located?";
                        $options = ['Abuja', 'Lagos', 'Port Harcourt', 'Kano'];
                        $correct = 1;
                    }
                    break;

                case 5: // Current Affairs
                    if ($i % 4 == 0) {
                        $states = ['Lagos', 'Kano', 'Kaduna', 'Oyo', 'Rivers', 'Anambra', 'Edo', 'Delta', 'Enugu'];
                        $capitals = ['Ikeja', 'Kano', 'Kaduna', 'Ibadan', 'Port Harcourt', 'Awka', 'Benin City', 'Asaba', 'Enugu'];
                        $idx = $i % count($states);
                        $question = "What is the capital city of " . $states[$idx] . " State in Nigeria?";
                        $options = [$capitals[($idx + 1) % count($capitals)], $capitals[$idx], $capitals[($idx + 2) % count($capitals)], $capitals[($idx + 3) % count($capitals)]];
                        $correct = 1;
                    } else if ($i % 4 == 1) {
                        $orgs = ['ECOWAS', 'African Union', 'United Nations', 'WHO', 'IMF'];
                        $cities = ['Abuja, Nigeria', 'Addis Ababa, Ethiopia', 'New York, USA', 'Geneva, Switzerland', 'Washington D.C., USA'];
                        $idx = $i % count($orgs);
                        $question = "Where is the headquarters of the international body '" . $orgs[$idx] . "' located?";
                        $options = [$cities[($idx + 1) % count($cities)], $cities[($idx + 2) % count($cities)], $cities[$idx], $cities[($idx + 3) % count($cities)]];
                        $correct = 2;
                    } else if ($i % 4 == 2) {
                        $question = "Which country hosts the G$i global climate change summit for the year " . (2020 + ($i % 8)) . "?";
                        $options = ['Egypt', 'United Arab Emirates', 'United Kingdom', 'Nigeria'];
                        $correct = $i % 4;
                    } else {
                        $question = "Who holds the public office of Prime Minister / President of " . (($i % 2 == 0) ? "South Africa" : "Ghana") . " as of 2026?";
                        $options = ['Cyril Ramaphosa', 'Nana Akufo-Addo', 'Bola Tinubu', 'Peter Obi'];
                        $correct = ($i % 2 == 0) ? 0 : 1;
                    }
                    break;

                case 6: // Science & Tech
                    if ($i % 4 == 0) {
                        $comp_terms = ['HTML', 'CSS', 'RAM', 'ROM', 'CPU', 'DNS', 'URL', 'JSON'];
                        $meanings = ['HyperText Markup Language', 'Cascading Style Sheets', 'Random Access Memory', 'Read Only Memory', 'Central Processing Unit', 'Domain Name System', 'Uniform Resource Locator', 'JavaScript Object Notation'];
                        $idx = $i % count($comp_terms);
                        $question = "In computing and internet technology, what does '" . $comp_terms[$idx] . "' stand for?";
                        $options = [$meanings[$idx], $meanings[($idx + 1) % count($meanings)], $meanings[($idx + 2) % count($meanings)], $meanings[($idx + 3) % count($meanings)]];
                        $correct = 0;
                    } else if ($i % 4 == 1) {
                        $compounds = ['Water', 'Carbon Dioxide', 'Sodium Chloride', 'Methane', 'Glucose', 'Ammonia'];
                        $formulas = ['H2O', 'CO2', 'NaCl', 'CH4', 'C6H12O6', 'NH3'];
                        $idx = $i % count($compounds);
                        $question = "What is the chemical formula of the common compound '" . $compounds[$idx] . "'?";
                        $options = [$formulas[($idx + 1) % count($formulas)], $formulas[$idx], $formulas[($idx + 2) % count($formulas)], $formulas[($idx + 3) % count($formulas)]];
                        $correct = 1;
                    } else if ($i % 4 == 2) {
                        $val = 100 + $i;
                        $question = "Convert binary code '" . decbin($val) . "' to a base-10 integer value.";
                        $options = [strval($val - 5), strval($val + 10), strval($val), strval($val - 12)];
                        $correct = 2;
                    } else {
                        $consts = ['Speed of Light', 'Acceleration due to Gravity', 'Pi', 'Planck\'s Constant'];
                        $vals = ['300,000 km/s', '9.8 m/s²', '3.14159', '6.626 x 10^-34 J·s'];
                        $idx = $i % count($consts);
                        $question = "What is the approximate standard scientific constant value of '" . $consts[$idx] . "'?";
                        $options = [$vals[($idx + 1) % count($vals)], $vals[($idx + 2) % count($vals)], $vals[($idx + 3) % count($vals)], $vals[$idx]];
                        $correct = 3;
                    }
                    break;

                case 7: // Logic & Puzzles
                    if ($i % 3 == 0) {
                        $seq_start = $i % 10;
                        $diff = 3 + ($i % 5);
                        $v1 = $seq_start;
                        $v2 = $v1 + $diff;
                        $v3 = $v2 + $diff;
                        $v4 = $v3 + $diff;
                        $v5 = $v4 + $diff;
                        $question = "What is the next number in the arithmetic sequence: $v1, $v2, $v3, $v4, ...?";
                        $options = [strval($v5 + 1), strval($v5 - 2), strval($v5), strval($v5 + $diff)];
                        $correct = 2;
                    } else if ($i % 3 == 1) {
                        $n_apples = 10 + ($i % 20);
                        $n_give = 2 + ($i % 5);
                        $left = $n_apples - $n_give;
                        $question = "If you have $n_apples apples and you give $n_give to a friend, how many apples do you have left?";
                        $options = [strval($left - 1), strval($left), strval($left + 2), strval($left - 3)];
                        $correct = 1;
                    } else {
                        $names = ['Aisha', 'Bisi', 'Chinedu', 'David'];
                        $idx1 = $i % count($names);
                        $idx2 = ($idx1 + 1) % count($names);
                        $idx3 = ($idx1 + 2) % count($names);
                        $question = "If " . $names[$idx1] . " is taller than " . $names[$idx2] . ", and " . $names[$idx2] . " is taller than " . $names[$idx3] . ", who is the tallest among the three?";
                        $options = [$names[$idx2], $names[$idx3], $names[$idx1], 'Cannot be determined'];
                        $correct = 2;
                    }
                    break;

                case 8: // History & Culture
                    if ($i % 4 == 0) {
                        $countries = ['Nigeria', 'Ghana', 'Kenya', 'South Africa', 'Egypt', 'Senegal', 'Algeria'];
                        $years = ['1960', '1957', '1963', '1910', '1922', '1960', '1962'];
                        $idx = $i % count($countries);
                        $question = "In which historical year did the African nation of " . $countries[$idx] . " gain independence?";
                        $options = [$years[($idx + 1) % count($years)], $years[$idx], $years[($idx + 2) % count($years)], $years[($idx + 3) % count($years)]];
                        $correct = 1;
                    } else if ($i % 4 == 1) {
                        $empires = ['Benin Empire', 'Oyo Empire', 'Sokoto Caliphate', 'Kanem-Bornu Empire', 'Mali Empire', 'Songhai Empire'];
                        $regions = ['Midwestern Nigeria', 'Southwestern Nigeria', 'Northern Nigeria', 'Northeastern Nigeria', 'West Africa', 'West Africa'];
                        $idx = $i % count($empires);
                        $question = "Which geographical region was the historical home of the ancient " . $empires[$idx] . "?";
                        $options = [$regions[($idx + 1) % count($regions)], $regions[($idx + 2) % count($regions)], $regions[$idx], $regions[($idx + 3) % count($regions)]];
                        $correct = 2;
                    } else if ($i % 4 == 2) {
                        $attire = ['Agbada', 'Babbar Riga', 'Isiagu', 'Kente'];
                        $culture = ['Yoruba', 'Hausa', 'Igbo', 'Ghanaian'];
                        $idx = $i % count($attire);
                        $question = "The traditional clothing attire '" . $attire[$idx] . "' originates from which cultural heritage?";
                        $options = [$culture[($idx + 1) % count($culture)], $culture[($idx + 2) % count($culture)], $culture[($idx + 3) % count($culture)], $culture[$idx]];
                        $correct = 3;
                    } else {
                        $leaders = ['Nnamdi Azikiwe', 'Tafawa Balewa', 'Obafemi Awolowo', 'Ahmadu Bello'];
                        $roles = ['First President', 'First Prime Minister', 'Premier of Western Region', 'Premier of Northern Region'];
                        $idx = $i % count($leaders);
                        $question = "Which historical role did '" . $leaders[$idx] . "' hold in Nigeria?";
                        $options = [$roles[$idx], $roles[($idx + 1) % count($roles)], $roles[($idx + 2) % count($roles)], $roles[($idx + 3) % count($roles)]];
                        $correct = 0;
                    }
                    break;
            }

            $difficulty = 'easy';
            if ($i > 333) {
                $difficulty = 'hard';
            } elseif ($i > 166) {
                $difficulty = 'medium';
            }

            $stmtQ->execute([$quiz_id, $question, $options[0], $options[1], $options[2], $options[3], $correct, $difficulty]);
        }
    }

    // 11. Create a default verified test user
    $test_pass_hash = password_hash("password123", PASSWORD_BCRYPT);
    $conn->exec("INSERT INTO users (full_name, email, phone, password_hash, is_verified, points, points_quiz, points_challenges, points_referrals, points_other, `rank`, win_streak, accuracy_rate, fastest_answer, today_points) 
                 VALUES ('Ayo Adebayo', 'ayo@thinkrichnaija.com', '+2348012345678', '$test_pass_hash', 1, 143200, 82000, 45000, 10000, 6200, 12, 7, 92, 5.8, 18)");

    $test_user_id = $conn->lastInsertId();
    $conn->exec("INSERT INTO wallets (user_id, balance, today_winnings) VALUES ($test_user_id, 125400.00, 15000.00)");

    // Seed referred user Bisi Alao
    $conn->exec("INSERT INTO users (full_name, email, phone, password_hash, is_verified, points, points_quiz, points_challenges, points_referrals, points_other, `rank`, win_streak, accuracy_rate, fastest_answer, today_points, referred_by) 
                 VALUES ('Bisi Alao', 'bisi@thinkrichnaija.com', '+2348087654321', '$test_pass_hash', 1, 25000, 15000, 5000, 5000, 0, 45, 3, 85, 6.2, 5, 'ayo@thinkrichnaija.com')");
    $bisi_user_id = $conn->lastInsertId();
    $conn->exec("INSERT INTO wallets (user_id, balance, today_winnings) VALUES ($bisi_user_id, 5000.00, 0.00)");

    // Seed user Chinedu Obi
    $conn->exec("INSERT INTO users (full_name, email, phone, password_hash, is_verified, points, points_quiz, points_challenges, points_referrals, points_other, `rank`, win_streak, accuracy_rate, fastest_answer, today_points) 
                 VALUES ('Chinedu Obi', 'chinedu@thinkrichnaija.com', '+2348033333333', '$test_pass_hash', 1, 42000, 30000, 10000, 0, 2000, 18, 5, 88, 5.9, 10)");
    $chinedu_user_id = $conn->lastInsertId();
    $conn->exec("INSERT INTO wallets (user_id, balance, today_winnings) VALUES ($chinedu_user_id, 8000.00, 0.00)");

    // Seed realistic ledger transactions leading to the balance:
    // 1. Initial Deposit (topup): 150,000
    $conn->exec("INSERT INTO wallet_transactions (user_id, action, amount, balance_before, balance_after, reference, ip_address, created_at) 
                 VALUES ($test_user_id, 'topup', 150000.00, 0.00, 150000.00, 'TXN-DEP-INIT', '127.0.0.1', DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY))");

    // 2. Five referrals (referral) of 200 each: total 1,000
    for ($i = 1; $i <= 5; $i++) {
        $bal_before = 150000.00 + ($i - 1) * 200.00;
        $bal_after = 150000.00 + $i * 200.00;
        $conn->exec("INSERT INTO wallet_transactions (user_id, action, amount, balance_before, balance_after, reference, ip_address, created_at) 
                     VALUES ($test_user_id, 'referral', 200.00, $bal_before, $bal_after, 'TXN-REF-00$i', '127.0.0.1', DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 4 DAY))");
    }

    // 3. Bank Cashout (withdraw): 40,600
    $conn->exec("INSERT INTO wallet_transactions (user_id, action, amount, balance_before, balance_after, reference, ip_address, created_at)
                 VALUES ($test_user_id, 'withdraw', 40600.00, 151000.00, 110400.00, 'TXN-WTH-001', '127.0.0.1', DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY))");

    // 4. Quiz winnings (reward): 15,000 (today)
    $conn->exec("INSERT INTO wallet_transactions (user_id, action, amount, balance_before, balance_after, reference, ip_address, created_at) 
                 VALUES ($test_user_id, 'reward', 15000.00, 110400.00, 125400.00, 'TXN-QZ1-TODAY', '127.0.0.1', CURRENT_TIMESTAMP)");

    // 5. User withdrawal profiles (Access Bank and Visa Card)
    $conn->exec("INSERT INTO user_withdrawal_profiles (user_id, type, bank_name, account_number, label, owner_name) 
                 VALUES ($test_user_id, 'bank', 'Access Bank', '0123456789', 'Access Bank ending in ...6789', 'Ayo Adebayo')");
    $conn->exec("INSERT INTO user_withdrawal_profiles (user_id, type, bank_name, account_number, label, owner_name) 
                 VALUES ($test_user_id, 'card', 'Visa Card', '**** **** **** 1234', 'Visa ending in 1234', 'Ayo Adebayo')");

    // 6. Seed Influencer application for Bisi Alao (user_id = 2)
    $conn->exec("INSERT INTO influencers (user_id, handle, platform, followers, pitch, status) 
                 VALUES ($bisi_user_id, 'bisialao_media', 'instagram', '20k-100k', 'I have a large student community on Instagram eager to learn and win real cash.', 'pending')");

    // 7. Seed Contestant applications
    $conn->exec("INSERT INTO contestants (user_id, pitch, status) 
                 VALUES ($bisi_user_id, 'I am a fast thinker and want to prove my skills on the live show to inspire others.', 'pending')");
    $conn->exec("INSERT INTO contestants (user_id, pitch, status) 
                 VALUES ($chinedu_user_id, 'I have won three local trivia tournaments and would love a chance to compete at the state level.', 'pending')");

    echo json_encode([
        "status" => "success",
        "message" => "Database thinkrich_db successfully initialized, structured and seeded with default datasets."
    ]);

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        "status" => "error",
        "message" => "Setup execution failed: " . $e->getMessage()
    ]);
}
?>