<?php
/**
 * Import/Export API
 * API สำหรับนำเข้าและส่งออกข้อมูลบุคลากร
 */

require_once '../../config/config.php';
require_once '../../models/Personnel.php';

// ตั้งค่า Content-Type
header('Content-Type: application/json; charset=utf-8');

// ตรวจสอบ HTTP Method
$method = $_SERVER['REQUEST_METHOD'];

try {
    // ตรวจสอบสิทธิ์
    requireAdmin();
    
    $personnel = new Personnel();
    
    switch ($method) {
        case 'POST':
            handlePost($personnel);
            break;
        case 'GET':
            handleGet($personnel);
            break;
        default:
            sendJsonResponse(['error' => 'Method not allowed'], 405);
    }
    
} catch (Exception $e) {
    error_log("Import/Export API error: " . $e->getMessage());
    sendJsonResponse(['error' => 'เกิดข้อผิดพลาดในระบบ'], 500);
}

// POST - Import data
function handlePost($personnel) {
    $action = $_GET['action'] ?? 'import';
    
    switch ($action) {
        case 'import':
            handleImport($personnel);
            break;
        case 'validate':
            handleValidate($personnel);
            break;
        default:
            sendJsonResponse(['error' => 'Action not found'], 404);
    }
}

// GET - Export data
function handleGet($personnel) {
    $action = $_GET['action'] ?? 'export';
    
    switch ($action) {
        case 'export':
            handleExport($personnel);
            break;
        case 'template':
            handleTemplate();
            break;
        default:
            sendJsonResponse(['error' => 'Action not found'], 404);
    }
}

// Import data from file
function handleImport($personnel) {
    if (!isset($_FILES['file'])) {
        sendJsonResponse(['error' => 'ไม่พบไฟล์ที่อัปโหลด'], 400);
    }
    
    $file = $_FILES['file'];
    $fileType = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    
    // ตรวจสอบประเภทไฟล์
    if (!in_array($fileType, ['csv', 'xlsx', 'xls'])) {
        sendJsonResponse(['error' => 'รองรับเฉพาะไฟล์ CSV และ Excel'], 400);
    }
    
    // ตรวจสอบขนาดไฟล์
    if ($file['size'] > MAX_FILE_SIZE) {
        sendJsonResponse(['error' => 'ไฟล์มีขนาดใหญ่เกินไป (สูงสุด 5MB)'], 400);
    }
    
    try {
        $data = [];
        
        if ($fileType === 'csv') {
            $data = parseCSV($file['tmp_name']);
        } else {
            $data = parseExcel($file['tmp_name']);
        }
        
        if (empty($data)) {
            sendJsonResponse(['error' => 'ไม่พบข้อมูลในไฟล์'], 400);
        }
        
        // ตรวจสอบข้อมูล
        $validation = validateImportData($data);
        if (!$validation['valid']) {
            sendJsonResponse([
                'error' => 'ข้อมูลไม่ถูกต้อง',
                'details' => $validation['errors']
            ], 400);
        }
        
        // นำเข้าข้อมูล
        $result = importPersonnelData($personnel, $data);
        
        sendJsonResponse([
            'success' => true,
            'message' => 'นำเข้าข้อมูลสำเร็จ',
            'data' => $result
        ]);
        
    } catch (Exception $e) {
        error_log("Import error: " . $e->getMessage());
        sendJsonResponse(['error' => 'เกิดข้อผิดพลาดในการนำเข้าข้อมูล'], 500);
    }
}

// Validate import data
function handleValidate($personnel) {
    if (!isset($_FILES['file'])) {
        sendJsonResponse(['error' => 'ไม่พบไฟล์ที่อัปโหลด'], 400);
    }
    
    $file = $_FILES['file'];
    $fileType = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    
    try {
        $data = [];
        
        if ($fileType === 'csv') {
            $data = parseCSV($file['tmp_name']);
        } else {
            $data = parseExcel($file['tmp_name']);
        }
        
        $validation = validateImportData($data);
        
        sendJsonResponse([
            'success' => true,
            'valid' => $validation['valid'],
            'errors' => $validation['errors'],
            'data' => $validation['valid'] ? $data : null
        ]);
        
    } catch (Exception $e) {
        error_log("Validation error: " . $e->getMessage());
        sendJsonResponse(['error' => 'เกิดข้อผิดพลาดในการตรวจสอบข้อมูล'], 500);
    }
}

