flatrender/backend/db/migrations/07_content_scenes.sql

-- =====================================================================
-- CONTENT SCHEMA — Part 3: Scenes & Editable Elements
-- =====================================================================

SET search_path TO content, public;

CREATE TYPE scene_kind            AS ENUM ('Normal','Config','DesignStart','DesignEnd');
CREATE TYPE content_element_type  AS ENUM (
    'Text','TextArea','Media','Audio','Voiceover',
    'CheckBox','DropDown','Fill','Color','Number',
    'Date','Toggle','Slider','Counter','Hidden'
);
CREATE TYPE justify_kind          AS ENUM ('LEFT_JUSTIFY','CENTER_JUSTIFY','RIGHT_JUSTIFY','FULL_JUSTIFY');
CREATE TYPE ai_input_type         AS ENUM ('None','TitleSuggest','BodySuggest','TranslateRtl','TranslateLtr','RemoveBG','UpscaleImage','TTS');
CREATE TYPE repeat_sort_strategy  AS ENUM ('Manual','Alphabetical','Numerical','InsertOrder');
CREATE TYPE attr_value_kind       AS ENUM ('fill','stroke','tracking','dropshadow');

-- ---------------------------------------------------------------------
-- scenes
-- ---------------------------------------------------------------------
CREATE TABLE scenes (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id                  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    -- Identity (maps to AE comp name)
    key                         TEXT NOT NULL,                       -- matches AE comp name
    title                       TEXT NOT NULL,
    localized_title             JSONB,                               -- {"fa":"...","en":"..."}

    -- Type
    scene_type                  scene_kind NOT NULL DEFAULT 'Normal',

    -- Media
    image                       TEXT,
    demo                        TEXT,
    scene_color_svg             TEXT,                                -- SVG color preview (legacy)
    snapshot_url                TEXT,                                -- pre-rendered representative frame

    -- Animation flags
    generate_kf                 BOOLEAN NOT NULL DEFAULT FALSE,

    -- Timing
    default_duration_sec        NUMERIC(8,2),
    min_duration_sec            NUMERIC(8,2),
    max_duration_sec            NUMERIC(8,2),
    overlap_at_end_sec          NUMERIC(6,2) NOT NULL DEFAULT 0,
    can_handle_duration         BOOLEAN NOT NULL DEFAULT TRUE,

    -- Customization
    manual_color_selection      BOOLEAN NOT NULL DEFAULT FALSE,

    sort                        INT NOT NULL DEFAULT 0,
    is_active                   BOOLEAN NOT NULL DEFAULT TRUE,

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

    UNIQUE (project_id, key)
);

CREATE INDEX idx_scenes_project ON scenes(project_id, sort) WHERE deleted_at IS NULL;
CREATE INDEX idx_scenes_type    ON scenes(project_id, scene_type);

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

-- ---------------------------------------------------------------------
-- M2M: scenes ↔ categories
-- ---------------------------------------------------------------------
CREATE TABLE scene_categories (
    scene_id    UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,
    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (scene_id, category_id)
);

