<?php
// 引入資料庫配置
require_once 'config.php';

// 檢查用戶是否已登錄
function isLoggedIn() {
    if (isset($_SESSION['admin_id']) && !empty($_SESSION['admin_id'])) {
        return true;
    }
    return false;
}

// 檢查用戶是否是超級管理員
function isSuperAdmin() {
    global $conn;
    if (isset($_SESSION['admin_id']) && !empty($_SESSION['admin_id'])) {
        $id = (int)$_SESSION['admin_id'];
        $sql = "SELECT role FROM admin WHERE id = $id LIMIT 1";
        $result = $conn->query($sql);
        
        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return $row['role'] === '超級管理員';
        }
    }
    return false;
}

// 重定向函數
function redirect($url) {
    header("Location: $url");
    exit;
}

// 顯示錯誤消息
function showError($message) {
    return "<div class='alert alert-danger'>$message</div>";
}

// 顯示成功消息
function showSuccess($message) {
    return "<div class='alert alert-success'>$message</div>";
}

// 分頁函數
function getPagination($total, $perPage, $currentPage, $url) {
    $totalPages = ceil($total / $perPage);
    
    if ($totalPages <= 1) {
        return '';
    }
    
    $pagination = '<div class="pagination">';
    
    // 上一頁
    if ($currentPage > 1) {
        $pagination .= '<a href="' . $url . '&page=' . ($currentPage - 1) . '">&laquo;</a>';
    } else {
        $pagination .= '<a class="disabled">&laquo;</a>';
    }
    
    // 頁碼
    $startPage = max(1, $currentPage - 2);
    $endPage = min($totalPages, $currentPage + 2);
    
    for ($i = $startPage; $i <= $endPage; $i++) {
        if ($i == $currentPage) {
            $pagination .= '<a class="active">' . $i . '</a>';
        } else {
            $pagination .= '<a href="' . $url . '&page=' . $i . '">' . $i . '</a>';
        }
    }
    
    // 下一頁
    if ($currentPage < $totalPages) {
        $pagination .= '<a href="' . $url . '&page=' . ($currentPage + 1) . '">&raquo;</a>';
    } else {
        $pagination .= '<a class="disabled">&raquo;</a>';
    }
    
    $pagination .= '</div>';
    
    return $pagination;
}

// 上傳圖片函數
function uploadImage($file, $targetDir = '../uploads/photos/') {
    // 檢查上傳錯誤
    if ($file['error'] !== UPLOAD_ERR_OK) {
        return [
            'success' => false,
            'message' => '上傳失敗，錯誤碼: ' . $file['error']
        ];
    }
    
    // 檢查檔案類型
    $allowedTypes = ['image/jpeg', 'image/png', 'image/gif'];
    if (!in_array($file['type'], $allowedTypes)) {
        return [
            'success' => false,
            'message' => '只允許上傳JPG, PNG或GIF圖片'
        ];
    }
    
    // 檢查檔大小 (限制為5MB)
    if ($file['size'] > 5 * 1024 * 1024) {
        return [
            'success' => false,
            'message' => '檔大小不能超過5MB'
        ];
    }
    
    // 創建目標目錄（如果不存在）
    if (!file_exists($targetDir)) {
        mkdir($targetDir, 0777, true);
    }
    
    // 生成唯一檔案名
    $fileExtension = pathinfo($file['name'], PATHINFO_EXTENSION);
    $newFileName = uniqid() . '_' . time() . '.' . $fileExtension;
    $targetPath = $targetDir . $newFileName;
    
    // 移動上傳的文件
    if (move_uploaded_file($file['tmp_name'], $targetPath)) {
        return [
            'success' => true,
            'file_path' => str_replace('../', '', $targetPath)
        ];
    } else {
        return [
            'success' => false,
            'message' => '檔上傳失敗，請重試'
        ];
    }
}

