//! PostgreSQL implementations of the auth session transaction traits.
//!
//! This module contains the concrete database logic for creating, pinging, and deleting
//! auth sessions in a PostgreSQL database. Each function is annotated with the
//! [`#[db_transaction]`](crate::db_transaction) attribute macro, which implements the
//! corresponding trait (defined in [`tx_definitions`](super::tx_definitions)) on
//! [`AuthPostGresDescriptor<T>`](crate::dal::connections::AuthPostGresDescriptor).
//!
//! # How it works
//!
//! The `#[db_transaction(AuthPostGresDescriptor, TraitName)]` macro expands each function
//! into an `impl<T: YieldPostGresPool> TraitName for AuthPostGresDescriptor<T>` block.
//! Inside the function body, `T::yield_pool()` provides a reference to the connection pool
//! (`&'static Pool<Postgres>`), which is used to execute queries.
//!
//! # Schema
//!
//! All queries target the `saps` schema. The table and supporting `ping` function can be
//! created by calling [`AuthSession::generate_migration_sql()`](super::model::AuthSession::generate_migration_sql).
//!
//! # Generic `U: UserRole`
//!
//! The `role` column is stored as `VARCHAR`. When inserting, the role is converted
//! to a string via `ToString`. When reading, the raw string is parsed back into `U` via
//! `TryFrom<String>` (both provided by the [`UserRole`] trait).
//! This means any role enum generated by [`construct_checks!`](crate::construct_checks) works
//! out of the box.
//!
//! # Example
//!
//! ```ignore
//! use saps::dal::connections::{AuthPostGresDescriptor, LivePostGresPool};
//! use saps::auth::dal::tx_definitions::{CreateAuthSession, PingAuthSession, DeleteAuthSession};
//! use saps::auth::dal::model::AuthSession;
//!
//! // Create a new session
//! let session = AuthSession::new(MyRole::Admin);
//! let created = AuthPostGresDescriptor::<LivePostGresPool>::create_auth_session(session).await?;
//!
//! // Ping the session (extends last_interacted, returns None if expired)
//! let active = AuthPostGresDescriptor::<LivePostGresPool>::ping_auth_session::<MyRole>(30, created.id).await?;
//!
//! // Delete the session
//! let deleted = AuthPostGresDescriptor::<LivePostGresPool>::delete_auth_session(created.id).await?;
//! ```

use super::{
    model::AuthSession,
    tx_definitions::{
        CreateAuthSession, DeleteAuthSession, DeleteAuthSessionMetaKey,
        DeleteAuthSessionsByMetaKey, GetAllAuthSessions, GetAuthSession, GetAuthSessionByMetaKey,
        GetAuthSessionsByMetaKey, PingAuthSession, UpdateAuthSessionMeta, UpsertAuthSessionMetaKey,
        UpsertAuthSessionsMetaKeyByMeta,
    },
};
use crate::{
    auth::token::checks::UserRole, dal::connections::AuthPostGresDescriptor, db_transaction,
};
use serde_json::Value;

/// Inserts a new auth session into `saps.auth_sessions`.
///
/// The caller constructs an [`AuthSession`] (typically via [`AuthSession::new`]) and this
/// function persists it to the database. The `role` is stored as a lowercase string
/// via `ToString`. The full row is returned from the `RETURNING` clause and parsed back
/// into an `AuthSession<U>`.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The insert fails (e.g. constraint violation)
/// - The returned `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, CreateAuthSession)]
async fn create_auth_session<U: UserRole>(session: AuthSession<U>) -> AuthSession<U> {
    let pool = T::yield_pool();
    let row = sqlx::query(
        r#"
        INSERT INTO saps.auth_sessions (id, role, date_created, last_interacted, meta)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id, role, date_created, last_interacted, meta
        "#,
    )
    .bind(session.id)
    .bind(session.role.to_string())
    .bind(session.date_created)
    .bind(session.last_interacted)
    .bind(session.meta)
    .fetch_one(pool)
    .await?;
    AuthSession::from_row(&row).map_err(|e| sqlx::Error::Protocol(e.message))
}

