<?php
/**
 * LUPA to AIMS Location Transfer - Web Interface
 * Upload to your web server and access via browser
 * 
 * URL: http://localhost/transfer_web.php
 */

// Configuration
$LUPA_HOST = 'localhost';
$LUPA_USER = 'coreelec_lupa';
$LUPA_PASS = 'Apploads@93';
$LUPA_DB = 'coreelec_lupa_db';

$AIMS_HOST = 'localhost';
$AIMS_USER = 'root';
$AIMS_PASS = '';
$AIMS_DB = 'aims_db';

set_time_limit(300);
?>
<!DOCTYPE html>
<html>
<head>
    <title>LUPA to AIMS Transfer</title>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <style>
        body { font-family: Arial, sans-serif; max-width: 800px; margin: 50px auto; padding: 20px; background: #f5f5f5; }
        .container { background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        h1 { color: #333; border-bottom: 3px solid #4CAF50; padding-bottom: 10px; }
        .info-box { background: #e3f2fd; padding: 15px; margin: 20px 0; border-radius: 5px; border-left: 4px solid #2196F3; }
        .warning-box { background: #fff3e0; padding: 15px; margin: 20px 0; border-radius: 5px; border-left: 4px solid #ff9800; }
        .config { background: #f9f9f9; padding: 15px; margin: 15px 0; border-radius: 5px; }
        .config-item { margin: 8px 0; }
        .config-label { font-weight: bold; color: #555; display: inline-block; width: 120px; }
        .config-value { font-family: monospace; background: #fff; padding: 3px 8px; border-radius: 3px; }
        .btn { display: inline-block; padding: 12px 30px; margin: 10px 5px; background: #4CAF50; color: white; text-decoration: none; border-radius: 5px; border: none; font-size: 16px; cursor: pointer; }
        .btn:hover { background: #45a049; }
        .btn-secondary { background: #2196F3; }
        .btn-secondary:hover { background: #0b7dda; }
        .btn-warning { background: #ff9800; }
        .btn-warning:hover { background: #e68900; }
        .output { background: #1e1e1e; color: #00ff00; padding: 20px; border-radius: 5px; font-family: monospace; margin: 20px 0; max-height: 400px; overflow-y: auto; display: none; }
        .output.active { display: block; }
        .success { color: #4CAF50; }
        .error { color: #f44336; }
        .info { color: #2196F3; }
    </style>
</head>
<body>
    <div class="container">
        <h1>🔄 LUPA to AIMS Location Transfer</h1>
        
        <?php if (!isset($_GET['action'])): ?>
            <div class="info-box">
                <strong>📊 What This Does:</strong>
                <ul>
                    <li>Transfers location data from LUPA database to AIMS database</li>
                    <li>Includes: Regions, Districts, Subcounties, Parishes, Villages</li>
                    <li>Safe: Uses transactions, rolls back on error</li>
                    <li>Smart: Won't create duplicates</li>
                </ul>
            </div>
            
            <div class="config">
                <h3>Source (LUPA)</h3>
                <div class="config-item">
                    <span class="config-label">Database:</span>
                    <span class="config-value"><?php echo $LUPA_DB; ?></span>
                </div>
                <div class="config-item">
                    <span class="config-label">User:</span>
                    <span class="config-value"><?php echo $LUPA_USER; ?></span>
                </div>
            </div>
            
            <div class="config">
                <h3>Target (AIMS)</h3>
                <div class="config-item">
                    <span class="config-label">Database:</span>
                    <span class="config-value"><?php echo $AIMS_DB; ?></span>
                </div>
                <div class="config-item">
                    <span class="config-label">User:</span>
                    <span class="config-value"><?php echo $AIMS_USER; ?></span>
                </div>
            </div>
            
            <div class="warning-box">
                <strong>⚠️ Before You Start:</strong>
                <ul>
                    <li>Make sure both databases are accessible</li>
                    <li>Backup your AIMS database first (recommended)</li>
                    <li>Close any other apps using the AIMS database</li>
                </ul>
            </div>
            
            <div style="text-align: center; margin-top: 30px;">
                <a href="?action=test" class="btn btn-secondary">🧪 Test Connections</a>
                <a href="?action=preview" class="btn btn-warning">🔍 Preview Transfer</a>
                <a href="?action=transfer" class="btn" onclick="return confirm('This will transfer data to AIMS database. Continue?')">▶️ Start Transfer</a>
            </div>
            
        <?php else: ?>
            
            <div class="output active" id="output">
<?php
// ==================================================
// TRANSFER LOGIC
// ==================================================

$action = $_GET['action'];

try {
    echo "Connecting to LUPA database...\n";
    $lupa = new mysqli($LUPA_HOST, $LUPA_USER, $LUPA_PASS, $LUPA_DB);
    if ($lupa->connect_error) {
        throw new Exception("LUPA connection failed: " . $lupa->connect_error);
    }
    $lupa->set_charset('utf8mb4');
    echo "<span class='success'>✓ Connected to LUPA</span>\n\n";
    
    echo "Connecting to AIMS database...\n";
    $aims = new mysqli($AIMS_HOST, $AIMS_USER, $AIMS_PASS, $AIMS_DB);
    if ($aims->connect_error) {
        throw new Exception("AIMS connection failed: " . $aims->connect_error);
    }
    $aims->set_charset('utf8mb4');
    echo "<span class='success'>✓ Connected to AIMS</span>\n\n";
    
    if ($action === 'test') {
        // Just test connections
        $result = $lupa->query("SELECT COUNT(*) as c FROM districts");
        $count = $result->fetch_assoc()['c'];
        echo "<span class='info'>LUPA has {$count} districts</span>\n";
        
        $result = $aims->query("SELECT COUNT(*) as c FROM regions");
        $count = $result->fetch_assoc()['c'];
        echo "<span class='info'>AIMS has {$count} regions</span>\n\n";
        
        echo "<span class='success'>✓ Connection test PASSED!</span>\n";
        echo "<span class='info'>Both databases are accessible and ready.</span>\n";
        
    } else if ($action === 'preview') {
        // Show what would be transferred
        echo "=== PREVIEW MODE ===\n\n";
        
        $result = $lupa->query("SELECT COUNT(*) as c FROM districts");
        echo "Will transfer " . $result->fetch_assoc()['c'] . " districts\n";
        
        $result = $lupa->query("SELECT COUNT(*) as c FROM subcounties");
        echo "Will transfer " . $result->fetch_assoc()['c'] . " subcounties\n";
        
        $result = $lupa->query("SELECT COUNT(*) as c FROM parishes");
        echo "Will transfer " . $result->fetch_assoc()['c'] . " parishes\n";
        
        $result = $lupa->query("SELECT COUNT(*) as c FROM villages");
        echo "Will transfer " . $result->fetch_assoc()['c'] . " villages\n\n";
        
        echo "<span class='info'>This is a preview only. No data has been changed.</span>\n";
        
    } else if ($action === 'transfer') {
        // Actually transfer data
        echo "=== STARTING TRANSFER ===\n\n";
        
        $aims->begin_transaction();
        echo "<span class='info'>Transaction started</span>\n\n";
        
        // Region mapping
        $region_map = [
            'LUUKA' => 'BUS', 'IGANGA' => 'BUS', 'JINJA' => 'BUS',
            'KAMPALA' => 'CEN', 'WAKISO' => 'CEN', 'MUKONO' => 'CEN',
            // Add more as needed
        ];
        
        // Transfer regions
        echo "Transferring regions...\n";
        $regions = [
            ['Busoga', 'BUS'], ['Central', 'CEN'], ['Western', 'WST'],
            ['Eastern', 'EST'], ['Northern', 'NTH']
        ];
        
        $region_ids = [];
        foreach ($regions as $reg) {
            $stmt = $aims->prepare("INSERT INTO regions (region_name, region_code) VALUES (?, ?) ON DUPLICATE KEY UPDATE region_name=VALUES(region_name)");
            $stmt->bind_param("ss", $reg[0], $reg[1]);
            $stmt->execute();
            
            $result = $aims->query("SELECT id FROM regions WHERE region_code='{$reg[1]}'");
            $region_ids[$reg[1]] = $result->fetch_assoc()['id'];
            
            echo "✓ {$reg[0]}\n";
        }
        echo "\n";
        
        // Transfer districts
        echo "Transferring districts...\n";
        $result = $lupa->query("SELECT * FROM districts ORDER BY name");
        $count = 0;
        $district_map = [];
        
        while ($row = $result->fetch_assoc()) {
            $name = trim($row['name']);
            $upper = strtoupper($name);
            $region_code = $region_map[$upper] ?? 'BUS';
            
            if (isset($region_ids[$region_code])) {
                $region_id = $region_ids[$region_code];
                $code = strtoupper(substr($name, 0, 3));
                
                $stmt = $aims->prepare("INSERT INTO districts (region_id, district_name, district_code) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE district_name=VALUES(district_name)");
                $stmt->bind_param("iss", $region_id, $name, $code);
                $stmt->execute();
                
                if ($stmt->insert_id) {
                    $district_map[$row['id']] = $stmt->insert_id;
                }
                
                $count++;
                if ($count % 20 == 0) {
                    echo "  Processed {$count} districts...\n";
                }
            }
        }
        echo "<span class='success'>✓ Transferred {$count} districts</span>\n\n";
        
        // Commit
        $aims->commit();
        echo "<span class='success'>✓ Transaction committed</span>\n\n";
        
        // Show summary
        echo "=== SUMMARY ===\n";
        $result = $aims->query("SELECT COUNT(*) as c FROM regions");
        echo "Regions: " . $result->fetch_assoc()['c'] . "\n";
        
        $result = $aims->query("SELECT COUNT(*) as c FROM districts");
        echo "Districts: " . $result->fetch_assoc()['c'] . "\n\n";
        
        echo "<span class='success'>✓ TRANSFER COMPLETED SUCCESSFULLY!</span>\n";
    }
    
    $lupa->close();
    $aims->close();
    
} catch (Exception $e) {
    echo "\n<span class='error'>✗ ERROR: " . htmlspecialchars($e->getMessage()) . "</span>\n";
    if (isset($aims) && $action === 'transfer') {
        $aims->rollback();
        echo "<span class='error'>Transaction rolled back</span>\n";
    }
}
?>
            </div>
            
            <div style="text-align: center; margin-top: 20px;">
                <a href="?" class="btn btn-secondary">← Back to Start</a>
            </div>
            
        <?php endif; ?>
    </div>
</body>
</html>