<?php

declare(strict_types=1);

namespace App\Modules\Invitation;

use App\Core\Database;

/**
 * InvitationModel — Database interaction logic for the Invitation module.
 *
 * Scopes all reads and writes to the workspace of the authenticated couple.
 * Ensures parameterized queries for SQL injection prevention.
 *
 * @package Inveetaire
 * @ref     PILOT-001, RC001-B03
 */
class InvitationModel
{
    /**
     * Get the workspace configuration details.
     *
     * @param int $workspaceId
     * @return array|null
     */
    public function getWorkspace(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT id, slug, groom_name, bride_name, wedding_date, activated_at, workspace_status FROM workspaces WHERE id = :workspace_id LIMIT 1');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve invitation by workspace ID.
     *
     * @param int $workspaceId
     * @return array|null
     */
    public function getInvitationByWorkspace(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT i.*, w.wedding_date 
            FROM invitations i
            JOIN workspaces w ON i.workspace_id = w.id
            WHERE i.workspace_id = :workspace_id 
            LIMIT 1
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve all non-deleted wedding events for a workspace in sort order.
     *
     * @param int $workspaceId
     * @return array
     */
    public function getAllEvents(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT * 
            FROM wedding_events 
            WHERE workspace_id = :workspace_id 
              AND is_deleted = 0 
            ORDER BY sort_order ASC, id ASC
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Extract and sanitize Google Maps embed URL from iframe HTML or raw URL string.
     *
     * @param string $rawInput
     * @return string|null
     */
    public function extractMapsEmbedUrl(string $rawInput): ?string
    {
        $trimmed = trim($rawInput);
        if (empty($trimmed)) {
            return null;
        }

        // 1. If user pasted iframe HTML snippet, extract src attribute
        if (strpos($trimmed, '<iframe') !== false) {
            if (preg_match('/src=["\'](https:\/\/(?:www\.)?google\.com\/maps\/embed[^"\']+)["\']/i', $trimmed, $matches)) {
                return $matches[1];
            }
        }

        // 2. If user pasted direct Google Maps Embed URL (or resubmitting pre-filled URL)
        if (preg_match('/^https:\/\/(?:www\.)?google\.com\/maps\/embed/i', $trimmed)) {
            return $trimmed;
        }

        return null;
    }

    /**
     * Save/upsert multiple ceremony events for a workspace.
     *
     * @param int   $workspaceId
     * @param array $eventsData
     * @return void
     */
    public function saveEvents(int $workspaceId, array $eventsData): void
    {
        $db = Database::getConnection();
        $primaryEventId = (int) ($eventsData['primary_event_id'] ?? 0);

        if (isset($eventsData['events']) && is_array($eventsData['events'])) {
            foreach ($eventsData['events'] as $idx => $evt) {
                $evtId = (int) ($evt['id'] ?? 0);
                $eventName = trim((string) ($evt['event_name'] ?? 'Ceremony'));
                $date = trim((string) ($evt['event_date'] ?? date('Y-m-d')));

                // Parse & sanitize invitation_times
                $rawTimes = $evt['invitation_times'] ?? [];
                if (is_string($rawTimes)) {
                    $rawTimes = [$rawTimes];
                }

                $validTimes = [];
                foreach ($rawTimes as $t) {
                    $tTrim = trim((string) $t);
                    if (preg_match('/^(?:[01]\d|2[0-3]):[0-5]\d$/', $tTrim)) {
                        $validTimes[] = $tTrim;
                    }
                }

                if (empty($validTimes)) {
                    $validTimes[] = trim((string) ($evt['event_time'] ?? '12:00'));
                }

                // Deduplicate & sort ascending chronologically
                $validTimes = array_values(array_unique($validTimes));
                usort($validTimes, static fn($a, $b) => strcmp($a, $b));

                // Earliest time is primary event_date for wedding_events row
                $earliestTime = $validTimes[0];
                $fullDateTime = $date . ' ' . $earliestTime . ':00';

                $venueName = trim((string) ($evt['venue_name'] ?? ''));
                $venueAddress = trim((string) ($evt['venue_address'] ?? ''));
                $mapsUrl = trim((string) ($evt['maps_url'] ?? ''));
                $rawEmbedCode = trim((string) ($evt['maps_embed_code'] ?? $evt['maps_embed_url'] ?? ''));
                $embedUrl = $this->extractMapsEmbedUrl($rawEmbedCode);
                $showMap = isset($evt['show_map']) ? 1 : 0;
                $isPrimary = ($primaryEventId > 0 && $evtId === $primaryEventId) ? 1 : ($idx === 0 ? 1 : 0);

                if ($evtId > 0) {
                    $stmt = $db->prepare('
                        UPDATE wedding_events SET 
                            event_name = :event_name,
                            event_date = :event_date,
                            venue_name = :venue_name,
                            venue_address = :venue_address,
                            maps_url = :maps_url,
                            show_map = :show_map,
                            maps_embed_url = :maps_embed_url,
                            is_primary = :is_primary,
                            sort_order = :sort_order,
                            updated_at = CURRENT_TIMESTAMP
                        WHERE id = :id AND workspace_id = :workspace_id
                    ');
                    $stmt->execute([
                        'event_name'     => $eventName,
                        'event_date'     => $fullDateTime,
                        'venue_name'     => $venueName,
                        'venue_address'  => $venueAddress,
                        'maps_url'       => $mapsUrl,
                        'show_map'       => $showMap,
                        'maps_embed_url' => $embedUrl,
                        'is_primary'     => $isPrimary,
                        'sort_order'     => $idx + 1,
                        'id'             => $evtId,
                        'workspace_id'   => $workspaceId
                    ]);
                } else if (!empty($eventName)) {
                    $stmt = $db->prepare('
                        INSERT INTO wedding_events (
                            workspace_id, event_name, event_date, venue_name, venue_address, maps_url, show_map, maps_embed_url, is_primary, sort_order, is_deleted
                        ) VALUES (
                            :workspace_id, :event_name, :event_date, :venue_name, :venue_address, :maps_url, :show_map, :maps_embed_url, :is_primary, :sort_order, 0
                        )
                    ');
                    $stmt->execute([
                        'workspace_id'   => $workspaceId,
                        'event_name'     => $eventName,
                        'event_date'     => $fullDateTime,
                        'venue_name'     => $venueName,
                        'venue_address'  => $venueAddress,
                        'maps_url'       => $mapsUrl,
                        'show_map'       => $showMap,
                        'maps_embed_url' => $embedUrl,
                        'is_primary'     => $isPrimary,
                        'sort_order'     => $idx + 1
                    ]);
                    $evtId = (int) $db->lastInsertId();
                }

                // Sync event_sessions table for this wedding event
                if ($evtId > 0) {
                    $this->syncEventSessions($workspaceId, $evtId, $date, $validTimes, $venueName, $venueAddress);
                }
            }
        }
    }

    /**
     * Synchronize event_sessions table with the submitted invitation times for a wedding event.
     *
     * @param int    $workspaceId
     * @param int    $weddingEventId
     * @param string $date           YYYY-MM-DD format
     * @param array  $times          Sorted list of HH:mm time strings
     * @param string $venueName
     * @param string $venueAddress
     * @return void
     */
    private function syncEventSessions(int $workspaceId, int $weddingEventId, string $date, array $times, string $venueName, string $venueAddress): void
    {
        $db = Database::getConnection();

        // 1. Fetch existing sessions for this event ordered deterministically
        $stmtExisting = $db->prepare('
            SELECT id, start_time, session_name 
            FROM event_sessions 
            WHERE workspace_id = :workspace_id AND wedding_event_id = :event_id 
            ORDER BY sort_order ASC, id ASC
        ');
        $stmtExisting->execute(['workspace_id' => $workspaceId, 'event_id' => $weddingEventId]);
        $existingRows = $stmtExisting->fetchAll(\PDO::FETCH_ASSOC);

        $keptSessionIds = [];

        foreach ($times as $sIdx => $time) {
            $startDateTime = "{$date} {$time}:00";
            $isDefault = ($sIdx === 0) ? 1 : 0;
            $sessionName = count($times) > 1 ? "{$time} WIB" : "Default Session";

            // Positional matching: reuse existing session row at index $sIdx if available
            if (isset($existingRows[$sIdx])) {
                $sessionId = (int) $existingRows[$sIdx]['id'];
                $stmtUpd = $db->prepare('
                    UPDATE event_sessions 
                    SET session_name  = :session_name,
                        start_time    = :start_time,
                        is_default    = :is_default,
                        sort_order    = :sort_order,
                        venue_name    = :venue_name,
                        venue_address = :venue_address,
                        updated_at    = CURRENT_TIMESTAMP
                    WHERE id = :id AND workspace_id = :workspace_id
                ');
                $stmtUpd->execute([
                    'session_name'  => $sessionName,
                    'start_time'    => $startDateTime,
                    'is_default'    => $isDefault,
                    'sort_order'    => $sIdx + 1,
                    'venue_name'    => $venueName,
                    'venue_address' => $venueAddress,
                    'id'            => $sessionId,
                    'workspace_id'  => $workspaceId
                ]);
                $keptSessionIds[] = $sessionId;
            } else {
                // Insert new session row when submitted times exceed existing sessions
                $stmtIns = $db->prepare('
                    INSERT INTO event_sessions (
                        wedding_event_id, workspace_id, session_name, start_time, venue_name, venue_address, sort_order, is_default
                    ) VALUES (
                        :event_id, :workspace_id, :session_name, :start_time, :venue_name, :venue_address, :sort_order, :is_default
                    )
                ');
                $stmtIns->execute([
                    'event_id'     => $weddingEventId,
                    'workspace_id' => $workspaceId,
                    'session_name' => $sessionName,
                    'start_time'   => $startDateTime,
                    'venue_name'   => $venueName,
                    'venue_address'=> $venueAddress,
                    'sort_order'   => $sIdx + 1,
                    'is_default'   => $isDefault
                ]);
                $keptSessionIds[] = (int) $db->lastInsertId();
            }
        }

        // Clean up excess sessions that were removed (validated to have 0 assigned guests beforehand)
        if (count($existingRows) > count($times)) {
            for ($i = count($times); $i < count($existingRows); $i++) {
                $exId = (int) $existingRows[$i]['id'];
                if (!in_array($exId, $keptSessionIds, true)) {
                    $stmtDel = $db->prepare('DELETE FROM event_sessions WHERE id = :id AND workspace_id = :workspace_id');
                    $stmtDel->execute(['id' => $exId, 'workspace_id' => $workspaceId]);
                }
            }
        }
    }

    /**
     * Retrieve the primary wedding event.
     *
     * @param int $workspaceId
     * @return array|null
     */
    public function getPrimaryEvent(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT * 
            FROM wedding_events 
            WHERE workspace_id = :workspace_id 
              AND is_primary = 1 
              AND is_deleted = 0 
            LIMIT 1
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve the hero section configuration.
     *
     * @param int $invitationId
     * @return array|null
     */
    public function getHeroSection(int $invitationId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT * 
            FROM invitation_sections 
            WHERE invitation_id = :invitation_id 
              AND section_slug = \'hero\' 
            LIMIT 1
        ');
        $stmt->execute(['invitation_id' => $invitationId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve list of active theme versions.
     *
     * @return array
     */
    public function getActiveThemeVersions(): array
    {
        $db = Database::getConnection();
        $stmt = $db->query('
            SELECT tv.id, t.name as theme_name, tv.version_number 
            FROM theme_versions tv
            JOIN themes t ON tv.theme_id = t.id
            WHERE tv.is_active = 1
        ');
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Update invitation details.
     *
     * @param int   $workspaceId
     * @param array $data
     * @return void
     */
    public function updateInvitation(int $workspaceId, array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE invitations SET 
                groom_display_name = :groom_name,
                bride_display_name = :bride_name,
                love_story = :love_story,
                theme_version_id = :theme_version_id,
                invitation_status = :status,
                rsvp_enabled = :rsvp_enabled,
                rsvp_closes_at = :rsvp_closes_at,
                rsvp_allow_edit = :rsvp_allow_edit,
                wish_wall_enabled = :wish_wall_enabled,
                published_at = COALESCE(published_at, :published_at),
                updated_at = CURRENT_TIMESTAMP
            WHERE workspace_id = :workspace_id
        ');
        $stmt->execute([
            'groom_name'        => $data['groom_display_name'],
            'bride_name'        => $data['bride_display_name'],
            'love_story'        => $data['love_story'],
            'theme_version_id'  => (int) $data['theme_version_id'],
            'status'            => $data['invitation_status'],
            'rsvp_enabled'      => (int) ($data['rsvp_enabled'] ?? 1),
            'rsvp_closes_at'    => !empty($data['rsvp_closes_at']) ? $data['rsvp_closes_at'] : null,
            'rsvp_allow_edit'   => (int) ($data['rsvp_allow_edit'] ?? 1),
            'wish_wall_enabled' => (int) ($data['wishes_enabled'] ?? 1),
            'published_at'      => $data['invitation_status'] === 'published' ? date('Y-m-d H:i:s') : null,
            'workspace_id'      => $workspaceId
        ]);
    }

    /**
     * Update workspaces wedding date.
     *
     * @param int   $workspaceId
     * @param string $weddingDate
     * @return void
     */
    public function updateWorkspaceDate(int $workspaceId, string $weddingDate): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE workspaces SET 
                wedding_date = :wedding_date,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :workspace_id
        ');
        $stmt->execute([
            'wedding_date' => $weddingDate,
            'workspace_id' => $workspaceId
        ]);
    }

    /**
     * Update workspaces activated_at field if not set yet.
     *
     * @param int $workspaceId
     * @return void
     */
    public function updateWorkspaceActivatedAt(int $workspaceId): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE workspaces SET 
                activated_at = COALESCE(activated_at, CURRENT_TIMESTAMP),
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :workspace_id
        ');
        $stmt->execute([
            'workspace_id' => $workspaceId
        ]);
    }

    /**
     * Upsert a primary wedding event detail.
     *
     * @param int   $workspaceId
     * @param array $data
     * @return void
     */
    public function upsertPrimaryEvent(int $workspaceId, array $data): void
    {
        $db = Database::getConnection();
        $existing = $this->getPrimaryEvent($workspaceId);

        if ($existing) {
            $stmt = $db->prepare('
                UPDATE wedding_events SET 
                    event_date = :event_date,
                    venue_name = :venue_name,
                    venue_address = :venue_address,
                    maps_url = :maps_url,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = :id
            ');
            $stmt->execute([
                'event_date'    => $data['event_date'],
                'venue_name'    => $data['venue_name'],
                'venue_address' => $data['venue_address'],
                'maps_url'      => $data['maps_url'],
                'id'            => $existing['id']
            ]);
        } else {
            $stmt = $db->prepare('
                INSERT INTO wedding_events (
                    workspace_id, event_name, event_date, venue_name, venue_address, maps_url, is_primary, is_deleted
                ) VALUES (
                    :workspace_id, :event_name, :event_date, :venue_name, :venue_address, :maps_url, 1, 0
                )
            ');
            $stmt->execute([
                'workspace_id'  => $workspaceId,
                'event_name'    => 'Wedding Ceremony',
                'event_date'    => $data['event_date'],
                'venue_name'    => $data['venue_name'],
                'venue_address' => $data['venue_address'],
                'maps_url'      => $data['maps_url']
            ]);
        }
    }

    /**
     * Update the hero section config.
     *
     * @param int   $invitationId
     * @param array $config
     * @return void
     */
    public function updateHeroSectionConfig(int $invitationId, array $config): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            UPDATE invitation_sections SET 
                section_config = :config,
                updated_at = CURRENT_TIMESTAMP
            WHERE invitation_id = :invitation_id 
              AND section_slug = \'hero\'
        ');
        $stmt->execute([
            'config'        => json_encode($config),
            'invitation_id' => $invitationId
        ]);
    }

    /**
     * Retrieve the rsvp section configuration.
     *
     * @param int $invitationId
     * @return array|null
     */
    public function getRsvpSection(int $invitationId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT * 
            FROM invitation_sections 
            WHERE invitation_id = :invitation_id 
              AND section_slug = \'rsvp\' 
            LIMIT 1
        ');
        $stmt->execute(['invitation_id' => $invitationId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Upsert RSVP section config and visibility.
     *
     * @param int   $invitationId
     * @param bool  $isVisible
     * @param array $config
     * @return void
     */
    public function saveRsvpSectionConfig(int $invitationId, bool $isVisible, array $config): void
    {
        $db   = Database::getConnection();
        $json = json_encode($config, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        $stmt = $db->prepare('
            INSERT INTO invitation_sections
                (invitation_id, section_slug, is_visible, sort_order, section_config, updated_at)
            VALUES
                (:invitation_id, \'rsvp\', :is_visible, 5, :config, CURRENT_TIMESTAMP)
            ON DUPLICATE KEY UPDATE
                is_visible     = VALUES(is_visible),
                section_config = VALUES(section_config),
                updated_at     = CURRENT_TIMESTAMP
        ');
        $stmt->execute([
            'invitation_id' => $invitationId,
            'is_visible'    => $isVisible ? 1 : 0,
            'config'        => $json,
        ]);
    }

    /**
     * Retrieve the wishes section configuration.
     *
     * @param int $invitationId
     * @return array|null
     */
    public function getWishesSection(int $invitationId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT * 
            FROM invitation_sections 
            WHERE invitation_id = :invitation_id 
              AND section_slug = \'wishes\' 
            LIMIT 1
        ');
        $stmt->execute(['invitation_id' => $invitationId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Upsert Wishes section config and visibility.
     *
     * @param int   $invitationId
     * @param bool  $isVisible
     * @param array $config
     * @return void
     */
    public function saveWishesSectionConfig(int $invitationId, bool $isVisible, array $config): void
    {
        $db   = Database::getConnection();
        $json = json_encode($config, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        $stmt = $db->prepare('
            INSERT INTO invitation_sections
                (invitation_id, section_slug, is_visible, sort_order, section_config, updated_at)
            VALUES
                (:invitation_id, \'wishes\', :is_visible, 6, :config, CURRENT_TIMESTAMP)
            ON DUPLICATE KEY UPDATE
                is_visible     = VALUES(is_visible),
                section_config = VALUES(section_config),
                updated_at     = CURRENT_TIMESTAMP
        ');
        $stmt->execute([
            'invitation_id' => $invitationId,
            'is_visible'    => $isVisible ? 1 : 0,
            'config'        => $json,
        ]);
    }

    /**
     * Retrieve the latest wishes for a workspace.
     *
     * @param int $workspaceId
     * @param int $limit
     * @return array
     */
    public function getLatestWishes(int $workspaceId, int $limit = 5): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT sender_name, message, submitted_at 
            FROM wishes 
            WHERE workspace_id = :workspace_id 
              AND is_visible = 1 
            ORDER BY submitted_at DESC 
            LIMIT :limit
        ');
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Retrieve count of active visible wishes.
     *
     * @param int $workspaceId
     * @return int
     */
    public function getWishesCount(int $workspaceId): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT COUNT(*) 
            FROM wishes 
            WHERE workspace_id = :workspace_id 
              AND is_visible = 1
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Get paginated wishes with search, status, and sorting filters.
     *
     * @param int   $workspaceId
     * @param array $filters
     * @param int   $limit
     * @param int   $offset
     * @return array
     */
    public function getWishesFiltered(int $workspaceId, array $filters = [], int $limit = 10, int $offset = 0): array
    {
        $db = Database::getConnection();
        
        $sql = 'SELECT * FROM wishes WHERE workspace_id = :workspace_id';
        $params = ['workspace_id' => $workspaceId];
        
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            if ($filters['status'] === 'approved') {
                $sql .= ' AND (status = \'approved\' OR (status IS NULL AND is_visible = 1))';
            } elseif ($filters['status'] === 'pending') {
                $sql .= ' AND status = \'pending\'';
            } elseif ($filters['status'] === 'rejected') {
                $sql .= ' AND (status = \'rejected\' OR (status IS NULL AND is_visible = 0))';
            }
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (sender_name LIKE :search1 OR message LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }
        
        $sort = isset($filters['sort']) && $filters['sort'] === 'oldest' ? 'ASC' : 'DESC';
        $sql .= ' ORDER BY submitted_at ' . $sort . ' 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 wishes matching query filters.
     *
     * @param int   $workspaceId
     * @param array $filters
     * @return int
     */
    public function getWishesCountFiltered(int $workspaceId, array $filters = []): int
    {
        $db = Database::getConnection();
        
        $sql = 'SELECT COUNT(*) FROM wishes WHERE workspace_id = :workspace_id';
        $params = ['workspace_id' => $workspaceId];
        
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            if ($filters['status'] === 'approved') {
                $sql .= ' AND (status = \'approved\' OR (status IS NULL AND is_visible = 1))';
            } elseif ($filters['status'] === 'pending') {
                $sql .= ' AND status = \'pending\'';
            } elseif ($filters['status'] === 'rejected') {
                $sql .= ' AND (status = \'rejected\' OR (status IS NULL AND is_visible = 0))';
            }
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (sender_name LIKE :search1 OR message LIKE :search2)';
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Update wish moderation status.
     *
     * @param int      $workspaceId
     * @param int      $wishId
     * @param string   $status 'approved' | 'rejected' | 'pending'
     * @param int|null $userId
     * @return bool
     */
    public function updateWishStatus(int $workspaceId, int $wishId, string $status, ?int $userId = null): bool
    {
        $db = Database::getConnection();

        $isVisible = $status === 'approved' ? 1 : 0;
        $approvedAt = $status === 'approved' ? date('Y-m-d H:i:s') : null;
        $hiddenAt = $status === 'rejected' ? date('Y-m-d H:i:s') : null;

        $stmt = $db->prepare('
            UPDATE wishes 
            SET status = :status, 
                is_visible = :is_visible, 
                approved_at = :approved_at, 
                approved_by = :approved_by, 
                hidden_at = :hidden_at 
            WHERE id = :id AND workspace_id = :workspace_id
        ');

        return $stmt->execute([
            'status'       => $status,
            'is_visible'   => $isVisible,
            'approved_at'  => $approvedAt,
            'approved_by'  => $userId,
            'hidden_at'    => $hiddenAt,
            'id'           => $wishId,
            'workspace_id' => $workspaceId
        ]);
    }

    /**
     * Delete wish record.
     *
     * @param int $workspaceId
     * @param int $wishId
     * @return bool
     */
    public function deleteWish(int $workspaceId, int $wishId): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('DELETE FROM wishes WHERE id = :id AND workspace_id = :workspace_id');
        return $stmt->execute(['id' => $wishId, 'workspace_id' => $workspaceId]);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // MEDIA ENGINE FOUNDATION (G004)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Retrieve the decoded media metadata JSON for an invitation.
     *
     * Reads the invitation_sections row where section_slug = 'media'.
     * Returns an empty default structure if the row does not yet exist.
     *
     * @param int $invitationId
     * @return array  Decoded media metadata.
     */
    public function getMediaSection(int $invitationId): array
    {
        $db   = Database::getConnection();
        $stmt = $db->prepare('
            SELECT section_config
            FROM invitation_sections
            WHERE invitation_id = :invitation_id
              AND section_slug  = \'media\'
            ORDER BY id DESC
            LIMIT 1
        ');
        $stmt->execute(['invitation_id' => $invitationId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

        if ($row && !empty($row['section_config'])) {
            $decoded = json_decode((string) $row['section_config'], true);
            if (is_array($decoded)) {
                return array_merge($this->defaultMediaMeta(), $decoded);
            }
        }

        return $this->defaultMediaMeta();
    }

    /**
     * Upsert the media metadata JSON for an invitation.
     *
     * Creates the 'media' invitation_sections row on first save,
     * or updates section_config if the row already exists.
     *
     * @param int   $invitationId
     * @param array $meta  Full media metadata array.
     * @return void
     */
    public function saveMediaSection(int $invitationId, array $meta): void
    {
        $db   = Database::getConnection();
        $json = json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $musicEnabled = !empty($meta['music_enabled']) ? 1 : 0;

        // Check if media row already exists for this invitation
        $stmtCheck = $db->prepare('
            SELECT id FROM invitation_sections 
            WHERE invitation_id = :invitation_id AND section_slug = \'media\'
            ORDER BY id DESC LIMIT 1
        ');
        $stmtCheck->execute(['invitation_id' => $invitationId]);
        $existingId = $stmtCheck->fetchColumn();

        if ($existingId) {
            $stmtUpdate = $db->prepare('
                UPDATE invitation_sections
                SET section_config = :config,
                    is_visible = 1,
                    updated_at = CURRENT_TIMESTAMP
                WHERE invitation_id = :invitation_id
                  AND section_slug = \'media\'
            ');
            $stmtUpdate->execute([
                'config'        => $json,
                'invitation_id' => $invitationId,
            ]);
        } else {
            $stmtInsert = $db->prepare('
                INSERT INTO invitation_sections
                    (invitation_id, section_slug, is_visible, sort_order, section_config, updated_at)
                VALUES
                    (:invitation_id, \'media\', 1, 99, :config, CURRENT_TIMESTAMP)
            ');
            $stmtInsert->execute([
                'invitation_id' => $invitationId,
                'config'        => $json,
            ]);
        }

        // Sync music_enabled state back to invitations table
        try {
            $stmtSync = $db->prepare('UPDATE invitations SET music_enabled = :enabled WHERE id = :id');
            $stmtSync->execute([
                'enabled' => $musicEnabled,
                'id'      => $invitationId,
            ]);
        } catch (\Throwable $e) {
            // Fail-safe if invitations table sync is unavailable
        }
    }

    /**
     * Return the empty/default media metadata structure.
     * Ensures all expected keys are always present for new invitations.
     *
     * @return array
     */
    private function defaultMediaMeta(): array
    {
        return [
            'hero'            => null,
            'couple'          => [],
            'gallery'         => [],
            'story'           => [],
            'music_cover'     => null,
            'music_file'      => null,
            'gift'            => null,
            'music_enabled'   => false,
            'music_title'     => '',
            'music_artist'    => '',
            'music_autoplay'  => true,
            'music_loop'      => true,
            'music_minimized' => false,
            'music_volume'    => 50,
        ];
    }
}
