<?php
/**
 * Test Data SQLite Arsip
 * Halaman untuk melihat dan test data arsip yang sudah disinkronkan ke SQLite
 */

require 'auth.php';
require_login();

$sqlite_path = 'data/sipp_sync.sqlite';

/**
 * Koneksi ke SQLite
 */
function connectSQLite($path) {
    try {
        if (!file_exists($path)) {
            throw new Exception("Database SQLite belum ada. Lakukan sinkronisasi terlebih dahulu.");
        }
        
        $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());
    }
}

// Ambil statistik database
$stats = null;
$sample_data = [];
$sync_logs = [];
$error_message = '';

try {
    $sqlite_pdo = connectSQLite($sqlite_path);
    
    // Statistik umum
    $stmt = $sqlite_pdo->query("SELECT COUNT(*) as total FROM arsip");
    $total_records = $stmt->fetch()['total'];
    
    // Sample data
    $stmt = $sqlite_pdo->query("SELECT * FROM arsip ORDER BY nomor_perkara LIMIT 10");
    $sample_data = $stmt->fetchAll();
    
    // Log sinkronisasi - pastikan tabel ada
    try {
        // Buat tabel sync_log jika belum ada
        $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);
        
        $stmt = $sqlite_pdo->query("SELECT * FROM sync_log ORDER BY sync_date DESC LIMIT 5");
        $sync_logs = $stmt->fetchAll();
    } catch (Exception $e) {
        $sync_logs = [];
    }
    
    $stats = [
        'total_records' => $total_records,
        'file_size' => file_exists($sqlite_path) ? filesize($sqlite_path) : 0
    ];
    
} catch (Exception $e) {
    $error_message = $e->getMessage();
}