// 獲取即將到期的證件數量
function getExpiringDocumentsCount($conn) {
    $counts = [
        'visitor_permits' => 0,
        'entry_permits' => 0,
        'access_cards' => 0,
        'airport_passes' => 0,
        'restricted_area_passes' => 0,
        'vehicle_restricted_passes' => 0,
        'total' => 0
    ];
    
    $tables = [
        'visitor_permits', 
        'entry_permits', 
        'access_cards', 
        'airport_passes', 
        'restricted_area_passes', 
        'vehicle_restricted_passes'
    ];
    
    foreach ($tables as $table) {
        $sql = "SELECT COUNT(*) as count FROM $table WHERE status = '即將到期'";
        $result = $conn->query($sql);
        if ($result && $row = $result->fetch_assoc()) {
            $counts[$table] = (int)$row['count'];
            $counts['total'] += $counts[$table];
        }
    }
    
    return $counts;
}

// 獲取已過期的證件數量
function getExpiredDocumentsCount($conn) {
    $counts = [
        'visitor_permits' => 0,
        'entry_permits' => 0,
        'access_cards' => 0,
        'airport_passes' => 0,
        'restricted_area_passes' => 0,
        'vehicle_restricted_passes' => 0,
        'total' => 0
    ];
    
    $tables = [
        'visitor_permits', 
        'entry_permits', 
        'access_cards', 
        'airport_passes', 
        'restricted_area_passes', 
        'vehicle_restricted_passes'
    ];
    
    foreach ($tables as $table) {
        $sql = "SELECT COUNT(*) as count FROM $table WHERE status = '已過期'";
        $result = $conn->query($sql);
        if ($result && $row = $result->fetch_assoc()) {
            $counts[$table] = (int)$row['count'];
            $counts['total'] += $counts[$table];
        }
    }
    
    return $counts;
}

// 獲取證件總數
function getTotalDocumentsCount($conn) {
    $counts = [
        'visitor_permits' => 0,
        'entry_permits' => 0,
        'access_cards' => 0,
        'airport_passes' => 0,
        'restricted_area_passes' => 0,
        'vehicle_restricted_passes' => 0,
        'total' => 0
    ];
    
    $tables = [
        'visitor_permits', 
        'entry_permits', 
        'access_cards', 
        'airport_passes', 
        'restricted_area_passes', 
        'vehicle_restricted_passes'
    ];
    
    foreach ($tables as $table) {
        $sql = "SELECT COUNT(*) as count FROM $table";
        $result = $conn->query($sql);
        if ($result && $row = $result->fetch_assoc()) {
            $counts[$table] = (int)$row['count'];
            $counts['total'] += $counts[$table];
        }
    }
    
    return $counts;
}

// 獲取人員數量
function getPersonnelCount($conn) {
    $sql = "SELECT COUNT(*) as count FROM personnel";
    $result = $conn->query($sql);
    if ($result && $row = $result->fetch_assoc()) {
        return (int)$row['count'];
    }
    return 0;
}

// 格式化日期顯示
function formatDate($date) {
    if (empty($date) || $date == '0000-00-00') {
        return '';
    }
    return date('Y-m-d', strtotime($date));
}

// 獲取證件狀態CSS類
function getStatusClass($status) {
    switch ($status) {
        case '有效':
            return 'status-success';
        case '即將到期':
            return 'status-pending';
        case '已過期':
            return 'status-failed';
        case '已歸還':
            return 'status-secondary';
        default:
            return '';
    }
}

// 檢查並更新所有證件狀態
function checkAndUpdateAllDocumentStatus($conn) {
    updateDocumentStatus($conn, 'visitor_permits');
    updateDocumentStatus($conn, 'entry_permits');
    updateDocumentStatus($conn, 'access_cards');
    updateDocumentStatus($conn, 'airport_passes');
    updateDocumentStatus($conn, 'restricted_area_passes');
    updateDocumentStatus($conn, 'vehicle_restricted_passes');
}

