<?php

namespace App\DAO;

use App\DAO\DAO;
use App\DAO\DAOResponse\DAOResponse;
use App\DAO\DAOResponse\DAOResponseInterface;
use App\ViewModels\Voucher\VoucherChangeStatusViewModel\VoucherChangeStatusViewModel;
use App\ViewModels\Voucher\VoucherRegistrationViewModel\VoucherRegistrationViewModel;
use App\Services\JWT\UsuarioLogado;
use App\Utils\DateUtils;

class VoucherDAO extends DAO {

	public function registerVoucher(VoucherRegistrationViewModel $voucher_vm): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
			insert into voucher 
				(id_tipo,id_compra,id_loja_compra_item,serial,id_pet,criado_por,data_criacao,hora_criacao,id_usuario,data_vencimento) values
				(:tipo_id:,:compra_id:, :compra_item_id:,:serial_uuid:, :pet_id:, :criado_por:,:data_c:,:hora_c:,:usuario_id:,:data_v:)
		SQL;

		$binds = [ 
			'tipo_id' => $voucher_vm->getIdTipo(),
			'compra_id' => $voucher_vm->getIdCompra(),
			'compra_item_id' => $voucher_vm->getIdCompraItem(),
			'serial_uuid' => $voucher_vm->getSerial(),
			'pet_id' => $voucher_vm->getIdPet(),
			'criado_por' => $voucher_vm->getCriadoPor(),
			'data_c' => $voucher_vm->getDataCriacao(),
			'hora_c' => $voucher_vm->getHoraCriacao(),
			'usuario_id' => $voucher_vm->getIdUsuario(),
			'data_v' => $voucher_vm->getDataVencimento()
		];

		$conn = db_connect();
		$numrows = $conn->query( $sql, $binds )->numRows;
		$errors = $conn->error();

