<?php

namespace Modules\Appointments\Models;

use core\Model;
use core\Database;

class Appointment extends Model
{
    protected string $table = 'appointments';
    protected string $primaryKey = 'id';
    protected array $fillable = [
        'customer_id',
        'start_time',
        'end_time',
        'status',
        'notes',
        'created_at',
        'updated_at',
    ];

    protected array $guarded = ['id'];
    protected array $casts = [
        'start_time' => 'datetime',
        'end_time' => 'datetime',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
    ];

    public function getTodayAppointments(): array
    {
        $db = Database::getInstance();
        $today = date('Y-m-d');

        return $db->query(
            "SELECT a.*, c.first_name as customer_first_name, c.last_name as customer_last_name,
                    GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_names,
                    GROUP_CONCAT(DISTINCT CONCAT(e.first_name, ' ', e.last_name) SEPARATOR ', ') as employee_names
             FROM appointments a
             INNER JOIN customers c ON a.customer_id = c.id
             LEFT JOIN appointment_services aps ON a.id = aps.appointment_id
             LEFT JOIN services s ON aps.service_id = s.id
             LEFT JOIN employees e ON aps.employee_id = e.id
             WHERE DATE(a.start_time) = :today AND a.deleted_at IS NULL
             GROUP BY a.id
             ORDER BY a.start_time ASC",
            [':today' => $today]
        );
    }

    public function getByDateRange(string $startDate, string $endDate): array
    {
        $db = Database::getInstance();

        return $db->query(
            "SELECT a.*, c.first_name as customer_first_name, c.last_name as customer_last_name,
                    GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_names,
                    GROUP_CONCAT(DISTINCT CONCAT(e.first_name, ' ', e.last_name) SEPARATOR ', ') as employee_names
             FROM appointments a
             INNER JOIN customers c ON a.customer_id = c.id
             LEFT JOIN appointment_services aps ON a.id = aps.appointment_id
             LEFT JOIN services s ON aps.service_id = s.id
             LEFT JOIN employees e ON aps.employee_id = e.id
             WHERE DATE(a.start_time) >= :start_date
               AND DATE(a.start_time) <= :end_date
               AND a.deleted_at IS NULL
             GROUP BY a.id
             ORDER BY a.start_time ASC",
            [':start_date' => $startDate, ':end_date' => $endDate]
        );
    }

    public function getByCustomer(int $customerId): array
    {
        $db = Database::getInstance();

        return $db->query(
            "SELECT a.*, GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_names,
                    GROUP_CONCAT(DISTINCT CONCAT(e.first_name, ' ', e.last_name) SEPARATOR ', ') as employee_names
             FROM appointments a
             LEFT JOIN appointment_services aps ON a.id = aps.appointment_id
             LEFT JOIN services s ON aps.service_id = s.id
             LEFT JOIN employees e ON aps.employee_id = e.id
             WHERE a.customer_id = :customer_id AND a.deleted_at IS NULL
             GROUP BY a.id
             ORDER BY a.start_time DESC",
            [':customer_id' => $customerId]
        );
    }

    public function getByEmployee(int $employeeId): array
    {
        $db = Database::getInstance();

        return $db->query(
            "SELECT a.*, c.first_name as customer_first_name, c.last_name as customer_last_name,
                    GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_names
             FROM appointments a
             INNER JOIN customers c ON a.customer_id = c.id
             INNER JOIN appointment_services aps ON a.id = aps.appointment_id
             LEFT JOIN services s ON aps.service_id = s.id
             WHERE aps.employee_id = :employee_id AND a.deleted_at IS NULL
             GROUP BY a.id
             ORDER BY a.start_time DESC",
            [':employee_id' => $employeeId]
        );
    }

    public function getCalendarEvents(string $start, string $end): array
    {
        $db = Database::getInstance();

        $appointments = $db->query(
            "SELECT a.id, a.start_time, a.end_time, a.status,
                    c.first_name as customer_first_name, c.last_name as customer_last_name,
                    GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_names,
                    GROUP_CONCAT(DISTINCT s.color SEPARATOR ',') as service_colors
             FROM appointments a
             INNER JOIN customers c ON a.customer_id = c.id
             LEFT JOIN appointment_services aps ON a.id = aps.appointment_id
             LEFT JOIN services s ON aps.service_id = s.id
             WHERE a.start_time >= :start
               AND a.start_time <= :end
               AND a.deleted_at IS NULL
             GROUP BY a.id
             ORDER BY a.start_time ASC",
            [':start' => $start, ':end' => $end]
        );

        $events = [];
        $statusColors = [
            'pending' => '#FFC107',
            'confirmed' => '#17A2B8',
            'checked_in' => '#28A745',
            'in_progress' => '#007BFF',
            'completed' => '#6C757D',
            'cancelled' => '#DC3545',
            'no_show' => '#343A40',
        ];

        foreach ($appointments as $apt) {
            $colors = array_filter(explode(',', $apt->service_colors ?? ''));
        $bgColor = !empty($colors) ? $colors[0] : ($statusColors[$apt->status] ?? '#C8A44E');

            $events[] = [
                'id' => $apt->id,
                'title' => $apt->customer_first_name . ' ' . $apt->customer_last_name . ' - ' . ($apt->service_names ?? ''),
                'start' => $apt->start_time,
                'end' => $apt->end_time,
                'backgroundColor' => $bgColor,
                'borderColor' => $bgColor,
                'textColor' => '#ffffff',
                'status' => $apt->status,
                'url' => url('appointments/show/' . $apt->id),
                'extendedProps' => [
                    'customer' => $apt->customer_first_name . ' ' . $apt->customer_last_name,
                    'services' => $apt->service_names ?? '',
                    'status' => $apt->status,
                ],
            ];
        }

        return $events;
    }
}
