<?php

namespace Core\App\Models;

use PDO;

class TeamMeetingModel
{
    private PDO $db;

    public function __construct()
    {
        $this->db = \db();
        $this->ensureSchema();
    }

    /**
     * Get all meetings — includes current user's RSVP and attendee status
     */
    public function getAll(int $limit = 50, int $currentAdminId = 0): array
    {
        $stmt = $this->db->prepare("
            SELECT m.*,
                   a.username AS creator_name, a.avatar AS creator_avatar,
                   (SELECT COUNT(*) FROM team_meeting_attendees WHERE meeting_id = m.id) AS attendee_count,
                   (SELECT COUNT(*) FROM team_meeting_attendees WHERE meeting_id = m.id AND rsvp = 'accepted') AS accepted_count,
                   (SELECT rsvp FROM team_meeting_attendees WHERE meeting_id = m.id AND admin_id = :cid1) AS my_rsvp,
                   (SELECT COUNT(*) FROM team_meeting_attendees WHERE meeting_id = m.id AND admin_id = :cid2) AS is_attendee
            FROM team_meetings m
            JOIN admins a ON a.id = m.created_by
            ORDER BY m.scheduled_at DESC
            LIMIT :lim
        ");
        $stmt->bindValue(':lim',  $limit,          PDO::PARAM_INT);
        $stmt->bindValue(':cid1', $currentAdminId, PDO::PARAM_INT);
        $stmt->bindValue(':cid2', $currentAdminId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get upcoming meetings only
     */
    public function getUpcoming(int $currentAdminId = 0): array
    {
        $stmt = $this->db->prepare("
            SELECT m.*,
                   a.username AS creator_name,
                   (SELECT COUNT(*) FROM team_meeting_attendees WHERE meeting_id = m.id AND rsvp = 'accepted') AS accepted_count,
                   (SELECT rsvp FROM team_meeting_attendees WHERE meeting_id = m.id AND admin_id = :cid) AS my_rsvp
            FROM team_meetings m
            JOIN admins a ON a.id = m.created_by
            WHERE m.status = 'upcoming' AND m.scheduled_at >= UTC_TIMESTAMP()
            ORDER BY m.scheduled_at ASC
            LIMIT 5
        ");
        $stmt->bindValue(':cid', $currentAdminId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get a single meeting with attendees
     */
    public function getById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT m.*, a.username AS creator_name
            FROM team_meetings m
            JOIN admins a ON a.id = m.created_by
            WHERE m.id = :id
        ");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) return null;

        // Fetch attendees
        $stmt2 = $this->db->prepare("
            SELECT ta.*, adm.username, adm.avatar, adm.role
            FROM team_meeting_attendees ta
            JOIN admins adm ON adm.id = ta.admin_id
            WHERE ta.meeting_id = :mid
            ORDER BY adm.username ASC
        ");
        $stmt2->execute([':mid' => $id]);
        $row['attendees'] = $stmt2->fetchAll(PDO::FETCH_ASSOC);

        return $row;
    }

    /**
     * Create a meeting and set attendees
     * Auto-sets has_group_chat=1 when type='group_chat'
     */
    public function create(array $data, array $attendeeIds): int
    {
        // Auto-enable group chat when type is group_chat
        if (($data['meeting_type'] ?? '') === 'group_chat') {
            $data['has_group_chat'] = 1;
        }

        $stmt = $this->db->prepare("
            INSERT INTO team_meetings
                (created_by, title, description, meeting_type, scheduled_at, duration_minutes,
                 location, has_group_chat, reminder_minutes, last_activity_at)
            VALUES
                (:created_by, :title, :desc, :type, :scheduled, :duration,
 :location, :group_chat, :reminder, UTC_TIMESTAMP())
        ");
        $stmt->execute([
            ':created_by'  => $data['created_by'],
            ':title'       => $data['title'],
            ':desc'        => $data['description'] ?? null,
            ':type'        => $data['meeting_type'] ?? 'other',
            ':scheduled'   => $data['scheduled_at'],
            ':duration'    => $data['duration_minutes'] ?? 60,
            ':location'    => $data['location'] ?? null,
            ':group_chat'  => !empty($data['has_group_chat']) ? 1 : 0,
            ':reminder'    => max(0, (int)($data['reminder_minutes'] ?? 0)),
        ]);
        $meetingId = (int)$this->db->lastInsertId();

        $this->syncAttendees($meetingId, $attendeeIds);
        return $meetingId;
    }

    /**
     * Update a meeting
     */
    public function update(int $id, array $data, array $attendeeIds): void
    {
        if (($data['meeting_type'] ?? '') === 'group_chat') {
            $data['has_group_chat'] = 1;
        }

        $stmt = $this->db->prepare("
            UPDATE team_meetings
            SET title = :title, description = :desc, meeting_type = :type,
                scheduled_at = :scheduled, duration_minutes = :duration,
                location = :location, status = :status,
                has_group_chat = :group_chat, reminder_minutes = :reminder,
                last_activity_at = COALESCE(last_activity_at, UTC_TIMESTAMP())
            WHERE id = :id
        ");
        $stmt->execute([
            ':title'      => $data['title'],
            ':desc'       => $data['description'] ?? null,
            ':type'       => $data['meeting_type'] ?? 'other',
            ':scheduled'  => $data['scheduled_at'],
            ':duration'   => $data['duration_minutes'] ?? 60,
            ':location'   => $data['location'] ?? null,
            ':status'     => $data['status'] ?? 'upcoming',
            ':group_chat' => !empty($data['has_group_chat']) ? 1 : 0,
            ':reminder'   => max(0, (int)($data['reminder_minutes'] ?? 0)),
            ':id'         => $id,
        ]);
        $this->syncAttendees($id, $attendeeIds);
    }

    /**
     * Delete a meeting
     */
    public function delete(int $id): void
    {
        $stmt = $this->db->prepare("DELETE FROM team_meetings WHERE id = :id");
        $stmt->execute([':id' => $id]);
    }

    /**
     * RSVP for a meeting
     */
    public function rsvp(int $meetingId, int $adminId, string $rsvp): void
    {
        $stmt = $this->db->prepare("
            UPDATE team_meeting_attendees
            SET rsvp = :rsvp, responded_at = UTC_TIMESTAMP()
            WHERE meeting_id = :mid AND admin_id = :aid
        ");
        $stmt->execute([':rsvp' => $rsvp, ':mid' => $meetingId, ':aid' => $adminId]);
        $this->touchActivity($meetingId);
    }

    /**
     * Get RSVP status for a user on a meeting
     */
    public function getRsvp(int $meetingId, int $adminId): ?string
    {
        $stmt = $this->db->prepare("
            SELECT rsvp FROM team_meeting_attendees
            WHERE meeting_id = :mid AND admin_id = :aid
        ");
        $stmt->execute([':mid' => $meetingId, ':aid' => $adminId]);
        $row = $stmt->fetchColumn();
        return $row ?: null;
    }

    /**
     * Atomically claim meetings whose reminder is due.
     * Uses an UPDATE first to prevent race conditions when multiple
     * admin sessions trigger heartbeat simultaneously.
     * Returns the meetings we just claimed (within last 10 seconds).
     */
    public function claimDueReminders(): array
    {
        // Step 1: Atomic claim — sets reminder_sent_at only once per meeting
        $this->db->exec("
            UPDATE team_meetings
            SET reminder_sent_at = UTC_TIMESTAMP()
            WHERE status = 'upcoming'
              AND reminder_minutes > 0
              AND reminder_sent_at IS NULL
              AND scheduled_at > UTC_TIMESTAMP()
              AND DATE_SUB(scheduled_at, INTERVAL reminder_minutes MINUTE) <= UTC_TIMESTAMP()
        ");

        // Step 2: Fetch the meetings we just claimed in this short UTC window.
        $stmt = $this->db->prepare("
            SELECT m.*,
                   GROUP_CONCAT(DISTINCT ta.admin_id) AS attendee_ids
            FROM team_meetings m
            JOIN team_meeting_attendees ta ON ta.meeting_id = m.id
            WHERE m.reminder_sent_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 SECOND)
              AND m.status = 'upcoming'
            GROUP BY m.id
        ");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }


    public function getActiveReminderRecipients(int $meetingId, array $attendeeIds): array
    {
        if ($meetingId <= 0 || empty($attendeeIds)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($attendeeIds), '?'));
        $sql = "
            SELECT a.id, a.username, a.email, a.phone
            FROM admins a
            JOIN team_meeting_attendees ta
              ON ta.admin_id = a.id
             AND ta.meeting_id = ?
            WHERE a.is_active = 1
              AND a.id IN ({$placeholders})
            ORDER BY a.username ASC
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute(array_merge([$meetingId], array_values($attendeeIds)));

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Auto-update overdue meetings to 'completed'
     */
    public function markOverdueCompleted(): void
    {
        $this->db->exec("
            UPDATE team_meetings
            SET status = 'completed', updated_at = UTC_TIMESTAMP()
            WHERE status = 'upcoming'
              AND DATE_ADD(scheduled_at, INTERVAL duration_minutes MINUTE) < UTC_TIMESTAMP()
        ");

        // Keep auto-end stable: a meeting is completed automatically only after
        // its configured duration has fully elapsed. This avoids brand-new
        // meetings being marked completed immediately at start time.
    }


    public function complete(int $id, int $adminId = 0, string $reason = 'manual'): void
    {
        $stmt = $this->db->prepare("
            UPDATE team_meetings
            SET status = 'completed', updated_at = UTC_TIMESTAMP()
            WHERE id = :id AND status != 'completed'
        ");
        $stmt->execute([':id' => $id]);

        try {
            $log = $this->db->prepare("
                INSERT INTO security_logs (type, message, url, created_at)
                VALUES ('meeting_complete', :message, '/admin/team/meetings', UTC_TIMESTAMP())
            ");
            $log->execute([':message' => sprintf('meeting_id=%d admin_id=%d reason=%s', $id, $adminId, $reason)]);
        } catch (\Throwable $e) {
        }
    }

    public function touchActivity(int $meetingId): void
    {
        $stmt = $this->db->prepare("UPDATE team_meetings SET last_activity_at = UTC_TIMESTAMP() WHERE id = :id");
        $stmt->execute([':id' => $meetingId]);
    }

    public function createStartNotificationIfDue(): void
    {
        $stmt = $this->db->query("
            SELECT id, title FROM team_meetings
            WHERE status = 'upcoming'
              AND start_notification_sent_at IS NULL
              AND scheduled_at <= DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 MINUTE)
              AND scheduled_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 5 MINUTE)
            ORDER BY scheduled_at ASC
            LIMIT 20
        ");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        if (empty($rows)) {
            return;
        }

        $ins = $this->db->prepare("INSERT INTO admin_notifications (type, reference_id, title, is_read, archived) VALUES ('meeting_started', ?, ?, 0, 0)");
        $upd = $this->db->prepare("UPDATE team_meetings SET start_notification_sent_at = UTC_TIMESTAMP() WHERE id = ?");

        foreach ($rows as $row) {
            $ins->execute([(int)$row['id'], __('team_meeting.started_notification', 'Meeting is starting now') . ': ' . (string)$row['title']]);
            $upd->execute([(int)$row['id']]);
        }
    }

    private function ensureSchema(): void
    {
        try {
            $this->db->exec("
                ALTER TABLE team_meetings
                ADD COLUMN IF NOT EXISTS last_activity_at datetime DEFAULT NULL AFTER updated_at,
                ADD COLUMN IF NOT EXISTS start_notification_sent_at datetime DEFAULT NULL AFTER reminder_sent_at
            ");
        } catch (\Throwable $e) {
        }
    }

    /**
     * Sync attendees (delete all and re-insert)
     */
    private function syncAttendees(int $meetingId, array $adminIds): void
    {
        $stmt = $this->db->prepare("DELETE FROM team_meeting_attendees WHERE meeting_id = :mid");
        $stmt->execute([':mid' => $meetingId]);

        if (empty($adminIds)) return;

        $ins = $this->db->prepare("
            INSERT INTO team_meeting_attendees (meeting_id, admin_id) VALUES (:mid, :aid)
        ");
        foreach ($adminIds as $adminId) {
            $ins->execute([':mid' => $meetingId, ':aid' => (int)$adminId]);
        }
    }
}
