-- 009_create_handover_records_table.sql
CREATE TABLE IF NOT EXISTS `handover_records` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `hop_dong_id`           BIGINT UNSIGNED NOT NULL,
    `loai`                  ENUM('nhan_phong','tra_phong') NOT NULL,
    `ngay_ban_giao`         DATE NOT NULL,
    `chi_so_dien_ban_giao`  DECIMAL(10,2) DEFAULT NULL,
    `chi_so_nuoc_ban_giao`  DECIMAL(10,2) DEFAULT NULL,
    `tinh_trang_tai_san`    TEXT DEFAULT NULL COMMENT 'Mô tả tình trạng tài sản',
    `ghi_chu`               TEXT DEFAULT NULL,
    `file_bien_ban`         VARCHAR(500) 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 `handover_records_hop_dong_id_index` (`hop_dong_id`),
    CONSTRAINT `fk_handover_hop_dong`
        FOREIGN KEY (`hop_dong_id`) REFERENCES `contracts` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