// Export data
function handleExport($personnel) {
    $format = $_GET['format'] ?? 'excel';
    $filters = [
        'search' => $_GET['search'] ?? '',
        'rank' => $_GET['rank'] ?? '',
        'gender' => $_GET['gender'] ?? ''
    ];
    
    try {
        // ดึงข้อมูล
        $data = $personnel->getAll(1000, 0, $filters['search'], $filters['rank'], $filters['gender']);
        
        if ($format === 'csv') {
            exportCSV($data);
        } else {
            exportExcel($data);
        }
        
    } catch (Exception $e) {
        error_log("Export error: " . $e->getMessage());
        sendJsonResponse(['error' => 'เกิดข้อผิดพลาดในการส่งออกข้อมูล'], 500);
    }
}

// Download template
function handleTemplate() {
    $format = $_GET['format'] ?? 'excel';
    
    if ($format === 'csv') {
        downloadCSVTemplate();
    } else {
        downloadExcelTemplate();
    }
}

// Parse CSV file
function parseCSV($filePath) {
    $data = [];
    $handle = fopen($filePath, 'r');
    
    if ($handle === false) {
        throw new Exception('ไม่สามารถเปิดไฟล์ CSV ได้');
    }
    
    // อ่าน header
    $headers = fgetcsv($handle);
    if (!$headers) {
        fclose($handle);
        throw new Exception('ไฟล์ CSV ไม่มี header');
    }
    
    // อ่านข้อมูล
    while (($row = fgetcsv($handle)) !== false) {
        if (count($row) === count($headers)) {
            $data[] = array_combine($headers, $row);
        }
    }
    
    fclose($handle);
    return $data;
}

// Parse Excel file
function parseExcel($filePath) {
    // ใช้ PhpSpreadsheet library
    require_once '../../vendor/autoload.php';
    
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
    $spreadsheet = $reader->load($filePath);
    $worksheet = $spreadsheet->getActiveSheet();
    
    $data = [];
    $headers = [];
    $rowIndex = 1;
    
    // อ่าน header
    foreach ($worksheet->getRowIterator(1, 1) as $row) {
        foreach ($row->getCellIterator() as $cell) {
            $headers[] = $cell->getValue();
        }
        break;
    }
    
    // อ่านข้อมูล
    foreach ($worksheet->getRowIterator(2) as $row) {
        $rowData = [];
        $cellIndex = 0;
        
        foreach ($row->getCellIterator() as $cell) {
            if ($cellIndex < count($headers)) {
                $rowData[$headers[$cellIndex]] = $cell->getValue();
                $cellIndex++;
            }
        }
        
        if (!empty(array_filter($rowData))) {
            $data[] = $rowData;
        }
    }
    
    return $data;
}

// Validate import data
function validateImportData($data) {
    $errors = [];
    $requiredFields = ['personnel_id', 'first_name', 'last_name'];
    
    foreach ($data as $index => $row) {
        $rowNumber = $index + 2; // +2 เพราะมี header และ index เริ่มจาก 0
        
        // ตรวจสอบฟิลด์ที่จำเป็น
        foreach ($requiredFields as $field) {
            if (empty($row[$field])) {
                $errors[] = "แถวที่ $rowNumber: ฟิลด์ $field จำเป็นต้องกรอก";
            }
        }
        
        // ตรวจสอบรหัสบุคลากรซ้ำ
        if (!empty($row['personnel_id'])) {
            // TODO: ตรวจสอบในฐานข้อมูล
        }
        
        // ตรวจสอบรูปแบบอีเมล
        if (!empty($row['email']) && !validateEmail($row['email'])) {
            $errors[] = "แถวที่ $rowNumber: รูปแบบอีเมลไม่ถูกต้อง";
        }
        
        // ตรวจสอบรูปแบบเบอร์โทรศัพท์
        if (!empty($row['phone']) && !preg_match('/^[0-9\-\+\(\)\s]+$/', $row['phone'])) {
            $errors[] = "แถวที่ $rowNumber: รูปแบบเบอร์โทรศัพท์ไม่ถูกต้อง";
        }
    }
    
    return [
        'valid' => empty($errors),
        'errors' => $errors
    ];
}