/// Pings an existing auth session by calling the `saps.ping` PostgreSQL function.
///
/// The `saps.ping(p_minutes, p_session_id)` function performs the following atomically:
/// 1. If the session's `last_interacted` is older than `p_minutes` ago, it **deletes**
///    the expired session and returns `NULL`.
/// 2. If the session is still valid, it **updates** `last_interacted` to `NOW()` and
///    returns the updated row.
/// 3. If no session exists for the given id, it returns `NULL`.
///
/// This function maps `NULL` to `None` and a valid row to `Some(AuthSession<U>)`.
///
/// # Arguments
///
/// * `minutes` - The session timeout in minutes. Sessions older than this are expired.
/// * `session_id` - The UUID of the session to ping.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The query fails
/// - The returned `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, PingAuthSession)]
async fn ping_auth_session<U: UserRole>(minutes: i32, session_id: &str) -> Option<AuthSession<U>> {
    let pool = T::yield_pool();
    let parsed_id: uuid::Uuid = session_id
        .parse()
        .map_err(|e| sqlx::Error::Protocol(format!("invalid session_id UUID: {}", e)))?;
    let row = sqlx::query("SELECT * FROM saps.ping($1, $2)")
        .bind(minutes)
        .bind(parsed_id)
        .fetch_optional(pool)
        .await?;
    match row {
        Some(r) => {
            // The ping function returns NULL as a composite row when the session is
            // expired or missing. Postgres still delivers a row with all-null columns,
            // so we check the id column before attempting to parse.
            let id: Option<uuid::Uuid> = sqlx::Row::try_get(&r, "id")
                .map_err(|e: sqlx::Error| sqlx::Error::Protocol(e.to_string()))?;
            if id.is_none() {
                return Ok(None);
            }
            let session =
                AuthSession::from_row(&r).map_err(|e| sqlx::Error::Protocol(e.message))?;
            Ok(Some(session))
        }
        None => Ok(None),
    }
}

/// Returns all auth sessions from `saps.auth_sessions`.
///
/// Each row's `role` VARCHAR is parsed back into `U` via `TryFrom<String>`.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The query fails
/// - Any row's `role` string cannot be parsed into `U`
#[db_transaction(AuthPostGresDescriptor, GetAllAuthSessions)]
async fn get_all_auth_sessions<U: UserRole>() -> Vec<AuthSession<U>> {
    let pool = T::yield_pool();
    let rows =
        sqlx::query("SELECT id, role, date_created, last_interacted, meta FROM saps.auth_sessions")
            .fetch_all(pool)
            .await?;
    let mut sessions = Vec::with_capacity(rows.len());
    for row in &rows {
        sessions.push(AuthSession::from_row(row).map_err(|e| sqlx::Error::Protocol(e.message))?);
    }
    Ok(sessions)
}

/// Fetches a single auth session by its id.
///
/// Returns `Some(AuthSession<U>)` if a row exists with the given id, or `None`
/// if no session is found. The `role` VARCHAR is parsed back into `U` via
/// `TryFrom<String>`.
///
/// # Arguments
///
/// * `session_id` - The UUID of the session to fetch, as a string.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The `session_id` is not a valid UUID
/// - The query fails
/// - The returned `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, GetAuthSession)]
async fn get_auth_session<U: UserRole>(session_id: &str) -> Option<AuthSession<U>> {
    let pool = T::yield_pool();
    let parsed_id: uuid::Uuid = session_id
        .parse()
        .map_err(|e| sqlx::Error::Protocol(format!("invalid session_id UUID: {}", e)))?;
    let row = sqlx::query(
        "SELECT id, role, date_created, last_interacted, meta FROM saps.auth_sessions WHERE id = $1"
    )
        .bind(parsed_id)
        .fetch_optional(pool)
        .await?;
    match row {
        Some(r) => {
            let session =
                AuthSession::from_row(&r).map_err(|e| sqlx::Error::Protocol(e.message))?;
            Ok(Some(session))
        }
        None => Ok(None),
    }
}

