<?php

function storeNotification($conn_rjllearning,array $data): bool {
    try {

        $stmt = $conn_rjllearning->prepare("
            INSERT INTO  app_notifications_history 
            (user_id, topic_name, date_created, time_created, status_id, workspace, institution, branch)
            VALUES 
            (:user_id, :topic_name, :date_created, :time_created, :status_id, :workspace, :institution, :branch)
        ");

        $stmt->execute([
            ':user_id'      => $data['user_id'],
            ':topic_name'   => $data['topic_name'],
            ':date_created' => $data['date_created'],
            ':time_created' => $data['time_created'],
            ':status_id'    => $data['status_id'] ?? 0,
            ':workspace'    => $data['workspace'] ?? null,
            ':institution'  => $data['institution'] ?? null,
            ':branch'       => $data['branch'] ?? null,
        ]);

        return true;
    } catch (Exception $e) {
        error_log("Error storing notification: " . $e->getMessage());
        return false;
    }
}

function fetchNotifications(
    $conn_rjllearning,
    $user_id,
    $date_created = null,
    $time_created = null,
    $status_id = null,
    $workspace = null,
    $institution = null,
    $branch = null
) {
    try {
        // Step 1: Get the latest read notification (status = 1) ID
        $last_read_query = "SELECT id FROM app_notifications_history 
                            WHERE user_id = ? AND status_id = 1 
                            ORDER BY id DESC LIMIT 1";
        $last_read_stmt = $conn_rjllearning->prepare($last_read_query);
        $last_read_stmt->execute([$user_id]);
        $last_read = $last_read_stmt->fetch(PDO::FETCH_ASSOC);
        $last_read_id = $last_read ? (int)$last_read['id'] : PHP_INT_MAX; // If no read, fetch all
        

        // Step 2: Build fetch query for status = 0 and id < last_read_id
        $query = "SELECT * FROM app_notifications_history WHERE user_id = ? ";
        $params = [$user_id];

        // if ($date_created !== null) {
        //     $query .= " AND date_created = ?";
        //     $params[] = $date_created;
        // }

        // if ($time_created !== null) {
        //     $query .= " AND time_created = ?";
        //     $params[] = $time_created;
        // }

        // if ($status_id !== null) {
        //     $query .= " AND status_id = ?";
        //     $params[] = $status_id;
        // }

        // if ($workspace !== null) {
        //     $query .= " AND workspace = ?";
        //     $params[] = $workspace;
        // }

        if ($institution !== null) {
            $query .= " AND institution = ?";
            $params[] = $institution;
        }

        if ($branch !== null) {
            $query .= " AND branch = ?";
            $params[] = $branch;
        }

        $query .= " ORDER BY id DESC";

        $stmt = $conn_rjllearning->prepare($query);
        $stmt->execute($params);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Step 3: Update those notifications to status = 1
        if (!empty($results)) {
            $ids = array_column($results, 'id');
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $updateQuery = "UPDATE app_notifications_history SET status_id = 1 WHERE id IN ($placeholders)";
            $updateStmt = $conn_rjllearning->prepare($updateQuery);
            $updateStmt->execute($ids);
        }

        // Step 4: Return the results
        return [
            'total' => count($results),
            'min_read_id' => $last_read_id ?? null,
            'max_read_id' => !empty($results) ? max(array_column($results, 'id')) : null,
            'data' => $results
        ];

    } catch (Exception $e) {
        error_log("Error fetching notifications: " . $e->getMessage());
        return [
            'total' => 0,
            'min_read_id' => null,
            'max_read_id' => null,
            'data' => []
        ];
    }
}




function getNotificationCountByUserId($conn_rjllearning, $user_id) {
    try {
        $query = "
            SELECT COUNT(*) as total_notifications
            FROM app_notifications_history
            WHERE user_id = :user_id
        ";

        $stmt = $conn_rjllearning->prepare($query);
        $stmt->execute([':user_id' => $user_id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return [
            'user_id' => $user_id,
            'total_notifications' => $result ? (int)$result['total_notifications'] : 0
        ];

    } catch (Exception $e) {
        error_log("Error fetching user notification count: " . $e->getMessage());
        return [
            'user_id' => $user_id,
            'total_notifications' => 0
        ];
    }
}

?>
