-- 015_create_maintenance_requests_table.sql
CREATE TABLE IF NOT EXISTS `maintenance_requests` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `phong_id`          BIGINT UNSIGNED NOT NULL,
    `nguoi_thue_id`     BIGINT UNSIGNED NOT NULL,
    `tieu_de`           VARCHAR(200) NOT NULL,
    `mo_ta`             TEXT NOT NULL,
    `anh_dinh_kem`      TEXT DEFAULT NULL COMMENT 'JSON array các đường dẫn ảnh',
    `muc_do_uu_tien`    ENUM('thap','trung_binh','cao','khan_cap') NOT NULL DEFAULT 'trung_binh',
    `trang_thai`        ENUM('cho_xu_ly','dang_xu_ly','hoan_thanh','tu_choi') NOT NULL DEFAULT 'cho_xu_ly',
    `ghi_chu_xu_ly`     TEXT DEFAULT NULL,
    `chi_phi_sua_chua`  DECIMAL(15,2) DEFAULT NULL,
    `hoan_thanh_luc`    TIMESTAMP NULL 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`),
    KEY `mr_phong_id_index` (`phong_id`),
    KEY `mr_nguoi_thue_id_index` (`nguoi_thue_id`),
    KEY `mr_trang_thai_index` (`trang_thai`),
    KEY `mr_deleted_at_index` (`deleted_at`),
    CONSTRAINT `fk_maint_phong`
        FOREIGN KEY (`phong_id`) REFERENCES `rooms` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_maint_nguoi_thue`
        FOREIGN KEY (`nguoi_thue_id`) REFERENCES `tenants` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;