<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;
use App\Helpers\Paginator;
use PDO;

final class CustomerModel extends BaseModel
{
    protected string $table = 'customers';

    protected array $fillable = [
        'business_line_id', 'company_id', 'source_lead_id', 'service_plan_id', 'assigned_staff_id',
        'first_name', 'last_name', 'email', 'phone', 'address_line1', 'address_line2',
        'city', 'province', 'postal_code', 'country', 'status', 'currency', 'customer_since',
    ];

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

    public function findWithRelations(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT cu.*, bl.name AS business_line_name, co.name AS company_name,
                    sp.name AS plan_name, CONCAT(u.first_name, " ", u.last_name) AS staff_name
             FROM customers cu
             LEFT JOIN business_lines bl ON bl.id = cu.business_line_id
             LEFT JOIN companies co ON co.id = cu.company_id
             LEFT JOIN service_plans sp ON sp.id = cu.service_plan_id
             LEFT JOIN users u ON u.id = cu.assigned_staff_id
             WHERE cu.id = :id AND cu.deleted_at IS NULL'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();

        return $row === false ? null : $row;
    }

    public function paginatedList(array $filters, Paginator $paginator): array
    {
        [$where, $bindings] = $this->buildFilterClause($filters);

        $countStmt = $this->db->prepare("SELECT COUNT(*) FROM customers cu {$where}");
        $countStmt->execute($bindings);
        $paginator->setTotal((int) $countStmt->fetchColumn());

        $sql = "SELECT cu.*, bl.name AS business_line_name, sp.name AS plan_name, co.name AS company_name
                FROM customers cu
                LEFT JOIN business_lines bl ON bl.id = cu.business_line_id
                LEFT JOIN service_plans sp ON sp.id = cu.service_plan_id
                LEFT JOIN companies co ON co.id = cu.company_id
                {$where}
                ORDER BY cu.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($bindings as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue('limit', $paginator->perPage(), PDO::PARAM_INT);
        $stmt->bindValue('offset', $paginator->offset(), PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /** Recalculates and caches lifetime_value from paid invoices — call after any payment is recorded. */
    public function recalculateLifetimeValue(int $customerId): void
    {
        $stmt = $this->db->prepare(
            "SELECT COALESCE(SUM(p.amount), 0) FROM payments p
             JOIN invoices i ON i.id = p.invoice_id
             WHERE i.customer_id = :id"
        );
        $stmt->execute(['id' => $customerId]);
        $total = (float) $stmt->fetchColumn();

        $this->update($customerId, ['lifetime_value' => $total]);
    }

    public function forSelect(): array
    {
        return $this->db->query(
            'SELECT id, CONCAT(first_name, " ", COALESCE(last_name, "")) AS name FROM customers WHERE deleted_at IS NULL ORDER BY first_name'
        )->fetchAll();
    }

    private function buildFilterClause(array $filters): array
    {
        $conditions = ['cu.deleted_at IS NULL'];
        $bindings = [];

        if (!empty($filters['search'])) {
            $conditions[] = '(cu.first_name LIKE :search1 OR cu.last_name LIKE :search2 OR cu.email LIKE :search3 OR cu.phone LIKE :search4)';
            $searchTerm = '%' . $filters['search'] . '%';
            $bindings['search1'] = $searchTerm;
            $bindings['search2'] = $searchTerm;
            $bindings['search3'] = $searchTerm;
            $bindings['search4'] = $searchTerm;
        }
        if (!empty($filters['business_line_id'])) {
            $conditions[] = 'cu.business_line_id = :bl';
            $bindings['bl'] = $filters['business_line_id'];
        }
        if (!empty($filters['status'])) {
            $conditions[] = 'cu.status = :status';
            $bindings['status'] = $filters['status'];
        }

        return ['WHERE ' . implode(' AND ', $conditions), $bindings];
    }
}