pxh 0.9.25

use std::{
    env,
    ffi::{OsStr, OsString},
    fs,
    fs::{File, OpenOptions},
    io,
    io::{Read, Write},
    os::unix::ffi::{OsStrExt, OsStringExt},
    path::{Path, PathBuf},
    str,
    time::Duration,
};

use bstr::{BString, ByteSlice};
use chrono::prelude::{Local, TimeZone};
use clap::{CommandFactory, Parser, Subcommand, ValueEnum};
use regex::bytes::Regex;
use rusqlite::{Connection, OpenFlags, Result};
use tempfile::NamedTempFile;

mod doctor;

#[derive(Clone, Debug, ValueEnum)]
enum ConfidenceLevel {
    Critical,
    High,
    Low,
    All,
}

impl ConfidenceLevel {
    fn as_str(&self) -> &str {
        match self {
            Self::Critical => "critical",
            Self::High => "high",
            Self::Low => "low",
            Self::All => "all",
        }
    }
}

// Type alias for secret pattern matching results
type SecretPatterns = (Vec<(&'static str, &'static str)>, regex::bytes::RegexSet);

/// Options passed in the v2 sync protocol
#[derive(Debug, Default, serde::Serialize, serde::Deserialize)]
struct SyncOptions {
    /// Pattern to scrub on remote (for remote scrub mode)
    #[serde(skip_serializing_if = "Option::is_none")]
    scrub: Option<String>,
    /// Use secret detection for scrub
    #[serde(skip_serializing_if = "Option::is_none")]
    scrub_scan: Option<bool>,
    /// Confidence level for secret detection
    #[serde(skip_serializing_if = "Option::is_none")]
    scrub_confidence: Option<String>,
    /// Dry-run mode for scrub
    #[serde(skip_serializing_if = "Option::is_none")]
    scrub_dry_run: Option<bool>,
    /// Disable secret filtering during sync import
    #[serde(skip_serializing_if = "Option::is_none")]
    no_secret_filter: Option<bool>,
}

// Regex compilation size limits for secret pattern matching
const REGEX_SIZE_LIMIT_DEFAULT: usize = 50 * 1024 * 1024; // 50MB
const REGEX_SIZE_LIMIT_ALL: usize = 100 * 1024 * 1024; // 100MB for combined patterns

// Maximum database size accepted during sync (1 GB)
const MAX_SYNC_DB_SIZE: u64 = 1024 * 1024 * 1024;

fn version_string() -> &'static str {
    use std::sync::LazyLock;
    static VERSION: LazyLock<String> = LazyLock::new(|| {
        format!(
            "{} (SQLite {}, schema v{})",
            env!("CARGO_PKG_VERSION"),
            rusqlite::version(),
            pxh::CURRENT_SCHEMA_VERSION
        )
    });
    &VERSION
}

#[derive(Parser, Debug)]
#[clap(author, version = version_string(), about, long_about = None)]
struct PxhArgs {
    #[clap(
        long,
        env = "PXH_DB_PATH",
        help = "path to the history database (default: ~/.local/share/pxh/pxh.db)"
    )]
    db: Option<PathBuf>,

    #[clap(subcommand)]
    command: Option<Commands>,
}

#[derive(Subcommand, Debug)]
enum Commands {
    #[clap(visible_alias = "s", about = "search for and display history entries")]
    Show(ShowCommand),
    #[clap(visible_alias = "r", about = "interactive history search (Ctrl-R replacement)")]
    Recall(pxh::recall::RecallCommand),
    #[clap(about = "install pxh helpers by modifying your shell rc file")]
    Install(InstallCommand),
    #[clap(about = "import history entries from your existing shell history or from an export")]
    Import(ImportCommand),
    #[clap(about = "export full history as JSON")]
    Export(ExportCommand),
    #[clap(about = "synchronize to and from a directory of other pxh history databases")]
    Sync(SyncCommand),
    #[clap(about = "scrub (remove) history entries matching the prompted-for string")]
    Scrub(ScrubCommand),
    #[clap(about = "(internal) invoked by the shell to insert a history entry")]
    Insert(InsertCommand),
    #[clap(about = "(internal) seal the previous inserted command to mark status, timing, etc")]
    Seal(SealCommand),
    #[clap(about = "(internal) shell configuration suitable for `source`'ing to enable pxh")]
    ShellConfig(ShellConfigCommand),
    #[clap(
        about = "(internal) print the most recent command matching a prefix for zsh-autosuggestions"
    )]
    Autosuggest(AutosuggestCommand),
    #[clap(
        about = "perform ANALYZE and VACUUM on the specified database files to optimize performance and reclaim space"
    )]
    Maintenance(MaintenanceCommand),
    #[clap(about = "scan history for potential secrets and sensitive data")]
    Scan(ScanCommand),
    #[clap(about = "generate shell completions")]
    Completions(CompletionsCommand),
    #[clap(about = "show history statistics")]
    Stats(StatsCommand),
    #[clap(visible_alias = "cfg", about = "edit or initialize configuration")]
    Config(ConfigCommand),
    #[clap(about = "diagnose common issues and produce diagnostic reports")]
    Doctor(doctor::DoctorCommand),
}

#[derive(Parser, Debug)]
struct InstallCommand {
    #[clap(help = "Shell to install helpers into (bash or zsh)")]
    shellname: String,
}

#[derive(Parser, Debug)]
struct ShowCommand {
    #[clap(short = 'i', long, help = "Perform case-insensitive matching", default_value_t = false)]
    ignore_case: bool,
    #[clap(
        short,
        long,
        default_value_t = 50,
        help = "Display at most this many entries; 0 for unlimited"
    )]
    limit: usize,
    #[clap(short, long, help = "Display extra fields in the output")]
    verbose: bool,
    #[clap(long, help = "Suppress headers")]
    suppress_headers: bool,
    #[clap(
        short = 'H',
        long,
        help = "Show entries that were populated while in the current working directory"
    )]
    here: bool,
    #[clap(long, help = "Filter to commands run in the specified directory (implies --here)")]
    working_directory: Option<PathBuf>,
    #[clap(
        short = 'S',
        long,
        conflicts_with_all = ["here", "working_directory"],
        help = "Display only commands from the specified session (\"current\", \"last\", or a session ID)"
    )]
    session: Option<String>,
    #[clap(short = 'F', long, help = "Show only commands that exited with a non-zero status")]
    failed: bool,
    #[clap(long, help = "Match patterns in any order instead of sequentially")]
    loosen: bool,
    #[clap(
        help = "One or more regular expressions to search through history entries; multiple values joined by `.*\\s.*`"
    )]
    patterns: Vec<String>,
    /// Internal: SQL-level LIMIT (may differ from display limit for --loosen)
    #[clap(skip)]
    sql_limit: Option<usize>,
}

#[derive(Parser, Debug)]
struct ImportCommand {
    #[clap(
        long,
        help = "Path to history file to import (defaults: bash=~/.bash_history, zsh=~/.zsh_history)"
    )]
    histfile: Option<PathBuf>,
    #[clap(long, help = "Type of shell history specified by --histfile")]
    shellname: String,
    #[clap(long, help = "Hostname to tag imported entries with (defaults to current hostname)")]
    hostname: Option<OsString>,
    #[clap(long, help = "Username to tag imported entries with (defaults to current user)")]
    username: Option<OsString>,
    #[clap(short = 'n', long, help = "Show what would be imported without making changes")]
    dry_run: bool,
}

/// Outcome of merging one source database into ours.
#[derive(Debug)]
struct MergeStats {
    /// Rows we actually scanned in the source (above the watermark, if any).
    considered: i64,
    /// Rows newly inserted into main.
    added: i64,
    /// Rows skipped due to secret-pattern filtering.
    filtered: i64,
    /// `MAX(id)` from the source -- the next watermark for it.
    new_max_id: Option<i64>,
}

#[derive(Parser, Debug)]
struct SyncCommand {
    #[clap(help = "Directory for sync operations (required for directory-based sync)")]
    dirname: Option<PathBuf>,
    #[clap(
        long,
        help = "(directory sync only) Only export the current database; do not read other databases",
        default_value_t = false
    )]
    export_only: bool,
    #[clap(long, help = "Remote host to sync with via SSH")]
    remote: Option<String>,
    #[clap(
        long,
        help = "Only send database to remote (no receive)",
        conflicts_with = "receive_only"
    )]
    send_only: bool,
    #[clap(
        long,
        help = "Only receive database from remote (no send)",
        conflicts_with = "send_only"
    )]
    receive_only: bool,
    #[clap(long, help = "Remote database path")]
    remote_db: Option<PathBuf>,
    #[clap(
        short = 'e',
        long,
        default_value = "ssh",
        help = "SSH command to use for connection (like rsync's -e option)"
    )]
    ssh_cmd: String,
    #[clap(long, default_value = "pxh", help = "Path to pxh binary on the remote host")]
    remote_pxh: String,
    #[clap(long, hide = true, help = "Internal: run in server mode")]
    server: bool,
    #[clap(
        long,
        help = "Only sync commands from the last N days (remote sync only)",
        value_name = "DAYS"
    )]
    since: Option<u32>,
    #[clap(long, hide = true, help = "Use stdin/stdout for sync instead of SSH (for testing)")]
    stdin_stdout: bool,
    #[clap(long, help = "Disable automatic filtering of potential secrets during sync import")]
    no_secret_filter: bool,
}

#[derive(Parser, Debug)]
struct ScrubCommand {
    #[clap(long, help = "Scrub from this histfile instead of (or in addition to) the database")]
    histfile: Option<PathBuf>,
    #[clap(
        long,
        conflicts_with = "histfile",
        help = "Scrub from all .db files in this directory (e.g., sync folder)"
    )]
    dir: Option<PathBuf>,
    #[clap(
        long,
        conflicts_with_all = ["histfile", "dir"],
        help = "Scrub from remote host via SSH sync protocol"
    )]
    remote: Option<String>,
    #[clap(
        short = 'e',
        long,
        default_value = "ssh",
        requires = "remote",
        help = "SSH command to use for connection (like rsync's -e option)"
    )]
    ssh_cmd: String,
    #[clap(long, requires = "remote", help = "Path to pxh binary on the remote host")]
    remote_pxh: Option<String>,
    #[clap(long, requires = "remote", help = "Remote database path")]
    remote_db: Option<PathBuf>,
    #[clap(short = 'n', long, help = "Dry-run mode (only display the rows, don't actually scrub)")]
    dry_run: bool,
    #[clap(
        long,
        help = "Use secret detection to find entries to scrub (instead of interactive prompt)"
    )]
    scan: bool,
    #[clap(short, long, default_value = "critical", value_enum)]
    confidence: ConfidenceLevel,
    #[clap(
        long,
        requires = "histfile",
        help = "Shell format for histfile (bash or zsh); auto-detected if not specified"
    )]
    shellname: Option<String>,
    #[clap(short = 'y', long, help = "Skip confirmation prompt")]
    yes: bool,
    #[clap(help = "The string to scrub (for interactive mode)")]
    contraband: Option<String>,
}

#[derive(Parser, Debug)]
struct InsertCommand {
    #[clap(long)]
    shellname: String,
    #[clap(long)]
    hostname: OsString,
    #[clap(long)]
    username: OsString,
    #[clap(long)]
    working_directory: Option<PathBuf>, // option because importing may lack working dir
    #[clap(long)]
    exit_status: Option<i64>,
    #[clap(long)]
    session_id: i64,
    #[clap(long)]
    start_unix_timestamp: Option<i64>, // similar to above
    #[clap(long)]
    end_unix_timestamp: Option<i64>,
    #[clap(allow_hyphen_values = true, trailing_var_arg = true)]
    command: Vec<OsString>,
}

