<?php

namespace App\DAO;

use App\DAO\DAOResponse\DAOResponse;
use App\DAO\DAOResponse\DAOResponseInterface;
use App\DAO\Pagination\Pagination;
use App\Entities\AtendimentoFormulario\AtendimentoFormulario;
use App\Entities\Formulario\Formulario;
use App\Entities\OpcaoPergunta\OpcaoPergunta;
use App\Entities\ProcedimentoFormulario\ProcedimentoFormulario;
use App\Mappers\FormularioMapper;
use App\Models\AtendimentoFormularioModel;
use App\Models\FormularioModel;
use App\Models\OpcaoPerguntaModel;
use App\Models\PerguntaModel;
use App\Models\ProcedimentoFormularioModel;
use App\Services\LogService;
use App\Utils\Collections\GeneralCollection;
use App\Utils\Collections\QueryFilterCollection;
use App\Utils\Collections\QueryOrderCollection;
use App\ViewModels\Consulta\ConsultaRegistrationViewModel\RespostaEscolhaOpcao;
use App\ViewModels\Consulta\ConsultaRegistrationViewModel\RespostaFormularioCollection;
use App\ViewModels\Formulario\FormularioRegistrationViewModel\FormularioRegistrationViewModel;
use App\ViewModels\Formulario\FormularioRegistrationViewModel\PerguntaRegistration;
use App\ViewModels\Formulario\FormularioUpdateViewModel\FormularioUpdateViewModel;
use Exception;

class FormularioDAO extends DAO {

