<?php
/**
 * =========================================================
 * IMPORT DATA KEPATUHAN PEMBAYARAN – CORRECTED VERSION
 * =========================================================
 * Fixed to use koneksi.php instead of connection.php
 */

declare(strict_types=1);

/* ================= SESSION ================= */
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

/* ================= AUTH ================= */
if (!isset($_SESSION['login']) || $_SESSION['login'] !== true) {
    header('Location: ../../auth/login.php');
    exit;
}

$role = strtoupper($_SESSION['role'] ?? '');
$ALLOW_IMPORT = in_array($role, ['A', 'ADMIN', 'SUPERADMIN', 'M', 'MANAGER', 'STAFF', 'S'], true);
if (!$ALLOW_IMPORT) {
    die('Akses ditolak');
}

/* ================= LOAD VENDOR AUTOLOAD ================= */
$autoloadPaths = [
    __DIR__ . '/../../vendor/autoload.php',
    __DIR__ . '/../../../vendor/autoload.php',
    __DIR__ . '/../../../../vendor/autoload.php',
];

$autoloadLoaded = false;
foreach ($autoloadPaths as $autoloadPath) {
    if (file_exists($autoloadPath)) {
        require_once $autoloadPath;
        $autoloadLoaded = true;
        break;
    }
}

/* ================= CONFIG - FIXED PATH ================= */
// Search for koneksi.php in multiple locations
$koneksiPaths = [
    __DIR__ . '/../../config/koneksi.php',
    __DIR__ . '/../../DataBase/koneksi.php',
    __DIR__ . '/../../database/koneksi.php',
    __DIR__ . '/../../../config/koneksi.php',
    __DIR__ . '/../../../DataBase/koneksi.php',
];

$koneksiFile = null;
foreach ($koneksiPaths as $path) {
    if (file_exists($path)) {
        $koneksiFile = $path;
        break;
    }
}

if (!$koneksiFile) {
    die('File koneksi.php tidak ditemukan. Pastikan file ada di folder config/ atau DataBase/');
}

require_once $koneksiFile;

use PhpOffice\PhpSpreadsheet\IOFactory;

date_default_timezone_set('Asia/Jakarta');

/* ================= DB CONNECTION CHECK ================= */
if (!isset($conn) || $conn->connect_error) {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($conn->connect_error) {
        die('Koneksi database gagal: ' . $conn->connect_error);
    }
    $conn->set_charset('utf8mb4');
}

/* ================= HELPERS ================= */
function cleanNum($v): float {
    if (is_numeric($v)) return (float)$v;
    $cleaned = preg_replace('/[^\d.-]/', '', str_replace(',', '', (string)$v));
    return $cleaned !== '' ? (float)$cleaned : 0.0;
}

function persen(float $c, float $t): float {
    return $t > 0 ? round(($c / $t) * 100, 2) : 0.0;
}

function kategori(float $p): string {
    if ($p >= 95) return 'EXCELLENT';
    if ($p >= 85) return 'GOOD';
    if ($p >= 70) return 'AVERAGE';
    return 'POOR';
}

function norm(string $s): string {
    return strtoupper(trim($s));
}

function sanitizeString(string $input): string {
    return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
}

