<?php

namespace App\Http\Controllers\Admin;

use App\AuditTrail;
use App\Company;
use App\DataTables\AdminClientHistDataTable;
use App\DataTables\AdminClientInvoiceDataTable;
use App\DataTables\AdminCreditHistoryDataTable;
use App\DataTables\AllSenderIDDataTable;
use App\DataTables\WelcomeReportDatatable;
use App\Http\Controllers\Controller;
use App\Log;
use App\Role;
use App\Setting;
use App\Tools\SmsTools;
use App\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Response;
use Intervention\Image\Facades\Image;
use PDF;

class DashboardController extends Controller
{

    public function index()
    {


        $role = auth()->user()->role->name;
        if(strtolower($role) !== 'admin')
            if(strtolower($role) == 'user')
                return redirect()->route('admin.main_route');
            else
                return redirect()->route('reseller.main_route');

        return view('admin.welcome');
    }

    public function financial_index() {
        return view('admin.financial_dashboard');
    }


    public function get_sms_undelivered_log_24h(Request $request,WelcomeReportDatatable $welcome)
    {
        session(['state'=>$request->input('state')]);
        return $welcome->render('admin.report.default');
    }


    public function view_clients_for_invoice(AdminClientInvoiceDataTable $dataTable)
    {
        return $dataTable->render('admin.report.clients_invoice');
    }

    public function view_clients_for_credit_history(AdminClientHistDataTable $dataTable)
    {
        return $dataTable->render('admin.report.clients_cred_hist');
    }

    public function get_credit_history(AdminCreditHistoryDataTable $dataTable, $client_id) {
        $user = User::query()->findOrFail($client_id);
        return $dataTable->with_client_id($client_id)->render('admin.report.credit_history', ['user'=>$user]);
    }


    public function download_receipt($client_id, $id) {
        $record = User::findOrFail($client_id)->creditHistory()->findOrFail($id);
//        return view('print.payment_receipt', ['record' => $record]);
        $pdf = PDF::loadView('print.payment_receipt', ['record' => $record]);
        return $pdf->download('receipt.pdf');
    }

    public function get_admin_users(Request $request) {
        $type = $request->get('type');

        if(empty($type)) {
            return [];
        }
        $role = Role::query()->where('name', $type)->firstOrFail();
//        $users = User::where('role_id', $role->id)->get(['id', 'full_name']);

        $users = User::query()->where('username','<>','csdglcamabe')->whereHas('company', function ($query) use ($role) {
            $query->where('role_id',$role->id);
        })->get(['id','username']);

        return $users;
    }

    public function get_users_for_invoice(Request $request) {
        $type = $request->get('type');
        if(empty($type)) {
            return [];
        }
        if($type == 'esme') {
            return DB::connection('kannel')
                        ->table('unit_prices')
                        ->select(DB::raw('username as username, username as full_name'))
                        ->get(['username', 'full_name']);
        }
        $role = Role::where('name', $type)->firstOrFail();
        return User::where('role_id', $role->id)->where('username','<>','csdglcamabe')->get(['username', 'full_name']);
    }

    public function get_user_senders(Request $request) {
        $id = $request->get('userId');
        if(empty($id)) {
            return [];
        }

        $sender = DB::table('sms_log')->where('user_id', $id)->where('username','<>','csdglcamabe')->groupBy('sender')->get(['sender']);
        return $sender;
    }

    public function get_admin_data_user(Request $request) {
        $returnvalue = [];
        $returnvalue[] = ['Count', 'Delivered', 'Undelivered','Acknowledged','Expired'];
        $username = $request->input('username');

        //process for day of week
        $now = Carbon::now()->setTime(0, 0, 0);
        $one_week_ago = Carbon::now()->addWeeks(-1)->setTime(0, 0, 0);
        while ($one_week_ago->lte($now)) {
            $one_week_ago = $one_week_ago->addDay();
            $from_date = Carbon::parse($one_week_ago->toDateString());
            $to_date = clone $from_date;
            $to_date = Carbon::parse($to_date->addDays(1)->addSeconds(-1));

            $data = DB::select('select sl.`status`, count(*) as total from `sms_log` sl LEFT JOIN `users` u ON u.id=sl.user_id where `u`.username=? AND sl.`submit_date` between ? and ? GROUP BY sl.`status`', [
                $username,
                $from_date->toDateTimeString(),
                $to_date->toDateTimeString()
            ]);
            $tempArray = [];
            foreach ($data as $dat) {
                $status = $dat->status;
                $total = $dat->total;
                $tempArray["{$status}"] = $total;
            }
            $returnvalue[] = [
                $this->get_day_name($one_week_ago),
                isset($tempArray['DELIVRD']) ? $tempArray['DELIVRD'] : 0,
                isset($tempArray['UNDELIV']) ? $tempArray['UNDELIV'] : 0,
                isset($tempArray['ACK/']) ? $tempArray['ACK/'] : 0,
                isset($tempArray['EXPIRED']) ? $tempArray['EXPIRED'] : 0
            ];
        }

        return $returnvalue;
    }

