<?php

namespace Televox\Http\Controllers;

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

class DescargaArchivos extends Controller
{
    private const AUDIOS_PATH = "audios/";
    private const GRABACIONES_ROOT = "/mnt/Grabaciones";
    private const DATE_FORMAT = "Y-m-d H:i:s";

    private function checkPermissions(Request $request, $reproducir)
    {
        if (
            (!$request->session()->get("descargar", false)) ||
            ($reproducir === 1 && !$request->session()->get("reproducir", false))
        ) {
            abort(403, "No tiene permisos para descargar grabaciones");
        }
    }

    private function getRecordingLog($archive)
    {
        return $archive ? "recording_log_archive" : "recording_log";
    }

    private function getRecordingData($id, $idClientes, $recording_log)
    {
        return DB::connection("Dialvox")
            ->table("$recording_log AS rl")
            ->where("rl.recording_id", "=", $id)
            ->where("rl.filename", "LIKE", "%_" . $idClientes)
            ->addSelect("rl.recording_id AS Nombre")
            ->addSelect(DB::raw("date(rl.start_time) as 'FechaInicio'"))
            ->addSelect(DB::raw("time(rl.start_time) AS 'HoraInicio'"))
            ->addSelect(DB::raw("time(rl.end_time) AS 'HoraFin'"))
            ->addSelect(DB::raw("sec_to_time(rl.length_in_sec) AS 'Duracion'"))
            ->addSelect("rl.filename AS GRAB_ID")
            ->addSelect("rl.user")
            ->addSelect(DB::raw("substr(rl.location,Locate('MP3/',rl.location)+4) AS 'GRAB_FILE'"))
            ->addSelect("rl.extension AS phone_number")
            ->addSelect("rl.lead_id AS lead_id")
            ->first();
    }

    private function createDirectory($path)
    {
        if (!is_dir($path)) {
            mkdir($path);
        }
    }

    private function copyFile($source, $destination)
    {
        if (is_file($source)) {
            return copy($source, $destination);
        } else {
            Log::error("No existe el archivo: $source");
        }
        return false;
    }

    private function logDownload(Request $request, $idClientes, $result, $tipo)
    {
        DB::connection("Televox")
            ->table("tbl_log")
            ->insert([
                "idClientes" => $idClientes,
                "ip" => $request->ip(),
                "fecha" => date(self::DATE_FORMAT),
                "id_usuario" => $request->session()->get("id"),
                "tipo" => $tipo,
                "detalle" => "El usuario {$request->session()->get("usuario")} " . ($tipo == 4 ? "escucho" : "descargo") . " la grabacion: $result->GRAB_ID Ext/Usuario: $result->user: Fecha: $result->FechaInicio Hora: $result->HoraInicio"
            ]);
    }

    public function download(Request $request, $id)
    {
        $idClientes = $request->session()->get("idClientes");
        $datos = $request->validate([
            "reproducir" => ["numeric", "nullable"],
            "archive" => ["boolean", "required"],
        ]);
        $reproducir = array_key_exists("reproducir", $datos) ? $datos["reproducir"] : 0;

        $this->checkPermissions($request, $reproducir);

        $recording_log = $this->getRecordingLog($datos["archive"]);
        $result = $this->getRecordingData($id, $idClientes, $recording_log);

        if (!$result) {
            abort(404, "Grabacion no encontrada");
        }

        $this->createDirectory(public_path(self::AUDIOS_PATH));

        $rutaGrab = env("RUTAGRAB", self::GRABACIONES_ROOT);
        $rutaFinalArchivo = public_path(self::AUDIOS_PATH . $result->GRAB_ID . ".mp3");
        $fechaRemplazada = str_replace("-", "/", $result->FechaInicio);
        $nombreArchivoRemplazado = str_replace($fechaRemplazada . "/", "", $result->GRAB_FILE);
        if (!is_file($rutaFinalArchivo)) {
            $retorno = $this->copyFile($rutaGrab . DIRECTORY_SEPARATOR . $result->GRAB_FILE, $rutaFinalArchivo);
            $retorno = $this->copyFile($rutaGrab . DIRECTORY_SEPARATOR . $fechaRemplazada . DIRECTORY_SEPARATOR . $nombreArchivoRemplazado, $rutaFinalArchivo);
        }

        if (!is_file($rutaFinalArchivo)) {
            abort(404, "No se pudo descargar la grabacion");
        }

        if ($reproducir) {
            $this->logDownload($request, $idClientes, $result, 4);
            DB::connection("Televox")
                ->table("audios_listen")
                ->updateOrInsert([
                    "id_grab" => $result->GRAB_ID,
                ], [
                    "id_usuario" => $request->session()->get("id")
                ]);
        } else {
            $this->logDownload($request, $idClientes, $result, 3);
        }

        return response()->download($rutaFinalArchivo);
    }

