<?php

namespace Teknisa\Libs\Util\Repositories;

use Doctrine\DBAL\Connection;
use Teknisa\Libs\Util\Utilities;

class Migration {
    const INSERT_LAST_MIG_QUERY = <<<SQL
INSERT INTO VERSAOOBJETO (NMOBJETOBD, NRVEROBJBD, NRORG, DTINCLUSAO, CDOPERINCLUSAO, NRORGINCLUSAO)
     VALUES (:NMOBJETOBD, :NRVEROBJBD, :NRORG, SYSDATE, :CDOPERADOR, :NRORG)
SQL;

    const UPDATE_LAST_MIG_QUERY = <<<SQL
UPDATE VERSAOOBJETO
   SET NRVEROBJBD   = :NRVEROBJBD,
       DSARQPENDMIG = :DSARQPENDMIG,
       CDOPERULTATU = :CDOPERADOR,
       NRORGULTATU  = :NRORG,
       DTULTATU     = SYSDATE
 WHERE NMOBJETOBD = :NMOBJETOBD
   AND TO_NUMBER(NRVEROBJBD) < TO_NUMBER(:NRVEROBJBD)
SQL;

    const PENDENT_FILE_MIG_QUERY = <<<SQL
                  UPDATE VERSAOOBJETO
    SET DSARQPENDMIG = :DSARQPENDMIG,
        CDOPERULTATU = :CDOPERADOR,
        NRORGULTATU  = :NRORG,
        DTULTATU     = SYSDATE
  WHERE NMOBJETOBD = :NMOBJETOBD
SQL;

  const UPDATE_VERSAOOBJETO = <<<SQL
    ALTER TABLE VERSAOOBJETO ADD DSARQPENDMIG CLOB
	/
	ALTER TABLE VERSAOOBJETO ADD NRORG NUMBER
	/
	ALTER TABLE VERSAOOBJETO ADD IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL
	/
	ALTER TABLE VERSAOOBJETO ADD DTINCLUSAO DATE
	/
	ALTER TABLE VERSAOOBJETO ADD DTULTATU DATE
	/
	ALTER TABLE VERSAOOBJETO ADD NRORGINCLUSAO NUMBER
	/
	ALTER TABLE VERSAOOBJETO ADD CDOPERINCLUSAO VARCHAR2(12)
	/
	ALTER TABLE VERSAOOBJETO ADD NRORGULTATU NUMBER
	/
	ALTER TABLE VERSAOOBJETO ADD CDOPERULTATU VARCHAR2(12)
SQL;

    /**
     * @param $nmObject
     * @param $customEntityManager
     * @return array
     */
    public static function getLastAppliedMigration($nmObject, $customEntityManager) {
        $query = <<<SQL
                  SELECT NMOBJETOBD AS "NMOBJETOBD", NRVEROBJBD AS "NRVEROBJBD", NRORG AS "NRORG", DSARQPENDMIG AS "DSARQPENDMIG"
                    FROM VERSAOOBJETO
                   WHERE NMOBJETOBD = :NMOBJETOBD
                ORDER BY DTULTATU DESC
SQL;

        $params = array( 'NMOBJETOBD' => $nmObject );
        $entityManager = $customEntityManager ? $customEntityManager : Utilities::getEntityManager();
        return $entityManager->getConnection()->fetchAllAssociative($query, $params);
    }

    /**
     * @param $script
     * @param $customEntityManager
     * @return \Doctrine\DBAL\Driver\Statement
     * @throws \Doctrine\DBAL\DBALException
     */
    public static function executeScript($script, $customEntityManager) {
        $entityManager = $customEntityManager ? $customEntityManager : Utilities::getEntityManager();
        return $entityManager->getConnection()->executeQuery($script);
    }

    /**
     * @param $customEntityManager
     * @return \Doctrine\DBAL\Driver\Statement
     * @throws \Doctrine\DBAL\DBALException
     */
    public static function executeUpdateVersaoObjetoScript($customEntityManager) {
        $entityManager = $customEntityManager ? $customEntityManager : Utilities::getEntityManager();
        return $entityManager->getConnection()->executeQuery(self::UPDATE_VERSAOOBJETO);
    }

    /**
     * @param $nmObject
     * @param $fileNumber
     * @param $nrOrg
     * @param $cdOperador
     * @param $customEntityManager
     * @param $pendentFiles
     * @return \Doctrine\DBAL\Driver\Statement
     * @throws \Doctrine\DBAL\DBALException
     */
    public static function insertLastMigration($nmObject, $fileNumber, $nrOrg, $cdOperador, $customEntityManager, $pendentFiles = '[]') {
        $params = array(
            'NMOBJETOBD'   => $nmObject,
            'NRVEROBJBD'   => $fileNumber,
            'NRORG'        => $nrOrg,
            'CDOPERADOR'   => $cdOperador,
            'DSARQPENDMIG' => $pendentFiles
        );
        $entityManager = $customEntityManager ? $customEntityManager : Utilities::getEntityManager();
        return $entityManager->getConnection()->executeQuery(self::INSERT_LAST_MIG_QUERY, $params);
    }

    /**
     * @param $nmObject
     * @param $fileNumber
     * @param $nrOrg
     * @param $cdOperador
     * @param $customEntityManager
     * @param $pendentFiles
     * @return \Doctrine\DBAL\Driver\Statement
     * @throws \Doctrine\DBAL\DBALException
     */
    public static function updateLastMigration($nmObject, $fileNumber, $nrOrg, $cdOperador, $customEntityManager, $pendentFiles = '[]') {
        $params = array(
            'NMOBJETOBD'   => $nmObject,
            'NRVEROBJBD'   => $fileNumber,
            'NRORG'        => $nrOrg,
            'CDOPERADOR'   => $cdOperador,
            'DSARQPENDMIG' => $pendentFiles
        );
        $entityManager = $customEntityManager ? $customEntityManager : Utilities::getEntityManager();
        return $entityManager->getConnection()->executeQuery(self::UPDATE_LAST_MIG_QUERY, $params);
    }
}