-- 006_create_tenants_table.sql
CREATE TABLE IF NOT EXISTS `tenants` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`           BIGINT UNSIGNED DEFAULT NULL,
    `ho_ten`            VARCHAR(150) NOT NULL,
    `so_dien_thoai`     VARCHAR(20) DEFAULT NULL,
    `email`             VARCHAR(191) DEFAULT NULL,
    `so_cmnd_cccd`      VARCHAR(20) NOT NULL,
    `ngay_cap_cmnd`     DATE DEFAULT NULL,
    `noi_cap_cmnd`      VARCHAR(200) DEFAULT NULL,
    `ngay_sinh`         DATE DEFAULT NULL,
    `gioi_tinh`         ENUM('nam','nu','khac') DEFAULT NULL,
    `dia_chi_thuong_tru` TEXT DEFAULT NULL,
    `que_quan`          VARCHAR(200) DEFAULT NULL,
    `nghe_nghiep`       VARCHAR(100) DEFAULT NULL,
    `noi_cong_tac`      VARCHAR(200) DEFAULT NULL,
    `deleted_at`        TIMESTAMP NULL DEFAULT NULL,
    `created_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `tenants_so_cmnd_cccd_unique` (`so_cmnd_cccd`),
    KEY `tenants_user_id_index` (`user_id`),
    KEY `tenants_deleted_at_index` (`deleted_at`),
    CONSTRAINT `fk_tenants_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;