trackWork 0.15.0

use anyhow::Result;
use chrono::{Datelike, NaiveDateTime, NaiveDate};
use rusqlite::{Connection, params};
use std::path::Path;

use crate::models::TimeEntry;

pub struct Database {
    pub(crate) conn: Connection,
}

impl Database {
    pub fn new<P: AsRef<Path>>(path: P) -> Result<Self> {
        let conn = Connection::open(path)?;
        let db = Database { conn };
        db.init_schema()?;
        Ok(db)
    }

    fn init_schema(&self) -> Result<()> {
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS time_entries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                description TEXT NOT NULL,
                start_time TEXT NOT NULL,
                end_time TEXT,
                display_order INTEGER NOT NULL DEFAULT 0,
                color INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )",
            [],
        )?;

        // Create settings table for app metadata
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS app_settings (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            )",
            [],
        )?;

        // Migration: Add color column if it doesn't exist
        let has_color = self.conn.query_row(
            "SELECT COUNT(*) FROM pragma_table_info('time_entries') WHERE name='color'",
            [],
            |row| row.get::<_, i64>(0),
        )?;

        if has_color == 0 {
            self.conn.execute(
                "ALTER TABLE time_entries ADD COLUMN color INTEGER NOT NULL DEFAULT 0",
                [],
            )?;
        }

        // Migration: Add issue_key column if it doesn't exist
        let has_issue_key = self.conn.query_row(
            "SELECT COUNT(*) FROM pragma_table_info('time_entries') WHERE name='issue_key'",
            [],
            |row| row.get::<_, i64>(0),
        )?;

        if has_issue_key == 0 {
            self.conn.execute(
                "ALTER TABLE time_entries ADD COLUMN issue_key TEXT DEFAULT ''",
                [],
            )?;
        }

        // Migration: Add logged column if it doesn't exist
        let has_logged = self.conn.query_row(
            "SELECT COUNT(*) FROM pragma_table_info('time_entries') WHERE name='logged'",
            [],
            |row| row.get::<_, i64>(0),
        )?;

        if has_logged == 0 {
            self.conn.execute(
                "ALTER TABLE time_entries ADD COLUMN logged INTEGER NOT NULL DEFAULT 0",
                [],
            )?;
        }

        // Migration: Add off_work column if it doesn't exist
        let has_off_work = self.conn.query_row(
            "SELECT COUNT(*) FROM pragma_table_info('time_entries') WHERE name='off_work'",
            [],
            |row| row.get::<_, i64>(0),
        )?;

        if has_off_work == 0 {
            self.conn.execute(
                "ALTER TABLE time_entries ADD COLUMN off_work INTEGER NOT NULL DEFAULT 0",
                [],
            )?;
        }

        // Per-day "at work" overrides. A row exists only when the user has
        // manually set a start and/or end; otherwise the span is derived from
        // the day's task entries.
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS day_entries (
                date TEXT PRIMARY KEY,
                start_override TEXT,
                end_override TEXT
            )",
            [],
        )?;

        // Create tasks table
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                issue_key TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL DEFAULT '',
                project TEXT NOT NULL DEFAULT ''
            )",
            [],
        )?;

        // Migration: Add worklog_id column if it doesn't exist. Holds the Jira
        // worklog id from the last successful log so it can be deleted remotely.
        let has_worklog_id = self.conn.query_row(
            "SELECT COUNT(*) FROM pragma_table_info('time_entries') WHERE name='worklog_id'",
            [],
            |row| row.get::<_, i64>(0),
        )?;

        if has_worklog_id == 0 {
            self.conn.execute(
                "ALTER TABLE time_entries ADD COLUMN worklog_id TEXT DEFAULT ''",
                [],
            )?;
        }

        Ok(())
    }

    pub fn create_entry(&self, description: String, start_time: NaiveDateTime, end_time: Option<NaiveDateTime>, issue_key: String) -> Result<i64> {
        let max_order: i64 = self.conn.query_row(
            "SELECT COALESCE(MAX(display_order), -1) FROM time_entries WHERE date(start_time) = date(?1)",
            params![start_time.format("%Y-%m-%d %H:%M:%S").to_string()],
            |row| row.get(0),
        )?;

        // Get color usage for today to pick least used colors
        let mut stmt = self.conn.prepare(
            "SELECT color, COUNT(*) as count FROM time_entries
             WHERE date(start_time) = date(?1)
             GROUP BY color
             ORDER BY display_order DESC"
        )?;

        let color_usage = stmt.query_map(
            params![start_time.format("%Y-%m-%d %H:%M:%S").to_string()],
            |row| Ok((row.get::<_, i64>(0)? as u8, row.get::<_, i64>(1)?))
        )?.collect::<Result<Vec<_>, _>>()?;

        let color = TimeEntry::pick_best_color(&color_usage);

        self.conn.execute(
            "INSERT INTO time_entries (description, start_time, end_time, display_order, color, issue_key) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                description,
                start_time.format("%Y-%m-%d %H:%M:%S").to_string(),
                end_time.map(|t| t.format("%Y-%m-%d %H:%M:%S").to_string()),
                max_order + 1,
                color,
                issue_key
            ],
        )?;
        Ok(self.conn.last_insert_rowid())
    }

    pub fn get_entries_for_date(&self, date: NaiveDate) -> Result<Vec<TimeEntry>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, description, start_time, end_time, display_order, COALESCE(color, 0), COALESCE(issue_key, ''), COALESCE(logged, 0), COALESCE(off_work, 0), COALESCE(worklog_id, '')
             FROM time_entries
             WHERE date(start_time) = ?1
             ORDER BY display_order ASC"
        )?;

        let entries = stmt.query_map(params![date.to_string()], |row| {
            Ok(TimeEntry {
                id: row.get(0)?,
                description: row.get(1)?,
                start_time: NaiveDateTime::parse_from_str(&row.get::<_, String>(2)?, "%Y-%m-%d %H:%M:%S")
                    .map_err(|e| rusqlite::Error::FromSqlConversionFailure(2, rusqlite::types::Type::Text, Box::new(e)))?,
                end_time: row.get::<_, Option<String>>(3)?
                    .and_then(|s| NaiveDateTime::parse_from_str(&s, "%Y-%m-%d %H:%M:%S").ok()),
                display_order: row.get(4)?,
                color: row.get::<_, i64>(5)? as u8,
                issue_key: row.get(6)?,
                logged: row.get::<_, i64>(7)? != 0,
                off_work: row.get::<_, i64>(8)? != 0,
                worklog_id: row.get(9)?,
            })
        })?
        .collect::<Result<Vec<_>, _>>()?;

        Ok(entries)
    }

    pub fn update_entry(&self, id: i64, description: String, start_time: NaiveDateTime, end_time: Option<NaiveDateTime>, issue_key: String) -> Result<()> {
        let _rows_affected = self.conn.execute(
            "UPDATE time_entries SET description = ?1, start_time = ?2, end_time = ?3, issue_key = ?4 WHERE id = ?5",
            params![
                description,
                start_time.format("%Y-%m-%d %H:%M:%S").to_string(),
                end_time.map(|t| t.format("%Y-%m-%d %H:%M:%S").to_string()),
                issue_key,
                id
            ],
        )?;
        Ok(())
    }

    pub fn delete_entry(&self, id: i64) -> Result<()> {
        self.conn.execute("DELETE FROM time_entries WHERE id = ?1", params![id])?;
        Ok(())
    }

    /// Auto-close any running timers whose `start_time` is from a prior local
    /// day. Sets `end_time` to 23:59:59 of the entry's own start date so the
    /// entry doesn't bleed into following days. Called at startup and again
    /// whenever the local date rolls over while the app is running.
    /// Returns the number of entries that were closed.
    pub fn auto_end_stale_running(&self, today: NaiveDate) -> Result<usize> {
        let today_str = today.to_string();
        let affected = self.conn.execute(
            "UPDATE time_entries
             SET end_time = strftime('%Y-%m-%d %H:%M:%S', date(start_time) || ' 23:59:59')
             WHERE end_time IS NULL AND date(start_time) < ?1",
            params![today_str],
        )?;
        Ok(affected)
    }

    pub fn reorder_entries(&self, date: NaiveDate, from_idx: usize, to_idx: usize) -> Result<()> {
        let mut entries = self.get_entries_for_date(date)?;

        if from_idx >= entries.len() || to_idx >= entries.len() {
            return Ok(());
        }

        let entry = entries.remove(from_idx);
        entries.insert(to_idx, entry);

        // Update display_order for all entries
        for (idx, entry) in entries.iter().enumerate() {
            self.conn.execute(
                "UPDATE time_entries SET display_order = ?1 WHERE id = ?2",
                params![idx as i64, entry.id],
            )?;
        }

        Ok(())
    }

    pub fn get_total_duration_for_date(&self, date: NaiveDate) -> Result<i64> {
        let entries = self.get_entries_for_date(date)?;
        let total_minutes: i64 = entries.iter()
            .filter(|e| !e.off_work)
            .filter_map(|e| e.duration_minutes())
            .sum();
        Ok(total_minutes)
    }

    pub fn get_weekly_stats(&self, date: NaiveDate) -> Result<Vec<(String, i64)>> {
        // Get the start of the week (Monday)
        let days_from_monday = date.weekday().num_days_from_monday();
        let week_start = date - chrono::Duration::days(days_from_monday as i64);
        let week_end = week_start + chrono::Duration::days(7);

        // For entries with issue_key, group by issue_key
        let mut stmt_with_issue = self.conn.prepare(
            "SELECT issue_key, SUM(
                CAST((julianday(COALESCE(end_time, datetime('now', 'localtime'))) - julianday(start_time)) * 24 * 60 AS INTEGER)
            ) as total_minutes
            FROM time_entries
            WHERE date(start_time) >= ?1 AND date(start_time) < ?2
              AND issue_key IS NOT NULL AND issue_key != ''
              AND COALESCE(off_work, 0) = 0
            GROUP BY issue_key
            ORDER BY total_minutes DESC"
        )?;

        let mut stats: Vec<(String, i64)> = stmt_with_issue.query_map(
            params![week_start.to_string(), week_end.to_string()],
            |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, i64>(1)?
                ))
            }
        )?
        .collect::<Result<Vec<_>, _>>()?;

        // For entries without issue_key, group by description
        let mut stmt_without_issue = self.conn.prepare(
            "SELECT description, SUM(
                CAST((julianday(COALESCE(end_time, datetime('now', 'localtime'))) - julianday(start_time)) * 24 * 60 AS INTEGER)
            ) as total_minutes
            FROM time_entries
            WHERE date(start_time) >= ?1 AND date(start_time) < ?2
              AND (issue_key IS NULL OR issue_key = '')
              AND COALESCE(off_work, 0) = 0
            GROUP BY description
            ORDER BY total_minutes DESC"
        )?;

        let no_issue_stats: Vec<(String, i64)> = stmt_without_issue.query_map(
            params![week_start.to_string(), week_end.to_string()],
            |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, i64>(1)?
                ))
            }
        )?
        .collect::<Result<Vec<_>, _>>()?;

        // Append no-issue stats at the end
        stats.extend(no_issue_stats);

        Ok(stats)
    }

    pub fn get_previous_tasks_with_issue_keys(&self) -> Result<Vec<(String, String, i64)>> {
        // Get unique tasks (by issue_key) with smart scoring:
        // - 1 point per usage
        // - Extra (4 - weeks_since) points for tasks used within last 4 weeks
        // Returns (issue_key, most_recent_description, usage_count)
        // Sorted by score but returns usage_count for display
        let mut stmt = self.conn.prepare(
            "SELECT
                issue_key,
                description,
                COUNT(*) as usage_count,
                MAX(start_time) as last_used,
                (
                    COUNT(*) +
                    SUM(
                        CASE
                            WHEN julianday('now') - julianday(start_time) <= 28
                            THEN (4 - CAST((julianday('now') - julianday(start_time)) / 7 AS INTEGER))
                            ELSE 0
                        END
                    )
                ) as score
             FROM time_entries
             WHERE issue_key IS NOT NULL AND issue_key != ''
             GROUP BY issue_key
             ORDER BY score DESC, last_used DESC
             LIMIT 50"
        )?;

        let tasks = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, i64>(2)?  // usage_count
            ))
        })?
        .collect::<Result<Vec<_>, _>>()?;

        Ok(tasks)
    }

    pub fn toggle_logged(&self, id: i64) -> Result<()> {
        self.conn.execute(
            "UPDATE time_entries SET logged = NOT logged WHERE id = ?1",
            params![id],
        )?;
        Ok(())
    }

    /// Mark an entry as logged and store the Jira worklog id (empty if none).
    pub fn mark_logged(&self, id: i64, worklog_id: &str) -> Result<()> {
        self.conn.execute(
            "UPDATE time_entries SET logged = 1, worklog_id = ?2 WHERE id = ?1",
            params![id, worklog_id],
        )?;
        Ok(())
    }

    /// Clear an entry's logged flag and any stored worklog id.
    pub fn unmark_logged(&self, id: i64) -> Result<()> {
        self.conn.execute(
            "UPDATE time_entries SET logged = 0, worklog_id = '' WHERE id = ?1",
            params![id],
        )?;
        Ok(())
    }

    pub fn toggle_off_work(&self, id: i64) -> Result<()> {
        self.conn.execute(
            "UPDATE time_entries SET off_work = NOT off_work WHERE id = ?1",
            params![id],
        )?;
        Ok(())
    }

    /// Returns the manual (start_override, end_override) for a day, if set.
    pub fn get_day_overrides(&self, date: NaiveDate) -> Result<(Option<NaiveDateTime>, Option<NaiveDateTime>)> {
        let result = self.conn.query_row(
            "SELECT start_override, end_override FROM day_entries WHERE date = ?1",
            params![date.to_string()],
            |row| Ok((row.get::<_, Option<String>>(0)?, row.get::<_, Option<String>>(1)?)),
        );

        let parse = |s: Option<String>| s.and_then(|s| NaiveDateTime::parse_from_str(&s, "%Y-%m-%d %H:%M:%S").ok());

        match result {
            Ok((start, end)) => Ok((parse(start), parse(end))),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok((None, None)),
            Err(e) => Err(e.into()),
        }
    }

    /// Persist the manual overrides for a day. Passing both as None removes the row.
    pub fn set_day_overrides(&self, date: NaiveDate, start: Option<NaiveDateTime>, end: Option<NaiveDateTime>) -> Result<()> {
        if start.is_none() && end.is_none() {
            self.conn.execute("DELETE FROM day_entries WHERE date = ?1", params![date.to_string()])?;
            return Ok(());
        }

        let fmt = |t: Option<NaiveDateTime>| t.map(|t| t.format("%Y-%m-%d %H:%M:%S").to_string());
        self.conn.execute(
            "INSERT INTO day_entries (date, start_override, end_override) VALUES (?1, ?2, ?3)
             ON CONFLICT(date) DO UPDATE SET start_override = ?2, end_override = ?3",
            params![date.to_string(), fmt(start), fmt(end)],
        )?;
        Ok(())
    }

    /// Read an arbitrary value from the app_settings key/value table.
    pub fn get_app_setting(&self, key: &str) -> Result<Option<String>> {
        let result = self.conn.query_row(
            "SELECT value FROM app_settings WHERE key = ?1",
            params![key],
            |row| row.get::<_, String>(0),
        );
        match result {
            Ok(value) => Ok(Some(value)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    /// Write an arbitrary value to the app_settings key/value table.
    pub fn set_app_setting(&self, key: &str, value: &str) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO app_settings (key, value) VALUES (?1, ?2)",
            params![key, value],
        )?;
        Ok(())
    }

    pub fn get_last_seen_version(&self) -> Result<Option<String>> {
        let result = self.conn.query_row(
            "SELECT value FROM app_settings WHERE key = 'last_seen_version'",
            [],
            |row| row.get::<_, String>(0),
        );

        match result {
            Ok(version) => Ok(Some(version)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    pub fn set_last_seen_version(&self, version: &str) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO app_settings (key, value) VALUES ('last_seen_version', ?1)",
            params![version],
        )?;
        Ok(())
    }

}