<?php

declare(strict_types=1);

return function (): string {

    return "
        CREATE TABLE employees (

            id BIGINT UNSIGNED
                NOT NULL
                AUTO_INCREMENT
                PRIMARY KEY,

            employee_id VARCHAR(30)
                NOT NULL
                UNIQUE,

            first_name VARCHAR(100)
                NOT NULL,

            middle_name VARCHAR(100)
                NULL,

            last_name VARCHAR(100)
                NOT NULL,

            gender ENUM(
                'MALE',
                'FEMALE',
                'OTHER'
            )
                NOT NULL,

            date_of_birth DATE
                NULL,

            nationality_number VARCHAR(100)
                NULL,

            rank_id BIGINT UNSIGNED
                NULL,

            department_id BIGINT UNSIGNED
                NULL,

            position_id BIGINT UNSIGNED
                NULL,

            state_id BIGINT UNSIGNED
                NULL,

            county_id BIGINT UNSIGNED
                NULL,

            payam_id BIGINT UNSIGNED
                NULL,

            hire_date DATE
                NULL,

            employment_status ENUM(
                'ACTIVE',
                'INACTIVE',
                'RETIRED',
                'SUSPENDED',
                'DECEASED'
            )
                NOT NULL
                DEFAULT 'ACTIVE',

            photo VARCHAR(255)
                NULL,

            biometric_status ENUM(
                'NOT_REGISTERED',
                'PARTIAL',
                'REGISTERED',
                'VERIFIED'
            )
                NOT NULL
                DEFAULT 'NOT_REGISTERED',

            created_by BIGINT UNSIGNED
                NULL,

            updated_by BIGINT UNSIGNED
                NULL,

            status TINYINT(1)
                NOT NULL
                DEFAULT 1,

            created_at TIMESTAMP
                NOT NULL,

            updated_at TIMESTAMP
                NOT NULL,

            INDEX idx_employee_rank (
                rank_id
            ),

            INDEX idx_employee_department (
                department_id
            ),

            INDEX idx_employee_position (
                position_id
            ),

            INDEX idx_employee_state (
                state_id
            ),

            INDEX idx_employee_county (
                county_id
            ),

            INDEX idx_employee_payam (
                payam_id
            ),

            INDEX idx_employee_status (
                employment_status
            ),

            INDEX idx_biometric_status (
                biometric_status
            ),

            CONSTRAINT fk_employee_rank
                FOREIGN KEY (rank_id)
                REFERENCES ranks(id)
                ON UPDATE CASCADE
                ON DELETE SET NULL,

            CONSTRAINT fk_employee_department
                FOREIGN KEY (department_id)
                REFERENCES departments(id)
                ON UPDATE CASCADE
                ON DELETE SET NULL,

            CONSTRAINT fk_employee_position
                FOREIGN KEY (position_id)
                REFERENCES positions(id)
                ON UPDATE CASCADE
                ON DELETE SET NULL,

            CONSTRAINT fk_employee_state
                FOREIGN KEY (state_id)
                REFERENCES states(id)
                ON UPDATE CASCADE
                ON DELETE SET NULL,

            CONSTRAINT fk_employee_county
                FOREIGN KEY (county_id)
                REFERENCES counties(id)
                ON UPDATE CASCADE
                ON DELETE SET NULL,

            CONSTRAINT fk_employee_payam
                FOREIGN KEY (payam_id)
                REFERENCES payams(id)
                ON UPDATE CASCADE
                ON DELETE SET NULL

        ) ENGINE=InnoDB
          DEFAULT CHARSET=utf8mb4
          COLLATE=utf8mb4_unicode_ci;
    ";
};