CREATE TABLE IF NOT EXISTS nigeria_states (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(10) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_nigeria_states_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS nigeria_lgas (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    state_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_nigeria_lgas_state_name (state_id, name),
    KEY idx_nigeria_lgas_state (state_id),
    CONSTRAINT fk_nigeria_lgas_state
        FOREIGN KEY (state_id) REFERENCES nigeria_states(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS compliance_issuing_authorities (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(180) NOT NULL,
    level ENUM('federal','state','local','mixed','private') NOT NULL DEFAULT 'mixed',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_compliance_issuing_authorities_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS compliance_issuing_offices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    authority_id BIGINT UNSIGNED NULL,
    state_id BIGINT UNSIGNED NULL,
    lga_id BIGINT UNSIGNED NULL,
    name VARCHAR(180) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_compliance_issuing_offices_name (name),
    KEY idx_compliance_issuing_offices_authority (authority_id),
    KEY idx_compliance_issuing_offices_state (state_id),
    KEY idx_compliance_issuing_offices_lga (lga_id),
    CONSTRAINT fk_compliance_issuing_offices_authority
        FOREIGN KEY (authority_id) REFERENCES compliance_issuing_authorities(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_compliance_issuing_offices_state
        FOREIGN KEY (state_id) REFERENCES nigeria_states(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_compliance_issuing_offices_lga
        FOREIGN KEY (lga_id) REFERENCES nigeria_lgas(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS compliance_vendors_agents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(180) NOT NULL,
    vendor_type ENUM('vendor','agent','broker','insurer','other') NOT NULL DEFAULT 'vendor',
    phone VARCHAR(30) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_compliance_vendors_agents_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO nigeria_states (name, code) VALUES
('Abia','AB'),('Adamawa','AD'),('Akwa Ibom','AK'),('Anambra','AN'),('Bauchi','BA'),
('Bayelsa','BY'),('Benue','BE'),('Borno','BO'),('Cross River','CR'),('Delta','DE'),
('Ebonyi','EB'),('Edo','ED'),('Ekiti','EK'),('Enugu','EN'),('FCT','FC'),
('Gombe','GO'),('Imo','IM'),('Jigawa','JI'),('Kaduna','KD'),('Kano','KN'),
('Katsina','KT'),('Kebbi','KE'),('Kogi','KO'),('Kwara','KW'),('Lagos','LA'),
('Nasarawa','NA'),('Niger','NI'),('Ogun','OG'),('Ondo','ON'),('Osun','OS'),
('Oyo','OY'),('Plateau','PL'),('Rivers','RI'),('Sokoto','SO'),('Taraba','TA'),
('Yobe','YO'),('Zamfara','ZA')
ON DUPLICATE KEY UPDATE code = VALUES(code);

INSERT INTO compliance_issuing_authorities (name, level) VALUES
('FRSC','federal'),
('NAICOM Licensed Insurer','federal'),
('Nigeria Police Force','federal'),
('State Internal Revenue / Licensing Authority','state'),
('Vehicle Inspection Office (VIO)','state'),
('State Ministry Of Transport','state'),
('Local Government Transport Authority','local')
ON DUPLICATE KEY UPDATE level = VALUES(level);