// Import personnel data
function importPersonnelData($personnel, $data) {
    $successCount = 0;
    $errorCount = 0;
    $errors = [];
    
    foreach ($data as $index => $row) {
        try {
            $personnelData = [
                'personnel_id' => $row['personnel_id'],
                'first_name' => $row['first_name'],
                'last_name' => $row['last_name'],
                'nickname' => $row['nickname'] ?? null,
                'is_public' => isset($row['is_public']) ? (bool)$row['is_public'] : true,
                'personal' => [
                    'citizen_id' => $row['citizen_id'] ?? null,
                    'birth_date' => $row['birth_date'] ?? null,
                    'gender' => $row['gender'] ?? null,
                    'phone' => $row['phone'] ?? null,
                    'email' => $row['email'] ?? null,
                    'address' => $row['address'] ?? null
                ],
                'rank' => [
                    'rank_name' => $row['rank_name'] ?? 'ไม่ระบุ',
                    'rank_level' => $row['rank_level'] ?? 'ครูผู้ช่วย',
                    'is_current' => true
                ],
                'teaching' => [
                    'subject_group' => $row['subject_group'] ?? 'การงานอาชีพ',
                    'academic_year' => $row['academic_year'] ?? date('Y') + 543,
                    'is_current' => true
                ],
                'work' => [
                    'work_status' => $row['work_status'] ?? 'ทำงาน',
                    'start_date' => $row['start_date'] ?? date('Y-m-d')
                ]
            ];
            
            $result = $personnel->create($personnelData);
            if ($result) {
                $successCount++;
            } else {
                $errorCount++;
                $errors[] = "แถวที่ " . ($index + 2) . ": ไม่สามารถบันทึกข้อมูลได้";
            }
            
        } catch (Exception $e) {
            $errorCount++;
            $errors[] = "แถวที่ " . ($index + 2) . ": " . $e->getMessage();
        }
    }
    
    return [
        'success_count' => $successCount,
        'error_count' => $errorCount,
        'errors' => $errors
    ];
}

// Export CSV
function exportCSV($data) {
    $filename = 'personnel_export_' . date('Y-m-d_H-i-s') . '.csv';
    
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    
    $output = fopen('php://output', 'w');
    
    // BOM for UTF-8
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // Headers
    $headers = [
        'รหัสบุคลากร', 'ชื่อ', 'นามสกุล', 'ชื่อเล่น', 'เพศ', 'เบอร์โทรศัพท์', 
        'อีเมล', 'วิทยฐานะ', 'กลุ่มสาระ', 'สถานะการทำงาน'
    ];
    fputcsv($output, $headers);
    
    // Data
    foreach ($data as $row) {
        fputcsv($output, [
            $row['personnel_id'],
            $row['first_name'],
            $row['last_name'],
            $row['nickname'] ?? '',
            $row['gender'] ?? '',
            $row['phone'] ?? '',
            $row['email'] ?? '',
            $row['rank_name'] ?? '',
            $row['subject_group'] ?? '',
            $row['work_status'] ?? ''
        ]);
    }
    
    fclose($output);
    exit;
}

