<?php

namespace App\DAO;

use App\DAO\DAOResponse\DAOResponse;
use App\DAO\DAOResponse\DAOResponseInterface;
use App\DAO\Pagination\Pagination;
use App\Entities\CEP\CEP;
use App\Entities\Cliente\Cliente;
use App\Entities\ConfirmacaoEmail\ConfirmacaoEmail;
use App\Entities\Endereco\Endereco;
use App\Entities\Pet\Pet;
use App\Entities\Paciente\Paciente;
use App\Entities\Pessoa\Pessoa;
use App\Entities\PessoaFisica\PessoaFisica;
use App\Entities\PetCliente\PetCliente;
use App\Entities\Profissional\Profissional;
use App\Entities\ProfissionalClinica\ProfissionalClinica;
use App\Entities\Usuario\Usuario;
use App\Entities\UsuarioPerfil\UsuarioPerfil;
use App\Mappers\PetMapper;
use App\Models\CEPModel;
use App\Models\ClienteModel;
use App\Models\ConfirmacaoEmailModel;
use App\Models\EnderecoModel;
use App\Models\PetClienteModel;
use App\Models\PetModel;
use App\Models\PacienteModel;
use App\Models\PessoaFisicaModel;
use App\Models\PessoaModel;
use App\Models\ProfissionalClinicaModel;
use App\Models\ProfissionalModel;
use App\Models\UsuarioModel;
use App\Models\UsuarioPerfilModel;
use App\Services\LogService;
use App\Services\ServiceResponse\ServiceResponseInterface;
use App\Utils\Collections\QueryFilterCollection;
use App\Utils\Collections\QueryOrderCollection;
use App\ViewModels\Pet\PetRegistrationViewModel\PetRegistrationViewModel;
use App\ViewModels\Pet\PetUpdateViewModel\PetUpdateViewModel;
use Exception;

class PetDAO extends DAO {

	public function persistPetRegistrationVM(PetRegistrationViewModel $pet_registration_vm): DAOResponse {

		$dao_response = new DAOResponse();
		try {
			$this->db->transStart();
			$pet = $this->persistPetFromPetRegistrationVM( $pet_registration_vm );
			$pet_cliente = $this->persistPetClienteFromPetRegistrationVM( $pet_registration_vm, $pet );

			$error = $this->db->error();
			if ($error && $error ['code']) {
				$this->setDatabaseErrorsForPetRegistration( $dao_response, $error );
			} else {
				$dao_response->setStatus( DAOResponseInterface::SUCESS );
				$dao_response->setData( $pet );
				$this->db->transComplete();
			}
		} catch ( Exception $exception ) {
			$this->setDatabaseErrorsForPetRegistration( $dao_response, $this->db->error() );
		}
		return $dao_response;

	}

	private function setDatabaseErrorsForPetRegistration(DAOResponse $dao_response, array $error): void {

		if ($error ['code']) {
			$dao_response->setStatus( $error ['code'] );
			$dao_response->setErrors( $this->getDatabaseResponseErrorsForPersistence( $error ) );
		} else {
			$dao_response->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
		}
		log_message( 'error', json_encode( $error ) );

	}

	private function persistPetFromPetRegistrationVM(PetRegistrationViewModel &$pet_registration_vm): Pet {

		$pet_model = new PetModel();
		$pet = PetMapper::getPetFromPetRegistrationVM( $pet_registration_vm );
		$pet_model->registerPet( $pet );
		if (!$pet->getId()) {
			throw new Exception( 'O cadastro de pet não teve êxito' );
		}
		return $pet;

	}

	private function persistPetClienteFromPetRegistrationVM(PetRegistrationViewModel &$pet_registration_vm, Pet $pet): PetCliente {

		$pet_model = new PetClienteModel();
		$pet_cliente = PetMapper::getPetClienteFromPetRegistrationVM( $pet_registration_vm, $pet );
		$pet_model->registerPetCliente( $pet_cliente );
		if (!$pet_cliente->getId()) {
			throw new Exception( 'O cadastro de pet_cliente não teve êxito' );
		}
		return $pet_cliente;

	}

