use anyhow::Result;
use sqlx::{Pool, Row, Sqlite};
use std::path::Path;

pub type DbPool = Pool<Sqlite>;

pub const DEFAULT_QUOTA_LIMIT: i64 = 2147483648;
pub const QUOTA_WARNING_THRESHOLD: f64 = 0.9;

static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("./migrations");

/// Represents a gamer's storage quota
#[derive(Debug, Clone)]
pub struct GamerQuota {
    #[allow(dead_code)]
    pub gamer_id: String,
    pub used_bytes: i64,
    pub limit_bytes: i64,
    pub warning_sent: bool,
}

/// Error when quota is exceeded
#[derive(Debug)]
pub struct QuotaExceeded {
    pub used_bytes: i64,
    pub limit_bytes: i64,
}

pub async fn init_database(db_path: &Path) -> Result<DbPool> {
    if let Some(parent) = db_path.parent() {
        std::fs::create_dir_all(parent)?;
    }

    let database_url = format!("sqlite:{}?mode=rwc", db_path.display());
    let pool = sqlx::sqlite::SqlitePoolOptions::new()
        .connect(&database_url)
        .await?;

    MIGRATOR.run(&pool).await?;

    Ok(pool)
}

/// Get quota for a gamer, returns None if no quota record exists (uses default)
pub async fn get_quota(pool: &DbPool, gamer_id: &str) -> Result<Option<GamerQuota>> {
    let row = sqlx::query(
        "SELECT gamer_id, used_bytes, limit_bytes, warning_sent FROM gamer_quotas WHERE gamer_id = ?",
    )
    .bind(gamer_id)
    .fetch_optional(pool)
    .await?;

    Ok(row.map(|r| GamerQuota {
        gamer_id: r.get("gamer_id"),
        used_bytes: r.get("used_bytes"),
        limit_bytes: r.get("limit_bytes"),
        warning_sent: r.get::<i64, _>("warning_sent") != 0,
    }))
}

/// Atomically reserve quota for a new upload - checks AND increments in a single transaction.
/// This prevents race conditions where two concurrent uploads could both pass check_quota
/// but then both increment, exceeding the quota.
/// Returns Err(QuotaExceeded) if no room, Ok(new_used_bytes) if successful.
pub async fn reserve_quota(pool: &DbPool, gamer_id: &str, bytes: i64) -> Result<i64, QuotaExceeded> {
    let mut tx = pool.begin().await.expect("Failed to begin transaction");

    let quota: GamerQuota = match sqlx::query(
        "SELECT gamer_id, used_bytes, limit_bytes, warning_sent FROM gamer_quotas WHERE gamer_id = ?",
    )
    .bind(gamer_id)
    .fetch_optional(&mut *tx)
    .await
    {
        Ok(Some(row)) => GamerQuota {
            gamer_id: row.get("gamer_id"),
            used_bytes: row.get("used_bytes"),
            limit_bytes: row.get("limit_bytes"),
            warning_sent: row.get::<i64, _>("warning_sent") != 0,
        },
        Ok(None) => GamerQuota {
            gamer_id: gamer_id.to_string(),
            used_bytes: 0,
            limit_bytes: DEFAULT_QUOTA_LIMIT,
            warning_sent: false,
        },
        Err(e) => {
            tracing::error!("Database error fetching quota for {}: {}", gamer_id, e);
            return Err(QuotaExceeded {
                used_bytes: 0,
                limit_bytes: DEFAULT_QUOTA_LIMIT,
            });
        }
    };

    if quota.used_bytes + bytes > quota.limit_bytes {
        tx.rollback().await.ok();
        return Err(QuotaExceeded {
            used_bytes: quota.used_bytes,
            limit_bytes: quota.limit_bytes,
        });
    }

    let new_used = quota.used_bytes + bytes;
    let should_warn = !quota.warning_sent
        && (new_used as f64 / quota.limit_bytes as f64) >= QUOTA_WARNING_THRESHOLD;

    if quota.used_bytes == 0 {
        sqlx::query(
            "INSERT INTO gamer_quotas (gamer_id, used_bytes, limit_bytes, warning_sent) VALUES (?, ?, ?, ?)
             ON CONFLICT(gamer_id) DO UPDATE SET used_bytes = ?, warning_sent = ?",
        )
        .bind(gamer_id)
        .bind(new_used)
        .bind(quota.limit_bytes)
        .bind(should_warn)
        .bind(new_used)
        .bind(should_warn)
        .execute(&mut *tx)
        .await
        .expect("Failed to insert quota record");
    } else {
        sqlx::query(
            "UPDATE gamer_quotas SET used_bytes = ?, warning_sent = ? WHERE gamer_id = ?",
        )
        .bind(new_used)
        .bind(should_warn)
        .bind(gamer_id)
        .execute(&mut *tx)
        .await
        .expect("Failed to update quota record");
    }

    tx.commit().await.expect("Failed to commit quota transaction");
    Ok(new_used)
}