import AppKit
import UniformTypeIdentifiers

/// Export für Numbers (.xlsx), Sicherung speichern und laden.
/// Eine echte .numbers-Datei lässt sich nicht erzeugen (Apple-eigenes Format);
/// die .xlsx öffnet Numbers direkt und wird nach dem Speichern dort geöffnet.
@MainActor
enum Exporter {

    static func exportXlsx(store: Store, ui: UIState) {
        let panel = NSSavePanel()
        panel.allowedContentTypes = [UTType(filenameExtension: "xlsx") ?? .data]
        panel.nameFieldStringValue = "Leben-und-Dienst-\(Day.today).xlsx"
        panel.message = "Der aktuelle Stand wird als Tabelle gespeichert und in Numbers geöffnet."
        guard panel.runModal() == .OK, let url = panel.url else { return }
        do {
            try XLSXWriter.workbook(sheets: sheets(store: store)).write(to: url, options: .atomic)
            openInNumbers(url)
            ui.show("Export gespeichert.")
        } catch {
            ui.show("Export fehlgeschlagen: \(error.localizedDescription)")
        }
    }

    static func saveBackup(store: Store, ui: UIState) {
        let panel = NSSavePanel()
        panel.allowedContentTypes = [.json]
        panel.nameFieldStringValue = "mawari-sicherung-\(Day.today).json"
        guard panel.runModal() == .OK, let url = panel.url else { return }
        do { try store.encoded().write(to: url, options: .atomic); ui.show("Sicherung gespeichert.") }
        catch { ui.show("Sicherung fehlgeschlagen: \(error.localizedDescription)") }
    }

    static func importBackup(store: Store, ui: UIState) {
        let panel = NSOpenPanel()
        panel.allowedContentTypes = [.json]
        panel.message = "Sicherung oder Datei aus dem HTML-Tool (leben-und-dienst.json) wählen. Der aktuelle Stand wird dabei ersetzt (eine Kopie bleibt im Datenordner)."
        guard panel.runModal() == .OK, let url = panel.url else { return }
        if !store.db.persons.isEmpty || !store.db.entries.isEmpty {
            let alert = NSAlert()
            alert.messageText = "Aktuelle Daten ersetzen?"
            alert.informativeText = "Alle Personen und Einträge werden durch die Datei ersetzt. Der bisherige Stand wird vorher im Datenordner gesichert."
            alert.addButton(withTitle: "Ersetzen")
            alert.addButton(withTitle: "Abbrechen")
            guard alert.runModal() == .alertFirstButtonReturn else { return }
        }
        do {
            try store.importFile(url)
            ui.tab = .overview
            ui.show("Geladen: \(store.db.persons.count) Personen, \(store.db.entries.count) Einträge.")
        } catch {
            ui.show("Datei konnte nicht gelesen werden.")
        }
    }

    private static func openInNumbers(_ url: URL) {
        if let numbers = NSWorkspace.shared.urlForApplication(withBundleIdentifier: "com.apple.iWork.Numbers") {
            NSWorkspace.shared.open([url], withApplicationAt: numbers, configuration: NSWorkspace.OpenConfiguration())
        } else {
            NSWorkspace.shared.open(url)
        }
    }

    // MARK: Inhalt

