<?php

error_reporting(E_ALL);
ini_set('display_errors', 1);

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

require_once __DIR__ . '/../../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

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

$file = $_FILES['risr_file'];

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

$ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));

if ($ext !== 'xlsx') {
    echo json_encode(['status' => 'error','message' => 'Only .xlsx files allowed.']);
    exit;
}

$uploadDir = __DIR__ . '/../../uploads/';
if (!is_dir($uploadDir)) {
    mkdir($uploadDir, 0777, true);
}

$filePath = $uploadDir . uniqid('risr_', true) . '.xlsx';

if (!move_uploaded_file($file['tmp_name'], $filePath)) {
    echo json_encode(['status' => 'error','message' => 'Failed to save file.']);
    exit;
}

try {
    $spreadsheet = IOFactory::load($filePath);
    $sheet = $spreadsheet->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    $headerRow = $sheet->rangeToArray('A1:' . $highestColumn . '1', null, true, false);
    $foundHeaders = $headerRow[0];

    $requiredHeaders = [
        'Question title',
        'Question text',
        'Core condition',
        'Presentation',
        'Skill',
        'Specialty',
        'Summative/Formative',
        'Year'
    ];
    $missingHeaders = array_values(array_diff($requiredHeaders, $foundHeaders));
    if (!empty($missingHeaders)) {
        echo json_encode([
            'status' => 'error',
            'message' => 'Missing required headers: ' . implode(', ', $missingHeaders)
        ]);
        exit;
    }

    // ---------- FIND ANSWER COLUMN INDEXES ----------
    $answerIndexes = [];
    foreach ($foundHeaders as $i => $header) {
        if ($header === 'Available answers') {
            $answerIndexes[] = $i;
        }
    }
    if (count($answerIndexes) !== 5 && count($answerIndexes) !== 1) {
        echo json_encode([
            'status' => 'error',
            'message' => "Invalid 'Available answers' columns count: expected 1 or 5, found " . count($answerIndexes) . '.'
        ]);
        exit;
    }

    $questionDetails = [];

    for ($row = 2; $row <= $highestRow; $row++) {

        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, '', true, false);
        $rowValues = $rowData[0];

        $rowAssoc = [];

        // ---------- NORMAL FIELDS ----------
        foreach ($foundHeaders as $index => $header) {
            if ($header !== 'Available answers') {
                $rowAssoc[$header] = $rowValues[$index] ?? '';
            }
        }

        // ---------- ANSWER FIELDS ----------
        /*
         * Two "Available answers" layouts are supported:
         * - Five columns: one option per column, cell shape "answer;score" — score stripped per column.
         * - One column: a single cell with five pairs "answer;score;answer;score;..." (10 semicolon-separated parts).
         *   Trailing semicolons on the cell are trimmed. A backslash before a semicolon (\;) is a literal semicolon
         *   inside an answer, not a field separator. Only the five answer texts are stored as Option A–E; scores are ignored.
         */
        $letters = ['A','B','C','D','E'];

        if (count($answerIndexes) === 5) {
            foreach ($answerIndexes as $i => $colIndex) {

                if (!isset($letters[$i])) continue;

                $raw = $rowValues[$colIndex] ?? '';

                // Remove score after semicolon
                $clean = explode(';', $raw)[0];

                $rowAssoc['Option ' . $letters[$i]] = trim($clean);
            }
        } else {
            $raw = $rowValues[$answerIndexes[0]] ?? '';

            $raw = rtrim(trim($raw), ';');

            if ($raw === '') {
                echo json_encode([
                    'status' => 'error',
                    'message' => "Row {$row}: 'Available answers' must not be empty."
                ]);
                exit;
            }

            $escSemiPlaceholder = "\x01\x02\x03SEMI\x03\x02\x01";
            $normalized = str_replace('\\;', $escSemiPlaceholder, $raw);

            $parts = explode(';', $normalized);

            if (count($parts) !== 10) {
                echo json_encode([
                    'status' => 'error',
                    'message' => "Row {$row}: 'Available answers' must contain exactly 10 semicolon-separated values, found " . count($parts) . '.'
                ]);
                exit;
            }

            $rowAssoc['Option A'] = trim(str_replace($escSemiPlaceholder, ';', $parts[0]));
            $rowAssoc['Option B'] = trim(str_replace($escSemiPlaceholder, ';', $parts[2]));
            $rowAssoc['Option C'] = trim(str_replace($escSemiPlaceholder, ';', $parts[4]));
            $rowAssoc['Option D'] = trim(str_replace($escSemiPlaceholder, ';', $parts[6]));
            $rowAssoc['Option E'] = trim(str_replace($escSemiPlaceholder, ';', $parts[8]));
        }

        $questionDetails[] = [
            'row_number' => $row,
            'data' => $rowAssoc
        ];
    }

    echo json_encode([
        'status' => 'success',
        'message' => 'File uploaded successfully.',
        'row_count' => $highestRow,
        'highest_column' => $highestColumn,
        'saved_file' => basename($filePath),
        'question_details' => $questionDetails
    ]);

} catch (Throwable $e) {
    echo json_encode([
        'status' => 'error',
        'message' => 'File uploaded but could not be read: ' . $e->getMessage()
    ]);
}