<?php

namespace Televox\Http\Controllers;

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

class Grabaciones extends Controller
{
    /**
     * @param  array  $columnNames
     * @param  array  $rows
     * @param  string  $fileName
     * @return \Symfony\Component\HttpFoundation\StreamedResponse
     */
    public static function getCsv($columnNames, $rows, $fileName = 'file.csv')
    {
        $headers = [
            'Content-type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename=' . $fileName,
            'Pragma' => 'no-cache',
            'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
            'Expires' => '0',
        ];
        $callback = function () use ($columnNames, $rows) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $columnNames);
            foreach ($rows as $row) {
                fputcsv($file, $row);
            }
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function getDetails(Request $request)
    {
        $datos = $request->validate([
            'GRAB_ID' => 'string|required',
            'archive' => 'boolean|required',
        ]);

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

        $results = DB::connection('Televox')
            ->table("$recording_log AS rl")
            ->leftJoin('tbl_comentarios AS tc', 'tc.id_grab', '=', 'rl.filename')
            ->leftJoin('tbl_status_grab AS tsg', 'tsg.id_grab', '=', 'rl.filename')
            ->join('tbl_status AS ts', 'ts.id_status', '=', 'idStatus')
            ->leftJoin('audios_listen AS al', 'al.id_grab', '=', 'rl.filename')
            ->where([['rl.filename', '=', $datos['GRAB_ID']], ['al.id_usuario', '=', $request->session()->get('id')]])
            ->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"),
                'ts.idStatus',
                'tc.text_comment'
            ])
            ->first();

        if ($results) {
            return ['Estado' => 'ok', 'Grabacion' => $results];
        } else {
            return ['Estado' => 'error', 'Descripcion' => 'No se encontro la grabacion'];
        }
    }

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

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

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

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

        $filas = [];
        $titulo = [
            'Identificador',
            'Fecha De Inicio',
            'Hora De Inicio',
            'Hora De Fin',
            'Duración',
            'Nombre',
            'Usuario',
            'Telefono',
            'Status',
            'Reproducido',
            'Comentarios'
        ];

        foreach ($results as $row) {
            $fila = [
                $row->Identificador,
                $row->FechaInicio,
                $row->HoraInicio,
                $row->HoraFin,
                $row->Duracion,
                $row->GRAB_ID,
                $row->Usuario,
                $row->Telefono,
                $row->NombreStatus,
                $row->listened == -1 ? 'No' : 'Si',
                $row->comentario
            ];
            $filas[] = $fila;
        }

        return self::getCsv($titulo, $filas, 'Reporte de grabaciones.csv');
    }

    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, 20);

        $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',
            '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')
            ->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 (!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);
        }


        $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")
        ]);

        /* $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();
    }
}
