<?php

class equipos_model
{
    private $conexion;

    public function __construct($conection)
    {
        $this->conexion = $conection;
    }

    public function count($filters)
    {
        $sql = " SELECT COUNT(*) AS total";
        $sql .= " FROM vista_equipos";
        $sql .= " WHERE 1=1 ";
        $params = [];
        $types = "";

        $sql .= $this->build_filters($filters, $params, $types);

        try {
            $stmt = UtilSQL::executeQuery($this->conexion, $sql, $params, $types);

            $sqlResult = $stmt->get_result();
            $stmt->close();

            $fila = $sqlResult->fetch_assoc();
            return $fila['total'];
        } catch (Exception $e) {
            return "Error al obtener el total: " . $e->getMessage();
        }
    }


    public function selectAll($filters, $paginar = true, $filtrar =true)
    {
        $sql = " SELECT * ";
        $sql .= " FROM vista_equipos";
        $sql .= " WHERE 1=1 ";
        $params = [];
        $types = "";

        if ($filtrar) {
            $sql .= $this->build_filters($filters, $params, $types);
            $sql .= UtilSQL::build_sort($filters);
        }
        if ($paginar) {
             $sql .= UtilSQL::build_paginacion($filters, $params, $types); 
        }
//        Tools::debugTXT( $sql );

        try {
            $stmt = UtilSQL::executeQuery($this->conexion, $sql, $params, $types);

            $sqlResult = $stmt->get_result();
            $stmt->close();

            $result = [];
            while ($fila = $sqlResult->fetch_assoc()) {
                $result[] = $fila;
            }
            return $result;
        } catch (Exception $e) {
            return "Error al obtener los registros: " . $e->getMessage();
        }
    }


    
    public function select_equipos_nocomm($filters, $paginar = true)
    {
        $sql = " SELECT * ";
        $sql .= " FROM vista_equipos";
        $sql .= " WHERE 1=1 ";
        $sql .= " AND UltimaRecepcion < NOW() - INTERVAL 15 MINUTE ";
        $sql .= " AND asp = 0 ";
        $sql .= " AND estudiarcomm = 1 ";
        $params = [];
        $types = "";

        $sql .= $this->build_filters($filters, $params, $types);
        $sql .= UtilSQL::build_sort($filters);
        if ($paginar) {
             $sql .= UtilSQL::build_paginacion($filters, $params, $types); 
        }
//        Tools::debugTXT( $sql );

        try {
            $stmt = UtilSQL::executeQuery($this->conexion, $sql, $params, $types);

            $sqlResult = $stmt->get_result();
            $stmt->close();

            $result = [];
            while ($fila = $sqlResult->fetch_assoc()) {
                $result[] = $fila;
            }
            return $result;
        } catch (Exception $e) {
            return "Error al obtener los registros: " . $e->getMessage();
        }
    }


    public function select($id)
    {
        $sql = "SELECT * FROM equipos WHERE Id = ?";

        try {
            $stmt = $this->conexion->prepare($sql);
            $stmt->bind_param("i", $id);

            if (!$stmt->execute()) {
                die('Error en la ejecución de la consulta SQL: ' . $stmt->error);
            }

            $sqlResult = $stmt->get_result();
            $stmt->close();

            $result = [];
            while ($fila = $sqlResult->fetch_assoc()) {
                $result[] = $fila;
            }
            return $result;
        } catch (mysqli_sql_exception $e) {
            error_log("Error en consulta SELECT: " . $e->getMessage()); // Guardar en log de errores
            return "Error al obtener el registro: " . $e->getMessage(); // Mensaje amigable para el usuario
        }
    }


    public function lastId()
    {
        $sql = "SELECT * FROM equipos ORDER BY Id DESC LIMIT 1";
        $stmt = $this->conexion->prepare($sql);

        if (!$stmt->execute()) {
            die('Error en la ejecución de la consulta SQL: ' . $stmt->error);
        }

        $resultSQL = $stmt->get_result()->fetch_assoc();
        $lastId = isset($resultSQL['Id']) ? (int)$resultSQL['Id'] : 0;
        $stmt->close();

        return $lastId;  
    }

