<?php

class Seguro_Model_ProponentesAbc extends Agro_Db_Table_Abstract {
	protected $_schema = 'seguro';
	protected $_name = 'proponentes_abc';
	protected $_primary = 'id';
	public $_order = 'ds_nome_proponente asc';

    public function existsCpfCnpj(string $cpfCnpj) : bool {
        $sql = $this->select()->setIntegrityCheck(false)
            ->from($this->_schema.'.'.$this->_name)
            ->where("cpf_cnpj = '".preg_replace('/[^a-zA-Z0-9_ %\[\]\(\)%&]/s','', $cpfCnpj)."'");

        $count = $this->fetchAll($sql)->count();

        return $count > 0 ? true : false;
    }

    public function save(array $data) {
        try {
            $row = $data['id'] ? $this->getRow($data['id']) : $this->createRow();
            $row->cpf_cnpj = preg_replace('/[^a-zA-Z0-9_ %\[\]\(\)%&]/s','', $data['cpf_cnpj']);
            $row->dt_vencimento = !empty($data['dt_vencimento']) ? Agro_Util::formatDate($data['dt_vencimento'], Zend_Date::W3C) : null;
            return $row->save();
        } catch (Exception $e) {
            return false;
        }
    }

    public function deleteByCpfCnpj(string $cpfCnpj): bool
    {
        try {
            $this->_db->query("
                DELETE FROM {$this->_schema}.{$this->_name} WHERE cpf_cnpj = '".preg_replace('/[^a-zA-Z0-9_ %\[\]\(\)%&]/s','', $cpfCnpj)."'
            ");
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    public function getData($where = false)
    {
        $select = $this->select();
		$select->setIntegrityCheck(false);
		$select->from(array('pa' => $this->_schema.'.'.$this->_name));
        $select->joinLeft(['p' => 'seguro.proponentes'], "REGEXP_REPLACE(p.cpf_cnpj, '[^a-zA-Z0-9_ %\[\]\(\)%&]', '', 'g') = pa.cpf_cnpj", ['ds_nome_proponente']);
        $select->order('pa.dt_vencimento');

		$this->getWhere($select, $where);
		return $select;
    }

    /**
     * Deleta todos os proponentes que estão com data de vendimento menor que a data atual.
     *
     * @return bool
     */
    public function deleteVencidos(): bool
    {
        try {
            $this->_db->query("DELETE FROM {$this->_schema}.{$this->_name} WHERE dt_vencimento < NOW()");
            return true;
        } catch (\Throwable $th) {
            return false;
        }
    }

    /**
     * Delete todos os registros da tabela
     *
     * @return bool
     */
    public function deleteAll(): bool
    {
        try {
            $this->_db->query("DELETE FROM {$this->_schema}.{$this->_name}");
            return true;
        } catch (\Throwable $th) {
            return false;
        }
    }

    /**
     * Retorna o registro pelo CPF/CNPJ informado
     *
     * @param string $cpfCnpj
     * @return Zend_Db_Table_Row|null
     */
    public function getByCpfCnpj(string $cpfCnpj): ?Zend_Db_Table_Row
    {
        $sql = $this->getData("pa.cpf_cnpj = '{$cpfCnpj}'");
        return $this->fetchRow($sql);
    }
}