-- =====================================================================
-- CONTENT SCHEMA — Part 4: Characters & Preset Stories
-- =====================================================================

SET search_path TO content, public;

-- ---------------------------------------------------------------------
-- scene_characters — character elements per scene
-- ---------------------------------------------------------------------
CREATE TABLE scene_characters (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scene_id            UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,
    key                 TEXT NOT NULL,
    name                TEXT NOT NULL,
    icon                TEXT,
    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_scene_chars_scene ON scene_characters(scene_id);

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

-- ---------------------------------------------------------------------
-- scene_character_controllers — animation properties of a character
-- ---------------------------------------------------------------------
CREATE TABLE scene_character_controllers (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scene_character_id      UUID NOT NULL REFERENCES scene_characters(id) ON DELETE CASCADE,
    name                    TEXT NOT NULL,
    key                     TEXT NOT NULL,
    default_value           TEXT,
    sort                    INT NOT NULL DEFAULT 0,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_scene_char_ctrl_char ON scene_character_controllers(scene_character_id);

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

-- ---------------------------------------------------------------------
-- scene_controller_options — discrete value options per controller
-- ---------------------------------------------------------------------
CREATE TABLE scene_controller_options (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    controller_id               UUID NOT NULL REFERENCES scene_character_controllers(id) ON DELETE CASCADE,
    name                        TEXT NOT NULL,
    icon                        TEXT,
    value                       TEXT NOT NULL,
    sort                        INT NOT NULL DEFAULT 0
);

CREATE INDEX idx_scene_ctrl_opt_ctrl ON scene_controller_options(controller_id);

-- ---------------------------------------------------------------------
-- project_character_controllers — project-wide character animation defs
-- ---------------------------------------------------------------------
CREATE TABLE project_character_controllers (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name                TEXT NOT NULL,
    key                 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, key)
);

CREATE INDEX idx_proj_char_ctrl_project ON project_character_controllers(project_id);

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

-- ---------------------------------------------------------------------
-- project_character_controller_options
-- ---------------------------------------------------------------------
CREATE TABLE project_character_controller_options (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    controller_id       UUID NOT NULL REFERENCES project_character_controllers(id) ON DELETE CASCADE,
    name                TEXT NOT NULL,
    icon                TEXT,
    value               TEXT NOT NULL,
    sort                INT NOT NULL DEFAULT 0
);

-- ---------------------------------------------------------------------
-- project_character_presets — named bundles of controller values
-- ---------------------------------------------------------------------
CREATE TABLE project_character_presets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    key                 UUID NOT NULL DEFAULT gen_random_uuid(),
    name                TEXT NOT NULL,
    icon                TEXT,
    sort                INT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_char_presets_project ON project_character_presets(project_id);

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

-- ---------------------------------------------------------------------
-- preset_character_controllers — controller values inside a preset
-- ---------------------------------------------------------------------
CREATE TABLE preset_character_controllers (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_preset_id     UUID NOT NULL REFERENCES project_character_presets(id) ON DELETE CASCADE,
    name                    TEXT NOT NULL,
    key                     TEXT NOT NULL,
    value                   TEXT NOT NULL,
    sort                    INT NOT NULL DEFAULT 0,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_preset_char_ctrl_preset ON preset_character_controllers(character_preset_id);

-- ---------------------------------------------------------------------
-- preset_stories — pre-made scene combinations
-- ---------------------------------------------------------------------
CREATE TABLE preset_stories (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name                TEXT NOT NULL,
    description         TEXT,
    demo                TEXT,
    music_id            UUID REFERENCES music_tracks(id),
    scenes_spa          TEXT,                          -- legacy serialized SPA (kept for migration)
    sort                INT NOT NULL DEFAULT 0,
    is_published        BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at          TIMESTAMPTZ
);

CREATE INDEX idx_preset_stories_project ON preset_stories(project_id) WHERE deleted_at IS NULL;

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

-- ---------------------------------------------------------------------
-- preset_scenes — which scenes appear in a preset story and order
-- ---------------------------------------------------------------------
CREATE TABLE preset_scenes (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    preset_story_id     UUID NOT NULL REFERENCES preset_stories(id) ON DELETE CASCADE,
    scene_id            UUID NOT NULL REFERENCES scenes(id) ON DELETE CASCADE,
    sort                INT NOT NULL DEFAULT 0,
    default_duration_sec NUMERIC(8,2),
    UNIQUE (preset_story_id, sort)
);

CREATE INDEX idx_preset_scenes_story ON preset_scenes(preset_story_id, sort);