use crate::database::Database;
use crate::error::FsPulseError;
use crate::roots::Root;

use rusqlite::{params, Connection, OptionalExtension, Result};
use serde::Serialize;

use std::fmt;

const SQL_SCAN_ID_OR_LATEST: &str =
    "SELECT scan_id, root_id, schedule_id, started_at, ended_at, was_restarted, state, is_hash, hash_all, is_val, val_all, file_count, folder_count, total_size, alert_count, add_count, modify_count, delete_count, error
        FROM scans
        WHERE scan_id = IFNULL(?1, (SELECT MAX(scan_id) FROM scans))";

const SQL_LATEST_FOR_ROOT: &str =
    "SELECT scan_id, root_id, schedule_id, started_at, ended_at, was_restarted, state, is_hash, hash_all, is_val, val_all, file_count, folder_count, total_size, alert_count, add_count, modify_count, delete_count, error
        FROM scans
        WHERE root_id = ?
        ORDER BY scan_id DESC LIMIT 1";

#[derive(Copy, Clone, Debug, PartialEq, serde::Serialize, serde::Deserialize)]
#[repr(i32)]
pub enum HashMode {
    None = 0,
    New = 1,
    All = 2,
}

impl HashMode {
    pub fn from_i32(value: i32) -> Option<Self> {
        match value {
            0 => Some(Self::None),
            1 => Some(Self::New),
            2 => Some(Self::All),
            _ => None,
        }
    }

    pub fn as_i32(self) -> i32 {
        self as i32
    }
}

#[derive(Copy, Clone, Debug, PartialEq, serde::Serialize, serde::Deserialize)]
#[repr(i32)]
pub enum ValidateMode {
    None = 0,
    New = 1,
    All = 2,
}

impl ValidateMode {
    pub fn from_i32(value: i32) -> Option<Self> {
        match value {
            0 => Some(Self::None),
            1 => Some(Self::New),
            2 => Some(Self::All),
            _ => None,
        }
    }

    pub fn as_i32(self) -> i32 {
        self as i32
    }
}

#[derive(Copy, Clone, Debug)]
pub struct AnalysisSpec {
    hash_mode: HashMode,
    val_mode: ValidateMode,
}

impl AnalysisSpec {
    pub fn from_modes(hash_mode: HashMode, val_mode: ValidateMode) -> Self {
        AnalysisSpec {
            hash_mode,
            val_mode,
        }
    }

    pub fn is_hash(&self) -> bool {
        self.hash_mode != HashMode::None
    }

    pub fn hash_all(&self) -> bool {
        self.hash_mode == HashMode::All
    }

    pub fn is_val(&self) -> bool {
        self.val_mode != ValidateMode::None
    }

    pub fn val_all(&self) -> bool {
        self.val_mode == ValidateMode::All
    }
}

#[derive(Clone, Debug)]
pub struct Scan {
    // Schema fields
    scan_id: i64,
    root_id: i64,
    #[allow(dead_code)]
    schedule_id: Option<i64>,
    #[allow(dead_code)]
    started_at: i64,
    #[allow(dead_code)]
    ended_at: Option<i64>,
    #[allow(dead_code)]
    was_restarted: bool,
    state: ScanState,
    analysis_spec: AnalysisSpec,
    file_count: Option<i64>,
    folder_count: Option<i64>,
    total_size: Option<i64>,
    alert_count: Option<i64>,
    add_count: Option<i64>,
    modify_count: Option<i64>,
    delete_count: Option<i64>,
    error: Option<String>,
}

#[derive(Debug, Copy, Clone, PartialEq, Eq, PartialOrd, Ord)]
#[repr(i64)] // Ensures explicit numeric representation
pub enum ScanState {
    Scanning = 1,
    Sweeping = 2,
    Analyzing = 3,
    Completed = 4,
    Stopped = 5,
    Error = 6,
}

impl ScanState {
    pub fn from_i64(value: i64) -> Self {
        match value {
            1 => ScanState::Scanning,
            2 => ScanState::Sweeping,
            3 => ScanState::Analyzing,
            4 => ScanState::Completed,
            5 => ScanState::Stopped,
            6 => ScanState::Error,
            _ => panic!("Invalid ScanState value: {}", value),
        }
    }

    pub fn as_i64(&self) -> i64 {
        *self as i64
    }

