use log::info;
use pest::{iterators::Pairs, Parser};
use rusqlite::{Row, Statement, ToSql};

use super::{
    columns::{
        ColSet, HASHES_QUERY_COLS, ITEMS_QUERY_COLS, ROOTS_QUERY_COLS, SCANS_QUERY_COLS,
        VERSIONS_QUERY_COLS,
    },
    filter::{BoolFilter, EnumFilter, IntFilter},
    show::{Format, Show},
};

use crate::{
    db::Database,
    error::FsPulseError,
    hash::Hash,
    items::{Access, ItemType},
    scans::ScanState,
    validate::validator::ValidationState,
};

use super::{
    columns::ColAlign,
    filter::{DateFilter, Filter, HashFilter, IdFilter, PathFilter, StringFilter},
    order::Order,
    QueryParser, Rule,
};

/// Query result type: (rows, column headers, column alignments)
pub type QueryResultData = (Vec<Vec<String>>, Vec<String>, Vec<ColAlign>);

pub trait QueryResult {
    fn prepare(&mut self, show: &mut Show);
    fn add_row(&mut self, row: Vec<String>);
    fn finalize(&mut self, show: &mut Show);
}

struct QueryResultVector {
    row_vec: Vec<Vec<String>>,
    column_headers: Vec<String>,
    column_alignments: Vec<ColAlign>,
}

impl QueryResult for QueryResultVector {
    fn prepare(&mut self, show: &mut Show) {
        show.ensure_columns();
    }
    fn add_row(&mut self, row: Vec<String>) {
        self.row_vec.push(row);
    }
    fn finalize(&mut self, show: &mut Show) {
        // Extract column headers and alignments from Show after columns are finalized
        self.column_headers = show.get_column_headers();
        self.column_alignments = show.get_column_alignments();
    }
}

impl QueryResultVector {
    fn new() -> Self {
        QueryResultVector {
            row_vec: Vec::new(),
            column_headers: Vec::new(),
            column_alignments: Vec::new(),
        }
    }
}

/// Defines the behavior of a validator.
pub trait Query {
    fn query_impl(&self) -> &QueryImpl;
    fn query_impl_mut(&mut self) -> &mut QueryImpl;

    fn col_set(&self) -> &ColSet {
        &self.query_impl().col_set
    }

    fn show(&self) -> &Show {
        &self.query_impl().show
    }
    fn show_mut(&mut self) -> &mut Show {
        &mut self.query_impl_mut().show
    }

    fn order(&self) -> &Option<Order> {
        &self.query_impl().order
    }
    fn set_order(&mut self, order: Option<Order>) {
        self.query_impl_mut().order = order
    }

    fn add_filter(&mut self, filter: Box<dyn Filter>) {
        self.query_impl_mut().filters.push(filter);
    }

    fn cols_as_select_list(&self) -> String {
        let mut select_list = String::new();
        let mut first = true;

        for col_spec in self.query_impl().col_set.values() {
            match first {
                true => first = false,
                false => select_list.push_str(", "),
            }
            select_list.push_str(col_spec.name_db);
        }

        select_list
    }

    /// Build SELECT list from the SHOW clause, with aggregate function wrappers.
    /// Used for GROUP BY queries where the select list is driven by SHOW, not the full ColMap.
    fn show_as_select_list(&self) -> String {
        self.show()
            .display_cols
            .iter()
            .map(|dc| {
                let col_db = if dc.display_col == "*" {
                    "*"
                } else {
                    self.col_set()
                        .col_set()
                        .get(dc.display_col)
                        .map(|s| s.name_db)
                        .unwrap_or(dc.display_col)
                };

                match &dc.agg {
                    Some(func) => format!("{}({})", func.sql_name(), col_db),
                    None => col_db.to_string(),
                }
            })
            .collect::<Vec<_>>()
            .join(", ")
    }

