<?php

class RefereeModel extends CI_Model


{
    const TABLE_NAME = 'Persona';
    const ALL_CATEGORIES = 0;

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    /**
     * @param $perpage limite de arbitros por pagina
     * @param $segment_uri offset donde obtener los arbitros de la base de datos
     * @return mixed
     */
    public function getRefereesBy($perpage, $segment_uri)
    {
        if ($segment_uri != null) {
            $sql = 'SELECT E.* ,C.descripcion
                FROM Persona E, CategoriaArbitral C
                WHERE E.idCategoriaArbitral IS NOT NULL AND
                      E.idCategoriaArbitral = C.idCategoriaArbitral
                ORDER BY C.idCategoriaArbitral ASC
                 LIMIT ? OFFSET ?
               ';
            $query = $this->db->query($sql, array($perpage, intval($segment_uri)));
        } else {
            $sql = 'SELECT E.* ,C.descripcion
                FROM Persona E, CategoriaArbitral C
                WHERE E.idCategoriaArbitral IS NOT NULL AND
                      E.idCategoriaArbitral = C.idCategoriaArbitral
                ORDER BY C.idCategoriaArbitral ASC
                LIMIT
                ?';
            $query = $this->db->query($sql, array($perpage));
        }

        return $query->result();
    }

    /**
     * @param $email
     * @return bool
     */
    public function getAllReferees()
    {
        $sql = 'SELECT E.* ,C.descripcion 
                FROM Persona E, CategoriaArbitral C
                WHERE E.idCategoriaArbitral IS NOT NULL AND 
                      E.idCategoriaArbitral = C.idCategoriaArbitral
                ORDER BY C.idCategoriaArbitral ASC ';
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function createReferee($dni, $nombre, $apellido1, $apellido2, $idCategoriaArbitral, $paisISOA3, $autonomia,
                                  $provincia, $email)
    {
        $referee = array(
            'dni' => $dni,
            'nombre' => $nombre,
            'apellido1' => $apellido1,
            'apellido2' => $apellido2,
            'paisISOA3' => $paisISOA3,
            'autonomia' => $autonomia,
            'provincia' => $provincia,
            'email' => $email,
            'idCategoriaArbitral' => $idCategoriaArbitral
        );

        return $this->db->insert(self::TABLE_NAME, $referee);
    }
    public function countRefereesBy($category) {
        if ($category == self::ALL_CATEGORIES) return sizeof($this->getAllReferees());

        else {
            $sql = 'SELECT A.* ,C.descripcion
               FROM Persona A,CategoriaArbitral C
               WHERE A.idCategoriaArbitral = C.idCategoriaArbitral AND A.idCategoriaArbitral = ?';
            $query = $this->db->query($sql, array($category));

            return $query->num_rows();
        }

    }
    public function getReferees($categorie, $perpage, $offset)
    {
//        if ($categorie == self::ALL_CATEGORIES) return $this->getAllReferees();
//
//        $sql = 'SELECT A.* ,C.descripcion
//               FROM Persona A,CategoriaArbitral C
//               WHERE A.idCategoriaArbitral = C.idCategoriaArbitral AND A.idCategoriaArbitral = ?';
//        $query = $this->db->query($sql, array($categorie));
//
//        return $query->result();

        if ($categorie == self::ALL_CATEGORIES) {
            $sql = 'SELECT A.* ,C.descripcion
               FROM Persona A,CategoriaArbitral C
               WHERE A.idCategoriaArbitral = C.idCategoriaArbitral AND A.idCategoriaArbitral like ?
                limit ? OFFSET ?';
            $offset = $offset == null ? 0 : $offset;
            $query = $this->db->query($sql, array('%', $perpage, intval($offset)));
        } else {
            $sql = 'SELECT A.* ,C.descripcion
               FROM Persona A,CategoriaArbitral C
              WHERE A.idCategoriaArbitral = C.idCategoriaArbitral AND A.idCategoriaArbitral = ?
                limit ? OFFSET ?';
            $query = $this->db->query($sql, array($categorie, $perpage, intval($offset)));
        }

        return $query->result();

    }

    public function addRefereeCategory($idRefereeCategory, $dni)
    {
        $data = array(
            'idCategoriaArbitral' => $idRefereeCategory
        );
        $this->db->where('dni', $dni);
        return $this->db->update('Persona', $data);
    }

    public function updateReferee($dni, $nombre, $apellido1, $apellido2, $idCategoriaArbitral, $paisISOA3, $autonomia,
                                  $provincia, $email)
    {

        $referee = array(
            'dni' => $dni,
            'nombre' => $nombre,
            'apellido1' => $apellido1,
            'apellido2' => $apellido2,
            'paisISOA3' => $paisISOA3,
            'autonomia' => $autonomia,
            'provincia' => $provincia,
            'idCategoriaArbitral' => $idCategoriaArbitral,
            'email' => $email
        );
        $this->db->where('dni', $dni);
        return $this->db->update(self::TABLE_NAME, $referee);

    }

    public function deleteReferee($dni)
    {
        $this->db->where('dni', $dni);
        $refere = array(
            'idcategoriaArbitral' => null
        );
        return $this->db->update(self::TABLE_NAME, $refere);
    }

    public function existsDNI($dni)
    {
        $sql = 'SELECT * FROM Persona P WHERE P.dni = ?';
        $query = $this->db->query($sql, array($dni));

        return $query->num_rows() != 0;
    }

    public function hasRefereeCategory($dni)
    {
        $sql = 'SELECT A.* FROM Persona A WHERE A.dni = ? and A.idCategoriaArbitral is not null';
        $query = $this->db->query($sql, array($dni));
        return $query->num_rows() != 0;
    }


}