//! 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()`.

mod id_types;
mod validated_types;
mod enums;
mod models;
mod subscription_writer;
pub mod users;
pub(crate) mod projects;
pub mod items;
pub mod versions;
pub(crate) mod chapters;
pub(crate) mod item_sections;
pub(crate) mod project_sections;
pub mod transactions;
pub(crate) mod discover;
pub(crate) mod custom_links;
pub(crate) mod auth;
pub mod waitlist;
pub(crate) mod blog_posts;
pub(crate) mod license_keys;
pub(crate) mod synckit;
pub mod synckit_billing;
pub(crate) mod oauth;
pub(crate) mod promo_codes;
pub(crate) mod follows;
pub(crate) mod subscriptions;
pub(crate) mod tags;
pub(crate) mod categories;
pub(crate) mod sessions;
pub(crate) mod totp;
pub(crate) mod passkeys;
pub(crate) mod health;
pub(crate) mod monitor;
pub(crate) mod scanning;
pub(crate) mod scan_jobs;
pub(crate) mod scan_admin_actions;
pub(crate) mod content_insertions;
pub(crate) mod invites;
pub(crate) mod analytics;
pub(crate) mod email_suppressions;
pub mod git_repos;
pub mod repo_collaborators;
pub mod ssh_keys;
pub mod issues;
pub(crate) mod reports;
pub(crate) mod fan_plus;
pub(crate) mod collections;
pub(crate) mod ota;
pub(crate) mod builds;
pub(crate) mod creator_tiers;
pub(crate) mod mailing_lists;
pub mod custom_domains;
pub mod patches;
pub mod bundles;
pub(crate) mod email_signups;
pub(crate) mod imports;
pub(crate) mod media_files;
pub(crate) mod tips;
pub(crate) mod project_members;
pub(crate) mod idempotency;
pub(crate) mod pending_refunds;
pub mod webhook_events;
pub(crate) mod scheduler_jobs;
pub(crate) mod moderation;
pub(crate) mod wishlists;
pub(crate) mod cart;
pub mod gallery_images;
pub mod page_views;
pub mod pending_s3_deletions;
pub(crate) mod pending_uploads;

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

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
}