-- Link HSN codes to a default GST rate
-- Adds hsn_codes.gst_rate_id (nullable) FK -> gst_rates(id)
-- Idempotent: safe to re-run.

BEGIN;

ALTER TABLE hsn_codes
  ADD COLUMN IF NOT EXISTS gst_rate_id BIGINT;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'hsn_codes_gst_rate_id_fkey'
  ) THEN
    ALTER TABLE hsn_codes
      ADD CONSTRAINT hsn_codes_gst_rate_id_fkey
      FOREIGN KEY (gst_rate_id) REFERENCES gst_rates(id);
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_hsn_codes_gst_rate_id ON hsn_codes (gst_rate_id);

-- Optional backfill: default existing HSN codes to the 18% slab (adjust per business rules)
-- UPDATE hsn_codes h
-- SET gst_rate_id = g.id
-- FROM gst_rates g
-- WHERE g.rate_pct = 18 AND g.is_active = TRUE AND h.gst_rate_id IS NULL;

COMMIT;

-- Verify:
-- SELECT h.code, g.rate_pct FROM hsn_codes h LEFT JOIN gst_rates g ON g.id = h.gst_rate_id ORDER BY h.code;