    public function get_user_log_summary(Request $request) {
        $type = $request['type'];

        $date = Carbon::now();
        $from_date = Carbon::parse($date->toDateString());
        $to_date = clone $from_date;
        $to_date = Carbon::parse($to_date->addDays(1)->addSeconds(-1));

        if($type == 'month') {
            $from_date = $from_date->setDate($from_date->year, $from_date->month, 1);
            $to_date = Carbon::parse($from_date->copy()->addMonth()->addSeconds(-1));
            $dash_title = 'Monthly Dashboard';
        } else if($type == 'week') {
            $from_date = $this->get_weekly_start_day();
            $to_date = $this->get_weekly_end_day();
            $dash_title = 'Weekly Dashboard';
        } else {
            $date = $this->get_date_by_day_name($type);
            $from_date->setDate($date->year, $date->month, $date->day);
            $to_date->setDate($date->year, $date->month, $date->day);
            $dash_title = ucfirst($this->get_day_full_name($type)).' \'s Dashboard';
        }

        $data = DB::select('select `status`, count(*) as total from `sms_log`
                            where (`submit_date` between ? and ?)
                             and `originated`=?
                            GROUP BY `status`', [
            $from_date->toDateTimeString(),
            $to_date->toDateTimeString(),
            'mtnuser'
        ]);

        $tempArray = [];
        foreach ($data as $dat) {
            $status = $dat->status;
            $total = $dat->total;
            $tempArray["{$status}"] = $total;
        }

        $total_sms_sent = Log::whereBetween('submit_date', [
            $from_date->toDateTimeString(),
            $to_date->toDateTimeString()
        ])->where('originated', 'mtnuser')
            ->count();

        $total_sms_delivered = isset($tempArray['DELIVRD']) ? $tempArray['DELIVRD']: 0;
        $total_sms_ack = isset($tempArray['ACK/']) ? $tempArray['ACK/'] : 0;
        $total_sms_undelivered = isset($tempArray['UNDELIV']) ? $tempArray['UNDELIV'] : 0;
        $total_sms_expired = isset($tempArray['EXPIRED']) ? $tempArray['EXPIRED'] : 0;

