use std::collections::{HashMap, HashSet};

use anyhow::{Context, Result, bail};
use chrono::{Duration, Local, TimeZone};
use clap::ValueEnum;
use rusqlite::params;

use super::{
    Database, parse_action_spec, parse_bound_field, parse_columns_spec, parse_sections_spec,
};
use crate::{
    filter,
    macro_lang::{MacroRuntime, macro_name, parse_key_binding},
    model::MacroDef,
};

const PRESET_VERSION: &str = "2";

#[derive(Debug, Clone, Copy, PartialEq, Eq, ValueEnum)]
pub enum Preset {
    Accounts,
    Study,
    Planner,
    Recipes,
    Rides,
    People,
}

impl Preset {
    pub const fn name(self) -> &'static str {
        match self {
            Self::Accounts => "accounts",
            Self::Study => "study",
            Self::Planner => "planner",
            Self::Recipes => "recipes",
            Self::Rides => "rides",
            Self::People => "people",
        }
    }
}

struct PresetSpec {
    title: &'static str,
    description: &'static str,
    categories: Vec<CategorySpec>,
    views: Vec<ViewSpec>,
    rules: Vec<RuleSpec>,
    macros: Vec<MacroSpec>,
    items: Vec<ItemSpec>,
}

struct CategorySpec {
    name: &'static str,
    parent: Option<&'static str>,
    kind: &'static str,
    match_text: &'static str,
    exclusive: bool,
}

const fn category(
    name: &'static str,
    parent: Option<&'static str>,
    kind: &'static str,
    match_text: &'static str,
    exclusive: bool,
) -> CategorySpec {
    CategorySpec {
        name,
        parent,
        kind,
        match_text,
        exclusive,
    }
}

struct ViewSpec {
    name: &'static str,
    kind: &'static str,
    filter_value: &'static str,
    sort_key: &'static str,
    show_done: bool,
    filter_expr: &'static str,
    columns: &'static str,
    sections: &'static str,
}

impl ViewSpec {
    const fn new(name: &'static str) -> Self {
        Self {
            name,
            kind: "list",
            filter_value: "",
            sort_key: "manual",
            show_done: true,
            filter_expr: "",
            columns: "item:48:Items,categories:27:Categories,when:20:When,priority:5:P",
            sections: "",
        }
    }

    const fn category(mut self, name: &'static str) -> Self {
        self.kind = "category";
        self.filter_value = name;
        self
    }

    const fn kind(mut self, kind: &'static str, value: &'static str) -> Self {
        self.kind = kind;
        self.filter_value = value;
        self
    }

    const fn sort(mut self, sort_key: &'static str) -> Self {
        self.sort_key = sort_key;
        self
    }

    const fn hide_done(mut self) -> Self {
        self.show_done = false;
        self
    }

    const fn filter(mut self, expression: &'static str) -> Self {
        self.filter_expr = expression;
        self
    }

    const fn columns(mut self, columns: &'static str) -> Self {
        self.columns = columns;
        self
    }

    const fn sections(mut self, sections: &'static str) -> Self {
        self.sections = sections;
        self
    }
}

struct RuleSpec {
    category: &'static str,
    condition: &'static str,
    action: &'static str,
}

const fn rule(category: &'static str, condition: &'static str, action: &'static str) -> RuleSpec {
    RuleSpec {
        category,
        condition,
        action,
    }
}

struct MacroSpec {
    name: &'static str,
    source: &'static str,
    key_binding: &'static str,
}

const fn agenda_macro(name: &'static str, source: &'static str) -> MacroSpec {
    MacroSpec {
        name,
        source,
        key_binding: "",
    }
}

struct ItemSpec {
    text: &'static str,
    note: &'static str,
    priority: i64,
    when: Option<(i64, u32, u32)>,
    numeric_value: Option<f64>,
    recurrence: &'static str,
    done: bool,
    categories: &'static [&'static str],
}

impl ItemSpec {
    const fn new(
        text: &'static str,
        note: &'static str,
        categories: &'static [&'static str],
    ) -> Self {
        Self {
            text,
            note,
            priority: 3,
            when: None,
            numeric_value: None,
            recurrence: "",
            done: false,
            categories,
        }
    }

    const fn priority(mut self, priority: i64) -> Self {
        self.priority = priority;
        self
    }

    const fn when(mut self, days: i64, hour: u32, minute: u32) -> Self {
        self.when = Some((days, hour, minute));
        self
    }

    const fn value(mut self, value: f64) -> Self {
        self.numeric_value = Some(value);
        self
    }

    const fn recurring(mut self, recurrence: &'static str) -> Self {
        self.recurrence = recurrence;
        self
    }

    const fn done(mut self) -> Self {
        self.done = true;
        self
    }
}

