-- 011_create_meter_readings_table.sql
CREATE TABLE IF NOT EXISTS `meter_readings` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `phong_id`      BIGINT UNSIGNED NOT NULL,
    `dich_vu_id`    BIGINT UNSIGNED NOT NULL,
    `ngay_ghi`      DATE NOT NULL,
    `chi_so_cu`     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `chi_so_moi`    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `tieu_thu`      DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'chi_so_moi - chi_so_cu',
    `nguoi_ghi_id`  BIGINT UNSIGNED NOT NULL,
    `ghi_chu`       VARCHAR(500) DEFAULT NULL,
    `anh_dong_ho`   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 `mr_phong_id_index` (`phong_id`),
    KEY `mr_dich_vu_id_index` (`dich_vu_id`),
    KEY `mr_nguoi_ghi_id_index` (`nguoi_ghi_id`),
    KEY `mr_ngay_ghi_index` (`ngay_ghi`),
    CONSTRAINT `fk_mr_phong`
        FOREIGN KEY (`phong_id`) REFERENCES `rooms` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_mr_dich_vu`
        FOREIGN KEY (`dich_vu_id`) REFERENCES `services` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_mr_nguoi_ghi`
        FOREIGN KEY (`nguoi_ghi_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;