    public function downloadFiles(Request $request)
    {
        $idClientes = $request->session()->get("idClientes");
        if (!$request->session()->get("descargar", false)) {
            abort(403, "No tiene permisos para descargar grabaciones");
        }

        $datos = $request->validate([
            "usuario" => ["alpha_num", "nullable"],
            "tipoLlam" => ["numeric", "between:-1,1", "nullable"],
            "identificador" => ["numeric", "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"],
            "fsegundosinicial" => ["between:0,59", "numeric", "required_with_all:fminutosinicial,fhorasinicial,inicio"],
            "fminutosinicial" => ["between:0,59", "numeric", "required_with_all:fsegundosinicial,fhorasinicial,inicio"],
            "fhorasinicial" => ["between:0,23", "numeric", "required_with_all:fminutosinicial,fsegundosinicial,inicio"],
            "fsegundosfinal" => ["between:0,59", "numeric", "required_with_all:fminutosfinal,horasfinal,fin"],
            "fminutosfinal" => ["between:0,59", "numeric", "required_with_all:fsegundosfinal,fin"],
            "fhorasfinal" => ["between:0,23", "numeric", "required_with_all:fminutosfinal,fsegundosfinal,fin"],
            "status" => ["numeric", "nullable"],
            "escuchando" => ["boolean", "nullable"],
            "archive" => ["boolean", "required"],
            "campaign_id" => ["nullable", "string", "required_with:list_id,status_id"],
            "list_id" => ["nullable", "string", "required_with:status_id"],
            "status_id" => ["nullable", "string"],
            "ingroup_id" => ["nullable", "string"],
        ]);

        if (!array_key_exists("list_id", $datos)) {
            $datos["list_id"] = null;
        }
        if (!array_key_exists("campaign_id", $datos)) {
            $datos["campaign_id"] = null;
        }
        if (!array_key_exists("status_id", $datos)) {
            $datos["status_id"] = null;
        }
        if (!array_key_exists("ingroup_id", $datos)) {
            $datos["ingroup_id"] = null;
        }
        if (!array_key_exists("cuenta", $datos)) {
            $datos["cuenta"] = null;
        }
        if (!array_key_exists("telefono", $datos)) {
            $datos["telefono"] = null;
        }
        if (!array_key_exists("usuario", $datos)) {
            $datos["usuario"] = null;
        }
        if (!array_key_exists("nombre", $datos)) {
            $datos["nombre"] = null;
        }
        if (!array_key_exists("identificador", $datos)) {
            $datos["identificador"] = null;
        }
        if (!array_key_exists("tipoLlam", $datos)) {
            $datos["tipoLlam"] = null;
        }
        if (!array_key_exists("status", $datos)) {
            $datos["status"] = null;
        }
        if (!array_key_exists("escuchando", $datos)) {
            $datos["escuchando"] = null;
        }




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

        $RUTAGRAB = env("RUTAGRAB", self::GRABACIONES_ROOT);
        $string = $this->processResults($results, $RUTAGRAB, $request, $idClientes);

        $this->createDirectory(public_path(".") . "/ZIP");

        $nombre = public_path("ZIP") . "/archivozip" . rand(1, 10000000) . ".zip";
        $zip = new \ZipArchive();
        if ($zip->open($nombre, \ZipArchive::CREATE) === true) {
            foreach ($string as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
            if (file_exists($nombre)) {
                return response()->download($nombre, "Grabaciones.zip");
            } else {
                Log::error("No pudo encontrar el archivo");
                abort(404, "No se pudieron conseguir las grabaciones");
            }
        } else {
            Log::error("No pudo crear el archivo");
            abort(404, "No se pudieron conseguir las grabaciones");
        }
    }

    private function buildFilters($datos, $idClientes)
    {
        $filtros = [];
        $recording_log = $this->getRecordingLog($datos["archive"]);

        if ($datos["status"]) {
            $filtros[] = ["ts.idStatus", "=", $datos["status"]];
        }



        if ($datos["campaign_id"]) {
            $filtros[] = ["vc.campaign_id", "=", $datos["campaign_id"]];
            if ($datos["list_id"]) {
                $filtros[] = ["vls.list_id", "=", $datos["list_id"]];
            }
            if ($datos["status_id"]) {
                $filtros[] = ["vl.status", "=", $datos["status_id"]];
            }
        } elseif ($datos["ingroup_id"]) {
            $filtros[] = ["vc.campaign_id", "=", $datos["ingroup_id"]];
        }

        $filtros[] = ["rl.filename", "LIKE", "%_" . $idClientes];

        if ($datos["cuenta"]) {
            $filtros[] = ["vl.vendor_lead_code", "LIKE", "%{$datos["cuenta"]}%"];
        }
        if ($datos["identificador"]) {
            $filtros[] = ["rl.recording_id", "=", $datos["identificador"]];
        }
        if ($datos["telefono"]) {
            $filtros[] = ["rl.extension", "LIKE", "%{$datos["telefono"]}%"];
        }
        if ($datos["nombre"]) {
            $filtros[] = ["rl.filename", "LIKE", "%{$datos["nombre"]}%"];
        }
        if ($datos["usuario"]) {
            $filtros[] = ["rl.user", "LIKE", "%{$datos["usuario"]}%"];
        }
        //TODO: Arreglar estos valores, ya que puede pasar el caso de que alguno sea 0 y en este caso es una valor valido
        if ($datos["segundosinicial"] && $datos["minutosinicial"] && $datos["horasinicial"]) {
            $filtros[] = ["rl.length_in_sec", ">=", $datos["segundosinicial"] + ($datos["minutosinicial"] * 60) + ($datos["horasinicial"] * 3600)];
        }
        if ($datos["segundosfinal"]) {
            $filtros[] = ["rl.length_in_sec", "<=", $datos["segundosfinal"] + ($datos["minutosfinal"] * 60) + ($datos["horasfinal"] * 3600)];
        }
        if ($datos["tipoLlam"]) {
            if ($datos["tipoLlam"] > 0) {
                $filtros[] = ["rl.filename", "LIKE", "IN_%"];
            } else {
                $filtros[] = ["rl.filename", "LIKE", "OUT_%"];
            }
        }

        return $filtros;
    }

    private function getResults($datos, $filtros, $request)
    {
        $recording_log = $this->getRecordingLog($datos["archive"]);
        $results = DB::connection("Televox")
            ->table("$recording_log as rl")
            ->crossJoin("vicidial_list AS vl")
            ->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")
            ->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")
            ->where($filtros)
            ->where(function ($q) use ($datos) {
                if (array_key_exists("inicio", $datos)) {
                    $q->where(function ($qq) use ($datos) {
                        $qq->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["fsegundosinicial"]}")));
                    })->orWhere(function ($qq) use ($datos) {
                        $qq->whereDate("rl.start_time", ">", date("Y-m-d", strtotime($datos["inicio"])));
                    });
                }
            })->where(function ($q) use ($datos) {
                if (array_key_exists("fin", $datos)) {
                    $q->where(function ($qq) use ($datos) {
                        $qq->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["fsegundosfinal"]}")));
                    })->orWhere(function ($qq) use ($datos) {
                        $qq->whereDate("rl.start_time", "<", date("Y-m-d", strtotime($datos["fin"])));
                    });
                }
            })->where(function ($q) use ($request) {
                $q->where(function ($qq) use ($request) {
                    $qq->where("al.id_usuario", "=", $request->session()->get("id"));
                })->orWhere(function ($qq) {
                    $qq->whereRaw("al.id_usuario IS NULL");
                });
            });

