<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use App\Models\User;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Http;

class DashboardController extends Controller
{
    public function getStatusReportGraph(Request $request)
    {
        $request->validate([
            'date' => 'nullable|date',
            'user_ID' => 'nullable|string', // Changed to nullable string
            'service' => 'sometimes|string|in:SMS,Voice,RCS,Whatsapp,International SMS'
        ]);

        $date = $request->input('date');
        $userId = $request->input('user_ID');
        $service = $request->input('service', 'SMS'); // Default to SMS
        $loggedInUser = Auth::user();

        $allowedUserIds = $this->getAllowedUserIds($loggedInUser);

        // SuperAdmin special rule: "All" means all users' data
        if ($userId !== 'All') {
            if ($userId && !in_array($userId, $allowedUserIds)) {
                return response()->json([
                    'success' => false,
                    'message' => 'You are not allowed to view this user’s report.'
                ], 403);
            }
        }

        // Get status counts based on service
        $statusCounts = $this->getStatusCountsByService($service, $userId, $date);

        return response()->json([
            'success' => true,
            'data' => [
                'status_counts' => $statusCounts
            ]
        ]);
    }

    private function getAllowedUserIds($user)
    {
        // SUPERADMIN → access to all users
        if ($user->role_ID == 4) {
            return User::pluck('id')->toArray();
        }

        // ADMIN → himself + all users created by him
        if ($user->role_ID == 3) {
            return User::where('created_by', $user->id)
                    ->pluck('id')
                    ->push($user->id)
                    ->toArray();
        }

        // RESELLER → himself + all resellers + users created by him
        if ($user->role_ID == 2) {
            return User::where('created_by', $user->id)
                    ->pluck('id')
                    ->push($user->id)
                    ->toArray();
        }

        // USER → only self
        return [$user->id];
    }

/**
 * Get overall status counts for different services (Monthly)
 */
    public function getOverallReportGraph(Request $request)
    {
        $request->validate([
            'month' => 'nullable|string|in:Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
            'user_ID' => 'nullable|string', // Changed to nullable string
            'service' => 'sometimes|string|in:SMS,Voice,RCS,Whatsapp,International SMS'
        ]);

        $monthString = $request->input('month');
        $userId = $request->input('user_ID');
        $service = $request->input('service', 'SMS'); // Default to SMS
        $currentYear = date('Y');

        // Convert month string to numeric month only if month is provided
        $month = null;
        if (!empty($monthString)) {
            $month = $this->convertMonthStringToNumber($monthString);
        }

        // Get status counts based on service for the month
        $statusCounts = $this->getOverallStatusCountsByService($service, $userId, $month, $currentYear);

        return response()->json([
            'success' => true,
            'data' => [
                'status_counts' => $statusCounts
            ]
        ]);
    }

    /**
     * Get gateway status counts from all tables (Daily)
     */
    public function getGatewayReportGraph(Request $request)
    {
        $request->validate([
            'date' => 'sometimes|date|nullable'
        ]);

        $date = $request->input('date');

        // Get status counts from all tables
        $statusCounts = $this->getGatewayStatusCounts($date);

        return response()->json([
        'success' => true,
        'data' => $statusCounts
        ]);
    }

    /**
     * Get user profile details
     */
    public function getUserProfile(Request $request)
    {
        $request->validate([
            'user_ID' => 'required|integer'
        ]);

        $userId = $request->input('user_ID');

        // Get user profile details with route and balance
        $userProfile = $this->getUserProfileDetails($userId);

        if (!$userProfile) {
            return response()->json([
                'success' => false,
                'message' => 'User not found'
            ], 404);
        }

        return response()->json([
            'success' => true,
            'data' => $userProfile
        ]);
    }

    /**
     * Convert month string to numeric month
     */
    private function convertMonthStringToNumber($monthString)
    {
        $months = [
            'Jan' => 1,
            'Feb' => 2,
            'Mar' => 3,
            'Apr' => 4,
            'May' => 5,
            'Jun' => 6,
            'Jul' => 7,
            'Aug' => 8,
            'Sep' => 9,
            'Oct' => 10,
            'Nov' => 11,
            'Dec' => 12
        ];

        return $months[$monthString] ?? 1; // Default to January if not found
    }