    private static func sheets(store: Store) -> [XLSXWriter.Sheet] {
        typealias C = XLSXWriter.Cell
        var ov: [[C]] = [
            [.text("Leben und Dienst – Übersicht", .title)],
            [.text("Stand \(Day.display(Day.today)) · Datum = letzte Einteilung · nie = freigegeben, aber noch nie eingeteilt · – = nicht freigegeben")],
            [],
        ]
        var maxCols = 2
        for group in TaskGroup.allCases {
            let d = UserDefaults.standard
            let sort = OverviewSort(column: d.string(forKey: "sort.\(group.rawValue).column") ?? "name",
                                    ascending: d.object(forKey: "sort.\(group.rawValue).ascending") as? Bool ?? true)
            let (tasks, rows) = overviewRows(store: store, group: group, sort: sort)
            maxCols = max(maxCols, tasks.count + 2)
            let band: XLSXWriter.Style = group == .diener ? .bandDiener : .bandSchulung
            ov.append([.text(group.title, band)] + Array(repeating: .text(" ", band), count: tasks.count + 1))
            ov.append([.text("Name", .header), .text("Zuletzt (irgendeine)", .header)] + tasks.map { .text($0.name, .header) })
            for r in rows {
                ov.append([.text(r.person.name), r.any.map { .date($0) } ?? .text("nie", .center)] + r.cells.map {
                    switch $0 {
                    case .notAllowed: return .text("–", .center)
                    case .never: return .text("nie", .center)
                    case .date(let s): return .date(s)
                    }
                })
            }
            ov.append([])
        }
        let hist: [[C]] = [["Datum", "Aufgabe", "Person", "Partner", "Thema / Notiz"].map { .text($0, .header) }]
            + store.db.entries.sorted { $0.date > $1.date }.map { e in
                [.date(e.date), .text(store.task(e.task)?.name ?? e.task), .text(store.name(e.person)),
                 .text(e.partner.isEmpty ? "" : store.name(e.partner)), .text(e.note)]
            }
        let pers: [[C]] = [(["Name", "Bruder/Schwester", "Notiz"] + store.db.tasks.map(\.name)).map { .text($0, .header) }]
            + store.personsSorted.map { p in
                [.text(p.name), .text(p.gender), .text(p.note)] + store.db.tasks.map { p.may($0.id) ? .text("x", .center) : .text("") }
            }
        return [
            .init(name: "Übersicht", rows: ov, widths: [26, 13] + Array(repeating: 14, count: maxCols - 2)),
            .init(name: "Verlauf", rows: hist, widths: [11, 26, 24, 24, 40]),
            .init(name: "Personen", rows: pers, widths: [24, 14, 24] + store.db.tasks.map { _ in 9 }),
        ]
    }
}

/// Minimaler .xlsx-Schreiber (ZIP ohne Kompression), ohne Fremdbibliothek.
enum XLSXWriter {
    enum Style: Int { case normal = 0, date = 1, header = 2, title = 3, bandDiener = 4, bandSchulung = 5, center = 6 }
    enum Cell { case text(String, Style = .normal), date(String) }
    struct Sheet { let name: String; let rows: [[Cell]]; let widths: [Double] }

