<?php
/**
 * LUPA to AIMS Transfer - FIXED WEB VERSION
 * No external dependencies - everything in one file
 * 
 * Place in: C:\Server\xampp\htdocs\aims\transfer.php
 * Access: http://localhost/aims/transfer.php
 */

set_time_limit(300);
ini_set('memory_limit', '256M');

// Database Configuration
$LUPA = ['host' => 'localhost', 'user' => 'coreelec_lupa', 'pass' => 'Apploads@93', 'db' => 'coreelec_lupa_db'];
$AIMS = ['host' => 'localhost', 'user' => 'root', 'pass' => '', 'db' => 'aims_db'];
?>
<!DOCTYPE html>
<html>
<head>
    <title>LUPA → AIMS Transfer</title>
    <meta charset="UTF-8">
    <style>
        body { font-family: Arial; max-width: 900px; margin: 30px auto; padding: 20px; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); }
        .box { background: white; border-radius: 12px; padding: 30px; box-shadow: 0 10px 40px rgba(0,0,0,0.3); }
        h1 { color: #667eea; border-bottom: 3px solid #667eea; padding-bottom: 15px; }
        .config { background: #f8f9fa; padding: 15px; margin: 15px 0; border-radius: 8px; border-left: 4px solid #667eea; }
        .btn { display: inline-block; padding: 12px 25px; margin: 10px 5px; border: none; border-radius: 6px; cursor: pointer; font-size: 15px; font-weight: 600; text-decoration: none; color: white; }
        .btn-primary { background: #667eea; }
        .btn-success { background: #28a745; }
        .btn-secondary { background: #6c757d; }
        .btn:hover { opacity: 0.9; transform: translateY(-1px); }
        .alert { padding: 15px; margin: 15px 0; border-radius: 8px; border-left: 4px solid; }
        .alert-info { background: #e3f2fd; border-color: #2196F3; color: #1976d2; }
        .alert-warning { background: #fff3e0; border-color: #ff9800; color: #f57c00; }
        .output { background: #1e1e1e; color: #0f0; padding: 20px; border-radius: 8px; font-family: monospace; max-height: 500px; overflow-y: auto; margin: 20px 0; }
        .success { color: #0f0; }
        .error { color: #f00; }
        .info { color: #0af; }
        .progress { background: #e0e0e0; height: 30px; border-radius: 15px; overflow: hidden; margin: 20px 0; }
        .progress-bar { background: linear-gradient(90deg, #667eea, #764ba2); height: 100%; text-align: center; line-height: 30px; color: white; font-weight: bold; transition: width 0.3s; }
    </style>
</head>
<body>
    <div class="box">
        <h1>🔄 LUPA → AIMS Location Transfer</h1>
        
        <?php if (!isset($_GET['action'])): ?>
            <div class="config">
                <strong>Source:</strong> <?= $LUPA['db'] ?> (<?= $LUPA['user'] ?>)
            </div>
            <div class="config">
                <strong>Target:</strong> <?= $AIMS['db'] ?> (<?= $AIMS['user'] ?>)
            </div>
            <div class="alert alert-info">
                <strong>📊 Will Transfer:</strong>
                <ul style="margin: 10px 0 0 20px;">
                    <li>5 Regions</li>
                    <li>146 Districts</li>
                    <li>Subcounties, Parishes, Villages</li>
                </ul>
            </div>
            <div class="alert alert-warning">
                <strong>⚠️ Important:</strong> This will modify AIMS database. Backup recommended!
            </div>
            <div style="text-align: center;">
                <a href="?action=test" class="btn btn-secondary">🧪 Test</a>
                <a href="?action=preview" class="btn btn-primary">🔍 Preview</a>
                <a href="?action=transfer" class="btn btn-success" onclick="return confirm('Start transfer?')">▶️ Transfer</a>
            </div>
        <?php else: 
            $action = $_GET['action'];
        ?>
            <div class="progress">
                <div class="progress-bar" id="bar" style="width: 0%">0%</div>
            </div>
            <div class="output"><?php
// ==================================================
// TRANSFER LOGIC
// ==================================================

try {
    echo "[" . date('H:i:s') . "] Starting {$action}...\n";
    flush();
    
    // Connect to LUPA
    echo "[" . date('H:i:s') . "] Connecting to LUPA...\n";
    flush();
    $lupa = new mysqli($LUPA['host'], $LUPA['user'], $LUPA['pass'], $LUPA['db']);
    if ($lupa->connect_error) throw new Exception("LUPA: " . $lupa->connect_error);
    $lupa->set_charset('utf8mb4');
    echo "<span class='success'>[" . date('H:i:s') . "] ✓ LUPA Connected</span>\n";
    flush();
    
    // Connect to AIMS
    echo "[" . date('H:i:s') . "] Connecting to AIMS...\n";
    flush();
    $aims = new mysqli($AIMS['host'], $AIMS['user'], $AIMS['pass'], $AIMS['db']);
    if ($aims->connect_error) throw new Exception("AIMS: " . $aims->connect_error);
    $aims->set_charset('utf8mb4');
    echo "<span class='success'>[" . date('H:i:s') . "] ✓ AIMS Connected</span>\n\n";
    echo "<script>document.getElementById('bar').style.width='15%';document.getElementById('bar').textContent='15%';</script>\n";
    flush();
    
    if ($action === 'test') {
        // Just test connections
        $r = $lupa->query("SELECT COUNT(*) as c FROM districts");
        echo "<span class='info'>LUPA: " . $r->fetch_assoc()['c'] . " districts</span>\n";
        
        $r = $aims->query("SELECT COUNT(*) as c FROM regions");
        echo "<span class='info'>AIMS: " . $r->fetch_assoc()['c'] . " regions</span>\n\n";
        
        echo "<span class='success'>✓ Test PASSED - Ready to transfer!</span>\n";
        echo "<script>document.getElementById('bar').style.width='100%';document.getElementById('bar').textContent='Done';</script>\n";
        
    } else if ($action === 'preview') {
        // Show what would be transferred
        echo "=== PREVIEW (No changes will be made) ===\n\n";
        
        $r = $lupa->query("SELECT COUNT(*) as c FROM districts");
        echo "Districts: " . $r->fetch_assoc()['c'] . "\n";
        
        $r = $lupa->query("SELECT COUNT(*) as c FROM subcounties");
        echo "Subcounties: " . $r->fetch_assoc()['c'] . "\n";
        
        $r = $lupa->query("SELECT COUNT(*) as c FROM parishes");
        echo "Parishes: " . $r->fetch_assoc()['c'] . "\n";
        
        $r = $lupa->query("SELECT COUNT(*) as c FROM villages");
        echo "Villages: " . $r->fetch_assoc()['c'] . "\n\n";
        
        echo "<span class='info'>This is preview only. Click 'Transfer' to actually transfer.</span>\n";
        echo "<script>document.getElementById('bar').style.width='100%';document.getElementById('bar').textContent='Done';</script>\n";
        
    } else if ($action === 'transfer') {
        // Actually transfer data
        echo "=== STARTING TRANSFER ===\n\n";
        
        $aims->begin_transaction();
        echo "Transaction started\n\n";
        
        // District to Region mapping
        $map = [
            'LUUKA'=>'BUS','IGANGA'=>'BUS','JINJA'=>'BUS','JINJA CITY'=>'BUS','BUGIRI'=>'BUS',
            'BUGWERI'=>'BUS','MAYUGE'=>'BUS','KAMULI'=>'BUS','BUYENDE'=>'BUS','KALIRO'=>'BUS','NAMUTUMBA'=>'BUS',
            'KAMPALA'=>'CEN','WAKISO'=>'CEN','MUKONO'=>'CEN','MPIGI'=>'CEN','BUIKWE'=>'CEN','MASAKA'=>'CEN',
            'MBARARA'=>'WST','KASESE'=>'WST','KABALE'=>'WST','FORT PORTAL'=>'WST','FORT PORTAL CITY'=>'WST',
            'MBALE'=>'EST','SOROTI'=>'EST','TORORO'=>'EST','KUMI'=>'EST','BUSIA'=>'EST',
            'GULU'=>'NTH','LIRA'=>'NTH','ARUA'=>'NTH','KITGUM'=>'NTH'
        ];
        
        // Transfer Regions
        echo "Step 1: Regions...\n";
        $regions = [['Busoga','BUS'],['Central','CEN'],['Western','WST'],['Eastern','EST'],['Northern','NTH']];
        $reg_ids = [];
        foreach ($regions as $r) {
            $s = $aims->prepare("INSERT INTO regions (region_name,region_code) VALUES (?,?) ON DUPLICATE KEY UPDATE region_name=VALUES(region_name)");
            $s->bind_param("ss", $r[0], $r[1]);
            $s->execute();
            $res = $aims->query("SELECT id FROM regions WHERE region_code='{$r[1]}'");
            $reg_ids[$r[1]] = $res->fetch_assoc()['id'];
            echo "  ✓ {$r[0]}\n";
        }
        echo "<span class='success'>✓ 5 regions</span>\n\n";
        echo "<script>document.getElementById('bar').style.width='30%';document.getElementById('bar').textContent='30%';</script>\n";
        flush();
        
        // Transfer Districts
        echo "Step 2: Districts...\n";
        $r = $lupa->query("SELECT * FROM districts ORDER BY name");
        $cnt = 0;
        $dist_map = [];
        while ($row = $r->fetch_assoc()) {
            $name = trim($row['name']);
            $upper = strtoupper($name);
            $reg_code = $map[$upper] ?? 'CEN';
            
            if (isset($reg_ids[$reg_code])) {
                $reg_id = $reg_ids[$reg_code];
                $code = strtoupper(substr($name, 0, 3));
                
                $s = $aims->prepare("INSERT INTO districts (region_id,district_name,district_code) VALUES (?,?,?) ON DUPLICATE KEY UPDATE district_name=VALUES(district_name)");
                $s->bind_param("iss", $reg_id, $name, $code);
                $s->execute();
                
                if ($s->insert_id) {
                    $dist_map[$row['id']] = $s->insert_id;
                } else {
                    $r2 = $aims->query("SELECT id FROM districts WHERE district_name='$name'");
                    if ($row2 = $r2->fetch_assoc()) {
                        $dist_map[$row['id']] = $row2['id'];
                    }
                }
                
                $cnt++;
                if ($cnt % 30 == 0) {
                    echo "  {$cnt} districts...\n";
                    $p = 30 + (($cnt / 150) * 40);
                    echo "<script>document.getElementById('bar').style.width='{$p}%';document.getElementById('bar').textContent=Math.round({$p})+'%';</script>\n";
                    flush();
                }
            }
        }
        echo "<span class='success'>✓ {$cnt} districts</span>\n\n";
        echo "<script>document.getElementById('bar').style.width='70%';document.getElementById('bar').textContent='70%';</script>\n";
        flush();
        
        // Transfer Subcounties
        echo "Step 3: Subcounties...\n";
        $r = $lupa->query("SELECT * FROM subcounties");
        $cnt = 0;
        $sub_map = [];
        while ($row = $r->fetch_assoc()) {
            if (!isset($dist_map[$row['district_id']])) continue;
            
            $dist_id = $dist_map[$row['district_id']];
            $name = trim($row['name']);
            $code = strtoupper(substr($name, 0, 3));
            
            $s = $aims->prepare("INSERT INTO subcounties (district_id,subcounty_name,subcounty_code) VALUES (?,?,?) ON DUPLICATE KEY UPDATE subcounty_name=VALUES(subcounty_name)");
            $s->bind_param("iss", $dist_id, $name, $code);
            $s->execute();
            
            if ($s->insert_id) {
                $sub_map[$row['id']] = $s->insert_id;
            } else {
                $r2 = $aims->query("SELECT id FROM subcounties WHERE district_id=$dist_id AND subcounty_name='$name'");
                if ($row2 = $r2->fetch_assoc()) {
                    $sub_map[$row['id']] = $row2['id'];
                }
            }
            $cnt++;
        }
        echo "<span class='success'>✓ {$cnt} subcounties</span>\n\n";
        echo "<script>document.getElementById('bar').style.width='85%';document.getElementById('bar').textContent='85%';</script>\n";
        flush();
        
        // Transfer Parishes
        echo "Step 4: Parishes...\n";
        $r = $lupa->query("SELECT * FROM parishes");
        $cnt = 0;
        $par_map = [];
        while ($row = $r->fetch_assoc()) {
            if (!isset($sub_map[$row['subcounty_id']])) continue;
            
            $sub_id = $sub_map[$row['subcounty_id']];
            $name = trim($row['name']);
            $code = strtoupper(substr($name, 0, 3));
            
            $s = $aims->prepare("INSERT INTO parishes (subcounty_id,parish_name,parish_code) VALUES (?,?,?) ON DUPLICATE KEY UPDATE parish_name=VALUES(parish_name)");
            $s->bind_param("iss", $sub_id, $name, $code);
            $s->execute();
            
            if ($s->insert_id) {
                $par_map[$row['id']] = $s->insert_id;
            }
            $cnt++;
        }
        echo "<span class='success'>✓ {$cnt} parishes</span>\n\n";
        echo "<script>document.getElementById('bar').style.width='95%';document.getElementById('bar').textContent='95%';</script>\n";
        flush();
        
        // Transfer Villages
        echo "Step 5: Villages...\n";
        $r = $lupa->query("SELECT * FROM villages");
        $cnt = 0;
        while ($row = $r->fetch_assoc()) {
            if (!isset($par_map[$row['parish_id']])) continue;
            
            $par_id = $par_map[$row['parish_id']];
            $name = trim($row['name']);
            $code = strtoupper(substr($name, 0, 3));
            
            $s = $aims->prepare("INSERT INTO villages (parish_id,village_name,village_code) VALUES (?,?,?) ON DUPLICATE KEY UPDATE village_name=VALUES(village_name)");
            $s->bind_param("iss", $par_id, $name, $code);
            $s->execute();
            $cnt++;
        }
        echo "<span class='success'>✓ {$cnt} villages</span>\n\n";
        flush();
        
        // Commit
        $aims->commit();
        echo "<span class='success'>✓ Transaction committed</span>\n\n";
        
        // Summary
        echo "=== SUMMARY ===\n";
        $r = $aims->query("SELECT COUNT(*) as c FROM regions");
        echo "Regions: " . $r->fetch_assoc()['c'] . "\n";
        $r = $aims->query("SELECT COUNT(*) as c FROM districts");
        echo "Districts: " . $r->fetch_assoc()['c'] . "\n";
        $r = $aims->query("SELECT COUNT(*) as c FROM subcounties");
        echo "Subcounties: " . $r->fetch_assoc()['c'] . "\n";
        $r = $aims->query("SELECT COUNT(*) as c FROM parishes");
        echo "Parishes: " . $r->fetch_assoc()['c'] . "\n";
        $r = $aims->query("SELECT COUNT(*) as c FROM villages");
        echo "Villages: " . $r->fetch_assoc()['c'] . "\n\n";
        
        echo "<span class='success'>✓✓✓ TRANSFER COMPLETED! ✓✓✓</span>\n";
        echo "<script>document.getElementById('bar').style.width='100%';document.getElementById('bar').textContent='Complete!';</script>\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'>Rolled back - no changes made</span>\n";
    }
    echo "<script>document.getElementById('bar').style.width='100%';document.getElementById('bar').textContent='Error';document.getElementById('bar').style.background='#f44336';</script>\n";
}
            ?></div>
            <div style="text-align:center; margin-top:20px;">
                <a href="?" class="btn btn-primary">← Back</a>
            </div>
        <?php endif; ?>
    </div>
</body>
</html>