<?php

namespace Televox\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SpeechRecognition extends Controller
{
    public function dashboard(Request $request)
    {
        $cliente_id = $request->session()->get('idClientes');

        $connection = DB::connection("Televox");

        $cliente = $connection
            ->table("analysis_client")
            ->where("client", "=", $cliente_id)
            ->first();

        $respuesta = array(
            "Estado" => "ok",
            "Resultados" => array()
        );

        $resultado = array();
        if ($cliente) {
            $intervalo = $cliente->time_frame;

            $analisis = $connection
                ->table("analysis")
                ->where("client", "=", $cliente_id)
                ->whereRaw("DATE(start_time) >= DATE_SUB(DATE(NOW()),INTERVAL ? DAY)", [$intervalo])
                ->where("estado", "=", "3")
                ->addSelect([
                    DB::raw("AVG(positive) as avg_positive"),
                    DB::raw("AVG(negative) as avg_negative"),
                    DB::raw("AVG(neutral) as avg_neutral"),
                    DB::raw("SUM(positive) as sum_positive"),
                    DB::raw("SUM(negative) as sum_negative"),
                    DB::raw("SUM(neutral) as sum_neutral"),
                ])
                ->first();

            $resultado["sentimientosDistribucion"] = array(
                "positivo" => $analisis->sum_positive,
                "negativo" => $analisis->sum_negative,
                "neutro" => $analisis->sum_neutral
            );
            $resultado["sentimientosPromedio"] = array(
                "positivo" => $analisis->avg_positive,
                "negativo" => $analisis->avg_negative,
                "neutro" => $analisis->avg_neutral
            );

            $palabrasQuery = $connection
                ->table("analysis_keyword")
                ->whereRaw("analysis_id IN (SELECT analysis_id FROM analysis WHERE client=? AND DATE(start_time) >= DATE_SUB(DATE(NOW()),INTERVAL ? DAY) AND estado=3)", [$cliente_id, $intervalo])
                ->where("is_problem", "=", 0)
                ->addSelect([
                    "keyword",
                    DB::raw("COUNT(*) as count")
                ])
                ->groupBy("keyword")
                ->orderByDesc("count")
                ->limit(12)
                ->get();

            $problemasQuery = $connection
                ->table("analysis_keyword")
                ->whereRaw("analysis_id IN (SELECT analysis_id FROM analysis WHERE client=? AND DATE(start_time) >= DATE_SUB(DATE(NOW()),INTERVAL ? DAY) AND estado=3)", [$cliente_id, $intervalo])
                ->where("is_problem", "=", 1)
                ->addSelect([
                    "keyword",
                    DB::raw("COUNT(*) as count")
                ])
                ->groupBy("keyword")
                ->orderByDesc("count")
                ->limit(12)
                ->get();

            $keywords = array();
            foreach ($palabrasQuery as $palabra) {
                $keywords[] = array(
                    "text" => $palabra->keyword,
                    "value" => $palabra->count
                );
            }

            $problemas = array();
            foreach ($problemasQuery as $palabra) {
                $problemas[] = array(
                    "text" => $palabra->keyword,
                    "value" => $palabra->count
                );
            }
            if (count($keywords) < 1) {
                $keywords[] = array(
                    "text" => "Sin información",
                    "value" => 30
                );
            }
            if (count($problemas) < 1) {
                $problemas[] = array(
                    "text" => "Sin información",
                    "value" => 30
                );
            }
            $resultado["keywords"] = $keywords;
            $resultado["problemas"] = $problemas;

            $resoluciones = $connection
                ->table("analysis")
                ->where("client", "=", $cliente_id)
                ->whereRaw("DATE(start_time) >= DATE_SUB(DATE(NOW()),INTERVAL ? DAY)", [$intervalo])
                ->where("estado", "=", "3")
                ->addSelect([
                    DB::raw("DATE(recording_start_time) as date"),
                    "user",
                    DB::raw("COUNT(*) as count"),
                    DB::raw("SUM(IF(resolved = 0, 0, 1)) as resueltas"),
                    DB::raw("MIN(recording_length_in_sec) as min_duration"),
                    DB::raw("MAX(recording_length_in_sec) as max_duration"),
                    DB::raw("AVG(recording_length_in_sec) as avg_duration"),
                ])
                ->groupBy("date", "user")
                ->get();

            $aht = array();
            $resolucionAgente = array();
            foreach ($resoluciones as $resolucion) {
                if (!isset($resolucionAgente[$resolucion->user])) {
                    $resolucionAgente[$resolucion->user] = array(
                        "lower" => 0,
                        "low" => 0,
                        "up" => 0,
                        "upper" => 0,
                        "min" => 10000,
                        "max" => 0,
                        "avg" => 0
                    );
                }
                $rangoUser = $resolucionAgente[$resolucion->user];
                $resolucionAgente[$resolucion->user]["min"] = min($rangoUser["min"], $resolucion->min_duration);
                $resolucionAgente[$resolucion->user]["max"] = max($rangoUser["max"], $resolucion->max_duration);
                $resolucionAgente[$resolucion->user]["avg"] = ($rangoUser["avg"] + $resolucion->avg_duration) / 2;

                $resolucionAgente[$resolucion->user]["lower"] = min($rangoUser["lower"], $resolucion->min_duration);
                $resolucionAgente[$resolucion->user]["low"] = min($rangoUser["low"], $resolucion->min_duration);

                $resolucionAgente[$resolucion->user]["upper"] = max($rangoUser["upper"], $resolucion->max_duration);
                $resolucionAgente[$resolucion->user]["up"] = max($rangoUser["up"], $resolucion->max_duration);


                if (!isset($aht[$resolucion->date])) {
                    $aht[$resolucion->date] = array(
                        "avg" => 0,
                        "total" => 0,
                        "resuelto" => 0,
                    );
                }
                $diaAht = $aht[$resolucion->date];
                $aht[$resolucion->date]["avg"] = ($diaAht["avg"] + $resolucion->avg_duration) / 2;
                $aht[$resolucion->date]["total"] += $resolucion->count;
                $aht[$resolucion->date]["resuelto"] += $resolucion->resueltas;
            }

            $respuesta_aht = array(
                "dias" => array(),
                "tasaResolucion" => array(),
                "aht" => array()
            );
            foreach ($aht as $dia => $info) {
                $respuesta_aht["dias"][] = $dia;
                $respuesta_aht["tasaResolucion"][] = $info["resuelto"] / min($info["total"], 1);
                $respuesta_aht["aht"][] = $info["avg"];
            }
            $resultado["aht"] = $respuesta_aht;

            $resolucionPromedios = array(
                "minimos" => [],
                "promedios" => [],
                "maximos" => [],
                "agentes" => []
            );
            foreach ($resolucionAgente as $agente => $info) {
                $resolucionPromedios["agentes"][] = $agente;
                $resolucionPromedios["minimos"][] = $info["min"];
                $resolucionPromedios["maximos"][] = $info["max"];
                $resolucionPromedios["promedios"][] = $info["avg"];
            }
            $resultado["resolucionPromedios"] = $resolucionPromedios;
            $resultado["resolucionRango"] = $resolucionAgente;

            $respuesta["Resultados"] = $resultado;
        }

        return $respuesta;
    }

