//! Storage module for LFG (Looking For Group) records.
//!
//! This module provides query helpers for LFG records stored in the `atproto_records` table.
//! For writes, use `atproto_record_upsert()` and `atproto_record_delete()` from the
//! `atproto_record` module.
//!
//! Records are stored as `AtprotoRecord` and can be deserialized to `Lfg` using:
//! ```ignore
//! let lfg: Lfg = serde_json::from_value(record.record.0.clone())?;
//! ```

use super::StoragePool;
use super::atproto_record::AtprotoRecord;
use super::errors::StorageError;
use crate::atproto::lexicon::lfg::NSID;

/// Get the active LFG record for a DID from atproto_records.
///
/// Returns the most recent active LFG record for the given DID, or None if
/// no active record exists.
pub async fn lfg_get_active_by_did(
    pool: &StoragePool,
    did: &str,
) -> Result<Option<AtprotoRecord>, StorageError> {
    let record = sqlx::query_as::<_, AtprotoRecord>(
        r#"
        SELECT aturi, did, cid, collection, indexed_at, record
        FROM atproto_records
        WHERE did = $1
          AND collection = $2
          AND (record->>'active')::boolean = true
          AND (record->>'endsAt')::timestamptz > NOW()
        ORDER BY indexed_at DESC
        LIMIT 1
        "#,
    )
    .bind(did)
    .bind(NSID)
    .fetch_optional(pool)
    .await
    .map_err(StorageError::UnableToExecuteQuery)?;

    Ok(record)
}

/// Get an LFG record by AT-URI from atproto_records.
pub async fn lfg_get_by_aturi(
    pool: &StoragePool,
    aturi: &str,
) -> Result<Option<AtprotoRecord>, StorageError> {
    let record = sqlx::query_as::<_, AtprotoRecord>(
        r#"
        SELECT aturi, did, cid, collection, indexed_at, record
        FROM atproto_records
        WHERE aturi = $1
          AND collection = $2
        "#,
    )
    .bind(aturi)
    .bind(NSID)
    .fetch_optional(pool)
    .await
    .map_err(StorageError::UnableToExecuteQuery)?;

    Ok(record)
}

/// Get all LFG records for a DID (including inactive/expired).
///
/// Used for tag history lookups.
pub async fn lfg_get_all_by_did(
    pool: &StoragePool,
    did: &str,
    limit: i64,
) -> Result<Vec<AtprotoRecord>, StorageError> {
    let records = sqlx::query_as::<_, AtprotoRecord>(
        r#"
        SELECT aturi, did, cid, collection, indexed_at, record
        FROM atproto_records
        WHERE did = $1
          AND collection = $2
        ORDER BY indexed_at DESC
        LIMIT $3
        "#,
    )
    .bind(did)
    .bind(NSID)
    .bind(limit)
    .fetch_all(pool)
    .await
    .map_err(StorageError::UnableToExecuteQuery)?;

    Ok(records)
}