-- 016_create_announcements_table.sql
CREATE TABLE IF NOT EXISTS `announcements` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nha_tro_id`            BIGINT UNSIGNED DEFAULT NULL COMMENT 'NULL = tất cả nhà trọ',
    `nguoi_tao_id`          BIGINT UNSIGNED NOT NULL,
    `tieu_de`               VARCHAR(200) NOT NULL,
    `noi_dung`              TEXT NOT NULL,
    `loai`                  ENUM('thong_bao_chung','khan_cap','hoa_don','sua_chua') NOT NULL DEFAULT 'thong_bao_chung',
    `ngay_dang`             DATE NOT NULL,
    `ngay_het_hieu_luc`     DATE 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 `ann_nha_tro_id_index` (`nha_tro_id`),
    KEY `ann_nguoi_tao_id_index` (`nguoi_tao_id`),
    KEY `ann_loai_index` (`loai`),
    KEY `ann_deleted_at_index` (`deleted_at`),
    CONSTRAINT `fk_ann_nha_tro`
        FOREIGN KEY (`nha_tro_id`) REFERENCES `properties` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_ann_nguoi_tao`
        FOREIGN KEY (`nguoi_tao_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;