//! SQL queries for checking record existence
//!
//! These queries check if records exist in their respective collection tables.

use tokio_postgres::GenericClient;

pub type QueryResult<T> = eyre::Result<T>;

/// Check if a post exists and should skip fetching
///
/// Returns true if the post exists with any status except 'stub'.
/// - stub: Return false (needs fetching to upgrade to complete)
/// - missing: Return true (permanently unfetchable after retries, skip)
/// - complete: Return true (already have data, skip)
/// - deleted: Return true (soft-deleted, skip)
/// - forbidden: Return true (access denied, skip)
pub async fn post_exists<C: GenericClient>(conn: &C, did: &str, rkey: i64) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM posts p
             INNER JOIN actors a ON p.actor_id = a.id
             WHERE a.did = $1 AND p.rkey = $2 AND p.status != 'stub'::post_status",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a like exists
pub async fn like_exists<C: GenericClient>(conn: &C, did: &str, rkey: i64) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM posts p
             INNER JOIN actors a ON a.id = ANY(p.like_actor_ids)
             WHERE a.did = $1 AND $2 = ANY(p.like_rkeys)
               AND p.like_rkeys[array_position(p.like_actor_ids, a.id)] = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a repost exists
pub async fn repost_exists<C: GenericClient>(conn: &C, did: &str, rkey: i64) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM reposts r
             INNER JOIN actors a ON r.actor_id = a.id
             WHERE a.did = $1 AND r.rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a follow exists in the actor's following[] array
pub async fn follow_exists<C: GenericClient>(conn: &C, did: &str, rkey: i64) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors a, unnest(COALESCE(a.following, ARRAY[]::follow_record[])) AS f
             WHERE a.did = $1 AND (f).rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a block exists in the actor's blocks[] array
pub async fn block_exists<C: GenericClient>(conn: &C, did: &str, rkey: i64) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors a, unnest(COALESCE(a.blocks, ARRAY[]::block_record[])) AS b
             WHERE a.did = $1 AND (b).rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a profile exists
pub async fn profile_exists<C: GenericClient>(conn: &C, did: &str) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors
             WHERE did = $1 AND profile_cid IS NOT NULL",
            &[&did],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a feedgen exists and should skip fetching
///
/// Returns true if the feedgen exists with any status except 'stub'.
/// - stub: Return false (needs fetching)
/// - missing: Return true (permanently unfetchable, skip)
/// - complete: Return true (already have data, skip)
/// - deleted: Return true (soft-deleted, skip)
pub async fn feedgen_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: &str,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM feedgens f
             INNER JOIN actors a ON f.actor_id = a.id
             WHERE a.did = $1 AND f.rkey = $2 AND f.status != 'stub'::feedgen_status",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a list exists and should skip fetching
///
/// Returns true if the list exists with any status except 'stub'.
/// - stub: Return false (needs fetching)
/// - missing: Return true (permanently unfetchable, skip)
/// - complete: Return true (already have data, skip)
/// - deleted: Return true (soft-deleted, skip)
/// - forbidden: Return true (access denied, skip)
pub async fn list_exists<C: GenericClient>(conn: &C, did: &str, rkey: &str) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM lists l
             INNER JOIN actors a ON l.actor_id = a.id
             WHERE a.did = $1 AND l.rkey = $2 AND l.status != 'stub'::record_status",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a list item exists
pub async fn list_item_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM list_items li
             INNER JOIN actors a ON li.actor_id = a.id
             WHERE a.did = $1 AND li.rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a list block exists in the actor's list_blocks array
pub async fn list_block_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors a, unnest(a.list_blocks) lb
             WHERE a.did = $1 AND (lb).rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a threadgate exists (DENORMALIZED)
///
/// DENORMALIZED: Checks posts.threadgate_allow instead of separate threadgates table.
pub async fn threadgate_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM posts p
             INNER JOIN actors a ON p.actor_id = a.id
             WHERE a.did = $1 AND p.rkey = $2
               AND p.threadgate_allow IS NOT NULL",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a postgate exists (DENORMALIZED)
///
/// DENORMALIZED: Checks posts.postgate_rules instead of separate postgates table.
pub async fn postgate_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM posts p
             INNER JOIN actors a ON p.actor_id = a.id
             WHERE a.did = $1 AND p.rkey = $2
               AND p.postgate_rules IS NOT NULL",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a starterpack exists and should skip fetching
///
/// Returns true if the starterpack exists with any status except 'stub'.
/// - stub: Return false (needs fetching)
/// - missing: Return true (permanently unfetchable, skip)
/// - complete: Return true (already have data, skip)
/// - deleted: Return true (soft-deleted, skip)
/// - forbidden: Return true (access denied, skip)
pub async fn starterpack_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM starterpacks sp
             INNER JOIN actors a ON sp.actor_id = a.id
             WHERE a.did = $1 AND sp.rkey = $2 AND sp.status != 'stub'::record_status",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a verification exists
pub async fn verification_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM verification v
             INNER JOIN actors a ON v.actor_id = a.id
             WHERE a.did = $1 AND v.rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a labeler exists and should skip fetching
///
/// DENORMALIZED: Labelers are now stored directly on actors table (labeler_status, labeler_cid, etc)
///
/// Returns true if the labeler exists with any status except 'stub'.
/// - stub: Return false (needs fetching)
/// - missing: Return true (permanently unfetchable, skip)
/// - complete: Return true (already have data, skip)
/// - deleted: Return true (soft-deleted, skip)
pub async fn labeler_exists<C: GenericClient>(conn: &C, did: &str) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors
             WHERE did = $1 AND labeler_status IS NOT NULL AND labeler_status != 'stub'::labeler_status",
            &[&did],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a status exists
pub async fn status_exists<C: GenericClient>(conn: &C, did: &str) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors
             WHERE did = $1 AND status_cid IS NOT NULL",
            &[&did],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a bookmark exists in the actor's bookmarks[] array
pub async fn bookmark_exists<C: GenericClient>(
    conn: &C,
    did: &str,
    rkey: i64,
) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors a, unnest(COALESCE(a.bookmarks, ARRAY[]::bookmark_record[])) AS b
             WHERE a.did = $1 AND (b).rkey = $2",
            &[&did, &rkey],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a chat declaration exists
pub async fn chat_decl_exists<C: GenericClient>(conn: &C, did: &str) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors
             WHERE did = $1 AND chat_allow_incoming IS NOT NULL",
            &[&did],
        )
        .await?;
    Ok(row.is_some())
}

/// Check if a notification declaration exists
pub async fn notif_decl_exists<C: GenericClient>(conn: &C, did: &str) -> QueryResult<bool> {
    let row = conn
        .query_opt(
            "SELECT 1 FROM actors
             WHERE did = $1 AND notif_decl_allow_subscriptions IS NOT NULL",
            &[&did],
        )
        .await?;
    Ok(row.is_some())
}