impl Database {
    pub fn apply_preset(&mut self, preset: Preset) -> Result<()> {
        if !self.new_document {
            bail!("--preset can only initialize a new document; choose a path that does not exist");
        }
        let spec = preset_spec(preset);
        validate_spec(&spec)?;
        let now = Local::now().to_rfc3339();
        let tx = self.conn.transaction()?;
        tx.execute_batch(
            "DELETE FROM macro_variables;
             DELETE FROM macros;
             DELETE FROM category_rules;
             DELETE FROM view_sections;
             DELETE FROM view_columns;
             DELETE FROM views;
             DELETE FROM dependencies;
             DELETE FROM item_categories;
             DELETE FROM items;
             DELETE FROM categories;",
        )?;

        let mut category_ids = HashMap::new();
        for (order, category) in spec.categories.iter().enumerate() {
            let parent_id = category
                .parent
                .map(|parent| {
                    category_ids
                        .get(parent)
                        .copied()
                        .with_context(|| format!("preset category parent {parent:?} is missing"))
                })
                .transpose()?;
            tx.execute(
                "INSERT INTO categories(name,parent_id,kind,match_text,exclusive,sort_order)
                 VALUES(?1,?2,?3,?4,?5,?6)",
                params![
                    category.name,
                    parent_id,
                    category.kind,
                    category.match_text,
                    category.exclusive as i64,
                    order as i64
                ],
            )?;
            category_ids.insert(category.name, tx.last_insert_rowid());
        }

        for (order, view) in spec.views.iter().enumerate() {
            tx.execute(
                "INSERT INTO views(name,kind,filter_value,sort_key,show_done,sort_order,filter_expr)
                 VALUES(?1,?2,?3,?4,?5,?6,?7)",
                params![
                    view.name,
                    view.kind,
                    view.filter_value,
                    view.sort_key,
                    view.show_done as i64,
                    order as i64,
                    view.filter_expr
                ],
            )?;
            let view_id = tx.last_insert_rowid();
            for (column_order, column) in parse_columns_spec(view.columns)?.iter().enumerate() {
                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,
                        column.category.as_deref().map(|name| category_ids[name]),
                        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,
                        column_order as i64
                    ],
                )?;
            }
            for (section_order, section) in parse_sections_spec(view.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,
                        section_order as i64
                    ],
                )?;
            }
        }

        for (order, preset_rule) in spec.rules.iter().enumerate() {
            let category_id = category_ids[preset_rule.category];
            let (action_kind, action_value) = parse_action_spec(preset_rule.action)?;
            tx.execute(
                "INSERT INTO category_rules(category_id,condition_expr,action_kind,action_value,enabled,sort_order)
                 VALUES(?1,?2,?3,?4,1,?5)",
                params![
                    category_id,
                    preset_rule.condition,
                    action_kind.as_str(),
                    action_value,
                    order as i64
                ],
            )?;
        }

        for (order, preset_macro) in spec.macros.iter().enumerate() {
            tx.execute(
                "INSERT INTO macros(name,source,key_binding,sort_order) VALUES(?1,?2,?3,?4)",
                params![
                    preset_macro.name,
                    preset_macro.source,
                    preset_macro.key_binding,
                    order as i64
                ],
            )?;
        }

        for (order, item) in spec.items.iter().enumerate() {
            let when_at = item
                .when
                .map(|(days, hour, minute)| preset_date(days, hour, minute))
                .transpose()?;
            let done_at = item.done.then(|| now.clone());
            tx.execute(
                "INSERT INTO items(text,note,priority,when_at,done_at,recurrence,created_at,updated_at,sort_order)
                 VALUES(?1,?2,?3,?4,?5,?6,?7,?7,?8)",
                params![
                    item.text,
                    item.note,
                    item.priority,
                    when_at,
                    done_at,
                    item.recurrence,
                    now,
                    order as i64
                ],
            )?;
            let item_id = tx.last_insert_rowid();
            for category in item.categories {
                tx.execute(
                    "INSERT INTO item_categories(item_id,category_id,assignment) VALUES(?1,?2,'explicit')",
                    params![item_id, category_ids[category]],
                )?;
            }
            if let Some(value) = item.numeric_value {
                let category = item
                    .categories
                    .iter()
                    .find(|name| {
                        spec.categories
                            .iter()
                            .any(|category| category.name == **name && category.kind == "numeric")
                    })
                    .with_context(|| {
                        format!(
                            "preset item {:?} has a value but no numeric category",
                            item.text
                        )
                    })?;
                tx.execute(
                    "INSERT INTO item_values(item_id,category_id,numeric_value,assignment) VALUES(?1,?2,?3,'explicit')",
                    params![item_id,category_ids[*category],value],
                )?;
            }
        }

        for (key, value) in [
            ("title", spec.title),
            ("document.description", spec.description),
            ("preset.name", preset.name()),
            ("preset.version", PRESET_VERSION),
        ] {
            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.new_document = false;
        Ok(())
    }
}

fn preset_date(days: i64, hour: u32, minute: u32) -> Result<String> {
    let date = Local::now().date_naive() + Duration::days(days);
    let naive = date
        .and_hms_opt(hour, minute, 0)
        .context("preset contains an invalid time")?;
    Local
        .from_local_datetime(&naive)
        .earliest()
        .context("preset date does not exist in the local time zone")
        .map(|date_time| date_time.to_rfc3339())
}

fn validate_spec(spec: &PresetSpec) -> Result<()> {
    let mut categories = HashSet::new();
    for category in &spec.categories {
        if !categories.insert(category.name) {
            bail!("preset has duplicate category {:?}", category.name);
        }
        if category
            .parent
            .is_some_and(|parent| !categories.contains(parent))
        {
            bail!("preset category parent must appear before its children");
        }
        if !matches!(category.kind, "standard" | "date" | "numeric") {
            bail!("preset has unsupported category kind {:?}", category.kind);
        }
    }

    let mut view_names = HashSet::new();
    for view in &spec.views {
        if !view_names.insert(view.name) {
            bail!("preset has duplicate view {:?}", view.name);
        }
        filter::parse(view.filter_expr)?;
        for column in parse_columns_spec(view.columns)? {
            if let Some(category_name) = column.category {
                let category = spec
                    .categories
                    .iter()
                    .find(|category| category.name == category_name)
                    .with_context(|| {
                        format!("preset view column category {category_name:?} is missing")
                    })?;
                if category.kind != column.field {
                    bail!(
                        "preset view column category {:?} is {}, not {}",
                        category_name,
                        category.kind,
                        column.field
                    );
                }
            }
        }
        if let Some((field, Some(category_name))) = parse_bound_field(view.sort_key) {
            let category = spec
                .categories
                .iter()
                .find(|category| category.name == category_name)
                .with_context(|| format!("preset sort category {category_name:?} is missing"))?;
            if category.kind != field {
                bail!(
                    "preset sort category {category_name:?} is {}, not {field}",
                    category.kind
                );
            }
        }
        parse_sections_spec(view.sections)?;
    }

    for preset_rule in &spec.rules {
        if !categories.contains(preset_rule.category) {
            bail!("preset rule category {:?} is missing", preset_rule.category);
        }
        filter::parse(preset_rule.condition)?;
        parse_action_spec(preset_rule.action)?;
    }

    let definitions = spec
        .macros
        .iter()
        .enumerate()
        .map(|(index, preset_macro)| {
            let parsed_name = macro_name(preset_macro.source)?;
            if !parsed_name.eq_ignore_ascii_case(preset_macro.name) {
                bail!("preset macro name does not match its source");
            }
            if !preset_macro.key_binding.is_empty() {
                parse_key_binding(preset_macro.key_binding)?;
            }
            Ok(MacroDef {
                id: index as i64,
                name: preset_macro.name.into(),
                source: preset_macro.source.into(),
                key_binding: preset_macro.key_binding.into(),
            })
        })
        .collect::<Result<Vec<_>>>()?;
    let mut macro_names = HashSet::new();
    for definition in &definitions {
        if !macro_names.insert(definition.name.to_lowercase()) {
            bail!("preset has duplicate macro {:?}", definition.name);
        }
        MacroRuntime::new(&definition.name, &definitions, HashMap::new())?;
    }

    for item in &spec.items {
        if item.text.trim().is_empty() {
            bail!("preset item text cannot be empty");
        }
        if !(1..=5).contains(&item.priority) {
            bail!("preset item priority is outside 1 through 5");
        }
        for category in item.categories {
            if !categories.contains(category) {
                bail!("preset item category {category:?} is missing");
            }
        }
    }
    Ok(())
}

