-- Insert post with direct actor_id and parent/root/embedded post natural keys
-- Self-contained schema: No records table, metadata embedded directly in posts
-- Parameters: $1=actor_id(INT4), $2=cid, $3=content(BYTEA, compressed),
--             $4=languages(text[], up to 3), $5=tags,
--             $6=parent_post_actor_id, $7=parent_post_rkey,
--             $8=root_post_actor_id, $9=root_post_rkey,
--             $10=embed, $11=embed_subtype, $12=violates_threadgate, $13=rkey(INT8),
--             $14=tokens(text[], for search)
--             $15=ext_embed(post_ext_embed), $16=video_embed(post_video_embed),
--             $17=embedded_post_actor_id, $18=embedded_post_rkey,
--             $19=record_detached(BOOL),
--             $20-$23=image_1, image_2, image_3, image_4 (post_image_embed),
--             $24-$31=facet_1 through facet_8 (post_facet_embed),
--             $32=mentions(int[])
-- Note: created_at is derived from TID rkey
-- Actor must already exist before calling this function
-- Parent and root posts must exist before calling this function (use ensure_post_exists first)
WITH embed_types AS (
    SELECT
        CASE WHEN $10::text IS NOT NULL THEN $10::text::embed_type ELSE NULL::embed_type END as embed_type,
        CASE WHEN $11::text IS NOT NULL THEN $11::text::embed_type ELSE NULL::embed_type END as embed_subtype
)
INSERT INTO posts (
    actor_id, rkey, cid, content, langs, tags,
    parent_post_actor_id, parent_post_rkey,
    root_post_actor_id, root_post_rkey,
    embed_type, embed_subtype,
    violates_threadgate, status, tokens,
    ext_embed, video_embed,
    embedded_post_actor_id, embedded_post_rkey,
    record_detached,
    image_1, image_2, image_3, image_4,
    facet_1, facet_2, facet_3, facet_4, facet_5, facet_6, facet_7, facet_8,
    mentions
)
SELECT
    $1,  -- actor_id (passed directly as INT4)
    $13,  -- rkey (INT8)
    $2::bytea,  -- cid (embedded)
    $3,  -- content (compressed BYTEA)
    -- Convert text array to language_code array (supports up to 3 languages per AT Protocol spec)
    -- Only include valid language codes that can be cast to the enum
    COALESCE(
        (SELECT array_agg(lang::language_code ORDER BY ordinality)
         FROM unnest($4::text[]) WITH ORDINALITY AS t(lang, ordinality)
         WHERE lang IS NOT NULL
           AND lang::text IN (SELECT enumlabel FROM pg_enum WHERE enumtypid = 'language_code'::regtype)
         LIMIT 3),
        ARRAY[]::language_code[]
    ),  -- langs (array of up to 3 language codes)
    $5,  -- tags
    $6,  -- parent_post_actor_id
    $7,  -- parent_post_rkey
    $8,  -- root_post_actor_id
    $9,  -- root_post_rkey
    (SELECT embed_type FROM embed_types),  -- embed_type
    (SELECT embed_subtype FROM embed_types),  -- embed_subtype
    $12,  -- violates_threadgate
    'complete'::post_status,  -- status (this is a complete post with full content)
    $14,  -- tokens (text array, pre-tokenized in Rust)
    $15,  -- ext_embed
    $16,  -- video_embed
    $17,  -- embedded_post_actor_id
    $18,  -- embedded_post_rkey
    $19,  -- record_detached
    $20, $21, $22, $23,  -- image_1, image_2, image_3, image_4
    $24, $25, $26, $27, $28, $29, $30, $31,  -- facet_1 through facet_8
    $32  -- mentions
-- Posts are immutable - cannot be edited in AT Protocol
-- However, if a stub/missing post already exists, upgrade it to complete
-- (missing posts can become complete if we later receive the actual data)
ON CONFLICT (actor_id, rkey) DO UPDATE
SET cid = EXCLUDED.cid,  -- Ensure CID is correct (stubs may have placeholder)
    content = EXCLUDED.content,
    langs = EXCLUDED.langs,
    tags = EXCLUDED.tags,
    parent_post_actor_id = EXCLUDED.parent_post_actor_id,
    parent_post_rkey = EXCLUDED.parent_post_rkey,
    root_post_actor_id = EXCLUDED.root_post_actor_id,
    root_post_rkey = EXCLUDED.root_post_rkey,
    embed_type = EXCLUDED.embed_type,
    embed_subtype = EXCLUDED.embed_subtype,
    violates_threadgate = EXCLUDED.violates_threadgate,
    status = 'complete'::post_status,
    tokens = EXCLUDED.tokens,  -- Update tokens when upgrading from stub/missing
    ext_embed = EXCLUDED.ext_embed,
    video_embed = EXCLUDED.video_embed,
    embedded_post_actor_id = EXCLUDED.embedded_post_actor_id,
    embedded_post_rkey = EXCLUDED.embedded_post_rkey,
    record_detached = EXCLUDED.record_detached,
    image_1 = EXCLUDED.image_1,
    image_2 = EXCLUDED.image_2,
    image_3 = EXCLUDED.image_3,
    image_4 = EXCLUDED.image_4,
    facet_1 = EXCLUDED.facet_1,
    facet_2 = EXCLUDED.facet_2,
    facet_3 = EXCLUDED.facet_3,
    facet_4 = EXCLUDED.facet_4,
    facet_5 = EXCLUDED.facet_5,
    facet_6 = EXCLUDED.facet_6,
    facet_7 = EXCLUDED.facet_7,
    facet_8 = EXCLUDED.facet_8,
    mentions = EXCLUDED.mentions
WHERE posts.status IN ('stub'::post_status, 'missing'::post_status)
RETURNING actor_id, rkey