<?php

namespace Bot\Database;

trait GuideDbTrait
{

    public function getGuideFolders($parentId)
    {
        if ($parentId === null) {
            $query = "SELECT id, name, parent_id, description FROM guide_folders WHERE parent_id IS NULL ORDER BY name ASC";
            $stmt = $this->mysqli->prepare($query);
        } else {
            $query = "SELECT id, name, parent_id, description FROM guide_folders WHERE parent_id = ? ORDER BY name ASC";
            $stmt = $this->mysqli->prepare($query);
            $stmt->bind_param("i", $parentId);
        }

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

        $stmt->execute();
        $result = $stmt->get_result();
        $folders = [];
        while ($row = $result->fetch_assoc()) {
            $folders[] = $row;
        }
        $stmt->close();
        return $folders;
    }


    public function getGuideFolderById($folderId)
    {
        if ($folderId === null) {
            return null; // ریشه پوشه خاصی نیست که توضیحات داشته باشد
        }
        $query = "SELECT id, name, parent_id, description FROM guide_folders WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare statement getGuideFolderById: " . $this->mysqli->error);
            return null;
        }
        $stmt->bind_param("i", $folderId);
        $stmt->execute();
        $result = $stmt->get_result();
        $folder = $result->fetch_assoc();
        $stmt->close();
        return $folder;
    }


    public function getGuideItemById($itemId)
    {
        $query = "SELECT id, folder_id, title, type, file_id, caption FROM guide_items WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);

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

        $stmt->bind_param("i", $itemId);
        $stmt->execute();
        $result = $stmt->get_result();
        $item = $result->fetch_assoc();
        $stmt->close();
        return $item;
    }


    public function saveGuideItem($folderId, $title, $fileType, $fileId, $caption)
    {
        $query = "INSERT INTO guide_items (folder_id, title, type, file_id, caption) VALUES (?, ?, ?, ?, ?)";
        $stmt = $this->mysqli->prepare($query);

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

        // 'issss' برای: integer, string, string, string, string (title اضافه شده)
        $stmt->bind_param("issss", $folderId, $title, $fileType, $fileId, $caption);

        $success = $stmt->execute();
        if (!$success) {
            error_log("Failed to execute statement for saveGuideItem: " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }


    public function deleteGuideFolder($folderId)
    {
        $query = "DELETE FROM guide_folders WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare deleteGuideFolder: " . $this->mysqli->error);
            return false;
        }
        $stmt->bind_param("i", $folderId);
        $success = $stmt->execute();
        if (!$success) {
            error_log("Failed to execute deleteGuideFolder: " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }


    public function deleteGuideItem($itemId)
    {
        $stmt = $this->mysqli->prepare("DELETE FROM guide_items WHERE id = ?");
        $stmt->bind_param("i", $itemId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }


    public function updateGuideFolderName($folderId, $newName)
    {
        $query = "UPDATE guide_folders SET name = ? WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare updateGuideFolderName: " . $this->mysqli->error);
            return false;
        }
        $stmt->bind_param("si", $newName, $folderId);
        $success = $stmt->execute();
        if (!$success) {
            error_log("Failed to execute updateGuideFolderName: " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }


    public function updateGuideFolderDescription($folderId, $newDescription)
    {
        $query = "UPDATE guide_folders SET description = ? WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare updateGuideFolderDescription: " . $this->mysqli->error);
            return false;
        }

        $descToSave = empty($newDescription) ? null : $newDescription;

        $stmt->bind_param("si", $descToSave, $folderId);
        $success = $stmt->execute();
        if (!$success) {
            error_log("Failed to execute updateGuideFolderDescription: " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }


    public function createGuideFolder($folderName, $parentId, $description)
    {
        $query = "INSERT INTO guide_folders (name, parent_id, description) VALUES (?, ?, ?)";
        $stmt = $this->mysqli->prepare($query);

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

        $stmt->bind_param("sis", $folderName, $parentId, $description);

        $success = $stmt->execute();
        if (!$success) {
            error_log("Failed to execute statement for createGuideFolder: " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }


    public function updateGuideItemName($itemId, $newName)
    {
        $stmt = $this->mysqli->prepare("UPDATE guide_items SET title = ? WHERE id = ?");
        $stmt->bind_param("si", $newName, $itemId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }


    public function updateGuideItemCaption($itemId, $newCaption)
    {
        $stmt = $this->mysqli->prepare("UPDATE guide_items SET caption = ? WHERE id = ?");
        $stmt->bind_param("si", $newCaption, $itemId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }


    public function updateGuideItemMedia($itemId, $fileType, $fileId, $caption)
    {
        $stmt = $this->mysqli->prepare("UPDATE guide_items SET type = ?, file_id = ?, caption = ? WHERE id = ?");
        $stmt->bind_param("sssi", $fileType, $fileId, $caption, $itemId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }


    public function getGuideItemsInFolder($folderId)
    {
        // 'title' رو هم به SELECT اضافه کنید
        $query = "SELECT id, folder_id, title, type, file_id, caption FROM guide_items WHERE folder_id " . ($folderId === null ? "IS NULL" : "= ?") . " ORDER BY id ASC";
        $stmt = $this->mysqli->prepare($query);

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

        if ($folderId !== null) {
            $stmt->bind_param("i", $folderId);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        $items = [];
        while ($row = $result->fetch_assoc()) {
            $items[] = $row;
        }
        $stmt->close();
        return $items;
    }


    public function getParentFolderId($folderId)
    {
        $query = "SELECT parent_id FROM guide_folders WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);

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

        $stmt->bind_param("i", $folderId);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['parent_id'] ?? null;
    }


    public function getBotStats()
    {
        $stats = [];

        // Total users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['total_users'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Total admins
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE is_admin = 1");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['total_admins'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Total channels
        $stmt = $this->mysqli->prepare("SELECT COUNT(channel) as total_channel FROM explorers");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['total_channels'] = $result->fetch_assoc()['total_channel'];
        $stmt->close();

        // Blocked users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE status = 'blocked'");
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stats['total_blocked_users'] = $row ? $row['total'] : 0;
        $stmt->close();

        return $stats;
    }


    public function getUserJoiningStats()
    {
        $stats = [];

        // Joined today
        $stmt = $this->mysqli->prepare("
        SELECT COUNT(*) as total 
        FROM users 
        WHERE join_date >= CURDATE() AND join_date < CURDATE() + INTERVAL 1 DAY
        ");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['joined_today'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Joined yesterday
        $stmt = $this->mysqli->prepare("
        SELECT COUNT(*) as total 
        FROM users 
        WHERE join_date >= CURDATE() - INTERVAL 1 DAY AND join_date < CURDATE()
        ");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['joined_yesterday'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Joined last week
        $stmt = $this->mysqli->prepare("
        SELECT COUNT(*) as total 
        FROM users 
        WHERE join_date >= CURDATE() - INTERVAL 7 DAY
        ");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['joined_last_week'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Joined last month
        $stmt = $this->mysqli->prepare("
        SELECT COUNT(*) as total 
        FROM users 
        WHERE join_date >= CURDATE() - INTERVAL 1 MONTH
        ");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['joined_last_month'] = $result->fetch_assoc()['total'];
        $stmt->close();

        return $stats;
    }


    public function getUserActivityStats()
    {
        $stats = [];

        // Online users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE status = 'active' AND last_activity >= NOW() - INTERVAL 5 MINUTE");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['online_users'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Hourly users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE last_activity >= NOW() - INTERVAL 1 HOUR");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['last_hour_users'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Daily users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE DATE(last_activity) = CURDATE() - INTERVAL 1 DAY");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['yesterday_users'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Weekly users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE last_activity >= NOW() - INTERVAL 7 DAY");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['last_week_users'] = $result->fetch_assoc()['total'];
        $stmt->close();

        // Monthly users
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total FROM users WHERE last_activity >= NOW() - INTERVAL 1 MONTH");
        $stmt->execute();
        $result = $stmt->get_result();
        $stats['last_month_users'] = $result->fetch_assoc()['total'];
        $stmt->close();


        return $stats;
    }


    public function getLastTenUsers()
    {
        $stmt = $this->mysqli->prepare("SELECT id, chat_id, username, first_name, last_name, join_date FROM users ORDER BY join_date DESC LIMIT 10");
        $stmt->execute();
        $result = $stmt->get_result();
        $lastUsers = [];
        while ($user = $result->fetch_assoc()) {
            $lastUsers[] = $user;
        }
        $stmt->close();
        return $lastUsers;
    }


    public function getLastTenBlockedUsers()
    {
        $stmt = $this->mysqli->prepare("SELECT id, chat_id, username, first_name, last_name, join_date FROM users WHERE status = 'blocked' ORDER BY join_date DESC LIMIT 10");
        $stmt->execute();
        $result = $stmt->get_result();
        $lastBlockedUsers = [];
        while ($user = $result->fetch_assoc()) {
            $lastBlockedUsers[] = $user;
        }
        $stmt->close();
        return $lastBlockedUsers;
    }


    public function getRoleStats()
    {
        $stats = [];


        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total_leaders FROM users WHERE is_leader=1");
        $stmt->execute();
        $stats['total_leaders'] = $stmt->get_result()->fetch_assoc()['total_leaders'] ?? 0;
        $stmt->close();

        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as total_users FROM users WHERE is_registered=1");
        $stmt->execute();
        $stats['total_users'] = $stmt->get_result()->fetch_assoc()['total_users'] ?? 0;
        $stmt->close();

        return $stats;
    }


    public function logUserWarning(int $userId, int $adminId, ?string $messageText, ?string $fileId = null, ?string $fileType = null): bool
    {
        $stmt = $this->mysqli->prepare("INSERT INTO user_warnings (user_id, admin_id, message_text, file_id, file_type, created_at) VALUES (?, ?, ?, ?, ?, NOW())");
        if (!$stmt) {
            error_log("Failed to prepare logUserWarning: " . $this->mysqli->error);
            return false;
        }
        $stmt->bind_param("iisss", $userId, $adminId, $messageText, $fileId, $fileType);
        $success = $stmt->execute();
        $stmt->close();
        return $success;
    }


    public function countUserWarnings(int $userId): int
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) AS total FROM user_warnings WHERE user_id = ?");
        if (!$stmt) {
            error_log("Failed to prepare countUserWarnings: " . $this->mysqli->error);
            return 0;
        }
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return (int)($row['total'] ?? 0);
    }

}
