<?php
/**
 * Sinkronisasi Data SIPP ke SQLite
 * Script untuk menyinkronkan data arsip dari server SIPP (192.168.1.240) ke database SQLite lokal
 */

require 'auth.php';
require_login();

set_time_limit(300); // 5 menit timeout
ini_set('memory_limit', '256M'); // Increase memory limit

// Konfigurasi database SIPP (sama persis dengan arsip.php)
$sipp_config = [
    'host' => '192.168.1.240',
    'dbname' => 'sippbaco',
    'username' => 'pnwtp',
    'password' => 'pnwtp2019',
    'port' => 3306
];

// Path database SQLite lokal
$sqlite_path = 'data/sipp_sync.sqlite';

/**
 * Koneksi ke database SIPP (menggunakan metode yang sama dengan arsip.php)
 */
function connectSipp($config) {
    try {
        $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['dbname']};charset=utf8mb4";
        $pdo = new PDO($dsn, $config['username'], $config['password']);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    } catch (PDOException $e) {
        throw new Exception("SIPP Connection Error: " . $e->getMessage());
    }
}

/**
 * Koneksi ke database SQLite
 */
function connectSQLite($path) {
    try {
        // Buat direktori jika belum ada
        $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());
    }
}

/**
 * Buat tabel arsip di SQLite
 */
function createArsipTable($sqlite_pdo) {
    $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);
    
    // Buat index untuk performa
    $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)");
}

/**
 * Buat tabel log sinkronisasi
 */
function createSyncLogTable($sqlite_pdo) {
    $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);
}

/**
 * Sinkronisasi data arsip
 */
function syncArsipData($sipp_pdo, $sqlite_pdo) {
    $start_time = microtime(true);
    $total_records = 0;
    $success_records = 0;
    $error_records = 0;
    $error_message = '';
    
    try {
        // Hapus data lama (opsional - bisa diatur untuk incremental sync)
        $sqlite_pdo->exec("DELETE FROM arsip");
        
        // Ambil data dari SIPP (menggunakan query yang sama dengan arsip.php)
        // Gunakan LIMIT untuk menghindari timeout pada data besar
        $sql = "SELECT * FROM arsip ORDER BY nomor_perkara LIMIT 1000";
        
        $stmt = $sipp_pdo->prepare($sql);
        $stmt->execute();
        
        // Prepare insert statement untuk SQLite
        $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);
        
        // Begin transaction untuk performa
        $sqlite_pdo->beginTransaction();
        
        while ($row = $stmt->fetch()) {
            $total_records++;
            
            try {
                $insert_stmt->execute([
                    $row['nomor_perkara'] ?? '',
                    $row['nomor_arsip'] ?? '',
                    $row['no_ruang'] ?? '',
                    $row['no_lemari'] ?? '',
                    $row['no_rak'] ?? '',
                    $row['no_berkas'] ?? ''
                ]);
                $success_records++;
            } catch (PDOException $e) {
                $error_records++;
                $error_message .= "Row error: " . $e->getMessage() . "; ";
            }
        }
        
        // Commit transaction
        $sqlite_pdo->commit();
        
    } catch (Exception $e) {
        if ($sqlite_pdo->inTransaction()) {
            $sqlite_pdo->rollback();
        }
        throw $e;
    }
    
    $end_time = microtime(true);
    $duration = $end_time - $start_time;
    
    // Log hasil sinkronisasi
    $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',
        $error_message
    ]);
    
    return [
        'total_records' => $total_records,
        'success_records' => $success_records,
        'error_records' => $error_records,
        'duration' => $duration,
        'status' => ($error_records == 0) ? 'SUCCESS' : 'PARTIAL_SUCCESS'
    ];
}

// Proses sinkronisasi jika diminta
$sync_result = null;
$error_message = '';

