<?php

namespace Modules\Reporting\Http\Controllers;

use Modules\Platform\Companies\Datatables\Scope\CurrentCompanyScope;
use Modules\Platform\Core\Http\Controllers\AppBaseController;
use Modules\Platform\Core\Helper\SettingsHelper;
use Modules\Platform\Core\Helper\UserHelper;
use Modules\Platform\User\Entities\User;
use Modules\Reporting\Entities\OutboundReport;
use App\Models\CallAiAnalysis;
use Modules\Reporting\Http\Controllers\Concerns\RendersAnalysisAction;
use Carbon\Carbon;
use Carbon\Carbonite;
use Carbon\CarbonPeriod;
use Carbon\CarbonInterval;
use App\Charts\CallsOverallChart;
use App\CDR;
use App\Charts\CallDist;
use Illuminate\Http\Request;
use DataTables;
use DB;
use Illuminate\Support\Facades\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Illuminate\Support\Facades\Auth;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use ZipArchive;

class OutboundController extends AppBaseController
{
    use RendersAnalysisAction;

    protected $moduleName = 'reporting';
    protected $SettingsHelper;
    protected $UserHelper;
    public function __construct()
    {
        parent::__construct();
        $this->SettingsHelper = new SettingsHelper();
        $this->UserHelper = new UserHelper();
    }

    public function outbound(Request $request){
        $conds                      = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        // $u = $this->outboundDatatable($request,$conds);
        if ($request->ajax() && empty($request->action)) {
            return $this->outboundDatatable($request,$conds);
        }elseif(!empty($request->action && $request->action == 'excel')){
            // Export should go here
            // dd($this->outboundDatatable($request));
        }
        
        // Same defensive read as the inbound report: the FreePBX `asterisk`
        // database is not present/readable on every box.
        try {
            $did_options = DB::table('asterisk.incoming')
                ->where('extension', 'like', '002022%')
                ->pluck('extension', 'extension')
                ->toArray();
        } catch (\Throwable $e) {
            $did_options = [];
        }

        $did_options = [
        'dids' => $did_options
        ];

        $agent_names = $this->UserHelper->getCompanyUsers();
        $agents_options = [
            'Agents' => $agent_names->pluck('name','id')->toArray()
        ];
        $agent_names = $agent_names->pluck('id','name')->toArray();

        $view = view('reporting::outbound');
        $filter_area                            = view('reporting::widgets.filter_area');
        $filter_area->with('conds',$conds);
        $filter_area->with('did_options',$did_options);
        $filter_area->with('did_ext',$request->did_ext);
        $filter_area->with('agents_options',$agents_options);
        $filter_area->with('user_ext',$request->user_ext);
        $filter_area->with('submit_route',route('reporting.ccoutbound'));
        
        $view->with('filter_area',$filter_area);
        $view->with('conds',$conds);
        $view->with('did_ext',$request->did_ext);
        $view->with('user_ext',$request->user_ext);
        return $view;
    }