fn preset_spec(preset: Preset) -> PresetSpec {
    match preset {
        Preset::Accounts => accounts(),
        Preset::Study => study(),
        Preset::Planner => planner(),
        Preset::Recipes => recipes(),
        Preset::Rides => rides(),
        Preset::People => people(),
    }
}

fn common_tail() -> Vec<ViewSpec> {
    vec![
        ViewSpec::new("Recently Done")
            .kind("done", "14")
            .sort("done"),
        ViewSpec::new("Trash").kind("trash", "").sort("updated"),
    ]
}

fn accounts() -> PresetSpec {
    let mut views = vec![
        ViewSpec::new("All Items").sort("updated"),
        ViewSpec::new("Accounts")
            .category("Accounts")
            .columns("item:36:Account,note:42:Profile,categories:28:Filed under"),
        ViewSpec::new("Pipeline")
            .category("Pipeline")
            .sort("priority")
            .hide_done()
            .columns("item:42:Opportunity,categories:30:Stage,when:20:Next action,priority:5:P")
            .sections("Prospects|category=Prospect;Qualified|category=Qualified;Proposals|category=Proposal;Customers|category=Customer"),
        ViewSpec::new("Open Issues")
            .category("Issues")
            .sort("priority")
            .hide_done()
            .filter("not category=Resolved")
            .columns("item:48:Issue,categories:28:Account / state,when:20:Due,priority:5:P"),
        ViewSpec::new("Calls and Meetings")
            .category("Activities")
            .sort("when")
            .hide_done()
            .columns("item:48:Activity,categories:28:Account / type,when:20:When,priority:5:P"),
        ViewSpec::new("Expenses")
            .category("Expenses")
            .sort("updated")
            .columns("item:42:Expense,categories:28:Account,numeric[Expenses]:14:Amount:sum:$:2:.:comma:parentheses:percent:right,note:30:Details"),
        ViewSpec::new("Status Report")
            .category("Status Report")
            .sort("priority")
            .columns("item:48:Status item,categories:28:Account,when:20:When,priority:5:P")
            .sections("Urgent|priority<=2;Routine|priority>2"),
    ];
    views.extend(common_tail());
    PresetSpec {
        title: "Rogue Agenda — Account Desk",
        description: "A relationship workspace for accounts, opportunities, issues, activities, expenses, and status reporting.",
        categories: vec![
            category("Accounts", None, "standard", "account:", false),
            category(
                "Northstar Labs",
                Some("Accounts"),
                "standard",
                "northstar",
                false,
            ),
            category(
                "Harbor Books",
                Some("Accounts"),
                "standard",
                "harbor",
                false,
            ),
            category("Contacts", None, "standard", "contact:", false),
            category("Pipeline", None, "standard", "opportunity:", false),
            category("Prospect", Some("Pipeline"), "standard", "", true),
            category("Qualified", Some("Pipeline"), "standard", "", true),
            category("Proposal", Some("Pipeline"), "standard", "proposal", true),
            category("Customer", Some("Pipeline"), "standard", "", true),
            category("Activities", None, "standard", "", false),
            category("Calls", Some("Activities"), "standard", "call,phone", false),
            category(
                "Meetings",
                Some("Activities"),
                "standard",
                "meet,meeting",
                false,
            ),
            category(
                "Follow-ups",
                Some("Activities"),
                "standard",
                "follow up,follow-up",
                false,
            ),
            category("Issues", None, "standard", "issue:", false),
            category("Open Issue", Some("Issues"), "standard", "", true),
            category("Waiting", Some("Issues"), "standard", "waiting", true),
            category("Resolved", Some("Issues"), "standard", "resolved", true),
            category("Expenses", None, "numeric", "expense:", false),
            category("Status Report", None, "standard", "status:", false),
        ],
        views,
        rules: vec![
            rule("Status Report", "priority=1 and open", ""),
            rule("Follow-ups", "text~follow-up", ""),
        ],
        macros: vec![
            agenda_macro(
                "captureaccount",
                "{captureaccount}\n{inputtext;Company or account name;%name}\nn{type;Account: }{type;%name}{enter}",
            ),
            agenda_macro(
                "capturecall",
                "{capturecall}\n{inputtext;Who should you call?;%name}\nn{type;Call }{type;%name}{enter}",
            ),
        ],
        items: vec![
            ItemSpec::new(
                "Account: Northstar Labs",
                "Renewable-energy analytics team. Primary contact: Mina Chen. Current objective: replace the weekly spreadsheet handoff.",
                &["Northstar Labs", "Customer"],
            ),
            ItemSpec::new(
                "Contact: Mina Chen — Operations Director",
                "Northstar Labs. Prefers concise email follow-ups after calls.",
                &["Contacts", "Northstar Labs"],
            ),
            ItemSpec::new(
                "Opportunity: Harbor Books inventory workshop",
                "Discovery found duplicate stock counts across three locations.",
                &["Harbor Books", "Qualified"],
            )
            .priority(2)
            .when(3, 10, 0),
            ItemSpec::new(
                "Call Mina about pilot measurements",
                "Confirm which dashboard measures belong in the two-week pilot.",
                &["Northstar Labs", "Calls"],
            )
            .priority(1)
            .when(1, 14, 0),
            ItemSpec::new(
                "Issue: Harbor export omits back-ordered titles",
                "Waiting for a sanitized export that reproduces the missing rows.",
                &["Harbor Books", "Waiting"],
            )
            .priority(1)
            .when(2, 9, 30),
            ItemSpec::new(
                "Expense: train to Northstar workshop",
                "Return fare for the discovery workshop.",
                &["Northstar Labs", "Expenses"],
            )
            .value(46.80)
            .done(),
            ItemSpec::new(
                "Status: Northstar pilot scope approved",
                "The pilot will cover intake latency, exception volume, and operator time.",
                &["Northstar Labs", "Status Report"],
            )
            .priority(2),
        ],
    }
}