#[derive(Parser, Debug)]
struct SealCommand {
    #[clap(long)]
    session_id: i64,
    #[clap(long)]
    exit_status: i32,
    #[clap(long)]
    end_unix_timestamp: i64,
}

#[derive(Parser, Debug)]
struct ShellConfigCommand {
    #[clap(help = "Shell name (bash or zsh)")]
    shellname: String,
    #[clap(long, help = "Don't bind Ctrl-R to pxh recall")]
    no_ctrl_r: bool,
}

#[derive(Parser, Debug)]
struct AutosuggestCommand {
    #[clap(help = "Prefix to match against command history")]
    prefix: OsString,
}

#[derive(Parser, Debug)]
struct CompletionsCommand {
    #[clap(help = "Shell to generate completions for (bash, zsh)")]
    shell: clap_complete::Shell,
}

#[derive(Parser, Debug)]
struct StatsCommand {}

#[derive(Parser, Debug)]
struct ConfigCommand {
    #[clap(long, help = "Print the config file path")]
    path: bool,
}

#[derive(Parser, Debug)]
struct ExportCommand {}

#[derive(Parser, Debug)]
struct MaintenanceCommand {
    #[clap(
        help = "Path(s) to SQLite database files to maintain (if not specified, maintains the current database)"
    )]
    files: Vec<PathBuf>,
}

#[derive(Parser, Debug)]
struct ScanCommand {
    #[clap(short, long, default_value = "critical", value_enum)]
    confidence: ConfidenceLevel,
    #[clap(short, long, help = "Output as JSON")]
    json: bool,
    #[clap(short, long, help = "Verbose output with pattern details")]
    verbose: bool,
    #[clap(long, help = "Scan this histfile instead of the database")]
    histfile: Option<PathBuf>,
    #[clap(
        long,
        conflicts_with = "histfile",
        help = "Scan all .db files in this directory (e.g., sync folder)"
    )]
    dir: Option<PathBuf>,
    #[clap(
        long,
        requires = "histfile",
        help = "Shell format for histfile (bash or zsh); auto-detected if not specified"
    )]
    shellname: Option<String>,
}

impl ImportCommand {
    fn default_histfile(shellname: &str) -> Result<PathBuf, Box<dyn std::error::Error>> {
        let home = home::home_dir().ok_or("could not determine home directory")?;
        match shellname {
            "bash" | "zsh" => {
                if let Ok(histfile) = env::var("HISTFILE") {
                    return Ok(PathBuf::from(histfile));
                }
                match shellname {
                    "bash" => Ok(home.join(".bash_history")),
                    _ => Ok(home.join(".zsh_history")),
                }
            }
            "json" => Err(Box::from("--histfile is required for json imports")),
            _ => Err(Box::from(format!("Unsupported shell: {} (PRs welcome!)", shellname))),
        }
    }

    fn go(&self, mut conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        let histfile = match &self.histfile {
            Some(path) => path.clone(),
            None => Self::default_histfile(&self.shellname)?,
        };
        let invocations = match self.shellname.as_ref() {
            "zsh" => pxh::import_zsh_history(
                &histfile,
                self.hostname.as_ref().map(|v| v.as_bytes().into()),
                self.username.as_ref().map(|v| v.as_bytes().into()),
            ),
            "bash" => pxh::import_bash_history(
                &histfile,
                self.hostname.as_ref().map(|v| v.as_bytes().into()),
                self.username.as_ref().map(|v| v.as_bytes().into()),
            ),
            "json" => pxh::import_json_history(&histfile),
            _ => Err(Box::from(format!("Unsupported shell: {} (PRs welcome!)", self.shellname))),
        }?;

        let total = invocations.len();
        let before_count: i64 =
            conn.query_row("SELECT COUNT(*) FROM command_history", [], |r| r.get(0))?;
        let tx = conn.transaction()?;
        for invocation in &invocations {
            invocation.insert(&tx)?;
        }
        let after_count: i64 =
            tx.query_row("SELECT COUNT(*) FROM command_history", [], |r| r.get(0))?;
        let new_count = after_count - before_count;
        let dup_count = total as i64 - new_count;

        if self.dry_run {
            tx.rollback()?;
            println!("Dry-run: {total} entries found, {new_count} new, {dup_count} duplicates.");
        } else {
            tx.commit()?;
            println!(
                "Imported {new_count} new entries ({dup_count} duplicates skipped) from {}.",
                histfile.display()
            );
        }
        Ok(())
    }
}

impl ShellConfigCommand {
    fn go(&self) -> Result<(), Box<dyn std::error::Error>> {
        // Check if ctrl-r should be disabled (via flag or config)
        let config = pxh::recall::Config::load();
        let disable_ctrl_r = self.no_ctrl_r || config.shell.disable_ctrl_r;

        let contents = match self.shellname.as_str() {
            "zsh" => String::from(include_str!("shell_configs/pxh.zsh")),
            "bash" => {
                let mut contents = String::new();
                contents.push_str(include_str!("vendor/bash-preexec.sh"));
                contents.push_str(include_str!("shell_configs/pxh.bash"));
                contents
            }
            _ => {
                return Err(Box::from(format!(
                    "Unsupported shell: {} (PRs welcome!)",
                    self.shellname
                )));
            }
        };

        // Remove ctrl-r binding section if disabled
        let contents = if disable_ctrl_r {
            contents
                .lines()
                .filter(|line| !line.contains("PXH_CTRL_R_BINDING"))
                .collect::<Vec<_>>()
                .join("\n")
                + "\n"
        } else {
            contents
        };

        io::stdout().write_all(contents.as_bytes())?;

        // Append shell completions so a single eval does everything
        let shell = match self.shellname.as_str() {
            "zsh" => clap_complete::Shell::Zsh,
            "bash" => clap_complete::Shell::Bash,
            _ => unreachable!(), // already handled above
        };
        clap_complete::generate(shell, &mut PxhArgs::command(), "pxh", &mut io::stdout());

        io::stdout().flush()?;
        Ok(())
    }
}

impl InstallCommand {
    fn go(&self) -> Result<(), Box<dyn std::error::Error>> {
        let shellname = self.shellname.as_ref();
        let rc_file = match shellname {
            "zsh" => ".zshrc",
            "bash" => ".bashrc",
            _ => return Err(Box::from(format!("Unsupported shell: {shellname} (PRs welcome!)"))),
        };

        let mut pb = home::home_dir().ok_or("Unable to determine your homedir")?;
        pb.push(rc_file);

        // Check what's already installed in the RC file.
        let contents = std::fs::read_to_string(&pb).unwrap_or_default();
        let has_shell_config = contents.contains("pxh shell-config");

        if has_shell_config {
            // Upgrade: remove separate completions line if present (shell-config now includes them)
            if contents.contains("pxh completions") {
                let new_contents: String = contents
                    .lines()
                    .filter(|line| !line.contains("pxh completions"))
                    .collect::<Vec<_>>()
                    .join("\n")
                    + "\n";
                std::fs::write(&pb, new_contents)?;
                println!(
                    "Removed separate completions line from {} (now included in shell-config).",
                    pb.display()
                );
            } else {
                println!("Shell config already present in {}; taking no action.", pb.display());
            }
            return Ok(());
        }

        let mut file = OpenOptions::new().append(true).create(true).open(&pb)?;

        write!(file, "\n# Install the pxh shell helpers to add interactive history realtime.")?;
        writeln!(
            file,
            r#"
if command -v pxh &> /dev/null; then
    eval "$(pxh shell-config {shellname})"
fi"#
        )?;
        println!("Shell config successfully added to {}.", pb.display());
        println!(
            "pxh will be active for all new shell sessions.  To activate for this session, run:"
        );
        println!("  source <(pxh shell-config {shellname})");
        Ok(())
    }
}

impl SealCommand {
    fn go(&self, mut conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Short busy_timeout: let our own jittered retry loop handle contention
        // so a single waiter can't burn the full timeout while others slip past.
        conn.busy_timeout(Duration::from_millis(100))?;
        pxh::with_write_retry(&mut conn, Duration::from_secs(1), |tx| {
            tx.execute(
                r#"
UPDATE command_history SET exit_status = ?, end_unix_timestamp = ?
 WHERE exit_status is NULL
   AND end_unix_timestamp IS NULL
   AND id = (SELECT MAX(id) FROM command_history hi WHERE hi.session_id = ?)"#,
                (self.exit_status, self.end_unix_timestamp, self.session_id),
            )?;
            Ok(())
        })
    }
}

impl AutosuggestCommand {
    fn go(&self, conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        let prefix = self.prefix.as_bytes();
        if prefix.is_empty() {
            return Ok(());
        }

        let mut stmt = conn.prepare(
            r#"
SELECT full_command
  FROM command_history
 WHERE substr(full_command, 1, ?) = CAST(? AS BLOB)
 ORDER BY start_unix_timestamp DESC, id DESC
 LIMIT 1"#,
        )?;

        let suggestion: Option<Vec<u8>> =
            stmt.query_row((prefix.len() as i64, prefix), |row| row.get(0)).ok();

        if let Some(cmd) = suggestion {
            io::stdout().write_all(&cmd)?;
            io::stdout().flush()?;
        }
        Ok(())
    }
}

impl ExportCommand {
    fn go(&self, conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        let mut stmt = conn.prepare(
        r#"
SELECT session_id, full_command, shellname, hostname, username, working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
  FROM command_history h
ORDER BY id"#,
    )?;
        let rows: Result<Vec<pxh::Invocation>, _> =
            stmt.query_map([], pxh::Invocation::from_row)?.collect();
        let rows = rows?;
        pxh::json_export(&rows)?;
        Ok(())
    }
}

