<?php

declare(strict_types=1);

namespace App\Modules\Workspace;

use App\Core\Database;

/**
 * WorkspaceModel — Workspace Provisioning Database Operations
 *
 * Exclusively handles database interaction (parameterized PDO queries)
 * for the Workspace module provisioning workflow (WS-001B).
 * No business logic, presentation logic, or transaction lifecycle management.
 *
 * @package Inveetaire
 * @ref     SPRINT3_PLANNING.md, SPRINT3_EXECUTION_GUIDE.md
 */
class WorkspaceModel
{
    /**
     * Check if a workspace slug already exists.
     *
     * @param string $slug
     * @return bool
     */
    public function slugExists(string $slug): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT id FROM workspaces WHERE slug = :slug LIMIT 1');
        $stmt->execute(['slug' => $slug]);
        return $stmt->fetch() !== false;
    }

    /**
     * Insert a new workspace record.
     */
    public function insertWorkspace(array $data): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO workspaces (
                slug, groom_name, bride_name, wedding_date, 
                event_type_id, commercial_plan_id, custom_guest_limit, workspace_status, created_by
            ) VALUES (
                :slug, :groom_name, :bride_name, :wedding_date, 
                :event_type_id, :commercial_plan_id, :custom_guest_limit, :workspace_status, :created_by
            )
        ');
        $stmt->execute([
            'slug'               => $data['slug'],
            'groom_name'         => $data['groom_name'],
            'bride_name'         => $data['bride_name'],
            'wedding_date'       => $data['wedding_date'],
            'event_type_id'      => $data['event_type_id'],
            'commercial_plan_id' => $data['commercial_plan_id'],
            'custom_guest_limit' => $data['custom_guest_limit'] ?? null,
            'workspace_status'   => $data['workspace_status'],
            'created_by'         => $data['created_by'],
        ]);
        return (int) $db->lastInsertId();
    }

    /**
     * Insert a new user record.
     */
    public function insertUser(array $data): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO users (
                email, username, display_name, password_hash, role_id, workspace_id, is_active
            ) VALUES (
                :email, :username, :display_name, :password_hash, :role_id, :workspace_id, :is_active
            )
        ');
        $stmt->execute([
            'email'         => $data['email'],
            'username'      => $data['username'] ?? null,
            'display_name'  => $data['display_name'] ?? null,
            'password_hash' => $data['password_hash'],
            'role_id'       => $data['role_id'],
            'workspace_id'  => $data['workspace_id'],
            'is_active'     => $data['is_active'],
        ]);
        return (int) $db->lastInsertId();
    }

    /**
     * Check if username exists.
     */
    public function usernameExists(string $username): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT id FROM users WHERE username = :username LIMIT 1');
        $stmt->execute(['username' => $username]);
        return $stmt->fetch() !== false;
    }

    /**
     * Insert a new retention policy record.
     *
     * @param array $data
     * @return void
     */
    public function insertRetentionPolicy(array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO retention_policies (
                workspace_id, active_retention_days, archive_retention_days, is_overridden
            ) VALUES (
                :workspace_id, :active_retention_days, :archive_retention_days, :is_overridden
            )
        ');
        $stmt->execute([
            'workspace_id'           => $data['workspace_id'],
            'active_retention_days'  => $data['active_retention_days'],
            'archive_retention_days' => $data['archive_retention_days'],
            'is_overridden'          => $data['is_overridden'],
        ]);
    }

    /**
     * Insert a new invitation record.
     *
     * @param array $data
     * @return int Inserted invitation ID
     */
    public function insertInvitation(array $data): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO invitations (
                workspace_id, theme_version_id, groom_display_name, bride_display_name, invitation_status
            ) VALUES (
                :workspace_id, :theme_version_id, :groom_display_name, :bride_display_name, :invitation_status
            )
        ');
        $stmt->execute([
            'workspace_id'       => $data['workspace_id'],
            'theme_version_id'   => $data['theme_version_id'],
            'groom_display_name' => $data['groom_display_name'],
            'bride_display_name' => $data['bride_display_name'],
            'invitation_status'  => $data['invitation_status'],
        ]);
        return (int) $db->lastInsertId();
    }

    /**
     * Insert a new invitation section record.
     *
     * @param array $data
     * @return void
     */
    public function insertInvitationSection(array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO invitation_sections (
                invitation_id, section_slug, is_visible, sort_order
            ) VALUES (
                :invitation_id, :section_slug, :is_visible, :sort_order
            )
        ');
        $stmt->execute([
            'invitation_id' => $data['invitation_id'],
            'section_slug'  => $data['section_slug'],
            'is_visible'    => $data['is_visible'],
            'sort_order'     => $data['sort_order'],
        ]);
    }

    /**
     * Insert a new message template record.
     *
     * @param array $data
     * @return void
     */
    public function insertMessageTemplate(array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO message_templates (
                workspace_id, template_type, body_text, is_default
            ) VALUES (
                :workspace_id, :template_type, :body_text, :is_default
            )
        ');
        $stmt->execute([
            'workspace_id'  => $data['workspace_id'],
            'template_type' => $data['template_type'],
            'body_text'     => $data['body_text'],
            'is_default'    => $data['is_default'],
        ]);
    }

    /**
     * Insert a new printer configuration record.
     *
     * @param array $data
     * @return void
     */
    public function insertPrinterConfiguration(array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO printer_configurations (
                workspace_id, angpao_label_enabled, souvenir_label_enabled, vip_label_enabled, copies_per_checkin
            ) VALUES (
                :workspace_id, :angpao_label_enabled, :souvenir_label_enabled, :vip_label_enabled, :copies_per_checkin
            )
        ');
        $stmt->execute([
            'workspace_id'           => $data['workspace_id'],
            'angpao_label_enabled'   => $data['angpao_label_enabled'],
            'souvenir_label_enabled' => $data['souvenir_label_enabled'],
            'vip_label_enabled'      => $data['vip_label_enabled'],
            'copies_per_checkin'     => $data['copies_per_checkin'],
        ]);
    }

    /**
     * Write a new audit log record.
     *
     * @param array $data
     * @return void
     */
    public function insertAuditLog(array $data): void
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            INSERT INTO audit_logs (
                actor_type, actor_id, actor_display_name, workspace_id,
                action_category, action_type, target_entity_type, target_entity_id,
                after_state, ip_address
            ) VALUES (
                :actor_type, :actor_id, :actor_display_name, :workspace_id,
                :action_category, :action_type, :target_entity_type, :target_entity_id,
                :after_state, :ip_address
            )
        ');
        $stmt->execute([
            'actor_type'         => $data['actor_type'],
            'actor_id'           => $data['actor_id'],
            'actor_display_name' => $data['actor_display_name'],
            'workspace_id'       => $data['workspace_id'],
            'action_category'    => $data['action_category'],
            'action_type'        => $data['action_type'],
            'target_entity_type' => $data['target_entity_type'],
            'target_entity_id'   => $data['target_entity_id'],
            'after_state'        => $data['after_state'],
            'ip_address'         => $data['ip_address'],
        ]);
    }

    /**
     * Get invitation status by workspace ID.
     *
     * @param int $workspaceId
     * @return string|null
     */
    public function getInvitationStatus(int $workspaceId): ?string
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT invitation_status FROM invitations WHERE workspace_id = :workspace_id LIMIT 1');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $status = $stmt->fetchColumn();
        return $status !== false ? (string) $status : null;
    }

    /**
     * Get theme version ID by workspace ID.
     *
     * @param int $workspaceId
     * @return int|null
     */
    public function getThemeVersionId(int $workspaceId): ?int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT theme_version_id FROM invitations WHERE workspace_id = :workspace_id LIMIT 1');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $val = $stmt->fetchColumn();
        return $val !== false ? (int) $val : null;
    }

    /**
     * Get non-deleted guest count by workspace ID.
     *
     * @param int $workspaceId
     * @return int
     */
    public function getGuestCount(int $workspaceId): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Get printer configuration by workspace ID.
     *
     * @param int $workspaceId
     * @return array|null
     */
    public function getPrinterConfig(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('
            SELECT id, workspace_id, angpao_label_enabled, souvenir_label_enabled, vip_label_enabled, 
                   label_width_mm, label_height_mm, copies_per_checkin, angpao_label_fields, updated_at 
            FROM printer_configurations 
            WHERE workspace_id = :workspace_id 
            LIMIT 1
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $config = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $config !== false ? $config : null;
    }

    /**
     * Get non-deleted wedding event count by workspace ID.
     *
     * @param int $workspaceId
     * @return int
     */
    public function getWeddingEventCount(int $workspaceId): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT COUNT(*) FROM wedding_events WHERE workspace_id = :workspace_id AND is_deleted = 0');
        $stmt->execute(['workspace_id' => $workspaceId]);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Get workspace status, wedding date, and activation timestamp by ID.
     *
     * @param int $workspaceId
     * @return array|null
     */
    public function getWorkspaceDetails(int $workspaceId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare('SELECT workspace_status, wedding_date, activated_at FROM workspaces WHERE id = :workspace_id LIMIT 1');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row !== false ? $row : null;
    }

    /**
     * Update workspace status and sets associated transition timestamps.
     *
     * @param int    $workspaceId
     * @param string $status
     * @return void
     */
    public function updateWorkspaceStatus(int $workspaceId, string $status): void
    {
        $db = Database::getConnection();
        $sql = 'UPDATE workspaces SET workspace_status = :status, updated_at = CURRENT_TIMESTAMP';
        $params = ['status' => $status, 'workspace_id' => $workspaceId];
        
        if ($status === 'live') {
            $sql .= ', wedding_day_at = CURRENT_TIMESTAMP';
        } elseif ($status === 'archived') {
            $sql .= ', archived_at = CURRENT_TIMESTAMP';
        } elseif ($status === 'purged') {
            $sql .= ', purged_at = CURRENT_TIMESTAMP';
        }
        
        $sql .= ' WHERE id = :workspace_id';
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
    }
}


