<?php

declare(strict_types=1);

namespace App\Modules\Guest;

use App\Core\Database;

/**
 * GuestModel — Database operations for Guest Management.
 *
 * Scopes all guest reads and writes to the workspace of the authenticated couple.
 * Ensures parameterized PDO queries for SQL injection prevention.
 *
 * @package Inveetaire
 * @ref     PILOT-002
 */
class GuestModel
{
    /**
     * Check if a guest with the same name already exists in the workspace.
     *
     * @param int    $workspaceId
     * @param string $fullName
     * @return bool
     */
    public function guestExistsByName(int $workspaceId, string $fullName, ?int $excludeGuestId = null): bool
    {
        $db = Database::getConnection();
        $sql = '
            SELECT id 
            FROM guests 
            WHERE workspace_id = :workspace_id 
              AND LOWER(TRIM(full_name)) = LOWER(TRIM(:full_name)) 
              AND is_deleted = 0
        ';
        $params = [
            'workspace_id' => $workspaceId,
            'full_name'    => $fullName
        ];
        if ($excludeGuestId !== null) {
            $sql .= ' AND id != :exclude_id';
            $params['exclude_id'] = $excludeGuestId;
        }
        $sql .= ' LIMIT 1';
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch() !== false;
    }

    /**
     * Check if a guest code (token) already exists.
     *
     * @param int    $workspaceId
     * @param string $guestCode
     * @return bool
     */
    public function guestCodeExists(int $workspaceId, string $guestCode): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id 
            FROM guests 
            WHERE workspace_id = :workspace_id 
              AND guest_code = :guest_code 
            LIMIT 1
        ');
        $stmt->execute([
            'workspace_id' => $workspaceId,
            'guest_code'   => $guestCode
        ]);
        return $stmt->fetch() !== false;
    }

    /**
     * Insert a new guest record.
     *
     * @param int   $workspaceId
     * @param array $data
     * @return int Inserted Guest ID
     */
    public function insertGuest(int $workspaceId, array $data): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO guests (
                workspace_id, guest_code, honorific, full_name, side, guest_group, 
                invited_pax, phone_number, notes, invitation_status, 
                rsvp_status, checkin_status, is_deleted
            ) VALUES (
                :workspace_id, :guest_code, :honorific, :full_name, :side, :guest_group, 
                :invited_pax, :phone_number, :notes, \'not_sent\', 
                \'pending\', \'not_arrived\', 0
            )
        ');

        $stmt->execute([
            'workspace_id' => $workspaceId,
            'guest_code'   => $data['guest_code'],
            'honorific'    => !empty($data['honorific']) ? $data['honorific'] : null,
            'full_name'    => $data['full_name'],
            'side'         => $data['side'] ?? 'couple',
            'guest_group'  => $data['guest_group'] ?? 'others',
            'invited_pax'  => (int) ($data['invited_pax'] ?? 1),
            'phone_number' => !empty($data['phone_number']) ? $data['phone_number'] : null,
            'notes'        => !empty($data['notes']) ? $data['notes'] : null
        ]);

        return (int) $db->lastInsertId();
    }

    /**
     * Get paginated guests with filters and search.
     *
     * @param int   $workspaceId
     * @param array $filters
     * @param int   $limit
     * @param int   $offset
     * @return array
     */
    public function getGuests(int $workspaceId, array $filters = [], int $limit = 10, int $offset = 0): array
    {
        $db = Database::getConnection();
        
        $sql = '
            SELECT g.*, r.response AS rsvp_response, r.confirmed_pax, r.message AS rsvp_message, r.submitted_at AS rsvp_submitted_at, r.updated_at AS rsvp_updated_at 
            FROM guests g
            LEFT JOIN rsvps r ON g.id = r.guest_id
            WHERE g.workspace_id = :workspace_id AND g.is_deleted = 0
        ';
        $params = ['workspace_id' => $workspaceId];
        
        if (!empty($filters['search'])) {
            $sql .= ' AND (g.full_name LIKE :search1 OR g.phone_number LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['group'])) {
            $sql .= ' AND g.guest_group = :group';
            $params['group'] = $filters['group'];
        }
        if (!empty($filters['rsvp'])) {
            $sql .= ' AND g.rsvp_status = :rsvp';
            $params['rsvp'] = $filters['rsvp'];
        }
        if (!empty($filters['attendance'])) {
            $sql .= ' AND g.checkin_status = :attendance';
            $params['attendance'] = $filters['attendance'];
        }
        
        $sql .= ' ORDER BY g.full_name ASC LIMIT :limit OFFSET :offset';
        
        $stmt = $db->prepare($sql);
        
        foreach ($params as $key => $val) {
            $stmt->bindValue(':' . $key, $val);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Count guests matching criteria.
     *
     * @param int   $workspaceId
     * @param array $filters
     * @return int
     */
    public function getGuestsCount(int $workspaceId, array $filters = []): int
    {
        $db = Database::getConnection();
        
        $sql = 'SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0';
        $params = ['workspace_id' => $workspaceId];
        
        if (!empty($filters['search'])) {
            $sql .= ' AND (full_name LIKE :search1 OR phone_number LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['group'])) {
            $sql .= ' AND guest_group = :group';
            $params['group'] = $filters['group'];
        }
        if (!empty($filters['rsvp'])) {
            $sql .= ' AND rsvp_status = :rsvp';
            $params['rsvp'] = $filters['rsvp'];
        }
        if (!empty($filters['attendance'])) {
            $sql .= ' AND checkin_status = :attendance';
            $params['attendance'] = $filters['attendance'];
        }
        
        $stmt = $db->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(':' . $key, $val);
        }
        $stmt->execute();
        return (int) $stmt->fetchColumn();
    }

    /**
     * Retrieve single guest.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return array|null
     */
    public function getGuestById(int $workspaceId, int $guestId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT * FROM guests WHERE id = :guest_id AND workspace_id = :workspace_id AND is_deleted = 0 LIMIT 1');
        $stmt->execute(['guest_id' => $guestId, 'workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Update existing guest details.
     *
     * @param int   $workspaceId
     * @param int   $guestId
     * @param array $data
     * @return void
     */
    public function updateGuest(int $workspaceId, int $guestId, array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE guests 
            SET honorific = :honorific,
                full_name = :full_name,
                phone_number = :phone_number,
                guest_group = :guest_group,
                invited_pax = :invited_pax,
                side = :side,
                notes = :notes,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :id AND workspace_id = :workspace_id
        ');
        $stmt->execute([
            'honorific'    => !empty($data['honorific']) ? $data['honorific'] : null,
            'full_name'    => $data['full_name'],
            'phone_number' => !empty($data['phone_number']) ? $data['phone_number'] : null,
            'guest_group'  => $data['guest_group'] ?? 'others',
            'invited_pax'  => (int) ($data['invited_pax'] ?? 1),
            'side'         => $data['side'] ?? 'couple',
            'notes'        => !empty($data['notes']) ? $data['notes'] : null,
            'id'           => $guestId,
            'workspace_id' => $workspaceId
        ]);
    }

    /**
     * Soft delete guest.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return void
     */
    public function deleteGuest(int $workspaceId, int $guestId): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE guests 
            SET is_deleted = 1, 
                deleted_at = CURRENT_TIMESTAMP, 
                updated_at = CURRENT_TIMESTAMP 
            WHERE id = :id AND workspace_id = :workspace_id
        ');
        $stmt->execute(['id' => $guestId, 'workspace_id' => $workspaceId]);
    }

    /**
     * Check if phone number is already registered in workspace.
     *
     * @param int      $workspaceId
     * @param string   $phone
     * @param int|null $excludeGuestId
     * @return bool
     */
    public function guestExistsByPhone(int $workspaceId, string $phone, ?int $excludeGuestId = null): bool
    {
        $db = Database::getConnection();
        $sql = 'SELECT id FROM guests WHERE workspace_id = :workspace_id AND phone_number = :phone AND is_deleted = 0';
        $params = [
            'workspace_id' => $workspaceId,
            'phone'        => $phone
        ];
        
        if ($excludeGuestId !== null) {
            $sql .= ' AND id != :exclude_id';
            $params['exclude_id'] = $excludeGuestId;
        }
        
        $sql .= ' LIMIT 1';
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch() !== false;
    }

    /**
     * Get distinct guest categories.
     *
     * @param int $workspaceId
     * @return array
     */
    public function getDistinctGroups(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT DISTINCT guest_group FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 ORDER BY guest_group ASC');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }

    /**
     * Get wedding_event_ids that this guest is invited to.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return array
     */
    public function getGuestEventIds(int $workspaceId, int $guestId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT wedding_event_id FROM guest_events WHERE guest_id = :guest_id AND workspace_id = :workspace_id');
        $stmt->execute(['guest_id' => $guestId, 'workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }

    /**
     * Get mapped event assignments [wedding_event_id => event_session_id|null] for a guest.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return array<int, int|null>
     */
    public function getGuestEventAssignments(int $workspaceId, int $guestId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT wedding_event_id, event_session_id FROM guest_events WHERE guest_id = :guest_id AND workspace_id = :workspace_id');
        $stmt->execute(['guest_id' => $guestId, 'workspace_id' => $workspaceId]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $assignments = [];
        foreach ($rows as $r) {
            $assignments[(int) $r['wedding_event_id']] = $r['event_session_id'] !== null ? (int) $r['event_session_id'] : null;
        }

        return $assignments;
    }

    /**
     * Synchronize guest event access and session assignment in guest_events mapping table.
     *
     * @param int   $workspaceId
     * @param int   $guestId
     * @param array $eventIds        Array of selected wedding_event_ids
     * @param array $eventSessions   Mapping array [wedding_event_id => event_session_id]
     * @return void
     */
    public function saveGuestEvents(int $workspaceId, int $guestId, array $eventIds, array $eventSessions = []): void
    {
        $db = Database::getConnection();
        
        $stmtDel = $db->prepare('DELETE FROM guest_events WHERE guest_id = :guest_id AND workspace_id = :workspace_id');
        $stmtDel->execute(['guest_id' => $guestId, 'workspace_id' => $workspaceId]);
        
        if (!empty($eventIds)) {
            $stmtInsert = $db->prepare('
                INSERT INTO guest_events (guest_id, wedding_event_id, event_session_id, workspace_id, rsvp_status) 
                VALUES (:guest_id, :event_id, :session_id, :workspace_id, "pending")
            ');
            foreach ($eventIds as $eventId) {
                $eId = (int) $eventId;
                $sessionId = isset($eventSessions[$eId]) && (int) $eventSessions[$eId] > 0 ? (int) $eventSessions[$eId] : null;

                $stmtInsert->execute([
                    'guest_id'     => $guestId,
                    'event_id'     => $eId,
                    'session_id'   => $sessionId,
                    'workspace_id' => $workspaceId
                ]);
            }
        }
    }

    /**
     * Get ceremony attendance matrix for all guests in workspace.
     *
     * Returns: [guest_id => [wedding_event_id => ['is_invited' => bool, 'is_checked_in' => bool, 'checked_in_at' => string|null]]]
     *
     * @param int $workspaceId
     * @return array
     */
    public function getAttendanceMatrixForWorkspace(int $workspaceId): array
    {
        $db = Database::getConnection();

        $stmtEvents = $db->prepare('
            SELECT guest_id, wedding_event_id 
            FROM guest_events 
            WHERE workspace_id = :workspace_id
        ');
        $stmtEvents->execute(['workspace_id' => $workspaceId]);
        $invitations = $stmtEvents->fetchAll(\PDO::FETCH_ASSOC);

        $stmtCheckins = $db->prepare('
            SELECT guest_id, wedding_event_id, checked_in_at 
            FROM checkins 
            WHERE workspace_id = :workspace_id
        ');
        $stmtCheckins->execute(['workspace_id' => $workspaceId]);
        $checkins = $stmtCheckins->fetchAll(\PDO::FETCH_ASSOC);

        $matrix = [];

        foreach ($invitations as $inv) {
            $gId = (int) $inv['guest_id'];
            $eId = (int) $inv['wedding_event_id'];
            if (!isset($matrix[$gId])) {
                $matrix[$gId] = [];
            }
            $matrix[$gId][$eId] = [
                'is_invited'    => true,
                'is_checked_in' => false,
                'checked_in_at' => null
            ];
        }

        foreach ($checkins as $chk) {
            $gId = (int) $chk['guest_id'];
            $eId = (int) $chk['wedding_event_id'];
            if (!isset($matrix[$gId])) {
                $matrix[$gId] = [];
            }
            if (!isset($matrix[$gId][$eId])) {
                $matrix[$gId][$eId] = [
                    'is_invited'    => true,
                    'is_checked_in' => true,
                    'checked_in_at' => $chk['checked_in_at']
                ];
            } else {
                $matrix[$gId][$eId]['is_checked_in'] = true;
                $matrix[$gId][$eId]['checked_in_at'] = $chk['checked_in_at'];
            }
        }

        return $matrix;
    }

    /**
     * Get paginated guests filtered by a specific wedding event.
     *
     * @param int   $workspaceId
     * @param int   $eventId
     * @param array $filters
     * @param int   $limit
     * @param int   $offset
     * @return array
     */
    public function getGuestsForEvent(int $workspaceId, int $eventId, array $filters = [], int $limit = 10, int $offset = 0): array
    {
        $db = Database::getConnection();
        
        $sql = '
            SELECT g.*, r.response AS rsvp_response, r.confirmed_pax, r.message AS rsvp_message, r.submitted_at AS rsvp_submitted_at, r.updated_at AS rsvp_updated_at
            FROM guests g 
            INNER JOIN guest_events ge ON g.id = ge.guest_id
            LEFT JOIN rsvps r ON g.id = r.guest_id
            WHERE g.workspace_id = :workspace_id 
              AND ge.wedding_event_id = :event_id 
              AND g.is_deleted = 0
        ';
        $params = [
            'workspace_id' => $workspaceId,
            'event_id'     => $eventId
        ];
        
        if (!empty($filters['search'])) {
            $sql .= ' AND (g.full_name LIKE :search1 OR g.phone_number LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['group'])) {
            $sql .= ' AND g.guest_group = :group';
            $params['group'] = $filters['group'];
        }
        if (!empty($filters['rsvp'])) {
            $sql .= ' AND g.rsvp_status = :rsvp';
            $params['rsvp'] = $filters['rsvp'];
        }
        if (!empty($filters['attendance'])) {
            $sql .= ' AND g.checkin_status = :attendance';
            $params['attendance'] = $filters['attendance'];
        }
        
        $sql .= ' ORDER BY g.full_name ASC LIMIT :limit OFFSET :offset';
        
        $stmt = $db->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(':' . $key, $val);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Count guests filtered by a specific wedding event.
     *
     * @param int   $workspaceId
     * @param int   $eventId
     * @param array $filters
     * @return int
     */
    public function getGuestsCountForEvent(int $workspaceId, int $eventId, array $filters = []): int
    {
        $db = Database::getConnection();
        
        $sql = '
            SELECT COUNT(*) 
            FROM guests g 
            INNER JOIN guest_events ge ON g.id = ge.guest_id
            WHERE g.workspace_id = :workspace_id 
              AND ge.wedding_event_id = :event_id 
              AND g.is_deleted = 0
        ';
        $params = [
            'workspace_id' => $workspaceId,
            'event_id'     => $eventId
        ];
        
        if (!empty($filters['search'])) {
            $sql .= ' AND (g.full_name LIKE :search1 OR g.phone_number LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['group'])) {
            $sql .= ' AND g.guest_group = :group';
            $params['group'] = $filters['group'];
        }
        if (!empty($filters['rsvp'])) {
            $sql .= ' AND g.rsvp_status = :rsvp';
            $params['rsvp'] = $filters['rsvp'];
        }
        if (!empty($filters['attendance'])) {
            $sql .= ' AND g.checkin_status = :attendance';
            $params['attendance'] = $filters['attendance'];
        }
        
        $stmt = $db->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(':' . $key, $val);
        }
        
        $stmt->execute();
        return (int) $stmt->fetchColumn();
    }

    /**
     * Record a guest opening their invitation link.
     * Updates first_opened_at (if NULL), last_opened_at, view_count, and invitation_status.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return void
     */
    public function recordInvitationOpen(int $workspaceId, int $guestId): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE guests 
            SET first_opened_at = COALESCE(first_opened_at, CURRENT_TIMESTAMP),
                last_opened_at = CURRENT_TIMESTAMP,
                view_count = view_count + 1,
                invitation_status = CASE 
                    WHEN invitation_status IN (\'not_sent\', \'sent\') THEN \'opened\' 
                    ELSE invitation_status 
                END,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :guest_id 
              AND workspace_id = :workspace_id 
              AND is_deleted = 0
        ');
        $stmt->execute([
            'guest_id'     => $guestId,
            'workspace_id' => $workspaceId
        ]);
    }

    /**
     * Compute invitation delivery statistics.
     *
     * @param int $workspaceId
     * @return array
     */
    public function getInvitationDeliveryStats(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT 
                COUNT(*) as total_guests,
                SUM(CASE WHEN (first_opened_at IS NOT NULL OR view_count > 0 OR invitation_status = \'opened\') THEN 1 ELSE 0 END) as opened_guests
            FROM guests 
            WHERE workspace_id = :workspace_id 
              AND is_deleted = 0
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

        $total = (int) ($row['total_guests'] ?? 0);
        $opened = (int) ($row['opened_guests'] ?? 0);
        $notOpened = max(0, $total - $opened);
        $openingRate = $total > 0 ? round(($opened / $total) * 100, 1) : 0.0;

        return [
            'total'        => $total,
            'opened'       => $opened,
            'not_opened'   => $notOpened,
            'opening_rate' => $openingRate
        ];
    }

    /**
     * Get paginated guest progress items with filters & search for Couple Dashboard table.
     *
     * @param int   $workspaceId
     * @param array $filters
     * @param int   $limit
     * @param int   $offset
     * @return array
     */
    public function getGuestProgressList(int $workspaceId, array $filters = [], int $limit = 20, int $offset = 0): array
    {
        $db = Database::getConnection();

        $sql = '
            SELECT 
                g.id,
                g.guest_code,
                g.honorific,
                g.full_name,
                g.phone_number,
                g.guest_group,
                g.invited_pax,
                g.invitation_status,
                g.rsvp_status,
                g.first_opened_at,
                g.last_opened_at,
                COALESCE(g.view_count, 0) as view_count,
                r.response AS rsvp_response,
                r.confirmed_pax,
                w.status AS wish_status,
                w.is_visible AS wish_is_visible,
                w.message AS wish_message
            FROM guests g
            LEFT JOIN rsvps r ON g.id = r.guest_id
            LEFT JOIN wishes w ON g.id = w.guest_id
            WHERE g.workspace_id = :workspace_id 
              AND g.is_deleted = 0
        ';
        $params = ['workspace_id' => $workspaceId];

        // Apply filters
        $filterType = $filters['filter'] ?? 'all';
        if ($filterType === 'opened') {
            $sql .= ' AND (g.first_opened_at IS NOT NULL OR g.view_count > 0 OR g.invitation_status = \'opened\')';
        } elseif ($filterType === 'not_opened') {
            $sql .= ' AND (g.first_opened_at IS NULL AND COALESCE(g.view_count, 0) = 0 AND g.invitation_status != \'opened\')';
        } elseif ($filterType === 'rsvp_pending') {
            $sql .= ' AND g.rsvp_status = \'pending\'';
        } elseif ($filterType === 'attending') {
            $sql .= ' AND g.rsvp_status = \'attending\'';
        } elseif ($filterType === 'declined') {
            $sql .= ' AND g.rsvp_status = \'declined\'';
        } elseif ($filterType === 'has_wish') {
            $sql .= ' AND w.id IS NOT NULL';
        } elseif ($filterType === 'no_wish') {
            $sql .= ' AND w.id IS NULL';
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (g.full_name LIKE :search1 OR g.phone_number LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }

        $sql .= ' ORDER BY g.full_name ASC LIMIT :limit OFFSET :offset';

        $stmt = $db->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(':' . $key, $val);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);

        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Count guest progress items for pagination.
     *
     * @param int   $workspaceId
     * @param array $filters
     * @return int
     */
    public function getGuestProgressCount(int $workspaceId, array $filters = []): int
    {
        $db = Database::getConnection();

        $sql = '
            SELECT COUNT(DISTINCT g.id) 
            FROM guests g
            LEFT JOIN rsvps r ON g.id = r.guest_id
            LEFT JOIN wishes w ON g.id = w.guest_id
            WHERE g.workspace_id = :workspace_id 
              AND g.is_deleted = 0
        ';
        $params = ['workspace_id' => $workspaceId];

        $filterType = $filters['filter'] ?? 'all';
        if ($filterType === 'opened') {
            $sql .= ' AND (g.first_opened_at IS NOT NULL OR g.view_count > 0 OR g.invitation_status = \'opened\')';
        } elseif ($filterType === 'not_opened') {
            $sql .= ' AND (g.first_opened_at IS NULL AND COALESCE(g.view_count, 0) = 0 AND g.invitation_status != \'opened\')';
        } elseif ($filterType === 'rsvp_pending') {
            $sql .= ' AND g.rsvp_status = \'pending\'';
        } elseif ($filterType === 'attending') {
            $sql .= ' AND g.rsvp_status = \'attending\'';
        } elseif ($filterType === 'declined') {
            $sql .= ' AND g.rsvp_status = \'declined\'';
        } elseif ($filterType === 'has_wish') {
            $sql .= ' AND w.id IS NOT NULL';
        } elseif ($filterType === 'no_wish') {
            $sql .= ' AND w.id IS NULL';
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (g.full_name LIKE :search1 OR g.phone_number LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }

        $stmt = $db->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(':' . $key, $val);
        }

        $stmt->execute();
        return (int) $stmt->fetchColumn();
    }
}
