<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

use ApiModel\Domain\Repository\UserRepositoryInterface;
use ApiModel\Infrastructure\Persistence\Database;
use ApiModel\Shared\Exceptions\BadRequestException;
use ApiModel\Shared\Exceptions\NotFoundException;
use PDO;

final class PdoUserRepository implements UserRepositoryInterface
{
    private ?bool $photoColumnSupported = null;

    public function __construct(private readonly PDO $pdo) {}

    public function findProfileById(string $userId): array
    {
        $sql = $this->supportsPhotoColumn()
            ? 'SELECT id, name, email, language, photo, ferreteria_id FROM auth.users WHERE id = ?'
            : 'SELECT id, name, email, language, ferreteria_id FROM auth.users WHERE id = ?';
        $st = $this->pdo->prepare($sql);
        $st->execute([$userId]);
        $row = $st->fetch();
        if (! $row) {
            throw new NotFoundException();
        }

        // Obtener los roles del usuario
        $rst = $this->pdo->prepare(
            'SELECT r.name_en FROM auth.roles r 
             JOIN auth.role_user ru ON ru.role_id = r.id 
             WHERE ru.user_id = ?'
        );
        $rst->execute([$userId]);
        $roles = $rst->fetchAll(PDO::FETCH_COLUMN);

        $row['role'] = implode(',', $roles);
        $row['photo'] = $this->supportsPhotoColumn()
            ? self::formatPhotoForClient($row['photo'] ?? null)
            : null;

        return $row;
    }

    public function updatePhoto(string $userId, ?string $photo): void
    {
        if (! $this->supportsPhotoColumn()) {
            throw new BadRequestException(
                'La columna photo no existe en auth.users. Ejecute: ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS photo TEXT NULL;',
            );
        }

        $this->pdo->prepare(
            'UPDATE auth.users SET photo = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        )->execute([$photo, $userId]);
    }

    private function supportsPhotoColumn(): bool
    {
        if ($this->photoColumnSupported !== null) {
            return $this->photoColumnSupported;
        }

        try {
            $this->pdo->query('SELECT photo FROM auth.users WHERE 1 = 0');
            $this->photoColumnSupported = true;
        } catch (\PDOException) {
            $this->photoColumnSupported = false;
        }

        return $this->photoColumnSupported;
    }

    public static function formatPhotoForClient(?string $photo): ?string
    {
        if ($photo === null || trim($photo) === '') {
            return null;
        }

        if (str_starts_with($photo, 'data:image/')) {
            return $photo;
        }

        return 'data:image/jpeg;base64,' . $photo;
    }

    public function updateLanguage(string $userId, string $lang): array
    {
        $this->pdo->prepare('UPDATE auth.users SET language = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?')->execute([$lang, $userId]);

        return ['language' => $lang];
    }

    public function findFullByEmail(string $email): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM auth.users WHERE email = ?');
        $st->execute([$email]);
        $row = $st->fetch();

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

        // Obtener los roles del usuario
        $rst = $this->pdo->prepare(
            'SELECT r.name_en FROM auth.roles r 
             JOIN auth.role_user ru ON ru.role_id = r.id 
             WHERE ru.user_id = ?'
        );
        $rst->execute([$row['id']]);
        $roles = $rst->fetchAll(PDO::FETCH_COLUMN);

        $row['role'] = implode(',', $roles);
        return $row;
    }

    public function findFullById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM auth.users WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();

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

        // Obtener los roles del usuario
        $rst = $this->pdo->prepare(
            'SELECT r.name_en FROM auth.roles r 
             JOIN auth.role_user ru ON ru.role_id = r.id 
             WHERE ru.user_id = ?'
        );
        $rst->execute([$id]);
        $roles = $rst->fetchAll(PDO::FETCH_COLUMN);