// Export Excel
function exportExcel($data) {
    require_once '../../vendor/autoload.php';
    
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    
    // Headers
    $headers = [
        'A1' => 'รหัสบุคลากร',
        'B1' => 'ชื่อ',
        'C1' => 'นามสกุล',
        'D1' => 'ชื่อเล่น',
        'E1' => 'เพศ',
        'F1' => 'เบอร์โทรศัพท์',
        'G1' => 'อีเมล',
        'H1' => 'วิทยฐานะ',
        'I1' => 'กลุ่มสาระ',
        'J1' => 'สถานะการทำงาน'
    ];
    
    foreach ($headers as $cell => $value) {
        $worksheet->setCellValue($cell, $value);
    }
    
    // Data
    $row = 2;
    foreach ($data as $item) {
        $worksheet->setCellValue('A' . $row, $item['personnel_id']);
        $worksheet->setCellValue('B' . $row, $item['first_name']);
        $worksheet->setCellValue('C' . $row, $item['last_name']);
        $worksheet->setCellValue('D' . $row, $item['nickname'] ?? '');
        $worksheet->setCellValue('E' . $row, $item['gender'] ?? '');
        $worksheet->setCellValue('F' . $row, $item['phone'] ?? '');
        $worksheet->setCellValue('G' . $row, $item['email'] ?? '');
        $worksheet->setCellValue('H' . $row, $item['rank_name'] ?? '');
        $worksheet->setCellValue('I' . $row, $item['subject_group'] ?? '');
        $worksheet->setCellValue('J' . $row, $item['work_status'] ?? '');
        $row++;
    }
    
    // Auto-size columns
    foreach (range('A', 'J') as $col) {
        $worksheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    $filename = 'personnel_export_' . date('Y-m-d_H-i-s') . '.xlsx';
    
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

// Download CSV template
function downloadCSVTemplate() {
    $filename = 'personnel_template.csv';
    
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    
    $output = fopen('php://output', 'w');
    
    // BOM for UTF-8
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // Headers
    $headers = [
        'personnel_id', 'first_name', 'last_name', 'nickname', 'gender', 
        'phone', 'email', 'address', 'rank_name', 'rank_level', 
        'subject_group', 'work_status', 'is_public'
    ];
    fputcsv($output, $headers);
    
    // Sample data
    $sample = [
        'P001', 'สมชาย', 'ใจดี', 'ชาย', 'ชาย', '081-234-5678', 
        'somchai@example.com', '123 ถนนสุขุมวิท', 'ครูชำนาญการ', 'ครูชำนาญการ', 
        'การงานอาชีพ', 'ทำงาน', '1'
    ];
    fputcsv($output, $sample);
    
    fclose($output);
    exit;
}

// Download Excel template
function downloadExcelTemplate() {
    require_once '../../vendor/autoload.php';
    
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    
    // Headers
    $headers = [
        'A1' => 'personnel_id',
        'B1' => 'first_name',
        'C1' => 'last_name',
        'D1' => 'nickname',
        'E1' => 'gender',
        'F1' => 'phone',
        'G1' => 'email',
        'H1' => 'address',
        'I1' => 'rank_name',
        'J1' => 'rank_level',
        'K1' => 'subject_group',
        'L1' => 'work_status',
        'M1' => 'is_public'
    ];
    
    foreach ($headers as $cell => $value) {
        $worksheet->setCellValue($cell, $value);
    }
    
    // Sample data
    $worksheet->setCellValue('A2', 'P001');
    $worksheet->setCellValue('B2', 'สมชาย');
    $worksheet->setCellValue('C2', 'ใจดี');
    $worksheet->setCellValue('D2', 'ชาย');
    $worksheet->setCellValue('E2', 'ชาย');
    $worksheet->setCellValue('F2', '081-234-5678');
    $worksheet->setCellValue('G2', 'somchai@example.com');
    $worksheet->setCellValue('H2', '123 ถนนสุขุมวิท');
    $worksheet->setCellValue('I2', 'ครูชำนาญการ');
    $worksheet->setCellValue('J2', 'ครูชำนาญการ');
    $worksheet->setCellValue('K2', 'การงานอาชีพ');
    $worksheet->setCellValue('L2', 'ทำงาน');
    $worksheet->setCellValue('M2', '1');
    
    // Auto-size columns
    foreach (range('A', 'M') as $col) {
        $worksheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    $filename = 'personnel_template.xlsx';
    
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}
?>
