use std::{
    fmt::Display,
    fs, io,
    path::{Path, PathBuf},
    str::FromStr,
};

use anyhow::{Context, Result, bail};
use chrono::{DateTime, Duration, Local, NaiveTime, TimeZone};
use rusqlite::{Connection, OptionalExtension, params};

use crate::date::{DateParseConfig, extract_when_configured, next_occurrence};
use crate::filter;
use crate::model::{
    Aggregate, Category, CategoryKind, CategoryRule, CategoryValue, DocumentSettings, DonePolicy,
    Item, ItemChanges, MacroDef, RuleActionKind, TrashPolicy, ViewColumn, ViewDef, ViewKind,
    ViewSection,
};

mod export;
mod import;
mod presets;
mod schema;

pub use presets::Preset;

use self::export::{csv, json, opt_json, render_html, render_ical, render_markdown};
use self::import::{IcalRecord, ical_unescape, parse_ical_date, rrule_to_recurrence};

fn enum_column<T>(row: &rusqlite::Row<'_>, index: usize) -> rusqlite::Result<T>
where
    T: FromStr,
    T::Err: Display,
{
    let value: String = row.get(index)?;
    value.parse::<T>().map_err(|error| {
        rusqlite::Error::FromSqlConversionFailure(
            index,
            rusqlite::types::Type::Text,
            Box::new(io::Error::new(
                io::ErrorKind::InvalidData,
                error.to_string(),
            )),
        )
    })
}

pub struct Database {
    conn: Connection,
    path: PathBuf,
    new_document: bool,
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct RuleConflict {
    pub item_id: i64,
    pub item_text: String,
    pub message: String,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct ExecuteSummary {
    pub items: usize,
    pub conflicts: usize,
}

impl Database {
    pub fn open(path: &Path) -> Result<Self> {
        let existed = path.exists() && path.metadata().is_ok_and(|metadata| metadata.len() > 0);
        let conn =
            Connection::open(path).with_context(|| format!("could not open {}", path.display()))?;
        conn.pragma_update(None, "foreign_keys", "ON")?;
        conn.pragma_update(None, "journal_mode", "WAL")?;
        conn.pragma_update(None, "synchronous", "NORMAL")?;
        let mut db = Self {
            conn,
            path: path.to_owned(),
            new_document: !existed,
        };
        schema::migrate(&db.conn)?;
        db.seed_defaults()?;
        if existed && db.document_settings()?.backup_on_open {
            db.backup_now()?;
        }
        Ok(db)
    }

    pub fn macros(&self) -> Result<Vec<MacroDef>> {
        let mut statement = self
            .conn
            .prepare("SELECT id,name,source,key_binding FROM macros ORDER BY sort_order,id")?;
        Ok(statement
            .query_map([], |row| {
                Ok(MacroDef {
                    id: row.get(0)?,
                    name: row.get(1)?,
                    source: row.get(2)?,
                    key_binding: row.get(3)?,
                })
            })?
            .collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn save_macro(
        &mut self,
        id: Option<i64>,
        name: &str,
        source: &str,
        key_binding: &str,
    ) -> Result<i64> {
        if name.trim().is_empty() {
            bail!("macro name cannot be empty")
        }
        if let Some(id) = id {
            self.conn.execute(
                "UPDATE macros SET name=?1,source=?2,key_binding=?3 WHERE id=?4",
                params![name.trim(), source, key_binding.trim(), id],
            )?;
            Ok(id)
        } else {
            let order: i64 = self.conn.query_row(
                "SELECT COALESCE(MAX(sort_order),-1)+1 FROM macros",
                [],
                |row| row.get(0),
            )?;
            self.conn.execute(
                "INSERT INTO macros(name,source,key_binding,sort_order) VALUES(?1,?2,?3,?4)",
                params![name.trim(), source, key_binding.trim(), order],
            )?;
            Ok(self.conn.last_insert_rowid())
        }
    }

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

    pub fn macro_variables(&self) -> Result<std::collections::HashMap<String, String>> {
        let mut statement = self
            .conn
            .prepare("SELECT name,value FROM macro_variables")?;
        Ok(statement
            .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn save_macro_variables(
        &mut self,
        variables: &std::collections::HashMap<String, String>,
    ) -> Result<()> {
        let transaction = self.conn.transaction()?;
        transaction.execute("DELETE FROM macro_variables", [])?;
        for (name, value) in variables {
            transaction.execute(
                "INSERT INTO macro_variables(name,value) VALUES(?1,?2)",
                params![name, value],
            )?;
        }
        transaction.commit()?;
        Ok(())
    }

    fn seed_defaults(&mut self) -> Result<()> {
        let tx = self.conn.transaction()?;
        let count: i64 = tx.query_row("SELECT COUNT(*) FROM categories", [], |r| r.get(0))?;
        if count == 0 {
            tx.execute("INSERT INTO categories(name, match_text, sort_order) VALUES('Tasks', 'todo,to-do,task,call,meet,review,send,buy', 0)", [])?;
            let tasks = tx.last_insert_rowid();
            tx.execute("INSERT INTO categories(name, parent_id, match_text, sort_order) VALUES('Calls', ?1, 'call,phone,ring', 0)", [tasks])?;
            tx.execute("INSERT INTO categories(name, parent_id, match_text, sort_order) VALUES('Meetings', ?1, 'meet,meeting,appointment', 1)", [tasks])?;
            tx.execute(
                "INSERT INTO categories(name, match_text, sort_order) VALUES('People', '', 1)",
                [],
            )?;
            tx.execute(
                "INSERT INTO categories(name, match_text, sort_order) VALUES('Projects', '', 2)",
                [],
            )?;
            tx.execute(
                "INSERT INTO categories(name, kind, sort_order) VALUES('When', 'date', 3)",
                [],
            )?;
            tx.execute(
                "INSERT INTO categories(name, kind, sort_order) VALUES('Priority', 'standard', 4)",
                [],
            )?;
            tx.execute(
                "INSERT INTO categories(name, kind, sort_order) VALUES('Value', 'numeric', 5)",
                [],
            )?;
        }
        let views: i64 = tx.query_row("SELECT COUNT(*) FROM views", [], |r| r.get(0))?;
        if views == 0 {
            for (name, kind, filter, sort, done, order) in [
                ("All Items", "list", "", "manual", 1, 0),
                ("Tasks", "category", "Tasks", "when", 0, 1),
                ("Upcoming", "upcoming", "7", "when", 0, 2),
                ("Recently Done", "done", "14", "done", 1, 3),
                ("Datebook", "datebook", "30", "when", 1, 4),
                ("Trash", "trash", "", "updated", 1, 5),
            ] {
                tx.execute("INSERT INTO views(name, kind, filter_value, sort_key, show_done, sort_order) VALUES(?1,?2,?3,?4,?5,?6)", params![name,kind,filter,sort,done,order])?;
            }
        }
        tx.execute(
            "INSERT OR IGNORE INTO meta(key,value) VALUES('title','Rogue Agenda')",
            [],
        )?;
        tx.commit()?;
        Ok(())
    }

    pub fn document_settings(&self) -> Result<DocumentSettings> {
        let defaults = DocumentSettings::default();
        let settings = DocumentSettings {
            description: self.meta_value("document.description", &defaults.description)?,
            backup_on_open: self.meta_bool("document.backup_on_open", defaults.backup_on_open)?,
            trash_policy: self
                .meta_value("document.trash_policy", defaults.trash_policy.as_str())?
                .parse()
                .map_err(anyhow::Error::msg)?,
            done_policy: self
                .meta_value("document.done_policy", defaults.done_policy.as_str())?
                .parse()
                .map_err(anyhow::Error::msg)?,
            automatic_filing: self
                .meta_bool("document.automatic_filing", defaults.automatic_filing)?,
            date_order: self
                .meta_value("document.date_order", defaults.date_order.as_str())?
                .parse()
                .map_err(anyhow::Error::msg)?,
            week_start: self
                .meta_value("document.week_start", defaults.week_start.as_str())?
                .parse()
                .map_err(anyhow::Error::msg)?,
            default_time: self.meta_value("document.default_time", &defaults.default_time)?,
            morning_time: self.meta_value("document.morning_time", &defaults.morning_time)?,
            afternoon_time: self.meta_value("document.afternoon_time", &defaults.afternoon_time)?,
            evening_time: self.meta_value("document.evening_time", &defaults.evening_time)?,
            note_tab_width: self
                .meta_value(
                    "document.note_tab_width",
                    &defaults.note_tab_width.to_string(),
                )?
                .parse()?,
            report_headers: self.meta_bool("document.report_headers", defaults.report_headers)?,
        };
        validate_document_settings(&settings)?;
        Ok(settings)
    }

    pub fn save_document_settings(&mut self, settings: &DocumentSettings) -> Result<()> {
        validate_document_settings(settings)?;
        let tx = self.conn.transaction()?;
        for (key, value) in [
            ("document.description", settings.description.clone()),
            (
                "document.backup_on_open",
                settings.backup_on_open.to_string(),
            ),
            ("document.trash_policy", settings.trash_policy.to_string()),
            ("document.done_policy", settings.done_policy.to_string()),
            (
                "document.automatic_filing",
                settings.automatic_filing.to_string(),
            ),
            ("document.date_order", settings.date_order.to_string()),
            ("document.week_start", settings.week_start.to_string()),
            ("document.default_time", settings.default_time.clone()),
            ("document.morning_time", settings.morning_time.clone()),
            ("document.afternoon_time", settings.afternoon_time.clone()),
            ("document.evening_time", settings.evening_time.clone()),
            (
                "document.note_tab_width",
                settings.note_tab_width.to_string(),
            ),
            (
                "document.report_headers",
                settings.report_headers.to_string(),
            ),
        ] {
            tx.execute(
                "INSERT INTO meta(key,value) VALUES(?1,?2) ON CONFLICT(key) DO UPDATE SET value=excluded.value",
                params![key, value],
            )?;
        }
        tx.commit()?;
        self.reapply_automatic_assignments()?;
        Ok(())
    }

    fn meta_value(&self, key: &str, default: &str) -> Result<String> {
        Ok(self
            .conn
            .query_row("SELECT value FROM meta WHERE key=?1", [key], |row| {
                row.get(0)
            })
            .optional()?
            .unwrap_or_else(|| default.to_owned()))
    }

    fn meta_bool(&self, key: &str, default: bool) -> Result<bool> {
        Ok(parse_bool(&self.meta_value(key, &default.to_string())?))
    }

    pub fn interpret_date(&self, text: &str) -> Result<Option<String>> {
        let settings = self.document_settings()?;
        Ok(extract_when_configured(
            text,
            DateParseConfig {
                date_order: settings.date_order,
                week_start: settings.week_start,
                default_time: &settings.default_time,
                morning_time: &settings.morning_time,
                afternoon_time: &settings.afternoon_time,
                evening_time: &settings.evening_time,
            },
        ))
    }

    pub fn backup_path(&self) -> PathBuf {
        let mut path = self.path.as_os_str().to_os_string();
        path.push(".bak");
        PathBuf::from(path)
    }

    pub fn backup_now(&self) -> Result<PathBuf> {
        self.conn.execute_batch("PRAGMA wal_checkpoint(FULL)")?;
        let destination = self.backup_path();
        fs::copy(&self.path, &destination).with_context(|| {
            format!(
                "could not back up {} to {}",
                self.path.display(),
                destination.display()
            )
        })?;
        Ok(destination)
    }

    pub fn views(&self) -> Result<Vec<ViewDef>> {
        let mut stmt = self.conn.prepare("SELECT id,name,kind,filter_value,sort_key,show_done,filter_expr FROM views ORDER BY sort_order,name")?;
        let mut views = stmt
            .query_map([], |r| {
                Ok(ViewDef {
                    id: r.get(0)?,
                    name: r.get(1)?,
                    kind: enum_column(r, 2)?,
                    filter_value: r.get(3)?,
                    sort_key: r.get(4)?,
                    show_done: r.get::<_, i64>(5)? != 0,
                    filter_expr: r.get(6)?,
                    columns: vec![],
                    sections: vec![],
                })
            })?
            .collect::<rusqlite::Result<Vec<_>>>()?;
        drop(stmt);
        for view in &mut views {
            view.columns = self.view_columns(view.id)?;
            view.sections = self.view_sections(view.id)?;
        }
        Ok(views)
    }

    fn view_columns(&self, view_id: i64) -> Result<Vec<ViewColumn>> {
        let mut stmt=self.conn.prepare("SELECT vc.field,c.name,vc.heading,vc.width,vc.aggregate,vc.number_label,vc.decimals,vc.decimal_separator,vc.thousands_separator,vc.negative_style,vc.percent_total,vc.alignment FROM view_columns vc LEFT JOIN categories c ON c.id=vc.category_id WHERE vc.view_id=?1 ORDER BY vc.sort_order,vc.id")?;
        let columns = stmt
            .query_map([view_id], |r| {
                Ok(ViewColumn {
                    field: r.get(0)?,
                    category: r.get(1)?,
                    heading: r.get(2)?,
                    width: r.get::<_, u16>(3)?,
                    aggregate: enum_column(r, 4)?,
                    number_label: r.get(5)?,
                    decimals: r.get(6)?,
                    decimal_separator: r.get(7)?,
                    thousands_separator: r.get(8)?,
                    negative_style: r.get(9)?,
                    percent_total: r.get::<_, i64>(10)? != 0,
                    alignment: r.get(11)?,
                })
            })?
            .collect::<rusqlite::Result<Vec<_>>>()?;
        if columns.is_empty() {
            Ok(default_columns())
        } else {
            Ok(columns)
        }
    }

    fn view_sections(&self, view_id: i64) -> Result<Vec<ViewSection>> {
        let mut stmt=self.conn.prepare("SELECT id,heading,filter_expr,collapsed FROM view_sections WHERE view_id=?1 ORDER BY sort_order,id")?;
        Ok(stmt
            .query_map([view_id], |r| {
                Ok(ViewSection {
                    id: r.get(0)?,
                    heading: r.get(1)?,
                    filter_expr: r.get(2)?,
                    collapsed: r.get::<_, i64>(3)? != 0,
                })
            })?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn categories(&self) -> Result<Vec<Category>> {
        let mut stmt = self.conn.prepare("SELECT id,name,parent_id,kind,match_text,exclusive FROM categories ORDER BY parent_id IS NOT NULL,parent_id,sort_order,name COLLATE NOCASE")?;
        Ok(stmt
            .query_map([], Self::category_row)?
            .collect::<rusqlite::Result<_>>()?)
    }

    fn category_row(r: &rusqlite::Row<'_>) -> rusqlite::Result<Category> {
        Ok(Category {
            id: r.get(0)?,
            name: r.get(1)?,
            parent_id: r.get(2)?,
            kind: enum_column(r, 3)?,
            match_text: r.get(4)?,
            exclusive: r.get::<_, i64>(5)? != 0,
        })
    }

    pub fn save_category(
        &self,
        id: Option<i64>,
        name: &str,
        parent_id: Option<i64>,
        kind: &str,
        match_text: &str,
        exclusive: bool,
    ) -> Result<i64> {
        let name = name.trim();
        if name.is_empty() {
            bail!("category name cannot be empty");
        }
        if !matches!(kind, "standard" | "date" | "numeric") {
            bail!("category kind must be standard, date, or numeric");
        }
        if id.is_some() && id == parent_id {
            bail!("a category cannot be its own parent");
        }
        if let Some(id) = id {
            let current_kind: String =
                self.conn
                    .query_row("SELECT kind FROM categories WHERE id=?1", [id], |row| {
                        row.get(0)
                    })?;
            let has_values: bool = self.conn.query_row(
                "SELECT EXISTS(SELECT 1 FROM item_values WHERE category_id=?1)",
                [id],
                |row| row.get(0),
            )?;
            if has_values && current_kind != kind {
                bail!("remove the category's values before changing its kind");
            }
        }
        if let (Some(id), Some(parent)) = (id, parent_id) {
            let cycle: bool = self.conn.query_row(
                "WITH RECURSIVE descendants(id) AS (
                   SELECT id FROM categories WHERE parent_id=?1
                   UNION ALL SELECT c.id FROM categories c JOIN descendants d ON c.parent_id=d.id
                 ) SELECT EXISTS(SELECT 1 FROM descendants WHERE id=?2)",
                params![id, parent],
                |r| r.get(0),
            )?;
            if cycle {
                bail!("category hierarchy would contain a cycle");
            }
        }
        if let Some(id) = id {
            self.conn.execute(
                "UPDATE categories SET name=?1,parent_id=?2,kind=?3,match_text=?4,exclusive=?5 WHERE id=?6",
                params![name, parent_id, kind, match_text.trim(), exclusive as i64, id],
            )?;
            Ok(id)
        } else {
            self.conn.execute("INSERT INTO categories(name,parent_id,kind,match_text,exclusive,sort_order) VALUES(?1,?2,?3,?4,?5,(SELECT COALESCE(MAX(sort_order),0)+1 FROM categories))",params![name,parent_id,kind,match_text.trim(),exclusive as i64])?;
            Ok(self.conn.last_insert_rowid())
        }
    }

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

    pub fn category_rules(&self, category_id: i64) -> Result<Vec<CategoryRule>> {
        let mut stmt=self.conn.prepare("SELECT id,category_id,condition_expr,action_kind,action_value,enabled FROM category_rules WHERE category_id=?1 ORDER BY sort_order,id")?;
        Ok(stmt
            .query_map([category_id], |r| {
                Ok(CategoryRule {
                    id: r.get(0)?,
                    category_id: r.get(1)?,
                    condition_expr: r.get(2)?,
                    action_kind: enum_column(r, 3)?,
                    action_value: r.get(4)?,
                    enabled: r.get::<_, i64>(5)? != 0,
                })
            })?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn save_primary_rule(
        &self,
        category_id: i64,
        condition_expr: &str,
        action_spec: &str,
    ) -> Result<()> {
        if condition_expr.trim().is_empty() && action_spec.trim().is_empty() {
            let tx = self.conn.unchecked_transaction()?;
            tx.execute(
                "DELETE FROM category_rules WHERE category_id=?1",
                [category_id],
            )?;
            tx.execute(
                "DELETE FROM item_categories WHERE category_id=?1 AND assignment='conditional'",
                [category_id],
            )?;
            tx.execute(
                "DELETE FROM item_values WHERE category_id=?1 AND assignment='conditional'",
                [category_id],
            )?;
            tx.commit()?;
            return Ok(());
        }
        filter::parse(condition_expr).context("invalid category rule condition")?;
        let (kind, value) = parse_action_spec(action_spec)?;
        let tx = self.conn.unchecked_transaction()?;
        tx.execute(
            "DELETE FROM category_rules WHERE category_id=?1",
            [category_id],
        )?;
        if condition_expr.trim().is_empty() {
            tx.execute(
                "DELETE FROM item_categories WHERE category_id=?1 AND assignment='conditional'",
                [category_id],
            )?;
            tx.execute(
                "DELETE FROM item_values WHERE category_id=?1 AND assignment='conditional'",
                [category_id],
            )?;
        }
        tx.execute("INSERT INTO category_rules(category_id,condition_expr,action_kind,action_value,enabled,sort_order) VALUES(?1,?2,?3,?4,1,0)",params![category_id,condition_expr.trim(),kind.as_str(),value])?;
        let rule_id = tx.last_insert_rowid();
        if kind != RuleActionKind::None {
            tx.execute(
                "INSERT OR IGNORE INTO rule_action_runs(rule_id,item_id)
                 SELECT ?1,positive.item_id FROM item_categories positive
                  WHERE positive.category_id=?2 AND positive.assignment<>'excluded'
                    AND NOT EXISTS(
                      SELECT 1 FROM item_categories excluded
                       WHERE excluded.item_id=positive.item_id
                         AND excluded.category_id=positive.category_id
                         AND excluded.assignment='excluded'
                    )",
                params![rule_id, category_id],
            )?;
        }
        tx.commit()?;
        Ok(())
    }

    #[allow(clippy::too_many_arguments)]
    pub fn save_view_design(
        &mut self,
        id: Option<i64>,
        name: &str,
        kind: &str,
        filter_value: &str,
        sort_key: &str,
        show_done: bool,
        filter_expr: &str,
        columns_spec: &str,
        sections_spec: &str,
    ) -> Result<i64> {
        let name = name.trim();
        if name.is_empty() {
            bail!("view name cannot be empty");
        }
        if !matches!(
            kind,
            "list" | "category" | "upcoming" | "done" | "datebook" | "trash"
        ) {
            bail!("unsupported view kind");
        }
        if !matches!(
            sort_key,
            "manual" | "when" | "done" | "priority" | "updated"
        ) && parse_bound_field(sort_key).is_none()
        {
            bail!("unsupported view sort key");
        }
        filter::parse(filter_expr).context("invalid view filter")?;
        let columns = parse_columns_spec(columns_spec)?;
        let sections = parse_sections_spec(sections_spec)?;
        if let Some((field, Some(category))) = parse_bound_field(sort_key) {
            let kind: String = self
                .conn
                .query_row(
                    "SELECT kind FROM categories WHERE name=?1 COLLATE NOCASE",
                    [&category],
                    |row| row.get(0),
                )
                .with_context(|| format!("sort category {category:?} does not exist"))?;
            if kind != field {
                bail!("sort category {category:?} is {kind}, not {field}");
            }
        }
        let tx = self.conn.transaction()?;
        let view_id = if let Some(id) = id {
            tx.execute("UPDATE views SET name=?1,kind=?2,filter_value=?3,sort_key=?4,show_done=?5,filter_expr=?6 WHERE id=?7",params![name,kind,filter_value.trim(),sort_key,show_done as i64,filter_expr.trim(),id])?;
            id
        } else {
            tx.execute("INSERT INTO views(name,kind,filter_value,sort_key,show_done,sort_order,filter_expr) VALUES(?1,?2,?3,?4,?5,(SELECT COALESCE(MAX(sort_order),0)+1 FROM views),?6)",params![name,kind,filter_value.trim(),sort_key,show_done as i64,filter_expr.trim()])?;
            tx.last_insert_rowid()
        };
        tx.execute("DELETE FROM view_columns WHERE view_id=?1", [view_id])?;
        for (order, column) in columns.iter().enumerate() {
            let category_id = column
                .category
                .as_deref()
                .map(|name| {
                    tx.query_row(
                        "SELECT id FROM categories WHERE name=?1 COLLATE NOCASE",
                        [name],
                        |row| row.get::<_, i64>(0),
                    )
                    .with_context(|| format!("view column category {name:?} does not exist"))
                })
                .transpose()?;
            if let Some(category_id) = category_id {
                let category_kind: String = tx.query_row(
                    "SELECT kind FROM categories WHERE id=?1",
                    [category_id],
                    |row| row.get(0),
                )?;
                if matches!(column.field.as_str(), "numeric" | "date")
                    && category_kind != column.field
                {
                    bail!(
                        "{} column category {:?} is {}, not {}",
                        column.field,
                        column.category,
                        category_kind,
                        column.field
                    );
                }
            }
            tx.execute("INSERT INTO view_columns(view_id,field,category_id,heading,width,aggregate,number_label,decimals,decimal_separator,thousands_separator,negative_style,percent_total,alignment,sort_order) VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14)",params![view_id,column.field,category_id,column.heading,column.width,column.aggregate.as_str(),column.number_label,column.decimals,column.decimal_separator,column.thousands_separator,column.negative_style,column.percent_total as i64,column.alignment,order as i64])?;
        }
        tx.execute("DELETE FROM view_sections WHERE view_id=?1", [view_id])?;
        for (order, section) in sections.iter().enumerate() {
            tx.execute("INSERT INTO view_sections(view_id,heading,filter_expr,collapsed,sort_order) VALUES(?1,?2,?3,?4,?5)",params![view_id,section.heading,section.filter_expr,section.collapsed as i64,order as i64])?;
        }
        tx.commit()?;
        Ok(view_id)
    }

    pub fn delete_view(&self, id: i64) -> Result<()> {
        let count: i64 = self
            .conn
            .query_row("SELECT COUNT(*) FROM views", [], |r| r.get(0))?;
        if count <= 1 {
            bail!("the last view cannot be deleted");
        }
        self.conn.execute("DELETE FROM views WHERE id=?1", [id])?;
        Ok(())
    }

    pub fn move_view(&self, id: i64, delta: i64) -> Result<()> {
        let views = self.views()?;
        let Some(index) = views.iter().position(|v| v.id == id) else {
            return Ok(());
        };
        let target = (index as i64 + delta).clamp(0, views.len().saturating_sub(1) as i64) as usize;
        if target == index {
            return Ok(());
        }
        self.conn.execute(
            "UPDATE views SET sort_order=?1 WHERE id=?2",
            params![target as i64, views[index].id],
        )?;
        self.conn.execute(
            "UPDATE views SET sort_order=?1 WHERE id=?2",
            params![index as i64, views[target].id],
        )?;
        Ok(())
    }

    pub fn dependency_ids(&self, item_id: i64) -> Result<Vec<i64>> {
        let mut stmt = self
            .conn
            .prepare("SELECT prerequisite_id FROM dependencies WHERE item_id=?1")?;
        Ok(stmt
            .query_map([item_id], |r| r.get(0))?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn toggle_dependency(&self, item_id: i64, prerequisite_id: i64) -> Result<()> {
        if item_id == prerequisite_id {
            bail!("an item cannot depend on itself");
        }
        let exists: bool = self.conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM dependencies WHERE item_id=?1 AND prerequisite_id=?2)",
            params![item_id, prerequisite_id],
            |r| r.get(0),
        )?;
        if exists {
            self.conn.execute(
                "DELETE FROM dependencies WHERE item_id=?1 AND prerequisite_id=?2",
                params![item_id, prerequisite_id],
            )?;
        } else {
            let cycle: bool = self.conn.query_row(
                "WITH RECURSIVE reach(id) AS (
                   SELECT prerequisite_id FROM dependencies WHERE item_id=?1
                   UNION SELECT d.prerequisite_id FROM dependencies d JOIN reach r ON d.item_id=r.id
                 ) SELECT EXISTS(SELECT 1 FROM reach WHERE id=?2)",
                params![prerequisite_id, item_id],
                |r| r.get(0),
            )?;
            if cycle {
                bail!("dependency would contain a cycle");
            }
            self.conn.execute(
                "INSERT INTO dependencies(item_id,prerequisite_id) VALUES(?1,?2)",
                params![item_id, prerequisite_id],
            )?;
        }
        Ok(())
    }

    pub fn unmet_dependencies(&self, item_id: i64) -> Result<i64> {
        Ok(self.conn.query_row("SELECT COUNT(*) FROM dependencies d JOIN items p ON p.id=d.prerequisite_id WHERE d.item_id=?1 AND p.done_at IS NULL",[item_id],|r|r.get(0))?)
    }

    pub fn add_item(&mut self, text: &str) -> Result<i64> {
        let text = text.trim();
        if text.is_empty() {
            bail!("item text cannot be empty");
        }
        let now = Local::now().to_rfc3339();
        let settings = self.document_settings()?;
        let when_at = self.interpret_date(text)?;
        let tx = self.conn.transaction()?;
        tx.execute("INSERT INTO items(text,when_at,created_at,updated_at,sort_order) VALUES(?1,?2,?3,?3,(SELECT COALESCE(MAX(sort_order),0)+1 FROM items))", params![text,when_at,now])?;
        let id = tx.last_insert_rowid();
        if settings.automatic_filing {
            Self::auto_assign_tx(&tx, id, text)?;
        }
        tx.commit()?;
        self.apply_rules_to_item(id)?;
        Ok(id)
    }

    fn auto_assign_tx(tx: &rusqlite::Transaction<'_>, item_id: i64, text: &str) -> Result<()> {
        tx.execute(
            "DELETE FROM item_categories WHERE item_id=?1 AND assignment='automatic'",
            [item_id],
        )?;
        let lower = text.to_lowercase();
        let mut stmt = tx.prepare("SELECT id,match_text FROM categories WHERE match_text <> ''")?;
        let matches: Vec<(i64, String)> = stmt
            .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?
            .collect::<rusqlite::Result<_>>()?;
        drop(stmt);
        for (cat, phrases) in matches {
            if phrases
                .split(',')
                .map(str::trim)
                .filter(|p| !p.is_empty())
                .any(|p| lower.contains(&p.to_lowercase()))
            {
                tx.execute("INSERT OR IGNORE INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'automatic')", params![item_id,cat])?;
            }
        }
        Ok(())
    }

    pub fn reapply_automatic_assignments(&mut self) -> Result<()> {
        let automatic_filing = self.document_settings()?.automatic_filing;
        let tx = self.conn.transaction()?;
        let items: Vec<(i64, String)> = {
            let mut stmt = tx.prepare("SELECT id,text FROM items WHERE discarded=0")?;
            stmt.query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?
                .collect::<rusqlite::Result<_>>()?
        };
        for (id, text) in items {
            if automatic_filing {
                Self::auto_assign_tx(&tx, id, &text)?;
            } else {
                tx.execute(
                    "DELETE FROM item_categories WHERE item_id=?1 AND assignment='automatic'",
                    [id],
                )?;
            }
        }
        tx.commit()?;
        self.apply_rules_to_all()?;
        Ok(())
    }

    pub fn update_item(&mut self, id: i64, changes: &ItemChanges) -> Result<()> {
        let automatic_filing = self.document_settings()?.automatic_filing;
        let tx = self.conn.transaction()?;
        if let Some(text) = &changes.text {
            if text.trim().is_empty() {
                bail!("item text cannot be empty");
            }
            tx.execute(
                "UPDATE items SET text=?1,updated_at=?2 WHERE id=?3",
                params![text.trim(), Local::now().to_rfc3339(), id],
            )?;
            if automatic_filing {
                Self::auto_assign_tx(&tx, id, text)?;
            }
        }
        if let Some(note) = &changes.note {
            tx.execute(
                "UPDATE items SET note=?1,updated_at=?2 WHERE id=?3",
                params![note, Local::now().to_rfc3339(), id],
            )?;
        }
        if let Some(priority) = changes.priority {
            tx.execute(
                "UPDATE items SET priority=?1,updated_at=?2 WHERE id=?3",
                params![priority.clamp(1, 5), Local::now().to_rfc3339(), id],
            )?;
        }
        if let Some(when_at) = &changes.when_at {
            tx.execute(
                "UPDATE items SET when_at=?1,updated_at=?2 WHERE id=?3",
                params![when_at, Local::now().to_rfc3339(), id],
            )?;
        }
        if let Some(alarm_at) = &changes.alarm_at {
            tx.execute(
                "UPDATE items SET alarm_at=?1,updated_at=?2 WHERE id=?3",
                params![alarm_at, Local::now().to_rfc3339(), id],
            )?;
        }
        if let Some(recurrence) = &changes.recurrence {
            if !recurrence.trim().is_empty() {
                let base = if let Some(when_at) = &changes.when_at {
                    when_at.clone()
                } else {
                    tx.query_row("SELECT when_at FROM items WHERE id=?1", [id], |r| {
                        r.get::<_, Option<String>>(0)
                    })?
                };
                if let Some(base) = base {
                    next_occurrence(&base, recurrence)?;
                }
            }
            tx.execute(
                "UPDATE items SET recurrence=?1,updated_at=?2 WHERE id=?3",
                params![recurrence.trim(), Local::now().to_rfc3339(), id],
            )?;
        }
        tx.commit()?;
        if let Some(value) = changes.numeric_value {
            self.set_legacy_numeric_value(id, value)?;
        }
        self.apply_rules_to_item(id)?;
        Ok(())
    }

    fn all_rules(&self) -> Result<Vec<CategoryRule>> {
        let mut stmt=self.conn.prepare("SELECT id,category_id,condition_expr,action_kind,action_value,enabled FROM category_rules WHERE enabled=1 ORDER BY sort_order,id")?;
        Ok(stmt
            .query_map([], |r| {
                Ok(CategoryRule {
                    id: r.get(0)?,
                    category_id: r.get(1)?,
                    condition_expr: r.get(2)?,
                    action_kind: enum_column(r, 3)?,
                    action_value: r.get(4)?,
                    enabled: r.get::<_, i64>(5)? != 0,
                })
            })?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn apply_rules_to_all(&mut self) -> Result<()> {
        let mut stmt = self
            .conn
            .prepare("SELECT id FROM items WHERE discarded=0")?;
        let ids = stmt
            .query_map([], |r| r.get(0))?
            .collect::<rusqlite::Result<Vec<i64>>>()?;
        drop(stmt);
        for id in ids {
            self.apply_rules_to_item(id)?;
        }
        Ok(())
    }

    pub fn execute_rules(&mut self, ids: &[i64]) -> Result<ExecuteSummary> {
        for id in ids {
            self.conn
                .execute("DELETE FROM rule_action_runs WHERE item_id=?1", [id])?;
            self.apply_rules_to_item(*id)?;
        }
        let conflicts = if ids.is_empty() {
            0
        } else {
            let placeholders = std::iter::repeat_n("?", ids.len())
                .collect::<Vec<_>>()
                .join(",");
            let mut statement = self.conn.prepare(&format!(
                "SELECT COUNT(*) FROM rule_conflicts WHERE item_id IN ({placeholders})"
            ))?;
            let values = ids
                .iter()
                .map(|id| id as &dyn rusqlite::ToSql)
                .collect::<Vec<_>>();
            statement.query_row(values.as_slice(), |row| row.get::<_, i64>(0))? as usize
        };
        Ok(ExecuteSummary {
            items: ids.len(),
            conflicts,
        })
    }

    pub fn all_item_ids(&self) -> Result<Vec<i64>> {
        let mut statement = self
            .conn
            .prepare("SELECT id FROM items WHERE discarded=0 ORDER BY sort_order,id")?;
        Ok(statement
            .query_map([], |row| row.get(0))?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn rule_conflicts(&self) -> Result<Vec<RuleConflict>> {
        let mut statement = self.conn.prepare(
            "SELECT c.item_id,i.text,c.message FROM rule_conflicts c
             JOIN items i ON i.id=c.item_id ORDER BY c.updated_at DESC",
        )?;
        Ok(statement
            .query_map([], |row| {
                Ok(RuleConflict {
                    item_id: row.get(0)?,
                    item_text: row.get(1)?,
                    message: row.get(2)?,
                })
            })?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn count_matching_filter(&self, source: &str) -> Result<usize> {
        let expr = filter::parse(source)?;
        let mut stmt = self
            .conn
            .prepare("SELECT id FROM items WHERE discarded=0")?;
        let ids = stmt
            .query_map([], |r| r.get(0))?
            .collect::<rusqlite::Result<Vec<i64>>>()?;
        drop(stmt);
        let mut count = 0;
        for id in ids {
            if let Some(item) = self.load_item(id)?
                && filter::evaluate(&expr, &item)
            {
                count += 1;
            }
        }
        Ok(count)
    }

    fn apply_rules_to_item(&mut self, item_id: i64) -> Result<()> {
        let rules = self.all_rules()?;
        for _ in 0..16 {
            let Some(mut item) = self.load_item(item_id)? else {
                return Ok(());
            };
            let mut changed = false;
            for rule in &rules {
                if !rule.condition_expr.trim().is_empty() {
                    let expr = filter::parse(&rule.condition_expr)?;
                    let matched = filter::evaluate(&expr, &item);
                    changed |=
                        self.set_conditional_assignment(item_id, rule.category_id, matched)?;
                }
            }
            item = self.load_item(item_id)?.unwrap_or(item);
            for rule in &rules {
                if rule.action_kind == RuleActionKind::None {
                    continue;
                }
                let source_assigned = item
                    .categories
                    .iter()
                    .any(|category| category.id == rule.category_id);
                let ran: bool = self.conn.query_row(
                    "SELECT EXISTS(SELECT 1 FROM rule_action_runs WHERE rule_id=?1 AND item_id=?2)",
                    params![rule.id, item_id],
                    |row| row.get(0),
                )?;
                if source_assigned && !ran {
                    changed |= self.apply_rule_action(item_id, rule, &item)?;
                    self.conn.execute(
                        "INSERT OR IGNORE INTO rule_action_runs(rule_id,item_id) VALUES(?1,?2)",
                        params![rule.id, item_id],
                    )?;
                } else if !source_assigned && ran {
                    self.conn.execute(
                        "DELETE FROM rule_action_runs WHERE rule_id=?1 AND item_id=?2",
                        params![rule.id, item_id],
                    )?;
                }
            }
            if !changed {
                self.conn
                    .execute("DELETE FROM rule_conflicts WHERE item_id=?1", [item_id])?;
                return Ok(());
            }
        }
        self.conn.execute(
            "INSERT INTO rule_conflicts(item_id,message,updated_at) VALUES(?1,?2,?3)
             ON CONFLICT(item_id) DO UPDATE SET message=excluded.message,updated_at=excluded.updated_at",
            params![item_id,"category conditions did not converge after 16 passes",Local::now().to_rfc3339()],
        )?;
        Ok(())
    }

    fn set_conditional_assignment(
        &self,
        item_id: i64,
        category_id: i64,
        matched: bool,
    ) -> Result<bool> {
        let exists: bool = self.conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='conditional')",
            params![item_id,category_id],
            |row| row.get(0),
        )?;
        if matched == exists {
            return Ok(false);
        }
        if matched {
            self.conn.execute(
                "INSERT INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'conditional')",
                params![item_id,category_id],
            )?;
        } else {
            self.conn.execute(
                "DELETE FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='conditional'",
                params![item_id,category_id],
            )?;
            self.conn.execute(
                "DELETE FROM item_values WHERE item_id=?1 AND category_id=?2 AND assignment='conditional'",
                params![item_id,category_id],
            )?;
        }
        Ok(true)
    }

    fn apply_rule_action(&self, item_id: i64, rule: &CategoryRule, item: &Item) -> Result<bool> {
        match rule.action_kind {
            RuleActionKind::None => Ok(false),
            RuleActionKind::Assign | RuleActionKind::Exclude => {
                let target = self.action_category(rule.category_id, &rule.action_value)?;
                let Some(target) = target else {
                    return Ok(false);
                };
                if rule.action_kind == RuleActionKind::Assign {
                    let exists: bool = self.conn.query_row("SELECT EXISTS(SELECT 1 FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='explicit')",params![item_id,target],|row|row.get(0))?;
                    if !exists {
                        self.conn.execute("DELETE FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='excluded'",params![item_id,target])?;
                        self.conn.execute("INSERT INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'explicit')",params![item_id,target])?;
                        return Ok(true);
                    }
                } else {
                    let exists: bool = self.conn.query_row("SELECT EXISTS(SELECT 1 FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='excluded')",params![item_id,target],|row|row.get(0))?;
                    if !exists {
                        self.conn.execute("INSERT INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'excluded')",params![item_id,target])?;
                        self.conn.execute(
                            "DELETE FROM item_values WHERE item_id=?1 AND category_id=?2",
                            params![item_id, target],
                        )?;
                        return Ok(true);
                    }
                }
                Ok(false)
            }
            RuleActionKind::Remove => {
                let Some(target) = self.action_category(rule.category_id, &rule.action_value)?
                else {
                    return Ok(false);
                };
                let changed = self.conn.execute(
                    "DELETE FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='explicit'",
                    params![item_id,target],
                )? > 0;
                self.conn.execute(
                    "DELETE FROM item_values WHERE item_id=?1 AND category_id=?2",
                    params![item_id, target],
                )?;
                let excluded = self.conn.execute(
                    "INSERT OR IGNORE INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'excluded')",
                    params![item_id,target],
                )? > 0;
                Ok(changed || excluded)
            }
            RuleActionKind::Numeric => {
                let (category, raw_value) = split_category_value(&rule.action_value)?;
                let target = self
                    .action_category(rule.category_id, category)?
                    .context("numeric action category does not exist")?;
                let value = raw_value.parse::<f64>()?;
                let changed = item.numeric_value_for(category) != Some(value);
                if changed {
                    self.set_category_value(item_id, target, Some(value), None, "explicit")?;
                }
                Ok(changed)
            }
            RuleActionKind::Date => {
                let (category, raw_value) = split_category_value(&rule.action_value)?;
                let target = self
                    .action_category(rule.category_id, category)?
                    .context("date action category does not exist")?;
                let value = self
                    .interpret_date(raw_value)?
                    .unwrap_or_else(|| raw_value.to_owned());
                let changed = item.date_value_for(category) != Some(value.as_str());
                if changed {
                    self.set_category_value(item_id, target, None, Some(&value), "explicit")?;
                }
                Ok(changed)
            }
            RuleActionKind::Value => {
                let value = rule.action_value.parse::<f64>()?;
                let target = self
                    .conn
                    .query_row(
                        "SELECT id FROM categories WHERE id=?1 AND kind='numeric'",
                        [rule.category_id],
                        |row| row.get::<_, i64>(0),
                    )
                    .optional()?
                    .or_else(|| {
                        self.conn
                            .query_row(
                                "SELECT id FROM categories WHERE kind='numeric' ORDER BY id LIMIT 1",
                                [],
                                |row| row.get(0),
                            )
                            .optional()
                            .ok()
                            .flatten()
                    })
                    .context("value action needs a numeric category")?;
                let changed = item
                    .values
                    .iter()
                    .find(|entry| entry.category_id == target)
                    .and_then(|entry| entry.numeric_value)
                    != Some(value);
                if changed {
                    self.set_category_value(item_id, target, Some(value), None, "explicit")?;
                }
                Ok(changed)
            }
            RuleActionKind::Export => {
                self.export_rule_item(item, &rule.action_value)?;
                Ok(false)
            }
            RuleActionKind::Discard => {
                if !item.discarded && parse_bool(&rule.action_value) {
                    self.conn.execute(
                        "UPDATE items SET discarded=1,updated_at=?1 WHERE id=?2",
                        params![Local::now().to_rfc3339(), item_id],
                    )?;
                    return Ok(true);
                }
                Ok(false)
            }
            RuleActionKind::Priority => {
                let value = rule.action_value.parse::<i64>()?.clamp(1, 5);
                if item.priority != value {
                    self.conn.execute(
                        "UPDATE items SET priority=?1,updated_at=?2 WHERE id=?3",
                        params![value, Local::now().to_rfc3339(), item_id],
                    )?;
                    Ok(true)
                } else {
                    Ok(false)
                }
            }
            RuleActionKind::When => {
                let value = self
                    .interpret_date(&rule.action_value)?
                    .context("rule action has no recognizable date")?;
                if item.when_at.as_deref() != Some(value.as_str()) {
                    self.conn.execute(
                        "UPDATE items SET when_at=?1,updated_at=?2 WHERE id=?3",
                        params![value, Local::now().to_rfc3339(), item_id],
                    )?;
                    Ok(true)
                } else {
                    Ok(false)
                }
            }
            RuleActionKind::Alarm => {
                let value = self
                    .interpret_date(&rule.action_value)?
                    .context("rule action has no recognizable alarm date")?;
                if item.alarm_at.as_deref() != Some(value.as_str()) {
                    self.conn.execute(
                        "UPDATE items SET alarm_at=?1,updated_at=?2 WHERE id=?3",
                        params![value, Local::now().to_rfc3339(), item_id],
                    )?;
                    Ok(true)
                } else {
                    Ok(false)
                }
            }
            RuleActionKind::Repeat => {
                let value = rule.action_value.trim();
                if let Some(when) = item.when_at.as_deref() {
                    next_occurrence(when, value)?;
                }
                if item.recurrence != value {
                    self.conn.execute(
                        "UPDATE items SET recurrence=?1,updated_at=?2 WHERE id=?3",
                        params![value, Local::now().to_rfc3339(), item_id],
                    )?;
                    Ok(true)
                } else {
                    Ok(false)
                }
            }
            RuleActionKind::Done => {
                let done = parse_bool(&rule.action_value);
                if item.done_at.is_some() != done {
                    let value = if done {
                        Some(Local::now().to_rfc3339())
                    } else {
                        None
                    };
                    self.conn.execute(
                        "UPDATE items SET done_at=?1,updated_at=?2 WHERE id=?3",
                        params![value, Local::now().to_rfc3339(), item_id],
                    )?;
                    Ok(true)
                } else {
                    Ok(false)
                }
            }
        }
    }

    fn action_category(&self, source: i64, name: &str) -> Result<Option<i64>> {
        if name.trim().is_empty() {
            return Ok(Some(source));
        }
        Ok(self
            .conn
            .query_row(
                "SELECT id FROM categories WHERE name=?1 COLLATE NOCASE",
                [name.trim()],
                |row| row.get(0),
            )
            .optional()?)
    }

    fn export_rule_item(&self, item: &Item, specification: &str) -> Result<()> {
        use std::io::Write;

        let (format, path) = specification
            .split_once('|')
            .context("export action must use format|path")?;
        let mut file = fs::OpenOptions::new()
            .create(true)
            .append(true)
            .open(path.trim())
            .with_context(|| format!("could not open rule export {}", path.trim()))?;
        match format.trim().to_lowercase().as_str() {
            "jsonl" | "json" => writeln!(
                file,
                "{{\"id\":{},\"text\":{},\"note\":{},\"categories\":{}}}",
                item.id,
                json(&item.text),
                json(&item.note),
                json(&item.category_names())
            )?,
            "csv" => writeln!(
                file,
                "{},{},{},{}",
                item.id,
                csv(&item.text),
                csv(&item.note),
                csv(&item.category_names())
            )?,
            _ => bail!("rule export format must be jsonl or csv"),
        }
        Ok(())
    }

    fn load_item(&self, id: i64) -> Result<Option<Item>> {
        let mut item=self.conn.query_row("SELECT id,text,note,priority,when_at,done_at,alarm_at,numeric_value,created_at,updated_at,discarded,recurrence FROM items WHERE id=?1",[id],|r|Ok(Item{id:r.get(0)?,text:r.get(1)?,note:r.get(2)?,priority:r.get(3)?,when_at:r.get(4)?,done_at:r.get(5)?,alarm_at:r.get(6)?,numeric_value:r.get(7)?,created_at:r.get(8)?,updated_at:r.get(9)?,discarded:r.get::<_,i64>(10)?!=0,recurrence:r.get(11)?,categories:vec![],values:vec![]})).optional()?;
        if let Some(item) = &mut item {
            item.categories = self.categories_for_item(id)?;
            item.values = self.values_for_item(id)?;
            item.numeric_value = item
                .values
                .iter()
                .find_map(|value| value.numeric_value)
                .or(item.numeric_value);
        }
        Ok(item)
    }

    pub fn toggle_done(&mut self, ids: &[i64]) -> Result<()> {
        let settings = self.document_settings()?;
        for id in ids {
            let Some(item) = self.load_item(*id)? else {
                continue;
            };
            let now = Local::now().to_rfc3339();
            let next = if item.done_at.is_none() && !item.recurrence.is_empty() {
                item.when_at
                    .as_deref()
                    .map(|when| next_occurrence(when, &item.recurrence))
                    .transpose()?
            } else {
                None
            };
            let tx = self.conn.transaction()?;
            let mut next_id = None;
            if let Some(next_when) = next {
                let next_alarm = shift_alarm(
                    item.alarm_at.as_deref(),
                    item.when_at.as_deref(),
                    &next_when,
                );
                tx.execute("INSERT INTO items(text,note,priority,when_at,alarm_at,recurrence,created_at,updated_at,sort_order) VALUES(?1,?2,?3,?4,?5,?6,?7,?7,(SELECT COALESCE(MAX(sort_order),0)+1 FROM items))",params![&item.text,&item.note,item.priority,next_when,next_alarm,&item.recurrence,&now])?;
                let created = tx.last_insert_rowid();
                if settings.automatic_filing {
                    Self::auto_assign_tx(&tx, created, &item.text)?;
                }
                tx.execute("INSERT OR REPLACE INTO item_categories(item_id,category_id,assignment) SELECT ?1,category_id,assignment FROM item_categories WHERE item_id=?2 AND assignment IN ('explicit','excluded')",params![created,id])?;
                tx.execute("INSERT OR REPLACE INTO item_values(item_id,category_id,numeric_value,date_value,assignment) SELECT ?1,category_id,numeric_value,date_value,assignment FROM item_values WHERE item_id=?2 AND assignment='explicit'",params![created,id])?;
                next_id = Some(created);
            }
            let discard_completed =
                item.done_at.is_none() && settings.done_policy == DonePolicy::Trash;
            tx.execute("UPDATE items SET done_at=CASE WHEN done_at IS NULL THEN ?1 ELSE NULL END,discarded=CASE WHEN ?3 THEN 1 ELSE discarded END,updated_at=?1 WHERE id=?2",params![now,id,discard_completed])?;
            tx.commit()?;
            self.apply_rules_to_item(*id)?;
            if let Some(next_id) = next_id {
                self.apply_rules_to_item(next_id)?;
            }
        }
        Ok(())
    }

    pub fn discard(&self, ids: &[i64], discarded: bool) -> Result<()> {
        let permanent =
            discarded && self.document_settings()?.trash_policy == TrashPolicy::Immediate;
        for id in ids {
            if permanent {
                self.conn.execute("DELETE FROM items WHERE id=?1", [id])?;
            } else {
                self.conn.execute(
                    "UPDATE items SET discarded=?1,updated_at=?2 WHERE id=?3",
                    params![discarded as i64, Local::now().to_rfc3339(), id],
                )?;
            }
        }
        Ok(())
    }

    pub fn toggle_assignment(&mut self, item_id: i64, category_id: i64) -> Result<()> {
        let explicit: bool = self.conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='explicit')",
            params![item_id,category_id], |row| row.get(0))?;
        let excluded: bool = self.conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='excluded')",
            params![item_id,category_id], |row| row.get(0))?;
        let inferred: bool = self.conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment IN ('automatic','conditional'))",
            params![item_id,category_id], |row| row.get(0))?;
        if excluded {
            self.conn.execute(
                "DELETE FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='excluded'",
                params![item_id, category_id],
            )?;
        } else if explicit || inferred {
            self.conn.execute(
                "DELETE FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='explicit'",
                params![item_id, category_id],
            )?;
            self.conn.execute(
                "INSERT OR IGNORE INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'excluded')",
                params![item_id, category_id],
            )?;
            self.conn.execute(
                "DELETE FROM item_values WHERE item_id=?1 AND category_id=?2",
                params![item_id, category_id],
            )?;
        } else {
            let exclusive: bool = self.conn.query_row(
                "SELECT exclusive FROM categories WHERE id=?1",
                [category_id],
                |r| r.get(0),
            )?;
            if exclusive {
                self.conn.execute(
                    "DELETE FROM item_values WHERE item_id=?1 AND category_id IN (SELECT sibling.id FROM categories chosen JOIN categories sibling ON sibling.parent_id IS chosen.parent_id AND sibling.exclusive=1 WHERE chosen.id=?2)",
                    params![item_id,category_id],
                )?;
                self.conn.execute(
                    "DELETE FROM item_categories WHERE item_id=?1 AND category_id IN (SELECT sibling.id FROM categories chosen JOIN categories sibling ON sibling.parent_id IS chosen.parent_id AND sibling.exclusive=1 WHERE chosen.id=?2)",
                    params![item_id,category_id],
                )?;
            }
            self.conn.execute("INSERT OR IGNORE INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'explicit')", params![item_id,category_id])?;
        }
        self.apply_rules_to_item(item_id)?;
        Ok(())
    }

    pub fn item_category_ids(&self, item_id: i64) -> Result<Vec<i64>> {
        let mut stmt = self.conn.prepare(
            "SELECT DISTINCT category_id FROM item_categories positive WHERE item_id=?1 AND assignment<>'excluded' AND NOT EXISTS(SELECT 1 FROM item_categories excluded WHERE excluded.item_id=positive.item_id AND excluded.category_id=positive.category_id AND excluded.assignment='excluded')",
        )?;
        Ok(stmt
            .query_map([item_id], |r| r.get(0))?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn excluded_category_ids(&self, item_id: i64) -> Result<Vec<i64>> {
        let mut stmt = self.conn.prepare(
            "SELECT category_id FROM item_categories WHERE item_id=?1 AND assignment='excluded'",
        )?;
        Ok(stmt
            .query_map([item_id], |r| r.get(0))?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn set_numeric_value(
        &mut self,
        item_id: i64,
        category_id: i64,
        value: Option<f64>,
    ) -> Result<()> {
        self.set_category_value(item_id, category_id, value, None, "explicit")?;
        self.apply_rules_to_item(item_id)
    }

    pub fn set_date_value(
        &mut self,
        item_id: i64,
        category_id: i64,
        value: Option<String>,
    ) -> Result<()> {
        self.set_category_value(item_id, category_id, None, value.as_deref(), "explicit")?;
        self.apply_rules_to_item(item_id)
    }

    fn set_legacy_numeric_value(&self, item_id: i64, value: Option<f64>) -> Result<()> {
        let category_id = self
            .conn
            .query_row(
                "SELECT c.id FROM categories c
                  LEFT JOIN item_categories ic ON ic.category_id=c.id AND ic.item_id=?1 AND ic.assignment<>'excluded'
                 WHERE c.kind='numeric' ORDER BY ic.item_id IS NULL,c.id LIMIT 1",
                [item_id],
                |row| row.get::<_, i64>(0),
            )
            .optional()?
            .context("a numeric category is required before setting a numeric value")?;
        self.set_category_value(item_id, category_id, value, None, "explicit")
    }

    fn set_category_value(
        &self,
        item_id: i64,
        category_id: i64,
        numeric_value: Option<f64>,
        date_value: Option<&str>,
        assignment: &str,
    ) -> Result<()> {
        let kind: CategoryKind = self.conn.query_row(
            "SELECT kind FROM categories WHERE id=?1",
            [category_id],
            |row| enum_column(row, 0),
        )?;
        if numeric_value.is_some() && kind != CategoryKind::Numeric {
            bail!("numeric values require a numeric category");
        }
        if date_value.is_some() && kind != CategoryKind::Date {
            bail!("date values require a date category");
        }
        if numeric_value.is_none() && date_value.is_none() {
            self.conn.execute(
                "DELETE FROM item_values WHERE item_id=?1 AND category_id=?2",
                params![item_id, category_id],
            )?;
            return Ok(());
        }
        self.conn.execute(
            "DELETE FROM item_categories WHERE item_id=?1 AND category_id=?2 AND assignment='excluded'",
            params![item_id, category_id],
        )?;
        self.conn.execute(
            "INSERT OR IGNORE INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,?3)",
            params![item_id, category_id, assignment],
        )?;
        self.conn.execute(
            "INSERT INTO item_values(item_id,category_id,numeric_value,date_value,assignment)
             VALUES(?1,?2,?3,?4,?5)
             ON CONFLICT(item_id,category_id) DO UPDATE SET
                numeric_value=excluded.numeric_value,date_value=excluded.date_value,assignment=excluded.assignment",
            params![item_id, category_id, numeric_value, date_value, assignment],
        )?;
        self.conn.execute(
            "UPDATE items SET updated_at=?1 WHERE id=?2",
            params![Local::now().to_rfc3339(), item_id],
        )?;
        Ok(())
    }

    pub fn items(&self, view: &ViewDef, search: &str) -> Result<Vec<Item>> {
        let mut sql = String::from(
            "SELECT DISTINCT i.id,i.text,i.note,i.priority,i.when_at,i.done_at,i.alarm_at,i.numeric_value,i.created_at,i.updated_at,i.discarded,i.recurrence FROM items i",
        );
        let mut values: Vec<String> = vec![];
        if view.kind == ViewKind::Category {
            sql.push_str(
                " JOIN item_categories ic ON ic.item_id=i.id AND ic.assignment<>'excluded'",
            );
        }
        sql.push_str(if view.kind == ViewKind::Trash {
            " WHERE i.discarded=1"
        } else {
            " WHERE i.discarded=0"
        });
        match view.kind {
            ViewKind::Category => {
                sql.push_str(" AND ic.category_id IN (WITH RECURSIVE descendants(id) AS (SELECT id FROM categories WHERE name=? UNION ALL SELECT c.id FROM categories c JOIN descendants d ON c.parent_id=d.id) SELECT id FROM descendants) AND NOT EXISTS(SELECT 1 FROM item_categories ex JOIN categories ec ON ec.id=ex.category_id WHERE ex.item_id=i.id AND ex.assignment='excluded' AND ec.name=?)");
                values.extend([view.filter_value.clone(), view.filter_value.clone()]);
            }
            ViewKind::Upcoming | ViewKind::Datebook => {
                let days = view.filter_value.parse::<i64>().unwrap_or(30);
                sql.push_str(" AND i.when_at IS NOT NULL AND i.when_at <= ?");
                values.push((Local::now() + Duration::days(days)).to_rfc3339());
            }
            ViewKind::Done => {
                let days = view.filter_value.parse::<i64>().unwrap_or(14);
                sql.push_str(" AND i.done_at IS NOT NULL AND i.done_at >= ?");
                values.push((Local::now() - Duration::days(days)).to_rfc3339());
            }
            ViewKind::List | ViewKind::Trash => {}
        }
        if !view.show_done && view.kind != ViewKind::Done {
            sql.push_str(" AND i.done_at IS NULL");
        }
        if !search.is_empty() {
            sql.push_str(" AND (i.text LIKE ? OR i.note LIKE ?)");
            let like = format!("%{search}%");
            values.extend([like.clone(), like]);
        }
        sql.push_str(match view.sort_key.as_str() {
            "when" => " ORDER BY i.when_at IS NULL,i.when_at,i.priority,i.sort_order",
            "done" => " ORDER BY i.done_at DESC,i.sort_order",
            "priority" => " ORDER BY i.priority,i.when_at IS NULL,i.when_at",
            "updated" => " ORDER BY i.updated_at DESC",
            _ => " ORDER BY i.sort_order,i.id",
        });
        let mut stmt = self.conn.prepare(&sql)?;
        let refs: Vec<&dyn rusqlite::ToSql> =
            values.iter().map(|s| s as &dyn rusqlite::ToSql).collect();
        let rows = stmt.query_map(refs.as_slice(), |r| {
            Ok(Item {
                id: r.get(0)?,
                text: r.get(1)?,
                note: r.get(2)?,
                priority: r.get(3)?,
                when_at: r.get(4)?,
                done_at: r.get(5)?,
                alarm_at: r.get(6)?,
                numeric_value: r.get(7)?,
                created_at: r.get(8)?,
                updated_at: r.get(9)?,
                discarded: r.get::<_, i64>(10)? != 0,
                recurrence: r.get(11)?,
                categories: vec![],
                values: vec![],
            })
        })?;
        let mut items: Vec<Item> = rows.collect::<rusqlite::Result<_>>()?;
        for item in &mut items {
            item.categories = self.categories_for_item(item.id)?;
            item.values = self.values_for_item(item.id)?;
            item.numeric_value = item
                .values
                .iter()
                .find_map(|value| value.numeric_value)
                .or(item.numeric_value);
        }
        if !view.filter_expr.trim().is_empty() {
            let expr = filter::parse(&view.filter_expr).context("invalid saved view filter")?;
            items.retain(|item| filter::evaluate(&expr, item));
        }
        if let Some((field, Some(category))) = parse_bound_field(&view.sort_key) {
            items.sort_by(|left, right| match field.as_str() {
                "numeric" => compare_optional(
                    left.numeric_value_for(&category),
                    right.numeric_value_for(&category),
                    |left, right| left.partial_cmp(right).unwrap_or(std::cmp::Ordering::Equal),
                ),
                "date" => compare_optional(
                    left.date_value_for(&category),
                    right.date_value_for(&category),
                    Ord::cmp,
                ),
                _ => std::cmp::Ordering::Equal,
            });
        }
        Ok(items)
    }

    fn values_for_item(&self, item_id: i64) -> Result<Vec<CategoryValue>> {
        let mut statement = self.conn.prepare(
            "SELECT v.category_id,c.name,c.kind,v.numeric_value,v.date_value,v.assignment
               FROM item_values v JOIN categories c ON c.id=v.category_id
              WHERE v.item_id=?1 ORDER BY c.name COLLATE NOCASE",
        )?;
        Ok(statement
            .query_map([item_id], |row| {
                Ok(CategoryValue {
                    category_id: row.get(0)?,
                    category_name: row.get(1)?,
                    kind: enum_column(row, 2)?,
                    numeric_value: row.get(3)?,
                    date_value: row.get(4)?,
                    assignment: row.get(5)?,
                })
            })?
            .collect::<rusqlite::Result<_>>()?)
    }

    fn categories_for_item(&self, item_id: i64) -> Result<Vec<Category>> {
        let mut stmt = self.conn.prepare("WITH RECURSIVE inherited(id) AS (
            SELECT category_id FROM item_categories WHERE item_id=?1 AND assignment<>'excluded'
            UNION SELECT c.parent_id FROM categories c JOIN inherited i ON c.id=i.id WHERE c.parent_id IS NOT NULL
        ) SELECT DISTINCT c.id,c.name,c.parent_id,c.kind,c.match_text,c.exclusive FROM categories c JOIN inherited i ON i.id=c.id
          WHERE c.id NOT IN (SELECT category_id FROM item_categories WHERE item_id=?1 AND assignment='excluded') ORDER BY c.name")?;
        Ok(stmt
            .query_map([item_id], Self::category_row)?
            .collect::<rusqlite::Result<_>>()?)
    }

    pub fn due_alarm_count(&self) -> Result<i64> {
        Ok(self.conn.query_row("SELECT COUNT(*) FROM items WHERE discarded=0 AND done_at IS NULL AND alarm_at IS NOT NULL AND alarm_at <= ?1", [Local::now().to_rfc3339()], |r| r.get(0))?)
    }

    pub fn checkpoint(&self) -> Result<()> {
        self.conn.execute_batch("PRAGMA wal_checkpoint(PASSIVE)")?;
        Ok(())
    }

    pub fn empty_trash(&self) -> Result<usize> {
        Ok(self
            .conn
            .execute("DELETE FROM items WHERE discarded=1", [])?)
    }

    pub fn close_maintenance(&self) -> Result<()> {
        if self.document_settings()?.trash_policy == TrashPolicy::OnClose {
            self.empty_trash()?;
        }
        self.checkpoint()
    }

    pub fn import_path(&mut self, path: &Path) -> Result<usize> {
        if path
            .extension()
            .is_some_and(|ext| ext.eq_ignore_ascii_case("ics"))
        {
            return self.import_ical(path);
        }
        self.import_text(path)
    }

    fn import_text(&mut self, path: &Path) -> Result<usize> {
        let text = fs::read_to_string(path)
            .with_context(|| format!("could not read {}", path.display()))?;
        let mut count = 0;
        for line in text.lines().map(str::trim).filter(|l| !l.is_empty()) {
            self.add_item(line)?;
            count += 1;
        }
        Ok(count)
    }

    fn import_ical(&mut self, path: &Path) -> Result<usize> {
        let source = fs::read_to_string(path)
            .with_context(|| format!("could not read {}", path.display()))?;
        let mut lines: Vec<String> = vec![];
        for raw in source.lines() {
            let line = raw.trim_end_matches('\r');
            if (line.starts_with(' ') || line.starts_with('\t')) && !lines.is_empty() {
                lines.last_mut().unwrap().push_str(line.trim_start());
            } else {
                lines.push(line.to_owned());
            }
        }
        let mut records = vec![];
        let mut current: Option<IcalRecord> = None;
        for line in lines {
            match line.as_str() {
                "BEGIN:VEVENT" | "BEGIN:VTODO" => current = Some(IcalRecord::default()),
                "END:VEVENT" | "END:VTODO" => {
                    if let Some(record) = current.take() {
                        records.push(record)
                    }
                }
                _ => {
                    if let Some(record) = &mut current
                        && let Some((raw_key, value)) = line.split_once(':')
                    {
                        let key = raw_key.split(';').next().unwrap_or(raw_key);
                        let value = ical_unescape(value);
                        match key {
                            "SUMMARY" => record.summary = value,
                            "DESCRIPTION" => record.note = value,
                            "DTSTART" | "DUE" => record.when_at = parse_ical_date(&value),
                            "COMPLETED" => {
                                record.done = true;
                                record.completed_at = parse_ical_date(&value);
                            }
                            "STATUS" if value.eq_ignore_ascii_case("COMPLETED") => {
                                record.done = true
                            }
                            "PRIORITY" => record.priority = value.parse().ok(),
                            "CATEGORIES" => {
                                record.categories = value.split(',').map(ical_unescape).collect()
                            }
                            "RRULE" => record.recurrence = rrule_to_recurrence(&value),
                            _ => {}
                        }
                    }
                }
            }
        }
        let mut count = 0;
        for record in records.into_iter().filter(|r| !r.summary.trim().is_empty()) {
            let id = self.add_item(&record.summary)?;
            self.update_item(
                id,
                &ItemChanges {
                    note: Some(record.note),
                    priority: record.priority.map(|p| p.clamp(1, 5)),
                    when_at: Some(record.when_at),
                    recurrence: Some(record.recurrence),
                    ..Default::default()
                },
            )?;
            for name in record
                .categories
                .into_iter()
                .filter(|n| !n.trim().is_empty())
            {
                let category_id = if let Some(category) = self
                    .categories()?
                    .into_iter()
                    .find(|c| c.name.eq_ignore_ascii_case(&name))
                {
                    category.id
                } else {
                    self.save_category(None, &name, None, "standard", "", false)?
                };
                if !self.item_category_ids(id)?.contains(&category_id) {
                    self.toggle_assignment(id, category_id)?;
                }
            }
            if record.done {
                let completed_at = record
                    .completed_at
                    .unwrap_or_else(|| Local::now().to_rfc3339());
                self.conn.execute(
                    "UPDATE items SET done_at=?1,updated_at=?1 WHERE id=?2",
                    params![completed_at, id],
                )?;
            }
            count += 1;
        }
        Ok(count)
    }

    pub fn export(&self, format: &str, path: &Path, view_name: Option<&str>) -> Result<usize> {
        let views = self.views()?;
        let view = if let Some(name) = view_name {
            views
                .into_iter()
                .find(|v| v.name.eq_ignore_ascii_case(name))
                .with_context(|| format!("view {name:?} does not exist"))?
        } else {
            views
                .into_iter()
                .find(|v| v.name == "All Items")
                .context("All Items view is missing")?
        };
        let items = self.items(&view, "")?;
        let out = if format.eq_ignore_ascii_case("json") {
            let records = items.iter().map(|i| {
                let legacy_value = i.numeric_value.or_else(|| {
                    i.values.iter().find_map(|value| value.numeric_value)
                });
                let values=i.values.iter().map(|value|format!("{}:{}",json(&value.category_name),value.numeric_value.map(|number|number.to_string()).or_else(||value.date_value.as_ref().map(|date|json(date))).unwrap_or_else(||"null".into()))).collect::<Vec<_>>().join(",");
                format!("  {{\"id\":{},\"text\":{},\"note\":{},\"priority\":{},\"when\":{},\"done\":{},\"alarm\":{},\"value\":{},\"values\":{{{}}},\"recurrence\":{},\"categories\":{}}}", i.id,json(&i.text),json(&i.note),i.priority,opt_json(i.when_at.as_deref()),opt_json(i.done_at.as_deref()),opt_json(i.alarm_at.as_deref()),legacy_value.map(|value|value.to_string()).unwrap_or_else(||"null".into()),values,json(&i.recurrence),json(&i.category_names()))
            }).collect::<Vec<_>>().join(",\n");
            format!("[\n{records}\n]\n")
        } else if format.eq_ignore_ascii_case("csv") {
            let mut s = String::from(
                "id,text,note,priority,when,done,alarm,value,values,recurrence,categories\n",
            );
            for i in &items {
                let legacy_value = i
                    .numeric_value
                    .or_else(|| i.values.iter().find_map(|value| value.numeric_value));
                let values = i
                    .values
                    .iter()
                    .map(|value| {
                        format!(
                            "{}={}",
                            value.category_name,
                            value
                                .numeric_value
                                .map(|number| number.to_string())
                                .or_else(|| value.date_value.clone())
                                .unwrap_or_default()
                        )
                    })
                    .collect::<Vec<_>>()
                    .join("; ");
                s.push_str(&format!(
                    "{},{},{},{},{},{},{},{},{},{},{}\n",
                    i.id,
                    csv(&i.text),
                    csv(&i.note),
                    i.priority,
                    csv(i.when_at.as_deref().unwrap_or("")),
                    csv(i.done_at.as_deref().unwrap_or("")),
                    csv(i.alarm_at.as_deref().unwrap_or("")),
                    legacy_value
                        .map(|value| value.to_string())
                        .unwrap_or_default(),
                    csv(&values),
                    csv(&i.recurrence),
                    csv(&i.category_names())
                ));
            }
            s
        } else if matches!(format.to_lowercase().as_str(), "md" | "markdown") {
            let settings = self.document_settings()?;
            render_markdown(
                &view,
                &items,
                &settings.description,
                settings.report_headers,
            )
        } else if format.eq_ignore_ascii_case("html") {
            let settings = self.document_settings()?;
            render_html(
                &view,
                &items,
                &settings.description,
                settings.report_headers,
            )
        } else if matches!(format.to_lowercase().as_str(), "ics" | "ical") {
            render_ical(&items)
        } else {
            bail!("export format must be csv, json, markdown, html, or ics");
        };
        fs::write(path, out).with_context(|| format!("could not write {}", path.display()))?;
        Ok(items.len())
    }

    pub fn seed_demo(&mut self) -> Result<()> {
        let exists: Option<i64> = self
            .conn
            .query_row("SELECT id FROM items LIMIT 1", [], |r| r.get(0))
            .optional()?;
        if exists.is_some() {
            return Ok(());
        }
        for text in [
            "Call Sarah tomorrow at 3pm to review the Wells proposal",
            "Meet with Anne next Tuesday at 9am to discuss her progress",
            "Review quarterly budget in 3 days",
            "An idea about information that may become useful later",
            "Buy train tickets this Friday",
        ] {
            self.add_item(text)?;
        }
        Ok(())
    }
}

fn compare_optional<T>(
    left: Option<T>,
    right: Option<T>,
    compare: impl FnOnce(&T, &T) -> std::cmp::Ordering,
) -> std::cmp::Ordering {
    match (left, right) {
        (Some(left), Some(right)) => compare(&left, &right),
        (Some(_), None) => std::cmp::Ordering::Less,
        (None, Some(_)) => std::cmp::Ordering::Greater,
        (None, None) => std::cmp::Ordering::Equal,
    }
}

fn shift_alarm(alarm: Option<&str>, old_when: Option<&str>, new_when: &str) -> Option<String> {
    let alarm = DateTime::parse_from_rfc3339(alarm?).ok()?.naive_local();
    let old = DateTime::parse_from_rfc3339(old_when?).ok()?.naive_local();
    let new = DateTime::parse_from_rfc3339(new_when).ok()?.naive_local();
    Local
        .from_local_datetime(&(new + (alarm - old)))
        .earliest()
        .map(|date_time| date_time.to_rfc3339())
}
fn default_columns() -> Vec<ViewColumn> {
    [
        ("item", "Items", 48),
        ("categories", "Categories", 27),
        ("when", "When", 20),
        ("priority", "P", 5),
    ]
    .into_iter()
    .map(|(field, heading, width)| ViewColumn {
        field: field.into(),
        category: None,
        heading: heading.into(),
        width,
        aggregate: Aggregate::None,
        number_label: String::new(),
        decimals: 2,
        decimal_separator: ".".into(),
        thousands_separator: "comma".into(),
        negative_style: "minus".into(),
        percent_total: false,
        alignment: if matches!(field, "priority") {
            "right"
        } else {
            "left"
        }
        .into(),
    })
    .collect()
}

fn parse_action_spec(spec: &str) -> Result<(RuleActionKind, String)> {
    if spec.trim().is_empty() {
        return Ok((RuleActionKind::None, String::new()));
    }
    let (kind, value) = spec.trim().split_once(':').unwrap_or((spec.trim(), ""));
    let kind: RuleActionKind = kind
        .trim()
        .to_lowercase()
        .parse()
        .map_err(anyhow::Error::msg)
        .context(
            "rule action must be assign, exclude, remove, priority, numeric, date, when, alarm, repeat, done, export, or discard",
        )?;
    if matches!(
        kind,
        RuleActionKind::Priority
            | RuleActionKind::Value
            | RuleActionKind::Numeric
            | RuleActionKind::Date
            | RuleActionKind::When
            | RuleActionKind::Alarm
            | RuleActionKind::Repeat
            | RuleActionKind::Done
            | RuleActionKind::Export
            | RuleActionKind::Discard
    ) && value.trim().is_empty()
    {
        bail!("rule action {kind} needs a value")
    }
    if kind == RuleActionKind::Repeat {
        next_occurrence("2026-01-05T09:00:00+00:00", value.trim())?;
    }
    if matches!(kind, RuleActionKind::Numeric | RuleActionKind::Date) {
        split_category_value(value)?;
    }
    if kind == RuleActionKind::Export && !value.contains('|') {
        bail!("export action must use export:jsonl|path or export:csv|path");
    }
    Ok((kind, value.trim().into()))
}

fn split_category_value(source: &str) -> Result<(&str, &str)> {
    let (category, value) = source
        .split_once('=')
        .context("category value actions must use Category=value")?;
    if category.trim().is_empty() || value.trim().is_empty() {
        bail!("category value actions must use Category=value");
    }
    Ok((category.trim(), value.trim()))
}

fn parse_bool(value: &str) -> bool {
    matches!(
        value.trim().to_lowercase().as_str(),
        "1" | "yes" | "true" | "on" | "done"
    )
}

fn validate_document_settings(settings: &DocumentSettings) -> Result<()> {
    for (name, value) in [
        ("default time", &settings.default_time),
        ("morning time", &settings.morning_time),
        ("afternoon time", &settings.afternoon_time),
        ("evening time", &settings.evening_time),
    ] {
        NaiveTime::parse_from_str(value, "%H:%M")
            .with_context(|| format!("{name} must use 24-hour HH:MM format"))?;
    }
    if !(1..=16).contains(&settings.note_tab_width) {
        bail!("note tab width must be between 1 and 16")
    }
    Ok(())
}

fn parse_columns_spec(spec: &str) -> Result<Vec<ViewColumn>> {
    if spec.trim().is_empty() {
        return Ok(default_columns());
    }
    let mut columns = vec![];
    for raw in spec.split(',').map(str::trim).filter(|s| !s.is_empty()) {
        let parts: Vec<&str> = raw.split(':').collect();
        let raw_field = parts[0].trim();
        let (field, category) =
            parse_bound_field(raw_field).unwrap_or_else(|| (raw_field.to_lowercase(), None));
        if !matches!(
            field.as_str(),
            "item"
                | "categories"
                | "when"
                | "priority"
                | "note"
                | "value"
                | "numeric"
                | "date"
                | "done"
                | "alarm"
                | "recurrence"
                | "created"
                | "updated"
        ) {
            bail!("unknown view column {field}");
        }
        let width = parts
            .get(1)
            .and_then(|s| s.parse::<u16>().ok())
            .unwrap_or(20)
            .clamp(4, 100);
        let heading = parts
            .get(2)
            .map(|s| s.trim().to_owned())
            .filter(|s| !s.is_empty())
            .unwrap_or_else(|| default_heading(&field).into());
        let aggregate: Aggregate = parts
            .get(3)
            .map(|s| s.trim().to_lowercase())
            .unwrap_or_else(|| "none".into())
            .parse()
            .map_err(anyhow::Error::msg)?;
        if aggregate != Aggregate::None
            && !matches!(field.as_str(), "numeric" | "value" | "priority")
        {
            bail!("aggregate {aggregate} needs a numeric column");
        }
        if matches!(field.as_str(), "numeric" | "date") && category.is_none() {
            bail!("{field} columns must name a category, for example {field}[Hours]");
        }
        let number_label = parts.get(4).map_or("", |value| value.trim()).to_owned();
        let decimals = parts
            .get(5)
            .map_or(Ok(2), |value| value.trim().parse::<u8>())
            .context("numeric column decimal places must be 0 through 15")?;
        if decimals > 15 {
            bail!("numeric column decimal places must be 0 through 15");
        }
        let decimal_separator = match parts.get(6).map_or("dot", |value| value.trim()) {
            "dot" | "." => ".",
            "comma" => ",",
            _ => bail!("numeric column decimal separator must be dot or comma"),
        };
        let thousands_separator = parts.get(7).map_or("comma", |value| value.trim());
        if !matches!(thousands_separator, "comma" | "dot" | "space" | "none") {
            bail!("numeric column thousands separator must be comma, dot, space, or none");
        }
        if matches!(
            (decimal_separator, thousands_separator),
            (",", "comma") | (".", "dot")
        ) {
            bail!("numeric column decimal and thousands separators must differ");
        }
        let negative_style = parts.get(8).map_or("minus", |value| value.trim());
        if !matches!(negative_style, "minus" | "parentheses" | "trailing") {
            bail!("numeric column negative style must be minus, parentheses, or trailing");
        }
        let percent_total = parts
            .get(9)
            .is_some_and(|value| value.trim().eq_ignore_ascii_case("percent"));
        let alignment = parts.get(10).map_or(
            if matches!(field.as_str(), "numeric" | "value" | "priority") {
                "right"
            } else {
                "left"
            },
            |value| value.trim(),
        );
        if !matches!(alignment, "left" | "center" | "right") {
            bail!("column alignment must be left, center, or right");
        }
        columns.push(ViewColumn {
            field,
            category,
            heading,
            width,
            aggregate,
            number_label,
            decimals,
            decimal_separator: decimal_separator.into(),
            thousands_separator: thousands_separator.into(),
            negative_style: negative_style.into(),
            percent_total,
            alignment: alignment.into(),
        });
    }
    if columns.is_empty() {
        bail!("a view needs at least one column")
    }
    Ok(columns)
}

fn parse_bound_field(source: &str) -> Option<(String, Option<String>)> {
    let (field, category) = source.trim().split_once('[')?;
    let category = category.strip_suffix(']')?.trim();
    if category.is_empty() || !matches!(field.to_lowercase().as_str(), "numeric" | "date" | "value")
    {
        return None;
    }
    Some((
        if field.eq_ignore_ascii_case("value") {
            "numeric"
        } else {
            field
        }
        .to_lowercase(),
        Some(category.into()),
    ))
}

fn parse_sections_spec(spec: &str) -> Result<Vec<ViewSection>> {
    let mut sections = vec![];
    for raw in spec.split(';').map(str::trim).filter(|s| !s.is_empty()) {
        let mut parts = raw.split('|');
        let heading = parts.next().unwrap_or_default().trim();
        let expr = parts.next().unwrap_or_default().trim();
        let collapsed = parts
            .next()
            .is_some_and(|v| v.trim().eq_ignore_ascii_case("collapsed"));
        if heading.is_empty() {
            bail!("section heading cannot be empty")
        }
        filter::parse(expr).with_context(|| format!("invalid filter for section {heading}"))?;
        sections.push(ViewSection {
            id: 0,
            heading: heading.into(),
            filter_expr: expr.into(),
            collapsed,
        });
    }
    Ok(sections)
}

fn default_heading(field: &str) -> &str {
    match field {
        "item" => "Items",
        "categories" => "Categories",
        "when" => "When",
        "priority" => "P",
        "note" => "Note",
        "value" => "Value",
        "numeric" => "Value",
        "date" => "Date",
        "done" => "Done",
        "alarm" => "Alarm",
        "recurrence" => "Repeats",
        "created" => "Created",
        "updated" => "Updated",
        _ => field,
    }
}

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

    #[test]
    fn persists_items_and_auto_assigns() {
        let d = tempdir().unwrap();
        let p = d.path().join("test.agnd");
        let mut db = Database::open(&p).unwrap();
        db.add_item("Call Ada tomorrow").unwrap();
        let view = db
            .views()
            .unwrap()
            .into_iter()
            .find(|v| v.name == "All Items")
            .unwrap();
        let items = db.items(&view, "").unwrap();
        assert_eq!(items.len(), 1);
        assert!(items[0].when_at.is_some());
        assert!(items[0].categories.iter().any(|c| c.name == "Calls"));
    }

    #[test]
    fn category_assignment_toggles() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("x.agnd")).unwrap();
        let item = db.add_item("Unclassified thought").unwrap();
        let cat = db
            .categories()
            .unwrap()
            .into_iter()
            .find(|c| c.name == "Projects")
            .unwrap();
        db.toggle_assignment(item, cat.id).unwrap();
        assert!(db.item_category_ids(item).unwrap().contains(&cat.id));
        db.toggle_assignment(item, cat.id).unwrap();
        assert!(!db.item_category_ids(item).unwrap().contains(&cat.id));
    }

    #[test]
    fn category_views_inherit_through_the_hierarchy() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("inherit.agnd")).unwrap();
        let parent = db
            .save_category(None, "Research", None, "standard", "", false)
            .unwrap();
        let child = db
            .save_category(None, "Rust", Some(parent), "standard", "", false)
            .unwrap();
        let item = db.add_item("Borrow ownership notes").unwrap();
        db.toggle_assignment(item, child).unwrap();
        let view_id = db
            .save_view_design(
                None,
                "Research view",
                "category",
                "Research",
                "manual",
                true,
                "",
                "item:48,categories:27,when:20,priority:5",
                "",
            )
            .unwrap();
        let view = db
            .views()
            .unwrap()
            .into_iter()
            .find(|v| v.id == view_id)
            .unwrap();
        assert_eq!(db.items(&view, "").unwrap().len(), 1);
    }

    #[test]
    fn dependency_cycles_are_rejected() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("deps.agnd")).unwrap();
        let first = db.add_item("First").unwrap();
        let second = db.add_item("Second").unwrap();
        db.toggle_dependency(second, first).unwrap();
        assert!(db.toggle_dependency(first, second).is_err());
        assert_eq!(db.unmet_dependencies(second).unwrap(), 1);
    }

    #[test]
    fn view_design_roundtrips_columns_sections_and_boolean_filter() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("views.agnd")).unwrap();
        let urgent = db.add_item("Urgent proposal").unwrap();
        db.update_item(
            urgent,
            &ItemChanges {
                priority: Some(1),
                numeric_value: Some(Some(125.0)),
                ..Default::default()
            },
        )
        .unwrap();
        db.add_item("Someday proposal").unwrap();
        let id = db
            .save_view_design(
                None,
                "Focused",
                "list",
                "",
                "priority",
                true,
                "priority<=2 and open",
                "item:60:Task:none,value:20:Cost:sum",
                "Urgent|priority=1;Later|priority>1|collapsed",
            )
            .unwrap();
        let view = db
            .views()
            .unwrap()
            .into_iter()
            .find(|v| v.id == id)
            .unwrap();
        assert_eq!(view.columns[1].heading, "Cost");
        assert_eq!(view.columns[1].aggregate, Aggregate::Sum);
        assert_eq!(view.sections.len(), 2);
        assert!(view.sections[1].collapsed);
        assert_eq!(db.items(&view, "").unwrap().len(), 1);
    }

    #[test]
    fn category_rules_apply_and_invalid_edits_are_atomic() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("rules.agnd")).unwrap();
        let important = db
            .save_category(None, "Important", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(important, "text~urgent", "").unwrap();
        let item = db.add_item("Urgent customer request").unwrap();
        assert!(db.item_category_ids(item).unwrap().contains(&important));
        let promoter = db
            .save_category(None, "Promoter", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(promoter, "category=Important", "priority:1")
            .unwrap();
        db.apply_rules_to_all().unwrap();
        let all = db
            .views()
            .unwrap()
            .into_iter()
            .find(|v| v.name == "All Items")
            .unwrap();
        assert_eq!(db.items(&all, "").unwrap()[0].priority, 1);
        assert!(
            db.save_primary_rule(important, "priority nope 2", "")
                .is_err()
        );
        assert_eq!(
            db.category_rules(important).unwrap()[0].condition_expr,
            "text~urgent"
        );
    }

    #[test]
    fn completing_a_recurring_item_creates_the_next_occurrence() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("recurring.agnd")).unwrap();
        let id = db.add_item("Weekly project review").unwrap();
        db.update_item(
            id,
            &ItemChanges {
                note: Some("Bring the status sheet".into()),
                priority: Some(2),
                when_at: Some(Some("2026-09-01T14:30:00+00:00".into())),
                alarm_at: Some(Some("2026-09-01T13:30:00+00:00".into())),
                numeric_value: Some(Some(45.0)),
                recurrence: Some("weekly".into()),
                ..Default::default()
            },
        )
        .unwrap();
        let projects = db
            .categories()
            .unwrap()
            .into_iter()
            .find(|c| c.name == "Projects")
            .unwrap();
        db.toggle_assignment(id, projects.id).unwrap();

        db.toggle_done(&[id]).unwrap();
        let all = db
            .views()
            .unwrap()
            .into_iter()
            .find(|v| v.name == "All Items")
            .unwrap();
        let items = db.items(&all, "").unwrap();
        assert_eq!(items.len(), 2);
        let completed = items.iter().find(|item| item.id == id).unwrap();
        let next = items.iter().find(|item| item.id != id).unwrap();
        assert!(completed.done_at.is_some());
        assert!(next.done_at.is_none());
        assert_eq!(next.note, "Bring the status sheet");
        assert_eq!(next.priority, 2);
        assert_eq!(next.numeric_value, Some(45.0));
        assert_eq!(next.recurrence, "weekly");
        assert!(
            next.when_at
                .as_deref()
                .unwrap()
                .contains("2026-09-08T14:30:00")
        );
        assert!(
            next.alarm_at
                .as_deref()
                .unwrap()
                .contains("2026-09-08T13:30:00")
        );
        assert!(next.categories.iter().any(|c| c.name == "Projects"));

        db.toggle_done(&[id]).unwrap();
        assert_eq!(db.items(&all, "").unwrap().len(), 2);
    }

    #[test]
    fn exports_view_scoped_markdown_and_html_reports() {
        let d = tempdir().unwrap();
        let mut db = Database::open(&d.path().join("report.agnd")).unwrap();
        let keep = db.add_item("Keep <this> report").unwrap();
        db.update_item(
            keep,
            &ItemChanges {
                priority: Some(1),
                ..Default::default()
            },
        )
        .unwrap();
        db.add_item("Omit report").unwrap();
        db.save_view_design(
            None,
            "Priority report",
            "list",
            "",
            "priority",
            true,
            "priority=1",
            "item:70:Action:none,priority:30:Rank:none",
            "Important|priority=1",
        )
        .unwrap();
        let md = d.path().join("report.md");
        db.export("markdown", &md, Some("Priority report")).unwrap();
        let md = fs::read_to_string(md).unwrap();
        assert!(md.contains("## Important"));
        assert!(md.contains("Keep <this> report"));
        assert!(!md.contains("Omit report"));
        let html_path = d.path().join("report.html");
        db.export("html", &html_path, Some("Priority report"))
            .unwrap();
        let html = fs::read_to_string(html_path).unwrap();
        assert!(html.contains("Keep &lt;this&gt; report"));
        assert!(!html.contains("Omit report"));
    }

    #[test]
    fn document_settings_persist_and_drive_document_behavior() {
        let directory = tempdir().unwrap();
        let path = directory.path().join("configured.agnd");
        {
            let mut db = Database::open(&path).unwrap();
            db.save_document_settings(&DocumentSettings {
                description: "European planning file".into(),
                backup_on_open: true,
                trash_policy: TrashPolicy::Immediate,
                done_policy: DonePolicy::Trash,
                automatic_filing: false,
                date_order: crate::model::DateOrder::DayMonthYear,
                week_start: crate::model::WeekStart::Sunday,
                default_time: "08:30".into(),
                morning_time: "08:00".into(),
                afternoon_time: "14:00".into(),
                evening_time: "19:15".into(),
                note_tab_width: 8,
                report_headers: true,
            })
            .unwrap();
        }

        let mut db = Database::open(&path).unwrap();
        assert!(db.backup_path().exists());
        let settings = db.document_settings().unwrap();
        assert_eq!(settings.description, "European planning file");
        assert_eq!(settings.note_tab_width, 8);
        let item = db
            .add_item("Call Ada on 18/08/2026 in the evening")
            .unwrap();
        let all = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.name == "All Items")
            .unwrap();
        let items = db.items(&all, "").unwrap();
        assert!(
            items[0]
                .when_at
                .as_deref()
                .unwrap()
                .contains("2026-08-18T19:15:00")
        );
        assert!(
            !items[0]
                .categories
                .iter()
                .any(|category| category.name == "Calls")
        );

        db.toggle_done(&[item]).unwrap();
        let trash = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.name == "Trash")
            .unwrap();
        assert_eq!(db.items(&trash, "").unwrap().len(), 1);
        db.discard(&[item], true).unwrap();
        assert!(db.items(&trash, "").unwrap().is_empty());
    }

    #[test]
    fn ical_roundtrip_preserves_planner_fields() {
        let d = tempdir().unwrap();
        let mut source = Database::open(&d.path().join("source.agnd")).unwrap();
        let id = source.add_item("Prepare launch").unwrap();
        source
            .update_item(
                id,
                &ItemChanges {
                    note: Some("Bring charts".into()),
                    priority: Some(1),
                    when_at: Some(Some("2026-09-01T14:30:00+00:00".into())),
                    ..Default::default()
                },
            )
            .unwrap();
        let project = source
            .categories()
            .unwrap()
            .into_iter()
            .find(|c| c.name == "Projects")
            .unwrap();
        source.toggle_assignment(id, project.id).unwrap();
        source.toggle_done(&[id]).unwrap();
        source
            .update_item(
                id,
                &ItemChanges {
                    recurrence: Some("every 2 weeks".into()),
                    ..Default::default()
                },
            )
            .unwrap();
        let path = d.path().join("tasks.ics");
        source.export("ics", &path, None).unwrap();
        let mut target = Database::open(&d.path().join("target.agnd")).unwrap();
        assert_eq!(target.import_path(&path).unwrap(), 1);
        let all = target
            .views()
            .unwrap()
            .into_iter()
            .find(|v| v.name == "All Items")
            .unwrap();
        let items = target.items(&all, "").unwrap();
        assert_eq!(items.len(), 1);
        assert_eq!(items[0].text, "Prepare launch");
        assert_eq!(items[0].note, "Bring charts");
        assert_eq!(items[0].priority, 1);
        assert!(
            items[0]
                .when_at
                .as_deref()
                .unwrap()
                .contains("2026-09-01T14:30:00")
        );
        assert!(items[0].done_at.is_some());
        assert_eq!(items[0].recurrence, "every 2 weeks");
        assert!(items[0].categories.iter().any(|c| c.name == "Projects"));
    }

    #[test]
    fn numeric_categories_hold_independent_values_and_drive_filters_and_sorting() {
        let directory = tempdir().unwrap();
        let mut db = Database::open(&directory.path().join("numbers.agnd")).unwrap();
        let hours = db
            .save_category(None, "Hours", None, "numeric", "", false)
            .unwrap();
        let rate = db
            .save_category(None, "Rate", None, "numeric", "", false)
            .unwrap();
        let first = db.add_item("First job").unwrap();
        let second = db.add_item("Second job").unwrap();
        let missing = db.add_item("No estimate").unwrap();
        db.set_numeric_value(first, hours, Some(8.25)).unwrap();
        db.set_numeric_value(first, rate, Some(125.0)).unwrap();
        db.set_numeric_value(second, hours, Some(2.0)).unwrap();
        let view_id = db
            .save_view_design(
                None,
                "Billable",
                "list",
                "",
                "numeric[Hours]",
                true,
                "numeric:Hours>=2 and numeric:Rate inside 100..150",
                "item:40:Job,numeric[Hours]:12:Hours:sum::2:dot:comma:minus:percent:right,numeric[Rate]:14:Rate:none:$:2:comma:dot:parentheses:value:right",
                "",
            )
            .unwrap();
        let view = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.id == view_id)
            .unwrap();
        let items = db.items(&view, "").unwrap();
        assert_eq!(items.len(), 1);
        assert_eq!(items[0].numeric_value_for("Hours"), Some(8.25));
        assert_eq!(items[0].numeric_value_for("Rate"), Some(125.0));
        assert!(view.columns[1].percent_total);
        assert_eq!(view.columns[1].aggregate_value(items.iter()), "Total 8.25");
        assert_eq!(view.columns[2].format_number(-1250.0), "($1.250,00)");
        let mut european = view.columns[2].clone();
        european.number_label.clear();
        european.decimal_separator = ",".into();
        european.thousands_separator = "dot".into();
        european.negative_style = "minus".into();
        assert_eq!(european.format_number(1234.5), "1.234,50");
        let all = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.name == "All Items")
            .unwrap();
        let all_items = db.items(&all, "").unwrap();
        let second_item = all_items.iter().find(|item| item.id == second).unwrap();
        assert_eq!(view.columns[2].numeric_value(second_item), None);
        let mut count_column = view.columns[1].clone();
        count_column.aggregate = Aggregate::Count;
        assert_eq!(count_column.aggregate_value(all_items.iter()), "Count 2");
        let sorted_id = db
            .save_view_design(
                None,
                "Sorted hours",
                "list",
                "",
                "numeric[Hours]",
                true,
                "",
                "item:40:Job,numeric[Hours]:12:Hours",
                "",
            )
            .unwrap();
        let sorted = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.id == sorted_id)
            .unwrap();
        assert_eq!(
            db.items(&sorted, "")
                .unwrap()
                .iter()
                .map(|item| item.id)
                .collect::<Vec<_>>(),
            vec![second, first, missing]
        );
    }

    #[test]
    fn conditional_assignments_withdraw_but_explicit_assignments_survive() {
        let directory = tempdir().unwrap();
        let mut db = Database::open(&directory.path().join("conditions.agnd")).unwrap();
        let mut settings = db.document_settings().unwrap();
        settings.automatic_filing = false;
        db.save_document_settings(&settings).unwrap();
        let urgent = db
            .save_category(None, "Urgent", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(urgent, "text~urgent", "").unwrap();
        let item = db.add_item("Urgent request").unwrap();
        assert!(db.item_category_ids(item).unwrap().contains(&urgent));
        db.toggle_assignment(item, urgent).unwrap();
        assert!(!db.item_category_ids(item).unwrap().contains(&urgent));
        db.toggle_assignment(item, urgent).unwrap();
        assert!(db.item_category_ids(item).unwrap().contains(&urgent));
        db.update_item(
            item,
            &ItemChanges {
                text: Some("Routine request".into()),
                ..Default::default()
            },
        )
        .unwrap();
        assert!(!db.item_category_ids(item).unwrap().contains(&urgent));

        db.update_item(
            item,
            &ItemChanges {
                text: Some("Urgent once more".into()),
                ..Default::default()
            },
        )
        .unwrap();
        assert!(db.item_category_ids(item).unwrap().contains(&urgent));
        db.save_primary_rule(urgent, "", "").unwrap();
        assert!(!db.item_category_ids(item).unwrap().contains(&urgent));
        db.save_primary_rule(urgent, "text~urgent", "").unwrap();

        db.toggle_assignment(item, urgent).unwrap();
        db.update_item(
            item,
            &ItemChanges {
                text: Some("Urgent again".into()),
                ..Default::default()
            },
        )
        .unwrap();
        db.update_item(
            item,
            &ItemChanges {
                text: Some("Routine again".into()),
                ..Default::default()
            },
        )
        .unwrap();
        assert!(db.item_category_ids(item).unwrap().contains(&urgent));
    }

    #[test]
    fn actions_are_explicit_one_shot_mutations_and_execute_reapplies_them() {
        let directory = tempdir().unwrap();
        let mut db = Database::open(&directory.path().join("actions.agnd")).unwrap();
        let source = db
            .save_category(None, "Billable", None, "standard", "", false)
            .unwrap();
        let hours = db
            .save_category(None, "Hours", None, "numeric", "", false)
            .unwrap();
        db.save_primary_rule(source, "", "numeric:Hours=7.5")
            .unwrap();
        let item = db.add_item("Consulting").unwrap();
        db.toggle_assignment(item, source).unwrap();
        let all = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.name == "All Items")
            .unwrap();
        assert_eq!(
            db.items(&all, "").unwrap()[0].numeric_value_for("Hours"),
            Some(7.5)
        );

        let existing_source = db
            .save_category(None, "Already Filed", None, "standard", "", false)
            .unwrap();
        let existing = db.add_item("Existing source member").unwrap();
        db.toggle_assignment(existing, existing_source).unwrap();
        db.save_primary_rule(existing_source, "", "numeric:Hours=9")
            .unwrap();
        db.apply_rules_to_all().unwrap();
        let existing_item = db
            .items(&all, "")
            .unwrap()
            .into_iter()
            .find(|item| item.id == existing)
            .unwrap();
        assert_eq!(existing_item.numeric_value_for("Hours"), None);
        db.execute_rules(&[existing]).unwrap();
        let existing_item = db
            .items(&all, "")
            .unwrap()
            .into_iter()
            .find(|item| item.id == existing)
            .unwrap();
        assert_eq!(existing_item.numeric_value_for("Hours"), Some(9.0));
        db.set_numeric_value(item, hours, Some(3.0)).unwrap();
        assert_eq!(
            db.items(&all, "").unwrap()[0].numeric_value_for("Hours"),
            Some(3.0)
        );
        assert_eq!(db.execute_rules(&[item]).unwrap().conflicts, 0);
        assert_eq!(
            db.items(&all, "").unwrap()[0].numeric_value_for("Hours"),
            Some(7.5)
        );
    }

    #[test]
    fn reports_include_numeric_footers_percentages_formatting_and_metadata() {
        let directory = tempdir().unwrap();
        let mut db = Database::open(&directory.path().join("calculation-report.agnd")).unwrap();
        let cost = db
            .save_category(None, "Cost", None, "numeric", "", false)
            .unwrap();
        for (text, value) in [("First", 25.0), ("Second", 75.0)] {
            let item = db.add_item(text).unwrap();
            db.set_numeric_value(item, cost, Some(value)).unwrap();
        }
        db.save_view_design(
            None,
            "Costs",
            "list",
            "",
            "manual",
            true,
            "",
            "item:50:Item,numeric[Cost]:20:Cost:sum:$:2:.:comma:parentheses:percent:right",
            "",
        )
        .unwrap();
        let markdown_path = directory.path().join("costs.md");
        db.export("markdown", &markdown_path, Some("Costs"))
            .unwrap();
        let markdown = fs::read_to_string(markdown_path).unwrap();
        assert!(markdown.contains("Cost %"));
        assert!(markdown.contains("25.00%"));
        assert!(markdown.contains("Total $100.00"));
        assert!(markdown.contains("Generated:"));
        let html_path = directory.path().join("costs.html");
        db.export("html", &html_path, Some("Costs")).unwrap();
        let html = fs::read_to_string(html_path).unwrap();
        assert!(html.contains("display:table-header-group"));
        assert!(html.contains("Total $100.00"));

        let mut settings = db.document_settings().unwrap();
        settings.report_headers = false;
        db.save_document_settings(&settings).unwrap();
        let plain_path = directory.path().join("plain.html");
        db.export("html", &plain_path, Some("Costs")).unwrap();
        let plain = fs::read_to_string(plain_path).unwrap();
        assert!(!plain.contains("<header>"));
        assert!(!plain.contains("<footer>"));
    }

    #[test]
    fn circular_conditions_are_saved_as_inspectable_conflicts() {
        let directory = tempdir().unwrap();
        let mut db = Database::open(&directory.path().join("conflict.agnd")).unwrap();
        let first = db
            .save_category(None, "First", None, "standard", "", false)
            .unwrap();
        let second = db
            .save_category(None, "Second", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(first, "not category=Second", "")
            .unwrap();
        db.save_primary_rule(second, "category=First", "").unwrap();
        let item = db.add_item("Oscillating item").unwrap();
        let conflicts = db.rule_conflicts().unwrap();
        assert_eq!(conflicts.len(), 1);
        assert_eq!(conflicts[0].item_id, item);
        assert!(conflicts[0].message.contains("did not converge"));
    }

    #[test]
    fn date_remove_export_and_discard_actions_cover_lotus_action_families() {
        let directory = tempdir().unwrap();
        let mut db = Database::open(&directory.path().join("action-families.agnd")).unwrap();
        let review = db
            .save_category(None, "Review Date", None, "date", "", false)
            .unwrap();
        let dated = db
            .save_category(None, "Needs Review", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(dated, "", "date:Review Date=tomorrow")
            .unwrap();
        let item = db.add_item("Review me").unwrap();
        db.toggle_assignment(item, dated).unwrap();
        let all = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.name == "All Items")
            .unwrap();
        assert!(
            db.items(&all, "").unwrap()[0]
                .date_value_for("Review Date")
                .is_some()
        );

        let target = db
            .save_category(None, "Temporary", None, "standard", "", false)
            .unwrap();
        let remover = db
            .save_category(None, "Remove Temporary", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(remover, "", "remove:Temporary")
            .unwrap();
        db.toggle_assignment(item, target).unwrap();
        db.toggle_assignment(item, remover).unwrap();
        assert!(!db.item_category_ids(item).unwrap().contains(&target));

        let export_source = db
            .save_category(None, "Archive", None, "standard", "", false)
            .unwrap();
        let export_path = directory.path().join("archive.jsonl");
        db.save_primary_rule(
            export_source,
            "",
            &format!("export:jsonl|{}", export_path.display()),
        )
        .unwrap();
        db.toggle_assignment(item, export_source).unwrap();
        assert!(
            fs::read_to_string(export_path)
                .unwrap()
                .contains("Review me")
        );

        let discard_source = db
            .save_category(None, "Discard", None, "standard", "", false)
            .unwrap();
        db.save_primary_rule(discard_source, "", "discard:true")
            .unwrap();
        db.toggle_assignment(item, discard_source).unwrap();
        let trash = db
            .views()
            .unwrap()
            .into_iter()
            .find(|view| view.name == "Trash")
            .unwrap();
        assert_eq!(db.items(&trash, "").unwrap().len(), 1);
        assert_eq!(
            review,
            db.items(&trash, "").unwrap()[0].values[0].category_id
        );
    }
}
