-- Insert/update starterpack with self-contained schema (no records table)
-- Parameters: $1=actor_id(INT4), $2=cid(bytea), $3=record(unused), $4=name, $5=description,
--             $6=description_facets, $7=list_actor_id(INT4), $8=list_rkey(TEXT),
--             $9=feed_actor_ids(INT4[]), $10=feed_rkeys(TEXT[]), $11=rkey(INT8)
-- Note: created_at is derived from TID rkey
-- Note: actor_id is provided by caller after calling get_actor_id (ensures zero sequence waste)
-- NOTE: URI parsing and feedgen natural key resolution now done in Rust
WITH unused_params AS (
    SELECT $3::jsonb as record  -- Type hint for unused parameter
),
upserted_starterpack AS (
    INSERT INTO starterpacks (actor_id, rkey, cid, owner_actor_id, name, description, description_facets, list_actor_id, list_rkey, status)
    SELECT
        $1,  -- actor_id (provided by caller)
        $11,  -- rkey (INT8)
        $2::bytea,  -- cid (embedded)
        $1,  -- owner_actor_id (same as actor_id for starterpacks)
        $4,
        $5,
        $6,
        $7,  -- list_actor_id (already resolved, may be stub)
        $8,  -- list_rkey (already resolved, may be stub)
        'complete'::record_status
    ON CONFLICT (actor_id, rkey) DO UPDATE SET
        cid=EXCLUDED.cid,
        name=EXCLUDED.name,
        description=EXCLUDED.description,
        description_facets=EXCLUDED.description_facets,
        list_actor_id=EXCLUDED.list_actor_id,
        list_rkey=EXCLUDED.list_rkey,
        status=EXCLUDED.status
    RETURNING rkey, XMAX::text::int as xmax_result
),
-- Delete old feeds before inserting new ones (only if starterpack was actually inserted)
deleted_feeds AS (
    DELETE FROM starterpack_feeds
    WHERE starterpack_id = (SELECT rkey FROM upserted_starterpack)
      AND EXISTS (SELECT 1 FROM upserted_starterpack)
),
-- Insert new feeds from resolved feedgen natural keys
-- Feeds are passed as two parallel arrays: feed_actor_ids and feed_rkeys
inserted_feeds AS (
    INSERT INTO starterpack_feeds (starterpack_id, feed_actor_id, feed_rkey, position)
    SELECT
        (SELECT rkey FROM upserted_starterpack),
        feed_actor_ids[idx],
        feed_rkeys[idx],
        (idx - 1)::smallint as position
    FROM generate_series(1, COALESCE(array_length($9::integer[], 1), 0)) AS idx,
         LATERAL (SELECT $9::integer[] AS feed_actor_ids, $10::text[] AS feed_rkeys) AS arrays
    WHERE $9 IS NOT NULL AND $10 IS NOT NULL
      AND EXISTS (SELECT 1 FROM upserted_starterpack)
    ON CONFLICT DO NOTHING
)
SELECT xmax_result FROM upserted_starterpack