impl MaintenanceCommand {
    fn go(&self, default_conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Helper function to get database size and other stats
        fn get_db_info(conn: &Connection) -> Result<(i64, i64, i64), Box<dyn std::error::Error>> {
            let page_count: i64 = conn.query_row("PRAGMA page_count", [], |r| r.get(0))?;
            let page_size: i64 = conn.query_row("PRAGMA page_size", [], |r| r.get(0))?;
            let freelist_count: i64 = conn.query_row("PRAGMA freelist_count", [], |r| r.get(0))?;
            Ok((page_count, page_size, freelist_count))
        }

        // Helper function to run maintenance on a single database connection
        fn maintain_database(
            conn: &Connection,
            db_name: &str,
        ) -> Result<(), Box<dyn std::error::Error>> {
            // Show database information before maintenance
            let (page_count, page_size, freelist_count) = get_db_info(conn)?;
            let total_size = page_count * page_size;
            let freelist_size = freelist_count * page_size;

            println!("Database '{db_name}' information before maintenance:");
            println!("  Total size: {:.2} MB", total_size as f64 / 1024.0 / 1024.0);
            println!("  Free space: {:.2} MB", freelist_size as f64 / 1024.0 / 1024.0);
            println!("  Page count: {page_count}");
            println!("  Page size: {page_size} bytes");
            println!("  Freelist count: {freelist_count}");

            // Validate this is actually a pxh database before modifying anything
            let is_pxh_db: bool = conn
                .query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='command_history'",
                    [],
                    |r| r.get::<_, i64>(0),
                )
                .map(|n| n > 0)?;
            if !is_pxh_db {
                return Err(format!(
                    "'{db_name}' does not look like a pxh database (no command_history table); refusing to run maintenance"
                )
                .into());
            }

            // Show row counts for main tables
            let command_count: i64 =
                conn.query_row("SELECT COUNT(*) FROM command_history", [], |r| r.get(0))?;
            println!("  Command history entries: {command_count}");
            println!();

            // Remove non-standard tables (except those prefixed with KEEP_)
            println!("Looking for non-standard tables to clean up...");
            let mut cleanup_count = 0;

            // Define the standard tables (excluding memory database tables)
            let standard_tables = ["command_history", "settings", "sqlite_sequence"];

            // Get all tables from the database
            let mut stmt = conn.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'
                                        EXCEPT SELECT name FROM sqlite_master WHERE name IN (?1, ?2, ?3)")?;

            let non_standard_tables: Vec<String> = stmt
                .query_map(
                    [&standard_tables[0], &standard_tables[1], &standard_tables[2]],
                    |row| row.get(0),
                )?
                .collect::<Result<Vec<String>, _>>()?;

            for table_name in non_standard_tables {
                if table_name.starts_with("KEEP_") {
                    println!("  Keeping user table: {table_name}");
                    continue;
                }

                println!("  Dropping non-standard table: {table_name}");
                let quoted = table_name.replace('"', "\"\"");
                conn.execute(&format!("DROP TABLE IF EXISTS \"{quoted}\""), [])?;
                cleanup_count += 1;
            }

            if cleanup_count > 0 {
                println!("Cleaned up {cleanup_count} non-standard tables");
            } else {
                println!("No non-standard tables found to clean up");
            }

            // Clean up non-standard indexes (except those prefixed with KEEP_)
            println!("Looking for non-standard indexes to clean up...");
            let standard_indexes = [
                "idx_command_history_unique",
                "history_session_id",
                "history_start_time",
                "idx_session_id_desc",
            ];

            // Exclude system indexes (sqlite_autoindex_*) and the standard indexes.
            // Also exclude indexes that relate to PRIMARY KEY or UNIQUE constraints to avoid errors
            let mut stmt = conn.prepare(
                "SELECT name FROM sqlite_master WHERE type='index' AND
                                        name NOT LIKE 'sqlite_autoindex_%' AND
                                        tbl_name NOT LIKE 'sqlite_%' AND
                                        name NOT IN (?1, ?2, ?3, ?4)",
            )?;

            let non_standard_indexes: Vec<String> = stmt
                .query_map(
                    [
                        &standard_indexes[0],
                        &standard_indexes[1],
                        &standard_indexes[2],
                        &standard_indexes[3],
                    ],
                    |row| row.get(0),
                )?
                .collect::<Result<Vec<String>, _>>()?;

            cleanup_count = 0;
            for index_name in non_standard_indexes {
                if index_name.starts_with("KEEP_") {
                    println!("  Keeping user index: {index_name}");
                    continue;
                }

                // Try to drop the index, but don't fail if it can't be dropped
                // (might be a PRIMARY KEY or UNIQUE constraint)
                let quoted = index_name.replace('"', "\"\"");
                match conn.execute(&format!("DROP INDEX IF EXISTS \"{quoted}\""), []) {
                    Ok(_) => {
                        println!("  Dropping non-standard index: {index_name}");
                        cleanup_count += 1;
                    }
                    Err(e) => {
                        println!("  Skipping index {index_name}: {e}");
                    }
                }
            }

            if cleanup_count > 0 {
                println!("Cleaned up {cleanup_count} non-standard indexes");
            } else {
                println!("No non-standard indexes found to clean up");
            }

            // Run ANALYZE to update statistics
            println!("Running ANALYZE...");
            conn.execute("ANALYZE", [])?;
            println!("ANALYZE completed successfully.");

            // Run VACUUM to reclaim space
            println!("Running VACUUM...");
            conn.execute("VACUUM", [])?;
            println!("VACUUM completed successfully.");

            // Show database information after maintenance
            let (page_count, page_size, freelist_count) = get_db_info(conn)?;
            let total_size = page_count * page_size;
            let freelist_size = freelist_count * page_size;

            println!("\nDatabase '{db_name}' information after maintenance:");
            println!("  Total size: {:.2} MB", total_size as f64 / 1024.0 / 1024.0);
            println!("  Free space: {:.2} MB", freelist_size as f64 / 1024.0 / 1024.0);
            println!("  Page count: {page_count}");
            println!("  Page size: {page_size} bytes");
            println!("  Freelist count: {freelist_count}");

            println!("\nDatabase '{db_name}' maintenance completed.");
            Ok(())
        }

        // If no files specified, use the default connection
        if self.files.is_empty() {
            return maintain_database(&default_conn, "default");
        }

        // Otherwise, process each file
        let mut success = true;
        for file_path in &self.files {
            let file_str = file_path.to_string_lossy();
            println!("\nPerforming maintenance on: {file_str}");

            // Open the database connection for this file
            match Connection::open(file_path) {
                Ok(conn) => {
                    if let Err(err) = maintain_database(&conn, &file_str) {
                        println!("Error maintaining database '{file_str}': {err}");
                        success = false;
                    }
                }
                Err(err) => {
                    println!("Error opening database '{file_str}': {err}");
                    success = false;
                }
            }
        }

        if success {
            println!("\nAll database maintenance operations completed successfully.");
            Ok(())
        } else {
            Err("One or more database maintenance operations failed".into())
        }
    }
}

#[derive(Clone, serde::Serialize)]
pub struct ScanMatch {
    command: String,
    pattern: String,
    timestamp: Option<i64>,
    #[serde(skip_serializing_if = "Option::is_none")]
    working_directory: Option<String>,
    #[serde(skip)]
    rowid: i64,
    #[serde(skip)]
    original_line: Option<String>,
}

impl ConfigCommand {
    fn go(&self) -> Result<(), Box<dyn std::error::Error>> {
        use pxh::recall::config::Config;

        let path = Config::default_config_path().ok_or("could not determine config path")?;

        if self.path {
            println!("{}", path.display());
            return Ok(());
        }

        // Create default config if it doesn't exist
        if !path.exists() {
            if let Some(parent) = path.parent() {
                fs::create_dir_all(parent)?;
            }
            let default_config = Config::default();
            let toml_str = toml::to_string_pretty(&default_config)?;
            fs::write(&path, toml_str)?;
        }

        // Open in $EDITOR
        let editor =
            env::var("VISUAL").or_else(|_| env::var("EDITOR")).unwrap_or_else(|_| "vi".to_string());
        let status = std::process::Command::new(&editor).arg(&path).status()?;
        if !status.success() {
            return Err(format!("{editor} exited with {status}").into());
        }
        Ok(())
    }
}

impl StatsCommand {
    fn go(&self, conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        let (total, unique): (i64, i64) = conn.query_row(
            "SELECT COUNT(*), COUNT(DISTINCT full_command) FROM command_history",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )?;

        let sessions: i64 =
            conn.query_row("SELECT COUNT(DISTINCT session_id) FROM command_history", [], |r| {
                r.get(0)
            })?;

        let (min_ts, max_ts): (Option<i64>, Option<i64>) = conn.query_row(
            "SELECT MIN(start_unix_timestamp), MAX(start_unix_timestamp) FROM command_history",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )?;

        let (success, with_status): (i64, i64) = conn.query_row(
            "SELECT COUNT(CASE WHEN exit_status = 0 THEN 1 END), COUNT(exit_status) FROM command_history",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )?;

        println!("Commands:   {total} total, {unique} unique");
        println!("Sessions:   {sessions}");

        if let (Some(min), Some(max)) = (min_ts, max_ts) {
            if let (Some(min_date), Some(max_date)) =
                (Local.timestamp_opt(min, 0).single(), Local.timestamp_opt(max, 0).single())
            {
                let days = (max - min) / 86400;
                println!(
                    "Period:     {} to {} ({days} days)",
                    min_date.format("%Y-%m-%d"),
                    max_date.format("%Y-%m-%d"),
                );
            } else {
                println!("Period:     n/a (invalid timestamps in database)");
            }
        }

        if with_status > 0 {
            let pct = 100.0 * success as f64 / with_status as f64;
            println!("Success:    {pct:.1}% (of {with_status} commands with exit status)");
        }

        // Shells
        let mut stmt = conn.prepare(
            "SELECT shellname, COUNT(*) as cnt FROM command_history GROUP BY shellname ORDER BY cnt DESC",
        )?;
        let shells: Vec<(String, i64)> =
            stmt.query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?.collect::<Result<_, _>>()?;
        if !shells.is_empty() {
            let shell_str: Vec<String> =
                shells.iter().map(|(name, cnt)| format!("{name}: {cnt}")).collect();
            println!("Shells:     {}", shell_str.join("  "));
        }

        // Top commands
        let mut stmt = conn.prepare(
            "SELECT full_command, COUNT(*) as cnt FROM command_history GROUP BY full_command ORDER BY cnt DESC LIMIT 10",
        )?;
        let top: Vec<(Vec<u8>, i64)> =
            stmt.query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?.collect::<Result<_, _>>()?;
        if !top.is_empty() {
            println!("\nTop commands:");
            for (cmd_bytes, cnt) in &top {
                let cmd = BString::new(cmd_bytes.clone());
                let display = if cmd.len() > 60 {
                    format!("{}...", &cmd[..60].to_str_lossy())
                } else {
                    cmd.to_str_lossy().into_owned()
                };
                println!("  {cnt:>6}  {display}");
            }
        }

        Ok(())
    }
}

impl ScanCommand {
    fn go(&self, conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Handle directory mode
        if let Some(ref dir) = self.dir {
            return self.go_dir_mode(dir);
        }

        let matches = self.run_scan(&conn)?;

        if self.json {
            println!("{}", serde_json::to_string_pretty(&matches)?);
            return Ok(());
        }

        if matches.is_empty() {
            println!("No potential secrets found.");
            return Ok(());
        }

        println!("Found {} potential secret(s):\n", matches.len());
        Self::display_matches(&matches, self.verbose);

        Ok(())
    }

    fn go_dir_mode(&self, dir: &Path) -> Result<(), Box<dyn std::error::Error>> {
        if !dir.exists() {
            return Err(format!("Directory does not exist: {}", dir.display()).into());
        }

        let (patterns, regex_set) = build_secret_patterns(self.confidence.as_str())?;
        if patterns.is_empty() {
            println!("No patterns available for confidence level '{}'.", self.confidence.as_str());
            return Ok(());
        }

        let db_extension = OsStr::new("db");
        let mut entries: Vec<_> = Vec::new();
        let mut skipped_entries = 0usize;
        for entry_result in fs::read_dir(dir)? {
            match entry_result {
                Ok(entry) => {
                    if entry.path().extension() == Some(db_extension) {
                        entries.push(entry);
                    }
                }
                Err(e) => {
                    eprintln!("Warning: Failed to read directory entry: {e}");
                    skipped_entries += 1;
                }
            }
        }

        if entries.is_empty() {
            println!("No .db files found in {}", dir.display());
            return Ok(());
        }

        println!("Scanning {} database files in {}\n", entries.len(), dir.display());

        let mut all_matches: Vec<ScanMatch> = Vec::new();
        let mut files_with_matches = 0usize;
        let mut skipped_files = 0usize;
        let num_files = entries.len();

        for entry in &entries {
            let path = entry.path();
            let conn = match Connection::open_with_flags(
                &path,
                OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX,
            ) {
                Ok(c) => c,
                Err(e) => {
                    eprintln!("Warning: Failed to open {}: {e}", path.display());
                    skipped_files += 1;
                    continue;
                }
            };

            // Validate it's a pxh database before scanning
            let is_pxh = conn
                .query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='command_history'",
                    [],
                    |r| r.get::<_, i64>(0),
                )
                .map(|n| n > 0)
                .unwrap_or(false);
            if !is_pxh {
                eprintln!("Warning: Skipping {} (not a pxh database)", path.display());
                skipped_files += 1;
                continue;
            }

            let mut matches: Vec<ScanMatch> = Vec::new();
            scan_database(&conn, &regex_set, &patterns, &mut matches, usize::MAX)?;

            if !matches.is_empty() {
                println!("{}:", path.display());
                if self.json {
                    // In JSON mode, collect all matches
                    all_matches.extend(matches.iter().cloned());
                } else {
                    Self::display_matches(&matches, self.verbose);
                }
                files_with_matches += 1;
            }
        }