if (isset($_POST['sync'])) {
    // Debug: Log that sync was triggered
    error_log("SYNC DEBUG: Sync process started");
    
    try {
        // Test koneksi terlebih dahulu
        $test_dsn = "mysql:host={$sipp_config['host']};port={$sipp_config['port']};dbname={$sipp_config['dbname']};charset=utf8mb4";
        $test_pdo = new PDO($test_dsn, $sipp_config['username'], $sipp_config['password']);
        $test_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Test query untuk memastikan tabel arsip bisa diakses
        $test_stmt = $test_pdo->query("SELECT COUNT(*) FROM arsip LIMIT 1");
        $test_count = $test_stmt->fetchColumn();
        error_log("SYNC DEBUG: SIPP connection OK, record count: " . $test_count);
        
        // Jika test berhasil, lanjutkan dengan sync
        $sipp_pdo = connectSipp($sipp_config);
        $sqlite_pdo = connectSQLite($sqlite_path);
        
        error_log("SYNC DEBUG: Both connections established");
        
        // Buat tabel jika belum ada
        createArsipTable($sqlite_pdo);
        createSyncLogTable($sqlite_pdo);
        
        error_log("SYNC DEBUG: Tables created/verified");
        
        // Lakukan sinkronisasi
        $sync_result = syncArsipData($sipp_pdo, $sqlite_pdo);
        
        error_log("SYNC DEBUG: Sync completed with result: " . json_encode($sync_result));
        
    } catch (Exception $e) {
        $error_message = "Koneksi ke server SIPP gagal: " . $e->getMessage();
        error_log("SYNC DEBUG: Error occurred: " . $error_message);
    }
}

// Ambil log sinkronisasi terakhir
$last_sync = null;
try {
    $sqlite_pdo = connectSQLite($sqlite_path);
    
    // Pastikan tabel sync_log ada sebelum query
    createSyncLogTable($sqlite_pdo);
    
    $stmt = $sqlite_pdo->query("SELECT * FROM sync_log ORDER BY sync_date DESC LIMIT 1");
    $last_sync = $stmt->fetch();
} catch (Exception $e) {
    // Ignore jika ada error
    error_log("Warning: Could not fetch last sync log: " . $e->getMessage());
}

