-- 007_create_contracts_table.sql
CREATE TABLE IF NOT EXISTS `contracts` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `so_hop_dong`           VARCHAR(50) NOT NULL,
    `phong_id`              BIGINT UNSIGNED NOT NULL,
    `dai_dien_thue_id`      BIGINT UNSIGNED NOT NULL,
    `ngay_bat_dau`          DATE NOT NULL,
    `ngay_ket_thuc`         DATE DEFAULT NULL,
    `tien_thue_thang`       DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `tien_coc`              DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `ngay_thanh_toan`       TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT 'Ngày trong tháng (1-28)',
    `chu_ky_lap_hoa_don`    TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Số tháng giữa các hóa đơn',
    `phi_phat_cham_tra`     DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '% / tháng',
    `thoi_gian_bao_truoc`   TINYINT UNSIGNED NOT NULL DEFAULT 30 COMMENT 'Ngày',
    `trang_thai`            ENUM('cho_ky','dang_thue','het_han','ket_thuc_som','thanh_ly') NOT NULL DEFAULT 'cho_ky',
    `dieu_khoan`            TEXT DEFAULT NULL,
    `file_hop_dong`         VARCHAR(500) 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 `contracts_so_hop_dong_unique` (`so_hop_dong`),
    KEY `contracts_phong_id_index` (`phong_id`),
    KEY `contracts_dai_dien_thue_id_index` (`dai_dien_thue_id`),
    KEY `contracts_trang_thai_index` (`trang_thai`),
    KEY `contracts_deleted_at_index` (`deleted_at`),
    CONSTRAINT `fk_contracts_phong`
        FOREIGN KEY (`phong_id`) REFERENCES `rooms` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_contracts_dai_dien_thue`
        FOREIGN KEY (`dai_dien_thue_id`) REFERENCES `tenants` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;