use rouille::{Request, Response};
use std::sync::Arc;

use super::helpers::{get_conn, json_error, require_auth};
use super::AppState;

/// GET /api/reports/export/{format}?type={reportType}&range={dateRange}&billing_id={id}
pub fn export(request: &Request, state: &Arc<AppState>, format: &str) -> Response {
    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    let qs = request.raw_query_string();
    let params = parse_query(&qs);
    let report_type = params.iter().find(|(k,_)| *k == "type").map(|(_,v)| *v).unwrap_or("sync");
    let date_range = params.iter().find(|(k,_)| *k == "range").map(|(_,v)| *v).unwrap_or("7d");
    let billing_id = params.iter().find(|(k,_)| *k == "billing_id").and_then(|(_,v)| v.parse::<i32>().ok());

    if let Some(bid) = billing_id {
        return export_billing_record(&mut conn, bid, format);
    }

    match report_type {
        "sync" => export_sync_report(&mut conn, date_range, format),
        "usage" => export_usage_report(&mut conn, date_range, format),
        "revenue" => export_revenue_report(&mut conn, date_range, format),
        "billing" => export_billing_report(&mut conn, date_range, format),
        "audit" => export_audit_report(&mut conn, date_range, format),
        _ => export_sync_report(&mut conn, date_range, format),
    }
}

fn export_sync_report(conn: &mut postgres::Client, range: &str, fmt: &str) -> Response {
    let interval = range_to_interval(range);
    let sql = format!(
        "SELECT id, job_type, sync_direction, status::text, records_processed, \
         records_failed, created_at::text, completed_at::text \
         FROM sync_jobs WHERE created_at > NOW() - INTERVAL '{}' \
         ORDER BY created_at DESC", interval);
    let rows = match conn.query(sql.as_str(), &[]) {
        Ok(r) => r,
        Err(e) => {
            log::error!("Sync report query error: {}", e);
            return json_error(500, "Database error");
        }
    };
    let headers = &["ID","Job Type","Direction","Status","Processed","Failed","Created","Completed"];
    let data: Vec<Vec<String>> = rows.iter().map(|r| vec![
        r.get::<_,i32>(0).to_string(), r.get::<_,String>(1), r.get::<_,String>(2),
        r.get::<_,String>(3), r.get::<_,i32>(4).to_string(), r.get::<_,i32>(5).to_string(),
        r.get::<_,String>(6), r.get::<_,Option<String>>(7).unwrap_or_default(),
    ]).collect();
    render(headers, &data, fmt, "sync-report")
}

fn export_usage_report(conn: &mut postgres::Client, range: &str, fmt: &str) -> Response {
    let interval = range_to_interval(range);
    let sql = format!(
        "SELECT id, subscription_id, metric_type::text, metric_value::float8, \
         COALESCE(unit, ''), recorded_at::text \
         FROM usage_metrics WHERE recorded_at > NOW() - INTERVAL '{}' \
         ORDER BY recorded_at DESC", interval);
    let rows = match conn.query(sql.as_str(), &[]) {
        Ok(r) => r,
        Err(e) => {
            log::error!("Usage report query error: {}", e);
            return json_error(500, "Database error");
        }
    };
    let headers = &["ID","Subscription","Metric Type","Value","Unit","Recorded At"];
    let data: Vec<Vec<String>> = rows.iter().map(|r| vec![
        r.get::<_,i32>(0).to_string(), r.get::<_,i32>(1).to_string(),
        r.get::<_,String>(2), format!("{:.2}", r.get::<_,f64>(3)),
        r.get::<_,String>(4), r.get::<_,String>(5),
    ]).collect();
    render(headers, &data, fmt, "usage-report")
}

fn export_revenue_report(conn: &mut postgres::Client, range: &str, fmt: &str) -> Response {
    let interval = range_to_interval(range);
    let sql = format!(
        "SELECT b.id, COALESCE(c.name, 'Customer ' || b.customer_id::text), \
         b.period_start::text, b.period_end::text, b.calculated_amount::float8, \
         b.currency, b.invoice_status, b.created_at::text \
         FROM billing_records b \
         LEFT JOIN customers c ON c.id = b.customer_id \
         WHERE b.created_at > NOW() - INTERVAL '{}' \
         ORDER BY b.created_at DESC", interval);
    let rows = match conn.query(sql.as_str(), &[]) {
        Ok(r) => r,
        Err(e) => {
            log::error!("Revenue report query error: {}", e);
            return json_error(500, "Database error");
        }
    };
    let headers = &["ID","Customer","Period Start","Period End","Amount","Currency","Status","Created"];
    let data: Vec<Vec<String>> = rows.iter().map(|r| vec![
        r.get::<_,i32>(0).to_string(), r.get::<_,String>(1),
        r.get::<_,String>(2), r.get::<_,String>(3),
        format!("{:.2}", r.get::<_,f64>(4)), r.get::<_,String>(5),
        r.get::<_,String>(6), r.get::<_,String>(7),
    ]).collect();
    render(headers, &data, fmt, "revenue-report")
}

