<?php

namespace App\Repositories;

use App\Models\User;
use Config\Database;
use PDO;

class UserRepository
{

    public function getUserRoles($userId)
    {
        $db = Database::getConnection();

        $sql = "SELECT *
            FROM roles r
            JOIN role_user ur ON ur.role_id = r.id
            WHERE ur.user_id = ?
            AND r.active = 1
            AND ur.active = 1";

        $stmt = $db->prepare($sql);
        $stmt->execute([$userId]);

        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }

    public static function findByUsername($username)
    {
        $db = Database::getConnection();
        $query = "SELECT * FROM users WHERE username = :username AND active = 1 LIMIT 1";
        $stmt = $db->prepare($query);
        $stmt->execute([':username' => $username]);

        $user = $stmt->fetch();
        return $user ? new User($user) : null;
    }


    // public function verifyPassword($password)
    // {
    //     return password_verify($password, $this->password);
    // }

    public function updateProfilePicture($fileName, $user)
    {
        $db = Database::getConnection();
        $query = "UPDATE users SET profile_picture = ? WHERE id = ?";
        $stmt = $db->prepare($query);
        return $stmt->execute([$fileName, $user->id]);
    }

    public function findComunityUserByDni($dni)
    {
        $db = Database::getConnection();
        $query = "SELECT id, name, paternal_surname, maternal_surname, phone, email FROM comunity_users WHERE dni = ?";
        $stmt = $db->prepare($query);
        $stmt->execute([$dni]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        return $user;
    }

    public function findComunityUserByRuc($ruc)
    {
        $db = Database::getConnection();
        $query = "SELECT id, company_name, ruc, phone, email FROM comunity_users WHERE ruc = ?";
        $stmt = $db->prepare($query);
        $stmt->execute([$ruc]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        return $user;
    }

    public function searchComunityUsersByName(string $q, int $limit = 20): array
    {
        $db = Database::getConnection();

        // Normaliza el término y arma patrón LIKE
        $like = '%' . $q . '%';

        // Única consulta con condiciones OR y COLLATE para case/diacritic-insensitive
        $sql = "
            SELECT
              id,
              name,
              paternal_surname,
              maternal_surname,
              dni,
              company_name,
              ruc,
              phone,
              email
            FROM comunity_users
            WHERE active = 1 AND deleted_at IS NULL
              AND (
                (name COLLATE utf8mb4_0900_ai_ci LIKE ?)
                OR (paternal_surname COLLATE utf8mb4_0900_ai_ci LIKE ?)
                OR (maternal_surname COLLATE utf8mb4_0900_ai_ci LIKE ?)
                OR (company_name COLLATE utf8mb4_0900_ai_ci LIKE ?)
              )
            ORDER BY
              -- Prioriza coincidencias más completas: empresa primero si hay razón social
              (company_name IS NOT NULL) DESC,
              paternal_surname IS NOT NULL DESC,
              maternal_surname IS NOT NULL DESC,
              name IS NOT NULL DESC,
              id DESC
            LIMIT ?
        ";

        $stmt = $db->prepare($sql);
        $stmt->bindValue(1, $like, PDO::PARAM_STR);
        $stmt->bindValue(2, $like, PDO::PARAM_STR);
        $stmt->bindValue(3, $like, PDO::PARAM_STR);
        $stmt->bindValue(4, $like, PDO::PARAM_STR);
        $stmt->bindValue(5, $limit, PDO::PARAM_INT);
        $stmt->execute();

        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Opcional: limpieza de strings para evitar nulls en frontend
        return array_map(function ($r) {
            return [
                'id' => (int)$r['id'],
                'name' => $r['name'] ?? '',
                'paternal_surname' => $r['paternal_surname'] ?? '',
                'maternal_surname' => $r['maternal_surname'] ?? '',
                'dni' => $r['dni'] ?? '',
                'company_name' => $r['company_name'] ?? '',
                'ruc' => $r['ruc'] ?? '',
                'phone' => $r['phone'] ?? '',
                'email' => $r['email'] ?? ''
            ];
        }, $rows);
    }

    public function searchComunityByDocument(string $document, int $limit = 5): array
    {
        $document = trim($document);
        if ($document === '') return ['type' => 'none'];

        $onlyDigits = preg_match('/^\d+$/', $document);

        // DNI (8)
        if ($onlyDigits && strlen($document) === 8) {
            $user = $this->findComunityUserByDni($document);
            if ($user) {
                return [
                    'type' => 'single',
                    'item' => [
                        'type' => 'dni',
                        'dni' => $document,
                        'name' => $user['name'] ?? null,
                        'paternal_surname' => $user['paternal_surname'] ?? null,
                        'maternal_surname' => $user['maternal_surname'] ?? null,
                        'phone' => $user['phone'] ?? null,
                        'email' => $user['email'] ?? null
                    ]
                ];
            }
            return ['type' => 'none'];
        }

        // RUC (11)
        if ($onlyDigits && strlen($document) === 11) {
            $db = \Config\Database::getConnection();

            // 1) filas con ruc = input
            $stmt = $db->prepare("SELECT id, company_name, ruc, rucglobal, email, phone FROM comunity_users WHERE ruc = ? AND deleted_at IS NULL");
            $stmt->execute([$document]);
            $rowsRuc = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            // 2) si no hay ruc = input, buscar por rucglobal = input
            if (empty($rowsRuc)) {
                $stmt2 = $db->prepare("SELECT id, company_name, ruc, rucglobal, email, phone FROM comunity_users WHERE rucglobal = ? AND deleted_at IS NULL");
                $stmt2->execute([$document]);
                $rowsGlobal = $stmt2->fetchAll(\PDO::FETCH_ASSOC);

                if (!empty($rowsGlobal)) {
                    $total = count($rowsGlobal);
                    // mapear y asegurar 'type' => 'ruc' en cada item
                    $mapped = array_map(function ($row) {
                        return [
                            'type' => 'ruc',
                            'ruc' => $row['ruc'] ?? null,
                            'company_name' => $row['company_name'] ?? null,
                            'email' => $row['email'] ?? null,
                            'phone' => $row['phone'] ?? null,
                            'rucglobal' => $row['rucglobal'] ?? null
                        ];
                    }, $rowsGlobal);

                    $items = array_slice($mapped, 0, $limit);

                    if ($total === 1) {
                        return [
                            'type' => 'single',
                            'item' => $items[0]
                        ];
                    }

                    return [
                        'type' => 'multiple',
                        'total_count' => $total,
                        'items' => $items
                    ];
                }

                return ['type' => 'none'];
            }

            // 3) si hay filas con ruc = input, buscar ramas cuyo rucglobal = input
            $stmt3 = $db->prepare("SELECT id, company_name, ruc, rucglobal, email, phone FROM comunity_users WHERE rucglobal = ? AND deleted_at IS NULL");
            $stmt3->execute([$document]);
            $rowsBranches = $stmt3->fetchAll(\PDO::FETCH_ASSOC);

            if (!empty($rowsBranches)) {
                $total = count($rowsBranches);
                $mapped = array_map(function ($row) {
                    return [
                        'type' => 'ruc',
                        'ruc' => $row['ruc'] ?? null,
                        'company_name' => $row['company_name'] ?? null,
                        'email' => $row['email'] ?? null,
                        'phone' => $row['phone'] ?? null,
                        'rucglobal' => $row['rucglobal'] ?? null
                    ];
                }, $rowsBranches);

                $items = array_slice($mapped, 0, $limit);

                return [
                    'type' => 'multiple',
                    'total_count' => $total,
                    'items' => $items
                ];
            }

            // 4) no hay ramas: si hay una sola fila con ruc = input -> single; si varias -> multiple
            $totalRuc = count($rowsRuc);
            if ($totalRuc === 1) {
                $row = $rowsRuc[0];
                return [
                    'type' => 'single',
                    'item' => [
                        'type' => 'ruc',
                        'ruc' => $row['ruc'] ?? null,
                        'company_name' => $row['company_name'] ?? null,
                        'email' => $row['email'] ?? null,
                        'phone' => $row['phone'] ?? null,
                        'rucglobal' => $row['rucglobal'] ?? null
                    ]
                ];
            }

            if ($totalRuc > 1) {
                $mapped = array_map(function ($row) {
                    return [
                        'type' => 'ruc',
                        'ruc' => $row['ruc'] ?? null,
                        'company_name' => $row['company_name'] ?? null,
                        'email' => $row['email'] ?? null,
                        'phone' => $row['phone'] ?? null,
                        'rucglobal' => $row['rucglobal'] ?? null
                    ];
                }, $rowsRuc);

                $items = array_slice($mapped, 0, $limit);

                return [
                    'type' => 'multiple',
                    'total_count' => $totalRuc,
                    'items' => $items
                ];
            }

            return ['type' => 'none'];
        }

        return ['type' => 'none'];
    }


    public function findComunityUserForPasswordRecovery($dni, $email, $ruc = null)
    {
        $db = Database::getConnection();
        $query = "SELECT id, dni, email, ruc, name, paternal_surname, maternal_surname FROM comunity_users WHERE dni = :dni AND email = :email AND deleted_at IS NULL";

        if ($ruc) {
            $query .= " AND ruc = :ruc";
        }

        $stmt = $db->prepare($query);
        $stmt->bindParam(':dni', $dni);
        $stmt->bindParam(':email', $email);
        if ($ruc) {
            $stmt->bindParam(':ruc', $ruc);
        }

        $stmt->execute();
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        return $user ?: null;
    }

    public function getAllUsersUgel()
    {
        $db = Database::getConnection();

        $query = "SELECT u.*, 
                        g.description AS group_name, 
                        sg.description AS subgroup_name 
                FROM ugel_users u
                LEFT JOIN `groups` g ON u.group_id = g.id
                LEFT JOIN subgroups sg ON u.subgroup_id = sg.id";

        $stmt = $db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }

    public function findUserUgelForPasswordRecovery($dni, $email)
    {
        $db = Database::getConnection();
        $query = "SELECT id, dni, email, name, paternal_surname, maternal_surname FROM ugel_users WHERE dni = :dni AND email = :email AND deleted_at IS NULL";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':dni', $dni);
        $stmt->bindParam(':email', $email);

        $stmt->execute();
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        return $user ?: null;
    }
}
