-- Merchant and store branding / SEO.
--
-- Two tiers, because they are two different things:
--
--   merchant_branding — the business. One legal entity, one logo, one set of
--                       social profiles. Feeds invoices, transactional email,
--                       Organization JSON-LD, and acts as the default for every
--                       store below it.
--
--   store_branding    — a shopfront. Its own subdomain, its own local area, its
--                       own opening hours. This is where search SEO lives,
--                       because a store is what has a URL.
--
-- Every store field is nullable and falls back to the merchant's: set a logo
-- once and five stores have it, while a store that wants its own overrides.
-- A copy would be a snapshot that drifts; inheritance stays right when the
-- brand changes.
--
-- Idempotent; safe to re-run.
BEGIN;

CREATE TABLE IF NOT EXISTS platform.merchant_branding (
  "id"                     TEXT PRIMARY KEY,
  "organizationId"         TEXT NOT NULL UNIQUE
    REFERENCES platform.organizations("id") ON DELETE CASCADE,

  -- Identity. Asset ids point at platform.media_assets so a logo obeys the same
  -- quota, trash and storage-switch rules as any other upload.
  "logoAssetId"            TEXT,
  "logoDarkAssetId"        TEXT,
  "logoTransparentAssetId" TEXT,
  "faviconAssetId"         TEXT,
  "primaryColor"           TEXT,

  -- The business, for Organization JSON-LD.
  "legalName"              TEXT,
  "supportEmail"           TEXT,
  "supportPhone"           TEXT,
  "websiteUrl"             TEXT,

  -- Defaults inherited by every store that has not set its own.
  "metaTitle"              TEXT,
  "metaDescription"        TEXT,
  "metaKeywords"           TEXT,

  -- Social. Becomes sameAs on the Organization node.
  "twitterHandle"          TEXT,
  "facebookUrl"            TEXT,
  "instagramUrl"           TEXT,
  "linkedinUrl"            TEXT,
  "youtubeUrl"             TEXT,

  "createdBy"              TEXT NOT NULL DEFAULT 'system',
  "createdOn"              TIMESTAMP(3) NOT NULL DEFAULT NOW(),
  "updatedBy"              TEXT NOT NULL DEFAULT 'system',
  "updatedOn"              TIMESTAMP(3) NOT NULL DEFAULT NOW(),

  -- Enforced here as well as in the form: a title truncated by Google is a
  -- title the merchant did not choose the ending of.
  CONSTRAINT merchant_branding_title_len CHECK ("metaTitle" IS NULL OR length("metaTitle") <= 70),
  CONSTRAINT merchant_branding_desc_len  CHECK ("metaDescription" IS NULL OR length("metaDescription") <= 320)
);

CREATE TABLE IF NOT EXISTS store.store_branding (
  "id"                     TEXT PRIMARY KEY,
  "storeId"                TEXT NOT NULL UNIQUE
    REFERENCES store.stores("id") ON DELETE CASCADE,

  -- Overrides for the merchant tier. NULL means "inherit".
  "logoAssetId"            TEXT,
  "logoDarkAssetId"        TEXT,
  "logoTransparentAssetId" TEXT,
  "faviconAssetId"         TEXT,
  "ogImageAssetId"         TEXT,

  -- Search. Store-level because a store is what has a URL.
  "metaTitle"              TEXT,
  "metaDescription"        TEXT,
  "metaKeywords"           TEXT,
  "canonicalBaseUrl"       TEXT,
  "robotsPolicy"           TEXT NOT NULL DEFAULT 'index,follow',

  -- Local SEO. This is what puts a shop in a map pack for its own town:
  -- "grocery store in Karimnagar" is answered by LocalBusiness markup carrying
  -- a real address and coordinates, not by a keyword in a meta tag.
  "businessType"           TEXT,
  "areaName"               TEXT,
  "streetAddress"          TEXT,
  "locality"               TEXT,
  "region"                 TEXT,
  "postalCode"             TEXT,
  "countryCode"            TEXT NOT NULL DEFAULT 'IN',
  "latitude"               DECIMAL(9,6),
  "longitude"              DECIMAL(9,6),
  "serviceAreas"           TEXT,
  "openingHours"           JSONB,
  "contactPhone"           TEXT,
  "contactEmail"           TEXT,
  "mapsUrl"                TEXT,

  -- Search-console and analytics verification, rendered server-side because
  -- that is where a crawler looks.
  "googleSiteVerification" TEXT,
  "bingSiteVerification"   TEXT,
  "gaMeasurementId"        TEXT,
  "gtmContainerId"         TEXT,

  "createdBy"              TEXT NOT NULL DEFAULT 'system',
  "createdOn"              TIMESTAMP(3) NOT NULL DEFAULT NOW(),
  "updatedBy"              TEXT NOT NULL DEFAULT 'system',
  "updatedOn"              TIMESTAMP(3) NOT NULL DEFAULT NOW(),

  CONSTRAINT store_branding_title_len CHECK ("metaTitle" IS NULL OR length("metaTitle") <= 70),
  CONSTRAINT store_branding_desc_len  CHECK ("metaDescription" IS NULL OR length("metaDescription") <= 320),
  -- A half-set coordinate pair is worse than none: it produces a map pin in
  -- the sea rather than no pin at all.
  CONSTRAINT store_branding_latlng_pair CHECK (
    ("latitude" IS NULL) = ("longitude" IS NULL)
  ),
  CONSTRAINT store_branding_robots CHECK (
    "robotsPolicy" IN ('index,follow', 'index,nofollow', 'noindex,follow', 'noindex,nofollow')
  )
);

CREATE INDEX IF NOT EXISTS store_branding_store_idx ON store.store_branding("storeId");

COMMENT ON COLUMN store.store_branding."areaName" IS
  'The neighbourhood or town this shop serves — Karimnagar, Jagityal. Used in the local title template and in LocalBusiness markup.';
COMMENT ON COLUMN store.store_branding."serviceAreas" IS
  'Comma-separated additional areas served, for areaServed on the LocalBusiness node.';

COMMIT;