    fn build_query_result(
        &mut self,
        sql_statement: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError>;

    /// Executes a count query using COUNT(*) SQL
    /// This is more efficient than loading all rows and counting them
    fn prepare_and_execute_count(&self) -> Result<i64, FsPulseError> {
        let conn = Database::get_connection()?;
        let (sql, params_vec) = self.build_sql(true, None, None);
        let sql_params: Vec<&dyn ToSql> = params_vec.iter().map(|b| &**b).collect();
        let mut sql_statement = conn.prepare(&sql)?;

        let count: i64 = sql_statement.query_row(&sql_params[..], |row| row.get(0))?;

        Ok(count)
    }

    /// Builds the SQL query string with optional count mode and limit/offset overrides
    /// When count_only=true, wraps query in subquery for COUNT(*) and omits ORDER BY
    /// When limit_override/offset_add are provided, adjusts pagination accordingly
    fn build_sql(
        &self,
        count_only: bool,
        limit_override: Option<i64>,
        offset_add: Option<i64>,
    ) -> (String, Vec<Box<dyn ToSql>>) {
        // Build SELECT clause
        let is_aggregate = !self.query_impl().group_by.is_empty();
        let select_list = if count_only {
            "COUNT(*)".to_string()
        } else if is_aggregate {
            self.show_as_select_list()
        } else {
            self.cols_as_select_list()
        };

        // Build WHERE clause and collect parameters
        let mut params_vec: Vec<Box<dyn ToSql>> = Vec::new();
        let mut where_clause = String::new();

        if !self.query_impl().filters.is_empty() {
            let mut first = true;
            where_clause.push_str("\nWHERE ");
            for filter in &self.query_impl().filters {
                if !first {
                    where_clause.push_str(" AND ");
                }
                first = false;

                // Note: This unwrap is safe because filters validate during parsing
                let (pred_str, pred_vec) = filter.to_predicate_parts().unwrap();
                where_clause.push_str(&pred_str);
                params_vec.extend(pred_vec);
            }
        }

        // Build ORDER BY, LIMIT, OFFSET clauses (omitted for count queries)
        let order_clause = if count_only {
            String::new()
        } else {
            match self.order() {
                Some(order) => order.to_order_clause(),
                None => String::new(),
            }
        };

        let limit_clause = match (self.query_impl().limit, limit_override) {
            (Some(user_limit), Some(override_limit)) => {
                // Calculate remaining rows within user's limit
                let offset = offset_add.unwrap_or(0);
                let remaining = user_limit - offset;
                let effective = remaining.min(override_limit).max(0);
                format!("\nLIMIT {effective}")
            }
            (None, Some(override_limit)) => format!("\nLIMIT {override_limit}"),
            (Some(limit), None) => format!("\nLIMIT {limit}"),
            (None, None) => String::new(),
        };

        let final_offset = match (self.query_impl().offset, offset_add) {
            (None, None) => None,
            (Some(o), None) => Some(o),
            (None, Some(a)) => Some(a),
            (Some(o), Some(a)) => Some(o + a),
        };

        let offset_clause = final_offset
            .map(|o| format!("\nOFFSET {o}"))
            .unwrap_or_default();

        // Build GROUP BY clause
        let group_clause = if self.query_impl().group_by.is_empty() {
            String::new()
        } else {
            format!("\nGROUP BY {}", self.query_impl().group_by.join(", "))
        };

        // Assemble final SQL
        let sql = if count_only {
            // For count queries, use subquery to correctly count with limit/offset
            let inner_sql = self
                .query_impl()
                .sql_template
                .replace("{select_list}", "1")
                .replace("{where_clause}", &where_clause)
                .replace("{group_clause}", &group_clause)
                .replace("{order_clause}", "")
                .replace("{limit_clause}", &limit_clause)
                .replace("{offset_clause}", &offset_clause);

            format!("SELECT COUNT(*) FROM ({}) AS subquery", inner_sql)
        } else {
            self.query_impl()
                .sql_template
                .replace("{select_list}", &select_list)
                .replace("{where_clause}", &where_clause)
                .replace("{group_clause}", &group_clause)
                .replace("{order_clause}", &order_clause)
                .replace("{limit_clause}", &limit_clause)
                .replace("{offset_clause}", &offset_clause)
        };

        (sql, params_vec)
    }

    fn is_aggregate(&self) -> bool {
        !self.query_impl().group_by.is_empty()
    }

    fn prepare_and_execute(
        &mut self,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let conn = Database::get_connection()?;
        let (sql, params_vec) = self.build_sql(false, None, None);
        let sql_params: Vec<&dyn ToSql> = params_vec.iter().map(|b| &**b).collect();
        let mut sql_statement = conn.prepare(&sql)?;

        if self.is_aggregate() {
            self.build_aggregate_result(&mut sql_statement, &sql_params, query_result)?;
        } else {
            self.build_query_result(&mut sql_statement, &sql_params, query_result)?;
        }

        Ok(())
    }

    fn prepare_and_execute_override(
        &mut self,
        limit_override: i64,
        offset_add: i64,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let conn = Database::get_connection()?;
        let (sql, params_vec) = self.build_sql(false, Some(limit_override), Some(offset_add));
        let sql_params: Vec<&dyn ToSql> = params_vec.iter().map(|b| &**b).collect();
        let mut sql_statement = conn.prepare(&sql)?;

        if self.is_aggregate() {
            self.build_aggregate_result(&mut sql_statement, &sql_params, query_result)?;
        } else {
            self.build_query_result(&mut sql_statement, &sql_params, query_result)?;
        }

        Ok(())
    }

    /// Generic row reader for aggregate queries. Reads all columns as rusqlite::Value
    /// and converts to strings, bypassing the domain-specific row structs.
    fn build_aggregate_result(
        &mut self,
        sql_statement: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let col_count = self.show().display_cols.len();

        query_result.prepare(&mut self.query_impl_mut().show);

        let mut rows = sql_statement.query(sql_params)?;
        while let Some(row) = rows.next()? {
            let mut values = Vec::with_capacity(col_count);
            for i in 0..col_count {
                let val: rusqlite::types::Value = row.get(i)?;
                let s = match val {
                    rusqlite::types::Value::Null => "-".to_string(),
                    rusqlite::types::Value::Integer(n) => n.to_string(),
                    rusqlite::types::Value::Real(f) => format!("{:.2}", f),
                    rusqlite::types::Value::Text(s) => s,
                    rusqlite::types::Value::Blob(b) => hex::encode(b),
                };
                values.push(s);
            }
            query_result.add_row(values);
        }

        Ok(())
    }
}

fn make_query(query_type: &str, count_only: bool) -> Box<dyn Query> {
    match (query_type, count_only) {
        ("roots", _) => Box::new(RootsQuery {
            imp: QueryImpl::new(QueryImpl::ROOTS_SQL_QUERY, ColSet::new(&ROOTS_QUERY_COLS)),
        }),
        ("scans", false) => Box::new(ScansQuery {
            imp: QueryImpl::new(QueryImpl::SCANS_SQL_QUERY, ColSet::new(&SCANS_QUERY_COLS)),
        }),
        ("scans", true) => Box::new(ScansQuery {
            imp: QueryImpl::new(
                QueryImpl::SCANS_SQL_QUERY_COUNT,
                ColSet::new(&SCANS_QUERY_COLS),
            ),
        }),
        ("items", _) => Box::new(ItemsQuery {
            imp: QueryImpl::new(QueryImpl::ITEMS_SQL_QUERY, ColSet::new(&ITEMS_QUERY_COLS)),
        }),
        ("versions", _) => Box::new(VersionsQuery {
            imp: QueryImpl::new(
                QueryImpl::VERSIONS_SQL_QUERY,
                ColSet::new(&VERSIONS_QUERY_COLS),
            ),
        }),
        ("hashes", _) => Box::new(HashesQuery {
            imp: QueryImpl::new(
                QueryImpl::HASHES_SQL_QUERY,
                ColSet::new(&HASHES_QUERY_COLS),
            ),
        }),
        _ => unreachable!(),
    }
}

impl Query for RootsQuery {
    fn query_impl(&self) -> &QueryImpl {
        &self.imp
    }
    fn query_impl_mut(&mut self) -> &mut QueryImpl {
        &mut self.imp
    }

