<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;
use PDO;

final class UserModel extends BaseModel
{
    protected string $table = 'users';

    protected array $fillable = [
        'role_id', 'department_id', 'first_name', 'last_name', 'email', 'phone',
        'job_title', 'password_hash', 'avatar_path', 'status', 'timezone',
        'two_factor_enabled', 'two_factor_secret',
    ];

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

    /** Full user row with role/department names joined in, for display purposes. Password hash stripped. */
    public function findWithRelations(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT u.*, r.name AS role_name, r.slug AS role_slug, d.name AS department_name
             FROM users u
             LEFT JOIN roles r ON r.id = u.role_id
             LEFT JOIN departments d ON d.id = u.department_id
             WHERE u.id = :id AND u.deleted_at IS NULL'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();

        if ($row === false) {
            return null;
        }

        unset($row['password_hash'], $row['two_factor_secret']);

        return $row;
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM users WHERE email = :email AND deleted_at IS NULL LIMIT 1');
        $stmt->execute(['email' => $email]);
        $row = $stmt->fetch();

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

    public function emailTaken(string $email, ?int $exceptUserId = null): bool
    {
        $sql = 'SELECT COUNT(*) FROM users WHERE email = :email AND deleted_at IS NULL';
        $params = ['email' => $email];

        if ($exceptUserId !== null) {
            $sql .= ' AND id != :id';
            $params['id'] = $exceptUserId;
        }

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

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

    /** Paginated user list with role/department names and business line access, filterable by search/status/role. */
    public function paginatedList(array $filters, \App\Helpers\Paginator $paginator): array
    {
        [$where, $bindings] = $this->buildFilterClause($filters);

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

        $sql = "SELECT u.id, u.first_name, u.last_name, u.email, u.phone, u.job_title, u.status,
                       u.avatar_path, u.last_login_at, u.created_at,
                       r.name AS role_name, r.slug AS role_slug, d.name AS department_name,
                       GROUP_CONCAT(DISTINCT bl.name ORDER BY bl.name SEPARATOR ', ') AS business_lines
                FROM users u
                LEFT JOIN roles r ON r.id = u.role_id
                LEFT JOIN departments d ON d.id = u.department_id
                LEFT JOIN user_business_lines ubl ON ubl.user_id = u.id
                LEFT JOIN business_lines bl ON bl.id = ubl.business_line_id
                {$where}
                GROUP BY u.id
                ORDER BY u.created_at DESC
                LIMIT :limit OFFSET :offset";

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

        return $stmt->fetchAll();
    }

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

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

        if (!empty($filters['status'])) {
            $conditions[] = 'u.status = :status';
            $bindings['status'] = $filters['status'];
        }

        if (!empty($filters['role_id'])) {
            $conditions[] = 'u.role_id = :role_id';
            $bindings['role_id'] = $filters['role_id'];
        }

        if (!empty($filters['department_id'])) {
            $conditions[] = 'u.department_id = :department_id';
            $bindings['department_id'] = $filters['department_id'];
        }

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