    public function getAnalysis(Request $request)
    {
        $datos = $request->validate([
            'id' => ['numeric', 'nullable', "required"],
        ]);

        $recording_id = $datos["id"];

        $salida = [
            "Estado" => "ok",
            "Resultados" => array()
        ];

        $connection = DB::connection("Televox");

        $grabacion = $connection
            ->table("recording_log as rl")
            ->where("recording_id", "=", $recording_id)
            ->where('rl.filename', 'LIKE', '%_' . $request->session()->get('idClientes'))
            ->addSelect([
                'rl.recording_id as Id',
                DB::raw("date(rl.start_time) as 'FechaInicio'"),
                DB::raw("time(rl.start_time) as 'HoraInicio'"),
                DB::raw("time(rl.end_time) as 'HoraFin'"),
                'rl.filename as Nombre',
                "rl.extension as Telefono",
            ])
            ->first();

        if (is_null($grabacion)) {
            $grabacion = $connection
                ->table("recording_log_archive as rl")
                ->where("recording_id", "=", $recording_id)
                ->where('rl.filename', 'LIKE', '%_' . $request->session()->get('idClientes'))
                ->addSelect([
                    'rl.recording_id as Id',
                    DB::raw("date(rl.start_time) as 'FechaInicio'"),
                    DB::raw("time(rl.start_time) as 'HoraInicio'"),
                    DB::raw("time(rl.end_time) as 'HoraFin'"),
                    'rl.filename as Nombre',
                    "rl.extension as Telefono",
                ])
                ->first();
        }

        if (is_null($grabacion)) {
            return $salida;
        }

        $resultado = array(
            "id" => $grabacion->Id,
            "fecha" => $grabacion->FechaInicio,
            "horaInicio" => $grabacion->HoraInicio,
            "horaFin" => $grabacion->HoraFin,
            "nombre" => $grabacion->Nombre,
            "telefono" => $grabacion->Telefono,
            "escuchado" => false
        );

        $analisis = $connection
            ->table("analysis")
            ->where("recording_id", "=", $recording_id)
            ->where('client', '=', $request->session()->get('idClientes'))
            ->first();

        if ($analisis && $analisis->estado == 3) {
            $analisis_id = $analisis->analysis_id;
            $resultado_analisis = array(
                "id" => $analisis->analysis_id,
                "resumen" => $analisis->transcript_resume,
                "resuelto" => $analisis->resolved,
                "sentimiento" => array(
                    "positivo" => $analisis->positive,
                    "negativo" => $analisis->negative,
                    "neutro" => $analisis->neutral
                )
            );

            $palabrasClave = $connection
                ->table("analysis_keyword")
                ->where("analysis_id", "=", $analisis_id)
                ->get();

            $keywords = array();
            $problemas = array();
            foreach ($palabrasClave as $palabra) {
                if ($palabra->is_problem) {
                    $problemas[] = $palabra->keyword;
                } else {
                    $keywords[] = $palabra->keyword;
                }
            }

            $resultado_analisis["palabrasClave"] = $keywords;
            $resultado_analisis["problemasClave"] = $problemas;

            $dialogos = $connection
                ->table("analysis_transcription")
                ->where("analysis_id", "=", $analisis_id)
                ->orderBy("conversation_index")
                ->get();

            $resultado_dialogos = array();
            foreach ($dialogos as $dialogo) {
                $resultado_dialogos[] = array(
                    "timestampInicio" => $dialogo->start_s,
                    "timestampFin" => $dialogo->end_s,
                    "texto" => $dialogo->transcription,
                    "locutor" => intval($dialogo->speaker) < 0 ? "agente" : "cliente",
                    "resolucion" => false,
                    "sentimiento" => array(
                        "positivo" => $dialogo->value_positive,
                        "negativo" => $dialogo->value_negative,
                        "neutro" => $dialogo->value_neutral
                    )
                );
            }
            $resultado_analisis["conversacion"] = $resultado_dialogos;

            $observaciones = $connection
                ->table("analysis_comment")
                ->where("analysis_id", "=", $analisis_id)
                ->get();
            $resultado_observaciones = array();
            foreach ($observaciones as $observacion) {
                $resultado_observaciones[] = array(
                    "observacion" => $observacion->comment
                );
            }

            $resultado["analisis"] = $resultado_analisis;

            $salida["Resultados"] = $resultado;
        }

        return $salida;
    }

