-- 003_create_rooms_table.sql
CREATE TABLE IF NOT EXISTS `rooms` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nha_tro_id`    BIGINT UNSIGNED NOT NULL,
    `so_phong`      VARCHAR(20) NOT NULL,
    `tang`          TINYINT NOT NULL DEFAULT 1,
    `dien_tich_m2`  DECIMAL(8,2) DEFAULT NULL,
    `gia_thue_co_ban` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `so_nguoi_toi_da` TINYINT UNSIGNED NOT NULL DEFAULT 2,
    `trang_thai`    ENUM('trong','dang_thue','bao_tri') NOT NULL DEFAULT 'trong',
    `mo_ta`         TEXT 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 `rooms_nha_tro_so_phong_unique` (`nha_tro_id`, `so_phong`),
    KEY `rooms_trang_thai_index` (`trang_thai`),
    KEY `rooms_deleted_at_index` (`deleted_at`),
    CONSTRAINT `fk_rooms_nha_tro`
        FOREIGN KEY (`nha_tro_id`) REFERENCES `properties` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;