use crossterm::event::{KeyCode, KeyEvent, KeyModifiers};
use ratatui::{
    layout::{Constraint, Direction, Layout, Rect},
    style::{Color, Modifier, Style},
    text::{Line, Span},
    widgets::{Block, Borders, Cell, Paragraph, Row, Table, TableState},
    Frame,
};
use sqlx::PgPool;

use crate::tui::app::AppAction;

#[derive(Clone)]
pub struct MigrationInfo {
    name: String,
    applied: bool,
    applied_at: String,
    up_sql: String,
    down_sql: String,
    from_disk: bool,
}

pub struct MigrationsTab {
    items: Vec<MigrationInfo>,
    pub state: TableState,
    pub status: String,
    pub dry_run: bool,
    pub show_pending_only: bool,
    pub editing: bool,
    pub edit_buffer: String,
    pub edit_path: Option<String>,
    pub action_msg: String,
}

impl Default for MigrationsTab {
    fn default() -> Self {
        let mut state = TableState::default();
        state.select(Some(0));
        Self {
            items: Vec::new(),
            state,
            status: "Loading...".into(),
            dry_run: false,
            show_pending_only: false,
            editing: false,
            edit_buffer: String::new(),
            edit_path: None,
            action_msg: String::new(),
        }
    }
}

fn migration_dir() -> &'static str {
    if std::path::Path::new("database/migrations").exists() {
        "database/migrations"
    } else {
        "migrations"
    }
}

fn parse_up_down(content: &str) -> (String, String) {
    if let Some(up_pos) = content.find("-- up\n") {
        let after_up = &content[up_pos + 6..];
        if let Some(down_pos) = after_up.find("-- down\n") {
            return (
                after_up[..down_pos].trim().to_string(),
                after_up[down_pos + 8..].trim().to_string(),
            );
        }
        return (after_up.trim().to_string(), String::new());
    }
    (content.trim().to_string(), String::new())
}

fn read_disk_migrations() -> Vec<(String, String, String, String)> {
    let dir = migration_dir();
    let path = std::path::Path::new(dir);
    if !path.exists() {
        return Vec::new();
    }
    let mut entries: Vec<_> = match std::fs::read_dir(path) {
        Ok(rd) => rd
            .filter_map(|e| e.ok())
            .filter(|e| {
                let p = e.path();
                p.extension().and_then(|s| s.to_str()) == Some("sql")
                    && !p
                        .file_name()
                        .and_then(|s| s.to_str())
                        .map(|s| s.contains(".down."))
                        .unwrap_or(false)
            })
            .collect(),
        Err(_) => return Vec::new(),
    };
    entries.sort_by_key(|e| e.file_name());

    entries
        .into_iter()
        .filter_map(|e| {
            let path = e.path();
            let name = path.file_stem()?.to_str()?.to_string();
            let content = std::fs::read_to_string(&path).ok()?;
            let (up, down) = parse_up_down(&content);
            let full_path = path.to_string_lossy().to_string();
            Some((name, up, down, full_path))
        })
        .collect()
}

impl MigrationsTab {
    pub async fn load(&mut self, pool: &PgPool) {
        let applied = fetch_applied(pool).await;
        let disk = read_disk_migrations();

        let mut items: Vec<MigrationInfo> = Vec::new();
        for (name, up_sql, down_sql, _path) in &disk {
            let already = applied.iter().find(|(n, _)| n == name);
            items.push(MigrationInfo {
                name: name.clone(),
                applied: already.is_some(),
                applied_at: already
                    .map(|(_, t)| t.clone())
                    .unwrap_or_else(|| "-".into()),
                up_sql: up_sql.clone(),
                down_sql: down_sql.clone(),
                from_disk: true,
            });
        }

        let total = items.len();
        let applied_count = items.iter().filter(|m| m.applied).count();
        let pending_count = total - applied_count;
        self.status = format!("{applied_count} applied, {pending_count} pending");
        self.items = items;

        if self.state.selected().is_none() || !self.items.is_empty() {
            let sel = self.state.selected().unwrap_or(0);
            if sel >= self.items.len() {
                self.state.select(Some(self.items.len().saturating_sub(1)));
            }
        }
        self.action_msg.clear();
    }

    fn selected_item(&self) -> Option<&MigrationInfo> {
        self.state
            .selected()
            .and_then(|i| self.items.get(i))
    }

