-- Migration: Add shop_id to stocks table
-- Date: 2026-02-06
-- Purpose: Enable per-shop stock tracking for stock transfers

-- Step 1: Add shop_id column
ALTER TABLE stocks
ADD COLUMN shop_id BIGINT NULL AFTER purchase_id;

-- Step 2: Add index for performance
CREATE INDEX idx_stocks_shop_id ON stocks(shop_id);

-- Step 3: Add foreign key constraint
ALTER TABLE stocks
ADD CONSTRAINT fk_stocks_shop_id
FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE SET NULL;

-- Step 4: Migrate existing stocks - set shop_id based on product's shop_id
-- This ensures existing stocks work with transfers immediately
UPDATE stocks s
INNER JOIN products p ON s.product_id = p.id
SET s.shop_id = p.shop_id
WHERE s.shop_id IS NULL AND p.shop_id IS NOT NULL;

-- Step 5: For stocks whose products don't have a shop_id, assign to first shop
-- This ensures all stocks have a shop for transfers to work
UPDATE stocks s
SET s.shop_id = (SELECT id FROM shops ORDER BY id ASC LIMIT 1)
WHERE s.shop_id IS NULL
AND EXISTS (SELECT 1 FROM shops LIMIT 1);