//! Database access layer.
//!
//! Each submodule handles a specific domain: users, projects, items, etc.
//! Types (id_types, validated_types, enums, models) are re-exported flat.
//! Query functions live in their submodules: `db::users::get_user_by_id()`.

pub mod acknowledgements; // pub so the integration test crate can drive the nag/escalate cycle directly
pub mod admin_alerts;
pub(crate) mod analytics;
pub(crate) mod auth;
pub mod blog_posts;
pub(crate) mod builds;
pub mod bundles;
pub(crate) mod cart;
pub(crate) mod categories;
pub(crate) mod chapters;
pub mod collections; // pub so the integration test crate can exercise the layer directly
pub(crate) mod content_insertions;
pub mod creator_tiers;
pub mod custom_domains;
pub(crate) mod custom_links;
pub mod custom_pages;
pub mod discover; // pub so the integration test crate can assert facet-count correctness directly
pub(crate) mod email_signups;
pub(crate) mod email_suppressions;
mod enums;
pub(crate) mod fan_plus;
pub(crate) mod follows;
pub mod gallery_images;
pub mod git_access_tokens;
pub mod git_notes; // pub so mnw-admin can rebuild the index from the repositories
pub mod git_repos;
pub(crate) mod health;
mod id_types;
pub mod idempotency; // pub so the integration test crate can exercise it directly
pub mod imports; // pub so the integration test crate can exercise the reaper/heartbeat layer directly
pub mod invites; // pub so the integration test crate can exercise redemption races directly
pub mod issues;
pub(crate) mod item_sections;
pub mod items;
pub mod license_keys;
pub mod lists;
pub mod mailing_lists;
pub(crate) mod media_files;
mod models;
pub(crate) mod moderation;
pub(crate) mod monitor;
pub(crate) mod oauth;
pub(crate) mod ota;
pub mod page_views;
pub mod pagination;
pub mod passkeys; // pub so the integration test crate can exercise the layer directly
pub mod patches;
pub mod pending_refunds;
pub mod pending_s3_deletions;
pub(crate) mod pending_uploads;
pub mod platform_credits;
pub(crate) mod project_members;
pub(crate) mod project_sections;
pub mod projects;
pub(crate) mod promo_codes;
pub mod repo_collaborators;
pub(crate) mod reports;
pub(crate) mod scan_admin_actions;
pub mod scan_jobs; // pub so the integration test crate can exercise the reaper/heartbeat layer directly
pub mod scanning; // pub so the integration test crate can exercise the quarantine purge/stamp layer directly
pub(crate) mod scheduler_jobs;
pub mod sessions;
pub mod ssh_keys;
mod subscription_writer;
pub(crate) mod subscriptions;
pub mod synckit; // pub so the integration test crate can exercise compaction directly
pub mod synckit_billing;
pub mod tags; // pub so the integration test crate can assert facet-count correctness directly
pub mod tips;
pub(crate) mod totp;
pub mod transactions;
pub mod users;
mod validated_types;
pub mod versions;
pub mod waitlist;
pub mod webhook_events;
pub(crate) mod wishlists;

pub use enums::*;
pub use id_types::*;
pub use models::*;
pub use validated_types::*;

use crate::error::Result;
use sqlx::PgPool;

/// Check the sandbox per-IP cap under an advisory lock on a single connection.
///
/// Acquires a session-level advisory lock, runs the count query, and unlocks; /// all on the same connection. Returns the active sandbox count.
///
/// This avoids the bug where `advisory_lock` + `advisory_unlock` through a pool
/// use different connections, leaving locks permanently held.
///
/// Uses `pg_try_advisory_lock` to avoid blocking under burst load; if the lock
/// is already held, returns an error rather than waiting.
pub async fn check_sandbox_cap(pool: &PgPool, lock_key: i64, ip: &str) -> Result<i64> {
    let mut conn = pool
        .acquire()
        .await
        .map_err(|e| crate::error::AppError::Internal(anyhow::anyhow!("pool acquire: {e}")))?;

    // Try to acquire lock (non-blocking), all on the same connection
    let acquired: bool = sqlx::query_scalar("SELECT pg_try_advisory_lock($1)")
        .bind(lock_key)
        .fetch_one(&mut *conn)
        .await?;

    if !acquired {
        return Err(crate::error::AppError::Internal(anyhow::anyhow!(
            "sandbox cap check: could not acquire advisory lock"
        )));
    }

    let count_result: Result<i64> = sqlx::query_scalar(
        r"
        SELECT COUNT(*) FROM users u
        JOIN user_sessions us ON us.user_id = u.id
        WHERE u.is_sandbox = TRUE
          AND u.sandbox_expires_at > NOW()
          AND us.ip_address = $1
        ",
    )
    .bind(ip)
    .fetch_one(&mut *conn)
    .await
    .map_err(Into::into);

    // Release the advisory lock on EVERY exit path, not just the success one.
    // If the COUNT above errored, an early `?` would return the connection to
    // the pool with the session-level lock still held, it would only clear
    // when `max_lifetime` rotates the connection out (up to 30 min later),
    // silently wedging the per-IP lock key in the meantime. Best-effort unlock
    // (a failed unlock is itself cleared by connection rotation).
    let _ = sqlx::query("SELECT pg_advisory_unlock($1)")
        .bind(lock_key)
        .execute(&mut *conn)
        .await;

    count_result
}