//! Events query command handlers.

use crate::cli::args::{EventsCommand, CodesCommand, GlobalArgs};
use crate::cli::output::OutputWriter;
use crate::db::duckdb::{AnalyticsDb, EventFilters, validation};
use crate::error::{ExitStatus, GdeltError, Result};
use crate::models::cameo;
use serde_json::json;

/// Handle events commands
pub async fn handle_events(cmd: EventsCommand, global: &GlobalArgs) -> Result<ExitStatus> {
    match cmd {
        EventsCommand::Query(args) => {
            let db = AnalyticsDb::open()?;
            let output = OutputWriter::new(global);

            let filters = EventFilters {
                actor: args.actor.clone(),
                event_code: args.event_code.clone(),
                quad_class: args.quad_class,
                country: args.country.clone(),
                start_date: args.start.clone(),
                end_date: args.end.clone(),
                goldstein_min: args.goldstein_min,
                goldstein_max: args.goldstein_max,
                limit: args.limit,
                offset: args.offset,
            };

            let events = db.query_events(filters)?;

            // If no results, check if we have any data and provide guidance
            if events.is_empty() && !global.quiet {
                let stats = db.stats()?;
                if stats.events_count == 0 {
                    eprintln!("No results found. No local event data available.");
                    eprintln!();
                    eprintln!("Sync data first:");
                    eprintln!("  gdelt data sync");
                    eprintln!("  gdelt data download events --start 2024-01-01 --end 2024-01-31");
                } else {
                    eprintln!("No results found for your query.");
                    eprintln!();
                    eprintln!("Try broadening your search:");
                    if args.actor.is_some() || args.country.is_some() || args.event_code.is_some() {
                        eprintln!("  - Remove filters (--actor, --country, --event-code)");
                    }
                    if args.start.is_some() || args.end.is_some() {
                        eprintln!("  - Expand date range (--start, --end)");
                    }
                    if args.goldstein_min.is_some() || args.goldstein_max.is_some() {
                        eprintln!("  - Adjust Goldstein scale filters");
                    }
                    eprintln!();
                    eprintln!("Check available data range: gdelt data status");
                }
            }

            output.write_value(&events)?;

            Ok(ExitStatus::Success)
        }

        EventsCommand::Lookup(args) => {
            let db = AnalyticsDb::open()?;
            let output = OutputWriter::new(global);

            let event_id: i64 = args.event_id.parse()
                .map_err(|_| GdeltError::Validation("Invalid event ID".into()))?;

            match db.lookup_event(event_id)? {
                Some(event) => {
                    output.write_value(&event)?;
                    Ok(ExitStatus::Success)
                }
                None => {
                    Err(GdeltError::NotFound(format!("Event {} not found", event_id)))
                }
            }
        }

        EventsCommand::Actors(args) => {
            let db = AnalyticsDb::open()?;
            let output = OutputWriter::new(global);

            // Validate and prepare parameters
            let actor_pattern: Option<String> = if let Some(ref actor) = args.actor {
                let validated = validation::validate_actor_code(actor)?;
                Some(format!("{}%", validated))
            } else {
                None
            };

            let (sql, params): (String, Vec<&dyn duckdb::ToSql>) = if args.stats {
                if let Some(ref pattern) = actor_pattern {
                    (
                        format!(
                            r#"
                            SELECT
                                actor1_code as actor_code,
                                COUNT(*) as event_count,
                                AVG(goldstein_scale) as avg_goldstein,
                                AVG(avg_tone) as avg_tone
                            FROM events
                            WHERE actor1_code IS NOT NULL
                            AND actor1_code LIKE ?
                            GROUP BY actor1_code
                            ORDER BY event_count DESC
                            LIMIT {}
                            "#,
                            args.limit
                        ),
                        vec![pattern as &dyn duckdb::ToSql]
                    )
                } else {
                    (
                        format!(
                            r#"
                            SELECT
                                actor1_code as actor_code,
                                COUNT(*) as event_count,
                                AVG(goldstein_scale) as avg_goldstein,
                                AVG(avg_tone) as avg_tone
                            FROM events
                            WHERE actor1_code IS NOT NULL
                            GROUP BY actor1_code
                            ORDER BY event_count DESC
                            LIMIT {}
                            "#,
                            args.limit
                        ),
                        vec![]
                    )
                }
            } else if let Some(ref pattern) = actor_pattern {
                (
                    format!(
                        r#"
                        SELECT DISTINCT actor1_code as actor_code
                        FROM events
                        WHERE actor1_code IS NOT NULL
                        AND actor1_code LIKE ?
                        ORDER BY actor1_code
                        LIMIT {}
                        "#,
                        args.limit
                    ),
                    vec![pattern as &dyn duckdb::ToSql]
                )
            } else {
                (
                    format!(
                        r#"
                        SELECT DISTINCT actor1_code as actor_code
                        FROM events
                        WHERE actor1_code IS NOT NULL
                        ORDER BY actor1_code
                        LIMIT {}
                        "#,
                        args.limit
                    ),
                    vec![]
                )
            };

            let result = db.query_with_params(&sql, &params)?;
            output.write_value(&result)?;

            Ok(ExitStatus::Success)
        }

        EventsCommand::Countries(args) => {
            let db = AnalyticsDb::open()?;
            let output = OutputWriter::new(global);

            // Build parameterized query
            let mut where_clauses = vec!["action_geo_country_code IS NOT NULL".to_string()];
            let mut params: Vec<Box<dyn duckdb::ToSql>> = Vec::new();

            if let Some(ref country) = args.country {
                let validated = validation::validate_country_code(country)?;
                where_clauses.push("action_geo_country_code = ?".to_string());
                params.push(Box::new(validated));
            }
            if let Some(ref start) = args.start {
                let date_int = validation::validate_date(start)?;
                where_clauses.push("sql_date >= ?".to_string());
                params.push(Box::new(date_int));
            }
            if let Some(ref end) = args.end {
                let date_int = validation::validate_date(end)?;
                where_clauses.push("sql_date <= ?".to_string());
                params.push(Box::new(date_int));
            }

            let sql = format!(
                r#"
                SELECT
                    action_geo_country_code as country_code,
                    COUNT(*) as event_count,
                    AVG(goldstein_scale) as avg_goldstein,
                    AVG(avg_tone) as avg_tone
                FROM events
                WHERE {}
                GROUP BY action_geo_country_code
                ORDER BY event_count DESC
                LIMIT 50
                "#,
                where_clauses.join(" AND ")
            );

            let params_refs: Vec<&dyn duckdb::ToSql> = params.iter().map(|p| p.as_ref()).collect();
            let result = db.query_with_params(&sql, &params_refs)?;
            output.write_value(&result)?;

            Ok(ExitStatus::Success)
        }

        EventsCommand::Codes(codes_cmd) => handle_codes(codes_cmd, global).await,
    }
}

