use sqlx::PgPool;

use crate::db::models::*;
use crate::db::{SyncAppId, UserId};
use crate::error::Result;

// ── Sync Blobs ──

/// Get a blob by content hash for a user within an app.
#[tracing::instrument(skip_all)]
pub async fn get_sync_blob_by_hash(
    pool: &PgPool,
    app_id: SyncAppId,
    user_id: UserId,
    hash: &str,
) -> Result<Option<DbSyncBlob>> {
    let blob = sqlx::query_as::<_, DbSyncBlob>(
        "SELECT * FROM sync_blobs WHERE app_id = $1 AND user_id = $2 AND hash = $3",
    )
    .bind(app_id)
    .bind(user_id)
    .bind(hash)
    .fetch_optional(pool)
    .await?;

    Ok(blob)
}

/// Insert a sync blob, updating size on conflict (idempotent).
///
/// Uses `ON CONFLICT DO UPDATE` to keep `size_bytes` consistent with the
/// actual S3 object when concurrent uploads race. `key` is the developer-
/// defined SDK key from the session JWT; it never changes on re-upload of
/// the same hash so the conflict path leaves it alone.
#[tracing::instrument(skip_all)]
pub async fn create_sync_blob_idempotent(
    pool: &PgPool,
    app_id: SyncAppId,
    user_id: UserId,
    hash: &str,
    size_bytes: i64,
    s3_key: &str,
    key: &str,
) -> Result<()> {
    sqlx::query(
        r#"
        INSERT INTO sync_blobs (app_id, user_id, hash, size_bytes, s3_key, key)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (app_id, user_id, hash)
        DO UPDATE SET size_bytes = EXCLUDED.size_bytes
        "#,
    )
    .bind(app_id)
    .bind(user_id)
    .bind(hash)
    .bind(size_bytes)
    .bind(s3_key)
    .bind(key)
    .execute(pool)
    .await?;

    Ok(())
}

/// Get total blob storage used by a user for an app (in bytes).
///
/// Currently unused at the call-site level (Phase 5 moved cap enforcement to
/// the app-level counters in `sync_app_usage_current`), but kept around for
/// the eventual per-user "what's taking up space" dashboard view.
#[allow(dead_code)]
#[tracing::instrument(skip_all)]
pub async fn get_blob_storage_used(
    pool: &PgPool,
    app_id: SyncAppId,
    user_id: UserId,
) -> Result<i64> {
    let total: Option<i64> = sqlx::query_scalar(
        "SELECT SUM(size_bytes)::BIGINT FROM sync_blobs WHERE app_id = $1 AND user_id = $2",
    )
    .bind(app_id)
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(total.unwrap_or(0))
}

/// Count devices registered for a user/app pair.
#[tracing::instrument(skip_all)]
pub async fn count_sync_devices(
    pool: &PgPool,
    app_id: SyncAppId,
    user_id: UserId,
) -> Result<i64> {
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM sync_devices WHERE app_id = $1 AND user_id = $2",
    )
    .bind(app_id)
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(count)
}