-- Migration: Add shop_id to purchases table
-- Date: 2026-06-15
-- Purpose: Attribute each purchase to the shop it was received into, so purchases
--          can be scoped per shop (data isolation) and filtered. Legacy rows stay
--          NULL (visible only to all-scope/privileged users).

-- Step 1: Add shop_id column (errno 1060 ignored by the runner if it already exists)
ALTER TABLE purchases
ADD COLUMN shop_id BIGINT NULL AFTER received_by;

-- Step 2: Index for scoped list queries
CREATE INDEX idx_purchases_shop_id ON purchases(shop_id);

-- Step 3: Foreign key to shops (set NULL on shop delete)
ALTER TABLE purchases
ADD CONSTRAINT fk_purchases_shop_id
FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE SET NULL;

-- Step 4: Best-effort backfill — infer each purchase's shop from the stock batches
-- it created (those rows already carry shop_id). Leaves genuinely ambiguous rows NULL.
UPDATE purchases pu
INNER JOIN (
    SELECT purchase_id, MIN(shop_id) AS shop_id
    FROM stocks
    WHERE purchase_id IS NOT NULL AND shop_id IS NOT NULL
    GROUP BY purchase_id
) sx ON sx.purchase_id = pu.id
SET pu.shop_id = sx.shop_id
WHERE pu.shop_id IS NULL;