<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Exception;
use App\Models\User;
use App\Models\SentMail;
use App\Models\Prompt;
use App\Models\Message;
use Firebase\JWT\JWT;
use App\Models\Freshcount;
use App\Models\ProjectResponse;
use Illuminate\Http\Request;
use Carbon\Carbon;
use Illuminate\Support\Facades\Mail;
use App\Mail\DailyEmailSummaryMail;
use PHPMailer\PHPMailer\PHPMailer;
// use PHPMailer\PHPMailer\Exception;


class SendDailyEmailSummary extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:send-daily-email-summary';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Send daily email using PHPMailer';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        //
        $this->info("Running email processing job...");
        $mail = new PHPMailer(true);

        // $batchSize = 100;
        $batchSize = 5;

        $queueJobs = DB::connection('master')
            ->table('cron_mail_send')
            ->where('mail_send', '!=', 'done')   // pick anything not done
            ->whereDate('created_at', DB::raw('CURDATE()')) // today only
            ->orderByDesc('priority')
            ->orderBy('created_at')
            ->orderBy('updated_at')
            ->limit($batchSize)
            ->get();

        if ($queueJobs->isEmpty()) {
            $this->info("No jobs found");
            return;
        }
        $processedJobIds = [];

        foreach ($queueJobs as $job) {

            $client = DB::connection('master')->table('clients')->find($job->client_id);
            if (!$client) {
                $this->error("Client {$job->client_id} not found");
                continue;
            }
            try {
                // 2. Dynamically switch to client DB
                config(['database.connections.clientdb.database' => $client->db_name]);
                DB::purge('clientdb'); // force reconnect
                $clientDb = DB::connection('clientdb');

                // 3. Get employee info
                $employees = $clientDb->table('users')->get();

                if (!$employees) {
                    throw new Exception("Employee {$job->employee_id} not found");
                }
                // Get all users with email
                $allUsers = $clientDb->table('users')
                    ->whereNotNull('email')
                    ->get();

                // ==========================
                // 1️⃣ ADMIN USERS FIRST
                // ==========================

                $adminUsers = $allUsers->where('user_level', 2);

                foreach ($adminUsers as $user) {

                    $targetUsers = $clientDb->table('users')
                                    ->where(function ($q) use ($user) {
                                        $q->where('auth_id', $user->id)
                                        ->orWhere('id', $user->id);
                                    })
                                    ->get(); // Admin sees all

                    $this->sendSummaryMail($client, $clientDb, $user, $targetUsers);
                }

                // ==========================
                // 2️⃣ DEPARTMENT HEADS
                // ==========================

                $deptHeads = $allUsers->where('user_level', 1);

                foreach ($deptHeads as $user) {

                    $targetUsers = $clientDb->table('users')
                        ->where(function ($q) use ($user) {
                            $q->where('Department_head_id', $user->id)
                            ->orWhere('id', $user->id);
                        })
                        ->get();

                    $this->sendSummaryMail($client, $clientDb, $user, $targetUsers);
                }


                // Get all employees
                // $employees = $clientDb->table('users')->get();

                // $combinedCounts = [];

                // foreach ($employees as $emp) {

                //     $combinedCounts[] = [
                //         'id' => $emp->id,
                //         'email' => $emp->email,
                //         'counts' => $this->common($emp->id)
                //     ];
                // }

                // $adminUsers = $clientDb->table('users')
                //     ->where('auth_id', 0)
                //     ->whereNotNull('email')
                //     ->get();

                // if ($adminUsers->isEmpty()) {
                //     $this->error("No admin users found");
                //     continue;
                // }

                // try {
                //     $mail = new PHPMailer(true);

                //     $mail->isSMTP();
                //     $mail->Host       = 'smtp.gmail.com';
                //     $mail->SMTPAuth   = true;
                //     $mail->Username   = env('MAIL_USERNAME'); 
                //     $mail->Password   = env('MAIL_PASSWORD'); 
                //     $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
                //     $mail->Port       = 587;

                //     $mail->setFrom(env('MAIL_FROM_ADDRESS'), env('MAIL_FROM_NAME'));

                //     // Send to company email
                //     $mail->addAddress(env('MAIL_FROM_ADDRESS'));

                //     // All admins in BCC
                //     foreach ($adminUsers as $adminUser) {
                //         $mail->addBCC($adminUser->email);
                //     }
                //     // $mail->addBCC('ami@mukesoft.com'); // for testing, remove in production

                //     $mail->isHTML(true);
                //     $mail->Subject = 'Daily Client Summary';

                //     $mail->Body = view('emails.daily_summary', [
                //         'client' => $client,
                //         'allEmployees' => $combinedCounts
                //     ])->render();

                //     $mail->AltBody = 'Daily Client Summary';

                //     $mail->send();

                //     $this->info("Summary sent for client {$client->id}");

                // } catch (Exception $e) {
                //     $this->error("Mailer Error: {$mail->ErrorInfo}");
                // }


                // 5. Update job mail_send
                DB::connection('master')->table('cron_mail_send')
                    ->where('id', $job->id)
                    ->update([
                        'mail_send' => 'done',
                        'updated_at' => now()
                    ]);
                $processedJobIds[] = $job->id;
            } catch (Exception $e) {
                DB::connection('master')->table('cron_mail_send')
                    ->where('id', $job->id)
                    ->update([
                        'mail_send' => 'failed',
                        'updated_at' => now()
                    ]);

                $this->error("Job {$job->id} failed: " . $e->getMessage());

                $logMessage = "[" . now() . "] Job {$job->id} failed: " . $e->getMessage() . PHP_EOL;

                file_put_contents(
                    storage_path('logs/Job_fail_log.log'),
                    $logMessage,
                    FILE_APPEND
                );
            }
        }
    }
    public function common($client, $employee_id = null)
    {
        $userId = $employee_id ?? Auth::id();

        // 2. Dynamically switch to client DB
        config(['database.connections.clientdb.database' => $client->db_name]);
        DB::purge('clientdb'); // force reconnect
        $clientDb = DB::connection('clientdb');

        $admin = $clientDb->table('users')->where('id', $userId)
            ->where('auth_id', 0)
            ->select('id', 'email')
            ->first();
        $emailArray = [];
        if ($admin) {

            $relatedUsers = $clientDb->table('users')->where('auth_id', $admin->id)
                ->get();

            foreach ($relatedUsers as $relatedUser) {
                $emailArray[] = [
                    'id' => $relatedUser->id,
                    'email' => $relatedUser->email,
                ];
            }
        }

        if (is_null($userId)) {
            abort(403, 'Unauthorized action. Please first Login common function Error', ['Content-Type' => 'text/html']);
        }
        // $userId = Auth::id(); // Get logged-in user ID
        // $userId = 1;
        if (true) {
        } else {
            $sentMails = DB::table('sent_mails')
                ->where('user_id', $userId)
                ->where('matched_subject', '0')
                // ->where('sent_mail_id','21')
                ->get();
            // dd($sentMails);
            foreach ($sentMails as $mail) {
                // return;
                // echo $mail->id."<br>";
                if ($mail->sent_mail_id) {
                    $message = DB::table('messages')
                        ->where('user_id', $userId)
                        ->where('clean_subject', $mail->clean_subject)
                        ->where('sender_email', $mail->receiver_email)
                        ->where('sent_mail_id', '-1')
                        ->first();
                    // ->get();
                    // dd( $message);
                    if ($message) {
                        // 2. Update the message to attach it with sent_mail_id
                        $update_messages = DB::table('messages')
                            ->where('id', $message->id)
                            ->update([
                                'sent_mail_id' => $mail->sent_mail_id,
                                'sent_date_s' => $mail->sent_date_s,
                                'message_status' => '1'
                            ]);
                        // echo "1";
                        // echo $update_messages;
                        // dd($update_messages);
                        // 3. Update matched_subject = 1 in current sent_mail row
                        $update_sent_mails = DB::table('sent_mails')
                            ->where('id', $mail->id)
                            ->update([
                                'matched_subject' => 1
                            ]);
                        // echo "2";
                        // echo $update_sent_mails;
                        // dd($update_sent_mails);
                    }
                    // dd("heello");
                }
            }
        }
        // dd("heello");
        // Return data as an associative array
        $email = null;
        $user_data = $clientDb->table('users')->where('id', $userId)->first();
        if ($user_data) {
            // $email=null;
            $email = $user_data->email;
            // $query->where('sender_email', '!=',$email );
        }

        if (true) {

            return [
                'employee_data' => $emailArray,

                'countAll' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->distinct('email_id')->count('id'), // Total unique messages
                'countUnread' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('message_status', 0)->distinct('email_id')->count('id'), // Unread messages
                'countRead' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('message_status', 1)->distinct('email_id')->count('id'), // Read messages

                'countis_not_spam' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_spam', 1)->where('is_promotion', '0')->where('message_status', 0)->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),
                'countis_spam' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_spam', 0)->where('is_promotion', '0')->where('message_status', 0)->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),
                'neutral' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_spam', 3)->where('is_promotion', '0')->where('message_status', 0)->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'promotion' => $clientDb->table('messages')->where('user_id', $userId)->where('is_promotion', '!=', '0')->where('sender_email', '!=', $email)->where('message_status', 0)->distinct('email_id')->count('id'),

                'send-mail' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_promotion', '0')->where('track_reply_email', '0')->where('action_required', 1)->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'replied_mail' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '!=', '-1')->distinct('email_id')->where('action_required', 1)->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'reply_pending_48hrs' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('action_required', 1)->where('sent_mail_id', '-1')->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >=  NOW() - INTERVAL 48 HOUR")->distinct('email_id')
                ->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'reply_pending_1month' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('action_required', 1)->where('sent_mail_id', '-1')->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >=  NOW() - INTERVAL 1 MONTH")->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),
                
                // 'pending_last_3_days' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '-1')->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >= ?", [Carbon::now()->subDays(3)->format('Y-m-d H:i:s')])->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                
                'pending_last_3_days' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '-1')->where('action_required', 1)->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >=  NOW() - INTERVAL 3 DAY")->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'sales' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('category', 1)->where('is_promotion', '=', '0')->where('message_status', 0)->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),
                'amc' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('category', 2)->where('is_promotion', '=', '0')->where('message_status', 0)->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'countis_not_spam_heading' => $clientDb->table('prompts')->where('user_id', $userId)->where('status', 0)->pluck('keywords')->first(),
            ];
        } else {

            return [
                'employee_data' => $emailArray,

                'countAll' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->distinct('email_id')->count('id'), // Total unique messages
                'countUnread' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('message_status', 0)->distinct('email_id')->count('id'), // Unread messages
                'countRead' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('message_status', 1)->distinct('email_id')->count('id'), // Read messages

                'countis_not_spam' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_spam', 1)->where('is_promotion', '=', '0')->where('message_status', 0)->distinct('email_id')->count('id'),
                'countis_spam' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_spam', 0)->where('is_promotion', '=', '0')->where('message_status', 0)->distinct('email_id')->count('id'),
                'neutral' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('is_spam', 3)->where('is_promotion', '=', '0')->where('message_status', 0)->distinct('email_id')->count('id'),

                'promotion' => $clientDb->table('messages')->where('user_id', $userId)->where('is_promotion', '!=', '0')->where('sender_email', '!=', $email)->where('message_status', 0)->distinct('email_id')->count('id'),

                'send-mail' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '-1')->where('action_required', 1)->distinct('email_id')->count('id'),

                'replied_mail' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '!=', '-1')->distinct('email_id')->count('id'),

                'reply_pending_48hrs' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('action_required', 1)->where('sent_mail_id', '-1')->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >=  NOW() - INTERVAL 48 HOUR")->distinct('email_id')->count('id'),

                'reply_pending_1month' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('action_required', 1)->where('sent_mail_id', '-1')->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >=  NOW() - INTERVAL 1 MONTH")->distinct('email_id')->count('id'),

                // 'pending_last_3_days' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '-1')->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >= ?", [Carbon::now()->subDays(3)->format('Y-m-d H:i:s')])->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),

                'pending_last_3_days' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('sent_mail_id', '-1')->where('action_required', 1)->whereRaw("STR_TO_DATE(LEFT(sent_date, 25), '%a, %e %b %Y %H:%i:%s') >=  NOW() - INTERVAL 3 DAY")->distinct('email_id')->whereRaw('LOWER(label_ids) NOT LIKE ?', ['%spam%'])->count('id'),


                'sales' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('category', 1)->where('message_status', 0)->distinct('email_id')->count('id'),
                'amc' => $clientDb->table('messages')->where('user_id', $userId)->where('sender_email', '!=', $email)->where('category', 2)->where('message_status', 0)->distinct('email_id')->count('id'),

                'countis_not_spam_heading' => $clientDb->table('prompts')->where('user_id', $userId)->where('status', 0)->pluck('keywords')->first(),
            ];
        }
    }
    private function sendSummaryMail($client, $clientDb, $user, $targetUsers)
    {
        $combinedCounts = [];

        foreach ($targetUsers as $emp) {
            $combinedCounts[] = [
                'id' => $emp->id,
                'email' => $emp->email,
                'counts' => $this->common($client, $emp->id)
            ];
        }
        
        try {
            $mail = new PHPMailer(true);

            $mail->isSMTP();
            $mail->Host       = 'smtp.gmail.com';
            $mail->SMTPAuth   = true;
            $mail->Username   = env('MAIL_USERNAME');
            $mail->Password   = env('MAIL_PASSWORD');
            $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
            $mail->Port       = 587;

            $mail->setFrom(env('MAIL_FROM_ADDRESS'), env('MAIL_FROM_NAME'));

            $mail->addAddress($user->email);
            // $mail->addAddress('pankaj@mukesoft.com');

            $mail->isHTML(true);
            $mail->Subject = 'Daily Summary';

            $mail->Body = view('emails.daily_summary', [
                'client' => $client,
                'allEmployees' => $combinedCounts
            ])->render();

            $mail->AltBody = 'Daily Summary';

            // $mail->send();
            $this->info("Mail Couts: " . json_encode($combinedCounts)); // Log the email body for debugging
            // die;

            $this->info("Summary sent to {$user->email}");
            // die;
            // $this->info("Summary content: " . json_encode($combinedCounts)); // Log the email body for debugging
        } catch (Exception $e) {
            $this->error("Mailer Error for {$user->email}: " . $e->getMessage());
        }
    }

}