-- Indian GST tax system: replaces the old flat per-country/region blended
-- tax (TaxSetting) with reusable GST slab profiles (CGST/SGST/IGST split)
-- that products attach to, and per-line-item tax breakdown storage on
-- checkout/order line items + aggregate totals on the session/order.

-- ── 1. Repurpose the previously-unwired TaxCategory into TaxSlabProfile ──
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'master' AND tablename = 'tax_categories') THEN
    ALTER TABLE master.tax_categories RENAME TO tax_slab_profiles;
  END IF;
END $$;
ALTER TABLE master.tax_slab_profiles DROP COLUMN IF EXISTS "rateBps";
ALTER TABLE master.tax_slab_profiles
  ADD COLUMN IF NOT EXISTS "hsnGroup" TEXT,
  ADD COLUMN IF NOT EXISTS "igstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "cgstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstRateBps" INTEGER NOT NULL DEFAULT 0;

-- ── 2. Product — tax mode/profile/HSN/custom rates ──
ALTER TABLE products.products
  ADD COLUMN IF NOT EXISTS "taxMode" TEXT NOT NULL DEFAULT 'predefined',
  ADD COLUMN IF NOT EXISTS "taxProfileId" TEXT,
  ADD COLUMN IF NOT EXISTS "hsnCode" TEXT,
  ADD COLUMN IF NOT EXISTS "customIgstRateBps" INTEGER,
  ADD COLUMN IF NOT EXISTS "customCgstRateBps" INTEGER,
  ADD COLUMN IF NOT EXISTS "customSgstRateBps" INTEGER,
  ADD COLUMN IF NOT EXISTS "isTaxInclusive" BOOLEAN NOT NULL DEFAULT false;

CREATE INDEX IF NOT EXISTS "products_taxProfileId_idx" ON products.products ("taxProfileId");

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'products_taxProfileId_fkey'
  ) THEN
    ALTER TABLE products.products
      ADD CONSTRAINT "products_taxProfileId_fkey"
      FOREIGN KEY ("taxProfileId") REFERENCES master.tax_slab_profiles(id) ON DELETE SET NULL;
  END IF;
END $$;

-- ── 3. Store — GSTIN + GST/TCS settings ──
ALTER TABLE store.stores
  ADD COLUMN IF NOT EXISTS "gstin" TEXT,
  ADD COLUMN IF NOT EXISTS "defaultTaxInclusive" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS "tcsRateBps" INTEGER NOT NULL DEFAULT 100,
  ADD COLUMN IF NOT EXISTS "tcsThresholdPaise" INTEGER;

-- ── 4. Checkout line items + order items — per-line GST breakdown ──
ALTER TABLE cart.checkout_line_items
  ADD COLUMN IF NOT EXISTS "hsnCode" TEXT,
  ADD COLUMN IF NOT EXISTS "igstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "cgstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "isInterState" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS "taxableValue" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "cgstAmount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstAmount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "igstAmount" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE sales.order_items
  ADD COLUMN IF NOT EXISTS "hsnCode" TEXT,
  ADD COLUMN IF NOT EXISTS "igstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "cgstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstRateBps" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "isInterState" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS "taxableValue" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "cgstAmount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstAmount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "igstAmount" INTEGER NOT NULL DEFAULT 0;

-- ── 5. Checkout sessions + orders — aggregate GST/TCS totals ──
ALTER TABLE cart.checkout_sessions
  ADD COLUMN IF NOT EXISTS "cgstTotal" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstTotal" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "igstTotal" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "tcsTotal" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE sales.orders
  ADD COLUMN IF NOT EXISTS "cgstTotal" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "sgstTotal" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "igstTotal" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "tcsTotal" INTEGER NOT NULL DEFAULT 0;

-- ── 6. Retire the old flat blended tax model — fully superseded, not kept
--       alongside the new per-product GST system. ──
DROP TABLE IF EXISTS store.tax_settings;