<?php

namespace App\Repository;

use App\Entity\Appreciation;
use App\Entity\Goal;
use App\Entity\Target;
use App\Entity\Notation;
use App\Entity\Activity;
use App\Entity\Action;
use App\Entity\Programm;
use App\Entity\Ministry;
use App\Entity\Mainstay;
use App\Entity\Accelerator;
use App\Entity\Costing;
use App\Entity\ActivityPTA;
use App\Entity\ActivityOSC;
use App\Entity\Commune;
use App\Entity\Department;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\ORM\Query\Expr;

/**
 * @method Goal|null find($id, $lockMode = null, $lockVersion = null)
 * @method Goal|null findOneBy(array $criteria, array $orderBy = null)
 * @method Goal[]    findAll()
 * @method Goal[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class GoalRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Goal::class);
    }

    public function getSensitivityByMinistry($year, ?Ministry $ministry, ?Programm $programm, ?Action $action)
    {
        $whereClause = $action ? 'a.id = :action' : ($programm ? 'p.id = :programm' : 'm.id = :ministry');
        $paramClause = $action ? 'action' : ($programm ? 'programm' : 'ministry');
        $paramObject = $action ? $action : ($programm ? $programm : $ministry);

        $qb = $this->createQueryBuilder('g')
            ->select('g.code')
            ->addSelect('g.name')
            ->addSelect('g.color')
            ->addSelect('SUM(n.sensitivity) AS sumSensitivity')
            ->addSelect('COUNT(n.activity) AS nbActivity')
            ->addSelect('SUM(CASE WHEN n.duplicated=false THEN y.amount ELSE 0 END) AS amount')

            ->innerJoin(Target::class, 't', Expr\Join::WITH, 'g.id = t.goal')
            ->innerJoin(Notation::class, 'n', Expr\Join::WITH, 't.id = n.target')
            ->innerJoin(Activity::class, 'y', Expr\Join::WITH, 'y.id = n.activity')
            ->innerJoin(Action::class, 'a', Expr\Join::WITH, 'a.id = y.action')
            ->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')
            ->innerJoin(Ministry::class, 'm', Expr\Join::WITH, 'm.id = p.ministry')

            ->andWhere('n.sensitivity != 0')
            ->andWhere('y.year = :year')
            ->andWhere('g.agenda = 2030')
            ->andWhere($whereClause)

            ->setParameter('year', $year)
            ->setParameter($paramClause, $paramObject)
            ->groupBy('g.id') 
            ->orderBy('g.id', 'ASC')
            ;

        $rows = $qb->getQuery()->getResult();

        foreach ($rows as $key => $row) {
            $rows[$key]['sensitivity'] = $rows[$key]['nbActivity'] ? round($rows[$key]['sumSensitivity'] / $rows[$key]['nbActivity'], 2) : 0;
            $appreciation = $this->getEntityManager()
                ->getRepository(Appreciation::class)
                ->findOneByValue($rows[$key]['sensitivity']);
            $rows[$key]['appreciation'] = $appreciation->getName();
            $rows[$key]['flag'] = $appreciation->getFlag();
        }

        return $rows;
    }

    public function getSensitivityByCommune($year, ?Commune $commune, ?Programm $programm, ?Action $action)
    {
        $whereClause = $action ? 'a.id = :action' : ($programm ? 'p.id = :programm' : 'm.id = :ministry');
        $paramClause = $action ? 'action' : ($programm ? 'programm' : 'ministry');
        $paramObject = $action ? $action : ($programm ? $programm : $commune);

        $qb = $this->createQueryBuilder('g')
            ->select('g.code')
            ->addSelect('g.name')
            ->addSelect('g.color')
            ->addSelect('SUM(n.sensitivity) AS sumSensitivity')
            ->addSelect('COUNT(n.activity) AS nbActivity')
            ->addSelect('SUM(CASE WHEN n.duplicated=false THEN y.amount ELSE 0 END) AS amount')

            ->innerJoin(Target::class, 't', Expr\Join::WITH, 'g.id = t.goal')
            ->innerJoin(Notation::class, 'n', Expr\Join::WITH, 't.id = n.target')
            ->innerJoin(Activity::class, 'y', Expr\Join::WITH, 'y.id = n.activity')
            ->innerJoin(Action::class, 'a', Expr\Join::WITH, 'a.id = y.action')
            ->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')
            ->innerJoin(Commune::class, 'm', Expr\Join::WITH, 'm.id = p.commune')

            ->andWhere('n.sensitivity != 0')
            ->andWhere('y.year = :year')
            ->andWhere('g.agenda = 2030')
            ->andWhere($whereClause)

            ->setParameter('year', $year)
            ->setParameter($paramClause, $paramObject)
            ->groupBy('g.id') 
            ->orderBy('g.id', 'ASC')
            ;

        $rows = $qb->getQuery()->getResult();

        foreach ($rows as $key => $row) {
            $rows[$key]['sensitivity'] = $rows[$key]['nbActivity'] ? round($rows[$key]['sumSensitivity'] / $rows[$key]['nbActivity'], 2) : 0;
            $appreciation = $this->getEntityManager()
                ->getRepository(Appreciation::class)
                ->findOneByValue($rows[$key]['sensitivity']);
            $rows[$key]['appreciation'] = $appreciation->getName();
            $rows[$key]['flag'] = $appreciation->getFlag();
        }

        return $rows;
    }

    public function getSensitivityGlobal($year, $type, ?Ministry $ministry, ?Commune $commune, ?Department $departement)
    {     
       $qb = $this->createQueryBuilder('g')
            ->select('g.code')
            ->addSelect('g.color')
            ->addSelect('SUM(n.sensitivity)/COUNT(n.activity) AS sensitivity')

            ->innerJoin(Target::class, 't', Expr\Join::WITH, 'g.id = t.goal')
            ->innerJoin(Notation::class, 'n', Expr\Join::WITH, 't.id = n.target')
            ->innerJoin(Activity::class, 'y', Expr\Join::WITH, 'y.id = n.activity')
            ->innerJoin(Action::class, 'a', Expr\Join::WITH, 'a.id = y.action')
            ->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')
            ->leftJoin('p.commune', 'c') 

            ->andWhere('y.year = :year')
            ->andWhere('g.agenda = 2030')
            ->andWhere($ministry ? 'p.ministry = :ministry' : '1 = :ministry')
            ->andWhere($commune ? 'p.commune = :commune' : '1 = :commune')
            ->andWhere($departement ? 'c.department = :departement' : '1 = :departement')
            ->andWhere(($type === 'ministere' || $ministry) ? 'p.ministry IS NOT NULL' : '1 = 1')
            ->andWhere(($type === 'commune' || $commune) ? 'p.commune IS NOT NULL' : '1 = 1')
            ->setParameter('year', $year)
            ->setParameter('ministry', $ministry ? $ministry : 1)
            ->setParameter('commune', $commune ? $commune : 1)
            ->setParameter('departement', $departement ? $departement : 1)
            ->groupBy('g.id') 
            ->orderBy('g.id', 'ASC')
            ;

        $rows = $qb->getQuery()->getResult();

        foreach ($rows as $key => $row) {
            $rows[$key]['sensitivity'] = ceil($rows[$key]['sensitivity']);
        }

        return $rows;
    }


    public function getFinancialPTA($year, Mainstay $mainstay)
    {
        $qb = $this->createQueryBuilder('g')
            ->select('g.code')
            ->addSelect('g.name')
            ->addSelect('g.color')
            ->addSelect('SUM(y.expectedCost) AS sumExpectedCost')
            ->addSelect('SUM(y.realizedCost) AS sumRealizedCost')
            ->addSelect('COUNT(y.id) AS nbActivity')

            ->innerJoin(ActivityPTA::class, 'y', Expr\Join::WITH, 'g.id = y.goal')

            //->andWhere('y.executed = 1')
            ->andWhere('y.year = :year')
            ->andWhere('g.agenda = 2030')
            ->andWhere('g.mainstay = :mainstay')
            ->setParameter('year', $year)
            ->setParameter('mainstay', $mainstay)
            ->groupBy('g.id') 
            ->orderBy('g.id', 'ASC')
            ;

        $rows = $qb->getQuery()->getResult();

        foreach ($rows as $key => $row) {
            $rows[$key]['rate'] = $rows[$key]['sumRealizedCost'] / $rows[$key]['sumExpectedCost'] * 100;
            /*$appreciation = $this->getEntityManager()
                ->getRepository(Appreciation::class)
                ->findOneByValue($rows[$key]['sensitivity']);
            $rows[$key]['appreciation'] = $appreciation->getName();
            $rows[$key]['flag'] = $appreciation->getFlag();*/
        }

        return array_reverse($rows);
    }

    public function getFinancialOSC($year, Mainstay $mainstay)
    {
        $qb = $this->createQueryBuilder('g')
            ->select('g.code')
            ->addSelect('g.name')
            ->addSelect('g.color')
            ->addSelect('SUM(y.expectedCost) AS sumExpectedCost')
            ->addSelect('SUM(y.realizedCost) AS sumRealizedCost')
            ->addSelect('COUNT(y.id) AS nbActivity')

            ->innerJoin(ActivityOSC::class, 'y', Expr\Join::WITH, 'g.id = y.goal')

            ->andWhere('y.executed = 1')
            ->andWhere('y.year = :year')
            ->andWhere('g.agenda = 2030')
            ->andWhere('g.mainstay = :mainstay')
            ->setParameter('year', $year)
            ->setParameter('mainstay', $mainstay)
            ->groupBy('g.id') 
            ->orderBy('g.id', 'ASC')
            ;

        $rows = $qb->getQuery()->getResult();

        foreach ($rows as $key => $row) {
            $rows[$key]['rate'] = $rows[$key]['sumRealizedCost'] / $rows[$key]['sumExpectedCost'] * 100;
            /*$appreciation = $this->getEntityManager()
                ->getRepository(Appreciation::class)
                ->findOneByValue($rows[$key]['sensitivity']);
            $rows[$key]['appreciation'] = $appreciation->getName();
            $rows[$key]['flag'] = $appreciation->getFlag();*/
        }

        return array_reverse($rows);
    }

    public function findGoalByCosting($costing) {
        return $this->createQueryBuilder('g')
            ->innerjoin('g.costings', 'c')
            ->andWhere('c.id = :costing')
            ->andWhere('g.agenda = 2030')
            ->setParameter('costing', $costing)
            ->orderBy('g.code', 'ASC')
        ;
    }

    public function findGoalByAccelerator($accelerator) {
        $costings = $accelerator->getCostings();
        return $this->createQueryBuilder('g')
            ->innerjoin('g.costings', 'c')
            ->andWhere('c.id IN (:costings)')
            ->andWhere('g.agenda = 2030')
            ->setParameter('costings', $costings)
            ->orderBy('g.code', 'ASC')
        ;
    }
}