<?php
/**
 * Sinkronisasi SIPP dengan Enhanced Error Handling
 */

require 'auth.php';
require_login();

set_time_limit(0); // No time limit
ini_set('memory_limit', '512M'); // Increase memory limit

// Konfigurasi database SIPP
$sipp_config = [
    'host' => '192.168.1.240',
    'dbname' => 'sippbaco',
    'username' => 'pnwtp',
    'password' => 'pnwtp2019',
    'port' => 3306,
    'charset' => 'utf8mb4'
];

$sqlite_path = 'data/sipp_sync.sqlite';

// Function untuk log error
function logError($message) {
    $log_file = 'data/sync_error.log';
    $timestamp = date('Y-m-d H:i:s');
    file_put_contents($log_file, "[$timestamp] $message" . PHP_EOL, FILE_APPEND | LOCK_EX);
}

// Function untuk koneksi SIPP dengan retry
function connectSippWithRetry($config, $maxRetries = 3) {
    $attempt = 0;
    
    while ($attempt < $maxRetries) {
        $attempt++;
        
        try {
            echo "<p>Attempt $attempt of $maxRetries...</p>";
            
            $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['dbname']};charset={$config['charset']}";
            
            $pdo = new PDO($dsn, $config['username'], $config['password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_TIMEOUT => 15, // 15 second timeout
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$config['charset']}"
            ]);
            
            // Test connection with simple query
            $pdo->query("SELECT 1");
            
            echo "<p style='color: green;'>✓ Connected to SIPP on attempt $attempt</p>";
            return $pdo;
            
        } catch (PDOException $e) {
            echo "<p style='color: orange;'>⚠ Attempt $attempt failed: " . $e->getMessage() . "</p>";
            logError("Connection attempt $attempt failed: " . $e->getMessage());
            
            if ($attempt < $maxRetries) {
                echo "<p>Waiting 2 seconds before retry...</p>";
                sleep(2);
            }
        }
    }
    
    throw new Exception("Failed to connect to SIPP after $maxRetries attempts");
}

// Function untuk koneksi SQLite
function connectSQLite($path) {
    try {
        $dir = dirname($path);
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }
        
        $pdo = new PDO("sqlite:$path", null, null, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
        
        return $pdo;
    } catch (PDOException $e) {
        throw new Exception("SQLite Connection Error: " . $e->getMessage());
    }
}

// Function untuk setup tabel SQLite
function setupSQLiteTables($sqlite_pdo) {
    // Arsip table
    $sql = "CREATE TABLE IF NOT EXISTS arsip (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nomor_perkara TEXT NOT NULL,
        nomor_arsip TEXT,
        no_ruang TEXT,
        no_lemari TEXT,
        no_rak TEXT,
        no_berkas TEXT,
        tanggal_sync DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(nomor_perkara, nomor_arsip)
    )";
    $sqlite_pdo->exec($sql);
    
    // Sync log table
    $sql = "CREATE TABLE IF NOT EXISTS sync_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sync_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        total_records INTEGER,
        success_records INTEGER,
        error_records INTEGER,
        sync_duration REAL,
        status TEXT,
        error_message TEXT
    )";
    $sqlite_pdo->exec($sql);
    
    // Indexes
    $sqlite_pdo->exec("CREATE INDEX IF NOT EXISTS idx_nomor_perkara ON arsip(nomor_perkara)");
    $sqlite_pdo->exec("CREATE INDEX IF NOT EXISTS idx_nomor_arsip ON arsip(nomor_arsip)");
}

?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Sinkronisasi Enhanced - Debug Mode</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <style>
        .log-container {
            background: #f8f9fa;
            border: 1px solid #dee2e6;
            border-radius: 5px;
            padding: 15px;
            max-height: 500px;
            overflow-y: auto;
            font-family: monospace;
            font-size: 14px;
        }
        .step {
            margin: 10px 0;
            padding: 10px;
            border-left: 4px solid #007bff;
            background: #f1f3f4;
        }
    </style>
