-- =============================================================================
-- MIGRATION: Template versioning (draft/publish) + email sender/branding settings
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Template gains isPublished (draft vs published, independent of isActive)
-- ---------------------------------------------------------------------------
ALTER TABLE platform.templates
  ADD COLUMN IF NOT EXISTS "isPublished" BOOLEAN NOT NULL DEFAULT TRUE;

-- ---------------------------------------------------------------------------
-- 2. Create platform.template_versions table
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform.template_versions (
    id              VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text,
    "templateId"    VARCHAR(255) NOT NULL
        REFERENCES platform.templates(id) ON DELETE CASCADE,
    "versionNumber" INTEGER      NOT NULL,
    name            VARCHAR(255) NOT NULL,
    subject         VARCHAR(255) NOT NULL,
    "bodyHtml"      TEXT         NOT NULL,
    "isActive"      BOOLEAN      NOT NULL,
    "isPublished"   BOOLEAN      NOT NULL,
    action          VARCHAR(255) NOT NULL,
    comment         TEXT,
    "createdBy"     VARCHAR(255) NOT NULL DEFAULT 'system',
    "createdOn"     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    CONSTRAINT template_versions_pkey PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS template_versions_template_id_idx
    ON platform.template_versions ("templateId");

-- ---------------------------------------------------------------------------
-- 3. Create platform.email_settings table (single global row)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform.email_settings (
    id             VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text,
    "storeId"      VARCHAR(255) NOT NULL DEFAULT 'store_demo',
    "senderEmail"  VARCHAR(255),
    "senderName"   VARCHAR(255),
    "replyToEmail" VARCHAR(255),
    "logoUrl"      TEXT,
    "primaryColor" VARCHAR(50),
    "updatedBy"    VARCHAR(255) NOT NULL DEFAULT 'system',
    "updatedOn"    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    CONSTRAINT email_settings_pkey PRIMARY KEY (id),
    CONSTRAINT email_settings_store_id_key UNIQUE ("storeId")
);

COMMIT;