use rusqlite::{Connection, Result};
use std::path::Path;
use std::fs;

pub fn init_db(db_path: &Path, extension_path: &Path) -> Result<Connection> {
    // Ensure parent directory exists
    if let Some(parent) = db_path.parent() {
        if !parent.exists() {
            fs::create_dir_all(parent).map_err(|e| rusqlite::Error::ToSqlConversionFailure(Box::new(e)))?;
        }
    }

    let conn = Connection::open(db_path)?;

    // Load sqlite-vec extension
    // IMPORTANT: This relies on vec0.dll being in a place where standard LoadLibrary can find it
    // OR we pass the absolute path.
    // Since build.rs copies it to the same dir as the exe, we can try loading by name "vec0" or full path.
    // If we use full path, we need to know where the exe is.
    // But passing extension_path allows flexibility from main.rs.
    unsafe {
        conn.load_extension_enable()?;
        conn.load_extension(extension_path, None)?;
        conn.load_extension_disable()?;
    }

    // Initialize Schema
    conn.execute_batch(
        "PRAGMA journal_mode = WAL;
         CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            created_at TEXT DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT DEFAULT (datetime('now', 'localtime'))
        );
        CREATE TRIGGER IF NOT EXISTS update_items_updated_at
        AFTER UPDATE ON items
        FOR EACH ROW
        BEGIN
            UPDATE items SET updated_at = datetime('now', 'localtime') WHERE id = OLD.id;
        END;
        CREATE VIRTUAL TABLE IF NOT EXISTS vec_items USING vec0(
            id INTEGER PRIMARY KEY,
            embedding FLOAT[768]
        );"
    )?;

    Ok(conn)
}

use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use std::str::FromStr;
use std::borrow::Cow;

pub async fn init_pool(db_path: &str, extension_path: impl Into<Cow<'static, str>>) -> Result<sqlx::SqlitePool, sqlx::Error> {
    let opts = SqliteConnectOptions::from_str(&format!("sqlite://{}?mode=rwc", db_path))?
        .extension(extension_path);

    let pool = SqlitePoolOptions::new()
        .connect_with(opts)
        .await?;
    Ok(pool)
}