</head>
<body>
    <div class="container mt-4">
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header bg-primary text-white">
                        <h5><i class="fa fa-bug"></i> Enhanced Sync Debug Mode</h5>
                    </div>
                    <div class="card-body">
                        
                        <?php if (isset($_POST['start_sync'])): ?>
                        
                        <div class="log-container">
                            <?php
                            echo "<div class='step'><strong>🚀 Starting Enhanced Sync Process...</strong></div>";
                            
                            $start_time = microtime(true);
                            $total_records = 0;
                            $success_records = 0;
                            $error_records = 0;
                            
                            try {
                                // Step 1: Connect to SQLite
                                echo "<div class='step'><strong>📁 Step 1: Setting up SQLite...</strong></div>";
                                $sqlite_pdo = connectSQLite($sqlite_path);
                                setupSQLiteTables($sqlite_pdo);
                                echo "<p style='color: green;'>✓ SQLite ready</p>";
                                
                                // Step 2: Connect to SIPP
                                echo "<div class='step'><strong>🔌 Step 2: Connecting to SIPP...</strong></div>";
                                $sipp_pdo = connectSippWithRetry($sipp_config, 5);
                                
                                // Step 3: Get data count
                                echo "<div class='step'><strong>📊 Step 3: Analyzing SIPP data...</strong></div>";
                                $count_stmt = $sipp_pdo->query("SELECT COUNT(*) as total FROM arsip WHERE nomor_perkara IS NOT NULL AND nomor_perkara != ''");
                                $count_result = $count_stmt->fetch();
                                $total_records = $count_result['total'];
                                echo "<p>Total records to sync: " . number_format($total_records) . "</p>";
                                
                                if ($total_records == 0) {
                                    throw new Exception("No records found in SIPP arsip table");
                                }
                                
                                // Step 4: Clear old data
                                echo "<div class='step'><strong>🗑️ Step 4: Clearing old SQLite data...</strong></div>";
                                $sqlite_pdo->exec("DELETE FROM arsip");
                                echo "<p>✓ Old data cleared</p>";
                                
                                // Step 5: Sync data
                                echo "<div class='step'><strong>🔄 Step 5: Syncing data (this may take a while)...</strong></div>";
                                
                                $sql = "SELECT DISTINCT 
                                            nomor_perkara,
                                            nomor_arsip,
                                            no_ruang,
                                            no_lemari,
                                            no_rak,
                                            no_berkas
                                        FROM arsip 
                                        WHERE nomor_perkara IS NOT NULL 
                                        AND nomor_perkara != ''
                                        ORDER BY nomor_perkara";
                                
                                $stmt = $sipp_pdo->prepare($sql);
                                $stmt->execute();
                                
                                $insert_sql = "INSERT OR REPLACE INTO arsip 
                                              (nomor_perkara, nomor_arsip, no_ruang, no_lemari, no_rak, no_berkas) 
                                              VALUES (?, ?, ?, ?, ?, ?)";
                                $insert_stmt = $sqlite_pdo->prepare($insert_sql);
                                
                                $sqlite_pdo->beginTransaction();
                                
                                $batch_size = 100;
                                $batch_count = 0;
                                
                                while ($row = $stmt->fetch()) {
                                    try {
                                        $insert_stmt->execute([
                                            $row['nomor_perkara'],
                                            $row['nomor_arsip'],
                                            $row['no_ruang'],
                                            $row['no_lemari'],
                                            $row['no_rak'],
                                            $row['no_berkas']
                                        ]);
                                        $success_records++;
                                        
                                        if (++$batch_count >= $batch_size) {
                                            echo "<p>Synced " . number_format($success_records) . " records...</p>";
                                            $batch_count = 0;
                                            flush();
                                            ob_flush();
                                        }
                                        
                                    } catch (PDOException $e) {
                                        $error_records++;
                                        echo "<p style='color: orange;'>⚠ Error on record: " . $e->getMessage() . "</p>";
                                    }
                                }
                                
                                $sqlite_pdo->commit();
                                
                                $end_time = microtime(true);
                                $duration = $end_time - $start_time;
                                
                                // Step 6: Log results
                                echo "<div class='step'><strong>📝 Step 6: Logging results...</strong></div>";
                                $log_sql = "INSERT INTO sync_log 
                                           (total_records, success_records, error_records, sync_duration, status, error_message) 
                                           VALUES (?, ?, ?, ?, ?, ?)";
                                $log_stmt = $sqlite_pdo->prepare($log_sql);
                                $log_stmt->execute([
                                    $total_records,
                                    $success_records,
                                    $error_records,
                                    $duration,
                                    ($error_records == 0) ? 'SUCCESS' : 'PARTIAL_SUCCESS',
                                    ''
                                ]);
                                
                                echo "<div class='step' style='border-color: green;'><strong>🎉 Sync Complete!</strong></div>";
                                echo "<p><strong>Total:</strong> " . number_format($total_records) . "</p>";
                                echo "<p><strong>Success:</strong> " . number_format($success_records) . "</p>";
                                echo "<p><strong>Errors:</strong> " . number_format($error_records) . "</p>";
                                echo "<p><strong>Duration:</strong> " . number_format($duration, 2) . " seconds</p>";
                                
                            } catch (Exception $e) {
                                echo "<div class='step' style='border-color: red;'><strong>❌ Sync Failed!</strong></div>";
                                echo "<p style='color: red;'><strong>Error:</strong> " . $e->getMessage() . "</p>";
                                
                                logError("Sync failed: " . $e->getMessage());
                                
                                // Log failure
                                if (isset($sqlite_pdo)) {
                                    try {
                                        $end_time = microtime(true);
                                        $duration = $end_time - $start_time;
                                        
                                        $log_sql = "INSERT INTO sync_log 
                                                   (total_records, success_records, error_records, sync_duration, status, error_message) 
                                                   VALUES (?, ?, ?, ?, ?, ?)";
                                        $log_stmt = $sqlite_pdo->prepare($log_sql);
                                        $log_stmt->execute([
                                            $total_records,
                                            $success_records,
                                            $error_records,
                                            $duration,
                                            'FAILED',
                                            $e->getMessage()
                                        ]);
                                    } catch (Exception $log_error) {
                                        echo "<p style='color: orange;'>⚠ Failed to log error: " . $log_error->getMessage() . "</p>";
                                    }
                                }
                            }
                            ?>
                        </div>
                        
                        <div class="mt-3">
                            <a href="sync-sipp-to-sqlite.php" class="btn btn-primary">Back to Normal Sync</a>
                            <a href="test-sqlite-arsip.php" class="btn btn-success">Test SQLite Data</a>
                        </div>
                        
                        <?php else: ?>
                        
                        <div class="alert alert-warning">
                            <h6><i class="fa fa-exclamation-triangle"></i> Debug Mode</h6>
                            <p>This enhanced sync mode provides detailed logging and error handling.</p>
                            <p><strong>Use this when:</strong> Normal sync fails and you need detailed diagnostics.</p>
                        </div>
                        
                        <form method="POST">
                            <button type="submit" name="start_sync" class="btn btn-warning btn-lg">
                                <i class="fa fa-play"></i> Start Enhanced Sync (Debug Mode)
                            </button>
                        </form>
                        
                        <?php endif; ?>
                        
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