    public function excelReport(Request $request){
        $conds                      = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        $user          = Auth::user();

        // Data source: outbound_reports — the same table and query logic as the
        // Outbound Report datatable, instead of asteriskcdrdb.cdr.
        $data = OutboundReport::whereBetween('call_time', [$conds['from'], $conds['to']]);

        // Scope to users visible to the current authenticated user (same as the report).
        $companyUsers = $this->UserHelper->getCompanyUsers();
        if ($user->access_to_all_entity != '1' && $user->groups->isNotEmpty()) {
            $companyUsers = User::whereHas('groups', function ($q) {
                $q->whereIn('groups.id', auth()->user()->accessibleQueueIds());
            })
            ->whereDoesntHave('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->visibleInReports()
            ->get();
        }
        $companyUserIds = $companyUsers->pluck('id')->map(fn($id) => (string) $id)->toArray();
        $data = $data->whereIn('agent_ext', $companyUserIds);

        if (!empty($request->did_ext)) {
            $data = $data->where('src', $request->did_ext);
        }

        if (!empty($request->user_ext)) {
            $data = $data->where('agent_ext', $request->user_ext);
        }

        // Same search semantics as the report (numeric search → called_number).
        if (!empty($request->search) && is_numeric($request->search)) {
            $data = $data->where('called_number', 'LIKE', '%' . trim($request->search) . '%');
        }

        if ($user->roles[0]->name == 'user' && $user->access_to_all_entity != '1') {
            $data = $data->where('agent_ext', (string) $user->id);
        }

        $data = $data->orderBy('call_time', 'DESC')->get();

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=users.xlsx',
        ];

        $callback = function () use ($data) {
            $excel = new Spreadsheet();

            $excel->getActiveSheet()->setCellValue('A1', 'Time');
            $excel->getActiveSheet()->setCellValue('B1', 'Ext.');
            $excel->getActiveSheet()->setCellValue('C1', 'Agent Name');
            $excel->getActiveSheet()->setCellValue('D1', 'Number');
            $excel->getActiveSheet()->setCellValue('E1', 'T. Duration');
            $excel->getActiveSheet()->setCellValue('F1', 'TalkTime');
            $excel->getActiveSheet()->setCellValue('G1', 'RingTime');
            $excel->getActiveSheet()->setCellValue('H1', 'Disp.');
            $excel->getActiveSheet()->setCellValue('I1', 'Dialed Thru');

            $data = $data->map(function ($data) {
                return [
                    date('Y-m-d H:i:s',strtotime($data->call_time)),
                    $data->agent_ext,
                    $data->agent_name,
                    $data->called_number,
                    gmdate('H:i:s', $data->duration),
                    gmdate('H:i:s', $data->talktime),
                    gmdate('H:i:s', $data->ringtime),
                    $data->disposition,
                    $data->src,
                ];
            });
            $sheet = $excel->getActiveSheet();
            // Apply header cell styles
            $headerStyle = [
                'font' => ['bold' => true],
            ];
            $sheet->getStyle('A1:J1')->applyFromArray($headerStyle);

            $sheet->fromArray($data->toArray(), null, 'A2');
            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };
        return Response::stream($callback, 200, $headers);
    }

