<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2012171330 extends AbstractMigration
{
    public function up(): void
    {
    	$this->insertStatus();
    	$this->insertPendencias();
        $this->insertRelatorio();
    	$this->upTables();
        $this->upPlAnularEndosso();
    }

    public function down(): void
    {
        $this->downPlAnularEndosso();
    	$this->downTables();
        $this->deleteRelatorio();
    	$this->deletePendencias();
    	$this->deleteStatus();
    }

    private function insertStatus()
    {
    	$sql = <<<SQL
    		INSERT INTO sistema.tipo_status(ds_tipo_status, ds_chave) VALUES ('Documentos Digitalizados', 'DOCUMENTO_DIGITALIZADO');

    		INSERT INTO sistema.status (ds_status, cd_status, ds_chave, id_tipo_status) VALUES ('Documento Pendente de Envio',   1,  'DOC_PENDENTE_ENVIO',  (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO'));
    		INSERT INTO sistema.status (ds_status, cd_status, ds_chave, id_tipo_status) VALUES ('Documento Anexado',             2,  'DOC_ANEXADO',         (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO'));
    		INSERT INTO sistema.status (ds_status, cd_status, ds_chave, id_tipo_status) VALUES ('Documento Dispensado',          3,  'DOC_DISPENSADO',      (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO'));
    		INSERT INTO sistema.status (ds_status, cd_status, ds_chave, id_tipo_status) VALUES ('Documento Baixado',             4,  'DOC_BAIXADO',         (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO'));
            INSERT INTO sistema.status (ds_status, cd_status, ds_chave, id_tipo_status) VALUES ('Documento Baixado por Chamado', 5,  'DOC_BAIXADO_CHAMADO', (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO'));
            INSERT INTO sistema.status (ds_status, cd_status, ds_chave, id_tipo_status) VALUES ('Documento com Pendência',       6,  'DOC_PENDENCIA',       (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO'));
SQL;

		$this->execute($sql);
    }

    private function deleteStatus()
    {
    	$sql = <<<SQL
    		DELETE FROM sistema.status WHERE id_tipo_status = (SELECT id FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO');
    		DELETE FROM sistema.tipo_status WHERE ds_tipo_status = 'Documentos Digitalizados' AND ds_chave = 'DOCUMENTO_DIGITALIZADO';
SQL;

		$this->execute($sql);
    }

    private function insertPendencias()
    {
    	$sql = <<<SQL
    		INSERT INTO atendimento.tipo_pendencia(ds_tipo_pendencia, ds_chave) VALUES ('Pendência de Documentos Digitalizados da Proposta', 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA');

		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Falta Assinatura',                       'PEND_DOC_DIGITALIZADO_PROPOSTA_FALTA_ASSINATURA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Assinatura incorreta',                   'PEND_DOC_DIGITALIZADO_PROPOSTA_ASSINATURA_INCORRETA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Imagem cortada ou incorreta',            'PEND_DOC_DIGITALIZADO_PROPOSTA_IMAGEM_INCORRETA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Orçamento',                              'PEND_DOC_DIGITALIZADO_PROPOSTA_ORCAMENTO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Documento Rasurado',                     'PEND_DOC_DIGITALIZADO_PROPOSTA_DOCUMENTO_RASURADO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Sem Carimbo Corretor',                   'PEND_DOC_DIGITALIZADO_PROPOSTA_SEM_CARIMBO_CORRETOR');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Falta Procuração',                       'PEND_DOC_DIGITALIZADO_PROPOSTA_FALTA_PROCURACAO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Proposta - Falta Carimbo CNPJ',                     'PEND_DOC_DIGITALIZADO_PROPOSTA_FALTA_CARIMBO_CNPJ');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Falta Assinatura',                  'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_FALTA_ASSINATURA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Assinatura incorreta',              'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_ASSINATURA_INCORRETA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Imagem cortada ou incorreta',       'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_IMAGEM_INCORRETA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Falta Preenchimento',               'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_FALTA_PREENCHIMENTO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Declarações divergentes',           'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_DECLARACOES_DIVERGENTES');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Documento Rasurado',                'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_DOCUMENTO_RASURADO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Falta Procuração',                  'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_FALTA_PROCURACAO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Federal - Falta Carimbo CNPJ',                'PEND_DOC_DIGITALIZADO_TERMO_FEDERAL_FALTA_CARIMBO_CNPJ');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Falta Assinatura',                 'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_FALTA_ASSINATURA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Assinatura incorreta',             'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_ASSINATURA_INCORRETA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Imagem cortada ou incorreta',      'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_IMAGEM_INCORRETA');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Falta Preenchimento',              'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_FALTA_PREENCHIMENTO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Documento Rasurado',               'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_DOCUMENTO_RASURADO');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Falta Carimbo CNPJ',               'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_FALTA_CARIMBO_CNPJ');
		    INSERT INTO atendimento.pendencias (id_tipo_pendencia, ds_nome_pendencia, ds_chave) VALUES ( (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA'), 'Termo Estadual - Falta Assinatura das Testemunhas', 'PEND_DOC_DIGITALIZADO_TERMO_ESTADUAL_FALTA_ASSINATURA_TESTEMUNHAS');
SQL;

		$this->execute($sql);
    }

    private function deletePendencias()
    {
    	$sql = <<<SQL
    		DELETE FROM atendimento.pendencias WHERE id_tipo_pendencia = (SELECT id FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA');
    		DELETE FROM atendimento.tipo_pendencia WHERE ds_chave = 'PENDENCIA_DOCUMENTOS_DIGITALIZADOS_PROPOSTA';
SQL;

		$this->execute($sql);
    }

    private function upTables()
    {
    	$sql = <<<SQL
			CREATE TABLE seguro.propostas_documento_digitalizado (
		                            id SERIAL,
		                            id_proposta INTEGER NOT NULL,
		                        	ds_nome_arquivo VARCHAR(100) NOT NULL,
		                            CONSTRAINT pk_propostas_doc_digitalizado PRIMARY KEY (id),
		                            CONSTRAINT fk_propostas FOREIGN KEY (id_proposta) REFERENCES seguro.propostas (id)
		                        ) INHERITS (public.campos_default);

		    CREATE TABLE seguro.propostas_status_documento_digitalizado (
									id SERIAL,
									id_proposta INTEGER NOT NULL,
 									id_status INTEGER NOT NULL,
 									ds_observacao TEXT,
 									CONSTRAINT pk_propostas_status_documento_digitalizado PRIMARY KEY (id),
 									CONSTRAINT fk_propostas FOREIGN KEY (id_proposta) REFERENCES seguro.propostas (id),
 									CONSTRAINT fk_status FOREIGN KEY (id_status) REFERENCES sistema.status (id)
		    					) INHERITS (public.campos_default);

		    CREATE TABLE seguro.propostas_pendencias_documento_digitalizado (
		    						id SERIAL,
		    						id_proposta_status_documento_digitalizado INT NOT NULL,
		    						id_pendencia INT NOT NULL,
		    						CONSTRAINT pk_propostas_pendencias_documento_digitalizado PRIMARY KEY (id),
 									CONSTRAINT fk_proposta_status_documento_digitalizado FOREIGN KEY (id_proposta_status_documento_digitalizado) REFERENCES seguro.propostas_status_documento_digitalizado (id),
 									CONSTRAINT fk_pendencias FOREIGN KEY (id_pendencia) REFERENCES atendimento.pendencias (id)
		    					) INHERITS (public.campos_default);
SQL;

		$this->execute($sql);
    }

    private function downTables()
    {
    	$sql = <<<SQL
    		DROP TABLE seguro.propostas_pendencias_documento_digitalizado;
    		DROP TABLE seguro.propostas_status_documento_digitalizado;
    		DROP TABLE seguro.propostas_documento_digitalizado;
SQL;

		$this->execute($sql);
    }

    private function insertRelatorio()
    {

        $sql = <<<SQL
        INSERT INTO sistema.acao (ds_label, ds_module, ds_controller, ds_action, ds_param, id_parent, ds_image, ds_descricao, fl_tipo, dt_criacao, id_usuario_criacao, id) 
            VALUES ('Relatório de Controle de Documentos Digitalizados', 'relatorio', 'corretor', 'documentosdigitalizados', 'formdocdigitalizado', 784, '', 'Relatório de Controle de Documentos Digitalizados', 'R', '2021-01-22 15:47:16', 3491, nextval('sistema.acao_id_seq'::regclass));
SQL;
        
        $this->execute($sql);
    }

    private function deleteRelatorio()
    {
        $sql = <<<SQL
            DELETE FROM sistema.acao  
             WHERE ds_label = 'Relatório de Controle de Documentos Digitalizados' 
               AND ds_module = 'relatorio'
               AND ds_controller = 'corretor'
               AND id_usuario_criacao = 3491;

SQL;
        
        $this->execute($sql);
    }

    private function upPlAnularEndosso()
    {
        $sql = <<<SQL

CREATE OR REPLACE FUNCTION manutencao.anular_endosso(idpropostaanulada integer, idstatus integer)
 RETURNS void
 LANGUAGE plpgsql
AS \$function\$
DECLARE
    idPropostaEndossada INTEGER;
    idAvisoSinistro     INTEGER;
    idProcesso          INTEGER;
    stSQL               VARCHAR;
    stSQL2              VARCHAR;
    reRecord            RECORD;
BEGIN
    SELECT id_proposta_endossada
      INTO idPropostaEndossada
      FROM seguro.propostas
     WHERE id = idPropostaAnulada
         ;

    stSQL := 'SELECT seguro.ativa_proposta_status('|| idPropostaAnulada ||','|| idStatus ||');';
    EXECUTE stSQL;
    UPDATE seguro.propostas_endosso SET fl_proposta_vigente = FALSE WHERE id_proposta = idPropostaAnulada;
    UPDATE seguro.propostas_endosso SET fl_proposta_vigente = TRUE  WHERE id_proposta = idPropostaEndossada;
    RAISE NOTICE 'anulada: %, endossada: %', idPropostaAnulada, idPropostaEndossada;

    stSQL2 := '
                SELECT id
                  FROM sinistro.avisos
                 WHERE id_proposta = '|| idPropostaAnulada ||'
              ';
    FOR reRecord IN EXECUTE stSQL2 LOOP
        UPDATE sinistro.avisos SET id_proposta = idPropostaEndossada WHERE id_proposta = idPropostaAnulada AND id = reRecord.id;
    END LOOP;

    stSQL2 := '
                SELECT id
                  FROM sinistro.processos
                 WHERE id_proposta = '|| idPropostaAnulada ||'
              ';
    FOR reRecord IN EXECUTE stSQL2 LOOP
        UPDATE sinistro.processos SET id_proposta = idPropostaEndossada WHERE id_proposta = idPropostaAnulada AND id = reRecord.id;
    END LOOP;


    -- CASO NAO SEJA A PROPOSTA MAE
    PERFORM * FROM seguro.propostas WHERE id = idPropostaAnulada AND id != id_proposta_mae;

    IF FOUND THEN
        -- CASO SEJA UMA ANULACAO
        IF idStatus = 213 THEN
            UPDATE seguro.propostas_status_documento_digitalizado SET fl_ativo = false WHERE id_proposta = idPropostaAnulada;
            INSERT INTO seguro.propostas_status_documento_digitalizado (id_proposta, id_status) 
                VALUES (
                    idPropostaAnulada,
                    (SELECT id FROM sistema.status 
                      WHERE ds_chave = 'DOC_DISPENSADO'
                        AND id_tipo_status = (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DOCUMENTO_DIGITALIZADO')
                    )
                );

        END IF;
    END IF;
END;
\$function\$

SQL;

        $this->execute($sql);
    }

    private function downPlAnularEndosso()
    {
        $sql = <<<SQL
        
CREATE OR REPLACE FUNCTION manutencao.anular_endosso(idpropostaanulada integer, idstatus integer)
 RETURNS void
 LANGUAGE plpgsql
AS \$function\$
DECLARE
    idPropostaEndossada INTEGER;
    idAvisoSinistro     INTEGER;
    idProcesso          INTEGER;
    stSQL               VARCHAR;
    stSQL2              VARCHAR;
    reRecord            RECORD;
BEGIN
    SELECT id_proposta_endossada
      INTO idPropostaEndossada
      FROM seguro.propostas
     WHERE id = idPropostaAnulada
         ;

    stSQL := 'SELECT seguro.ativa_proposta_status('|| idPropostaAnulada ||','|| idStatus ||');';
    EXECUTE stSQL;
    UPDATE seguro.propostas_endosso SET fl_proposta_vigente = FALSE WHERE id_proposta = idPropostaAnulada;
    UPDATE seguro.propostas_endosso SET fl_proposta_vigente = TRUE  WHERE id_proposta = idPropostaEndossada;
    RAISE NOTICE 'anulada: %, endossada: %', idPropostaAnulada, idPropostaEndossada;

    stSQL2 := '
                SELECT id
                  FROM sinistro.avisos
                 WHERE id_proposta = '|| idPropostaAnulada ||'
              ';
    FOR reRecord IN EXECUTE stSQL2 LOOP
        UPDATE sinistro.avisos SET id_proposta = idPropostaEndossada WHERE id_proposta = idPropostaAnulada AND id = reRecord.id;
    END LOOP;

    stSQL2 := '
                SELECT id
                  FROM sinistro.processos
                 WHERE id_proposta = '|| idPropostaAnulada ||'
              ';
    FOR reRecord IN EXECUTE stSQL2 LOOP
        UPDATE sinistro.processos SET id_proposta = idPropostaEndossada WHERE id_proposta = idPropostaAnulada AND id = reRecord.id;
    END LOOP;
END;
\$function\$

SQL;

        $this->execute($sql);
    }
}