	public function registerFormulario(FormularioRegistrationViewModel $formulario_registration_vm): DAOResponse {

		$dao_response = new DAOResponse();
		try {
			$this->db->transStart();
			$formulario = $this->persistFormularioFromFormularioRegistrationVM( $formulario_registration_vm );
			$pergunta_collection = $this->persistPerguntasFromFormularioRegistrationVM( $formulario, $formulario_registration_vm );
			$procedimento_formulario = $this->persistProcedimentoFromFormularioRegistrationVM( $formulario, $formulario_registration_vm );
            if($formulario_registration_vm->getAtendimento() == true):
			    $atendimento_formulario = $this->persistAtendimentoFromFormularioRegistrationVM( $formulario, $formulario_registration_vm );
            endif;
			$error = $this->db->error();
			if ($error && $error ['code']) {
				$this->setDatabaseErrorsForFormularioRegistration( $dao_response, $error );
			} else {
				$this->db->transComplete();
				$dao_response->setStatus( DAOResponseInterface::SUCESS );
			}
		} catch ( Exception $exception ) {
			$this->setDatabaseErrorsForFormularioRegistration( $dao_response, $this->db->error() );
		}
		return $dao_response;

	}

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

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

	}

	private function persistFormularioFromFormularioRegistrationVM(FormularioRegistrationViewModel $formulario_registration_vm): Formulario {

		$formulario = FormularioMapper::getFormularioFromFormularioRegistrationVM( $formulario_registration_vm );
		$formulario_model = new FormularioModel();
		$formulario_model->registerFormulario( $formulario );
		if (!$formulario->getId()) {
			throw new Exception( 'O cadastro de formulario não teve êxito' );
		}
		return $formulario;

	}

	private function persistProcedimentoFromFormularioRegistrationVM(Formulario $formulario, FormularioRegistrationViewModel $formulario_registration_vm): GeneralCollection {

		$procedimento_formulario_model = new ProcedimentoFormularioModel();
		$formulario_collection = new GeneralCollection();
		foreach ( $formulario_registration_vm->getProcedimentos() as $opcao_text ) {
			$formulario_collection->push( 
				ProcedimentoFormulario::builder()->setIdPROCEDIMENTO( $opcao_text )
					->setIdFORMULARIO( $formulario->getId() )
					->build() );
		}
		if ($formulario_collection->length > 0 && !$procedimento_formulario_model->registerOpcaoPerguntaCollection( $formulario_collection )) {
			throw new Exception( 'O cadastro de procedimento_formulario não teve êxito' );
		}
		return $formulario_collection;

	}

    private function persistAtendimentoFromFormularioRegistrationVM(Formulario $formulario, FormularioRegistrationViewModel $formulario_registration_vm): GeneralCollection {

        $atendimento_formulario_model = new AtendimentoFormularioModel();
        $formulario_collection = new GeneralCollection();
        $formulario_collection->push(
            AtendimentoFormulario::builder()
                ->setIdFormulario( $formulario->getId() )
                ->build() );
        if ($formulario_collection->length > 0 && !$atendimento_formulario_model->registerAtendimentoFormularioCollection( $formulario_collection )) {
            throw new Exception( 'O cadastro de atendimento_formulario não teve êxito' );
        }
        return $formulario_collection;

    }

	private function persistPerguntasFromFormularioRegistrationVM(Formulario $formulario, FormularioRegistrationViewModel $formulario_registration_vm): GeneralCollection {

		$pergunta_model = new PerguntaModel();
		$opcao_pergunta_model = new OpcaoPerguntaModel();
		$pergunta_collection = new GeneralCollection();
		$formulario_registration_vm->getPerguntas()
			->forEach( 
			function (PerguntaRegistration $pergunta_registration) use ($formulario, $pergunta_model, $opcao_pergunta_model, $pergunta_collection) {
				$pergunta = FormularioMapper::getPerguntaFromFormularioRegistrationVM( $formulario, $pergunta_registration );
				$pergunta_model->registerPergunta( $pergunta );
				if ($pergunta->getId()) {
					$pergunta_collection->push( $pergunta );
					$opcao_pergunta_collection = new GeneralCollection();
					foreach ( $pergunta_registration->getOpcoes() as $opcao_text ) {
						$opcao_pergunta_collection->push( OpcaoPergunta::builder()->setDescricao( $opcao_text )
							->setIdPERGUNTA( $pergunta->getId() )
							->build() );
					}
					if ($opcao_pergunta_collection->length > 0 && !$opcao_pergunta_model->registerOpcaoPerguntaCollection( $opcao_pergunta_collection )) {
						throw new Exception( 'O cadastro de opcao_pergunta não teve êxito' );
					}
				} else {
					throw new Exception( 'O cadastro de pergunta não teve êxito' );
				}
			} );
		return $pergunta_collection;

	}

	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'], 'id_medico' ) !== false) {
					$dao_response_errors ['id_medico'] = 'Esse médico não existe.';
				}
				if (strpos( $error ['message'], 'id_tipo_pergunta' ) !== false) {
					$dao_response_errors ['id_tipo_pergunta'] = 'Esse tipo de pergunta não existe.';
				}
			}
		}
		return $dao_response_errors;

	}

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

		$sql = '
            SELECT
                formulario.id as id,
                formulario.titulo as titulo,
                formulario.descricao as descricao,
                formulario.ativo as ativo,
                (SELECT COUNT(pergunta.id) FROM pergunta WHERE pergunta.id_formulario = formulario.id) as quantidade_perguntas
            FROM pergunta
            INNER JOIN formulario ON formulario.id = pergunta.id_formulario
            LEFT JOIN procedimento_formulario ON procedimento_formulario.id_formulario = formulario.id
            WHERE 
                formulario.id_medico = 1
            GROUP BY formulario.id
        ';

		$binds = [ 
			'id_medico' => $id_medico
		];
		return $this->getDAOResponseFromPaginatedSelect( $binds, $pagination, $query_filter_collection, $query_order_collection, $sql,
			function (array $result_array): GeneralCollection {
				return FormularioMapper::getFormularioListingVMCollectionFromArray( $result_array );
			} );

	}

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

		$sql = <<<SQL
			select distinct
				formulario.id as id, formulario.titulo as titulo, formulario.descricao as descricao, formulario.ativo as ativo
				, (SELECT COUNT(pergunta.id) FROM pergunta WHERE pergunta.id_formulario = formulario.id) as quantidade_perguntas
			from  pergunta , formulario
				left join procedimento_formulario ON procedimento_formulario.id_formulario = formulario.id
			where
				formulario.id = pergunta.id_formulario
			GROUP BY formulario.id		
		
		SQL;

		$binds = [ ];
		return $this->getDAOResponseFromPaginatedSelect( $binds, $pagination, $query_filter_collection, $query_order_collection, $sql,
			function (array $result_array): GeneralCollection {
				return FormularioMapper::getFormularioListingVMCollectionFromArray( $result_array );
			} );

	}

	public function getFormularioDetailsByFormularioID(int $id_formulario): DAOResponse {

		$sql = "
            SELECT
                formulario.titulo as formulario_titulo,
                formulario.descricao as formulario_descricao,
                formulario.id_medico as formulario_id_medico,
                formulario.ativo as formulario_ativo,
                procedimento_formulario.id_procedimento as formulario_procedimentos,
                pergunta.id as pergunta_id,
				pergunta.ordem,
				pergunta.ativo,
                pergunta.texto_pergunta as pergunta_texto_pergunta,
                pergunta.id_tipo_pergunta as pergunta_id_tipo_pergunta,
                pergunta.obrigatorio as pergunta_obrigatorio,
                opcao_pergunta.id as opcao_pergunta_id,
                opcao_pergunta.descricao as opcao_pergunta_descricao,
                opcao_pergunta.id_pergunta as opcao_pergunta_id_pergunta,
                formulario_atendimento.id as formulario_atendimento_id
            FROM
                pergunta
            INNER JOIN formulario ON formulario.id = pergunta.id_formulario
            LEFT JOIN procedimento_formulario ON procedimento_formulario.id_formulario = formulario.id
            LEFT JOIN opcao_pergunta ON pergunta.id = opcao_pergunta.id_pergunta
            LEFT JOIN formulario_atendimento ON formulario_atendimento.id_formulario = formulario.id
            WHERE 
            	formulario.id = :id_formulario: 
        ";
		$binds = [ 
			'id_formulario' => $id_formulario
		];
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		$dao_response = new DAOResponse();
		if (count( $result_array )) {
			$formulario_details_vm = FormularioMapper::getFormularioDetailsVMFromArray( $result_array );
			$dao_response->setData( $formulario_details_vm );
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		} else {
			$dao_response->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
		}
		return $dao_response;

	}

	public function getPerguntasByIdAgendamento(int $id_agendamento, $data_post): DAOResponse {

		$sql = "
            SELECT
                pergunta.id as id_pergunta,
                pergunta.obrigatorio as obrigatorio,
                pergunta.id_tipo_pergunta as id_tipo_pergunta,
				pergunta.ordem as ordem, pergunta.ativo as ativo
            FROM
                agendamento
            INNER JOIN procedimento_formulario ON procedimento_formulario.id_procedimento = agendamento.id_procedimento
            LEFT JOIN pergunta ON pergunta.id_formulario = procedimento_formulario.id_formulario 
            WHERE 
                  agendamento.id = :id_agendamento: AND 
                  procedimento_formulario.id_formulario = " . $data_post ["id_formulario"];
		$query_filter_collection = new QueryFilterCollection();
		$query_order_collection = new QueryOrderCollection();
		$binds = [ 
			'id_agendamento' => $id_agendamento
		];
		return $this->getDAOResponseFromMultipleRowSelect( $sql,
			function (array $response_array): GeneralCollection {
				return FormularioMapper::getPerguntaDetailsVMCollectionFromArray( $response_array );
			}, $query_filter_collection, $query_order_collection, $binds );

	}

	public function getPerguntasOpcoesByIdsPerguntas(RespostaFormularioCollection $resposta_formulario_collection) {

		$sql = "
            SELECT
                id as id_opcao_pergunta,
                id_pergunta as id_pergunta
            FROM
                opcao_pergunta
        ";
		$query_filter_collection = new QueryFilterCollection();
		$query_order_collection = new QueryOrderCollection();
		$sql .= "WHERE opcao_pergunta.id_pergunta in (''";
		$resposta_formulario_collection->forEach( 
			function (RespostaEscolhaOpcao $resposta_escolha_opcao, int $index) use (&$sql): void {
				$sql .= ($index > -1 ? ',' : '') . $resposta_escolha_opcao->getIdPergunta();
			} );
		$sql .= ")";


		return $this->getDAOResponseFromMultipleRowSelect( $sql,
			function (array $response_array): GeneralCollection {
				return FormularioMapper::getPerguntaOpcoesVMCollectionFromArray( $response_array );
			}, $query_filter_collection, $query_order_collection, [ ] );

	}

	public function getFormularioFilledByFormularioID(int $id_formulario): DAOResponse {

		$sql = "
            SELECT
                formulario.titulo
            FROM
                formulario, resposta_formulario, pergunta
            WHERE 
                resposta_formulario.id_pergunta = pergunta.id AND 
                pergunta.id_formulario = formulario.id AND
            	formulario.id = :id_formulario: GROUP BY formulario.titulo
        ";

		$binds = [ 
			'id_formulario' => $id_formulario
		];
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		$dao_response = new DAOResponse();
		if (count( $result_array )) {
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		} else {
			$dao_response->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
		}
		return $dao_response;

	}

	public function getFormularioDeleteByFormularioID(int $id_formulario): DAOResponse {

		$this->db->transStart();
		$sql = "
            DELETE opcao_pergunta FROM
                formulario, opcao_pergunta, pergunta
            WHERE 
                opcao_pergunta.id_pergunta = pergunta.id AND 
                pergunta.id_formulario = formulario.id AND
                formulario.id = :id_formulario:";

		$binds = [ 
			'id_formulario' => $id_formulario
		];
		$query = $this->db->query( $sql, $binds );
		$affected_rows = $query->connID->affected_rows;
		$dao_response = new DAOResponse();
		if ($affected_rows < 0) {
			$dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
			return $dao_response;
		} else {
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}

		$sql = "
            DELETE procedimento_formulario FROM procedimento_formulario
            WHERE procedimento_formulario.id_formulario = :id_formulario:";

		$query = $this->db->query( $sql, $binds );
		$affected_rows = $query->connID->affected_rows;
		if ($affected_rows < 0) {
			$dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
			return $dao_response;
		} else {
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}

		$sql = "
            DELETE pergunta FROM pergunta
            WHERE pergunta.id_formulario = :id_formulario:";

		$query = $this->db->query( $sql, $binds );
		$affected_rows = $query->connID->affected_rows;
		if ($affected_rows < 0) {
			$dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
			return $dao_response;
		} else {
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}

        $sql = "
            DELETE formulario_atendimento FROM formulario_atendimento
            WHERE id_formulario = :id_formulario:";

        $query = $this->db->query( $sql, $binds );
        $affected_rows = $query->connID->affected_rows;
        if ($affected_rows < 0) {
            $dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
            return $dao_response;
        } else {
            $dao_response->setStatus( DAOResponseInterface::SUCESS );
        }

		$sql = "
            DELETE formulario FROM formulario
            WHERE formulario.id = :id_formulario:";

		$query = $this->db->query( $sql, $binds );
		$affected_rows = $query->connID->affected_rows;
		if ($affected_rows < 0) {
			$dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
			return $dao_response;
		} else {
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}

		$this->db->transComplete();
		return $dao_response;

	}

	public function getProcedimentoFormularioDeleteByFormularioID(int $id_formulario): DAOResponse {

		$this->db->transStart();
		$sql = "
            DELETE procedimento_formulario FROM procedimento_formulario
            WHERE procedimento_formulario.id_formulario = :id_formulario:";

		$binds = [ 
			'id_formulario' => $id_formulario
		];
		$query = $this->db->query( $sql, $binds );
		$affected_rows = $query->connID->affected_rows;
		$dao_response = new DAOResponse();
		if ($affected_rows < 0) {
			$dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
			return $dao_response;
		} else {
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}

        $sql = "
            DELETE formulario_atendimento FROM formulario_atendimento
            WHERE id_formulario = :id_formulario:";

        $query = $this->db->query( $sql, $binds );
        $affected_rows = $query->connID->affected_rows;
        if ($affected_rows < 0) {
            $dao_response->setStatus( DAOResponseInterface::ERROR_DELETING );
            return $dao_response;
        } else {
            $dao_response->setStatus( DAOResponseInterface::SUCESS );
        }

		$this->db->transComplete();
		return $dao_response;

	}

	public function persistFormularioSimpleUpdateVM(int $id_formulario, FormularioUpdateViewModel $formulario_update_vm): DAOResponse {

		$status_persistence = $this->persistFormularioSimpleFromFormularioUpdateVM( $id_formulario, $formulario_update_vm );
		$error = $this->db->error();
		$this->db->close();
		$dao_response = new DAOResponse();
		if ($error && $error ['code']) {
			$status_response = $error ['code'];
			$dao_response_errors = [ ];
			$dao_response->setErrors( $dao_response_errors );
			(new LogService())->log_error( $status_response, "Ocorreu um erro durante a atualização do formulario $id_formulario",
				$formulario_update_vm->toArray() );
		} else {
			$status_response = $status_persistence ? DAOResponseInterface::SUCESS : DAOResponseInterface::RECORD_NOT_FOUND;
		}
		$dao_response->setStatus( $status_response );
		return $dao_response;

	}

	private function persistFormularioSimpleFromFormularioUpdateVM(int $id_procedimento, FormularioUpdateViewModel $procedimento_update_vm): bool {

		$procedimento_model = new FormularioModel();
		$procedimento = FormularioMapper::getFormularioSimpleFromFormularioUpdateVM( $procedimento_update_vm );
		$sucess_update = $procedimento_model->updateFormularioSimpleByID( $id_procedimento, $procedimento );
		return $sucess_update;

	}

	# ------------------------------------------------------------------------------------------------------------------------#
	public function persistFormularioDesativaVM(int $id_formulario): DAOResponse {

		$dao_response = new DAOResponse();
		$this->db->transStart();
		$status_persistence = $this->persistFomularioDeleteVM( $id_formulario );
		$error = $this->db->error();
		if ($error && $error ['code']) {
			$this->setDatabaseErrorsForFormularioDelete( $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 persistFomularioDeleteVM(int $id_formulario) {

		$endereco_model = new FormularioModel();
		$sucess_update = $endereco_model->updateStatusFormularioSimpleByID( $id_formulario );
		return $sucess_update;

	}

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

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

	}

	public function getListFormulariosByProcedimentoId(int $idProcedimento): DAOResponse {

		$dao_response = new DAOResponse();

		$sql = <<<SQL
			select pf.id_formulario , f.descricao
			from procedimento_formulario pf , formulario f
			where
				pf.id_procedimento = :procedimento_id: 
				and pf.id_formulario  = f.id  
				and f.ativo = 1
		SQL;

		$binds = [ 
			'procedimento_id' => $idProcedimento
		];

		$conn = db_connect();
		$result = $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 );
		} else {
			$dao_response->setData( $result );
			$dao_response->setStatus( DAOResponseInterface::SUCESS );
		}

		return $dao_response;

	}

    public function getListFormulariosByAtendimento(): DAOResponse {

        $dao_response = new DAOResponse();

        $sql = <<<SQL
			select 
			    f.id , 
			    f.descricao 
			from formulario f 
			    inner join formulario_atendimento fa on f.id = fa.id_formulario
			where f.ativo = 1
		SQL;

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

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

        return $dao_response;

    }

}