use dirs::data_dir;
use rusqlite::{Connection, Result};
use std::fs;
use std::fs::File;
use std::io::{BufRead, BufReader};

use crate::filter::sanitize_command;
use crate::util::find_project_root;

#[derive(Debug)]
pub struct Event {
    pub ts: i64,
    pub pwd: String,
    pub cmd: String,
    pub exit_code: i32,
    pub duration: i64,
}

pub fn recap_dir() -> std::path::PathBuf {
    let mut path = data_dir().expect("no data dir");
    path.push("recap");
    fs::create_dir_all(&path).ok();
    path
}

pub fn init_db(dir: &std::path::Path) -> Result<Connection> {
    let conn = Connection::open(dir.join("recap.db"))?;
    conn.execute_batch(
        "
        CREATE TABLE IF NOT EXISTS events (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            ts        INTEGER NOT NULL,
            pwd       TEXT    NOT NULL,
            cmd       TEXT    NOT NULL,
            exit_code INTEGER NOT NULL,
            duration  INTEGER NOT NULL,
            CONSTRAINT no_break UNIQUE (ts, cmd)
        );
        CREATE INDEX IF NOT EXISTS idx_ts  ON events(ts);
        CREATE INDEX IF NOT EXISTS idx_pwd ON events(pwd);
    ",
    )?;
    Ok(conn)
}

pub fn flush_events(
    conn: &Connection,
    dir: &std::path::Path,
) -> Result<usize, Box<dyn std::error::Error>> {
    let tsv_path = dir.join("events.tsv");
    if !tsv_path.exists() {
        return Ok(0);
    }

    // Rename before reading so any events written by the shell after this point
    // land in a fresh events.tsv and are not lost when we remove the tmp file.
    let tmp_path = dir.join("events.tsv.flushing");
    fs::rename(&tsv_path, &tmp_path)?;

    let file = File::open(&tmp_path)?;
    let reader = BufReader::new(file);

    let mut stmt = conn.prepare(
        "INSERT OR IGNORE INTO events (ts, pwd, cmd, exit_code, duration)
         VALUES (?1, ?2, ?3, ?4, ?5)",
    )?;

    let mut count = 0;
    let tx = conn.unchecked_transaction()?;

    for line in reader.lines().map_while(Result::ok) {
        let parts: Vec<&str> = line.splitn(5, '\t').collect();
        if parts.len() == 5 {
            let ts: i64 = parts[0].parse().unwrap_or(0);
            let exit: i32 = parts[3].parse().unwrap_or(0);
            let dur: i64 = parts[4].parse().unwrap_or(0);
            let sanitized = sanitize_command(parts[2]);
            if stmt
                .execute(rusqlite::params![ts, parts[1], sanitized, exit, dur])
                .is_ok()
            {
                count += 1;
            }
        }
    }

    tx.commit()?;
    fs::remove_file(&tmp_path).ok();
    Ok(count)
}

pub fn load_events(conn: &Connection, start_ms: i64, end_ms: i64) -> Result<Vec<Event>> {
    let mut stmt = conn.prepare(
        "SELECT ts, pwd, cmd, exit_code, duration FROM events
         WHERE ts >= ?1 AND ts <= ?2
         ORDER BY ts ASC",
    )?;
    let events = stmt
        .query_map([start_ms, end_ms], |row| {
            Ok(Event {
                ts: row.get(0)?,
                pwd: row.get(1)?,
                cmd: row.get(2)?,
                exit_code: row.get(3)?,
                duration: row.get(4)?,
            })
        })?
        .flatten()
        .collect();
    Ok(events)
}

pub fn day_bounds(date: chrono::NaiveDate) -> (i64, i64) {
    let start = date
        .and_hms_opt(0, 0, 0)
        .unwrap()
        .and_local_timezone(chrono::Local)
        .unwrap()
        .timestamp_millis();
    let end = date
        .and_hms_opt(23, 59, 59)
        .unwrap()
        .and_local_timezone(chrono::Local)
        .unwrap()
        .timestamp_millis();
    (start, end)
}

