-- Branding/SEO refinements.
--
-- Removes two fields that duplicated existing concepts, and adds the ones that
-- actually change what a local shop ranks for.
--
-- Idempotent; safe to re-run.
BEGIN;

-- ── Removed: already owned elsewhere ────────────────────────────────────────
--
--   primaryColor — Theme.settings is the palette. Two places to set a brand
--                  colour is two places for them to disagree, and the email
--                  renderer already reads EmailSettings.primaryColor.
--   websiteUrl   — StoreDomain is the store's address on the web, with
--                  isPrimary and verification behind it. A free-text URL beside
--                  it invites a merchant to type something that contradicts the
--                  domain they actually own.
ALTER TABLE platform.merchant_branding
  DROP COLUMN IF EXISTS "primaryColor",
  DROP COLUMN IF EXISTS "websiteUrl";

-- ── Added: the fields that earn their place ────────────────────────────────

ALTER TABLE platform.merchant_branding
  -- India's dominant channel for local retail. Distinct from a phone number
  -- because it becomes a click-to-chat link, not a tel: link.
  ADD COLUMN IF NOT EXISTS "whatsappNumber" TEXT;

ALTER TABLE store.store_branding
  -- Shown directly in Google's local results as ₹ / ₹₹ / ₹₹₹. One of the very
  -- few structured-data fields a searcher actually sees before clicking.
  ADD COLUMN IF NOT EXISTS "priceRange" TEXT,

  -- The canonical identifier of a Google Business Profile. A maps URL is a
  -- link; the Place ID is what ties this store to that listing, and it is the
  -- single highest-leverage field on this table for local search.
  ADD COLUMN IF NOT EXISTS "googlePlaceId" TEXT,

  ADD COLUMN IF NOT EXISTS "whatsappNumber" TEXT,

  -- A shop is often two things at once — a bakery that is also a cafe. Google
  -- Business Profile allows a primary plus secondary categories; one type
  -- column could not express it. Comma-separated schema.org types.
  ADD COLUMN IF NOT EXISTS "additionalTypes" TEXT,

  -- Delivery reach as a radius rather than a list of town names. "We deliver
  -- within 15 km" is what a delivery business actually means, and schema.org
  -- expresses it as a GeoCircle, which is more precise than naming places.
  ADD COLUMN IF NOT EXISTS "serviceRadiusKm" INTEGER,

  -- Festival closures. Diwali, Sankranti, Ramzan — a shop that shows "Open
  -- now" on a day it is shut has told a customer something worse than nothing.
  ADD COLUMN IF NOT EXISTS "specialHours" JSONB,

  -- The template every product page title is built from, so the town reaches
  -- pages nobody edits by hand. "{product} in {area} | {store}" is the
  -- difference between ranking for "cake Karimnagar" and not.
  ADD COLUMN IF NOT EXISTS "productTitleTemplate" TEXT;

ALTER TABLE store.store_branding
  DROP CONSTRAINT IF EXISTS store_branding_price_range;
ALTER TABLE store.store_branding
  ADD CONSTRAINT store_branding_price_range CHECK (
    "priceRange" IS NULL OR "priceRange" IN ('₹', '₹₹', '₹₹₹', '₹₹₹₹')
  );

ALTER TABLE store.store_branding
  DROP CONSTRAINT IF EXISTS store_branding_radius;
ALTER TABLE store.store_branding
  ADD CONSTRAINT store_branding_radius CHECK (
    "serviceRadiusKm" IS NULL OR ("serviceRadiusKm" > 0 AND "serviceRadiusKm" <= 500)
  );

COMMENT ON COLUMN store.store_branding."googlePlaceId" IS
  'Google Business Profile Place ID (ChIJ...). Ties this store to its Google listing; found via the Place ID Finder.';
COMMENT ON COLUMN store.store_branding."productTitleTemplate" IS
  'Placeholders: {product}, {store}, {area}. Applied to every product page so the town reaches pages nobody edits.';
COMMENT ON COLUMN store.store_branding."specialHours" IS
  '[{ date, opens, closes, closed, note }] — festival and holiday overrides.';

COMMIT;