//! BalancedBreakfast Database Layer

pub mod id_types;
pub mod models;
pub mod repository;

pub use id_types::*;
pub use models::*;
pub use repository::*;

use sqlx::sqlite::SqlitePoolOptions;
use sqlx::SqlitePool;

/// SQLite timestamp format used for all datetime columns.
pub const TIMESTAMP_FMT: &str = "%Y-%m-%d %H:%M:%S";

#[derive(Clone)]
/// SQLite database connection pool
pub struct Database {
    pool: SqlitePool,
}

impl Database {
    /// Create a new database connection
    #[tracing::instrument(skip_all)]
    pub async fn connect(database_url: &str) -> Result<Self, sqlx::Error> {
        let pool = SqlitePoolOptions::new()
            .max_connections(16)
            .after_connect(|conn, _meta| {
                Box::pin(async move {
                    sqlx::query("PRAGMA foreign_keys = ON")
                        .execute(&mut *conn)
                        .await?;
                    Ok(())
                })
            })
            .connect(database_url)
            .await?;

        Ok(Self { pool })
    }

    /// Run migrations
    #[tracing::instrument(skip_all)]
    pub async fn migrate(&self) -> Result<(), sqlx::migrate::MigrateError> {
        sqlx::migrate!("../../migrations/sqlite").run(&self.pool).await
    }

    /// Get the connection pool
    #[tracing::instrument(skip_all)]
    pub fn pool(&self) -> &SqlitePool {
        &self.pool
    }

    /// Get a repository for feed subscription CRUD (create, enable/disable, delete,
    /// list by busser, update last_fetch timestamp).
    #[tracing::instrument(skip_all)]
    pub fn feeds(&self) -> FeedsRepository {
        FeedsRepository::new(self.pool.clone())
    }

    /// Get a repository for feed item operations: upsert, read/star toggling,
    /// paginated listing (by busser, by feed, unread, starred), and counts.
    #[tracing::instrument(skip_all)]
    pub fn items(&self) -> ItemsRepository {
        ItemsRepository::new(self.pool.clone())
    }

    /// Get a repository for feed tag CRUD (per-feed tag assignment,
    /// distinct tag listing, bulk feed-tag pairs for sidebar).
    #[tracing::instrument(skip_all)]
    pub fn tags(&self) -> TagsRepository {
        TagsRepository::new(self.pool.clone())
    }

    /// Get a repository for busser key-value state (cursors, tokens, pagination
    /// markers). Each entry is keyed by `(busser_id, key)`.
    #[tracing::instrument(skip_all)]
    pub fn state(&self) -> StateRepository {
        StateRepository::new(self.pool.clone())
    }

    /// Get a repository for user_config key-value pairs (theme, welcome flag,
    /// etc.). Synced via sync_changelog triggers (migration 007).
    #[tracing::instrument(skip_all)]
    pub fn config(&self) -> ConfigRepository {
        ConfigRepository::new(self.pool.clone())
    }

    /// Get a repository for query feed CRUD (saved filter rules that act as
    /// virtual sources). Synced via sync_changelog triggers (migration 009).
    #[tracing::instrument(skip_all)]
    pub fn query_feeds(&self) -> QueryFeedsRepository {
        QueryFeedsRepository::new(self.pool.clone())
    }

    /// Get a repository for bookmark (reading list) CRUD. Bookmarks are
    /// permanent, user-owned references to URLs. Synced via sync_changelog
    /// triggers (migration 012).
    #[tracing::instrument(skip_all)]
    pub fn bookmarks(&self) -> BookmarksRepository {
        BookmarksRepository::new(self.pool.clone())
    }
}