fn study() -> PresetSpec {
    let mut views = vec![
        ViewSpec::new("All Items").sort("updated"),
        ViewSpec::new("Due Review")
            .sort("when")
            .hide_done()
            .filter("category=Cards and (overdue or due=7d)")
            .columns("item:55:Question,categories:28:Deck / stage,when:20:Review,numeric[Cycle]:8:Cycle:none::0:.:none:minus:value:right"),
        ViewSpec::new("New Cards")
            .category("New")
            .sort("manual")
            .hide_done()
            .columns("item:60:Question,categories:30:Deck,note:36:Answer"),
        ViewSpec::new("All Cards")
            .category("Cards")
            .sort("when")
            .columns("item:55:Question,categories:28:Deck / stage,when:20:Review,numeric[Cycle]:8:Cycle:none::0:.:none:minus:value:right"),
        ViewSpec::new("Deck Browser")
            .category("Cards")
            .columns("item:52:Question,note:42:Answer,categories:28:Deck")
            .sections("Computing|category=Computing;Languages|category=Languages;History|category=History"),
        ViewSpec::new("Datebook").kind("datebook", "30").sort("when"),
    ];
    views.extend(common_tail());
    PresetSpec {
        title: "Rogue Agenda — Study Deck",
        description: "A note-backed flashcard workspace with decks, learning stages, review dates, and cycle values.",
        categories: vec![
            category("Cards", None, "standard", "q:", false),
            category("Decks", None, "standard", "", false),
            category("Computing", Some("Decks"), "standard", "", false),
            category("Languages", Some("Decks"), "standard", "", false),
            category("History", Some("Decks"), "standard", "", false),
            category("Learning Stage", None, "standard", "", false),
            category("New", Some("Learning Stage"), "standard", "", true),
            category("Learning", Some("Learning Stage"), "standard", "", true),
            category("Review", Some("Learning Stage"), "standard", "", true),
            category("Review Date", None, "date", "", false),
            category("Cycle", None, "numeric", "", false),
        ],
        views,
        rules: vec![rule("Cards", "text~q:", "")],
        macros: vec![
            agenda_macro(
                "capturecard",
                "{capturecard}\n{inputtext;Question;%question}\n{inputtext;Answer;%answer}\nn{type;Q: }{type;%question}{enter}{F5}{type;%answer}{F5}",
            ),
            agenda_macro(
                "revealanswer",
                "{revealanswer}\n{largebox;Question;#HIGHLIGHT_VALUE}\n{F5}",
            ),
        ],
        items: vec![
            ItemSpec::new(
                "Q: What does ownership prevent in Rust?",
                "It prevents multiple parts of a program from mutating or freeing the same value without explicit coordination.",
                &["Cards", "Computing", "Review", "Cycle"],
            )
            .when(0, 9, 0)
            .value(3.0),
            ItemSpec::new(
                "Q: Which SQL clause filters grouped results?",
                "HAVING filters after grouping; WHERE filters rows before grouping.",
                &["Cards", "Computing", "Learning", "Cycle"],
            )
            .when(1, 9, 0)
            .value(2.0),
            ItemSpec::new(
                "Q: What does ‘bonjour’ mean?",
                "Hello or good day in French.",
                &["Cards", "Languages", "New", "Cycle"],
            )
            .value(0.0),
            ItemSpec::new(
                "Q: In which year did the first transatlantic telegraph cable succeed?",
                "A durable transatlantic cable entered service in 1866.",
                &["Cards", "History", "Review", "Cycle"],
            )
            .when(5, 9, 0)
            .value(5.0),
            ItemSpec::new(
                "Q: What is spaced repetition trying to optimize?",
                "Review near the point of forgetting so that durable recall grows with less total study time.",
                &["Cards", "New", "Cycle"],
            )
            .value(0.0),
        ],
    }
}