    pub fn render(&mut self, frame: &mut Frame, area: Rect) {
        if self.editing {
            return self.render_editor(frame, area);
        }

        let has_preview = self.selected_item().map_or(false, |m| !m.up_sql.is_empty());
        let constraints = if has_preview {
            [Constraint::Ratio(1, 2), Constraint::Ratio(1, 2)]
        } else {
            [Constraint::Min(0), Constraint::Length(0)]
        };

        let chunks = Layout::default()
            .direction(Direction::Vertical)
            .constraints(constraints)
            .split(area);

        self.render_list(frame, chunks[0]);
        self.render_preview(frame, chunks[1]);
    }

    fn render_list(&mut self, frame: &mut Frame, area: Rect) {
        let items: Vec<MigrationInfo> = if self.show_pending_only {
            self.items.iter().filter(|m| !m.applied).cloned().collect()
        } else {
            self.items.clone()
        };

        let header = Row::new(vec![
            Cell::from("Status").style(Style::default().add_modifier(Modifier::BOLD)),
            Cell::from("Migration").style(Style::default().add_modifier(Modifier::BOLD)),
            Cell::from("Applied At").style(Style::default().add_modifier(Modifier::BOLD)),
        ])
        .height(1);

        let rows: Vec<Row> = items
            .iter()
            .map(|m| {
                let (icon, color) = if m.applied {
                    ("✅", Color::Green)
                } else {
                    ("⏳", Color::Yellow)
                };
                Row::new(vec![
                    Cell::from(icon).style(Style::default().fg(color)),
                    Cell::from(m.name.as_str()),
                    Cell::from(m.applied_at.as_str())
                        .style(Style::default().fg(Color::DarkGray)),
                ])
            })
            .collect();

        let widths = [
            Constraint::Length(8),
            Constraint::Min(30),
            Constraint::Length(22),
        ];

        let mode = if self.dry_run { " DRY-RUN " } else { "" };
        let filter = if self.show_pending_only {
            " (pending only)"
        } else {
            ""
        };
        let title = format!(" Migrations{filter} — {}{mode} ", self.status);
        let table = Table::new(rows, widths)
            .header(header)
            .block(
                Block::default()
                    .title(title)
                    .borders(Borders::ALL)
                    .border_style(if self.dry_run {
                        Style::default().fg(Color::Yellow)
                    } else {
                        Style::default().fg(Color::White)
                    }),
            )
            .highlight_style(Style::default().bg(Color::DarkGray))
            .highlight_symbol("▶ ");

        frame.render_stateful_widget(table, area, &mut self.state);
    }

    fn render_preview(&self, frame: &mut Frame, area: Rect) {
        if area.width < 10 || area.height < 3 {
            return;
        }

        let Some(m) = self.selected_item() else {
            return;
        };

        let chunks = Layout::default()
            .direction(Direction::Vertical)
            .constraints([Constraint::Min(3), Constraint::Length(3)])
            .split(area);

        let mut lines = Vec::new();
        lines.push(Line::from(vec![
            Span::styled(
                format!(" {} ", m.name),
                Style::default()
                    .fg(Color::Cyan)
                    .add_modifier(Modifier::BOLD),
            ),
        ]));
        lines.push(Line::from(Span::raw("")));

        let preview = if !m.up_sql.is_empty() {
            &m.up_sql
        } else {
            "(no -- up section found in file)"
        };

        for line in preview.lines() {
            lines.push(Line::from(Span::raw(line.to_string())));
        }

        if m.applied && !m.down_sql.is_empty() {
            lines.push(Line::from(Span::raw("")));
            lines.push(Line::from(vec![
                Span::styled(
                    " Rollback SQL:",
                    Style::default().fg(Color::Red),
                ),
            ]));
            for line in m.down_sql.lines() {
                lines.push(Line::from(vec![
                    Span::styled("  ", Style::default().fg(Color::Red)),
                    Span::raw(line.to_string()),
                ]));
            }
        }

        let para = Paragraph::new(lines).block(
            Block::default()
                .title(" SQL Preview ")
                .borders(Borders::ALL),
        );
        frame.render_widget(para, chunks[0]);

        let action_style = if self.action_msg.starts_with("✓") {
            Color::Green
        } else if self.action_msg.starts_with("✗") {
            Color::Red
        } else {
            Color::DarkGray
        };

        let help = Paragraph::new(Line::from(vec![
            Span::styled(" F5 ", Style::default().fg(Color::Cyan)),
            Span::raw("Up  "),
            Span::styled(" F6 ", Style::default().fg(Color::Cyan)),
            Span::raw("Down  "),
            Span::styled(" F7 ", Style::default().fg(Color::Cyan)),
            Span::raw("Apply all  "),
            Span::styled(" F8 ", Style::default().fg(Color::Cyan)),
            Span::raw(if self.dry_run { "Live" } else { "Dry-run" }),
            Span::raw("  "),
            Span::styled(" Enter ", Style::default().fg(Color::Cyan)),
            Span::raw("Edit  "),
            Span::styled(" p ", Style::default().fg(Color::Cyan)),
            Span::raw("Toggle pending  "),
            Span::styled(
                &self.action_msg,
                Style::default().fg(action_style),
            ),
        ]))
        .block(
            Block::default()
                .title(" Actions ")
                .borders(Borders::ALL),
        );
        frame.render_widget(help, chunks[1]);
    }