        // Print summary
        if self.json {
            println!("{}", serde_json::to_string_pretty(&all_matches)?);
        } else if files_with_matches == 0 && skipped_files == 0 {
            println!("No potential secrets found.");
        } else {
            println!(
                "\nSummary: Found potential secrets in {} of {} database(s).",
                files_with_matches, num_files
            );
        }

        // Report skipped files and return error if any were skipped
        let total_skipped = skipped_entries + skipped_files;
        if total_skipped > 0 {
            eprintln!(
                "WARNING: {} file(s) could not be processed. Results may be incomplete.",
                total_skipped
            );
            return Err(format!(
                "Scan incomplete: {} file(s) could not be processed",
                total_skipped
            )
            .into());
        }

        Ok(())
    }

    fn run_scan(&self, conn: &Connection) -> Result<Vec<ScanMatch>, Box<dyn std::error::Error>> {
        let (patterns, regex_set) = build_secret_patterns(self.confidence.as_str())?;

        if patterns.is_empty() {
            return Ok(vec![]);
        }

        let mut matches: Vec<ScanMatch> = Vec::new();

        if let Some(ref histfile) = self.histfile {
            let content = fs::read(histfile)?;
            let shellname = self.shellname.clone().unwrap_or_else(|| detect_shell_format(&content));
            scan_histfile(&content, &shellname, &regex_set, &patterns, &mut matches, usize::MAX)?;
        } else {
            scan_database(conn, &regex_set, &patterns, &mut matches, usize::MAX)?;
        }

        Ok(matches)
    }

    fn display_matches(matches: &[ScanMatch], verbose: bool) {
        for m in matches {
            let time_str = m.timestamp.map_or_else(
                || "n/a".to_string(),
                |ts| {
                    Local
                        .timestamp_opt(ts, 0)
                        .single()
                        .map(|t| t.format("%Y-%m-%d %H:%M:%S").to_string())
                        .unwrap_or_else(|| "n/a".to_string())
                },
            );
            if verbose {
                println!("  [{time_str}] {}", m.pattern);
                if let Some(ref wd) = m.working_directory {
                    println!("  Directory: {wd}");
                }
                println!("  {}\n", m.command);
            } else {
                println!("  [{time_str}] {}", m.pattern);
                println!("  {}\n", m.command);
            }
        }
    }
}

// Shared scanning utilities

pub fn build_secret_patterns(
    confidence: &str,
) -> Result<SecretPatterns, Box<dyn std::error::Error>> {
    use pxh::secrets_patterns::{PATTERNS_CRITICAL, PATTERNS_HIGH, PATTERNS_LOW};
    use regex::bytes::RegexSetBuilder;

    let (patterns, size_limit): (Vec<(&str, &str)>, usize) = match confidence {
        "critical" => (PATTERNS_CRITICAL.to_vec(), REGEX_SIZE_LIMIT_DEFAULT),
        "high" => {
            let mut v = PATTERNS_CRITICAL.to_vec();
            v.extend(PATTERNS_HIGH);
            (v, REGEX_SIZE_LIMIT_DEFAULT)
        }
        "low" => {
            let mut v = PATTERNS_CRITICAL.to_vec();
            v.extend(PATTERNS_HIGH);
            v.extend(PATTERNS_LOW);
            (v, REGEX_SIZE_LIMIT_ALL)
        }
        "all" => {
            let mut all = PATTERNS_CRITICAL.to_vec();
            all.extend(PATTERNS_HIGH);
            all.extend(PATTERNS_LOW);
            (all, REGEX_SIZE_LIMIT_ALL)
        }
        _ => {
            return Err(format!(
                "Invalid confidence level: '{}'. Use 'critical', 'high', 'low', or 'all'",
                confidence
            )
            .into());
        }
    };

    let regex_patterns: Vec<&str> = patterns.iter().map(|(_, regex)| *regex).collect();
    let regex_set = RegexSetBuilder::new(&regex_patterns).size_limit(size_limit).build()?;

    Ok((patterns, regex_set))
}

fn is_bash_timestamp_line(line: &[u8]) -> bool {
    line.starts_with(b"#") && line.len() > 1 && line[1..].iter().all(|&b| b.is_ascii_digit())
}

fn prompt_for_confirmation() -> Result<bool, Box<dyn std::error::Error>> {
    print!("Proceed with scrubbing? [y/N] ");
    io::stdout().flush()?;
    let mut input = String::new();
    io::stdin().read_line(&mut input)?;
    Ok(input.trim().eq_ignore_ascii_case("y"))
}

fn looks_like_zsh_extended_history(line: &[u8]) -> bool {
    // Zsh EXTENDED_HISTORY format: `: <digits>:<digits>;`
    // Must not match bash no-ops like `: ${VAR:=x}; cmd`
    if !line.starts_with(b": ") {
        return false;
    }
    let rest = &line[2..];
    let colon_pos = match rest.iter().position(|&b| b == b':') {
        Some(p) => p,
        None => return false,
    };
    let semi_pos = match rest.iter().position(|&b| b == b';') {
        Some(p) => p,
        None => return false,
    };
    // timestamp field must be all digits, and colon must come before semicolon
    colon_pos < semi_pos && rest[..colon_pos].iter().all(|b| b.is_ascii_digit())
}

fn detect_shell_format(content: &[u8]) -> String {
    for line in content.split(|&b| b == b'\n').take(10) {
        if line.is_empty() {
            continue;
        }
        if looks_like_zsh_extended_history(line) {
            return "zsh".to_string();
        }
        if is_bash_timestamp_line(line) {
            return "bash".to_string();
        }
    }
    eprintln!(
        "Warning: Could not detect shell format, defaulting to bash. Use --shellname to specify."
    );
    "bash".to_string()
}

pub fn scan_database(
    conn: &Connection,
    regex_set: &regex::bytes::RegexSet,
    patterns: &[(&str, &str)],
    matches: &mut Vec<ScanMatch>,
    limit: usize,
) -> Result<(), Box<dyn std::error::Error>> {
    let mut stmt = conn.prepare(
        "SELECT rowid, full_command, start_unix_timestamp, working_directory FROM command_history",
    )?;

    let mut rows = stmt.query([])?;
    while let Some(row) = rows.next()? {
        if matches.len() >= limit {
            break;
        }

        let rowid: i64 = row.get(0)?;
        let command: Vec<u8> = row.get(1)?;
        let timestamp: Option<i64> = row.get(2)?;
        let working_directory: Option<Vec<u8>> = row.get(3)?;

        let matched_indices: Vec<usize> = regex_set.matches(&command).into_iter().collect();
        for idx in matched_indices {
            if matches.len() >= limit {
                break;
            }
            matches.push(ScanMatch {
                command: String::from_utf8_lossy(&command).to_string(),
                pattern: patterns[idx].0.to_string(),
                timestamp,
                working_directory: working_directory
                    .as_ref()
                    .map(|wd| String::from_utf8_lossy(wd).to_string()),
                rowid,
                original_line: None,
            });
        }
    }
    Ok(())
}

fn scan_histfile(
    content: &[u8],
    shellname: &str,
    regex_set: &regex::bytes::RegexSet,
    patterns: &[(&str, &str)],
    matches: &mut Vec<ScanMatch>,
    limit: usize,
) -> Result<(), Box<dyn std::error::Error>> {
    // For zsh, pre-join backslash-continuation lines so multi-line
    // commands are scanned as a single unit.
    let joined;
    let lines: Vec<&[u8]> = if shellname == "zsh" {
        joined = pxh::join_continuation_lines(content);
        joined.iter().map(|v| v.as_slice()).collect()
    } else {
        content.split(|&b| b == b'\n').collect()
    };
    let mut prev_timestamp_line: Option<&[u8]> = None;

    for line in &lines {
        if matches.len() >= limit {
            break;
        }
        if line.is_empty() {
            prev_timestamp_line = None;
            continue;
        }

        let parsed = parse_histfile_line(line, shellname, prev_timestamp_line);

        if shellname == "bash" && is_bash_timestamp_line(line) {
            prev_timestamp_line = Some(line);
            continue;
        }
        prev_timestamp_line = None;

        let Some((command, timestamp, lines_to_remove)) = parsed else {
            continue;
        };

        if command.is_empty() {
            continue;
        }

        let matched_indices: Vec<usize> = regex_set.matches(&command).into_iter().collect();
        for idx in matched_indices {
            if matches.len() >= limit {
                break;
            }
            matches.push(ScanMatch {
                command: String::from_utf8_lossy(&command).to_string(),
                pattern: patterns[idx].0.to_string(),
                timestamp,
                working_directory: None,
                rowid: 0,
                original_line: Some(lines_to_remove.clone()),
            });
        }
    }
    Ok(())
}

fn parse_histfile_line(
    line: &[u8],
    shellname: &str,
    prev_timestamp_line: Option<&[u8]>,
) -> Option<(Vec<u8>, Option<i64>, String)> {
    match shellname {
        "zsh" => {
            let semi_pos = line.iter().position(|&b| b == b';')?;
            let fields = &line[..semi_pos];
            let parts: Vec<&[u8]> = fields.splitn(3, |&b| b == b':').collect();
            if parts.len() < 3 {
                return None;
            }

            let command = line[semi_pos + 1..].to_vec();
            let timestamp = parts.get(1).and_then(|ts| {
                let ts_str = String::from_utf8_lossy(ts);
                ts_str.trim().parse::<i64>().ok()
            });

            // For multi-line commands (joined by join_continuation_lines),
            // reconstruct the on-disk representation with trailing backslashes
            // so scrub_from_histfile can match the physical lines.
            let line_lossy = String::from_utf8_lossy(line);
            let lines_to_remove = if line_lossy.contains('\n') {
                let physical: Vec<&str> = line_lossy.split('\n').collect();
                physical
                    .iter()
                    .enumerate()
                    .map(
                        |(i, l)| {
                            if i < physical.len() - 1 { format!("{l}\\") } else { l.to_string() }
                        },
                    )
                    .collect::<Vec<_>>()
                    .join("\n")
            } else {
                line_lossy.to_string()
            };

            Some((command, timestamp, lines_to_remove))
        }
        "bash" => {
            if is_bash_timestamp_line(line) {
                return None;
            }

            let mut lines_to_remove = String::new();
            let timestamp = if let Some(ts_line) = prev_timestamp_line {
                lines_to_remove.push_str(&String::from_utf8_lossy(ts_line));
                lines_to_remove.push('\n');
                String::from_utf8_lossy(&ts_line[1..]).trim().parse::<i64>().ok()
            } else {
                None
            };
            lines_to_remove.push_str(&String::from_utf8_lossy(line));

            Some((line.to_vec(), timestamp, lines_to_remove))
        }
        _ => Some((line.to_vec(), None, String::from_utf8_lossy(line).to_string())),
    }
}

fn scrub_from_database(
    conn: &Connection,
    matches: &[ScanMatch],
) -> Result<usize, Box<dyn std::error::Error>> {
    let mut rowids_to_delete: Vec<i64> = matches.iter().map(|m| m.rowid).collect();
    rowids_to_delete.sort();
    rowids_to_delete.dedup();

    for rowid in &rowids_to_delete {
        conn.execute("DELETE FROM command_history WHERE rowid = ?", [rowid])?;
    }

    Ok(rowids_to_delete.len())
}

