<?php

namespace Modules\Customers\Models;

use core\Model;
use core\Database;

class Customer extends Model
{
    protected string $table = 'customers';
    protected string $primaryKey = 'id';
    protected array $fillable = [
        'first_name',
        'last_name',
        'email',
        'phone',
        'mobile',
        'date_of_birth',
        'gender',
        'address',
        'city',
        'state',
        'zip',
        'notes',
        'allergies',
        'hair_history',
        'skin_notes',
        'source',
        'status',
        'branch_id',
        'photo',
        'created_at',
        'updated_at',
    ];

    protected array $guarded = ['id'];
    protected array $casts = [
        'date_of_birth' => 'date',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
        'deleted_at' => 'datetime',
    ];

    public function search(string $query): array
    {
        if (empty($query)) {
            return [];
        }

        $db = Database::getInstance();
        $searchTerm = '%' . $query . '%';

        return $db->table($this->table)
            ->select('id, first_name, last_name, email, phone, photo')
            ->where('deleted_at', 'IS', null)
            ->where(function ($q) use ($searchTerm) {
                $q->where('first_name', 'LIKE', $searchTerm)
                  ->orWhere('last_name', 'LIKE', $searchTerm)
                  ->orWhere('email', 'LIKE', $searchTerm)
                  ->orWhere('phone', 'LIKE', $searchTerm);
            })
            ->orderBy('first_name', 'ASC')
            ->limit(20)
            ->get();
    }

    public function getWithRecentActivity(): array
    {
        $db = Database::getInstance();

        return $db->table($this->table . ' c')
            ->select('c.*,
                (SELECT MAX(a.created_at) FROM appointments a WHERE a.customer_id = c.id AND a.deleted_at IS NULL) as last_activity,
                (SELECT COUNT(*) FROM appointments a WHERE a.customer_id = c.id AND a.deleted_at IS NULL) as visit_count')
            ->where('c.deleted_at', 'IS', null)
            ->having('last_activity IS NOT NULL')
            ->orderBy('last_activity', 'DESC')
            ->limit(10)
            ->get();
    }

    public function getTotalCustomersCount(): int
    {
        return $this->db->table($this->table)
            ->where('deleted_at', 'IS', null)
            ->count();
    }

    public function getCustomerStats(int $id): object
    {
        $db = Database::getInstance();

        $totalVisits = $db->table('appointments')
            ->where('customer_id', $id)
            ->where('deleted_at', 'IS', null)
            ->count();

        $totalSpent = $db->table('invoices')
            ->where('customer_id', $id)
            ->where('deleted_at', 'IS', null)
            ->whereIn('status', ['paid', 'partial'])
            ->sum('total');

        $lastVisit = $db->table('appointments')
            ->where('customer_id', $id)
            ->where('deleted_at', 'IS', null)
            ->orderBy('start_time', 'DESC')
            ->first();

        $membership = $db->table('customer_memberships')
            ->where('customer_id', $id)
            ->where('status', 'active')
            ->first();

        $loyaltyPoints = $db->table('loyalty_points')
            ->where('customer_id', $id)
            ->sum('balance');

        return (object)[
            'total_visits' => $totalVisits,
            'total_spent' => $totalSpent,
            'last_visit' => $lastVisit?->start_time,
            'last_visit_date' => $lastVisit ? date(DATE_DISPLAY_FORMAT, strtotime($lastVisit->start_time)) : null,
            'membership' => $membership,
            'loyalty_points' => $loyaltyPoints ?? 0,
        ];
    }
}
