<?php
include('include/headscript.php');
require __DIR__ . '/../../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['xlsFile'])) {

    $headerRow = intval($_POST['headerRow']) - 1;
    $fileTmp = $_FILES['xlsFile']['tmp_name'];

    if (!$fileTmp) {
        echo json_encode(['status' => 'error', 'message' => 'No file uploaded.']);
        exit;
    }

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

        // Normalize headers
        $headers = $data[$headerRow + 1];
        $normalizedHeaders = [];
        foreach ($headers as $k => $v) {
            $key = strtolower(trim($v));
            $key = str_replace([' ', '.', '-', '/'], '_', $key);
            $normalizedHeaders[$k] = $key;
        }

        // Build data array
        $rows = array_slice($data, $headerRow + 1);
        $array = [];
        foreach ($rows as $row) {
            $item = [];
            foreach ($normalizedHeaders as $k => $key) {
                $item[$key] = trim($row[$k]);
            }
            $array[] = $item;
        }

        // Extract categories
        $categories = [];
        foreach ($array as $row) {
            if (!empty($row['category'])) {
                $cat = trim($row['category']);
                if ($cat !== '' && !in_array($cat, $categories)) {
                    $categories[] = $cat;
                }
            }
        }
        sort($categories);

        echo json_encode([
            'status' => 'ok',
            'columns' => array_keys($array[0] ?? []),
            'categories' => $categories,
            'data' => $array
        ]);
    } catch (Exception $e) {
        echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
    }
}