fn scrub_from_histfile(
    histfile: &Path,
    matches: &[ScanMatch],
) -> Result<usize, Box<dyn std::error::Error>> {
    let mut lines_to_remove: Vec<String> = matches
        .iter()
        .filter_map(|m| m.original_line.clone())
        .flat_map(|s| s.lines().map(String::from).collect::<Vec<_>>())
        .collect();
    lines_to_remove.sort();
    lines_to_remove.dedup();

    let entry_count = matches.iter().filter(|m| m.original_line.is_some()).count();
    let lines_refs: Vec<&str> = lines_to_remove.iter().map(|s| s.as_str()).collect();
    pxh::atomically_remove_matching_lines_from_file(histfile, &lines_refs)?;

    Ok(entry_count)
}

// Merge all (hopefully) pxh files ending in .db in the specified path
// into the current database, then write an output with our hostname.

impl SyncCommand {
    /// Create a temporary database file with optional --since filtering
    fn create_filtered_db_copy(
        &self,
        conn: &mut Connection,
    ) -> Result<NamedTempFile, Box<dyn std::error::Error>> {
        // Create a temporary file with the database
        let temp_file = NamedTempFile::new()?;

        // Use VACUUM INTO to create a complete copy
        conn.execute("VACUUM INTO ?", (temp_file.path().to_str(),))?;

        if let Some(days) = self.since {
            // Open the temp database and delete old records
            let temp_conn = Connection::open(temp_file.path())?;

            // Calculate timestamp threshold
            let now = std::time::SystemTime::now()
                .duration_since(std::time::UNIX_EPOCH)
                .unwrap()
                .as_secs() as i64;
            let threshold = now - (days as i64 * 86400);

            // Delete records older than the threshold (including NULLs,
            // which have no known timestamp and shouldn't bypass the filter)
            temp_conn.execute(
                "DELETE FROM command_history WHERE start_unix_timestamp IS NULL OR start_unix_timestamp <= ?",
                [threshold],
            )?;

            // VACUUM to reclaim space
            temp_conn.execute("VACUUM", ())?;
            drop(temp_conn); // Close the connection to flush all changes
        }

        Ok(temp_file)
    }
    fn go(&self, mut conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Validate that --send-only and --receive-only are only used with remote sync
        if (self.send_only || self.receive_only) && self.remote.is_none() && !self.stdin_stdout {
            return Err(Box::from(
                "--send-only and --receive-only flags require --remote or --stdin-stdout to be specified",
            ));
        }

        // Validate that --remote and directory are not used together
        if self.remote.is_some() && self.dirname.is_some() {
            return Err(Box::from("Cannot specify both --remote and a directory path"));
        }

        // Validate that --since is only used with remote sync
        if self.since.is_some() && self.remote.is_none() && !self.stdin_stdout && !self.server {
            return Err(Box::from(
                "--since requires --remote or --stdin-stdout (not supported in directory sync mode)",
            ));
        }

        // If in server mode, handle sync protocol
        if self.server {
            return self.handle_server_mode(&mut conn);
        }

        // Handle remote sync if specified (either SSH or stdin/stdout)
        if self.stdin_stdout || self.remote.is_some() {
            return self.handle_remote_sync(&mut conn);
        }

        // Original directory-based sync behavior requires dirname
        let dirname =
            self.dirname.as_ref().ok_or("Directory path is required for directory-based sync")?;

        if !dirname.exists() {
            fs::create_dir(dirname)?;
        }
        let mut output_path = dirname.clone();
        let config = pxh::recall::config::Config::load();
        let hostname = pxh::resolve_hostname(&config, &conn);
        output_path.push(format!("{}.db", hostname.to_str_lossy()));
        // TODO: vacuum seems to want a plain text string path, unlike
        // ATTACH below which takes an os_str as bytes, so we can't
        // use BString to get a vec<u8>.  Look into why this is and if
        // there is a workaround.
        let output_path_str =
            output_path.to_str().ok_or("Unable to represent output filename as a string")?;

        if !self.export_only {
            let entries = fs::read_dir(dirname)?;
            let db_extension = OsStr::new("db");
            let filter_secrets = !self.no_secret_filter;
            // Track machine_ids seen this run so we can warn if two source
            // databases claim the same identity (cloned install, misconfig).
            let mut seen_machine_ids: std::collections::HashMap<u64, PathBuf> =
                std::collections::HashMap::new();
            for entry in entries {
                let path = entry?.path();
                if path.extension() == Some(db_extension) && output_path != path {
                    Self::sync_from_directory_source(
                        &mut conn,
                        path,
                        filter_secrets,
                        &mut seen_machine_ids,
                    )?;
                }
            }
        }

        // Create database copy without filtering (--since only applies to remote sync)
        let temp_file = NamedTempFile::new_in(dirname.as_path())?;
        conn.execute("VACUUM INTO ?", (temp_file.path().to_str(),))?;
        temp_file.persist(output_path_str)?;
        if self.export_only {
            println!("Backed-up database to {output_path_str}");
        } else {
            println!("Saved merged database to {output_path_str}");
        }

        Ok(())
    }

    fn handle_remote_sync(&self, conn: &mut Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Determine sync mode
        let mode = if self.send_only {
            "send"
        } else if self.receive_only {
            "receive"
        } else {
            "bidirectional"
        };

        let mut child = if self.stdin_stdout {
            // For stdin/stdout mode (testing), we're already connected
            // Create a dummy child process that uses stdin/stdout
            None
        } else {
            // SSH mode
            let host = self.remote.as_ref().ok_or("Remote host required for SSH sync")?;
            println!("Syncing with {host}...");

            // Parse SSH command and arguments
            let (ssh_cmd, ssh_args) = pxh::helpers::parse_ssh_command(&self.ssh_cmd);

            let remote_db_path = self
                .remote_db
                .as_ref()
                .map_or_else(pxh::helpers::default_remote_db_expr, |p| p.display().to_string());

            // Build remote pxh command with candidate path probing
            let since_arg = self.since.map_or(String::new(), |d| format!(" --since {d}"));
            let pxh_args = format!("--db {remote_db_path} sync --server{since_arg}");
            let remote_command =
                pxh::helpers::build_remote_pxh_command(&self.remote_pxh, &pxh_args);

            let mut cmd = std::process::Command::new(&ssh_cmd);
            cmd.args(&ssh_args)
                .arg(host)
                .arg(&remote_command)
                .stdin(std::process::Stdio::piped())
                .stdout(std::process::Stdio::piped())
                .stderr(std::process::Stdio::inherit()); // Map stderr to our stderr

            Some(cmd.spawn().map_err(|e| format!("Failed to spawn SSH command: {e}"))?)
        };

        // Handle stdin/stdout directly or through SSH child process
        let (mut stdin_writer, mut stdout_reader) = if self.stdin_stdout {
            // Use actual stdin/stdout
            (
                Box::new(std::io::stdout()) as Box<dyn Write>,
                Box::new(std::io::stdin()) as Box<dyn Read>,
            )
        } else {
            // Use SSH child process pipes
            if let Some(ref mut child) = child {
                let stdin = child.stdin.take().ok_or("Failed to get stdin from SSH process")?;
                let stdout = child.stdout.take().ok_or("Failed to get stdout from SSH process")?;
                (Box::new(stdin) as Box<dyn Write>, Box::new(stdout) as Box<dyn Read>)
            } else {
                return Err(Box::from("No child process available"));
            }
        };

        // Send mode to server
        stdin_writer.write_all(mode.as_bytes())?;
        stdin_writer.write_all(b"\n")?;
        stdin_writer.flush()?;

        // Execute the appropriate sync operations
        match mode {
            "send" => {
                self.send_database(&mut stdin_writer, conn)?;
                drop(stdin_writer);
            }
            "receive" => {
                // For receive-only, we need to close stdin
                drop(stdin_writer);
                self.receive_database(&mut stdout_reader, conn)?;
            }
            "bidirectional" => {
                self.send_database(&mut stdin_writer, conn)?;
                // Close stdin to signal we're done sending
                drop(stdin_writer);
                self.receive_database(&mut stdout_reader, conn)?;
            }
            _ => unreachable!(),
        }

        // Wait for child process if using SSH
        if let Some(mut child) = child {
            let status = child.wait()?;
            if !status.success() {
                return Err(Box::from("Remote sync failed"));
            }
        }

        if !self.stdin_stdout {
            println!("Sync completed successfully");
        }

        Ok(())
    }

    /// Sync from one source DB during a directory-mode merge. Reads the source's
    /// `local_machine_id` to look up an incremental-sync watermark in our own
    /// settings, then merges only rows above that watermark. Falls back to a
    /// full scan if the source doesn't advertise a `local_machine_id` (older
    /// pxh, never ran `install`). Tracks duplicate machine_ids across this run
    /// and warns -- two source databases shouldn't share identity.
    fn sync_from_directory_source(
        conn: &mut Connection,
        path: PathBuf,
        filter_secrets: bool,
        seen_machine_ids: &mut std::collections::HashMap<u64, PathBuf>,
    ) -> Result<(), Box<dyn std::error::Error>> {
        // Quick read-only peek at the source for its machine_id. Open read-only
        // so we don't touch the file before the merge function runs its own
        // schema setup with a regular connection.
        let mut source_machine_id = {
            let other = Connection::open_with_flags(&path, OpenFlags::SQLITE_OPEN_READ_ONLY).ok();
            other.as_ref().and_then(pxh::read_local_machine_id)
        };

        if let Some(mid) = source_machine_id
            && let Some(prev) = seen_machine_ids.insert(mid, path.clone())
        {
            eprintln!(
                "Warning: {} and {} both report machine_id {mid} -- one of them is likely a clone or misconfigured. Falling back to full scan for this source to avoid silently missing rows.",
                prev.display(),
                path.display()
            );
            // Skip watermark + machine_id-keyed bookkeeping for this source --
            // the two DBs share an identity, so their id spaces aren't
            // comparable. INSERT OR IGNORE still keeps the merge correct.
            source_machine_id = None;
        }

        // Resolve watermark for this source (None means full-scan fallback).
        let watermark_key = source_machine_id.map(|mid| format!("sync_watermark_{mid}"));
        let watermark: Option<i64> = watermark_key.as_deref().and_then(|key| {
            pxh::get_setting(conn, key)
                .ok()
                .flatten()
                .and_then(|bs| std::str::from_utf8(bs.as_slice()).ok()?.parse::<i64>().ok())
        });

        print!("Syncing from {}...", path.to_string_lossy());
        let stats = Self::merge_database_from_file_filtered(conn, path, filter_secrets, watermark)?;

        // Sanity check: if the source's max(id) regressed below our watermark,
        // the source DB was probably restored from backup or rebuilt. The merge
        // we just ran missed nothing (we had it all), but reset the watermark
        // to the new max so future syncs use the correct value.
        if let (Some(wm), Some(new_max)) = (watermark, stats.new_max_id)
            && new_max < wm
        {
            eprintln!(" (notice: source max(id) {new_max} < watermark {wm}; resetting watermark)");
        }

        if stats.filtered > 0 {
            println!(
                "done, considered {} rows, added {}, filtered {}",
                stats.considered, stats.added, stats.filtered
            );
        } else {
            println!("done, considered {} rows and added {}", stats.considered, stats.added);
        }

        // Persist the new watermark when we have a machine_id key for it.
        if let (Some(key), Some(new_max)) = (watermark_key, stats.new_max_id) {
            let bs = BString::from(new_max.to_string());
            pxh::set_setting(conn, &key, &bs)?;
        }
        Ok(())
    }

