use anyhow::{Context, Result};
use std::path::PathBuf;
use std::sync::Arc;

/// Single local SQLite database for all application data.
#[derive(Clone)]
pub struct Database {
    conn: Arc<libsql::Connection>,
}

impl Database {
    /// Connect to a local SQLite database at the given path.
    /// Creates the database file and tables if they don't exist.
    pub async fn connect(path: &std::path::Path) -> Result<Self> {
        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent).context("Failed to create data directory")?;
        }

        eprintln!("[keel:db] using local SQLite at {}", path.display());
        let db = libsql::Builder::new_local(path)
            .build()
            .await
            .context("Failed to open local SQLite")?;

        let conn = db.connect().context("Failed to create connection")?;
        let database = Self {
            conn: Arc::new(conn),
        };
        database.configure_connection().await?;
        database.init_tables().await?;
        database.run_migrations().await?;
        Ok(database)
    }

    /// Connect to a specific database file path (string version).
    pub async fn connect_with_path(path: &str) -> Result<Self> {
        let db = libsql::Builder::new_local(path)
            .build()
            .await
            .context("Failed to open SQLite at given path")?;
        let conn = db.connect().context("Failed to create connection")?;
        let database = Self {
            conn: Arc::new(conn),
        };
        database.configure_connection().await?;
        database.init_tables().await?;
        database.run_migrations().await?;
        Ok(database)
    }

    /// Connect to an in-memory database (for tests).
    pub async fn connect_in_memory() -> Result<Self> {
        let db = libsql::Builder::new_local(":memory:")
            .build()
            .await
            .context("Failed to open in-memory SQLite")?;
        let conn = db.connect().context("Failed to create connection")?;
        let database = Self {
            conn: Arc::new(conn),
        };
        database.configure_connection().await?;
        database.init_tables().await?;
        database.run_migrations().await?;
        Ok(database)
    }

    /// Apply performance and concurrency PRAGMAs on every new connection.
    async fn configure_connection(&self) -> Result<()> {
        self.conn
            .execute_batch(
                "PRAGMA journal_mode = WAL;
             PRAGMA foreign_keys = ON;
             PRAGMA busy_timeout = 5000;
             PRAGMA synchronous = NORMAL;
             PRAGMA cache_size = -64000;
             PRAGMA temp_store = MEMORY;",
            )
            .await
            .context("Failed to configure SQLite connection")?;
        Ok(())
    }

    pub fn conn(&self) -> &libsql::Connection {
        &self.conn
    }

    async fn init_tables(&self) -> Result<()> {
        self.conn
            .execute_batch(
                "CREATE TABLE IF NOT EXISTS projects (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                folder_path TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS sessions (
                id TEXT PRIMARY KEY,
                job_id TEXT,
                claude_session_id TEXT,
                status TEXT NOT NULL DEFAULT 'running',
                prompt TEXT NOT NULL,
                created_at TEXT NOT NULL,
                completed_at TEXT
            );
            CREATE TABLE IF NOT EXISTS session_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL REFERENCES sessions(id),
                event_type TEXT NOT NULL,
                content TEXT NOT NULL,
                timestamp TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS preferences (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );",
            )
            .await
            .context("Failed to init tables")?;
        Ok(())
    }

    pub async fn get_preference(&self, key: &str) -> Result<Option<String>> {
        let mut rows = self
            .conn
            .query("SELECT value FROM preferences WHERE key = ?1", [key])
            .await?;
        match rows.next().await? {
            Some(row) => Ok(Some(row.get::<String>(0)?)),
            None => Ok(None),
        }
    }

    pub async fn set_preference(&self, key: &str, value: &str) -> Result<()> {
        self.conn
            .execute(
                "INSERT OR REPLACE INTO preferences (key, value) VALUES (?1, ?2)",
                libsql::params![key.to_string(), value.to_string()],
            )
            .await?;
        Ok(())
    }
}

/// Returns the default data directory for a given app name.
/// On macOS: ~/Library/Application Support/<app_name>/
pub fn default_data_dir(app_name: &str) -> PathBuf {
    let home = std::env::var("HOME").unwrap_or_else(|_| ".".into());
    PathBuf::from(home)
        .join("Library")
        .join("Application Support")
        .join(app_name)
}

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

    #[tokio::test]
    async fn test_wal_mode_enabled() {
        let db = Database::connect_in_memory().await.unwrap();
        let mut rows = db
            .conn()
            .query("PRAGMA journal_mode", libsql::params![])
            .await
            .unwrap();
        let row = rows.next().await.unwrap().unwrap();
        let mode: String = row.get(0).unwrap();
        // In-memory SQLite always reports "memory" for journal_mode,
        // but the PRAGMA executed without error, confirming configure_connection works.
        assert!(mode == "wal" || mode == "memory");
    }

    #[tokio::test]
    async fn test_busy_timeout_set() {
        let db = Database::connect_in_memory().await.unwrap();
        let mut rows = db
            .conn()
            .query("PRAGMA busy_timeout", libsql::params![])
            .await
            .unwrap();
        let row = rows.next().await.unwrap().unwrap();
        let timeout: i64 = row.get(0).unwrap();
        assert_eq!(timeout, 5000);
    }

    #[tokio::test]
    async fn test_preferences_roundtrip() {
        let db = Database::connect_in_memory().await.unwrap();
        assert!(db.get_preference("key1").await.unwrap().is_none());

        db.set_preference("key1", "value1").await.unwrap();
        assert_eq!(
            db.get_preference("key1").await.unwrap(),
            Some("value1".to_string())
        );

        db.set_preference("key1", "value2").await.unwrap();
        assert_eq!(
            db.get_preference("key1").await.unwrap(),
            Some("value2".to_string())
        );
    }
}