    fn build_query_result(
        &mut self,
        sql_statement: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let rows = sql_statement.query_map(sql_params, RootsQueryRow::from_row)?;

        query_result.prepare(&mut self.query_impl_mut().show);

        for row in rows {
            let roots_query_row: RootsQueryRow = row?;
            self.append_roots_row(&roots_query_row, query_result)?;
        }

        Ok(())
    }
}
struct RootsQuery {
    imp: QueryImpl,
}

impl RootsQuery {
    pub fn append_roots_row(
        &self,
        root: &RootsQueryRow,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut row: Vec<String> = Vec::new();

        for col in &self.show().display_cols {
            let col_string = match col.display_col {
                "root_id" => Format::format_i64(root.root_id),
                "root_path" => Format::format_path(&root.root_path, col.format)?,
                _ => {
                    return Err(FsPulseError::Error("Invalid column".into()));
                }
            };

            row.push(col_string);
        }

        query_result.add_row(row);

        Ok(())
    }
}

impl Query for ItemsQuery {
    fn query_impl(&self) -> &QueryImpl {
        &self.imp
    }
    fn query_impl_mut(&mut self) -> &mut QueryImpl {
        &mut self.imp
    }

    fn build_query_result(
        &mut self,
        sql_statment: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let rows = sql_statment.query_map(sql_params, ItemsQueryRow::from_row)?;

        query_result.prepare(&mut self.query_impl_mut().show);

        for row in rows {
            let items_query_row = row?;
            self.append_items_row(&items_query_row, query_result)?;
        }

        Ok(())
    }
}

struct ItemsQuery {
    imp: QueryImpl,
}

impl ItemsQuery {
    pub fn append_items_row(
        &self,
        item: &ItemsQueryRow,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut row: Vec<String> = Vec::new();

        for col in &self.show().display_cols {
            let col_string = match col.display_col {
                "item_id" => Format::format_i64(item.item_id),
                "root_id" => Format::format_i64(item.root_id),
                "item_path" => Format::format_path(&item.item_path, col.format)?,
                "item_name" => Format::format_path(&item.item_name, col.format)?,
                "file_extension" => Format::format_opt_string(&item.file_extension),
                "item_type" => Format::format_item_type(item.item_type, col.format)?,
                "has_validator" => Format::format_bool(item.has_validator, col.format)?,
                "do_not_validate" => Format::format_bool(item.do_not_validate, col.format)?,
                _ => {
                    return Err(FsPulseError::Error("Invalid column".into()));
                }
            };

            row.push(col_string);
        }

        query_result.add_row(row);

        Ok(())
    }
}

impl Query for ScansQuery {
    fn query_impl(&self) -> &QueryImpl {
        &self.imp
    }
    fn query_impl_mut(&mut self) -> &mut QueryImpl {
        &mut self.imp
    }