async fn handle_codes(cmd: CodesCommand, global: &GlobalArgs) -> Result<ExitStatus> {
    let output = OutputWriter::new(global);

    match cmd {
        CodesCommand::List(args) => {
            use crate::cli::args::CodeType;

            let result = match args.code_type {
                Some(CodeType::Event) | None => {
                    json!({
                        "type": "event",
                        "codes": cameo::ROOT_CODES.iter().map(|c| json!({
                            "code": c.code,
                            "description": c.description,
                            "quad_class": c.quad_class,
                            "goldstein_scale": c.goldstein_scale,
                        })).collect::<Vec<_>>()
                    })
                }
                Some(CodeType::Actor) => {
                    json!({
                        "type": "actor",
                        "codes": cameo::ACTOR_TYPE_CODES.iter().map(|(code, desc)| json!({
                            "code": code,
                            "description": desc,
                        })).collect::<Vec<_>>()
                    })
                }
                Some(CodeType::Country) => {
                    json!({
                        "type": "country",
                        "codes": cameo::MAJOR_COUNTRIES.iter().map(|c| json!({
                            "code": c.code,
                            "name": c.name,
                            "fips": c.fips,
                            "iso": c.iso,
                        })).collect::<Vec<_>>()
                    })
                }
                Some(CodeType::Ethnic) => {
                    json!({
                        "type": "ethnic",
                        "message": "Ethnic codes are extensive - use search instead"
                    })
                }
                Some(CodeType::Religion) => {
                    json!({
                        "type": "religion",
                        "codes": [
                            {"code": "CHR", "description": "Christian"},
                            {"code": "MOS", "description": "Muslim"},
                            {"code": "JEW", "description": "Jewish"},
                            {"code": "BUD", "description": "Buddhist"},
                            {"code": "HIN", "description": "Hindu"},
                            {"code": "ATH", "description": "Atheist"},
                            {"code": "AGN", "description": "Agnostic"},
                        ]
                    })
                }
            };

            output.write_value(&result)?;
            Ok(ExitStatus::Success)
        }

        CodesCommand::Search(args) => {
            let results = cameo::search_codes(&args.pattern);

            let codes: Vec<_> = results.iter().map(|c| json!({
                "code": c.code,
                "description": c.description,
                "quad_class": c.quad_class,
                "goldstein_scale": c.goldstein_scale,
            })).collect();

            output.write_value(&json!({ "results": codes }))?;
            Ok(ExitStatus::Success)
        }

        CodesCommand::Describe(args) => {
            if let Some(code) = cameo::lookup_code(&args.code) {
                let quad_desc = code.quad_class
                    .and_then(|q| cameo::quad_class_description(q))
                    .map(|s| s.to_string());

                output.write_value(&json!({
                    "code": code.code,
                    "description": code.description,
                    "quad_class": code.quad_class,
                    "quad_class_description": quad_desc,
                    "goldstein_scale": code.goldstein_scale,
                }))?;
                Ok(ExitStatus::Success)
            } else if let Some(actor_desc) = cameo::actor_type_description(&args.code.to_uppercase()) {
                output.write_value(&json!({
                    "type": "actor",
                    "code": args.code.to_uppercase(),
                    "description": actor_desc,
                }))?;
                Ok(ExitStatus::Success)
            } else if let Some(country) = cameo::lookup_country(&args.code) {
                output.write_value(&json!({
                    "type": "country",
                    "code": country.code,
                    "name": country.name,
                    "fips": country.fips,
                    "iso": country.iso,
                }))?;
                Ok(ExitStatus::Success)
            } else {
                Err(GdeltError::NotFound(format!("Code not found: {}", args.code)))
            }
        }
    }
}