    pub fn short_name(&self) -> &'static str {
        match self {
            ScanState::Scanning => "S",
            ScanState::Sweeping => "W",
            ScanState::Analyzing => "A",
            ScanState::Completed => "C",
            ScanState::Stopped => "P",
            ScanState::Error => "E",
        }
    }

    pub fn full_name(&self) -> &'static str {
        match self {
            ScanState::Scanning => "Scanning",
            ScanState::Sweeping => "Sweeping",
            ScanState::Analyzing => "Analyzing",
            ScanState::Completed => "Completed",
            ScanState::Stopped => "Stopped",
            ScanState::Error => "Error",
        }
    }

    pub fn from_string(s: &str) -> Option<Self> {
        // Try to match against full name or short name (case-insensitive)
        match s.to_ascii_uppercase().as_str() {
            // Full names
            "SCANNING" => Some(ScanState::Scanning),
            "SWEEPING" => Some(ScanState::Sweeping),
            "ANALYZING" => Some(ScanState::Analyzing),
            "COMPLETED" => Some(ScanState::Completed),
            "STOPPED" => Some(ScanState::Stopped),
            "ERROR" => Some(ScanState::Error),
            // Short names
            "S" => Some(ScanState::Scanning),
            "W" => Some(ScanState::Sweeping),
            "A" => Some(ScanState::Analyzing),
            "C" => Some(ScanState::Completed),
            "P" => Some(ScanState::Stopped),
            "E" => Some(ScanState::Error),
            _ => None,
        }
    }
}

impl fmt::Display for ScanState {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        write!(f, "{}", self.full_name())
    }
}

impl crate::query::QueryEnum for ScanState {
    fn from_token(s: &str) -> Option<i64> {
        Self::from_string(s).map(|state| state.as_i64())
    }
}

impl Scan {
    // Create a Scan that will be used during a directory scan
    // In this case, the scan_id is not yet known
    fn new_for_scan(
        scan_id: i64,
        root_id: i64,
        schedule_id: Option<i64>,
        started_at: i64,
        state: i64,
        analysis_spec: AnalysisSpec,
    ) -> Self {
        Scan {
            scan_id,
            root_id,
            schedule_id,
            started_at,
            ended_at: None,
            was_restarted: false,
            state: ScanState::from_i64(state),
            analysis_spec,
            file_count: None,
            folder_count: None,
            total_size: None,
            alert_count: None,
            add_count: None,
            modify_count: None,
            delete_count: None,
            error: None,
        }
    }

    pub fn create(
        conn: &rusqlite::Connection,
        root: &Root,
        schedule_id: Option<i64>,
        analysis_spec: &AnalysisSpec,
    ) -> Result<Self, FsPulseError> {
        let (scan_id, started_at): (i64, i64) = conn.query_row(
            "INSERT INTO scans (root_id, schedule_id, state, is_hash, hash_all, is_val, val_all, started_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, strftime('%s', 'now', 'utc'))
             RETURNING scan_id, started_at",
            params![
                root.root_id(),
                schedule_id,
                ScanState::Scanning.as_i64(),
                analysis_spec.is_hash() as i64,
                analysis_spec.hash_all() as i64,
                analysis_spec.is_val() as i64,
                analysis_spec.val_all() as i64,
            ],
            |row| Ok((row.get(0)?, row.get(1)?)),
        )?;

        let scan = Scan::new_for_scan(
            scan_id,
            root.root_id(),
            schedule_id,
            started_at,
            ScanState::Scanning.as_i64(),
            *analysis_spec,
        );
        Ok(scan)
    }

    pub fn get_latest_for_root(root_id: i64) -> Result<Option<Self>, FsPulseError> {
        let conn = Database::get_connection()?;
        Self::get_by_id_or_latest(&conn, None, Some(root_id))
    }

