-- =====================================================
-- NIGERIA IMMIGRATION SERVICE - MIGRANT e-REGISTRATION
-- DATABASE: migrant_ereg
-- VERSION: 1.0
-- =====================================================

CREATE DATABASE IF NOT EXISTS migrant_ereg
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE migrant_ereg;

-- =====================================================
-- 1. USERS TABLE (RBAC IMPLEMENTATION)
-- =====================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    role ENUM('SuperAdmin', 'Admin', 'ImmigrationOfficer', 'Reviewer') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_login DATETIME NULL,
    last_login_ip VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_role (role),
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- =====================================================
-- 2. BIOGRAPHICAL INFORMATION
-- =====================================================
CREATE TABLE biographical_information (
    person_id VARCHAR(100) NOT NULL PRIMARY KEY,
    document_type VARCHAR(50) NOT NULL,
    country_of_issue VARCHAR(100) NOT NULL,
    document_no VARCHAR(50) NOT NULL UNIQUE,
    surname VARCHAR(100) NOT NULL,
    given_name VARCHAR(100) NOT NULL,
    nationality VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    sex ENUM('Male','Female','Other') NOT NULL,
    expiry_date DATE NULL,
    national_id_no VARCHAR(50),
    date_of_issue DATE,
    issuing_authority VARCHAR(150),
    occupation VARCHAR(150),
    profession VARCHAR(150),
    highest_qualification VARCHAR(150),
    email_address VARCHAR(150),
    blood_type ENUM('A+','A-','B+','B-','AB+','AB-','O+','O-'),
    marital_status ENUM('Single','Married','Divorced','Widowed','Separated'),
    place_of_birth VARCHAR(150),
    country_of_birth VARCHAR(100),
    state_province VARCHAR(100),
    town VARCHAR(100),
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_document_no (document_no),
    INDEX idx_nationality (nationality),
    INDEX idx_surname (surname)
) ENGINE=InnoDB;

-- =====================================================
-- 3. APPLICATION STATUS (WORKFLOW ENGINE)
-- =====================================================
CREATE TABLE application_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    status ENUM('Draft', 'Submitted', 'Under Review', 'Approved', 'Rejected') NOT NULL DEFAULT 'Draft',
    remarks TEXT,
    reviewed_by INT,
    reviewed_at DATETIME,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_status (status),
    INDEX idx_person_status (person_id, status)
) ENGINE=InnoDB;

-- =====================================================
-- 4. FOREIGN ADDRESS
-- =====================================================
CREATE TABLE foreign_address (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    country VARCHAR(100),
    state_province VARCHAR(100),
    town VARCHAR(100),
    postal_code VARCHAR(20),
    abroad_address TEXT NOT NULL,
    phone_number VARCHAR(20),
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_person_id (person_id)
) ENGINE=InnoDB;

-- =====================================================
-- 5. NIGERIA RESIDENCE
-- =====================================================
CREATE TABLE nigeria_residence (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    country VARCHAR(100),
    state VARCHAR(100),
    local_government VARCHAR(100),
    town VARCHAR(100),
    postal_code VARCHAR(20),
    address TEXT NOT NULL,
    phone_number VARCHAR(20),
    reference_person_name VARCHAR(150),
    reference_person_phone VARCHAR(20),
    is_current BOOLEAN DEFAULT TRUE,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_person_id (person_id),
    INDEX idx_is_current (is_current)
) ENGINE=InnoDB;

-- =====================================================
-- 6. EMPLOYMENT HISTORY
-- =====================================================
CREATE TABLE employment_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    employer_name VARCHAR(200) NOT NULL,
    employer_state VARCHAR(100),
    employer_address TEXT NOT NULL,
    employer_profession_industry VARCHAR(150),
    employer_email VARCHAR(150),
    employer_phone_number VARCHAR(20),
    job_title VARCHAR(150) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_person_id (person_id),
    INDEX idx_employer_name (employer_name)
) ENGINE=InnoDB;

-- =====================================================
-- 7. IMMIGRANT STATUS
-- =====================================================
CREATE TABLE immigrant_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    employer_id INT NULL,
    status_category ENUM(
        'Visitor','Temporary Resident','Student',
        'Worker','Permanent Resident','Refugee',
        'Asylum Seeker','Diplomat','Undocumented'
    ) NOT NULL,
    visa_type VARCHAR(100),
    permit_number VARCHAR(100) UNIQUE,
    date_granted DATE NOT NULL,
    expiry_date DATE NULL,
    is_active BOOLEAN DEFAULT TRUE,
    status_condition TEXT,
    sponsor_type ENUM('Employer','Institution','Self','Government') NULL,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_person_id (person_id),
    INDEX idx_permit_number (permit_number),
    INDEX idx_status_category (status_category)
) ENGINE=InnoDB;

-- =====================================================
-- 8. TRAVEL HISTORY
-- =====================================================
CREATE TABLE travel_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    traveled_on_own_free_will BOOLEAN NOT NULL,
    purpose_of_travel ENUM(
        'Holiday','Work','Study','Business',
        'Medical','Transit','Relocation','Other'
    ) NOT NULL,
    departure_date DATE NOT NULL,
    arrival_date DATE NOT NULL,
    departure_country VARCHAR(100) NOT NULL,
    departure_state_province VARCHAR(100),
    departure_town VARCHAR(100),
    transportation_type ENUM(
        'Flight','Car','Bus','Train','Ship','Other'
    ) NOT NULL,
    travel_document_type ENUM(
        'Passport','Refugee Passport',
        'Emergency Travel Certificate',
        'National ID','Other'
    ) NOT NULL,
    port_of_entry VARCHAR(150) NOT NULL,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_person_id (person_id),
    INDEX idx_departure_date (departure_date)
) ENGINE=InnoDB;

