<?php
require_once __DIR__.'/../../config/functions.php';
requireLogin();

header('Content-Type: application/json');

if (!verifyCsrfToken($_POST['csrf_token'] ?? '')) {
    echo json_encode(['success' => false, 'message' => 'Ralat CSRF.']);
    exit;
}

if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
    echo json_encode(['success' => false, 'message' => 'Sila pilih fail CSV yang sah.']);
    exit;
}

// Auto-migrate database to add missing columns if they don't exist
try {
    $pdo->exec("ALTER TABLE takwim ADD COLUMN peringkat VARCHAR(100) DEFAULT NULL AFTER tajuk");
} catch(Exception $e) {}
try {
    $pdo->exec("ALTER TABLE takwim ADD COLUMN pengelola_keb VARCHAR(200) DEFAULT NULL AFTER peringkat");
} catch(Exception $e) {}
try {
    $pdo->exec("ALTER TABLE takwim ADD COLUMN bidang VARCHAR(100) DEFAULT NULL AFTER pengelola_keb");
} catch(Exception $e) {}
try {
    $pdo->exec("ALTER TABLE takwim ADD COLUMN subunit_id INT UNSIGNED DEFAULT NULL AFTER unit_id");
} catch(Exception $e) {}

$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");

if ($handle === FALSE) {
    echo json_encode(['success' => false, 'message' => 'Gagal membaca fail.']);
    exit;
}

// Skip BOM if exists
$bom = fread($handle, 3);
if ($bom !== "\xEF\xBB\xBF") {
    rewind($handle);
}

// Read headers
$headers = fgetcsv($handle);
if (!$headers) {
    echo json_encode(['success' => false, 'message' => 'Fail CSV kosong.']);
    fclose($handle);
    exit;
}

// Normalize headers: Trim, Uppercase, and replace spaces with underscores
$normHeaders = array_map(function($h) {
    return strtoupper(str_replace(' ', '_', trim($h)));
}, $headers);

function findIdx($needles, $haystack) {
    foreach ($needles as $n) {
        $idx = array_search(strtoupper($n), $haystack);
        if ($idx !== false) return $idx;
    }
    return false;
}

$idIdx = findIdx(['ID'], $normHeaders);
$tajukIdx = findIdx(['NAMA_PROGRAM', 'NAMA_PROG', 'PROGRAM', 'TAJUK'], $normHeaders);
$tMulaIdx = findIdx(['T_MULA', 'TARIKH_MULA', 'START_DATE'], $normHeaders);
$tAkhirIdx = findIdx(['T_AKHIR', 'TARIKH_TAMAT', 'END_DATE'], $normHeaders);
$peringkatIdx = findIdx(['PERINGKAT'], $normHeaders);
$pengelolaIdx = findIdx(['PENGELOLA', 'PENGELOLA_KEB', 'PENGANJUR'], $normHeaders);
$subunitIdx = findIdx(['SUBUNIT', 'SUBUNIT_ID', 'NAMA_SUBUNIT'], $normHeaders);
$unitIdx = findIdx(['UNIT', 'UNIT_ID', 'KOD_UNIT'], $normHeaders);
$pegawaiIdx = findIdx(['PEGAWAI', 'PEGAWAI_ID', 'NAMA_PEGAWAI'], $normHeaders);
$catatanIdx = findIdx(['CATATAN', 'DESKRIPSI', 'REMARKS'], $normHeaders);
$masaMulaIdx = findIdx(['MASA_MULA', 'START_TIME', 'MASA'], $normHeaders);
$masaTamatIdx = findIdx(['MASA_TAMAT', 'END_TIME'], $normHeaders);
$semuaHariIdx = findIdx(['SEMUA_HARI', 'ALL_DAY'], $normHeaders);

if ($tajukIdx === false || $tMulaIdx === false) {
    echo json_encode(['success' => false, 'message' => 'Format CSV tidak sah. Header tidak dijumpai. Pastikan ada lajur NAMA PROG dan T_MULA.']);
    fclose($handle);
    exit;
}

// Cache units for lookup
$unitsCache = [];
$stmtU = $pdo->query("SELECT id, kod, nama_unit FROM unit WHERE status=1");
while ($u = $stmtU->fetch()) {
    $unitsCache['ID_'.trim($u['id'])] = $u['id'];
    if (!empty($u['kod'])) $unitsCache[strtoupper(trim($u['kod']))] = $u['id'];
}

// Cache subunits for lookup
$subunitsCache = [];
$stmtS = $pdo->query("SELECT id, nama_subunit FROM subunit WHERE status=1");
while ($s = $stmtS->fetch()) {
    $subunitsCache['ID_'.trim($s['id'])] = $s['id'];
    if (!empty($s['nama_subunit'])) $subunitsCache[strtoupper(trim($s['nama_subunit']))] = $s['id'];
}

// Cache pegawai for lookup
$pegawaiCache = [];
$stmtP = $pdo->query("SELECT id, nama FROM pegawai WHERE status=1");
while ($p = $stmtP->fetch()) {
    $pegawaiCache['ID_'.trim($p['id'])] = $p['id'];
    if (!empty($p['nama'])) $pegawaiCache[strtoupper(trim($p['nama']))] = $p['id'];
}

$countInsert = 0;
$countUpdate = 0;
$countError = 0;

