use std::fs;
use std::io::Write;
use std::path::{Path, PathBuf};

use serde_json::{Map, Value};
use zip::write::FileOptions;
use zip::{CompressionMethod, ZipWriter};

pub struct LinelossExportRequest {
    pub sheet_name: String,
    pub column_defs: Vec<(String, String)>,
    pub rows: Vec<Map<String, Value>>,
    pub output_path: PathBuf,
}

pub fn export_lineloss_xlsx(request: &LinelossExportRequest) -> anyhow::Result<PathBuf> {
    if request.rows.is_empty() {
        anyhow::bail!("rows must not be empty");
    }
    if request.column_defs.is_empty() {
        anyhow::bail!("column_defs must not be empty");
    }

    let sheet_xml = build_worksheet_xml(&request.column_defs, &request.rows);

    write_xlsx(
        &request.output_path,
        &request.sheet_name,
        &sheet_xml,
    )?;

    Ok(request.output_path.clone())
}

fn build_worksheet_xml(
    column_defs: &[(String, String)],
    rows: &[Map<String, Value>],
) -> String {
    let mut xml_rows = Vec::with_capacity(rows.len() + 1);

    // Header row (row 1)
    let header_cells: Vec<String> = column_defs
        .iter()
        .enumerate()
        .map(|(col_idx, (_key, label))| {
            let col_letter = column_letter(col_idx);
            format!(
                "<c r=\"{col_letter}1\" t=\"inlineStr\"><is><t>{}</t></is></c>",
                xml_escape(label)
            )
        })
        .collect();
    xml_rows.push(format!("<row r=\"1\">{}</row>", header_cells.join("")));

    // Data rows (row 2+)
    for (row_idx, row) in rows.iter().enumerate() {
        let excel_row = row_idx + 2;
        let cells: Vec<String> = column_defs
            .iter()
            .enumerate()
            .map(|(col_idx, (key, _label))| {
                let col_letter = column_letter(col_idx);
                let value = row
                    .get(key)
                    .map(|v| value_to_string(v))
                    .unwrap_or_default();
                format!(
                    "<c r=\"{col_letter}{excel_row}\" t=\"inlineStr\"><is><t>{}</t></is></c>",
                    xml_escape(&value)
                )
            })
            .collect();
        xml_rows.push(format!("<row r=\"{excel_row}\">{}</row>", cells.join("")));
    }

    format!(
        "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\
<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\
<sheetData>{}</sheetData>\
</worksheet>",
        xml_rows.join("")
    )
}

fn column_letter(index: usize) -> String {
    let mut result = String::new();
    let mut n = index;
    loop {
        result.insert(0, (b'A' + (n % 26) as u8) as char);
        if n < 26 {
            break;
        }
        n = n / 26 - 1;
    }
    result
}

fn value_to_string(value: &Value) -> String {
    match value {
        Value::String(text) => text.clone(),
        Value::Number(number) => number.to_string(),
        Value::Bool(flag) => flag.to_string(),
        Value::Null => String::new(),
        other => other.to_string(),
    }
}

fn xml_escape(value: &str) -> String {
    value
        .replace('&', "&amp;")
        .replace('<', "&lt;")
        .replace('>', "&gt;")
}

fn write_xlsx(output_path: &Path, sheet_name: &str, sheet_xml: &str) -> anyhow::Result<()> {
    if let Some(parent) = output_path.parent() {
        fs::create_dir_all(parent)?;
    }
    if output_path.exists() {
        fs::remove_file(output_path)?;
    }

    let file = fs::File::create(output_path)?;
    let mut zip = ZipWriter::new(file);
    let options = FileOptions::default().compression_method(CompressionMethod::Stored);

    zip.start_file("[Content_Types].xml", options)?;
    zip.write_all(content_types_xml().as_bytes())?;

    zip.start_file("_rels/.rels", options)?;
    zip.write_all(root_rels_xml().as_bytes())?;

    zip.start_file("docProps/app.xml", options)?;
    zip.write_all(app_xml().as_bytes())?;

    zip.start_file("docProps/core.xml", options)?;
    zip.write_all(core_xml().as_bytes())?;

    zip.start_file("xl/workbook.xml", options)?;
    zip.write_all(workbook_xml(&xml_escape(sheet_name)).as_bytes())?;

    zip.start_file("xl/_rels/workbook.xml.rels", options)?;
    zip.write_all(workbook_rels_xml().as_bytes())?;

    zip.start_file("xl/worksheets/sheet1.xml", options)?;
    zip.write_all(sheet_xml.as_bytes())?;

    zip.finish()?;
    Ok(())
}

fn content_types_xml() -> &'static str {
    r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
  <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
</Types>"#
}

fn root_rels_xml() -> &'static str {
    r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
</Relationships>"#
}

fn app_xml() -> &'static str {
    r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"
            xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
  <Application>sgClaw</Application>
</Properties>"#
}

fn core_xml() -> &'static str {
    r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties"
                   xmlns:dc="http://purl.org/dc/elements/1.1/"
                   xmlns:dcterms="http://purl.org/dc/terms/"
                   xmlns:dcmitype="http://purl.org/dc/dcmitype/"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <dc:title>台区线损报表</dc:title>
</cp:coreProperties>"#
}

fn workbook_xml(sheet_name: &str) -> String {
    format!(
        r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets>
    <sheet name="{sheet_name}" sheetId="1" r:id="rId1"/>
  </sheets>
</workbook>"#
    )
}

fn workbook_rels_xml() -> &'static str {
    r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
</Relationships>"#
}

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

    #[test]
    fn column_letter_maps_indices_correctly() {
        assert_eq!(column_letter(0), "A");
        assert_eq!(column_letter(1), "B");
        assert_eq!(column_letter(6), "G");
        assert_eq!(column_letter(25), "Z");
        assert_eq!(column_letter(26), "AA");
    }
}
