//! 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, GetAllAuthSessions, PingAuthSession},
};
use crate::{
    auth::token::checks::UserRole,
    dal::connections::AuthPostGresDescriptor,
    db_transaction,
};


/// 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)
}

/// 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)
}
