use crate::cli::render::{self, Theme};
use rusqlite::Connection;
use serde::Serialize;

use crate::Config;
use crate::cli::robot::RobotMeta;
use crate::core::db::create_connection;
use crate::core::error::Result;
use crate::core::metrics::StageTiming;
use crate::core::paths::get_db_path;
use crate::core::time::{format_full_datetime, ms_to_iso};

const RECENT_RUNS_LIMIT: usize = 10;

fn is_zero(value: &i64) -> bool {
    *value == 0
}

#[derive(Debug)]
pub struct SyncRunInfo {
    pub id: i64,
    pub started_at: i64,
    pub finished_at: Option<i64>,
    pub status: String,
    pub command: String,
    pub error: Option<String>,
    pub run_id: Option<String>,
    pub total_items_processed: i64,
    pub total_errors: i64,
    pub stages: Option<Vec<StageTiming>>,
    // Per-entity counts (from migration 027)
    pub issues_fetched: i64,
    pub issues_ingested: i64,
    pub mrs_fetched: i64,
    pub mrs_ingested: i64,
    pub skipped_stale: i64,
    pub docs_regenerated: i64,
    pub docs_embedded: i64,
    pub warnings_count: i64,
}

#[derive(Debug)]
pub struct CursorInfo {
    pub project_path: String,
    pub resource_type: String,
    pub updated_at_cursor: Option<i64>,
    pub tie_breaker_id: Option<i64>,
}

#[derive(Debug)]
pub struct DataSummary {
    pub issue_count: i64,
    pub mr_count: i64,
    pub discussion_count: i64,
    pub note_count: i64,
    pub system_note_count: i64,
}

#[derive(Debug)]
pub struct SyncStatusResult {
    pub runs: Vec<SyncRunInfo>,
    pub cursors: Vec<CursorInfo>,
    pub summary: DataSummary,
}

pub fn run_sync_status(config: &Config) -> Result<SyncStatusResult> {
    let db_path = get_db_path(config.storage.db_path.as_deref());
    let conn = create_connection(&db_path)?;

    let runs = get_recent_sync_runs(&conn, RECENT_RUNS_LIMIT)?;
    let cursors = get_cursor_positions(&conn)?;
    let summary = get_data_summary(&conn)?;

    Ok(SyncStatusResult {
        runs,
        cursors,
        summary,
    })
}

fn get_recent_sync_runs(conn: &Connection, limit: usize) -> Result<Vec<SyncRunInfo>> {
    let mut stmt = conn.prepare(
        "SELECT id, started_at, finished_at, status, command, error,
                run_id, total_items_processed, total_errors, metrics_json,
                issues_fetched, issues_ingested, mrs_fetched, mrs_ingested,
                skipped_stale, docs_regenerated, docs_embedded, warnings_count
         FROM sync_runs
         ORDER BY started_at DESC
         LIMIT ?1",
    )?;

    let runs: std::result::Result<Vec<_>, _> = stmt
        .query_map([limit as i64], |row| {
            let metrics_json: Option<String> = row.get(9)?;
            let stages: Option<Vec<StageTiming>> =
                metrics_json.and_then(|json| serde_json::from_str(&json).ok());

            Ok(SyncRunInfo {
                id: row.get(0)?,
                started_at: row.get(1)?,
                finished_at: row.get(2)?,
                status: row.get(3)?,
                command: row.get(4)?,
                error: row.get(5)?,
                run_id: row.get(6)?,
                total_items_processed: row.get::<_, Option<i64>>(7)?.unwrap_or(0),
                total_errors: row.get::<_, Option<i64>>(8)?.unwrap_or(0),
                stages,
                issues_fetched: row.get::<_, Option<i64>>(10)?.unwrap_or(0),
                issues_ingested: row.get::<_, Option<i64>>(11)?.unwrap_or(0),
                mrs_fetched: row.get::<_, Option<i64>>(12)?.unwrap_or(0),
                mrs_ingested: row.get::<_, Option<i64>>(13)?.unwrap_or(0),
                skipped_stale: row.get::<_, Option<i64>>(14)?.unwrap_or(0),
                docs_regenerated: row.get::<_, Option<i64>>(15)?.unwrap_or(0),
                docs_embedded: row.get::<_, Option<i64>>(16)?.unwrap_or(0),
                warnings_count: row.get::<_, Option<i64>>(17)?.unwrap_or(0),
            })
        })?
        .collect();

    Ok(runs?)
}

fn get_cursor_positions(conn: &Connection) -> Result<Vec<CursorInfo>> {
    let mut stmt = conn.prepare(
        "SELECT p.path_with_namespace, sc.resource_type, sc.updated_at_cursor, sc.tie_breaker_id
         FROM sync_cursors sc
         JOIN projects p ON sc.project_id = p.id
         ORDER BY p.path_with_namespace, sc.resource_type",
    )?;

    let cursors: std::result::Result<Vec<_>, _> = stmt
        .query_map([], |row| {
            Ok(CursorInfo {
                project_path: row.get(0)?,
                resource_type: row.get(1)?,
                updated_at_cursor: row.get(2)?,
                tie_breaker_id: row.get(3)?,
            })
        })?
        .collect();

    Ok(cursors?)
}

