use sqlx::Row;
use sqlx::PgPool;
use uuid::Uuid;

use crate::domain::CurrentUser;

pub async fn load_current_user_by_session(
    db_pool: &PgPool,
    session_id: Uuid,
) -> Result<Option<CurrentUser>, sqlx::Error> {
    let record = sqlx::query(
        r#"
        SELECT users.did, users.handle
        FROM sessions
        JOIN users ON users.did = sessions.user_did
        WHERE sessions.id = $1
        AND sessions.expires_at > NOW()
        "#,
    )
    .bind(session_id)
    .fetch_optional(db_pool)
    .await?;

    let Some(record) = record else {
        return Ok(None);
    };

    let did: String = record.try_get("did")?;
    let handle: Option<String> = record.try_get("handle")?;

    Ok(Some(CurrentUser { did, handle }))
}

/// Inserts or updates the user's handle.
pub async fn upsert_user(
    db_pool: &PgPool,
    did: &str,
    handle: Option<&str>,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO users (did, handle)
        VALUES ($1, $2)
        ON CONFLICT (did)
        DO UPDATE SET handle = EXCLUDED.handle
        "#,
    )
    .bind(did)
    .bind(handle)
    .execute(db_pool)
    .await?;

    Ok(())
}

/// Stores OAuth tokens for a user.
pub async fn store_tokens(
    db_pool: &PgPool,
    user_did: &str,
    access_token: &str,
    refresh_token: Option<&str>,
    token_type: &str,
    scopes: &str,
    expires_in: u32,
    issuer: &str,
    dpop_private_key: &str,
) -> Result<(), sqlx::Error> {
    let expires_at = chrono::Utc::now() + chrono::Duration::seconds(i64::from(expires_in));
    sqlx::query(
        r#"
        INSERT INTO oauth_tokens (
            user_did,
            access_token,
            refresh_token,
            token_type,
            scopes,
            expires_at,
            issuer,
            dpop_private_key,
            updated_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())
        ON CONFLICT (user_did)
        DO UPDATE SET
            access_token = EXCLUDED.access_token,
            refresh_token = EXCLUDED.refresh_token,
            token_type = EXCLUDED.token_type,
            scopes = EXCLUDED.scopes,
            expires_at = EXCLUDED.expires_at,
            issuer = EXCLUDED.issuer,
            dpop_private_key = EXCLUDED.dpop_private_key,
            updated_at = NOW()
        "#,
    )
    .bind(user_did)
    .bind(access_token)
    .bind(refresh_token)
    .bind(token_type)
    .bind(scopes)
    .bind(expires_at)
    .bind(issuer)
    .bind(dpop_private_key)
    .execute(db_pool)
    .await?;

    Ok(())
}

/// Creates a new session for a user.
pub async fn create_session(
    db_pool: &PgPool,
    session_id: Uuid,
    user_did: &str,
    expires_at: chrono::DateTime<chrono::Utc>,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO sessions (id, user_did, expires_at)
        VALUES ($1, $2, $3)
        "#,
    )
    .bind(session_id)
    .bind(user_did)
    .bind(expires_at)
    .execute(db_pool)
    .await?;
    Ok(())
}

/// Deletes a session by id.
pub async fn delete_session(db_pool: &PgPool, session_id: Uuid) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        DELETE FROM sessions WHERE id = $1
        "#,
    )
    .bind(session_id)
    .execute(db_pool)
    .await?;
    Ok(())
}