<?php

namespace Modules\Reporting\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Response;
use DataTables;
use Modules\Platform\Core\Http\Controllers\AppBaseController;
use Modules\Platform\Core\Helper\CompanySettings;
use Modules\Platform\Core\Helper\SettingsHelper;
use Modules\Platform\User\Entities\Group;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class SurveyReportController extends AppBaseController
{
    public function index(Request $request)
    {
        if ($request->ajax()) {
            return $this->responsesDataTable($request);
        }

        $report = $this->buildReport($request, false);
        $filterArea = view('reporting::widgets.filter_area');
        $filterArea->with('conds', $report['conditions']);
        $filterArea->with('agents_options', ['Agents' => $report['agents_options']]);
        $filterArea->with('user_ext', $report['filters']['agent']);
        $filterArea->with('submit_route', route('reporting.survey-report'));
        $report['filter_area'] = $filterArea;

        return view('reporting::survey_report', $report);
    }

    public function export(Request $request)
    {
        $report = $this->buildReport($request);
        $spreadsheet = new Spreadsheet();

        $summarySheet = $spreadsheet->getActiveSheet();
        $summarySheet->setTitle('Summary');
        $summarySheet->fromArray([
            ['Survey Report'],
            ['From', $report['conditions']['from'], 'To', $report['conditions']['to']],
            ['Surveys', $report['total_surveys'], 'Answers', $report['total_answers']],
            ['Inbound surveys', $report['direction_totals']['inbound'], 'Outbound surveys', $report['direction_totals']['outbound']],
            [],
        ], null, 'A1');

        $summaryHeadings = [];
        $summaryModes = [];
        foreach ($report['questions'] as $question) {
            $summaryHeadings[] = 'Question ' . $question['number'] . ' Mode';
            $summaryModes[] = $question['mode'] ?? '';
        }
        $summarySheet->fromArray([$summaryHeadings], null, 'A6');
        $summarySheet->fromArray([$summaryModes], null, 'A7');

        $agentSheet = $spreadsheet->createSheet();
        $agentSheet->setTitle('By Agent');
        $agentHeaders = ['Agent', 'Survey Calls', 'Answers'];
        foreach ($report['questions'] as $question) {
            $agentHeaders[] = 'Q' . $question['number'] . ' Mode';
        }
        $agentSheet->fromArray([$agentHeaders], null, 'A1');

        $agentRows = [];
        foreach ($report['agents'] as $agent) {
            $row = [$agent['name'], $agent['survey_count'], $agent['answer_count']];
            foreach ($report['questions'] as $question) {
                $row[] = $agent['modes'][$question['number']] ?? '';
            }
            $agentRows[] = $row;
        }
        if ($agentRows) {
            $agentSheet->fromArray($agentRows, null, 'A2');
        }

        $responseSheet = $spreadsheet->createSheet();
        $responseSheet->setTitle('Responses');
        $responseHeaders = ['Call Date', 'Agent', 'Phone Number', 'Direction'];
        foreach ($report['questions'] as $question) {
            $responseHeaders[] = 'Q' . $question['number'];
        }
        $responseSheet->fromArray([$responseHeaders], null, 'A1');

        $responseRows = [];
        foreach ($report['responses'] as $response) {
            $row = [
                $response['call_date'],
                $response['agent'],
                $response['phone_number'],
                ucfirst(strtolower($response['direction'])),
            ];
            foreach ($report['questions'] as $question) {
                $row[] = $response['answers'][$question['number']] ?? '';
            }
            $responseRows[] = $row;
        }
        if ($responseRows) {
            $responseSheet->fromArray($responseRows, null, 'A2');
        }

        foreach ($spreadsheet->getAllSheets() as $sheet) {
            $highestColumn = Coordinate::columnIndexFromString($sheet->getHighestColumn());
            foreach (range(1, max(1, $highestColumn)) as $column) {
                $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
            }
            $sheet->freezePane('A2');
        }

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

        return Response::stream(function () use ($spreadsheet) {
            IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        }, 200, $headers);
    }

    public function exportSummary(Request $request)
    {
        $report = $this->buildReport($request);
        $headings = [];
        $modes = [];
        foreach ($report['questions'] as $question) {
            $headings[] = 'Question ' . $question['number'] . ' Mode';
            $modes[] = $question['mode'] ?? '';
        }

        return $this->exportTable(
            'survey-overall-summary.xlsx',
            $headings,
            [$modes]
        );
    }

    public function exportAgents(Request $request)
    {
        $report = $this->buildReport($request);
        $headings = ['Agent', 'Survey Calls', 'Recorded Answers'];
        foreach ($report['questions'] as $question) {
            $headings[] = 'Q' . $question['number'] . ' Mode';
        }

        $rows = [];
        foreach ($report['agents'] as $agent) {
            $row = [$agent['name'], $agent['survey_count'], $agent['answer_count']];
            foreach ($report['questions'] as $question) {
                $row[] = $agent['modes'][$question['number']] ?? '';
            }
            $rows[] = $row;
        }

        return $this->exportTable('survey-results-by-agent.xlsx', $headings, $rows);
    }

    public function exportResponses(Request $request)
    {
        $report = $this->buildReport($request);
        $search = trim((string) $request->input('search', ''));
        if ($search !== '') {
            $report['responses'] = array_values(array_filter(
                $report['responses'],
                fn($response) => stripos((string) ($response['phone_number'] ?? ''), $search) !== false
            ));
        }

        $headings = ['Call Date', 'Agent', 'Phone Number', 'Direction'];
        foreach ($report['questions'] as $question) {
            $headings[] = 'Q' . $question['number'];
        }

        $rows = [];
        foreach ($report['responses'] as $response) {
            $row = [
                $response['call_date'],
                $response['agent'],
                $response['phone_number'],
                ucfirst(strtolower($response['direction'])),
            ];
            foreach ($report['questions'] as $question) {
                $row[] = $response['answers'][$question['number']] ?? '';
            }
            $rows[] = $row;
        }

        return $this->exportTable('survey-response-details.xlsx', $headings, $rows);
    }

    private function exportTable(string $filename, array $headings, array $rows)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray([$headings], null, 'A1');
        if ($rows) {
            $sheet->fromArray($rows, null, 'A2');
        }
        $sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')
            ->getFont()->setBold(true);
        $sheet->freezePane('A2');
        $sheet->setAutoFilter('A1:' . $sheet->getHighestColumn() . max(1, $sheet->getHighestRow()));

        $highestColumn = Coordinate::columnIndexFromString($sheet->getHighestColumn());
        foreach (range(1, max(1, $highestColumn)) as $column) {
            $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        }

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

        return Response::stream(function () use ($spreadsheet) {
            IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        }, 200, $headers);
    }

    private function responsesDataTable(Request $request)
    {
        $conditions = $this->dateConditions($request);
        $questionCount = (int) CompanySettings::get('SURVEY_QUESTIONS_COUNT', 1);
        $questionCount = max(1, min(5, $questionCount));
        $user = Auth::user();

        $query = DB::table('call_logs')
            ->leftJoin('users', 'users.id', '=', 'call_logs.user_id')
            ->whereBetween('call_logs.call_date', [$conditions['from'], $conditions['to']])
            ->whereIn('call_logs.direction', ['inbound', 'outbound'])
            ->whereNotNull('call_logs.survey')
            ->select([
                'call_logs.id',
                'call_logs.linkedid',
                'call_logs.call_date',
                'call_logs.phone_number',
                'call_logs.direction',
                'call_logs.user_id',
                'call_logs.survey',
                'users.name as agent_name',
            ]);

        $this->applyAccessScope($query, $user);

        $agentFilter = (string) $request->input('user_ext', '');
        if ($agentFilter !== '' && ctype_digit($agentFilter)) {
            $query->where('call_logs.user_id', (int) $agentFilter);
        }

        $query->where(function ($answers) use ($questionCount) {
            for ($question = 1; $question <= $questionCount; $question++) {
                $answers->orWhereRaw('TRIM(' . $this->surveyAnswerSqlExpression($question) . ") <> ''");
            }
        });

        $search = trim((string) $request->input('search.value', ''));
        if ($search !== '') {
            $query->where('call_logs.phone_number', 'LIKE', '%' . $search . '%');
        }

        $dataTable = DataTables::of($query)
            ->addColumn('agent', fn($row) => $row->agent_name ?: 'Unassigned')
            ->orderColumn('call_logs.call_date', function ($query, $order) {
                $query->orderBy('call_logs.call_date', $order)
                    ->orderBy('call_logs.id', $order);
            });

        for ($question = 1; $question <= $questionCount; $question++) {
            $dataTable->addColumn('q' . $question, function ($row) use ($question) {
                return $this->surveyAnswer($row->survey, $question);
            });
        }

        return $dataTable
            ->addColumn('recording', function ($row) {
                if (!Auth::user()->hasPermissionTo('reporting.listen')) {
                    return __('Not Available');
                }

                $url = route('reporting.listen.page', ['id' => $row->linkedid]);
                return '<a class="btn btn-info btn-sm" href="' . e($url) . '" target="_blank" rel="noopener">'
                    . '<i class="fa fa-play" aria-hidden="true"></i> ' . e(__('Play Recording')) . '</a>';
            })
            ->rawColumns(['recording'])
            ->make(true);
    }

    private function buildReport(Request $request, bool $includeResponses = true): array
    {
        $conditions = $this->dateConditions($request);
        $questionCount = (int) CompanySettings::get('SURVEY_QUESTIONS_COUNT', 1);
        $questionCount = max(1, min(5, $questionCount));
        $user = Auth::user();

        $query = DB::table('call_logs')
            ->leftJoin('users', 'users.id', '=', 'call_logs.user_id')
            ->whereBetween('call_logs.call_date', [$conditions['from'], $conditions['to']])
            ->whereIn('call_logs.direction', ['inbound', 'outbound'])
            ->whereNotNull('call_logs.survey')
            ->select([
                'call_logs.id',
                'call_logs.linkedid',
                'call_logs.call_date',
                'call_logs.phone_number',
                'call_logs.direction',
                'call_logs.user_id',
                'call_logs.survey',
                'users.name as agent_name',
            ]);

        // $this->applyAccessScope($query, $user);

        $rows = $query->orderByDesc('call_logs.call_date')
            ->orderByDesc('call_logs.id')
            ->get();
        $agentsOptions = [];
        foreach ($rows as $row) {
            $agentKey = $row->user_id === null ? 'unassigned' : (string) $row->user_id;
            $agentsOptions[$agentKey] = $row->agent_name ?: 'Unassigned';
        }
        asort($agentsOptions, SORT_NATURAL | SORT_FLAG_CASE);

        $agentFilter = (string) $request->input('user_ext', '');
        if ($agentFilter !== '' && ctype_digit($agentFilter)) {
            $rows = $rows->where('user_id', (int) $agentFilter)->values();
        } else {
            $agentFilter = '';
        }

        $questionChoices = array_fill(1, $questionCount, []);
        $agentTotals = [];
        $responses = [];
        $totalSurveys = 0;
        $totalAnswers = 0;
        $directionTotals = ['inbound' => 0, 'outbound' => 0];

        foreach ($rows as $row) {
            $answers = is_array($row->survey) ? $row->survey : json_decode($row->survey, true);
            if (!is_array($answers)) {
                continue;
            }

            $recordedAnswers = [];
            for ($question = 1; $question <= $questionCount; $question++) {
                $answer = $answers['Q' . $question] ?? $answers[$question] ?? null;
                if (!is_scalar($answer) || trim((string) $answer) === '') {
                    continue;
                }

                $answer = trim((string) $answer);
                $recordedAnswers[$question] = $answer;
                $questionChoices[$question][$answer] = ($questionChoices[$question][$answer] ?? 0) + 1;
            }

            if (!$recordedAnswers) {
                continue;
            }

            $agentKey = $row->user_id === null ? 'unassigned' : (string) $row->user_id;
            if (!isset($agentTotals[$agentKey])) {
                $agentTotals[$agentKey] = [
                    'name' => $row->agent_name ?: 'Unassigned',
                    'survey_count' => 0,
                    'answer_count' => 0,
                    'questions' => array_fill(1, $questionCount, []),
                ];
            }

            $agentTotals[$agentKey]['survey_count']++;
            $agentTotals[$agentKey]['answer_count'] += count($recordedAnswers);
            $totalAnswers += count($recordedAnswers);
            if (isset($directionTotals[$row->direction])) {
                $directionTotals[$row->direction]++;
            }
            foreach ($recordedAnswers as $question => $answer) {
                $agentTotals[$agentKey]['questions'][$question][$answer] =
                    ($agentTotals[$agentKey]['questions'][$question][$answer] ?? 0) + 1;
            }

            $totalSurveys++;
            if ($includeResponses) {
                $responses[] = [
                    'linkedid' => $row->linkedid,
                    'call_date' => $row->call_date,
                    'agent' => $row->agent_name ?: 'Unassigned',
                    'phone_number' => $row->phone_number,
                    'direction' => $row->direction,
                    'answers' => $recordedAnswers,
                ];
            }
        }

        $questions = [];
        foreach ($questionChoices as $number => $choices) {
            arsort($choices);
            $responseCount = array_sum($choices);
            $formattedChoices = [];
            foreach ($choices as $value => $count) {
                $formattedChoices[] = [
                    'value' => $value,
                    'count' => $count,
                    'percentage' => $responseCount ? round(($count / $responseCount) * 100, 1) : 0,
                ];
            }
            $questions[] = [
                'number' => $number,
                'response_count' => $responseCount,
                'choices' => $formattedChoices,
                'mode' => $choices ? (string) array_key_first($choices) : null,
            ];
        }

        $agents = array_values($agentTotals);
        foreach ($agents as &$agent) {
            $agent['modes'] = [];
            foreach ($agent['questions'] as $question => $choices) {
                if ($choices) {
                    arsort($choices);
                    $agent['modes'][$question] = (string) array_key_first($choices);
                }
            }
        }
        unset($agent);

        usort($agents, function ($left, $right) {
            return strcasecmp($left['name'], $right['name']);
        });

        return [
            'conditions' => $conditions,
            'question_count' => $questionCount,
            'total_surveys' => $totalSurveys,
            'total_answers' => $totalAnswers,
            'direction_totals' => $directionTotals,
            'questions' => $questions,
            'agents' => $agents,
            'responses' => $responses,
            'agents_options' => $agentsOptions,
            'filters' => [
                'agent' => $agentFilter,
            ],
        ];
    }

    private function applyAccessScope($query, $user): void
    {
        if ($user->hasRole('admin')) {
            return;
        }

        if ($user->hasRole('user')) {
            $query->where('call_logs.user_id', $user->id);
            return;
        }

        if ($user->hasRole('company_manager') || $user->access_to_all_entity == '1') {
            $query->where('users.company_id', $user->company_id);
            return;
        }

        $queueIds = $user->accessibleQueueIds();
        $queueIds = $queueIds instanceof \Illuminate\Support\Collection ? $queueIds->all() : (array) $queueIds;

        $query->where(function ($scope) use ($user, $queueIds) {
            $scope->where('call_logs.user_id', $user->id);
            if ($queueIds) {
                $scope->orWhereIn('call_logs.user_id', function ($subquery) use ($user, $queueIds) {
                    $subquery->select('users.id')
                        ->from('users')
                        ->join('group_user', 'group_user.user_id', '=', 'users.id')
                        ->where('users.company_id', $user->company_id)
                        ->whereIn('group_user.group_id', $queueIds);
                });
            }
        });
    }

    private function surveyAnswerSqlExpression(int $question): string
    {
        $numericPath = '$."' . $question . '"';

        return "COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(call_logs.survey, '$.Q{$question}')), 'null'), "
            . "NULLIF(JSON_UNQUOTE(JSON_EXTRACT(call_logs.survey, '{$numericPath}')), 'null'), '')";
    }

    private function surveyAnswer($survey, int $question): string
    {
        $answers = is_array($survey) ? $survey : json_decode((string) $survey, true);
        $answer = is_array($answers)
            ? ($answers['Q' . $question] ?? $answers[$question] ?? '')
            : '';

        return is_scalar($answer) ? trim((string) $answer) : '';
    }

    private function dateConditions(Request $request): array
    {
        $default = (new SettingsHelper())->defaultConds();
        $from = Carbon::parse($default['from']);
        $to = Carbon::parse($default['to']);

        if ($request->filled('daterange')) {
            $range = explode(' - ', $request->input('daterange'), 2);
            if (count($range) === 2) {
                try {
                    $parsedFrom = Carbon::createFromFormat('Y-m-d H:i:s', trim($range[0]));
                    $parsedTo = Carbon::createFromFormat('Y-m-d H:i:s', trim($range[1]));
                    if (!$parsedFrom || !$parsedTo) {
                        throw new \InvalidArgumentException('Invalid report date range.');
                    }
                    $from = $parsedFrom;
                    $to = $parsedTo;
                    if ($to->lt($from)) {
                        [$from, $to] = [$to, $from];
                    }
                } catch (\Exception $exception) {
                    $from = Carbon::now()->startOfDay();
                    $to = Carbon::now()->endOfDay();
                }
            }
        }

        return [
            'from' => $from->format('Y-m-d H:i:s'),
            'to' => $to->format('Y-m-d H:i:s'),
        ];
    }

}