-- ============================================================
-- Migration 56b: Create brand_ampere_rates
-- ============================================================
-- Recovered table definition. The original CREATE statement for this table was
-- never committed to database/migrations/, which made the migration chain
-- unrunnable on an empty database: migration 57 ALTERs this table and failed
-- with SQLSTATE 42S02 (1146). See BUG-19 / blocker B7 in PROGRESS.md.
--
-- The definition below is the live production shape MINUS the three indexes
-- that migration 57 creates unconditionally (idx_bar_lookup, idx_bar_history,
-- idx_bar_product). Every column added by migration 57 uses
-- ADD COLUMN IF NOT EXISTS, so migration 57 remains a safe no‑op for columns
-- and still supplies the three indexes.
--
-- Numbered 56b so that SORT_STRING places it after 56_create_used_battery_rates
-- and before 57_unified_brand_rates_migration.
CREATE TABLE IF NOT EXISTS `brand_ampere_rates` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT(10) UNSIGNED NOT NULL COMMENT 'FK to brands.id',
    `rate_type` ENUM('retail','wholesale','used_battery','ampere_credit') NOT NULL DEFAULT 'ampere_credit' COMMENT 'Type of rate: retail, wholesale, used_battery, or ampere_credit',
    `amperage_id` INT(10) UNSIGNED DEFAULT NULL,
    `product_id` INT(10) UNSIGNED DEFAULT NULL,
    `rate_per_ampere` DECIMAL(14,4) NOT NULL COMMENT 'Rial amount per 1 ampere',
    `effective_from` DATETIME NOT NULL COMMENT 'When this rate becomes active',
    `effective_to` DATETIME DEFAULT NULL COMMENT 'NULL = currently active; set by trigger when new rate inserted',
    `is_current` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = active rate, 0 = archived',
    `version` INT(10) UNSIGNED NOT NULL DEFAULT 1,
    `notes` VARCHAR(500) DEFAULT NULL COMMENT 'Admin note for this rate change',
    `created_by` INT(10) UNSIGNED DEFAULT NULL COMMENT 'FK to admin_users.id',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    `rate_unit` ENUM('rial') NOT NULL DEFAULT 'rial',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_bar_brand_effective` (`brand_id`,`rate_type`,`effective_from`),
    KEY `idx_bar_brand_current` (`brand_id`,`is_current`),
    KEY `fk_bar_created_by` (`created_by`),
    KEY `idx_bar_current_type` (`brand_id`,`rate_type`,`is_current`,`effective_from`),
    CONSTRAINT `fk_bar_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_bar_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_bar_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `chk_bar_rate_positive` CHECK (`rate_per_ampere` > 0),
    CONSTRAINT `chk_bar_current_consistency` CHECK (`is_current` = 1 AND `effective_to` IS NULL OR `is_current` = 0 AND `effective_to` IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Brand-level ampere rate schedule (Spec 5, 15)';
