<?php

namespace App\Repository;

use App\Entity\Plan;
use App\Entity\PlanCaracteristica;
use Doctrine\ORM\EntityRepository;

class PlanRepository extends EntityRepository
{
    public function existeNombre($id, $nombre) {

        $query = $this->createQueryBuilder('p')
            ->select('p.id')
            ->andWhere('p.nombre = :nombre')
            ->andWhere('p.id != :id')
            ->setParameter('nombre', $nombre)
            ->setParameter('id', $id)
            ->setMaxResults(1)
            ->getQuery();

        return $query->execute();
    }

    public function listadoAdmin() {

        $entityManager = $this->getEntityManager();
        $planes = $entityManager->createQuery(
            'SELECT
                pl.id, pl.nivel, pl.descripcion, pl.nombre, pl.estatus, MIN(co.costo) costo
            FROM
                App\Entity\Plan pl,
                App\Entity\PlanCosto co
            WHERE
                co.plan = pl.id
            GROUP BY
                pl.id
            ORDER BY
                pl.nivel, pl.nombre , pl.id ASC'
        )
        ->execute();

        return $planes;
    }

    public function mostrar() {

        $entityManager = $this->getEntityManager();
        $planes = $entityManager->createQuery(
            'SELECT
                pl.id, pl.nombre, MIN(co.costo) costo
            FROM
                App\Entity\Plan pl,
                App\Entity\PlanCosto co
            WHERE
                co.plan = pl.id AND
                pl.estatus = :estatus
            GROUP BY
                pl.id
            ORDER BY
                pl.nivel, pl.nombre , pl.id ASC'
        )
        ->setParameter('estatus', Plan::ESTATUS_ACTIVO)
        ->execute();

        if(!empty($planes)) {
            $this->setDetallesMostrar($planes, [
                PlanCaracteristica::ESTATUS_ACTIVO
            ]);
        }

        return $planes;
    }

    public function mostrarAdmin($id) {

        $entityManager = $this->getEntityManager();
        $plan = $entityManager->createQuery(
            'SELECT
                pl.id, pl.nombre, pl.descripcion, pl.estatus
            FROM
                App\Entity\Plan pl
            WHERE
                pl.id = :id
            GROUP BY
                pl.id
            ORDER BY
                pl.nivel, pl.nombre , pl.id ASC'
        )
        ->setParameter('id', $id)
        ->execute();

        if(!empty($plan)) {
            $this->setDetallesMostrar($plan, [
                PlanCaracteristica::ESTATUS_ACTIVO,
                PlanCaracteristica::ESTATUS_INACTIVO
            ]);
            $this->setCostoMostrar($plan);
        }

        return $plan;
    }

    private function setCostoMostrar(&$planes) {

        $entityManager = $this->getEntityManager();
        foreach ($planes as $plan) {
            $ids[] = $plan['id'];
        }

        $costos = $entityManager->createQuery(
                'SELECT
                    pl.id plan, co.costo, co.anio
                FROM
                    App\Entity\Plan pl,
                    App\Entity\PlanCosto co
                WHERE
                    pl.id IN ( :ids ) AND
                    pl.id = co.plan
                 ORDER BY co.anio ASC'
            )
            ->setParameter('ids', $ids)
            ->execute();

        if(!empty($costos)) {
            foreach ($planes as &$plan) {
                foreach ($costos as $costo) {
                    if($plan['id'] == $costo['plan']) {
                        $plan['costos'][] = $costo;
                    }
                }
            }
        }
    }

    private function setDetallesMostrar(&$planes, array $estatus) {

        $entityManager = $this->getEntityManager();
        foreach ($planes as $plan) {
            $ids[] = $plan['id'];
        }

        $query = $entityManager->createQuery(
                'SELECT
                    pl.id plan, ca.nombre, ca.estatus, de.detalle
                FROM
                    App\Entity\Plan pl,
                    App\Entity\PlanDetalle de,
                    App\Entity\PlanCaracteristica ca
                WHERE
                    pl.id IN ( :ids ) AND
                    pl.id = de.plan AND
                    de.caracteristica = ca.id AND
                    ca.estatus IN ( :estatus )
                 ORDER BY ca.nombre ASC'
            )
            ->setParameter('ids', $ids);

        $query->setParameter('estatus', $estatus);

        $detalles = $query->execute();

        if(!empty($detalles)) {
            foreach ($planes as &$plan) {
                foreach ($detalles as $detalle) {
                    if($plan['id'] == $detalle['plan']) {
                        $plan['detalles'][] = $detalle;
                    }
                }
            }
        }
    }

}