<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;

final class AppointmentModel extends BaseModel
{
    protected string $table = 'appointments';

    protected array $fillable = [
        'business_line_id', 'customer_id', 'lead_id', 'parent_appointment_id', 'service_type_id',
        'assigned_staff_id', 'scheduled_start', 'scheduled_end', 'status', 'location_address', 'notes',
        'cancelled_reason', 'recurrence_rule', 'is_recurring_parent', 'reminder_minutes_before',
    ];

    protected bool $softDeletes = true;
    protected bool $auditStamps = true;

    /** All appointments overlapping [start, end) — the calendar view's core query, day/week/month all just vary the range. */
    public function inRange(string $start, string $end, array $filters = []): array
    {
        $conditions = ['a.deleted_at IS NULL', 'a.scheduled_start < :end', 'a.scheduled_end > :start'];
        $bindings = ['start' => $start, 'end' => $end];

        if (!empty($filters['business_line_id'])) {
            $conditions[] = 'a.business_line_id = :bl';
            $bindings['bl'] = $filters['business_line_id'];
        }
        if (!empty($filters['assigned_staff_id'])) {
            $conditions[] = 'a.assigned_staff_id = :staff';
            $bindings['staff'] = $filters['assigned_staff_id'];
        }
        if (!empty($filters['status'])) {
            $conditions[] = 'a.status = :status';
            $bindings['status'] = $filters['status'];
        }

        $sql = 'SELECT a.*, bl.name AS business_line_name, st.name AS service_name,
                       CONCAT_WS(\' \', u.first_name, u.last_name) AS staff_name,
                       CONCAT_WS(\' \', cu.first_name, cu.last_name) AS customer_name,
                       CONCAT_WS(\' \', l.first_name, l.last_name) AS lead_name
                FROM appointments a
                LEFT JOIN business_lines bl ON bl.id = a.business_line_id
                LEFT JOIN service_types st ON st.id = a.service_type_id
                LEFT JOIN users u ON u.id = a.assigned_staff_id
                LEFT JOIN customers cu ON cu.id = a.customer_id
                LEFT JOIN leads l ON l.id = a.lead_id
                WHERE ' . implode(' AND ', $conditions) . '
                ORDER BY a.scheduled_start';

        $stmt = $this->db->prepare($sql);
        $stmt->execute($bindings);

        return $stmt->fetchAll();
    }

    /** True if the staff member has a conflicting, non-cancelled appointment in this window (optionally excluding one appointment, e.g. when rescheduling it). */
    public function hasConflict(int $staffId, string $start, string $end, ?int $excludeId = null): bool
    {
        $sql = "SELECT COUNT(*) FROM appointments
                WHERE assigned_staff_id = :staff AND deleted_at IS NULL AND status NOT IN ('cancelled')
                AND scheduled_start < :end AND scheduled_end > :start";
        $bindings = ['staff' => $staffId, 'start' => $start, 'end' => $end];

        if ($excludeId !== null) {
            $sql .= ' AND id != :exclude';
            $bindings['exclude'] = $excludeId;
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($bindings);

        return (int) $stmt->fetchColumn() > 0;
    }

    public function historyFor(string $entityType, int $entityId): array
    {
        $column = $entityType === 'customer' ? 'customer_id' : 'lead_id';

        $stmt = $this->db->prepare(
            "SELECT a.*, st.name AS service_name, CONCAT(u.first_name, ' ', u.last_name) AS staff_name
             FROM appointments a
             LEFT JOIN service_types st ON st.id = a.service_type_id
             LEFT JOIN users u ON u.id = a.assigned_staff_id
             WHERE a.{$column} = :id AND a.deleted_at IS NULL
             ORDER BY a.scheduled_start DESC"
        );
        $stmt->execute(['id' => $entityId]);

        return $stmt->fetchAll();
    }

    public function childOccurrences(int $parentId): array
    {
        $stmt = $this->db->prepare('SELECT * FROM appointments WHERE parent_appointment_id = :id AND deleted_at IS NULL ORDER BY scheduled_start');
        $stmt->execute(['id' => $parentId]);

        return $stmt->fetchAll();
    }

    /** Appointments due a reminder: status=scheduled, not yet reminded, starting within the next N minutes. */
    public function dueForReminder(int $withinMinutes): array
    {
        $stmt = $this->db->prepare(
            "SELECT a.*, CONCAT(cu.first_name, ' ', cu.last_name) AS customer_name, cu.email AS customer_email, cu.phone AS customer_phone
             FROM appointments a
             LEFT JOIN customers cu ON cu.id = a.customer_id
             WHERE a.status = 'scheduled' AND a.reminder_sent_at IS NULL AND a.deleted_at IS NULL
               AND a.scheduled_start BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL :minutes MINUTE)"
        );
        $stmt->execute(['minutes' => $withinMinutes]);

        return $stmt->fetchAll();
    }

    public function markReminderSent(int $id): void
    {
        $this->db->prepare('UPDATE appointments SET reminder_sent_at = NOW() WHERE id = :id')->execute(['id' => $id]);
    }
}