CREATE TABLE IF NOT EXISTS carriers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(190) NOT NULL,
    dba_name VARCHAR(190) NULL,
    contact_name VARCHAR(140) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    mc_number VARCHAR(80) NULL,
    dot_number VARCHAR(80) NULL,
    tax_id VARCHAR(100) NULL,
    address VARCHAR(255) NULL,
    dispatch_fee_type ENUM('percentage','fixed_per_load','monthly') NOT NULL DEFAULT 'percentage',
    dispatch_fee_value DECIMAL(12,2) NOT NULL DEFAULT 0,
    payment_terms VARCHAR(80) NOT NULL DEFAULT 'Weekly',
    authority_status ENUM('active','pending','inactive','revoked') NOT NULL DEFAULT 'active',
    safety_rating VARCHAR(80) NULL,
    insurance_expiry DATE NULL,
    cargo_insurance_expiry DATE NULL,
    onboarding_status ENUM('lead','documents_pending','review','approved','on_hold','inactive') NOT NULL DEFAULT 'documents_pending',
    status ENUM('active','on_hold','inactive') NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_carriers_status (status, onboarding_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS drivers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(140) NOT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    license_number VARCHAR(100) NULL,
    license_expiry DATE NULL,
    medical_card_expiry DATE NULL,
    carrier_id INT UNSIGNED NULL,
    employment_type ENUM('carrier_driver','owner_operator','company_driver','leased_driver') NOT NULL DEFAULT 'carrier_driver',
    pay_type ENUM('percentage', 'per_mile', 'fixed') NOT NULL DEFAULT 'percentage',
    rate DECIMAL(10,2) NOT NULL DEFAULT 0,
    status ENUM('available', 'on_load', 'inactive') NOT NULL DEFAULT 'available',
    address VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_driver_carrier FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(40) NOT NULL DEFAULT 'admin',
    department VARCHAR(100) NULL,
    phone VARCHAR(50) NULL,
    permissions TEXT NULL,
    last_login_at DATETIME NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    driver_id INT UNSIGNED NULL,
    carrier_id INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_carrier FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trucks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    unit_number VARCHAR(80) NOT NULL UNIQUE,
    vin VARCHAR(100) NULL,
    plate_number VARCHAR(80) NULL,
    make VARCHAR(80) NULL,
    model VARCHAR(80) NULL,
    year SMALLINT NULL,
    insurance_expiry DATE NULL,
    registration_expiry DATE NULL,
    inspection_expiry DATE NULL,
    carrier_id INT UNSIGNED NULL,
    ownership_type ENUM('carrier_owned','company_owned','leased','rental') NOT NULL DEFAULT 'carrier_owned',
    status ENUM('available', 'on_load', 'maintenance', 'inactive') NOT NULL DEFAULT 'available',
    current_mileage INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_truck_carrier FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trailers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trailer_number VARCHAR(80) NOT NULL UNIQUE,
    type VARCHAR(80) NULL,
    plate_number VARCHAR(80) NULL,
    registration_expiry DATE NULL,
    inspection_expiry DATE NULL,
    carrier_id INT UNSIGNED NULL,
    ownership_type ENUM('carrier_owned','company_owned','leased','rental','customer_owned') NOT NULL DEFAULT 'carrier_owned',
    length_ft DECIMAL(6,2) NULL,
    max_payload_lbs INT UNSIGNED NULL,
    door_type VARCHAR(80) NULL,
    food_grade TINYINT(1) NOT NULL DEFAULT 0,
    air_ride TINYINT(1) NOT NULL DEFAULT 0,
    liftgate TINYINT(1) NOT NULL DEFAULT 0,
    hazmat_ready TINYINT(1) NOT NULL DEFAULT 0,
    reefer_capable TINYINT(1) NOT NULL DEFAULT 0,
    reefer_make VARCHAR(100) NULL,
    reefer_model VARCHAR(100) NULL,
    reefer_unit_number VARCHAR(100) NULL,
    reefer_year SMALLINT NULL,
    temperature_unit ENUM('F','C') NOT NULL DEFAULT 'F',
    min_temperature DECIMAL(7,2) NULL,
    max_temperature DECIMAL(7,2) NULL,
    current_setpoint DECIMAL(7,2) NULL,
    current_temperature DECIMAL(7,2) NULL,
    reefer_mode ENUM('continuous','cycle_sentry','off') NULL,
    reefer_hours DECIMAL(10,1) NULL,
    reefer_fuel_level DECIMAL(5,2) NULL,
    last_temperature_at DATETIME NULL,
    last_washout_date DATE NULL,
    reefer_service_due DATE NULL,
    telematics_id VARCHAR(120) NULL,
    equipment_features TEXT NULL,
    status ENUM('available', 'assigned', 'maintenance', 'inactive') NOT NULL DEFAULT 'available',
    truck_id INT UNSIGNED NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_trailer_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE SET NULL,
    CONSTRAINT fk_trailer_carrier FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS brokers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(190) NOT NULL,
    contact_name VARCHAR(140) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    mc_number VARCHAR(80) NULL,
    address VARCHAR(255) NULL,
    payment_terms VARCHAR(80) NOT NULL DEFAULT 'Net 30',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS loads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    load_number VARCHAR(100) NOT NULL UNIQUE,
    reference_number VARCHAR(120) NULL,
    broker_id INT UNSIGNED NULL,
    carrier_id INT UNSIGNED NULL,
    driver_id INT UNSIGNED NULL,
    truck_id INT UNSIGNED NULL,
    trailer_id INT UNSIGNED NULL,
    pickup_location VARCHAR(255) NOT NULL,
    delivery_location VARCHAR(255) NOT NULL,
    pickup_datetime DATETIME NULL,
    delivery_datetime DATETIME NULL,
    rate DECIMAL(12,2) NOT NULL DEFAULT 0,
    carrier_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
    dispatch_fee_type ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    dispatch_fee_value DECIMAL(12,2) NOT NULL DEFAULT 0,
    dispatch_fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    carrier_net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    equipment_source ENUM('external_carrier','company_equipment','rental','power_only') NOT NULL DEFAULT 'external_carrier',
    payment_responsibility ENUM('broker_pays_carrier','dispatch_collects','carrier_collects') NOT NULL DEFAULT 'broker_pays_carrier',
    carrier_reference VARCHAR(120) NULL,
    carrier_confirmation_status ENUM('not_sent','sent','accepted','rejected') NOT NULL DEFAULT 'not_sent',
    miles DECIMAL(10,2) NOT NULL DEFAULT 0,
    commodity VARCHAR(160) NULL,
    weight DECIMAL(10,2) NULL,
    temperature_controlled TINYINT(1) NOT NULL DEFAULT 0,
    temperature_unit ENUM('F','C') NOT NULL DEFAULT 'F',
    temperature_setpoint DECIMAL(7,2) NULL,
    temperature_min DECIMAL(7,2) NULL,
    temperature_max DECIMAL(7,2) NULL,
    reefer_mode ENUM('continuous','cycle_sentry') NULL,
    precool_required TINYINT(1) NOT NULL DEFAULT 0,
    precool_temperature DECIMAL(7,2) NULL,
    pulp_temperature_required TINYINT(1) NOT NULL DEFAULT 0,
    washout_required TINYINT(1) NOT NULL DEFAULT 0,
    seal_required TINYINT(1) NOT NULL DEFAULT 0,
    temperature_instructions TEXT NULL,
    status ENUM('new', 'assigned', 'arrived_pickup', 'picked_up', 'in_transit', 'arrived_delivery', 'delivered', 'invoiced', 'paid', 'cancelled') NOT NULL DEFAULT 'new',
    current_latitude DECIMAL(10,7) NULL,
    current_longitude DECIMAL(10,7) NULL,
    last_location_address VARCHAR(255) NULL,
    last_location_at DATETIME NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_load_broker FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE SET NULL,
    CONSTRAINT fk_load_carrier FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL,
    CONSTRAINT fk_load_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
    CONSTRAINT fk_load_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE SET NULL,
    CONSTRAINT fk_load_trailer FOREIGN KEY (trailer_id) REFERENCES trailers(id) ON DELETE SET NULL,
    CONSTRAINT fk_load_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS load_stops (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    load_id INT UNSIGNED NOT NULL,
    stop_type ENUM('pickup', 'delivery') NOT NULL DEFAULT 'pickup',
    sequence INT UNSIGNED NOT NULL DEFAULT 1,
    location VARCHAR(255) NOT NULL,
    scheduled_at DATETIME NULL,
    contact_name VARCHAR(140) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    temperature_reading DECIMAL(7,2) NULL,
    temperature_unit ENUM('F','C') NULL,
    seal_number VARCHAR(120) NULL,
    completed_at DATETIME NULL,
    completed_by INT UNSIGNED NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_load_stop_sequence (load_id, sequence),
    CONSTRAINT fk_stop_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
    CONSTRAINT fk_stop_completed_by FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    related_type VARCHAR(40) NOT NULL,
    related_id INT UNSIGNED NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size INT UNSIGNED NULL,
    expires_at DATE NULL,
    notes TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    location_address VARCHAR(255) NULL,
    captured_at DATETIME NULL,
    uploaded_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_documents_related (related_type, related_id),
    CONSTRAINT fk_documents_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS driver_locations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NULL,
    load_id INT UNSIGNED NULL,
    status VARCHAR(80) NULL,
    latitude DECIMAL(10,7) NOT NULL,
    longitude DECIMAL(10,7) NOT NULL,
    accuracy DECIMAL(10,2) NULL,
    speed DECIMAL(10,2) NULL,
    heading DECIMAL(10,2) NULL,
    battery_level DECIMAL(5,2) NULL,
    location_address VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_driver_locations_driver (driver_id),
    INDEX idx_driver_locations_load (load_id),
    CONSTRAINT fk_location_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_location_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_location_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS load_events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    load_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NULL,
    event_type VARCHAR(80) NOT NULL,
    description TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    location_address VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_load_events_load (load_id),
    CONSTRAINT fk_event_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS temperature_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    load_id INT UNSIGNED NOT NULL,
    trailer_id INT UNSIGNED NULL,
    driver_id INT UNSIGNED NULL,
    stop_id INT UNSIGNED NULL,
    document_id INT UNSIGNED NULL,
    temperature DECIMAL(7,2) NOT NULL,
    temperature_unit ENUM('F','C') NOT NULL DEFAULT 'F',
    setpoint DECIMAL(7,2) NULL,
    reefer_mode ENUM('continuous','cycle_sentry','off') NULL,
    reefer_fuel_level DECIMAL(5,2) NULL,
    pulp_temperature DECIMAL(7,2) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    location_address VARCHAR(255) NULL,
    notes TEXT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by INT UNSIGNED NULL,
    INDEX idx_temp_load (load_id, recorded_at),
    INDEX idx_temp_trailer (trailer_id, recorded_at),
    CONSTRAINT fk_temp_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
    CONSTRAINT fk_temp_trailer FOREIGN KEY (trailer_id) REFERENCES trailers(id) ON DELETE SET NULL,
    CONSTRAINT fk_temp_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
    CONSTRAINT fk_temp_stop FOREIGN KEY (stop_id) REFERENCES load_stops(id) ON DELETE SET NULL,
    CONSTRAINT fk_temp_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    CONSTRAINT fk_temp_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(100) NOT NULL UNIQUE,
    load_id INT UNSIGNED NULL,
    broker_id INT UNSIGNED NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('draft', 'approved', 'sent', 'viewed', 'partially_paid', 'paid', 'overdue', 'cancelled', 'written_off', 'void') NOT NULL DEFAULT 'draft',
    invoice_date DATE NULL,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount DECIMAL(12,2) NOT NULL DEFAULT 0,
    balance_due DECIMAL(12,2) NOT NULL DEFAULT 0,
    sent_at DATETIME NULL,
    viewed_at DATETIME NULL,
    payment_terms VARCHAR(100) NULL,
    billing_notes TEXT NULL,
    due_date DATE NULL,
    paid_at DATETIME NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_invoice_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL,
    CONSTRAINT fk_invoice_broker FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_invoice_item_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    payment_date DATE NOT NULL,
    method VARCHAR(80) NULL,
    reference VARCHAR(120) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payment_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS driver_settlements (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    settlement_number VARCHAR(100) NOT NULL UNIQUE,
    driver_id INT UNSIGNED NOT NULL,
    load_id INT UNSIGNED NULL,
    gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('pending', 'paid') NOT NULL DEFAULT 'pending',
    paid_at DATETIME NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_settlement_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_settlement_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    truck_id INT UNSIGNED NULL,
    trailer_id INT UNSIGNED NULL,
    service_type VARCHAR(120) NOT NULL,
    service_date DATE NOT NULL,
    next_service_date DATE NULL,
    mileage INT UNSIGNED NULL,
    cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('scheduled', 'completed', 'pending') NOT NULL DEFAULT 'completed',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_maintenance_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE CASCADE,
    CONSTRAINT fk_maintenance_trailer FOREIGN KEY (trailer_id) REFERENCES trailers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The administrator account is created securely by install.php.

INSERT INTO carriers (id, company_name, dba_name, contact_name, email, phone, mc_number, dot_number, dispatch_fee_type, dispatch_fee_value, payment_terms, authority_status, onboarding_status, status, insurance_expiry)
VALUES
(1, 'Reed Transport LLC', 'Reed Transport', 'Michael Reed', 'dispatch@reedtransport.test', '+1 555 400 1001', 'MC-724190', 'DOT-3482110', 'percentage', 8.00, 'Weekly', 'active', 'approved', 'active', '2027-05-31'),
(2, 'Carter Hauling Inc.', NULL, 'James Carter', 'ops@carterhauling.test', '+1 555 400 1002', 'MC-883201', 'DOT-5198842', 'fixed_per_load', 150.00, 'Weekly', 'active', 'approved', 'active', '2027-07-15')
ON DUPLICATE KEY UPDATE company_name = company_name;

INSERT INTO drivers (id, name, email, phone, license_number, license_expiry, medical_card_expiry, carrier_id, employment_type, pay_type, rate, status, address)
VALUES
(1, 'Michael Reed', 'michael@example.com', '+1 555 100 2001', 'CDL-92831', '2027-08-12', '2027-04-12', 1, 'owner_operator', 'percentage', 25.00, 'available', 'Dallas, TX'),
(2, 'James Carter', 'james@example.com', '+1 555 100 2002', 'CDL-78221', '2027-05-21', '2027-03-08', 2, 'carrier_driver', 'per_mile', 0.65, 'on_load', 'Houston, TX')
ON DUPLICATE KEY UPDATE name = name;

INSERT INTO trucks (id, unit_number, vin, plate_number, make, model, year, insurance_expiry, registration_expiry, inspection_expiry, carrier_id, ownership_type, status, current_mileage)
VALUES
(1, 'TRK-101', '1XP5DB9X44D833456', 'TX-8271', 'Peterbilt', '579', 2021, '2027-01-15', '2027-03-01', '2027-02-12', 1, 'carrier_owned', 'available', 182000),
(2, 'TRK-102', '3AKJGLD54ESFM9182', 'TX-9348', 'Freightliner', 'Cascadia', 2020, '2027-02-10', '2027-02-25', '2027-01-30', 2, 'carrier_owned', 'on_load', 238500)
ON DUPLICATE KEY UPDATE unit_number = unit_number;

INSERT INTO trailers (id, trailer_number, type, plate_number, registration_expiry, inspection_expiry, carrier_id, ownership_type, reefer_capable, reefer_make, reefer_model, reefer_unit_number, temperature_unit, min_temperature, max_temperature, current_setpoint, current_temperature, reefer_mode, status, truck_id)
VALUES
(1, 'TRL-201', 'Dry Van', 'TX-T201', '2027-02-14', '2027-04-01', 1, 'carrier_owned', 0, NULL, NULL, NULL, 'F', NULL, NULL, NULL, NULL, NULL, 'available', NULL),
(2, 'TRL-202', 'Reefer', 'TX-T202', '2027-05-19', '2027-05-30', 2, 'carrier_owned', 1, 'Carrier', 'X4 7500', 'RF-202', 'F', -20, 70, 34, 35, 'continuous', 'assigned', 2)
ON DUPLICATE KEY UPDATE trailer_number = trailer_number;

INSERT INTO brokers (id, company_name, contact_name, email, phone, mc_number, address, payment_terms, status)
VALUES
(1, 'BlueLine Logistics', 'Sarah Miles', 'sarah@blueline.test', '+1 555 200 3001', 'MC-583910', 'Chicago, IL', 'Net 30', 'active'),
(2, 'Prime Freight Group', 'Daniel Brooks', 'daniel@primefreight.test', '+1 555 200 3002', 'MC-118452', 'Atlanta, GA', 'Net 21', 'active')
ON DUPLICATE KEY UPDATE company_name = company_name;

INSERT INTO loads (id, load_number, reference_number, broker_id, carrier_id, driver_id, truck_id, trailer_id, pickup_location, delivery_location, pickup_datetime, delivery_datetime, rate, carrier_rate, dispatch_fee_type, dispatch_fee_value, dispatch_fee_amount, carrier_net_amount, equipment_source, payment_responsibility, miles, commodity, weight, temperature_controlled, temperature_unit, temperature_setpoint, temperature_min, temperature_max, reefer_mode, precool_required, precool_temperature, status, created_by)
VALUES
(1, 'LD-1001', 'REF-89412', 1, 2, 2, 2, 2, 'Dallas, TX', 'Phoenix, AZ', DATE_ADD(NOW(), INTERVAL 1 DAY), DATE_ADD(NOW(), INTERVAL 3 DAY), 2400.00, 2400.00, 'percentage', 8.00, 192.00, 2208.00, 'external_carrier', 'broker_pays_carrier', 1065, 'Fresh Produce', 42000, 1, 'F', 34, 32, 36, 'continuous', 1, 34, 'in_transit', 1),
(2, 'LD-1002', 'REF-71102', 2, 1, 1, 1, 1, 'Houston, TX', 'Memphis, TN', DATE_ADD(NOW(), INTERVAL 2 DAY), DATE_ADD(NOW(), INTERVAL 4 DAY), 1800.00, 1800.00, 'percentage', 8.00, 144.00, 1656.00, 'external_carrier', 'broker_pays_carrier', 567, 'Retail Goods', 32000, 0, 'F', NULL, NULL, NULL, NULL, 0, NULL, 'new', 1)
ON DUPLICATE KEY UPDATE load_number = load_number;

INSERT INTO load_stops (load_id, stop_type, sequence, location, scheduled_at, contact_name)
VALUES
(1, 'pickup', 1, 'Dallas Warehouse 12', DATE_ADD(NOW(), INTERVAL 1 DAY), 'Shipping Desk'),
(1, 'delivery', 2, 'Phoenix Distribution Center', DATE_ADD(NOW(), INTERVAL 3 DAY), 'Receiving')
ON DUPLICATE KEY UPDATE load_id = load_id;

INSERT INTO maintenance_records (id, truck_id, trailer_id, service_type, service_date, next_service_date, mileage, cost, status, notes)
VALUES
(1, 1, NULL, 'Oil Change', DATE_SUB(CURDATE(), INTERVAL 20 DAY), DATE_ADD(CURDATE(), INTERVAL 25 DAY), 180500, 390.00, 'completed', 'Next oil change due soon'),
(2, NULL, 2, 'Reefer Inspection', DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_ADD(CURDATE(), INTERVAL 18 DAY), NULL, 125.00, 'completed', 'Temperature unit checked')
ON DUPLICATE KEY UPDATE service_type = service_type;

INSERT INTO activity_logs (user_id, action, description)
VALUES
(1, 'system_ready', 'System installed and ready for dispatch operations')
ON DUPLICATE KEY UPDATE action = action;


-- Complete operations, accounting, live tracking, and border-crossing modules.
CREATE TABLE IF NOT EXISTS system_settings (
    setting_key VARCHAR(120) PRIMARY KEY,
    setting_value TEXT NULL,
    updated_by INT UNSIGNED NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NULL,
    load_id INT UNSIGNED NULL,
    status ENUM('active','paused','stopped') NOT NULL DEFAULT 'active',
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_ping_at DATETIME NULL,
    stopped_at DATETIME NULL,
    consent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tracking_driver (driver_id, status),
    INDEX idx_tracking_load (load_id),
    CONSTRAINT fk_tracking_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_tracking_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_tracking_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customs_brokers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(190) NOT NULL,
    contact_name VARCHAR(140) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    license_number VARCHAR(100) NULL,
    address VARCHAR(255) NULL,
    notes TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS border_crossings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    crossing_number VARCHAR(100) NOT NULL UNIQUE,
    load_id INT UNSIGNED NOT NULL,
    customs_broker_id INT UNSIGNED NULL,
    crossing_name VARCHAR(190) NOT NULL,
    port_code VARCHAR(80) NULL,
    origin_country VARCHAR(100) NOT NULL,
    destination_country VARCHAR(100) NOT NULL,
    direction VARCHAR(80) NULL,
    planned_at DATETIME NULL,
    arrived_at DATETIME NULL,
    queue_started_at DATETIME NULL,
    inspection_started_at DATETIME NULL,
    cleared_at DATETIME NULL,
    crossed_at DATETIME NULL,
    status VARCHAR(60) NOT NULL DEFAULT 'not_started',
    reference_number VARCHAR(120) NULL,
    entry_number VARCHAR(120) NULL,
    seal_number VARCHAR(120) NULL,
    commodity VARCHAR(190) NULL,
    package_count INT UNSIGNED NULL,
    weight DECIMAL(12,2) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    geofence_radius_m INT UNSIGNED NOT NULL DEFAULT 1500,
    detention_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
    detention_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    detention_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    dispatcher_notes TEXT NULL,
    driver_instructions TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_border_status (status, planned_at),
    CONSTRAINT fk_border_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
    CONSTRAINT fk_border_customs_broker FOREIGN KEY (customs_broker_id) REFERENCES customs_brokers(id) ON DELETE SET NULL,
    CONSTRAINT fk_border_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS border_checklist_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    border_crossing_id INT UNSIGNED NOT NULL,
    item_name VARCHAR(190) NOT NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('missing','uploaded','approved','rejected','waived') NOT NULL DEFAULT 'missing',
    document_id INT UNSIGNED NULL,
    reviewed_by INT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    notes TEXT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_border_checklist_crossing FOREIGN KEY (border_crossing_id) REFERENCES border_crossings(id) ON DELETE CASCADE,
    CONSTRAINT fk_border_checklist_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    CONSTRAINT fk_border_checklist_user FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS border_issues (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    border_crossing_id INT UNSIGNED NOT NULL,
    issue_type VARCHAR(120) NOT NULL,
    authority_name VARCHAR(190) NULL,
    reference_number VARCHAR(120) NULL,
    status ENUM('open','under_review','resolved','cancelled') NOT NULL DEFAULT 'open',
    started_at DATETIME NULL,
    resolved_at DATETIME NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    description TEXT NULL,
    resolution TEXT NULL,
    reported_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_border_issue_crossing FOREIGN KEY (border_crossing_id) REFERENCES border_crossings(id) ON DELETE CASCADE,
    CONSTRAINT fk_border_issue_user FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    role_target VARCHAR(40) NULL,
    title VARCHAR(190) NOT NULL,
    message TEXT NULL,
    link VARCHAR(255) NULL,
    level ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',
    read_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notifications_user (user_id, read_at),
    CONSTRAINT fk_notification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(190) NOT NULL,
    contact_name VARCHAR(140) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    tax_id VARCHAR(100) NULL,
    address VARCHAR(255) NULL,
    payment_terms VARCHAR(80) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL UNIQUE,
    account_code VARCHAR(30) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expense_number VARCHAR(100) NOT NULL UNIQUE,
    category_id INT UNSIGNED NULL,
    vendor_id INT UNSIGNED NULL,
    load_id INT UNSIGNED NULL,
    driver_id INT UNSIGNED NULL,
    truck_id INT UNSIGNED NULL,
    trailer_id INT UNSIGNED NULL,
    border_crossing_id INT UNSIGNED NULL,
    expense_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(80) NULL,
    status ENUM('submitted','under_review','approved','rejected','reimbursed','posted') NOT NULL DEFAULT 'submitted',
    receipt_document_id INT UNSIGNED NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    location_address VARCHAR(255) NULL,
    description TEXT NULL,
    submitted_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_expense_category FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_trailer FOREIGN KEY (trailer_id) REFERENCES trailers(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_border FOREIGN KEY (border_crossing_id) REFERENCES border_crossings(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_receipt FOREIGN KEY (receipt_document_id) REFERENCES documents(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_submitter FOREIGN KEY (submitted_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(190) NOT NULL,
    account_type ENUM('bank','cash','credit_card','fuel_card') NOT NULL DEFAULT 'bank',
    institution VARCHAR(190) NULL,
    last_four VARCHAR(10) NULL,
    opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
    current_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bank_account_id INT UNSIGNED NOT NULL,
    transaction_date DATE NOT NULL,
    transaction_type ENUM('deposit','withdrawal','transfer_in','transfer_out','fee','adjustment') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    reference_number VARCHAR(120) NULL,
    payee VARCHAR(190) NULL,
    description TEXT NULL,
    reconciled_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bank_tx_account FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE,
    CONSTRAINT fk_bank_tx_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chart_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    account_type ENUM('asset','liability','equity','revenue','expense') NOT NULL,
    parent_id INT UNSIGNED NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    system_account TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT fk_chart_parent FOREIGN KEY (parent_id) REFERENCES chart_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS journal_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_number VARCHAR(100) NOT NULL UNIQUE,
    entry_date DATE NOT NULL,
    source_type VARCHAR(60) NULL,
    source_id INT UNSIGNED NULL,
    memo TEXT NULL,
    status ENUM('draft','posted','reversed') NOT NULL DEFAULT 'posted',
    created_by INT UNSIGNED NULL,
    posted_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_journal_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS journal_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    journal_entry_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    description VARCHAR(255) NULL,
    debit DECIMAL(14,2) NOT NULL DEFAULT 0,
    credit DECIMAL(14,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_journal_line_entry FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    CONSTRAINT fk_journal_line_account FOREIGN KEY (account_id) REFERENCES chart_accounts(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendor_bills (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bill_number VARCHAR(100) NOT NULL UNIQUE,
    vendor_id INT UNSIGNED NOT NULL,
    bill_date DATE NOT NULL,
    due_date DATE NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    balance_due DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('draft','approved','partially_paid','paid','overdue','void') NOT NULL DEFAULT 'draft',
    notes TEXT NULL,
    document_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_vendor_bill_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE RESTRICT,
    CONSTRAINT fk_vendor_bill_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    CONSTRAINT fk_vendor_bill_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendor_bill_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vendor_bill_id INT UNSIGNED NOT NULL,
    bank_account_id INT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(80) NULL,
    reference_number VARCHAR(120) NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bill_payment_bill FOREIGN KEY (vendor_bill_id) REFERENCES vendor_bills(id) ON DELETE CASCADE,
    CONSTRAINT fk_bill_payment_bank FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_payment_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS factoring_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL UNIQUE,
    vendor_id INT UNSIGNED NULL,
    status ENUM('not_factored','submitted','funded','reserve_pending','completed','charged_back') NOT NULL DEFAULT 'not_factored',
    submitted_at DATETIME NULL,
    advance_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    reserve_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    advance_received DECIMAL(12,2) NOT NULL DEFAULT 0,
    reserve_released DECIMAL(12,2) NOT NULL DEFAULT 0,
    chargeback_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_factoring_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_factoring_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    description TEXT NULL,
    assigned_to INT UNSIGNED NULL,
    related_type VARCHAR(50) NULL,
    related_id INT UNSIGNED NULL,
    priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    status ENUM('open','in_progress','blocked','completed') NOT NULL DEFAULT 'open',
    due_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    completed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_task_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_task_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO expense_categories (name, account_code) VALUES
('Fuel','5100'),('Tolls','5110'),('Parking','5120'),('Lumper','5130'),('Maintenance','5200'),('Repairs','5210'),('Border Fees','5300'),('Customs / Broker Fees','5310'),('Permits','5320'),('Challan / Fine','5330'),('Hotel','5400'),('Meals','5410'),('Insurance','5500'),('Office','5600'),('Other','5990')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO chart_accounts (code, name, account_type, system_account) VALUES
('1000','Cash and Bank','asset',1),('1100','Accounts Receivable','asset',1),('1200','Prepaid Expenses','asset',0),('2000','Accounts Payable','liability',1),('2100','Taxes Payable','liability',0),('3000','Owner Equity','equity',1),('4000','Freight Revenue','revenue',1),('4100','Accessorial Revenue','revenue',0),('5000','Driver Pay','expense',1),('5100','Fuel Expense','expense',1),('5110','Toll Expense','expense',0),('5130','Lumper Expense','expense',0),('5200','Maintenance Expense','expense',0),('5300','Border Expense','expense',0),('5500','Insurance Expense','expense',0),('5600','Office Expense','expense',0),('5990','Other Expense','expense',0)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO system_settings (setting_key, setting_value) VALUES
('company_name','FleetLume'),('company_address',''),('company_phone',''),('company_email',''),('company_mc',''),('company_dot',''),('company_logo',''),('invoice_payment_instructions','Please remit payment using the agreed payment method.'),('invoice_terms','Thank you for your business.'),('currency_symbol','$'),('tracking_interval_seconds','30'),('seo_title','FleetLume | Dispatch, Driver HOS, GPS & Accounting'),('seo_description','FleetLume helps dispatch companies manage carriers, loads, driver logbooks and HOS, live GPS, reefer temperatures, fuel allowances, border crossings, documents, invoicing, and accounting.')
ON DUPLICATE KEY UPDATE setting_value = setting_value;


CREATE TABLE IF NOT EXISTS fuel_allocations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    allocation_number VARCHAR(100) NOT NULL UNIQUE,
    load_id INT UNSIGNED NULL,
    carrier_id INT UNSIGNED NULL,
    driver_id INT UNSIGNED NULL,
    truck_id INT UNSIGNED NULL,
    bank_account_id INT UNSIGNED NULL,
    allocation_method ENUM('fuel_card','cash_advance','company_card','reimbursement') NOT NULL DEFAULT 'fuel_card',
    calculation_method ENUM('manual','miles_mpg','percentage_of_load') NOT NULL DEFAULT 'miles_mpg',
    planned_miles DECIMAL(10,2) NOT NULL DEFAULT 0,
    target_mpg DECIMAL(6,2) NOT NULL DEFAULT 6.50,
    expected_price_per_gallon DECIMAL(10,3) NOT NULL DEFAULT 0,
    reserve_percent DECIMAL(6,2) NOT NULL DEFAULT 0,
    recommended_gallons DECIMAL(12,3) NOT NULL DEFAULT 0,
    recommended_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    allocated_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    spent_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    remaining_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    valid_from DATE NULL,
    valid_until DATE NULL,
    status ENUM('draft','approved','issued','partially_used','used','closed','cancelled') NOT NULL DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_fuel_allocation_driver (driver_id, status),
    INDEX idx_fuel_allocation_load (load_id),
    CONSTRAINT fk_fuel_allocation_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_allocation_carrier FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_allocation_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_allocation_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_allocation_account FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_allocation_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_allocation_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fuel_transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    allocation_id INT UNSIGNED NOT NULL,
    expense_id INT UNSIGNED NULL,
    transaction_number VARCHAR(100) NOT NULL UNIQUE,
    transaction_date DATE NOT NULL,
    merchant VARCHAR(190) NULL,
    city_state VARCHAR(160) NULL,
    gallons DECIMAL(12,3) NOT NULL DEFAULT 0,
    price_per_gallon DECIMAL(10,3) NOT NULL DEFAULT 0,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    odometer INT UNSIGNED NULL,
    status ENUM('submitted','approved','rejected','posted') NOT NULL DEFAULT 'submitted',
    receipt_document_id INT UNSIGNED NULL,
    notes TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    location_address VARCHAR(255) NULL,
    submitted_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_fuel_tx_allocation (allocation_id, status),
    CONSTRAINT fk_fuel_tx_allocation FOREIGN KEY (allocation_id) REFERENCES fuel_allocations(id) ON DELETE CASCADE,
    CONSTRAINT fk_fuel_tx_expense FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_tx_receipt FOREIGN KEY (receipt_document_id) REFERENCES documents(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_tx_submitter FOREIGN KEY (submitted_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_tx_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO expense_categories (name, account_code, active)
SELECT 'Fuel', '5100', 1
WHERE NOT EXISTS (SELECT 1 FROM expense_categories WHERE name='Fuel');

INSERT INTO chart_accounts (code, name, account_type, active, system_account)
SELECT '5100', 'Fuel Expense', 'expense', 1, 1
WHERE NOT EXISTS (SELECT 1 FROM chart_accounts WHERE code='5100');

-- Driver Hours of Service and operational logbook module.
CREATE TABLE IF NOT EXISTS driver_hos_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id INT UNSIGNED NOT NULL UNIQUE,
    operating_region ENUM('us','canada','cross_border') NOT NULL DEFAULT 'cross_border',
    default_rule_set ENUM('us_property','canada_south','canada_north') NOT NULL DEFAULT 'us_property',
    us_cycle ENUM('60_7','70_8') NOT NULL DEFAULT '70_8',
    canada_cycle ENUM('cycle_1','cycle_2') NOT NULL DEFAULT 'cycle_1',
    home_terminal_name VARCHAR(190) NULL,
    home_terminal_address VARCHAR(255) NULL,
    home_terminal_timezone VARCHAR(100) NOT NULL DEFAULT 'America/Chicago',
    log_day_start_time TIME NOT NULL DEFAULT '00:00:00',
    eld_provider VARCHAR(190) NULL,
    eld_device_identifier VARCHAR(190) NULL,
    eld_exempt TINYINT(1) NOT NULL DEFAULT 0,
    eld_exemption_reason TEXT NULL,
    short_haul_enabled TINYINT(1) NOT NULL DEFAULT 0,
    personal_conveyance_allowed TINYINT(1) NOT NULL DEFAULT 1,
    yard_move_allowed TINYINT(1) NOT NULL DEFAULT 1,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_hos_profile_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hos_daily_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id INT UNSIGNED NOT NULL,
    log_date DATE NOT NULL,
    jurisdiction ENUM('us','canada') NOT NULL DEFAULT 'us',
    rule_set ENUM('us_property','canada_south','canada_north') NOT NULL DEFAULT 'us_property',
    cycle_code VARCHAR(40) NULL,
    home_terminal_timezone VARCHAR(100) NOT NULL DEFAULT 'America/Chicago',
    vehicle_numbers VARCHAR(255) NULL,
    trailer_numbers VARCHAR(255) NULL,
    shipping_documents VARCHAR(255) NULL,
    start_odometer INT UNSIGNED NULL,
    end_odometer INT UNSIGNED NULL,
    total_off_duty_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    total_sleeper_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    total_driving_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    total_on_duty_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    exceptions JSON NULL,
    violations JSON NULL,
    notes TEXT NULL,
    status ENUM('draft','certified','needs_recertification','locked') NOT NULL DEFAULT 'draft',
    certified_by INT UNSIGNED NULL,
    certification_name VARCHAR(190) NULL,
    certified_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_hos_driver_date (driver_id, log_date),
    INDEX idx_hos_logs_status (status, log_date),
    CONSTRAINT fk_hos_log_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_hos_log_certifier FOREIGN KEY (certified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hos_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id INT UNSIGNED NOT NULL,
    daily_log_id INT UNSIGNED NULL,
    event_at DATETIME NOT NULL,
    duty_status ENUM('off_duty','sleeper_berth','driving','on_duty') NOT NULL,
    special_status ENUM('personal_conveyance','yard_move') NULL,
    jurisdiction ENUM('us','canada') NOT NULL DEFAULT 'us',
    rule_set ENUM('us_property','canada_south','canada_north') NOT NULL DEFAULT 'us_property',
    location_address VARCHAR(255) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    odometer INT UNSIGNED NULL,
    engine_hours DECIMAL(12,1) NULL,
    load_id INT UNSIGNED NULL,
    truck_id INT UNSIGNED NULL,
    trailer_id INT UNSIGNED NULL,
    annotation TEXT NULL,
    origin ENUM('manual','gps','eld_import','system') NOT NULL DEFAULT 'manual',
    record_status ENUM('active','superseded') NOT NULL DEFAULT 'active',
    supersedes_event_id BIGINT UNSIGNED NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_hos_events_driver_time (driver_id, event_at, record_status),
    INDEX idx_hos_events_log (daily_log_id),
    INDEX idx_hos_events_load (load_id),
    CONSTRAINT fk_hos_event_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_hos_event_log FOREIGN KEY (daily_log_id) REFERENCES hos_daily_logs(id) ON DELETE SET NULL,
    CONSTRAINT fk_hos_event_load FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE SET NULL,
    CONSTRAINT fk_hos_event_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE SET NULL,
    CONSTRAINT fk_hos_event_trailer FOREIGN KEY (trailer_id) REFERENCES trailers(id) ON DELETE SET NULL,
    CONSTRAINT fk_hos_event_supersedes FOREIGN KEY (supersedes_event_id) REFERENCES hos_events(id) ON DELETE SET NULL,
    CONSTRAINT fk_hos_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hos_edit_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    driver_id INT UNSIGNED NOT NULL,
    proposed_event_at DATETIME NOT NULL,
    proposed_duty_status ENUM('off_duty','sleeper_berth','driving','on_duty') NOT NULL,
    proposed_special_status ENUM('personal_conveyance','yard_move') NULL,
    proposed_location_address VARCHAR(255) NULL,
    proposed_annotation TEXT NULL,
    reason TEXT NOT NULL,
    status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    requested_by INT UNSIGNED NULL,
    reviewed_by INT UNSIGNED NULL,
    review_notes TEXT NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_hos_edit_status (status, created_at),
    INDEX idx_hos_edit_driver (driver_id, status),
    CONSTRAINT fk_hos_edit_event FOREIGN KEY (event_id) REFERENCES hos_events(id) ON DELETE CASCADE,
    CONSTRAINT fk_hos_edit_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_hos_edit_requester FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_hos_edit_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO driver_hos_profiles (driver_id, operating_region, default_rule_set, us_cycle, canada_cycle, home_terminal_name, home_terminal_address, home_terminal_timezone)
SELECT id, 'cross_border', 'us_property', '70_8', 'cycle_1', 'Main Terminal', address, 'America/Chicago' FROM drivers
ON DUPLICATE KEY UPDATE driver_id = VALUES(driver_id);

INSERT INTO system_settings (setting_key, setting_value) VALUES
('hos_module_enabled','1'),
('hos_disclaimer','FleetLume provides operational logbook records and clock calculations. Use a registered or certified ELD where legally required.'),
('hos_default_us_cycle','70_8'),
('hos_default_canada_cycle','cycle_1')
ON DUPLICATE KEY UPDATE setting_value = setting_value;


-- FleetLume AI integration. API keys are never stored in this table.
CREATE TABLE IF NOT EXISTS ai_usage_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    feature VARCHAR(80) NOT NULL,
    model_name VARCHAR(120) NOT NULL,
    prompt_hash CHAR(64) NOT NULL,
    status ENUM('success','failed') NOT NULL,
    latency_ms INT UNSIGNED NOT NULL DEFAULT 0,
    prompt_tokens INT UNSIGNED NOT NULL DEFAULT 0,
    output_tokens INT UNSIGNED NOT NULL DEFAULT 0,
    total_tokens INT UNSIGNED NOT NULL DEFAULT 0,
    output_characters INT UNSIGNED NOT NULL DEFAULT 0,
    error_message VARCHAR(1000) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ai_usage_user_time (user_id, created_at),
    INDEX idx_ai_usage_feature_time (feature, created_at),
    CONSTRAINT fk_ai_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_settings (setting_key, setting_value) VALUES
('gemini_enabled','0'),
('gemini_model','gemini-3.5-flash'),
('gemini_context_enabled','1'),
('gemini_max_requests_per_hour','30'),
('gemini_timeout_seconds','75')
ON DUPLICATE KEY UPDATE setting_value = setting_value;

-- FleetLume AI automation and transparent change-management layer.
CREATE TABLE IF NOT EXISTS ai_automation_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    event_type ENUM('scheduled','load_exception','document_missing','reefer_alert','fuel_exception','hos_risk','border_delay','invoice_overdue','carrier_compliance') NOT NULL DEFAULT 'scheduled',
    execution_mode ENUM('recommend_only','auto_safe') NOT NULL DEFAULT 'recommend_only',
    frequency ENUM('hourly','every_4_hours','daily','manual') NOT NULL DEFAULT 'daily',
    priority TINYINT UNSIGNED NOT NULL DEFAULT 50,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    config_json JSON NULL,
    last_run_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ai_rules_enabled (enabled, frequency, priority),
    CONSTRAINT fk_ai_rule_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_automation_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rule_id INT UNSIGNED NULL,
    status ENUM('running','completed','completed_with_errors','failed') NOT NULL DEFAULT 'running',
    trigger_source ENUM('manual','cron','event','api') NOT NULL DEFAULT 'manual',
    summary TEXT NULL,
    items_scanned INT UNSIGNED NOT NULL DEFAULT 0,
    actions_proposed INT UNSIGNED NOT NULL DEFAULT 0,
    actions_executed INT UNSIGNED NOT NULL DEFAULT 0,
    error_message TEXT NULL,
    initiated_by INT UNSIGNED NULL,
    started_at DATETIME NOT NULL,
    completed_at DATETIME NULL,
    INDEX idx_ai_runs_time (started_at, status),
    CONSTRAINT fk_ai_run_rule FOREIGN KEY (rule_id) REFERENCES ai_automation_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_ai_run_user FOREIGN KEY (initiated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_action_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    run_id BIGINT UNSIGNED NULL,
    rule_id INT UNSIGNED NULL,
    action_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(60) NULL,
    entity_id INT UNSIGNED NULL,
    title VARCHAR(190) NOT NULL,
    description TEXT NULL,
    payload_json JSON NULL,
    risk_level ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    status ENUM('proposed','approved','executed','rejected','failed','skipped') NOT NULL DEFAULT 'proposed',
    requires_approval TINYINT(1) NOT NULL DEFAULT 1,
    created_by_ai TINYINT(1) NOT NULL DEFAULT 1,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    executed_by INT UNSIGNED NULL,
    executed_at DATETIME NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ai_actions_status (status, risk_level, created_at),
    INDEX idx_ai_actions_entity (entity_type, entity_id),
    CONSTRAINT fk_ai_action_run FOREIGN KEY (run_id) REFERENCES ai_automation_runs(id) ON DELETE SET NULL,
    CONSTRAINT fk_ai_action_rule FOREIGN KEY (rule_id) REFERENCES ai_automation_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_ai_action_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_ai_action_executor FOREIGN KEY (executed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_change_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action_id BIGINT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    change_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(60) NULL,
    entity_id INT UNSIGNED NULL,
    title VARCHAR(190) NOT NULL,
    before_json JSON NULL,
    after_json JSON NULL,
    message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ai_changes_time (created_at),
    INDEX idx_ai_changes_entity (entity_type, entity_id),
    CONSTRAINT fk_ai_change_action FOREIGN KEY (action_id) REFERENCES ai_action_queue(id) ON DELETE SET NULL,
    CONSTRAINT fk_ai_change_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO ai_automation_rules (name,description,event_type,execution_mode,frequency,priority,enabled,config_json)
SELECT 'Operations exception monitor','Find late loads, missing documents, reefer alerts, HOS risks, border delays, fuel exceptions, overdue invoices, and carrier compliance issues.','scheduled','auto_safe','hourly',90,1,
JSON_OBJECT('lookahead_hours',24,'grace_minutes',30,'create_task',true,'send_notification',true,'use_ai_summary',true,'target_roles',JSON_ARRAY('owner','admin','dispatcher'))
WHERE NOT EXISTS (SELECT 1 FROM ai_automation_rules WHERE name='Operations exception monitor');

INSERT INTO ai_automation_rules (name,description,event_type,execution_mode,frequency,priority,enabled,config_json)
SELECT 'Accounting collections monitor','Find overdue invoices and create accountable collection follow-up work.','invoice_overdue','auto_safe','daily',70,1,
JSON_OBJECT('lookahead_hours',24,'grace_minutes',0,'create_task',true,'send_notification',true,'use_ai_summary',true,'target_roles',JSON_ARRAY('owner','admin','accountant'))
WHERE NOT EXISTS (SELECT 1 FROM ai_automation_rules WHERE name='Accounting collections monitor');

INSERT INTO ai_automation_rules (name,description,event_type,execution_mode,frequency,priority,enabled,config_json)
SELECT 'Reefer safety monitor','Escalate temperature readings outside the configured load range.','reefer_alert','auto_safe','hourly',100,1,
JSON_OBJECT('lookahead_hours',12,'grace_minutes',0,'create_task',true,'send_notification',true,'use_ai_summary',true,'target_roles',JSON_ARRAY('owner','admin','dispatcher','maintenance'))
WHERE NOT EXISTS (SELECT 1 FROM ai_automation_rules WHERE name='Reefer safety monitor');

INSERT INTO system_settings (setting_key,setting_value) VALUES
('ai_automation_enabled','1'),
('ai_automation_notify_changes','1'),
('ui_version','3'),
('ui_compact_sidebar_default','0')
ON DUPLICATE KEY UPDATE setting_value=setting_value;
