<?php
session_start();
require_once 'db.php';

$instansi_id = $_SESSION['instansi_id'] ?? 1;

/**
 * Function parsing xlsx tanpa library pihak ketiga
 */
function parseXlsxRows($filePath) {
    $rows = [];
    $zip = new ZipArchive();
    if ($zip->open($filePath) === TRUE) {
        $sharedStrings = [];
        $stringsXmlContent = $zip->getFromName('xl/sharedStrings.xml');
        if ($stringsXmlContent !== false) {
            $xmlStrings = simplexml_load_string($stringsXmlContent);
            if ($xmlStrings && isset($xmlStrings->si)) {
                foreach ($xmlStrings->si as $val) {
                    if (isset($val->t)) {
                        $sharedStrings[] = (string)$val->t;
                    } else {
                        $text = '';
                        if (isset($val->r)) {
                            foreach ($val->r as $r) {
                                $text .= (string)$r->t;
                            }
                        }
                        $sharedStrings[] = $text;
                    }
                }
            }
        }

        $sheetXmlContent = $zip->getFromName('xl/worksheets/sheet1.xml');
        if ($sheetXmlContent !== false) {
            $sheetXml = simplexml_load_string($sheetXmlContent);
            if ($sheetXml && isset($sheetXml->sheetData->row)) {
                foreach ($sheetXml->sheetData->row as $rowElem) {
                    $rowVals = [];
                    foreach ($rowElem->c as $cell) {
                        $type = (string)$cell['t'];
                        $v = (string)$cell->v;
                        if ($type === 's' && is_numeric($v)) {
                            $idx = (int)$v;
                            $val = $sharedStrings[$idx] ?? $v;
                        } else {
                            $val = $v;
                        }
                        $rowVals[] = trim($val);
                    }
                    $rows[] = $rowVals;
                }
            }
        }
        $zip->close();
    }
    return $rows;
}

/**
 * Helper ubah format tanggal LAPOR (misal: "17 Jul 2026" & "08:09:17") ke SQL Datetime
 */
function parseLaporDateTime($dateStr, $timeStr = '00:00:00') {
    if (empty($dateStr)) return date('Y-m-d H:i:s');
    
    // Peta bulan Bahasa Indonesia / Inggris Singkat
    $months = [
        'Jan' => '01', 'Feb' => '02', 'Mar' => '03', 'Apr' => '04',
        'Mei' => '05', 'May' => '05', 'Jun' => '06', 'Jul' => '07',
        'Agu' => '08', 'Aug' => '08', 'Sep' => '09', 'Okt' => '10',
        'Oct' => '10', 'Nov' => '11', 'Des' => '12', 'Dec' => '12'
    ];

    $parts = explode(' ', trim($dateStr));
    if (count($parts) >= 3) {
        $day = str_pad($parts[0], 2, '0', STR_PAD_LEFT);
        $mStr = $parts[1];
        $month = $months[$mStr] ?? '01';
        $year = $parts[2];
        return "$year-$month-$day " . ($timeStr ?: '00:00:00');
    }

    $ts = strtotime($dateStr);
    if ($ts) return date('Y-m-d H:i:s', $ts);
    return date('Y-m-d H:i:s');
}

/**
 * Proses Impor File Excel SP4N LAPOR
 */
