<?php

namespace App\Console\Commands;

use App\Report;
use App\Tools\EmailAlert;
use App\Tools\ServiceTools;
use App\User;
use App\UserJob;
use Cache\Adapter\Redis\RedisCachePool;
use Cache\Bridge\SimpleCache\SimpleCacheBridge;
use Carbon\Carbon;
use ExcelMerge\ExcelMerge;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Settings;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ReportProcessor extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'areport:start';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Process report ';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->info(Carbon::now()->toDateTimeString().'| Checking for report to process...');
        Report::query()
            ->where('status', 'pending')
            ->select(['id'])
            ->orderBy('id')->chunk(10, function($jo) {

                $this->info('Fetched '.count($jo).' pending report(s)');
                $query = [];

                foreach ($jo as $j) {
                    try {
                        //check stop service flag
                        if (!ServiceTools::can_run_service()) {
                            $this->info('Service Stopped By User!');
                            exit(1);
                        }

                        $job = Report::find($j->id);
                        if (empty($job))
                            continue;


                        $user = User::find($job->user_id);

                        //start processing the report
                        $filter_by = $job->filter_by;

                        $from_date = Carbon::parse(Carbon::parse($job->from_date)->toDateString());
                        $to_date = Carbon::parse(Carbon::parse($job->to_date)->addDay()->toDateString());
                        $to_date = $to_date->subSecond();
                        $service_name = 'sms';
                        $counter = 0;
                        $file_name = null;


                        $this->info('Preparing report '.$job->name);

                        $this->setCache();

                        if ($filter_by == 'service') {

                                $query = DB::table('logs')->where([
                                    ['user_id', '=', $job->user_id],
                                    ['submit_date', '>=', $from_date],
                                    ['submit_date', '<', $to_date]
                                ]);

                            $file_name = $this->buildExcel($query,$counter, $user, $job, $service_name,$file_name);


                        }

                        else if ($filter_by == 'campaign') {

                            $this->info('Processing campaign...');
                            $campaign = json_decode($job->filter_by_campaign);
                            $file_name = $this->buildExcelByCamp($campaign,$counter, $user, $job, $service_name,$file_name,$from_date,$to_date);

                        }

                        else if ($filter_by == 'sender')
                        {
                              $this->info('Processing sender...');
                              $sender = $job->filter_by_sender;
                                $query = DB::table('logs')->where([
                                    ['user_id', '=', $job->user_id],
                                    ['sender', '=', $sender],
                                    ['submit_date', '>=', $from_date],
                                    ['submit_date', '<', $to_date]
                                ]);

                            $file_name = $this->buildExcel($query,$counter, $user, $job, $service_name,$file_name);
                        }


                        $job->status =   empty($file_name) ? 'NONE': 'done';
                        $job->path   =  !empty($file_name) ? storage_path("reports/$file_name") : null;
                        $job->save();
                        $this->info("DONE!");

                    } catch (\Throwable $t) {
                        $this->error($t->getMessage());
                    }
                }
            });
    }


    public function buildExcel($query, $counter, $user, $job, $service_name,$file_name)
    {
        $this->info('Building report '.$job->name);


        $query->orderBy('submit_date', 'desc')->chunk(10000, function ($row) use (&$fileName, &$counter, &$user, &$job, &$folder, &$service_name,&$file_name) {
            try {

                $fileName = 'report_' . $user->username;
                $folder = storage_path('reports/'.$fileName);
                $folder = str_replace(' ', '_', $folder);

                if (!file_exists($folder)) {
                    mkdir($folder,0777,true);
                }


                $counter++;

                $i = 2;

                $spreadsheet = new Spreadsheet();


                $sheet = $spreadsheet->getActiveSheet();

                $sheet->setCellValue('A1', 'Campaign');
                $sheet->setCellValue('B1', 'Msisdn');
                $sheet->setCellValue('C1', 'Message');
                $sheet->setCellValue('D1', 'Status');
                $sheet->setCellValue('E1', 'Username');
                $sheet->setCellValue('F1', 'Sender');
                $sheet->setCellValue('G1', 'Submit Date');
                $sheet->setCellValue('H1', 'SMS Count');
                $sheet->setCellValue('I1', 'Network');
                $sheet->setCellValue('J1', 'MsgId');
                $sheet->setCellValue('K1', 'Delivery Date');

                foreach ($row as $dat) {

                    $i++;
                    $sheet->setCellValue('A'.$i, $dat->name ?? null);
                    $sheet->setCellValue('B'.$i, $dat->msisdn);
                    $sheet->setCellValue('C'.$i, $dat->message);
                    $sheet->setCellValue('D'.$i, $dat->status);
                    $sheet->setCellValue('E'.$i, $dat->username);
                    $sheet->setCellValue('F'.$i, $dat->sender);
                    $sheet->setCellValue('G'.$i, $dat->submit_date);
                    $sheet->setCellValue('H'.$i, $dat->sms_count);
                    $sheet->setCellValue('I'.$i, $dat->network);
                    $sheet->setCellValue('J'.$i, $dat->msgid);
                    $sheet->setCellValue('K'.$i, $dat->delivery_date);

                }


                $writer = new Xlsx($spreadsheet);
                $file_name = $fileName.'_sheet_'.$counter.'.xlsx';
                $writer->save(storage_path("reports/$fileName/$file_name"));

            } catch (\Exception $e) {
                throw $e;
            }
        });
        return $file_name;
    }


    public function buildExcelByCamp($campaign,$counter, $user, $job, $service_name,$file_name,$from_date,$to_date)
    {
        ini_set("memory_limit", "-1");
        $this->info('Building campaign report '.$job->name);

        $spreadsheet = new Spreadsheet();
        $ind = 0;
        $campaign = [$campaign];
        $this->info(count($campaign));
        try {
            foreach ($campaign as $camp) {
                $query = DB::table('logs')
                    ->join('user_jobs', 'user_jobs.id', '=', 'logs.job_id')
                    ->where([
                        ['logs.user_id', '=', $job->user_id],
                        ['logs.submit_date', '>=', $from_date],
                        ['logs.submit_date', '<', $to_date],
                        ['user_jobs.name', '=', $camp]
                    ]);

                $workSheet = new Worksheet($spreadsheet, $camp);
                $sheet = $spreadsheet->addSheet($workSheet, $ind);

                $query->orderBy('submit_date', 'desc')->chunk(10000, function ($row)
                use (&$fileName, &$counter, &$user, &$job, &$folder, &$service_name, &$file_name, &$filter_by, &$spreadsheet, &$sheet) {
                    try {

                        $fileName = 'report_' . $user->username;
                        $folder = storage_path('reports/' . $fileName);
                        $folder = str_replace(' ', '_', $folder);

                        if (!file_exists($folder)) {
                            mkdir($folder, 0777, true);
                        }

                        $counter++;
                        $i = 2;


                        $sheet->setCellValue('A1', 'Campaign');
                        $sheet->setCellValue('B1', 'Msisdn');
                        $sheet->setCellValue('C1', 'Message');
                        $sheet->setCellValue('D1', 'Status');
                        $sheet->setCellValue('E1', 'Username');
                        $sheet->setCellValue('F1', 'Sender');
                        $sheet->setCellValue('G1', 'Submit Date');
                        $sheet->setCellValue('H1', 'SMS Count');
                        $sheet->setCellValue('I1', 'Network');
                        $sheet->setCellValue('J1', 'MsgId');
                        $sheet->setCellValue('K1', 'Delivery Date');

                        foreach ($row as $dat) {

                            $sheet->setCellValue('A' . $i, $dat->name ?? null);
                            $sheet->setCellValue('B' . $i, $dat->msisdn);
                            $sheet->setCellValue('C' . $i, $dat->message);
                            $sheet->setCellValue('D' . $i, $dat->status);
                            $sheet->setCellValue('E' . $i, $dat->username);
                            $sheet->setCellValue('F' . $i, $dat->sender);
                            $sheet->setCellValue('G' . $i, $dat->submit_date);
                            $sheet->setCellValue('H' . $i, $dat->sms_count);
                            $sheet->setCellValue('I' . $i, $dat->network);
                            $sheet->setCellValue('J' . $i, $dat->msgid);
                            $sheet->setCellValue('K' . $i, $dat->delivery_date);
                            $i++;
                        }


                    } catch (\Exception $e) {
                        $this->error($e->getMessage());
                    }
                });
                $ind++;
            }


            $sheetIndex = $spreadsheet->getIndex(
                $spreadsheet->getSheetByName('Worksheet')
            );  //remove unnecessary sheet from array

            $spreadsheet->removeSheetByIndex($sheetIndex);
            $writer = new Xlsx($spreadsheet);
            $file_name = $fileName . '_sheet_' . $counter . '.xlsx';
            $path = $fileName.'/'.$file_name;
            $writer->save(storage_path("reports/$path"));

            return $path;
        }
        catch (\Exception $e)
        {
            $this->error($e->getMessage());
        }
    }


    public function setCache()
    {
        $this->info('Setting Cache Via Redis');
        $client = new \Redis();
        $client->connect('127.0.0.1', 6379);
        $pool = new RedisCachePool($client);
        $simpleCache =  new SimpleCacheBridge($pool);
        Settings::setCache($simpleCache);
    }

}