-- AI Vehicle Allocation: Recommendations log and scoring weights config

CREATE TABLE IF NOT EXISTS allocation_recommendations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    availability_score DECIMAL(5,2) DEFAULT 0,
    type_match_score DECIMAL(5,2) DEFAULT 0,
    fuel_score DECIMAL(5,2) DEFAULT 0,
    maintenance_score DECIMAL(5,2) DEFAULT 0,
    condition_score DECIMAL(5,2) DEFAULT 0,
    utilization_score DECIMAL(5,2) DEFAULT 0,
    rank_position INT NOT NULL,
    selected TINYINT(1) DEFAULT 0,
    overridden TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ar_request (request_id),
    INDEX idx_ar_vehicle (vehicle_id),
    FOREIGN KEY (request_id) REFERENCES vehicle_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS allocation_weights (
    id INT AUTO_INCREMENT PRIMARY KEY,
    criterion VARCHAR(50) NOT NULL UNIQUE,
    weight INT NOT NULL DEFAULT 10,
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO allocation_weights (criterion, weight) VALUES
('availability', 30),
('type_match', 20),
('fuel', 15),
('maintenance', 15),
('condition', 10),
('utilization', 10);

INSERT IGNORE INTO permissions (code, description) VALUES
('allocation.ai_view', 'View AI allocation recommendations'),
('allocation.ai_override', 'Override AI allocation recommendations');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code LIKE 'allocation.%';