    fn render_editor(&mut self, frame: &mut Frame, area: Rect) {
        let chunks = Layout::default()
            .direction(Direction::Vertical)
            .constraints([Constraint::Min(3), Constraint::Length(3)])
            .split(area);

        let line_count = self.edit_buffer.lines().count().max(1);
        let display = if line_count > 5 {
            let take: String = self
                .edit_buffer
                .lines()
                .take(5)
                .collect::<Vec<_>>()
                .join("\n");
            format!("{take}\n... ({line_count} lines)")
        } else {
            self.edit_buffer.clone()
        };

        let para = Paragraph::new(display.as_str())
            .style(Style::default().fg(Color::Yellow))
            .block(
                Block::default()
                    .title(" Edit SQL — Ctrl+S save, Esc cancel ")
                    .borders(Borders::ALL)
                    .border_style(Style::default().fg(Color::Yellow)),
            );
        frame.render_widget(para, chunks[0]);

        let hint = Paragraph::new(Line::from(vec![
            Span::styled(" Ctrl+S ", Style::default().fg(Color::Green)),
            Span::raw("Save & close  "),
            Span::styled(" Esc ", Style::default().fg(Color::Red)),
            Span::raw("Discard"),
        ]))
        .block(Block::default().title(" Editor ").borders(Borders::ALL));
        frame.render_widget(hint, chunks[1]);
    }

    pub async fn apply_up(&mut self, pool: &PgPool) {
        let Some(m) = self.selected_item().filter(|m| !m.applied) else {
            self.action_msg = "✗ No pending migration selected".into();
            return;
        };
        if m.up_sql.is_empty() {
            self.action_msg = "✗ Migration has no UP SQL".into();
            return;
        }

        if self.dry_run {
            self.action_msg = format!("✓ [DRY-RUN] Would apply: {}", m.name);
            return;
        }

        match run_sql_up(pool, &m.name, &m.up_sql).await {
            Ok(_) => self.action_msg = format!("✓ Applied: {}", m.name),
            Err(e) => self.action_msg = format!("✗ Failed: {e}"),
        }
    }

    pub async fn rollback_down(&mut self, pool: &PgPool) {
        let Some(m) = self.selected_item().filter(|m| m.applied) else {
            self.action_msg = "✗ No applied migration selected".into();
            return;
        };
        if m.down_sql.is_empty() {
            self.action_msg = format!("✗ {} has no DOWN SQL", m.name);
            return;
        }

        if self.dry_run {
            self.action_msg = format!("✓ [DRY-RUN] Would rollback: {}", m.name);
            return;
        }

        match run_sql_down(pool, &m.name, &m.down_sql).await {
            Ok(_) => self.action_msg = format!("✓ Rolled back: {}", m.name),
            Err(e) => self.action_msg = format!("✗ Failed: {e}"),
        }
    }

    pub async fn apply_all(&mut self, pool: &PgPool) {
        let pending: Vec<&MigrationInfo> = self.items.iter().filter(|m| !m.applied).collect();
        if pending.is_empty() {
            self.action_msg = "✗ No pending migrations".into();
            return;
        }

        if self.dry_run {
            let names: Vec<&str> = pending.iter().map(|m| m.name.as_str()).collect();
            self.action_msg = format!("✓ [DRY-RUN] Would apply {}: {}", pending.len(), names.join(", "));
            return;
        }

        let mut ok = 0usize;
        let mut last_err = String::new();
        for m in &pending {
            if m.up_sql.is_empty() {
                continue;
            }
            match run_sql_up(pool, &m.name, &m.up_sql).await {
                Ok(_) => ok += 1,
                Err(e) => {
                    last_err = format!("{}: {e}", m.name);
                    break;
                }
            }
        }

        if last_err.is_empty() {
            self.action_msg = format!("✓ Applied {ok} migration(s)");
        } else {
            self.action_msg = format!("✗ Stopped at {last_err}");
        }
    }

    pub fn toggle_dry_run(&mut self) {
        self.dry_run = !self.dry_run;
        self.action_msg = if self.dry_run {
            "✓ Dry-run mode ON".into()
        } else {
            "✓ Live mode".into()
        };
    }

