<?php

declare(strict_types=1);

namespace App\Modules\Messaging;

use App\Core\Database;

/**
 * MessageModel — Manages retrieval and persistence of message templates,
 * guest details, workspaces, and event parameters.
 *
 * Prepared statements only with strict workspace isolation.
 *
 * @package Inveetaire
 * @ref     PILOT-006
 */
class MessageModel
{
    /**
     * Retrieve the template for a workspace and type.
     *
     * @param int    $workspaceId
     * @param string $type
     * @return array|null Template row or null.
     */
    public function getTemplate(int $workspaceId, string $type): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT body_text, is_default 
            FROM message_templates 
            WHERE workspace_id = :workspace_id 
              AND template_type = :type 
            LIMIT 1
        ');
        $stmt->execute([
            'workspace_id' => $workspaceId,
            'type'         => $type
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Persist or update the message template text.
     *
     * @param int    $workspaceId
     * @param string $type
     * @param string $bodyText
     * @return void
     */
    public function saveTemplate(int $workspaceId, string $type, string $bodyText): void
    {
        $db = Database::getConnection();

        // Check if template exists
        $stmtCheck = $db->prepare('
            SELECT id FROM message_templates 
            WHERE workspace_id = :workspace_id 
              AND template_type = :type 
            LIMIT 1
        ');
        $stmtCheck->execute([
            'workspace_id' => $workspaceId,
            'type'         => $type
        ]);
        $exists = $stmtCheck->fetchColumn();

        if ($exists) {
            $stmtUpdate = $db->prepare('
                UPDATE message_templates 
                SET body_text = :body, 
                    is_default = 0, 
                    updated_at = CURRENT_TIMESTAMP 
                WHERE workspace_id = :workspace_id 
                  AND template_type = :type
            ');
            $stmtUpdate->execute([
                'body'         => $bodyText,
                'workspace_id' => $workspaceId,
                'type'         => $type
            ]);
        } else {
            $stmtInsert = $db->prepare('
                INSERT INTO message_templates (workspace_id, template_type, body_text, is_default, created_at) 
                VALUES (:workspace_id, :type, :body, 0, CURRENT_TIMESTAMP)
            ');
            $stmtInsert->execute([
                'workspace_id' => $workspaceId,
                'type'         => $type,
                'body'         => $bodyText
            ]);
        }
    }

    /**
     * Retrieve all active workspace guests.
     *
     * @param int $workspaceId
     * @return array
     */
    public function getGuests(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, guest_code, full_name, side, guest_group, invited_pax, phone_number 
            FROM guests 
            WHERE workspace_id = :workspace_id 
              AND is_deleted = 0
            ORDER BY full_name ASC
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Retrieve workspace core 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 
            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 active invitation display names.
     *
     * @param int $workspaceId
     * @return array|null
     */
    public function getInvitation(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, groom_display_name, bride_display_name 
            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 all wedding events configured for the workspace.
     *
     * @param int $workspaceId
     * @return array
     */
    public function getWeddingEvents(int $workspaceId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, event_name, event_date, venue_name, venue_address, maps_url, is_primary 
            FROM wedding_events 
            WHERE workspace_id = :workspace_id 
              AND is_deleted = 0
            ORDER BY sort_order ASC, event_date ASC
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Retrieve explicit event IDs assigned to a specific guest.
     *
     * @param int $workspaceId
     * @param int $guestId
     * @return array List of assigned wedding_event_id values.
     */
    public function getAssignedEventIds(int $workspaceId, int $guestId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT wedding_event_id 
            FROM guest_events 
            WHERE workspace_id = :workspace_id 
              AND guest_id = :guest_id
        ');
        $stmt->execute([
            'workspace_id' => $workspaceId,
            'guest_id'     => $guestId
        ]);
        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }
}
