<?php
error_reporting(E_ALL);
ini_set('display_errors', 0); // Don't output errors directly

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

register_shutdown_function(function() {
    $error = error_get_last();
    if ($error !== null && in_array($error['type'], [E_ERROR, E_CORE_ERROR, E_COMPILE_ERROR, E_USER_ERROR])) {
        // Clear any previous output
        while (ob_get_level()) { ob_end_clean(); }
        echo json_encode([
            'status' => 'error', 
            'message' => 'Fatal Error: ' . $error['message'] . ' in ' . $error['file'] . ' on line ' . $error['line']
        ]);
    }
});

ob_start(); // Start output buffering

require_once 'config.php';
// ต้องรัน composer require phpoffice/phpspreadsheet ก่อน
if (file_exists('vendor/autoload.php')) {
    require 'vendor/autoload.php';
} else {
    ob_clean();
    echo json_encode(['status' => 'error', 'message' => 'ไม่พบ Library PhpSpreadsheet กรุณารัน composer install']);
    exit;
}

use PhpOffice\PhpSpreadsheet\IOFactory;

checkLogin();

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    echo json_encode(['status' => 'error', 'message' => 'Invalid request method']);
    exit;
}

if (!isset($_FILES['excel_file']) || $_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
    echo json_encode(['status' => 'error', 'message' => 'Upload failed or no file selected']);
    exit;
}

if (empty($_POST['profile_id'])) {
    echo json_encode(['status' => 'error', 'message' => 'กรุณาเลือกชุดข้อมูล (Profile)']);
    exit;
}
$profile_id = $_POST['profile_id'];

// Get Profile Info
$log_db = getDB(DB_LOG_HOST, DB_LOG_NAME, DB_LOG_USER, DB_LOG_PASS);
$stmt = $log_db->prepare("SELECT * FROM export_profiles WHERE id = ?");
$stmt->execute([$profile_id]);
$profile = $stmt->fetch();

if (!$profile) {
    echo json_encode(['status' => 'error', 'message' => 'ไม่พบชุดข้อมูลที่เลือก']);
    exit;
}

$expectedCols = array_map('trim', explode(',', strtolower($profile['excel_columns'])));

$file = $_FILES['excel_file']['tmp_name'];
$fileName = $_FILES['excel_file']['name'];

