package com.fieldlabel.collector.service

import android.content.Context
import com.fieldlabel.collector.domain.model.CollectionRecord
import com.fieldlabel.collector.domain.model.CustomFieldDef
import com.fieldlabel.collector.domain.model.CustomFieldValue
import com.fieldlabel.collector.domain.model.RouteLog
import com.fieldlabel.collector.domain.model.RoutePoint
import com.fieldlabel.collector.domain.model.SpecimenPhoto
import dagger.hilt.android.qualifiers.ApplicationContext
import java.io.ByteArrayOutputStream
import java.io.File
import java.io.OutputStream
import java.time.format.DateTimeFormatter
import java.util.zip.ZipEntry
import java.util.zip.ZipOutputStream
import javax.inject.Inject
import javax.inject.Singleton

// ──────────────────────────────────────────────────────────────────────────────
// エクスポート状態
// ──────────────────────────────────────────────────────────────────────────────
sealed class ExportState {
    object Idle       : ExportState()
    object InProgress : ExportState()
    data class Done (val file: File)     : ExportState()
    data class Error(val message: String): ExportState()
}

// ──────────────────────────────────────────────────────────────────────────────
// エクスポートサービス (schema v3)
//
// ZIP 構成:
//   manifest.json
//   records.xlsx         ← 多シート (Locations / Specimens / Photos / Videos / Audio / Values)
//   media/{locationId}/{idKey}_{NNN}.{ext}   ← 写真・動画・音声ファイル
//
// シート定義 (v3):
//   Locations  : location_id | collection_date | collector | address | latitude | longitude | altitude_m
//   Specimens  : specimen_id | location_id | specimen_number | collection_date | collector | address |
//                latitude | longitude | altitude_m | notes
//   Photos     : photo_id | location_id | specimen_id | file_path | taken_at
//   Videos     : video_id | location_id | specimen_id | file_path | taken_at
//   Audio      : audio_id | location_id | specimen_id | file_path | taken_at
//   Values     : value_id | field_label | unit | value_type | applies_to | location_id | specimen_id | value
// ──────────────────────────────────────────────────────────────────────────────
@Singleton
class ExportService @Inject constructor(
    @ApplicationContext private val context: Context
) {
    private val ts get() = java.time.LocalDateTime.now()
        .format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"))

    // ── 公開 API ──────────────────────────────────────────────────────────────

    /**
     * 多シート XLSX を生成する。
     */
    fun buildXlsx(
        records:      List<CollectionRecord>,
        media:        List<SpecimenPhoto>,
        fieldDefs:    List<CustomFieldDef>   = emptyList(),
        fieldValues:  List<CustomFieldValue> = emptyList(),
        routeLogs:    List<RouteLog>         = emptyList(),
        routePoints:  List<RoutePoint>       = emptyList()
    ): File {
        val file = File(context.cacheDir, "collection_$ts.xlsx")
        file.outputStream().buffered().use {
            writeMultiSheetXlsx(records, media, fieldDefs, fieldValues, it,
                renamedPaths = emptyMap(), routeLogs = routeLogs, routePoints = routePoints)
        }
        return file
    }

    /**
     * XLSX + メディアファイル + manifest.json をまとめた ZIP を生成する。
     */
    fun buildZip(
        records:      List<CollectionRecord>,
        media:        List<SpecimenPhoto>,
        fieldDefs:    List<CustomFieldDef>   = emptyList(),
        fieldValues:  List<CustomFieldValue> = emptyList(),
        routeLogs:    List<RouteLog>         = emptyList(),
        routePoints:  List<RoutePoint>       = emptyList()
    ): File {
        val renamedPaths = buildRenamedMediaMap(media)

        val zipFile = File(context.cacheDir, "collection_$ts.zip")
        ZipOutputStream(zipFile.outputStream().buffered()).use { zip ->
            // manifest.json
            zip.addStr("manifest.json", buildManifestJson(records, media, fieldDefs, routeLogs))

            // records.xlsx（全シート）
            val xlsxBytes = ByteArrayOutputStream().also { ba ->
                writeMultiSheetXlsx(records, media, fieldDefs, fieldValues, ba,
                    renamedPaths, routeLogs, routePoints)
            }.toByteArray()
            zip.addBytes("records.xlsx", xlsxBytes)

            // メディアファイルをリネーム済みパスで格納
            media.forEach { m ->
                val src = File(m.filePath)
                if (src.exists()) {
                    val zipPath = renamedPaths[m.filePath] ?: return@forEach
                    zip.putNextEntry(ZipEntry(zipPath))
                    src.inputStream().use { it.copyTo(zip) }
                    zip.closeEntry()
                }
            }
        }
        return zipFile
    }

    // ── 内部実装 ──────────────────────────────────────────────────────────────

    private fun buildManifestJson(
        records:    List<CollectionRecord>,
        media:      List<SpecimenPhoto>,
        fieldDefs:  List<CustomFieldDef>,
        routeLogs:  List<RouteLog> = emptyList()
    ): String {
        val locationCount = records.map { it.locationId }.distinct().size
        val photoCount    = media.count { it.isPhoto }
        val videoCount    = media.count { it.isVideo }
        val audioCount    = media.count { it.isAudio }
        val now           = java.time.LocalDateTime.now().toString()
        return """{
  "app": "FieldLabel Collector",
  "schema": 4,
  "exportedAt": "$now",
  "records": ${records.size},
  "locations": $locationCount,
  "photos": $photoCount,
  "videos": $videoCount,
  "audio": $audioCount,
  "customFields": ${fieldDefs.size},
  "routeLogs": ${routeLogs.size}
}"""
    }

    /**
     * メディアリストから「元ファイルパス → ZIP 内相対パス（リネーム済み）」マップを生成する。
     * media/{locationId}/{idKey}_{NNN}.{ext}
     */
    private fun buildRenamedMediaMap(media: List<SpecimenPhoto>): Map<String, String> {
        val result   = mutableMapOf<String, String>()
        val counters = mutableMapOf<String, Int>()
        media.filter { File(it.filePath).exists() }.forEach { m ->
            val idKey   = m.specimenId ?: m.locationId
            val counter = (counters[idKey] ?: 0) + 1
            counters[idKey] = counter
            val ext     = File(m.filePath).extension.ifBlank {
                when (m.mediaType) { "video" -> "mp4"; "audio" -> "m4a"; else -> "jpg" }
            }
            val newName = "${idKey}_%03d.$ext".format(counter)
            result[m.filePath] = "media/${m.locationId}/$newName"
        }
        return result
    }

    // ─────────────────────────────────────────────────────────────────────────
    // 多シート XLSX ライター
    // ─────────────────────────────────────────────────────────────────────────

    private fun writeMultiSheetXlsx(
        records:      List<CollectionRecord>,
        media:        List<SpecimenPhoto>,
        fieldDefs:    List<CustomFieldDef>,
        fieldValues:  List<CustomFieldValue>,
        out:          OutputStream,
        renamedPaths: Map<String, String>,
        routeLogs:    List<RouteLog>    = emptyList(),
        routePoints:  List<RoutePoint>  = emptyList()
    ) {
        // ── 各シートのデータを構築 ──
        val sheetsData = buildSheetsData(records, media, fieldDefs, fieldValues, renamedPaths,
            routeLogs, routePoints)

        // ── XLSX (= ZIP + XML) を書き出す ──
        ZipOutputStream(out).use { zip ->
            zip.addStr("[Content_Types].xml",       buildContentTypesXml(sheetsData.size))
            zip.addStr("_rels/.rels",                RELS_XML)
            zip.addStr("xl/workbook.xml",            buildWorkbookXml(sheetsData))
            zip.addStr("xl/_rels/workbook.xml.rels", buildWorkbookRelsXml(sheetsData))
            zip.addStr("xl/styles.xml",              STYLES_XML)

            sheetsData.forEachIndexed { idx, sheet ->
                val sheetNum = idx + 1
                val hyperlinks = collectHyperlinks(sheet.rows, sheet.headers.size)
                zip.addStr("xl/worksheets/sheet$sheetNum.xml",
                    buildSheetXml(sheet.rows, sheet.headers.size, hyperlinks))
                if (hyperlinks.isNotEmpty()) {
                    zip.addStr("xl/worksheets/_rels/sheet$sheetNum.xml.rels",
                        buildSheetRelsXml(hyperlinks))
                }
            }
        }
    }

    private data class SheetData(
        val name: String,
        val headers: List<String>,
        val rows: List<List<SheetCell>>   // 行0 = ヘッダー行
    )

    private fun buildSheetsData(
        records:      List<CollectionRecord>,
        media:        List<SpecimenPhoto>,
        fieldDefs:    List<CustomFieldDef>,
        fieldValues:  List<CustomFieldValue>,
        renamedPaths: Map<String, String>,
        routeLogs:    List<RouteLog>    = emptyList(),
        routePoints:  List<RoutePoint>  = emptyList()
    ): List<SheetData> {
        val sheets = mutableListOf<SheetData>()

        // ── 1. Locations シート ──────────────────────────────────────────────
        val locHeaders = listOf("location_id","collection_date","collector",
            "address","latitude","longitude","altitude_m")
        val locRows = mutableListOf<List<SheetCell>>()
        locRows.add(locHeaders.map { SheetCell(it, bold = true) })
        records.groupBy { it.locationId }.forEach { (locId, recs) ->
            val r = recs.first()   // 代表レコードから地点情報を取得
            locRows.add(listOf(
                SheetCell(locId),
                SheetCell(r.collectionDate.toString()),
                SheetCell(r.collectorName),
                SheetCell(r.address ?: ""),
                SheetCell(num = r.latitude),
                SheetCell(num = r.longitude),
                SheetCell(num = r.altitude)
            ))
        }
        sheets.add(SheetData("Locations", locHeaders, locRows))

        // ── 2. Specimens シート ──────────────────────────────────────────────
        val specimenHeaders = listOf("specimen_id","location_id","specimen_number",
            "collection_date","collector","address","latitude","longitude","altitude_m","notes")
        val specimenRows = mutableListOf<List<SheetCell>>()
        specimenRows.add(specimenHeaders.map { SheetCell(it, bold = true) })
        records.forEach { r ->
            specimenRows.add(listOf(
                SheetCell(r.specimenId),
                SheetCell(r.locationId),
                SheetCell(num = r.specimenNumber.toDouble()),
                SheetCell(r.collectionDate.toString()),
                SheetCell(r.collectorName),
                SheetCell(r.address ?: ""),
                SheetCell(num = r.latitude),
                SheetCell(num = r.longitude),
                SheetCell(num = r.altitude),
                SheetCell(r.notes)
            ))
        }
        sheets.add(SheetData("Specimens", specimenHeaders, specimenRows))

        // ── 3/4/5. Photos / Videos / Audio シート ──────────────────────────
        listOf("photo" to "Photos", "video" to "Videos", "audio" to "Audio").forEach { (type, sheetName) ->
            val mediaItems = media.filter { it.mediaType == type }
            val mediaHeaders = listOf("${type}_id","location_id","specimen_id","file_path","taken_at")
            val mediaRows = mutableListOf<List<SheetCell>>()
            mediaRows.add(mediaHeaders.map { SheetCell(it, bold = true) })
            mediaItems.forEach { m ->
                val zipPath = renamedPaths[m.filePath]
                    ?: "media/${m.locationId}/${File(m.filePath).name}"
                mediaRows.add(listOf(
                    SheetCell(m.id),
                    SheetCell(m.locationId),
                    SheetCell(m.specimenId ?: ""),
                    SheetCell(zipPath, hyperlink = zipPath),
                    SheetCell(m.takenAt.toString())
                ))
            }
            sheets.add(SheetData(sheetName, mediaHeaders, mediaRows))
        }

        // ── 6. Values シート ─────────────────────────────────────────────────
        if (fieldDefs.isNotEmpty()) {
            val defMap = fieldDefs.associateBy { it.id }
            val valHeaders = listOf("value_id","field_label","unit","value_type",
                "applies_to","location_id","specimen_id","value")
            val valRows = mutableListOf<List<SheetCell>>()
            valRows.add(valHeaders.map { SheetCell(it, bold = true) })
            fieldValues.forEach { v ->
                val def = defMap[v.fieldDefId]
                valRows.add(listOf(
                    SheetCell(v.id),
                    SheetCell(def?.label ?: v.fieldDefId),
                    SheetCell(def?.unit ?: ""),
                    SheetCell(def?.valueType ?: ""),
                    SheetCell(def?.appliesTo ?: ""),
                    SheetCell(v.locationId),
                    SheetCell(v.specimenId ?: ""),
                    SheetCell(v.value)
                ))
            }
            sheets.add(SheetData("Values", valHeaders, valRows))
        }

        // ── 7. 移動ログ シート ────────────────────────────────────────────────
        if (routeLogs.isNotEmpty()) {
            val logHeaders = listOf(
                "route_id", "name", "start_time", "end_time",
                "duration_sec", "distance_m", "point_count"
            )
            val logRows = mutableListOf<List<SheetCell>>()
            logRows.add(logHeaders.map { SheetCell(it, bold = true) })
            routeLogs.forEach { log ->
                val durationSec = ((log.endTime ?: System.currentTimeMillis()) - log.startTime) / 1_000
                logRows.add(listOf(
                    SheetCell(log.id),
                    SheetCell(log.name),
                    SheetCell(epochToIso(log.startTime)),
                    SheetCell(log.endTime?.let { epochToIso(it) } ?: ""),
                    SheetCell(num = durationSec.toDouble()),
                    SheetCell(num = log.totalDistanceMeters),
                    SheetCell(num = log.pointCount.toDouble())
                ))
            }
            sheets.add(SheetData("移動ログ", logHeaders, logRows))

            // ── 8. 移動ログ詳細 シート ────────────────────────────────────
            if (routePoints.isNotEmpty()) {
                val ptHeaders = listOf(
                    "point_id", "route_id", "timestamp", "latitude",
                    "longitude", "altitude_m", "accuracy_m"
                )
                val ptRows = mutableListOf<List<SheetCell>>()
                ptRows.add(ptHeaders.map { SheetCell(it, bold = true) })
                routePoints.forEach { pt ->
                    ptRows.add(listOf(
                        SheetCell(pt.id),
                        SheetCell(pt.routeLogId),
                        SheetCell(epochToIso(pt.timestamp)),
                        SheetCell(num = pt.latitude),
                        SheetCell(num = pt.longitude),
                        SheetCell(num = pt.altitude),
                        SheetCell(num = pt.accuracy.toDouble())
                    ))
                }
                sheets.add(SheetData("移動ログ詳細", ptHeaders, ptRows))
            }
        }

        return sheets
    }

    /** epoch ms → ISO-8601 ローカル日時文字列 */
    private fun epochToIso(epochMs: Long): String =
        java.time.Instant.ofEpochMilli(epochMs)
            .atZone(java.time.ZoneId.systemDefault())
            .format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss"))

    // ── XML 生成ヘルパー ──────────────────────────────────────────────────────

    private fun buildContentTypesXml(sheetCount: Int): String = buildString {
        append("""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>""")
        append("""<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">""")
        append("""<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>""")
        append("""<Default Extension="xml"  ContentType="application/xml"/>""")
        append("""<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>""")
        for (i in 1..sheetCount) {
            append("""<Override PartName="/xl/worksheets/sheet$i.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>""")
        }
        append("""<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>""")
        append("</Types>")
    }

    private fun buildWorkbookXml(sheets: List<SheetData>): String = buildString {
        append("""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>""")
        append("""<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">""")
        append("<sheets>")
        sheets.forEachIndexed { idx, sheet ->
            append("""<sheet name="${escXml(sheet.name)}" sheetId="${idx + 1}" r:id="rSheet${idx + 1}"/>""")
        }
        append("</sheets>")
        append("</workbook>")
    }

    private fun buildWorkbookRelsXml(sheets: List<SheetData>): String = buildString {
        append("""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>""")
        append("""<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">""")
        sheets.forEachIndexed { idx, _ ->
            append("""<Relationship Id="rSheet${idx + 1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${idx + 1}.xml"/>""")
        }
        append("""<Relationship Id="rStyles" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>""")
        append("</Relationships>")
    }

    private fun buildSheetXml(
        rows:       List<List<SheetCell>>,
        colCount:   Int,
        hyperlinks: List<XlsxHyperlink> = emptyList()
    ): String {
        val hlRefs = hyperlinks.map { it.ref }.toSet()
        return buildString {
            append("""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>""")
            append("""<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">""")
            // 列幅
            append("<cols>")
            repeat(colCount) { i ->
                val w = if (i < COL_WIDTHS.size) COL_WIDTHS[i] else 16
                val c = i + 1
                append("""<col min="$c" max="$c" width="$w" customWidth="1"/>""")
            }
            append("</cols>")
            append("<sheetData>")
            rows.forEachIndexed { rowIdx, cells ->
                append("""<row r="${rowIdx + 1}">""")
                cells.forEachIndexed { colIdx, cell ->
                    val ref   = "${colLetter(colIdx + 1)}${rowIdx + 1}"
                    val style = when {
                        cell.bold     -> 1
                        ref in hlRefs -> 2
                        else          -> 0
                    }
                    val n = cell.num
                    if (n != null) {
                        append("""<c r="$ref" s="$style"><v>$n</v></c>""")
                    } else {
                        append("""<c r="$ref" t="inlineStr" s="$style"><is><t xml:space="preserve">""")
                        append(escXml(cell.text))
                        append("</t></is></c>")
                    }
                }
                append("</row>")
            }
            append("</sheetData>")
            if (hyperlinks.isNotEmpty()) {
                append("<hyperlinks>")
                hyperlinks.forEach { hl ->
                    append("""<hyperlink ref="${hl.ref}" r:id="${hl.relId}"/>""")
                }
                append("</hyperlinks>")
            }
            append("</worksheet>")
        }
    }

    private fun collectHyperlinks(rows: List<List<SheetCell>>, colCount: Int): List<XlsxHyperlink> {
        val result = mutableListOf<XlsxHyperlink>()
        rows.forEachIndexed { rowIdx, cells ->
            if (rowIdx == 0) return@forEachIndexed
            cells.forEachIndexed { colIdx, cell ->
                if (!cell.hyperlink.isNullOrBlank()) {
                    val ref   = "${colLetter(colIdx + 1)}${rowIdx + 1}"
                    val relId = "rHl${result.size + 1}"
                    result.add(XlsxHyperlink(ref, relId, cell.hyperlink))
                }
            }
        }
        return result
    }

    private fun buildSheetRelsXml(hyperlinks: List<XlsxHyperlink>): String = buildString {
        append("""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>""")
        append("""<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">""")
        hyperlinks.forEach { hl ->
            append("""<Relationship Id="${hl.relId}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="${escXml(hl.target)}" TargetMode="External"/>""")
        }
        append("</Relationships>")
    }

    private fun colLetter(n: Int): String {
        val sb = StringBuilder()
        var v = n
        while (v > 0) { sb.insert(0, 'A' + (v - 1) % 26); v = (v - 1) / 26 }
        return sb.toString()
    }

    private fun escXml(s: String) = s
        .replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
        .replace("\"", "&quot;").replace("'", "&apos;")
        .replace(Regex("[\\x00-\\x08\\x0B\\x0C\\x0E-\\x1F]"), "")

    private fun ZipOutputStream.addStr(name: String, content: String) {
        putNextEntry(ZipEntry(name))
        write(content.toByteArray(Charsets.UTF_8))
        closeEntry()
    }

    private fun ZipOutputStream.addBytes(name: String, bytes: ByteArray) {
        putNextEntry(ZipEntry(name))
        write(bytes)
        closeEntry()
    }

    // ── 静的 XML 定数 ─────────────────────────────────────────────────────────

    companion object {
        private val COL_WIDTHS = listOf(20, 18, 14, 14, 16, 32, 14, 14, 12, 24, 44, 20)

        private val RELS_XML = """<?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>"""

        private val STYLES_XML = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="3">
    <font><sz val="11"/><name val="Arial"/></font>
    <font><b/><sz val="11"/><name val="Arial"/></font>
    <font><u/><sz val="11"/><color rgb="FF0563C1"/><name val="Arial"/></font>
  </fonts>
  <fills count="3">
    <fill><patternFill patternType="none"/></fill>
    <fill><patternFill patternType="gray125"/></fill>
    <fill><patternFill patternType="solid"><fgColor rgb="FFD9E8FB"/></patternFill></fill>
  </fills>
  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="3">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0"/>
  </cellXfs>
</styleSheet>"""
    }
}

// ── パッケージレベルの内部型 ────────────────────────────────────────────────────

/** セルデータ。hyperlink が非 null のとき Excel ハイパーリンクとして登録する */
internal data class SheetCell(
    val text:      String  = "",
    val num:       Double? = null,
    val bold:      Boolean = false,
    val hyperlink: String? = null
)

/** XLSX ハイパーリンクエントリ */
internal data class XlsxHyperlink(
    val ref:    String,
    val relId:  String,
    val target: String
)