// 獲取人員資訊
function getPersonnelById($conn, $id) {
    $id = (int)$id;
    $sql = "SELECT * FROM personnel WHERE id = $id LIMIT 1";
    $result = $conn->query($sql);
    
    if ($result && $result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    
    return null;
}

// 獲取人員的所有證件資訊
function getPersonnelDocuments($conn, $personnelId) {
    $personnelId = (int)$personnelId;
    $documents = [
        'visitor_permits' => [],
        'entry_permits' => [],
        'access_cards' => [],
        'airport_passes' => [],
        'restricted_area_passes' => [],
        'vehicle_restricted_passes' => []
    ];
    
    // 獲取訪客許可證
    $sql = "SELECT * FROM visitor_permits WHERE personnel_id = $personnelId ORDER BY expiry_date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $documents['visitor_permits'][] = $row;
        }
    }
    
    // 獲取進入許可證
    $sql = "SELECT * FROM entry_permits WHERE personnel_id = $personnelId ORDER BY expiry_date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $documents['entry_permits'][] = $row;
        }
    }
    
    // 獲取門禁卡
    $sql = "SELECT * FROM access_cards WHERE personnel_id = $personnelId ORDER BY expiry_date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $documents['access_cards'][] = $row;
        }
    }
    
    // 獲取機場證
    $sql = "SELECT * FROM airport_passes WHERE personnel_id = $personnelId ORDER BY expiry_date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $documents['airport_passes'][] = $row;
        }
    }
    
    // 獲取禁區證
    $sql = "SELECT * FROM restricted_area_passes WHERE personnel_id = $personnelId ORDER BY expiry_date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $documents['restricted_area_passes'][] = $row;
        }
    }
    
    // 獲取車輛禁區證
    $sql = "SELECT * FROM vehicle_restricted_passes WHERE personnel_id = $personnelId ORDER BY expiry_date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $documents['vehicle_restricted_passes'][] = $row;
        }
    }
    
    return $documents;
}

// 搜索人員
function searchPersonnel($conn, $keyword, $page = 1, $perPage = 10) {
    $keyword = sanitize($conn, $keyword);
    $offset = ($page - 1) * $perPage;
    
    // 計算總記錄數
    $countSql = "SELECT COUNT(*) as total FROM personnel 
                WHERE english_name LIKE '%$keyword%' 
                OR chinese_name LIKE '%$keyword%' 
                OR chinese_code LIKE '%$keyword%' 
                OR nickname LIKE '%$keyword%' 
                OR phone LIKE '%$keyword%' 
                OR id_passport LIKE '%$keyword%'";
    $countResult = $conn->query($countSql);
    $totalRecords = 0;
    
    if ($countResult && $row = $countResult->fetch_assoc()) {
        $totalRecords = (int)$row['total'];
    }
    
    // 獲取分頁資料
    $sql = "SELECT * FROM personnel 
            WHERE english_name LIKE '%$keyword%' 
            OR chinese_name LIKE '%$keyword%' 
            OR chinese_code LIKE '%$keyword%' 
            OR nickname LIKE '%$keyword%' 
            OR phone LIKE '%$keyword%' 
            OR id_passport LIKE '%$keyword%'
            ORDER BY id DESC
            LIMIT $offset, $perPage";
    
    $result = $conn->query($sql);
    $data = [];
    
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
    }
    
    return [
        'total' => $totalRecords,
        'data' => $data
    ];
}