pub fn load_yesterday(conn: &Connection) -> Result<Vec<Event>> {
    let yesterday = chrono::Local::now().date_naive().pred_opt().unwrap();
    let (start, end) = day_bounds(yesterday);
    load_events(conn, start, end)
}

pub fn load_today(conn: &Connection) -> Result<Vec<Event>> {
    let (start, end) = day_bounds(chrono::Local::now().date_naive());
    load_events(conn, start, end)
}

pub fn load_date(conn: &Connection, date: chrono::NaiveDate) -> Result<Vec<Event>> {
    let (start, end) = day_bounds(date);
    load_events(conn, start, end)
}

pub fn load_week_offset(conn: &Connection, weeks_ago: i64) -> Result<Vec<Event>> {
    let now = chrono::Local::now();
    let start = (now - chrono::Duration::days(weeks_ago * 7 + 6))
        .date_naive()
        .and_hms_opt(0, 0, 0)
        .unwrap()
        .and_local_timezone(chrono::Local)
        .unwrap()
        .timestamp_millis();
    let end = (now - chrono::Duration::days(weeks_ago * 7))
        .date_naive()
        .and_hms_opt(23, 59, 59)
        .unwrap()
        .and_local_timezone(chrono::Local)
        .unwrap()
        .timestamp_millis();
    load_events(conn, start, end)
}

pub fn load_project(conn: &Connection, project: &str) -> Result<Vec<Event>> {
    // Pre-filter with LIKE for performance, then exact-match on the git project root name.
    let mut stmt = conn.prepare(
        "SELECT ts, pwd, cmd, exit_code, duration FROM events
         WHERE pwd LIKE '%' || ?1 || '%'
         ORDER BY ts DESC",
    )?;
    let project_lower = project.to_lowercase();
    let candidates: Vec<Event> = stmt
        .query_map([project], |row| {
            Ok(Event {
                ts: row.get(0)?,
                pwd: row.get(1)?,
                cmd: row.get(2)?,
                exit_code: row.get(3)?,
                duration: row.get(4)?,
            })
        })?
        .flatten()
        .collect();
    let events = candidates
        .into_iter()
        .filter(|e| {
            find_project_root(&e.pwd)
                .and_then(|r| {
                    std::path::Path::new(&r)
                        .file_name()
                        .map(|f| f.to_string_lossy().to_lowercase() == project_lower)
                })
                .unwrap_or(false)
        })
        .collect();
    Ok(events)
}

pub fn load_search(conn: &Connection, query: &str) -> Result<Vec<Event>> {
    let mut stmt = conn.prepare(
        "SELECT ts, pwd, cmd, exit_code, duration FROM events
         WHERE cmd LIKE '%' || ?1 || '%'
         ORDER BY ts DESC
         LIMIT 50",
    )?;
    let events = stmt
        .query_map([query], |row| {
            Ok(Event {
                ts: row.get(0)?,
                pwd: row.get(1)?,
                cmd: row.get(2)?,
                exit_code: row.get(3)?,
                duration: row.get(4)?,
            })
        })?
        .flatten()
        .collect();
    Ok(events)
}

pub fn prune_events(conn: &Connection, days: i64) -> Result<usize, Box<dyn std::error::Error>> {
    let cutoff = (chrono::Local::now() - chrono::Duration::days(days)).timestamp_millis();
    let deleted = conn.execute("DELETE FROM events WHERE ts < ?1", [cutoff])?;
    Ok(deleted)
}

pub fn is_database_empty(conn: &Connection) -> Result<bool, Box<dyn std::error::Error>> {
    let count: i64 = conn.query_row("SELECT COUNT(*) FROM events", [], |row| row.get(0))?;
    Ok(count == 0)
}
