Subversion Repositories SmartDukaan

-- Migration: Add outlet_name to fofo.fofo_store and backfill from user.address
-- Run during low-traffic window (fofo_store is heavily queried, ALTER takes metadata lock)

-- Step 1: Add column
ALTER TABLE fofo.fofo_store ADD COLUMN outlet_name VARCHAR(255) DEFAULT NULL AFTER gst_number;

-- Step 2: Backfill from address.name via retailer_registered_address
-- Uses MAX(rra.address_id) to handle the 3 stores with multiple address entries
UPDATE fofo.fofo_store fs
JOIN (
    SELECT rra.retailer_id, a.name
    FROM dtr.retailer_registered_address rra
    JOIN user.address a ON a.id = rra.address_id
    WHERE rra.address_id = (
        SELECT MAX(rra2.address_id)
        FROM dtr.retailer_registered_address rra2
        WHERE rra2.retailer_id = rra.retailer_id
    )
) src ON src.retailer_id = fs.id
SET fs.outlet_name = src.name;

-- Step 3: Verify
SELECT COUNT(*) AS total, SUM(outlet_name IS NOT NULL) AS migrated FROM fofo.fofo_store;