//! SQLite database layer for GoingsOn.
//!
//! Provides SQLite implementations of all repository traits defined in `goingson-core`.
//! Uses `sqlx` for async database operations with connection pooling.
//!
//! # Usage
//!
//! ```ignore
//! let pool = goingson_db_sqlite::init_pool(Some("./data.db")).await?;
//! goingson_db_sqlite::run_migrations(&pool).await?;
//! let task_repo = SqliteTaskRepository::new(pool.clone());
//! ```

pub mod migrations;
pub mod repository;
pub mod utils;

use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use sqlx::SqlitePool;
use std::str::FromStr;
use std::time::Duration;

/// Initialize the SQLite database connection pool
/// If database_path is None, uses an in-memory database
pub async fn init_pool(database_path: Option<&str>) -> Result<SqlitePool, sqlx::Error> {
    let path = database_path.unwrap_or("goingson.db");

    // Build options from a real filesystem path via `.filename()` so a path
    // containing URI syntax (`?vfs=`, `?mode=memory`, `#frag`) is treated as a
    // literal file, never parsed as connection options. `:memory:` still needs
    // the URL form so sqlx sets its `in_memory` flag and the 5-connection pool
    // shares one database (tests rely on this).
    let base = if path == ":memory:" {
        SqliteConnectOptions::from_str("sqlite::memory:")?
    } else {
        SqliteConnectOptions::new().filename(path)
    };
    let options = base
        .create_if_missing(true)
        .foreign_keys(true)
        .journal_mode(SqliteJournalMode::Wal)
        // WAL allows concurrent readers + one writer; a second writer (e.g. a
        // backup tick racing a sync pull) would otherwise get SQLITE_BUSY
        // immediately. Wait briefly for the lock instead. acquire_timeout only
        // bounds pool checkout, not lock contention inside a held connection.
        .busy_timeout(Duration::from_secs(5));

    SqlitePoolOptions::new()
        .max_connections(5)
        .acquire_timeout(Duration::from_secs(3))
        .connect_with(options)
        .await
}

/// Run database migrations
pub async fn run_migrations(pool: &SqlitePool) -> Result<(), sqlx::migrate::MigrateError> {
    sqlx::migrate!("../../migrations/sqlite")
        .run(pool)
        .await?;

    // Ensure foreign keys are enabled (safety net for interrupted migrations)
    sqlx::query("PRAGMA foreign_keys = ON")
        .execute(pool)
        .await
        .map_err(sqlx::migrate::MigrateError::Execute)?;

    Ok(())
}

pub use repository::{
    SqliteAttachmentRepository,
    SqliteBackupSettingsRepository,
    SqliteContactRepository,
    SqliteDailyNoteRepository,
    SqliteProjectRepository,
    SqliteTaskRepository,
    SqliteEventRepository,
    SqliteEmailRepository,
    SqliteUserRepository,
    SqliteEmailAccountRepository,
    SqliteStatsRepository,
    SqliteSearchRepository,
    SqliteMilestoneRepository,
    SqliteMonthlyReviewRepository,
    SqliteSavedViewRepository,
    SqliteSyncAccountRepository,
    SqliteWeeklyReviewRepository,
};

pub use repository::restore::{restore_all, BACKUP_TABLES, EXCLUDED_TABLES};