// 獲取所有即將到期的證件
function getAllExpiringDocuments($conn) {
    $expiringDocs = [];
    
    // 訪客許可證
    $sql = "SELECT vp.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM visitor_permits vp 
            JOIN personnel p ON vp.personnel_id = p.id 
            WHERE vp.status = '即將到期'
            ORDER BY vp.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '訪客許可證';
            $row['table'] = 'visitor_permits';
            $expiringDocs[] = $row;
        }
    }
    
    // 進入許可證
    $sql = "SELECT ep.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM entry_permits ep 
            JOIN personnel p ON ep.personnel_id = p.id 
            WHERE ep.status = '即將到期'
            ORDER BY ep.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '進入許可證';
            $row['table'] = 'entry_permits';
            $expiringDocs[] = $row;
        }
    }
    
    // 門禁卡
    $sql = "SELECT ac.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM access_cards ac 
            JOIN personnel p ON ac.personnel_id = p.id 
            WHERE ac.status = '即將到期'
            ORDER BY ac.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '門禁卡';
            $row['table'] = 'access_cards';
            $expiringDocs[] = $row;
        }
    }
    
    // 機場證
    $sql = "SELECT ap.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM airport_passes ap 
            JOIN personnel p ON ap.personnel_id = p.id 
            WHERE ap.status = '即將到期'
            ORDER BY ap.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '機場證';
            $row['table'] = 'airport_passes';
            $expiringDocs[] = $row;
        }
    }
    
    // 禁區證
    $sql = "SELECT rap.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM restricted_area_passes rap 
            JOIN personnel p ON rap.personnel_id = p.id 
            WHERE rap.status = '即將到期'
            ORDER BY rap.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '禁區證';
            $row['table'] = 'restricted_area_passes';
            $expiringDocs[] = $row;
        }
    }
    
    // 車輛禁區證
    $sql = "SELECT vrp.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM vehicle_restricted_passes vrp 
            JOIN personnel p ON vrp.personnel_id = p.id 
            WHERE vrp.status = '即將到期'
            ORDER BY vrp.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '車輛禁區證';
            $row['table'] = 'vehicle_restricted_passes';
            $expiringDocs[] = $row;
        }
    }
    
    return $expiringDocs;
}

// 獲取所有已過期的證件
function getAllExpiredDocuments($conn) {
    $expiredDocs = [];
    
    // 訪客許可證
    $sql = "SELECT vp.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM visitor_permits vp 
            JOIN personnel p ON vp.personnel_id = p.id 
            WHERE vp.status = '已過期'
            ORDER BY vp.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '訪客許可證';
            $row['table'] = 'visitor_permits';
            $expiredDocs[] = $row;
        }
    }
    
    // 進入許可證
    $sql = "SELECT ep.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM entry_permits ep 
            JOIN personnel p ON ep.personnel_id = p.id 
            WHERE ep.status = '已過期'
            ORDER BY ep.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '進入許可證';
            $row['table'] = 'entry_permits';
            $expiredDocs[] = $row;
        }
    }
    
    // 門禁卡
    $sql = "SELECT ac.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM access_cards ac 
            JOIN personnel p ON ac.personnel_id = p.id 
            WHERE ac.status = '已過期'
            ORDER BY ac.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '門禁卡';
            $row['table'] = 'access_cards';
            $expiredDocs[] = $row;
        }
    }
    
    // 機場證
    $sql = "SELECT ap.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM airport_passes ap 
            JOIN personnel p ON ap.personnel_id = p.id 
            WHERE ap.status = '已過期'
            ORDER BY ap.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '機場證';
            $row['table'] = 'airport_passes';
            $expiredDocs[] = $row;
        }
    }
    
    // 禁區證
    $sql = "SELECT rap.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM restricted_area_passes rap 
            JOIN personnel p ON rap.personnel_id = p.id 
            WHERE rap.status = '已過期'
            ORDER BY rap.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '禁區證';
            $row['table'] = 'restricted_area_passes';
            $expiredDocs[] = $row;
        }
    }
    
    // 車輛禁區證
    $sql = "SELECT vrp.*, p.chinese_name, p.english_name, p.phone, p.email 
            FROM vehicle_restricted_passes vrp 
            JOIN personnel p ON vrp.personnel_id = p.id 
            WHERE vrp.status = '已過期'
            ORDER BY vrp.expiry_date ASC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['type'] = '車輛禁區證';
            $row['table'] = 'vehicle_restricted_passes';
            $expiredDocs[] = $row;
        }
    }
    
    return $expiredDocs;
}
?>
