<?php

namespace Bot\Database;

trait UserDbTrait
{

    public function getAllUsers()
    {
        $query = "SELECT * FROM users";

        $stmt = $this->mysqli->prepare($query);

        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getAllUsers: " . $this->mysqli->error);
            return [];
        }

        if (!$stmt->execute()) {
            error_log("❌ Failed to execute statement in getAllUsers: " . $stmt->error);
            $stmt->close();
            return [];
        }

        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);

        $stmt->close();
        return $users;
    }


    public function getAllUsersPaginated(int $limit, int $offset): array
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM users ORDER BY id DESC LIMIT ? OFFSET ?");
        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getAllUsersPaginated: " . $this->mysqli->error);
            return [];
        }
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $tours = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $tours;
    }


    public function getAllUsersCount(): int
    {
        $result = $this->mysqli->query("SELECT COUNT(*) as count FROM users");
        $row = $result->fetch_assoc();
        return $row['count'] ?? 0;
    }


    public function getAdmins()
    {
        $stmt = $this->mysqli->prepare("SELECT id, chat_id, username FROM users WHERE is_admin = 1");
        $stmt->execute();
        $result = $stmt->get_result();
        $admins = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $admins;
    }


    public function getLeaderByChatId($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT id, chat_id, username FROM users WHERE is_admin = 0 AND is_leader=1 AND chat_id=?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result();

        return $result->fetch_assoc();
    }


    public function getExplorerByChatId($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM explorers WHERE  chat_id=?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result();

        return $result->fetch_assoc();
    }


    public function getLeaders()
    {
        $stmt = $this->mysqli->prepare("SELECT id, chat_id, username FROM users WHERE is_admin = 0 AND is_leader=1");
        $stmt->execute();
        $result = $stmt->get_result();
        $leaders = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $leaders;
    }


    public function addAdmin($chatId)
    {
        // اگر کاربر وجود ندارد، خطا برگردان
        $user = $this->getUserByUserId($chatId);
        if (!$user) {
            return false;
        }
        $stmt = $this->mysqli->prepare("UPDATE users SET is_admin = 1 WHERE chat_id = ?");
        $stmt->bind_param("i", $chatId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }


    public function deleteAdmin($chatId)
    {
        $stmt = $this->mysqli->prepare("UPDATE users SET is_admin = 0 WHERE chat_id = ?");
        $stmt->bind_param("i", $chatId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }


    public function deleteUserByChatId($chatId)
    {
        $query = "DELETE FROM `explorers` WHERE `chat_id` = ?";

        $stmt = $this->mysqli->prepare($query);

        if (!$stmt) {
            error_log("❌ Prepare failed: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("i", $chatId);

        if (!$stmt->execute()) {
            error_log("❌ Execute failed: " . $stmt->error);
            return false;
        }

        $stmt->close();
        return true;
    }


    public function getUsernameByChatId($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT `username` FROM `users` WHERE `chat_id` = ?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result['username'] ?? 'Unknown';
    }


    public function setUserLanguage($chatId, $language)
    {
        $stmt = $this->mysqli->prepare("UPDATE `users` SET `language` = ? WHERE `chat_id` = ?");
        $stmt->bind_param("si", $language, $chatId);
        return $stmt->execute();
    }


    public function getUserByUsername($username)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM users WHERE username = ? LIMIT 1");
        $stmt->bind_param("s", $username);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }


    public function getUserLanguage($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT `language` FROM `users` WHERE `chat_id` = ? LIMIT 1");
        $stmt->bind_param('s', $chatId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result['language'] ?? 'fa';
    }


    public function getUserInfo($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT `username`, `first_name`, `last_name` FROM `users` WHERE `chat_id` = ?");
        if (!$stmt) {
            error_log("Failed to prepare statement: " . $this->mysqli->error);
            return null;
        }

        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result();

        $user = $result->fetch_assoc();
        $stmt->close();

        if (!$user) {
            error_log("User not found for chat_id: {$chatId}");
            return null;
        }

        return $user;
    }


    public function saveUser($user, $entryToken = null)
    {
        $excludedUsers = [193551966];
        if (in_array($user['id'], $excludedUsers)) {
            return;
        }


        $stmt = $this->mysqli->prepare("SELECT username, first_name, last_name, language FROM users WHERE chat_id = ?");
        $stmt->bind_param("i", $user['id']);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result->num_rows === 0) {
            $stmt->close();

            $username = $user['username'] ?? '';
            $firstName = $user['first_name'] ?? '';
            $lastName = $user['last_name'] ?? '';
            $language = $user['language_code'] ?? 'en';


            $stmt = $this->mysqli->prepare("
            INSERT INTO users (chat_id, username, first_name, last_name, language, last_activity, entry_token) 
            VALUES (?, ?, ?, ?, ?, NOW(), ?)
        ");
            $stmt->bind_param(
                "isssss",
                $user['id'],
                $username,
                $firstName,
                $lastName,
                $language,
                $entryToken
            );
            $stmt->execute();
        } else {
            $stmt->close();

            $username = $user['username'] ?? '';
            $firstName = $user['first_name'] ?? '';
            $lastName = $user['last_name'] ?? '';
            $language = $user['language_code'] ?? 'en';

            $stmt = $this->mysqli->prepare("
            UPDATE users 
            SET username = ?, first_name = ?, last_name = ?, language = ?, last_activity = NOW()
            WHERE chat_id = ?
        ");
            $stmt->bind_param(
                "ssssi",
                $username,
                $firstName,
                $lastName,
                $language,
                $user['id']
            );
            $stmt->execute();
        }
    }


    public function insertExplorer($chatId, array $explorer): bool
    {

        $role = 18;

        $stmt = $this->mysqli->prepare("
        INSERT INTO explorers (chat_id,role_id, first_name, last_name, nick_name, date_of_birth, phone, city)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            first_name = VALUES(first_name),
            last_name = VALUES(last_name),
            nick_name = VALUES(nick_name),
            date_of_birth = VALUES(date_of_birth),
            phone = VALUES(phone),
            city = VALUES(city)
    ");
        $stmt->bind_param(
            "iissssss", // 'i' مربوط به role_id حذف شد
            $chatId,
            $role,
            $explorer['first_name'],
            $explorer['last_name'],
            $explorer['nick_name'],
            $explorer['date_of_birth'],
            $explorer['phone'],
            $explorer['city']
        );

        $success = $stmt->execute();
        $stmt->close();

        if ($success) {
            $this->setRegisteredStatus($chatId, true);
        }

        return $success;
    }


    public function insertLeader($chatId, array $leader): bool
    {

        $role = 17;
        $stmt = $this->mysqli->prepare("

        INSERT INTO explorers (chat_id,role_id, first_name, last_name, nick_name, date_of_birth, phone, city, address, national_code, channel, group_link, card_number, sheba, card_owner_name)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            role_id = VALUES(role_id),
            first_name = VALUES(first_name),
            last_name = VALUES(last_name),
            nick_name = VALUES(nick_name),
            date_of_birth = VALUES(date_of_birth),
            phone = VALUES(phone),
            city = VALUES(city),
            address = VALUES(address),
            national_code = VALUES(national_code),
            channel = VALUES(channel),
            group_link = VALUES(group_link),
            card_number = VALUES(card_number),
            sheba = VALUES(sheba),
            card_owner_name = VALUES(card_owner_name)
             ");
        $stmt->bind_param(
            "iisssssssssssss", // نوع پارامترها با حذف role_id و افزودن فیلدهای کارت، اصلاح شد
            $chatId,
            $role,

            $leader['first_name'],
            $leader['last_name'],
            $leader['nick_name'],
            $leader['date_of_birth'],
            $leader['phone'],
            $leader['city'],
            $leader['address'],
            $leader['national_code'],
            $leader['channel'],
            $leader['group_link'],
            $leader['card_number'],

            $leader['sheba'],
            $leader['card_owner_name']
        );

        $success = $stmt->execute();
        $stmt->close();

        // قدم سوم: در صورت موفقیت، وضعیت‌های کاربر را در جدول users به‌روز کن
        if ($success) {
            //  $this->setLeaderStatus($chatId, true);
            $this->setRegisteredStatus($chatId, true); // هر لیدر، یک کاربر ثبت‌نام شده نیز هست
        }

        return $success;
    }


    public function updateLeaderField($chatId, $fieldKey, $newValue): bool
    {
        // لیست فیلدهای مجاز برای جلوگیری از SQL Injection
        $allowedFields = [
            'first_name',
            'last_name',
            'nick_name',
            'date_of_birth',
            'phone',
            'city',
            'address',
            'national_code',
            'channel',
            'group_link',
            'card_number',
            'sheba',
            'card_owner_name'
        ];

        // اطمینان از معتبر بودن فیلد
        if (!in_array($fieldKey, $allowedFields)) {
            return false;
        }

        // ساخت query به صورت داینامیک اما ایمن
        $query = "UPDATE explorers SET `$fieldKey` = ? WHERE chat_id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("❌ Prepare failed: " . $this->mysqli->error);
            return false;
        }

        // bind نوع پارامترها: s = string, i = int
        $stmt->bind_param("si", $newValue, $chatId);

        $success = $stmt->execute();
        if (!$success) {
            error_log("❌ Execute failed: " . $stmt->error);
        }

        $stmt->close();
        return $success;
    }


    public function getUserByChatIdOrUsername($identifier)
    {
        if (is_numeric($identifier)) {
            $stmt = $this->mysqli->prepare("SELECT * FROM users WHERE chat_id = ?");
            $stmt->bind_param("i", $identifier);
        } else {
            $username = ltrim($identifier, '@');
            $stmt = $this->mysqli->prepare("SELECT * FROM users WHERE username = ?");
            $stmt->bind_param("s", $username);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        $stmt->close();

        return $user;
    }


    public function getUserFullName($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT `first_name`, `last_name` FROM `users` WHERE `chat_id` = ?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return trim(($result['first_name'] ?? '') . ' ' . ($result['last_name'] ?? ''));
    }


    public function getUsersBatch($limit = 20, $offset = 0)
    {
        $query = "SELECT id, chat_id, username, first_name, last_name, join_date, last_activity, status, language, is_admin, entry_token 
              FROM users 
              ORDER BY id ASC 
              LIMIT ? OFFSET ?";

        $stmt = $this->mysqli->prepare($query);

        if (!$stmt) {
            error_log("❌ Prepare failed: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("ii", $limit, $offset);

        if (!$stmt->execute()) {
            error_log("❌ Execute failed: " . $stmt->error);
            return [];
        }

        $result = $stmt->get_result();
        return $result->fetch_all(MYSQLI_ASSOC);
    }


    public function updateUserStatus($chatId, $status)
    {
        $query = "UPDATE users SET status = ? WHERE chat_id = ?";
        $stmt = $this->mysqli->prepare($query);

        if (!$stmt) {
            error_log("Database Error: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("si", $status, $chatId);

        if (!$stmt->execute()) {
            error_log("Error updating status for User ID: $chatId");
            $stmt->close();
            return false;
        }

        $affectedRows = $stmt->affected_rows;
        $stmt->close();

        return $affectedRows > 0;
    }


    public function isAdmin($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT is_admin FROM users WHERE chat_id = ?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        $stmt->close();

        return $user && $user['is_admin'] == 1;
    }


    public function getUserByUserId($userId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM users WHERE chat_id = ? LIMIT 1");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }


    public function getUserById($Id)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM users WHERE id = ? LIMIT 1");
        $stmt->bind_param("i", $Id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }


    public function registeredExplorerByUserId($userId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM explorers WHERE chat_id = ? LIMIT 1");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }


    public function updateExplorerField($chatId, $field, $value)
    {
        $allowedFields = ['first_name', 'last_name', 'nick_name', 'phone', 'city'];
        if (!in_array($field, $allowedFields)) {
            return false;
        }

        $query = "UPDATE explorers SET `$field` = ? WHERE chat_id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("❌ Prepare failed in updateExplorerField: " . $this->mysqli->error);
            return false;
        }
        $stmt->bind_param("si", $value, $chatId);
        $success = $stmt->execute();
        $stmt->close();
        return $success;
    }


    public function setLeaderStatus(int $chatId, bool $status): bool
    {
        $isLeader = $status ? 1 : 0;
        $stmt = $this->mysqli->prepare("UPDATE users SET is_leader = ? WHERE chat_id = ?");
        $stmt->bind_param("ii", $isLeader, $chatId);
        return $stmt->execute();
    }


    public function setRegisteredStatus(int $chatId, bool $status): bool
    {
        $isRegistered = $status ? 1 : 0;
        $stmt = $this->mysqli->prepare("UPDATE users SET is_registered = ? WHERE chat_id = ?");
        $stmt->bind_param("ii", $isRegistered, $chatId);
        return $stmt->execute();
    }


    public function getUsersByroleId($roleName)
    {

        $sql = "";

        if ($roleName === 'leader') {
            $sql = "SELECT e.* FROM explorers e JOIN users u ON e.chat_id = u.chat_id WHERE u.is_leader = 1";
        } elseif ($roleName === 'explorer') {

            $sql = "SELECT e.* FROM explorers e JOIN users u ON e.chat_id = u.chat_id WHERE u.is_registered = 1 AND u.is_leader = 0";
        } else {
            return [];
        }

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getExplorersByRole: " . $this->mysqli->error);
            return [];
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $users;
    }


    public function getLeadersWithPagination($limit, $page)
    {
        $offset = ($page - 1) * $limit;
        $stmt = $this->mysqli->prepare("SELECT e.* FROM explorers e JOIN users u ON e.chat_id = u.chat_id WHERE u.is_leader = 1 LIMIT ? OFFSET ?");
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $leaders = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $leaders;
    }


    public function getUsersWithPagination($limit, $page)
    {
        $offset = ($page - 1) * $limit;
        $stmt = $this->mysqli->prepare("SELECT e.* FROM explorers e JOIN users u ON e.chat_id = u.chat_id WHERE u.is_registered = 1 AND u.is_leader=0 AND u.is_admin=0 LIMIT ? OFFSET ?");
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $users;
    }


    public function getLeaderCount()
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) AS total FROM `users` WHERE is_leader=1");
        $stmt->execute();
        $result = $stmt->get_result();
        $total = $result->fetch_assoc()['total'];
        $stmt->close();
        return $total;
    }


    public function getUserCount()
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) AS total FROM `users` WHERE is_leader=0 AND is_registered=1 AND is_admin=0");
        $stmt->execute();
        $result = $stmt->get_result();
        $total = $result->fetch_assoc()['total'];
        $stmt->close();
        return $total;
    }


    public function blockUser($userId)
    {
        $stmt = $this->mysqli->prepare("UPDATE users SET status = 'blocked' WHERE chat_id = ?");
        if (!$stmt) {
            error_log("❌ Failed to prepare blockUser statement: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("i", $userId);

        if (!$stmt->execute()) {
            error_log("❌ Failed to execute blockUser statement: " . $stmt->error);
            return false;
        }

        return true;
    }


    public function unblockUser($userId)
    {
        $stmt = $this->mysqli->prepare("UPDATE users SET status = 'active' WHERE chat_id = ?");
        if (!$stmt) {
            error_log("❌ Failed to prepare unblockUser statement: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("i", $userId);

        if (!$stmt->execute()) {
            error_log("❌ Failed to execute unblockUser statement: " . $stmt->error);
            return false;
        }

        return true;
    }


    public function getBlockedUsers()
    {
        $stmt = $this->mysqli->prepare("SELECT user_id, first_name, last_name FROM users WHERE status = 'blocked'");

        if (!$stmt) {
            error_log("❌ Failed to prepare getBlockedUsers statement: " . $this->mysqli->error);
            return [];
        }

        if (!$stmt->execute()) {
            error_log("❌ Failed to execute getBlockedUsers statement: " . $stmt->error);
            return [];
        }

        $result = $stmt->get_result();
        $users = [];

        while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }

        $stmt->close();
        return $users;
    }


    public function getBlockedUsersCount()
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE status = 'blocked'");

        if (!$stmt) {
            error_log("❌ Failed to prepare getBlockedUsersCount statement: " . $this->mysqli->error);
            return 0;
        }

        if (!$stmt->execute()) {
            error_log("❌ Failed to execute getBlockedUsersCount statement: " . $stmt->error);
            return 0;
        }

        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();

        return (int)$row['total'];
    }


    public function getBlockedUsersWithPagination($limit, $page)
    {
        $offset = ($page - 1) * $limit;
        $stmt = $this->mysqli->prepare("SELECT `id`, `first_name`, `last_name`, `chat_id` FROM `users` WHERE status = 'blocked'  LIMIT ? OFFSET ?");
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $users;
    }


    public function getUserByChatId($chatId)
    {
        $query = "SELECT `id`, `chat_id`, `username`, `first_name`, `last_name`, `join_date`, 
                     `last_activity`, `status`, `language`, `is_admin`, `entry_token` 
              FROM `users` 
              WHERE `chat_id` = ? 
              LIMIT 1";

        $stmt = $this->mysqli->prepare($query);

        if (!$stmt) {
            error_log("❌ Prepare failed: " . $this->mysqli->error);
            return null;
        }

        $stmt->bind_param("i", $chatId);

        if (!$stmt->execute()) {
            error_log("❌ Execute failed: " . $stmt->error);
            return null;
        }

        $result = $stmt->get_result();
        $user = $result->fetch_assoc();

        $stmt->close();

        return $user ?: null;
    }


    public function getLastLeaders($limit = 10)
    {
        // The query is corrected to use f.user_id for the join and CONCAT for the full name.
        $query = "
        SELECT 
            u.chat_id,
            CONCAT(e.first_name, ' ', e.last_name) as full_name,
            u.username,
            e.created_at
        FROM explorers AS e
        JOIN users AS u ON e.chat_id = u.chat_id AND u.is_leader=1
        ORDER BY e.id DESC
        LIMIT ?
    ";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            // Log error if prepare fails
            error_log("Failed to prepare getLastLeaders statement: " . $this->mysqli->error);
            return [];
        }
        $stmt->bind_param("i", $limit);
        $stmt->execute();
        $result = $stmt->get_result();
        $leaders = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $leaders;
    }


    public function getLastExplorers($limit = 10)
    {
        // The query is corrected to use f.user_id for the join and CONCAT for the full name.
        $query = "
        SELECT 
            u.chat_id,
            CONCAT(e.first_name, ' ', e.last_name) as full_name,
            u.username,
            e.created_at
        FROM explorers AS e
        JOIN users AS u ON e.chat_id = u.chat_id AND u.is_registered=1
        ORDER BY e.id DESC
        LIMIT ?
    ";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            // Log error if prepare fails
            error_log("Failed to prepare getLastExplorers statement: " . $this->mysqli->error);
            return [];
        }
        $stmt->bind_param("i", $limit);
        $stmt->execute();
        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $users;
    }

}
