Subversion Repositories SmartDukaan

-- Migration: Backfill GST numbers from user.counter to fofo.fofo_store
-- Purpose: fofo_store.gst_number is now the canonical source for partner GST.
--          This script copies existing valid GST numbers from the counter table.
-- Safety: Non-destructive. Old counter data is NOT modified or deleted.

-- Step 0: Add gst_number column if it doesn't exist
ALTER TABLE fofo.fofo_store ADD COLUMN IF NOT EXISTS gst_number VARCHAR(15) DEFAULT NULL;

-- Step 1: Verify data before migration (dry run)
SELECT fs.id, fs.code, fs.gst_number AS current_fofo_gst, c.gstin AS counter_gst
FROM fofo.fofo_store fs
JOIN user.privatedealuser pdu ON pdu.id = fs.id
JOIN user.counter c ON c.id = pdu.counter_id
WHERE c.gstin IS NOT NULL
  AND TRIM(c.gstin) != ''
  AND LENGTH(TRIM(c.gstin)) = 15;

-- Step 2: Backfill valid GST numbers
UPDATE fofo.fofo_store fs
JOIN user.privatedealuser pdu ON pdu.id = fs.id
JOIN user.counter c ON c.id = pdu.counter_id
SET fs.gst_number = TRIM(c.gstin)
WHERE c.gstin IS NOT NULL
  AND TRIM(c.gstin) != ''
  AND LENGTH(TRIM(c.gstin)) = 15;

-- Step 3: Verify migration results
SELECT COUNT(*) AS migrated_count
FROM fofo.fofo_store
WHERE gst_number IS NOT NULL AND gst_number != '';