-- Driver Management Extensions: Medical Records, Certifications, Training, Violations
-- Run: mysql -u root vehicleinspectio_vehicledb23 < this_file

CREATE TABLE IF NOT EXISTS driver_medical_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id BIGINT NOT NULL,
    examination_type VARCHAR(100) NOT NULL,
    examination_date DATE NOT NULL,
    performed_by VARCHAR(200),
    results TEXT,
    status ENUM('fit','unfit','conditional','pending') DEFAULT 'pending',
    valid_until DATE,
    notes TEXT,
    document_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_dmr_driver (driver_id),
    INDEX idx_dmr_status (status),
    INDEX idx_dmr_valid (valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS driver_certifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id BIGINT NOT NULL,
    certification_type VARCHAR(100) NOT NULL,
    certification_name VARCHAR(200) NOT NULL,
    issuing_authority VARCHAR(200),
    certification_number VARCHAR(100),
    issued_date DATE,
    expiry_date DATE,
    status ENUM('active','expired','suspended','revoked') DEFAULT 'active',
    notes TEXT,
    document_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_dc_driver (driver_id),
    INDEX idx_dc_status (status),
    INDEX idx_dc_expiry (expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS driver_training_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id BIGINT NOT NULL,
    training_type VARCHAR(100) NOT NULL,
    training_name VARCHAR(200) NOT NULL,
    training_provider VARCHAR(200),
    training_date DATE,
    completion_date DATE,
    duration_hours DECIMAL(8,1),
    status ENUM('completed','in_progress','withdrawn','cancelled') DEFAULT 'completed',
    certificate_issued TINYINT(1) DEFAULT 0,
    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_dth_driver (driver_id),
    INDEX idx_dth_status (status),
    INDEX idx_dth_date (training_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS driver_violations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id BIGINT NOT NULL,
    violation_type VARCHAR(100) NOT NULL,
    violation_date DATE NOT NULL,
    offense_description TEXT,
    location VARCHAR(255),
    penalty_points INT DEFAULT 0,
    fine_amount DECIMAL(12,2) DEFAULT 0.00,
    status ENUM('pending','paid','contested','resolved','waived') DEFAULT 'pending',
    resolution_date DATE,
    police_report_number VARCHAR(100),
    vehicle_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_dv_driver (driver_id),
    INDEX idx_dv_status (status),
    INDEX idx_dv_date (violation_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('driver.medical.view', 'View driver medical records'),
('driver.medical.create', 'Add/edit driver medical records'),
('driver.medical.delete', 'Delete driver medical records'),
('driver.certification.view', 'View driver certifications'),
('driver.certification.create', 'Add/edit driver certifications'),
('driver.certification.delete', 'Delete driver certifications'),
('driver.training.view', 'View driver training history'),
('driver.training.create', 'Add/edit driver training history'),
('driver.training.delete', 'Delete driver training history'),
('driver.violation.view', 'View driver violations'),
('driver.violation.create', 'Add/edit driver violations'),
('driver.violation.delete', 'Delete driver violations');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code LIKE 'driver.medical.%' OR code LIKE 'driver.certification.%' OR code LIKE 'driver.training.%' OR code LIKE 'driver.violation.%';