    fn build_query_result(
        &mut self,
        sql_statment: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let rows = sql_statment.query_map(sql_params, ScansQueryRow::from_row)?;

        query_result.prepare(&mut self.query_impl_mut().show);

        for row in rows {
            let scans_query_row = row?;
            self.append_scans_row(&scans_query_row, query_result)?;
        }

        Ok(())
    }
}
struct ScansQuery {
    imp: QueryImpl,
}

impl ScansQuery {
    pub fn append_scans_row(
        &self,
        scan: &ScansQueryRow,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut row: Vec<String> = Vec::new();

        for col in &self.show().display_cols {
            let col_string = match col.display_col {
                "scan_id" => Format::format_i64(scan.scan_id),
                "root_id" => Format::format_i64(scan.root_id),
                "schedule_id" => Format::format_opt_i64(scan.schedule_id),
                "started_at" => Format::format_date(scan.started_at, col.format)?,
                "ended_at" => Format::format_opt_date(scan.ended_at, col.format)?,
                "was_restarted" => Format::format_bool(scan.was_restarted, col.format)?,
                "scan_state" => Format::format_scan_state(scan.state, col.format)?,
                "is_hash" => Format::format_bool(scan.is_hash, col.format)?,
                "hash_all" => Format::format_bool(scan.hash_all, col.format)?,
                "is_val" => Format::format_bool(scan.is_val, col.format)?,
                "file_count" => Format::format_opt_i64(scan.file_count),
                "folder_count" => Format::format_opt_i64(scan.folder_count),
                "total_size" => Format::format_opt_i64(scan.total_size),
                "new_hash_suspect_count" => Format::format_opt_i64(scan.new_hash_suspect_count),
                "new_val_invalid_count" => Format::format_opt_i64(scan.new_val_invalid_count),
                "add_count" => Format::format_opt_i64(scan.add_count),
                "modify_count" => Format::format_opt_i64(scan.modify_count),
                "delete_count" => Format::format_opt_i64(scan.delete_count),
                "val_unknown_count" => Format::format_opt_i64(scan.val_unknown_count),
                "val_valid_count" => Format::format_opt_i64(scan.val_valid_count),
                "val_invalid_count" => Format::format_opt_i64(scan.val_invalid_count),
                "val_no_validator_count" => Format::format_opt_i64(scan.val_no_validator_count),
                "hash_unknown_count" => Format::format_opt_i64(scan.hash_unknown_count),
                "hash_baseline_count" => Format::format_opt_i64(scan.hash_baseline_count),
                "hash_suspect_count" => Format::format_opt_i64(scan.hash_suspect_count),
                "error" => Format::format_opt_string(&scan.error),
                _ => {
                    return Err(FsPulseError::Error("Invalid column".into()));
                }
            };

            row.push(col_string);
        }

        query_result.add_row(row);

        Ok(())
    }
}

impl Query for VersionsQuery {
    fn query_impl(&self) -> &QueryImpl {
        &self.imp
    }
    fn query_impl_mut(&mut self) -> &mut QueryImpl {
        &mut self.imp
    }