        return $results->limit(1000)
            ->addSelect("rl.recording_id as Nombre")
            ->addSelect(DB::raw("date(rl.start_time) as 'FechaInicio'"))
            ->addSelect(DB::raw("time(rl.start_time) as 'HoraInicio'"))
            ->addSelect(DB::raw("time(rl.end_time) as 'HoraFin'"))
            ->addSelect(DB::raw("sec_to_time(rl.length_in_sec) as 'Duracion'"))
            ->addSelect("rl.filename AS GRAB_ID")
            ->addSelect("rl.user")
            ->addSelect(DB::raw("substr(rl.location,Locate('MP3/',rl.location)+4) AS 'GRAB_FILE'"))
            ->addSelect("rl.extension AS phone_number")
            ->addSelect("rl.lead_id AS lead_id")
            ->get();
    }

    private function processResults($results, $RUTAGRAB, $request, $idClientes)
    {
        $string = [];
        foreach ($results as $result) {
            $realGRABFILE = $result->GRAB_FILE;
            $fechaRemplazada = str_replace("-", "/", $result->FechaInicio);
            $nombreArchivoRemplazado = str_replace($fechaRemplazada . "/", "", $realGRABFILE);

            $this->createDirectory(public_path(self::AUDIOS_PATH));

            if (!file_exists(public_path(self::AUDIOS_PATH . $result->GRAB_ID . ".mp3"))) {
                $this->copyFile($RUTAGRAB . DIRECTORY_SEPARATOR . $realGRABFILE, public_path(self::AUDIOS_PATH) . DIRECTORY_SEPARATOR . $result->GRAB_ID . ".mp3");
                $this->copyFile($RUTAGRAB . DIRECTORY_SEPARATOR . $fechaRemplazada . DIRECTORY_SEPARATOR . $nombreArchivoRemplazado, public_path(self::AUDIOS_PATH) . DIRECTORY_SEPARATOR . $result->GRAB_ID . ".mp3");
            }

            if (file_exists(public_path(self::AUDIOS_PATH . $result->GRAB_ID . ".mp3"))) {
                $string[] = public_path("audios") . "/$result->GRAB_ID.mp3";
                DB::connection("Televox")->table("tbl_log")->insertGetId([
                    "idClientes" => $idClientes,
                    "ip" => $request->ip(),
                    "fecha" => date(self::DATE_FORMAT),
                    "id_usuario" => $request->session()->get("id"),
                    "tipo" => 3,
                    "detalle" => "El usuario " . $request->session()->get("usuario") . " descargo la grabacion: $result->GRAB_ID Ext/Usuario: $result->user: Fecha: $result->FechaInicio Hora: $result->HoraInicio"
                ]);
            } else {
                abort(404, "No se pudo descargar la grabacion");
            }
        }
        return $string;
    }
}