-- =====================================================
-- 9. MEDICAL HISTORY
-- =====================================================
CREATE TABLE medical_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    illness_name VARCHAR(150) NOT NULL,
    illness_date DATE NULL,
    is_current BOOLEAN DEFAULT TRUE,
    vaccine_name VARCHAR(150) NOT NULL,
    vaccine_date DATE NULL,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_person_id (person_id)
) ENGINE=InnoDB;

-- =====================================================
-- 10. BIOMETRICS MASTER TABLE
-- =====================================================
CREATE TABLE biometrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    capture_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    capture_device_id VARCHAR(100),
    capture_location VARCHAR(150),
    created_by INT,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_person_id (person_id),
    INDEX idx_capture_date (capture_date)
) ENGINE=InnoDB;

-- =====================================================
-- 11. FINGERPRINTS (ENCRYPTED STORAGE)
-- =====================================================
CREATE TABLE fingerprints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    biometrics_id INT NOT NULL,
    finger_position ENUM(
        'Right Thumb','Right Index','Right Middle','Right Ring','Right Little',
        'Left Thumb','Left Index','Left Middle','Left Ring','Left Little'
    ) NOT NULL,
    fingerprint_template LONGBLOB NOT NULL,
    template_format VARCHAR(50),
    quality_score INT,
    capture_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (biometrics_id) REFERENCES biometrics(id) ON DELETE CASCADE,
    INDEX idx_biometrics_id (biometrics_id)
) ENGINE=InnoDB;

-- =====================================================
-- 12. PHOTOS
-- =====================================================
CREATE TABLE photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    biometrics_id INT NOT NULL,
    photo_data LONGBLOB NOT NULL,
    photo_format VARCHAR(20),
    photo_quality_score INT,
    capture_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (biometrics_id) REFERENCES biometrics(id) ON DELETE CASCADE,
    INDEX idx_biometrics_id (biometrics_id)
) ENGINE=InnoDB;

-- =====================================================
-- 13. UPLOADED DOCUMENTS
-- =====================================================
CREATE TABLE uploaded_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(100) NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    uploaded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (person_id) REFERENCES biographical_information(person_id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id),
    INDEX idx_person_id (person_id),
    INDEX idx_document_type (document_type)
) ENGINE=InnoDB;

-- =====================================================
-- 14. AUDIT LOGS
-- =====================================================
CREATE TABLE audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),
    action ENUM('LOGIN', 'LOGOUT', 'CREATE', 'UPDATE', 'DELETE', 'APPROVE', 'REJECT', 'VIEW') NOT NULL,
    table_name VARCHAR(100),
    record_id VARCHAR(100),
    old_data JSON,
    new_data JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at),
    INDEX idx_ip_address (ip_address)
) ENGINE=InnoDB;

-- =====================================================
-- 15. LOGIN ATTEMPTS (RATE LIMITING)
-- =====================================================
CREATE TABLE login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    ip_address VARCHAR(45) NOT NULL,
    attempt_time DATETIME NOT NULL,
    success BOOLEAN DEFAULT FALSE,
    INDEX idx_ip_time (ip_address, attempt_time),
    INDEX idx_username_time (username, attempt_time)
) ENGINE=InnoDB;

-- =====================================================
-- 16. SESSIONS TABLE (CUSTOM SESSION HANDLING)
-- =====================================================
CREATE TABLE sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT,
    last_activity INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB;

-- =====================================================
-- INSERT DEFAULT USERS (PASSWORD: Admin@123)
-- =====================================================
INSERT INTO users (username, email, password_hash, full_name, role) VALUES
('superadmin', 'superadmin@nis.gov.ng', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Administrator', 'SuperAdmin'),
('admin', 'admin@nis.gov.ng', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'Admin'),
('officer', 'officer@nis.gov.ng', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Immigration Officer', 'ImmigrationOfficer'),
('reviewer', 'reviewer@nis.gov.ng', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Application Reviewer', 'Reviewer');

-- =====================================================
-- INSERT SAMPLE DATA FOR TESTING
-- =====================================================
INSERT INTO biographical_information (
    person_id, document_type, country_of_issue, document_no, 
    surname, given_name, nationality, date_of_birth, sex,
    email_address, created_by
) VALUES 
('NIS-MIG-2024-000001', 'PASSPORT', 'NIGERIA', 'A00000001', 'OKONKWO', 'CHUKWUMA', 'NIGERIAN', '1985-06-15', 'Male', 'chukwuma.okonkwo@email.com', 1),
('NIS-MIG-2024-000002', 'PASSPORT', 'GHANA', 'G00000002', 'MENSAH', 'AKUA', 'GHANAIAN', '1990-03-22', 'Female', 'akua.mensah@email.com', 1),
('NIS-MIG-2024-000003', 'PASSPORT', 'CAMEROON', 'C00000003', 'NDONG', 'JEAN', 'CAMEROONIAN', '1988-11-10', 'Male', 'jean.ndong@email.com', 1);

INSERT INTO application_status (person_id, status, created_by) VALUES
('NIS-MIG-2024-000001', 'Approved', 1),
('NIS-MIG-2024-000002', 'Submitted', 1),
('NIS-MIG-2024-000003', 'Draft', 1);