-- Zamfara State Ministry of Commerce, Industry & Tourism
-- Certificate Registration & Issuance Management System
-- Requires MySQL 8.0+ or a recent MariaDB release.

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(32) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_role_active (role, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS businesses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    certificate_number VARCHAR(40) NULL,
    form_number VARCHAR(80) NOT NULL,
    business_name VARCHAR(190) NOT NULL,
    previous_business_name VARCHAR(190) NULL,
    business_type VARCHAR(80) NOT NULL,
    business_category VARCHAR(120) NULL,
    owner_name VARCHAR(190) NULL,
    phone VARCHAR(40) NOT NULL,
    email VARCHAR(190) NULL,
    business_address VARCHAR(255) NOT NULL,
    lga VARCHAR(80) NOT NULL,
    ward VARCHAR(100) NULL,
    nature_of_business VARCHAR(255) NULL,
    registration_date DATE NULL,
    expiry_date DATE NOT NULL,
    cac_number VARCHAR(100) NULL,
    tin VARCHAR(100) NULL,
    supporting_document_path VARCHAR(255) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'Registered',
    rejection_reason TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    verified_by BIGINT UNSIGNED NULL,
    verified_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_businesses_certificate_number (certificate_number),
    UNIQUE KEY uq_businesses_form_number (form_number),
    KEY idx_businesses_status_created (status, created_at),
    KEY idx_businesses_lga (lga),
    KEY idx_businesses_expiry (expiry_date),
    KEY idx_businesses_name (business_name),
    CONSTRAINT fk_businesses_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_businesses_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_businesses_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS certificate_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(120) NOT NULL,
    settings_json JSON NOT NULL,
    logo_path VARCHAR(255) NULL,
    background_path VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_templates_active (is_active),
    CONSTRAINT fk_templates_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_templates_updated_by 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 certificates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NULL,
    template_name_snapshot VARCHAR(120) NULL,
    template_snapshot_json JSON NULL,
    verification_code CHAR(64) NOT NULL,
    issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expiry_date DATE NOT NULL,
    printed_by BIGINT UNSIGNED NOT NULL,
    revoked_at DATETIME NULL,
    revocation_reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_certificates_verification_code (verification_code),
    KEY idx_certificates_business_issued (business_id, issued_at),
    KEY idx_certificates_expiry (expiry_date),
    CONSTRAINT fk_certificates_business FOREIGN KEY (business_id) REFERENCES businesses(id),
    CONSTRAINT fk_certificates_template FOREIGN KEY (template_id) REFERENCES certificate_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_certificates_printed_by FOREIGN KEY (printed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    actor_user_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    summary VARCHAR(255) NULL,
    changes_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_created (created_at),
    KEY idx_audit_entity (entity_type, entity_id),
    KEY idx_audit_actor (actor_user_id),
    CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email_key CHAR(64) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_login_attempts_window (email_key, ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_settings (
    setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value TEXT NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_settings_updated_by FOREIGN KEY (updated_by) 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
    ('ministry_name', 'Zamfara State Ministry of Commerce, Industry & Tourism'),
    ('certificate_prefix', 'ZMCIT'),
    ('default_validity_months', '12'),
    ('session_timeout_minutes', '20')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);