fn export_billing_report(conn: &mut postgres::Client, range: &str, fmt: &str) -> Response {
    let interval = range_to_interval(range);
    let sql = format!(
        "SELECT id, customer_id, period_start::text, period_end::text, \
         calculated_amount::float8, currency, invoice_status, created_at::text \
         FROM billing_records WHERE created_at > NOW() - INTERVAL '{}' \
         ORDER BY created_at DESC", interval);
    let rows = match conn.query(sql.as_str(), &[]) {
        Ok(r) => r,
        Err(e) => {
            log::error!("Billing report query error: {}", e);
            return json_error(500, "Database error");
        }
    };
    let headers = &["ID","Customer","Period Start","Period End","Amount","Currency","Status","Created"];
    let data: Vec<Vec<String>> = rows.iter().map(|r| vec![
        r.get::<_,i32>(0).to_string(), r.get::<_,i32>(1).to_string(),
        r.get::<_,String>(2), r.get::<_,String>(3),
        format!("{:.2}", r.get::<_,f64>(4)), r.get::<_,String>(5),
        r.get::<_,String>(6), r.get::<_,String>(7),
    ]).collect();
    render(headers, &data, fmt, "billing-report")
}

fn export_audit_report(conn: &mut postgres::Client, range: &str, fmt: &str) -> Response {
    let interval = range_to_interval(range);
    let sql = format!(
        "SELECT a.id, u.username, a.event, host(a.ip_address) as ip, \
         a.user_agent, a.timestamp::text \
         FROM session_audit_log a LEFT JOIN users u ON u.id = a.user_id \
         WHERE a.timestamp > NOW() - INTERVAL '{}' ORDER BY a.timestamp DESC", interval);
    let rows = match conn.query(sql.as_str(), &[]) {
        Ok(r) => r,
        Err(e) => {
            log::error!("Audit report query error: {}", e);
            return json_error(500, "Database error");
        }
    };
    let headers = &["ID","User","Event","IP","User Agent","Timestamp"];
    let data: Vec<Vec<String>> = rows.iter().map(|r| vec![
        r.get::<_,i32>(0).to_string(),
        r.get::<_,Option<String>>(1).unwrap_or_default(),
        r.get::<_,String>(2),
        r.get::<_,Option<String>>(3).unwrap_or_default(),
        r.get::<_,Option<String>>(4).unwrap_or_default(),
        r.get::<_,String>(5),
    ]).collect();
    render(headers, &data, fmt, "audit-report")
}

fn export_billing_record(conn: &mut postgres::Client, id: i32, fmt: &str) -> Response {
    let row = match conn.query_opt(
        "SELECT b.id, b.customer_id, c.name, b.period_start::text, b.period_end::text, \
         b.calculated_amount::float8, b.currency, b.invoice_status, b.created_at::text \
         FROM billing_records b LEFT JOIN customers c ON c.id = b.customer_id WHERE b.id = $1",
        &[&id],
    ) {
        Ok(Some(r)) => r,
        Ok(None) => return json_error(404, "Billing record not found"),
        Err(e) => {
            log::error!("Billing record query error: {}", e);
            return json_error(500, "Database error");
        }
    };
    let headers = &["ID","Customer ID","Customer","Start","End","Amount","Currency","Status","Created"];
    let data = vec![vec![
        row.get::<_,i32>(0).to_string(), row.get::<_,i32>(1).to_string(),
        row.get::<_,Option<String>>(2).unwrap_or_else(|| "N/A".into()),
        row.get::<_,String>(3), row.get::<_,String>(4),
        format!("{:.2}", row.get::<_,f64>(5)),
        row.get::<_,String>(6), row.get::<_,String>(7), row.get::<_,String>(8),
    ]];
    render(headers, &data, fmt, &format!("invoice-{}", id))
}

fn render(headers: &[&str], data: &[Vec<String>], fmt: &str, name: &str) -> Response {
    match fmt {
        "csv" => render_csv(headers, data, name),
        "xlsx" => render_xlsx(headers, data, name),
        "pdf" => render_pdf(headers, data, name),
        _ => json_error(400, &format!("Unsupported format: {}", fmt)),
    }
}

fn render_csv(headers: &[&str], data: &[Vec<String>], name: &str) -> Response {
    let mut wtr = csv::Writer::from_writer(Vec::new());
    let _ = wtr.write_record(headers);
    for row in data { let _ = wtr.write_record(row); }
    let bytes = wtr.into_inner().unwrap_or_default();
    Response::from_data("text/csv; charset=utf-8", bytes).with_additional_header(
        "Content-Disposition", format!("attachment; filename=\"{}.csv\"", name))
}