    public function getRegistros(Request $request)
    {
        $datos = $this->validateRequest($request);

        $recording_log = $datos['archive'] ? 'recording_log_archive' : 'recording_log';

        $filtros = $this->buildFilters($datos, $request);

        $limit = ($datos['pagina'] - 1) * 20;

        $results = $this->getResults($datos, $filtros, $recording_log, $request, $limit, 21);

        $titulo = [
            'Identificador',
            'Fecha',
            'Hora Inicio',
            'Hora Fin',
            'Duracion',
            'Nombre',
            'Usuario',
            'Telefono',
            'Nombre del Status',
            'Color',
            'Escuchado'
        ];

        $query = '[' . implode(', ', array_map(fn($key, $val) => "$key => $val", array_keys($datos), $datos)) . ']';

        DB::connection('Televox')->table('tbl_log')->insertGetId([
            'idClientes' => $request->session()->get('idClientes'),
            'ip' => $request->ip(),
            'fecha' => now(),
            'id_usuario' => $request->session()->get('id'),
            'tipo' => 2,
            'detalle' => 'El usuario ' . $request->session()->get('usuario') . " hizo una consulta a las grabaciones con los parametros con: $query"
        ]);

        return ['Estado' => 'ok', 'Registros' => $results, 'Cuantos' => count($results), 'titulos' => $titulo];
    }

