<?php

declare(strict_types=1);

namespace App\Modules\PublicInvitation;

use App\Core\Database;

/**
 * PublicInvitationModel — Database reads for public invitation landing page.
 *
 * All lookups are parameterized and isolate queries to the active workspace.
 *
 * @package Inveetaire
 * @ref     PILOT-003
 */
class PublicInvitationModel
{
    /**
     * Look up a workspace by its unique URL slug.
     *
     * @param string $slug
     * @return array|null Workspace row or null if not found.
     */
    public function getWorkspaceBySlug(string $slug): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, slug, groom_name, bride_name, wedding_date, workspace_status 
            FROM workspaces 
            WHERE slug = :slug
            LIMIT 1
        ');
        $stmt->execute(['slug' => $slug]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Look up a workspace by its primary key.
     * Used by Live Preview to resolve slug and names from session workspace ID.
     *
     * @param int $workspaceId
     * @return array|null Workspace row or null if not found.
     */
    public function getWorkspaceById(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, slug, groom_name, bride_name, wedding_date, 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;
    }

    /**
     * Look up a guest by token (guest_code) within a specific workspace context.
     *
     * @param int    $workspaceId
     * @param string $token
     * @return array|null Guest row or null if not found.
     */
    public function getGuestByToken(int $workspaceId, string $token): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, workspace_id, guest_code, full_name, side, guest_group, invited_pax, phone_number, notes 
            FROM guests 
            WHERE workspace_id = :workspace_id 
              AND guest_code = :token
              AND is_deleted = 0
            LIMIT 1
        ');
        $stmt->execute([
            'workspace_id' => $workspaceId,
            'token'        => $token
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Look up the active invitation config for a workspace.
     *
     * @param int $workspaceId
     * @return array|null Invitation row or null if not found.
     */
    public function getInvitationByWorkspace(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, workspace_id, theme_version_id, groom_display_name, bride_display_name, invitation_status, love_story 
            FROM invitations 
            WHERE workspace_id = :workspace_id
            LIMIT 1
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve the wedding events assigned to a guest.
     *
     * Multi-event resolution is supported. If no explicit entries exist in
     * the guest_events mapping table, it falls back to returning all active
     * wedding events configured for the workspace.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return array List of wedding events.
     */
    public function getAssignedEvents(int $workspaceId, int $guestId): array
    {
        $db = Database::getConnection();

        // Attempt to resolve explicit events assigned to this guest with session schedules
        $stmtExplicit = $db->prepare('
            SELECT 
                we.id, 
                we.event_name, 
                COALESCE(es.start_time, default_es.start_time, we.event_date) AS event_date,
                COALESCE(es.venue_name, default_es.venue_name, we.venue_name) AS venue_name,
                COALESCE(es.venue_address, default_es.venue_address, we.venue_address) AS venue_address,
                we.maps_url, 
                we.show_map, 
                we.maps_embed_url, 
                we.is_primary, 
                we.dress_code, 
                we.description,
                COALESCE(es.session_name, default_es.session_name, \'Default Session\') AS session_name,
                COALESCE(es.id, default_es.id) AS event_session_id
            FROM wedding_events we
            INNER JOIN guest_events ge ON we.id = ge.wedding_event_id
            LEFT JOIN event_sessions es ON ge.event_session_id = es.id
            LEFT JOIN event_sessions default_es ON (we.id = default_es.wedding_event_id AND default_es.is_default = 1)
            WHERE ge.guest_id = :guest_id
              AND we.workspace_id = :workspace_id
              AND we.is_deleted = 0
            ORDER BY we.sort_order ASC, event_date ASC
        ');
        $stmtExplicit->execute([
            'workspace_id' => $workspaceId,
            'guest_id'     => $guestId
        ]);

        $assigned = $stmtExplicit->fetchAll(\PDO::FETCH_ASSOC);
        if (!empty($assigned)) {
            return $assigned;
        }

        // Fallback: If guest has no explicit event restrictions, return all workspace events
        return $this->getAllEvents($workspaceId);
    }

    /**
     * Retrieve all active wedding events for a workspace.
     * Used by Live Preview — the virtual guest is invited to all events using default sessions.
     *
     * @param int $workspaceId
     * @return array List of all active wedding events.
     */
    public function getAllEvents(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT 
                we.id, 
                we.event_name, 
                COALESCE(default_es.start_time, we.event_date) AS event_date,
                COALESCE(default_es.venue_name, we.venue_name) AS venue_name,
                COALESCE(default_es.venue_address, we.venue_address) AS venue_address,
                we.maps_url, 
                we.show_map, 
                we.maps_embed_url, 
                we.is_primary, 
                we.dress_code, 
                we.description,
                COALESCE(default_es.session_name, \'Default Session\') AS session_name,
                default_es.id AS event_session_id
            FROM wedding_events we
            LEFT JOIN event_sessions default_es ON (we.id = default_es.wedding_event_id AND default_es.is_default = 1)
            WHERE we.workspace_id = :workspace_id
              AND we.is_deleted = 0
            ORDER BY we.sort_order ASC, event_date ASC
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Retrieve the theme detail linked to the theme version.
     *
     * @param int $themeVersionId
     * @return array|null Theme row or null.
     */
    public function getThemeByVersionId(int $themeVersionId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT t.id, t.name, t.slug, t.description 
            FROM themes t
            INNER JOIN theme_versions tv ON t.id = tv.theme_id
            WHERE tv.id = :version_id
            LIMIT 1
        ');
        $stmt->execute(['version_id' => $themeVersionId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve all configured layout sections for an invitation.
     *
     * @param int $invitationId
     * @return array List of sections ordered by sort_order.
     */
    public function getInvitationSections(int $invitationId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT section_slug, is_visible, sort_order, section_config 
            FROM invitation_sections 
            WHERE invitation_id = :invitation_id
            ORDER BY sort_order ASC
        ');
        $stmt->execute(['invitation_id' => $invitationId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Retrieve a guest's RSVP record.
     *
     * @param int $guestId
     * @return array|null RSVP row or null.
     */
    public function getGuestRsvp(int $guestId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT response, confirmed_pax 
            FROM rsvps 
            WHERE guest_id = :guest_id
            LIMIT 1
        ');
        $stmt->execute(['guest_id' => $guestId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve a guest's wish message.
     *
     * @param int $guestId
     * @return array|null Wish row or null.
     */
    public function getGuestWish(int $guestId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT message 
            FROM wishes 
            WHERE guest_id = :guest_id
              AND is_visible = 1
            LIMIT 1
        ');
        $stmt->execute(['guest_id' => $guestId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Resolve validation data for a guest token.
     *
     * @param string $token Guest secure token.
     * @return array|null Combined validation row or null if guest token is invalid.
     */
    public function getValidationData(string $token): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT 
                g.guest_code,
                g.full_name,
                g.honorific,
                g.guest_group,
                g.invited_pax,
                w.slug AS workspace_slug,
                w.workspace_status,
                i.groom_display_name,
                i.bride_display_name,
                i.invitation_status
            FROM guests g
            JOIN workspaces w ON g.workspace_id = w.id
            JOIN invitations i ON w.id = i.workspace_id
            WHERE g.guest_code = :token
              AND g.is_deleted = 0
            LIMIT 1
        ');
        $stmt->execute(['token' => $token]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve wedding gift settings for a given invitation.
     */
    public function getGiftSettings(int $invitationId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT * FROM wedding_gift_settings WHERE invitation_id = ? LIMIT 1');
        $stmt->execute([$invitationId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Retrieve active gift accounts for a given setting ID.
     */
    public function getGiftAccounts(int $settingId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT * FROM wedding_gift_accounts WHERE setting_id = ? AND is_active = 1 ORDER BY sort_order ASC');
        $stmt->execute([$settingId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}
