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;