function parseDate($dateStr) {
    $dateStr = trim($dateStr);
    if (empty($dateStr)) return null;
    if (preg_match('/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/', $dateStr, $m)) {
        return $m[3] . '-' . str_pad($m[2], 2, '0', STR_PAD_LEFT) . '-' . str_pad($m[1], 2, '0', STR_PAD_LEFT);
    }
    if (preg_match('/^(\d{4})[\/\-](\d{1,2})[\/\-](\d{1,2})$/', $dateStr, $m)) {
        return $m[1] . '-' . str_pad($m[2], 2, '0', STR_PAD_LEFT) . '-' . str_pad($m[3], 2, '0', STR_PAD_LEFT);
    }
    return null;
}

$currentUserId = currentUserId();

while (($data = fgetcsv($handle)) !== FALSE) {
    $idVal = ($idIdx !== false && isset($data[$idIdx])) ? trim($data[$idIdx]) : '';
    $tajukVal = isset($data[$tajukIdx]) ? trim($data[$tajukIdx]) : '';
    $tMulaVal = isset($data[$tMulaIdx]) ? parseDate($data[$tMulaIdx]) : null;
    $tAkhirVal = ($tAkhirIdx !== false && isset($data[$tAkhirIdx])) ? parseDate($data[$tAkhirIdx]) : null;
    
    if (empty($tajukVal) || empty($tMulaVal)) {
        $countError++;
        continue;
    }

    $peringkatVal = ($peringkatIdx !== false && isset($data[$peringkatIdx])) ? trim($data[$peringkatIdx]) : null;
    $pengelolaVal = ($pengelolaIdx !== false && isset($data[$pengelolaIdx])) ? trim($data[$pengelolaIdx]) : null;
    $subunitStr = ($subunitIdx !== false && isset($data[$subunitIdx])) ? strtoupper(trim($data[$subunitIdx])) : '';
    $unitStr = ($unitIdx !== false && isset($data[$unitIdx])) ? strtoupper(trim($data[$unitIdx])) : '';
    $pegawaiStr = ($pegawaiIdx !== false && isset($data[$pegawaiIdx])) ? strtoupper(trim($data[$pegawaiIdx])) : '';
    $catatanVal = ($catatanIdx !== false && isset($data[$catatanIdx])) ? trim($data[$catatanIdx]) : null;
    $masaMulaVal = ($masaMulaIdx !== false && isset($data[$masaMulaIdx]) && !empty(trim($data[$masaMulaIdx]))) ? trim($data[$masaMulaIdx]) : null;
    $masaTamatVal = ($masaTamatIdx !== false && isset($data[$masaTamatIdx]) && !empty(trim($data[$masaTamatIdx]))) ? trim($data[$masaTamatIdx]) : null;
    
    // Determine semua_hari status
    if ($semuaHariIdx !== false && isset($data[$semuaHariIdx])) {
        $sh = strtoupper(trim($data[$semuaHariIdx]));
        $semuaHariVal = ($sh === '1' || $sh === 'YA' || $sh === 'YES' || $sh === 'TRUE') ? 1 : 0;
    } else {
        // Default to all-day (1) if no start time is provided
        $semuaHariVal = empty($masaMulaVal) ? 1 : 0;
    }
    
    // Lookup unit
    $unitId = !empty($unitStr) ? ($unitsCache[$unitStr] ?? ($unitsCache['ID_'.$unitStr] ?? null)) : null;
    
    // Lookup subunit
    $subunitId = !empty($subunitStr) ? ($subunitsCache[$subunitStr] ?? ($subunitsCache['ID_'.$subunitStr] ?? null)) : null;
    
    // Lookup pegawai
    $pegawaiId = !empty($pegawaiStr) ? ($pegawaiCache[$pegawaiStr] ?? ($pegawaiCache['ID_'.$pegawaiStr] ?? null)) : null;
    
    if (!empty($idVal) && is_numeric($idVal)) {
        $chk = $pdo->prepare("SELECT id FROM takwim WHERE id=?");
        $chk->execute([$idVal]);
        if ($chk->fetch()) {
            $upd = $pdo->prepare("UPDATE takwim SET tajuk=?, tarikh_mula=?, tarikh_tamat=?, masa_mula=?, masa_tamat=?, semua_hari=?, peringkat=?, pengelola_keb=?, subunit_id=?, unit_id=?, pegawai_id=?, deskripsi=?, updated_at=NOW() WHERE id=?");
            $upd->execute([$tajukVal, $tMulaVal, $tAkhirVal, $masaMulaVal, $masaTamatVal, $semuaHariVal, $peringkatVal, $pengelolaVal, $subunitId, $unitId, $pegawaiId, $catatanVal, $idVal]);
            $countUpdate++;
            continue;
        }
    }
    
    $ins = $pdo->prepare("INSERT INTO takwim (tajuk, tarikh_mula, tarikh_tamat, masa_mula, masa_tamat, semua_hari, peringkat, pengelola_keb, subunit_id, unit_id, pegawai_id, deskripsi, created_by, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)");
    $ins->execute([$tajukVal, $tMulaVal, $tAkhirVal, $masaMulaVal, $masaTamatVal, $semuaHariVal, $peringkatVal, $pengelolaVal, $subunitId, $unitId, $pegawaiId, $catatanVal, $currentUserId]);
    $countInsert++;
}

fclose($handle);

echo json_encode([
    'success' => true, 
    'message' => "Selesai! $countInsert rekod ditambah, $countUpdate rekod dikemaskini." . ($countError > 0 ? " ($countError baris gagal kerana tiada tajuk atau tarikh mula)" : "")
]);