	public function getPetList(?Pagination $pagination, ?QueryFilterCollection $query_filter_collection, ?QueryOrderCollection $query_order_collection = null): DAOResponse {

		// $sql = '
		// SELECT
		// pet.id as id_pet , pet.nome as nome_pet , pet.data_nascimento as data_nascimento
		// , pet.cor, pet.sexo as sexo, pet.castrado as castrado, pet.porte as porte
		// , pet.observacoes,pet.numero_chip as numero_chip
		// , pet.rga_pedigree as rga_pedigree, pet.doado as doado,pet.doado as doado
		// , especie.nome as nome_especie
		// , raca.nome as nome_raca
		// , raca.id as id_raca
		// , cliente.id as id_cliente
		// , pessoa_fisica.nome as nome
		// , pessoa_fisica.cpf as cpf
		// , pet_foto.foto_path
		// , paciente.id as id_paciente
		// FROM usuario, raca, especie , pet_cliente ,cliente, pessoa_fisica , paciente, pet
		// LEFT JOIN pet_foto on pet_foto.id_pet = pet.id
		// where
		// pet.id_raca = raca.id
		// and raca.id_especie = especie.id
		// and pet.id = pet_cliente.id_pet
		// and pet_cliente.id_cliente = cliente.id
		// and cliente.id_pessoa = pessoa_fisica.id_pessoa
		// and usuario.id_pessoa = pessoa_fisica.id_pessoa
		// and paciente.id_pessoa = pessoa_fisica.id_pessoa
		// ';
		$sql = <<<SQL
			SELECT
				pet.id as id_pet 
			    , pet.nome as nome_pet 
			    , pet.data_nascimento as data_nascimento
				, pet.cor
			    , pet.sexo as sexo
			    , pet.castrado as castrado
			    , pet.porte as porte
				, pet.observacoes
			    , pet.numero_chip as numero_chip
				, pet.rga_pedigree as rga_pedigree
			    , pet.doado as doado
				, especie.nome as nome_especie
				, raca.nome as nome_raca
			    , raca.id as id_raca
				, cliente.id as id_cliente
				, pessoa_fisica.nome as nome
			    , pessoa_fisica.cpf as cpf
				, '' as foto_path
				, paciente.id as id_paciente
			FROM
				pet , raca , especie, pet_cliente, cliente , 
				pessoa_fisica, paciente, usuario
			where
				pet.id_raca = raca.id
				and raca.id_especie = especie.id
				and pet.id = pet_cliente.id_pet
				and pet_cliente.id_cliente = cliente.id
				and cliente.id_pessoa = pessoa_fisica.id_pessoa
				and cliente.id_pessoa = paciente.id_pessoa
			    and usuario.id_pessoa = cliente.id_pessoa
		SQL;
		$binds = [ ];
		return $this->getDAOResponseFromPaginatedSelect( $binds, $pagination, $query_filter_collection, $query_order_collection, $sql,
			function (array $result_array) {
				return PetMapper::getPetDetailsVMListFromArray( $result_array );
			} );

	}

	public function getPetListByTutorId(?Pagination $pagination, ?QueryFilterCollection $query_filter_collection,
			?QueryOrderCollection $query_order_collection = null): DAOResponse {

		$sql = '
            SELECT 
            pet.id as id_pet,
            pet.nome as nome_pet,
            pet.data_nascimento as data_nascimento,
            pet.cor as cor,
            pet.sexo as sexo,
            pet.castrado as castrado,
            pet.numero_chip as numero_chip,
            pet.rga_pedigree as rga_pedigree,
            pet.doado as doado,
            pet.nome as nome_pet,
            pet.porte as porte,
            pet.observacoes as observacoes,
            raca.id as id_raca,
            raca.nome as nome_raca,
            especie.id as id_especie,
            especie.nome as nome_especie,
            pet_cliente.id_cliente  as id_cliente,
            pessoa_fisica.nome,
            pessoa_fisica.cpf,
            paciente.id as id_paciente
            FROM pet
            INNER JOIN pet_status on pet_status.id = pet.id_pet_status
            LEFT JOIN pet_foto on pet_foto.id_pet = pet.id 
            INNER JOIN raca on raca.id  = pet.id_raca 
            INNER JOIN especie on especie.id = raca.id_especie 
            INNER JOIN pet_cliente on pet_cliente.id_pet = pet.id
            INNER JOIN cliente on pet_cliente.id_cliente = cliente.id 
            INNER JOIN pessoa_fisica on cliente.id_pessoa = pessoa_fisica.id_pessoa 
            INNER JOIN paciente on paciente.id_pessoa = pessoa_fisica.id_pessoa 
        ';
		$binds = [ ];
		return $this->getDAOResponseFromPaginatedSelect( $binds, $pagination, $query_filter_collection, $query_order_collection, $sql,
			function (array $result_array) {
				return PetMapper::getPetDetailsVMListFromArray( $result_array );
			} );

	}