        $row['role'] = implode(',', $roles);
        return $row;
    }

    public function updateUserAfterExternalSync(string $id, string $name, string $externalId): void
    {
        $this->pdo->prepare(
            'UPDATE auth.users SET name = ?, external_id = ?, is_active = TRUE, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        )->execute([$name, $externalId, $id]);
    }

    public function insertClientUser(string $id, string $name, string $email, string $externalId, string $language): void
    {
        $this->pdo->prepare(
            'INSERT INTO auth.users (id, name, email, password_hash, external_id, language, is_active)
             VALUES (?,?,?,NULL,?,?,TRUE)',
        )->execute([$id, $name, $email, $externalId, $language]);

        $roleId = $this->findOrCreateRoleByName('CLIENTE');
        $this->pdo->prepare('INSERT INTO auth.role_user (role_id, user_id) VALUES (?, ?)')
            ->execute([$roleId, $id]);
    }

    public function emailExists(string $email): bool
    {
        $chk = $this->pdo->prepare('SELECT 1 FROM auth.users WHERE email = ?');
        $chk->execute([$email]);

        return (bool) $chk->fetch();
    }

    public function insertUserWithPassword(string $id, string $name, string $email, string $passwordHash, string $role, string $language): void
    {
        $this->pdo->prepare(
            'INSERT INTO auth.users (id, name, email, password_hash, language, is_active) VALUES (?,?,?,?,?,TRUE)',
        )->execute([$id, $name, $email, $passwordHash, $language]);

        $roleId = $this->findOrCreateRoleByName($role);
        $this->pdo->prepare('INSERT INTO auth.role_user (role_id, user_id) VALUES (?, ?)')
            ->execute([$roleId, $id]);
    }

    public function insertUserWithoutPassword(string $id, string $name, string $email, string $language): void
    {
        $this->pdo->prepare(
            'INSERT INTO auth.users (id, name, email, password_hash, language, is_active) VALUES (?,?,?,NULL,?,TRUE)',
        )->execute([$id, $name, $email, $language]);
    }

    public function updateUserName(string $id, string $name): void
    {
        $this->pdo->prepare('UPDATE auth.users SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?')->execute([$name, $id]);
    }

    public function updateUserEmail(string $id, string $email): void
    {
        $this->pdo->prepare('UPDATE auth.users SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?')->execute([$email, $id]);
    }

    public function updateUserRole(string $id, string $role): void
    {
        // Eliminar roles anteriores
        $this->pdo->prepare('DELETE FROM auth.role_user WHERE user_id = ?')->execute([$id]);

        // Asignar nuevo rol
        $roleId = $this->findOrCreateRoleByName($role);
        $this->pdo->prepare('INSERT INTO auth.role_user (role_id, user_id) VALUES (?, ?)')
            ->execute([$roleId, $id]);
    }

    public function updateUserIsActive(string $id, bool $isActive): void
    {
        $this->pdo->prepare('UPDATE auth.users SET is_active = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?')->execute([Database::bool($isActive), $id]);
    }

    public function updatePasswordHash(string $id, string $passwordHash): void
    {
        $this->pdo->prepare('UPDATE auth.users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?')->execute([$passwordHash, $id]);
    }

    public function countUsersForAdmin(?string $searchQuery): int
    {
        $where = '';
        $params = [];
        if ($searchQuery !== null && trim($searchQuery) !== '') {
            $where = ' WHERE (LOWER(email) LIKE ? OR LOWER(name) LIKE ?)';
            $t = '%' . strtolower(trim($searchQuery)) . '%';
            $params = [$t, $t];
        }
        $cst = $this->pdo->prepare('SELECT COUNT(*) FROM auth.users' . $where);
        $cst->execute($params);

        return (int) $cst->fetchColumn();
    }

    public function listUsersForAdmin(int $limit, int $offset, ?string $searchQuery): array
    {
        $where = '';
        $params = [];
        if ($searchQuery !== null && trim($searchQuery) !== '') {
            $where = ' WHERE (LOWER(email) LIKE ? OR LOWER(name) LIKE ?)';
            $t = '%' . strtolower(trim($searchQuery)) . '%';
            $params = [$t, $t];
        }
        $sql = 'SELECT id, name, email, is_active, language, created_at, ferreteria_id FROM auth.users' . $where . ' ORDER BY created_at DESC LIMIT ? OFFSET ?';
        $st = $this->pdo->prepare($sql);
        $st->execute([...$params, $limit, $offset]);
        $out = [];
        foreach ($st->fetchAll() as $u) {
            // Obtener roles de este usuario (IDs y Nombres)
            $rst = $this->pdo->prepare(
                'SELECT r.id, r.name_en FROM auth.roles r 
                 JOIN auth.role_user ru ON ru.role_id = r.id 
                 WHERE ru.user_id = ?'
            );
            $rst->execute([$u['id']]);
            $roleRows = $rst->fetchAll();

            $roleIds = array_column($roleRows, 'id');
            $roleNames = array_column($roleRows, 'name_en');

            // Obtener ferretería asociada (casamiento)
            $ferreteriaNombre = '';
            if (!empty($u['ferreteria_id'])) {
                $fst = $this->pdo->prepare('SELECT nombre FROM public.ferreterias WHERE id = ?');
                $fst->execute([$u['ferreteria_id']]);
                $ferreteriaNombre = $fst->fetchColumn() ?: '';
            }

            $out[] = [
                'id' => $u['id'],
                'name' => $u['name'],
                'email' => $u['email'],
                'role' => implode(',', $roleNames),
                'viewroles' => json_encode($roleIds),
                'companies' => $ferreteriaNombre,
                'ferreteria_id' => $u['ferreteria_id'],
                'is_active' => Database::fromBool($u['is_active']),
                'activo' => Database::fromBool($u['is_active']),
                'language' => $u['language'],
                'created_at' => $u['created_at'],
            ];
        }

        return $out;
    }

    public function updateUserRoles(string $userId, array $roleIds): void
    {
        $this->pdo->prepare('DELETE FROM auth.role_user WHERE user_id = ?')->execute([$userId]);
        $st = $this->pdo->prepare('INSERT INTO auth.role_user (role_id, user_id) VALUES (?, ?)');
        foreach ($roleIds as $roleId) {
            $st->execute([$roleId, $userId]);
        }
    }

    public function deleteUser(string $userId): void
    {
        $this->pdo->prepare('DELETE FROM auth.users WHERE id = ?')->execute([$userId]);
    }

    public function updateUserFerreteria(string $id, ?string $ferreteriaId): void
    {
        $this->pdo->prepare('UPDATE auth.users SET ferreteria_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?')
            ->execute([$ferreteriaId, $id]);
    }

    private function findOrCreateRoleByName(string $roleName): string
    {
        $st = $this->pdo->prepare('SELECT id FROM auth.roles WHERE name_en = ? OR name_es = ?');
        $st->execute([$roleName, $roleName]);
        $roleId = $st->fetchColumn();
        if ($roleId) {
            return (string) $roleId;
        }

        $uuid = Database::uuid();
        $this->pdo->prepare(
            'INSERT INTO auth.roles (id, name_es, name_en, description_es, description_en) 
             VALUES (?, ?, ?, ?, ?)'
        )->execute([$uuid, $roleName, $roleName, 'Rol ' . $roleName, 'Role ' . $roleName]);

        return $uuid;
    }
}