    private function validateRequest($request)
    {
        return $request->validate([
            'tipoLlam' => 'numeric|between:-1,1|nullable',
            'identificador' => 'numeric|nullable',
            'usuario' => 'alpha_num|nullable',
            'nombre' => 'string|nullable',
            'telefono' => 'alpha_num|nullable',
            'cuenta' => 'alpha_num|nullable',
            'segundosinicial' => 'between:0,59|numeric|required_with_all:minutosinicial,horasinicial',
            'minutosinicial' => 'between:0,59|numeric|required_with_all:segundosinicial,horasinicial',
            'horasinicial' => 'between:0,23|numeric|required_with_all:minutosinicial,segundosinicial',
            'segundosfinal' => 'between:0,59|numeric|required_with_all:minutosfinal,horasfinal',
            'minutosfinal' => 'between:0,59|numeric|required_with_all:segundosfinal,horasfinal',
            'horasfinal' => 'between:0,23|numeric|required_with_all:minutosfinal,segundosfinal',
            'inicio' => 'before_or_equal:today|date|required_with_all:fminutosinicial,fhorasinicial,fsegundosinicial',
            'fin' => 'after_or_equal:inicio|before_or_equal:today|date|required_with_all:fminutosfinal,fhorasfinal,fsegundosfinal',
            'fminutosinicial' => 'between:0,59|numeric|required_with_all:fminutosinicial,fhorasinicial,inicio',
            'fhorasinicial' => 'between:0,23|numeric|required_with_all:fminutosinicial,fsegundosinicial,inicio',
            'fsegundosfinal' => 'between:0,59|numeric|required_with_all:fminutosfinal,fhorasfinal,fin',
            'fminutosfinal' => 'between:0,59|numeric|required_with_all:fsegundosfinal,fhorasfinal,fin',
            'fhorasfinal' => 'between:0,23|numeric|required_with_all:minutosfinal,segundosfinal,fin',
            'pagina' => 'min:0|numeric|required',
            'status' => 'numeric|nullable',
            'escuchando' => 'boolean|nullable',
            'archive' => 'boolean|required',
            'analized' => 'boolean|required',
            'campaign_id' => 'nullable|string|required_with:list_id,status_id',
            'list_id' => 'nullable|string',
            'status_id' => 'nullable|string',
        ]);
    }

    private function buildFilters($datos, $request)
    {
        $filtros = [['rl.filename', 'LIKE', '%_' . $request->session()->get('idClientes')]];

        if (!empty($datos['status'])) {
            $filtros[] = ['ts.idStatus', '=', $datos['status']];
        }
        if (!empty($datos['campaign_id'])) {
            $filtros[] = ['vc.campaign_id', '=', $datos['campaign_id']];
            if (!empty($datos['list_id'])) {
                $filtros[] = ['vls.list_id', '=', $datos['list_id']];
            }
            if (!empty($datos['status_id'])) {
                $filtros[] = ['vl.status', '=', $datos['status_id']];
            }
        }

        if (!empty($datos['cuenta'])) {
            $filtros[] = ['vl.vendor_lead_code', 'LIKE', "%{$datos['cuenta']}%"];
        }
        if (!empty($datos['identificador'])) {
            $filtros[] = ['rl.recording_id', '=', "{$datos['identificador']}"];
        }
        if (!empty($datos['telefono'])) {
            $filtros[] = ['rl.extension', 'LIKE', "%{$datos['telefono']}%"];
        }
        if (!empty($datos['nombre'])) {
            $filtros[] = ['rl.filename', 'LIKE', "%{$datos['nombre']}%"];
        }
        if (!empty($datos['usuario'])) {
            $filtros[] = ['rl.user', 'LIKE', "%{$datos['usuario']}%"];
        }
        if (!empty($datos['segundosinicial'])) {
            $filtros[] = ['rl.length_in_sec', '>=', $datos['segundosinicial'] + $datos['minutosinicial'] * 60 + $datos['horasinicial'] * 3600];
        }
        if (!empty($datos['segundosfinal'])) {
            $filtros[] = ['rl.length_in_sec', '<=', $datos['segundosfinal'] + $datos['minutosfinal'] * 60 + $datos['horasfinal'] * 3600];
        }
        if (!empty($datos['tipoLlam'])) {
            $filtros[] = ['rl.filename', 'LIKE', $datos['tipoLlam'] > 0 ? 'IN_%' : 'OUT_%'];
        }

        return $filtros;
    }

