//! Integration tests for community (bookmarks) and starter pack SQL operations
//!
//! Tests coverage for:
//! - `bookmark_upsert` (discriminated union for subject type)
//! - `bookmark_delete`
//! - `starter_pack_upsert` (list resolution, insert and update paths)
//! - `starter_pack_delete`

mod common;

use chrono::Utc;
use common::{test_cid, test_pool};
use consumer::database_writer::EventSource;
use consumer::db::operations::{community, graph, starter_pack};
use consumer::types::records::{AppBskyGraphList, AppBskyGraphStarterPack, StarterPackFeedItem};
use lexica::community_lexicon::bookmarks::Bookmark;
use eyre::WrapErr;

// Helper function to extract rkey from AT URI
fn extract_rkey(uri: &str) -> String {
    uri.split('/').next_back().unwrap_or("").to_string()
}

// ========================================
// Bookmark Tests
// ========================================

#[tokio::test]
async fn test_bookmark_upsert_post_subject() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors exist
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser1")
        .await
        .wrap_err("Failed to ensure bookmark actor")?;
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:postauthor1")
        .await
        .wrap_err("Failed to ensure post author")?;

    // Create a post to bookmark
    let post_uri = "at://did:plc:postauthor1/app.bsky.feed.post/3l7mkz4lmk22q";
    let (post_author_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:postauthor1").await?;
    let post = consumer::types::records::AppBskyFeedPost {
        text: "Test post".to_string(),
        facets: None,
        reply: None,
        embed: None,
        langs: None,
        labels: None,
        tags: None,
        created_at: chrono::Utc::now()};
    consumer::db::operations::feed::post_insert(&tx,
        post_author_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(post_uri)).unwrap(),
        test_cid(),
        post,
        EventSource::Tap,
    )
    .await
    .wrap_err("Failed to insert post for bookmark test")?;

    let bookmark = Bookmark {
        subject: "at://did:plc:postauthor1/app.bsky.feed.post/3l7mkz4lmk22q".to_string(),
        tags: vec!["favorite".to_string(), "tech".to_string()],
        created_at: Utc::now()};

    let (actor_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser1").await?;
    let result =
        community::bookmark_upsert(&tx, actor_id, parakeet_db::models::tid_to_i64("3l7mkz4lmk22r").unwrap(), bookmark).await;

    assert_eq!(result.wrap_err("Operation failed")?, 1, "Should insert 1 bookmark");

    // Verify bookmark was created with correct natural key reference
    // Bookmarks are stored as bookmark_record[] array on actors table
    let bookmark_data = tx
        .query_one(
            "SELECT (b).post_actor_id, (b).post_rkey
             FROM actors, UNNEST(bookmarks) AS b
             WHERE did = $1 AND (b).rkey = tid_to_i64($2)",
            &[&"did:plc:bookmarkuser1", &"3l7mkz4lmk22r"],
        )
        .await
        .wrap_err("query bookmark data")?;

    assert_eq!(
        bookmark_data.get::<_, i32>(0),
        post_author_id,
        "Bookmark should reference correct post author"
    );
    assert_eq!(
        bookmark_data.get::<_, i64>(1),
        parakeet_db::models::tid_to_i64(&extract_rkey(post_uri)).unwrap(),
        "Bookmark should reference correct post rkey"
    );

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}