    pub fn toggle_pending(&mut self) {
        self.show_pending_only = !self.show_pending_only;
        self.state.select(Some(0));
        self.action_msg.clear();
    }

    pub fn start_edit(&mut self) {
        let Some(m) = self.selected_item().filter(|m| m.from_disk) else {
            self.action_msg = "✗ Select a disk-based migration to edit".into();
            return;
        };
        let dir = migration_dir();
        let path = format!("{dir}/{}.sql", m.name);
        if let Ok(content) = std::fs::read_to_string(&path) {
            self.edit_buffer = content;
            self.edit_path = Some(path);
            self.editing = true;
            self.action_msg.clear();
        } else {
            self.action_msg = format!("✗ Cannot read {path}");
        }
    }

    pub fn save_edit(&mut self) {
        if let Some(ref path) = self.edit_path.clone() {
            if std::fs::write(path, &self.edit_buffer).is_ok() {
                self.action_msg = format!("✓ Saved {path}");
            } else {
                self.action_msg = format!("✗ Failed to save {path}");
            }
        }
        self.editing = false;
        self.edit_path = None;
        self.edit_buffer.clear();
    }

    pub fn cancel_edit(&mut self) {
        self.editing = false;
        self.edit_path = None;
        self.edit_buffer.clear();
        self.action_msg = "✗ Edit cancelled".into();
    }

    pub fn handle_key(&mut self, key: KeyEvent) -> AppAction {
        if self.editing {
            match key.code {
                KeyCode::Char(c) if key.modifiers.contains(KeyModifiers::CONTROL) => match c {
                    's' => {
                        self.save_edit();
                        AppAction::Reload
                    }
                    _ => AppAction::None,
                },
                KeyCode::Esc => {
                    self.cancel_edit();
                    AppAction::Reload
                }
                _ => AppAction::None,
            }
        } else {
            match key.code {
                KeyCode::Up => {
                    let max = self.items.len().saturating_sub(1);
                    let i = self.state.selected().unwrap_or(0).saturating_sub(1).min(max);
                    self.state.select(Some(i));
                    AppAction::None
                }
                KeyCode::Down => {
                    let max = self.items.len().saturating_sub(1);
                    let i = (self.state.selected().unwrap_or(0) + 1).min(max);
                    self.state.select(Some(i));
                    AppAction::None
                }
                KeyCode::F(5) => AppAction::MigrateUp,
                KeyCode::F(6) => AppAction::MigrateDown,
                KeyCode::F(7) => AppAction::MigrateAll,
                KeyCode::F(8) => {
                    self.toggle_dry_run();
                    AppAction::None
                }
                KeyCode::Char('p') | KeyCode::Char('P') => {
                    self.toggle_pending();
                    AppAction::Reload
                }
                KeyCode::Enter => {
                    self.start_edit();
                    AppAction::None
                }
                KeyCode::Char('r') | KeyCode::Char('R') => AppAction::Reload,
                _ => AppAction::None,
            }
        }
    }
}

async fn fetch_applied(pool: &PgPool) -> Vec<(String, String)> {
    use sqlx::Row as _;

    sqlx::query(
        "SELECT name, to_char(run_at, 'YYYY-MM-DD HH24:MI:SS') AS applied_at \
         FROM _migrations ORDER BY id",
    )
    .fetch_all(pool)
    .await
    .unwrap_or_default()
    .into_iter()
    .map(|r| {
        (
            r.try_get::<String, _>("name").unwrap_or_default(),
            r.try_get::<String, _>("applied_at").unwrap_or_else(|_| "-".into()),
        )
    })
    .collect()
}

async fn run_sql_up(pool: &PgPool, name: &str, sql: &str) -> anyhow::Result<()> {
    for stmt in sql.split(';') {
        let stmt = stmt.trim();
        if !stmt.is_empty() {
            sqlx::query(stmt).execute(pool).await?;
        }
    }
    sqlx::query("INSERT INTO _migrations (name, batch) VALUES ($1, (SELECT COALESCE(MAX(batch), 0) + 1 FROM _migrations))")
        .bind(name)
        .execute(pool)
        .await?;
    Ok(())
}

async fn run_sql_down(pool: &PgPool, name: &str, sql: &str) -> anyhow::Result<()> {
    for stmt in sql.split(';') {
        let stmt = stmt.trim();
        if !stmt.is_empty() {
            sqlx::query(stmt).execute(pool).await?;
        }
    }
    sqlx::query("DELETE FROM _migrations WHERE name = $1")
        .bind(name)
        .execute(pool)
        .await?;
    Ok(())
}