    static func workbook(sheets: [Sheet]) -> Data {
        var files: [(String, String)] = [
            ("[Content_Types].xml", #"<?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/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>"#
                + sheets.indices.map { #"<Override PartName="/xl/worksheets/sheet\#($0 + 1).xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>"# }.joined() + "</Types>"),
            ("_rels/.rels", #"<?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"/></Relationships>"#),
            ("xl/workbook.xml", #"<?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>"#
                + sheets.enumerated().map { #"<sheet name="\#(esc($1.name))" sheetId="\#($0 + 1)" r:id="rId\#($0 + 1)"/>"# }.joined() + "</sheets></workbook>"),
            ("xl/_rels/workbook.xml.rels", #"<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">"#
                + sheets.indices.map { #"<Relationship Id="rId\#($0 + 1)" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet\#($0 + 1).xml"/>"# }.joined()
                + #"<Relationship Id="rId\#(sheets.count + 1)" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/></Relationships>"#),
            ("xl/styles.xml", #"<?xml version="1.0" encoding="UTF-8" standalone="yes"?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt numFmtId="164" formatCode="dd.mm.yy"/></numFmts><fonts count="4"><font><sz val="12"/><name val="Helvetica Neue"/></font><font><b/><sz val="12"/><name val="Helvetica Neue"/></font><font><b/><sz val="15"/><name val="Helvetica Neue"/></font><font><b/><sz val="12"/><color rgb="FFFFFFFF"/><name val="Helvetica Neue"/></font></fonts><fills count="5"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FFF2EEE6"/></patternFill></fill><fill><patternFill patternType="solid"><fgColor rgb="FF2F6B4F"/></patternFill></fill><fill><patternFill patternType="solid"><fgColor rgb="FF7A5230"/></patternFill></fill></fills><borders count="1"><border/></borders><cellStyleXfs count="1"><xf/></cellStyleXfs><cellXfs count="7"><xf/><xf numFmtId="164" applyNumberFormat="1" applyAlignment="1"><alignment horizontal="center"/></xf><xf fontId="1" fillId="2" applyFont="1" applyFill="1" applyAlignment="1"><alignment wrapText="1" vertical="bottom"/></xf><xf fontId="2" applyFont="1"/><xf fontId="3" fillId="3" applyFont="1" applyFill="1"/><xf fontId="3" fillId="4" applyFont="1" applyFill="1"/><xf applyAlignment="1"><alignment horizontal="center"/></xf></cellXfs></styleSheet>"#),
        ]
        for (i, s) in sheets.enumerated() { files.append(("xl/worksheets/sheet\(i + 1).xml", sheetXML(s))) }
        return zip(files.map { ($0.0, Data($0.1.utf8)) })
    }

    private static func sheetXML(_ s: Sheet) -> String {
        let cols = s.widths.enumerated().map { #"<col min="\#($0 + 1)" max="\#($0 + 1)" width="\#($1)" customWidth="1"/>"# }.joined()
        var out = #"<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><cols>\#(cols)</cols><sheetData>"#
        for (r, row) in s.rows.enumerated() {
            out += #"<row r="\#(r + 1)">"#
            for (c, cell) in row.enumerated() {
                let ref = column(c) + "\(r + 1)"
                switch cell {
                case .text(let t, let st):
                    guard !t.isEmpty else { continue }
                    out += #"<c r="\#(ref)" t="inlineStr"\#(st == .normal ? "" : #" s="\#(st.rawValue)""#)><is><t xml:space="preserve">\#(esc(t))</t></is></c>"#
                case .date(let d):
                    out += #"<c r="\#(ref)" s="1"><v>\#(serial(d))</v></c>"#
                }
            }
            out += "</row>"
        }
        return out + "</sheetData></worksheet>"
    }

    private static func serial(_ iso: String) -> Int {
        let p = iso.split(separator: "-").compactMap { Int($0) }
        guard p.count == 3 else { return 0 }
        var cal = Calendar(identifier: .gregorian); cal.timeZone = TimeZone(identifier: "UTC")!
        let d = cal.date(from: DateComponents(year: p[0], month: p[1], day: p[2]))!
        let base = cal.date(from: DateComponents(year: 1899, month: 12, day: 30))!
        return cal.dateComponents([.day], from: base, to: d).day ?? 0
    }

    private static func column(_ i: Int) -> String {
        var n = i + 1, s = ""
        while n > 0 { let m = (n - 1) % 26; s = String(UnicodeScalar(65 + m)!) + s; n = (n - 1) / 26 }
        return s
    }

    private static func esc(_ s: String) -> String {
        s.replacingOccurrences(of: "&", with: "&amp;").replacingOccurrences(of: "<", with: "&lt;")
            .replacingOccurrences(of: ">", with: "&gt;").replacingOccurrences(of: "\"", with: "&quot;")
    }

    // MARK: ZIP (stored)

    private static let crcTable: [UInt32] = (0..<256).map { n -> UInt32 in
        var c = UInt32(n)
        for _ in 0..<8 { c = c & 1 != 0 ? 0xEDB88320 ^ (c >> 1) : c >> 1 }
        return c
    }
    private static func crc32(_ d: Data) -> UInt32 {
        var c: UInt32 = 0xFFFFFFFF
        for b in d { c = crcTable[Int((c ^ UInt32(b)) & 0xFF)] ^ (c >> 8) }
        return c ^ 0xFFFFFFFF
    }

    private static func zip(_ files: [(String, Data)]) -> Data {
        var out = Data(), central = Data()
        func u16(_ v: Int, _ d: inout Data) { withUnsafeBytes(of: UInt16(v).littleEndian) { d.append(contentsOf: $0) } }
        func u32(_ v: UInt32, _ d: inout Data) { withUnsafeBytes(of: v.littleEndian) { d.append(contentsOf: $0) } }
        for (name, body) in files {
            let n = Data(name.utf8), crc = crc32(body), offset = UInt32(out.count)
            u32(0x04034b50, &out); u16(20, &out); u16(0x0800, &out); u16(0, &out); u16(0, &out); u16(0, &out)
            u32(crc, &out); u32(UInt32(body.count), &out); u32(UInt32(body.count), &out); u16(n.count, &out); u16(0, &out)
            out.append(n); out.append(body)
            u32(0x02014b50, &central); u16(20, &central); u16(20, &central); u16(0x0800, &central); u16(0, &central)
            u16(0, &central); u16(0, &central); u32(crc, &central); u32(UInt32(body.count), &central); u32(UInt32(body.count), &central)
            u16(n.count, &central); u16(0, &central); u16(0, &central); u16(0, &central); u16(0, &central); u32(0, &central)
            u32(offset, &central); central.append(n)
        }
        let cdOffset = UInt32(out.count)
        out.append(central)
        u32(0x06054b50, &out); u16(0, &out); u16(0, &out); u16(files.count, &out); u16(files.count, &out)
        u32(UInt32(central.count), &out); u32(cdOffset, &out); u16(0, &out)
        return out
    }
}