-- Vehicle Requisition Module
-- Run: mysql -u root vehicleinspectio_vehicledb23 < this_file

CREATE TABLE IF NOT EXISTS vehicle_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_number VARCHAR(30) NOT NULL UNIQUE,
    staff_id BIGINT NOT NULL,
    pickup_location VARCHAR(255),
    destination VARCHAR(255),
    journey_purpose TEXT,
    start_date DATE,
    end_date DATE,
    priority ENUM('low','normal','high','urgent') DEFAULT 'normal',
    passenger_count INT DEFAULT 1,
    supporting_document VARCHAR(255),
    status ENUM('draft','pending','approved','rejected','returned','assigned','in_progress','completed','cancelled') DEFAULT 'draft',
    assigned_vehicle_id BIGINT UNSIGNED NULL,
    assigned_driver_id BIGINT UNSIGNED NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_vehicle_requests_staff (staff_id),
    INDEX idx_vehicle_requests_status (status),
    INDEX idx_vehicle_requests_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_request_approvals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    step_order INT NOT NULL,
    step_label VARCHAR(100) NOT NULL,
    approver_role VARCHAR(50) NOT NULL,
    approver_id BIGINT NULL,
    status ENUM('pending','approved','rejected','returned') DEFAULT 'pending',
    comment TEXT,
    acted_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES vehicle_requests(id) ON DELETE CASCADE,
    INDEX idx_vra_request (request_id),
    INDEX idx_vra_approver (approver_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('vehicle_request.create', 'Submit vehicle requests'),
('vehicle_request.view', 'View vehicle requests'),
('vehicle_request.approve', 'Approve vehicle requests'),
('vehicle_request.assign', 'Assign vehicle and driver to requests');

-- Assign permissions to admin role (id 1)
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code LIKE 'vehicle_request.%';