/// Replaces the `meta` JSON for an existing auth session.
///
/// Updates the `meta` column of the row matching `session_id` with the supplied
/// JSON value. No error is returned if the session does not exist; the update
/// simply affects zero rows.
///
/// # Arguments
///
/// * `session_id` - The UUID of the session to update, as a string.
/// * `meta` - The JSON value to store in the `meta` column.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The `session_id` is not a valid UUID
/// - The query fails
#[db_transaction(AuthPostGresDescriptor, UpdateAuthSessionMeta)]
async fn update_auth_session_meta(session_id: &str, meta: Value) -> () {
    let pool = T::yield_pool();
    let parsed_id: uuid::Uuid = session_id
        .parse()
        .map_err(|e| sqlx::Error::Protocol(format!("invalid session_id UUID: {}", e)))?;
    sqlx::query("UPDATE saps.auth_sessions SET meta = $1 WHERE id = $2")
        .bind(meta)
        .bind(parsed_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// Sets a single key in the `meta` JSON of an existing auth session, leaving
/// all other keys intact, and returns the updated session.
///
/// If `meta` is `NULL`, it is treated as an empty object before the key is
/// inserted. If the key already exists, its value is overwritten; all other
/// keys are preserved. The merge happens server-side via `jsonb_set` so the
/// update is atomic with respect to concurrent writers. Returns
/// `Some(AuthSession<U>)` with the post-update row, or `None` if no session
/// exists for `session_id`.
///
/// # Arguments
///
/// * `session_id` - The UUID of the session to update, as a string.
/// * `key` - The top-level key to insert or overwrite in the `meta` object.
/// * `value` - The JSON value to associate with `key`.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The `session_id` is not a valid UUID
/// - The query fails
/// - The returned `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, UpsertAuthSessionMetaKey)]
async fn upsert_auth_session_meta_key<U: UserRole>(
    session_id: &str,
    key: &str,
    value: Value,
) -> Option<AuthSession<U>> {
    let pool = T::yield_pool();
    let parsed_id: uuid::Uuid = session_id
        .parse()
        .map_err(|e| sqlx::Error::Protocol(format!("invalid session_id UUID: {}", e)))?;
    let row = sqlx::query(
        "UPDATE saps.auth_sessions \
         SET meta = jsonb_set(COALESCE(meta, '{}'::jsonb), ARRAY[$1], $2, true) \
         WHERE id = $3 \
         RETURNING id, role, date_created, last_interacted, meta",
    )
        .bind(key)
        .bind(value)
        .bind(parsed_id)
        .fetch_optional(pool)
        .await?;
    match row {
        Some(r) => {
            let session =
                AuthSession::from_row(&r).map_err(|e| sqlx::Error::Protocol(e.message))?;
            Ok(Some(session))
        }
        None => Ok(None),
    }
}

/// Removes a single top-level key from the `meta` JSON of an existing auth
/// session, leaving all other keys intact, and returns the updated session.
///
/// Uses the PostgreSQL `jsonb - text` operator, which strips the named key
/// from a JSONB object. If `meta` is `NULL` the column stays `NULL`. If the
/// key is absent the row is rewritten unchanged. Other keys are preserved.
/// Returns `Some(AuthSession<U>)` with the post-update row, or `None` if no
/// session exists for `session_id`.
///
/// # Arguments
///
/// * `session_id` - The UUID of the session to update, as a string.
/// * `key` - The top-level key to remove from the `meta` object.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The `session_id` is not a valid UUID
/// - The query fails
/// - The returned `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, DeleteAuthSessionMetaKey)]
async fn delete_auth_session_meta_key<U: UserRole>(
    session_id: &str,
    key: &str,
) -> Option<AuthSession<U>> {
    let pool = T::yield_pool();
    let parsed_id: uuid::Uuid = session_id
        .parse()
        .map_err(|e| sqlx::Error::Protocol(format!("invalid session_id UUID: {}", e)))?;
    let row = sqlx::query(
        "UPDATE saps.auth_sessions SET meta = meta - $1 WHERE id = $2 \
         RETURNING id, role, date_created, last_interacted, meta",
    )
        .bind(key)
        .bind(parsed_id)
        .fetch_optional(pool)
        .await?;
    match row {
        Some(r) => {
            let session =
                AuthSession::from_row(&r).map_err(|e| sqlx::Error::Protocol(e.message))?;
            Ok(Some(session))
        }
        None => Ok(None),
    }
}

/// Returns every auth session whose `meta` contains the given key/value pair.
///
/// Uses the JSONB containment operator `@>`, so `meta` must include the named
/// key with a value equal to `value` (other keys are ignored). Sessions whose
/// `meta` is `NULL` never match. The query is index-friendly with a GIN index
/// on `meta`.
///
/// # Arguments
///
/// * `key` - The top-level key to look for in `meta`.
/// * `value` - The JSON value the key must equal.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The query fails
/// - Any row's `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, GetAuthSessionsByMetaKey)]
async fn get_auth_sessions_by_meta_key<U: UserRole>(
    key: &str,
    value: Value,
) -> Vec<AuthSession<U>> {
    let pool = T::yield_pool();
    let rows = sqlx::query(
        "SELECT id, role, date_created, last_interacted, meta \
         FROM saps.auth_sessions \
         WHERE meta @> jsonb_build_object($1::text, $2::jsonb)",
    )
        .bind(key)
        .bind(value)
        .fetch_all(pool)
        .await?;
    let mut sessions = Vec::with_capacity(rows.len());
    for row in &rows {
        sessions.push(AuthSession::from_row(row).map_err(|e| sqlx::Error::Protocol(e.message))?);
    }
    Ok(sessions)
}

/// Returns at most one auth session whose `meta` contains the given key/value
/// pair.
///
/// Designed to be paired with a partial unique index on `meta->>key` (see
/// [`AuthSession::generate_unique_meta_key_sql`](super::model::AuthSession::generate_unique_meta_key_sql)),
/// where the index guarantees that at most one row can match. The query
/// itself uses `LIMIT 1` so it stays correct even if the unique index is
/// missing — it just becomes "any one matching row" in that case.
///
/// # Arguments
///
/// * `key` - The top-level key to look for in `meta`.
/// * `value` - The JSON value the key must equal.
///
/// # Errors
///
/// Returns `sqlx::Error` if:
/// - The query fails
/// - The returned `role` string cannot be parsed back into `U`
#[db_transaction(AuthPostGresDescriptor, GetAuthSessionByMetaKey)]
async fn get_auth_session_by_meta_key<U: UserRole>(
    key: &str,
    value: Value,
) -> Option<AuthSession<U>> {
    let pool = T::yield_pool();
    let row = sqlx::query(
        "SELECT id, role, date_created, last_interacted, meta \
         FROM saps.auth_sessions \
         WHERE meta @> jsonb_build_object($1::text, $2::jsonb) \
         LIMIT 1",
    )
        .bind(key)
        .bind(value)
        .fetch_optional(pool)
        .await?;
    match row {
        Some(r) => {
            let session =
                AuthSession::from_row(&r).map_err(|e| sqlx::Error::Protocol(e.message))?;
            Ok(Some(session))
        }
        None => Ok(None),
    }
}

/// Sets `upsert_key`/`upsert_value` on every session whose `meta` matches
/// `match_key`/`match_value`, returning the number of rows that were updated.
///
/// Combines [`get_auth_sessions_by_meta_key`] (containment lookup) and
/// [`upsert_auth_session_meta_key`] (jsonb_set with `create_missing`) into a
/// single statement so the lookup and the write happen atomically. `meta` is
/// coalesced from `NULL` to `'{}'::jsonb` before the upsert, so a row that
/// only carries the match key (and has no other meta) still gets the new
/// key inserted.
///
/// Pair this with a partial unique index on `match_key` (see
/// [`AuthSession::generate_unique_meta_key_sql`](super::model::AuthSession::generate_unique_meta_key_sql))
/// to guarantee the return value is `0` or `1`. Without the index, every
/// session whose `meta` contains the matching pair is updated.
///
/// # Arguments
///
/// * `match_key` - Top-level key used to find the target session(s).
/// * `match_value` - JSON value the match key must equal.
/// * `upsert_key` - Top-level key to insert or overwrite in `meta`.
/// * `upsert_value` - JSON value to associate with `upsert_key`.
///
/// # Errors
///
/// Returns `sqlx::Error` if the query fails.
#[db_transaction(AuthPostGresDescriptor, UpsertAuthSessionsMetaKeyByMeta)]
async fn upsert_auth_sessions_meta_key_by_meta(
    match_key: &str,
    match_value: Value,
    upsert_key: &str,
    upsert_value: Value,
) -> u64 {
    let pool = T::yield_pool();
    let result = sqlx::query(
        "UPDATE saps.auth_sessions \
         SET meta = jsonb_set(COALESCE(meta, '{}'::jsonb), ARRAY[$3], $4, true) \
         WHERE meta @> jsonb_build_object($1::text, $2::jsonb)",
    )
        .bind(match_key)
        .bind(match_value)
        .bind(upsert_key)
        .bind(upsert_value)
        .execute(pool)
        .await?;
    Ok(result.rows_affected())
}

/// Deletes every auth session whose `meta` contains the given key/value pair.
///
/// Uses the JSONB containment operator `@>` to match rows where `meta`
/// includes `key` with a value equal to `value`. Returns the number of rows
/// removed.
///
/// # Arguments
///
/// * `key` - The top-level key to look for in `meta`.
/// * `value` - The JSON value the key must equal.
///
/// # Errors
///
/// Returns `sqlx::Error` if the query fails.
#[db_transaction(AuthPostGresDescriptor, DeleteAuthSessionsByMetaKey)]
async fn delete_auth_sessions_by_meta_key(key: &str, value: Value) -> u64 {
    let pool = T::yield_pool();
    let result = sqlx::query(
        "DELETE FROM saps.auth_sessions \
         WHERE meta @> jsonb_build_object($1::text, $2::jsonb)",
    )
        .bind(key)
        .bind(value)
        .execute(pool)
        .await?;
    Ok(result.rows_affected())
}

/// Deletes an auth session by its UUID.
///
/// Removes the row from `saps.auth_sessions` where `id` matches. Returns `true`
/// if a row was deleted, `false` if no session existed with that id.
///
/// # Arguments
///
/// * `session_id` - The UUID of the session to delete.
///
/// # Errors
///
/// Returns `sqlx::Error` if the query fails.
#[db_transaction(AuthPostGresDescriptor, DeleteAuthSession)]
async fn delete_auth_session(session_id: uuid::Uuid) -> bool {
    let pool = T::yield_pool();
    let result = sqlx::query("DELETE FROM saps.auth_sessions WHERE id = $1")
        .bind(session_id)
        .execute(pool)
        .await?;
    Ok(result.rows_affected() > 0)
}