    /// Merge history from a database file with optional secret filtering and
    /// an optional incremental-sync watermark (skip rows in `other` whose
    /// `id <= watermark`). The unsealed-row UPDATE always scans all of `other`
    /// since seal info may sit at any id, watermark or not.
    fn merge_database_from_file_filtered(
        conn: &mut Connection,
        path: PathBuf,
        filter_secrets: bool,
        watermark: Option<i64>,
    ) -> Result<MergeStats, Box<dyn std::error::Error>> {
        // Ensure the other database has the current schema before ATTACHing,
        // so older databases (e.g. pre-machine_id) don't fail on missing columns.
        {
            let other = Connection::open(&path)?;
            pxh::initialize_base_schema(&other)?;
            pxh::run_schema_migrations(&other)?;
        }

        let tx = conn.transaction()?;
        let before_count: i64 =
            tx.prepare("SELECT COUNT(*) FROM main.command_history")?.query_row((), |r| r.get(0))?;
        tx.execute("ATTACH DATABASE ? AS other", (path.as_os_str().as_bytes(),))?;

        let mut filtered_count: i64 = 0;
        // -1 sentinel matches all rows (id is AUTOINCREMENT, so always >= 1).
        let lo = watermark.unwrap_or(-1);

        if filter_secrets {
            // Build secret patterns for filtering (use "critical" confidence by default)
            let (patterns, regex_set) = build_secret_patterns("critical")?;

            if patterns.is_empty() {
                // No patterns, just do regular merge
                tx.execute(
                    r#"
INSERT OR IGNORE INTO main.command_history (
    session_id, full_command, shellname, hostname, username,
    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
)
SELECT session_id, full_command, shellname, hostname, username,
    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
FROM other.command_history
WHERE id > ?
"#,
                    [lo],
                )?;
            } else {
                // Fetch records from other database and filter
                let mut stmt = tx.prepare(
                    r#"
SELECT session_id, full_command, shellname, hostname, username,
       working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
FROM other.command_history
WHERE id > ?
"#,
                )?;

                let mut rows = stmt.query([lo])?;
                while let Some(row) = rows.next()? {
                    let full_command: Vec<u8> = row.get(1)?;

                    // Check if command matches any secret pattern
                    if regex_set.is_match(&full_command) {
                        filtered_count += 1;
                        continue;
                    }

                    // Insert the record if it doesn't match any secret pattern
                    tx.execute(
                        r#"
INSERT OR IGNORE INTO main.command_history (
    session_id, full_command, shellname, hostname, username,
    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"#,
                        (
                            row.get::<_, i64>(0)?,
                            &full_command,
                            row.get::<_, String>(2)?,
                            row.get::<_, Option<Vec<u8>>>(3)?,
                            row.get::<_, Option<Vec<u8>>>(4)?,
                            row.get::<_, Option<Vec<u8>>>(5)?,
                            row.get::<_, Option<i64>>(6)?,
                            row.get::<_, Option<i64>>(7)?,
                            row.get::<_, Option<i64>>(8)?,
                            row.get::<_, Option<i64>>(9)?,
                        ),
                    )?;
                }
            }
        } else {
            // No filtering, just do regular merge
            tx.execute(
                r#"
INSERT OR IGNORE INTO main.command_history (
    session_id, full_command, shellname, hostname, username,
    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
)
SELECT session_id, full_command, shellname, hostname, username,
    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
FROM other.command_history
WHERE id > ?
"#,
                [lo],
            )?;
        }

        // Upgrade unsealed rows: if a command was synced while still running
        // (exit_status/end_unix_timestamp NULL), fill in the sealed values from
        // the other database where available. Scans all of `other` regardless
        // of watermark -- a seal can land at any id.
        tx.execute(
            r#"
UPDATE main.command_history
SET exit_status = o.exit_status,
    end_unix_timestamp = o.end_unix_timestamp
FROM other.command_history o
WHERE main.command_history.exit_status IS NULL
  AND o.exit_status IS NOT NULL
  AND main.command_history.full_command = o.full_command
  AND main.command_history.start_unix_timestamp IS o.start_unix_timestamp
  AND main.command_history.shellname = o.shellname
  AND COALESCE(main.command_history.hostname, '') = COALESCE(o.hostname, '')
"#,
            (),
        )?;

        // Count records after the merge
        let after_count: i64 =
            tx.prepare("SELECT COUNT(*) FROM main.command_history")?.query_row((), |r| r.get(0))?;

        // Rows considered = source rows we actually scanned (above watermark).
        let other_count: i64 = tx
            .prepare("SELECT COUNT(*) FROM other.command_history WHERE id > ?")?
            .query_row([lo], |r| r.get(0))?;

        // Highest id in source -- caller persists as the next watermark.
        let new_max_id: Option<i64> = tx
            .prepare("SELECT MAX(id) FROM other.command_history")?
            .query_row((), |r| r.get(0))
            .ok();

        tx.commit()?;
        conn.execute("DETACH DATABASE other", ())?;
        Ok(MergeStats {
            considered: other_count,
            added: after_count - before_count,
            filtered: filtered_count,
            new_max_id,
        })
    }

    fn handle_server_mode(&self, conn: &mut Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Read mode from stdin
        let mut mode = String::new();
        std::io::stdin().read_line(&mut mode)?;

        if mode.is_empty() {
            return Err(Box::from("No sync mode received"));
        }

        let mode = mode.trim();

        // Check for v2 protocol modes
        let (base_mode, options) = if mode.ends_with("-v2") {
            // v2 protocol: read options JSON from next line
            let mut options_json = String::new();
            std::io::stdin().read_line(&mut options_json)?;
            let options: SyncOptions = serde_json::from_str(options_json.trim())
                .map_err(|e| format!("Failed to parse v2 protocol options: {e}. Client and server may have incompatible versions."))?;
            (mode.strip_suffix("-v2").unwrap(), options)
        } else {
            (mode, SyncOptions::default())
        };

        match base_mode {
            "send" => {
                // Server receives database from client
                self.receive_database_with_options(&mut std::io::stdin(), conn, &options)?;
            }
            "receive" => {
                // Server sends database to client
                self.send_database(&mut std::io::stdout(), conn)?;
            }
            "bidirectional" => {
                // Server receives then sends
                self.receive_database_with_options(&mut std::io::stdin(), conn, &options)?;
                self.send_database(&mut std::io::stdout(), conn)?;
            }
            "scrub" => {
                // Remote scrub: execute scrub and return result
                let result = self.execute_remote_scrub(conn, &options)?;
                println!("{result}");
            }
            _ => return Err(Box::from(format!("Unknown sync mode: {mode}"))),
        }

        Ok(())
    }

    /// Execute scrub operation as requested by remote client
    fn execute_remote_scrub(
        &self,
        conn: &Connection,
        options: &SyncOptions,
    ) -> Result<String, Box<dyn std::error::Error>> {
        let dry_run = options.scrub_dry_run.unwrap_or(false);
        let confidence = options.scrub_confidence.as_deref().unwrap_or("critical");

        let mut matches: Vec<ScanMatch> = Vec::new();

        if options.scrub_scan.unwrap_or(false) {
            // Use secret detection
            let (patterns, regex_set) = build_secret_patterns(confidence)?;
            if !patterns.is_empty() {
                scan_database(conn, &regex_set, &patterns, &mut matches, usize::MAX)?;
            }
        } else if let Some(ref pattern) = options.scrub {
            // Use explicit pattern
            let mut stmt = conn.prepare(
                "SELECT rowid, full_command, start_unix_timestamp, working_directory FROM command_history WHERE INSTR(full_command, ?) > 0",
            )?;
            let mut rows = stmt.query([pattern.as_bytes()])?;
            while let Some(row) = rows.next()? {
                matches.push(ScanMatch {
                    rowid: row.get(0)?,
                    command: String::from_utf8_lossy(&row.get::<_, Vec<u8>>(1)?).to_string(),
                    pattern: "manual".to_string(),
                    timestamp: row.get(2)?,
                    working_directory: row
                        .get::<_, Option<Vec<u8>>>(3)?
                        .map(|v| String::from_utf8_lossy(&v).to_string()),
                    original_line: None,
                });
            }
        } else {
            return Err("Remote scrub requires --scan or a pattern".into());
        }

        if matches.is_empty() {
            return Ok("No entries matched for scrubbing.".to_string());
        }

        if dry_run {
            return Ok(format!("Dry-run: {} entries would be scrubbed.", matches.len()));
        }

        let count = scrub_from_database(conn, &matches)?;
        Ok(format!("Scrubbed {} entries from remote database.", count))
    }

    fn send_database<W: Write>(
        &self,
        writer: &mut W,
        conn: &mut Connection,
    ) -> Result<(), Box<dyn std::error::Error>> {
        // Create filtered database copy
        let temp_file = self.create_filtered_db_copy(conn)?;

        // Get file size
        let metadata = std::fs::metadata(temp_file.path())?;
        let size = metadata.len();

        // Send size and database
        writer.write_all(&size.to_le_bytes())?;

        let mut file = File::open(temp_file.path())?;
        io::copy(&mut file, writer)?;
        writer.flush()?;

        Ok(())
    }

    fn receive_database<R: Read>(
        &self,
        reader: &mut R,
        conn: &mut Connection,
    ) -> Result<(), Box<dyn std::error::Error>> {
        let options =
            SyncOptions { no_secret_filter: Some(self.no_secret_filter), ..Default::default() };
        self.receive_database_with_options(reader, conn, &options)
    }

    fn receive_database_with_options<R: Read>(
        &self,
        reader: &mut R,
        conn: &mut Connection,
        options: &SyncOptions,
    ) -> Result<(), Box<dyn std::error::Error>> {
        // Receive database size (8 bytes)
        let mut size_bytes = [0u8; 8];
        reader.read_exact(&mut size_bytes)?;
        let size = u64::from_le_bytes(size_bytes);

        if size > MAX_SYNC_DB_SIZE {
            return Err(format!(
                "Received database size ({} bytes) exceeds maximum allowed ({} bytes)",
                size, MAX_SYNC_DB_SIZE
            )
            .into());
        }

        // Receive database data
        let mut data = vec![0u8; size as usize];
        reader.read_exact(&mut data)?;

        // Create temporary file for the received database
        let temp_file = tempfile::NamedTempFile::new()?;
        std::fs::write(temp_file.path(), &data)?;

        // Determine if we should filter secrets
        let filter_secrets = !options.no_secret_filter.unwrap_or(false);

        // Use the merge function with optional secret filtering.
        // SSH/stdin-stdout sync streams a full DB snapshot each time, so we
        // don't apply a watermark here -- pass None for full-scan behavior.
        let stats = Self::merge_database_from_file_filtered(
            conn,
            temp_file.path().to_path_buf(),
            filter_secrets,
            None,
        )?;
        let other_count = stats.considered;
        let added_count = stats.added;
        let filtered_count = stats.filtered;

        // Get current hostname and database path
        let current_hostname = pxh::get_hostname();
        let current_db_path =
            conn.path().map(|p| p.to_string()).unwrap_or_else(|| "in-memory".to_string());

        let mut msg = format!(
            "{current_hostname}: Merged into {current_db_path} considered {other_count} entries, added {added_count} entries"
        );
        if filtered_count > 0 {
            msg.push_str(&format!(", filtered {filtered_count} potential secrets"));
        }
        eprintln!("{msg}");
        Ok(())
    }
}

// Helper trait for any command that may want to render a list of
// commands during execution.
trait PrintableCommand {
    fn verbose(&self) -> bool;
    fn suppress_headers(&self) -> bool;
    fn display_limit(&self) -> usize;
    fn failed(&self) -> bool;