    /**
 * Get user profile details
 */
    public function getUserProfileDetails(Request $request)
    {
        try {
            // Get the authenticated user's ID
            $userId = auth()->id();

            if (!$userId) {
                return response()->json([
                    'success' => false,
                    'message' => 'User not authenticated'
                ], 401);
            }

            // Get user profile details with route and balance
            $user = DB::table('users')
                ->select(
                    'users.id as user_id',
                    'users.role_ID',
                    'users.route_IDs',
                    'roles.name as role_name'
                )
                ->leftJoin('roles', 'users.role_ID', '=', 'roles.id')
                ->where('users.id', $userId)
                ->first();

            if (!$user) {
                return response()->json([
                    'success' => false,
                    'message' => 'User not found'
                ], 404);
            }

            // Initialize variables for route data
            $routeDisplay = '';
            $routeNames = [];
            $routeBalances = [];

            // Check if route_IDs contains multiple routes (comma-separated)
            if (!empty($user->route_IDs)) {
                $routeIds = explode(',', $user->route_IDs);
                
                // Get route names for all route IDs
                $routes = DB::table('manage_routes')
                    ->whereIn('id', $routeIds)
                    ->get()
                    ->keyBy('id');

                // Get balances for all route IDs
                $balances = DB::table('credit_manage')
                    ->whereIn('route_id', $routeIds)
                    ->where('userid', $userId)
                    ->get()
                    ->keyBy('route_id');

                // Build route display with names and balances
                foreach ($routeIds as $routeId) {
                    $routeId = trim($routeId);
                    $routeName = isset($routes[$routeId]) ? $routes[$routeId]->route_name : 'Route ' . $routeId;
                    $balance = isset($balances[$routeId]) ? $balances[$routeId]->balance : null;
                    
                    $routeNames[] = $routeName;
                    
                    if ($balance !== null) {
                        $routeBalances[] = $routeName . ' (' . $balance . ')';
                    } else {
                        $routeBalances[] = $routeName;
                    }
                }

                // Format the route display
                if (!empty($routeBalances)) {
                    $routeDisplay = implode(', ', $routeBalances);
                } else if (!empty($routeNames)) {
                    $routeDisplay = implode(', ', $routeNames);
                }
            }

            $roleDisplay = $user->role_name ?: 'Role ' . $user->role_ID;

            return response()->json([
                'success' => true,
                'data' => [
                    'user_id' => $user->user_id,
                    'role' => $roleDisplay,
                    'routes' => [$routeDisplay]
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    /**
     * Get status counts based on service type (Daily)
     */
    private function getStatusCountsByService($service, $userId, $date)
    {
        switch ($service) {
            case 'Voice':
                return $this->getVoiceStatusCounts($userId, $date);
            
            case 'RCS':
                return $this->getRCSStatusCounts($userId, $date);
            
            case 'Whatsapp':
                return $this->getWhatsappStatusCounts($userId, $date);
            
            case 'International SMS':
                return $this->getInternationalSMSStatusCounts($userId, $date);
            
            case 'SMS':
            default:
                return $this->getSMSStatusCounts($userId, $date);
        }
    }

    /**
     * Get overall status counts based on service type (Monthly)
     */
    private function getOverallStatusCountsByService($service, $userId, $month, $year)
    {
        switch ($service) {
            case 'Voice':
                return $this->getOverallVoiceStatusCounts($userId, $month, $year);
            
            case 'RCS':
                return $this->getOverallRCSStatusCounts($userId, $month, $year);
            
            case 'Whatsapp':
                return $this->getOverallWhatsappStatusCounts($userId, $month, $year);
            
            case 'International SMS':
                return $this->getOverallInternationalSMSStatusCounts($userId, $month, $year);
            
            case 'SMS':
            default:
                return $this->getOverallSMSStatusCounts($userId, $month, $year);
        }
    }

    /**
     * Get SMS status counts (Daily)
     */
    private function getSMSStatusCounts($userId, $date)
    {
        $tables = [
            'send_numbers' => 'userids',
            'schedule_send_numbers' => 'userids'
        ];

        return $this->getCombinedStatusCounts($tables, $userId, $date);
    }

    /**
     * Get Voice status counts (Daily)
     */
    private function getVoiceStatusCounts($userId, $date)
    {
        $tables = [
            'voice_call_numbers' => 'userid',
            'schedule_voice_call_numbers' => 'userid'
        ];

        return $this->getCombinedStatusCounts($tables, $userId, $date);
    }

    /**
     * Get RCS status counts (Daily)
     */
    private function getRCSStatusCounts($userId, $date)
    {
        $tables = [
            'rcs_send_numbers' => 'userid',
            'rcs_schedule_send_numbers' => 'userid'
        ];

        return $this->getCombinedStatusCounts($tables, $userId, $date);
    }

    /**
     * Get Whatsapp status counts (Daily)
     */
    private function getWhatsappStatusCounts($userId, $date)
    {
        $tables = [
            'whatsapp_send_numbers' => 'userid',
            'whatsapp_schedule_send_numbers' => 'userid'
        ];

        return $this->getCombinedStatusCounts($tables, $userId, $date);
    }

    /**
     * Get International SMS status counts (Daily)
     */
    private function getInternationalSMSStatusCounts($userId, $date)
    {
        $tables = [
            'international_send_numbers' => 'userids',
            'international_schedule_send_numbers' => 'userids'
        ];

        return $this->getCombinedStatusCounts($tables, $userId, $date);
    }

    /**
     * Get Overall SMS status counts (Monthly)
     */
    private function getOverallSMSStatusCounts($userId, $month, $year)
    {
        $tables = [
            'send_numbers' => 'userids',
            'schedule_send_numbers' => 'userids'
        ];

        return $this->getCombinedOverallStatusCounts($tables, $userId, $month, $year);
    }

    /**
     * Get Overall Voice status counts (Monthly)
     */
    private function getOverallVoiceStatusCounts($userId, $month, $year)
    {
        $tables = [
            'voice_call_numbers' => 'userid',
            'schedule_voice_call_numbers' => 'userid'
        ];

        return $this->getCombinedOverallStatusCounts($tables, $userId, $month, $year);
    }

    /**
     * Get Overall RCS status counts (Monthly)
     */
    private function getOverallRCSStatusCounts($userId, $month, $year)
    {
        $tables = [
            'rcs_send_numbers' => 'userid',
            'rcs_schedule_send_numbers' => 'userid'
        ];

        return $this->getCombinedOverallStatusCounts($tables, $userId, $month, $year);
    }

    /**
     * Get Overall Whatsapp status counts (Monthly)
     */
    private function getOverallWhatsappStatusCounts($userId, $month, $year)
    {
        $tables = [
            'whatsapp_send_numbers' => 'userid',
            'whatsapp_schedule_send_numbers' => 'userid'
        ];

        return $this->getCombinedOverallStatusCounts($tables, $userId, $month, $year);
    }

    /**
     * Get Overall International SMS status counts (Monthly)
     */
    private function getOverallInternationalSMSStatusCounts($userId, $month, $year)
    {
        $tables = [
            'international_send_numbers' => 'userids',
            'international_schedule_send_numbers' => 'userids'
        ];

        return $this->getCombinedOverallStatusCounts($tables, $userId, $month, $year);
    }

    /**
     * Get Gateway status counts from all tables (Daily)
     */
    private function getGatewayStatusCounts($date)
    {
        // If date is null or empty, return empty array
        if (empty($date)) {
            return [];
        }

        $allTables = [
            'send_numbers' => [
                'user_column' => 'userids',
                'gateway_table' => 'sms_gateway',
                'service_id_column' => 'service_id'
            ],
            'schedule_send_numbers' => [
                'user_column' => 'userids', 
                'gateway_table' => 'sms_gateway',
                'service_id_column' => 'service_id'
            ],
            'voice_call_numbers' => [
                'user_column' => 'userid',
                'gateway_table' => 'voice_gateway', 
                'service_id_column' => 'service_id'
            ],
            'schedule_voice_call_numbers' => [
                'user_column' => 'userid',
                'gateway_table' => 'voice_gateway',
                'service_id_column' => 'service_id'
            ],
            'rcs_send_numbers' => [
                'user_column' => 'userid',
                'gateway_table' => 'rcs_gateway',
                'service_id_column' => 'service_id'
            ],
            'rcs_schedule_send_numbers' => [
                'user_column' => 'userid',
                'gateway_table' => 'rcs_gateway',
                'service_id_column' => 'service_id'
            ],
            'whatsapp_send_numbers' => [
                'user_column' => 'userid',
                'gateway_table' => 'whatsapp_gateway',
                'service_id_column' => 'service_id'
            ],
            'whatsapp_schedule_send_numbers' => [
                'user_column' => 'userid',
                'gateway_table' => 'whatsapp_gateway',
                'service_id_column' => 'service_id'
            ],
            'international_send_numbers' => [
                'user_column' => 'userids',
                'gateway_table' => 'sms_gateway',
                'service_id_column' => 'service_id'
            ],
            'international_schedule_send_numbers' => [
                'user_column' => 'userids',
                'gateway_table' => 'sms_gateway',
                'service_id_column' => 'service_id'
            ]
        ];

        $gatewayResults = [];
        
        foreach ($allTables as $table => $tableInfo) {
            $userColumn = $tableInfo['user_column'];
            $gatewayTable = $tableInfo['gateway_table'];
            $serviceIdColumn = $tableInfo['service_id_column'];

            try {
                // Get all gateway names from the gateway table
                $availableGateways = DB::table($gatewayTable)
                    ->whereNotNull('gateway_name')
                    ->where('gateway_name', '!=', '')
                    ->pluck('gateway_name')
                    ->toArray();

                // Get data from main table
                $query = DB::table($table)
                    ->select('status', $serviceIdColumn, DB::raw('COUNT(*) as count'));
                
                // Only apply date filter if date is provided and not empty
                if (!empty($date)) {
                    $query->whereDate('created_at', $date);
                }

                $results = $query->groupBy('status', $serviceIdColumn)
                    ->get();

                foreach ($results as $result) {
                    $serviceIds = $result->$serviceIdColumn;
                    $status = $result->status;
                    
                    // Handle comma-separated gateway names
                    $gatewayNames = [];
                    if (strpos($serviceIds, ',') !== false) {
                        $gatewayNames = array_map('trim', explode(',', $serviceIds));
                    } else {
                        $gatewayNames = [trim($serviceIds)];
                    }

                    foreach ($gatewayNames as $gatewayName) {
                        // Only count if gateway name exists in available gateways
                        if (in_array($gatewayName, $availableGateways)) {
                            if (!isset($gatewayResults[$gatewayName])) {
                                $gatewayResults[$gatewayName] = [
                                    'Delivered' => 0,
                                    'Submitted' => 0,
                                    'Failed' => 0 // Changed from nested array to direct value
                                ];
                            }
                            
                            if ($status === 'Delivered') {
                                $gatewayResults[$gatewayName]['Delivered'] += $result->count;
                            } elseif ($status === 'Submitted') {
                                $gatewayResults[$gatewayName]['Submitted'] += $result->count;
                            } else {
                                // All failed statuses go to Failed count
                                $gatewayResults[$gatewayName]['Failed'] += $result->count;
                            }
                        }
                    }
                }

            } catch (\Exception $e) {
                continue;
            }
        }

        // Format the response as array with single object containing all gateways
        $formattedResponse = [$gatewayResults];
        
        return $formattedResponse;
    }

    /**
     * Get combined status counts from multiple tables with the new format (Daily)
     */
   private function getCombinedStatusCounts($tables, $userId, $date)
    {
        $combinedResults = [];
        
        foreach ($tables as $table => $userIdColumn) {
            $query = DB::table($table)
                ->select('status', DB::raw('COUNT(*) as count'));
            
            // Only apply user filter if user_ID is provided and not empty/All
            if (!empty($userId) && $userId !== 'All') {
                $query->where($userIdColumn, $userId);
            }
            
            // Only apply date filter if date is provided and not null
            if ($date) {
                $query->whereBetween('created_at', [$date . ' 00:00:00', $date . ' 23:59:59']);
            }

            $results = $query->groupBy('status')
                ->get()
                ->toArray();

            $combinedResults = array_merge($combinedResults, $results);
        }

        // Initialize counts with the new format
        $statusCounts = [
            'Delivered' => 0,
            'Submitted' => 0,
            'Scheduled' => 0, 
            'Failed' => [
                'Failed' => 0
            ]
        ];

        // Combine counts for same status from different tables
        foreach ($combinedResults as $result) {
            $status = $result->status;
            
            if ($status === 'Delivered') {
                $statusCounts['Delivered'] += $result->count;
            } elseif ($status === 'Submitted') {
                $statusCounts['Submitted'] += $result->count;
            } elseif ($status === 'Scheduled') { 
                $statusCounts['Scheduled'] += $result->count;
            } else {
                // All other statuses (including Pending) go to Failed.Failed
                $statusCounts['Failed']['Failed'] += $result->count;
            }
        }

        return $statusCounts;
    }

    /**
     * Get combined overall status counts from multiple tables with the new format (Monthly)
     */
    private function getCombinedOverallStatusCounts($tables, $userId, $month, $year)
    {
        $combinedResults = [];
        
        foreach ($tables as $table => $userIdColumn) {
            $query = DB::table($table)
                ->select('status', DB::raw('COUNT(*) as count'));
            
            // Only apply user filter if user_ID is provided and not empty/All
            if (!empty($userId) && $userId !== 'All') {
                $query->where($userIdColumn, $userId);
            }
            
            // Only apply month filter if month is provided and not empty/null
            if (!empty($month)) {
                $query->whereMonth('created_at', $month);
            }
            
            // Only apply year filter if year is provided and not empty/null
            if (!empty($year)) {
                $query->whereYear('created_at', $year);
            }
            
            $results = $query->groupBy('status')
                ->get()
                ->toArray();

            $combinedResults = array_merge($combinedResults, $results);
        }

        // Initialize counts with the new format
        $statusCounts = [
            'Delivered' => 0,
            'Submitted' => 0,
            'Failed' => [
                'Failed' => 0
            ]
        ];

        // Combine counts for same status from different tables
        foreach ($combinedResults as $result) {
            $status = $result->status;
            
            if ($status === 'Delivered') {
                $statusCounts['Delivered'] += $result->count;
            } elseif ($status === 'Submitted') {
                $statusCounts['Submitted'] += $result->count;
            } else {
                // All other statuses (including Pending) go to Failed.Failed
                $statusCounts['Failed']['Failed'] += $result->count;
            }
        }

        return $statusCounts;
    }

    /**
 * Get users count as per role (Optimized Version)
 */
    public function getUsersCountByRole(Request $request) 
    {
        try {
            // Get logged-in user info
            $loggedInUserId = auth()->id();
            $loggedInUser = DB::table('users')
                ->select('role_ID')
                ->where('id', $loggedInUserId)
                ->first();

            if (!$loggedInUser) {
                return response()->json([
                    'success' => false,
                    'message' => 'User not found'
                ], 404);
            }

            $loggedInUserRole = $loggedInUser->role_ID;
            $rolesToCount = [];

            // Decide which roles can be viewed
            switch ($loggedInUserRole) {
                case 4: // Superadmin
                    $rolesToCount = [3, 2, 1]; // Admin, Reseller, Users
                    break;

                case 3: // Admin
                case 2: // Reseller
                    $rolesToCount = [2, 1]; // Reseller + Users
                    break;

                default:
                    return response()->json([
                        'success' => false,
                        'message' => 'Unauthorized access'
                    ], 403);
            }

            // Build query
            $query = DB::table('users')
                ->select('role_ID', DB::raw('COUNT(*) as count'))
                ->whereIn('role_ID', $rolesToCount);

            // ❗ Superadmin should NOT filter by created_by
            if ($loggedInUserRole != 4) {
                $query->where('created_by', $loggedInUserId);
            }

            // Execute query
            $counts = $query->groupBy('role_ID')
                ->get()
                ->keyBy('role_ID');

            // Prepare response based on role
            if ($loggedInUserRole == 4) {
                // Superadmin response
                $response = [
                    'admin' => $counts->has(3) ? $counts[3]->count : 0,
                    'reseller' => $counts->has(2) ? $counts[2]->count : 0,
                    'users' => $counts->has(1) ? $counts[1]->count : 0
                ];
            } else {
                // Admin or Reseller response
                $response = [
                    'reseller' => $counts->has(2) ? $counts[2]->count : 0,
                    'users' => $counts->has(1) ? $counts[1]->count : 0
                ];
            }

            return response()->json([
                'success' => true,
                'data' => $response
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getTopUsersByMsgCredit(Request $request)
    {
        try {
            $currentDate = now()->format('Y-m-d');
            
            // Get top 10 users with their total msgcredit sum from all tables
            $topUsers = $this->getCombinedMsgCreditSum($currentDate);

            return response()->json([
                'success' => true,
                'data' => [
                    'top_users' => $topUsers,
                    'date' => $currentDate
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get combined msgcredit sum from all tables (Fixed version)
     */
    private function getCombinedMsgCreditSum($date)
    {
        // Define all tables with their user ID columns
        $allTables = [
            // SMS tables
            ['table' => 'send_numbers', 'user_column' => 'userids'],
            ['table' => 'schedule_send_numbers', 'user_column' => 'userids'],
            
            // Voice tables
            ['table' => 'voice_call_numbers', 'user_column' => 'userid'],
            ['table' => 'schedule_voice_call_numbers', 'user_column' => 'userid'],
            
            // RCS tables
            ['table' => 'rcs_send_numbers', 'user_column' => 'userid'],
            ['table' => 'rcs_schedule_send_numbers', 'user_column' => 'userid'],
            
            // Whatsapp tables
            ['table' => 'whatsapp_send_numbers', 'user_column' => 'userid'],
            ['table' => 'whatsapp_schedule_send_numbers', 'user_column' => 'userid'],
            
            // International SMS tables
            ['table' => 'international_send_numbers', 'user_column' => 'userids'],
            ['table' => 'international_schedule_send_numbers', 'user_column' => 'userids']
        ];

        $userTotals = [];

        // Process each table separately and combine results
        foreach ($allTables as $tableInfo) {
            try {
                $table = $tableInfo['table'];
                $userColumn = $tableInfo['user_column'];
                
                // Check if table exists and has msgcredit column
                if (!$this->tableExists($table)) {
                    continue;
                }
                
                if (!$this->columnExists($table, 'msgcredit')) {
                    continue;
                }
                
                $results = DB::table($table)
                    ->select(
                        DB::raw("CAST({$userColumn} AS UNSIGNED) as user_id"),
                        DB::raw('COALESCE(SUM(msgcredit), 0) as total_msgcredit')
                    )
                    ->whereDate('created_at', $date)
                    ->whereNotNull($userColumn)
                    ->where($userColumn, '!=', '')
                    ->groupBy(DB::raw("CAST({$userColumn} AS UNSIGNED)"))
                    ->get();

                foreach ($results as $result) {
                    $userId = (int)$result->user_id;
                    if ($userId > 0) {
                        if (!isset($userTotals[$userId])) {
                            $userTotals[$userId] = 0;
                        }
                        $userTotals[$userId] += (float)$result->total_msgcredit;
                    }
                }
                
            } catch (\Exception $e) {
                continue; 
            }
        }

        // Sort by total msgcredit descending and take top 10
        arsort($userTotals);
        $topUserIds = array_slice(array_keys($userTotals), 0, 10, true);

        // Get usernames for the top user IDs
        if (empty($topUserIds)) {
            return [];
        }

        $users = DB::table('users')
            ->select('id', 'username')
            ->whereIn('id', $topUserIds)
            ->get()
            ->keyBy('id');

        // Format the response
        $result = [];
        foreach ($topUserIds as $userId) {
            $username = isset($users[$userId]) ? $users[$userId]->username : 'User ' . $userId;
            
            $result[] = [
                'username' => $username,
                'total_msgcredit' => (int)$userTotals[$userId]
            ];
        }

        return $result;
    }

    /**
     * Check if table exists
     */
    private function tableExists($tableName)
    {
        try {
            return DB::select("SHOW TABLES LIKE '{$tableName}'");
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * Check if column exists in table
     */
    private function columnExists($tableName, $columnName)
    {
        try {
            $columns = DB::select("SHOW COLUMNS FROM {$tableName} LIKE '{$columnName}'");
            return !empty($columns);
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
 * Get Cut Off Summary
 */
    public function getCutOffSummary(Request $request)
    {
        try {
            $request->validate([
                'duration' => 'sometimes|string|in:today,yesterday,this month'
            ]);

            $duration = $request->input('duration', 'today'); 
            
            // Get date range based on duration
            $dateRange = $this->getDateRange($duration);

            // Get Credit Cutoff from smart_cutoff table
            $creditCutoff = $this->getCreditCutoff($dateRange);
            
            // Get Total Submission from all send_messages tables
            $totalSubmission = $this->getTotalSubmission($dateRange);

            //  Get Percent
            $percent = $this->getCreditCutoffPercent($dateRange);

            return response()->json([
                'success' => true,
                'data' => [
                    'credit_cutoff' => $creditCutoff,
                    'total_submission' => $totalSubmission,
                    'duration' => $duration,
                    'percent' => $percent
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    /**
     * Get date range based on duration
     */
    private function getDateRange($duration)
    {
        $today = now();
        
        switch ($duration) {
            case 'today':
                return [
                    'start' => $today->format('Y-m-d'),
                    'end' => $today->format('Y-m-d')
                ];
                
            case 'yesterday':
                $yesterday = $today->subDay();
                return [
                    'start' => $yesterday->format('Y-m-d'),
                    'end' => $yesterday->format('Y-m-d')
                ];
                
            case 'this month':
                return [
                    'start' => $today->startOfMonth()->format('Y-m-d'),
                    'end' => $today->endOfMonth()->format('Y-m-d')
                ];
                
            default:
                return [
                    'start' => $today->format('Y-m-d'),
                    'end' => $today->format('Y-m-d')
                ];
        }
    }

    /**
     * Get Credit Cutoff from smart_cutoff table
     */
    private function getCreditCutoff($dateRange)
    {
        try {
            $query = DB::table('smart_cutoff')
                ->select(DB::raw('COALESCE(SUM(msg_count), 0) as total_msg_count'));
                
            // Apply date filter
            if ($dateRange['start'] === $dateRange['end']) {
                $query->whereDate('created_at', $dateRange['start']);
            } else {
                $query->whereBetween('created_at', [$dateRange['start'] . ' 00:00:00', $dateRange['end'] . ' 23:59:59']);
            }
            
            $result = $query->first();
            
            return (int)($result->total_msg_count ?? 0);
            
        } catch (\Exception $e) {
            return 0;
        }
    }

    private function getCreditCutoffPercent($dateRange)
    {
        try {
            $query = DB::table('smart_cutoff')
                ->select(DB::raw('COALESCE(SUM(percent), 0) as total_percent'));
                
            // Apply date filter
            if ($dateRange['start'] === $dateRange['end']) {
                $query->whereDate('created_at', $dateRange['start']);
            } else {
                $query->whereBetween('created_at', [
                    $dateRange['start'] . ' 00:00:00', 
                    $dateRange['end'] . ' 23:59:59'
                ]);
            }
            
            $result = $query->first();
            
            return (int)($result->total_percent ?? 0);
            
        } catch (\Exception $e) {
            return 0;
        }
    }

    /**
     * Get Total Submission from all send_messages tables
     */
    private function getTotalSubmission($dateRange)
    {
        // Define all send_messages tables
        $allTables = [
            // SMS tables
            'send_messages',
            'schedule_send_messages',
            
            // Voice tables
            'voice_call_messages',
            'schedule_voice_call_messages',
            
            // RCS tables
            'rcs_send_messages',
            'rcs_schedule_send_messages',
            
            // Whatsapp tables
            'whatsapp_send_messages',
            'whatsapp_schedule_send_messages',
            
            // International SMS tables
            'international_send_messages',
            'international_schedule_send_messages'
        ];

        $totalSubmission = 0;

        foreach ($allTables as $table) {
            try {
                $query = DB::table($table)
                    ->select(DB::raw('COALESCE(SUM(numbers_count), 0) as total_numbers_count'));
                    
                // Apply date filter
                if ($dateRange['start'] === $dateRange['end']) {
                    $query->whereDate('created_at', $dateRange['start']);
                } else {
                    $query->whereBetween('created_at', [$dateRange['start'] . ' 00:00:00', $dateRange['end'] . ' 23:59:59']);
                }
                
                $result = $query->first();
                
                $tableCount = (int)($result->total_numbers_count ?? 0);
                $totalSubmission += $tableCount;
                
            } catch (\Exception $e) {
                continue;
            }
        }
        
        return $totalSubmission;
    }

    public function getLastActivities(Request $request)
    {
        try {
            // Get the authenticated user's ID
            $userId = auth()->id();

            if (!$userId) {
                return response()->json([
                    'success' => false,
                    'message' => 'User not authenticated'
                ], 401);
            }

            // Get last 5 activities from ip_logs table (remove date restriction to get latest activities)
            $activities = $this->getUserActivities($userId);

            // Format response with just messages as requested
            $activityMessages = array_column($activities, 'message');

            return response()->json([
                'success' => true,
                'data' => [
                    'activities' => $activityMessages, // Only messages array
                    'total_activities' => count($activityMessages)
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    /**
     * Get user activities from ip_logs table (Updated version)
     */
    private function getUserActivities($userId)
    {
        try {
            // First, let's check if the table exists and has data
            $tableExists = DB::select("SHOW TABLES LIKE 'ip_logs'");
            
            if (empty($tableExists)) {
                return $this->getFallbackActivities();
            }

            // Check total records for this user
            $totalUserRecords = DB::table('ip_logs')
                ->where('user_id', $userId)
                ->count();

            // If no records found for today, get from any date (latest 5)
            $activities = DB::table('ip_logs')
                ->select('login_status', 'ip_address', 'created_at')
                ->where('user_id', $userId)
                ->orderBy('created_at', 'desc')
                ->limit(5)
                ->get();

            // If no activities found in ip_logs, return fallback
            if ($activities->count() === 0) {
                return $this->getFallbackActivities();
            }

            $formattedActivities = [];

            foreach ($activities as $activity) {
                $status = $activity->login_status == 1 ? 'Success' : 'Failed';
                $ipAddress = $activity->ip_address ?? 'Unknown IP';
                $createdAt = Carbon::parse($activity->created_at);
                
                // Format: Login Success by IP: 103.163.191.181 at 17-10-2025 12:56 PM
                $message = "Login {$status} by IP: {$ipAddress} at " . $createdAt->format('d-m-Y h:i A');
                
                $formattedActivities[] = [
                    'message' => $message,
                    'login_status' => $activity->login_status,
                    'status_text' => $status,
                    'ip_address' => $ipAddress,
                    'created_at' => $activity->created_at,
                    'formatted_date' => $createdAt->format('d-m-Y h:i A')
                ];
            }

            return $formattedActivities;

        } catch (\Exception $e) {
            return $this->getFallbackActivities();
        }
    }

    /**
     * Generate fallback activities when no data is found in ip_logs
     */
    private function getFallbackActivities()
    {
        $fallbackActivities = [];
        $currentTime = now();
        
        // Generate 5 fallback activities with current timestamp
        for ($i = 0; $i < 5; $i++) {
            $timestamp = $currentTime->copy()->subMinutes($i * 10); // 10 minutes apart
            
            $message = "Login Success by IP: 103.163.191." . (181 + $i) . " at " . $timestamp->format('d-m-Y h:i A');
            
            $fallbackActivities[] = [
                'message' => $message,
                'login_status' => 1,
                'status_text' => 'Success',
                'ip_address' => '103.163.191.' . (181 + $i),
                'created_at' => $timestamp->toDateTimeString(),
                'formatted_date' => $timestamp->format('d-m-Y h:i A')
            ];
        }
        return $fallbackActivities;
    }

    /**
 * Get Sender ID Report Graph
 */
    public function getSenderIdReportGraph(Request $request)
    {
        try {
            $request->validate([
                'date' => 'nullable|date', // Changed to nullable
                'sender_ID' => 'nullable|string' // Changed to nullable
            ]);

            $date = $request->input('date');
            $senderID = $request->input('sender_ID');

            // Get status counts from SMS tables for the specific sender ID
            $statusCounts = $this->getSenderIdStatusCounts($date, $senderID);

            return response()->json([
                'success' => true,
                'data' => [
                    'status_counts' => $statusCounts
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    /**
     * Get status counts from SMS tables for specific sender ID
     */
    private function getSenderIdStatusCounts($date, $senderID)
    {
        // Define SMS tables to check
        $smsTables = [
            ['table' => 'send_numbers', 'user_column' => 'userids'],
            ['table' => 'schedule_send_numbers', 'user_column' => 'userids']
        ];

        $combinedResults = [];

        foreach ($smsTables as $tableInfo) {
            try {
                $table = $tableInfo['table'];
                $userColumn = $tableInfo['user_column'];

                $query = DB::table($table)
                    ->select('status', DB::raw('COUNT(*) as count'))
                    ->where('isActive', 1); // Add isActive = 1 filter

                // Only apply sender ID filter if sender_ID is provided and not empty/All
                if (!empty($senderID) && $senderID !== 'All') {
                    $query->where('senderid', $senderID);
                }

                // Only apply date filter if date is provided and not null
                if ($date) {
                    $query->whereDate('created_at', $date);
                }

                $results = $query->groupBy('status')
                    ->get()
                    ->toArray();

                $combinedResults = array_merge($combinedResults, $results);

            } catch (\Exception $e) {
                continue;
            }
        }

        // Initialize counts with the required format
        $statusCounts = [
            'Delivered' => 0,
            'Submitted' => 0,
            'Failed' => [
                'Failed' => 0
            ]
        ];

        // Combine counts for same status from different tables
        foreach ($combinedResults as $result) {
            $status = $result->status;
            
            if ($status === 'Delivered') {
                $statusCounts['Delivered'] += $result->count;
            } elseif ($status === 'Submitted') {
                $statusCounts['Submitted'] += $result->count;
            } else {
                // All other statuses (including Pending, Failed, etc.) go to Failed.Failed
                $statusCounts['Failed']['Failed'] += $result->count;
            }
        }

        return $statusCounts;
    }

    /**
 * Get Scheduled Campaigns Dates (Clean version based on working debug)
 */
    public function getScheduledCampaignsDates(Request $request)
    {
        try {
            // Define all schedule message tables
            $scheduleTables = [
                'schedule_send_messages',
                'whatsapp_schedule_send_messages', 
                'rcs_schedule_send_messages',
                'international_schedule_send_messages',
                'schedule_voice_call'
            ];

            $allScheduleDates = [];

            foreach ($scheduleTables as $table) {
                try {
                    // First, check if table exists (keep this essential check)
                    $tableExists = DB::select("SHOW TABLES LIKE '{$table}'");
                    
                    if (empty($tableExists)) {
                        continue; // Skip if table doesn't exist
                    }

                    // Check if required columns exist (keep this essential check)
                    $columns = DB::select("SHOW COLUMNS FROM {$table}");
                    $columnNames = array_column($columns, 'Field');
                    
                    $hasScheduleDate = in_array('schedule_date', $columnNames);
                    $hasScheduleSent = in_array('schedule_sent', $columnNames);

                    if (!$hasScheduleDate || !$hasScheduleSent) {
                        continue; // Skip if missing required columns
                    }

                    // Use the exact same query from debug version that works
                    $dates = DB::table($table)
                        ->select(DB::raw('DISTINCT DATE(schedule_date) as schedule_date'))
                        ->where('schedule_sent', 0)
                        ->whereNotNull('schedule_date')
                        ->where('schedule_date', '>', now())
                        ->orderBy('schedule_date')
                        ->pluck('schedule_date')
                        ->toArray();

                    // Convert to required format
                    foreach ($dates as $date) {
                        $allScheduleDates[] = [
                            'schedule_date' => $date . ' 00:00:00',
                            'schedule_sent' => 0
                        ];
                    }

                } catch (\Exception $e) {
                    continue; // Skip on error
                }
            }

            // Remove duplicates
            $uniqueDates = [];
            $seenDates = [];

            foreach ($allScheduleDates as $date) {
                if (!in_array($date['schedule_date'], $seenDates)) {
                    $seenDates[] = $date['schedule_date'];
                    $uniqueDates[] = $date;
                }
            }

            // Sort by schedule date
            usort($uniqueDates, function($a, $b) {
                return strcmp($a['schedule_date'], $b['schedule_date']);
            });

            return response()->json([
                'success' => true,
                'data' => [
                    'schedule_dates' => $uniqueDates,
                    'total_dates' => count($uniqueDates)
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    /**
     * Get Scheduled Campaigns Details for specific date
     */
    public function getScheduledCampaignsDetails(Request $request)
    {
        try {
            $request->validate([
                'schedule_date' => 'required|date'
            ]);

            $scheduleDate = $request->input('schedule_date');

            // Define all schedule message tables with their service names
            $scheduleTables = [
                'schedule_send_messages' => 'SMS',
                'whatsapp_schedule_send_messages' => 'WhatsApp',
                'rcs_schedule_send_messages' => 'RCS',
                'international_schedule_send_messages' => 'International SMS',
                'schedule_voice_call' => 'Voice Call'
            ];

            $allCampaigns = [];

            foreach ($scheduleTables as $table => $serviceType) {
                try {

                    $campaigns = DB::table($table)
                        ->select(
                            'job_id',
                            'numbers_count',
                            'schedule_date',
                            DB::raw("'{$serviceType}' as service_type"),
                            DB::raw("'{$table}' as source_table")
                        )
                        ->where('schedule_sent', 0) // Not sent yet
                        ->whereDate('schedule_date', $scheduleDate)
                        ->orderBy('schedule_date')
                        ->get()
                        ->toArray();

                    foreach ($campaigns as $campaign) {
                        $allCampaigns[] = [
                            'job_id' => $campaign->job_id,
                            'number_count' => (int)$campaign->numbers_count,
                            'schedule_date' => $campaign->schedule_date
                        ];
                    }

                } catch (\Exception $e) {
                    continue;
                }
            }

            // Sort by schedule date
            usort($allCampaigns, function($a, $b) {
                return strtotime($a['schedule_date']) - strtotime($b['schedule_date']);
            });

            return response()->json([
                'success' => true,
                'data' => [
                    'campaigns' => $allCampaigns,
                    'schedule_date' => $scheduleDate,
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    /**
     * Get Scheduled Campaigns Calendar
     */
    public function getScheduledCampaignsCalendar(Request $request)
    {
        try {
            // Define all schedule message tables with their service names
            $scheduleTables = [
                'schedule_send_messages' => 'SMS',
                'whatsapp_schedule_send_messages' => 'WhatsApp',
                'rcs_schedule_send_messages' => 'RCS',
                'international_schedule_send_messages' => 'International SMS',
                'schedule_voice_call' => 'Voice Call'
            ];

            $calendarData = [];
            $allCampaigns = [];

            foreach ($scheduleTables as $table => $serviceType) {
                try {
                    // Check if table exists
                    $tableExists = DB::select("SHOW TABLES LIKE '{$table}'");
                    if (empty($tableExists)) {
                        continue;
                    }

                    // Check if required columns exist
                    $columns = DB::select("SHOW COLUMNS FROM {$table}");
                    $columnNames = array_column($columns, 'Field');
                    
                    $hasScheduleDate = in_array('schedule_date', $columnNames);
                    $hasScheduleSent = in_array('schedule_sent', $columnNames);
                    $hasJobId = in_array('job_id', $columnNames);
                    $hasNumbersCount = in_array('numbers_count', $columnNames);

                    if (!$hasScheduleDate || !$hasScheduleSent || !$hasJobId || !$hasNumbersCount) {
                        continue;
                    }

                    // Get all pending campaigns from this table
                    $campaigns = DB::table($table)
                        ->select(
                            'job_id',
                            'numbers_count',
                            'schedule_date',
                            DB::raw("'{$serviceType}' as service_type"),
                            DB::raw("'{$table}' as source_table")
                        )
                        ->where('schedule_sent', 0)
                        ->whereNotNull('schedule_date')
                        ->where('schedule_date', '>', now())
                        ->orderBy('schedule_date')
                        ->get();

                    foreach ($campaigns as $campaign) {
                        $scheduleDate = Carbon::parse($campaign->schedule_date);
                        $dateKey = $scheduleDate->format('Y-m-d');
                        $timeKey = $scheduleDate->format('H:i:s');
                        
                        $campaignData = [
                            'id' => $campaign->job_id . '-' . $scheduleDate->format('His'),
                            'title' => $serviceType . ' Campaign',
                            'time' => $campaign->schedule_date,
                            'jobId' => $campaign->job_id,
                            'number_count' => (int)$campaign->numbers_count
                        ];

                        $allCampaigns[] = $campaignData;

                        // Group by date
                        if (!isset($calendarData[$dateKey])) {
                            $calendarData[$dateKey] = [];
                        }
                        
                        $calendarData[$dateKey][] = $campaignData;
                    }

                } catch (\Exception $e) {
                    continue;
                }
            }

            // Sort campaigns within each date by time
            foreach ($calendarData as $date => &$campaigns) {
                usort($campaigns, function($a, $b) {
                    return strcmp($a['time'], $b['time']);
                });
            }

            // Get unique schedule dates for quick reference
            $uniqueDates = array_keys($calendarData);
            sort($uniqueDates);

            return response()->json([
                'success' => true,
                'data' => [
                    'scheduled_dates' => $calendarData
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Internal server error'
            ], 500);
        }
    }

    //  Dashboard Gateway Status
    public function getDashboardGatewayStatus(Request $request)
    {
        $request->validate([
            'url' => 'required|url',
        ]);

        try 
        {
            $response = Http::timeout(30)->get($request->url);

            if($response->successful()) 
            {
                return response()->json([
                    'success' => true,
                    'data' => $response->body()
                ]);
            } 
            else 
            {
                return response()->json([
                    'success' => false,
                    'error' => 'Failed to fetch data'
                ], 400);
            }

        } 
        catch(\Exception $e) 
        {
            return response()->json([
                'success' => false,
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getUserWiseQueue(Request $request)
    {
        try {
            // Get the authenticated user
            $user = Auth::user();
            
            if (!$user) {
                return response()->json([
                    'success' => false,
                    'message' => 'User not authenticated'
                ], 401);
            }

            // Get all active users
            $users = DB::table('users')
                ->select('id', 'username as username')
                ->where('isActive', 1)
                ->get();

            $userQueueData = [];
            
            // Set timezone to IST and format
            $currentTime = Carbon::now('Asia/Kolkata');
            $formattedTime = $currentTime->format('D M d Y H:i:s \G\M\T\+0530');

            foreach ($users as $userRecord) {

                // Count schedule queue
                $scheduleQueue = DB::table('schedule_send_numbers')
                    ->where('userids', $userRecord->id)
                    ->where('is_picked', 0)
                    ->count();

                // Count live queue
                $liveQueue = DB::table('send_numbers')
                    ->where('userids', $userRecord->id)
                    ->where('is_picked', 0)
                    ->count();

                // CONDITION: Only add if schedule_queue OR live_queue is >= 1
                if ($scheduleQueue > 0 || $liveQueue > 0) {
                    $userQueueData[] = [
                        'user_ID' => $userRecord->id,
                        'username' => $userRecord->username,
                        'schedule_queue' => $scheduleQueue,
                        'live_queue' => $liveQueue
                    ];
                }
            }

            return response()->json([
                'success' => true,
                'data' => $userQueueData,
                'current_time' => $formattedTime
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error fetching user queue activities',
                'error' => $e->getMessage()
            ], 500);
        }
    }

}