    pub fn get_by_id_or_latest(
        conn: &rusqlite::Connection,
        scan_id: Option<i64>,
        root_id: Option<i64>,
    ) -> Result<Option<Self>, FsPulseError> {
        let (query, query_param) = match (scan_id, root_id) {
            (Some(_), _) => (SQL_SCAN_ID_OR_LATEST, scan_id),
            (_, Some(_)) => (SQL_LATEST_FOR_ROOT, root_id),
            _ => (SQL_SCAN_ID_OR_LATEST, None),
        };

        // If the scan id wasn't explicitly specified, load the most recent otherwise,
        // load the specified scan
        let scan_row: Option<Scan> = conn
            .query_row(query, params![query_param], |row| {
                let is_hash = row.get(7)?;
                let hash_all = row.get(8)?;
                let hash_mode = match (is_hash, hash_all) {
                    (false, _) => HashMode::None,
                    (_, true) => HashMode::All,
                    _ => HashMode::New,
                };

                let is_val = row.get(9)?;
                let val_all = row.get(10)?;

                let val_mode = match (is_val, val_all) {
                    (false, _) => ValidateMode::None,
                    (_, true) => ValidateMode::All,
                    _ => ValidateMode::New,
                };

                Ok(Scan {
                    scan_id: row.get(0)?,
                    root_id: row.get(1)?,
                    schedule_id: row.get(2)?,
                    started_at: row.get(3)?,
                    ended_at: row.get(4)?,
                    was_restarted: row.get(5)?,
                    state: ScanState::from_i64(row.get(6)?),
                    analysis_spec: AnalysisSpec {
                        hash_mode,
                        val_mode,
                    },
                    file_count: row.get(11)?,
                    folder_count: row.get(12)?,
                    total_size: row.get(13)?,
                    alert_count: row.get(14)?,
                    add_count: row.get(15)?,
                    modify_count: row.get(16)?,
                    delete_count: row.get(17)?,
                    error: row.get(18)?,
                })
            })
            .optional()?;

        Ok(scan_row)
    }

    pub fn scan_id(&self) -> i64 {
        self.scan_id
    }

    pub fn root_id(&self) -> i64 {
        self.root_id
    }

    pub fn state(&self) -> ScanState {
        self.state
    }

    pub fn analysis_spec(&self) -> &AnalysisSpec {
        &self.analysis_spec
    }

    pub fn started_at(&self) -> i64 {
        self.started_at
    }

    pub fn error(&self) -> Option<&str> {
        self.error.as_deref()
    }

    pub fn file_count(&self) -> Option<i64> {
        self.file_count
    }

    pub fn folder_count(&self) -> Option<i64> {
        self.folder_count
    }

    pub fn total_size(&self) -> Option<i64> {
        self.total_size
    }

    pub fn alert_count(&self) -> Option<i64> {
        self.alert_count
    }

    pub fn add_count(&self) -> Option<i64> {
        self.add_count
    }

    pub fn modify_count(&self) -> Option<i64> {
        self.modify_count
    }

    pub fn delete_count(&self) -> Option<i64> {
        self.delete_count
    }

    pub fn set_total_size(
        &mut self,
        conn: &Connection,
        total_size: i64,
    ) -> Result<(), FsPulseError> {
        let rows_updated = conn.execute(
            "UPDATE scans SET total_size = ? WHERE scan_id = ?",
            [total_size, self.scan_id],
        )?;

        if rows_updated == 0 {
            return Err(FsPulseError::Error(format!(
                "Could not update the total_size of Scan Id {} to {}",
                self.scan_id, total_size
            )));
        }

        self.total_size = Some(total_size);

        Ok(())
    }

    pub fn set_state_sweeping(&mut self, conn: &Connection) -> Result<(), FsPulseError> {
        match self.state() {
            ScanState::Scanning => self.set_state(conn, ScanState::Sweeping),
            _ => Err(FsPulseError::Error(format!(
                "Can't set Scan Id {} to state sweeping from state {}",
                self.scan_id(),
                self.state().as_i64()
            ))),
        }
    }

    pub fn set_state_analyzing(&mut self, conn: &Connection) -> Result<(), FsPulseError> {
        match self.state() {
            ScanState::Sweeping => self.set_state(conn, ScanState::Analyzing),
            _ => Err(FsPulseError::Error(format!(
                "Can't set Scan Id {} to state analyzing from state {}",
                self.scan_id(),
                self.state().as_i64()
            ))),
        }
    }