/* ================= EXCEL PARSER ================= */
function parseExcelKepatuhan(string $filepath): array {
    if (!class_exists('PhpOffice\PhpSpreadsheet\IOFactory')) {
        throw new Exception('PhpSpreadsheet library tidak terinstall');
    }

    try {
        $spreadsheet = IOFactory::load($filepath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray(null, true, true, true);

        $data = [];
        $periodDetected = '';

        foreach ($rows as $i => $r) {
            if ($i === 1) continue; // skip header

            $ta = trim($r['B'] ?? '');
            if ($ta === '' || strtoupper($ta) === 'TOTAL') continue;

            $period = trim($r['C'] ?? '');
            $category = norm($r['D'] ?? 'TOTAL');

            if ($periodDetected === '' && $period !== '') {
                $periodDetected = $period;
            }

            $target = cleanNum($r['F'] ?? 0);
            $capaian = cleanNum($r['G'] ?? 0);
            if ($target == 0 && $capaian == 0) continue;

            $piutang = $target - $capaian;
            $pers = persen($capaian, $target);
            $kat = kategori($pers);

            $data[] = [
                'ta' => sanitizeString($ta),
                'period' => sanitizeString($period ?: $periodDetected),
                'category' => $category,
                'target' => $target,
                'capaian' => $capaian,
                'piutang' => $piutang,
                'persen' => $pers,
                'kategori' => $kat
            ];
        }

        if (empty($data)) {
            throw new Exception('Tidak ada data valid dalam file');
        }

        return [
            'period' => $periodDetected,
            'rows' => $data,
            'total' => count($data)
        ];

    } catch (Exception $e) {
        throw new Exception('Error parsing Excel: ' . $e->getMessage());
    }
}

/* ================= CSV PARSER (FALLBACK) ================= */
function parseCSVKepatuhan(string $filepath): array {
    $data = [];
    $periodDetected = '';

    if (($handle = fopen($filepath, 'r')) === FALSE) {
        throw new Exception('Tidak dapat membuka file CSV');
    }

    $lineNum = 0;
    while (($row = fgetcsv($handle, 10000, ',')) !== FALSE) {
        $lineNum++;

        // Skip header
        if ($lineNum === 1 || (isset($row[0]) && strtoupper(trim($row[0])) === 'NO')) {
            continue;
        }

        $ta = trim($row[1] ?? '');
        if ($ta === '' || strtoupper($ta) === 'TOTAL') continue;

        $period = trim($row[2] ?? '');
        $category = norm($row[3] ?? 'TOTAL');

        if ($periodDetected === '' && $period !== '') {
            $periodDetected = $period;
        }

        $target = cleanNum($row[5] ?? 0);
        $capaian = cleanNum($row[6] ?? 0);
        if ($target == 0 && $capaian == 0) continue;

        $piutang = $target - $capaian;
        $pers = persen($capaian, $target);
        $kat = kategori($pers);

        $data[] = [
            'ta' => sanitizeString($ta),
            'period' => sanitizeString($period ?: $periodDetected),
            'category' => $category,
            'target' => $target,
            'capaian' => $capaian,
            'piutang' => $piutang,
            'persen' => $pers,
            'kategori' => $kat
        ];
    }

    fclose($handle);

    if (empty($data)) {
        throw new Exception('Tidak ada data valid dalam file CSV');
    }

    return [
        'period' => $periodDetected,
        'rows' => $data,
        'total' => count($data)
    ];
}

/* ================= GET AVAILABLE PERIODS ================= */
function getPeriods(): array {
    global $conn;
    try {
        $result = $conn->query("
            SELECT DISTINCT TRIM(period) as period
            FROM kepatuhan_pembayaran
            WHERE UPPER(TRIM(month)) = 'TOTAL'
            ORDER BY period DESC
        ");

        $periods = [];
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                if (!empty($row['period'])) {
                    $periods[] = $row['period'];
                }
            }
        }
        return $periods;
    } catch (Exception $e) {
        error_log("Error fetching periods: " . $e->getMessage());
        return [];
    }
}