    public function outboundDatatable($request, $conds)
    {
        $user          = Auth::user();
        $canSeeSurvey  = $user->hasPermissionTo('reporting.survey_outbound');

        // Build base query from the outbound_reports table
        $data = OutboundReport::whereBetween('call_time', [$conds['from'], $conds['to']]);

        // Scope to users visible to the current authenticated user
        $companyUsers = $this->UserHelper->getCompanyUsers();
        if ($user->access_to_all_entity != '1' && $user->groups->isNotEmpty()) {
            $companyUsers = User::whereHas('groups', function ($q) {
                $q->whereIn('groups.id', auth()->user()->accessibleQueueIds());
            })
            ->whereDoesntHave('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->visibleInReports()
            ->get();
        }
        $companyUserIds = $companyUsers->pluck('id')->map(fn($id) => (string) $id)->toArray();
        $data = $data->whereIn('agent_ext', $companyUserIds);

        if (!empty($request->did_ext)) {
            $data = $data->where('src', $request->did_ext);
        }

        if (!empty($request->user_ext)) {
            $data = $data->where('agent_ext', $request->user_ext);
        }

        if (!empty($request->search['value']) && is_numeric($request->search['value'])) {
            $data = $data->where('called_number', 'LIKE', '%' . trim($request->search['value']) . '%');
        }

        if ($user->roles[0]->name == 'user' && $user->access_to_all_entity != '1') {
            $data = $data->where('agent_ext', (string) $user->id);
        }

        // The AI analysis state for each row, resolved in the same statement
        // (correlated subquery) instead of one query per row.
        $data = $data->addSelect([
            'ai_analysis_status' => CallAiAnalysis::query()
                ->select('status')
                ->whereColumn('call_ai_analyses.linkedid', 'outbound_reports.linkedid')
                ->orderByDesc('id')
                ->limit(1),
        ]);

        return Datatables::of($data)
            ->addIndexColumn()
            // Internal alias only: do not ship it to the browser.
            ->removeColumn('ai_analysis_status')
            ->addColumn('action', function ($record) {
                $btn = '';

                if (Auth::user()->hasPermissionTo('reporting.listen')) {
                    $display_url = route('reporting.listen.page', ['id' => $record->linkedid]);

                    if (!$record->done) {
                        $btn = '<a href="' . $display_url . '" onclick="outDone(event,' . $record->linkedid . ')" target="_blank" class="edit btn btn-info btn-sm" style="color:white">Listen</a>';
                    } else {
                        $btn = '<a href="' . $display_url . '" target="_blank" class="edit btn btn-success btn-sm" style="color:white">Listen</a>';
                    }
                }

                // AI summary: Submit while there is no analysis, the stored
                // analysis once it exists (a link styled as a button through to
                // the analysis page).
                $analysis = $this->analysisActionHtml((string) $record->linkedid, $record->ai_analysis_status ?? null, 'outbound');
                $action = trim($btn . ' ' . $analysis);

                return $action !== '' ? $action : 'Not Avail';
            })
            ->editColumn('call_time', function ($row) {
                return date('Y-m-d h:i:s A', strtotime($row->call_time));
            })
            ->editColumn('src', function ($row) {
                return $row->src ? str_replace('002022', '022', $row->src) : '';
            })
            ->rawColumns(['action'])
            ->make(true);
    }
    
    function outDone($id)
    {
        try {
            // Mark in the reporting table (new canonical store)
            OutboundReport::where('linkedid', $id)->update(['done' => 1]);

            // Also update the CDR source of truth for backward compatibility
            // (API controller and other consumers still read userfield)
            DB::update('UPDATE asteriskcdrdb.cdr SET userfield = 1 WHERE linkedid = ?', [$id]);

            return response()->json(['success' => true]);
        } catch (\Throwable $th) {
            throw $th;
        }
    }

    function outDist(Request $request) {
        $conds                      = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        $filter = [
            'conds' => $conds,
        ];
        $data = $this->getOutDistribution($filter);
        // return $data;
        $view = view('reporting::outDist');
        // tables
            $monthsDatatables = Datatables::of($data['months_table'])
                ->addIndexColumn()
                ->addColumn('offered', function($row){
                    return $row['answord'] + $row['busy'] + $row['failed'] + $row['unans'];
                })
                ->addColumn('rate', function($row){
                    return round($row['answord']/($row['answord'] + $row['busy'] + $row['failed'] + $row['unans'])*100,1) . ' %';
                })
            ->make(true);
            $monthsDatatablesJson = json_encode($monthsDatatables->getData()->data);
            $view->with('monthsDatatablesJson',$monthsDatatablesJson);
            // 
            $daysDatatables = Datatables::of($data['days_table'])
                ->addIndexColumn()
                ->addColumn('offered', function($row){
                    return $row['answord'] + $row['busy'] + $row['failed'] + $row['unans'];
                })
                ->addColumn('rate', function($row){
                    return round($row['answord']/($row['answord'] + $row['busy'] + $row['failed'] + $row['unans'])*100,1) . ' %';
                })
            ->make(true);
            $daysDatatablesJson = json_encode($daysDatatables->getData()->data);
            $view->with('daysDatatablesJson',$daysDatatablesJson);
            // 
            $hourDatatables = Datatables::of($data['hourse_table'])
                ->addIndexColumn()
                ->addColumn('offered', function($row){
                    return $row['answord'] + $row['busy'] + $row['failed'] + $row['unans'];
                })
                ->addColumn('rate', function($row){
                    return round($row['answord']/($row['answord'] + $row['busy'] + $row['failed'] + $row['unans'])*100,1) . ' %';
                })
            ->make(true);
            $hourDatatablesJson = json_encode($hourDatatables->getData()->data);
            $view->with('hourDatatablesJson',$hourDatatablesJson);
        // /////////////////////////////////////////////////////
        
        // charts
            $view->with('MonthDist', new CallDist([
                'type' => 'out',
                'data' => $data['months_table'],
            ]));
            $view->with('dayDist', new CallDist([
                'type' => 'out',
                'data' => $data['days_table'],
            ]));
            $view->with('hourseDist', new CallDist([
                'type' => 'out',
                'data' => $data['hourse_table'],
            ]));
        ////////////////////////////////////////////////////


        $filter_area = view('reporting::widgets.filter_area');
        $filter_area->with('conds',$conds);
        $filter_area->with('submit_route',route('reporting.ccoutDist'));

        $view->with('filter_area',$filter_area);
        $view->with('data',$data);
        // $view->with('queues',$queues);
        $view->with('conds',$conds);
        $view->with('did_ext',$request->did_ext);
        return $view;
    }

    public function getOutDistribution($data = array()){
        $conds = $data['conds'];
        $user           = \Auth::user();

        // --- Same scope logic as the Outbound Calls report (web + API) ---
        $company_users = $this->UserHelper->getCompanyUsers();
        if ($user->access_to_all_entity != '1' && $user->groups->isNotEmpty()) {
            $company_users = User::whereHas('groups', function ($q) {
                $q->whereIn('groups.id', auth()->user()->accessibleQueueIds());
            })
            ->whereDoesntHave('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->visibleInReports()
            ->get();
        }
        $company_users = $company_users->pluck('id')->map(fn($id) => (string) $id)->toArray();

        // Data source: outbound_reports (one row per call) — same table and
        // query logic as the Outbound Calls report, instead of asteriskcdrdb.cdr.
        // Column aliases keep the historical names so the aggregation loop below
        // is unchanged: calldate ← call_time, cnum ← agent_ext.
        $query = DB::table('outbound_reports')
            ->select(
                'call_time as calldate',
                'disposition',
                'agent_ext as cnum'
            )
            ->whereBetween('call_time', [$conds['from'], $conds['to']])
            ->whereIn('agent_ext', $company_users)

            // Combined category bucket also stays fixed across implementations.
            ->whereIn('disposition', ['ANSWERED', 'BUSY', 'FAILED', 'NO ANSWER']);

        // Role-based restriction: same as the Outbound Calls report.
        if ($user->roles[0]->name === 'user' && $user->access_to_all_entity != '1') {
            $query->where('agent_ext', (string) $user->id);
        }

		$return_array = [
            'days_processed' => [],
            'hours_processed' => [],
            'months_processed' => [],
            'months_table' => [],
            'days_table' => [],
            'hourse_table' => [],
            'answered_by_month' => [],
            'answered_by_day' => [],
            'answered_by_hour' => [],
            'unans_by_month' => [],
            'unans_by_day' => [],
            'unans_by_hour' => [],
            'busy_by_month' => [],
            'busy_by_day' => [],
            'busy_by_hour' => [],
            'failed_by_month' => [],
            'failed_by_day' => [],
            'failed_by_hour' => [],
            'answered' => 0,
            'unanswered' => 0,
        ];
        $results = $query->orderBy('call_time', 'ASC')->get();
		if ($results->isNotEmpty()) {
            $countMonths = 0;
            $pervMonth = NULL;
            $countDays = 0;
            $pervDay = NULL;
			foreach($results as $row){
				// get days_processed
                $time_exploded = explode(" ", $row->calldate);
                $one_day = trim($time_exploded[0]);
                if (!in_array($one_day, $return_array['days_processed'])) {
                    if ($countDays != 0) {
                        @$return_array['days_table'][] = [
                            'day' => $pervDay,
                            'label' => $pervDay,
                            'answord' => $return_array['answered_by_day'][$pervDay] ?? 0,
                            'unans' => $return_array['unans_by_day'][$pervDay] ?? 0,
                            'failed' => $return_array['failed_by_day'][$pervDay] ?? 0,
                            'busy' => $return_array['busy_by_day'][$pervDay] ?? 0,
                        ];
                    }
                    $return_array['days_processed'][] = $one_day;
                    $pervDay = $one_day;
                    $countDays = $countDays + 1;
                }
                
				// get hours_processed
                $hour_exploded = explode(":", @$time_exploded[1]);
                $one_hour = $hour_exploded[0];
                if (!in_array($one_hour, $return_array['hours_processed'])) {
                    $return_array['hours_processed'][] = $one_hour;
                }

                // get months_processed
                $month_year_label = Carbon::parse($row->calldate)->format('m-Y');
                if (!in_array($month_year_label, $return_array['months_processed'])) {
                    if ($countMonths != 0) {
                        @$return_array['months_table'][] = [
                            'month' => $pervMonth,
                            'label' => $pervMonth,
                            'answord' => $return_array['answered_by_month'][$pervMonth] ?? 0,
                            'failed' => $return_array['failed_by_month'][$pervMonth] ?? 0,
                            'busy' => $return_array['busy_by_month'][$pervMonth] ?? 0,
                            'unans' => $return_array['unans_by_month'][$pervMonth] ?? 0,
                        ];
                    }
                    $return_array['months_processed'][] = $month_year_label;
                    $pervMonth = $month_year_label;
                    $countMonths = $countMonths + 1;
                }
                // Process events
                if ($row->disposition == 'FAILED') {
                    @$return_array['unanswered']++;
                    @$return_array['failed_by_day'][$one_day]++;
                    @$return_array['failed_by_hour'][$one_hour]++;
                    // @$return_array['failed_by_dayname'][date('w', strtotime($row->calldate))]++;
                    @$return_array['failed_by_month'][$month_year_label]++;
                }elseif ($row->disposition == 'BUSY') {
                    @$return_array['unanswered']++;
                    @$return_array['busy_by_day'][$one_day]++;
                    @$return_array['busy_by_hour'][$one_hour]++;
                    // @$return_array['busy_by_dayname'][date('w', strtotime($row->calldate))]++;
                    @$return_array['busy_by_month'][$month_year_label]++;
                }elseif ($row->disposition == 'NO ANSWER') {
                    @$return_array['unanswered']++;
                    @$return_array['unans_by_day'][$one_day]++;
                    @$return_array['unans_by_hour'][$one_hour]++;
                    // @$return_array['unans_by_dayname'][date('w', strtotime($row->calldate))]++;
                    @$return_array['unans_by_month'][$month_year_label]++;
                }elseif ($row->disposition == 'ANSWERED') {
					@$return_array['answered']++;
                    @$return_array['answered_by_day'][$one_day]++;
                    @$return_array['answered_by_hour'][$one_hour]++;
                    // @$return_array['answered_by_dayname'][date('w', strtotime($row->calldate))]++;
                    @$return_array['answered_by_month'][$month_year_label]++;

                    // @$return_array['total_time_by_day'][$one_day] += $row->data2;
                    // @$return_array['total_hold_by_day'][$one_day] += $row->data1;

                    // @$return_array['total_time_by_dayname'][date('w', strtotime($row->calldate))] += $row->data2;
                    // @$return_array['total_hold_by_dayname'][date('w', strtotime($row->calldate))] += $row->data1;

                    // @$return_array['total_time_by_hour'][$one_hour] += $row->data2;
                    // @$return_array['total_hold_by_hour'][$one_hour] += $row->data1;

                    // @$return_array['total_time_by_month'][$month_year_label] += $row->data2;
                    // @$return_array['total_hold_by_month'][$month_year_label] += $row->data1;
				}

                
			}
            @$return_array['months_table'][] = [
                'month' => $month_year_label,
                'label' => $month_year_label,
                'answord' => $return_array['answered_by_month'][$pervMonth] ?? 0,
                'failed' => $return_array['failed_by_month'][$pervMonth] ?? 0,
                'busy' => $return_array['busy_by_month'][$pervMonth] ?? 0,
                'unans' => $return_array['unans_by_month'][$pervMonth] ?? 0,
            ];
            @$return_array['days_table'][] = [
                'day' => $one_day,
                'label' => $one_day,
                'answord' => $return_array['answered_by_day'][$one_day] ?? 0,
                'busy' => $return_array['busy_by_day'][$one_day] ?? 0,
                'failed' => $return_array['failed_by_day'][$one_day] ?? 0,
                'unans' => $return_array['unans_by_day'][$one_day] ?? 0,
            ];
            // Sort hours numerically (earliest -> latest) BEFORE building the
            // table so the chart and the data table share the exact same order.
            sort($return_array['hours_processed'], SORT_NUMERIC);

            foreach($return_array['hours_processed'] as $hour){
                @$return_array['hourse_table'][] = [
                    'hourse' => $hour,
                    'label' => $hour,
                    'answord' => $return_array['answered_by_hour'][$hour] ?? 0,
                    'busy' => $return_array['busy_by_hour'][$hour] ?? 0,
                    'failed' => $return_array['failed_by_hour'][$hour] ?? 0,
                    'unans' => $return_array['unans_by_hour'][$hour] ?? 0,
                ];
            }
			$answered = $return_array['answered'];
            $unanswered = $return_array['unanswered'];

            if ($unanswered != 0 && $answered != 0) {
                $overall_percentage = ($unanswered / $answered) * 100;
            } elseif ($unanswered == 0) {
                $overall_percentage = 100;
            } else {
                $overall_percentage = 0;
            }
            $overall_percentage = number_format($overall_percentage, 2);
            $total_calls = $return_array['answered'] + $return_array['unanswered'];

		} else {
			// No rows returned
			$return_array['answered'] 			= 0;
			$return_array['unanswered'] 		= 0;
		}
		return $return_array;
	}
}