	public function getPet(QueryFilterCollection $query_filter_collection): DAOResponse {

		$sql = '
            SELECT 
            pet.id as id_pet,
            pet.nome as nome_pet,
            pet.data_nascimento as data_nascimento,
            pet.cor as cor,
            pet.sexo as sexo,
            pet.castrado as castrado,
            pet.numero_chip as numero_chip,
            pet.rga_pedigree as rga_pedigree,
            pet.doado as doado,
            pet.nome as nome_pet,
            pet.porte as porte,
            pet.peso as peso,
            pet.observacoes as observacoes,
            raca.id as id_raca,
            raca.nome as nome_raca,
            especie.id as id_especie,
            especie.nome as nome_especie,
            pet_cliente.id_cliente  as id_cliente,
            pessoa_fisica.nome,
            pessoa_fisica.cpf,
            paciente.id as id_paciente
            FROM pet
            INNER JOIN pet_status on pet_status.id = pet.id_pet_status
            LEFT JOIN pet_foto on pet_foto.id_pet = pet.id 
            INNER JOIN raca on raca.id  = pet.id_raca 
            INNER JOIN especie on especie.id = raca.id_especie 
            INNER JOIN pet_cliente on pet_cliente.id_pet = pet.id
            INNER JOIN cliente on pet_cliente.id_cliente = cliente.id 
            INNER JOIN pessoa_fisica on cliente.id_pessoa = pessoa_fisica.id_pessoa 
            INNER JOIN paciente on paciente.id_pessoa = pessoa_fisica.id_pessoa 
        ';
		$binds = [ ];
		return $this->getDAOResponseFromUniqueRowSelect( $binds, $query_filter_collection, $sql,
			function (array $result_array) {
				return PetMapper::getPetDetailsVMFromArray( $result_array [0] );
			} );

	}

	public function getPetByClient(int $id_pet, int $cliente): array {

		$sql = '
            SELECT 
            pet.id as id_pet,
            pet.nome as nome_pet,
            pet.data_nascimento as data_nascimento,
            pet.cor as cor,
            pet.sexo as sexo,
            pet.castrado as castrado,
            pet.numero_chip as numero_chip,
            pet.rga_pedigree as rga_pedigree,
            pet.doado as doado,
            pet.nome as nome_pet,
            pet.porte as porte,
            pet.peso as peso,
            pet.observacoes as observacoes,
            raca.id as id_raca,
            raca.nome as nome_raca,
            especie.id as id_especie,
            especie.nome as nome_especie,
            pet_cliente.id_cliente  as id_cliente,
            pessoa_fisica.nome,
            pessoa_fisica.cpf,
            paciente.id as id_paciente
            FROM pet
            INNER JOIN pet_status on pet_status.id = pet.id_pet_status
            LEFT JOIN pet_foto on pet_foto.id_pet = pet.id 
            INNER JOIN raca on raca.id  = pet.id_raca 
            INNER JOIN especie on especie.id = raca.id_especie 
            INNER JOIN pet_cliente on pet_cliente.id_pet = pet.id
            INNER JOIN cliente on pet_cliente.id_cliente = cliente.id 
            INNER JOIN pessoa_fisica on cliente.id_pessoa = pessoa_fisica.id_pessoa 
            INNER JOIN paciente on paciente.id_pessoa = pessoa_fisica.id_pessoa
            WHERE pet_cliente.id_pet = :id_pet: and pet_cliente.id_cliente = :id_cliente:
        ';
		$binds = [ 
			'id_pet' => $id_pet,
			'id_cliente' => $cliente
		];
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();

		return $result_array;

	}

