flatrender/backend/db/migrations/12_render_jobs.sql

-- =====================================================================
-- RENDER SCHEMA — Part 2: Jobs, Frames, Snapshots, Exports
-- =====================================================================

SET search_path TO render, public;

CREATE TYPE render_step AS ENUM (
    'Queued','Preparing','TemplateCache','JsxGen','Music',
    'Rendering','Validating','Repairing','Optimisation','Video',
    'Mixing','Final','Uploading','Done','Failed','Cancelled'
);

CREATE TYPE price_kind AS ENUM ('Free','Preview','Cash','Plan','Snapshot','Reseller');

CREATE TYPE render_quality AS ENUM ('Low','Medium','High','Full','Lossless');

CREATE TYPE frame_job_status AS ENUM (
    'Pending','Rendering','Validated','Repairing','Converting','Done','Failed'
);

CREATE TYPE render_priority_queue AS ENUM (
    'snapshot','vip','paid','preview','mockup','voiceover'
);

-- ---------------------------------------------------------------------
-- render_jobs — top-level render task
-- ---------------------------------------------------------------------
CREATE TABLE render_jobs (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id                   UUID NOT NULL,
    user_id                     UUID NOT NULL,

    -- References (loose cross-schema)
    saved_project_id            UUID NOT NULL,
    original_project_id         UUID NOT NULL,
    project_name                TEXT,

    -- Job identity
    title                       TEXT,
    name                        TEXT,
    external_job_id             TEXT,                                   -- RabbitMQ message ID
    priority_queue              render_priority_queue NOT NULL,
    priority_score              INT NOT NULL DEFAULT 50,                -- 0-100, higher = sooner

    -- Pipeline state
    step                        render_step NOT NULL DEFAULT 'Queued',
    render_progress             INT NOT NULL DEFAULT 0  CHECK (render_progress BETWEEN 0 AND 100),
    convert_progress            INT NOT NULL DEFAULT 0,
    image_preview_b64           TEXT,                                   -- last frame thumbnail

    -- Pricing
    price_type                  price_kind NOT NULL,
    paid_price_minor            BIGINT NOT NULL DEFAULT 0,
    discount_code               TEXT,
    support_flatrender          BOOLEAN NOT NULL DEFAULT FALSE,

    -- Output config
    mode                        TEXT NOT NULL,                          -- FIX/FLEXIBLE/MockUp/MusicVisualizer
    quality                     render_quality NOT NULL DEFAULT 'High',
    resolution                  TEXT NOT NULL,                          -- FullHD/FourK
    r_height                    INT NOT NULL,
    frame_rate                  INT NOT NULL DEFAULT 30,
    is_60_fps                   BOOLEAN NOT NULL DEFAULT FALSE,
    duration_sec                NUMERIC(8,2) NOT NULL,
    export_duration_sec         NUMERIC(8,2),

    -- Audio (NEW)
    has_music                   BOOLEAN NOT NULL DEFAULT FALSE,
    has_sfx                     BOOLEAN NOT NULL DEFAULT FALSE,
    has_voiceover               BOOLEAN NOT NULL DEFAULT FALSE,
    music_volume                NUMERIC(4,3),
    sfx_volume                  NUMERIC(4,3),
    voiceover_volume            NUMERIC(4,3),

    -- Resource allocation
    render_node_count           INT NOT NULL DEFAULT 1,
    current_active_nodes        INT NOT NULL DEFAULT 0,
    region                      TEXT,                                   -- preferred region
    tell_me_when_done           BOOLEAN NOT NULL DEFAULT TRUE,

    -- Retry / recovery
    retry_count                 INT NOT NULL DEFAULT 0,
    max_retries                 INT NOT NULL DEFAULT 3,
    repair_attempts             INT NOT NULL DEFAULT 0,
    failed_message              TEXT,
    failed_at_step              render_step,

    -- File outputs (paths in MinIO; final URL goes in exports.path)
    render_folder               TEXT,                                   -- temp working dir
    output_folder               TEXT,                                   -- frames dir
    physical_render_folder      TEXT,                                   -- absolute path for nodes
    physical_output_folder      TEXT,
    target_replica_name         TEXT,

    -- Reference to result
    export_id                   UUID,

    -- Timing
    task_start_date             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    start_render_date           TIMESTAMPTZ,
    done_task_date              TIMESTAMPTZ,
    queued_at                   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at                  TIMESTAMPTZ,
    completed_at                TIMESTAMPTZ,

    metadata                    JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_render_jobs_user           ON render_jobs(user_id, created_at DESC);
CREATE INDEX idx_render_jobs_tenant         ON render_jobs(tenant_id, created_at DESC);
CREATE INDEX idx_render_jobs_step           ON render_jobs(step) WHERE step NOT IN ('Done','Failed','Cancelled');
CREATE INDEX idx_render_jobs_queue          ON render_jobs(priority_queue, priority_score DESC, queued_at)
       WHERE step = 'Queued';
CREATE INDEX idx_render_jobs_in_flight      ON render_jobs(started_at) WHERE step NOT IN ('Done','Failed','Cancelled','Queued');

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

-- ---------------------------------------------------------------------
-- frame_jobs — per-node frame-range assignments
-- ---------------------------------------------------------------------
CREATE TABLE frame_jobs (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    render_job_id               UUID NOT NULL REFERENCES render_jobs(id) ON DELETE CASCADE,
    node_id                     UUID NOT NULL REFERENCES render_nodes(id),

    -- Range
    start_frame                 INT NOT NULL,
    end_frame                   INT NOT NULL,
    collect_frame_count         INT NOT NULL,
    order_value                 INT NOT NULL DEFAULT 0,
    folder_name                 TEXT NOT NULL,                          -- "O0", "O1"...
    convert_url                 TEXT,

    -- Status
    status                      frame_job_status NOT NULL DEFAULT 'Pending',
    frames_rendered             INT NOT NULL DEFAULT 0,
    frames_validated            INT NOT NULL DEFAULT 0,

    -- Errors
    attempt                     INT NOT NULL DEFAULT 1,
    last_error                  TEXT,

    -- Outputs
    output_mp4_url              TEXT,                                   -- chunk MP4 after ffmpeg

    -- Timing
    assigned_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at                  TIMESTAMPTZ,
    last_progress_at            TIMESTAMPTZ,
    completed_at                TIMESTAMPTZ,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_frame_jobs_render ON frame_jobs(render_job_id, order_value);
CREATE INDEX idx_frame_jobs_node   ON frame_jobs(node_id, status);
CREATE INDEX idx_frame_jobs_stalled ON frame_jobs(last_progress_at) WHERE status = 'Rendering';

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

-- ---------------------------------------------------------------------
-- frame_repair_jobs — missing/corrupt frame repair tracking
-- ---------------------------------------------------------------------
CREATE TABLE frame_repair_jobs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    render_job_id       UUID NOT NULL REFERENCES render_jobs(id) ON DELETE CASCADE,
    node_id             UUID REFERENCES render_nodes(id),

    -- Range to repair
    start_frame         INT NOT NULL,
    end_frame           INT NOT NULL,
    missing_frames      INT[] NOT NULL DEFAULT '{}',
    corrupt_frames      INT[] NOT NULL DEFAULT '{}',

    attempt             INT NOT NULL DEFAULT 1,
    status              frame_job_status NOT NULL DEFAULT 'Pending',
    error               TEXT,

    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at        TIMESTAMPTZ
);

CREATE INDEX idx_frame_repair_job ON frame_repair_jobs(render_job_id, attempt);

-- ---------------------------------------------------------------------
-- snapshots — single-frame scene previews (new feature)
-- ---------------------------------------------------------------------
CREATE TABLE snapshots (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,
    saved_project_id    UUID NOT NULL,
    scene_key           TEXT NOT NULL,
    frame_number        INT NOT NULL,

    -- Cache key — same inputs = same output
    inputs_hash         TEXT NOT NULL,

    -- Status
    status              TEXT NOT NULL DEFAULT 'Pending',         -- Pending/Rendering/Done/Failed
    render_node_id      UUID REFERENCES render_nodes(id),

    -- Output
    image_url           TEXT,
    thumbnail_url       TEXT,
    width               INT,
    height              INT,
    size_bytes          BIGINT,

    -- Timing
    requested_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at        TIMESTAMPTZ,
    duration_ms         INT,
    expires_at          TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '7 days',

    error_message       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_snapshots_cache ON snapshots(saved_project_id, scene_key, frame_number, inputs_hash)
       WHERE status = 'Done';
CREATE INDEX idx_snapshots_user        ON snapshots(user_id, requested_at DESC);
CREATE INDEX idx_snapshots_expire      ON snapshots(expires_at) WHERE status = 'Done';

-- ---------------------------------------------------------------------
-- exports — final rendered output records
-- ---------------------------------------------------------------------
CREATE TYPE export_create_type AS ENUM ('Render','Upload','Snapshot','Reupload');
CREATE TYPE export_file_type   AS ENUM ('Video','Image','Audio','GIF','PDF');

CREATE TABLE exports (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,

    saved_project_id    UUID NOT NULL,
    project_id          UUID NOT NULL,
    render_job_id       UUID REFERENCES render_jobs(id) ON DELETE SET NULL,

    -- Output
    image               TEXT,                           -- thumbnail
    path                TEXT NOT NULL,                  -- main file URL
    file_extension      TEXT NOT NULL DEFAULT 'mp4',
    file_type           export_file_type NOT NULL DEFAULT 'Video',

    render_quality      render_quality NOT NULL,
    create_type         export_create_type NOT NULL DEFAULT 'Render',
    size_bytes          BIGINT NOT NULL,
    duration_sec        NUMERIC(8,2),
    width               INT,
    height              INT,

    -- Lifecycle
    produce_date        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    auto_delete_date    TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '30 days',
    delete_notified     BOOLEAN NOT NULL DEFAULT FALSE,

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

CREATE INDEX idx_exports_user           ON exports(user_id, produce_date DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_exports_tenant         ON exports(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_exports_saved_project  ON exports(saved_project_id);
CREATE INDEX idx_exports_auto_delete    ON exports(auto_delete_date) WHERE deleted_at IS NULL;

-- Now wire FK back to render_jobs
ALTER TABLE render_jobs
  ADD CONSTRAINT fk_render_jobs_export
  FOREIGN KEY (export_id) REFERENCES exports(id) ON DELETE SET NULL;

-- ---------------------------------------------------------------------
-- export_files — mockup mode produces multiple images per export
-- ---------------------------------------------------------------------
CREATE TABLE export_files (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    export_id           UUID NOT NULL REFERENCES exports(id) ON DELETE CASCADE,
    user_id             UUID NOT NULL,

    name                TEXT,
    thumbnail           TEXT,
    path                TEXT NOT NULL,
    size_bytes          BIGINT NOT NULL,
    file_type           export_file_type NOT NULL DEFAULT 'Image',
    width               INT,
    height              INT,
    sort                INT NOT NULL DEFAULT 0,

    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_export_files_export ON export_files(export_id, sort);

-- ---------------------------------------------------------------------
-- render_progress_events — WebSocket fan-out source (short-lived)
-- ---------------------------------------------------------------------
CREATE TABLE render_progress_events (
    id                  BIGSERIAL PRIMARY KEY,
    render_job_id       UUID NOT NULL REFERENCES render_jobs(id) ON DELETE CASCADE,
    step                render_step NOT NULL,
    progress            INT NOT NULL,
    current_frame       INT,
    total_frames        INT,
    eta_seconds         INT,
    preview_b64         TEXT,
    message             TEXT,
    emitted_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_progress_events_job ON render_progress_events(render_job_id, emitted_at DESC);
-- Cleanup: keep last N per job via cron, drop > 7 days