		if ($errors ['code'] != 0) {
			$daoResponse->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
			$daoResponse->setErrors( $errors );
			$daoResponse->setData( [ ] );
			log_message( "ERROR", json_encode( $errors ) );
		} else {
			$affectrows = $conn->affectedRows();
			if ($affectrows == 0) {
				$daoResponse->setStatus( DAOResponseInterface::INSUFFICIENT_DATA );
				$daoResponse->setData( [ ] );
			} else {
				$newid = $conn->insertID();
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'voucher' => [ 
						'id_voucher' => $newid,
						'serial' => $voucher_vm->getSerial()
					]
				] );
			}
		}

		return $daoResponse;

	}

	public function changeStatusVoucher(VoucherChangeStatusViewModel $voucherChangeStatus_vm): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
			update voucher
				set id_status = :status_id: , alterado_por = :usuario_id:,
					data_alteracao = :data_a:, hora_alteracao = :hora_a:
				where id = :id_voucher:
		SQL;

		$binds = [ 
			'id_voucher' => $voucherChangeStatus_vm->getId(),
			'status_id' => $voucherChangeStatus_vm->getIdStatus(),
			'usuario_id' => $voucherChangeStatus_vm->getAlteradoPor(),
			'data_a' => $voucherChangeStatus_vm->getDataAlteracao(),
			'hora_a' => $voucherChangeStatus_vm->getHoraAlteracao()
		];

		$conn = db_connect();
		$numrows = $conn->query( $sql, $binds )->numRows;
		$errors = $conn->error();

		if ($errors ['code'] != 0) {
			$daoResponse->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
			$daoResponse->setErrors( $errors );
			$daoResponse->setData( [ ] );
			log_message( "ERROR", json_encode( $errors ) );
		} else {
			$affectrows = $conn->affectedRows();
			if ($affectrows == 0) {
				$daoResponse->setStatus( DAOResponseInterface::INSUFFICIENT_DATA );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ ] );
			}
		}

		return $daoResponse;

	}

	public function getVoucherById(int $id_voucher): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
			SELECT DISTINCT
				v.id , v.id_tipo , tv.descricao as tipo_voucher , v.id_status , vs.descricao as voucher_status,
				v.data_criacao, v.data_vencimento , v.serial ,
				v.id_pet , p.nome as pet , v.id_compra , l.id as id_loja , u.descricao as unidade,
				v.id_usuario , pf1.nome as tutor
			FROM
				voucher v, tipo_voucher tv , voucher_status vs, pet p, loja_compra lc , 
				loja l , unidade u,	usuario us1, pessoa_fisica pf1
			WHERE
				v.id = :voucher_id:
				and v.id_tipo = tv.id
				and v.id_status = vs.id
				and v.id_pet = p.id
				and v.id_compra = lc.id
				and v.id_usuario = us1.id
				and us1.id_pessoa = pf1.id_pessoa
				and lc.id_loja = l.id
				and l.id_unidade = u.id 
		SQL;
		$binds = [ 
			'voucher_id' => $id_voucher
		];

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

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

			if (count( $result ) == 0) {
				$daoResponse->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'vouchers' => $result
				] );
			}
		}

		return $daoResponse;

	}

	public function getVoucherListByPetId(int $id_pet): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
			SELECT DISTINCT
				v.id , v.id_tipo , tv.descricao as tipo_voucher , v.id_status , vs.descricao as voucher_status,
				v.data_criacao, v.data_vencimento , v.serial ,
				v.id_pet , p.nome as pet , v.id_compra , l.id as id_loja , u.descricao as unidade,
				v.id_usuario , pf1.nome as tutor
			FROM
				voucher v, tipo_voucher tv , voucher_status vs, pet p, loja_compra lc ,
				loja l , unidade u,	usuario us1, pessoa_fisica pf1
			WHERE
				v.id_pet = :pet_id:				  
				and v.id_tipo = tv.id
				and v.id_status = vs.id
				and v.id_pet = p.id
				and v.id_compra = lc.id
				and v.id_usuario = us1.id
				and us1.id_pessoa = pf1.id_pessoa
				and lc.id_loja = l.id
				and l.id_unidade = u.id
			ORDER BY v.data_criacao DESC
		SQL;
		$binds = [ 
			'pet_id' => $id_pet
		];

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

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

			if (count( $result ) == 0) {
				$daoResponse->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'vouchers' => $result
				] );
			}
		}

		return $daoResponse;

	}

	public function getVoucherAtivosByPetId(int $id_pet): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
			SELECT DISTINCT
				v.id, v.id_pet , p.nome as nome_pet, v.id_tipo ,
				tv.descricao as voucher_tipo, v.serial , v.id_status ,
				vs.descricao as voucher_status, v.data_vencimento
			FROM
				voucher v, pet p, voucher_status vs, tipo_voucher tv
			WHERE
				v.id_pet = :pet_id:
				and v.id_status = 1
				and v.id_pet = p.id
				and v.id_status = vs.id
				and v.id_tipo = tv.id 
		SQL;
		$binds = [ 
			'pet_id' => $id_pet
		];

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

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

			if (count( $result ) == 0) {
				$daoResponse->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'vouchers' => $result
				] );
			}
		}

		return $daoResponse;

	}

	public function getVoucherListByUnidadeId(int $id_unidade): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
		SELECT DISTINCT
			v.id , v.id_tipo , tv.descricao as tipo_voucher , v.id_status , 
			vs.descricao as voucher_status,	v.data_criacao, v.data_vencimento , 
			v.serial , v.id_pet , p.nome as pet , v.id_compra , l.id as id_loja , 
			u.descricao as unidade, v.id_usuario , c.id as id_tutor , pf1.nome as tutor
		FROM
			voucher v, tipo_voucher tv , voucher_status vs, pet p, loja_compra lc ,
			loja l , unidade u,	usuario us, pessoa_fisica pf1 , cliente c
		WHERE
			l.id_unidade = :unidade_id:
			and v.id_tipo = tv.id
			and v.id_status = vs.id
			and v.id_pet = p.id
			and v.id_compra = lc.id
			and v.id_usuario = us.id
			and us.id_pessoa = pf1.id_pessoa
			and us.id_pessoa = c.id_pessoa
			and lc.id_loja = l.id
			and l.id_unidade = u.id
		SQL;
		$binds = [ 
			'unidade_id' => $id_unidade
		];

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

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

			if (count( $result ) == 0) {
				$daoResponse->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'vouchers' => $result
				] );
			}
		}

		return $daoResponse;

	}

	public function getVoucherBySerial($serial): DAOResponse {

		$daoResponse = new DAOResponse();

		$termo = '%' . $serial . '%';
		$sql = <<<SQL
			SELECT DISTINCT
				v.id , v.id_tipo , tv.descricao as tipo_voucher , v.id_status , vs.descricao as voucher_status,
				v.data_criacao, v.data_vencimento , v.serial ,
				v.id_pet , p.nome as pet , v.id_compra , l.id as id_loja , u.descricao as unidade,
				v.id_usuario , pf1.nome as tutor
			FROM
				voucher v, tipo_voucher tv , voucher_status vs, pet p, loja_compra lc ,
				loja l , unidade u,	usuario us1, pessoa_fisica pf1
			WHERE
				v.serial like :serial_id:
				and v.id_tipo = tv.id
				and v.id_status = vs.id
				and v.id_pet = p.id
				and v.id_compra = lc.id
				and v.id_usuario = us1.id
				and us1.id_pessoa = pf1.id_pessoa
				and lc.id_loja = l.id
				and l.id_unidade = u.id
		SQL;
		$binds = [ 
			'serial_id' => $termo
		];

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

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

			if (count( $result ) == 0) {
				$daoResponse->setStatus( DAOResponseInterface::RECORD_NOT_FOUND );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ 
					'vouchers' => $result
				] );
			}
		}

		return $daoResponse;

	}

	public function getNewUUID(): DAOResponse {

		$daoResponse = new DAOResponse();

		$sql = <<<SQL
			select uuid() as seria_uuid
		SQL;

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

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

		return $daoResponse;

	}
	
	public function setVoucherUnsed(int $id_voucher):DAOResponse {
		$daoResponse = new DAOResponse();
		
		$usr = UsuarioLogado::getUsuarioId();
		
		$sql = <<<SQL
			update voucher
				set id_status = 1 , alterado_por = :usuario_id:,
					data_alteracao = :data_a:, hora_alteracao = :hora_a:
				where id = :id_voucher:
		SQL;
		
		$binds = [
				'id_voucher' => $id_voucher,				
				'usuario_id' => $usr,
				'data_a' => DateUtils::today(),
				'hora_a' => DateUtils::now_time()
		];
		
		$conn = db_connect();
		$numrows = $conn->query( $sql, $binds )->numRows;
		$errors = $conn->error();
		
		if ($errors ['code'] != 0) {
			$daoResponse->setStatus( DAOResponseInterface::DATABASE_CONNECTION_ERROR );
			$daoResponse->setErrors( $errors );
			$daoResponse->setData( [ ] );
			log_message( "ERROR", json_encode( $errors ) );
		} else {
			$affectrows = $conn->affectedRows();
			if ($affectrows == 0) {
				$daoResponse->setStatus( DAOResponseInterface::INSUFFICIENT_DATA );
				$daoResponse->setData( [ ] );
			} else {
				$daoResponse->setStatus( DAOResponseInterface::SUCESS );
				$daoResponse->setData( [ ] );
			}
		}
		
		return $daoResponse;
	}

}