?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Sinkronisasi Data SIPP ke SQLite</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.2/css/all.min.css">
</head>
<body>
    <div class="container mt-4">
        <div class="row justify-content-center">
            <div class="col-md-10">
                <div class="card">
                    <div class="card-header bg-primary text-white">
                        <h5 class="mb-0">
                            <i class="fa fa-sync me-2"></i>Sinkronisasi Data SIPP ke SQLite
                        </h5>
                    </div>
                    <div class="card-body">
                        
                        <!-- Info -->
                        <div class="alert alert-info">
                            <h6><i class="fa fa-info-circle me-2"></i>Tentang Sinkronisasi</h6>
                            <p class="mb-1">Script ini akan menyalin data arsip dari server SIPP (192.168.1.240) ke database SQLite lokal.</p>
                            <p class="mb-0"><strong>Keuntungan:</strong> Aplikasi tetap berfungsi meski server SIPP offline, performa lebih cepat.</p>
                        </div>
                        
                        <!-- Status Koneksi -->
                        <div class="row mb-4">
                            <div class="col-md-6">
                                <div class="card border-info">
                                    <div class="card-body">
                                        <h6><i class="fa fa-server me-2"></i>Server SIPP</h6>
                                        <p class="mb-1"><strong>Host:</strong> <?php echo $sipp_config['host']; ?></p>
                                        <p class="mb-1"><strong>Database:</strong> <?php echo $sipp_config['dbname']; ?></p>
                                        <div id="sipp-status">
                                            <span class="badge bg-secondary">Checking...</span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="card border-success">
                                    <div class="card-body">
                                        <h6><i class="fa fa-database me-2"></i>SQLite Lokal</h6>
                                        <p class="mb-1"><strong>Path:</strong> <?php echo $sqlite_path; ?></p>
                                        <p class="mb-1"><strong>Size:</strong> 
                                            <?php 
                                            if (file_exists($sqlite_path)) {
                                                echo number_format(filesize($sqlite_path) / 1024, 2) . ' KB';
                                            } else {
                                                echo 'Belum ada';
                                            }
                                            ?>
                                        </p>
                                        <span class="badge bg-success">Ready</span>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Hasil Sinkronisasi -->
                        <?php if ($sync_result): ?>
                        <div class="alert alert-success">
                            <h6><i class="fa fa-check-circle me-2"></i>Sinkronisasi Berhasil</h6>
                            <div class="row">
                                <div class="col-md-3">
                                    <strong>Total Records:</strong><br>
                                    <span class="h4 text-primary"><?php echo number_format($sync_result['total_records']); ?></span>
                                </div>
                                <div class="col-md-3">
                                    <strong>Berhasil:</strong><br>
                                    <span class="h4 text-success"><?php echo number_format($sync_result['success_records']); ?></span>
                                </div>
                                <div class="col-md-3">
                                    <strong>Error:</strong><br>
                                    <span class="h4 text-danger"><?php echo number_format($sync_result['error_records']); ?></span>
                                </div>
                                <div class="col-md-3">
                                    <strong>Duration:</strong><br>
                                    <span class="h4 text-info"><?php echo number_format($sync_result['duration'], 2); ?>s</span>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <?php if ($error_message): ?>
                        <div class="alert alert-danger">
                            <h6><i class="fa fa-exclamation-triangle me-2"></i>Error</h6>
                            <p><?php echo htmlspecialchars($error_message); ?></p>
                        </div>
                        <?php endif; ?>
                        
                        <!-- Sinkronisasi Terakhir -->
                        <?php if ($last_sync): ?>
                        <div class="alert alert-light">
                            <h6><i class="fa fa-history me-2"></i>Sinkronisasi Terakhir</h6>
                            <div class="row">
                                <div class="col-md-6">
                                    <p class="mb-1"><strong>Tanggal:</strong> <?php echo $last_sync['sync_date']; ?></p>
                                    <p class="mb-1"><strong>Status:</strong> 
                                        <span class="badge bg-<?php echo $last_sync['status'] == 'SUCCESS' ? 'success' : 'warning'; ?>">
                                            <?php echo $last_sync['status']; ?>
                                        </span>
                                    </p>
                                </div>
                                <div class="col-md-6">
                                    <p class="mb-1"><strong>Total Records:</strong> <?php echo number_format($last_sync['total_records']); ?></p>
                                    <p class="mb-1"><strong>Duration:</strong> <?php echo number_format($last_sync['sync_duration'], 2); ?>s</p>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <!-- Form Sinkronisasi -->
                        <form method="POST" id="syncForm">
                            <div class="d-grid gap-2">
                                <button type="submit" name="sync" class="btn btn-primary btn-lg" id="syncBtn">
                                    <i class="fa fa-sync me-2"></i>Mulai Sinkronisasi (Max 1000 records)
                                </button>
                                <small class="text-muted text-center">
                                    <i class="fa fa-info-circle me-1"></i>
                                    Proses sync dibatasi 1000 records untuk menghindari timeout. 
                                    Untuk sync semua data, jalankan beberapa kali.
                                </small>
                            </div>
                        </form>
                        
                        <hr>
                        
                        <!-- Navigasi -->
                        <div class="d-flex gap-2">
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fa fa-arrow-left me-1"></i>Kembali
                            </a>
                            <a href="test-sqlite-arsip.php" class="btn btn-info">
                                <i class="fa fa-eye me-1"></i>Test Data SQLite
                            </a>
                            <a href="arsip-sqlite.php" class="btn btn-success">
                                <i class="fa fa-table me-1"></i>Lihat Arsip SQLite
                            </a>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Check SIPP connection status
        async function checkSippConnection() {
            const statusElement = document.getElementById('sipp-status');
            
            try {
                const response = await fetch('api/sipp-status.php');
                const result = await response.json();
                
                if (result.success) {
                    statusElement.innerHTML = '<span class="badge bg-success">Connected</span>';
                } else {
                    statusElement.innerHTML = '<span class="badge bg-danger">Error</span>';
                    console.error('SIPP Connection Error:', result.message);
                }
            } catch (error) {
                statusElement.innerHTML = '<span class="badge bg-danger">Error</span>';
                console.error('API Error:', error);
            }
        }
        
        // Form submission with loading and timeout warning
        document.getElementById('syncForm').addEventListener('submit', function() {
            const btn = document.getElementById('syncBtn');
            btn.innerHTML = '<i class="fa fa-spinner fa-spin me-2"></i>Syncing... Please wait (up to 5 minutes)';
            btn.disabled = true;
            
            // Show warning after 30 seconds
            setTimeout(function() {
                if (btn.disabled) {
                    btn.innerHTML = '<i class="fa fa-spinner fa-spin me-2"></i>Still processing... This may take a while for large datasets';
                }
            }, 30000);
        });
        
        // Check connection on page load
        document.addEventListener('DOMContentLoaded', function() {
            checkSippConnection();
        });
    </script>
</body>
</html>
