-- =============================================================================
-- Migration : 2026_06_21_eis_site_product_item_link.sql
-- Purpose   : Stamp a direct FK from items → mra_eis_site_products so that
--             every physical item knows exactly which EIS stock batch it belongs
--             to. Eliminates runtime ambiguity when the same product_code exists
--             at multiple terminals / price tiers in mra_eis_site_products.
--             Also records which EIS batch was deducted in sold_items for audit.
-- Safe to re-run: all ALTER TABLE statements use IF NOT EXISTS / IF EXISTS.
-- =============================================================================

-- ─── Step 1 ─ Add eis_site_product_id to items ──────────────────────────────
-- Stamped once at stock-batch import time via the admin mapping UI.
-- NULL = unmapped (sale will be blocked until admin assigns the EIS batch).

ALTER TABLE `items`
    ADD COLUMN IF NOT EXISTS `eis_site_product_id` INT(11) NULL DEFAULT NULL
        COMMENT 'FK -> mra_eis_site_products.id; stamped at import, never changes';

CREATE INDEX IF NOT EXISTS `idx_items_eis_site_product`
    ON `items` (`eis_site_product_id`);

-- ─── Step 2 ─ Add eis_site_product_id to sold_items ─────────────────────────
-- Copied from items.eis_site_product_id at sale time and frozen permanently.
-- Provides an immutable audit trail: which EIS batch was deducted per sale.

ALTER TABLE `sold_items`
    ADD COLUMN IF NOT EXISTS `eis_site_product_id` INT(11) NULL DEFAULT NULL
        COMMENT 'FK -> mra_eis_site_products.id; frozen at sale time for audit';

CREATE INDEX IF NOT EXISTS `idx_sold_items_eis_site_product`
    ON `sold_items` (`eis_site_product_id`);

-- ─── Step 3 ─ Add selling_price to mra_eis_product_code_map ─────────────────
-- Enables price-tier disambiguation: the same product_code at the same terminal
-- but at different prices maps to different EIS rows.
-- Also widens the unique key from (ims_product_code) alone to
-- (ims_product_code, terminal_local_id, selling_price) so a product can be
-- mapped independently per terminal/branch.

ALTER TABLE `mra_eis_product_code_map`
    ADD COLUMN IF NOT EXISTS `selling_price` DECIMAL(18,4) NOT NULL DEFAULT 0.0000
        COMMENT 'Price of the mapped mra_eis_site_products row at time of mapping';

-- Drop the old narrow unique key (ims_product_code only) if it still exists.
ALTER TABLE `mra_eis_product_code_map`
    DROP INDEX IF EXISTS `uniq_mra_eis_product_code_map`;

-- Add the wider unique key.
ALTER TABLE `mra_eis_product_code_map`
    ADD UNIQUE KEY IF NOT EXISTS `uniq_product_code_terminal_price`
        (`ims_product_code`, `terminal_local_id`, `selling_price`);

-- ─── Step 4 ─ Backfill selling_price on existing mappings ───────────────────
-- Set selling_price from the linked EIS row for rows that were created before
-- this migration (they will have selling_price = 0.0000).

UPDATE `mra_eis_product_code_map` m
JOIN   `mra_eis_site_products` sp ON sp.id = m.site_product_id
SET    m.selling_price = sp.price
WHERE  m.selling_price = 0
  AND  sp.price > 0;

-- ─── Step 5 ─ Automated backfill of items.eis_site_product_id ───────────────
-- Safe to run: only stamps items where exactly ONE EIS row matches the item's
-- branch + product_code combination. Ambiguous items (multiple matching EIS
-- rows) are left NULL for the admin to resolve via the batch mapping UI.
-- Already-sold items are never touched.

UPDATE `items` i
JOIN   `mra_eis_terminals` t
           ON  t.branch_id = i.current_location_id
           AND t.status    = 'activated'
JOIN   `mra_eis_site_products` sp
           ON  sp.product_code      = i.product_code
           AND sp.site_id           = t.site_id
           AND sp.terminal_local_id = t.id
SET    i.eis_site_product_id = sp.id
WHERE  i.eis_site_product_id IS NULL
  AND  i.product_code IS NOT NULL
  AND  i.product_code != ''
  -- Do not touch items that have already been sold
  AND  NOT EXISTS (
           SELECT 1
           FROM   `sold_items` s
           WHERE  s.unique_code = i.unique_code
       )
  -- Only stamp where exactly one EIS row matches this item's branch + product
  AND (
           SELECT COUNT(*)
           FROM   `mra_eis_terminals`    t2
           JOIN   `mra_eis_site_products` sp2
                      ON  sp2.product_code      = i.product_code
                      AND sp2.site_id           = t2.site_id
                      AND sp2.terminal_local_id = t2.id
           WHERE  t2.branch_id = i.current_location_id
             AND  t2.status    = 'activated'
       ) = 1;

-- ─── Step 6 ─ Backfill eis_site_product_id on historical sold_items ─────────
-- For items that were already sold before this migration, copy the value from
-- items.eis_site_product_id (which was just backfilled above) so the audit
-- trail is as complete as possible.

UPDATE `sold_items` si
JOIN   `items` i ON i.item_id = si.item_id
SET    si.eis_site_product_id = i.eis_site_product_id
WHERE  si.eis_site_product_id IS NULL
  AND  i.eis_site_product_id  IS NOT NULL;