function processLaporImport($filePath, $pdo, $instansi_id) {
    $rows = parseXlsxRows($filePath);
    if (empty($rows)) {
        return ['status' => 'error', 'message' => 'Gagal membaca file Excel atau file kosong.'];
    }

    // Ambil semua SKPD di database untuk pencocokan otomatis
    $stmt_skpd = $pdo->prepare("SELECT id, nama_skpd, singkatan, keywords FROM skpd WHERE instansi_id = ?");
    $stmt_skpd->execute([$instansi_id]);
    $skpd_list = $stmt_skpd->fetchAll(PDO::FETCH_ASSOC);

    // Cari baris header yang memuat 'Tracking ID'
    $headerRowIndex = -1;
    $colMap = [];

    foreach ($rows as $idx => $row) {
        foreach ($row as $cIdx => $cellText) {
            if (stripos($cellText, 'Tracking ID') !== false) {
                $headerRowIndex = $idx;
                break 2;
            }
        }
    }

    if ($headerRowIndex === -1) {
        return ['status' => 'error', 'message' => 'Format file Excel tidak sesuai standar SP4N LAPOR! (Header "Tracking ID" tidak ditemukan).'];
    }

    // Pemetaan nama kolom ke indeks array
    $headerRow = $rows[$headerRowIndex];
    foreach ($headerRow as $cIdx => $colName) {
        $name = strtolower(trim($colName));
        if (str_contains($name, 'tracking id')) $colMap['tracking_id'] = $cIdx;
        elseif (str_contains($name, 'tanggal laporan masuk')) $colMap['tgl_masuk'] = $cIdx;
        elseif (str_contains($name, 'waktu laporan masuk')) $colMap['waktu_masuk'] = $cIdx;
        elseif (str_contains($name, 'nama pelapor')) $colMap['nama_pelapor'] = $cIdx;
        elseif (str_contains($name, 'klasifikasi')) $colMap['klasifikasi'] = $cIdx;
        elseif (str_contains($name, 'kategori laporan')) $colMap['kategori'] = $cIdx;
        elseif (str_contains($name, 'judul laporan')) $colMap['judul'] = $cIdx;
        elseif (str_contains($name, 'isi laporan awal') || str_contains($name, 'isi laporan')) $colMap['isi_laporan'] = $cIdx;
        elseif (str_contains($name, 'instansi terdisposisi')) $colMap['instansi_terdisposisi'] = $cIdx;
        elseif (str_contains($name, 'status laporan') || $name === 'status') $colMap['status'] = $cIdx;
        elseif (str_contains($name, 'tindak lanjut laporan 1') || str_contains($name, 'tindak lanjut')) $colMap['tindak_lanjut'] = $cIdx;
    }

    $inserted = 0;
    $updated = 0;

    $stmt_check = $pdo->prepare("SELECT id FROM lapor WHERE instansi_id = ? AND tracking_id = ?");
    $stmt_insert = $pdo->prepare("INSERT INTO lapor (instansi_id, skpd_id, tracking_id, tanggal_masuk, nama_pelapor, klasifikasi, kategori, judul, isi_laporan, instansi_terdisposisi, status_laporan, tindak_lanjut) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt_update = $pdo->prepare("UPDATE lapor SET skpd_id = ?, tanggal_masuk = ?, nama_pelapor = ?, klasifikasi = ?, kategori = ?, judul = ?, isi_laporan = ?, instansi_terdisposisi = ?, status_laporan = ?, tindak_lanjut = ? WHERE id = ?");

    // Process data rows
    for ($i = $headerRowIndex + 1; $i < count($rows); $i++) {
        $r = $rows[$i];
        if (empty($r)) continue;

        $tracking_id = trim($r[$colMap['tracking_id'] ?? 0] ?? '');
        if (empty($tracking_id) || !is_numeric($tracking_id)) continue;

        $tglStr = $r[$colMap['tgl_masuk'] ?? 1] ?? '';
        $waktuStr = $r[$colMap['waktu_masuk'] ?? 2] ?? '';
        $dtMasuk = parseLaporDateTime($tglStr, $waktuStr);

        $nama_pelapor = $r[$colMap['nama_pelapor'] ?? 3] ?? 'Anonim';
        $klasifikasi = $r[$colMap['klasifikasi'] ?? 4] ?? 'Pengaduan';
        $kategori = $r[$colMap['kategori'] ?? 6] ?? 'Umum';
        $judul = $r[$colMap['judul'] ?? 7] ?? 'Laporan SP4N LAPOR';
        $isi_laporan = $r[$colMap['isi_laporan'] ?? 8] ?? '';
        $instansi_terdisposisi = $r[$colMap['instansi_terdisposisi'] ?? 24] ?? '';
        $status_laporan = $r[$colMap['status'] ?? 27] ?? 'Proses';
        $tindak_lanjut = $r[$colMap['tindak_lanjut'] ?? 32] ?? '';

        // Auto Match SKPD
        $matched_skpd_id = null;
        if (!empty($instansi_terdisposisi)) {
            $cleanInstansi = strtolower($instansi_terdisposisi);
            foreach ($skpd_list as $sk) {
                $skNama = strtolower($sk['nama_skpd']);
                $skSingkatan = strtolower($sk['singkatan'] ?? '');

                if (!empty($skSingkatan) && str_contains($cleanInstansi, $skSingkatan)) {
                    $matched_skpd_id = $sk['id'];
                    break;
                }
                
                // Bandingkan kemiripan teks nama SKPD
                similar_text($cleanInstansi, $skNama, $percent);
                if ($percent > 45 || str_contains($cleanInstansi, $skNama) || str_contains($skNama, $cleanInstansi)) {
                    $matched_skpd_id = $sk['id'];
                    break;
                }
            }
        }

        // Cek jika belum dapat via instansi_terdisposisi, coba via kata kunci judul/isi
        if (!$matched_skpd_id) {
            $fullText = strtolower($judul . ' ' . $isi_laporan);
            foreach ($skpd_list as $sk) {
                $kws = array_filter(array_map('trim', explode(',', strtolower($sk['keywords'] ?? ''))));
                foreach ($kws as $kw) {
                    if (strlen($kw) > 2 && str_contains($fullText, $kw)) {
                        $matched_skpd_id = $sk['id'];
                        break 2;
                    }
                }
            }
        }

        $stmt_check->execute([$instansi_id, $tracking_id]);
        $existing = $stmt_check->fetch(PDO::FETCH_ASSOC);

        if ($existing) {
            $stmt_update->execute([
                $matched_skpd_id, $dtMasuk, $nama_pelapor, $klasifikasi, $kategori, 
                $judul, $isi_laporan, $instansi_terdisposisi, $status_laporan, $tindak_lanjut, 
                $existing['id']
            ]);
            $updated++;
        } else {
            $stmt_insert->execute([
                $instansi_id, $matched_skpd_id, $tracking_id, $dtMasuk, $nama_pelapor, 
                $klasifikasi, $kategori, $judul, $isi_laporan, $instansi_terdisposisi, 
                $status_laporan, $tindak_lanjut
            ]);
            $inserted++;
        }
    }

    return [
        'status' => 'success',
        'message' => "Impor SP4N LAPOR Berhasil! {$inserted} Laporan Baru Ditambahkan, {$updated} Laporan Diperbarui.",
        'inserted' => $inserted,
        'updated' => $updated
    ];
}

// Check action parameter for file upload
$action = $_REQUEST['action'] ?? '';

if ($action === 'upload_excel' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_FILES['file_lapor']) && $_FILES['file_lapor']['error'] === UPLOAD_ERR_OK) {
        $tmpName = $_FILES['file_lapor']['tmp_name'];
        $res = processLaporImport($tmpName, $pdo, $instansi_id);
        header("Location: lapor.php?msg=" . urlencode($res['message']));
        exit;
    } else {
        header("Location: lapor.php?err=" . urlencode("Gagal mengunggah file Excel. Mohon periksa format file."));
        exit;
    }
}
