-- 008_create_contract_tenants_table.sql
CREATE TABLE IF NOT EXISTS `contract_tenants` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `hop_dong_id`       BIGINT UNSIGNED NOT NULL,
    `nguoi_thue_id`     BIGINT UNSIGNED NOT NULL,
    `la_dai_dien`       TINYINT(1) NOT NULL DEFAULT 0,
    `ngay_vao`          DATE NOT NULL,
    `ngay_ra`           DATE DEFAULT NULL COMMENT 'NULL = đang ở',
    `created_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ct_hop_dong_id_index` (`hop_dong_id`),
    KEY `ct_nguoi_thue_id_index` (`nguoi_thue_id`),
    CONSTRAINT `fk_ct_hop_dong`
        FOREIGN KEY (`hop_dong_id`) REFERENCES `contracts` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_ct_nguoi_thue`
        FOREIGN KEY (`nguoi_thue_id`) REFERENCES `tenants` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;