fn planner() -> PresetSpec {
    let mut views = vec![
        ViewSpec::new("All Items").sort("manual"),
        ViewSpec::new("Today")
            .kind("upcoming", "1")
            .sort("when")
            .hide_done(),
        ViewSpec::new("This Week")
            .kind("upcoming", "7")
            .sort("when")
            .hide_done()
            .columns(
                "item:50:Commitment,categories:28:Project / context,when:20:When,priority:5:P",
            ),
        ViewSpec::new("Next Actions")
            .category("Next Actions")
            .sort("priority")
            .hide_done()
            .columns("item:52:Action,categories:30:Project / context,when:20:When,priority:5:P"),
        ViewSpec::new("Projects")
            .category("Projects")
            .sort("priority")
            .hide_done(),
        ViewSpec::new("Calls and Meetings")
            .category("Scheduled Activity")
            .sort("when")
            .hide_done(),
        ViewSpec::new("Ideas")
            .category("Ideas")
            .columns("item:58:Idea,note:48:Notes,categories:25:Area"),
        ViewSpec::new("Datebook")
            .kind("datebook", "30")
            .sort("when"),
    ];
    views.extend(common_tail());
    PresetSpec {
        title: "Rogue Agenda — Activities Planner",
        description: "An extensible daily planner for projects, next actions, scheduled activities, contexts, and ideas.",
        categories: vec![
            category(
                "Next Actions",
                None,
                "standard",
                "todo,call,send,buy,review",
                false,
            ),
            category("Scheduled Activity", None, "standard", "", false),
            category(
                "Calls",
                Some("Scheduled Activity"),
                "standard",
                "call,phone",
                false,
            ),
            category(
                "Meetings",
                Some("Scheduled Activity"),
                "standard",
                "meet,meeting,appointment",
                false,
            ),
            category(
                "Errands",
                Some("Next Actions"),
                "standard",
                "buy,pick up,errand",
                false,
            ),
            category("Projects", None, "standard", "project:", false),
            category(
                "Website Refresh",
                Some("Projects"),
                "standard",
                "website",
                false,
            ),
            category("Studio Move", Some("Projects"), "standard", "studio", false),
            category("Areas", None, "standard", "", false),
            category("Work", Some("Areas"), "standard", "", false),
            category("Home", Some("Areas"), "standard", "", false),
            category("Personal", Some("Areas"), "standard", "", false),
            category("Contexts", None, "standard", "", false),
            category(
                "At Computer",
                Some("Contexts"),
                "standard",
                "email,website,draft",
                false,
            ),
            category(
                "On Phone",
                Some("Contexts"),
                "standard",
                "call,phone",
                false,
            ),
            category(
                "Out and About",
                Some("Contexts"),
                "standard",
                "buy,pick up",
                false,
            ),
            category("Ideas", None, "standard", "idea:,maybe", false),
            category("When", None, "date", "", false),
            category("Priority", None, "standard", "", false),
        ],
        views,
        rules: vec![
            rule("Next Actions", "priority<=2 and open", ""),
            rule(
                "Scheduled Activity",
                "dated and (category=Calls or category=Meetings)",
                "",
            ),
        ],
        macros: vec![
            agenda_macro(
                "quickcapture",
                "{quickcapture}\n{inputtext;Capture a thought or action;%item}\nn{type;%item}{enter}",
            ),
            agenda_macro(
                "captureidea",
                "{captureidea}\n{inputtext;What is the idea?;%idea}\nn{type;Idea: }{type;%idea}{enter}",
            ),
        ],
        items: vec![
            ItemSpec::new(
                "Project: Website refresh",
                "Outcome: a fast, accessible portfolio with a simpler inquiry path.",
                &["Website Refresh", "Work"],
            )
            .priority(2),
            ItemSpec::new(
                "Draft website content inventory",
                "List every current page and decide keep, rewrite, merge, or remove.",
                &["Website Refresh", "Next Actions", "At Computer"],
            )
            .priority(1)
            .when(1, 9, 0),
            ItemSpec::new(
                "Call movers for a revised studio estimate",
                "Ask whether packing crates and weekend delivery are included.",
                &["Studio Move", "Calls", "On Phone"],
            )
            .priority(2)
            .when(2, 11, 0),
            ItemSpec::new(
                "Meet Jo for the weekly project review",
                "Review current risks and choose the next visible milestone.",
                &["Website Refresh", "Meetings", "Work"],
            )
            .priority(1)
            .when(3, 15, 0)
            .recurring("weekly"),
            ItemSpec::new(
                "Buy archival boxes for the studio move",
                "Measure the largest flat work before choosing box sizes.",
                &["Studio Move", "Errands", "Out and About"],
            )
            .when(4, 17, 0),
            ItemSpec::new(
                "Idea: publish a short monthly field note",
                "A single observation, one image, and a link to related work.",
                &["Ideas", "Work"],
            ),
            ItemSpec::new(
                "Send the accessibility checklist to Jo",
                "Checklist sent with the content inventory template.",
                &["Website Refresh", "Next Actions", "At Computer"],
            )
            .done(),
        ],
    }
}