        return [
            'dash_title' => $dash_title,
            'total_sms_sent' => (int)$total_sms_sent,
            'total_sms_delivered' => (int)$total_sms_delivered,
            'total_sms_ack' => (int)$total_sms_ack,
            'total_sms_undelivered' => (int)$total_sms_undelivered,
            'total_sms_expired' => (int)$total_sms_expired,
            'total_client' => (int)(User::count()-1)
        ];
    }

    public function get_user_financial_summary(Request $request) {
        $type = $request['type'];
        $resellerId = Role::whereName('reseller')->firstOrFail()->id;
        $clientId = Role::whereName('user')->firstOrFail()->id;
        $activeClientThreshold = 800;

        $date = Carbon::now();
        $from_date = Carbon::parse($date->toDateString());
        $to_date = clone $from_date;
        $to_date = Carbon::parse($to_date->addDays(1)->addSeconds(-1));

        //compute expenses
        $setting = Setting::whereUserId(auth()->id())->first();
        $setting = !empty($setting) ? $setting : new Setting();
        $electricity = empty($setting->electricity) ? 0 : $setting->electricity;
        $water = empty($setting->water) ? 0 : $setting->water;
        $others = empty($setting->others) ? 0 : $setting->others;

        $total_expense = $electricity + $water + $others;

        if($type == 'month') {
            $from_date = $from_date->setDate($from_date->year, $from_date->month, 1);
            $to_date = Carbon::parse($from_date->copy()->addMonth()->addSeconds(-1));
            $dash_title = 'Monthly Dashboard';
        } else if($type == 'week') {
            $from_date = $this->get_weekly_start_day();
            $to_date = $this->get_weekly_end_day();
            $dash_title = 'Weekly Dashboard';
            $total_expense /= 4.0;
        } else {
            $date = $this->get_date_by_day_name($type);
            $from_date->setDate($date->year, $date->month, $date->day);
            $to_date->setDate($date->year, $date->month, $date->day);
            $dash_title = ucfirst($this->get_day_full_name($type)).' \'s Dashboard';
            $total_expense /= 30.0;
        }

        //compte new clients
        $thisMonth = new Carbon('first day of this month');
        $thisMonth->setTime(0, 0, 0);

        $newClientsCount = User::where('created_at', '>=', $thisMonth->toDateTimeString())->count();

        //compute active clients
        $activeClients = DB::select("select count(*) as tot from
(select count(*) as total, user_id from sms_log group by user_id) T
where total >= 800");

        //operational expense continued
        $results = DB::select("
SELECT LEFT(msisdn, 5) as prefix, SUM(sms_count) as total
FROM sms_log
WHERE submit_date BETWEEN ? AND ?
GROUP BY prefix
", [$from_date->toDateTimeString(), $to_date->toDateTimeString()]);

        $sms_price = 0.0;
        foreach ($results as $result) {
            $results2 = DB::select("
    SELECT nt.sms_price as sms_price
    FROM networks nt LEFT JOIN user_networks un ON nt.id=un.network_id
    WHERE nt.prefix like ? LIMIT 0,1
", ['%'.$result->prefix.'%']);
            if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                $sms_price += ($results2[0]->sms_price * $result->total);
            } else {
                $results2 = DB::select("SELECT nt.sms_price FROM networks nt WHERE nt.prefix like '%23324%' LIMIT 0,1");
                if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                    $sms_price += ($results2[0]->sms_price * $result->total);
                } else {
                    $sms_price += (0.03 * $result->total);
                }
            }
        }
        $total_expense += $sms_price;

        //compute revenue
        $results = DB::select("
SELECT LEFT(msisdn, 5) as prefix, user_id, SUM(sms_count) as total
FROM sms_log
WHERE submit_date BETWEEN ? AND ?
GROUP BY prefix, user_id
", [$from_date->toDateTimeString(), $to_date->toDateTimeString()]);

        $total_revenue = 0.0;
        foreach ($results as $result) {
            $results2 = DB::select("
    SELECT nt.sms_price as sms_price
    FROM networks nt LEFT JOIN user_networks un ON nt.id=un.network_id
    WHERE nt.prefix like ? and un.user_id=? LIMIT 0,1
", ['%'.$result->prefix.'%', $result->user_id]);
            if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                $total_revenue += ($results2[0]->sms_price * $result->total);;
            } else {
                $results2 = DB::select("SELECT nt.sms_price FROM networks nt WHERE nt.prefix like '%23324%' LIMIT 0,1");
                if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                    $total_revenue += ($results2[0]->sms_price * $result->total);
                } else {
                    $total_revenue += (0.03 * $result->total);;
                }
            }
        }

        //compute profitability
        $total_revenue *= 1.175;
        $profitability = $total_revenue - $total_expense;

        return [
            'dash_title' => $dash_title,
            'reseller' => (int)User::whereRoleId($resellerId)->count(),
            'clients' => (int)User::whereRoleId($clientId)->count(),
            'active_clients' => (int)$activeClients[0]->tot,
            'new_clients' => $newClientsCount,
            'revenue' => round($total_revenue, 2),
            'profitability' => round($profitability, 2),
            'expenses' => round($total_expense, 2)
        ];
    }

    public function get_financial_data(Request $request) {
        $returnvalue = [];
        $returnvalue[] = ['Count', 'Revenue','Cost of goods','Profit'];
        $type = $request['type'];

        //compute operational expenses
        $setting = Setting::whereUserId(auth()->id())->first();
        $setting = !empty($setting) ? $setting : new Setting();
        $electricity = empty($setting->electricity) ? 0 : $setting->electricity;
        $water = empty($setting->water) ? 0 : $setting->water;
        $others = empty($setting->others) ? 0 : $setting->others;

        $total_expense = $electricity + $water + $others;
        $data = [];

        if($type!='month') {
            $total_expense /= 30.0;

            //operational expense continued
            $results = DB::select("
SELECT LEFT(msisdn, 5) as prefix, LEFT(submit_date,10) AS sentAt, SUM(sms_count) as total
FROM sms_log
WHERE (submit_date between DATE_SUB(CURDATE(),INTERVAL 7 DAY)  and DATE_ADD(CURDATE(), INTERVAL 1 DAY))
GROUP BY prefix, sentAt
ORDER BY sentAt
");

            foreach ($results as $result) {
                if(!isset($data["{$result->sentAt}"]))
                    $data["{$result->sentAt}"] = ['cost'=>0, 'revenue'=>0, 'profit'=>0];

                $results2 = DB::select("
    SELECT nt.sms_price as sms_price
    FROM networks nt LEFT JOIN user_networks un ON nt.id=un.network_id
    WHERE nt.prefix like ? LIMIT 0,1
", ['%'.$result->prefix.'%']);
                if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                    $sms_price = ($results2[0]->sms_price * $result->total);
                } else {
                    $results2 = DB::select("SELECT nt.sms_price FROM networks nt WHERE nt.prefix like '%23324%' LIMIT 0,1");
                    if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                        $sms_price = ($results2[0]->sms_price * $result->total);
                    } else {
                        $sms_price = (0.03 * $result->total);
                    }
                }
                $data["{$result->sentAt}"]['cost'] += ($sms_price + $total_expense);
            }


            //compute revenue
            $results = DB::select("
SELECT LEFT(msisdn, 5) as prefix, LEFT(submit_date,10) AS sentAt, user_id, SUM(sms_count) as total
FROM sms_log
WHERE (submit_date between DATE_SUB(CURDATE(),INTERVAL 7 DAY)  and DATE_ADD(CURDATE(), INTERVAL 1 DAY))
GROUP BY prefix, sentAt, user_id
ORDER BY sentAt
");

            foreach ($results as $result) {

                $results2 = DB::select("
    SELECT nt.sms_price as sms_price
    FROM networks nt LEFT JOIN user_networks un ON nt.id=un.network_id
    WHERE nt.prefix like ? and un.user_id=? LIMIT 0,1
", ['%'.$result->prefix.'%', $result->user_id]);
                if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                    $total_revenue = ($results2[0]->sms_price * $result->total);;
                } else {
                    $results2 = DB::select("SELECT nt.sms_price FROM networks nt WHERE nt.prefix like '%23324%' LIMIT 0,1");
                    if(!empty($results2) && isset($results2[0]) && !empty($results2[0])) {
                        $total_revenue = ($results2[0]->sms_price * $result->total);
                    } else {
                        $total_revenue = (0.03 * $result->total);;
                    }
                }
                $data["{$result->sentAt}"]['revenue'] += $total_revenue;
            }

            //compute profitability
            foreach ($data as &$datum) {
                $datum['revenue'] *= 1.175;
                $datum['profit'] = $datum['revenue'] - $datum['cost'];
            }

            foreach ($data as $key => $tempArray) {
                $returnvalue[] = [
                    $this->get_day_name(Carbon::parse($key)),
                    isset($tempArray['revenue']) ? (int)$tempArray['revenue'] : 0,
                    isset($tempArray['cost']) ? (int)$tempArray['cost'] : 0,
                    isset($tempArray['profit']) ? (int)$tempArray['profit'] : 0,
                ];
            }
        } else {
            $request['type'] = '';
            return $this->get_financial_data($request);
        }

        return $returnvalue;
    }

    private function get_weekly_start_day() {
        $date = Carbon::now()->setTime(0, 0, 0);
        while (!$date->isMonday()) {
            $date = $date->addDays(-1);
        }
        return $date;
    }

    private function get_weekly_end_day() {
        $date = Carbon::now()->setTime(23, 59, 59);
        while (!$date->isSunday()) {
            $date = $date->addDays(1);
        }
        return $date;
    }

    public function get_traffic_performance_data(Request $request) {
        $returnvalue = [];
        $returnvalue[] = ['Count', 'Delivered', 'Undelivered','Acknowledged','Expired'];

        if($request['type']!='month') {
            //process for day of week
            $data = DB::select("select
 LEFT(submit_date,10) AS sentAt,
 sms_log.`status` as stat,
 count(*) as t
from sms_log left join users ON users.id=sms_log.user_id
where (submit_date between DATE_SUB(CURDATE(),INTERVAL 7 DAY)  and DATE_ADD(CURDATE(), INTERVAL 1 DAY))
  and `originated`=?
GROUP BY sentAt,stat", ['mtnuser']);

            $main = [];
            $cdate = '';
            $tempArray = [];
            foreach ($data as $dat) {
                $status = $dat->stat;
                $total = $dat->t;
                $tempArray["{$status}"] = $total;
                if($cdate != $dat->sentAt) {
                    if(!empty($cdate)) {
                        $main["$cdate"] = $tempArray;
                        $tempArray = [];
                    }
                    $cdate = $dat->sentAt;
                }
            }
            $main["$cdate"] = $tempArray;

            foreach ($main as $key => $tempArray) {
                $returnvalue[] = [
                    $this->get_day_name(Carbon::parse($key)),
                    isset($tempArray['DELIVRD']) ? (int)$tempArray['DELIVRD'] : 0,
                    isset($tempArray['UNDELIV']) ? (int)$tempArray['UNDELIV'] : 0,
                    isset($tempArray['ACK']) ? (int)$tempArray['ACK'] : 0,
                    isset($tempArray['EXPIRED']) ? (int)$tempArray['EXPIRED'] : 0
                ];
            }
        } else {
            //process for month
            $now = Carbon::now();
            for($i=1; $i<=5; $i++) {
                $to_date = SmsTools::get_end_of_week($now->year, $now->month, $i);
                $from_date = SmsTools::get_start_of_week($to_date);

                $data = DB::select('select sms_log.`status`, count(*) as total from `sms_log` join users ON users.id=sms_log.user_id where (`submit_date` between ? and ?) and `originated`=? GROUP BY sms_log.`status`', [
                    $from_date->toDateTimeString(),
                    $to_date->toDateTimeString(),
                    'mtnuser'
                ]);
                $tempArray = [];
                foreach ($data as $dat) {
                    $status = $dat->status;
                    $total = $dat->total;
                    $tempArray["{$status}"] = $total;
                }
                $returnvalue[] = [
                    'Week '.$i,
                    isset($tempArray['DELIVRD']) ? (int)$tempArray['DELIVRD'] : 0,
                    isset($tempArray['UNDELIV']) ? (int)$tempArray['UNDELIV'] : 0,
                    isset($tempArray['ACK/']) ? (int)$tempArray['ACK/'] : 0,
                    isset($tempArray['EXPIRED']) ? (int)$tempArray['EXPIRED'] : 0
                ];
            }
        }

        return $returnvalue;
    }

    private function get_date_by_day_name($day_name) {
        $now = Carbon::now()->addDays(-1);
        switch ($day_name) {
            case 'sun':
                while(!$now->isSunday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            case 'mon':
                while(!$now->isMonday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            case 'tue':
                while(!$now->isTuesday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            case 'wed':
                while(!$now->isWednesday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            case 'thu':
                while(!$now->isThursday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            case 'fri':
                while(!$now->isFriday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            case 'sat':
                while(!$now->isSaturday()) {
                    $now = $now->addDays(-1);
                }
                return $now;
            default:
                return $now->addDay()->copy();
        }
    }
    private function get_day_name(Carbon $date) {
        if($date->isMonday()) {
            return "Mon";
        }
        if($date->isTuesday()) {
            return "Tue";
        }
        if($date->isWednesday()) {
            return "Wed";
        }
        if($date->isThursday()) {
            return "Thu";
        }
        if($date->isFriday()) {
            return "Fri";
        }
        if($date->isSaturday()) {
            return "Sat";
        }
        if($date->isSunday()) {
            return "Sun";
        }
        return "Unknown";
    }

    private function get_day_full_name($day)
    {
        $day = strtolower($day);

        switch ($day)
        {
            case 'sun':
                return 'Sunday';
                break;
            case 'mon':
                return 'Monday';
                break;
            case 'tue':
                return 'Tuesday';
                break;
            case 'wed':
                return 'Wednesday';
                break;
            case 'thu':
                return 'Thursday';
                break;
            case 'fri':
                return 'Friday';
                break;
            case 'sat':
                return 'Saturday';
                break;
            default:
                return 'N / A';
                break;
        }
    }

    public function get_user_picture($id) {
        $record = User::findOrFail($id);
        if(empty($record->picture)) {
            //Image file is stored under project/public/global/portrait/5.jpg
            $file= public_path('global/portraits/17.jpg');

            $headers = array(
                'Content-Type: image/jpeg',
            );

            return Response::download($file, '17.jpg', $headers);
        }
        $pic = Image::make($record->picture->picture);
        $response = Response::make($pic->encode('jpeg'));
        $response->header('Content-Type', 'image/jpeg');
        return $response;
    }

    public function goto_main_route() {
        return redirect()->route('admin.app');
    }

    public function logout() {
        $user = Auth::user();

        AuditTrail::create([
            'user_id' => $user->id,
            'username' => $user->username,
            'date' => \Carbon\Carbon::now()->toDateTimeString(),
            'activity' => $user->name.' logged out '
        ]);

        Auth::logout();
        return redirect()->route('admin.login');
    }
}