use rusqlite::{params, Connection, Result};
use crate::models::Bookmark;
use chrono::Utc;
use std::path::PathBuf;

/// Return the platform-appropriate path for the bookmark database.
///
/// On Linux this is `~/.local/share/search_hub/bookmarks.db`.  The parent
/// directory is created if it does not exist.
///
/// # Example
///
/// ```ignore
/// let path = search_hub::storage::default_db_path();
/// println!("{}", path.display());
/// ```
///
/// # Panics
///
/// Panics if the platform has no valid data directory or the directory
/// cannot be created.
pub fn default_db_path() -> PathBuf {
    let dirs = directories::ProjectDirs::from("com", "search_hub", "search_hub")
        .expect("no valid data directory");
    let data_dir = dirs.data_dir();
    std::fs::create_dir_all(data_dir).expect("failed to create data directory");
    data_dir.join("bookmarks.db")
}

/// Open (or create) the database at `path` and ensure the FTS5 table and
/// all required columns exist, running migrations if needed.
///
/// # Example
///
/// ```ignore
/// let conn = search_hub::storage::init_db("/tmp/test.db")
///     .expect("database init");
/// ```
///
/// # Parameters
///
/// * `path` - Filesystem path to the SQLite database file.
///
/// # Returns
///
/// A rusqlite `Connection` with the FTS5 table ready.
///
/// # Panics
///
/// May panic if the database cannot be opened or migrations fail.
pub fn init_db(path: &str) -> Result<Connection> {
    let conn = Connection::open(path)?;

    conn.execute_batch(
        "CREATE VIRTUAL TABLE IF NOT EXISTS bookmarks USING fts5(
            id UNINDEXED, title, url, description, source, content, tags, created_at UNINDEXED
         );",
    )?;

    // Migration: add content column to databases created before the column existed.
    let cols = conn
        .prepare("PRAGMA table_info(bookmarks)")
        .and_then(|mut stmt| {
            let cols: Vec<String> = stmt
                .query_map([], |row| row.get::<_, String>(1))?
                .collect::<Result<Vec<_>>>()?;
            Ok(cols)
        })
        .unwrap_or_default();

    if !cols.iter().any(|c| c == "content") {
        if conn.execute("ALTER TABLE bookmarks ADD COLUMN content TEXT", []).is_err() {
            conn.execute_batch(
                "CREATE TABLE bookmarks_backup AS SELECT rowid, id, title, url, description, created_at FROM bookmarks;
                 DROP TABLE IF EXISTS bookmarks;
                 CREATE VIRTUAL TABLE bookmarks USING fts5(
                    id UNINDEXED, title, url, description, source, content, tags, created_at UNINDEXED
                 );
                 INSERT INTO bookmarks (rowid, id, title, url, description, created_at)
                    SELECT rowid, id, title, url, description, created_at FROM bookmarks_backup;
                 DROP TABLE bookmarks_backup;",
            )?;
        }
    }

    if !cols.iter().any(|c| c == "tags") {
        if conn.execute("ALTER TABLE bookmarks ADD COLUMN tags TEXT", []).is_err() {
            let _existing_cols = cols.join(", ");
            conn.execute_batch(
                &format!("CREATE TABLE bookmarks_backup AS SELECT rowid, id, title, url, description, {}, created_at FROM bookmarks;
                 DROP TABLE IF EXISTS bookmarks;
                 CREATE VIRTUAL TABLE bookmarks USING fts5(
                    id UNINDEXED, title, url, description, source, content, tags, created_at UNINDEXED
                 );
                 INSERT INTO bookmarks (rowid, id, title, url, description, {}, created_at)
                    SELECT rowid, id, title, url, description, {}, created_at FROM bookmarks_backup;
                 DROP TABLE bookmarks_backup;",
                    if cols.iter().any(|c| c == "content") { "content" } else { "NULL as content" },
                    if cols.iter().any(|c| c == "content") { "content, tags" } else { "tags" },
                    if cols.iter().any(|c| c == "content") { "content, NULL as tags" } else { "NULL as content" },
                ),
            )?;
        }
    }

    if !cols.iter().any(|c| c == "source") {
        if conn.execute("ALTER TABLE bookmarks ADD COLUMN source TEXT", []).is_err() {
            conn.execute_batch(
                "CREATE TABLE bookmarks_backup AS SELECT rowid, id, title, url, description, content, tags, created_at FROM bookmarks;
                 DROP TABLE IF EXISTS bookmarks;
                 CREATE VIRTUAL TABLE bookmarks USING fts5(
                    id UNINDEXED, title, url, description, source, content, tags, created_at UNINDEXED
                 );
                 INSERT INTO bookmarks (rowid, id, title, url, description, content, tags, created_at)
                    SELECT rowid, id, title, url, description, content, tags, created_at FROM bookmarks_backup;
                 DROP TABLE bookmarks_backup;",
            )?;
        }
    }

    Ok(conn)
}

