-- ============================================================
-- Migration 57: Unified Brand Pricing System (PDO-Compatible)
-- ============================================================

-- Add new columns if they do not exist
ALTER TABLE `brand_ampere_rates`
    ADD COLUMN IF NOT EXISTS `rate_type` ENUM('retail','wholesale','used_battery','ampere_credit') NOT NULL DEFAULT 'ampere_credit' AFTER `brand_id`,
    ADD COLUMN IF NOT EXISTS `amperage_id` INT(10) UNSIGNED NULL AFTER `rate_type`,
    ADD COLUMN IF NOT EXISTS `product_id` INT(10) UNSIGNED NULL AFTER `amperage_id`,
    ADD COLUMN IF NOT EXISTS `effective_to` DATETIME NULL AFTER `effective_from`,
    ADD COLUMN IF NOT EXISTS `is_current` TINYINT(1) NOT NULL DEFAULT 1 AFTER `effective_to`,
    ADD COLUMN IF NOT EXISTS `version` INT(10) UNSIGNED NOT NULL DEFAULT 1 AFTER `is_current`,
    ADD COLUMN IF NOT EXISTS `notes` VARCHAR(500) NULL AFTER `version`,
    ADD COLUMN IF NOT EXISTS `created_by` INT(10) UNSIGNED NULL AFTER `notes`;

-- Remove legacy unique index and virtual column if present
ALTER TABLE `brand_ampere_rates`
    DROP INDEX IF EXISTS `uq_single_active_rate`,
    DROP COLUMN IF EXISTS `_active_brand_uk`;

-- Ensure uk_bar_brand_effective includes rate_type
ALTER TABLE `brand_ampere_rates`
    DROP INDEX IF EXISTS `uk_bar_brand_effective`;
ALTER TABLE `brand_ampere_rates`
    ADD UNIQUE KEY `uk_bar_brand_effective` (`brand_id`, `rate_type`, `effective_from`);

-- Ensure lookup indexes exist (drop then create to be idempotent)
DROP INDEX IF EXISTS `idx_bar_lookup` ON `brand_ampere_rates`;
CREATE INDEX `idx_bar_lookup` ON `brand_ampere_rates` (`rate_type`, `brand_id`, `amperage_id`, `product_id`, `is_current`);

DROP INDEX IF EXISTS `idx_bar_history` ON `brand_ampere_rates`;
CREATE INDEX `idx_bar_history` ON `brand_ampere_rates` (`brand_id`, `rate_type`, `effective_from`);

DROP INDEX IF EXISTS `idx_bar_product` ON `brand_ampere_rates`;
CREATE INDEX `idx_bar_product` ON `brand_ampere_rates` (`product_id`, `rate_type`, `is_current`);

-- NOTE: used_battery_rates table has no product_id column (global rates only).
-- Per-product used_battery rates are entered fresh via the new BrandRateService UI.



-- Recreate views (drop then create)
DROP VIEW IF EXISTS `v_brand_ampere_rate_history`;
CREATE VIEW `v_brand_ampere_rate_history` AS
SELECT
    bar.id,
    bar.brand_id,
    b.name AS brand_name,
    bar.rate_type,
    bar.product_id,
    p.name AS product_name,
    bar.amperage_id,
    a.amperage AS amperage_value,
    bar.rate_per_ampere,
    bar.effective_from,
    bar.effective_to,
    bar.is_current,
    bar.version,
    bar.notes,
    bar.created_at,
    bar.created_by,
    CONCAT(au.first_name, ' ', au.last_name) AS creator_name
FROM `brand_ampere_rates` bar
LEFT JOIN `brands` b ON b.id = bar.brand_id
LEFT JOIN `products` p ON p.id = bar.product_id
LEFT JOIN `amperages` a ON a.id = bar.amperage_id
LEFT JOIN `admin_users` au ON au.id = bar.created_by
ORDER BY bar.brand_id, bar.rate_type, bar.effective_from DESC;

DROP VIEW IF EXISTS `v_partner_ampere_summary`;
CREATE VIEW `v_partner_ampere_summary` AS
SELECT
    paa.id AS account_id,
    paa.partner_id,
    c.first_name AS partner_first_name,
    c.last_name  AS partner_last_name,
    c.mobile     AS partner_mobile,
    paa.brand_id,
    b.name       AS brand_name,
    paa.balance_ampere,
    paa.credit_limit_ampere,
    paa.credit_limit_ampere - paa.balance_ampere AS credit_remaining,
    paa.is_blocked,
    paa.blocked_reason,
    bar.rate_per_ampere AS current_rate,
    bar.effective_from AS rate_effective_from,
    CAST(paa.balance_ampere * COALESCE(bar.rate_per_ampere,0) AS UNSIGNED) AS estimated_rial_value,
    paa.created_at,
    paa.updated_at
FROM `partner_ampere_accounts` paa
JOIN `customers` c ON c.id = paa.partner_id
JOIN `brands` b ON b.id = paa.brand_id
LEFT JOIN `brand_ampere_rates` bar ON bar.brand_id = paa.brand_id
    AND bar.rate_type = 'ampere_credit'
    AND bar.is_current = 1
    AND bar.amperage_id IS NULL
    AND bar.product_id IS NULL;

-- NOTE: Guard trigger trg_bar_duplicate_guard is applied separately via
-- database/triggers/57_trg_bar_duplicate_guard.sql (cannot run DELIMITER in PDO)
-- End of migration 57
