<?php
require_once 'config/database.php';
try {
    // Drop and recreate the table without foreign keys first
    $pdo->exec('DROP TABLE IF EXISTS vehicle_assignments');
    
    $sql = "
    CREATE TABLE vehicle_assignments (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        vehicle_id BIGINT UNSIGNED NOT NULL,
        driver_id BIGINT UNSIGNED NOT NULL,
        company_id BIGINT UNSIGNED NULL,
        assignment_start DATE NOT NULL,
        assignment_end DATE NULL,
        status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
        leased_days INT NULL,
        notes TEXT NULL,
        created_by_user_id BIGINT UNSIGNED NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_vehicle_assignments_vehicle (vehicle_id),
        KEY idx_vehicle_assignments_driver (driver_id),
        KEY idx_vehicle_assignments_dates (assignment_start, assignment_end),
        KEY idx_vehicle_assignments_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Vehicle to driver assignments with date ranges';
    ";
    
    $pdo->exec($sql);
    echo 'Table created successfully without foreign keys';
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage();
}
?>