Subversion Repositories SmartDukaan

-- 2026-09-28 - store every GSTIN in fofo.fofo_store in upper case.

--

-- A GSTIN is upper case by definition. Four rows were not: 06bmlpk3545g1z0 (HRMS007),

-- 03AAccn9802g1zp (T-UPSKN1434, our own registration on a real store) and two junk test values on

-- T-UPKND1433 / T-UPSRV1439. All four stores are inactive. They predate the normalisation that

-- RetailerServiceImpl.validateGstNumbers applies (r37669, live 2026-09-21), and that only covers the

-- partner-update path - the LOI and trial-form paths write the field directly.

--

-- The code side is fixed at the mutation site: FofoStore.setGstNumber now normalises, so no write

-- path can store mixed case again. This script corrects the rows already on record.

--

-- Case matters downstream: NIC expects upper case, and any comparison that is not case-insensitive

-- silently fails to match - including the "is this our own seller GSTIN" check that decides whether

-- a NIC refusal is about the buyer or about us.

--

-- NOT APPLIED automatically. Affects 4 rows. dtr.retailer.number has 34 mixed-case values and is

-- deliberately NOT touched here - out of scope, raise separately.

CREATE TABLE IF NOT EXISTS fofo.fofo_store_gst_case_bak_20260928 (

    id          INT         NOT NULL,

    gst_number  VARCHAR(50) NULL,

    backed_up_at DATETIME   NOT NULL,

    PRIMARY KEY (id)

) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO fofo.fofo_store_gst_case_bak_20260928 (id, gst_number, backed_up_at)

SELECT id, gst_number, NOW() FROM fofo.fofo_store

WHERE gst_number IS NOT NULL AND gst_number <> ''

  AND CAST(gst_number AS BINARY) <> CAST(UPPER(gst_number) AS BINARY);

UPDATE fofo.fofo_store

SET gst_number = UPPER(gst_number)

WHERE gst_number IS NOT NULL AND gst_number <> ''

  AND CAST(gst_number AS BINARY) <> CAST(UPPER(gst_number) AS BINARY);

-- Verify: expect 0 remaining, and the backup holding what was changed.

-- SELECT COUNT(*) FROM fofo.fofo_store

--  WHERE gst_number <> '' AND CAST(gst_number AS BINARY) <> CAST(UPPER(gst_number) AS BINARY);

-- SELECT * FROM fofo.fofo_store_gst_case_bak_20260928;

--

-- Rollback, if ever needed:

-- UPDATE fofo.fofo_store s JOIN fofo.fofo_store_gst_case_bak_20260928 b ON b.id = s.id

--    SET s.gst_number = b.gst_number;