<?php
// setup_database.php

// Database configuration
$host = '127.0.o.1';
$db_name = 'jweb'; // Using your existing database name
$username = 'root'; // Change to your database username
$password = 'YourStrongPassword123'; // Change to your database password

try {
    // Connect to MySQL server
    $pdo = new PDO("mysql:host=$host", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Select the database
    $pdo->exec("USE $db_name");
    echo "Using database: $db_name<br>";
    
    // Users table
    $sql = "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        tier ENUM('Basic', 'Premium', 'Gold') DEFAULT 'Basic',
        package ENUM('NOVA', 'SUPERIOR', 'GOLD') DEFAULT 'NOVA',
        balance DECIMAL(10, 2) DEFAULT 0.00,
        total_deposits DECIMAL(10, 2) DEFAULT 0.00,
        total_withdrawals DECIMAL(10, 2) DEFAULT 0.00,
        rewards DECIMAL(10, 2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";
    $pdo->exec($sql);
    echo "Users table created successfully<br>";
    
    // Transactions table
    $sql = "CREATE TABLE IF NOT EXISTS transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        type ENUM('deposit', 'withdrawal', 'bonus', 'purchase', 'transfer', 'earning') NOT NULL,
        amount DECIMAL(10, 2) NOT NULL,
        description VARCHAR(255),
        status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
        reference VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($sql);
    echo "Transactions table created successfully<br>";
    
    // Packages table
    $sql = "CREATE TABLE IF NOT EXISTS packages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        award DECIMAL(10, 2) NOT NULL,
        features TEXT,
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($sql);
    echo "Packages table created successfully<br>";
    
    // Insert default packages
    $sql = "INSERT IGNORE INTO packages (name, price, award, features) VALUES
        ('NOVA', 1000.00, 3000.00, 'Auto Deposit'),
        ('SUPERIOR', 2500.00, 7500.00, 'Auto Deposit'),
        ('GOLD', 5500.00, 16500.00, 'Auto Deposit')";
    $pdo->exec($sql);
    echo "Default packages inserted successfully<br>";
    
    // Create a test user if none exists
    $sql = "SELECT COUNT(*) FROM users";
    $result = $pdo->query($sql);
    $count = $result->fetchColumn();
    
    if ($count == 0) {
        $password_hash = password_hash('password123', PASSWORD_DEFAULT);
        $sql = "INSERT INTO users (username, email, password_hash, tier, package, balance, total_deposits, total_withdrawals, rewards) 
                VALUES ('testuser', 'test@example.com', '$password_hash', 'Premium', 'NOVA', 5000.00, 10000.00, 5000.00, 1000.00)";
        $pdo->exec($sql);
        echo "Test user created successfully<br>";
        
        // Add some sample transactions
        $user_id = $pdo->lastInsertId();
        $transactions = [
            ['user_id' => $user_id, 'type' => 'deposit', 'amount' => 1000.00, 'description' => 'Initial deposit', 'status' => 'completed'],
            ['user_id' => $user_id, 'type' => 'withdrawal', 'amount' => 500.00, 'description' => 'Cash withdrawal', 'status' => 'completed'],
            ['user_id' => $user_id, 'type' => 'bonus', 'amount' => 100.00, 'description' => 'Welcome bonus', 'status' => 'completed'],
            ['user_id' => $user_id, 'type' => 'purchase', 'amount' => 250.00, 'description' => 'Product purchase', 'status' => 'completed'],
            ['user_id' => $user_id, 'type' => 'earning', 'amount' => 150.00, 'description' => 'Daily earnings', 'status' => 'completed']
        ];
        
        foreach ($transactions as $transaction) {
            $sql = "INSERT INTO transactions (user_id, type, amount, description, status) 
                    VALUES (:user_id, :type, :amount, :description, :status)";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($transaction);
        }
        echo "Sample transactions created successfully<br>";
    }
    
    echo "Database setup completed successfully!";
    
} catch (PDOException $e) {
    die("Database error: " . $e->getMessage());
}
?>