#[tokio::test]
async fn test_bookmark_upsert_idempotent() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors and create post
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser2")
        .await
        .wrap_err("Failed to ensure bookmark actor")?;
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:postauthor2")
        .await
        .wrap_err("Failed to ensure post author")?;

    let post_uri = "at://did:plc:postauthor2/app.bsky.feed.post/3l7mkz4lmk22s";
    let (post_author_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:postauthor2").await?;
    let post = consumer::types::records::AppBskyFeedPost {
        text: "Test post 2".to_string(),
        facets: None,
        reply: None,
        embed: None,
        langs: None,
        labels: None,
        tags: None,
        created_at: chrono::Utc::now()};
    consumer::db::operations::feed::post_insert(&tx,
        post_author_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(post_uri)).unwrap(),
        test_cid(),
        post,
        EventSource::Tap,
    )
    .await
    .wrap_err("Failed to insert post for bookmark idempotency test")?;

    let bookmark = Bookmark {
        subject: "at://did:plc:postauthor2/app.bsky.feed.post/3l7mkz4lmk22s".to_string(),
        tags: vec![],
        created_at: Utc::now()};

    let (actor_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser2").await?;
    // Insert bookmark first time
    let result1 = community::bookmark_upsert(
        &tx,
        actor_id,
        parakeet_db::models::tid_to_i64("3l7mkz4lmk22t").unwrap(),
        bookmark.clone(),
    )
    .await;
    assert_eq!(result1.unwrap(), 1, "First insert should return 1");

    // Insert same bookmark again (same rkey)
    let result2 =
        community::bookmark_upsert(&tx, actor_id, parakeet_db::models::tid_to_i64("3l7mkz4lmk22t").unwrap(), bookmark).await;
    assert_eq!(
        result2.unwrap(),
        0,
        "Duplicate insert should return 0 (ON CONFLICT DO NOTHING)"
    );

    // Verify only one bookmark exists (count from bookmarks[] array)
    let count = tx
        .query_one(
            "SELECT array_length(bookmarks, 1)
             FROM actors
             WHERE did = $1",
            &[&"did:plc:bookmarkuser2"],
        )
        .await
        .wrap_err("count bookmarks")?
        .get::<_, Option<i32>>(0)
        .unwrap_or(0);

    assert_eq!(count, 1, "Should have exactly 1 bookmark");

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}

