-- 012_create_invoices_table.sql
CREATE TABLE IF NOT EXISTS `invoices` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `hop_dong_id`           BIGINT UNSIGNED NOT NULL,
    `so_hoa_don`            VARCHAR(50) NOT NULL,
    `ky_thanh_toan`         VARCHAR(7) NOT NULL COMMENT 'YYYY-MM',
    `ngay_phat_hanh`        DATE NOT NULL,
    `han_thanh_toan`        DATE NOT NULL,
    `tong_tien`             DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `da_thanh_toan`         DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `tien_phat_cham_tra`    DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `trang_thai`            ENUM('nhap','da_gui','thanh_toan_mot_phan','da_thanh_toan','qua_han','da_huy') NOT NULL DEFAULT 'nhap',
    `ghi_chu`               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 `invoices_so_hoa_don_unique` (`so_hoa_don`),
    KEY `invoices_hop_dong_id_index` (`hop_dong_id`),
    KEY `invoices_ky_thanh_toan_index` (`ky_thanh_toan`),
    KEY `invoices_trang_thai_index` (`trang_thai`),
    KEY `invoices_deleted_at_index` (`deleted_at`),
    CONSTRAINT `fk_invoices_hop_dong`
        FOREIGN KEY (`hop_dong_id`) REFERENCES `contracts` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;