/// Check whether a bookmark with the given URL already exists.
///
/// # Example
///
/// ```ignore
/// let exists = search_hub::storage::bookmark_exists_by_url(&conn, "https://example.com")
///     .expect("query failed");
/// ```
///
/// # Parameters
///
/// * `conn` - An open database connection.
/// * `url`  - The URL to check.
///
/// # Returns
///
/// `true` if at least one row with that URL exists.
pub fn bookmark_exists_by_url(conn: &Connection, url: &str) -> Result<bool> {
    let mut stmt = conn.prepare("SELECT COUNT(*) FROM bookmarks WHERE url = ?")?;
    let count: i64 = stmt.query_row(params![url], |row| row.get(0))?;
    Ok(count > 0)
}

/// Insert a new bookmark row and return its FTS5 rowid.
///
/// The `created_at` field on the passed struct is **ignored**; the current
/// timestamp is always used.
///
/// # Example
///
/// ```ignore
/// let rowid = search_hub::storage::insert_bookmark(&conn, &my_bookmark)
///     .expect("insert");
/// ```
///
/// # Parameters
///
/// * `conn`     - An open database connection.
/// * `bookmark` - The bookmark data to insert.
///
/// # Returns
///
/// The newly-assigned FTS5 rowid.
pub fn insert_bookmark(conn: &Connection, bookmark: &Bookmark) -> Result<i64> {
    let now = Utc::now();
    conn.execute(
        "INSERT INTO bookmarks (title, url, description, source, content, tags, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
        params![bookmark.title, bookmark.url, bookmark.description, bookmark.source, bookmark.content, bookmark.tags, now],
    )?;
    Ok(conn.last_insert_rowid())
}

/// Update the `content` and `tags` columns for an existing bookmark.
///
/// # Example
///
/// ```ignore
/// search_hub::storage::update_bookmark_content_tags(&conn, rowid, Some(md), Some("web, rust"))
///     .expect("update");
/// ```
///
/// # Parameters
///
/// * `conn`    - An open database connection.
/// * `rowid`   - The FTS5 rowid of the bookmark to update.
/// * `content` - New Markdown content (or `None` to clear).
/// * `tags`    - New comma-separated tags (or `None` to clear).
pub fn update_bookmark_content_tags(conn: &Connection, rowid: i64, content: Option<&str>, tags: Option<&str>) -> Result<()> {
    conn.execute(
        "UPDATE bookmarks SET content = ?, tags = ? WHERE rowid = ?",
        params![content, tags, rowid],
    )?;
    Ok(())
}

/// Return bookmarks from the database with pagination.
///
/// # Examples
///
/// ```ignore
/// let all = search_hub::storage::list_bookmarks(&conn, 1, 20)
///     .expect("list");
/// ```
///
/// # Parameters
///
/// * `conn`      - An open database connection.
/// * `page`      - 1-indexed page number.
/// * `page_size` - Results per page.
///
/// # Returns
///
/// A `Vec<Bookmark>` with the page of rows.
pub fn list_bookmarks(conn: &Connection, page: usize, page_size: usize) -> Result<Vec<Bookmark>> {
    let offset = (page.saturating_sub(1)) * page_size;
    let mut stmt = conn.prepare("SELECT rowid, title, url, description, source, content, tags, created_at FROM bookmarks ORDER BY rowid LIMIT ? OFFSET ?")?;
    let book_iter = stmt.query_map(params![page_size as i64, offset as i64], |row| {
        Ok(Bookmark {
            id: row.get(0)?,
            title: row.get(1)?,
            url: row.get(2)?,
            description: row.get(3)?,
            source: row.get(4)?,
            content: row.get(5)?,
            tags: row.get(6)?,
            created_at: row.get(7)?,
        })
    })?;

    let mut bookmarks = Vec::new();
    for book in book_iter {
        bookmarks.push(book?);
    }
    Ok(bookmarks)
}

