<?php
/**
 * Database Migration Script
 * Copy data from source database to destination database
 * 
 * Usage: Run this script via web browser or command line
 * Make sure to configure source and destination database settings below
 */

// Prevent direct access without proper authentication
session_start();
if (!isset($_SESSION['clientId']) && !isset($_GET['allow_migration'])) {
    die('Access denied. Please login first or add ?allow_migration=1 to URL for testing.');
}

// Configuration
$config = [
    'source' => [
        'host' => 'localhost',
        'database' => 'source_database_name',  // Change this to your source database
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8mb4'
    ],
    'destination' => [
        'host' => 'localhost',
        'database' => 'rxcrm',  // Current database from connect.php
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8mb4'
    ]
];

// Tables to migrate (add/remove tables as needed)
$tablesToMigrate = [
    'tblpatient',
    'tblpatientdetails',
    'tblwcpart1',
    'tblreimbursement',
    'tblpatientstatus',
    // Add more tables as needed
];

// Migration options
$options = [
    'truncate_destination' => false,  // Set to true to clear destination tables before copying
    'batch_size' => 1000,            // Number of records to process at once
    'ignore_duplicates' => true,     // Skip duplicate records based on primary key
    'log_progress' => true           // Show progress during migration
];

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Migration Tool</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 1200px; margin: 0 auto; padding: 20px; background: #f5f5f5; }
        .container { background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        .header { text-align: center; margin-bottom: 30px; }
        .config-section { background: #f8f9fa; padding: 20px; border-radius: 8px; margin-bottom: 20px; }
        .button { background: #007bff; color: white; padding: 12px 24px; border: none; border-radius: 5px; cursor: pointer; font-size: 16px; margin: 5px; }
        .button:hover { background: #0056b3; }
        .button.danger { background: #dc3545; }
        .button.danger:hover { background: #c82333; }
        .button.success { background: #28a745; }
        .button.success:hover { background: #218838; }
        .log { background: #f8f9fa; border: 1px solid #dee2e6; padding: 15px; border-radius: 5px; height: 400px; overflow-y: auto; font-family: monospace; font-size: 14px; }
        .progress { width: 100%; height: 20px; background: #e9ecef; border-radius: 10px; overflow: hidden; margin: 10px 0; }
        .progress-bar { height: 100%; background: #007bff; transition: width 0.3s ease; }
        .status { padding: 10px; border-radius: 5px; margin: 10px 0; }
        .status.success { background: #d4edda; color: #155724; border: 1px solid #c3e6cb; }
        .status.error { background: #f8d7da; color: #721c24; border: 1px solid #f5c6cb; }
        .status.warning { background: #fff3cd; color: #856404; border: 1px solid #ffeaa7; }
        .table-list { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 10px; margin: 15px 0; }
        .table-item { background: #e9ecef; padding: 8px 12px; border-radius: 5px; text-align: center; }
        .hidden { display: none; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>🔄 Database Migration Tool</h1>
            <p>Copy data from source database to destination database</p>
        </div>

        <div class="config-section">
            <h3>📋 Configuration</h3>
            <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 20px;">
                <div>
                    <h4>Source Database</h4>
                    <p><strong>Host:</strong> <?= htmlspecialchars($config['source']['host']) ?></p>
                    <p><strong>Database:</strong> <?= htmlspecialchars($config['source']['database']) ?></p>
                    <p><strong>Username:</strong> <?= htmlspecialchars($config['source']['username']) ?></p>
                </div>
                <div>
                    <h4>Destination Database</h4>
                    <p><strong>Host:</strong> <?= htmlspecialchars($config['destination']['host']) ?></p>
                    <p><strong>Database:</strong> <?= htmlspecialchars($config['destination']['database']) ?></p>
                    <p><strong>Username:</strong> <?= htmlspecialchars($config['destination']['username']) ?></p>
                </div>
            </div>
            
            <h4>Tables to Migrate</h4>
            <div class="table-list">
                <?php foreach ($tablesToMigrate as $table): ?>
                    <div class="table-item"><?= htmlspecialchars($table) ?></div>
                <?php endforeach; ?>
            </div>
        </div>

        <div style="text-align: center; margin: 20px 0;">
            <button class="button" onclick="testConnections()">🔍 Test Connections</button>
            <button class="button success" onclick="startMigration()">🚀 Start Migration</button>
            <button class="button" onclick="validateData()">✅ Validate Data</button>
            <button class="button danger" onclick="clearLog()">🗑️ Clear Log</button>
        </div>

        <div id="progress-section" class="hidden">
            <h3>Migration Progress</h3>
            <div class="progress">
                <div class="progress-bar" id="progress-bar" style="width: 0%"></div>
            </div>
            <p id="progress-text">Ready to start...</p>
        </div>

        <div id="status-section"></div>

        <h3>📝 Migration Log</h3>
        <div id="log" class="log">
            <div style="color: #6c757d;">Migration log will appear here...</div>
        </div>
    </div>

    <script>
        function log(message, type = 'info') {
            const logDiv = document.getElementById('log');
            const timestamp = new Date().toLocaleTimeString();
            const colors = {
                'info': '#333',
                'success': '#28a745',
                'error': '#dc3545',
                'warning': '#ffc107'
            };
            
            logDiv.innerHTML += `<div style="color: ${colors[type]}; margin: 2px 0;">[${timestamp}] ${message}</div>`;
            logDiv.scrollTop = logDiv.scrollHeight;
        }

        function clearLog() {
            document.getElementById('log').innerHTML = '<div style="color: #6c757d;">Log cleared...</div>';
        }

        function showStatus(message, type) {
            const statusDiv = document.getElementById('status-section');
            statusDiv.innerHTML = `<div class="status ${type}">${message}</div>`;
        }

        function updateProgress(percent, text) {
            document.getElementById('progress-section').classList.remove('hidden');
            document.getElementById('progress-bar').style.width = percent + '%';
            document.getElementById('progress-text').textContent = text;
        }

        function testConnections() {
            log('Testing database connections...', 'info');
            
            fetch('migrate.php?action=test_connections', {
                method: 'POST',
                headers: {'Content-Type': 'application/json'}
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    log('✅ Connection test successful', 'success');
                    showStatus('Both database connections are working properly!', 'success');
                } else {
                    log('❌ Connection test failed: ' + data.error, 'error');
                    showStatus('Connection test failed: ' + data.error, 'error');
                }
            })
            .catch(error => {
                log('❌ Connection test error: ' + error, 'error');
                showStatus('Connection test error: ' + error, 'error');
            });
        }

        function startMigration() {
            if (!confirm('Are you sure you want to start the migration? This process may take several minutes.')) {
                return;
            }

            log('🚀 Starting migration process...', 'info');
            updateProgress(0, 'Initializing migration...');
            
            fetch('migrate.php?action=migrate', {
                method: 'POST',
                headers: {'Content-Type': 'application/json'}
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    log('✅ Migration completed successfully!', 'success');
                    updateProgress(100, 'Migration completed!');
                    showStatus(`Migration completed! ${data.total_records} records migrated across ${data.tables_migrated} tables.`, 'success');
                } else {
                    log('❌ Migration failed: ' + data.error, 'error');
                    showStatus('Migration failed: ' + data.error, 'error');
                }
            })
            .catch(error => {
                log('❌ Migration error: ' + error, 'error');
                showStatus('Migration error: ' + error, 'error');
            });
        }

        function validateData() {
            log('🔍 Validating migrated data...', 'info');
            
            fetch('migrate.php?action=validate', {
                method: 'POST',
                headers: {'Content-Type': 'application/json'}
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    log('✅ Data validation completed', 'success');
                    showStatus('Data validation completed. Check log for details.', 'success');
                } else {
                    log('❌ Data validation failed: ' + data.error, 'error');
                    showStatus('Data validation failed: ' + data.error, 'error');
                }
            })
            .catch(error => {
                log('❌ Validation error: ' + error, 'error');
                showStatus('Validation error: ' + error, 'error');
            });
        }
    </script>
</body>
</html>

<?php
// Handle AJAX requests
if (isset($_GET['action'])) {
    header('Content-Type: application/json');
    
    try {
        switch ($_GET['action']) {
            case 'test_connections':
                echo json_encode(testDatabaseConnections($config));
                break;
                
            case 'migrate':
                echo json_encode(performMigration($config, $tablesToMigrate, $options));
                break;
                
            case 'validate':
                echo json_encode(validateMigration($config, $tablesToMigrate));
                break;
                
            default:
                echo json_encode(['success' => false, 'error' => 'Invalid action']);
        }
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'error' => $e->getMessage()]);
    }
    exit;
}

/**
 * Test database connections
 */
function testDatabaseConnections($config) {
    try {
        // Test source connection
        $sourcePdo = new PDO(
            "mysql:host={$config['source']['host']};dbname={$config['source']['database']};charset={$config['source']['charset']}",
            $config['source']['username'],
            $config['source']['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        // Test destination connection
        $destPdo = new PDO(
            "mysql:host={$config['destination']['host']};dbname={$config['destination']['database']};charset={$config['destination']['charset']}",
            $config['destination']['username'],
            $config['destination']['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        return ['success' => true, 'message' => 'Both connections successful'];
        
    } catch (PDOException $e) {
        return ['success' => false, 'error' => $e->getMessage()];
    }
}

/**
 * Perform the actual migration
 */
function performMigration($config, $tablesToMigrate, $options) {
    try {
        // Connect to both databases
        $sourcePdo = new PDO(
            "mysql:host={$config['source']['host']};dbname={$config['source']['database']};charset={$config['source']['charset']}",
            $config['source']['username'],
            $config['source']['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        $destPdo = new PDO(
            "mysql:host={$config['destination']['host']};dbname={$config['destination']['database']};charset={$config['destination']['charset']}",
            $config['destination']['username'],
            $config['destination']['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        $totalRecords = 0;
        $tablesMigrated = 0;
        
        foreach ($tablesToMigrate as $table) {
            // Check if table exists in source
            $checkTable = $sourcePdo->query("SHOW TABLES LIKE '$table'");
            if ($checkTable->rowCount() == 0) {
                continue; // Skip if table doesn't exist
            }
            
            // Get table structure
            $columns = getTableColumns($sourcePdo, $table);
            $primaryKey = getPrimaryKey($sourcePdo, $table);
            
            // Truncate destination table if option is set
            if ($options['truncate_destination']) {
                $destPdo->exec("TRUNCATE TABLE `$table`");
            }
            
            // Get total records for progress tracking
            $countStmt = $sourcePdo->query("SELECT COUNT(*) FROM `$table`");
            $totalTableRecords = $countStmt->fetchColumn();
            
            if ($totalTableRecords == 0) {
                continue; // Skip empty tables
            }
            
            // Migrate data in batches
            $offset = 0;
            $recordsMigrated = 0;
            
            while ($offset < $totalTableRecords) {
                $stmt = $sourcePdo->query("SELECT * FROM `$table` LIMIT {$options['batch_size']} OFFSET $offset");
                $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                if (empty($records)) {
                    break;
                }
                
                // Insert records into destination
                foreach ($records as $record) {
                    $recordsMigrated += insertRecord($destPdo, $table, $record, $primaryKey, $options['ignore_duplicates']);
                }
                
                $offset += $options['batch_size'];
            }
            
            $totalRecords += $recordsMigrated;
            $tablesMigrated++;
        }
        
        return [
            'success' => true,
            'total_records' => $totalRecords,
            'tables_migrated' => $tablesMigrated
        ];
        
    } catch (Exception $e) {
        return ['success' => false, 'error' => $e->getMessage()];
    }
}

/**
 * Get table columns
 */
function getTableColumns($pdo, $table) {
    $stmt = $pdo->query("DESCRIBE `$table`");
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

/**
 * Get primary key column
 */
function getPrimaryKey($pdo, $table) {
    $stmt = $pdo->query("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY'");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result ? $result['Column_name'] : null;
}

/**
 * Insert a single record
 */
function insertRecord($pdo, $table, $record, $primaryKey, $ignoreDuplicates) {
    try {
        $columns = array_keys($record);
        $placeholders = ':' . implode(', :', $columns);
        $columnsList = '`' . implode('`, `', $columns) . '`';
        
        $sql = "INSERT INTO `$table` ($columnsList) VALUES ($placeholders)";
        
        if ($ignoreDuplicates && $primaryKey) {
            $sql .= " ON DUPLICATE KEY UPDATE `$primaryKey` = `$primaryKey`";
        }
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($record);
        
        return 1; // Record inserted
        
    } catch (PDOException $e) {
        if ($ignoreDuplicates && $e->getCode() == 23000) {
            return 0; // Duplicate ignored
        }
        throw $e;
    }
}

/**
 * Validate migration
 */
function validateMigration($config, $tablesToMigrate) {
    try {
        // Connect to both databases
        $sourcePdo = new PDO(
            "mysql:host={$config['source']['host']};dbname={$config['source']['database']};charset={$config['source']['charset']}",
            $config['source']['username'],
            $config['source']['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        $destPdo = new PDO(
            "mysql:host={$config['destination']['host']};dbname={$config['destination']['database']};charset={$config['destination']['charset']}",
            $config['destination']['username'],
            $config['destination']['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        $validation = [];
        
        foreach ($tablesToMigrate as $table) {
            // Count records in both databases
            $sourceCount = $sourcePdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
            $destCount = $destPdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
            
            $validation[$table] = [
                'source_count' => $sourceCount,
                'destination_count' => $destCount,
                'match' => $sourceCount == $destCount
            ];
        }
        
        return ['success' => true, 'validation' => $validation];
        
    } catch (Exception $e) {
        return ['success' => false, 'error' => $e->getMessage()];
    }
}
?>