-- New fleet management module tables

-- 1. Trips / Journey Management
CREATE TABLE IF NOT EXISTS trips (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NULL,
    start_odometer INT NULL,
    end_odometer INT NULL,
    start_location VARCHAR(255) NULL,
    end_location VARCHAR(255) NULL,
    purpose VARCHAR(255) NULL,
    route_description TEXT NULL,
    status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_trips_vehicle (vehicle_id),
    KEY idx_trips_driver (driver_id),
    KEY idx_trips_status (status),
    KEY idx_trips_dates (start_date, end_date),
    CONSTRAINT fk_trips_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_trips_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Insurance Management
CREATE TABLE IF NOT EXISTS vehicle_insurance_policies (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    policy_number VARCHAR(100) NOT NULL,
    insurer_name VARCHAR(200) NOT NULL,
    policy_type ENUM('comprehensive','third_party','third_party_fire_theft','other') NOT NULL DEFAULT 'comprehensive',
    coverage_start DATE NOT NULL,
    coverage_end DATE NOT NULL,
    premium_amount DECIMAL(12,2) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_insurance_vehicle (vehicle_id),
    KEY idx_insurance_active (is_active),
    KEY idx_insurance_coverage (coverage_start, coverage_end),
    CONSTRAINT fk_insurance_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS insurance_claims (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    policy_id BIGINT UNSIGNED NOT NULL,
    claim_number VARCHAR(100) NULL,
    incident_date DATE NULL,
    claim_amount DECIMAL(12,2) NULL,
    settlement_amount DECIMAL(12,2) NULL,
    status ENUM('filed','assessed','approved','settled','rejected') NOT NULL DEFAULT 'filed',
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_insurance_claims_policy (policy_id),
    CONSTRAINT fk_claims_policy FOREIGN KEY (policy_id) REFERENCES vehicle_insurance_policies(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Accident / Incident Reporting
CREATE TABLE IF NOT EXISTS incidents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NULL,
    incident_date DATE NOT NULL,
    incident_time TIME NULL,
    location VARCHAR(255) NULL,
    incident_type ENUM('collision','fire','theft','vandalism','natural_disaster','other') NOT NULL DEFAULT 'collision',
    severity ENUM('minor','moderate','major','total_loss') NOT NULL DEFAULT 'minor',
    description TEXT NULL,
    police_report_number VARCHAR(100) NULL,
    police_station VARCHAR(200) NULL,
    third_party_details TEXT NULL,
    is_fleet_fault TINYINT(1) NULL,
    damage_cost_estimate DECIMAL(12,2) NULL,
    status ENUM('reported','assessing','in_repair','resolved','closed') NOT NULL DEFAULT 'reported',
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_incidents_vehicle (vehicle_id),
    KEY idx_incidents_driver (driver_id),
    KEY idx_incidents_date (incident_date),
    KEY idx_incidents_status (status),
    CONSTRAINT fk_incidents_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_incidents_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_damages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    incident_id BIGINT UNSIGNED NOT NULL,
    damage_area VARCHAR(100) NULL,
    damage_description TEXT NULL,
    estimated_cost DECIMAL(12,2) NULL,
    actual_cost DECIMAL(12,2) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_incident_damages_incident (incident_id),
    CONSTRAINT fk_damages_incident FOREIGN KEY (incident_id) REFERENCES incidents(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Spare Parts / Inventory
CREATE TABLE IF NOT EXISTS part_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_part_categories_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS parts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id BIGINT UNSIGNED NULL,
    part_number VARCHAR(100) NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    unit_of_measure VARCHAR(30) NOT NULL DEFAULT 'piece',
    quantity_in_stock DECIMAL(10,2) NOT NULL DEFAULT 0,
    reorder_level DECIMAL(10,2) NULL,
    unit_cost DECIMAL(12,2) NULL,
    supplier_name VARCHAR(200) NULL,
    bin_location VARCHAR(100) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_parts_category (category_id),
    KEY idx_parts_active (is_active),
    KEY idx_parts_number (part_number),
    CONSTRAINT fk_parts_category FOREIGN KEY (category_id) REFERENCES part_categories(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS part_transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    part_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('purchase','usage','return','adjustment','transfer') NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_cost DECIMAL(12,2) NULL,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_part_transactions_part (part_id),
    KEY idx_part_transactions_ref (reference_type, reference_id),
    CONSTRAINT fk_part_transactions_part FOREIGN KEY (part_id) REFERENCES parts(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Tyre Management
CREATE TABLE IF NOT EXISTS tyres (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NULL,
    brand VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    size VARCHAR(50) NULL,
    serial_number VARCHAR(100) NULL,
    position VARCHAR(50) NULL,
    purchase_date DATE NULL,
    purchase_cost DECIMAL(12,2) NULL,
    fitted_date DATE NULL,
    fitted_odometer INT NULL,
    removed_date DATE NULL,
    removed_odometer INT NULL,
    removal_reason ENUM('worn_out','damaged','retreaded','other') NULL,
    status ENUM('in_stock','fitted','retreaded','scrapped') NOT NULL DEFAULT 'in_stock',
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_tyres_vehicle (vehicle_id),
    KEY idx_tyres_status (status),
    KEY idx_tyres_serial (serial_number),
    CONSTRAINT fk_tyres_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tyre_retreads (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tyre_id BIGINT UNSIGNED NOT NULL,
    retread_date DATE NOT NULL,
    retread_cost DECIMAL(12,2) NULL,
    vendor_name VARCHAR(200) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_tyre_retreads_tyre (tyre_id),
    CONSTRAINT fk_retreads_tyre FOREIGN KEY (tyre_id) REFERENCES tyres(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. General Expense Tracking
CREATE TABLE IF NOT EXISTS expense_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_expense_categories_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_expenses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NULL,
    expense_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    description VARCHAR(255) NULL,
    receipt_url VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    deleted_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_vehicle_expenses_vehicle (vehicle_id),
    KEY idx_vehicle_expenses_driver (driver_id),
    KEY idx_vehicle_expenses_category (category_id),
    KEY idx_vehicle_expenses_date (expense_date),
    CONSTRAINT fk_vehicle_expenses_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_expenses_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vehicle_expenses_category FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default expense categories
INSERT INTO expense_categories (name, description) VALUES
('Toll', 'Toll road fees'),
('Parking', 'Parking fees'),
('Permit', 'Vehicle permits and road worthiness'),
('Cleaning', 'Vehicle washing and cleaning'),
('Towing', 'Towing and recovery'),
('Fines', 'Traffic fines and penalties'),
('Miscellaneous', 'Other vehicle-related expenses')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- Seed default part categories
INSERT INTO part_categories (name, description) VALUES
('Engine', 'Engine parts and components'),
('Transmission', 'Transmission and drivetrain'),
('Brakes', 'Brake system parts'),
('Suspension', 'Suspension and steering'),
('Electrical', 'Electrical and electronics'),
('Body', 'Body parts and panels'),
('Filters', 'Oil, air, and fuel filters'),
('Fluids', 'Lubricants and fluids'),
('Tyres', 'Tyres and tubes'),
('AC', 'Air conditioning parts')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- Add permissions for new modules
INSERT IGNORE INTO permissions (code, description) VALUES
('trip.view', 'View trips and journeys'),
('trip.create', 'Create trips'),
('trip.edit', 'Edit trips'),
('trip.delete', 'Delete trips'),
('insurance.view', 'View insurance policies'),
('insurance.create', 'Create insurance policies'),
('insurance.edit', 'Edit insurance policies'),
('insurance.delete', 'Delete insurance policies'),
('incident.view', 'View incidents and accidents'),
('incident.create', 'Create incidents'),
('incident.edit', 'Edit incidents'),
('incident.delete', 'Delete incidents'),
('part.view', 'View spare parts inventory'),
('part.create', 'Create parts'),
('part.edit', 'Edit parts'),
('part.delete', 'Delete parts'),
('tyre.view', 'View tyres'),
('tyre.create', 'Create tyres'),
('tyre.edit', 'Edit tyres'),
('tyre.delete', 'Delete tyres'),
('expense.view', 'View vehicle expenses'),
('expense.create', 'Create expenses'),
('expense.edit', 'Edit expenses'),
('expense.delete', 'Delete expenses'),
('driver_scorecard.view', 'View driver scorecard'),
('data.import', 'Import data from CSV'),
('data.export', 'Export data to CSV');

-- Grant new permissions to all existing roles
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE p.code IN (
    'trip.view','trip.create','trip.edit','trip.delete',
    'insurance.view','insurance.create','insurance.edit','insurance.delete',
    'incident.view','incident.create','incident.edit','incident.delete',
    'part.view','part.create','part.edit','part.delete',
    'tyre.view','tyre.create','tyre.edit','tyre.delete',
    'expense.view','expense.create','expense.edit','expense.delete',
    'driver_scorecard.view',
    'data.import','data.export'
);