-- Nigeria vehicle papers/documentation registry
-- Assumption: this seeds commonly tracked Nigerian vehicle documents and
-- keeps the model extensible because exact state/commercial requirements vary.

CREATE TABLE IF NOT EXISTS nigeria_document_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(60) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    applies_to ENUM('vehicle','driver','fleet','operator') NOT NULL DEFAULT 'vehicle',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_nigeria_document_categories_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS nigeria_document_types (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(180) NOT NULL,
    applies_to ENUM('vehicle','driver','fleet','operator') NOT NULL DEFAULT 'vehicle',
    issuing_level ENUM('federal','state','local','mixed') NOT NULL DEFAULT 'mixed',
    issuing_authority VARCHAR(180) NULL,
    renewal_cycle_months INT NULL,
    required_for_private TINYINT(1) NOT NULL DEFAULT 0,
    required_for_commercial TINYINT(1) NOT NULL DEFAULT 0,
    required_for_heavy_duty TINYINT(1) NOT NULL DEFAULT 0,
    required_for_interstate TINYINT(1) NOT NULL DEFAULT 0,
    requires_document_number TINYINT(1) NOT NULL DEFAULT 1,
    requires_issue_date TINYINT(1) NOT NULL DEFAULT 1,
    requires_expiry_date TINYINT(1) NOT NULL DEFAULT 1,
    default_notification_days INT NOT NULL DEFAULT 30,
    existing_storage_table VARCHAR(120) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_nigeria_document_types_code (code),
    KEY idx_nigeria_document_types_category (category_id),
    CONSTRAINT fk_nigeria_document_types_category
        FOREIGN KEY (category_id) REFERENCES nigeria_document_categories(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_document_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    document_type_id BIGINT UNSIGNED NOT NULL,
    jurisdiction_state VARCHAR(120) NULL,
    jurisdiction_lga VARCHAR(120) NULL,
    profile_scope ENUM('national','state','local','route','contract') NOT NULL DEFAULT 'national',
    is_mandatory TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    remarks TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_vehicle_document_profiles_vehicle_doc_scope (
        vehicle_id,
        document_type_id,
        jurisdiction_state,
        jurisdiction_lga,
        profile_scope
    ),
    KEY idx_vehicle_document_profiles_document_type (document_type_id),
    CONSTRAINT fk_vehicle_document_profiles_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_document_profiles_document_type
        FOREIGN KEY (document_type_id) REFERENCES nigeria_document_types(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_document_renewals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    profile_id BIGINT UNSIGNED NOT NULL,
    document_number VARCHAR(120) NULL,
    issue_date DATE NULL,
    expiry_date DATE NULL,
    notification_date DATE NULL,
    issuing_authority_name VARCHAR(180) NULL,
    issuing_office VARCHAR(180) NULL,
    vendor_name VARCHAR(180) NULL,
    status ENUM('draft','valid','expired','revoked','waived') NOT NULL DEFAULT 'valid',
    amount_paid DECIMAL(12,2) NULL,
    payment_reference VARCHAR(120) NULL,
    receipt_number VARCHAR(120) NULL,
    file_url VARCHAR(255) NULL,
    notes TEXT NULL,
    metadata_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_vehicle_document_renewals_profile (profile_id),
    KEY idx_vehicle_document_renewals_expiry (expiry_date),
    CONSTRAINT fk_vehicle_document_renewals_profile
        FOREIGN KEY (profile_id) REFERENCES vehicle_document_profiles(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO nigeria_document_categories (code, name, description, applies_to)
VALUES
    ('REGISTRATION_OWNERSHIP', 'Registration / Ownership', 'Core vehicle registration and ownership papers.', 'vehicle'),
    ('SAFETY_COMPLIANCE', 'Safety / Compliance', 'Roadworthiness and safety-driven regulatory documents.', 'vehicle'),
    ('INSURANCE', 'Insurance', 'Motor insurance and related risk coverage documents.', 'vehicle'),
    ('COMMERCIAL_OPERATIONS', 'Commercial Operations', 'State and route permits commonly needed for commercial operations.', 'vehicle'),
    ('SPECIAL_PERMITS', 'Special Permits', 'Security or specialist approvals that may apply to some vehicles.', 'vehicle')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    description = VALUES(description),
    applies_to = VALUES(applies_to);

INSERT INTO nigeria_document_types (
    category_id,
    code,
    name,
    applies_to,
    issuing_level,
    issuing_authority,
    renewal_cycle_months,
    required_for_private,
    required_for_commercial,
    required_for_heavy_duty,
    required_for_interstate,
    requires_document_number,
    requires_issue_date,
    requires_expiry_date,
    default_notification_days,
    existing_storage_table,
    notes
)
SELECT c.id, t.code, t.name, 'vehicle', t.issuing_level, t.issuing_authority, t.renewal_cycle_months,
       t.required_for_private, t.required_for_commercial, t.required_for_heavy_duty, t.required_for_interstate,
       t.requires_document_number, t.requires_issue_date, t.requires_expiry_date, t.default_notification_days,
       t.existing_storage_table, t.notes
FROM nigeria_document_categories c
JOIN (
    SELECT 'REGISTRATION_OWNERSHIP' AS category_code, 'VEHICLE_LICENSE' AS code, 'Vehicle License' AS name,
           'state' AS issuing_level, 'State Internal Revenue / MVAA / Licensing Authority' AS issuing_authority,
           12 AS renewal_cycle_months, 1 AS required_for_private, 1 AS required_for_commercial,
           1 AS required_for_heavy_duty, 1 AS required_for_interstate, 1 AS requires_document_number,
           1 AS requires_issue_date, 1 AS requires_expiry_date, 30 AS default_notification_days,
           'vehicle_licenses' AS existing_storage_table,
           'Existing table already present in this project.' AS notes
    UNION ALL
    SELECT 'REGISTRATION_OWNERSHIP', 'PROOF_OF_OWNERSHIP', 'Proof Of Ownership Certificate',
           'federal', 'FRSC / NVIS / State Vehicle Registration Authority',
           0, 1, 1, 1, 1, 1, 1, 0, 0, NULL,
           'Usually not renewed yearly but important for compliance tracking.'
    UNION ALL
    SELECT 'SAFETY_COMPLIANCE', 'ROADWORTHINESS_CERTIFICATE', 'Roadworthiness Certificate',
           'state', 'VIO / State Vehicle Inspection Office',
           12, 1, 1, 1, 1, 0, 1, 1, 30, 'roadworthiness',
           'Existing table already present in this project.'
    UNION ALL
    SELECT 'INSURANCE', 'MOTOR_INSURANCE_THIRD_PARTY', 'Motor Insurance (Third Party)',
           'federal', 'NAICOM Licensed Insurer',
           12, 1, 1, 1, 1, 1, 1, 1, 30, 'vehicle_insurance',
           'Existing table already present in this project. Third-party cover is a common baseline requirement.'
    UNION ALL
    SELECT 'INSURANCE', 'MOTOR_INSURANCE_COMPREHENSIVE', 'Motor Insurance (Comprehensive)',
           'federal', 'NAICOM Licensed Insurer',
           12, 0, 1, 1, 1, 1, 1, 1, 30, NULL,
           'Useful when the fleet also tracks comprehensive policies separately.'
    UNION ALL
    SELECT 'COMMERCIAL_OPERATIONS', 'HACKNEY_PERMIT', 'Hackney Permit',
           'state', 'State Transport / Licensing Authority',
           12, 0, 1, 1, 1, 1, 1, 1, 30, NULL,
           'Common for commercial/passenger operations and typically state-specific.'
    UNION ALL
    SELECT 'COMMERCIAL_OPERATIONS', 'LOCAL_GOVT_TRANSPORT_PERMIT', 'Local Government Transport Permit / Levy',
           'local', 'Local Government Transport Authority',
           12, 0, 1, 1, 1, 1, 1, 1, 30, NULL,
           'Covers common local government transport papers that vary by LGA.'
    UNION ALL
    SELECT 'COMMERCIAL_OPERATIONS', 'INTERSTATE_CARRIER_PERMIT', 'Interstate Carrier / Haulage Permit',
           'mixed', 'State / Federal Transport Authority',
           12, 0, 1, 1, 1, 1, 1, 1, 30, NULL,
           'Useful for long-distance or haulage fleets where permits vary by route/use.'
    UNION ALL
    SELECT 'SAFETY_COMPLIANCE', 'SPEED_LIMITER_CERTIFICATE', 'Speed Limiter Certificate',
           'federal', 'FRSC / Accredited Speed Limiter Service Provider',
           12, 0, 1, 1, 1, 1, 1, 1, 30, NULL,
           'FRSC maintains a speed limiter portal and enforces it especially for commercial vehicles.'
    UNION ALL
    SELECT 'SPECIAL_PERMITS', 'TINTED_GLASS_PERMIT', 'Tinted Glass Permit',
           'federal', 'Nigeria Police Force',
           12, 0, 0, 0, 0, 1, 1, 1, 30, NULL,
           'Only applicable where the vehicle requires official tinted-glass approval.'
) t
    ON t.category_code = c.code
ON DUPLICATE KEY UPDATE
    category_id = VALUES(category_id),
    name = VALUES(name),
    applies_to = VALUES(applies_to),
    issuing_level = VALUES(issuing_level),
    issuing_authority = VALUES(issuing_authority),
    renewal_cycle_months = VALUES(renewal_cycle_months),
    required_for_private = VALUES(required_for_private),
    required_for_commercial = VALUES(required_for_commercial),
    required_for_heavy_duty = VALUES(required_for_heavy_duty),
    required_for_interstate = VALUES(required_for_interstate),
    requires_document_number = VALUES(requires_document_number),
    requires_issue_date = VALUES(requires_issue_date),
    requires_expiry_date = VALUES(requires_expiry_date),
    default_notification_days = VALUES(default_notification_days),
    existing_storage_table = VALUES(existing_storage_table),
    notes = VALUES(notes);
