-- Shipping, redesigned for India — doc 25 §11.
--
-- One pricing method per delivery channel. The address decides the channel, so
-- no two methods ever compete to price the same order and there is no
-- precedence to configure. The UNIQUE (storeId, channel) below is not a
-- safeguard around the design; it IS the design.
--
-- The old shipping_zones / shipping_rates tables are left in place. Orders
-- store `shippingTotal` as an integer with no foreign key, so historical orders
-- are unaffected either way — but dropping tables a running deployment might
-- still read is a migration that has to be timed, and this one does not.
--
-- Idempotent; safe to re-run.
BEGIN;

CREATE TABLE IF NOT EXISTS store.shipping_channels (
  id               TEXT PRIMARY KEY,
  "storeId"        TEXT NOT NULL,
  channel          TEXT NOT NULL,
  kind             TEXT NOT NULL,
  name             TEXT NOT NULL,
  "basePaise"      INTEGER,

  "freeAbovePaise" INTEGER,
  "codEnabled"     BOOLEAN NOT NULL DEFAULT TRUE,
  "codFlatPaise"   INTEGER NOT NULL DEFAULT 0,
  "codPercentBps"  INTEGER NOT NULL DEFAULT 0,
  "handlingPaise"  INTEGER NOT NULL DEFAULT 0,

  "isActive"       BOOLEAN NOT NULL DEFAULT TRUE,
  "createdBy"      TEXT NOT NULL DEFAULT 'system',
  "createdOn"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"      TEXT NOT NULL DEFAULT 'system',
  "updatedOn"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- One method per channel per store.
CREATE UNIQUE INDEX IF NOT EXISTS shipping_channels_store_channel
  ON store.shipping_channels ("storeId", channel);
CREATE INDEX IF NOT EXISTS shipping_channels_store ON store.shipping_channels ("storeId");

ALTER TABLE store.shipping_channels DROP CONSTRAINT IF EXISTS shipping_channels_channel_ck;
ALTER TABLE store.shipping_channels ADD CONSTRAINT shipping_channels_channel_ck
  CHECK (channel IN ('local', 'outstation'));

ALTER TABLE store.shipping_channels DROP CONSTRAINT IF EXISTS shipping_channels_kind_ck;
ALTER TABLE store.shipping_channels ADD CONSTRAINT shipping_channels_kind_ck
  CHECK (kind IN ('free','flat','order_value','per_item','distance','weight','zone_weight','courier_live'));

-- A distance method in the outstation channel would price a 1,200 km courier
-- journey off kilometre bands; a zone method locally would produce five zones
-- that are all the same zone. Neither is a preference.
ALTER TABLE store.shipping_channels DROP CONSTRAINT IF EXISTS shipping_channels_kind_fits_channel;
ALTER TABLE store.shipping_channels ADD CONSTRAINT shipping_channels_kind_fits_channel CHECK (
  (channel = 'local'      AND kind IN ('free','flat','order_value','per_item','distance'))
  OR
  (channel = 'outstation' AND kind IN ('free','flat','order_value','per_item','weight','zone_weight','courier_live'))
);

ALTER TABLE store.shipping_channels DROP CONSTRAINT IF EXISTS shipping_channels_money_ck;
ALTER TABLE store.shipping_channels ADD CONSTRAINT shipping_channels_money_ck CHECK (
  ("basePaise"      IS NULL OR "basePaise"      >= 0) AND
  ("freeAbovePaise" IS NULL OR "freeAbovePaise" >= 0) AND
  "codFlatPaise"  >= 0 AND
  "handlingPaise" >= 0 AND
  "codPercentBps" BETWEEN 0 AND 10000
);

-- ── Store-wide settings ─────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS store.shipping_settings (
  "storeId"            TEXT PRIMARY KEY,
  "localBoundary"      TEXT NOT NULL DEFAULT 'radius',
  "localRadiusKm"      INTEGER,
  "originPincode"      TEXT,
  "pickupEnabled"      BOOLEAN NOT NULL DEFAULT FALSE,
  "pickupReadyMinutes" INTEGER,
  "defaultWeightGrams" INTEGER NOT NULL DEFAULT 500,
  "createdBy"          TEXT NOT NULL DEFAULT 'system',
  "createdOn"          TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"          TEXT NOT NULL DEFAULT 'system',
  "updatedOn"          TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE store.shipping_settings DROP CONSTRAINT IF EXISTS shipping_settings_boundary_ck;
ALTER TABLE store.shipping_settings ADD CONSTRAINT shipping_settings_boundary_ck
  CHECK ("localBoundary" IN ('radius', 'same_city', 'same_state'));

-- Never zero. A product with no weight recorded must not ship free.
ALTER TABLE store.shipping_settings DROP CONSTRAINT IF EXISTS shipping_settings_weight_ck;
ALTER TABLE store.shipping_settings ADD CONSTRAINT shipping_settings_weight_ck
  CHECK ("defaultWeightGrams" > 0);

ALTER TABLE store.shipping_settings DROP CONSTRAINT IF EXISTS shipping_settings_radius_ck;
ALTER TABLE store.shipping_settings ADD CONSTRAINT shipping_settings_radius_ck
  CHECK ("localRadiusKm" IS NULL OR ("localRadiusKm" > 0 AND "localRadiusKm" <= 500));

-- ── Child tables, one per method that needs rows ────────────────────────────

CREATE TABLE IF NOT EXISTS store.shipping_distance_bands (
  id           TEXT PRIMARY KEY,
  "channelId"  TEXT NOT NULL REFERENCES store.shipping_channels(id) ON DELETE CASCADE,
  "uptoKm"     INTEGER NOT NULL,
  "basePaise"  INTEGER NOT NULL,
  "perKmPaise" INTEGER NOT NULL DEFAULT 0,
  "minMinutes" INTEGER,
  "maxMinutes" INTEGER,
  CONSTRAINT shipping_distance_bands_ck CHECK ("uptoKm" > 0 AND "basePaise" >= 0 AND "perKmPaise" >= 0)
);
CREATE INDEX IF NOT EXISTS shipping_distance_bands_channel ON store.shipping_distance_bands ("channelId");

CREATE TABLE IF NOT EXISTS store.shipping_zone_rates (
  id               TEXT PRIMARY KEY,
  "channelId"      TEXT NOT NULL REFERENCES store.shipping_channels(id) ON DELETE CASCADE,
  zone             TEXT NOT NULL,
  "basePaise"      INTEGER NOT NULL,
  "firstSlabGrams" INTEGER NOT NULL DEFAULT 500,
  "perSlabPaise"   INTEGER NOT NULL DEFAULT 0,
  "slabGrams"      INTEGER NOT NULL DEFAULT 500,
  "minDays"        INTEGER,
  "maxDays"        INTEGER,
  CONSTRAINT shipping_zone_rates_zone_ck
    CHECK (zone IN ('local','regional','metro','rest_of_india','special')),
  CONSTRAINT shipping_zone_rates_money_ck
    CHECK ("basePaise" >= 0 AND "perSlabPaise" >= 0 AND "firstSlabGrams" > 0 AND "slabGrams" > 0)
);
CREATE UNIQUE INDEX IF NOT EXISTS shipping_zone_rates_channel_zone
  ON store.shipping_zone_rates ("channelId", zone);
CREATE INDEX IF NOT EXISTS shipping_zone_rates_channel ON store.shipping_zone_rates ("channelId");

CREATE TABLE IF NOT EXISTS store.shipping_value_slabs (
  id           TEXT PRIMARY KEY,
  "channelId"  TEXT NOT NULL REFERENCES store.shipping_channels(id) ON DELETE CASCADE,
  "uptoPaise"  INTEGER NOT NULL,
  "pricePaise" INTEGER NOT NULL,
  CONSTRAINT shipping_value_slabs_ck CHECK ("uptoPaise" >= 0 AND "pricePaise" >= 0)
);
CREATE INDEX IF NOT EXISTS shipping_value_slabs_channel ON store.shipping_value_slabs ("channelId");

CREATE TABLE IF NOT EXISTS store.shipping_weight_slabs (
  id           TEXT PRIMARY KEY,
  "channelId"  TEXT NOT NULL REFERENCES store.shipping_channels(id) ON DELETE CASCADE,
  "uptoGrams"  INTEGER NOT NULL,
  "pricePaise" INTEGER NOT NULL,
  CONSTRAINT shipping_weight_slabs_ck CHECK ("uptoGrams" > 0 AND "pricePaise" >= 0)
);
CREATE INDEX IF NOT EXISTS shipping_weight_slabs_channel ON store.shipping_weight_slabs ("channelId");

-- ── Migrate what exists ─────────────────────────────────────────────────────
--
-- Every store with configured rates gets an Outstation flat method carrying
-- their cheapest existing rate, so nobody loses a price they had set. Stores
-- with no rates get the default (₹50, free above ₹499) — a working
-- configuration on day one rather than an empty screen.

INSERT INTO store.shipping_settings ("storeId", "localBoundary", "defaultWeightGrams")
SELECT s.id, 'radius', 500
FROM store.stores s
ON CONFLICT ("storeId") DO NOTHING;

INSERT INTO store.shipping_channels (id, "storeId", channel, kind, name, "basePaise", "freeAbovePaise")
SELECT
  gen_random_uuid()::text,
  s.id,
  'outstation',
  'flat',
  'Standard delivery',
  COALESCE((SELECT MIN(r.amount) FROM store.shipping_rates r WHERE r."storeId" = s.id AND r."isActive"), 5000),
  49900
FROM store.stores s
ON CONFLICT ("storeId", channel) DO NOTHING;

COMMENT ON TABLE store.shipping_channels IS
  'One pricing method per delivery channel. The address decides the channel — see docs/25-india-shipping-redesign.md §11.';

COMMIT;