<?php
/**
 * Halaman Arsip SQLite
 * Menampilkan data arsip dari database SQLite lokal
 */

require 'auth.php';
require_login();

$sqlite_path = 'data/sipp_sync.sqlite';

// Cek apakah ini auto-fallback dari SIPP yang tidak tersedia
$is_auto_fallback = isset($_GET['source']) && $_GET['source'] === 'auto';

// Pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 50;
$offset = ($page - 1) * $limit;

// Search
$search = isset($_GET['search']) ? trim($_GET['search']) : '';

// Sort
$sort_column = isset($_GET['sort']) ? $_GET['sort'] : 'nomor_perkara';
$sort_direction = isset($_GET['dir']) && $_GET['dir'] == 'desc' ? 'DESC' : 'ASC';

$allowed_columns = ['nomor_perkara', 'nomor_arsip', 'no_ruang', 'no_lemari', 'no_rak', 'no_berkas'];
if (!in_array($sort_column, $allowed_columns)) {
    $sort_column = 'nomor_perkara';
}

$data = [];
$total_records = 0;
$total_pages = 0;
$error_message = '';

try {
    if (!file_exists($sqlite_path)) {
        throw new Exception('Database SQLite belum ada. Lakukan sinkronisasi terlebih dahulu.');
    }
    
    $sqlite_pdo = new PDO("sqlite:$sqlite_path", null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
    
    // Build WHERE clause
    $where_clause = '';
    $params = [];
    
    if (!empty($search)) {
        $where_clause = "WHERE nomor_perkara LIKE :search OR nomor_arsip LIKE :search";
        $params[':search'] = "%$search%";
    }
    
    // Count total records
    $count_sql = "SELECT COUNT(*) as total FROM arsip $where_clause";
    $count_stmt = $sqlite_pdo->prepare($count_sql);
    $count_stmt->execute($params);
    $total_records = $count_stmt->fetch()['total'];
    $total_pages = ceil($total_records / $limit);
    
    // Get data
    $data_sql = "SELECT * FROM arsip $where_clause ORDER BY $sort_column $sort_direction LIMIT $limit OFFSET $offset";
    $data_stmt = $sqlite_pdo->prepare($data_sql);
    $data_stmt->execute($params);
    $data = $data_stmt->fetchAll();
    
} catch (Exception $e) {
    $error_message = $e->getMessage();
}

// Helper function for sort URL
function getSortUrl($column) {
    global $sort_column, $sort_direction, $search, $page;
    $new_direction = ($sort_column == $column && $sort_direction == 'ASC') ? 'desc' : 'asc';
    $url = "?sort=$column&dir=$new_direction&page=1";
    if (!empty($search)) {
        $url .= "&search=" . urlencode($search);
    }
    return $url;
}

// Helper function for sort icon
function getSortIcon($column) {
    global $sort_column, $sort_direction;
    if ($sort_column == $column) {
        return $sort_direction == 'ASC' ? 'fa-sort-up' : 'fa-sort-down';
    }
    return 'fa-sort';
}
?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Arsip SIPP (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">
    <style>
        .arsip-card {
            border: 2px solid #28a745;
            border-radius: 10px;
            box-shadow: 0 4px 6px rgba(40, 167, 69, 0.1);
        }
        .arsip-header {
            background: linear-gradient(135deg, #28a745, #20c997);
            border-radius: 8px 8px 0 0;
        }
        .table-arsip th {
            background-color: #e8f5e8;
            border-bottom: 2px solid #28a745;
            color: #155724;
            font-weight: 600;
        }
        .table-arsip tbody tr:hover {
            background-color: #f8fff8;
        }
        .badge-sqlite {
            background-color: #17a2b8;
        }
        .sort-link {
            color: #155724;
            text-decoration: none;
        }
        .sort-link:hover {
            color: #28a745;
        }
    </style>
</head>
<body class="bg-light">
    <div class="container-fluid mt-4">
        
        <!-- Auto-fallback notification -->
        <?php if ($is_auto_fallback): ?>
        <div class="alert alert-info alert-dismissible fade show">
            <i class="fa fa-info-circle me-2"></i>
            <strong>Mode Offline:</strong> Server SIPP tidak dapat diakses (mungkin karena hosting). 
            Menampilkan data dari database lokal SQLite.
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
        <?php endif; ?>
        
        <!-- Header -->
        <div class="row mb-4">
            <div class="col-12">
                <div class="card arsip-card">
                    <div class="card-header arsip-header text-white">
                        <div class="row align-items-center">
                            <div class="col-md-6">
                                <h4 class="mb-0">
                                    <i class="fa fa-database me-2"></i>
                                    Arsip SIPP 
                                    <span class="badge badge-sqlite">SQLite</span>
                                    <?php if ($is_auto_fallback): ?>
                                    <span class="badge bg-warning text-dark">Offline Mode</span>
                                    <?php endif; ?>
                                </h4>
                                <small>Data arsip dari database lokal SQLite</small>
                            </div>
                            <div class="col-md-6 text-end">
                                <div class="d-flex gap-2 justify-content-end">
                                    <a href="sync-sipp-to-sqlite.php" class="btn btn-light btn-sm">
                                        <i class="fa fa-sync me-1"></i>Sinkronisasi
                                    </a>
                                    <a href="arsip.php" class="btn btn-outline-light btn-sm">
                                        <i class="fa fa-server me-1"></i>Mode SIPP Online
                                    </a>
                                    <a href="index.php" class="btn btn-outline-light btn-sm">
                                        <i class="fa fa-home me-1"></i>Dashboard
                                    </a>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        
        <?php if ($error_message): ?>
        <div class="row mb-4">
            <div class="col-12">
                <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>
            </div>
        </div>
        <?php else: ?>
        
        <!-- Search and Info -->
        <div class="row mb-3">
            <div class="col-md-6">
                <form method="GET" class="d-flex">
                    <input type="hidden" name="sort" value="<?php echo $sort_column; ?>">
                    <input type="hidden" name="dir" value="<?php echo strtolower($sort_direction); ?>">
                    <input type="text" name="search" class="form-control me-2" 
                           placeholder="Cari nomor perkara atau nomor arsip..." 
                           value="<?php echo htmlspecialchars($search); ?>">
                    <button type="submit" class="btn btn-success">
                        <i class="fa fa-search"></i>
                    </button>
                </form>
            </div>
            <div class="col-md-6 text-end">
                <div class="text-muted">
                    <small>
                        Menampilkan <?php echo number_format(count($data)); ?> dari <?php echo number_format($total_records); ?> record
                        <?php if (!empty($search)): ?>
                        | Pencarian: "<?php echo htmlspecialchars($search); ?>"
                        <a href="?" class="text-decoration-none ms-1">
                            <i class="fa fa-times-circle"></i>
                        </a>
                        <?php endif; ?>
                    </small>
                </div>
            </div>
        </div>
        
        <!-- Table -->
        <div class="row">
            <div class="col-12">
                <div class="card arsip-card">
                    <div class="card-body p-0">
                        <?php if (!empty($data)): ?>
                        <div class="table-responsive">
                            <table class="table table-arsip table-hover mb-0">
                                <thead>
                                    <tr>
                                        <th>
                                            <a href="<?php echo getSortUrl('nomor_perkara'); ?>" class="sort-link">
                                                Nomor Perkara <i class="fa <?php echo getSortIcon('nomor_perkara'); ?>"></i>
                                            </a>
                                        </th>
                                        <th>
                                            <a href="<?php echo getSortUrl('nomor_arsip'); ?>" class="sort-link">
                                                Nomor Arsip <i class="fa <?php echo getSortIcon('nomor_arsip'); ?>"></i>
                                            </a>
                                        </th>
                                        <th>
                                            <a href="<?php echo getSortUrl('no_ruang'); ?>" class="sort-link">
                                                Ruang <i class="fa <?php echo getSortIcon('no_ruang'); ?>"></i>
                                            </a>
                                        </th>
                                        <th>
                                            <a href="<?php echo getSortUrl('no_lemari'); ?>" class="sort-link">
                                                Lemari <i class="fa <?php echo getSortIcon('no_lemari'); ?>"></i>
                                            </a>
                                        </th>
                                        <th>
                                            <a href="<?php echo getSortUrl('no_rak'); ?>" class="sort-link">
                                                Rak <i class="fa <?php echo getSortIcon('no_rak'); ?>"></i>
                                            </a>
                                        </th>
                                        <th>
                                            <a href="<?php echo getSortUrl('no_berkas'); ?>" class="sort-link">
                                                Berkas <i class="fa <?php echo getSortIcon('no_berkas'); ?>"></i>
                                            </a>
                                        </th>
                                        <th width="100">Aksi</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($data as $row): ?>
                                    <tr>
                                        <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>
                                            <button type="button" class="btn btn-sm btn-outline-success" 
                                                    onclick="showDetail(<?php echo htmlspecialchars(json_encode($row)); ?>)">
                                                <i class="fa fa-eye"></i>
                                            </button>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                        <?php else: ?>
                        <div class="text-center py-5">
                            <i class="fa fa-inbox fa-3x text-muted mb-3"></i>
                            <h5 class="text-muted">Tidak ada data ditemukan</h5>
                            <?php if (!empty($search)): ?>
                            <p class="text-muted">Coba ubah kata kunci pencarian</p>
                            <?php else: ?>
                            <p class="text-muted">Lakukan sinkronisasi untuk mengambil data dari server SIPP</p>
                            <a href="sync-sipp-to-sqlite.php" class="btn btn-success">
                                <i class="fa fa-sync me-1"></i>Sinkronisasi Data
                            </a>
                            <?php endif; ?>
                        </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Pagination -->
        <?php if ($total_pages > 1): ?>
        <div class="row mt-4">
            <div class="col-12">
                <nav aria-label="Page navigation">
                    <ul class="pagination justify-content-center">
                        <?php if ($page > 1): ?>
                        <li class="page-item">
                            <a class="page-link" href="?page=<?php echo $page-1; ?>&sort=<?php echo $sort_column; ?>&dir=<?php echo strtolower($sort_direction); ?><?php echo !empty($search) ? '&search='.urlencode($search) : ''; ?>">
                                <i class="fa fa-chevron-left"></i>
                            </a>
                        </li>
                        <?php endif; ?>
                        
                        <?php for ($i = max(1, $page-2); $i <= min($total_pages, $page+2); $i++): ?>
                        <li class="page-item <?php echo $i == $page ? 'active' : ''; ?>">
                            <a class="page-link" href="?page=<?php echo $i; ?>&sort=<?php echo $sort_column; ?>&dir=<?php echo strtolower($sort_direction); ?><?php echo !empty($search) ? '&search='.urlencode($search) : ''; ?>">
                                <?php echo $i; ?>
                            </a>
                        </li>
                        <?php endfor; ?>
                        
                        <?php if ($page < $total_pages): ?>
                        <li class="page-item">
                            <a class="page-link" href="?page=<?php echo $page+1; ?>&sort=<?php echo $sort_column; ?>&dir=<?php echo strtolower($sort_direction); ?><?php echo !empty($search) ? '&search='.urlencode($search) : ''; ?>">
                                <i class="fa fa-chevron-right"></i>
                            </a>
                        </li>
                        <?php endif; ?>
                    </ul>
                </nav>
            </div>
        </div>
        <?php endif; ?>
        
        <?php endif; ?>
    </div>

    <!-- Detail Modal -->
    <div class="modal fade" id="detailModal" tabindex="-1">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <div class="modal-header bg-success text-white">
                    <h5 class="modal-title">
                        <i class="fa fa-info-circle me-2"></i>Detail Arsip
                    </h5>
                    <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body" id="detailContent">
                    <!-- Content will be loaded here -->
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Tutup</button>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function showDetail(data) {
            const content = `
                <div class="row">
                    <div class="col-md-6">
                        <div class="card border-success">
                            <div class="card-header bg-light">
                                <h6 class="mb-0"><i class="fa fa-gavel me-2"></i>Informasi Perkara</h6>
                            </div>
                            <div class="card-body">
                                <p><strong>Nomor Perkara:</strong><br>${data.nomor_perkara}</p>
                                <p><strong>Nomor Arsip:</strong><br>${data.nomor_arsip}</p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="card border-info">
                            <div class="card-header bg-light">
                                <h6 class="mb-0"><i class="fa fa-map-marker-alt me-2"></i>Lokasi Fisik</h6>
                            </div>
                            <div class="card-body">
                                <p><strong>Ruang:</strong> ${data.no_ruang}</p>
                                <p><strong>Lemari:</strong> ${data.no_lemari}</p>
                                <p><strong>Rak:</strong> ${data.no_rak}</p>
                                <p><strong>Berkas:</strong> ${data.no_berkas}</p>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="alert alert-info mt-3">
                    <i class="fa fa-database me-2"></i>
                    Data ini berasal dari database SQLite lokal.
                </div>
            `;
            
            document.getElementById('detailContent').innerHTML = content;
            new bootstrap.Modal(document.getElementById('detailModal')).show();
        }
    </script>
</body>
</html>