    fn extra_filter_step(
        &self,
        rows: Vec<pxh::Invocation>,
    ) -> Result<Vec<pxh::Invocation>, Box<dyn std::error::Error>>;

    fn present_results(&self, conn: &Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Now that we have the relevant rows, just present the output
        let mut stmt = conn.prepare(
	r#"
SELECT session_id, full_command, shellname, working_directory, hostname, username, exit_status, start_unix_timestamp, end_unix_timestamp
  FROM memdb.show_results sr, command_history h
 WHERE sr.ch_rowid = h.rowid
ORDER BY ch_start_unix_timestamp DESC, ch_id DESC
"#)?;

        let rows: Result<Vec<pxh::Invocation>, _> =
            stmt.query_map([], pxh::Invocation::from_row)?.collect();
        let rows = self.extra_filter_step(rows?)?;
        let fields: &[&str] = if self.verbose() {
            &["start_time", "duration", "session", "context", "status", "command"]
        } else if self.failed() {
            &["start_time", "status", "command"]
        } else {
            &["start_time", "command"]
        };
        pxh::present_results_human_readable(fields, &rows, self.suppress_headers())?;
        Ok(())
    }
}

impl PrintableCommand for ScrubCommand {
    fn verbose(&self) -> bool {
        false
    }

    fn suppress_headers(&self) -> bool {
        false
    }

    fn display_limit(&self) -> usize {
        0
    }

    fn failed(&self) -> bool {
        false
    }

    fn extra_filter_step(
        &self,
        rows: Vec<pxh::Invocation>,
    ) -> Result<Vec<pxh::Invocation>, Box<dyn std::error::Error>> {
        Ok(rows)
    }
}

impl ScrubCommand {
    fn go(&self, conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        // Handle directory mode
        if let Some(ref dir) = self.dir {
            return self.go_dir_mode(dir);
        }

        // Handle remote mode
        if let Some(ref remote) = self.remote {
            return self.go_remote_mode(remote);
        }

        // Default: local database mode
        if self.scan { self.go_scan_mode(&conn) } else { self.go_interactive_mode(conn) }
    }

    fn go_dir_mode(&self, dir: &Path) -> Result<(), Box<dyn std::error::Error>> {
        if !dir.exists() {
            return Err(format!("Directory does not exist: {}", dir.display()).into());
        }

        // Build patterns once before the loop for efficiency
        let (patterns, regex_set) = if self.scan {
            let result = build_secret_patterns(self.confidence.as_str())?;
            if result.0.is_empty() {
                println!(
                    "No patterns available for confidence level '{}'.",
                    self.confidence.as_str()
                );
                return Ok(());
            }
            result
        } else if self.contraband.as_deref().is_none_or(str::is_empty) {
            return Err("Directory mode requires --scan or a non-empty contraband pattern".into());
        } else {
            (vec![], regex::bytes::RegexSet::empty())
        };

        let db_extension = OsStr::new("db");
        let mut entries: Vec<_> = Vec::new();
        let mut skipped_entries = 0usize;
        for entry_result in fs::read_dir(dir)? {
            match entry_result {
                Ok(entry) => {
                    if entry.path().extension() == Some(db_extension) {
                        entries.push(entry);
                    }
                }
                Err(e) => {
                    eprintln!("Warning: Failed to read directory entry: {e}");
                    skipped_entries += 1;
                }
            }
        }

        if entries.is_empty() {
            println!("No .db files found in {}", dir.display());
            return Ok(());
        }

        println!("Found {} database files in {}", entries.len(), dir.display());

        // First pass: count total matches for confirmation
        let mut total_matches = 0;
        let mut skipped_files = 0usize;
        let mut file_match_counts: Vec<(PathBuf, Vec<ScanMatch>)> = Vec::new();

        for entry in &entries {
            let path = entry.path();
            print!("Scanning {}...", path.display());

            let conn = match Connection::open(&path) {
                Ok(c) => c,
                Err(e) => {
                    eprintln!(" failed to open: {e}");
                    skipped_files += 1;
                    continue;
                }
            };

            // Validate it's a pxh database
            let is_pxh = conn
                .query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='command_history'",
                    [],
                    |r| r.get::<_, i64>(0),
                )
                .map(|n| n > 0)
                .unwrap_or(false);
            if !is_pxh {
                eprintln!(" skipped (not a pxh database)");
                skipped_files += 1;
                continue;
            }

            let mut matches: Vec<ScanMatch> = Vec::new();
            if self.scan {
                scan_database(&conn, &regex_set, &patterns, &mut matches, usize::MAX)?;
            } else if let Some(ref contraband) = self.contraband {
                let mut stmt = conn.prepare(
                    "SELECT rowid, full_command, start_unix_timestamp, working_directory FROM command_history WHERE INSTR(full_command, ?) > 0"
                )?;
                let mut rows = stmt.query([contraband.as_bytes()])?;
                while let Some(row) = rows.next()? {
                    matches.push(ScanMatch {
                        rowid: row.get(0)?,
                        command: String::from_utf8_lossy(&row.get::<_, Vec<u8>>(1)?).to_string(),
                        pattern: "manual".to_string(),
                        timestamp: row.get(2)?,
                        working_directory: row
                            .get::<_, Option<Vec<u8>>>(3)?
                            .map(|v| String::from_utf8_lossy(&v).to_string()),
                        original_line: None,
                    });
                }
            }

            println!(" {} entries", matches.len());
            total_matches += matches.len();
            if !matches.is_empty() {
                file_match_counts.push((path, matches));
            }
        }

        // Report skipped files before proceeding
        let total_skipped = skipped_entries + skipped_files;
        if total_skipped > 0 {
            eprintln!(
                "WARNING: {} file(s) could not be processed. Results may be incomplete.",
                total_skipped
            );
        }

        if total_matches == 0 {
            println!("\nNo entries found to scrub.");
            if total_skipped > 0 {
                return Err(format!(
                    "Scrub incomplete: {} file(s) could not be processed",
                    total_skipped
                )
                .into());
            }
            return Ok(());
        }

        println!("\nFound {} entries across {} file(s).", total_matches, file_match_counts.len());

        if self.dry_run {
            println!("Dry-run mode: no changes made.");
            if total_skipped > 0 {
                return Err(format!(
                    "Scrub incomplete: {} file(s) could not be processed",
                    total_skipped
                )
                .into());
            }
            return Ok(());
        }

        // Confirmation prompt (unless --yes is specified)
        if !self.yes {
            print!("Proceed with scrubbing? [y/N] ");
            io::stdout().flush()?;
            let mut input = String::new();
            io::stdin().read_line(&mut input)?;
            if !input.trim().eq_ignore_ascii_case("y") {
                println!("Aborted.");
                return Ok(());
            }
        }

        // Second pass: actually scrub
        let mut total_scrubbed = 0;
        let mut files_modified = 0;

        for (path, matches) in file_match_counts {
            let conn = Connection::open(&path)?;
            let count = scrub_from_database(&conn, &matches)?;
            total_scrubbed += count;
            files_modified += 1;
        }

        println!("Scrubbed {} entries from {} file(s).", total_scrubbed, files_modified);
        if total_scrubbed > 0 {
            eprintln!("Hint: run `pxh maintenance` to VACUUM and reclaim disk space.");
        }

        if total_skipped > 0 {
            return Err(format!(
                "Scrub incomplete: {} file(s) could not be processed",
                total_skipped
            )
            .into());
        }

        Ok(())
    }

    fn go_remote_mode(&self, remote: &str) -> Result<(), Box<dyn std::error::Error>> {
        // Validate inputs early - fail fast before establishing SSH connection
        if !self.scan && self.contraband.is_none() {
            return Err("Remote scrub requires --scan or a contraband pattern".into());
        }
        if let Some(c) = &self.contraband
            && c.is_empty()
        {
            return Err("String to scrub must be non-empty; aborting.".into());
        }
        if !self.dry_run && !self.yes && !prompt_for_confirmation()? {
            println!("Aborted.");
            return Ok(());
        }

        // Build the scrub options to send via the v2 protocol
        let scrub_pattern = if self.scan {
            None // Will use secret detection on remote
        } else {
            self.contraband.clone()
        };

        let configured_path = self.remote_pxh.as_deref().unwrap_or("pxh");

        let remote_db_path = self
            .remote_db
            .as_ref()
            .map_or_else(pxh::helpers::default_remote_db_expr, |p| p.display().to_string());

        // Parse SSH command
        let (ssh_cmd, ssh_args) = pxh::helpers::parse_ssh_command(&self.ssh_cmd);

        // Build remote command - v2 protocol for scrub
        let pxh_args = format!("--db {remote_db_path} sync --server");
        let remote_command = pxh::helpers::build_remote_pxh_command(configured_path, &pxh_args);

        let mut cmd = std::process::Command::new(&ssh_cmd);
        cmd.args(&ssh_args)
            .arg(remote)
            .arg(&remote_command)
            .stdin(std::process::Stdio::piped())
            .stdout(std::process::Stdio::piped())
            .stderr(std::process::Stdio::inherit());

        let mut child = cmd.spawn().map_err(|e| format!("Failed to spawn SSH command: {e}"))?;

        let mut stdin_writer = child.stdin.take().ok_or("Failed to get stdin from SSH process")?;
        let mut stdout_reader =
            child.stdout.take().ok_or("Failed to get stdout from SSH process")?;

        // Send scrub-v2 mode with options
        let options = SyncOptions {
            scrub: scrub_pattern,
            scrub_scan: if self.scan { Some(true) } else { None },
            scrub_confidence: if self.scan {
                Some(self.confidence.as_str().to_string())
            } else {
                None
            },
            scrub_dry_run: if self.dry_run { Some(true) } else { None },
            no_secret_filter: None,
        };

        stdin_writer.write_all(b"scrub-v2\n")?;
        let options_json = serde_json::to_string(&options)?;
        stdin_writer.write_all(options_json.as_bytes())?;
        stdin_writer.write_all(b"\n")?;
        stdin_writer.flush()?;

        // Close stdin to signal we're done
        drop(stdin_writer);

        // Read response from remote
        let mut response = String::new();
        stdout_reader.read_to_string(&mut response)?;

        let status = child.wait()?;
        if !status.success() {
            return Err(format!("Remote scrub failed: {}", response).into());
        }

        println!("{}", response.trim());
        Ok(())
    }

    fn go_scan_mode(&self, conn: &Connection) -> Result<(), Box<dyn std::error::Error>> {
        let (patterns, regex_set) = build_secret_patterns(self.confidence.as_str())?;

        if patterns.is_empty() {
            println!("No patterns available.");
            return Ok(());
        }

        let mut matches: Vec<ScanMatch> = Vec::new();

        if let Some(ref histfile) = self.histfile {
            let content = fs::read(histfile)?;
            let shellname = self.shellname.clone().unwrap_or_else(|| detect_shell_format(&content));
            scan_histfile(&content, &shellname, &regex_set, &patterns, &mut matches, usize::MAX)?;
        } else {
            scan_database(conn, &regex_set, &patterns, &mut matches, usize::MAX)?;
        }

        if matches.is_empty() {
            println!("No potential secrets found. Nothing to scrub.");
            return Ok(());
        }

        println!("Found {} potential secret(s) to scrub:\n", matches.len());
        ScanCommand::display_matches(&matches, false);

        if self.dry_run {
            println!("Dry-run mode: no changes made.");
            return Ok(());
        }

        if !self.yes && !prompt_for_confirmation()? {
            println!("Aborted.");
            return Ok(());
        }

        if let Some(ref histfile) = self.histfile {
            let count = scrub_from_histfile(histfile, &matches)?;
            println!("Scrubbed {} entries from {}.", count, histfile.display());
        } else {
            let count = scrub_from_database(conn, &matches)?;
            println!("Scrubbed {} entries from database.", count);
            if count > 0 {
                eprintln!("Hint: run `pxh maintenance` to VACUUM and reclaim disk space.");
            }
        }

        Ok(())
    }