    fn build_query_result(
        &mut self,
        sql_statement: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let rows = sql_statement.query_map(sql_params, VersionsQueryRow::from_row)?;

        query_result.prepare(&mut self.query_impl_mut().show);

        for row in rows {
            let versions_query_row: VersionsQueryRow = row?;
            self.append_versions_row(&versions_query_row, query_result)?;
        }

        Ok(())
    }
}

struct VersionsQuery {
    imp: QueryImpl,
}

impl VersionsQuery {
    pub fn append_versions_row(
        &self,
        version: &VersionsQueryRow,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut row: Vec<String> = Vec::new();

        for col in &self.show().display_cols {
            let col_string = match col.display_col {
                "item_version" => Format::format_i64(version.item_version),
                "item_id" => Format::format_i64(version.item_id),
                "root_id" => Format::format_i64(version.root_id),
                "item_path" => Format::format_path(&version.item_path, col.format)?,
                "item_name" => Format::format_path(&version.item_name, col.format)?,
                "file_extension" => Format::format_opt_string(&version.file_extension),
                "item_type" => Format::format_item_type(version.item_type, col.format)?,
                "first_scan_id" => Format::format_i64(version.first_scan_id),
                "last_scan_id" => Format::format_i64(version.last_scan_id),
                "is_added" => Format::format_bool(version.is_added, col.format)?,
                "is_deleted" => Format::format_bool(version.is_deleted, col.format)?,
                "is_current" => Format::format_bool(version.is_current, col.format)?,
                "access" => Format::format_access(version.access, col.format)?,
                "mod_date" => Format::format_opt_date(version.mod_date, col.format)?,
                "size" => Format::format_opt_i64(version.size),
                "add_count" => Format::format_opt_i64(version.add_count),
                "modify_count" => Format::format_opt_i64(version.modify_count),
                "delete_count" => Format::format_opt_i64(version.delete_count),
                "unchanged_count" => Format::format_opt_i64(version.unchanged_count),
                "val_state" => match version.val_state {
                    Some(v) => Format::format_val_state(ValidationState::from_i64(v), col.format)?,
                    None => String::new(),
                },
                "val_error" => Format::format_opt_string(&version.val_error),
                _ => {
                    return Err(FsPulseError::Error("Invalid column".into()));
                }
            };

            row.push(col_string);
        }

        query_result.add_row(row);

        Ok(())
    }
}

impl Query for HashesQuery {
    fn query_impl(&self) -> &QueryImpl {
        &self.imp
    }
    fn query_impl_mut(&mut self) -> &mut QueryImpl {
        &mut self.imp
    }

    fn build_query_result(
        &mut self,
        sql_statement: &mut Statement,
        sql_params: &[&dyn ToSql],
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let rows = sql_statement.query_map(sql_params, HashesQueryRow::from_row)?;

        query_result.prepare(&mut self.query_impl_mut().show);

        for row in rows {
            let hashes_query_row: HashesQueryRow = row?;
            self.append_hashes_row(&hashes_query_row, query_result)?;
        }

        Ok(())
    }
}

struct HashesQuery {
    imp: QueryImpl,
}

impl HashesQuery {
    pub fn append_hashes_row(
        &self,
        hash: &HashesQueryRow,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut row: Vec<String> = Vec::new();

        for col in &self.show().display_cols {
            let col_string = match col.display_col {
                "item_id" => Format::format_i64(hash.item_id),
                "item_version" => Format::format_i64(hash.item_version),
                "item_path" => Format::format_path(&hash.item_path, col.format)?,
                "item_name" => Format::format_path(&hash.item_name, col.format)?,
                "first_scan_id" => Format::format_i64(hash.first_scan_id),
                "last_scan_id" => Format::format_i64(hash.last_scan_id),
                "file_hash" => Format::format_opt_string(&hash.file_hash),
                "hash_state" => Format::format_hash_state(hash.hash_state, col.format)?,
                _ => {
                    return Err(FsPulseError::Error("Invalid column".into()));
                }
            };

            row.push(col_string);
        }

        query_result.add_row(row);

        Ok(())
    }
}

struct HashesQueryRow {
    item_id: i64,
    item_version: i64,
    item_path: String,
    item_name: String,
    first_scan_id: i64,
    last_scan_id: i64,
    file_hash: Option<String>,
    hash_state: Option<i64>,
}

impl HashesQueryRow {
    fn from_row(row: &rusqlite::Row) -> rusqlite::Result<Self> {
        Ok(HashesQueryRow {
            item_id: row.get(0)?,
            item_version: row.get(1)?,
            item_path: row.get(2)?,
            item_name: row.get(3)?,
            first_scan_id: row.get(4)?,
            last_scan_id: row.get(5)?,
            file_hash: Hash::opt_blob_to_hex(row.get(6)?),
            hash_state: row.get(7)?,
        })
    }
}

pub struct QueryProcessor;

#[derive(Debug)]
pub struct QueryImpl {
    sql_template: &'static str,
    col_set: ColSet,

    filters: Vec<Box<dyn Filter>>,
    group_by: Vec<&'static str>,
    show: Show,
    order: Option<Order>,
    limit: Option<i64>,
    offset: Option<i64>,
}

impl QueryImpl {
    const ROOTS_SQL_QUERY: &str = "SELECT {select_list}
        FROM roots
        {where_clause}
        {group_clause}
        {order_clause}
        {limit_clause}
        {offset_clause}";

    const SCANS_SQL_QUERY: &str = "SELECT {select_list}
        FROM scans
        {where_clause}
        {group_clause}
        {order_clause}
        {limit_clause}
        {offset_clause}";