    public function insert($descriptor, $id)
    {
        $sql = "INSERT INTO equipos (Descriptor, Id) VALUES (?,?)";

        $stmt = $this->conexion->prepare($sql);

        if (!$stmt) {
            die('Error en la preparación de la consulta SQL: ' . $this->conexion->error);
        }

        $stmt->bind_param("si", $descriptor, $id);

        if (!$stmt->execute()) {
            die('Error al ejecutar la consulta SQL: ' . $stmt->error);
        }

        $stmt->close();
        return true;
    }

    public function delete($id)
    {
        $sql = "DELETE FROM equipos WHERE Id = ? ";
        $stmt = $this->conexion->prepare($sql);

        if (!$stmt) {
            die('Error en la preparación de la consulta SQL: ' . $this->conexion->error);
        }

        $stmt->bind_param("i", $id);
        if (!$stmt->execute()) {
            die('Error al ejecutar la consulta SQL: ' . $stmt->error);
        }

        $stmt->close();
        return true;
    }

    public function update($id, $data)
    {
        $sql = "UPDATE equipos SET 
                ns = ?, 
                PIN = ?,
                Descriptor = ?,
                numero_corto = ?,
                numero_largo = ?,
                diferenciahoraria = ?,
                estudiarcomm = ?,
                estudiaralim = ?,
                asp = ?,
                instalador = ?,
                tipoinstalacion = ?,
                instalacion = ?,
                asp = ?
            WHERE Id = ?";

        $stmt = $this->conexion->prepare($sql);
        if (!$stmt) {
            die('Error en la preparación de la consulta SQL: ' . $this->conexion->error);
        }

        $stmt->bind_param(
            "issddiiiiiiiii",            
            $data['ns'],
            $data['PIN'],
            $data['Descriptor'],
            $data['numero_corto'],
            $data['numero_largo'],
            $data['diferenciahoraria'],
            $data['estudiarcomm'],
            $data['estudiaralim'],
            $data['asp'],
            $data['instalador'],
            $data['tipoinstalacion'],
            $data['instalacion'],
            $data['asp'],
            $id
        );        
        if (!$stmt->execute()) {
            die('Error al ejecutar la consulta SQL: ' . $stmt->error);
        }

        $stmt->close();
        return true;
    }


    private function build_filters($filters, &$params, &$types)
    {
        $sql = "";
        
        if ( isset($filters['search']) &&  $filters['search']!= '') {
            $searchValue = $filters['search'];
            $sql .= " AND instalacion_nombre LIKE ?";
            $params[] = "%" . $searchValue . "%";
            $types .= "s";
        }
        
        if ( isset($filters['filter_instalaciones'])  ) {
            $sql .= " AND instalacion IN (" . implode(',', array_fill(0, count($filters['filter_instalaciones']), '?')) . ")";
            $params = array_merge($params, $filters['filter_instalaciones']);
            $types .= str_repeat('i', count($filters['filter_instalaciones'])); // si son enteros            
        }

        // ojo q usamos instalacio_tipo de la vista, no tipoinstalacion de la tabla
        if ( isset($filters['filter_instalacion_tipo']) && $filters['filter_instalacion_tipo'] != '-1' ) {
            $sql .= " AND instalacion_tipo = ? ";
            $params[] = (int)$filters['filter_instalacion_tipo'];
            $types .= "i";
        }

        if ( isset($filters['filter_instalador']) && $filters['filter_instalador'] != '-1' ) {
            $sql .= " AND instalador = ? ";
            $params[] = (int)$filters['filter_instalador'];
            $types .= "i";
        }

        if ( isset($filters['filter_asp']) && $filters['filter_asp'] != '-1' ) {
            $sql .= " AND asp = ? ";
            $params[] = (int)$filters['filter_asp'];
            $types .= "i";
        }

//        Tools::debugTXT( $sql );

        return $sql;
    }

} // class