#[tokio::test]
async fn test_bookmark_delete() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors and create post
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser3")
        .await
        .wrap_err("Failed to ensure bookmark actor")?;
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:postauthor3")
        .await
        .wrap_err("Failed to ensure post author")?;

    let post_uri = "at://did:plc:postauthor3/app.bsky.feed.post/3l7mkz4lmk22u";
    let (post_author_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:postauthor3").await?;
    let post = consumer::types::records::AppBskyFeedPost {
        text: "Test post 3".to_string(),
        facets: None,
        reply: None,
        embed: None,
        langs: None,
        labels: None,
        tags: None,
        created_at: chrono::Utc::now()};
    consumer::db::operations::feed::post_insert(&tx,
        post_author_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(post_uri)).unwrap(),
        test_cid(),
        post,
        EventSource::Tap,
    )
    .await
    .wrap_err("Failed to insert post for bookmark delete test")?;

    let bookmark = Bookmark {
        subject: "at://did:plc:postauthor3/app.bsky.feed.post/3l7mkz4lmk22u".to_string(),
        tags: vec!["delete-me".to_string()],
        created_at: Utc::now()};

    let (actor_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser3").await?;
    community::bookmark_upsert(&tx, actor_id, parakeet_db::models::tid_to_i64("3l7mkz4lmk22v").unwrap(), bookmark)
        .await
        .wrap_err("INSERT bookmark for deletion test")?;

    // Delete the bookmark
    let (deleter_actor_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:bookmarkuser3").await?;
    let result = community::bookmark_delete(&tx, parakeet_db::models::tid_to_i64("3l7mkz4lmk22v").unwrap(), deleter_actor_id).await;
    assert_eq!(result.unwrap(), 1, "Should delete 1 bookmark");

    // Verify bookmark was deleted from bookmarks[] array
    let exists = tx
        .query_opt(
            "SELECT 1
             FROM actors, UNNEST(COALESCE(bookmarks, ARRAY[]::bookmark_record[])) AS b
             WHERE did = $1 AND (b).rkey = tid_to_i64($2)",
            &[&"did:plc:bookmarkuser3", &"3l7mkz4lmk22v"],
        )
        .await
        .wrap_err("check bookmark deleted")?;

    assert!(exists.is_none(), "Bookmark should be deleted");

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}

// ========================================
// Starter Pack Tests
// ========================================

#[tokio::test]
async fn test_starter_pack_upsert_insert() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors exist
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner1")
        .await
        .wrap_err("Failed to ensure starterpack owner")?;

    // Create a list for the starter pack (starter packs require a list to exist)
    let list_uri = "at://did:plc:starterpackowner1/app.bsky.graph.list/3l7mkz4lmk22w";
    let (list_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner1").await?;
    let list = AppBskyGraphList {
        purpose: "app.bsky.graph.defs#curatelist".to_string(),
        name: "Starter Pack List".to_string(),
        description: Some("A list for the starter pack".to_string()),
        description_facets: None,
        avatar: None,
        labels: None,
        created_at: Utc::now()};

    graph::list_upsert(
        &tx,
        list_owner_id,
        &extract_rkey(list_uri),
        test_cid(),
        list,
    )
    .await
    .wrap_err("INSERT list for starter pack")?;

    let starter_pack = AppBskyGraphStarterPack {
        name: "Welcome to Bluesky".to_string(),
        description: Some("Great people to follow".to_string()),
        description_facets: None,
        list: "at://did:plc:starterpackowner1/app.bsky.graph.list/3l7mkz4lmk22w".to_string(),
        feeds: Some(vec![
            StarterPackFeedItem {
                uri: "at://did:plc:someone/app.bsky.feed.generator/feed1".to_string()},
            StarterPackFeedItem {
                uri: "at://did:plc:someone/app.bsky.feed.generator/feed2".to_string()},
        ]),
        created_at: Utc::now()};

    let at_uri = "at://did:plc:starterpackowner1/app.bsky.graph.starterpack/3l7mkz4lmk22x";
    let (sp_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner1").await?;

    let result = starter_pack::starter_pack_upsert(
        &tx,
        sp_owner_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(at_uri)).unwrap(),
        test_cid(),
        starter_pack,
    )
    .await;

    assert!(
        result.wrap_err("Operation failed")?,
        "Should return true for INSERT (XMAX=0)"
    );

    // Verify starter pack was created
    let pack_data = tx
        .query_one(
            "SELECT sp.name, sp.description, sp.list_actor_id IS NOT NULL
             FROM starterpacks sp
             INNER JOIN actors a ON sp.actor_id = a.id
             WHERE a.did = $1 AND sp.rkey = tid_to_i64($2)",
            &[&"did:plc:starterpackowner1", &"3l7mkz4lmk22x"],
        )
        .await
        .wrap_err("query starter pack data")?;

    assert_eq!(
        pack_data.get::<_, String>(0),
        "Welcome to Bluesky",
        "Name should match"
    );
    assert_eq!(
        pack_data.get::<_, Option<String>>(1),
        Some("Great people to follow".to_string()),
        "Description should match"
    );
    assert!(
        pack_data.get::<_, bool>(2),
        "List ID should be resolved"
    );

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}

#[tokio::test]
async fn test_starter_pack_upsert_update() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors exist
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner2")
        .await
        .wrap_err("Failed to ensure starterpack owner")?;

    // Create a list
    let list_uri = "at://did:plc:starterpackowner2/app.bsky.graph.list/3l7mkz4lmk22y";
    let (list_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner2").await?;
    let list = AppBskyGraphList {
        purpose: "app.bsky.graph.defs#curatelist".to_string(),
        name: "List for Update Test".to_string(),
        description: None,
        description_facets: None,
        avatar: None,
        labels: None,
        created_at: Utc::now()};

    graph::list_upsert(
        &tx,
        list_owner_id,
        &extract_rkey(list_uri),
        test_cid(),
        list,
    )
    .await
    .wrap_err("INSERT list for starter pack update test")?;

    let at_uri = "at://did:plc:starterpackowner2/app.bsky.graph.starterpack/3l7mkz4lmk22z";
    let (sp_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner2").await?;

    // Insert initial starter pack
    let starter_pack1 = AppBskyGraphStarterPack {
        name: "Initial Name".to_string(),
        description: Some("Initial description".to_string()),
        description_facets: None,
        list: "at://did:plc:starterpackowner2/app.bsky.graph.list/3l7mkz4lmk22y".to_string(),
        feeds: None,
        created_at: Utc::now()};

    let result1 = starter_pack::starter_pack_upsert(
        &tx,
        sp_owner_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(at_uri)).unwrap(),
        test_cid(),
        starter_pack1,
    )
    .await;
    assert!(result1.unwrap(), "First insert should return true");

    // Update the starter pack
    let starter_pack2 = AppBskyGraphStarterPack {
        name: "Updated Name".to_string(),
        description: Some("Updated description".to_string()),
        description_facets: None,
        list: "at://did:plc:starterpackowner2/app.bsky.graph.list/3l7mkz4lmk22y".to_string(),
        feeds: Some(vec![StarterPackFeedItem {
            uri: "at://did:plc:someone/app.bsky.feed.generator/newfeed".to_string()}]),
        created_at: Utc::now()};

    let result2 = starter_pack::starter_pack_upsert(
        &tx,
        sp_owner_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(at_uri)).unwrap(),
        test_cid(),
        starter_pack2,
    )
    .await;
    assert!(
        !result2.unwrap(),
        "Update should return false (XMAX>0)"
    );

    // Verify starter pack was updated
    let pack_data = tx
        .query_one(
            "SELECT sp.name, sp.description
             FROM starterpacks sp
             INNER JOIN actors a ON sp.actor_id = a.id
             WHERE a.did = $1 AND sp.rkey = tid_to_i64($2)",
            &[&"did:plc:starterpackowner2", &"3l7mkz4lmk22z"],
        )
        .await
        .wrap_err("query updated starter pack")?;

    assert_eq!(
        pack_data.get::<_, String>(0),
        "Updated Name",
        "Name should be updated"
    );
    assert_eq!(
        pack_data.get::<_, Option<String>>(1),
        Some("Updated description".to_string()),
        "Description should be updated"
    );

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}

