<?php
// Mengaktifkan Debug Log
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Membuat log debug
$log_file = 'debug_log.txt';
function write_log($message) {
    global $log_file;
    $log_message = date('Y-m-d H:i:s') . ' - ' . $message . "\n";
    file_put_contents($log_file, $log_message, FILE_APPEND);
}

// Koneksi ke Database
$servername = "103.247.8.48";
$username = "ptbc8361_tasks";
$password = "ptbc8361_tasks";
$dbname = "ptbc8361_tasks";

// Membuat koneksi
$conn = new mysqli($servername, $username, $password, $dbname);

// Cek koneksi
if ($conn->connect_error) {
    write_log("Koneksi gagal: " . $conn->connect_error);
    die("Koneksi gagal: " . $conn->connect_error);
}
write_log("Koneksi berhasil");

// Memuat library PHPSpreadsheet
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

// Nama tabel database
$table_name = 'project_progress';

// Enqueue Styles dan Scripts
function enqueue_csv_styles_scripts() {
    echo '<link rel="stylesheet" href="style.css">';
    echo '<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>';
    echo '<script src="script.js"></script>';
}
enqueue_csv_styles_scripts();

// Tambahkan log untuk POST dan FILES
write_log("POST data: " . json_encode($_POST));
write_log("FILES data: " . json_encode($_FILES));

// Memeriksa batas ukuran unggahan
write_log("upload_max_filesize: " . ini_get('upload_max_filesize'));
write_log("post_max_size: " . ini_get('post_max_size'));

$previewData = [];
$csvFilePath = 'converted_file.csv';
?>

<!DOCTYPE html>
<html>
<head>
    <title>Upload dan Update Data XLSX</title>
</head>
<body>
<div class="csv-upload-container">
    <h1>Unggah dan Update Data XLSX</h1>
    <form id="csv-upload-form" method="POST" enctype="multipart/form-data">
        <input type="file" name="csvFile" id="csvFile" accept=".xlsx" required>
        <button type="submit" name="preview" id="execute-upload" class="button button-primary">Tampilkan Preview</button>
    </form>

    <?php if (!empty($previewData)): ?>
        <h3>Preview Data XLSX (10 Baris Pertama):</h3>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Project</th>
                    <th>Progress</th>
                    <th>Status</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($previewData as $data): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($data[0]); ?></td>
                        <td><?php echo htmlspecialchars($data[1]); ?></td>
                        <td><?php echo htmlspecialchars($data[5]); ?></td>
                        <td><?php echo htmlspecialchars($data[6]); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        <form method="POST">
            <button type="submit" name="execute_upload" class="button button-success">Proses Pembaruan</button>
        </form>
    <?php endif; ?>
</div>
</body>
</html>

<?php
// Logika saat form di-submit untuk preview
if (isset($_POST['preview']) && isset($_FILES['csvFile']) && $_FILES['csvFile']['error'] === UPLOAD_ERR_OK) {
    write_log("Mulai proses preview file");
    $fileTmpName = $_FILES['csvFile']['tmp_name'];
    if (!file_exists($fileTmpName)) {
        write_log("File tidak ditemukan di tmp: " . $fileTmpName);
        exit;
    }
    try {
        $spreadsheet = IOFactory::load($fileTmpName);
        $sheet = $spreadsheet->getActiveSheet();

        // Konversi file XLSX menjadi CSV
        $csvWriter = new Csv($spreadsheet);
        $csvWriter->setDelimiter(';');
        $csvWriter->save($csvFilePath);
        write_log("File XLSX berhasil dikonversi menjadi CSV: $csvFilePath");

        $counter = 0;
        foreach ($sheet->getRowIterator() as $row) {
            if ($counter === 0) {
                $counter++;
                continue; // Lewati header
            }
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            $rowData = [];
            foreach ($cellIterator as $cell) {
                $rowData[] = $cell->getValue();
            }
            $previewData[] = $rowData;
            if (++$counter > 10) break;
        }
        write_log("Preview data berhasil ditampilkan");
    } catch (Exception $e) {
        write_log("Error saat memuat file: " . $e->getMessage());
    }
}

// Logika untuk memproses pembaruan data ke database
if (isset($_POST['execute_upload']) && file_exists($csvFilePath)) {
    write_log("Mulai proses unggah data dari file CSV");
    $fileHandle = fopen($csvFilePath, 'r');
    $conn->query("TRUNCATE TABLE $table_name");

    fgetcsv($fileHandle, 0, ';'); // Lewati header
    while (($row = fgetcsv($fileHandle, 0, ';')) !== FALSE) {
        $data = array_map('mysqli_real_escape_string', array_combine(
            ['id', 'project', 'tidp_midp', 'nama_file', 'pic', 'progres', 'status', 'bobot',
             'tanggal_mulai_rencana', 'tanggal_mulai_realisasi', 'tanggal_selesai_rencana',
             'tanggal_selesai_realisasi', 'keterangan'],
            $row
        ));
        $sql = "INSERT INTO $table_name (" . implode(',', array_keys($data)) . ") VALUES ('" . implode("','", $data) . "')";
        if ($conn->query($sql) === TRUE) {
            write_log("Data ID {$data['id']} berhasil dimasukkan");
        } else {
            write_log("Error memasukkan data: " . $conn->error);
        }
    }
    fclose($fileHandle);
    unlink($csvFilePath); // Hapus file CSV setelah selesai
    echo "<div class='updated'><p>Data berhasil diperbarui dengan data baru!</p></div>";
}

$conn->close();
write_log("Koneksi ditutup");
?>