    private function getResults($datos, $filtros, $recording_log, $request, $offset = null, $limit = null)
    {
        $query = DB::connection('Televox')->table("$recording_log as rl")
            ->leftJoin('tbl_status_grab AS tsg', 'tsg.id_grab', '=', 'rl.filename')
            ->leftJoin('tbl_status AS ts', 'tsg.id_status', '=', 'ts.idStatus')
            ->leftJoin('audios_listen AS al', 'al.id_grab', '=', 'rl.filename')
            ->leftJoin("analysis as ai", "ai.recording_id", '=', 'rl.recording_id')
            ->where($filtros)
            ->where(function ($q) use ($datos) {
                if (!empty($datos['inicio'])) {
                    $q->whereDate('rl.start_time', '>=', date('Y-m-d', strtotime($datos['inicio'])))
                        ->whereTime('rl.start_time', '>=', date('H:i:s', strtotime("{$datos['fhorasinicial']}:{$datos['fminutosinicial']}:{$datos['fminutosinicial']}")));
                }
                if (!empty($datos['fin'])) {
                    $q->whereDate('rl.start_time', '<=', date('Y-m-d', strtotime($datos['fin'])))
                        ->whereTime('rl.start_time', '<=', date('H:i:s', strtotime("{$datos['fhorasfinal']}:{$datos['fminutosfinal']}:{$datos['fminutosfinal']}")));
                }
            });
        if (isset($datos['escuchando']) && $datos['escuchando']) {
            $query->where('al.id_usuario', '=', $request->session()->get('id'));
        }

        if (isset($datos["analized"]) && $datos["analized"]) {
            $query->whereNotNull('ai.estado');
        } else {
            $query->whereNull('ai.estado');
        }

        if (!empty($datos['campaign_id']) || !empty($datos['cuenta'])) {
            $query->crossJoin('vicidial_list AS vl')
                ->join('vicidial_lists AS vls', 'vls.list_id', '=', 'vl.list_id')
                ->join('vicidial_campaigns AS vc', 'vc.campaign_id', '=', 'vls.campaign_id')
                ->whereRaw('rl.lead_id=vl.lead_id');
        }

        if ($offset !== null && $limit !== null) {
            $query->offset($offset)->limit($limit + 1);
        }


        $query->addSelect([
            'rl.recording_id as Nombre',
            DB::raw("date(rl.start_time) as 'FechaInicio'"),
            DB::raw("time(rl.start_time) as 'HoraInicio'"),
            DB::raw("time(rl.end_time) as 'HoraFin'"),
            DB::raw("sec_to_time(rl.length_in_sec) as 'Duracion'"),
            'rl.filename as GRAB_ID',
            'rl.user',
            "rl.extension as 'phone_number'",
            'ts.nombre as NombreStatus',
            'ts.color as ColorStatus',
            DB::raw("COALESCE(al.id_usuario,'-1') AS listened"),
            DB::raw("COALESCE(ai.estado,'-1') AS analized"),
            "ai.positive as positivo",
            "ai.negative as negativo",
            "ai.neutral as neutro"
        ]);

        /* $bindings = $query->getBindings();
        $sql = $query->toSql();
        $QUERY = vsprintf(str_replace('?', "'%s'", $sql), $bindings);
        $QUERY = str_replace('\'%s\'', '%s', $QUERY);
        $QUERY = str_replace('\'', '', $QUERY);
        echo $QUERY;
        die(); */


        return $query->get();
    }
}
