<?php
/**
 * Self-healing Database Migrations
 * Automatically ensures the database schema matches the expected state.
 */

try {
    $pdo = getDBConnection();

    // 1. Ensure 'archived' status exists in drivers table
    $stmt = $pdo->query("SHOW COLUMNS FROM drivers LIKE 'status'");
    $column = $stmt->fetch();

    if ($column && strpos($column['Type'], "'archived'") === false) {
        $currentType = $column['Type'];
        $newType = str_replace(")", ",'archived')", $currentType);
        $pdo->exec("ALTER TABLE drivers MODIFY COLUMN status $newType");
    }

    // 2. Ensure hire_date column exists in drivers table
    $stmt = $pdo->query("SHOW COLUMNS FROM drivers LIKE 'hire_date'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE drivers ADD COLUMN hire_date DATE NULL AFTER status");
        $pdo->exec("UPDATE drivers SET hire_date = CURDATE() WHERE hire_date IS NULL");
    }

    // Ensure 'email' column exists in drivers table
    $stmt = $pdo->query("SHOW COLUMNS FROM drivers LIKE 'email'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE drivers ADD COLUMN email VARCHAR(255) UNIQUE NULL AFTER last_name");
    }

    // Ensure 'user_id' column exists in drivers table
    $stmt = $pdo->query("SHOW COLUMNS FROM drivers LIKE 'user_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE drivers ADD COLUMN user_id INT NULL AFTER id");
        $pdo->exec("ALTER TABLE drivers ADD CONSTRAINT fk_driver_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL");
    }

    // 3. Ensure favorites table exists
    $pdo->exec("CREATE TABLE IF NOT EXISTS favorites (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        vehicle_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
        UNIQUE KEY (user_id, vehicle_id)
    )");

    // 4. Ensure is_featured column exists in vehicles table
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'is_featured'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE vehicles ADD COLUMN is_featured TINYINT(1) DEFAULT 0 AFTER status");
        // Mark some vehicles as featured by default if any exist
        $pdo->exec("UPDATE vehicles SET is_featured = 1 LIMIT 3");
    }

    // 5. Ensure pickup location details exist in bookings table
    $stmt = $pdo->query("SHOW COLUMNS FROM bookings LIKE 'pickup_description'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE bookings ADD COLUMN pickup_description TEXT NULL AFTER pickup_location");
        $pdo->exec("ALTER TABLE bookings ADD COLUMN pickup_latitude DECIMAL(10, 8) NULL AFTER pickup_description");
        $pdo->exec("ALTER TABLE bookings ADD COLUMN pickup_longitude DECIMAL(11, 8) NULL AFTER pickup_latitude");
    }

    // Ensure dropoff location details exist in bookings table
    $stmt = $pdo->query("SHOW COLUMNS FROM bookings LIKE 'dropoff_description'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE bookings ADD COLUMN dropoff_description TEXT NULL AFTER dropoff_location");
        $pdo->exec("ALTER TABLE bookings ADD COLUMN dropoff_latitude DECIMAL(10, 8) NULL AFTER dropoff_description");
        $pdo->exec("ALTER TABLE bookings ADD COLUMN dropoff_longitude DECIMAL(11, 8) NULL AFTER dropoff_latitude");
    }

    // 6. Ensure balance_amount column exists in bookings table
    $stmt = $pdo->query("SHOW COLUMNS FROM bookings LIKE 'balance_amount'");
    if (!$stmt->fetch()) {
        try {
            $pdo->exec("ALTER TABLE bookings ADD COLUMN balance_amount DECIMAL(10, 2) GENERATED ALWAYS AS (total_amount - downpayment_amount) STORED AFTER downpayment_amount");
        } catch (Exception $e) {
            // Fallback for older MySQL versions that don't support generated columns
            $pdo->exec("ALTER TABLE bookings ADD COLUMN balance_amount DECIMAL(10, 2) NULL AFTER downpayment_amount");
            $pdo->exec("UPDATE bookings SET balance_amount = total_amount - downpayment_amount");
        }
    }

    // 7. Add 'driver' to user_type enum in users table
    $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'user_type'");
    $column = $stmt->fetch();
    if ($column && strpos($column['Type'], "'driver'") === false) {
        $pdo->exec("ALTER TABLE users MODIFY COLUMN user_type ENUM('customer', 'admin', 'driver') DEFAULT 'customer'");
    }

    // 8. Create chats table
    $pdo->exec("CREATE TABLE IF NOT EXISTS chats (
        id INT AUTO_INCREMENT PRIMARY KEY,
        booking_id INT NOT NULL,
        sender_id INT NOT NULL,
        message TEXT NOT NULL,
        is_read TINYINT(1) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
        FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
    )");

    // 9. Ensure has_rear_camera column exists in vehicles table
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'has_rear_camera'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE vehicles ADD COLUMN has_rear_camera TINYINT(1) DEFAULT 0 AFTER description");
    }

    // 10. Update fuel_type enum to include gasoline (resolves fleet upload mismatch)
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'fuel_type'");
    $column = $stmt->fetch();
    if ($column && strpos($column['Type'], "'gasoline'") === false) {
        // Change from ENUM('petrol', 'diesel', 'electric', 'hybrid') to include gasoline
        $pdo->exec("ALTER TABLE vehicles MODIFY COLUMN fuel_type ENUM('gasoline', 'diesel', 'electric', 'hybrid', 'petrol') DEFAULT 'gasoline'");
        // Map existing 'petrol' to 'gasoline'
        $pdo->exec("UPDATE vehicles SET fuel_type = 'gasoline' WHERE fuel_type = 'petrol'");
    }

    // 11. Ensure sender details exist in payments table
    $stmt = $pdo->query("SHOW COLUMNS FROM payments LIKE 'sender_name'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE payments ADD COLUMN sender_name VARCHAR(255) NULL AFTER transaction_reference");
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM payments LIKE 'sender_number'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE payments ADD COLUMN sender_number VARCHAR(50) NULL AFTER sender_name");
    }

    // 12. Create reviews table
    $pdo->exec("CREATE TABLE IF NOT EXISTS reviews (
        id INT AUTO_INCREMENT PRIMARY KEY,
        booking_id INT NOT NULL,
        user_id INT NOT NULL,
        vehicle_id INT,
        driver_id INT,
        overall_rating DECIMAL(2,1) NOT NULL,
        vehicle_rating DECIMAL(2,1) NOT NULL,
        driver_rating DECIMAL(2,1) NOT NULL,
        service_rating DECIMAL(2,1) NOT NULL,
        vehicle_comment TEXT,
        driver_comment TEXT,
        service_comment TEXT,
        vehicle_images JSON,
        is_verified TINYINT(1) DEFAULT 1,
        is_public TINYINT(1) DEFAULT 1,
        admin_response TEXT,
        responded_by INT,
        responded_at TIMESTAMP NULL,
        status ENUM('pending', 'published', 'hidden', 'flagged') DEFAULT 'published',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE RESTRICT,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
        FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
        FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
        FOREIGN KEY (responded_by) REFERENCES users(id) ON DELETE SET NULL,
        INDEX idx_booking (booking_id),
        INDEX idx_user (user_id),
        INDEX idx_status (status)
    )");

    // 13. Add rating and total_reviews columns to vehicles table
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'rating'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE vehicles ADD COLUMN rating DECIMAL(3,2) DEFAULT 0 AFTER description");
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'total_reviews'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE vehicles ADD COLUMN total_reviews INT DEFAULT 0 AFTER rating");
    }

    // 14. Add total_reviews column to drivers table
    $stmt = $pdo->query("SHOW COLUMNS FROM drivers LIKE 'total_reviews'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE drivers ADD COLUMN total_reviews INT DEFAULT 0 AFTER total_trips");
    }

    // 15. Add rating column to drivers table
    $stmt = $pdo->query("SHOW COLUMNS FROM drivers LIKE 'rating'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE drivers ADD COLUMN rating DECIMAL(3,2) DEFAULT 0 AFTER total_reviews");
    }

    // 16. Ensure vehicles.rating column exists (recheck for completeness)
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'rating'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE vehicles ADD COLUMN rating DECIMAL(3,2) DEFAULT 0 AFTER description");
    }

    // 17. Ensure vehicles.total_reviews column exists (recheck for completeness)
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'total_reviews'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE vehicles ADD COLUMN total_reviews INT DEFAULT 0 AFTER rating");
    }

    // 17.5 Ensure notifications table exists
    $pdo->exec("CREATE TABLE IF NOT EXISTS notifications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NULL,
        recipient_type ENUM('customer', 'admin') DEFAULT 'customer',
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        type ENUM('info', 'warning', 'success', 'error', 'booking', 'payment', 'review') DEFAULT 'info',
        is_read TINYINT(1) DEFAULT 0,
        link VARCHAR(500),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user (user_id),
        INDEX idx_read (is_read),
        INDEX idx_created (created_at)
    )");

    // 18. Add fcm_token column to users and admin_users
    $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'fcm_token'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE users ADD COLUMN fcm_token TEXT NULL");
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM admin_users LIKE 'fcm_token'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE admin_users ADD COLUMN fcm_token TEXT NULL");
    }

    // 19. Ensure Firebase settings exist in settings table
    $firebase_settings = [
        'fcm_server_key' => 'Firebase Cloud Messaging Server Key',
        'firebase_project_id' => 'Firebase Project ID',
        'firebase_database_url' => 'Firebase Realtime Database URL',
        'firebase_messaging_sender_id' => 'Firebase Messaging Sender ID',
        'firebase_vapid_key' => 'Firebase VAPID Key',
        'firebase_api_key' => 'Firebase API Key',
        'firebase_auth_domain' => 'Firebase Auth Domain',
        'firebase_storage_bucket' => 'Firebase Storage Bucket',
        'firebase_app_id' => 'Firebase App ID'
    ];

    foreach ($firebase_settings as $key => $desc) {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM settings WHERE key_name = ?");
        $stmt->execute([$key]);
        if ($stmt->fetchColumn() == 0) {
            $defaultValue = '';
            if ($key === 'firebase_vapid_key') $defaultValue = 'fctrO4nhtTrMGlaxi4dZK0U0t9IO61jKBf0wiES2h5Y';
            if ($key === 'firebase_project_id') $defaultValue = 'carental-cd345';
            if ($key === 'firebase_database_url') $defaultValue = 'https://carental-cd345-default-rtdb.firebaseio.com';
            if ($key === 'firebase_messaging_sender_id') $defaultValue = '318959738016';
            if ($key === 'firebase_api_key') $defaultValue = 'AIzaSyDdTbeS9cJ3qanX_dY3BrBqatKKknECgDY';
            if ($key === 'firebase_auth_domain') $defaultValue = 'carental-cd345.firebaseapp.com';
            if ($key === 'firebase_storage_bucket') $defaultValue = 'carental-cd345.firebasestorage.app';
            if ($key === 'firebase_app_id') $defaultValue = '1:318959738016:web:091bc5223de92cbf927dc5';

            $stmt = $pdo->prepare("INSERT INTO settings (key_name, key_value, description) VALUES (?, ?, ?)");
            $stmt->execute([$key, $defaultValue, $desc]);
        }
    }

    // 20. Ensure carwash columns exist in bookings table
    $stmt = $pdo->query("SHOW COLUMNS FROM bookings LIKE 'with_carwash'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE bookings ADD COLUMN with_carwash TINYINT(1) DEFAULT 0 AFTER special_requests");
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM bookings LIKE 'carwash_amount'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE bookings ADD COLUMN carwash_amount DECIMAL(10, 2) DEFAULT 0.00 AFTER with_carwash");
    }


    // 22. Support Dynamic Vehicle Types - Change ENUM to VARCHAR
    $stmt = $pdo->query("SHOW COLUMNS FROM vehicles LIKE 'vehicle_type'");
    $column = $stmt->fetch();
    if ($column && strpos($column['Type'], 'enum') !== false) {
        $pdo->exec("ALTER TABLE vehicles MODIFY COLUMN vehicle_type VARCHAR(50) DEFAULT 'sedan'");
    }

    // 23. Ensure cleaning_fee column exists in pricing_settings
    $stmt = $pdo->query("SHOW COLUMNS FROM pricing_settings LIKE 'cleaning_fee'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE pricing_settings ADD COLUMN cleaning_fee DECIMAL(10, 2) DEFAULT 150.00 AFTER late_fee");
    }

    // 24. Remove orphaned carwash_fee from settings table
    $pdo->exec("DELETE FROM settings WHERE key_name = 'carwash_fee'");

} catch (Exception $e) {
    error_log("Migration failed: " . $e->getMessage());
}
?>
