//! SyncKit per-user devices: upsert a paired device, list a user's devices,
//! verify ownership, advance its sync cursor (with a last-seen touch), and
//! delete it.

use sqlx::PgPool;

use crate::db::enums::SyncPlatform;
use crate::db::models::DbSyncDevice;
use crate::db::{SyncAppId, SyncDeviceId, UserId};
use crate::error::Result;

// ── Sync Devices ──

/// Register or update a sync device.
///
/// Upserts on the `(app_id, user_id, device_name)` unique constraint.
/// On conflict (same device re-registering), updates the platform string
/// and bumps `last_seen_at` so stale-device detection stays accurate.
///
/// `client_version` is the SDK version off the request's User-Agent. `None`
/// means the request carried no recognisable one, and it leaves any previously
/// recorded version in place rather than blanking it: a client that stops
/// sending the header has not downgraded to unknown, it is just quiet.
#[tracing::instrument(skip_all)]
pub async fn upsert_sync_device(
    pool: &PgPool,
    app_id: SyncAppId,
    user_id: UserId,
    device_name: &str,
    platform: SyncPlatform,
    client_version: Option<&str>,
) -> Result<DbSyncDevice> {
    let device = sqlx::query_as::<_, DbSyncDevice>(
        r"
        INSERT INTO sync_devices (app_id, user_id, device_name, platform, client_version)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT (app_id, user_id, device_name)
        DO UPDATE SET
            platform = EXCLUDED.platform,
            last_seen_at = NOW(),
            client_version = COALESCE(EXCLUDED.client_version, sync_devices.client_version)
        RETURNING *
        ",
    )
    .bind(app_id)
    .bind(user_id)
    .bind(device_name)
    .bind(platform)
    .bind(client_version)
    .fetch_one(pool)
    .await?;

    Ok(device)
}

/// List all devices for a user within an app.
#[tracing::instrument(skip_all)]
pub async fn get_sync_devices(
    pool: &PgPool,
    app_id: SyncAppId,
    user_id: UserId,
) -> Result<Vec<DbSyncDevice>> {
    let devices = sqlx::query_as::<_, DbSyncDevice>(
        "SELECT * FROM sync_devices WHERE app_id = $1 AND user_id = $2 ORDER BY last_seen_at DESC LIMIT 100",
    )
    .bind(app_id)
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(devices)
}

/// Does this device belong to this `(app, user)`? Indexed point lookup on the
/// `sync_devices` PK + owner columns, the hot push/pull paths use this instead
/// of fetching every device and linear-scanning, so device verification stays
/// O(1) as a user's device count grows.
#[tracing::instrument(skip_all)]
pub async fn sync_device_belongs(
    pool: &PgPool,
    device_id: SyncDeviceId,
    app_id: SyncAppId,
    user_id: UserId,
) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM sync_devices WHERE id = $1 AND app_id = $2 AND user_id = $3)",
    )
    .bind(device_id)
    .bind(app_id)
    .bind(user_id)
    .fetch_one(pool)
    .await?;
    Ok(exists)
}

/// Mark a device seen and advance its pull cursor in one statement, the pull
/// hot path used to issue a separate touch and a separate cursor update; this
/// folds both into a single UPDATE. `GREATEST` keeps the cursor monotonic.
///
/// Also refreshes `client_version` from the request that triggered the pull, so
/// a device that upgrades reports its new version on its next sync instead of
/// waiting for a re-registration that may never come. `None` leaves the stored
/// value alone (see [`upsert_sync_device`]).
#[tracing::instrument(skip_all)]
pub async fn touch_and_advance_cursor(
    pool: &PgPool,
    device_id: SyncDeviceId,
    new_cursor: i64,
    client_version: Option<&str>,
) -> Result<()> {
    sqlx::query(
        "UPDATE sync_devices
         SET last_seen_at = NOW(),
             last_pulled_seq = GREATEST(last_pulled_seq, $2),
             client_version = COALESCE($3, client_version)
         WHERE id = $1",
    )
    .bind(device_id)
    .bind(new_cursor)
    .bind(client_version)
    .execute(pool)
    .await?;
    Ok(())
}

/// Delete a device by ID (only if owned by user within app).
#[tracing::instrument(skip_all)]
pub async fn delete_sync_device(
    pool: &PgPool,
    device_id: SyncDeviceId,
    app_id: SyncAppId,
    user_id: UserId,
) -> Result<bool> {
    let result =
        sqlx::query("DELETE FROM sync_devices WHERE id = $1 AND app_id = $2 AND user_id = $3")
            .bind(device_id)
            .bind(app_id)
            .bind(user_id)
            .execute(pool)
            .await?;

    Ok(result.rows_affected() > 0)
}

/// One row of the field-version readout: how many devices last synced on a
/// given SDK version, and when the most recent of them was seen.
#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
pub struct ClientVersionCount {
    /// SDK version, or `None` for devices that never reported one.
    pub client_version: Option<String>,
    /// Devices last seen on that version within the window.
    pub devices: i64,
    /// Most recent sync from any device on that version.
    pub last_seen_at: chrono::DateTime<chrono::Utc>,
}

/// Distribution of SyncKit versions across devices that synced within the last
/// `window_days`. Answers "which SyncKit is in the field", which is a different
/// question from "which SyncKit did we publish".
///
/// The window matters: without it, a laptop that synced once in 2025 and was
/// never opened again would keep an ancient version in the readout forever and
/// make the fleet look more stale than it is.
#[tracing::instrument(skip_all)]
pub async fn client_version_distribution(
    pool: &PgPool,
    window_days: i32,
) -> Result<Vec<ClientVersionCount>> {
    let rows = sqlx::query_as::<_, ClientVersionCount>(
        "SELECT client_version, COUNT(*) AS devices, MAX(last_seen_at) AS last_seen_at
         FROM sync_devices
         WHERE last_seen_at > NOW() - make_interval(days => $1)
         GROUP BY client_version
         ORDER BY devices DESC, client_version DESC NULLS LAST",
    )
    .bind(window_days)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Touch last_seen_at for a device.
#[tracing::instrument(skip_all)]
pub async fn touch_sync_device(pool: &PgPool, device_id: SyncDeviceId) -> Result<()> {
    sqlx::query("UPDATE sync_devices SET last_seen_at = NOW() WHERE id = $1")
        .bind(device_id)
        .execute(pool)
        .await?;

    Ok(())
}