<?php
session_start();
require_once 'db.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

$db_path = __DIR__ . '/data/data.sqlite';

function checkSQLiteTable($db_path) {
    try {
        $sqlite_pdo = new PDO("sqlite:$db_path");
        $sqlite_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Check if table exists
        $stmt = $sqlite_pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='arsip'");
        $table_exists = $stmt->fetch();
        
        if (!$table_exists) {
            return [
                'status' => 'error',
                'message' => 'Tabel arsip tidak ditemukan di SQLite database',
                'table_exists' => false,
                'record_count' => 0
            ];
        }
        
        // Count records
        $stmt = $sqlite_pdo->query("SELECT COUNT(*) as count FROM arsip");
        $count = $stmt->fetch()['count'];
        
        // Get sample data
        $stmt = $sqlite_pdo->query("SELECT * FROM arsip LIMIT 5");
        $sample_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return [
            'status' => 'success',
            'message' => 'Tabel arsip tersedia',
            'table_exists' => true,
            'record_count' => $count,
            'sample_data' => $sample_data
        ];
        
    } catch (PDOException $e) {
        return [
            'status' => 'error',
            'message' => 'Database error: ' . $e->getMessage(),
            'table_exists' => false,
            'record_count' => 0
        ];
    }
}

$result = checkSQLiteTable($db_path);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Check Arsip SQLite - Sistem Pinjam Berkas</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <style>
        .card-header-custom {
            background: linear-gradient(135deg, #28a745, #20c997);
            color: white;
        }
        .table-container {
            max-height: 400px;
            overflow-y: auto;
        }
    </style>
</head>
<body class="bg-light">
    <div class="container mt-5">
        <div class="row justify-content-center">
            <div class="col-md-10">
                <div class="card shadow">
                    <div class="card-header card-header-custom">
                        <h4 class="mb-0">
                            <i class="fa fa-database me-2"></i>Status Database Arsip SQLite
                        </h4>
                    </div>
                    <div class="card-body">
                        <?php if ($result['status'] === 'success'): ?>
                        <div class="alert alert-success">
                            <h6><i class="fa fa-check-circle me-2"></i><?php echo $result['message']; ?></h6>
                            <p class="mb-0">Total records: <strong><?php echo number_format($result['record_count']); ?></strong></p>
                        </div>
                        
                        <?php if (!empty($result['sample_data'])): ?>
                        <h6 class="mt-4">Sample Data (5 records):</h6>
                        <div class="table-container">
                            <table class="table table-striped table-sm">
                                <thead class="table-dark">
                                    <tr>
                                        <th>No Perkara</th>
                                        <th>Nomor Berkas</th>
                                        <th>Majelis</th>
                                        <th>Tahun</th>
                                        <th>Tanggal</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($result['sample_data'] as $row): ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars($row['no_perkara'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($row['nomor_berkas'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($row['majelis'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($row['tahun'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($row['tanggal'] ?? 'N/A'); ?></td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                        <?php endif; ?>
                        
                        <?php else: ?>
                        <div class="alert alert-danger">
                            <h6><i class="fa fa-times-circle me-2"></i>Error</h6>
                            <p><?php echo htmlspecialchars($result['message']); ?></p>
                        </div>
                        
                        <div class="alert alert-info">
                            <h6><i class="fa fa-info-circle me-2"></i>Solusi:</h6>
                            <ul class="mb-0">
                                <li>Lakukan sinkronisasi data dari SIPP</li>
                                <li>Atau import data dari file CSV</li>
                                <li>Pastikan database SQLite bisa diakses</li>
                            </ul>
                        </div>
                        <?php endif; ?>
                        
                        <div class="mt-4">
                            <h6>Database Information:</h6>
                            <table class="table table-sm">
                                <tr>
                                    <td><strong>Database Path:</strong></td>
                                    <td><code><?php echo $db_path; ?></code></td>
                                </tr>
                                <tr>
                                    <td><strong>File Exists:</strong></td>
                                    <td>
                                        <?php if (file_exists($db_path)): ?>
                                            <span class="text-success"><i class="fa fa-check"></i> Yes</span>
                                        <?php else: ?>
                                            <span class="text-danger"><i class="fa fa-times"></i> No</span>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                                <tr>
                                    <td><strong>File Size:</strong></td>
                                    <td>
                                        <?php if (file_exists($db_path)): ?>
                                            <?php echo number_format(filesize($db_path) / 1024, 2); ?> KB
                                        <?php else: ?>
                                            N/A
                                        <?php endif; ?>
                                    </td>
                                </tr>
                                <tr>
                                    <td><strong>Table Exists:</strong></td>
                                    <td>
                                        <?php if ($result['table_exists']): ?>
                                            <span class="text-success"><i class="fa fa-check"></i> Yes</span>
                                        <?php else: ?>
                                            <span class="text-danger"><i class="fa fa-times"></i> No</span>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            </table>
                        </div>
                        
                        <div class="mt-4 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="import-arsip-csv.php" class="btn btn-info">
                                <i class="fa fa-upload me-1"></i>Import CSV
                            </a>
                            <a href="arsip-sqlite.php" class="btn btn-success">
                                <i class="fa fa-eye me-1"></i>Lihat Arsip
                            </a>
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fa fa-home me-1"></i>Kembali
                            </a>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
