<?php
/**
 * PFA XML Import Handler
 * Handles file upload and database import with real-time progress reporting
 * Supports large files up to 2GB
 */

// CRITICAL: Set limits for large file uploads
@ini_set('upload_max_filesize', '2048M');
@ini_set('post_max_size', '2048M');
@ini_set('max_execution_time', '1800');     // 30 minutes
@ini_set('max_input_time', '1800');
@ini_set('memory_limit', '2048M');
set_time_limit(1800);

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

class ImportHandler {
    private $pdo;
    private $logs = [];
    private $stats = [
        'total_processed' => 0,
        'success_count' => 0,
        'error_count' => 0,
        'duration' => 0
    ];
    private $startTime;
    private $sanctionsMap = [];
    private $countryMap = [];
    private $description1Map = [];
    private $description2Map = [];
    private $description3Map = [];
    
    public function __construct() {
        $this->startTime = microtime(true);
    }
    
    public function handleRequest() {
        try {
            // Validate request
            if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
                throw new Exception('Invalid request method');
            }
            
            // Get database credentials
            $dbHost = $_POST['db_host'] ?? 'localhost';
            $dbName = $_POST['db_name'] ?? '';
            $dbUser = $_POST['db_user'] ?? '';
            $dbPass = $_POST['db_pass'] ?? '';
            
            if (empty($dbName) || empty($dbUser)) {
                throw new Exception('Database credentials are required');
            }
            
            // Check import method
            $importMethod = $_POST['import_method'] ?? 'upload';
            
            if ($importMethod === 'server') {
                // Handle server file import
                $serverFilePath = $_POST['server_file_path'] ?? '';
                
                if (empty($serverFilePath)) {
                    throw new Exception('Server file path is required');
                }
                
                // Validate file exists and is readable
                if (!file_exists($serverFilePath)) {
                    throw new Exception('File not found on server: ' . $serverFilePath);
                }
                
                if (!is_readable($serverFilePath)) {
                    throw new Exception('File is not readable: ' . $serverFilePath);
                }
                
                $fileExt = strtolower(pathinfo($serverFilePath, PATHINFO_EXTENSION));
                $fileName = basename($serverFilePath);
                $fileSize = filesize($serverFilePath);
                
                $this->log('Using server file: ' . $fileName, 'success');
                $this->log('File path: ' . $serverFilePath, 'info');
                $this->log('File size: ' . $this->formatFileSize($fileSize), 'info');
                
                // Handle ZIP or XML
                if ($fileExt === 'zip') {
                    $xmlFiles = $this->extractZipFile($serverFilePath);
                    
                    if (empty($xmlFiles)) {
                        throw new Exception('No XML files found in ZIP archive');
                    }
                    
                    $this->log('Found ' . count($xmlFiles) . ' XML file(s) in ZIP archive', 'info');
                    
                    // Connect to database
                    $this->connectDatabase($dbHost, $dbName, $dbUser, $dbPass);
                    
                    // Create table
                    $this->createTable();
                    
                    // Process each XML file
                    foreach ($xmlFiles as $index => $xmlFile) {
                        $xmlFileName = basename($xmlFile);
                        $this->log("Processing file " . ($index + 1) . "/" . count($xmlFiles) . ": $xmlFileName", 'info');
                        $this->importXML($xmlFile, $xmlFileName);
                        
                        // Clean up extracted file
                        @unlink($xmlFile);
                    }
                    
                    // Clean up temp directory
                    $this->cleanupTempFiles($xmlFiles);
                    
                } else {
                    // Handle single XML file
                    if (!$this->isValidXML($serverFilePath)) {
                        throw new Exception('Invalid XML file');
                    }
                    
                    // Connect to database
                    $this->connectDatabase($dbHost, $dbName, $dbUser, $dbPass);
                    
                    // Create table
                    $this->createTable();
                    
                    // Import data
                    $this->importXML($serverFilePath, $fileName);
                }
                
            } else {
                // Handle file upload (original method)
                if (!isset($_FILES['xml_file']) || $_FILES['xml_file']['error'] !== UPLOAD_ERR_OK) {
                    throw new Exception('File upload failed');
                }
                
                $uploadedFile = $_FILES['xml_file'];
                $fileExt = strtolower(pathinfo($uploadedFile['name'], PATHINFO_EXTENSION));
                
                $this->log('File uploaded successfully: ' . $uploadedFile['name'], 'success');
                $this->log('File size: ' . $this->formatFileSize($uploadedFile['size']), 'info');
                
                // Handle ZIP files
                if ($fileExt === 'zip') {
                    $xmlFiles = $this->extractZipFile($uploadedFile['tmp_name']);
                    
                    if (empty($xmlFiles)) {
                        throw new Exception('No XML files found in ZIP archive');
                    }
                    
                    $this->log('Found ' . count($xmlFiles) . ' XML file(s) in ZIP archive', 'info');
                    
                    // Connect to database
                    $this->connectDatabase($dbHost, $dbName, $dbUser, $dbPass);
                    
                    // Create table
                    $this->createTable();
                    
                    // Process each XML file
                    foreach ($xmlFiles as $index => $xmlFile) {
                        $xmlFileName = basename($xmlFile);
                        $this->log("Processing file " . ($index + 1) . "/" . count($xmlFiles) . ": $xmlFileName", 'info');
                        $this->importXML($xmlFile, $xmlFileName);
                        
                        // Clean up extracted file
                        @unlink($xmlFile);
                    }
                    
                    // Clean up temp directory
                    $this->cleanupTempFiles($xmlFiles);
                    
                } else {
                    // Handle single XML file
                    if (!$this->isValidXML($uploadedFile['tmp_name'])) {
                        throw new Exception('Invalid XML file');
                    }
                    
                    // Connect to database
                    $this->connectDatabase($dbHost, $dbName, $dbUser, $dbPass);
                    
                    // Create table
                    $this->createTable();
                    
                    // Import data
                    $this->importXML($uploadedFile['tmp_name'], $uploadedFile['name']);
                }
            }
            
            // Calculate duration
            $this->stats['duration'] = round(microtime(true) - $this->startTime, 2);
            
            // Return success response
            $this->sendResponse(true);
            
        } catch (Exception $e) {
            $this->log('Error: ' . $e->getMessage(), 'error');
            $this->sendResponse(false);
        }
    }
    
    private function isValidXML($file) {
        libxml_use_internal_errors(true);
        $xml = simplexml_load_file($file);
        libxml_clear_errors();
        return $xml !== false;
    }
    
    private function extractZipFile($zipPath) {
        if (!class_exists('ZipArchive')) {
            throw new Exception('ZIP extension is not available on this server');
        }
        
        $zip = new ZipArchive();
        $xmlFiles = [];
        
        if ($zip->open($zipPath) === true) {
            $tempDir = sys_get_temp_dir() . '/pfa_import_' . uniqid();
            
            if (!mkdir($tempDir, 0755, true)) {
                throw new Exception('Failed to create temporary directory');
            }
            
            $this->log('Extracting ZIP archive...', 'info');
            
            // Extract all files
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $filename = $zip->getNameIndex($i);
                $fileInfo = pathinfo($filename);
                
                // Skip directories and non-XML files
                if (substr($filename, -1) === '/' || 
                    !isset($fileInfo['extension']) || 
                    strtolower($fileInfo['extension']) !== 'xml') {
                    continue;
                }
                
                // Extract file
                $extractedPath = $tempDir . '/' . basename($filename);
                
                if (copy("zip://" . $zipPath . "#" . $filename, $extractedPath)) {
                    // Validate XML
                    if ($this->isValidXML($extractedPath)) {
                        $xmlFiles[] = $extractedPath;
                        $this->log('Extracted: ' . basename($filename), 'success');
                    } else {
                        $this->log('Skipped invalid XML: ' . basename($filename), 'warning');
                        @unlink($extractedPath);
                    }
                }
            }
            
            $zip->close();
            
            $this->log('Successfully extracted ' . count($xmlFiles) . ' valid XML file(s)', 'success');
            
        } else {
            throw new Exception('Failed to open ZIP archive');
        }
        
        return $xmlFiles;
    }
    
    private function cleanupTempFiles($files) {
        foreach ($files as $file) {
            if (file_exists($file)) {
                @unlink($file);
            }
        }
        
        // Remove temp directory if empty
        if (!empty($files)) {
            $tempDir = dirname($files[0]);
            if (is_dir($tempDir)) {
                @rmdir($tempDir);
            }
        }
    }
    
    private function connectDatabase($host, $dbName, $user, $pass) {
        try {
            $dsn = "mysql:host=$host;dbname=$dbName;charset=utf8mb4";
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ];
            $this->pdo = new PDO($dsn, $user, $pass, $options);
            $this->log('Database connected successfully', 'success');
        } catch (PDOException $e) {
            throw new Exception('Database connection failed: ' . $e->getMessage());
        }
    }
    
    private function createTable() {
        $sql = "CREATE TABLE IF NOT EXISTS pfa_records (
            id INT AUTO_INCREMENT PRIMARY KEY,
            external_id VARCHAR(50) NOT NULL,
            name_type VARCHAR(50),
            first_name VARCHAR(255),
            middle_name VARCHAR(255),
            surname VARCHAR(255),
            single_string_name VARCHAR(500),
            title VARCHAR(255),
            gender VARCHAR(20),
            date_of_birth DATE,
            nationality VARCHAR(100),
            category VARCHAR(100),
            description1 VARCHAR(255),
            description2 VARCHAR(255),
            description3 VARCHAR(255),
            program VARCHAR(255),
            occupation TEXT,
            address TEXT,
            country VARCHAR(100),
            list_set VARCHAR(100),
            list_version VARCHAR(50),
            zone VARCHAR(100),
            sanctions_references TEXT,
            active_status VARCHAR(50),
            deceased VARCHAR(10),
            source_xml_file VARCHAR(255),
            raw_xml_data LONGTEXT,
            disabled TINYINT(1) DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_external_id (external_id),
            INDEX idx_name (first_name, surname),
            INDEX idx_active_status (active_status),
            INDEX idx_deceased (deceased)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        try {
            $this->pdo->exec($sql);
            $this->log('Table structure verified/created', 'success');
        } catch (PDOException $e) {
            throw new Exception('Table creation failed: ' . $e->getMessage());
        }
    }
    
    private function importXML($filePath, $fileName) {
        $this->log('Loading XML file...', 'info');
        
        libxml_use_internal_errors(true);
        $xml = simplexml_load_file($filePath);
        
        if ($xml === false) {
            throw new Exception('Failed to parse XML file');
        }
        
        $this->log('XML parsed successfully', 'success');
        
        // Get metadata
        $listVersion = (string)$xml['date'];
        
        // Build reference maps
        $this->buildReferenceMaps($xml);
        
        // Process records
        $this->processRecords($xml, $fileName, $listVersion);
    }
    
    private function buildReferenceMaps($xml) {
        $this->log('Building reference maps...', 'info');
        
        // Sanctions references
        if (isset($xml->SanctionsReferencesList->ReferenceName)) {
            foreach ($xml->SanctionsReferencesList->ReferenceName as $ref) {
                $code = (string)$ref['code'];
                $this->sanctionsMap[$code] = (string)$ref['name'];
            }
        }
        
        // Countries
        if (isset($xml->CountryList->CountryName)) {
            foreach ($xml->CountryList->CountryName as $country) {
                $code = (string)$country['code'];
                $this->countryMap[$code] = (string)$country['name'];
            }
        }
        
        // Descriptions
        if (isset($xml->Description1List->Description)) {
            foreach ($xml->Description1List->Description as $desc) {
                $code = (string)$desc['code'];
                $this->description1Map[$code] = (string)$desc['name'];
            }
        }
        
        if (isset($xml->Description2List->Description)) {
            foreach ($xml->Description2List->Description as $desc) {
                $code = (string)$desc['code'];
                $this->description2Map[$code] = (string)$desc['name'];
            }
        }
        
        if (isset($xml->Description3List->Description)) {
            foreach ($xml->Description3List->Description as $desc) {
                $code = (string)$desc['code'];
                $this->description3Map[$code] = (string)$desc['name'];
            }
        }
        
        $this->log('Reference maps built - Countries: ' . count($this->countryMap) . 
                   ', Sanctions: ' . count($this->sanctionsMap), 'info');
    }
    
    private function processRecords($xml, $fileName, $listVersion) {
        if (!isset($xml->Records)) {
            throw new Exception('No records found in XML');
        }
        
        $this->log('Starting record import from: ' . $fileName, 'info');
        
        // Prepare insert statement
        $stmt = $this->pdo->prepare("
            INSERT INTO pfa_records (
                external_id, name_type, first_name, middle_name, surname, 
                single_string_name, title, gender, date_of_birth, nationality,
                category, description1, description2, description3, program,
                occupation, address, country, list_set, list_version,
                zone, sanctions_references, active_status, deceased,
                source_xml_file, raw_xml_data, disabled
            ) VALUES (
                :external_id, :name_type, :first_name, :middle_name, :surname,
                :single_string_name, :title, :gender, :date_of_birth, :nationality,
                :category, :description1, :description2, :description3, :program,
                :occupation, :address, :country, :list_set, :list_version,
                :zone, :sanctions_references, :active_status, :deceased,
                :source_xml_file, :raw_xml_data, :disabled
            )
        ");
        
        $totalRecords = 0;
        
        // Count records
        foreach ($xml->Records->children() as $record) {
            if ($record->getName() === 'Person') {
                $totalRecords++;
            }
        }
        
        $this->log("Found $totalRecords person record(s) in this file", 'info');
        
        $processedCount = 0;
        $fileSuccessCount = 0;
        $fileErrorCount = 0;
        
        foreach ($xml->Records->children() as $record) {
            if ($record->getName() === 'Person') {
                try {
                    $data = $this->extractPersonData($record, $fileName, $listVersion);
                    $stmt->execute($data);
                    $fileSuccessCount++;
                    $processedCount++;
                    
                    // Log progress every 50 records
                    if ($processedCount % 50 == 0) {
                        $percent = round(($processedCount / $totalRecords) * 100);
                        $this->log("Progress: $processedCount/$totalRecords records ($percent%)", 'info');
                    }
                    
                } catch (PDOException $e) {
                    $fileErrorCount++;
                    $this->log('Error inserting record ID ' . (string)$record['id'] . ': ' . $e->getMessage(), 'error');
                }
            }
        }
        
        // Update cumulative stats
        $this->stats['total_processed'] += $totalRecords;
        $this->stats['success_count'] += $fileSuccessCount;
        $this->stats['error_count'] += $fileErrorCount;
        
        $this->log("File completed - Success: $fileSuccessCount, Errors: $fileErrorCount", 'success');
    }
    
    private function extractPersonData($person, $fileName, $listVersion) {
        return [
            'external_id' => (string)$person['id'],
            'name_type' => $this->extractNameType($person),
            'first_name' => $this->extractFirstName($person),
            'middle_name' => $this->extractMiddleName($person),
            'surname' => $this->extractSurname($person),
            'single_string_name' => $this->extractSingleStringName($person),
            'title' => $this->extractTitle($person),
            'gender' => (string)$person->Gender,
            'date_of_birth' => $this->extractDateOfBirth($person),
            'nationality' => $this->extractNationality($person),
            'category' => $this->extractCategory($person),
            'description1' => $this->extractDescription1($person),
            'description2' => $this->extractDescription2($person),
            'description3' => $this->extractDescription3($person),
            'program' => null,
            'occupation' => $this->extractOccupation($person),
            'address' => $this->extractAddress($person),
            'country' => $this->extractCountry($person),
            'list_set' => 'PFA',
            'list_version' => $listVersion,
            'zone' => null,
            'sanctions_references' => null,
            'active_status' => (string)$person->ActiveStatus,
            'deceased' => (string)$person->Deceased,
            'source_xml_file' => $fileName,
            'raw_xml_data' => $person->asXML(),
            'disabled' => 0
        ];
    }
    
    private function extractNameType($person) {
        return isset($person->NameDetails->Name->NameValue) ? 'Primary' : null;
    }
    
    private function extractFirstName($person) {
        return isset($person->NameDetails->Name->NameValue->FirstName) 
            ? (string)$person->NameDetails->Name->NameValue->FirstName : null;
    }
    
    private function extractMiddleName($person) {
        return isset($person->NameDetails->Name->NameValue->MiddleName)
            ? (string)$person->NameDetails->Name->NameValue->MiddleName : null;
    }
    
    private function extractSurname($person) {
        return isset($person->NameDetails->Name->NameValue->Surname)
            ? (string)$person->NameDetails->Name->NameValue->Surname : null;
    }
    
    private function extractSingleStringName($person) {
        return isset($person->NameDetails->Name->NameValue->SingleStringName)
            ? (string)$person->NameDetails->Name->NameValue->SingleStringName : null;
    }
    
    private function extractTitle($person) {
        return isset($person->NameDetails->Name->NameValue->Title)
            ? (string)$person->NameDetails->Name->NameValue->Title : null;
    }
    
    private function extractDateOfBirth($person) {
        if (isset($person->DateDetails->Date)) {
            foreach ($person->DateDetails->Date as $date) {
                if (isset($date->DateValue)) {
                    $dateValue = $date->DateValue;
                    $year = (string)$dateValue['Year'];
                    $month = (string)$dateValue['Month'];
                    $day = (string)$dateValue['Day'];
                    
                    if ($year && $month && $day) {
                        $monthNum = date('m', strtotime($month . ' 1'));
                        try {
                            return sprintf('%04d-%02d-%02d', $year, $monthNum, $day);
                        } catch (Exception $e) {
                            continue;
                        }
                    }
                }
            }
        }
        return null;
    }
    
    private function extractNationality($person) {
        $nationalities = [];
        if (isset($person->CountryDetails->Country)) {
            foreach ($person->CountryDetails->Country as $country) {
                if (isset($country->CountryValue)) {
                    $code = (string)$country->CountryValue['Code'];
                    if (isset($this->countryMap[$code])) {
                        $nationalities[] = $this->countryMap[$code];
                    }
                }
            }
        }
        return !empty($nationalities) ? implode(', ', array_unique($nationalities)) : null;
    }
    
    private function extractCategory($person) {
        if (isset($person->RoleDetail->Roles->OccTitle)) {
            $occCat = (string)$person->RoleDetail->Roles->OccTitle['OccCat'];
            return $occCat ? 'Category-' . $occCat : null;
        }
        return null;
    }
    
    private function extractDescription1($person) {
        if (isset($person->Descriptions->Description)) {
            foreach ($person->Descriptions->Description as $desc) {
                $code = (string)$desc['Description1'];
                if ($code && isset($this->description1Map[$code])) {
                    return $this->description1Map[$code];
                }
            }
        }
        return null;
    }
    
    private function extractDescription2($person) {
        if (isset($person->Descriptions->Description)) {
            foreach ($person->Descriptions->Description as $desc) {
                $code = (string)$desc['Description2'];
                if ($code && isset($this->description2Map[$code])) {
                    return $this->description2Map[$code];
                }
            }
        }
        return null;
    }
    
    private function extractDescription3($person) {
        if (isset($person->Descriptions->Description)) {
            foreach ($person->Descriptions->Description as $desc) {
                $code = (string)$desc['Description3'];
                if ($code && isset($this->description3Map[$code])) {
                    return $this->description3Map[$code];
                }
            }
        }
        return null;
    }
    
    private function extractOccupation($person) {
        $occupations = [];
        if (isset($person->RoleDetail->Roles)) {
            foreach ($person->RoleDetail->Roles as $role) {
                if (isset($role->OccTitle)) {
                    $title = (string)$role->OccTitle;
                    $since = (string)$role->OccTitle['SinceYear'];
                    $to = (string)$role->OccTitle['ToYear'];
                    
                    $occStr = $title;
                    if ($since || $to) {
                        $occStr .= ' (' . $since . ($to ? ' - ' . $to : '') . ')';
                    }
                    $occupations[] = $occStr;
                }
            }
        }
        return !empty($occupations) ? implode('; ', $occupations) : null;
    }
    
    private function extractAddress($person) {
        $addresses = [];
        if (isset($person->BirthPlace->Place)) {
            foreach ($person->BirthPlace->Place as $place) {
                $name = (string)$place['name'];
                if ($name) {
                    $addresses[] = $name;
                }
            }
        }
        return !empty($addresses) ? implode('; ', $addresses) : null;
    }
    
    private function extractCountry($person) {
        if (isset($person->CountryDetails->Country->CountryValue)) {
            $code = (string)$person->CountryDetails->Country->CountryValue['Code'];
            return isset($this->countryMap[$code]) ? $this->countryMap[$code] : null;
        }
        return null;
    }
    
    private function log($message, $type = 'info') {
        $this->logs[] = [
            'message' => $message,
            'type' => $type,
            'timestamp' => date('Y-m-d H:i:s')
        ];
    }
    
    private function sendResponse($success) {
        $response = [
            'success' => $success,
            'complete' => true,
            'logs' => $this->logs,
            'stats' => $this->stats,
            'progress' => 100
        ];
        
        echo json_encode($response);
        exit;
    }
    
    private function formatFileSize($bytes) {
        if ($bytes < 1024) return $bytes . ' B';
        if ($bytes < 1024 * 1024) return round($bytes / 1024, 2) . ' KB';
        return round($bytes / (1024 * 1024), 2) . ' MB';
    }
}

// Execute handler
$handler = new ImportHandler();
$handler->handleRequest();