    fn go_interactive_mode(&self, mut conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        if self.histfile.is_some() && self.contraband.is_none() {
            return Err(
                "Interactive mode with --histfile requires specifying the string to scrub".into()
            );
        }

        let contraband = match &self.contraband {
            Some(value) => {
                println!(
                    "WARNING: specifying the contraband on the command line is inherently risky; prefer not specifying it\n"
                );
                value.clone()
            }
            None => {
                let mut input = String::new();
                print!("String to scrub: ");
                std::io::stdout().flush()?;
                io::stdin().read_line(&mut input)?;
                input.trim_end().into()
            }
        };

        if contraband.is_empty() {
            println!(); // newline after input prompt in case user hit ctrl-D
            return Err(String::from("String to scrub must be non-empty; aborting.").into());
        }

        conn.execute("DELETE FROM memdb.show_results", ())?;
        conn.execute(
            r#"
INSERT INTO memdb.show_results (ch_rowid, ch_start_unix_timestamp, ch_id)
SELECT rowid, start_unix_timestamp, id
  FROM command_history h
 WHERE INSTR(full_command, ?) > 0
ORDER BY start_unix_timestamp DESC, id DESC"#,
            (&contraband,),
        )?;
        println!("Entries to scrub from pxh database...\n");
        self.present_results(&conn)?;

        if self.dry_run {
            println!("\nDry-run, no entries scrubbed.");
            return Ok(());
        }

        if !self.yes && !prompt_for_confirmation()? {
            println!("Aborted.");
            return Ok(());
        }

        let tx = conn.transaction()?;
        tx.execute(
            "DELETE FROM command_history WHERE rowid IN (SELECT ch_rowid FROM memdb.show_results)",
            (),
        )?;
        tx.commit()?;

        if let Some(ref histfile) = self.histfile {
            pxh::atomically_remove_lines_from_file(histfile, &contraband)?;
            println!("\nEntries scrubbed from database and {}.", histfile.display());
        } else {
            println!("\nEntries scrubbed from database.");
        }
        Ok(())
    }
}

// Show history in two steps:
//   1. Populate memdb.show_results with the relevant rows to show
//   2. Invoke present_results to actually show them.
//
// This two step dance is preparatory work for `--here` and `--around`
// queries where we need some arbitrary subset of selected entries.
// Rather than round-trip the rowid's and create a complex query, we
// just use a temp memory table.
impl PrintableCommand for ShowCommand {
    fn extra_filter_step(
        &self,
        rows: Vec<pxh::Invocation>,
    ) -> Result<Vec<pxh::Invocation>, Box<dyn std::error::Error>> {
        let regexes: Result<Vec<Regex>, _> = self
            .patterns
            .iter()
            .skip(1)
            .map(|s| {
                regex::bytes::RegexBuilder::new(s.as_str())
                    .case_insensitive(self.ignore_case)
                    .size_limit(REGEX_SIZE_LIMIT_DEFAULT)
                    .build()
            })
            .collect();
        let regexes = regexes?;
        Ok(rows
            .into_iter()
            .filter(|row| match_all_regexes(row, &regexes))
            .rev()
            .take(self.display_limit())
            .collect())
    }

    fn verbose(&self) -> bool {
        self.verbose
    }

    fn suppress_headers(&self) -> bool {
        self.suppress_headers
    }
    fn display_limit(&self) -> usize {
        self.limit
    }
    fn failed(&self) -> bool {
        self.failed
    }
}

impl ShowCommand {
    /// SQL-level LIMIT: may be larger than display_limit for --loosen,
    /// which needs all candidates before client-side filtering.
    fn query_limit(&self) -> usize {
        self.sql_limit.unwrap_or(self.limit)
    }
}

impl ShowCommand {
    fn go(&self, conn: Connection) -> Result<(), Box<dyn std::error::Error>> {
        // If we are loosening then just use the first string for the
        // sqlite query.  This requires fetching all matches, however,
        // to properly limit the final count.
        let pattern = if self.loosen {
            self.patterns.first().map_or_else(String::default, String::clone)
        } else {
            self.patterns.iter().map(|p| format!("(?:{p})")).collect::<Vec<_>>().join(".*\\s.*")
        };

        let pattern = if self.ignore_case { format!("(?i){pattern}") } else { pattern };

        conn.execute("DELETE FROM memdb.show_results", ())?;

        let here = self.here || self.working_directory.is_some();
        let failed_clause =
            if self.failed { " AND exit_status IS NOT NULL AND exit_status != 0" } else { "" };

        let working_directory = self.working_directory.as_ref().map_or_else(
            || {
                env::var_os("PWD")
                    .map(PathBuf::from)
                    .or_else(|| env::current_dir().ok())
                    .unwrap_or_default()
            },
            |v| v.clone(),
        );

        if let Some(ref maybe_session) = self.session {
            let session_id: i64 = match maybe_session.as_str() {
                "current" => {
                    let val = env::var("PXH_SESSION_ID").map_err(|_| {
                        "PXH_SESSION_ID not set; are you in a pxh-enabled shell?"
                    })?;
                    val.parse::<i64>()?
                }
                "last" => conn.query_row(
                    "SELECT session_id FROM command_history ORDER BY start_unix_timestamp DESC, id DESC LIMIT 1",
                    [],
                    |r| r.get(0),
                )?,
                id => id.parse::<i64>()?,
            };

            conn.execute(
                &format!(
                    r#"
INSERT INTO memdb.show_results (ch_rowid, ch_start_unix_timestamp, ch_id)
SELECT rowid, start_unix_timestamp, id
  FROM command_history h
 WHERE full_command REGEXP ? AND session_id = ?{failed_clause}
ORDER BY start_unix_timestamp DESC, id DESC
LIMIT ?"#
                ),
                (pattern, session_id, self.query_limit() as i64),
            )?;
        } else if here {
            conn.execute(
                &format!(
                    r#"
INSERT INTO memdb.show_results (ch_rowid, ch_start_unix_timestamp, ch_id)
SELECT rowid, start_unix_timestamp, id
  FROM command_history h
 WHERE working_directory = CAST(? as blob)
   AND full_command REGEXP ?{failed_clause}
ORDER BY start_unix_timestamp DESC, id DESC
LIMIT ?"#
                ),
                (working_directory.as_os_str().as_bytes(), pattern, self.query_limit() as i64),
            )?;
        } else {
            conn.execute(
                &format!(
                    r#"
INSERT INTO memdb.show_results (ch_rowid, ch_start_unix_timestamp, ch_id)
SELECT rowid, start_unix_timestamp, id
  FROM command_history h
 WHERE full_command REGEXP ?{failed_clause}
ORDER BY start_unix_timestamp DESC, id DESC
LIMIT ?"#
                ),
                (pattern, self.query_limit() as i64),
            )?;
        }

        self.present_results(&conn)
    }
}

fn match_all_regexes(row: &pxh::Invocation, regexes: &[Regex]) -> bool {
    regexes.iter().all(|regex| regex.is_match(row.command.as_slice()))
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Reset SIGPIPE to default OS behavior so piping to head/grep exits cleanly
    // instead of producing a BrokenPipe error.
    unsafe {
        libc::signal(libc::SIGPIPE, libc::SIG_DFL);
    }

    env_logger::init();

    // Check if binary was invoked as "pxhs", which is a shorthand for "pxh show"
    let args_vec = env::args_os().collect::<Vec<_>>();

    // Check if the executable name contains "pxhs" (handles both direct calls and symlinks)
    let is_pxhs = pxh::helpers::determine_is_pxhs(&args_vec);

    let mut args = if is_pxhs {
        // When invoked as pxhs, always transform to "pxh show ..."
        let program = args_vec[0].clone(); // Get program name
        let rest = args_vec.iter().skip(1).cloned(); // Get remaining args

        // Build new args with "show" inserted after program name
        let combined_args = std::iter::once(program)
            .chain(std::iter::once(OsString::from("show")))
            .chain(rest)
            .collect::<Vec<_>>();

        PxhArgs::parse_from(combined_args)
    } else {
        PxhArgs::parse()
    };

    if args.db.is_none() {
        args.db = pxh::default_db_path();
    }

    let Some(command) = args.command.as_mut() else {
        PxhArgs::command().print_help()?;
        return Ok(());
    };
    let make_conn = || pxh::sqlite_connection(&args.db);
    let make_conn_full = || pxh::sqlite_connection_full(&args.db);
    match command {
        Commands::ShellConfig(cmd) => {
            cmd.go()?;
        }
        Commands::Install(cmd) => {
            cmd.go()?;
            // Migrate host settings on install (not on every command)
            if let Ok(conn) = make_conn() {
                pxh::migrate_host_settings(&conn);
            }
        }
        Commands::Completions(cmd) => {
            clap_complete::generate(cmd.shell, &mut PxhArgs::command(), "pxh", &mut io::stdout());
        }
        Commands::Config(cmd) => {
            // Migrate host settings before editing config
            if let Ok(conn) = make_conn() {
                pxh::migrate_host_settings(&conn);
            }
            cmd.go()?;
        }
        Commands::Import(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Export(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Show(cmd) => {
            if cmd.limit == 0 {
                cmd.limit = i32::MAX as usize;
            }
            if cmd.loosen {
                cmd.sql_limit = Some(i32::MAX as usize);
            }
            cmd.go(make_conn_full()?)?;
        }
        Commands::Scrub(cmd) => {
            cmd.go(make_conn_full()?)?;
        }
        Commands::Seal(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Sync(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Maintenance(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Scan(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Recall(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Autosuggest(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Stats(cmd) => {
            cmd.go(make_conn()?)?;
        }
        Commands::Insert(cmd) => {
            // Load config before make_conn() since migrate_host_settings
            // (called during connection setup) may modify the config file
            let config = pxh::recall::config::Config::load();
            let mut conn = make_conn()?;

            // Check if command matches any ignore pattern
            let command_str = cmd.command.join(OsStr::new(" "));
            let command_lossy = command_str.to_string_lossy();
            if !config.history.ignore_patterns.is_empty()
                && let Ok(set) = regex::RegexSet::new(&config.history.ignore_patterns)
                && set.is_match(&command_lossy)
            {
                return Ok(());
            }

            let invocation = pxh::Invocation {
                command: command_str.as_bytes().into(),
                shellname: cmd.shellname.clone(),
                working_directory: cmd
                    .working_directory
                    .as_ref()
                    .map(|v| BString::from(v.as_path().as_os_str().as_bytes())),
                hostname: Some(BString::from(cmd.hostname.clone().into_vec())),
                username: Some(BString::from(cmd.username.clone().into_vec())),
                exit_status: cmd.exit_status,
                start_unix_timestamp: cmd.start_unix_timestamp,
                end_unix_timestamp: cmd.end_unix_timestamp,
                session_id: cmd.session_id,
                machine_id: config.host.machine_id,
            };
            // Short busy_timeout: let our own jittered retry loop handle contention
            // so a single waiter can't burn the full timeout while others slip past.
            conn.busy_timeout(Duration::from_millis(100))?;
            pxh::with_write_retry(&mut conn, Duration::from_secs(1), |tx| invocation.insert(tx))?;
        }
        Commands::Doctor(cmd) => {
            let conn = make_conn().ok();
            cmd.go(conn, &args.db)?;
        }
    }
    Ok(())
}