<?php

namespace App\Repository;

use App\Entity\Adherent;
use App\Entity\Comptoir;
use App\Entity\Flux;
use App\Entity\Groupe;
use App\Entity\Prestataire;
use App\Entity\Transaction;
use App\Entity\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Flux|null find($id, $lockMode = null, $lockVersion = null)
 * @method Flux|null findOneBy(array $criteria, array $orderBy = null)
 * @method Flux[]    findAll()
 * @method Flux[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class FluxRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Flux::class);
        $em = $this->getEntityManager();
        $this->connection = $em->getConnection();
        $this->tableName = $em->getMetadataFactory()->getMetadataFor(Flux::class)->getTableName();
    }

    /**
     * @param Prestataire $presta     [description]
     * @param string      $from Date from which to fetch the flux
     * @param array       $filter For each element of array, add condition f.key = value
     *
     * @return Query Returns an array of flux ids matching conditions
     */
    private function getQueryByPrestataireCore(Prestataire $presta, $filter = null, $from = null, $to = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.prestataire_id = :id OR f.prestataire_dest_id = :id)";
        if ($from != null) {
            $sqlQuery .= " AND f.created_at >= :from";
        }
        if ($to != null) {
            $sqlQuery .= " AND f.created_at <= :to";
        }
        $i = 0;
        foreach ($filter as $key => $value) {
            if($value === null) { //for convenience
                $sqlQuery .= " AND f." . $key . " IS NULL";
            } else {
                $sqlQuery .= " AND f." . $key . " = :param_$i";
            }
            $i++;
        }
        $statement = $this->connection->prepare($sqlQuery);
        if ($from != null) {
            $statement->bindValue(':from', $from);
        }
        if ($to != null) {
            $statement->bindValue(':to', $to);
        }
        $i = 0;
        foreach ($filter as $key => $value) {
            if($value !== null) {
                $statement->bindValue(":param_$i", $value);
            }
            $i++;
        }
        $statement->bindValue(':id', $presta->getId());
        $statement->execute();
        return $statement->fetchAll();
    }

    /**
     * @param Prestataire $presta     [description]
     * @param integer $m From 1 to 12
     * @param integer $Y Format YYYY
     */
    public function getValidCcasTransactionsByPrestaAndMonth(Prestataire $presta, $m, $Y)
    {
        //It's easier to write full SQL query because we want to join adherent to select and sort by anonymous token
        $sqlQuery = "
            SELECT
                a.anonymous_token as anonymous_token,
                f.montant as montant,
                f.created_at as created_at
            FROM {$this->tableName} f
            JOIN adherent a ON f.adherent_id = a.id
            WHERE (f.prestataire_id = :id OR f.prestataire_dest_id = :id)
            AND f.created_at >= :from
            AND f.created_at <= :to
            AND f.cancellerflux_id IS NULL
            AND f.is_ccas = :is_ccas
            AND f.type = :type
            ORDER BY a.anonymous_token ASC, f.created_at ASC
        ";

        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':id', $presta->getId());

        $from = new \DateTime();
        $from->setDate($Y,$m,1)->setTime(0,0);
        $statement->bindValue(':from', $from->format("Y-m-d H:i:s"));

        $to = new \DateTime();
        $to->setDate($Y,$m+1,0)->setTime(23,59,59);
        $statement->bindValue(':to', $to->format("Y-m-d H:i:s"));

        $statement->bindValue(":is_ccas", true);
        $statement->bindValue(":type", Transaction::TYPE_TRANSACTION_ADHERENT_PRESTATAIRE);

        $statement->execute();
        return $statement->fetchAll();
    }

    /**
     * @param Prestataire $presta     [description]
     * @param integer $m From 1 to 12
     * @param integer $Y Format YYYY
     */
    public function getSumOfValidCcasTransactionsByPrestaAndMonth(Prestataire $presta, $m, $Y)
    {
        $filter = array();
        $filter['is_ccas'] = true;
        $filter['cancellerflux_id'] = null; //exclude cancelled transactions
        $filter['type'] = Transaction::TYPE_TRANSACTION_ADHERENT_PRESTATAIRE; //exclude cancellers
        $from = new \DateTime();
        $from->setDate($Y,$m,1)->setTime(0,0);
        $to = new \DateTime();
        $to->setDate($Y,$m+1,0)->setTime(23,59,59);
        $ids = $this->getQueryByPrestataireCore(
            $presta,
            $filter,
            $from->format("Y-m-d H:i:s"),
            $to->format("Y-m-d H:i:s")
        );

        $qb = $this->createQueryBuilder('f');

        return $qb
            ->select('sum(f.montant)')
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $ids)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
            ->getSingleScalarResult();
    }


    /**
     * @param Prestataire $presta     [description]
     * @param string      $parenttype Parent type of flux (cotisation, transfert, transaction, vente...)
     * @param string      $type Type of flux (cotisation, transfert, transaction, vente...)
     * @param string      $from Date from which to fetch the flux
     *
     * @return Query Returns a query for finding an array of Flux
     */
    public function getQueryByPrestataire(Prestataire $presta, string $parenttype = null, string $type = null, $from = null)
    {
        $filter = array();
        if ($parenttype) {
            $filter['parenttype'] = $parenttype;
        }
        if ($type) {
            $filter['type'] = $type;
        }
        $results = $this->getQueryByPrestataireCore($presta,$filter,$from);
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    /**
     * @param User          $user operator of the flux
     * @param Prestataire   $presta
     * @param string        $from Date from which to fetch the flux
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByCaissier(User $user, Prestataire $presta, $from = true)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f";
        $sqlQuery .= " WHERE ((f.type = 'adherent_prestataire' AND f.prestataire_id = :pid) OR (f.type = 'prestataire_prestataire' AND f.prestataire_dest_id = :pid))";
        $sqlQuery .= " AND f.user_id = :cid";  // set operateur
        if ($from != null) {
            $sqlQuery .= " AND f.created_at >= :from";
        }

        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':pid', $presta->getId());
        $statement->bindValue(':cid', $user->getId());
        if ($from != null) {
            $statement->bindValue(':from', $from);
        }
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    public function getQueryByAdherentAndDestinataire(Adherent $adherent, Prestataire $prestataire, string $type = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.adherent_id = :id AND f.prestataire_id = :presta_id)";
        if ($type != null) {
            $sqlQuery .= " AND f.type = :type";
        }
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':id', $adherent->getId());
        $statement->bindValue(':presta_id', $prestataire->getId());
        if ($type != null) {
            $statement->bindValue(':type', $type);
        }
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
            ->getResult()
        ;
    }

    /**
     * @param Adherent $adherent   [description]
     * @param string   $parenttype Parent type of flux (cotisation, transfert, transaction, vente...)
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByAdherent(Adherent $adherent, $parenttype = null)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE (f.adherent_id = :id OR f.adherent_dest_id = :id)";
        if (null != $parenttype) {
            $sqlQuery .= ' AND f.parenttype = :type';
        }
        $statement = $this->connection->prepare($sqlQuery);
        if (null != $parenttype) {
            $statement->bindValue(':type', $parenttype);
        }
        $statement->bindValue(':id', $adherent->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    /**
     * @param Comptoir $comptoir [description]
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByComptoir(Comptoir $comptoir)
    {
        $statement = $this->connection->prepare("SELECT f.id FROM {$this->tableName} f WHERE f.comptoir_id = :id");
        $statement->bindValue('id', $comptoir->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    /**
     * @param Comptoir $comptoir [description]
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByComptoirParams(Comptoir $comptoir, $params)
    {
        $sql = "SELECT f.id FROM {$this->tableName} f WHERE f.comptoir_id = :id";
        if (isset($param['parenttype'])) {
            $sql .= ' AND f.parenttype = :parenttype';
        }
        if (isset($param['type'])) {
            $sql .= ' AND f.type = :type';
        }
        if (isset($param['start'])) {
            $sql .= ' AND f.created_at > :start';
        }
        if (isset($param['end'])) {
            $sql .= ' AND f.created_at < :end';
        }
        $statement = $this->connection->prepare($sql);
        $statement->bindValue('id', $comptoir->getId());
        if (isset($param['parenttype'])) {
            $statement->bindValue('parenttype', $param['parenttype']);
        }
        if (isset($param['type'])) {
            $statement->bindValue('type', $param['type']);
        }
        if (isset($param['start'])) {
            $statement->bindValue('start', $param['start']);
        }
        if (isset($param['end'])) {
            $statement->bindValue('end', $param['end']);
        }
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    /**
     * @param Groupe $groupe [description]
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByGroupe(Groupe $groupe)
    {
        $statement = $this->connection->prepare("SELECT f.id FROM {$this->tableName} f WHERE f.groupe_id = :id");
        $statement->bindValue('id', $groupe->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    /**
     * @param User $user
     *
     * @return Query Returns a query fo finding an array of Flux
     */
    public function getQueryByUser(User $user)
    {
        $sqlQuery = "SELECT f.id FROM {$this->tableName} f WHERE f.user_id = :id";
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':id', $user->getId());
        $statement->execute();
        $results = $statement->fetchAll();
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->where($qb->expr()->in('f.id', ':ids'))
            ->setParameter('ids', $results)
            ->orderBy('f.createdAt', 'DESC')
            ->getQuery()
        ;
    }

    public function getTotalVenteAchat()
    {
        $qb = $this->createQueryBuilder('f');

        return $qb
            ->select('SUM(f.montant) AS balance')
            ->where("f.type = 'vente' OR f.type = 'achat' OR f.type = 'vente_emlc'")
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * @param Adherent $adherent the user to look the cotisations for
     * @param String $from, $to dates (ISO format) between wich to look for
     * 
     * @return Array list of cotisations between the dates
     */
    public function getTavCotisationsBetweenDates($adherent, $from, $to)
    {
        $sqlQuery = "SELECT f.id
            FROM {$this->tableName} f
            WHERE f.type IN ('achat_monnaie_adherent', 'vente_emlc_adherent')
            AND f.created_at >= :f
            AND f.created_at <= :t
            AND (f.adherent_id = :adh_id OR f.adherent_dest_id = :adh_id)";
        
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':f', $from);
        $statement->bindValue(':t', $to);
        $statement->bindValue(':adh_id', $adherent->getId());
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }

    /**
     * @param Adherent $adherent the user to look the cotisation for
     * 
     * @return 
     */
    public function getLastTavCotisation($adherent)
    {
        $sqlQuery = "SELECT f.created_at
            FROM {$this->tableName} f
            WHERE f.type IN ('achat_monnaie_adherent', 'vente_emlc_adherent')
            AND (f.adherent_id = :adh_id OR f.adherent_dest_id = :adh_id)
            ORDER BY created_at DESC";
        
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':adh_id', $adherent->getId());
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }

    /**
     * @param String $subterritory_name the subterritory name to look for
     * 
     * @return 
     */
    public function getQuerybyAdherentSubterritory($subterritory_name)
    {
        $sqlQuery = "SELECT f.id
            FROM {$this->tableName} f
            LEFT JOIN adherent a ON (a.id = f.adherent_id OR a.id = f.adherent_dest_id)
            LEFT JOIN geoloc g ON a.geoloc_id = g.id
            LEFT JOIN subterritory st ON g.subterritory_id = st.id
            WHERE st.name = :subterritory_name";
        
        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':subterritory_name', $subterritory_name);
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }

    /**
     * @param int $limit, amount to fetch
     * 
     * @return Array list of cotisations
     */
    public function getTavCotisationsWithoutAllocation($limit)
    {
        $sqlQuery = "SELECT f.id, f.montant, f.adherent_id, f.adherent_dest_id
            FROM {$this->tableName} f
            LEFT JOIN allocation a ON a.cotisation_flux_id = f.id
            WHERE f.type IN ('achat_monnaie_adherent', 'vente_emlc_adherent')
            AND a.id IS NULL
            ORDER BY f.created_at DESC";

        if (null !== $limit && 0 !== intval($limit)) {
            $sqlQuery .= " LIMIT " . intval($limit); 
        }
        
        $statement = $this->connection->prepare($sqlQuery);
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }

    /**
     * Given a TAV cotisation flux, get the potential complementary flux that completes the full TAV allocation.
     * Get a flux that's not been already saved as a complementary flux in allocation. 
     * 
     * @see App\Entity\Allocation 
     * 
     * @param int $fluxCotisId, cotisation flux id
     */
    public function getRelatedTavAllocationFlux($fluxCotisId) {
        $sqlQuery = "SELECT fb.id, fb.type, fb.montant
            FROM {$this->tableName} f
            LEFT JOIN flux fb
                ON ABS(TIMESTAMPDIFF(MICROSECOND, f.created_at, fb.created_at)) < 2000000
                AND 
                    (
                        f.adherent_id = fb.adherent_id
                        OR f.adherent_id = fb.adherent_dest_id
                        OR f.adherent_dest_id = fb.adherent_id
                        OR f.adherent_dest_id = fb.adherent_dest_id
                    )
                AND fb.type IN ('prelevement_cotisation_adherent', 'reversement_cotisation_adherent')
            LEFT JOIN allocation a ON a.complementary_flux_id = fb.id
            WHERE f.id = :fluxId
            AND a.id IS NULL";

        $statement = $this->connection->prepare($sqlQuery);
        $statement->bindValue(':fluxId', $fluxCotisId);
        $statement->execute();
        $results = $statement->fetchAll();

        return $results;
    }
}