try {
    set_time_limit(300); // Allow up to 5 minutes for processing

    // Load Excel file
    $spreadsheet = IOFactory::load($file);
    $worksheet = $spreadsheet->getActiveSheet();
    
    // Find column indexes
    $headerRow = $worksheet->getRowIterator(1)->current();
    $cellIterator = $headerRow->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    
    $colIndexes = [];
    foreach ($cellIterator as $cell) {
        $val = strtolower(trim($cell->getValue() ?? ''));
        if (in_array($val, $expectedCols)) {
            $colIndexes[$val] = $cell->getColumn();
        }
    }
    
    foreach ($expectedCols as $ec) {
        if (!isset($colIndexes[$ec])) {
            echo json_encode(['status' => 'error', 'message' => "ไม่พบคอลัมน์ '$ec' ในไฟล์ Excel แถวแรก"]);
            exit;
        }
    }
    
    // Extract Data from Excel
    $excelData = [];
    foreach ($worksheet->getRowIterator(2) as $row) {
        $rowData = [];
        $hasData = false;
        foreach ($expectedCols as $ec) {
            $cell = $worksheet->getCell($colIndexes[$ec] . $row->getRowIndex());
            $val = trim($cell->getValue() ?? '');
            $rowData[$ec] = $val;
            if ($val !== '') $hasData = true;
        }
        if ($hasData) {
            $excelData[] = $rowData;
        }
    }
    
    if (empty($excelData)) {
        echo json_encode(['status' => 'error', 'message' => 'ไม่พบข้อมูลในไฟล์ Excel']);
        exit;
    }
    
    // Check if SQL parameters match Excel columns
    preg_match_all('/:([a-zA-Z0-9_]+)/', $profile['sql_query'], $matches);
    $sqlParams = array_unique(array_map('strtolower', $matches[1]));
    
    $missingInExcelCols = array_diff($sqlParams, $expectedCols);
    if (!empty($missingInExcelCols)) {
        echo json_encode(['status' => 'error', 'message' => 'คำสั่ง SQL เรียกใช้เงื่อนไข [:' . implode(', :', $missingInExcelCols) . '] แต่คุณไม่ได้กำหนดคอลัมน์นี้ไว้ในการตั้งค่าชุดข้อมูล (คอลัมน์ Excel)']);
        exit;
    }
    
    // Query HOSxP Database
    $his_db = getDB(DB_HIS_HOST, DB_HIS_NAME, DB_HIS_USER, DB_HIS_PASS);
    
    $results = [];
    try {
        $stmt = $his_db->prepare($profile['sql_query']);
    } catch (PDOException $e) {
        echo json_encode(['status' => 'error', 'message' => 'รูปแบบคำสั่ง SQL ไม่ถูกต้อง หรืออ้างอิงฟิลด์/ตารางที่ไม่มีอยู่จริง: ' . $e->getMessage()]);
        exit;
    }
    
    // Execute query for each row
    foreach ($excelData as $index => $rowData) {
        $params = [];
        // Bind ONLY the parameters that the SQL query expects to avoid PDO "Invalid parameter number" errors
        foreach ($sqlParams as $sp) {
            $params[':' . $sp] = $rowData[$sp];
        }
        
        try {
            $stmt->execute($params);
            $res = $stmt->fetchAll();
        } catch (PDOException $e) {
            echo json_encode(['status' => 'error', 'message' => 'เกิดข้อผิดพลาดในการรัน SQL แถวที่ ' . ($index + 2) . ' ของไฟล์ Excel: ' . $e->getMessage()]);
            exit;
        }
        
        if (!empty($res)) {
            // Convert TIS-620/Windows-874 to UTF-8
            foreach ($res as &$r) {
                foreach ($r as $key => $value) {
                    if (is_string($value)) {
                        $r[$key] = iconv('TIS-620', 'UTF-8//IGNORE', $value);
                    }
                }
            }
            unset($r); // break reference
            
            $results = array_merge($results, $res);
        }
    }
    
    // Sort globally by the first column returned by the query (usually person_id or hn)
    // if $results is not empty
    if (!empty($results)) {
        $firstKey = array_key_first($results[0]);
        usort($results, function($a, $b) use ($firstKey) {
            // numeric sort if looks like numbers, else string sort
            if (is_numeric($a[$firstKey]) && is_numeric($b[$firstKey])) {
                return (float)$a[$firstKey] <=> (float)$b[$firstKey];
            }
            return strcmp($a[$firstKey], $b[$firstKey]);
        });
    }
    
    // Log to hosxp_export_person
    $logStmt = $log_db->prepare("INSERT INTO export_logs (username, profile_id, file_name, total_rows, found_rows) VALUES (?, ?, ?, ?, ?)");
    $logStmt->execute([
        $_SESSION['fullname'] ?? 'Unknown User',
        $profile_id,
        $fileName,
        count($excelData),
        count($results)
    ]);
    
    // Add system log
    addSystemLog('EXPORT_DATA', "ประมวลผลข้อมูล (ไฟล์: $fileName, พบ: " . count($results) . "/" . count($excelData) . " รายการ)");
    
    ob_clean(); // Clear buffer before sending success
    $json = json_encode([
        'status' => 'success',
        'message' => 'ประมวลผลสำเร็จ พบข้อมูล ' . count($results) . ' รายการ จากทั้งหมด ' . count($excelData) . ' รายการ',
        'data' => $results
    ], JSON_INVALID_UTF8_SUBSTITUTE);

    if ($json === false) {
        echo json_encode(['status' => 'error', 'message' => 'JSON Encode Error: ' . json_last_error_msg()]);
    } else {
        echo $json;
    }

} catch (Throwable $e) {
    ob_clean(); // Clear buffer before sending error
    echo json_encode(['status' => 'error', 'message' => 'Error: ' . $e->getMessage()]);
}