-- t-paliad-315 (m/paliad#141): Composer Slice C — building blocks library.
--
-- Per the design at docs/design-submission-generator-v2-2026-05-26.md §4.4
-- and the Q2 / Q9 ratifications:
--
--   Q2 (m, 2026-05-26): building blocks are plain text paste sources.
--     No building_block_id reference is stored on submission_sections —
--     insertion is a one-way copy of content_md_<lang> into the section.
--     This table records the library; submission_sections doesn't know
--     where its content came from.
--
--   Q9 (m, 2026-05-26): four visibility tiers — private / team / firm
--     / global. Picker filtering and RLS SELECT predicate both honour
--     the tier. Tier upgrades (private → team/firm/global) go through
--     admin moderation in later slices; Slice C starts with admin-only
--     mutations (no user-initiated rows yet).
--
-- The _admin_versions companion table mirrors the email-templates
-- retention=20 audit history. It is INTERNAL to the admin editor —
-- not referenced from submission_sections, not exposed to the lawyer.
-- It exists so accidental delete + accidental overwrite are
-- recoverable.

CREATE TABLE IF NOT EXISTS paliad.submission_building_blocks (
    id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    slug              text NOT NULL,
    firm              text,                       -- e.g. 'HLC', NULL = cross-firm
    section_key       text NOT NULL,              -- which section kind this block fits
    proceeding_family text,                       -- 'de.inf.lg', NULL = any family
    title_de          text NOT NULL,
    title_en          text NOT NULL,
    description_de    text,
    description_en    text,
    content_md_de     text NOT NULL DEFAULT '',
    content_md_en     text NOT NULL DEFAULT '',
    author_id         uuid REFERENCES paliad.users(id) ON DELETE SET NULL,
    visibility        text NOT NULL,              -- 'private' | 'team' | 'firm' | 'global'
    is_published      bool NOT NULL DEFAULT false,
    created_at        timestamptz NOT NULL DEFAULT now(),
    updated_at        timestamptz NOT NULL DEFAULT now(),
    deleted_at        timestamptz,

    CONSTRAINT submission_building_blocks_visibility_check
        CHECK (visibility IN ('private', 'team', 'firm', 'global')),
    CONSTRAINT submission_building_blocks_unique_slug_per_firm
        UNIQUE (slug, firm)
);

CREATE INDEX IF NOT EXISTS submission_building_blocks_section_visibility_idx
    ON paliad.submission_building_blocks (section_key, visibility, firm, proceeding_family)
    WHERE deleted_at IS NULL AND is_published;

CREATE INDEX IF NOT EXISTS submission_building_blocks_author_idx
    ON paliad.submission_building_blocks (author_id)
    WHERE deleted_at IS NULL;

ALTER TABLE paliad.submission_building_blocks ENABLE ROW LEVEL SECURITY;

-- SELECT policy: coarse-grained RLS that admits every non-deleted
-- block to any authenticated user. The Go-side BuildingBlockService
-- applies the fine-grained tier predicate (private / team / firm /
-- global) using branding.Name + team-membership joins. This split
-- keeps the SQL simple and lets the tier semantics evolve in code
-- without RLS migrations.
--
-- The exception below is 'private': only the author sees their own
-- private rows. That's the hard line where a tier upgrade is
-- substantive enough to warrant DB-level enforcement.
DROP POLICY IF EXISTS submission_building_blocks_select ON paliad.submission_building_blocks;
CREATE POLICY submission_building_blocks_select
    ON paliad.submission_building_blocks FOR SELECT TO authenticated
    USING (
        deleted_at IS NULL
        AND (
            visibility <> 'private'
            OR author_id = auth.uid()
        )
    );

-- INSERT / UPDATE / DELETE intentionally absent — admin mutations
-- happen at the Go handler layer with explicit adminGate. RLS without
-- mutation policies denies them by default.

DROP TRIGGER IF EXISTS submission_building_blocks_set_updated_at ON paliad.submission_building_blocks;
CREATE TRIGGER submission_building_blocks_set_updated_at
    BEFORE UPDATE ON paliad.submission_building_blocks
    FOR EACH ROW EXECUTE FUNCTION paliad.tg_set_updated_at();

COMMENT ON TABLE paliad.submission_building_blocks IS
    't-paliad-315: Composer building-block library. Plain text paste sources for section content (no lineage tracked on sections per Q2 ratification). 4-tier visibility per Q9.';


-- _admin_versions: append-only history per block. Admin-side only;
-- not referenced from submission_sections. Retention 20 per block,
-- GCed in the same transaction as the Save (mirrors email-templates).
CREATE TABLE IF NOT EXISTS paliad.submission_building_block_admin_versions (
    id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    building_block_id uuid NOT NULL REFERENCES paliad.submission_building_blocks(id) ON DELETE CASCADE,
    content_md_de     text NOT NULL,
    content_md_en     text NOT NULL,
    title_de          text NOT NULL,
    title_en          text NOT NULL,
    edited_by         uuid REFERENCES paliad.users(id) ON DELETE SET NULL,
    note              text,
    created_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS submission_building_block_admin_versions_block_idx
    ON paliad.submission_building_block_admin_versions (building_block_id, created_at DESC);

ALTER TABLE paliad.submission_building_block_admin_versions ENABLE ROW LEVEL SECURITY;

-- Admin-only audit; the handler layer gates this via adminGate and
-- writes via SECURITY DEFINER paths or admin-role SQL. No RLS SELECT
-- policy exists, so non-admin users get an empty result set.

COMMENT ON TABLE paliad.submission_building_block_admin_versions IS
    't-paliad-315: append-only history per building block. Admin-side only; retention 20 rows per block, GCed at Save time.';
