<?php

declare(strict_types=1);

return function (): string {

    return "
        CREATE TABLE states (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            state_code VARCHAR(30) NOT NULL UNIQUE,
            state_name VARCHAR(150) NOT NULL UNIQUE,
            status TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

        CREATE TABLE counties (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            state_id BIGINT UNSIGNED NOT NULL,
            county_code VARCHAR(30) NOT NULL UNIQUE,
            county_name VARCHAR(150) NOT NULL,
            status TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL,

            CONSTRAINT fk_counties_state
                FOREIGN KEY (state_id)
                REFERENCES states(id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

        CREATE TABLE payams (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            county_id BIGINT UNSIGNED NOT NULL,
            payam_code VARCHAR(30) NOT NULL UNIQUE,
            payam_name VARCHAR(150) NOT NULL,
            status TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL,

            CONSTRAINT fk_payams_county
                FOREIGN KEY (county_id)
                REFERENCES counties(id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
};