flatrender/backend/db/migrations/06_content_projects.sql

-- =====================================================================
-- CONTENT SCHEMA — Part 2: Project Containers & Projects (Templates)
-- =====================================================================

SET search_path TO content, public;

-- Tenants can mark projects as private (only their users see them)
-- or use the global FlatRender catalog.

CREATE TYPE resolution_kind AS ENUM ('HD','FullHD','TwoK','FourK');

-- ---------------------------------------------------------------------
-- project_containers — the "product" (template pack)
-- ---------------------------------------------------------------------
CREATE TABLE project_containers (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id               UUID,                                    -- NULL = global
    -- FK to identity.tenants is enforced via service code (cross-schema FK kept loose)

    slug                    CITEXT NOT NULL UNIQUE,
    name                    TEXT NOT NULL,
    description             TEXT,
    keywords                TEXT,
    news_text               TEXT,

    -- Media
    image                   TEXT,
    demo                    TEXT,
    full_demo               TEXT,
    mini_demo               TEXT,
    demo_script_tag         TEXT,

    -- Modes & classifications
    is_published            BOOLEAN NOT NULL DEFAULT FALSE,
    is_premium              BOOLEAN NOT NULL DEFAULT FALSE,
    is_mockup               BOOLEAN NOT NULL DEFAULT FALSE,
    primary_mode            choose_mode NOT NULL DEFAULT 'FLEXIBLE',

    -- Stats (denormalized for speed)
    rate_avg                NUMERIC(3,2),
    rate_count              INT NOT NULL DEFAULT 0,
    view_count              BIGINT NOT NULL DEFAULT 0,
    use_count               BIGINT NOT NULL DEFAULT 0,

    sort                    INT NOT NULL DEFAULT 0,
    sort_date               TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at              TIMESTAMPTZ
);

CREATE INDEX idx_containers_published  ON project_containers(is_published, sort_date DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_containers_tenant     ON project_containers(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_containers_name_trgm  ON project_containers USING gin (name gin_trgm_ops);

CREATE TRIGGER tg_containers_updated_at
BEFORE UPDATE ON project_containers FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- M2M: container ↔ categories
-- ---------------------------------------------------------------------
CREATE TABLE container_categories (
    container_id        UUID NOT NULL REFERENCES project_containers(id) ON DELETE CASCADE,
    category_id         UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    sort                INT NOT NULL DEFAULT 0,
    PRIMARY KEY (container_id, category_id)
);

CREATE INDEX idx_cc_category ON container_categories(category_id);

-- ---------------------------------------------------------------------
-- M2M: container ↔ tags
-- ---------------------------------------------------------------------
CREATE TABLE container_tags (
    container_id        UUID NOT NULL REFERENCES project_containers(id) ON DELETE CASCADE,
    tag_id              UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (container_id, tag_id)
);

CREATE INDEX idx_ct_tag ON container_tags(tag_id);

-- ---------------------------------------------------------------------
-- projects — one aspect-ratio variant of a container
-- ---------------------------------------------------------------------
CREATE TABLE projects (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    container_id                UUID NOT NULL REFERENCES project_containers(id) ON DELETE CASCADE,
    project_server_id           UUID REFERENCES project_servers(id),

    name                        TEXT NOT NULL,
    description                 TEXT,
    image                       TEXT,
    full_demo                   TEXT,
    demo_script_tag             TEXT,
    download_link               TEXT,

    -- AEP file storage
    aep_minio_bucket            TEXT,
    aep_minio_key               TEXT,
    aep_file_url                TEXT,
    aep_file_md5                TEXT,                            -- for node cache check
    aep_file_size_bytes         BIGINT,
    aep_uploaded_at             TIMESTAMPTZ,
    folder                      TEXT,                            -- legacy path on server

    -- Geometry
    original_width              INT NOT NULL,
    original_height             INT NOT NULL,
    aspect                      TEXT,                            -- '16:9','9:16','1:1','4:5',...

    -- Timing
    project_duration_sec        NUMERIC(8,2) NOT NULL,
    min_duration_sec            NUMERIC(8,2),
    max_duration_sec            NUMERIC(8,2),
    free_fps                    INT NOT NULL DEFAULT 30,

    -- Mode
    choose_mode                 choose_mode NOT NULL,
    resolution                  resolution_kind NOT NULL DEFAULT 'FullHD',
    vip_factor                  NUMERIC(4,2) NOT NULL DEFAULT 1.0,
    render_aep_comp             TEXT NOT NULL DEFAULT 'flatrender', -- main comp name

    -- Misc (legacy artifacts to preserve)
    shared_layer_image          TEXT,
    shared_colors_svg           TEXT,
    shared_color_presets_svg    TEXT,

    -- Status
    is_published                BOOLEAN NOT NULL DEFAULT FALSE,

    sort                        INT NOT NULL DEFAULT 0,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at                  TIMESTAMPTZ
);

CREATE INDEX idx_projects_container   ON projects(container_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_projects_published   ON projects(is_published) WHERE deleted_at IS NULL;
CREATE INDEX idx_projects_aep_md5     ON projects(aep_file_md5) WHERE aep_file_md5 IS NOT NULL;

CREATE TRIGGER tg_projects_updated_at
BEFORE UPDATE ON projects FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();