//! SSH key CRUD and lookup queries.

use sqlx::PgPool;

use super::models::{DbSshKey, SshKeyUserLookup, SshKeyWithUsername};
use super::{SshKeyId, UserId};
use crate::error::Result;

/// Add an SSH public key for a user.
#[tracing::instrument(skip_all)]
pub async fn add_key(
    pool: &PgPool,
    user_id: UserId,
    public_key: &str,
    fingerprint: &str,
    label: &str,
) -> Result<DbSshKey> {
    let key = sqlx::query_as::<_, DbSshKey>(
        r#"
        INSERT INTO ssh_keys (user_id, public_key, fingerprint, label)
        VALUES ($1, $2, $3, $4)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(public_key)
    .bind(fingerprint)
    .bind(label)
    .fetch_one(pool)
    .await?;

    Ok(key)
}

/// List all SSH keys for a user, newest first.
#[tracing::instrument(skip_all)]
pub async fn list_keys_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbSshKey>> {
    let keys = sqlx::query_as::<_, DbSshKey>(
        "SELECT * FROM ssh_keys WHERE user_id = $1 ORDER BY created_at DESC LIMIT 100",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(keys)
}

/// Delete an SSH key. Returns false if not found or not owned by the user.
#[tracing::instrument(skip_all)]
pub async fn delete_key(pool: &PgPool, key_id: SshKeyId, user_id: UserId) -> Result<bool> {
    let result =
        sqlx::query("DELETE FROM ssh_keys WHERE id = $1 AND user_id = $2")
            .bind(key_id)
            .bind(user_id)
            .execute(pool)
            .await?;

    Ok(result.rows_affected() > 0)
}

/// Delete an SSH key by fingerprint. Returns false if not found or not owned by the user.
#[tracing::instrument(skip_all)]
pub async fn delete_key_by_fingerprint(
    pool: &PgPool,
    user_id: UserId,
    fingerprint: &str,
) -> Result<bool> {
    let result =
        sqlx::query("DELETE FROM ssh_keys WHERE user_id = $1 AND fingerprint = $2")
            .bind(user_id)
            .bind(fingerprint)
            .execute(pool)
            .await?;

    Ok(result.rows_affected() > 0)
}

/// Get all SSH keys with their owner's username, for authorized_keys rebuild.
#[tracing::instrument(skip_all)]
pub async fn get_all_keys_with_username(pool: &PgPool) -> Result<Vec<SshKeyWithUsername>> {
    let rows = sqlx::query_as::<_, SshKeyWithUsername>(
        r#"
        SELECT sk.id, sk.public_key, u.username::TEXT as username
        FROM ssh_keys sk
        JOIN users u ON u.id = sk.user_id
        ORDER BY sk.created_at
        "#,
    )
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Look up a user by their SSH key fingerprint. Used by the CLI SSH server
/// to authenticate connections.
#[tracing::instrument(skip_all)]
pub async fn lookup_user_by_fingerprint(
    pool: &PgPool,
    fingerprint: &str,
) -> Result<Option<SshKeyUserLookup>> {
    let row = sqlx::query_as::<_, SshKeyUserLookup>(
        r#"
        SELECT u.id AS user_id, u.username, u.display_name, u.email,
               u.creator_tier, u.can_create_projects,
               (u.suspended_at IS NOT NULL) AS suspended
        FROM ssh_keys sk
        JOIN users u ON u.id = sk.user_id
        WHERE sk.fingerprint = $1
        "#,
    )
    .bind(fingerprint)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}

/// Look up an SSH key by ID, returning the key and its owner. For git-auth.
#[tracing::instrument(skip_all)]
pub async fn get_key_with_user(
    pool: &PgPool,
    key_id: SshKeyId,
) -> Result<Option<(SshKeyId, UserId, String)>> {
    let row = sqlx::query_as::<_, (SshKeyId, UserId, String)>(
        r#"
        SELECT sk.id, sk.user_id, u.username::TEXT as username
        FROM ssh_keys sk
        JOIN users u ON u.id = sk.user_id
        WHERE sk.id = $1
        "#,
    )
    .bind(key_id)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}