-- ---------------------------------------------------------------------
-- repeater_items — repeating sub-blocks within a scene
-- ---------------------------------------------------------------------
CREATE TABLE repeater_items (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scene_id                    UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,

    title                       TEXT NOT NULL,
    repeat_box_key              TEXT NOT NULL,           -- AE layer name of container
    repeat_item_key             TEXT NOT NULL,           -- AE layer name of item template

    max_repeat_count            INT NOT NULL DEFAULT 10,
    user_can_change_sort        BOOLEAN NOT NULL DEFAULT TRUE,
    repeat_sort_strategy        repeat_sort_strategy NOT NULL DEFAULT 'Manual',

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

CREATE INDEX idx_repeaters_scene ON repeater_items(scene_id);

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

-- ---------------------------------------------------------------------
-- scene_content_elements — every editable field in a scene
-- ---------------------------------------------------------------------
CREATE TABLE scene_content_elements (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scene_id                    UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,
    repeater_item_id            UUID REFERENCES repeater_items(id) ON DELETE CASCADE,

    -- Identity (maps to AE frl_/frd_ layer name)
    key                         TEXT NOT NULL,
    title                       TEXT NOT NULL,
    localized_title             JSONB,
    hint                        TEXT,

    type                        content_element_type NOT NULL,
    default_value               TEXT,

    -- Text-specific
    font_id                     UUID REFERENCES fonts(id),
    font_face                   TEXT,
    font_face_name              TEXT,
    font_size                   INT,
    default_font_size           INT,
    default_font_face           TEXT,
    is_font_changeable          BOOLEAN NOT NULL DEFAULT TRUE,
    is_font_size_changeable     BOOLEAN NOT NULL DEFAULT TRUE,
    justify                     justify_kind NOT NULL DEFAULT 'CENTER_JUSTIFY',
    can_justify                 BOOLEAN NOT NULL DEFAULT TRUE,
    position_in_container       INT NOT NULL DEFAULT 0,        -- 0-8 (see JSX positionMode)
    is_text_box                 BOOLEAN NOT NULL DEFAULT FALSE,
    max_size                    INT,                           -- max char count
    direction_layer_key         TEXT,                          -- companion frd_ for RTL
    direction_layer_value       INT NOT NULL DEFAULT 0,        -- 0=LTR, 1=RTL

    -- Media-specific
    video_support               BOOLEAN NOT NULL DEFAULT FALSE,
    min_duration_sec            NUMERIC(6,2),
    max_duration_sec            NUMERIC(6,2),
    width                       INT,
    height                      INT,
    thumbnail                   TEXT,

    -- Dropdown / mapped list
    mapped_list                 JSONB,                         -- [{"label","value"}, ...]
    counter_mode                TEXT,

    -- AI
    ai_input_type               ai_input_type NOT NULL DEFAULT 'None',

    -- Visibility / linking
    is_hidden                   BOOLEAN NOT NULL DEFAULT FALSE,
    is_focused                  BOOLEAN NOT NULL DEFAULT FALSE,
    opacity_controller_key      TEXT,                          -- ref another element's key

    -- Design pattern variants (legacy DP1-4 system)
    dp1_image                   TEXT,  dp1_title TEXT,
    dp2_image                   TEXT,  dp2_title TEXT,
    dp3_image                   TEXT,  dp3_title TEXT,
    dp4_image                   TEXT,  dp4_title TEXT,

    -- Repeater virtualization
    virtual_count               INT NOT NULL DEFAULT 1,

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

    UNIQUE (scene_id, key)
);

CREATE INDEX idx_sce_scene    ON scene_content_elements(scene_id, sort);
CREATE INDEX idx_sce_repeater ON scene_content_elements(repeater_item_id) WHERE repeater_item_id IS NOT NULL;

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

-- ---------------------------------------------------------------------
-- scene_color_elements — color zones (frd_ data layers per scene)
-- ---------------------------------------------------------------------
CREATE TABLE scene_color_elements (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scene_id            UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,

    element_key         TEXT NOT NULL,                       -- matches frd_ layer name
    title               TEXT NOT NULL,
    icon                TEXT,
    attr_value          attr_value_kind NOT NULL DEFAULT 'fill',
    default_color       TEXT NOT NULL,                       -- '#RRGGBB' or 'r,g,b'

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

    UNIQUE (scene_id, element_key)
);

CREATE INDEX idx_sce_color_scene ON scene_color_elements(scene_id, sort);

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

-- ---------------------------------------------------------------------
-- scene_color_presets — theme presets per scene
-- ---------------------------------------------------------------------
CREATE TABLE scene_color_presets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scene_id            UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,
    name                TEXT,
    sort                INT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE scene_color_preset_items (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    preset_id           UUID NOT NULL REFERENCES scene_color_presets(id) ON DELETE CASCADE,
    element_key         TEXT NOT NULL,
    value               TEXT NOT NULL,
    sort                INT NOT NULL DEFAULT 0
);

CREATE INDEX idx_sc_preset_items_preset ON scene_color_preset_items(preset_id);

-- ---------------------------------------------------------------------
-- shared_colors — global colors across project (frshare comp)
-- ---------------------------------------------------------------------
CREATE TABLE shared_colors (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    element_key         TEXT NOT NULL,
    title               TEXT NOT NULL,
    icon                TEXT,
    attr_value          attr_value_kind NOT NULL DEFAULT 'fill',
    default_color       TEXT NOT NULL,

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

    UNIQUE (project_id, element_key)
);

CREATE INDEX idx_shared_colors_project ON shared_colors(project_id, sort);

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

-- ---------------------------------------------------------------------
-- shared_color_presets — project-wide palette themes
-- ---------------------------------------------------------------------
CREATE TABLE shared_color_presets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name                TEXT,
    sort                INT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE shared_color_preset_items (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    preset_id           UUID NOT NULL REFERENCES shared_color_presets(id) ON DELETE CASCADE,
    element_key         TEXT NOT NULL,
    value               TEXT NOT NULL,
    sort                INT NOT NULL DEFAULT 0
);

CREATE INDEX idx_shared_preset_items_preset ON shared_color_preset_items(preset_id);

-- ---------------------------------------------------------------------
-- shared_layers — global text/media layers across all scenes
-- ---------------------------------------------------------------------
CREATE TABLE shared_layers (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id                  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    key                         TEXT NOT NULL,
    title                       TEXT NOT NULL,
    localized_title             JSONB,
    hint                        TEXT,

    type                        content_element_type NOT NULL,
    default_value               TEXT,

    font_id                     UUID REFERENCES fonts(id),
    font_face                   TEXT,
    font_face_name              TEXT,
    font_size                   INT,
    default_font_size           INT,
    default_font_face           TEXT,
    is_font_changeable          BOOLEAN NOT NULL DEFAULT TRUE,
    is_font_size_changeable     BOOLEAN NOT NULL DEFAULT TRUE,
    justify                     justify_kind NOT NULL DEFAULT 'CENTER_JUSTIFY',
    can_justify                 BOOLEAN NOT NULL DEFAULT TRUE,
    position_in_container       INT NOT NULL DEFAULT 0,
    is_text_box                 BOOLEAN NOT NULL DEFAULT FALSE,
    max_size                    INT,
    direction_layer_key         TEXT,
    direction_layer_value       INT NOT NULL DEFAULT 0,

    video_support               BOOLEAN NOT NULL DEFAULT FALSE,
    min_duration_sec            NUMERIC(6,2),
    max_duration_sec            NUMERIC(6,2),
    width                       INT,
    height                      INT,
    thumbnail                   TEXT,

    mapped_list                 JSONB,
    counter_mode                TEXT,
    ai_input_type               ai_input_type NOT NULL DEFAULT 'None',

    is_hidden                   BOOLEAN NOT NULL DEFAULT FALSE,
    is_focused                  BOOLEAN NOT NULL DEFAULT FALSE,

    dp1_image                   TEXT,  dp1_title TEXT,
    dp2_image                   TEXT,  dp2_title TEXT,
    dp3_image                   TEXT,  dp3_title TEXT,
    dp4_image                   TEXT,  dp4_title TEXT,

    virtual_count               INT NOT NULL DEFAULT 1,
    sort                        INT NOT NULL DEFAULT 0,

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

    UNIQUE (project_id, key)
);

CREATE INDEX idx_shared_layers_project ON shared_layers(project_id, sort);

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

-- ---------------------------------------------------------------------
-- template_svg_previews — NEW: drop-an-image → traced SVG for live color preview
-- ---------------------------------------------------------------------
CREATE TABLE template_svg_previews (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID REFERENCES projects(id) ON DELETE CASCADE,
    scene_id            UUID REFERENCES scenes(id) ON DELETE CASCADE,
    -- Exactly one of project_id or scene_id should be set

    source_image_url    TEXT,                                   -- the dropped image
    svg_url             TEXT NOT NULL,                          -- in MinIO
    thumbnail_url       TEXT,

    -- Maps each SVG <path data-color-key="frd_bg"> to a color element
    color_zones         JSONB NOT NULL,
    -- Example: [{"element_key":"frd_bg","detected_color":"#1A1A2E","bbox":[x,y,w,h]}]

    width               INT,
    height              INT,
    generation_method   TEXT,                                   -- 'auto','manual','ai-assisted'
    generated_by_ai     BOOLEAN NOT NULL DEFAULT FALSE,
    quality_score       NUMERIC(3,2),                           -- 0-1 confidence

    created_by_user_id  UUID,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CHECK ((project_id IS NULL) <> (scene_id IS NULL))   -- exactly one
);

CREATE INDEX idx_svg_previews_project ON template_svg_previews(project_id);
CREATE INDEX idx_svg_previews_scene   ON template_svg_previews(scene_id);

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