tuible 0.0.2-alpha.1

use std::cmp::Ordering;
use std::collections::BTreeMap;
use std::time::Instant;

use ratatui::widgets::{ListState, TableState};

use crate::config::Config;
use crate::db::model::{Column, Row, SchemaColumn, TablePage, Value};
use crate::history::{QueryHistory, QueryHistoryStore};
use crate::operation::OperationId;

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum Panel {
    Tables,
    Grid,
    Sql,
    Inspector,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum DataTab {
    #[default]
    Rows,
    Schema,
}

impl DataTab {
    pub fn toggle(&mut self) {
        *self = match self {
            DataTab::Rows => DataTab::Schema,
            DataTab::Schema => DataTab::Rows,
        };
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum RecordTab {
    #[default]
    Fields,
    Json,
}

impl RecordTab {
    pub fn toggle(&mut self) {
        *self = match self {
            RecordTab::Fields => RecordTab::Json,
            RecordTab::Json => RecordTab::Fields,
        };
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum EditorTab {
    #[default]
    Filters,
    Sql,
}

impl EditorTab {
    pub fn next(self) -> Self {
        match self {
            EditorTab::Filters => EditorTab::Sql,
            EditorTab::Sql => EditorTab::Filters,
        }
    }

    pub fn previous(self) -> Self {
        self.next()
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum SqlMode {
    #[default]
    Normal,
    Insert,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum Visual {
    #[default]
    None,
    Rows {
        anchor: usize,
    },
    Column,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum SortDirection {
    Ascending,
    Descending,
}

impl SortDirection {
    pub fn label(self) -> &'static str {
        match self {
            SortDirection::Ascending => "ascending",
            SortDirection::Descending => "descending",
        }
    }

    pub fn indicator(self) -> &'static str {
        match self {
            SortDirection::Ascending => "ASC",
            SortDirection::Descending => "DESC",
        }
    }
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct GridSort {
    pub column: usize,
    pub column_name: String,
    pub direction: SortDirection,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum EditTarget {
    Cell,
    RowLimit,
    ColWidth,
    ForegroundTimeout,
}

#[derive(Debug, Clone, PartialEq)]
pub struct CellEditIdentity {
    pub table: String,
    pub rowid: i64,
    pub column: String,
    pub selected_row: usize,
    pub selected_col: usize,
    pub original: Value,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum CopyFormat {
    Csv,
    Tsv,
    Json,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum FilterOperator {
    #[default]
    Equal,
    NotEqual,
    Contains,
    NotContains,
    In,
    NotIn,
    Greater,
    GreaterOrEqual,
    Less,
    LessOrEqual,
    IsNull,
    IsNotNull,
}

impl FilterOperator {
    pub fn next(self) -> Self {
        match self {
            FilterOperator::Equal => FilterOperator::NotEqual,
            FilterOperator::NotEqual => FilterOperator::Contains,
            FilterOperator::Contains => FilterOperator::NotContains,
            FilterOperator::NotContains => FilterOperator::In,
            FilterOperator::In => FilterOperator::NotIn,
            FilterOperator::NotIn => FilterOperator::Greater,
            FilterOperator::Greater => FilterOperator::GreaterOrEqual,
            FilterOperator::GreaterOrEqual => FilterOperator::Less,
            FilterOperator::Less => FilterOperator::LessOrEqual,
            FilterOperator::LessOrEqual => FilterOperator::IsNull,
            FilterOperator::IsNull => FilterOperator::IsNotNull,
            FilterOperator::IsNotNull => FilterOperator::Equal,
        }
    }

    pub fn previous(self) -> Self {
        match self {
            FilterOperator::Equal => FilterOperator::IsNotNull,
            FilterOperator::NotEqual => FilterOperator::Equal,
            FilterOperator::Contains => FilterOperator::NotEqual,
            FilterOperator::NotContains => FilterOperator::Contains,
            FilterOperator::In => FilterOperator::NotContains,
            FilterOperator::NotIn => FilterOperator::In,
            FilterOperator::Greater => FilterOperator::NotIn,
            FilterOperator::GreaterOrEqual => FilterOperator::Greater,
            FilterOperator::Less => FilterOperator::GreaterOrEqual,
            FilterOperator::LessOrEqual => FilterOperator::Less,
            FilterOperator::IsNull => FilterOperator::LessOrEqual,
            FilterOperator::IsNotNull => FilterOperator::IsNull,
        }
    }

    pub fn label(self) -> &'static str {
        match self {
            FilterOperator::Equal => "equals",
            FilterOperator::NotEqual => "not equal",
            FilterOperator::Contains => "contains",
            FilterOperator::NotContains => "not contains",
            FilterOperator::In => "in",
            FilterOperator::NotIn => "not in",
            FilterOperator::Greater => "greater than",
            FilterOperator::GreaterOrEqual => "greater or equal",
            FilterOperator::Less => "less than",
            FilterOperator::LessOrEqual => "less or equal",
            FilterOperator::IsNull => "is null",
            FilterOperator::IsNotNull => "is not null",
        }
    }

    pub fn needs_value(self) -> bool {
        !matches!(self, FilterOperator::IsNull | FilterOperator::IsNotNull)
    }
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct FilterRow {
    pub enabled: bool,
    pub column_index: usize,
    pub column_name: Option<String>,
    pub column_type: Option<FilterColumnType>,
    pub operator: FilterOperator,
    pub value: String,
}

impl Default for FilterRow {
    fn default() -> Self {
        Self {
            enabled: true,
            column_index: 0,
            column_name: None,
            column_type: None,
            operator: FilterOperator::default(),
            value: String::new(),
        }
    }
}

impl FilterRow {
    pub fn set_column(&mut self, index: usize, name: String) {
        self.column_index = index;
        self.column_name = Some(name);
        self.column_type = None;
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FilterColumnType {
    Number,
    Boolean,
    Text,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum FilterField {
    #[default]
    Enabled,
    Column,
    Operator,
    Value,
    Apply,
    Remove,
    Add,
}

impl FilterField {
    pub fn next(self) -> Self {
        match self {
            FilterField::Enabled => FilterField::Column,
            FilterField::Column => FilterField::Operator,
            FilterField::Operator => FilterField::Value,
            FilterField::Value => FilterField::Apply,
            FilterField::Apply => FilterField::Remove,
            FilterField::Remove => FilterField::Add,
            FilterField::Add => FilterField::Enabled,
        }
    }

    pub fn previous(self) -> Self {
        match self {
            FilterField::Enabled => FilterField::Add,
            FilterField::Column => FilterField::Enabled,
            FilterField::Operator => FilterField::Column,
            FilterField::Value => FilterField::Operator,
            FilterField::Apply => FilterField::Value,
            FilterField::Remove => FilterField::Apply,
            FilterField::Add => FilterField::Remove,
        }
    }
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct FilterState {
    rows: Vec<FilterRow>,
    selected_row: usize,
    selected_field: FilterField,
}

impl Default for FilterState {
    fn default() -> Self {
        Self {
            rows: vec![FilterRow::default()],
            selected_row: 0,
            selected_field: FilterField::Enabled,
        }
    }
}

impl FilterState {
    pub fn rows(&self) -> &[FilterRow] {
        &self.rows
    }

    pub fn selected_row_index(&self) -> usize {
        self.selected_row
    }

    pub fn selected_field(&self) -> FilterField {
        self.selected_field
    }

    pub fn selected_row(&self) -> &FilterRow {
        &self.rows[self.selected_row]
    }

    pub fn selected_row_mut(&mut self) -> &mut FilterRow {
        &mut self.rows[self.selected_row]
    }

    pub fn select_row(&mut self, index: usize) {
        self.selected_row = index.min(self.rows.len() - 1);
    }

    pub fn next_row(&mut self) {
        self.select_row(self.selected_row.saturating_add(1));
    }

    pub fn previous_row(&mut self) {
        self.selected_row = self.selected_row.saturating_sub(1);
    }

    pub fn select_field(&mut self, field: FilterField) {
        self.selected_field = field;
    }

    pub fn next_field(&mut self) {
        self.selected_field = self.selected_field.next();
    }

    pub fn previous_field(&mut self) {
        self.selected_field = self.selected_field.previous();
    }

    pub fn add_row(&mut self) {
        self.rows.push(FilterRow::default());
        self.selected_row = self.rows.len() - 1;
        self.selected_field = FilterField::Enabled;
    }

    pub fn remove_selected_row(&mut self) {
        if self.rows.len() == 1 {
            self.rows[0] = FilterRow::default();
            self.selected_field = FilterField::Enabled;
        } else {
            self.rows.remove(self.selected_row);
            self.selected_row = self.selected_row.min(self.rows.len() - 1);
        }
    }

    pub fn clamp_columns(&mut self, column_count: usize) {
        let last_column = column_count.saturating_sub(1);
        for row in &mut self.rows {
            row.column_index = row.column_index.min(last_column);
        }
    }

    pub fn remap_columns(&mut self, previous: &[Column], current: &[Column]) {
        for row in &mut self.rows {
            let column_name = row
                .column_name
                .clone()
                .or_else(|| {
                    previous
                        .get(row.column_index)
                        .map(|column| column.name.clone())
                })
                .or_else(|| {
                    current
                        .get(row.column_index)
                        .map(|column| column.name.clone())
                });
            row.column_name.clone_from(&column_name);
            row.column_index = column_name
                .as_ref()
                .and_then(|name| current.iter().position(|column| column.name.eq(name)))
                .unwrap_or_else(|| row.column_index.min(current.len().saturating_sub(1)));
        }
    }

    pub fn capture_column_types(&mut self, columns: &[Column], rows: &[Row]) {
        for filter in &mut self.rows {
            let Some(column_name) = filter.column_name.as_deref() else {
                continue;
            };
            let Some(index) = columns.iter().position(|column| column.name == column_name) else {
                continue;
            };
            let Some(value) = rows
                .iter()
                .filter_map(|row| row.values.get(index))
                .find(|value| !matches!(value, Value::Null))
            else {
                continue;
            };
            filter.column_type = Some(match value {
                Value::Int(_) | Value::Float(_) | Value::Decimal(_) => FilterColumnType::Number,
                Value::Bool(_) => FilterColumnType::Boolean,
                Value::Null | Value::Text(_) | Value::Bytes(_) | Value::Json(_) => {
                    FilterColumnType::Text
                }
            });
        }
    }

    pub fn active_rows(&self) -> impl Iterator<Item = &FilterRow> {
        self.rows.iter().filter(|row| row.enabled)
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum CompletionKind {
    Keyword,
    Table,
    Column,
    Function,
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct CompletionItem {
    pub value: String,
    pub label: String,
    pub detail: String,
    pub kind: CompletionKind,
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct CompletionMenu {
    pub items: Vec<CompletionItem>,
    pub selected: usize,
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct HistorySearch {
    pub query: String,
    pub matches: Vec<String>,
    pub selected: usize,
}

impl HistorySearch {
    fn new(history: &QueryHistory) -> Self {
        let mut search = Self {
            query: String::new(),
            matches: Vec::new(),
            selected: 0,
        };
        search.refresh(history);
        search
    }

    fn refresh(&mut self, history: &QueryHistory) {
        self.matches = history
            .entries()
            .iter()
            .rev()
            .filter(|statement| is_subsequence_match(statement, &self.query))
            .cloned()
            .collect();
        self.selected = self.selected.min(self.matches.len().saturating_sub(1));
    }
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ActiveFilter {
    pub table: String,
    pub sql: String,
}

impl CopyFormat {
    pub fn next(self) -> Self {
        match self {
            CopyFormat::Csv => CopyFormat::Tsv,
            CopyFormat::Tsv => CopyFormat::Json,
            CopyFormat::Json => CopyFormat::Csv,
        }
    }

    pub fn previous(self) -> Self {
        match self {
            CopyFormat::Csv => CopyFormat::Json,
            CopyFormat::Tsv => CopyFormat::Csv,
            CopyFormat::Json => CopyFormat::Tsv,
        }
    }

    pub fn label(self) -> &'static str {
        match self {
            CopyFormat::Csv => "CSV",
            CopyFormat::Tsv => "TSV",
            CopyFormat::Json => "JSON",
        }
    }
}

#[derive(Debug, Default, PartialEq)]
pub struct InputState {
    pub text: String,
    pub cursor: usize,
}

impl InputState {
    pub fn insert(&mut self, character: char) {
        let byte = byte_index(&self.text, self.cursor);
        self.text.insert(byte, character);
        self.cursor += 1;
    }

    pub fn insert_str(&mut self, value: &str) {
        let byte = byte_index(&self.text, self.cursor);
        self.text.insert_str(byte, value);
        self.cursor += value.chars().count();
    }

    pub fn backspace(&mut self) {
        if self.cursor == 0 {
            return;
        }
        let start = byte_index(&self.text, self.cursor - 1);
        let end = byte_index(&self.text, self.cursor);
        self.text.replace_range(start..end, "");
        self.cursor -= 1;
    }

    pub fn move_left(&mut self) {
        self.cursor = self.cursor.saturating_sub(1);
    }

    pub fn move_right(&mut self) {
        self.cursor = (self.cursor + 1).min(self.text.chars().count());
    }

    pub fn move_up(&mut self) {
        self.move_vertical(-1);
    }

    pub fn move_down(&mut self) {
        self.move_vertical(1);
    }

    pub fn move_home(&mut self) {
        let (line, _) = self.line_col();
        self.cursor = line_ranges(&self.text)[line].0;
    }

    pub fn move_end(&mut self) {
        let (line, _) = self.line_col();
        self.cursor = line_ranges(&self.text)[line].1;
    }

    pub fn line_col(&self) -> (usize, usize) {
        let before: String = self.text.chars().take(self.cursor).collect();
        let line = before
            .chars()
            .filter(|character| *character == '\n')
            .count();
        let column = before
            .chars()
            .rev()
            .take_while(|character| *character != '\n')
            .count();
        (line, column)
    }

    pub fn current_prefix(&self) -> String {
        self.text
            .chars()
            .take(self.cursor)
            .collect::<String>()
            .chars()
            .rev()
            .take_while(|character| character.is_alphanumeric() || *character == '_')
            .collect::<String>()
            .chars()
            .rev()
            .collect()
    }

    pub fn accept_completion(&mut self, completion: &str) {
        let prefix_len = self.current_prefix().chars().count();
        let mut start = self.cursor.saturating_sub(prefix_len);
        let mut end = self.cursor;
        if completion.starts_with('"') && completion.ends_with('"') {
            if start > 0 && self.text.chars().nth(start - 1) == Some('"') {
                start -= 1;
            }
            if self.text.chars().nth(end) == Some('"') {
                end += 1;
            }
        }
        let byte_start = byte_index(&self.text, start);
        let byte_end = byte_index(&self.text, end);
        self.text.replace_range(byte_start..byte_end, completion);
        self.cursor = start + completion.chars().count();
    }

    pub fn text_with_cursor(&self) -> String {
        let mut text = self.text.clone();
        text.insert(byte_index(&text, self.cursor), '█');
        text
    }

    fn move_vertical(&mut self, direction: isize) {
        let ranges = line_ranges(&self.text);
        let (line, column) = self.line_col();
        let target = line.saturating_add_signed(direction).min(ranges.len() - 1);
        let (start, end) = ranges[target];
        self.cursor = start + column.min(end - start);
    }
}

fn byte_index(text: &str, char_index: usize) -> usize {
    text.char_indices()
        .nth(char_index)
        .map_or(text.len(), |(byte, _)| byte)
}

fn line_ranges(text: &str) -> Vec<(usize, usize)> {
    let mut ranges = Vec::new();
    let mut start = 0;
    for (index, character) in text.chars().enumerate() {
        if character == '\n' {
            ranges.push((start, index));
            start = index + 1;
        }
    }
    ranges.push((start, text.chars().count()));
    ranges
}

#[derive(Debug, PartialEq)]
pub enum Overlay {
    None,
    Help,
    History(HistorySearch),
    Schema {
        table: String,
        columns: Vec<SchemaColumn>,
    },
    Edit {
        text: String,
        target: EditTarget,
        cell: Option<CellEditIdentity>,
    },
    ConfirmSql {
        sql: String,
    },
    Settings {
        selected: usize,
    },
    Value {
        title: String,
        text: String,
    },
    Copy {
        format: CopyFormat,
        headers: bool,
    },
}

#[derive(Debug, Default)]
pub struct GridState {
    pub columns: Vec<Column>,
    pub rows: Vec<Row>,
    pub rowids: Option<Vec<i64>>,
    pub selected_row: usize,
    pub selected_col: usize,
    pub col_offset: usize,
    pub table_state: TableState,
    pub page_offset: usize,
    pub has_more: bool,
    pub visual: Visual,
    pub sort: Option<GridSort>,
    source_order: Vec<usize>,
}

impl GridState {
    fn cycle_sort(&mut self) -> Option<Result<Option<GridSort>, ()>> {
        let column = self.columns.get(self.selected_col)?;
        let next = match &self.sort {
            Some(sort)
                if sort.column == self.selected_col
                    && sort.direction == SortDirection::Ascending =>
            {
                Some(GridSort {
                    column: self.selected_col,
                    column_name: column.name.clone(),
                    direction: SortDirection::Descending,
                })
            }
            Some(sort)
                if sort.column == self.selected_col
                    && sort.direction == SortDirection::Descending =>
            {
                None
            }
            _ => Some(GridSort {
                column: self.selected_col,
                column_name: column.name.clone(),
                direction: SortDirection::Ascending,
            }),
        };
        self.sort.clone_from(&next);
        let reordered = if next.is_some() {
            self.apply_sort()
        } else {
            self.restore_source_order()
        };
        if !reordered {
            self.sort = None;
            return Some(Err(()));
        }
        Some(Ok(next))
    }

    fn apply_sort(&mut self) -> bool {
        let Some(sort) = self.sort.clone() else {
            return true;
        };
        if sort.column >= self.columns.len() {
            return false;
        }
        self.reorder_rows(|left, left_source, right, right_source| {
            let ordering = compare_optional_values(
                left.values.get(sort.column),
                right.values.get(sort.column),
            );
            let ordering = match sort.direction {
                SortDirection::Ascending => ordering,
                SortDirection::Descending => ordering.reverse(),
            };
            ordering.then_with(|| left_source.cmp(&right_source))
        })
    }

    fn restore_source_order(&mut self) -> bool {
        self.reorder_rows(|_, left_source, _, right_source| left_source.cmp(&right_source))
    }

    fn reorder_rows(
        &mut self,
        mut compare: impl FnMut(&Row, usize, &Row, usize) -> Ordering,
    ) -> bool {
        if self.source_order.len() != self.rows.len()
            || self
                .rowids
                .as_ref()
                .is_some_and(|rowids| rowids.len() != self.rows.len())
        {
            return false;
        }

        let rows = std::mem::take(&mut self.rows);
        let source_order = std::mem::take(&mut self.source_order);
        let had_rowids = self.rowids.is_some();
        let mut records: Vec<(Row, Option<i64>, usize)> = match self.rowids.take() {
            Some(rowids) => rows
                .into_iter()
                .zip(rowids)
                .zip(source_order)
                .map(|((row, rowid), source)| (row, Some(rowid), source))
                .collect(),
            None => rows
                .into_iter()
                .zip(source_order)
                .map(|(row, source)| (row, None, source))
                .collect(),
        };
        records.sort_by(|left, right| compare(&left.0, left.2, &right.0, right.2));

        let mut rows = Vec::with_capacity(records.len());
        let mut rowids = Vec::with_capacity(records.len());
        let mut source_order = Vec::with_capacity(records.len());
        for (row, rowid, source) in records {
            rows.push(row);
            if let Some(rowid) = rowid {
                rowids.push(rowid);
            }
            source_order.push(source);
        }
        self.rows = rows;
        self.rowids = had_rowids.then_some(rowids);
        self.source_order = source_order;
        true
    }
}

fn remap_sort(mut sort: GridSort, columns: &[Column]) -> Option<GridSort> {
    let column = columns
        .get(sort.column)
        .filter(|column| column.name == sort.column_name)
        .map(|_| sort.column)
        .or_else(|| {
            columns
                .iter()
                .position(|column| column.name == sort.column_name)
        })?;
    sort.column = column;
    Some(sort)
}

fn compare_optional_values(left: Option<&Value>, right: Option<&Value>) -> Ordering {
    match (left, right) {
        (Some(left), Some(right)) => compare_values(left, right),
        (None, None) => Ordering::Equal,
        (None, Some(_)) => Ordering::Less,
        (Some(_), None) => Ordering::Greater,
    }
}

fn compare_values(left: &Value, right: &Value) -> Ordering {
    let rank = value_rank(left).cmp(&value_rank(right));
    if rank != Ordering::Equal {
        return rank;
    }
    match (left, right) {
        (Value::Null, Value::Null) => Ordering::Equal,
        (
            left @ (Value::Int(_) | Value::Float(_) | Value::Decimal(_)),
            right @ (Value::Int(_) | Value::Float(_) | Value::Decimal(_)),
        ) => compare_numbers(left, right),
        (Value::Bool(left), Value::Bool(right)) => left.cmp(right),
        (Value::Text(left), Value::Text(right)) => left.cmp(right),
        (Value::Bytes(left), Value::Bytes(right)) => left.cmp(right),
        (Value::Json(left), Value::Json(right)) => compare_json(left, right),
        _ => Ordering::Equal,
    }
}

fn value_rank(value: &Value) -> u8 {
    match value {
        Value::Null => 0,
        Value::Int(_) | Value::Float(_) | Value::Decimal(_) => 1,
        Value::Bool(_) => 2,
        Value::Text(_) => 3,
        Value::Bytes(_) => 4,
        Value::Json(_) => 5,
    }
}

enum ComparableNumber {
    NegativeInfinity,
    Finite(NormalizedNumber),
    PositiveInfinity,
    Nan(u64),
    Invalid(String),
}

impl ComparableNumber {
    fn rank(&self) -> u8 {
        match self {
            ComparableNumber::NegativeInfinity => 0,
            ComparableNumber::Finite(_) => 1,
            ComparableNumber::PositiveInfinity => 2,
            ComparableNumber::Nan(_) => 3,
            ComparableNumber::Invalid(_) => 4,
        }
    }
}

struct NormalizedNumber {
    negative: bool,
    digits: Vec<u8>,
    magnitude: i128,
}

fn compare_numbers(left: &Value, right: &Value) -> Ordering {
    compare_comparable_numbers(&comparable_number(left), &comparable_number(right))
}

fn comparable_number(value: &Value) -> ComparableNumber {
    match value {
        Value::Int(value) => normalized_or_invalid(value.to_string()),
        Value::Decimal(value) => normalized_or_invalid(value.clone()),
        Value::Float(value) if value.is_nan() => ComparableNumber::Nan(value.to_bits()),
        Value::Float(value) if *value == f64::NEG_INFINITY => ComparableNumber::NegativeInfinity,
        Value::Float(value) if *value == f64::INFINITY => ComparableNumber::PositiveInfinity,
        Value::Float(value) => normalized_or_invalid(value.to_string()),
        _ => ComparableNumber::Invalid(value.to_string()),
    }
}

fn normalized_or_invalid(value: String) -> ComparableNumber {
    normalize_number(&value).map_or(ComparableNumber::Invalid(value), ComparableNumber::Finite)
}

fn compare_comparable_numbers(left: &ComparableNumber, right: &ComparableNumber) -> Ordering {
    let rank = left.rank().cmp(&right.rank());
    if rank != Ordering::Equal {
        return rank;
    }
    match (left, right) {
        (ComparableNumber::Finite(left), ComparableNumber::Finite(right)) => {
            compare_normalized_numbers(left, right)
        }
        (ComparableNumber::Nan(left), ComparableNumber::Nan(right)) => left.cmp(right),
        (ComparableNumber::Invalid(left), ComparableNumber::Invalid(right)) => left.cmp(right),
        _ => Ordering::Equal,
    }
}

fn normalize_number(value: &str) -> Option<NormalizedNumber> {
    let value = value.trim();
    let (negative, unsigned) = if let Some(unsigned) = value.strip_prefix('-') {
        (true, unsigned)
    } else if let Some(unsigned) = value.strip_prefix('+') {
        (false, unsigned)
    } else {
        (false, value)
    };
    let exponent_start = unsigned.find(['e', 'E']);
    let (mantissa, exponent) = if let Some(index) = exponent_start {
        (
            &unsigned[..index],
            parse_exponent(unsigned.get(index + 1..)?)?,
        )
    } else {
        (unsigned, 0)
    };

    let mut digits = Vec::with_capacity(mantissa.len());
    let mut decimal_seen = false;
    let mut fractional_digits = 0usize;
    for byte in mantissa.bytes() {
        match byte {
            b'0'..=b'9' => {
                digits.push(byte);
                if decimal_seen {
                    fractional_digits = fractional_digits.saturating_add(1);
                }
            }
            b'.' if !decimal_seen => decimal_seen = true,
            _ => return None,
        }
    }
    if digits.is_empty() {
        return None;
    }
    let first_nonzero = digits.iter().position(|digit| *digit != b'0');
    let Some(first_nonzero) = first_nonzero else {
        return Some(NormalizedNumber {
            negative: false,
            digits: vec![b'0'],
            magnitude: 1,
        });
    };
    digits.drain(..first_nonzero);

    let fractional_digits = i128::try_from(fractional_digits).unwrap_or(i128::MAX);
    let mut scale = exponent.saturating_sub(fractional_digits);
    while digits.len() > 1 && digits.last() == Some(&b'0') {
        digits.pop();
        scale = scale.saturating_add(1);
    }
    let digit_count = i128::try_from(digits.len()).unwrap_or(i128::MAX);
    Some(NormalizedNumber {
        negative,
        magnitude: scale.saturating_add(digit_count),
        digits,
    })
}

fn parse_exponent(value: &str) -> Option<i128> {
    let (negative, digits) = if let Some(digits) = value.strip_prefix('-') {
        (true, digits)
    } else if let Some(digits) = value.strip_prefix('+') {
        (false, digits)
    } else {
        (false, value)
    };
    if digits.is_empty() {
        return None;
    }
    let mut exponent = 0i128;
    for byte in digits.bytes() {
        if !byte.is_ascii_digit() {
            return None;
        }
        exponent = exponent
            .saturating_mul(10)
            .saturating_add(i128::from(byte - b'0'));
    }
    Some(if negative { -exponent } else { exponent })
}

fn compare_normalized_numbers(left: &NormalizedNumber, right: &NormalizedNumber) -> Ordering {
    if left.negative != right.negative {
        return right.negative.cmp(&left.negative);
    }
    let mut ordering = left.magnitude.cmp(&right.magnitude);
    if ordering == Ordering::Equal {
        let digits = left.digits.len().max(right.digits.len());
        for index in 0..digits {
            let left = left.digits.get(index).copied().unwrap_or(b'0');
            let right = right.digits.get(index).copied().unwrap_or(b'0');
            ordering = left.cmp(&right);
            if ordering != Ordering::Equal {
                break;
            }
        }
    }
    if left.negative {
        ordering.reverse()
    } else {
        ordering
    }
}

fn compare_json(left: &serde_json::Value, right: &serde_json::Value) -> Ordering {
    let rank = json_rank(left).cmp(&json_rank(right));
    if rank != Ordering::Equal {
        return rank;
    }
    match (left, right) {
        (serde_json::Value::Null, serde_json::Value::Null) => Ordering::Equal,
        (serde_json::Value::Bool(left), serde_json::Value::Bool(right)) => left.cmp(right),
        (serde_json::Value::Number(left), serde_json::Value::Number(right)) => {
            match (
                normalize_number(left.as_str()),
                normalize_number(right.as_str()),
            ) {
                (Some(left), Some(right)) => compare_normalized_numbers(&left, &right),
                _ => left.as_str().cmp(right.as_str()),
            }
        }
        (serde_json::Value::String(left), serde_json::Value::String(right)) => left.cmp(right),
        (serde_json::Value::Array(left), serde_json::Value::Array(right)) => {
            for (left, right) in left.iter().zip(right) {
                let ordering = compare_json(left, right);
                if ordering != Ordering::Equal {
                    return ordering;
                }
            }
            left.len().cmp(&right.len())
        }
        (serde_json::Value::Object(left), serde_json::Value::Object(right)) => {
            let mut left = left.iter();
            let mut right = right.iter();
            loop {
                match (left.next(), right.next()) {
                    (Some((left_key, left_value)), Some((right_key, right_value))) => {
                        let ordering = left_key
                            .cmp(right_key)
                            .then_with(|| compare_json(left_value, right_value));
                        if ordering != Ordering::Equal {
                            return ordering;
                        }
                    }
                    (Some(_), None) => return Ordering::Greater,
                    (None, Some(_)) => return Ordering::Less,
                    (None, None) => return Ordering::Equal,
                }
            }
        }
        _ => Ordering::Equal,
    }
}

fn json_rank(value: &serde_json::Value) -> u8 {
    match value {
        serde_json::Value::Null => 0,
        serde_json::Value::Bool(_) => 1,
        serde_json::Value::Number(_) => 2,
        serde_json::Value::String(_) => 3,
        serde_json::Value::Array(_) => 4,
        serde_json::Value::Object(_) => 5,
    }
}

pub struct AppState {
    pub tables: Vec<String>,
    pub selected_table: usize,
    pub focus: Panel,
    pub grid: GridState,
    pub status: String,
    pub should_quit: bool,
    pub loaded_table: Option<String>,
    pub overlay: Overlay,
    pub editor_tab: EditorTab,
    pub filters: FilterState,
    pub sql: InputState,
    pub sql_mode: SqlMode,
    pub tables_state: ListState,
    pub read_only: bool,
    pub config: Config,
    pub sidebar_visible: bool,
    pub backend_name: &'static str,
    pub supports_cell_edit: bool,
    pub inspector_visible: bool,
    pub inspector_scroll: u16,
    pub schemas: BTreeMap<String, Vec<SchemaColumn>>,
    pub completion: Option<CompletionMenu>,
    pub data_tab: DataTab,
    pub record_tab: RecordTab,
    pub config_writable: bool,
    pub active_filter: Option<ActiveFilter>,
    pub operation: Option<ForegroundOperation>,
    pub query_history: QueryHistory,
    query_history_store: Option<QueryHistoryStore>,
    history_recording_enabled: bool,
}

pub struct ForegroundOperation {
    pub id: OperationId,
    pub label: String,
    pub started: Instant,
}

impl AppState {
    #[cfg(test)]
    pub fn new(tables: Vec<String>) -> Self {
        Self::new_with_access(tables, false)
    }

    pub fn new_with_access(tables: Vec<String>, read_only: bool) -> Self {
        Self {
            tables,
            selected_table: 0,
            focus: Panel::Tables,
            grid: GridState::default(),
            status: String::new(),
            should_quit: false,
            loaded_table: None,
            overlay: Overlay::None,
            editor_tab: EditorTab::default(),
            filters: FilterState::default(),
            sql: InputState::default(),
            sql_mode: SqlMode::Normal,
            tables_state: ListState::default(),
            read_only,
            config: Config::default(),
            sidebar_visible: true,
            backend_name: "SQLite",
            supports_cell_edit: true,
            inspector_visible: true,
            inspector_scroll: 0,
            schemas: BTreeMap::new(),
            completion: None,
            data_tab: DataTab::Rows,
            record_tab: RecordTab::Fields,
            config_writable: true,
            active_filter: None,
            operation: None,
            query_history: QueryHistory::default(),
            query_history_store: None,
            history_recording_enabled: true,
        }
    }

    pub fn set_query_history_store(&mut self, store: QueryHistoryStore, history: QueryHistory) {
        self.query_history = history;
        self.query_history_store = Some(store);
    }

    pub fn record_successful_read_query(&mut self, statement: &str) -> anyhow::Result<()> {
        if !self.history_recording_enabled || !self.config.query_history_enabled {
            return Ok(());
        }
        self.query_history.record(statement);
        if let Some(store) = &self.query_history_store {
            self.query_history = store.record(statement)?;
        }
        Ok(())
    }

    pub fn set_history_recording_enabled(&mut self, enabled: bool) {
        self.history_recording_enabled = enabled;
    }

    pub fn history_recording_enabled(&self) -> bool {
        self.history_recording_enabled && self.config.query_history_enabled
    }

    pub fn open_history_search(&mut self) {
        self.overlay = Overlay::History(HistorySearch::new(&self.query_history));
    }

    pub fn insert_history_search(&mut self, text: &str) {
        let Overlay::History(search) = &mut self.overlay else {
            return;
        };
        search.query.push_str(text);
        search.refresh(&self.query_history);
    }

    pub fn backspace_history_search(&mut self) {
        let Overlay::History(search) = &mut self.overlay else {
            return;
        };
        search.query.pop();
        search.refresh(&self.query_history);
    }

    pub fn move_history_selection(&mut self, direction: isize) {
        let Overlay::History(search) = &mut self.overlay else {
            return;
        };
        search.selected = search
            .selected
            .saturating_add_signed(direction)
            .min(search.matches.len().saturating_sub(1));
    }

    pub fn accept_history_selection(&mut self) -> bool {
        let statement = match &self.overlay {
            Overlay::History(search) => search.matches.get(search.selected).cloned(),
            _ => None,
        };
        let Some(statement) = statement else {
            return false;
        };
        self.sql.cursor = statement.chars().count();
        self.sql.text = statement;
        self.sql_mode = SqlMode::Normal;
        self.completion = None;
        self.overlay = Overlay::None;
        true
    }

    pub fn selected_cell_text(&self) -> Option<String> {
        let row = self.grid.rows.get(self.grid.selected_row)?;
        row.values
            .get(self.grid.selected_col)
            .map(|v| v.to_string())
    }

    pub fn selected_cell_value(&self) -> Option<&crate::db::model::Value> {
        self.grid
            .rows
            .get(self.grid.selected_row)?
            .values
            .get(self.grid.selected_col)
    }

    pub fn selected_rowid(&self) -> Option<i64> {
        self.grid
            .rowids
            .as_ref()?
            .get(self.grid.selected_row)
            .copied()
    }

    pub fn selected_column_name(&self) -> Option<&str> {
        self.grid
            .columns
            .get(self.grid.selected_col)
            .map(|c| c.name.as_str())
    }

    pub fn visual_yank_text(&self) -> Option<String> {
        match self.grid.visual {
            Visual::None => self.selected_cell_text(),
            Visual::Rows { anchor } => {
                let start = anchor.min(self.grid.selected_row);
                let end = anchor.max(self.grid.selected_row);
                Some(
                    self.grid.rows[start..=end]
                        .iter()
                        .map(|row| {
                            row.values
                                .iter()
                                .map(ToString::to_string)
                                .collect::<Vec<_>>()
                                .join("\t")
                        })
                        .collect::<Vec<_>>()
                        .join("\n"),
                )
            }
            Visual::Column => Some(
                self.grid
                    .rows
                    .iter()
                    .filter_map(|row| row.values.get(self.grid.selected_col))
                    .map(ToString::to_string)
                    .collect::<Vec<_>>()
                    .join("\n"),
            ),
        }
    }

    pub fn copy_scope_label(&self) -> String {
        match self.grid.visual {
            Visual::None => format!(
                "current row {}",
                self.grid.page_offset + self.grid.selected_row + 1
            ),
            Visual::Rows { anchor } => {
                let start = self.grid.page_offset + anchor.min(self.grid.selected_row) + 1;
                let end = self.grid.page_offset + anchor.max(self.grid.selected_row) + 1;
                format!("rows {start}-{end}")
            }
            Visual::Column => self.selected_column_name().map_or_else(
                || "current column".to_string(),
                |name| format!("column {name}"),
            ),
        }
    }

    pub fn cache_schema(&mut self, table: String, columns: Vec<SchemaColumn>) {
        self.schemas.insert(table, columns);
    }

    pub fn refresh_completion(&mut self, explicit: bool) {
        if self.sql_mode != SqlMode::Insert || (!explicit && !should_auto_complete(&self.sql)) {
            self.completion = None;
            return;
        }
        let items = sql_completions(&self.sql, &self.tables, &self.schemas, &self.grid.columns);
        if items.is_empty() {
            self.completion = None;
            return;
        }
        let selected = self
            .completion
            .as_ref()
            .map_or(0, |menu| menu.selected.min(items.len().saturating_sub(1)));
        self.completion = Some(CompletionMenu { items, selected });
    }

    pub fn move_completion(&mut self, direction: isize) {
        let Some(menu) = &mut self.completion else {
            return;
        };
        menu.selected = menu
            .selected
            .saturating_add_signed(direction)
            .min(menu.items.len().saturating_sub(1));
    }

    pub fn accept_completion(&mut self) -> bool {
        let value = self
            .completion
            .as_ref()
            .and_then(|menu| menu.items.get(menu.selected))
            .map(|item| item.value.clone());
        let Some(value) = value else { return false };
        self.sql.accept_completion(&value);
        self.completion = None;
        true
    }
}

#[derive(Debug)]
pub enum AppEvent {
    Quit,
    ToggleFocus,
    FocusPrev,
    FocusPanel(Panel),
    MoveDown,
    MoveUp,
    MoveLeft,
    MoveRight,
    JumpTop,
    JumpBottom,
    CycleSort,
    ToggleHelp,
    CloseOverlay,
    RowsLoaded {
        table: Option<String>,
        page: TablePage,
    },
    SchemaLoaded {
        table: String,
        columns: Vec<SchemaColumn>,
    },
    EditStart,
    InputChar(char),
    InputBackspace,
    Error(String),
    Status(String),
}

pub fn dispatch(state: &mut AppState, event: AppEvent) {
    match event {
        AppEvent::Quit => state.should_quit = true,
        AppEvent::ToggleFocus => state.focus = next_visible_focus(state, false),
        AppEvent::FocusPrev => state.focus = next_visible_focus(state, true),
        AppEvent::FocusPanel(panel) => {
            state.focus = panel;
            if panel != Panel::Sql {
                state.completion = None;
            }
        }
        AppEvent::MoveDown => match state.focus {
            Panel::Tables => {
                if !state.tables.is_empty() {
                    state.selected_table = (state.selected_table + 1).min(state.tables.len() - 1);
                }
            }
            Panel::Grid => {
                if state.data_tab == DataTab::Rows && !state.grid.rows.is_empty() {
                    state.grid.selected_row =
                        (state.grid.selected_row + 1).min(state.grid.rows.len() - 1);
                    state.inspector_scroll = 0;
                }
            }
            Panel::Inspector => {
                state.inspector_scroll = state.inspector_scroll.saturating_add(1);
            }
            Panel::Sql => {}
        },
        AppEvent::MoveUp => match state.focus {
            Panel::Tables => state.selected_table = state.selected_table.saturating_sub(1),
            Panel::Grid => {
                if state.data_tab == DataTab::Rows {
                    state.grid.selected_row = state.grid.selected_row.saturating_sub(1);
                    state.inspector_scroll = 0;
                }
            }
            Panel::Inspector => {
                state.inspector_scroll = state.inspector_scroll.saturating_sub(1);
            }
            Panel::Sql => {}
        },
        AppEvent::MoveLeft => {
            if state.focus == Panel::Grid && state.data_tab == DataTab::Rows {
                state.grid.selected_col = state.grid.selected_col.saturating_sub(1);
                state.inspector_scroll = 0;
            }
        }
        AppEvent::MoveRight => {
            if state.focus == Panel::Grid
                && state.data_tab == DataTab::Rows
                && !state.grid.columns.is_empty()
            {
                state.grid.selected_col =
                    (state.grid.selected_col + 1).min(state.grid.columns.len() - 1);
                state.inspector_scroll = 0;
            }
        }
        AppEvent::JumpTop => match state.focus {
            Panel::Tables => state.selected_table = 0,
            Panel::Grid => {
                if state.data_tab == DataTab::Rows {
                    state.grid.selected_row = 0;
                    state.inspector_scroll = 0;
                }
            }
            Panel::Sql | Panel::Inspector => {}
        },
        AppEvent::JumpBottom => match state.focus {
            Panel::Tables => state.selected_table = state.tables.len().saturating_sub(1),
            Panel::Grid => {
                if state.data_tab == DataTab::Rows {
                    state.grid.selected_row = state.grid.rows.len().saturating_sub(1);
                    state.inspector_scroll = 0;
                }
            }
            Panel::Sql | Panel::Inspector => {}
        },
        AppEvent::CycleSort => {
            if state.focus != Panel::Grid || state.data_tab != DataTab::Rows {
                return;
            }
            let outcome = state.grid.cycle_sort();
            state.grid.visual = Visual::None;
            match outcome {
                Some(Ok(Some(sort))) => {
                    state.status = format!(
                        "sorted current result/page by {} {}",
                        sort.column_name,
                        sort.direction.label()
                    );
                }
                Some(Ok(None)) => {
                    state.status = "sorting off for current result/page".to_string();
                }
                Some(Err(())) => {
                    state.status =
                        "sorting unavailable for current result/page: row identity mismatch"
                            .to_string();
                }
                None => {}
            }
        }
        AppEvent::ToggleHelp => {
            state.overlay = if state.overlay == Overlay::Help {
                Overlay::None
            } else {
                Overlay::Help
            };
        }
        AppEvent::CloseOverlay => state.overlay = Overlay::None,
        AppEvent::RowsLoaded { table, mut page } => {
            let table_changed = state.loaded_table.as_ref() != table.as_ref();
            let rowids_misaligned = page
                .rowids
                .as_ref()
                .is_some_and(|rowids| rowids.len() != page.rows.len());
            if rowids_misaligned {
                page.rowids = None;
            }
            if table_changed {
                state.filters = FilterState::default();
            } else if page.columns.is_empty() && page.rows.is_empty() {
                page.columns.clone_from(&state.grid.columns);
            }
            let previous_columns = if table_changed {
                Vec::new()
            } else {
                state.grid.columns.clone()
            };
            state
                .filters
                .remap_columns(&previous_columns, &page.columns);
            state
                .filters
                .capture_column_types(&page.columns, &page.rows);
            let mut status = if page.rows.is_empty() {
                "0 rows".to_string()
            } else {
                let first = page.offset + 1;
                let last = page.offset + page.rows.len();
                let suffix = if page.has_more { "+" } else { "" };
                format!("rows {first}-{last}{suffix}")
            };
            state.filters.clamp_columns(page.columns.len());
            let previous_sort = if table_changed {
                None
            } else {
                state.grid.sort.clone()
            };
            let had_sort = previous_sort.is_some();
            let sort = previous_sort.and_then(|sort| remap_sort(sort, &page.columns));
            let source_order = (0..page.rows.len()).collect();
            let mut grid = GridState {
                columns: page.columns,
                rows: page.rows,
                rowids: page.rowids,
                page_offset: page.offset,
                has_more: page.has_more,
                sort,
                source_order,
                ..GridState::default()
            };
            if grid.sort.is_some() {
                if grid.apply_sort() {
                    if let Some(sort) = &grid.sort {
                        status.push_str(&format!(
                            " | sorted current result/page by {} {}",
                            sort.column_name,
                            sort.direction.label()
                        ));
                    }
                } else {
                    grid.sort = None;
                    status
                        .push_str(" | sorting off for current result/page: row identity mismatch");
                }
            } else if had_sort {
                status.push_str(
                    " | sorting off for current result/page: sorted column is unavailable",
                );
            }
            if rowids_misaligned {
                status.push_str(" | editing disabled: row identity mismatch");
            }
            state.status = status;
            state.grid = grid;
            state.loaded_table = table;
            state.inspector_scroll = 0;
            state.refresh_completion(false);
        }
        AppEvent::SchemaLoaded { table, columns } => {
            state.cache_schema(table.clone(), columns.clone());
            state.overlay = Overlay::Schema { table, columns };
        }
        AppEvent::EditStart => {
            if state.focus != Panel::Grid
                || state.data_tab != DataTab::Rows
                || state.grid.rows.is_empty()
            {
                return;
            }
            if state.read_only {
                state.status = "read-only mode: reopen with --write to edit".to_string();
                return;
            }
            if !state.supports_cell_edit {
                state.status = "inline editing is unavailable for this backend".to_string();
                return;
            }
            if state.selected_rowid().is_none() {
                state.status = "editing unavailable for this table (no rowid)".to_string();
                return;
            }
            if matches!(
                state.selected_cell_value(),
                Some(crate::db::model::Value::Bytes(_))
            ) {
                state.status = "binary values cannot be edited in the TUI".to_string();
                return;
            }
            let (Some(text), Some(table), Some(rowid), Some(column), Some(original)) = (
                state.selected_cell_text(),
                state.loaded_table.clone(),
                state.selected_rowid(),
                state.selected_column_name().map(str::to_string),
                state.selected_cell_value().cloned(),
            ) else {
                return;
            };
            state.overlay = Overlay::Edit {
                text,
                target: EditTarget::Cell,
                cell: Some(CellEditIdentity {
                    table,
                    rowid,
                    column,
                    selected_row: state.grid.selected_row,
                    selected_col: state.grid.selected_col,
                    original,
                }),
            };
        }
        AppEvent::InputChar(c) => {
            if let Overlay::Edit { text, .. } = &mut state.overlay {
                text.push(c);
            } else if state.focus == Panel::Sql && state.sql_mode == SqlMode::Insert {
                state.sql.insert(c);
                state.refresh_completion(false);
            }
        }
        AppEvent::InputBackspace => {
            if let Overlay::Edit { text, .. } = &mut state.overlay {
                text.pop();
            } else if state.focus == Panel::Sql && state.sql_mode == SqlMode::Insert {
                state.sql.backspace();
                state.refresh_completion(false);
            }
        }
        AppEvent::Error(msg) | AppEvent::Status(msg) => state.status = msg,
    }
}

fn next_visible_focus(state: &AppState, reverse: bool) -> Panel {
    let mut panels = Vec::with_capacity(4);
    if state.sidebar_visible {
        panels.push(Panel::Tables);
    }
    if state.config.editor_visible {
        panels.push(Panel::Sql);
    }
    panels.push(Panel::Grid);
    if state.inspector_visible {
        panels.push(Panel::Inspector);
    }
    let current = panels
        .iter()
        .position(|panel| *panel == state.focus)
        .unwrap_or(0);
    if reverse {
        panels[(current + panels.len() - 1) % panels.len()]
    } else {
        panels[(current + 1) % panels.len()]
    }
}

const SQL_KEYWORDS: &[&str] = &[
    "SELECT",
    "FROM",
    "WHERE",
    "JOIN",
    "LEFT",
    "INNER",
    "ON",
    "GROUP",
    "ORDER",
    "BY",
    "LIMIT",
    "OFFSET",
    "INSERT",
    "INTO",
    "VALUES",
    "UPDATE",
    "SET",
    "DELETE",
    "CREATE",
    "TABLE",
    "DROP",
    "ALTER",
    "WITH",
    "AS",
    "AND",
    "OR",
    "NOT",
    "NULL",
    "IS",
    "IN",
    "LIKE",
    "HAVING",
    "DISTINCT",
    "RETURNING",
];

const SQL_FUNCTIONS: &[&str] = &[
    "AVG", "COALESCE", "COUNT", "DATE", "LENGTH", "LOWER", "MAX", "MIN", "ROUND", "SUM", "UPPER",
];

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum CompletionContext {
    General,
    Table,
    Column,
}

pub fn sql_completions(
    input: &InputState,
    tables: &[String],
    schemas: &BTreeMap<String, Vec<SchemaColumn>>,
    columns: &[Column],
) -> Vec<CompletionItem> {
    let prefix = input.current_prefix().to_ascii_lowercase();
    let context = completion_context(input);
    let mut candidates = Vec::new();

    for table in tables {
        candidates.push(completion_item(table, CompletionKind::Table, "table", true));
    }
    for column in columns {
        candidates.push(completion_item(
            &column.name,
            CompletionKind::Column,
            "result",
            true,
        ));
    }
    for (table, schema) in schemas {
        for column in schema {
            candidates.push(completion_item(
                &column.name,
                CompletionKind::Column,
                table,
                true,
            ));
        }
    }
    candidates.extend(
        SQL_FUNCTIONS
            .iter()
            .map(|function| completion_item(function, CompletionKind::Function, "function", false)),
    );
    candidates.extend(
        SQL_KEYWORDS
            .iter()
            .map(|keyword| completion_item(keyword, CompletionKind::Keyword, "keyword", false)),
    );

    let mut ranked: Vec<(usize, usize, CompletionItem)> = candidates
        .into_iter()
        .filter_map(|item| {
            let normalized = item.label.to_ascii_lowercase();
            if !prefix.is_empty() && normalized == prefix {
                return None;
            }
            fuzzy_score(&normalized, &prefix)
                .map(|score| (completion_priority(context, item.kind), score, item))
        })
        .collect();
    ranked.sort_by(|left, right| {
        (left.0, left.1, left.2.label.to_ascii_lowercase()).cmp(&(
            right.0,
            right.1,
            right.2.label.to_ascii_lowercase(),
        ))
    });

    let mut items = Vec::new();
    for (_, _, item) in ranked {
        if items.iter().any(|existing: &CompletionItem| {
            existing.kind == item.kind && existing.label.eq_ignore_ascii_case(&item.label)
        }) {
            continue;
        }
        items.push(item);
        if items.len() == 50 {
            break;
        }
    }
    items
}

fn completion_item(
    candidate: &str,
    kind: CompletionKind,
    detail: &str,
    identifier: bool,
) -> CompletionItem {
    CompletionItem {
        value: if identifier {
            format!("\"{}\"", candidate.replace('"', "\"\""))
        } else {
            candidate.to_string()
        },
        label: candidate.to_string(),
        detail: detail.to_string(),
        kind,
    }
}

fn completion_context(input: &InputState) -> CompletionContext {
    let prefix_length = input.current_prefix().chars().count();
    let before: String = input
        .text
        .chars()
        .take(input.cursor.saturating_sub(prefix_length))
        .collect();
    let keyword = before
        .split(|character: char| !character.is_ascii_alphanumeric() && character != '_')
        .rfind(|token| !token.is_empty())
        .map(str::to_ascii_uppercase);
    match keyword.as_deref() {
        Some("FROM" | "JOIN" | "INTO" | "UPDATE" | "TABLE") => CompletionContext::Table,
        Some(
            "SELECT" | "WHERE" | "ON" | "BY" | "SET" | "GROUP" | "ORDER" | "HAVING" | "AND" | "OR",
        ) => CompletionContext::Column,
        _ if before.trim_end().ends_with('.') => CompletionContext::Column,
        _ => CompletionContext::General,
    }
}

fn completion_priority(context: CompletionContext, kind: CompletionKind) -> usize {
    match (context, kind) {
        (CompletionContext::Table, CompletionKind::Table)
        | (CompletionContext::Column, CompletionKind::Column) => 0,
        (CompletionContext::Column, CompletionKind::Function) => 1,
        (CompletionContext::Table, CompletionKind::Keyword)
        | (CompletionContext::Column, CompletionKind::Keyword) => 2,
        (CompletionContext::Table, _) | (CompletionContext::Column, _) => 3,
        (CompletionContext::General, CompletionKind::Keyword) => 0,
        (CompletionContext::General, CompletionKind::Table) => 1,
        (CompletionContext::General, CompletionKind::Column) => 2,
        (CompletionContext::General, CompletionKind::Function) => 3,
    }
}

fn fuzzy_score(candidate: &str, prefix: &str) -> Option<usize> {
    if prefix.is_empty() {
        return Some(0);
    }
    if candidate.starts_with(prefix) {
        return Some(0);
    }
    if let Some(position) = candidate.find(prefix) {
        return Some(10 + position);
    }
    let mut offset = 0;
    let mut score = 20;
    for character in prefix.chars() {
        let position = candidate[offset..].find(character)?;
        score += position;
        offset += position + character.len_utf8();
    }
    Some(score)
}

fn is_subsequence_match(candidate: &str, query: &str) -> bool {
    let mut candidate = candidate.chars().flat_map(char::to_lowercase);
    query
        .chars()
        .flat_map(char::to_lowercase)
        .all(|expected| candidate.any(|actual| actual == expected))
}

fn should_auto_complete(input: &InputState) -> bool {
    if !input.current_prefix().is_empty() {
        return true;
    }
    let before: String = input.text.chars().take(input.cursor).collect();
    before.ends_with('.')
        || (before.chars().last().is_some_and(char::is_whitespace)
            && completion_context(input) != CompletionContext::General)
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::model::Value;

    fn state_with_tables() -> AppState {
        AppState::new(vec![
            "authors".to_string(),
            "books".to_string(),
            "reviews".to_string(),
        ])
    }

    fn page(rows: usize, cols: usize, with_rowids: bool) -> TablePage {
        TablePage {
            columns: (0..cols)
                .map(|i| Column {
                    name: format!("c{i}"),
                })
                .collect(),
            rows: (0..rows)
                .map(|r| Row {
                    values: (0..cols)
                        .map(|c| Value::Int((r * cols + c) as i64))
                        .collect(),
                })
                .collect(),
            rowids: with_rowids.then(|| (1..=rows as i64).collect()),
            offset: 0,
            has_more: false,
        }
    }

    fn result_page(columns: &[&str], rows: Vec<Vec<Value>>, rowids: Option<Vec<i64>>) -> TablePage {
        TablePage {
            columns: columns
                .iter()
                .map(|name| Column {
                    name: (*name).to_string(),
                })
                .collect(),
            rows: rows.into_iter().map(|values| Row { values }).collect(),
            rowids,
            offset: 0,
            has_more: false,
        }
    }

    fn state_with_grid() -> AppState {
        let mut state = state_with_tables();
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: page(3, 3, true),
            },
        );
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Grid));
        state
    }

    #[test]
    fn editor_and_filter_defaults_are_ready_for_input() {
        let state = state_with_tables();

        assert_eq!(state.editor_tab, EditorTab::Filters);
        assert_eq!(state.editor_tab.next(), EditorTab::Sql);
        assert_eq!(EditorTab::Sql.next(), EditorTab::Filters);
        assert_eq!(EditorTab::Filters.previous(), EditorTab::Sql);
        assert_eq!(state.filters.rows(), &[FilterRow::default()]);
        assert_eq!(state.filters.selected_row_index(), 0);
        assert_eq!(state.filters.selected_field(), FilterField::Enabled);
        assert_eq!(state.filters.selected_row().operator, FilterOperator::Equal);
    }

    #[test]
    fn filter_rows_can_be_added_removed_and_selected_safely() {
        let mut filters = FilterState::default();
        filters.selected_row_mut().value = "first".to_string();
        filters.add_row();
        filters.selected_row_mut().enabled = false;

        assert_eq!(filters.rows().len(), 2);
        assert_eq!(filters.selected_row_index(), 1);
        assert_eq!(filters.active_rows().count(), 1);

        filters.select_row(usize::MAX);
        assert_eq!(filters.selected_row_index(), 1);
        filters.previous_row();
        filters.previous_row();
        assert_eq!(filters.selected_row_index(), 0);
        filters.next_row();
        filters.next_row();
        assert_eq!(filters.selected_row_index(), 1);

        filters.remove_selected_row();
        assert_eq!(filters.rows().len(), 1);
        assert_eq!(filters.selected_row_index(), 0);
        assert_eq!(filters.selected_row().value, "first");

        filters.remove_selected_row();
        assert_eq!(filters.rows(), &[FilterRow::default()]);
    }

    #[test]
    fn filter_fields_and_operators_cycle_in_stable_order() {
        let fields = [
            FilterField::Enabled,
            FilterField::Column,
            FilterField::Operator,
            FilterField::Value,
            FilterField::Apply,
            FilterField::Remove,
            FilterField::Add,
        ];
        let mut filters = FilterState::default();
        for expected in fields {
            assert_eq!(filters.selected_field(), expected);
            filters.next_field();
        }
        assert_eq!(filters.selected_field(), FilterField::Enabled);
        filters.previous_field();
        assert_eq!(filters.selected_field(), FilterField::Add);
        filters.select_field(FilterField::Operator);
        assert_eq!(filters.selected_field(), FilterField::Operator);

        let operators = [
            (FilterOperator::Equal, "equals"),
            (FilterOperator::NotEqual, "not equal"),
            (FilterOperator::Contains, "contains"),
            (FilterOperator::NotContains, "not contains"),
            (FilterOperator::In, "in"),
            (FilterOperator::NotIn, "not in"),
            (FilterOperator::Greater, "greater than"),
            (FilterOperator::GreaterOrEqual, "greater or equal"),
            (FilterOperator::Less, "less than"),
            (FilterOperator::LessOrEqual, "less or equal"),
            (FilterOperator::IsNull, "is null"),
            (FilterOperator::IsNotNull, "is not null"),
        ];
        let mut operator = FilterOperator::default();
        for &(expected, label) in &operators {
            assert_eq!(operator, expected);
            assert_eq!(operator.label(), label);
            operator = operator.next();
        }
        assert_eq!(operator, FilterOperator::Equal);
        for &(expected, _) in operators.iter().rev() {
            operator = operator.previous();
            assert_eq!(operator, expected);
        }
        assert_eq!(operator, FilterOperator::Equal);
    }

    #[test]
    fn null_filter_operators_do_not_need_values() {
        assert!(!FilterOperator::IsNull.needs_value());
        assert!(!FilterOperator::IsNotNull.needs_value());
        for operator in [
            FilterOperator::Equal,
            FilterOperator::NotEqual,
            FilterOperator::Contains,
            FilterOperator::NotContains,
            FilterOperator::In,
            FilterOperator::NotIn,
            FilterOperator::Greater,
            FilterOperator::GreaterOrEqual,
            FilterOperator::Less,
            FilterOperator::LessOrEqual,
        ] {
            assert!(operator.needs_value());
        }
    }

    #[test]
    fn filter_columns_clamp_when_schema_width_changes() {
        let mut filters = FilterState::default();
        filters.selected_row_mut().column_index = 8;
        filters.add_row();
        filters.selected_row_mut().column_index = 3;

        filters.clamp_columns(3);
        assert_eq!(
            filters
                .rows()
                .iter()
                .map(|row| row.column_index)
                .collect::<Vec<_>>(),
            vec![2, 2]
        );

        filters.clamp_columns(0);
        assert!(filters.rows().iter().all(|row| row.column_index == 0));
    }

    #[test]
    fn filter_columns_follow_names_when_results_reorder() {
        let mut filters = FilterState::default();
        filters.selected_row_mut().column_index = 2;
        let previous = vec![
            Column { name: "a".into() },
            Column { name: "b".into() },
            Column { name: "c".into() },
        ];
        let current = vec![Column { name: "c".into() }, Column { name: "a".into() }];

        filters.remap_columns(&previous, &current);

        assert_eq!(filters.selected_row().column_index, 0);
        assert_eq!(filters.selected_row().column_name.as_deref(), Some("c"));

        let sparse = vec![
            Column { name: "a".into() },
            Column {
                name: "status".into(),
            },
        ];
        filters.remap_columns(&current, &sparse);

        assert_eq!(filters.selected_row().column_name.as_deref(), Some("c"));
    }

    #[test]
    fn filters_survive_empty_results_but_reset_for_another_table() {
        let mut state = state_with_grid();
        state
            .filters
            .selected_row_mut()
            .set_column(2, "c2".to_string());
        state.filters.selected_row_mut().value = "needle".to_string();

        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: page(0, 0, true),
            },
        );

        assert_eq!(state.grid.columns.len(), 3);
        assert_eq!(state.filters.selected_row().column_index, 2);
        assert_eq!(state.filters.selected_row().value, "needle");

        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("books".into()),
                page: page(1, 2, true),
            },
        );

        assert_eq!(state.filters.rows().len(), 1);
        assert_eq!(state.filters.selected_row().column_index, 0);
        assert_eq!(
            state.filters.selected_row().column_name.as_deref(),
            Some("c0")
        );
        assert!(state.filters.selected_row().value.is_empty());
    }

    #[test]
    fn quit_sets_should_quit() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::Quit);
        assert!(state.should_quit);
    }

    #[test]
    fn tab_cycles_through_all_visible_panels() {
        let mut state = state_with_tables();
        assert_eq!(state.focus, Panel::Tables);
        dispatch(&mut state, AppEvent::ToggleFocus);
        assert_eq!(state.focus, Panel::Sql);
        dispatch(&mut state, AppEvent::ToggleFocus);
        assert_eq!(state.focus, Panel::Grid);
        dispatch(&mut state, AppEvent::ToggleFocus);
        assert_eq!(state.focus, Panel::Inspector);
        dispatch(&mut state, AppEvent::ToggleFocus);
        assert_eq!(state.focus, Panel::Tables);
    }

    #[test]
    fn reverse_focus_cycles_backwards() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::FocusPrev);
        assert_eq!(state.focus, Panel::Inspector);
        dispatch(&mut state, AppEvent::FocusPrev);
        assert_eq!(state.focus, Panel::Grid);
    }

    #[test]
    fn focus_panel_jumps_directly() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Sql));
        assert_eq!(state.focus, Panel::Sql);
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Grid));
        assert_eq!(state.focus, Panel::Grid);
    }

    #[test]
    fn move_down_advances_selected_table_and_stops_at_end() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::MoveDown);
        assert_eq!(state.selected_table, 1);
        dispatch(&mut state, AppEvent::MoveDown);
        dispatch(&mut state, AppEvent::MoveDown);
        assert_eq!(state.selected_table, 2);
    }

    #[test]
    fn move_up_stops_at_zero() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::MoveUp);
        assert_eq!(state.selected_table, 0);
    }

    #[test]
    fn grid_horizontal_moves_clamp_to_columns() {
        let mut state = state_with_grid();
        dispatch(&mut state, AppEvent::MoveLeft);
        assert_eq!(state.grid.selected_col, 0);
        dispatch(&mut state, AppEvent::MoveRight);
        dispatch(&mut state, AppEvent::MoveRight);
        dispatch(&mut state, AppEvent::MoveRight);
        assert_eq!(state.grid.selected_col, 2);
        dispatch(&mut state, AppEvent::MoveLeft);
        assert_eq!(state.grid.selected_col, 1);
    }

    #[test]
    fn schema_tab_does_not_move_or_edit_hidden_rows() {
        let mut state = state_with_grid();
        state.data_tab = DataTab::Schema;

        dispatch(&mut state, AppEvent::MoveDown);
        dispatch(&mut state, AppEvent::MoveRight);
        dispatch(&mut state, AppEvent::EditStart);

        assert_eq!(state.grid.selected_row, 0);
        assert_eq!(state.grid.selected_col, 0);
        assert_eq!(state.overlay, Overlay::None);
    }

    #[test]
    fn jump_top_and_bottom_move_selection_to_edges() {
        let mut state = state_with_grid();
        dispatch(&mut state, AppEvent::JumpBottom);
        assert_eq!(state.grid.selected_row, 2);
        dispatch(&mut state, AppEvent::JumpTop);
        assert_eq!(state.grid.selected_row, 0);

        dispatch(&mut state, AppEvent::FocusPanel(Panel::Tables));
        dispatch(&mut state, AppEvent::JumpBottom);
        assert_eq!(state.selected_table, 2);
    }

    #[test]
    fn toggle_help_opens_and_closes_overlay() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::ToggleHelp);
        assert_eq!(state.overlay, Overlay::Help);
        dispatch(&mut state, AppEvent::ToggleHelp);
        assert_eq!(state.overlay, Overlay::None);
    }

    #[test]
    fn close_overlay_dismisses_help() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::ToggleHelp);
        dispatch(&mut state, AppEvent::CloseOverlay);
        assert_eq!(state.overlay, Overlay::None);
    }

    #[test]
    fn rows_loaded_populates_grid_and_tracks_table() {
        let mut state = state_with_tables();
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("books".into()),
                page: page(5, 2, true),
            },
        );
        assert_eq!(state.grid.rows.len(), 5);
        assert_eq!(state.grid.selected_row, 0);
        assert_eq!(state.grid.selected_col, 0);
        assert_eq!(state.loaded_table.as_deref(), Some("books"));
        assert!(state.status.contains("rows 1-5"));
    }

    #[test]
    fn grid_sort_cycles_type_aware_orders_stably_and_restores_source_order() {
        let values = vec![
            Value::Text("z".into()),
            Value::Int(10),
            Value::Null,
            Value::Decimal("2.50".into()),
            Value::Float(2.5),
            Value::Bool(true),
            Value::Bool(false),
            Value::Bytes(vec![1, 2]),
            Value::Bytes(vec![1, 1]),
            Value::Json(serde_json::json!(10)),
            Value::Json(serde_json::json!(2)),
            Value::Text("a".into()),
            Value::Decimal("9007199254740993".into()),
            Value::Decimal("-3".into()),
            Value::Json(serde_json::json!([1])),
            Value::Json(serde_json::json!("a")),
            Value::Text("a".into()),
        ];
        let row_count = values.len();
        let mut state = state_with_tables();
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: result_page(
                    &["value"],
                    values.into_iter().map(|value| vec![value]).collect(),
                    Some((1..=row_count as i64).collect()),
                ),
            },
        );
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Grid));

        dispatch(&mut state, AppEvent::CycleSort);

        assert_eq!(
            state.grid.sort.as_ref().map(|sort| sort.direction),
            Some(SortDirection::Ascending)
        );
        assert_eq!(
            state.grid.rowids,
            Some(vec![
                3, 14, 4, 5, 2, 13, 7, 6, 12, 17, 1, 9, 8, 11, 10, 16, 15
            ])
        );
        assert!(state.status.contains("current result/page"));

        dispatch(&mut state, AppEvent::CycleSort);

        assert_eq!(
            state.grid.sort.as_ref().map(|sort| sort.direction),
            Some(SortDirection::Descending)
        );
        assert_eq!(
            state.grid.rowids,
            Some(vec![
                15, 16, 10, 11, 8, 9, 1, 12, 17, 6, 7, 13, 2, 4, 5, 14, 3
            ])
        );

        dispatch(&mut state, AppEvent::CycleSort);

        assert_eq!(state.grid.sort, None);
        assert_eq!(state.grid.rowids, Some((1..=row_count as i64).collect()));
        assert_eq!(state.status, "sorting off for current result/page");
    }

    #[test]
    fn sort_reapplies_by_column_name_and_resets_for_incompatible_results_and_tables() {
        let mut state = state_with_tables();
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: result_page(
                    &["score", "name"],
                    vec![
                        vec![Value::Int(2), Value::Text("B".into())],
                        vec![Value::Int(1), Value::Text("A".into())],
                    ],
                    Some(vec![20, 10]),
                ),
            },
        );
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Grid));
        dispatch(&mut state, AppEvent::CycleSort);

        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: result_page(
                    &["name", "score"],
                    vec![
                        vec![Value::Text("D".into()), Value::Int(4)],
                        vec![Value::Text("C".into()), Value::Int(3)],
                    ],
                    Some(vec![40, 30]),
                ),
            },
        );

        assert_eq!(
            state
                .grid
                .sort
                .as_ref()
                .map(|sort| (sort.column, sort.column_name.as_str())),
            Some((1, "score"))
        );
        assert_eq!(state.grid.rowids, Some(vec![30, 40]));
        assert!(state.status.contains("sorted current result/page"));

        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: result_page(
                    &["name"],
                    vec![vec![Value::Text("D".into())], vec![Value::Text("C".into())]],
                    Some(vec![40, 30]),
                ),
            },
        );

        assert_eq!(state.grid.sort, None);
        assert_eq!(state.grid.rowids, Some(vec![40, 30]));
        assert!(state.status.contains("sorted column is unavailable"));

        dispatch(&mut state, AppEvent::CycleSort);
        assert!(state.grid.sort.is_some());
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("books".into()),
                page: result_page(
                    &["name"],
                    vec![vec![Value::Text("Z".into())], vec![Value::Text("A".into())]],
                    Some(vec![2, 1]),
                ),
            },
        );

        assert_eq!(state.grid.sort, None);
        assert_eq!(state.grid.rowids, Some(vec![2, 1]));
    }

    #[test]
    fn misaligned_loaded_rowids_are_discarded_before_sorting_and_editing() {
        let mut state = state_with_tables();
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: result_page(
                    &["score"],
                    vec![vec![Value::Int(2)], vec![Value::Int(1)]],
                    Some(vec![20, 10]),
                ),
            },
        );
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Grid));
        dispatch(&mut state, AppEvent::CycleSort);

        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: result_page(
                    &["score"],
                    vec![vec![Value::Int(4)], vec![Value::Int(3)]],
                    Some(vec![40]),
                ),
            },
        );

        assert!(state.grid.sort.is_some());
        assert_eq!(state.grid.rowids, None);
        assert_eq!(state.grid.rows[0].values, vec![Value::Int(3)]);
        assert_eq!(state.grid.rows[1].values, vec![Value::Int(4)]);
        assert!(state.status.contains("row identity mismatch"));

        dispatch(&mut state, AppEvent::EditStart);

        assert_eq!(state.overlay, Overlay::None);
        assert_eq!(
            state.status,
            "editing unavailable for this table (no rowid)"
        );
    }

    #[test]
    fn schema_loaded_opens_schema_overlay() {
        let mut state = state_with_tables();
        let columns = vec![SchemaColumn {
            name: "id".into(),
            col_type: "INTEGER".into(),
            notnull: false,
            pk: true,
        }];
        dispatch(
            &mut state,
            AppEvent::SchemaLoaded {
                table: "authors".into(),
                columns,
            },
        );
        match &state.overlay {
            Overlay::Schema { table, columns } => {
                assert_eq!(table, "authors");
                assert_eq!(columns.len(), 1);
            }
            other => panic!("expected schema overlay, got {other:?}"),
        }
    }

    #[test]
    fn edit_start_prefills_selected_cell() {
        let mut state = state_with_grid();
        dispatch(&mut state, AppEvent::MoveRight);
        dispatch(&mut state, AppEvent::EditStart);
        assert!(matches!(
            &state.overlay,
            Overlay::Edit {
                text,
                target: EditTarget::Cell,
                cell: Some(identity),
            } if text == "1"
                && identity.table == "authors"
                && identity.rowid == 1
                && identity.column == "c1"
                && identity.selected_col == 1
        ));
    }

    #[test]
    fn edit_start_without_rowids_reports_status() {
        let mut state = state_with_tables();
        dispatch(
            &mut state,
            AppEvent::RowsLoaded {
                table: Some("authors".into()),
                page: page(2, 2, false),
            },
        );
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Grid));
        dispatch(&mut state, AppEvent::EditStart);
        assert_eq!(state.overlay, Overlay::None);
        assert!(!state.status.is_empty());
    }

    #[test]
    fn input_chars_go_to_sql_buffer_when_sql_focused() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::FocusPanel(Panel::Sql));
        state.sql_mode = SqlMode::Insert;
        for c in "select 1".chars() {
            dispatch(&mut state, AppEvent::InputChar(c));
        }
        dispatch(&mut state, AppEvent::InputBackspace);
        assert_eq!(state.sql.text, "select ");
    }

    #[test]
    fn input_chars_go_to_edit_overlay_when_editing() {
        let mut state = state_with_grid();
        dispatch(&mut state, AppEvent::EditStart);
        dispatch(&mut state, AppEvent::InputBackspace);
        dispatch(&mut state, AppEvent::InputChar('7'));
        assert!(matches!(
            &state.overlay,
            Overlay::Edit {
                text,
                target: EditTarget::Cell,
                cell: Some(_),
            } if text == "7"
        ));
        assert!(state.sql.text.is_empty());
    }

    #[test]
    fn input_state_moves_between_lines_and_completes_unicode_safely() {
        let mut input = InputState::default();
        input.insert_str("sélect\nfrom");
        input.move_home();
        assert_eq!(input.line_col(), (1, 0));
        input.move_up();
        assert_eq!(input.line_col(), (0, 0));
        input.move_end();
        assert_eq!(input.line_col(), (0, 6));

        input.text = "sel".into();
        input.cursor = 3;
        input.accept_completion("SELECT");
        assert_eq!(input.text, "SELECT");
        assert_eq!(input.cursor, 6);

        input.text = "\"wor".to_string();
        input.cursor = 4;
        input.accept_completion("\"work-items\"");
        assert_eq!(input.text, "\"work-items\"");
    }

    #[test]
    fn suggestions_include_keywords_tables_and_columns() {
        let mut input = InputState::default();
        input.insert_str("sel");
        assert_eq!(
            sql_completions(&input, &[], &BTreeMap::new(), &[])
                .first()
                .map(|item| item.value.as_str()),
            Some("SELECT")
        );

        input.text = "aut".into();
        input.cursor = 3;
        assert_eq!(
            sql_completions(&input, &["authors".into()], &BTreeMap::new(), &[])
                .first()
                .map(|item| item.value.as_str()),
            Some("\"authors\"")
        );

        input.text.clear();
        input.cursor = 0;
        let completions = sql_completions(&input, &["authors".into()], &BTreeMap::new(), &[]);
        assert!(completions.iter().any(|item| item.value == "\"authors\""));
        assert!(completions.iter().any(|item| item.value == "SELECT"));

        input.text = "work".into();
        input.cursor = 4;
        assert_eq!(
            sql_completions(&input, &["work-items".into()], &BTreeMap::new(), &[],)
                .first()
                .map(|item| item.value.as_str()),
            Some("\"work-items\"")
        );
    }

    #[test]
    fn completions_are_contextual_and_schema_aware() {
        let mut schemas = BTreeMap::new();
        schemas.insert(
            "authors".to_string(),
            vec![SchemaColumn {
                name: "display_name".to_string(),
                col_type: "TEXT".to_string(),
                notnull: false,
                pk: false,
            }],
        );
        let mut input = InputState::default();
        input.insert_str("SELECT ");
        let columns = sql_completions(&input, &["authors".into()], &schemas, &[]);
        assert_eq!(columns[0].label, "display_name");
        assert_eq!(columns[0].detail, "authors");

        input.text = "SELECT * FROM ".to_string();
        input.cursor = input.text.chars().count();
        let tables = sql_completions(&input, &["authors".into()], &schemas, &[]);
        assert_eq!(tables[0].label, "authors");
        assert_eq!(tables[0].kind, CompletionKind::Table);

        input.text = "SELECT * FROM \"aut".to_string();
        input.cursor = input.text.chars().count();
        let quoted = sql_completions(&input, &["authors".into()], &schemas, &[]);
        input.accept_completion(&quoted[0].value);
        assert_eq!(input.text, "SELECT * FROM \"authors\"");

        input.text = "SELECT * FROM ord".to_string();
        input.cursor = input.text.chars().count();
        let reserved = sql_completions(&input, &["order".into()], &schemas, &[]);
        assert_eq!(reserved[0].value, "\"order\"");
    }

    #[test]
    fn live_completion_opens_and_accepts_a_fuzzy_match() {
        let mut state = state_with_tables();
        state.focus = Panel::Sql;
        state.sql_mode = SqlMode::Insert;
        state.sql.insert_str("slc");
        state.refresh_completion(false);

        assert_eq!(
            state
                .completion
                .as_ref()
                .and_then(|menu| menu.items.first())
                .map(|item| item.value.as_str()),
            Some("SELECT")
        );
        assert!(state.accept_completion());
        assert_eq!(state.sql.text, "SELECT");
        assert!(state.completion.is_none());
    }

    #[test]
    fn history_search_uses_case_insensitive_typed_subsequences() {
        let mut state = state_with_tables();
        state
            .query_history
            .record("SELECT name FROM authors ORDER BY name");
        state
            .query_history
            .record("SELECT title FROM books ORDER BY title");
        state.query_history.record("UPDATE authors SET name = name");

        state.open_history_search();
        state.insert_history_search("sfbk");

        let Overlay::History(search) = &state.overlay else {
            panic!("expected history overlay");
        };
        assert_eq!(search.query, "sfbk");
        assert_eq!(search.matches, &["SELECT title FROM books ORDER BY title"]);

        state.backspace_history_search();
        let Overlay::History(search) = &state.overlay else {
            panic!("expected history overlay");
        };
        assert_eq!(search.query, "sfb");
        assert_eq!(search.matches.len(), 2);
    }

    #[test]
    fn accepting_history_loads_the_query_without_changing_history_or_status() {
        let mut state = state_with_tables();
        state.query_history.record("SELECT 1");
        state.query_history.record("SELECT 2");
        state.status = "not executed".to_string();
        state.open_history_search();
        state.move_history_selection(1);

        assert!(state.accept_history_selection());

        assert_eq!(state.overlay, Overlay::None);
        assert_eq!(state.sql.text, "SELECT 1");
        assert_eq!(state.sql.cursor, 8);
        assert_eq!(state.sql_mode, SqlMode::Normal);
        assert_eq!(state.status, "not executed");
        assert_eq!(state.query_history.entries(), &["SELECT 1", "SELECT 2"]);
    }

    #[test]
    fn disabled_query_history_does_not_record_even_in_memory() {
        let mut state = state_with_tables();
        state.config.query_history_enabled = false;

        assert!(
            state
                .record_successful_read_query("SELECT 'private'")
                .is_ok()
        );

        assert!(state.query_history.entries().is_empty());
    }

    #[test]
    fn visual_yank_formats_rows_and_columns() {
        let mut state = state_with_grid();
        state.grid.visual = Visual::Rows { anchor: 0 };
        state.grid.selected_row = 1;
        assert_eq!(
            state.visual_yank_text().as_deref(),
            Some("0\t1\t2\n3\t4\t5")
        );

        state.grid.visual = Visual::Column;
        state.grid.selected_col = 1;
        assert_eq!(state.visual_yank_text().as_deref(), Some("1\n4\n7"));
    }

    #[test]
    fn error_and_status_set_message() {
        let mut state = state_with_tables();
        dispatch(&mut state, AppEvent::Error("boom".to_string()));
        assert_eq!(state.status, "boom");
        dispatch(&mut state, AppEvent::Status("copied".to_string()));
        assert_eq!(state.status, "copied");
    }
}