<?php
/**
 * Audit Trail System
 * View and manage system audit logs
 */

// DEBUG: Show all errors
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../includes/SecurityHelper.php';

// Ensure a single session is started
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Check authentication - redirect to login if not logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ' . BASE_URL . '/login.php');
    exit;
}

// Check admin role - support both new and legacy session variables
$user_role = $_SESSION['user_role'] ?? $_SESSION['role'] ?? '';
if (!isset($_SESSION['user_id']) || ($user_role !== 'admin')) {
    http_response_code(403);
    die('❌ অ্যাক্সেস denied। আপনার এই পেজ দেখার অনুমতি নেই।<br><br>
    <a href="' . BASE_URL . '/login.php" class="btn btn-primary">লগইন পেজে যান</a>');
}

// Get filter parameters
$filter_action = $_GET['action'] ?? '';
$filter_table = $_GET['table'] ?? '';
$filter_user = $_GET['user'] ?? '';
$filter_date_from = $_GET['date_from'] ?? '';
$filter_date_to = $_GET['date_to'] ?? '';

// Build query
$where = [];
$params = [];

if ($filter_action) {
    $where[] = "action = ?";
    $params[] = $filter_action;
}
if ($filter_table) {
    $where[] = "table_name = ?";
    $params[] = $filter_table;
}
if ($filter_user) {
    $where[] = "(user_id = ? OR user_role LIKE ?)";
    $params[] = $filter_user;
    $params[] = "%$filter_user%";
}
if ($filter_date_from) {
    $where[] = "created_at >= ?";
    $params[] = $filter_date_from . ' 00:00:00';
}
if ($filter_date_to) {
    $where[] = "created_at <= ?";
    $params[] = $filter_date_to . ' 23:59:59';
}

$whereClause = count($where) > 0 ? 'WHERE ' . implode(' AND ', $where) : '';

// Get paginated logs
$page = intval($_GET['page'] ?? 1);
$per_page = 50;
$offset = ($page - 1) * $per_page;
$total_records = 0;
$total_pages = 0;
$logs = [];

// Get audit logs with error handling
$logs_error = '';
try {
    // First check if audit_log table exists
    $check_table = $pdo->query("SHOW TABLES LIKE 'audit_log'");
    if ($check_table->rowCount() == 0) {
        error_log('audit/index.php: audit_log table does not exist');
        $logs = [];
        $total_records = 0;
        $logs_error = 'অডিট লগ টেবিল পাওয়া যায়নি। অনুগ্রহ করে প্রথমে ডাটাবেস সেটআপ করুন।';
    } else {
        // Check total records in table (without filters)
        $total_in_table = $pdo->query("SELECT COUNT(*) FROM audit_log")->fetchColumn();
        
        $count_stmt = $pdo->prepare("SELECT COUNT(*) FROM audit_log $whereClause");
        $count_stmt->execute($params);
        $total_records = $count_stmt->fetchColumn();
        $total_pages = ceil($total_records / $per_page);
        
        // Debug output
        $debug_sql = "SELECT * FROM audit_log $whereClause ORDER BY created_at DESC LIMIT $offset, $per_page";
        
        $stmt = $pdo->prepare("
            SELECT al.*
            FROM audit_log al
            $whereClause
            ORDER BY al.created_at DESC
            LIMIT $offset, $per_page
        ");
        $stmt->execute($params);
        $logs = $stmt->fetchAll();
        
        // If no records found with filters, show helpful message
        if ($total_records == 0 && $total_in_table > 0) {
            // Check date range
            $date_range = $pdo->query("SELECT MIN(created_at) as min_date, MAX(created_at) as max_date FROM audit_log")->fetch();
            $logs_error = "নির্বাচিত তারিখের রেঞ্জে কোনো তথ্য পাওয়া যায়নি।<br>ডাটাবেসে তথ্য আছে: {$date_range['min_date']} থেকে {$date_range['max_date']} পর্যন্ত।";
        } elseif ($total_in_table == 0) {
            $logs_error = 'অডিট লগ টেবিলে কোনো তথ্য নেই।';
        }
        
        // Debug: Show SQL and params if debug=1
        if (isset($_GET['debug']) && $_GET['debug'] == '1') {
            echo "<div class='alert alert-info'><strong>Debug:</strong><br>SQL: " . htmlspecialchars($debug_sql) . "<br>Params: " . htmlspecialchars(json_encode($params)) . "<br>Total in table: $total_in_table<br>Filtered records: $total_records<br>WHERE: " . htmlspecialchars($whereClause) . "</div>";
        }
    }
} catch (Exception $e) {
    error_log('audit/index.php: Error loading audit logs: ' . $e->getMessage());
    $logs = [];
    $total_records = 0;
    $logs_error = 'তথ্য লোড করতে সমস্যা হয়েছে: ' . $e->getMessage();
}

// Get statistics
$stats = [
    'total_logs' => 0,
    'unique_users' => 0,
    'inserts' => 0,
    'updates' => 0,
    'deletes' => 0,
    'last_7_days' => 0
];

try {
    $stats_query = $pdo->query("
        SELECT 
            COUNT(*) as total_logs,
            COUNT(DISTINCT user_id) as unique_users,
            COUNT(CASE WHEN action = 'INSERT' THEN 1 END) as inserts,
            COUNT(CASE WHEN action = 'UPDATE' THEN 1 END) as updates,
            COUNT(CASE WHEN action = 'DELETE' THEN 1 END) as deletes,
            COUNT(CASE WHEN created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as last_7_days
        FROM audit_log
    ");
    if ($stats_query) {
        $stats = $stats_query->fetch();
    }
} catch (Exception $e) {
    error_log('audit/index.php: Error loading statistics: ' . $e->getMessage());
}

// Get tables list for filter
$tables = [];
try {
    $tables = $pdo->query("SELECT DISTINCT table_name FROM audit_log WHERE table_name IS NOT NULL ORDER BY table_name")->fetchAll(PDO::FETCH_COLUMN);
} catch (Exception $e) {
    error_log('audit/index.php: Error loading tables: ' . $e->getMessage());
    $tables = [];
}

// Get users list for filter
$users = [];
try {
    $users = $pdo->query("
        SELECT DISTINCT al.user_id
        FROM audit_log al 
        WHERE al.user_id IS NOT NULL
        ORDER BY al.user_id
    ")->fetchAll();
} catch (Exception $e) {
    error_log('audit/index.php: Error loading users: ' . $e->getMessage());
    $users = [];
}

// DEBUG: Log successful page load
error_log('audit/index.php: Page loaded successfully. Logs: ' . count($logs) . ', Tables: ' . count($tables));
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>অডিট ট্রেইল - <?= APP_NAME ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        .audit-log-row:hover {
            background-color: #f8f9fa;
        }
        .change-positive {
            color: #28a745;
        }
        .change-negative {
            color: #dc3545;
        }
    </style>
    <link href="../../assets/css/style.css" rel="stylesheet">
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">
                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
                    <h1 class="h2">🔍 অডিট ট্রেইল সিস্টেম</h1>
                    <div>
                        <a href="../../index.php" class="btn btn-outline-primary">
                            <i class="fas fa-home"></i> হোম পেজ
                        </a>
                        <a href="login_attempts.php" class="btn btn-outline-warning ms-2">
                            <i class="fas fa-shield-alt"></i> লগইন প্রচেষ্টা
                        </a>
                        <a href="login_attempts_archive.php" class="btn btn-outline-secondary ms-2">
                            <i class="fas fa-archive"></i> আর্কাইভ দেখুন
                        </a>
                        <button class="btn btn-outline-danger ms-2" onclick="archiveOldLogs()">
                            <i class="fas fa-archive"></i> পুরানো লগ আর্কাইভ করুন
                        </button>
                    </div>
                </div>
                
                <!-- Statistics Cards -->
                <div class="row mb-4">
                    <div class="col-md-2">
                        <div class="card bg-primary text-white text-center">
                            <div class="card-body">
                                <h4><?= number_format($stats['total_logs']) ?></h4>
                                <small>মোট লগ</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-success text-white text-center">
                            <div class="card-body">
                                <h4><?= number_format($stats['inserts']) ?></h4>
                                <small>নতুন এন্ট্রি</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-info text-white text-center">
                            <div class="card-body">
                                <h4><?= number_format($stats['updates']) ?></h4>
                                <small>আপডেট</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-danger text-white text-center">
                            <div class="card-body">
                                <h4><?= number_format($stats['deletes']) ?></h4>
                                <small>মুছে ফেলা</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-warning text-dark text-center">
                            <div class="card-body">
                                <h4><?= number_format($stats['unique_users']) ?></h4>
                                <small>ব্যবহারকারী</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-secondary text-white text-center">
                            <div class="card-body">
                                <h4><?= number_format($stats['last_7_days']) ?></h4>
                                <small>গত ৭ দিন</small>
                            </div>
                        </div>
                    </div>
                </div>
                
                <!-- Filters -->
                <div class="card mb-4">
                    <div class="card-header">
                        <h5 class="mb-0"><i class="fas fa-filter"></i> ফিল্টার</h5>
                    </div>
                    <div class="card-body">
                        <form method="GET" class="row">
                            <div class="col-md-2 mb-3">
                                <label class="form-label">অ্যাকশন</label>
                                <select name="action" class="form-select">
                                    <option value="">সব</option>
                                    <option value="INSERT" <?= $filter_action == 'INSERT' ? 'selected' : '' ?>>নতুন এন্ট্রি</option>
                                    <option value="UPDATE" <?= $filter_action == 'UPDATE' ? 'selected' : '' ?>>আপডেট</option>
                                    <option value="DELETE" <?= $filter_action == 'DELETE' ? 'selected' : '' ?>>মুছে ফেলা</option>
                                    <option value="LOGIN" <?= $filter_action == 'LOGIN' ? 'selected' : '' ?>>লগইন</option>
                                    <option value="LOGOUT" <?= $filter_action == 'LOGOUT' ? 'selected' : '' ?>>লগআউট</option>
                                </select>
                            </div>
                            <div class="col-md-2 mb-3">
                                <label class="form-label">টেবিল</label>
                                <select name="table" class="form-select">
                                    <option value="">সব</option>
                                    <?php foreach ($tables as $table): ?>
                                    <option value="<?= $table ?>" <?= $filter_table == $table ? 'selected' : '' ?>><?= $table ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-2 mb-3">
                                <label class="form-label">ব্যবহারকারী</label>
                                <select name="user" class="form-select">
                                    <option value="">সব</option>
                                    <?php foreach ($users as $user): ?>
                                    <option value="<?= $user['user_id'] ?>" <?= $filter_user == $user['user_id'] ? 'selected' : '' ?>>
                                        <?= htmlspecialchars($user['username'] ?? 'Unknown') ?>
                                    </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-3 mb-3">
                                <label class="form-label">তারিখ থেকে</label>
                                <input type="date" name="date_from" class="form-control" value="<?= $filter_date_from ?>">
                            </div>
                            <div class="col-md-3 mb-3">
                                <label class="form-label">তারিখ পর্যন্ত</label>
                                <input type="date" name="date_to" class="form-control" value="<?= $filter_date_to ?>">
                            </div>
                            <div class="col-md-12">
                                <button type="submit" class="btn btn-primary">
                                    <i class="fas fa-search"></i> ফিল্টার করুন
                                </button>
                                <a href="?" class="btn btn-secondary">
                                    <i class="fas fa-undo"></i> রিসেট
                                </a>
                            </div>
                        </form>
                    </div>
                </div>
                
                <!-- Audit Log Table -->
                <div class="card">
                    <div class="card-header d-flex justify-content-between align-items-center">
                        <h5 class="mb-0">📋 অডিট লগ</h5>
                        <small class="text-muted">পেজ <?= $page ?> এর <?= $total_pages ?> (মোট <?= $total_records ?> লগ)</small>
                    </div>
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-striped table-hover mb-0">
                                <thead class="table-dark">
                                    <tr>
                                        <th>সময়</th>
                                        <th>ব্যবহারকারী</th>
                                        <th>অ্যাকশন</th>
                                        <th>টেবিল</th>
                                        <th>রেকর্ড ID</th>
                                        <th>IP ঠিকানা</th>
                                        <th>বিস্তারিত</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($logs as $log): ?>
                                    <tr class="audit-log-row">
                                        <td nowrap>
                                            <?= date('d M Y', strtotime($log['created_at'])) ?><br>
                                            <small class="text-muted"><?= date('h:i:s A', strtotime($log['created_at'])) ?></small>
                                        </td>
                                        <td>
                                            <strong><?= htmlspecialchars($log['username'] ?? 'System') ?></strong><br>
                                            <small class="badge bg-info"><?= $log['user_role'] ?></small>
                                        </td>
                                        <td>
                                            <span class="badge bg-<?= $log['action'] == 'INSERT' ? 'success' : ($log['action'] == 'UPDATE' ? 'warning' : ($log['action'] == 'DELETE' ? 'danger' : 'primary')) ?>">
                                                <?= $log['action'] ?>
                                            </span>
                                        </td>
                                        <td><?= $log['table_name'] ?? '-' ?></td>
                                        <td><?= $log['record_id'] ?? '-' ?></td>
                                        <td><small><?= $log['ip_address'] ?></small></td>
                                        <td>
                                            <button class="btn btn-sm btn-outline-info" onclick="showDetails('<?= $log['id'] ?>')">
                                                <i class="fas fa-eye"></i> দেখুন
                                            </button>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                    
                                    <?php if (count($logs) === 0): ?>
                                    <tr>
                                        <td colspan="7" class="text-center py-5">
                                            <?php if ($logs_error): ?>
                                                <div class="alert alert-warning mb-0">
                                                    <i class="fas fa-exclamation-triangle fa-2x mb-2"></i><br>
                                                    <?= $logs_error ?>
                                                </div>
                                            <?php else: ?>
                                                <i class="fas fa-inbox fa-2x mb-2 text-muted"></i><br>
                                                <span class="text-muted">কোনো অডিট লগ পাওয়া যায়নি</span>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    
                    <!-- Pagination -->
                    <?php if ($total_pages > 1): ?>
                    <div class="card-footer">
                        <nav>
                            <ul class="pagination justify-content-center mb-0">
                                <li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>">
                                    <a class="page-link" href="?page=<?= $page-1 ?>&<?= http_build_query(array_diff_key($_GET, ['page' => ''])) ?>">পূর্ববর্তী</a>
                                </li>
                                <?php for ($i = max(1, $page-2); $i <= min($total_pages, $page+2); $i++): ?>
                                <li class="page-item <?= $i == $page ? 'active' : '' ?>">
                                    <a class="page-link" href="?page=<?= $i ?>&<?= http_build_query(array_diff_key($_GET, ['page' => ''])) ?>"><?= $i ?></a>
                                </li>
                                <?php endfor; ?>
                                <li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
                                    <a class="page-link" href="?page=<?= $page+1 ?>&<?= http_build_query(array_diff_key($_GET, ['page' => ''])) ?>">পরবর্তী</a>
                                </li>
                            </ul>
                        </nav>
                    </div>
                    <?php endif; ?>
                </div>
                
            </main>
        </div>
    </div>
    
    <!-- Log Details Modal -->
    <div class="modal fade" id="detailsModal" tabindex="-1">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">অডিট লগ বিস্তারিত</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body" id="modalContent">
                    Loading...
                </div>
            </div>
        </div>
    </div>
    
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function showDetails(logId) {
            // Fetch and display log details
            fetch('get_log_details.php?id=' + logId)
                .then(response => response.json())
                .then(data => {
                    let html = `
                        <table class="table table-bordered">
                            <tr><td><strong>Action:</strong></td><td>${data.action}</td></tr>
                            <tr><td><strong>Table:</strong></td><td>${data.table_name || '-'}</td></tr>
                            <tr><td><strong>Record ID:</strong></td><td>${data.record_id || '-'}</td></tr>
                            <tr><td><strong>User:</strong></td><td>${data.username || 'System'} (${data.user_role})</td></tr>
                            <tr><td><strong>Time:</strong></td><td>${data.created_at}</td></tr>
                            <tr><td><strong>IP Address:</strong></td><td>${data.ip_address}</td></tr>
                            <tr><td><strong>User Agent:</strong></td><td><small>${data.user_agent || '-'}</small></td></tr>
                        </table>
                    `;
                    
                    if (data.old_values) {
                        html += '<h6>Old Values:</h6><pre class="bg-light p-2">' + JSON.stringify(JSON.parse(data.old_values), null, 2) + '</pre>';
                    }
                    if (data.new_values) {
                        html += '<h6>New Values:</h6><pre class="bg-light p-2">' + JSON.stringify(JSON.parse(data.new_values), null, 2) + '</pre>';
                    }
                    
                    document.getElementById('modalContent').innerHTML = html;
                    new bootstrap.Modal(document.getElementById('detailsModal')).show();
                });
        }
        
        function archiveOldLogs() {
            if (confirm('আপনি কি ৩০ দিনের পুরানো লগইন প্রচেষ্টা আর্কাইভ করতে চান?')) {
                window.location.href = 'archive_login_attempts.php';
            }
        }
    </script>
</body>
</html>
