-- =============================================================================
-- MIGRATION: Email template storage — platform.template_types + platform.templates
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Create platform.template_types table
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform.template_types (
    id            VARCHAR(255)    NOT NULL DEFAULT gen_random_uuid()::text,
    code          VARCHAR(255)    NOT NULL,
    name          VARCHAR(255)    NOT NULL,
    category      VARCHAR(255)    NOT NULL,
    description   TEXT,
    "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(),

    CONSTRAINT template_types_pkey PRIMARY KEY (id),
    CONSTRAINT template_types_code_key UNIQUE (code)
);

-- ---------------------------------------------------------------------------
-- 2. Create platform.templates table
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform.templates (
    id                VARCHAR(255)    NOT NULL DEFAULT gen_random_uuid()::text,
    "storeId"         VARCHAR(255)    NOT NULL DEFAULT 'store_demo',
    "templateTypeId"  VARCHAR(255)    NOT NULL
        REFERENCES platform.template_types(id) ON DELETE RESTRICT,
    name              VARCHAR(255)    NOT NULL,
    subject           VARCHAR(255)    NOT NULL,
    "bodyHtml"        TEXT            NOT NULL,
    "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(),

    CONSTRAINT templates_pkey PRIMARY KEY (id),
    CONSTRAINT templates_store_type_key UNIQUE ("storeId", "templateTypeId")
);

CREATE INDEX IF NOT EXISTS templates_store_id_idx ON platform.templates ("storeId");
CREATE INDEX IF NOT EXISTS templates_template_type_id_idx ON platform.templates ("templateTypeId");

COMMIT;