fn get_data_summary(conn: &Connection) -> Result<DataSummary> {
    let issue_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM issues", [], |row| row.get(0))
        .unwrap_or(0);

    let mr_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM merge_requests", [], |row| row.get(0))
        .unwrap_or(0);

    let discussion_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM discussions", [], |row| row.get(0))
        .unwrap_or(0);

    let (note_count, system_note_count): (i64, i64) = conn
        .query_row(
            "SELECT COUNT(*), COALESCE(SUM(is_system), 0) FROM notes",
            [],
            |row| Ok((row.get(0)?, row.get(1)?)),
        )
        .unwrap_or((0, 0));

    Ok(DataSummary {
        issue_count,
        mr_count,
        discussion_count,
        note_count,
        system_note_count,
    })
}

fn format_duration(ms: i64) -> String {
    let seconds = ms / 1000;
    let minutes = seconds / 60;
    let hours = minutes / 60;

    if hours > 0 {
        format!("{}h {}m {}s", hours, minutes % 60, seconds % 60)
    } else if minutes > 0 {
        format!("{}m {}s", minutes, seconds % 60)
    } else if ms >= 1000 {
        format!("{:.1}s", ms as f64 / 1000.0)
    } else {
        format!("{}ms", ms)
    }
}

#[derive(Serialize)]
struct SyncStatusJsonOutput {
    ok: bool,
    data: SyncStatusJsonData,
    meta: RobotMeta,
}

#[derive(Serialize)]
struct SyncStatusJsonData {
    runs: Vec<SyncRunJsonInfo>,
    cursors: Vec<CursorJsonInfo>,
    summary: SummaryJsonInfo,
}

#[derive(Serialize)]
struct SyncRunJsonInfo {
    id: i64,
    status: String,
    command: String,
    started_at: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    completed_at: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    duration_ms: Option<i64>,
    #[serde(skip_serializing_if = "Option::is_none")]
    run_id: Option<String>,
    total_items_processed: i64,
    total_errors: i64,
    #[serde(skip_serializing_if = "Option::is_none")]
    error: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    stages: Option<Vec<StageTiming>>,
    // Per-entity counts
    #[serde(skip_serializing_if = "is_zero")]
    issues_fetched: i64,
    #[serde(skip_serializing_if = "is_zero")]
    issues_ingested: i64,
    #[serde(skip_serializing_if = "is_zero")]
    mrs_fetched: i64,
    #[serde(skip_serializing_if = "is_zero")]
    mrs_ingested: i64,
    #[serde(skip_serializing_if = "is_zero")]
    skipped_stale: i64,
    #[serde(skip_serializing_if = "is_zero")]
    docs_regenerated: i64,
    #[serde(skip_serializing_if = "is_zero")]
    docs_embedded: i64,
    #[serde(skip_serializing_if = "is_zero")]
    warnings_count: i64,
}

#[derive(Serialize)]
struct CursorJsonInfo {
    project: String,
    resource_type: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    updated_at_cursor: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    tie_breaker_id: Option<i64>,
}

#[derive(Serialize)]
struct SummaryJsonInfo {
    issues: i64,
    merge_requests: i64,
    discussions: i64,
    notes: i64,
    system_notes: i64,
}

pub fn print_sync_status_json(result: &SyncStatusResult, elapsed_ms: u64) {
    let runs = result
        .runs
        .iter()
        .map(|run| {
            let duration_ms = run.finished_at.map(|f| f - run.started_at);
            SyncRunJsonInfo {
                id: run.id,
                status: run.status.clone(),
                command: run.command.clone(),
                started_at: ms_to_iso(run.started_at),
                completed_at: run.finished_at.map(ms_to_iso),
                duration_ms,
                run_id: run.run_id.clone(),
                total_items_processed: run.total_items_processed,
                total_errors: run.total_errors,
                error: run.error.clone(),
                stages: run.stages.clone(),
                issues_fetched: run.issues_fetched,
                issues_ingested: run.issues_ingested,
                mrs_fetched: run.mrs_fetched,
                mrs_ingested: run.mrs_ingested,
                skipped_stale: run.skipped_stale,
                docs_regenerated: run.docs_regenerated,
                docs_embedded: run.docs_embedded,
                warnings_count: run.warnings_count,
            }
        })
        .collect();

    let cursors = result
        .cursors
        .iter()
        .map(|c| CursorJsonInfo {
            project: c.project_path.clone(),
            resource_type: c.resource_type.clone(),
            updated_at_cursor: c.updated_at_cursor.filter(|&ts| ts > 0).map(ms_to_iso),
            tie_breaker_id: c.tie_breaker_id,
        })
        .collect();

    let output = SyncStatusJsonOutput {
        ok: true,
        data: SyncStatusJsonData {
            runs,
            cursors,
            summary: SummaryJsonInfo {
                issues: result.summary.issue_count,
                merge_requests: result.summary.mr_count,
                discussions: result.summary.discussion_count,
                notes: result.summary.note_count - result.summary.system_note_count,
                system_notes: result.summary.system_note_count,
            },
        },
        meta: RobotMeta::new(elapsed_ms),
    };

    match serde_json::to_string(&output) {
        Ok(json) => println!("{json}"),
        Err(e) => eprintln!("Error serializing to JSON: {e}"),
    }
}