fn recipes() -> PresetSpec {
    let mut views = vec![
        ViewSpec::new("Recipe Box")
            .category("Recipes")
            .columns("item:42:Recipe,categories:35:Course / cuisine,note:55:Ingredients and method,numeric[Servings]:10:Serves:none::0:.:none:minus:value:right"),
        ViewSpec::new("Favorites")
            .category("Favorites")
            .columns("item:45:Favorite,categories:35:Tags,note:58:Recipe,numeric[Servings]:10:Serves:none::0:.:none:minus:value:right"),
        ViewSpec::new("Main Dishes")
            .category("Main Dishes")
            .columns("item:45:Main dish,categories:35:Tags,note:58:Recipe,numeric[Servings]:10:Serves:none::0:.:none:minus:value:right"),
        ViewSpec::new("Quick Meals")
            .category("Quick")
            .columns("item:45:Recipe,categories:35:Tags,note:58:Method,numeric[Servings]:10:Serves:none::0:.:none:minus:value:right"),
        ViewSpec::new("Vegetarian")
            .category("Vegetarian")
            .columns("item:45:Recipe,categories:35:Tags,note:58:Recipe,numeric[Servings]:10:Serves:none::0:.:none:minus:value:right"),
        ViewSpec::new("By Course")
            .category("Recipes")
            .columns("item:45:Recipe,categories:35:Tags,numeric[Servings]:10:Serves:none::0:.:none:minus:value:right")
            .sections("Breakfast|category=Breakfast;Main dishes|category='Main Dishes';Desserts|category=Desserts;Drinks|category=Drinks"),
        ViewSpec::new("All Items").sort("updated"),
    ];
    views.extend(common_tail());
    PresetSpec {
        title: "Rogue Agenda — Recipe Box",
        description: "A browseable cooking notebook organized by course, cuisine, dietary tags, ingredients, and favorites.",
        categories: vec![
            category("Recipes", None, "standard", "recipe:", false),
            category("Course", None, "standard", "", false),
            category("Breakfast", Some("Course"), "standard", "", true),
            category("Main Dishes", Some("Course"), "standard", "", true),
            category("Desserts", Some("Course"), "standard", "", true),
            category("Drinks", Some("Course"), "standard", "", true),
            category("Cuisine", None, "standard", "", false),
            category("Mediterranean", Some("Cuisine"), "standard", "", false),
            category("East Asian", Some("Cuisine"), "standard", "", false),
            category("Northern European", Some("Cuisine"), "standard", "", false),
            category("Diet", None, "standard", "", false),
            category("Vegetarian", Some("Diet"), "standard", "", false),
            category("Vegan", Some("Diet"), "standard", "", false),
            category("Gluten Free", Some("Diet"), "standard", "", false),
            category("Quick", None, "standard", "quick,15 minute,20 minute", false),
            category("Favorites", None, "standard", "favorite", false),
            category("Ingredients", None, "standard", "", false),
            category("Beans", Some("Ingredients"), "standard", "beans,chickpeas", false),
            category("Rice", Some("Ingredients"), "standard", "rice", false),
            category("Fruit", Some("Ingredients"), "standard", "apple,pear,lemon", false),
            category("Servings", None, "numeric", "", false),
        ],
        views,
        rules: vec![
            rule("Recipes", "text~recipe:", ""),
            rule("Quick", "note~minutes", ""),
        ],
        macros: vec![
            agenda_macro(
                "newrecipe",
                "{newrecipe}\n{inputtext;Recipe name;%name}\nn{type;Recipe: }{type;%name}{enter}{F5}{type;Ingredients:}{enter;2}{type;Method:}{enter;2}{type;Notes:}{F5}",
            ),
            agenda_macro(
                "cookingnote",
                "{cookingnote}\n{inputtext;What did you learn while cooking?;%note}\nn{type;Kitchen note: }{type;%note}{enter}",
            ),
        ],
        items: vec![
            ItemSpec::new(
                "Recipe: Lemon chickpea skillet",
                "Ingredients: chickpeas, spinach, lemon, garlic, olive oil, cumin.\n\nMethod: Warm garlic and cumin in oil, fold in chickpeas and spinach, then finish with lemon. About 20 minutes.",
                &["Recipes", "Main Dishes", "Mediterranean", "Vegetarian", "Vegan", "Gluten Free", "Beans", "Fruit", "Quick", "Favorites", "Servings"],
            )
            .value(3.0),
            ItemSpec::new(
                "Recipe: Ginger mushroom rice bowl",
                "Ingredients: cooked rice, mushrooms, ginger, scallions, tamari, sesame oil.\n\nMethod: Brown mushrooms well, add ginger and tamari, and serve over hot rice. About 20 minutes.",
                &["Recipes", "Main Dishes", "East Asian", "Vegetarian", "Vegan", "Rice", "Quick", "Servings"],
            )
            .value(2.0),
            ItemSpec::new(
                "Recipe: Apple oat breakfast jars",
                "Ingredients: rolled oats, grated apple, yogurt or oat milk, cinnamon, toasted seeds.\n\nMethod: Mix in jars and chill overnight.",
                &["Recipes", "Breakfast", "Northern European", "Vegetarian", "Fruit", "Servings"],
            )
            .value(4.0),
            ItemSpec::new(
                "Recipe: Pear and cocoa cups",
                "Ingredients: ripe pear, cocoa, thick yogurt, toasted hazelnuts.\n\nMethod: Layer diced pear with cocoa yogurt and finish with nuts.",
                &["Recipes", "Desserts", "Vegetarian", "Fruit", "Quick", "Servings"],
            )
            .value(2.0),
            ItemSpec::new(
                "Recipe: Rosemary citrus cooler",
                "Ingredients: orange, lemon, rosemary, sparkling water, ice.\n\nMethod: Muddle one rosemary sprig with citrus juice, strain, and top with sparkling water.",
                &["Recipes", "Drinks", "Vegan", "Gluten Free", "Fruit", "Quick", "Servings"],
            )
            .value(4.0),
        ],
    }
}