    pub fn set_state_completed(&mut self, conn: &Connection) -> Result<(), FsPulseError> {
        match self.state() {
            ScanState::Analyzing => {
                // Use IMMEDIATE transaction for read-then-write pattern
                let (file_count, folder_count, alert_count, add_count, modify_count, delete_count) =
                    Database::immediate_transaction(conn, |c| {
                        // Compute file_count and folder_count (exclude tombstones)
                        let (file_count, folder_count): (i64, i64) = c
                            .query_row(
                                "SELECT
                                SUM(CASE WHEN item_type = 0 THEN 1 ELSE 0 END) AS file_count,
                                SUM(CASE WHEN item_type = 1 THEN 1 ELSE 0 END) AS folder_count
                                FROM items WHERE last_scan = ? AND is_ts = 0",
                                [self.scan_id],
                                |row| Ok((row.get(0)?, row.get(1)?)),
                            )
                            .unwrap_or((0, 0));

                        // Compute alert_count
                        let alert_count: i64 = c
                            .query_row(
                                "SELECT COUNT(*) FROM alerts WHERE scan_id = ?",
                                [self.scan_id],
                                |row| row.get(0),
                            )
                            .unwrap_or(0);

                        // Compute add_count, modify_count, delete_count
                        let (add_count, modify_count, delete_count): (i64, i64, i64) = c
                            .query_row(
                                "SELECT
                                COUNT(*) FILTER (WHERE change_type = 1),
                                COUNT(*) FILTER (WHERE change_type = 2),
                                COUNT(*) FILTER (WHERE change_type = 3)
                                FROM changes WHERE scan_id = ?",
                                [self.scan_id],
                                |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
                            )
                            .unwrap_or((0, 0, 0));

                        // Update the scan with all counts and set state to Completed in one operation
                        c.execute(
                            "UPDATE scans SET
                                file_count = ?,
                                folder_count = ?,
                                alert_count = ?,
                                add_count = ?,
                                modify_count = ?,
                                delete_count = ?,
                                state = ?,
                                ended_at = strftime('%s', 'now', 'utc')
                            WHERE scan_id = ?",
                            (
                                file_count,
                                folder_count,
                                alert_count,
                                add_count,
                                modify_count,
                                delete_count,
                                ScanState::Completed.as_i64(),
                                self.scan_id,
                            ),
                        )?;

                        Ok((
                            file_count,
                            folder_count,
                            alert_count,
                            add_count,
                            modify_count,
                            delete_count,
                        ))
                    })?;

                // Update in-memory struct
                self.state = ScanState::Completed;
                self.file_count = Some(file_count);
                self.folder_count = Some(folder_count);
                self.alert_count = Some(alert_count);
                self.add_count = Some(add_count);
                self.modify_count = Some(modify_count);
                self.delete_count = Some(delete_count);

                Ok(())
            }
            _ => Err(FsPulseError::Error(format!(
                "Can't set Scan Id {} to state completed from state {}",
                self.scan_id(),
                self.state().as_i64()
            ))),
        }
    }

    pub fn set_state_stopped(&mut self, conn: &Connection) -> Result<(), FsPulseError> {
        match self.state() {
            ScanState::Scanning | ScanState::Sweeping | ScanState::Analyzing => {
                Scan::stop_scan(conn, self, None)?;
                self.state = ScanState::Stopped;
                Ok(())
            }
            _ => Err(FsPulseError::Error(format!(
                "Can't stop scan - invalid state {}",
                self.state().as_i64()
            ))),
        }
    }

    fn set_state(&mut self, conn: &Connection, new_state: ScanState) -> Result<(), FsPulseError> {
        let rows_updated = conn.execute(
            "UPDATE scans SET state = ? WHERE scan_id = ?",
            [new_state.as_i64(), self.scan_id],
        )?;

        if rows_updated == 0 {
            return Err(FsPulseError::Error(format!(
                "Could not update the state of Scan Id {} to {}",
                self.scan_id,
                new_state.as_i64()
            )));
        }

        self.state = new_state;

        Ok(())
    }