    const SCANS_SQL_QUERY_COUNT: &str = "SELECT {select_list}
        FROM scans
        {where_clause}
        {group_clause}
        {order_clause}
        {limit_clause}
        {offset_clause}";

    const ITEMS_SQL_QUERY: &str = "SELECT {select_list}
        FROM items i
        {where_clause}
        {group_clause}
        {order_clause}
        {limit_clause}
        {offset_clause}";

    const VERSIONS_SQL_QUERY: &str = "SELECT {select_list}
        FROM item_versions iv
        JOIN items i ON i.item_id = iv.item_id
        {where_clause}
        {group_clause}
        {order_clause}
        {limit_clause}
        {offset_clause}";

    const HASHES_SQL_QUERY: &str = "SELECT {select_list}
        FROM hash_versions hv
        JOIN item_versions iv ON iv.item_id = hv.item_id AND iv.item_version = hv.item_version
        JOIN items i ON i.item_id = hv.item_id
        {where_clause}
        {group_clause}
        {order_clause}
        {limit_clause}
        {offset_clause}";

    fn new(sql_template: &'static str, col_set: ColSet) -> Self {
        QueryImpl {
            sql_template,
            col_set,

            filters: Vec::new(),
            group_by: Vec::new(),
            show: Show::new(col_set),
            order: None,
            limit: None,
            offset: None,
        }
    }
}

struct RootsQueryRow {
    root_id: i64,
    root_path: String,
}

impl RootsQueryRow {
    fn from_row(row: &Row) -> rusqlite::Result<Self> {
        Ok(RootsQueryRow {
            root_id: row.get(0)?,
            root_path: row.get(1)?,
        })
    }
}

struct ItemsQueryRow {
    item_id: i64,
    root_id: i64,
    item_path: String,
    item_name: String,
    file_extension: Option<String>,
    item_type: ItemType,
    has_validator: bool,
    do_not_validate: bool,
}

impl ItemsQueryRow {
    fn from_row(row: &rusqlite::Row) -> rusqlite::Result<Self> {
        Ok(ItemsQueryRow {
            item_id: row.get(0)?,
            root_id: row.get(1)?,
            item_path: row.get(2)?,
            item_name: row.get(3)?,
            file_extension: row.get(4)?,
            item_type: ItemType::from_i64(row.get(5)?),
            has_validator: row.get(6)?,
            do_not_validate: row.get(7)?,
        })
    }
}

struct VersionsQueryRow {
    item_version: i64,
    item_id: i64,
    root_id: i64,
    item_path: String,
    item_name: String,
    file_extension: Option<String>,
    item_type: ItemType,
    first_scan_id: i64,
    last_scan_id: i64,
    is_added: bool,
    is_deleted: bool,
    is_current: bool,
    access: Access,
    mod_date: Option<i64>,
    size: Option<i64>,
    add_count: Option<i64>,
    modify_count: Option<i64>,
    delete_count: Option<i64>,
    unchanged_count: Option<i64>,
    val_state: Option<i64>,
    val_error: Option<String>,
}

impl VersionsQueryRow {
    fn from_row(row: &rusqlite::Row) -> rusqlite::Result<Self> {
        Ok(VersionsQueryRow {
            item_version: row.get(0)?,
            item_id: row.get(1)?,
            root_id: row.get(2)?,
            item_path: row.get(3)?,
            item_name: row.get(4)?,
            file_extension: row.get(5)?,
            item_type: ItemType::from_i64(row.get(6)?),
            first_scan_id: row.get(7)?,
            last_scan_id: row.get(8)?,
            is_added: row.get(9)?,
            is_deleted: row.get(10)?,
            is_current: row.get(11)?,
            access: Access::from_i64(row.get(12)?),
            mod_date: row.get(13)?,
            size: row.get(14)?,
            add_count: row.get(15)?,
            modify_count: row.get(16)?,
            delete_count: row.get(17)?,
            unchanged_count: row.get(18)?,
            val_state: row.get(19)?,
            val_error: row.get(20)?,
        })
    }
}

pub struct ScansQueryRow {
    scan_id: i64,
    root_id: i64,
    schedule_id: Option<i64>,
    started_at: i64,
    ended_at: Option<i64>,
    was_restarted: bool,
    state: ScanState,
    is_hash: bool,
    hash_all: bool,
    is_val: bool,
    file_count: Option<i64>,
    folder_count: Option<i64>,
    total_size: Option<i64>,
    new_hash_suspect_count: Option<i64>,
    new_val_invalid_count: Option<i64>,
    add_count: Option<i64>,
    modify_count: Option<i64>,
    delete_count: Option<i64>,
    val_unknown_count: Option<i64>,
    val_valid_count: Option<i64>,
    val_invalid_count: Option<i64>,
    val_no_validator_count: Option<i64>,
    hash_unknown_count: Option<i64>,
    hash_baseline_count: Option<i64>,
    hash_suspect_count: Option<i64>,
    error: Option<String>,
}

impl ScansQueryRow {
    pub fn from_row(row: &Row) -> rusqlite::Result<Self> {
        let state_i64: i64 = row.get(6)?;
        Ok(ScansQueryRow {
            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(state_i64),
            is_hash: row.get(7)?,
            hash_all: row.get(8)?,
            is_val: row.get(9)?,
            file_count: row.get(10)?,
            folder_count: row.get(11)?,
            total_size: row.get(12)?,
            new_hash_suspect_count: row.get(13)?,
            new_val_invalid_count: row.get(14)?,
            add_count: row.get(15)?,
            modify_count: row.get(16)?,
            delete_count: row.get(17)?,
            val_unknown_count: row.get(18)?,
            val_valid_count: row.get(19)?,
            val_invalid_count: row.get(20)?,
            val_no_validator_count: row.get(21)?,
            hash_unknown_count: row.get(22)?,
            hash_baseline_count: row.get(23)?,
            hash_suspect_count: row.get(24)?,
            error: row.get(25)?,
        })
    }
}

impl QueryProcessor {
    pub fn execute_query(query_str: &str) -> Result<QueryResultData, FsPulseError> {
        let mut qrv = QueryResultVector::new();
        Self::process_query(query_str, &mut qrv)?;
        Ok((qrv.row_vec, qrv.column_headers, qrv.column_alignments))
    }