#[tokio::test]
async fn test_starter_pack_delete() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors exist
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner3")
        .await
        .wrap_err("Failed to ensure starterpack owner")?;

    // Create a list
    let list_uri = "at://did:plc:starterpackowner3/app.bsky.graph.list/3l7mkz4lmk232";
    let (list_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner3").await?;
    let list = AppBskyGraphList {
        purpose: "app.bsky.graph.defs#curatelist".to_string(),
        name: "List for Delete Test".to_string(),
        description: None,
        description_facets: None,
        avatar: None,
        labels: None,
        created_at: Utc::now()};

    graph::list_upsert(
        &tx,
        list_owner_id,
        &extract_rkey(list_uri),
        test_cid(),
        list,
    )
    .await
    .wrap_err("INSERT list for starter pack delete test")?;

    let at_uri = "at://did:plc:starterpackowner3/app.bsky.graph.starterpack/3l7mkz4lmk233";
    let (sp_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner3").await?;

    // Insert starter pack
    let starter_pack = AppBskyGraphStarterPack {
        name: "To Be Deleted".to_string(),
        description: None,
        description_facets: None,
        list: "at://did:plc:starterpackowner3/app.bsky.graph.list/3l7mkz4lmk232".to_string(),
        feeds: None,
        created_at: Utc::now()};

    starter_pack::starter_pack_upsert(
        &tx,
        sp_owner_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(at_uri)).unwrap(),
        test_cid(),
        starter_pack,
    )
    .await
    .wrap_err_with(|| format!("INSERT starter pack: {}", at_uri))?;

    // Delete the starter pack
    let result = starter_pack::starter_pack_delete(&tx, sp_owner_id, parakeet_db::models::tid_to_i64(&extract_rkey(at_uri)).unwrap()).await;
    assert_eq!(result.unwrap(), 1, "Should delete 1 starter pack");

    // Verify starter pack was deleted
    let exists = tx
        .query_opt(
            "SELECT 1 FROM starterpacks sp
             INNER JOIN actors a ON sp.actor_id = a.id
             WHERE a.did = $1 AND sp.rkey = tid_to_i64($2)",
            &[&"did:plc:starterpackowner3", &"3l7mkz4lmk233"],
        )
        .await
        .wrap_err("check starter pack deleted")?;

    assert!(exists.is_none(), "Starter pack should be deleted");

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}

