-- 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);