<?php
/**
 * PFA XML to MySQL Importer
 * Imports PFA (Politically Exposed Persons) XML data into MySQL database
 */

// Database Configuration
define('DB_HOST', 'localhost');
define('DB_NAME', 'your_database_name');
define('DB_USER', 'your_username');
define('DB_PASS', 'your_password');
define('DB_CHARSET', 'utf8mb4');

// File Configuration
define('XML_FILE_PATH', 'PFA2_202602102200_D_reduced.xml');

class PFAImporter {
    private $pdo;
    private $xmlFile;
    private $listVersion;
    private $listDate;
    private $sanctionsMap = [];
    private $countryMap = [];
    private $description1Map = [];
    private $description2Map = [];
    private $description3Map = [];
    
    public function __construct($xmlFile) {
        $this->xmlFile = $xmlFile;
        $this->connectDatabase();
    }
    
    private function connectDatabase() {
        try {
            $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
            $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, DB_USER, DB_PASS, $options);
            echo "✓ Database connected successfully\n";
        } catch (PDOException $e) {
            die("Database connection failed: " . $e->getMessage() . "\n");
        }
    }
    
    public 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);
            echo "✓ Table 'pfa_records' created/verified successfully\n";
        } catch (PDOException $e) {
            die("Table creation failed: " . $e->getMessage() . "\n");
        }
    }
    
    public function import() {
        echo "Starting import process...\n";
        
        // Load and parse XML
        libxml_use_internal_errors(true);
        $xml = simplexml_load_file($this->xmlFile);
        
        if ($xml === false) {
            $errors = libxml_get_errors();
            foreach ($errors as $error) {
                echo "XML Error: " . $error->message . "\n";
            }
            libxml_clear_errors();
            die("Failed to load XML file\n");
        }
        
        echo "✓ XML file loaded successfully\n";
        
        // Get file metadata
        $this->listDate = (string)$xml['date'];
        $this->listVersion = (string)$xml['date'];
        
        // Build reference maps
        $this->buildReferenceMaps($xml);
        
        // Process records
        $this->processRecords($xml);
        
        echo "\n✓ Import completed successfully\n";
    }
    
    private function buildReferenceMaps($xml) {
        echo "Building reference maps...\n";
        
        // 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'];
            }
        }
        
        echo "✓ Reference maps built (Sanctions: " . count($this->sanctionsMap) . 
             ", Countries: " . count($this->countryMap) . ")\n";
    }
    
    private function processRecords($xml) {
        if (!isset($xml->Records)) {
            echo "No records found in XML\n";
            return;
        }
        
        $totalRecords = 0;
        $successCount = 0;
        $errorCount = 0;
        
        // 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
            )
        ");
        
        // Process each Person record
        foreach ($xml->Records->children() as $record) {
            if ($record->getName() === 'Person') {
                $totalRecords++;
                
                try {
                    $data = $this->extractPersonData($record);
                    $stmt->execute($data);
                    $successCount++;
                    
                    if ($totalRecords % 100 == 0) {
                        echo "Processed $totalRecords records...\n";
                    }
                } catch (PDOException $e) {
                    $errorCount++;
                    echo "Error inserting record ID " . (string)$record['id'] . ": " . $e->getMessage() . "\n";
                }
            }
        }
        
        echo "\nImport Summary:\n";
        echo "  Total records processed: $totalRecords\n";
        echo "  Successfully inserted: $successCount\n";
        echo "  Errors: $errorCount\n";
    }
    
    private function extractPersonData($person) {
        $data = [
            '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' => $this->extractProgram($person),
            'occupation' => $this->extractOccupation($person),
            'address' => $this->extractAddress($person),
            'country' => $this->extractCountry($person),
            'list_set' => 'PFA',
            'list_version' => $this->listVersion,
            'zone' => null,
            'sanctions_references' => $this->extractSanctionsReferences($person),
            'active_status' => (string)$person->ActiveStatus,
            'deceased' => (string)$person->Deceased,
            'source_xml_file' => basename($this->xmlFile),
            'raw_xml_data' => $person->asXML(),
            'disabled' => 0
        ];
        
        return $data;
    }
    
    private function extractNameType($person) {
        if (isset($person->NameDetails->Name->NameValue)) {
            return 'Primary';
        }
        return null;
    }
    
    private function extractFirstName($person) {
        if (isset($person->NameDetails->Name->NameValue->FirstName)) {
            return (string)$person->NameDetails->Name->NameValue->FirstName;
        }
        return null;
    }
    
    private function extractMiddleName($person) {
        if (isset($person->NameDetails->Name->NameValue->MiddleName)) {
            return (string)$person->NameDetails->Name->NameValue->MiddleName;
        }
        return null;
    }
    
    private function extractSurname($person) {
        if (isset($person->NameDetails->Name->NameValue->Surname)) {
            return (string)$person->NameDetails->Name->NameValue->Surname;
        }
        return null;
    }
    
    private function extractSingleStringName($person) {
        if (isset($person->NameDetails->Name->NameValue->SingleStringName)) {
            return (string)$person->NameDetails->Name->NameValue->SingleStringName;
        }
        return null;
    }
    
    private function extractTitle($person) {
        if (isset($person->NameDetails->Name->NameValue->Title)) {
            return (string)$person->NameDetails->Name->NameValue->Title;
        }
        return 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) {
                        // Convert month name to number
                        $monthNum = date('m', strtotime($month . ' 1'));
                        try {
                            $dateStr = sprintf('%04d-%02d-%02d', $year, $monthNum, $day);
                            return $dateStr;
                        } 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) {
        // Extract from record type or role
        if (isset($person->RoleDetail->Roles->OccTitle)) {
            $occCat = (string)$person->RoleDetail->Roles->OccTitle['OccCat'];
            if ($occCat) {
                return 'Category-' . $occCat;
            }
        }
        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 extractProgram($person) {
        // Extract from sanctions or program references
        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'];
            if (isset($this->countryMap[$code])) {
                return $this->countryMap[$code];
            }
        }
        return null;
    }
    
    private function extractSanctionsReferences($person) {
        // This would require additional logic to match records with sanctions
        // For now, returning null as the structure doesn't directly link them
        return null;
    }
}

// Main execution
try {
    echo "=== PFA XML to MySQL Importer ===\n\n";
    
    // Check if XML file exists
    if (!file_exists(XML_FILE_PATH)) {
        die("Error: XML file not found at: " . XML_FILE_PATH . "\n");
    }
    
    // Create importer instance
    $importer = new PFAImporter(XML_FILE_PATH);
    
    // Create table
    $importer->createTable();
    
    // Import data
    $importer->import();
    
} catch (Exception $e) {
    echo "Fatal error: " . $e->getMessage() . "\n";
    exit(1);
}