fn render_xlsx(headers: &[&str], data: &[Vec<String>], name: &str) -> Response {
    let mut wb = rust_xlsxwriter::Workbook::new();
    let ws = wb.add_worksheet();
    let bold = rust_xlsxwriter::Format::new().set_bold();
    for (c, h) in headers.iter().enumerate() {
        let _ = ws.write_string_with_format(0, c as u16, *h, &bold);
    }
    for (ri, row) in data.iter().enumerate() {
        for (ci, cell) in row.iter().enumerate() {
            if let Ok(n) = cell.parse::<f64>() {
                let _ = ws.write_number((ri+1) as u32, ci as u16, n);
            } else {
                let _ = ws.write_string((ri+1) as u32, ci as u16, cell);
            }
        }
    }
    match wb.save_to_buffer() {
        Ok(buf) => Response::from_data(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", buf,
        ).with_additional_header("Content-Disposition", format!("attachment; filename=\"{}.xlsx\"", name)),
        Err(e) => json_error(500, &format!("XLSX error: {}", e)),
    }
}

fn render_pdf(headers: &[&str], data: &[Vec<String>], name: &str) -> Response {
    let pdf = build_pdf(headers, data, name);
    Response::from_data("application/pdf", pdf).with_additional_header(
        "Content-Disposition", format!("attachment; filename=\"{}.pdf\"", name))
}

fn build_pdf(headers: &[&str], data: &[Vec<String>], title: &str) -> Vec<u8> {
    let date = chrono::Utc::now().format("%Y-%m-%d %H:%M UTC").to_string();
    let esc = |s: &str| s.replace('\\', "\\\\").replace('(', "\\(").replace(')', "\\)");
    let mut lines: Vec<String> = vec![
        esc(title), esc(&format!("Generated: {}", date)), String::new(),
        esc(&headers.join("  |  ")), "-".repeat(78),
    ];
    if data.is_empty() {
        lines.push("No data available.".into());
    } else {
        for row in data {
            let line = esc(&row.join("  |  "));
            if line.len() > 95 { lines.push(line[..95].to_string()); } else { lines.push(line); }
        }
    }
    let lh = 14; let ys = 750;
    let mut pages: Vec<String> = Vec::new();
    let mut page = String::new(); let mut y = ys;
    for line in &lines {
        if y < 50 { pages.push(page.clone()); page.clear(); y = ys; }
        page.push_str(&format!("BT /F1 9 Tf 40 {} Td ({}) Tj ET\n", y, line));
        y -= lh;
    }
    if !page.is_empty() { pages.push(page); }
    if pages.is_empty() { pages.push("BT /F1 9 Tf 40 750 Td (Empty report) Tj ET\n".into()); }

    let mut buf = Vec::new();
    let mut off: Vec<usize> = Vec::new();
    buf.extend_from_slice(b"%PDF-1.4\n");
    off.push(buf.len());
    buf.extend_from_slice(b"1 0 obj\n<< /Type /Catalog /Pages 2 0 R >>\nendobj\n");
    let np = pages.len();
    off.push(buf.len());
    let kids: String = (0..np).map(|i| format!("{} 0 R", 4+i*2)).collect::<Vec<_>>().join(" ");
    buf.extend_from_slice(format!("2 0 obj\n<< /Type /Pages /Kids [{}] /Count {} >>\nendobj\n", kids, np).as_bytes());
    off.push(buf.len());
    buf.extend_from_slice(b"3 0 obj\n<< /Type /Font /Subtype /Type1 /BaseFont /Courier >>\nendobj\n");
    for (i, c) in pages.iter().enumerate() {
        let po = 4+i*2; let so = po+1;
        off.push(buf.len());
        buf.extend_from_slice(format!("{} 0 obj\n<< /Type /Page /Parent 2 0 R /MediaBox [0 0 612 792] /Contents {} 0 R /Resources << /Font << /F1 3 0 R >> >> >>\nendobj\n", po, so).as_bytes());
        off.push(buf.len());
        buf.extend_from_slice(format!("{} 0 obj\n<< /Length {} >>\nstream\n{}endstream\nendobj\n", so, c.len(), c).as_bytes());
    }
    let xo = buf.len();
    buf.extend_from_slice(format!("xref\n0 {}\n", off.len()+1).as_bytes());
    buf.extend_from_slice(b"0000000000 65535 f \n");
    for o in &off { buf.extend_from_slice(format!("{:010} 00000 n \n", o).as_bytes()); }
    buf.extend_from_slice(format!("trailer\n<< /Size {} /Root 1 0 R >>\nstartxref\n{}\n%%EOF\n", off.len()+1, xo).as_bytes());
    buf
}

fn range_to_interval(range: &str) -> &str {
    match range { "24h"=>"1 day", "7d"=>"7 days", "30d"=>"30 days", "90d"=>"90 days", "1y"=>"1 year", _=>"7 days" }
}

fn parse_query(qs: &str) -> Vec<(&str, &str)> {
    qs.split('&').filter_map(|p| {
        let mut parts = p.splitn(2, '=');
        let k = parts.next()?;
        let v = parts.next().unwrap_or("");
        if k.is_empty() { None } else { Some((k, v)) }
    }).collect()
}
