//! SQLite schema management for QuickDID.
//!
//! This module provides functionality to create and manage the SQLite database
//! schema used by the SQLite-backed handle resolver cache.

use anyhow::Result;
use sqlx::{Sqlite, SqlitePool, migrate::MigrateDatabase};
use std::path::Path;

/// SQL schema for the handle resolution cache table.
const CREATE_HANDLE_RESOLUTION_CACHE_TABLE: &str = r#"
CREATE TABLE IF NOT EXISTS handle_resolution_cache (
    key INTEGER PRIMARY KEY,
    result BLOB NOT NULL,
    created INTEGER NOT NULL,
    updated INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_handle_resolution_cache_updated 
ON handle_resolution_cache(updated);
"#;

/// SQL schema for the handle resolution queue table.
const CREATE_HANDLE_RESOLUTION_QUEUE_TABLE: &str = r#"
CREATE TABLE IF NOT EXISTS handle_resolution_queue (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    work TEXT NOT NULL,
    queued_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_handle_resolution_queue_queued_at 
ON handle_resolution_queue(queued_at);
"#;

/// Create or connect to a SQLite database and ensure schema is initialized.
///
/// # Arguments
///
/// * `database_url` - SQLite database URL (e.g., "sqlite:./quickdid.db" or "sqlite::memory:")
///
/// # Returns
///
/// Returns a SqlitePool connected to the database with schema initialized.
///
/// # Example
///
/// ```no_run
/// use quickdid::sqlite_schema::create_sqlite_pool;
///
/// # async fn example() -> anyhow::Result<()> {
/// // File-based database
/// let pool = create_sqlite_pool("sqlite:./quickdid.db").await?;
///
/// // In-memory database (for testing)
/// let pool = create_sqlite_pool("sqlite::memory:").await?;
/// # Ok(())
/// # }
/// ```
pub async fn create_sqlite_pool(database_url: &str) -> Result<SqlitePool> {
    tracing::info!("Initializing SQLite database: {}", database_url);

    // Extract the database path from the URL for file-based databases
    if let Some(path) = database_url.strip_prefix("sqlite:")
        && path != ":memory:"
        && !path.is_empty()
    {
        // Create the database file if it doesn't exist
        if !Sqlite::database_exists(database_url).await? {
            tracing::info!("Creating SQLite database file: {}", path);
            Sqlite::create_database(database_url).await?;
        }

        // Ensure the parent directory exists
        if let Some(parent) = Path::new(path).parent()
            && !parent.exists()
        {
            tracing::info!("Creating directory: {}", parent.display());
            std::fs::create_dir_all(parent)?;
        }
    }

    // Connect to the database
    let pool = SqlitePool::connect(database_url).await?;
    tracing::info!("Connected to SQLite database");

    // Create the schema
    create_schema(&pool).await?;

    Ok(pool)
}

/// Create the database schema if it doesn't exist.
///
/// # Arguments
///
/// * `pool` - SQLite connection pool
///
/// # Example
///
/// ```no_run
/// use quickdid::sqlite_schema::create_schema;
/// use sqlx::SqlitePool;
///
/// # async fn example() -> anyhow::Result<()> {
/// let pool = SqlitePool::connect("sqlite::memory:").await?;
/// create_schema(&pool).await?;
/// # Ok(())
/// # }
/// ```
pub async fn create_schema(pool: &SqlitePool) -> Result<()> {
    tracing::debug!("Creating SQLite schema if not exists");

    // Execute the schema creation SQL
    sqlx::query(CREATE_HANDLE_RESOLUTION_CACHE_TABLE)
        .execute(pool)
        .await?;

    sqlx::query(CREATE_HANDLE_RESOLUTION_QUEUE_TABLE)
        .execute(pool)
        .await?;

    tracing::info!("SQLite schema initialized");

    Ok(())
}

/// Clean up expired entries from the handle resolution cache.
///
/// This function removes entries that are older than the specified TTL.
/// It should be called periodically to prevent the database from growing indefinitely.
///
/// # Arguments
///
/// * `pool` - SQLite connection pool
/// * `ttl_seconds` - TTL in seconds for cache entries
///
/// # Returns
///
/// Returns the number of entries deleted.
///
/// # Example
///
/// ```no_run
/// use quickdid::sqlite_schema::cleanup_expired_entries;
/// use sqlx::SqlitePool;
///
/// # async fn example() -> anyhow::Result<()> {
/// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?;
/// let deleted_count = cleanup_expired_entries(&pool, 7776000).await?; // 90 days
/// println!("Deleted {} expired entries", deleted_count);
/// # Ok(())
/// # }
/// ```
pub async fn cleanup_expired_entries(pool: &SqlitePool, ttl_seconds: u64) -> Result<u64> {
    let current_timestamp = std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .unwrap_or_default()
        .as_secs() as i64;

    let cutoff_timestamp = current_timestamp - (ttl_seconds as i64);

    let result = sqlx::query("DELETE FROM handle_resolution_cache WHERE updated < ?1")
        .bind(cutoff_timestamp)
        .execute(pool)
        .await?;

    let deleted_count = result.rows_affected();
    if deleted_count > 0 {
        tracing::info!("Cleaned up {} expired cache entries", deleted_count);
    }

    Ok(deleted_count)
}

/// Get statistics about the handle resolution cache.
///
/// # Arguments
///
/// * `pool` - SQLite connection pool
///
/// # Returns
///
/// Returns a tuple of (total_entries, database_size_bytes).
///
/// # Example
///
/// ```no_run
/// use quickdid::sqlite_schema::get_cache_stats;
/// use sqlx::SqlitePool;
///
/// # async fn example() -> anyhow::Result<()> {
/// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?;
/// let (total_entries, size_bytes) = get_cache_stats(&pool).await?;
/// println!("Cache has {} entries, {} bytes", total_entries, size_bytes);
/// # Ok(())
/// # }
/// ```
pub async fn get_cache_stats(pool: &SqlitePool) -> Result<(i64, i64)> {
    // Get total entries
    let total_entries: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_cache")
        .fetch_one(pool)
        .await?;

    // Get database page size and page count to calculate total size
    let page_size: i64 = sqlx::query_scalar("PRAGMA page_size")
        .fetch_one(pool)
        .await?;

    let page_count: i64 = sqlx::query_scalar("PRAGMA page_count")
        .fetch_one(pool)
        .await?;

    let size_bytes = page_size * page_count;

    Ok((total_entries, size_bytes))
}

/// Clean up old entries from the handle resolution queue.
///
/// This function removes entries that are older than the specified age.
///
/// # Arguments
///
/// * `pool` - SQLite connection pool
/// * `max_age_seconds` - Maximum age in seconds for queue entries to be kept
///
/// # Returns
///
/// Returns the number of entries deleted.
///
/// # Example
///
/// ```no_run
/// use quickdid::sqlite_schema::cleanup_queue_entries;
/// use sqlx::SqlitePool;
///
/// # async fn example() -> anyhow::Result<()> {
/// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?;
/// let deleted_count = cleanup_queue_entries(&pool, 86400).await?; // 1 day
/// println!("Deleted {} old queue entries", deleted_count);
/// # Ok(())
/// # }
/// ```
pub async fn cleanup_queue_entries(pool: &SqlitePool, max_age_seconds: u64) -> Result<u64> {
    let current_timestamp = std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .unwrap_or_default()
        .as_secs() as i64;

    let cutoff_timestamp = current_timestamp - (max_age_seconds as i64);

    let result = sqlx::query("DELETE FROM handle_resolution_queue WHERE queued_at < ?1")
        .bind(cutoff_timestamp)
        .execute(pool)
        .await?;

    let deleted_count = result.rows_affected();
    if deleted_count > 0 {
        tracing::info!("Cleaned up {} old queue entries", deleted_count);
    }

    Ok(deleted_count)
}

/// Get statistics about the handle resolution queue.
///
/// # Arguments
///
/// * `pool` - SQLite connection pool
///
/// # Returns
///
/// Returns the total number of entries in the queue.
///
/// # Example
///
/// ```no_run
/// use quickdid::sqlite_schema::get_queue_stats;
/// use sqlx::SqlitePool;
///
/// # async fn example() -> anyhow::Result<()> {
/// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?;
/// let total = get_queue_stats(&pool).await?;
/// println!("Queue: {} total entries", total);
/// # Ok(())
/// # }
/// ```
pub async fn get_queue_stats(pool: &SqlitePool) -> Result<i64> {
    // Get total entries
    let total_entries: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_queue")
        .fetch_one(pool)
        .await?;

    Ok(total_entries)
}

#[cfg(test)]
mod tests {
    use super::*;

    #[tokio::test]
    async fn test_create_sqlite_pool_memory() {
        let pool = create_sqlite_pool("sqlite::memory:")
            .await
            .expect("Failed to create in-memory SQLite pool");

        // Verify the table was created
        let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_cache")
            .fetch_one(&pool)
            .await
            .expect("Failed to query table");

        assert_eq!(count, 0);
    }

    #[tokio::test]
    async fn test_cleanup_expired_entries() {
        let pool = create_sqlite_pool("sqlite::memory:")
            .await
            .expect("Failed to create in-memory SQLite pool");

        // Insert a test entry that's already expired
        let old_timestamp = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .unwrap()
            .as_secs() as i64
            - 3600; // 1 hour ago

        sqlx::query(
            "INSERT INTO handle_resolution_cache (key, result, created, updated) VALUES (1, ?1, ?2, ?2)"
        )
        .bind(&b"test_data"[..])
        .bind(old_timestamp)
        .execute(&pool)
        .await
        .expect("Failed to insert test data");

        // Clean up entries older than 30 minutes (1800 seconds)
        let deleted = cleanup_expired_entries(&pool, 1800)
            .await
            .expect("Failed to cleanup expired entries");

        assert_eq!(deleted, 1);

        // Verify the entry was deleted
        let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_cache")
            .fetch_one(&pool)
            .await
            .expect("Failed to query table");

        assert_eq!(count, 0);
    }

    #[tokio::test]
    async fn test_get_cache_stats() {
        let pool = create_sqlite_pool("sqlite::memory:")
            .await
            .expect("Failed to create in-memory SQLite pool");

        // Insert a test entry
        let current_timestamp = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .unwrap()
            .as_secs() as i64;

        sqlx::query(
            "INSERT INTO handle_resolution_cache (key, result, created, updated) VALUES (1, ?1, ?2, ?2)"
        )
        .bind(&b"test_data"[..])
        .bind(current_timestamp)
        .execute(&pool)
        .await
        .expect("Failed to insert test data");

        let (total_entries, size_bytes) = get_cache_stats(&pool)
            .await
            .expect("Failed to get cache stats");

        assert_eq!(total_entries, 1);
        assert!(size_bytes > 0);
    }
}