/* ================= PROCESS UPLOAD ================= */
$message = '';
$type = 'success';
$stats = null;

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file'])) {
    try {
        $file = $_FILES['file'];

        // Validate upload
        if ($file['error'] !== UPLOAD_ERR_OK) {
            throw new Exception('File tidak valid (error: ' . $file['error'] . ')');
        }

        if ($file['size'] > 10 * 1024 * 1024) {
            throw new Exception('File terlalu besar (max 10MB)');
        }

        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if (!in_array($ext, ['xlsx', 'xls', 'csv'])) {
            throw new Exception('Format harus XLSX, XLS, atau CSV');
        }

        // Parse file
        if ($ext === 'csv') {
            $parsed = parseCSVKepatuhan($file['tmp_name']);
        } else {
            $parsed = parseExcelKepatuhan($file['tmp_name']);
        }

        if (empty($parsed['period'])) {
            throw new Exception('Period tidak ditemukan dalam file');
        }

        // Start transaction
        $conn->begin_transaction();

        $stmtCheck = $conn->prepare("
            SELECT id FROM kepatuhan_pembayaran
            WHERE TRIM(UPPER(ta)) = TRIM(UPPER(?))
              AND TRIM(period) = TRIM(?)
              AND TRIM(UPPER(category)) = TRIM(UPPER(?))
              AND UPPER(TRIM(month)) = 'TOTAL'
            LIMIT 1
        ");

        $stmtInsert = $conn->prepare("
            INSERT INTO kepatuhan_pembayaran
            (ta, period, category, month, target, capaian, piutang, persen, kategori)
            VALUES (?, ?, ?, 'TOTAL', ?, ?, ?, ?, ?)
        ");

        $stmtUpdate = $conn->prepare("
            UPDATE kepatuhan_pembayaran
            SET target=?, capaian=?, piutang=?, persen=?, kategori=?, updated_at=NOW()
            WHERE id=?
        ");

        $inserted = $updated = 0;

        foreach ($parsed['rows'] as $row) {
            // Check if exists
            $stmtCheck->bind_param('sss', $row['ta'], $row['period'], $row['category']);
            $stmtCheck->execute();
            $res = $stmtCheck->get_result();

            if ($existingRow = $res->fetch_assoc()) {
                // Update
                $stmtUpdate->bind_param(
                    'ddddsi',
                    $row['target'], $row['capaian'], $row['piutang'],
                    $row['persen'], $row['kategori'], $existingRow['id']
                );
                $stmtUpdate->execute();
                $updated++;
            } else {
                // Insert
                $stmtInsert->bind_param(
                    'sssdddds',
                    $row['ta'], $row['period'], $row['category'],
                    $row['target'], $row['capaian'], $row['piutang'],
                    $row['persen'], $row['kategori']
                );
                $stmtInsert->execute();
                $inserted++;
            }
        }

        $conn->commit();

        $stats = [
            'inserted' => $inserted,
            'updated' => $updated,
            'period' => $parsed['period'],
            'total' => $inserted + $updated
        ];

        $message = "✅ Import berhasil! Baru: $inserted | Update: $updated | Periode: {$parsed['period']}";
        
        // Auto redirect after 2 seconds
        header("refresh:2;url=index.php?period=" . urlencode($parsed['period']));

    } catch (Throwable $e) {
        if ($conn->errno) {
            $conn->rollback();
        }
        $type = 'error';
        $message = '❌ ' . $e->getMessage();
    }
}

$periods = getPeriods();
$phpSpreadsheetAvailable = class_exists('PhpOffice\PhpSpreadsheet\IOFactory');

?>
<!DOCTYPE html>
<html lang="id">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Import Data - Kepatuhan Pembayaran</title>
<script src="https://cdn.tailwindcss.com"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700;800&display=swap" rel="stylesheet">
<script>
    tailwind.config = {
        theme: {
            extend: {
                colors: {
                    primary: '#7AB2B2',
                    'primary-dark': '#4D869C',
                    'primary-light': '#CDE8E5',
                    accent: '#EEF7FF',
                }
            }
        }
    }
</script>
<style>
body { font-family: 'Inter', sans-serif; }
.upload-area { transition: all 0.3s ease; }
.upload-area:hover { transform: translateY(-2px); }
</style>
</head>
<body class="bg-gradient-to-br from-slate-50 via-blue-50 to-slate-100 min-h-screen p-6">

<div class="max-w-4xl mx-auto">

    <!-- Back Button -->
    <div class="mb-4">
        <a href="index.php" class="inline-flex items-center gap-2 px-4 py-2 bg-white text-slate-700 rounded-xl font-semibold text-sm hover:shadow-lg transition-all border border-slate-200">
            <i class="fas fa-arrow-left text-primary"></i>
            Kembali ke Dashboard
        </a>
    </div>

    <!-- Header -->
    <div class="bg-gradient-to-r from-primary via-primary-dark to-primary-dark rounded-2xl shadow-xl p-6 mb-6">
        <div class="flex items-center gap-4">
            <div class="w-14 h-14 bg-white/20 rounded-xl flex items-center justify-center backdrop-blur-sm">
                <i class="fas fa-file-import text-white text-2xl"></i>
            </div>
            <div>
                <h1 class="text-2xl font-black text-white">Import Data Kepatuhan Pembayaran</h1>
                <p class="text-white/80 text-sm">Upload file Excel (.xlsx, .xls) atau CSV</p>
            </div>
        </div>
    </div>

    <!-- PhpSpreadsheet Warning -->
    <?php if (!$phpSpreadsheetAvailable): ?>
    <div class="bg-amber-50 border-l-4 border-amber-500 rounded-xl p-4 mb-6">
        <div class="flex items-start gap-3">
            <i class="fas fa-exclamation-triangle text-amber-600 text-xl mt-1"></i>
            <div>
                <h3 class="font-bold text-amber-900 mb-1">PhpSpreadsheet Tidak Terdeteksi</h3>
                <p class="text-sm text-amber-800">Hanya format CSV yang tersedia. Untuk Excel (.xlsx, .xls), pastikan PhpSpreadsheet terinstall.</p>
                <code class="text-xs bg-amber-100 px-2 py-1 rounded mt-2 inline-block">composer require phpoffice/phpspreadsheet</code>
            </div>
        </div>
    </div>
    <?php endif; ?>

    <!-- Alert Messages -->
    <?php if ($message): ?>
    <div class="bg-<?= $type === 'success' ? 'green' : 'red' ?>-50 border-l-4 border-<?= $type === 'success' ? 'green' : 'red' ?>-500 rounded-xl p-4 mb-6">
        <div class="flex items-start gap-3">
            <i class="fas fa-<?= $type === 'success' ? 'check-circle' : 'exclamation-circle' ?> text-<?= $type === 'success' ? 'green' : 'red' ?>-600 text-xl mt-1"></i>
            <div class="flex-1">
                <p class="font-semibold text-<?= $type === 'success' ? 'green' : 'red' ?>-900"><?= htmlspecialchars($message) ?></p>
            </div>
        </div>
    </div>
    <?php endif; ?>

    <!-- Import Stats -->
    <?php if ($stats): ?>
    <div class="bg-white rounded-xl shadow-lg p-6 mb-6">
        <h3 class="text-lg font-bold text-slate-800 mb-4 flex items-center gap-2">
            <i class="fas fa-chart-bar text-primary"></i>
            Hasil Import
        </h3>
        <div class="grid grid-cols-3 gap-4">
            <div class="bg-green-50 rounded-xl p-4 text-center border border-green-200">
                <div class="text-3xl font-black text-green-600"><?= $stats['inserted'] ?></div>
                <div class="text-xs font-semibold text-green-700 mt-1">Data Baru</div>
            </div>
            <div class="bg-blue-50 rounded-xl p-4 text-center border border-blue-200">
                <div class="text-3xl font-black text-blue-600"><?= $stats['updated'] ?></div>
                <div class="text-xs font-semibold text-blue-700 mt-1">Data Diupdate</div>
            </div>
            <div class="bg-primary/10 rounded-xl p-4 text-center border border-primary/30">
                <div class="text-3xl font-black text-primary-dark"><?= $stats['total'] ?></div>
                <div class="text-xs font-semibold text-primary-dark mt-1">Total</div>
            </div>
        </div>
        <p class="text-center text-sm text-slate-600 mt-4">
            <i class="fas fa-info-circle text-primary mr-1"></i>
            Mengalihkan ke dashboard periode <strong><?= htmlspecialchars($stats['period']) ?></strong> dalam 2 detik...
        </p>
    </div>
    <?php endif; ?>

    <!-- Upload Form -->
    <div class="bg-white rounded-xl shadow-lg p-6 mb-6">
        <div class="bg-slate-50 rounded-xl p-4 mb-6 border border-slate-200">
            <h3 class="font-bold text-slate-800 mb-3 flex items-center gap-2">
                <i class="fas fa-info-circle text-primary"></i>
                Petunjuk Import
            </h3>
            <ul class="space-y-2 text-sm text-slate-700">
                <li class="flex items-start gap-2">
                    <i class="fas fa-check text-green-500 mt-1"></i>
                    <span>Format file: <strong><?= $phpSpreadsheetAvailable ? 'Excel (.xlsx, .xls) atau CSV' : 'CSV' ?></strong></span>
                </li>
                <li class="flex items-start gap-2">
                    <i class="fas fa-check text-green-500 mt-1"></i>
                    <span>Kolom: <code class="text-xs bg-slate-200 px-1 rounded">NO | TA | PERIODE | KATEGORI | BULAN | TARGET | CAPAIAN</code></span>
                </li>
                <li class="flex items-start gap-2">
                    <i class="fas fa-check text-green-500 mt-1"></i>
                    <span>Piutang, Persentase, dan Status dihitung otomatis</span>
                </li>
                <li class="flex items-start gap-2">
                    <i class="fas fa-check text-green-500 mt-1"></i>
                    <span>Data duplikat akan diupdate secara otomatis</span>
                </li>
                <li class="flex items-start gap-2">
                    <i class="fas fa-check text-green-500 mt-1"></i>
                    <span>Maksimal ukuran file: <strong>10 MB</strong></span>
                </li>
            </ul>
        </div>

        <form method="POST" enctype="multipart/form-data">
            <label class="block mb-2 text-sm font-bold text-slate-700">
                <i class="fas fa-file-excel text-green-600 mr-1"></i>
                Pilih File
            </label>
            
            <div class="upload-area border-3 border-dashed border-slate-300 rounded-xl p-8 text-center bg-slate-50 hover:bg-slate-100 hover:border-primary cursor-pointer mb-4" 
                 onclick="document.getElementById('fileInput').click()">
                <i class="fas fa-cloud-upload-alt text-5xl text-primary mb-3"></i>
                <p class="font-bold text-slate-800 mb-1">Klik untuk memilih file</p>
                <p class="text-xs text-slate-500">Format: <?= $phpSpreadsheetAvailable ? '.xlsx, .xls, .csv' : '.csv' ?> (Max. 10MB)</p>
                <input type="file" 
                       name="file" 
                       id="fileInput" 
                       accept="<?= $phpSpreadsheetAvailable ? '.xlsx,.xls,.csv' : '.csv' ?>" 
                       required 
                       class="hidden">
            </div>
            
            <div id="fileName" class="text-sm text-slate-600 mb-4 hidden">
                <i class="fas fa-file-excel text-green-600"></i>
                <span id="fileNameText" class="font-semibold"></span>
                <span id="fileSize" class="text-slate-500"></span>
            </div>

            <button type="submit" 
                    class="w-full bg-gradient-to-r from-primary to-primary-dark text-white font-bold py-3 rounded-xl hover:shadow-xl transition-all text-base">
                <i class="fas fa-upload mr-2"></i>
                Upload & Import Data
            </button>
        </form>
    </div>

    <!-- Available Periods -->
    <?php if (!empty($periods)): ?>
    <div class="bg-white rounded-xl shadow-lg p-6">
        <h3 class="text-sm font-bold text-slate-700 mb-3 flex items-center gap-2">
            <i class="fas fa-database text-primary"></i>
            Periode yang Tersedia di Database
        </h3>
        <div class="flex flex-wrap gap-2">
            <?php foreach ($periods as $p): ?>
            <span class="px-3 py-1 bg-primary/10 text-primary-dark rounded-full text-xs font-bold border border-primary/20">
                <?= htmlspecialchars($p) ?>
            </span>
            <?php endforeach; ?>
        </div>
    </div>
    <?php endif; ?>

</div>

<script>
document.getElementById('fileInput').addEventListener('change', function(e) {
    const file = e.target.files[0];
    if (file) {
        const fileNameDiv = document.getElementById('fileName');
        const fileNameText = document.getElementById('fileNameText');
        const fileSize = document.getElementById('fileSize');
        
        fileNameText.textContent = file.name;
        fileSize.textContent = ' (' + (file.size / 1024 / 1024).toFixed(2) + ' MB)';
        fileNameDiv.classList.remove('hidden');
    }
});
</script>

</body>
</html>
<?php
if (isset($conn)) {
    $conn->close();
}
?>