-- =============================================================================
-- MIGRATION: Storefront Templates table + store FK
--
-- The *structure* axis of a storefront (page layout / which sections render),
-- orthogonal to platform.themes which owns presentation tokens only.
--
-- NOTE ON NAMING: platform.templates / template_types / template_versions are
-- the EMAIL template system. This is a separate, unrelated table.
--
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Create platform.storefront_templates table
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform.storefront_templates (
    id                  VARCHAR(255)    NOT NULL DEFAULT gen_random_uuid()::text,
    name                VARCHAR(255)    NOT NULL,
    slug                VARCHAR(255)    NOT NULL,
    description         TEXT,
    "previewImageUrl"   TEXT,
    -- Minimum Organization.plan required to apply this template. Plain text +
    -- CHECK below, matching organizations.plan (migrations 021/022) rather
    -- than a PG enum, so a new tier is a constraint change not a type change.
    tier                TEXT            NOT NULL DEFAULT 'free',
    -- Display-only (feature bullets / preview imagery for the merchant picker).
    -- Deliberately does NOT describe which pages the template overrides — the
    -- code registry in web/ is the single source of truth for that.
    metadata            JSONB,
    "isPublished"       BOOLEAN         NOT NULL DEFAULT FALSE,
    "isActive"          BOOLEAN         NOT NULL DEFAULT TRUE,
    "sortOrder"         INTEGER         NOT NULL DEFAULT 0,
    "createdBy"         VARCHAR(255)    NOT NULL DEFAULT 'system',
    "createdOn"         TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    "updatedBy"         VARCHAR(255)    NOT NULL DEFAULT 'system',
    "updatedOn"         TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    "deletedAt"         TIMESTAMPTZ,
    "deletedBy"         VARCHAR(255),

    CONSTRAINT storefront_templates_pkey PRIMARY KEY (id),
    CONSTRAINT storefront_templates_slug_key UNIQUE (slug)
);

-- Tier value guard — same pattern as organizations_approvalStatus_check (021).
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'storefront_templates_tier_check'
  ) THEN
    ALTER TABLE platform.storefront_templates
      ADD CONSTRAINT "storefront_templates_tier_check"
      CHECK (tier IN ('free', 'basic', 'premium'));
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS storefront_templates_published_active_idx
    ON platform.storefront_templates ("isPublished", "isActive");

CREATE INDEX IF NOT EXISTS storefront_templates_tier_idx
    ON platform.storefront_templates (tier);

-- ---------------------------------------------------------------------------
-- 2. Add storefrontTemplateId FK column to store.stores (nullable)
--
-- Intentionally NOT backfilled. NULL is the normal, permanent state for every
-- store that has never picked a template, and NULL resolves to `classic` at
-- read time — that is what makes moving the current storefront into the
-- `classic` template a zero-change migration with no data touched.
-- ---------------------------------------------------------------------------
ALTER TABLE store.stores
    ADD COLUMN IF NOT EXISTS "storefrontTemplateId" VARCHAR(255)
        REFERENCES platform.storefront_templates(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS stores_storefront_template_id_idx
    ON store.stores ("storefrontTemplateId");

COMMIT;