/// Full-text search the bookmarks table using the FTS5 MATCH syntax.
///
/// # Example
///
/// ```ignore
/// let results = search_hub::storage::search_bookmarks(&conn, "rust tutorial", 1, 20)
///     .expect("search");
/// ```
///
/// # Parameters
///
/// * `conn`      - An open database connection.
/// * `query`     - An FTS5 search query string.
/// * `page`      - 1-indexed page number.
/// * `page_size` - Results per page.
///
/// # Returns
///
/// A `Vec<Bookmark>` matching the query (empty if none match).
pub fn search_bookmarks(conn: &Connection, query: &str, page: usize, page_size: usize) -> Result<Vec<Bookmark>> {
    let offset = (page.saturating_sub(1)) * page_size;
    let mut stmt = conn.prepare("SELECT rowid, title, url, description, source, content, tags, created_at FROM bookmarks WHERE bookmarks MATCH ? ORDER BY rank LIMIT ? OFFSET ?")?;
    let book_iter = stmt.query_map(params![query, page_size as i64, offset as i64], |row| {
        Ok(Bookmark {
            id: row.get(0)?,
            title: row.get(1)?,
            url: row.get(2)?,
            description: row.get(3)?,
            source: row.get(4)?,
            content: row.get(5)?,
            tags: row.get(6)?,
            created_at: row.get(7)?,
        })
    })?;

    let mut bookmarks = Vec::new();
    for book in book_iter {
        bookmarks.push(book?);
    }
    Ok(bookmarks)
}

/// Count bookmarks matching an FTS5 query.
pub fn count_search_bookmarks(conn: &Connection, query: &str) -> Result<usize> {
    let mut stmt = conn.prepare("SELECT COUNT(*) FROM bookmarks WHERE bookmarks MATCH ?")?;
    let count: i64 = stmt.query_row(params![query], |row| row.get(0))?;
    Ok(count as usize)
}

/// Fetch a single bookmark by its FTS5 rowid.
///
/// # Example
///
/// ```ignore
/// if let Some(bm) = search_hub::storage::get_bookmark(&conn, 1).expect("query") {
///     println!("{}", bm.title);
/// }
/// ```
///
/// # Parameters
///
/// * `conn`  - An open database connection.
/// * `rowid` - The FTS5 rowid to look up.
///
/// # Returns
///
/// `Some(Bookmark)` if found, or `None` if no row matches.
pub fn get_bookmark(conn: &Connection, rowid: i64) -> Result<Option<Bookmark>> {
    let mut stmt = conn.prepare("SELECT rowid, title, url, description, source, content, tags, created_at FROM bookmarks WHERE rowid = ?")?;
    let mut rows = stmt.query_map(params![rowid], |row| {
        Ok(Bookmark {
            id: row.get(0)?,
            title: row.get(1)?,
            url: row.get(2)?,
            description: row.get(3)?,
            source: row.get(4)?,
            content: row.get(5)?,
            tags: row.get(6)?,
            created_at: row.get(7)?,
        })
    })?;
    match rows.next() {
        Some(Ok(b)) => Ok(Some(b)),
        Some(Err(e)) => Err(e),
        None => Ok(None),
    }
}

/// Update the `tags` column for an existing bookmark (leaving content unchanged).
///
/// # Example
///
/// ```ignore
/// search_hub::storage::update_bookmark_tags(&conn, rowid, Some("web, rust"))
///     .expect("update");
/// ```
///
/// # Parameters
///
/// * `conn`  - An open database connection.
/// * `rowid` - The FTS5 rowid of the bookmark to update.
/// * `tags`  - New comma-separated tags (or `None` to clear).
pub fn update_bookmark_tags(conn: &Connection, rowid: i64, tags: Option<&str>) -> Result<()> {
    conn.execute(
        "UPDATE bookmarks SET tags = ? WHERE rowid = ?",
        params![tags, rowid],
    )?;
    Ok(())
}

/// Count all bookmarks in the database.
pub fn count_bookmarks(conn: &Connection) -> Result<usize> {
    let mut stmt = conn.prepare("SELECT COUNT(*) FROM bookmarks")?;
    let count: i64 = stmt.query_row([], |row| row.get(0))?;
    Ok(count as usize)
}