    pub fn stop_scan(
        conn: &Connection,
        scan: &Scan,
        error_message: Option<&str>,
    ) -> Result<(), FsPulseError> {
        Database::immediate_transaction(conn, |c| {
            // Find the id of the last scan on this root to not be stopped
            // We'll restore this scan_id to all partially updated by the
            // scan being stopped
            let prev_scan_id: i64 = c.query_row(
                "SELECT COALESCE(
                    (SELECT MAX(scan_id)
                     FROM scans
                     WHERE root_id = ?
                       AND scan_id < ?
                       AND state = 4
                    ),
                    0
                ) AS prev_scan_id",
                [scan.root_id(), scan.scan_id()],
                |row| row.get::<_, i64>(0),
            )?;

            // Undo Add (when they are reyhdrates) and Type Change
            // When an item was previously tombstoned and then was found again during a scan
            // the item is rehydrated. This means that is_ts is set to false and all properties
            // on the item are cleared and set to new values. In this case the cleared properties are
            // stored in the change record, and we can recover them from there. A type change is
            // handled similarly. When an item that was known to be a file or folder is next seen as the other
            // type, we clear the properties (and store them on the change record). So, in both cases, we can
            // now recover the modfiied properties from the change and this batch handles the minor differences
            // between the two operations
            c.execute(
                "UPDATE items
                SET (
                    is_ts,
                    access,
                    mod_date,
                    size,
                    last_hash_scan,
                    file_hash,
                    last_val_scan,
                    val,
                    val_error,
                    last_scan
                ) =
                (
                    SELECT
                        CASE WHEN c.change_type = 1 THEN 1 ELSE items.is_ts END,
                        COALESCE(c.access_old, items.access),
                        c.mod_date_old,
                        c.size_old,
                        c.last_hash_scan_old,
                        c.hash_old,
                        c.last_val_scan_old,
                        c.val_old,
                        c.val_error_old,
                        ?1
                    FROM changes c
                    WHERE c.item_id = items.item_id
                        AND c.scan_id = ?2
                        AND (c.change_type = 1 AND c.is_undelete = 1)
                    LIMIT 1
                )
                WHERE item_id IN (
                    SELECT item_id
                    FROM changes
                    WHERE scan_id = ?2
                        AND (change_type = 1 AND is_undelete = 1)
                )",
                [prev_scan_id, scan.scan_id()],
            )?;

            // Undoing a modify requires selectively copying back (from the change)
            // the property groups that were part of the modify
            c.execute(
                "UPDATE items
                SET (
                    access,
                    mod_date,
                    size,
                    last_hash_scan,
                    file_hash,
                    last_val_scan,
                    val,
                    val_error,
                    last_scan
                ) =
                (
                SELECT
                    CASE WHEN c.access_old IS NOT NULL THEN c.access_old ELSE items.access END,
                    CASE WHEN c.meta_change = 1 THEN COALESCE(c.mod_date_old, items.mod_date) ELSE items.mod_date END,
                    CASE WHEN c.meta_change = 1 THEN COALESCE(c.size_old, items.size) ELSE items.size END,
                    CASE WHEN c.hash_change = 1 THEN c.last_hash_scan_old ELSE items.last_hash_scan END,
                    CASE WHEN c.hash_change = 1 THEN c.hash_old ELSE items.file_hash END,
                    CASE WHEN c.val_change = 1 THEN c.last_val_scan_old ELSE items.last_val_scan END,
                    CASE WHEN c.val_change = 1 THEN c.val_old ELSE items.val END,
                    CASE WHEN c.val_change = 1 THEN c.val_error_old ELSE items.val_error END,
                    ?1
                FROM changes c
                WHERE c.item_id = items.item_id
                    AND c.scan_id = ?2
                    AND c.change_type = 2
                LIMIT 1
                )
                WHERE last_scan = ?2
                AND EXISTS (
                    SELECT 1 FROM changes c
                    WHERE c.item_id = items.item_id
                        AND c.scan_id = ?2
                        AND c.change_type = 2
                )",
                [prev_scan_id, scan.scan_id()]
            )?;

            // Undo deletes. This is simple because deletes just set the tombstone flag
            c.execute(
                "UPDATE items
                SET is_ts = 0,
                    last_scan = ?1
                WHERE item_id IN (
                    SELECT item_id
                    FROM changes
                    WHERE scan_id = ?2
                      AND change_type = 3
                )",
                [prev_scan_id, scan.scan_id()],
            )?;

            // Undo alerts. Delete all of the alerts created during the scan
            c.execute(
                "DELETE FROM alerts
                WHERE scan_id = ?1",
                [scan.scan_id()],
            )?;

            // Mark the scan as stopped (state=5) or error (state=6)
            // Null the total_size that may have been set at the end of the scanning phase - it's not reliable in the
            // context of a stop or error
            let final_state = if error_message.is_some() { 6 } else { 5 };

            c.execute(
                "UPDATE scans SET state = ?, total_size = NULL, error = ? WHERE scan_id = ?",
                params![final_state, error_message, scan.scan_id()],
            )?;

            // Find the items that had their last_scan updated but where no change
            // record was created, and reset their last_scan
            c.execute(
                "UPDATE items
                 SET last_scan = ?1
                 WHERE last_scan = ?2
                   AND NOT EXISTS (
                     SELECT 1 FROM changes c
                     WHERE c.item_id = items.item_id
                       AND c.scan_id = ?2
                   )",
                [prev_scan_id, scan.scan_id()],
            )?;

            // Delete the change records from the stopped scan
            c.execute("DELETE FROM changes WHERE scan_id = ?1", [scan.scan_id()])?;

            // Final step is to delete the remaining items that were created during
            // the scan. We have to do this after the change records are deleted otherwise
            // attemping to delete these rows will generate a referential integrity violation
            // since we'll be abandoning change records. This operation assumes that the
            // only remaining items with a last_scan of the current scan are the simple
            // adds. This should be true :)
            c.execute(
                "DELETE FROM items
            WHERE last_scan = ?",
                [scan.scan_id()],
            )?;

            Ok(())
        })
    }
}