    pub fn execute_query_override(
        query_str: &str,
        limit_override: i64,
        offset_add: i64,
    ) -> Result<QueryResultData, FsPulseError> {
        let mut qrv = QueryResultVector::new();
        Self::process_query_override(query_str, limit_override, offset_add, &mut qrv)?;
        Ok((qrv.row_vec, qrv.column_headers, qrv.column_alignments))
    }

    pub fn execute_query_count(query_str: &str) -> Result<i64, FsPulseError> {
        Self::process_query_count(query_str)
    }

    fn process_query(
        query_str: &str,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut parsed_query = QueryParser::parse(Rule::query, query_str)
            .map_err(|err| FsPulseError::ParsingError(Box::new(err)))?;

        let query_pair = parsed_query.next().unwrap();
        let mut query_iter = query_pair.into_inner();

        let query_type_pair = query_iter.next().unwrap();

        let mut query = make_query(query_type_pair.as_str(), false);

        QueryProcessor::build(&mut *query, &mut query_iter)?;

        query.prepare_and_execute(query_result)?;
        query_result.finalize(&mut query.query_impl_mut().show);

        Ok(())
    }

    fn process_query_override(
        query_str: &str,
        limit_override: i64,
        offset_add: i64,
        query_result: &mut dyn QueryResult,
    ) -> Result<(), FsPulseError> {
        let mut parsed_query = QueryParser::parse(Rule::query, query_str)
            .map_err(|err| FsPulseError::ParsingError(Box::new(err)))?;

        let query_pair = parsed_query.next().unwrap();
        let mut query_iter = query_pair.into_inner();

        let query_type_pair = query_iter.next().unwrap();

        let mut query = make_query(query_type_pair.as_str(), false);

        QueryProcessor::build(&mut *query, &mut query_iter)?;

        query.prepare_and_execute_override(limit_override, offset_add, query_result)?;
        query_result.finalize(&mut query.query_impl_mut().show);

        Ok(())
    }

    fn process_query_count(query_str: &str) -> Result<i64, FsPulseError> {
        info!("Parsing count query: {query_str}");

        let mut parsed_query = QueryParser::parse(Rule::query, query_str)
            .map_err(|err| FsPulseError::ParsingError(Box::new(err)))?;

        info!("Parsed count query: {parsed_query}");

        let query_pair = parsed_query.next().unwrap();
        let mut query_iter = query_pair.into_inner();

        let query_type_pair = query_iter.next().unwrap();

        let mut query = make_query(query_type_pair.as_str(), true);

        QueryProcessor::build(&mut *query, &mut query_iter)?;

        // Use the count execution path - access directly through dyn Query
        let count = (*query).prepare_and_execute_count()?;

        Ok(count)
    }

    pub fn validate_parsed_filter(rule: Rule, pairs: &mut Pairs<Rule>) -> Option<String> {
        if rule == Rule::date_filter_EOI {
            match DateFilter::validate_values(pairs) {
                Ok(_) => return None,
                Err(e) => return Some(e.to_string()),
            }
        };
        None
    }