/// Delete a bookmark by its FTS5 rowid.
///
/// # Example
///
/// ```ignore
/// search_hub::storage::delete_bookmark(&conn, 1)
///     .expect("delete");
/// ```
///
/// # Parameters
///
/// * `conn` - An open database connection.
/// * `id`   - The FTS5 rowid to delete.
pub fn delete_bookmark(conn: &Connection, id: i32) -> Result<()> {
    conn.execute("DELETE FROM bookmarks WHERE rowid = ?", params![id])?;
    Ok(())
}

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

    fn test_db() -> Connection {
        init_db(":memory:").expect("init")
    }

    fn sample_bm() -> Bookmark {
        Bookmark {
            id: 0,
            title: "Test Page".into(),
            url: "https://example.com".into(),
            description: Some("A test".into()),
            source: "bookmark".into(),
            content: Some("some markdown content".into()),
            tags: Some("rust, test".into()),
            created_at: Utc::now(),
        }
    }

    #[test]
    fn insert_and_list() {
        let conn = test_db();
        insert_bookmark(&conn, &sample_bm()).expect("insert");
        let all = list_bookmarks(&conn, 1, 100).expect("list");
        assert_eq!(all.len(), 1);
        assert_eq!(all[0].title, "Test Page");
        assert_eq!(all[0].url, "https://example.com");
        assert_eq!(all[0].tags.as_deref(), Some("rust, test"));
    }

    #[test]
    fn insert_and_search() {
        let conn = test_db();
        insert_bookmark(&conn, &sample_bm()).expect("insert");
        let results = search_bookmarks(&conn, "Test", 1, 100).expect("search");
        assert_eq!(results.len(), 1);
        assert_eq!(results[0].title, "Test Page");
    }

    #[test]
    fn search_no_match() {
        let conn = test_db();
        insert_bookmark(&conn, &sample_bm()).expect("insert");
        let results = search_bookmarks(&conn, "zzznotfound", 1, 100).expect("search");
        assert!(results.is_empty());
    }

    #[test]
    fn insert_then_get() {
        let conn = test_db();
        let rowid = insert_bookmark(&conn, &sample_bm()).expect("insert");
        let bm = get_bookmark(&conn, rowid).expect("get");
        assert!(bm.is_some());
        assert_eq!(bm.unwrap().url, "https://example.com");
    }

    #[test]
    fn get_nonexistent() {
        let conn = test_db();
        let bm = get_bookmark(&conn, 999).expect("get");
        assert!(bm.is_none());
    }

    #[test]
    fn insert_then_delete() {
        let conn = test_db();
        let rowid = insert_bookmark(&conn, &sample_bm()).expect("insert");
        delete_bookmark(&conn, rowid as i32).expect("delete");
        let all = list_bookmarks(&conn, 1, 100).expect("list");
        assert!(all.is_empty());
    }

    #[test]
    fn update_content_and_tags() {
        let conn = test_db();
        let rowid = insert_bookmark(&conn, &sample_bm()).expect("insert");
        update_bookmark_content_tags(&conn, rowid, Some("new content"), Some("web, updated")).expect("update");
        let bm = get_bookmark(&conn, rowid).expect("get").unwrap();
        assert_eq!(bm.content.as_deref(), Some("new content"));
        assert_eq!(bm.tags.as_deref(), Some("web, updated"));
    }

    #[test]
    fn update_tags_only() {
        let conn = test_db();
        let rowid = insert_bookmark(&conn, &sample_bm()).expect("insert");
        update_bookmark_tags(&conn, rowid, Some("only-tags")).expect("update");
        let bm = get_bookmark(&conn, rowid).expect("get").unwrap();
        assert_eq!(bm.tags.as_deref(), Some("only-tags"));
        assert_eq!(bm.content.as_deref(), Some("some markdown content"));
    }

    #[test]
    fn exists_by_url() {
        let conn = test_db();
        insert_bookmark(&conn, &sample_bm()).expect("insert");
        assert!(bookmark_exists_by_url(&conn, "https://example.com").expect("exists"));
        assert!(!bookmark_exists_by_url(&conn, "https://other.com").expect("exists"));
    }

    #[test]
    fn list_multiple_bookmarks() {
        let conn = test_db();
        let b1 = Bookmark { title: "First".into(), url: "https://a.com".into(), ..sample_bm() };
        let b2 = Bookmark { title: "Second".into(), url: "https://b.com".into(), ..sample_bm() };
        insert_bookmark(&conn, &b1).expect("insert");
        insert_bookmark(&conn, &b2).expect("insert");
        assert_eq!(list_bookmarks(&conn, 1, 100).expect("list").len(), 2);
    }
}