/// Statistics for a completed or in-progress scan
#[derive(Debug, Clone)]
pub struct ScanStats {
    pub scan_id: i64,
    pub root_id: i64,
    pub root_path: String,
    pub state: ScanState,
    pub started_at: i64,

    // Total counts from scans table
    pub total_files: i64,
    pub total_folders: i64,
    pub total_size: i64,

    // Total change counts from scans table
    pub total_adds: i64,
    pub total_modifies: i64,
    pub total_deletes: i64,

    // Change breakdown by type (files)
    pub files_added: i64,
    pub files_modified: i64,
    pub files_deleted: i64,

    // Change breakdown by type (folders)
    pub folders_added: i64,
    pub folders_modified: i64,
    pub folders_deleted: i64,

    // Analysis statistics
    pub items_hashed: i64,
    pub items_validated: i64,
    pub alerts_generated: i64,

    // Scan configuration
    pub hash_enabled: bool,
    pub validation_enabled: bool,

    // Error information
    pub error: Option<String>,
}

impl ScanStats {
    /// Get statistics for a specific scan ID
    pub fn get_for_scan(conn: &Connection, scan_id: i64) -> Result<Option<Self>, FsPulseError> {
        // Use existing function to get scan
        let scan = match Scan::get_by_id_or_latest(conn, Some(scan_id), None)? {
            Some(s) => s,
            None => return Ok(None),
        };

        // Use existing function to get root path
        let root = crate::roots::Root::get_by_id(conn, scan.root_id())?
            .ok_or_else(|| FsPulseError::Error(format!("Root {} not found", scan.root_id())))?;

        // Get change statistics broken down by file vs folder
        let changes: (i64, i64, i64, i64, i64, i64) = conn.query_row(
            "SELECT
                SUM(CASE WHEN c.change_type = 1 AND i.item_type = 0 THEN 1 ELSE 0 END) as files_added,
                SUM(CASE WHEN c.change_type = 2 AND i.item_type = 0 THEN 1 ELSE 0 END) as files_modified,
                SUM(CASE WHEN c.change_type = 3 AND i.item_type = 0 THEN 1 ELSE 0 END) as files_deleted,
                SUM(CASE WHEN c.change_type = 1 AND i.item_type = 1 THEN 1 ELSE 0 END) as folders_added,
                SUM(CASE WHEN c.change_type = 2 AND i.item_type = 1 THEN 1 ELSE 0 END) as folders_modified,
                SUM(CASE WHEN c.change_type = 3 AND i.item_type = 1 THEN 1 ELSE 0 END) as folders_deleted
             FROM changes c
             JOIN items i ON c.item_id = i.item_id
             WHERE c.scan_id = ?",
            params![scan_id],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?, row.get(4)?, row.get(5)?))
        ).unwrap_or((0, 0, 0, 0, 0, 0));

        // Get hashing statistics
        let items_hashed: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM items
             WHERE last_scan = ? AND hash IS NOT NULL",
                params![scan_id],
                |row| row.get(0),
            )
            .unwrap_or(0);

        // Get validation statistics
        let items_validated: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM items
             WHERE last_scan = ? AND (val IS NOT NULL OR val_error IS NOT NULL)",
                params![scan_id],
                |row| row.get(0),
            )
            .unwrap_or(0);

        Ok(Some(ScanStats {
            scan_id: scan.scan_id(),
            root_id: scan.root_id(),
            root_path: root.root_path().to_string(),
            state: scan.state(),
            started_at: scan.started_at(),
            total_files: scan.file_count().unwrap_or(0),
            total_folders: scan.folder_count().unwrap_or(0),
            total_size: scan.total_size().unwrap_or(0),
            total_adds: scan.add_count().unwrap_or(0),
            total_modifies: scan.modify_count().unwrap_or(0),
            total_deletes: scan.delete_count().unwrap_or(0),
            files_added: changes.0,
            files_modified: changes.1,
            files_deleted: changes.2,
            folders_added: changes.3,
            folders_modified: changes.4,
            folders_deleted: changes.5,
            items_hashed,
            items_validated,
            alerts_generated: scan.alert_count().unwrap_or(0),
            hash_enabled: scan.analysis_spec().is_hash(),
            validation_enabled: scan.analysis_spec().is_val(),
            error: scan.error().map(|s| s.to_string()),
        }))
    }

    /// Get statistics for the most recent scan across all roots
    pub fn get_latest(conn: &Connection) -> Result<Option<Self>, FsPulseError> {
        // Use existing function with None to get latest scan
        let scan = match Scan::get_by_id_or_latest(conn, None, None)? {
            Some(s) => s,
            None => return Ok(None),
        };

        Self::get_for_scan(conn, scan.scan_id())
    }
}