    pub fn validate_filter(rule: Rule, filter: &str) -> Option<String> {
        match QueryParser::parse(rule, filter).as_mut() {
            // In cases such as "dates", input valiation happens during
            // query building, not parsing, since the parser doesn't understand
            // date validity. For these cases, we need to explicitly validate
            Ok(parsed_query) => QueryProcessor::validate_parsed_filter(rule, parsed_query),
            Err(e) => Some(e.to_string()),
        }
    }

    fn build(query: &mut dyn Query, query_iter: &mut Pairs<Rule>) -> Result<(), FsPulseError> {
        for token in query_iter {
            //println!("{:?}", token.as_rule());
            match token.as_rule() {
                Rule::id_filter => {
                    IdFilter::add_to_query(token, query)?;
                }
                Rule::date_filter => {
                    DateFilter::add_to_query(token, query)?;
                }
                Rule::bool_filter => {
                    BoolFilter::add_bool_filter_to_query(token, query)?;
                }
                Rule::scan_state_filter
                | Rule::item_type_filter
                | Rule::val_state_filter
                | Rule::access_filter
                | Rule::hash_state_filter => {
                    EnumFilter::add_enum_filter_to_query(token, query)?;
                }
                Rule::path_filter => {
                    PathFilter::add_to_query(token, query)?;
                }
                Rule::string_filter => {
                    StringFilter::add_string_filter_to_query(token, query)?;
                }
                Rule::hash_filter => {
                    HashFilter::add_hash_filter_to_query(token, query)?;
                }
                Rule::int_filter => {
                    IntFilter::add_int_filter_to_query(token, query)?;
                }
                Rule::group_list => {
                    for group_col_pair in token.into_inner() {
                        let col_name = group_col_pair.into_inner().next().unwrap().as_str();
                        let name_db = query.col_set().col_name_to_db(col_name)
                            .ok_or_else(|| FsPulseError::CustomParsingError(format!(
                                "Invalid column in GROUP BY: '{col_name}'"
                            )))?;
                        query.query_impl_mut().group_by.push(name_db);
                    }
                }
                Rule::show_list => {
                    query.show_mut().build_from_pest_pair(token)?;
                }
                Rule::order_list => {
                    let order = Order::from_pest_pair(token, *query.col_set())?;
                    query.set_order(Some(order));
                }
                Rule::limit_val => {
                    query.query_impl_mut().limit = Some(token.as_str().parse().unwrap());
                }
                Rule::offset_val => {
                    query.query_impl_mut().offset = Some(token.as_str().parse().unwrap());
                }
                _ => {}
            }
        }

        // Validate aggregate query constraints
        if !query.query_impl().group_by.is_empty() {
            // GROUP BY requires an explicit SHOW clause
            if query.show().display_cols.is_empty() {
                return Err(FsPulseError::CustomParsingError(
                    "GROUP BY requires a SHOW clause".to_string(),
                ));
            }

            // Every non-aggregate column in SHOW must appear in GROUP BY
            for dc in &query.show().display_cols {
                if dc.agg.is_none() {
                    let col_db = query.col_set().col_set().get(dc.display_col)
                        .map(|s| s.name_db)
                        .unwrap_or(dc.display_col);

                    if !query.query_impl().group_by.contains(&col_db) {
                        return Err(FsPulseError::CustomParsingError(format!(
                            "Column '{}' in SHOW must appear in GROUP BY or use an aggregate function",
                            dc.display_col
                        )));
                    }
                }
            }

            // Type checking for aggregate functions
            use super::show::AggFunc;
            use super::columns::ColType;

            for dc in &query.show().display_cols {
                if let Some(func) = &dc.agg {
                    if dc.display_col == "*" {
                        // count(*) is always valid
                        continue;
                    }
                    if let Some(col_spec) = query.col_set().col_set().get(dc.display_col) {
                        match func {
                            AggFunc::Sum | AggFunc::Avg => {
                                if !matches!(col_spec.col_type, ColType::Int) {
                                    return Err(FsPulseError::CustomParsingError(format!(
                                        "{}() requires an integer column, but '{}' is {}",
                                        func.name(), dc.display_col, col_spec.col_type.info().type_name
                                    )));
                                }
                            }
                            AggFunc::Min | AggFunc::Max => {
                                if !matches!(col_spec.col_type, ColType::Int | ColType::Date | ColType::Id) {
                                    return Err(FsPulseError::CustomParsingError(format!(
                                        "{}() requires an integer, date, or id column, but '{}' is {}",
                                        func.name(), dc.display_col, col_spec.col_type.info().type_name
                                    )));
                                }
                            }
                            AggFunc::Count => {} // count(col) is valid for any type
                        }
                    }
                }
            }
        }

        Ok(())
    }
}
