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

try {
    $pdo = db();
    echo "Connected to database successfully.<br>";

    // Idempotently add columns to the cars table if they don't exist
    $columns_to_add = [
        'status' => "ALTER TABLE cars ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending'",
        'color' => "ALTER TABLE cars ADD COLUMN color VARCHAR(50)",
        'province' => "ALTER TABLE cars ADD COLUMN province VARCHAR(100)",
        'city' => "ALTER TABLE cars ADD COLUMN city VARCHAR(100)",
        'description' => "ALTER TABLE cars ADD COLUMN description TEXT",
    ];

    $stmt = $pdo->query("DESCRIBE cars");
    $existing_columns = $stmt->fetchAll(PDO::FETCH_COLUMN);

    foreach ($columns_to_add as $column => $sql) {
        if (!in_array($column, $existing_columns)) {
            $pdo->exec($sql);
            echo "Column '{$column}' added to 'cars' table.<br>";
        }
    }

    // Truncate to ensure exactly 10 cars as requested
    $pdo->exec("SET FOREIGN_KEY_CHECKS = 0;");
    $pdo->exec("TRUNCATE TABLE cars;");
    $pdo->exec("SET FOREIGN_KEY_CHECKS = 1;");
    echo "Table 'cars' truncated to fresh state.<br>";

    $cars = [
        [
            'make' => 'Toyota', 'model' => 'Corolla', 'year' => 2018, 'price' => 13500, 'mileage' => 85000, 'color' => 'White', 'province' => 'Kabul', 'city' => 'Kabul', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/1545743/pexels-photo-1545743.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'A well-maintained Corolla, very popular in Kabul. Clean interior, smooth engine. -5 plate number.'
        ],
        [
            'make' => 'Toyota', 'model' => 'Land Cruiser', 'year' => 2020, 'price' => 75000, 'mileage' => 45000, 'color' => 'Black', 'province' => 'Herat', 'city' => 'Herat', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/3764984/pexels-photo-3764984.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'Powerful V8 Land Cruiser (VXR). Full option, sunroof, leather seats. Imported from Dubai.'
        ],
        [
            'make' => 'Mercedes-Benz', 'model' => 'C200', 'year' => 2016, 'price' => 22000, 'mileage' => 72000, 'color' => 'Silver', 'province' => 'Balkh', 'city' => 'Mazar-i-Sharif', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/241316/pexels-photo-241316.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'German luxury and comfort. Smooth ride with a clean interior. Perfect for Mazar city roads.'
        ],
        [
            'make' => 'Toyota', 'model' => 'Hilux', 'year' => 2021, 'price' => 35000, 'mileage' => 25000, 'color' => 'Red', 'province' => 'Kandahar', 'city' => 'Kandahar', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/248747/pexels-photo-248747.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'Robust and versatile Hilux (Surf style). Excellent for both city and rural roads in Kandahar.'
        ],
        [
            'make' => 'Honda', 'model' => 'Civic', 'year' => 2019, 'price' => 17000, 'mileage' => 55000, 'color' => 'Blue', 'province' => 'Kabul', 'city' => 'Kabul', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/1637859/pexels-photo-1637859.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'Sporty and modern Honda Civic. Features a sunroof and great fuel economy. American import.'
        ],
        [
            'make' => 'Ford', 'model' => 'Ranger', 'year' => 2017, 'price' => 24000, 'mileage' => 95000, 'color' => 'Gray', 'province' => 'Nangarhar', 'city' => 'Jalalabad', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/119435/pexels-photo-119435.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'American toughness. This Ford Ranger is built to handle tough terrain in Jalalabad area.'
        ],
        [
            'make' => 'Toyota', 'model' => 'RAV4', 'year' => 2018, 'price' => 21000, 'mileage' => 62000, 'color' => 'White', 'province' => 'Ghazni', 'city' => 'Ghazni', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/707046/pexels-photo-707046.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'Family-friendly SUV. Spacious and comfortable for long journeys between provinces.'
        ],
        [
            'make' => 'Lexus', 'model' => 'LX 570', 'year' => 2019, 'price' => 85000, 'mileage' => 40000, 'color' => 'Pearl White', 'province' => 'Kabul', 'city' => 'Kabul', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/116675/pexels-photo-116675.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'The pinnacle of luxury and capability. Top-of-the-line model with all options. Bulletproof glass.'
        ],
        [
            'make' => 'Toyota', 'model' => 'Camry', 'year' => 2017, 'price' => 18500, 'mileage' => 75000, 'color' => 'Silver', 'province' => 'Kunduz', 'city' => 'Kunduz', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/1035108/pexels-photo-1035108.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'Reliable and fuel-efficient Camry. Smooth driving and very comfortable seats.'
        ],
        [
            'make' => 'Suzuki', 'model' => 'Alto', 'year' => 2015, 'price' => 4500, 'mileage' => 110000, 'color' => 'Red', 'province' => 'Bamyan', 'city' => 'Bamyan', 'status' => 'approved',
            'image_url' => 'https://images.pexels.com/photos/1035108/pexels-photo-1035108.jpeg?auto=compress&cs=tinysrgb&w=800',
            'description' => 'Small and economical Alto. Great for narrow streets and very low fuel consumption.'
        ],
    ];

    $insertSql = "INSERT INTO cars (make, model, year, price, mileage, color, province, city, status, image_url, description) VALUES (:make, :model, :year, :price, :mileage, :color, :province, :city, :status, :image_url, :description)";
    $stmt = $pdo->prepare($insertSql);

    $count = 0;
    foreach ($cars as $car) {
        $stmt->execute($car);
        $count++;
    }
    echo "Seeded " . $count . " cars into the database.<br>";

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