SET search_path TO atlas, public;

-- 水印派生引用不可变模板版本；编辑草稿确认前不改写原图或 SKU 选图。
CREATE TABLE media_watermark_derivatives (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    source_asset_id UUID NOT NULL,
    watermark_template_id UUID NOT NULL,
    watermark_template_version INTEGER NOT NULL CHECK (watermark_template_version > 0),
    output_asset_id UUID,
    status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'succeeded', 'failed')),
    error_code TEXT,
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    FOREIGN KEY (workspace_id, source_asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT,
    FOREIGN KEY (workspace_id, output_asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT,
    FOREIGN KEY (workspace_id, watermark_template_id, watermark_template_version)
        REFERENCES watermark_template_versions(workspace_id, template_id, version) ON DELETE RESTRICT,
    UNIQUE (workspace_id, id),
    CHECK ((status = 'succeeded') = (output_asset_id IS NOT NULL)),
    CHECK (status <> 'failed' OR error_code IS NOT NULL)
);
CREATE INDEX ix_media_watermark_derivatives_source
    ON media_watermark_derivatives (workspace_id, source_asset_id, created_at DESC);

ALTER TABLE media_watermark_derivatives ENABLE ROW LEVEL SECURITY;
ALTER TABLE media_watermark_derivatives FORCE ROW LEVEL SECURITY;
CREATE POLICY workspace_isolation ON media_watermark_derivatives
    USING (workspace_id = app_current_workspace_id())
    WITH CHECK (workspace_id = app_current_workspace_id());