-- t-paliad-349 (m/paliad#157): docforge slice 7 — pin an uploaded template
-- version onto a submission draft (generation-on-uploaded-templates).
--
-- A draft can now source its document from a docforge uploaded template
-- (paliad.template_versions) instead of a legacy Gitea base. template_version_id
-- is the snapshot pin (PRD §4 A3): the draft renders the exact carrier of the
-- version it was bound to, so a later template edit (which creates a new
-- version) doesn't shift an in-flight draft.
--
-- Nullable + additive: existing drafts keep template_version_id NULL and
-- render via their existing path (Composer base_id, or the v1 fallback).
-- The three sources are mutually exclusive in practice; the export path
-- checks template_version_id first, then base_id, then v1.
--
-- ON DELETE SET NULL: if the pinned version is removed, the draft detaches
-- and falls back rather than failing — same posture as base_id's
-- ON DELETE SET NULL.

ALTER TABLE paliad.submission_drafts
    ADD COLUMN IF NOT EXISTS template_version_id uuid
        REFERENCES paliad.template_versions(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS submission_drafts_template_version_idx
    ON paliad.submission_drafts (template_version_id)
    WHERE template_version_id IS NOT NULL;

COMMENT ON COLUMN paliad.submission_drafts.template_version_id IS
    't-paliad-349: pinned docforge template version (snapshot-at-create). NULL = render via base_id Composer path or v1 fallback.';