fn rides() -> PresetSpec {
    let mut views = vec![
        ViewSpec::new("Ride Log")
            .category("Rides")
            .sort("when")
            .columns("item:45:Ride,categories:35:Bike / type / weather,when:20:Date,numeric[Distance]:14:Distance km:sum::1:.:comma:minus:percent:right,note:35:Notes"),
        ViewSpec::new("Training")
            .category("Training")
            .sort("when")
            .columns("item:48:Session,categories:32:Bike / route,when:20:Date,numeric[Distance]:14:Distance km:sum::1:.:comma:minus:percent:right"),
        ViewSpec::new("Long Rides")
            .category("Rides")
            .filter("value>=50")
            .sort("updated")
            .columns("item:48:Ride,categories:35:Bike / route,when:20:Date,numeric[Distance]:14:Distance km:avg::1:.:comma:minus:value:right"),
        ViewSpec::new("By Bicycle")
            .category("Rides")
            .columns("item:45:Ride,when:20:Date,numeric[Distance]:14:Distance km:sum::1:.:comma:minus:percent:right,categories:35:Type / weather")
            .sections("Road bike|category='Road Bike';Touring bike|category='Touring Bike';City bike|category='City Bike'"),
        ViewSpec::new("Maintenance")
            .category("Maintenance")
            .sort("when")
            .hide_done()
            .columns("item:52:Maintenance,categories:30:Bicycle,when:20:Due,priority:5:P,note:40:Details"),
        ViewSpec::new("Datebook").kind("datebook", "30").sort("when"),
        ViewSpec::new("All Items").sort("updated"),
    ];
    views.extend(common_tail());
    PresetSpec {
        title: "Rogue Agenda — Bicycle Log",
        description: "A cycling log for rides, bicycles, routes, conditions, distance totals, training, and maintenance.",
        categories: vec![
            category("Rides", None, "standard", "ride:", false),
            category("Ride Type", None, "standard", "", false),
            category("Commute", Some("Ride Type"), "standard", "commute", false),
            category(
                "Training",
                Some("Ride Type"),
                "standard",
                "training,interval",
                false,
            ),
            category(
                "Leisure",
                Some("Ride Type"),
                "standard",
                "leisure,tour",
                false,
            ),
            category("Bicycles", None, "standard", "", false),
            category(
                "Road Bike",
                Some("Bicycles"),
                "standard",
                "road bike",
                false,
            ),
            category(
                "Touring Bike",
                Some("Bicycles"),
                "standard",
                "touring bike",
                false,
            ),
            category(
                "City Bike",
                Some("Bicycles"),
                "standard",
                "city bike",
                false,
            ),
            category("Weather", None, "standard", "", false),
            category("Clear", Some("Weather"), "standard", "sunny,clear", false),
            category(
                "Cloudy",
                Some("Weather"),
                "standard",
                "cloudy,overcast",
                false,
            ),
            category("Rain", Some("Weather"), "standard", "rain,wet", false),
            category("Routes", None, "standard", "route:", false),
            category(
                "River Loop",
                Some("Routes"),
                "standard",
                "river loop",
                false,
            ),
            category(
                "North Ridge",
                Some("Routes"),
                "standard",
                "north ridge",
                false,
            ),
            category(
                "Maintenance",
                None,
                "standard",
                "service,replace,lubricate,maintenance",
                false,
            ),
            category("Distance", None, "numeric", "", false),
        ],
        views,
        rules: vec![
            rule("Rides", "text~ride:", ""),
            rule("Maintenance", "text~service or text~replace", ""),
        ],
        macros: vec![
            agenda_macro(
                "logride",
                "{logride}\n{inputtext;Ride summary;%ride}\nn{type;Ride: }{type;%ride}{enter}",
            ),
            agenda_macro(
                "servicereminder",
                "{servicereminder}\n{inputtext;What needs service?;%service}\nn{type;Service }{type;%service}{enter}",
            ),
        ],
        items: vec![
            ItemSpec::new(
                "Ride: sunrise River Loop training",
                "Steady endurance pace. Light headwind on the return leg; average speed 24.1 km/h.",
                &[
                    "Rides",
                    "Training",
                    "Road Bike",
                    "River Loop",
                    "Clear",
                    "Distance",
                ],
            )
            .when(-5, 6, 30)
            .value(42.6)
            .done(),
            ItemSpec::new(
                "Ride: wet commute to the library",
                "Low traffic and fresh brake pads worked well in the rain.",
                &["Rides", "Commute", "City Bike", "Rain", "Distance"],
            )
            .when(-3, 8, 15)
            .value(11.8)
            .done(),
            ItemSpec::new(
                "Ride: North Ridge café tour",
                "Long rolling route with a steep final climb; carry an extra bottle next time.",
                &[
                    "Rides",
                    "Leisure",
                    "Touring Bike",
                    "North Ridge",
                    "Cloudy",
                    "Distance",
                ],
            )
            .when(-1, 9, 0)
            .value(67.4)
            .done(),
            ItemSpec::new(
                "Ride: short recovery spin on the River Loop",
                "Keep the effort conversational and skip the hill repeats.",
                &[
                    "Rides",
                    "Training",
                    "Road Bike",
                    "River Loop",
                    "Clear",
                    "Distance",
                ],
            )
            .when(2, 17, 30)
            .value(24.0),
            ItemSpec::new(
                "Replace touring bike chain",
                "Chain checker is past 0.75; inspect the cassette at the same time.",
                &["Maintenance", "Touring Bike"],
            )
            .priority(1)
            .when(4, 10, 0),
            ItemSpec::new(
                "Service city bike lights",
                "Clean charging contacts and test the rear mount.",
                &["Maintenance", "City Bike"],
            )
            .priority(2)
            .when(7, 18, 0),
        ],
    }
}