	private function getDatabaseResponseErrorsForPersistence(array $error): array {

		$dao_response_errors = [ ];
		if ($error && $error ['code']) {
			if ($error ['code'] === DAOResponseInterface::FOREIGN_KEY_DONT_EXISTS) {
				if (strpos( $error ['message'], 'pet_cliente_ibfk_2' ) !== false) {
					$dao_response_errors ['cliente'] = 'Esse cliente não existe.';
				}
				if (strpos( $error ['message'], 'pet_ibfk_2' ) !== false) {
					$dao_response_errors ['status'] = 'Esse status não existe.';
				}
				if (strpos( $error ['message'], 'pet_ibfk_1' ) !== false) {
					$dao_response_errors ['raca'] = 'Essa raça não existe.';
				}
			} else if ($error ['code'] === DAOResponseInterface::DUPLICATED_UNIQUE_KEY) {
				if (strpos( $error ['message'], '\'cpf\'' ) !== false) {
					$dao_response_errors ['cpf'] = 'Uma pessoa com esse CPF já está cadastrada no sistema.';
				}
				if (strpos( $error ['message'], '\'rg\'' ) !== false) {
					$dao_response_errors ['rg'] = 'Uma pessoa com esse RG já está cadastrada no sistema.';
				}
				if (strpos( $error ['message'], '\'email\'' ) !== false) {
					$dao_response_errors ['email'] = 'Uma pessoa com esse email já está cadastrada no sistema.';
				}
			}
		}
		return $dao_response_errors;

	}

	public function verifyPetAndMedicoAreLinked(int $id_medico, int $pet_uid): DAOResponse {

		$sql = '
            SELECT
                1
            FROM pet_medico, pet, profissional, usuario
            WHERE 
                pet_medico.id_pet = pet.id AND
                profissional.id = pet.id_profissional AND
                usuario.id_pessoa = profissional.id_pessoa AND
                usuario.id = :pet_uid: AND
                id_medico = :id_medico:
            LIMIT 1
        ';
		$binds = [ 
			'id_medico' => $id_medico,
			'pet_uid' => $pet_uid
		];
		return $this->getDAOResponseFromUniqueRowSelect( $binds, null, $sql, function (array $result_array): bool {
			return true;
		} );

	}

	public function persistPetUpdateVM(int $id_pet, PetUpdateViewModel $pet_update_vm): DAOResponse {

		$dao_response = new DAOResponse();
		$this->db->transStart();
		$status_persistence = $this->persistPetFromPetUpdateVM( $id_pet, $pet_update_vm );
		$error = $this->db->error();
		if ($error && $error ['code']) {
			$this->setDatabaseErrorsForPetUpdate( $dao_response, $error );
			return $dao_response;
		} else if ($status_persistence) {
			$this->db->transComplete();
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}
		$this->db->close();
		return $dao_response;

	}

	private function setDatabaseErrorsForPetUpdate(DAOResponse $dao_response, array $error): void {

		$dao_response->setStatus( $error ['code'] );
		$dao_response->setErrors( $this->getDatabaseResponseErrorsForPersistence( $error ) );

	}

	private function persistPetFromPetUpdateVM(int $id_pet, PetUpdateViewModel &$pet_update_vm): bool {

		$pet_model = new PetModel();
		$pet = PetMapper::getPetFromPetUpdateVM( $pet_update_vm );
		$sucess_update = $pet_model->updatePetByID( $id_pet, $pet );
		return $sucess_update;

	}