/// Scan history row for the scan history table
#[derive(Debug, Clone, Serialize)]
pub struct ScanHistoryRow {
    pub scan_id: i64,
    pub root_id: i64,
    pub started_at: i64,
    pub ended_at: Option<i64>,
    pub was_restarted: bool,
    pub schedule_id: Option<i64>,
    pub schedule_name: Option<String>,
    pub add_count: Option<i64>,
    pub modify_count: Option<i64>,
    pub delete_count: Option<i64>,
    pub state: i64,
}

/// Get count of scan history entries (completed, stopped, or error states)
/// Optionally filtered by root_id
pub fn get_scan_history_count(
    conn: &Connection,
    root_id: Option<i64>,
) -> Result<i64, FsPulseError> {
    let count: i64 = if let Some(root_id) = root_id {
        conn.query_row(
            "SELECT COUNT(*) FROM scans
             WHERE state IN (4, 5, 6) AND root_id = ?",
            [root_id],
            |row| row.get(0),
        )?
    } else {
        conn.query_row(
            "SELECT COUNT(*) FROM scans
             WHERE state IN (4, 5, 6)",
            [],
            |row| row.get(0),
        )?
    };

    Ok(count)
}

/// Get paginated scan history with schedule information
/// Only includes completed, stopped, or error states
/// Optionally filtered by root_id
/// Ordered by scan_id DESC
pub fn get_scan_history(
    conn: &Connection,
    root_id: Option<i64>,
    limit: i64,
    offset: i64,
) -> Result<Vec<ScanHistoryRow>, FsPulseError> {
    let mut result = Vec::new();

    if let Some(root_id) = root_id {
        let mut stmt = conn.prepare(
            "SELECT
                s.scan_id,
                s.root_id,
                s.started_at,
                s.ended_at,
                s.was_restarted,
                s.schedule_id,
                sch.schedule_name,
                s.add_count,
                s.modify_count,
                s.delete_count,
                s.state
            FROM scans s
            LEFT JOIN scan_schedules sch ON s.schedule_id = sch.schedule_id
            WHERE s.state IN (4, 5, 6) AND s.root_id = ?
            ORDER BY s.scan_id DESC
            LIMIT ? OFFSET ?",
        )?;

        let rows = stmt.query_map(params![root_id, limit, offset], |row| {
            Ok(ScanHistoryRow {
                scan_id: row.get(0)?,
                root_id: row.get(1)?,
                started_at: row.get(2)?,
                ended_at: row.get(3)?,
                was_restarted: row.get(4)?,
                schedule_id: row.get(5)?,
                schedule_name: row.get(6)?,
                add_count: row.get(7)?,
                modify_count: row.get(8)?,
                delete_count: row.get(9)?,
                state: row.get(10)?,
            })
        })?;

        for row in rows {
            result.push(row?);
        }
    } else {
        let mut stmt = conn.prepare(
            "SELECT
                s.scan_id,
                s.root_id,
                s.started_at,
                s.ended_at,
                s.was_restarted,
                s.schedule_id,
                sch.schedule_name,
                s.add_count,
                s.modify_count,
                s.delete_count,
                s.state
            FROM scans s
            LEFT JOIN scan_schedules sch ON s.schedule_id = sch.schedule_id
            WHERE s.state IN (4, 5, 6)
            ORDER BY s.scan_id DESC
            LIMIT ? OFFSET ?",
        )?;

        let rows = stmt.query_map(params![limit, offset], |row| {
            Ok(ScanHistoryRow {
                scan_id: row.get(0)?,
                root_id: row.get(1)?,
                started_at: row.get(2)?,
                ended_at: row.get(3)?,
                was_restarted: row.get(4)?,
                schedule_id: row.get(5)?,
                schedule_name: row.get(6)?,
                add_count: row.get(7)?,
                modify_count: row.get(8)?,
                delete_count: row.get(9)?,
                state: row.get(10)?,
            })
        })?;

        for row in rows {
            result.push(row?);
        }
    }

    Ok(result)
}

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

    #[test]
    fn test_scan_state_as_i64() {
        assert_eq!(ScanState::Scanning.as_i64(), 1);
        assert_eq!(ScanState::Sweeping.as_i64(), 2);
        assert_eq!(ScanState::Analyzing.as_i64(), 3);
        assert_eq!(ScanState::Completed.as_i64(), 4);
        assert_eq!(ScanState::Stopped.as_i64(), 5);
        assert_eq!(ScanState::Error.as_i64(), 6);
    }

    #[test]
    fn test_scan_state_from_i64() {
        assert_eq!(ScanState::from_i64(1), ScanState::Scanning);
        assert_eq!(ScanState::from_i64(2), ScanState::Sweeping);
        assert_eq!(ScanState::from_i64(3), ScanState::Analyzing);
        assert_eq!(ScanState::from_i64(4), ScanState::Completed);
        assert_eq!(ScanState::from_i64(5), ScanState::Stopped);
        assert_eq!(ScanState::from_i64(6), ScanState::Error);
    }

    #[test]
    fn test_scan_state_round_trip() {
        let states = [
            ScanState::Scanning,
            ScanState::Sweeping,
            ScanState::Analyzing,
            ScanState::Completed,
            ScanState::Stopped,
            ScanState::Error,
        ];

        for state in states {
            let i64_val = state.as_i64();
            let converted_back = ScanState::from_i64(i64_val);
            assert_eq!(state, converted_back, "Round trip failed for {state:?}");
        }
    }

    #[test]
    fn test_scan_state_display() {
        assert_eq!(ScanState::Scanning.to_string(), "Scanning");
        assert_eq!(ScanState::Sweeping.to_string(), "Sweeping");
        assert_eq!(ScanState::Analyzing.to_string(), "Analyzing");
        assert_eq!(ScanState::Completed.to_string(), "Completed");
        assert_eq!(ScanState::Stopped.to_string(), "Stopped");
        assert_eq!(ScanState::Error.to_string(), "Error");
    }

    #[test]
    fn test_scan_state_ordering() {
        // Test that enum ordering works as expected
        assert!(ScanState::Scanning < ScanState::Sweeping);
        assert!(ScanState::Sweeping < ScanState::Analyzing);
        assert!(ScanState::Analyzing < ScanState::Completed);
        assert!(ScanState::Completed < ScanState::Stopped);
        assert!(ScanState::Stopped < ScanState::Error);
    }

    #[test]
    fn test_hash_mode_enum() {
        // Test that HashMode enum has expected variants
        let _none = HashMode::None;
        let _new = HashMode::New;
        let _all = HashMode::All;

        // Test PartialEq
        assert_eq!(HashMode::None, HashMode::None);
        assert_ne!(HashMode::None, HashMode::New);
        assert_ne!(HashMode::New, HashMode::All);
    }

    #[test]
    fn test_validate_mode_enum() {
        // Test that ValidateMode enum has expected variants
        let _none = ValidateMode::None;
        let _new = ValidateMode::New;
        let _all = ValidateMode::All;

        // Test PartialEq
        assert_eq!(ValidateMode::None, ValidateMode::None);
        assert_ne!(ValidateMode::None, ValidateMode::New);
        assert_ne!(ValidateMode::New, ValidateMode::All);
    }
}