?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Test Data SQLite Arsip</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-12">
                <div class="card">
                    <div class="card-header bg-success text-white">
                        <h5 class="mb-0">
                            <i class="fa fa-database me-2"></i>Test Data SQLite Arsip
                        </h5>
                    </div>
                    <div class="card-body">
                        
                        <?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>
                            <a href="sync-sipp-to-sqlite.php" class="btn btn-primary">
                                <i class="fa fa-sync me-1"></i>Lakukan Sinkronisasi
                            </a>
                        </div>
                        <?php else: ?>
                        
                        <!-- Statistik Database -->
                        <div class="row mb-4">
                            <div class="col-md-4">
                                <div class="card border-primary">
                                    <div class="card-body text-center">
                                        <h3 class="text-primary"><?php echo number_format($stats['total_records']); ?></h3>
                                        <p class="mb-0">Total Records</p>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="card border-info">
                                    <div class="card-body text-center">
                                        <h3 class="text-info"><?php echo number_format($stats['file_size'] / 1024, 2); ?> KB</h3>
                                        <p class="mb-0">Database Size</p>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="card border-success">
                                    <div class="card-body text-center">
                                        <h3 class="text-success">
                                            <?php echo file_exists($sqlite_path) ? 'Ready' : 'Not Found'; ?>
                                        </h3>
                                        <p class="mb-0">Status</p>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Sample Data -->
                        <?php if (!empty($sample_data)): ?>
                        <div class="card mb-4">
                            <div class="card-header">
                                <h6 class="mb-0"><i class="fa fa-table me-2"></i>Sample Data (10 Records)</h6>
                            </div>
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-sm table-striped">
                                        <thead>
                                            <tr>
                                                <th>ID</th>
                                                <th>Nomor Perkara</th>
                                                <th>Nomor Arsip</th>
                                                <th>Ruang</th>
                                                <th>Lemari</th>
                                                <th>Rak</th>
                                                <th>Berkas</th>
                                                <th>Sync Date</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($sample_data as $row): ?>
                                            <tr>
                                                <td><?php echo $row['id']; ?></td>
                                                <td><strong><?php echo htmlspecialchars($row['nomor_perkara']); ?></strong></td>
                                                <td><?php echo htmlspecialchars($row['nomor_arsip']); ?></td>
                                                <td><?php echo htmlspecialchars($row['no_ruang']); ?></td>
                                                <td><?php echo htmlspecialchars($row['no_lemari']); ?></td>
                                                <td><?php echo htmlspecialchars($row['no_rak']); ?></td>
                                                <td><?php echo htmlspecialchars($row['no_berkas']); ?></td>
                                                <td><?php echo $row['tanggal_sync']; ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <!-- Log Sinkronisasi -->
                        <?php if (!empty($sync_logs)): ?>
                        <div class="card mb-4">
                            <div class="card-header">
                                <h6 class="mb-0"><i class="fa fa-history me-2"></i>Riwayat Sinkronisasi</h6>
                            </div>
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-sm">
                                        <thead>
                                            <tr>
                                                <th>Tanggal</th>
                                                <th>Total</th>
                                                <th>Berhasil</th>
                                                <th>Error</th>
                                                <th>Duration</th>
                                                <th>Status</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($sync_logs as $log): ?>
                                            <tr>
                                                <td><?php echo $log['sync_date']; ?></td>
                                                <td><?php echo number_format($log['total_records']); ?></td>
                                                <td class="text-success"><?php echo number_format($log['success_records']); ?></td>
                                                <td class="text-danger"><?php echo number_format($log['error_records']); ?></td>
                                                <td><?php echo number_format($log['sync_duration'], 2); ?>s</td>
                                                <td>
                                                    <span class="badge bg-<?php echo $log['status'] == 'SUCCESS' ? 'success' : 'warning'; ?>">
                                                        <?php echo $log['status']; ?>
                                                    </span>
                                                </td>
                                            </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <!-- Test Autocomplete -->
                        <div class="card mb-4">
                            <div class="card-header">
                                <h6 class="mb-0"><i class="fa fa-search me-2"></i>Test Autocomplete SQLite</h6>
                            </div>
                            <div class="card-body">
                                <div class="mb-3">
                                    <label for="test_nomor_perkara" class="form-label">Test Pencarian Nomor Perkara:</label>
                                    <input type="text" class="form-control" id="test_nomor_perkara" 
                                           placeholder="Ketik untuk mencari..." autocomplete="off">
                                </div>
                                <div id="search_results"></div>
                            </div>
                        </div>
                        
                        <?php endif; ?>
                        
                        <!-- Navigasi -->
                        <div class="d-flex gap-2">
                            <a href="sync-sipp-to-sqlite.php" class="btn btn-primary">
                                <i class="fa fa-sync me-1"></i>Sinkronisasi
                            </a>
                            <a href="arsip-sqlite.php" class="btn btn-success">
                                <i class="fa fa-table me-1"></i>Lihat Arsip SQLite
                            </a>
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fa fa-arrow-left me-1"></i>Kembali
                            </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>
        // Test search function
        async function searchNomorPerkara(query) {
            if (query.length < 2) {
                document.getElementById('search_results').innerHTML = '';
                return;
            }
            
            try {
                const response = await fetch(`api/search-nomor-perkara-sqlite.php?q=${encodeURIComponent(query)}`);
                const data = await response.json();
                
                const resultsDiv = document.getElementById('search_results');
                
                if (Array.isArray(data) && data.length > 0) {
                    let html = '<div class="alert alert-success">Found ' + data.length + ' results:</div>';
                    html += '<div class="table-responsive"><table class="table table-sm table-bordered">';
                    html += '<thead><tr><th>Nomor Perkara</th><th>Nomor Arsip</th><th>Ruang</th><th>Lemari</th><th>Rak</th><th>Berkas</th></tr></thead><tbody>';
                    
                    data.forEach(item => {
                        html += `<tr>
                            <td><strong>${item.nomor_perkara || '-'}</strong></td>
                            <td>${item.nomor_arsip || '-'}</td>
                            <td>${item.no_ruang || '-'}</td>
                            <td>${item.no_lemari || '-'}</td>
                            <td>${item.no_rak || '-'}</td>
                            <td>${item.no_berkas || '-'}</td>
                        </tr>`;
                    });
                    
                    html += '</tbody></table></div>';
                    resultsDiv.innerHTML = html;
                    
                } else {
                    resultsDiv.innerHTML = '<div class="alert alert-warning">No results found.</div>';
                }
                
            } catch (error) {
                document.getElementById('search_results').innerHTML = 
                    '<div class="alert alert-danger">Search error: ' + error.message + '</div>';
            }
        }
        
        // Bind search input
        document.getElementById('test_nomor_perkara').addEventListener('input', function() {
            const query = this.value;
            clearTimeout(this.searchTimeout);
            this.searchTimeout = setTimeout(() => searchNomorPerkara(query), 300);
        });
    </script>
</body>
</html>