pub fn print_sync_status(result: &SyncStatusResult) {
    println!("{}", Theme::bold().underline().render("Recent Sync Runs"));
    println!();

    if result.runs.is_empty() {
        println!("  {}", Theme::dim().render("No sync runs recorded yet."));
        println!(
            "  {}",
            Theme::dim().render("Run 'lore sync' or 'lore ingest' to start.")
        );
    } else {
        for run in &result.runs {
            print_run_line(run);
        }
    }

    println!();

    println!("{}", Theme::bold().underline().render("Cursor Positions"));
    println!();

    if result.cursors.is_empty() {
        println!("  {}", Theme::dim().render("No cursors recorded yet."));
    } else {
        for cursor in &result.cursors {
            println!(
                "  {} ({}):",
                Theme::info().render(&cursor.project_path),
                cursor.resource_type
            );

            match cursor.updated_at_cursor {
                Some(ts) if ts > 0 => {
                    println!("    Last updated_at: {}", ms_to_iso(ts));
                }
                _ => {
                    println!(
                        "    Last updated_at: {}",
                        Theme::dim().render("Not started")
                    );
                }
            }

            if let Some(id) = cursor.tie_breaker_id {
                println!("    Last GitLab ID:  {}", id);
            }
        }
    }

    println!();

    println!("{}", Theme::bold().underline().render("Data Summary"));
    println!();

    println!(
        "  Issues:      {}",
        Theme::bold().render(&render::format_number(result.summary.issue_count))
    );
    println!(
        "  MRs:         {}",
        Theme::bold().render(&render::format_number(result.summary.mr_count))
    );
    println!(
        "  Discussions: {}",
        Theme::bold().render(&render::format_number(result.summary.discussion_count))
    );

    let user_notes = result.summary.note_count - result.summary.system_note_count;
    println!(
        "  Notes:       {} {}",
        Theme::bold().render(&render::format_number(user_notes)),
        Theme::dim().render(&format!(
            "(excluding {} system)",
            render::format_number(result.summary.system_note_count)
        ))
    );
}

fn print_run_line(run: &SyncRunInfo) {
    let status_styled = match run.status.as_str() {
        "succeeded" => Theme::success().render(&run.status),
        "failed" => Theme::error().render(&run.status),
        "running" => Theme::warning().render(&run.status),
        _ => Theme::dim().render(&run.status),
    };

    let run_label = run
        .run_id
        .as_deref()
        .map_or_else(|| format!("#{}", run.id), |id| format!("Run {id}"));

    let duration = run.finished_at.map(|f| format_duration(f - run.started_at));

    let time = format_full_datetime(run.started_at);

    let mut parts = vec![
        Theme::bold().render(&run_label),
        status_styled,
        Theme::dim().render(&run.command),
        time,
    ];

    if let Some(d) = duration {
        parts.push(d);
    } else {
        parts.push("in progress".to_string());
    }

    if run.total_items_processed > 0 {
        parts.push(format!("{} items", run.total_items_processed));
    }

    if run.total_errors > 0 {
        parts.push(Theme::error().render(&format!("{} errors", run.total_errors)));
    }

    println!("  {}", parts.join(" | "));

    if let Some(error) = &run.error {
        println!("    {}", Theme::error().render(error));
    }
}

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

    #[test]
    fn format_duration_handles_seconds() {
        assert_eq!(format_duration(5_000), "5.0s");
        assert_eq!(format_duration(59_000), "59.0s");
    }

    #[test]
    fn format_duration_handles_minutes() {
        assert_eq!(format_duration(60_000), "1m 0s");
        assert_eq!(format_duration(90_000), "1m 30s");
        assert_eq!(format_duration(300_000), "5m 0s");
    }

    #[test]
    fn format_duration_handles_hours() {
        assert_eq!(format_duration(3_600_000), "1h 0m 0s");
        assert_eq!(format_duration(5_400_000), "1h 30m 0s");
        assert_eq!(format_duration(3_723_000), "1h 2m 3s");
    }

    #[test]
    fn format_duration_handles_milliseconds() {
        assert_eq!(format_duration(500), "500ms");
        assert_eq!(format_duration(0), "0ms");
    }

    #[test]
    fn format_number_adds_thousands_separators() {
        assert_eq!(render::format_number(1000), "1,000");
        assert_eq!(render::format_number(1234567), "1,234,567");
    }
}
