38703-vm/db/setup.php

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

try {
    $pdo = db();

    // Users Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20) DEFAULT NULL,
        address TEXT DEFAULT NULL,
        role ENUM('guest', 'user', 'admin') DEFAULT 'user',
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        deleted_at TIMESTAMP NULL DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Cars Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS cars (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        brand VARCHAR(50) NOT NULL,
        model VARCHAR(50) NOT NULL,
        year INT NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        city VARCHAR(50) NOT NULL,
        description TEXT,
        status ENUM('pending', 'approved', 'rejected', 'sold') DEFAULT 'pending',
        is_hot_deal BOOLEAN DEFAULT FALSE,
        reserved_by INT NULL,
        reserved_at TIMESTAMP NULL,
        reservation_expires_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        deleted_at TIMESTAMP NULL DEFAULT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        CONSTRAINT fk_cars_reserved_by FOREIGN KEY (reserved_by) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Car Images Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS car_images (
        id INT AUTO_INCREMENT PRIMARY KEY,
        car_id INT NOT NULL,
        image_path VARCHAR(255) NOT NULL,
        is_main BOOLEAN DEFAULT FALSE,
        FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Reviews Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS reviews (
        id INT AUTO_INCREMENT PRIMARY KEY,
        car_id INT NOT NULL,
        user_id INT NOT NULL,
        rating INT CHECK (rating >= 1 AND rating <= 5),
        comment TEXT,
        status ENUM('pending', 'approved') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Purchases Table (Enterprise Module)
    $pdo->exec("CREATE TABLE IF NOT EXISTS purchases (
        id INT AUTO_INCREMENT PRIMARY KEY,
        transaction_id CHAR(36) NULL,
        reference_number VARCHAR(50) NULL,
        verification_token VARCHAR(64) NULL,
        car_id INT NOT NULL,
        user_id INT NOT NULL,
        buyer_name VARCHAR(100),
        buyer_email VARCHAR(100),
        buyer_phone VARCHAR(20),
        bank_id VARCHAR(100),
        personal_info TEXT,
        base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        marketplace_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        tax DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        payment_method ENUM('card', 'bank_transfer', 'wallet') NULL,
        escrow_status ENUM('awaiting_verification', 'held_in_escrow', 'released', 'cancelled') DEFAULT 'awaiting_verification',
        expires_at TIMESTAMP NULL,
        status ENUM('initiated', 'processing', 'paid', 'failed', 'refunded', 'chargeback', 'reserved', 'completed', 'cancelled', 'pending', 'approved', 'rejected') DEFAULT 'initiated',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Contact Messages
    $pdo->exec("CREATE TABLE IF NOT EXISTS contact_messages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        email VARCHAR(100),
        subject VARCHAR(200),
        message TEXT,
        status ENUM('unread', 'read', 'answered') DEFAULT 'unread',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Settings Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS settings (
        `key` VARCHAR(50) PRIMARY KEY,
        `value` VARCHAR(255) NOT NULL,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Seed Settings
    $pdo->exec("INSERT IGNORE INTO settings (`key`, `value`) VALUES 
        ('marketplace_fee_percentage', '5'),
        ('tax_percentage', '10');");

    // Seed Admin User
    $adminEmail = 'admin@gmail.com';
    $stmt = $pdo->prepare("SELECT id FROM users WHERE email = ?");
    $stmt->execute([$adminEmail]);
    if (!$stmt->fetch()) {
        $password = password_hash('12345678', PASSWORD_DEFAULT);
        $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)")
            ->execute(['Admin', $adminEmail, $password, 'admin']);
    }

    echo "Database setup successfully.";
} catch (PDOException $e) {
    die("Database error: " . $e->getMessage());
}