-- =============================================================================
-- MIGRATION: Platform Themes table + store FK
-- Idempotent — safe to re-execute
-- =============================================================================

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Create platform.themes table
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform.themes (
    id                  VARCHAR(255)    NOT NULL DEFAULT gen_random_uuid()::text,
    name                VARCHAR(255)    NOT NULL,
    slug                VARCHAR(255)    NOT NULL,
    description         TEXT,
    "previewImageUrl"   TEXT,
    settings            JSONB           NOT NULL DEFAULT '{}',
    "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 themes_pkey PRIMARY KEY (id),
    CONSTRAINT themes_slug_key UNIQUE (slug)
);

CREATE INDEX IF NOT EXISTS themes_published_active_idx
    ON platform.themes ("isPublished", "isActive");

-- ---------------------------------------------------------------------------
-- 2. Add theme_id FK column to store.stores (nullable)
-- ---------------------------------------------------------------------------
ALTER TABLE store.stores
    ADD COLUMN IF NOT EXISTS "themeId" VARCHAR(255)
        REFERENCES platform.themes(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS stores_theme_id_idx ON store.stores ("themeId");

COMMIT;