	public function getClienteIdByPetId(int $petid): DAOResponse {

		$dao_response = new DAOResponse();
		$sql = <<<SQL
			select id_cliente from pet_cliente pc where pc.id_pet = :id_pet:	 
		SQL;
		$binds = [ 
			'id_pet' => $petid
		];

		$conn = db_connect();
		$resp = $conn->query( $sql, $binds )
			->getResultArray();
		$errors = $conn->error();

		if ($errors ['code'] != 0) {
			$dao_response->setErrors( $errors );
			$dao_response->setData( [ ] );
			$dao_response->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
			return $dao_response;
		}

		$dao_response->setData( $resp );
		$dao_response->setStatus( DAOResponseInterface::SUCESS );

		return $dao_response;

	}

	public function getTutorIdsByPetId(int $idPet): DAOResponse {

		$dao_response = new DAOResponse();
		$sql = <<<SQL
			select pc.id_pet , pc.id_cliente , c.id_pessoa , p.id as id_paciente
			from pet_cliente pc , cliente c , paciente p
			where pc.id_pet = :id_pet: and pc.id_cliente = c.id and c.id_pessoa = p.id_pessoa 
		SQL;
		$binds = [ 
			'id_pet' => $idPet
		];

		$conn = db_connect();
		$resp = $conn->query( $sql, $binds )
			->getResultArray();
		$errors = $conn->error();

		if ($errors ['code'] != 0) {
			$dao_response->setErrors( $errors );
			$dao_response->setData( [ ] );
			$dao_response->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
			return $dao_response;
		}

		$dao_response->setData( $resp );
		$dao_response->setStatus( DAOResponseInterface::SUCESS );

		return $dao_response;

	}

	public function getImagePet(int $id_pet) {

		$sql = '
            SELECT
                usuario.foto as foto
            FROM pet, pessoa, profissional, pessoa_fisica, usuario, endereco, cep
            WHERE 
                pet.id_profissional = profissional.id AND
                profissional.id_pessoa = pessoa.id AND
                pessoa.id = pessoa_fisica.id_pessoa AND
                pessoa.id = usuario.id_pessoa AND
                pessoa.id_endereco = endereco.id AND
                endereco.cep = cep.id AND
            	usuario.ativo = 1 AND pet.id = :id_pet:
        ';
		$binds = [ 
			'id_pet' => $id_pet
		];
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		return (($result_array [0] ['foto']));

	}

	public function getDadosPetById(int $idPet): DAOResponse {

		$dao_response = new DAOResponse();
		$sql = <<<SQL
			select 
				id,	data_nascimento, id_raca, cor, id_pet_status, sexo, castrado,
				numero_chip, rga_pedigree, doado, observacoes, porte, nome, peso
			from pet
			where id = :id_pet:
		SQL;
		$binds = [ 
			'id_pet' => $idPet
		];

		$conn = db_connect();
		$resp = $conn->query( $sql, $binds )
			->getResultArray();
		$errors = $conn->error();

		if ($errors ['code'] != 0) {
			$dao_response->setErrors( $errors );
			$dao_response->setData( [ ] );
			$dao_response->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
			return $dao_response;
		}

		$dao_response->setData( $resp [0] );
		$dao_response->setStatus( DAOResponseInterface::SUCESS );

		return $dao_response;

	}

	public function getPetFromId(int $id_pet) {

		$sql = '
            SELECT 1
            FROM pet
            WHERE pet.id = :id_pet:
        ';
		$binds = [ 
			'id_pet' => $id_pet
		];
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		return ($result_array);

	}

	public function getDadosCartaoMedicinaCCByPetId(int $id_pet): DAOResponse {

		$daoResponse = new DAOResponse();
		$sql = <<<SQL
			select 
				p.id, p.nome, p.cartao_medicina_carinho , p.telemedicina
			from 
				pet p
			where 
				p.id = :id_pet:
		SQL;
		$binds = [ 
			'id_pet' => $id_pet
		];

		$conn = db_connect();
		$result = $conn->query( $sql, $binds )
			->getResultArray();
		$errors = $conn->error();

		if ($errors ['code'] != 0) {
			$daoResponse->setErrors( $errors );
			$daoResponse->setData( [ ] );
			$daoResponse->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
		} else {
			if (count( $result ) == 0) {
				$daoResponse->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'cartao_mcc' => $result [0]
				] );
			}
		}

		return $daoResponse;

	}

}