| 35896 |
amit |
1 |
-- Migration: Backfill GST numbers from user.counter to fofo.fofo_store
|
|
|
2 |
-- Purpose: fofo_store.gst_number is now the canonical source for partner GST.
|
|
|
3 |
-- This script copies existing valid GST numbers from the counter table.
|
|
|
4 |
-- Safety: Non-destructive. Old counter data is NOT modified or deleted.
|
|
|
5 |
|
| 35908 |
amit |
6 |
-- Step 0: Add gst_number column if it doesn't exist
|
|
|
7 |
ALTER TABLE fofo.fofo_store ADD COLUMN IF NOT EXISTS gst_number VARCHAR(15) DEFAULT NULL;
|
|
|
8 |
|
| 35896 |
amit |
9 |
-- Step 1: Verify data before migration (dry run)
|
|
|
10 |
SELECT fs.id, fs.code, fs.gst_number AS current_fofo_gst, c.gstin AS counter_gst
|
|
|
11 |
FROM fofo.fofo_store fs
|
|
|
12 |
JOIN user.privatedealuser pdu ON pdu.id = fs.id
|
|
|
13 |
JOIN user.counter c ON c.id = pdu.counter_id
|
|
|
14 |
WHERE c.gstin IS NOT NULL
|
|
|
15 |
AND TRIM(c.gstin) != ''
|
|
|
16 |
AND LENGTH(TRIM(c.gstin)) = 15;
|
|
|
17 |
|
|
|
18 |
-- Step 2: Backfill valid GST numbers
|
|
|
19 |
UPDATE fofo.fofo_store fs
|
|
|
20 |
JOIN user.privatedealuser pdu ON pdu.id = fs.id
|
|
|
21 |
JOIN user.counter c ON c.id = pdu.counter_id
|
|
|
22 |
SET fs.gst_number = TRIM(c.gstin)
|
|
|
23 |
WHERE c.gstin IS NOT NULL
|
|
|
24 |
AND TRIM(c.gstin) != ''
|
|
|
25 |
AND LENGTH(TRIM(c.gstin)) = 15;
|
|
|
26 |
|
|
|
27 |
-- Step 3: Verify migration results
|
|
|
28 |
SELECT COUNT(*) AS migrated_count
|
|
|
29 |
FROM fofo.fofo_store
|
|
|
30 |
WHERE gst_number IS NOT NULL AND gst_number != '';
|