use crate::importer::open_backup;
use crate::models::Bookmark;
use chrono::DateTime;
use std::path::{Path, PathBuf};

pub fn query_bookmarks(profile_path: &Path) -> anyhow::Result<Vec<Bookmark>> {
    let conn = open_backup(profile_path)?;

    let mut stmt = conn.prepare(
        "SELECT b.id, COALESCE(b.title, p.title, ''), p.url, p.description,
                CAST(b.dateAdded AS INTEGER) / 1000000
         FROM moz_bookmarks b
         JOIN moz_places p ON b.fk = p.id
         WHERE b.type = 1",
    )?;

    let bookmarks = stmt
        .query_map([], |row| {
            let id: i32 = row.get(0)?;
            let title: String = row.get(1)?;
            let url: String = row.get(2)?;
            let description: Option<String> = row.get(3)?;
            let timestamp: i64 = row.get(4)?;

            let created_at = DateTime::from_timestamp(timestamp, 0)
                .unwrap_or(DateTime::from_timestamp(0, 0).unwrap());

            Ok(Bookmark {
                id,
                title,
                url,
                description: description.filter(|d| !d.is_empty()),
                source: "bookmark".into(),
                content: None,
                tags: None,
                created_at,
            })
        })?
        .collect::<Result<Vec<_>, _>>()?;

    Ok(bookmarks)
}

pub fn query_history(profile_path: &Path) -> anyhow::Result<Vec<Bookmark>> {
    let conn = open_backup(profile_path)?;

    let mut stmt = conn.prepare(
        "SELECT DISTINCT p.id, COALESCE(p.title, ''), p.url, p.description,
                CAST(COALESCE(p.last_visit_date, 0) AS INTEGER) / 1000000
         FROM moz_places p
         JOIN moz_historyvisits v ON p.id = v.place_id
         WHERE p.hidden = 0
         ORDER BY p.last_visit_date DESC",
    )?;

    let entries = stmt
        .query_map([], |row| {
            let id: i32 = row.get(0)?;
            let title: String = row.get(1)?;
            let url: String = row.get(2)?;
            let description: Option<String> = row.get(3)?;
            let timestamp: i64 = row.get(4)?;

            let created_at = DateTime::from_timestamp(timestamp, 0)
                .unwrap_or(DateTime::from_timestamp(0, 0).unwrap());

            Ok(Bookmark {
                id,
                title,
                url,
                description: description.filter(|d| !d.is_empty()),
                source: "history".into(),
                content: None,
                tags: None,
                created_at,
            })
        })?
        .collect::<Result<Vec<_>, _>>()?;

    Ok(entries)
}

pub fn discover_profiles(base_dir: &str) -> Vec<PathBuf> {
    let mut profiles = Vec::new();
    let dir = home_dir().map(|p| p.join(base_dir));

    if let Some(dir) = dir {
        if let Ok(entries) = std::fs::read_dir(dir) {
            for entry in entries.flatten() {
                let path = entry.path();
                if path.is_dir() && path.join("places.sqlite").exists() {
                    profiles.push(path);
                }
            }
        }
    }

    profiles
}

fn home_dir() -> Option<PathBuf> {
    std::env::var("HOME")
        .ok()
        .map(PathBuf::from)
        .or_else(|| {
            if cfg!(target_os = "windows") {
                std::env::var("USERPROFILE").ok().map(PathBuf::from)
            } else {
                None
            }
        })
}