<?php

declare(strict_types=1);

namespace App\Models;

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

final class CompanyModel extends BaseModel
{
    protected string $table = 'companies';

    protected array $fillable = [
        'business_line_id', 'name', 'industry', 'website', 'phone',
        'address_line1', 'address_line2', 'city', 'province', 'postal_code', 'country',
        'contract_status', 'site_count', 'annual_value', 'currency', 'owner_id',
    ];

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

    public function findWithRelations(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT c.*, bl.name AS business_line_name, CONCAT(u.first_name, " ", u.last_name) AS owner_name,
                    (SELECT COUNT(*) FROM leads l WHERE l.company_id = c.id AND l.deleted_at IS NULL) AS lead_count,
                    (SELECT COUNT(*) FROM customers cu WHERE cu.company_id = c.id AND cu.deleted_at IS NULL) AS customer_count
             FROM companies c
             LEFT JOIN business_lines bl ON bl.id = c.business_line_id
             LEFT JOIN users u ON u.id = c.owner_id
             WHERE c.id = :id AND c.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 companies c {$where}");
        $countStmt->execute($bindings);
        $paginator->setTotal((int) $countStmt->fetchColumn());

        $sql = "SELECT c.*, bl.name AS business_line_name, CONCAT(u.first_name, ' ', u.last_name) AS owner_name
                FROM companies c
                LEFT JOIN business_lines bl ON bl.id = c.business_line_id
                LEFT JOIN users u ON u.id = c.owner_id
                {$where}
                ORDER BY c.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();
    }

    /** All companies for a lightweight <select> — id/name pairs only. */
    public function forSelect(): array
    {
        return $this->db->query('SELECT id, name FROM companies WHERE deleted_at IS NULL ORDER BY name')->fetchAll();
    }

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

        if (!empty($filters['search'])) {
            $conditions[] = '(c.name LIKE :search1 OR c.industry LIKE :search2)';
            $searchTerm = '%' . $filters['search'] . '%';
            $bindings['search1'] = $searchTerm;
            $bindings['search2'] = $searchTerm;
        }
        if (!empty($filters['business_line_id'])) {
            $conditions[] = 'c.business_line_id = :bl';
            $bindings['bl'] = $filters['business_line_id'];
        }
        if (!empty($filters['contract_status'])) {
            $conditions[] = 'c.contract_status = :status';
            $bindings['status'] = $filters['contract_status'];
        }

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