36557-vm/db/setup.php

<?php
require_once 'db/config.php';

function run_sql($db, $sql, $message) {
    try {
        $db->exec($sql);
        echo $message . "<br>";
    } catch (PDOException $e) {
        // Suppress errors if the alteration already exists, but show others
        if (!str_contains($e->getMessage(), 'Duplicate') && !str_contains($e->getMessage(), 'already exists') && !str_contains($e->getMessage(), 'Unknown table')) {
            echo "Error: " . $e->getMessage() . "<br>";
        }
    }
}

try {
    $db = db();

    // 0. Drop dependent tables first to avoid foreign key issues
    run_sql($db, "DROP TABLE IF EXISTS `passive_income_schedule`", "Table 'passive_income_schedule' dropped if exists.");
    run_sql($db, "DROP TABLE IF EXISTS `commissions`", "Table 'commissions' dropped if exists.");
    run_sql($db, "DROP TABLE IF EXISTS `wallet_ledger`", "Table 'wallet_ledger' dropped if exists.");

    // 1. Users table
    $sqlUsers = "CREATE TABLE IF NOT EXISTS `users` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `name` VARCHAR(255) NOT NULL,
      `email` VARCHAR(255) NOT NULL UNIQUE,
      `password` VARCHAR(255) NOT NULL,
      `referral_code` VARCHAR(50) NOT NULL UNIQUE,
      `sponsor_id` INT NULL,
      `role` ENUM('Super Admin', 'Admin', 'Finance', 'Agent', 'Support') NOT NULL DEFAULT 'Agent',
      `agent_tier` ENUM('Normal', 'Silver', 'Gold', 'Diamond') NULL DEFAULT 'Normal',
      `cumulative_bookings` DECIMAL(15, 2) DEFAULT 0.00,
      `phone` VARCHAR(255) NULL,
      `company` VARCHAR(255) NULL,
      `notes` TEXT NULL,
      `wallet_balance` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
      `total_direct_income` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
      `total_team_income` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
      `total_passive_income` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
      `total_leg_match_income` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (sponsor_id) REFERENCES users(id) ON DELETE SET NULL
    )";
    run_sql($db, $sqlUsers, "Table 'users' created or already exists.");

    // Add columns to users table if they don't exist
    run_sql($db, "ALTER TABLE users ADD COLUMN wallet_balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00", "Column 'wallet_balance' added to 'users' table.");
    run_sql($db, "ALTER TABLE users ADD COLUMN total_direct_income DECIMAL(15, 2) NOT NULL DEFAULT 0.00", "Column 'total_direct_income' added to 'users' table.");
    run_sql($db, "ALTER TABLE users ADD COLUMN total_team_income DECIMAL(15, 2) NOT NULL DEFAULT 0.00", "Column 'total_team_income' added to 'users' table.");
    run_sql($db, "ALTER TABLE users ADD COLUMN total_passive_income DECIMAL(15, 2) NOT NULL DEFAULT 0.00", "Column 'total_passive_income' added to 'users' table.");
    run_sql($db, "ALTER TABLE users ADD COLUMN total_leg_match_income DECIMAL(15, 2) NOT NULL DEFAULT 0.00", "Column 'total_leg_match_income' added to 'users' table.");
    run_sql($db, "ALTER TABLE users MODIFY cumulative_bookings DECIMAL(15, 2) DEFAULT 0.00", "Column 'cumulative_bookings' in 'users' table modified.");
    run_sql($db, "ALTER TABLE users MODIFY `role` ENUM('Super Admin', 'Admin', 'Finance', 'Agent', 'Support') NOT NULL DEFAULT 'Agent'", "Column 'role' in 'users' table modified.");


    // 2. Bookings table
    $sqlBookings = "CREATE TABLE IF NOT EXISTS `bookings` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT NOT NULL,
        `plot_id` VARCHAR(255) NOT NULL,
        `amount` DECIMAL(15, 2) NOT NULL,
        `booking_date` DATE NOT NULL,
        `proof_document` VARCHAR(255) NOT NULL,
        `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )";
    run_sql($db, $sqlBookings, "Table 'bookings' created or already exists.");
    run_sql($db, "ALTER TABLE bookings MODIFY amount DECIMAL(15, 2) NOT NULL", "Column 'amount' in 'bookings' table modified.");

    // 3. Transactions table (replaces wallet_ledger and commissions)
    $sqlTransactions = "CREATE TABLE IF NOT EXISTS `transactions` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT NOT NULL,
        `amount` DECIMAL(15, 2) NOT NULL,
        `type` ENUM('commission_direct', 'commission_team', 'passive_income', 'leg_match_bonus', 'withdrawal', 'withdrawal_fee', 'deposit', 'booking_refund') NOT NULL,
        `description` TEXT,
        `related_booking_id` INT NULL,
        `related_user_id` INT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (related_booking_id) REFERENCES bookings(id) ON DELETE SET NULL,
        FOREIGN KEY (related_user_id) REFERENCES users(id) ON DELETE SET NULL
    )";
    run_sql($db, $sqlTransactions, "Table 'transactions' created or already exists.");

    // 4. Withdrawals table
    $sqlWithdrawals = "CREATE TABLE IF NOT EXISTS `withdrawals` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT NOT NULL,
        `amount` DECIMAL(15, 2) NOT NULL,
        `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
        `rejection_reason` TEXT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `processed_at` TIMESTAMP NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )";
    run_sql($db, $sqlWithdrawals, "Table 'withdrawals' created or already exists.");
     run_sql($db, "ALTER TABLE withdrawals MODIFY amount DECIMAL(15, 2) NOT NULL", "Column 'amount' in 'withdrawals' table modified.");

    // 5. Leg Milestones table
    $sqlLegMilestones = "CREATE TABLE IF NOT EXISTS `leg_milestones` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT NOT NULL,
        `leg_user_id` INT NOT NULL, /* The user in the downline whose leg reached the milestone */
        `milestone_amount` DECIMAL(15, 2) NOT NULL,
        `bonus_amount` DECIMAL(15, 2) NOT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (leg_user_id) REFERENCES users(id) ON DELETE CASCADE
    )";
    run_sql($db, $sqlLegMilestones, "Table 'leg_milestones' created or already exists.");

    // 6. Passive Income Schedule table (now references transactions)
    $sqlPassiveIncome = "CREATE TABLE IF NOT EXISTS `passive_income_schedule` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `transaction_id` INT NOT NULL, /* The direct commission transaction */
        `user_id` INT NOT NULL,
        `amount` DECIMAL(15, 2) NOT NULL,
        `payment_date` DATE NOT NULL,
        `status` ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )";
    run_sql($db, $sqlPassiveIncome, "Table 'passive_income_schedule' created or already exists.");


    // 7. Insert/Update Super Admin User
    $adminName = 'Super Admin';
    $adminEmail = 'admin@example.com';
    $adminPassword = 'admin';
    $hashedPassword = password_hash($adminPassword, PASSWORD_BCRYPT);
    $adminReferralCode = 'ADMIN';

    $stmt = $db->prepare("SELECT id FROM users WHERE email = :email");
    $stmt->execute([':email' => $adminEmail]);
    if ($stmt->rowCount() == 0) {
        $sql = "INSERT INTO users (name, email, password, referral_code, `role`) VALUES (:name, :email, :password, :referral_code, 'Super Admin')";
        $stmt = $db->prepare($sql);
        $stmt->execute([
            ':name' => $adminName,
            ':email' => $adminEmail,
            ':password' => $hashedPassword,
            ':referral_code' => $adminReferralCode
        ]);
        echo "Super Admin user created successfully.";
    } else {
        $sql = "UPDATE users SET password = :password, `role` = 'Super Admin' WHERE email = :email";
        $stmt = $db->prepare($sql);
        $stmt->execute([
            ':password' => $hashedPassword,
            ':email' => $adminEmail
        ]);
        echo "Super Admin user updated successfully.";
    }
    echo "<br>Database setup/update complete.";

} catch (PDOException $e) {
    die("DB ERROR: ". $e->getMessage());
}