-- 005_create_electricity_tiers_table.sql
CREATE TABLE IF NOT EXISTS `electricity_tiers` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `bac`           TINYINT UNSIGNED NOT NULL COMMENT '1-6',
    `kwh_tu`        DECIMAL(8,2) NOT NULL,
    `kwh_den`       DECIMAL(8,2) DEFAULT NULL COMMENT 'NULL = không giới hạn',
    `don_gia_kwh`   DECIMAL(10,2) NOT NULL,
    `hieu_luc_tu`   DATE NOT NULL,
    `hieu_luc_den`  DATE DEFAULT NULL COMMENT 'NULL = còn hiệu lực',
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `electricity_tiers_bac_index` (`bac`),
    KEY `electricity_tiers_hieu_luc_index` (`hieu_luc_tu`, `hieu_luc_den`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dữ liệu bậc thang EVN 2024 (tham khảo)
INSERT INTO `electricity_tiers` (`bac`, `kwh_tu`, `kwh_den`, `don_gia_kwh`, `hieu_luc_tu`) VALUES
(1,   0,  50,   1806.00, '2024-01-01'),
(2,  51, 100,   1866.00, '2024-01-01'),
(3, 101, 200,   2167.00, '2024-01-01'),
(4, 201, 300,   2729.00, '2024-01-01'),
(5, 301, 400,   3050.00, '2024-01-01'),
(6, 401, NULL,  3151.00, '2024-01-01');