#[tokio::test]
async fn test_starter_pack_with_feeds_insertion() -> eyre::Result<()> {
    let pool = test_pool();
    let mut conn = pool.get().await.wrap_err("Failed to get connection")?;
    let tx = conn
        .transaction()
        .await
        .wrap_err("Failed to start transaction")?;

    // Ensure actors exist
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner4")
        .await
        .wrap_err("Failed to ensure starterpack owner")?;
    consumer::db::operations::feed::get_actor_id(&tx, "did:plc:feedowner")
        .await
        .wrap_err("Failed to ensure feed owner")?;
    let (service_actor_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:web:feedservice.example")
        .await
        .wrap_err("Failed to ensure feed service")?;

    // Create feedgens that will be referenced by the starter pack
    let (feedgen_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:feedowner").await?;

    let feedgen1_uri = "at://did:plc:feedowner/app.bsky.feed.generator/feed1";
    let feedgen1 = consumer::types::records::AppBskyFeedGenerator {
        did: "did:web:feedservice.example".to_string(),
        display_name: "Feed 1".to_string(),
        description: Some("First feed".to_string()),
        description_facets: None,
        avatar: None,
        accepts_interactions: None,
        content_mode: None,
        labels: None,
        created_at: Utc::now(),
    };

    consumer::db::operations::feed::feedgen_upsert(
        &tx,
        feedgen_owner_id,
        &extract_rkey(feedgen1_uri),
        test_cid(),
        service_actor_id,
        feedgen1,
    )
    .await
    .wrap_err("INSERT first feedgen")?;

    let feedgen2_uri = "at://did:plc:feedowner/app.bsky.feed.generator/feed2";
    let feedgen2 = consumer::types::records::AppBskyFeedGenerator {
        did: "did:web:feedservice.example".to_string(),
        display_name: "Feed 2".to_string(),
        description: Some("Second feed".to_string()),
        description_facets: None,
        avatar: None,
        accepts_interactions: None,
        content_mode: None,
        labels: None,
        created_at: Utc::now(),
    };

    consumer::db::operations::feed::feedgen_upsert(
        &tx,
        feedgen_owner_id,
        &extract_rkey(feedgen2_uri),
        test_cid(),
        service_actor_id,
        feedgen2,
    )
    .await
    .wrap_err("INSERT second feedgen")?;

    let feedgen3_uri = "at://did:plc:feedowner/app.bsky.feed.generator/feed3";
    let feedgen3 = consumer::types::records::AppBskyFeedGenerator {
        did: "did:web:feedservice.example".to_string(),
        display_name: "Feed 3".to_string(),
        description: Some("Third feed".to_string()),
        description_facets: None,
        avatar: None,
        accepts_interactions: None,
        content_mode: None,
        labels: None,
        created_at: Utc::now(),
    };

    consumer::db::operations::feed::feedgen_upsert(
        &tx,
        feedgen_owner_id,
        &extract_rkey(feedgen3_uri),
        test_cid(),
        service_actor_id,
        feedgen3,
    )
    .await
    .wrap_err("INSERT third feedgen")?;

    // Create a list for the starter pack
    let list_uri = "at://did:plc:starterpackowner4/app.bsky.graph.list/3l7mkz4lmk234";
    let (list_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner4").await?;
    let list = AppBskyGraphList {
        purpose: "app.bsky.graph.defs#curatelist".to_string(),
        name: "List with Feeds".to_string(),
        description: None,
        description_facets: None,
        avatar: None,
        labels: None,
        created_at: Utc::now(),
    };

    graph::list_upsert(
        &tx,
        list_owner_id,
        &extract_rkey(list_uri),
        test_cid(),
        list,
    )
    .await
    .wrap_err("INSERT list for feeds test")?;

    // Create a starter pack with 3 feeds in specific order
    let starter_pack = AppBskyGraphStarterPack {
        name: "Pack with Feeds".to_string(),
        description: Some("Testing feed insertion and ordering".to_string()),
        description_facets: None,
        list: "at://did:plc:starterpackowner4/app.bsky.graph.list/3l7mkz4lmk234".to_string(),
        feeds: Some(vec![
            StarterPackFeedItem {
                uri: "at://did:plc:feedowner/app.bsky.feed.generator/feed1".to_string(),
            },
            StarterPackFeedItem {
                uri: "at://did:plc:feedowner/app.bsky.feed.generator/feed2".to_string(),
            },
            StarterPackFeedItem {
                uri: "at://did:plc:feedowner/app.bsky.feed.generator/feed3".to_string(),
            },
        ]),
        created_at: Utc::now(),
    };

    let at_uri = "at://did:plc:starterpackowner4/app.bsky.graph.starterpack/3l7mkz4lmk235";
    let (sp_owner_id, _, _) = consumer::db::operations::feed::get_actor_id(&tx, "did:plc:starterpackowner4").await?;

    let result = starter_pack::starter_pack_upsert(
        &tx,
        sp_owner_id,
        parakeet_db::models::tid_to_i64(&extract_rkey(at_uri)).unwrap(),
        test_cid(),
        starter_pack,
    )
    .await;

    assert!(result.wrap_err("Starter pack insert failed")?, "Should insert starter pack");

    // Verify starter pack was created
    let pack_exists: bool = tx
        .query_one(
            "SELECT EXISTS(
                SELECT 1 FROM starterpacks sp
                INNER JOIN actors a ON sp.actor_id = a.id
                WHERE a.did = $1 AND sp.rkey = tid_to_i64($2)
            )",
            &[&"did:plc:starterpackowner4", &"3l7mkz4lmk235"],
        )
        .await
        .wrap_err("query starter pack existence")?
        .get(0);
    assert!(pack_exists, "Starter pack should exist");

    // Verify feeds were inserted with correct count
    // Phase 6: starterpacks and feedgens use natural keys, no synthetic ids
    let feed_count: i64 = tx
        .query_one(
            "SELECT COUNT(*)
             FROM starterpack_feeds spf
             INNER JOIN starterpacks sp ON spf.starterpack_id = sp.rkey
             INNER JOIN actors a ON sp.actor_id = a.id
             WHERE a.did = $1 AND sp.rkey = tid_to_i64($2)",
            &[&"did:plc:starterpackowner4", &"3l7mkz4lmk235"],
        )
        .await
        .wrap_err("query feed count")?
        .get(0);
    assert_eq!(feed_count, 3, "Should have 3 feeds");

    // Verify feeds are in correct order with correct positions
    let feeds: Vec<(i16, String)> = tx
        .query(
            "SELECT spf.position, spf.feed_rkey
             FROM starterpack_feeds spf
             INNER JOIN starterpacks sp ON spf.starterpack_id = sp.rkey
             INNER JOIN actors a ON sp.actor_id = a.id
             WHERE a.did = $1 AND sp.rkey = tid_to_i64($2)
             ORDER BY spf.position",
            &[&"did:plc:starterpackowner4", &"3l7mkz4lmk235"],
        )
        .await
        .wrap_err("query feeds with positions")?
        .iter()
        .map(|row| (row.get(0), row.get(1)))
        .collect();

    assert_eq!(feeds.len(), 3, "Should retrieve 3 feeds");
    assert_eq!(feeds[0].0, 0, "First feed should have position 0");
    assert_eq!(feeds[0].1, "feed1", "First feed should be feed1");
    assert_eq!(feeds[1].0, 1, "Second feed should have position 1");
    assert_eq!(feeds[1].1, "feed2", "Second feed should be feed2");
    assert_eq!(feeds[2].0, 2, "Third feed should have position 2");
    assert_eq!(feeds[2].1, "feed3", "Third feed should be feed3");

    tx.rollback().await.wrap_err("Failed to rollback")?;
    Ok(())
}