fn people() -> PresetSpec {
    let mut views = vec![
        ViewSpec::new("People Directory")
            .category("People Records")
            .columns("item:40:Person,categories:36:Team / status,note:58:Profile,when:20:Next review"),
        ViewSpec::new("Upcoming Reviews")
            .category("Reviews")
            .sort("when")
            .hide_done()
            .columns("item:48:Review,categories:32:Team / person,when:20:When,priority:5:P,note:42:Preparation"),
        ViewSpec::new("Goals")
            .category("Goals")
            .sort("priority")
            .hide_done()
            .columns("item:52:Goal,categories:34:Person / team,when:20:Target,numeric[Progress]:12:Progress:none:%:0:.:none:minus:value:right,priority:5:P,note:40:Evidence"),
        ViewSpec::new("Development")
            .category("Development")
            .sort("when")
            .hide_done()
            .columns("item:50:Development action,categories:34:Person / team,when:20:Target,note:45:Notes"),
        ViewSpec::new("Candidates")
            .category("Candidate")
            .sort("updated")
            .hide_done()
            .columns("item:42:Candidate,categories:34:Role / stage,note:58:Interview notes,priority:5:P"),
        ViewSpec::new("Achievements")
            .category("Achievements")
            .sort("updated")
            .columns("item:50:Achievement,categories:34:Person / team,when:20:Date,note:45:Evidence"),
        ViewSpec::new("All Items").sort("updated"),
    ];
    views.extend(common_tail());
    PresetSpec {
        title: "Rogue Agenda — People Manager",
        description: "A lightweight people workspace for profiles, teams, goals, reviews, development, achievements, and candidates.",
        categories: vec![
            category("People Records", None, "standard", "person:", false),
            category("Teams", None, "standard", "", false),
            category("Product", Some("Teams"), "standard", "", false),
            category("Operations", Some("Teams"), "standard", "", false),
            category("Community", Some("Teams"), "standard", "", false),
            category("People Status", None, "standard", "", false),
            category("Active", Some("People Status"), "standard", "", true),
            category("Candidate", Some("People Status"), "standard", "candidate:", true),
            category("Alumni", Some("People Status"), "standard", "", true),
            category("Reviews", None, "standard", "review:", false),
            category("Goals", None, "standard", "goal:", false),
            category("Development", None, "standard", "development:,learn,training", false),
            category("Achievements", None, "standard", "achievement:", false),
            category("Hiring", None, "standard", "interview,candidate,role:", false),
            category("Review Date", None, "date", "", false),
            category("Progress", None, "numeric", "", false),
        ],
        views,
        rules: vec![
            rule("People Records", "text~person:", ""),
            rule("Reviews", "text~review:", ""),
            rule("Goals", "text~goal:", ""),
        ],
        macros: vec![
            agenda_macro(
                "addperson",
                "{addperson}\n{inputtext;Person name;%name}\n{inputtext;Role or focus;%role}\nn{type;Person: }{type;%name}{type; — }{type;%role}{enter}",
            ),
            agenda_macro(
                "addgoal",
                "{addgoal}\n{inputtext;Describe the goal;%goal}\nn{type;Goal: }{type;%goal}{enter}",
            ),
        ],
        items: vec![
            ItemSpec::new(
                "Person: Amina Okafor — Product designer",
                "Focus: research synthesis and accessible interaction patterns. Prefers written context before design reviews.",
                &["People Records", "Product", "Active"],
            ),
            ItemSpec::new(
                "Person: Theo Martin — Operations coordinator",
                "Focus: dependable handoffs and lightweight process documentation.",
                &["People Records", "Operations", "Active"],
            ),
            ItemSpec::new(
                "Goal: Amina leads two customer research debriefs",
                "Evidence: agendas prepared, insights circulated, and follow-up decisions recorded.",
                &["Goals", "Product", "Progress"],
            )
            .priority(2)
            .when(14, 16, 0)
            .value(50.0),
            ItemSpec::new(
                "Review: quarterly conversation with Theo",
                "Prepare examples of strong handoffs and identify one process that can be retired.",
                &["Reviews", "Operations"],
            )
            .priority(1)
            .when(5, 10, 30),
            ItemSpec::new(
                "Development: Amina shadows an accessibility audit",
                "Pair with the audit facilitator and write a one-page reflection afterward.",
                &["Development", "Product"],
            )
            .when(9, 13, 0),
            ItemSpec::new(
                "Achievement: Theo simplified the intake checklist",
                "The revised checklist removed four duplicate questions and reduced incomplete requests.",
                &["Achievements", "Operations"],
            )
            .when(-4, 9, 0)
            .done(),
            ItemSpec::new(
                "Candidate: Jules Rivera — community producer",
                "Strong facilitation examples. Next interview should explore editorial planning and conflict resolution.",
                &["Candidate", "Hiring", "Community"],
            )
            .priority(2)
            .when(3, 14, 30),
        ],
    }
}

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

    #[test]
    fn every_preset_builds_a_rich_valid_workspace() {
        for preset in [
            Preset::Accounts,
            Preset::Study,
            Preset::Planner,
            Preset::Recipes,
            Preset::Rides,
            Preset::People,
        ] {
            let directory = tempdir().unwrap();
            let path = directory.path().join(format!("{}.agnd", preset.name()));
            let mut database = Database::open(&path).unwrap();
            database.apply_preset(preset).unwrap();

            let item_count: i64 = database
                .conn
                .query_row("SELECT COUNT(*) FROM items", [], |row| row.get(0))
                .unwrap();
            assert!(item_count >= 5, "{} has too few items", preset.name());
            assert!(database.categories().unwrap().len() >= 10);
            assert!(database.views().unwrap().len() >= 7);
            assert!(database.macros().unwrap().len() >= 2);
            let unbound_numeric_columns: i64 = database
                .conn
                .query_row(
                    "SELECT COUNT(*) FROM view_columns WHERE field='numeric' AND category_id IS NULL",
                    [],
                    |row| row.get(0),
                )
                .unwrap();
            assert_eq!(
                unbound_numeric_columns,
                0,
                "{} has an unbound numeric column",
                preset.name()
            );
            let misplaced_values: i64 = database
                .conn
                .query_row(
                    "SELECT COUNT(*) FROM item_values v JOIN categories c ON c.id=v.category_id WHERE v.numeric_value IS NOT NULL AND c.kind<>'numeric'",
                    [],
                    |row| row.get(0),
                )
                .unwrap();
            assert_eq!(
                misplaced_values,
                0,
                "{} has a value outside a numeric category",
                preset.name()
            );
            assert_eq!(
                database
                    .conn
                    .query_row(
                        "SELECT value FROM meta WHERE key='preset.name'",
                        [],
                        |row| { row.get::<_, String>(0) }
                    )
                    .unwrap(),
                preset.name()
            );
        }
    }

    #[test]
    fn presets_refuse_to_replace_an_existing_document() {
        let directory = tempdir().unwrap();
        let path = directory.path().join("existing.agnd");
        Database::open(&path).unwrap();
        let mut reopened = Database::open(&path).unwrap();
        let error = reopened.apply_preset(Preset::Planner).unwrap_err();
        assert!(error.to_string().contains("only initialize a new document"));
    }

    #[test]
    fn a_preset_cannot_be_applied_twice_in_one_session() {
        let directory = tempdir().unwrap();
        let path = directory.path().join("twice.agnd");
        let mut database = Database::open(&path).unwrap();
        database.apply_preset(Preset::Study).unwrap();
        assert!(database.apply_preset(Preset::Study).is_err());
    }
}
