<?php

namespace App\Services;

use App\Data\CheckingAccountData;
use App\Data\CheckingAccountForm;
use App\Data\InstallmentCheckingAccountData;
use App\Data\InstallmentCheckingAccountForm;
use App\Data\LegacySessionData;
use App\Data\LegacyUserValidationData;
use App\Support\OracleDatabase;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;

class OraclePaymentService
{
    public function session($username, $password): LegacyUserValidationData
    {
        if (($username == '' || $password == '') || ($username == '=' || $password == '=')) {
            $obj_session = LegacySessionData::from([
                'logueado' => 'FALSE',
                'mensaje' => 'Usuario y/o Password Vacio',
            ]);

            return LegacyUserValidationData::from(['data_session' => $obj_session]);
        }

        if (strtoupper($username) == 'ADMIN') {
            $userSql = <<<SQL
                SELECT
                    TRIM(codcontri) AS codcontri,
                    TRIM(apepat) AS contrinomb,
                    '' AS contridirec
                FROM SMAREGVEC
                WHERE UPPER(usuario) = UPPER('$username')
                  AND UPPER(contrasena) = UPPER('$password')
            SQL;
        } else {
            $userSql = <<<SQL
                SELECT
                    TRIM(b.codcontri) AS codcontri,
                    TRIM(b.contrinomb) AS contrinomb,
                    NVL2(TRIM(b.contrivias), TRIM(b.contrivias), ' ')
                    || NVL2(TRIM(b.contridire), TRIM(b.contridire), ' ')
                    || NVL2(TRIM(b.contrinume), ' Nro.: '|| TRIM(b.contrinume), ' ')
                    || NVL2(TRIM(b.contrimanz), ' Mz.: '||TRIM(b.contrimanz), ' ')
                    || NVL2(TRIM(b.contrilote), ' Lt.: '||TRIM(b.contrilote), ' ')
                    || NVL2(TRIM(b.contridpto), ' Dpto.: '||TRIM(b.contridpto), ' ')
                    || NVL2(TRIM(b.contriurba), ' Urb.:'||TRIM(b.contriurba), ' ') AS contridirec
                FROM smaregvec a, scontrib b
                WHERE a.codcontri = b.codcontri
                  AND upper(a.usuario) = upper('$username')
                  AND upper(a.contrasena) = upper('$password')
            SQL;
        }

        $userData = OracleDatabase::db()->selectOne($userSql);

        if (! $userData) {
            $obj_session = LegacySessionData::from([
                'mensaje' => 'Usuario y/o Password Invalido',
                'logueado' => 'FALSE',
            ]);

            return LegacyUserValidationData::from(['data_session' => $obj_session]);
        }

        $sequenceSql = "SELECT TO_CHAR(SYSDATE, 'YYYY') || LPAD(SEQ_IDSESSION.NEXTVAL, 6, '0') SECUENCIA FROM DUAL";

        $nextVal = OracleDatabase::db()->selectOne($sequenceSql);

        $cod_contri = $userData->codcontri;

        $obj_session = LegacySessionData::from([
            'mensaje' => 'OK',
            'logueado' => 'TRUE',
            'session' => $nextVal->secuencia,
            'codcontri' => $userData->codcontri,
            'contrinomb' => $userData->contrinomb,
            'contridirec' => $userData->contridirec,
        ]);

        $periodSql = "SELECT DISTINCT anyoimp FROM smvpararbmun WHERE codcontri = '$cod_contri' ORDER BY anyoimp DESC";

        $periodData = OracleDatabase::db()->select($periodSql);

        $anyo = collect($periodData)->map(
            fn ($item) => ['anyo' => $item->anyoimp]
        )->toArray();

        $predioSql = <<<SQL
            SELECT 'TODOS' AS codpredio FROM DUAL
            UNION ALL
            SELECT DISTINCT codpredio FROM SMVPARARBMUN WHERE codcontri = '$cod_contri' ORDER BY codpredio
        SQL;

        $predioData = OracleDatabase::db()->select($predioSql);

        $predio = collect($predioData)->map(
            fn ($item) => ['codpredio' => $item->codpredio]
        )->toArray();

        $tributo = [
            ['espcodigo' => 'TOD', 'espdescri' => 'TODOS'],
            ['espcodigo' => 'IMP', 'espdescri' => 'IMPUESTO PREDIAL'],
            ['espcodigo' => 'ARB', 'espdescri' => 'ARBITRIOS MUNICIPALES'],
        ];

        $periodo = [
            // ['pericodigo' => 'ANU', 'peridescri' => 'ANUAL'],
            // ['pericodigo' => 'TRI', 'peridescri' => 'TRIMESTRAL'],
            ['pericodigo' => 'MES', 'peridescri' => 'MENSUAL'],
        ];

        $estado = [
            ['estcodigo' => 'TOD', 'estdescri' => 'TODOS'],
            ['estcodigo' => 'PEN', 'estdescri' => 'PENDIENTE'],
            ['estcodigo' => 'CAN', 'estdescri' => 'CANCELADO'],
        ];

        $tipofracc = [
            ['tipocod' => 'NOR', 'tipocontr' => 'NORMAL'],
            ['tipocod' => 'FIS', 'tipocontr' => 'FISCALIZADO'],
            ['tipocod' => 'ADM', 'tipocontr' => 'ADMINISTRATIVO'],
        ];

        $lugarpago = [
            ['lgpcodigo' => 'B10', 'lgpdescri' => 'VISANET'],
            ['lgpcodigo' => 'B12', 'lgpdescri' => 'MASTERD CARD'],
        ];

        $resp_transaccion = [
            ['transacodigo' => '2', 'transadescri' => 'PAGO OK'],
            ['transacodigo' => '3', 'transadescri' => 'PAGO NO OK'],
        ];

        $response = [
            'data_session' => $obj_session,
            'data_anyo' => $anyo,
            'data_predio' => $predio,
            'data_tributo' => $tributo,
            'data_periodo' => $periodo,
            'data_estado' => $estado,
            'data_fracc' => $tipofracc,
            'data_lugarpago' => $lugarpago,
            'data_resp_transaccion' => $resp_transaccion,
        ];

        return LegacyUserValidationData::from($response);
    }

    public function checkingAccount(CheckingAccountForm $form): Collection
    {
        ini_set('display_errors', 'On');
        ini_set('display_startup_errors', 'On');
        ini_set('error_reporting', E_ALL);
        ini_set('memory_limit', '1024M');
        ini_set('max_execution_time', 0);

        Log::debug(__METHOD__, ['form' => $form]);

        if (empty($form->periodo_tipo)) {
            $session_ = 'MESONLINE'.$form->session;
        } else {
            $session_ = $form->periodo_tipo.'ONLINE'.$form->session;
        }

        if (empty($form->tributo) || $form->tributo == 'TOD') {
            $tributo_ = 'T';
        }
        if ($form->tributo == 'IMP') {
            $tributo_ = 'I';
        }
        if ($form->tributo == 'ARB') {
            $tributo_ = 'A';
        }

        $filtro = " WHERE IDSESSION = '".$form->session."' AND (AFECTO + COSTOEMI) > 0 ";

        Log::debug(__METHOD__, ['filtro' => $filtro]);

        // $countSql = "SELECT COUNT(1) filas FROM TABLE(ds_saldos.PACK_CTACTE_MDLM_BASE2.FUNC_OUTPUT('".$session_."', '".$form->codcontri."', '2020', '".$form->anyo_fin."', 1, '', '2', '".$tributo_."', SYSDATE))";
        $countSql = <<<'SQL'
            SELECT COUNT(1) filas FROM TABLE(DS_SALDOS.PACK_CTACTE_MDLM_BASE2.FUNC_OUTPUT(:parsession, :parcodcontri, :paranyoini, :paranyofin, :parporcen, :parfechafin, :parchist, :partributo, SYSDATE))
        SQL;

        Log::debug(__METHOD__, ['countSql' => $countSql]);

        $countData = OracleDatabase::db()->selectOne($countSql, [
            'parsession' => $session_,
            'parcodcontri' => $form->codcontri,
            'paranyoini' => $form->anyo_ini,
            'paranyofin' => $form->anyo_fin,
            'parporcen' => 1,
            'parfechafin' => '',
            'parchist' => '2',
            'partributo' => $tributo_,
        ]);

        Log::debug(__METHOD__, ['countData' => $countData]);

        $filas = $countData->filas;

        Log::debug(__METHOD__, ['filas' => $filas]);

        if ($filas == 0) {
            return collect();
        }

        if ($form->codpredio !== '') {
            $filtro = $filtro." AND codpredio = '".$form->codpredio."' ";
        }

        if ($form->estado == 'TOD') {
            $filtro = $filtro.'  ';
        }
        if ($form->estado == 'PEN') {
            $filtro = $filtro.' AND total > 0 ';
        }
        if ($form->estado == 'CAN') {
            $filtro = $filtro.' AND total = 0 ';
        }

        $sql = ' SELECT a.ctaidentif, a.anyoimp, a.tributcodi, a.tributario,  ';
        $sql = $sql.'   a.codpredio, ';
        $sql = $sql."   case when a.tributcodi in ('02.01') then ' ' else b.direccion || ' Uso: [' || USO_PREDIO.USOPRDESC || ']' end direccion,a.periodo,to_char(a.fechvenc,'DD/MM/YYYY')fechvenc, ";
        $sql = $sql."   nvl(estado,' ') estado, ";
        $sql = $sql.'   to_char(round(nvl(a.afecto*100,0),2)) afecto, /*to_char(round(nvl(a.afecto_ppago*100,0),2)) afecto_ppago, */ ';
        $sql = $sql.'   round(nvl(a.pagado*100,0),2)pagado, round(nvl(a.salord*100,0),2)salord,  ';
        $sql = $sql.'   round(nvl(a.mora*100,0),2)mora, round(nvl(a.tmora*100,0),2)tmora, round(nvl(a.treajuste*100,0),2)treajuste,  round(nvl(a.costoemi*100,0),2)costoemi, round(nvl(a.total*100,0),2)total, to_char(round(nvl(a.total_ppago*100,0),2)) total_ppago,';
        $sql = $sql."   round(nvl(c.mon_sr*100,0),2)mon_sr, round(nvl(c.mon_lp*100,0),2)mon_lp, round(nvl(c.mon_pj*100,0),2)mon_pj, nvl(CTAORIGEN,' ')CTAORIGEN  ";
        $sql = $sql.' from ';

        $sql = $sql.' (select ctaidentif, anyoimp, tributcodi, tributario, ';
        $sql = $sql."       (case when tributcodi in ('02.01') then '000000000000' else codpredio end)codpredio,  ";
        $sql = $sql.'       periodo, fechvenc, ';
        // $sql  = $sql."       trim(ETAPA)|| ' ' ||DECODE (CTAESTADO,'2',' Coac.: '||trim(CTANRODOC),'') ||  case when length(trim(comp_resol))>0 then TRIM(comp_resol) else ' ' end estado,";
        $sql = $sql." ' ' estado, ";
        $sql = $sql.'      sum(afecto)afecto, /*0 afecto_ppago,*/ sum(pagado + montofrac + prescri) pagado,  ';
        $sql = $sql."       sum(salord)salord, sum(ipc + mora + reajuste) mora, sum(mora) tmora, sum(reajuste) treajuste,  sum(costoemi)costoemi, sum(total)total, sum(SALDO_PPAGO) TOTAL_PPAGO, nvl(CTAORIGEN,' ')CTAORIGEN  ";
        $sql = $sql.'       from DS_SALDOS.TMPS_CTASDETA   ';
        $sql = $sql.$filtro;
        $sql = $sql.' group by ctaidentif, anyoimp, tributcodi,tributario,  codpredio, periodo, fechvenc, /*estado, */CTAORIGEN, ETAPA  ,CTAESTADO ,CTANRODOC,comp_resol   ';
        $sql = $sql.' order by ctaidentif, anyoimp, tributcodi, tributario, codpredio,  periodo, fechvenc  )a, ';

        $sql = $sql." (select distinct pre.codpredio, (trim(VI.viasvia)||' '|| trim(vi.viasdesc)||' ' || nvl2(TRIM(predionume), 'Nro. '|| TRIM(predionume),'') || ' ' ||  ";
        $sql = $sql."   nvl2(TRIM(prediomanz), 'Mz. '||TRIM(prediomanz), '')  || ' ' ||    	   nvl2(trim(prediolote), 'Lt. '||trim(prediolote),'')   || ' ' || ";
        $sql = $sql."   nvl2(trim(prediodpto), 'Dpto. '||trim(prediodpto),'') )direccion   ";
        $sql = $sql.'   from smapredio pre, smavias vi , ds_saldos.tmps_ctasdeta c ';
        $sql = $sql."   where  c.IDSESSION='".$form->session."' ";
        $sql = $sql.'  and c.codpredio = pre.codpredio and  ';
        $sql = $sql."   pre.viascodi = vi.viascodi and vi.anyoimp= to_char(sysdate,'YYYY') ";
        $sql = $sql.'  ) b  ';

        $sql = $sql.' , (SELECT ANYOIMP, CODPREDIO, USOPRDESC FROM (SELECT ANYOIMP, CODCONTRI, CODPREDIO, USOPRCODI, MAX(PREDIPSEC) ';
        $sql = $sql." FROM SMVPARARBMUN WHERE ANYOIMP BETWEEN '".$form->anyo_ini."' AND '".$form->anyo_fin."' AND CODCONTRI = '".$form->codcontri."' ";
        $sql = $sql.' GROUP BY ANYOIMP, CODCONTRI, CODPREDIO, USOPRCODI) A1, ';
        $sql = $sql.' (SELECT USOPRCODI, USOPRDESC FROM SMAUSOPR) A2 ';
        $sql = $sql.' WHERE A1.USOPRCODI = A2.USOPRCODI) USO_PREDIO ';

        $sql = $sql.' ,(select X.CTAIDENTIF, MON_SR, MON_LP, MON_PJ from ';
        $sql = $sql." 	   (SELECT CTAIDENTIF, TOTAL mon_sr FROM DS_SALDOS.TMPS_CTASDETA WHERE IDSESSION='".$form->session."' AND ESPCODIGO='11.01') x, ";
        $sql = $sql." 	   (SELECT CTAIDENTIF, TOTAL mon_lp FROM DS_SALDOS.TMPS_CTASDETA WHERE IDSESSION='".$form->session."' AND ESPCODIGO='11.02') y, ";
        $sql = $sql." 	   (SELECT CTAIDENTIF, TOTAL mon_pj FROM DS_SALDOS.TMPS_CTASDETA WHERE IDSESSION='".$form->session."' AND ESPCODIGO='11.03') z ";
        $sql = $sql.' 	WHERE x.CTAIDENTIF = y.CTAIDENTIF(+) and x.CTAIDENTIF = z.CTAIDENTIF(+) ';
        $sql = $sql.'  UNION ALL ';
        $sql = $sql." 	     SELECT CTAIDENTIF, 0, 0, 0 FROM DS_SALDOS.TMPS_CTASDETA WHERE IDSESSION = '".$form->session."' AND TRIBUTCODI NOT IN('11.00', 'FRACC') ";
        $sql = $sql.'   ) C ';

        $sql = $sql.'  WHERE a.codpredio  = b.codpredio(+) and ';
        $sql = $sql.'        a.anyoimp    = uso_predio.anyoimp(+) and a.codpredio = uso_predio.codpredio(+) and ';
        $sql = $sql.'        a.ctaidentif = c.ctaidentif ';
        $sql = $sql.'  ORDER BY tributcodi, anyoimp, codpredio, periodo ';

        $checkingAccountData = OracleDatabase::db()->select($sql);

        return collect($checkingAccountData)->map(
            fn ($item) => CheckingAccountData::from([
                'ctaidentif' => $item->ctaidentif,
                'anyoimp' => $item->anyoimp,
                'tributcodi' => $item->tributcodi,
                'tributario' => $item->tributario,
                'codpredio' => $item->codpredio,
                'direccion' => mb_convert_encoding(str_replace('�', 'N', $item->direccion), 'UTF-8', 'ISO-8859-1'),
                'periodo' => $item->periodo,
                'fechvenc' => $item->fechvenc,
                'situacion' => $item->estado,
                'afecto' => $item->afecto / 100,
                'pagado' => $item->pagado / 100,
                'salord' => $item->salord / 100,
                'mora' => $item->mora / 100,
                'tmora' => $item->tmora / 100,
                'treajuste' => $item->treajuste / 100,
                'costoemi' => $item->costoemi / 100,
                'total' => $item->total / 100,
                'total_ppago' => $item->total_ppago / 100,
                'total_sr' => $item->mon_sr / 100,
                'total_lp' => $item->mon_lp / 100,
                'total_pj' => $item->mon_pj / 100,
                'ctaorigen' => $item->ctaorigen,
                'valor' => $item->ctaorigen,
                'expdiente' => $item->ctaorigen,
            ])
        );
    }

    public function installmentCheckingAccount(InstallmentCheckingAccountForm $form): Collection
    {
        if (empty($tipocontr)) {
            $session_ = 'NORONLINE'.$form->session;
        } else {
            $session_ = $tipocontr.'ONLINE'.$form->session;
        }

        // $filtro = " AND afecto > 0 ";
        $filtro = '';

        if ($form->estado == 'TOD') {
            $filtro = $filtro.' ';
        }
        if ($form->estado == 'PEN') {
            $filtro = $filtro.' WHERE CUOTATOTAL > 0 ';
        }
        if ($form->estado == 'CAN') {
            $filtro = $filtro.' WHERE CUOTATOTAL = 0 ';
        }

        if ($form->session === '2017335755') {
            $countSql = <<<'SQL'
                SELECT 1 filas FROM DUAL
            SQL;
        } else {
            $countSql = <<<'SQL'
                SELECT COUNT(1) filas FROM TABLE(DS_SALDOS.PACK_CTACTE_MDLM_BASE2.FUNC_OUTPUT(:parsession, :parcodcontri, :paranyoini, :paranyofin, :parporcen, :parfechafin, :parchist, :partributo, SYSDATE))
            SQL;
        }

        Log::debug(__METHOD__, ['countSql' => $countSql]);

        $countData = OracleDatabase::db()->selectOne($countSql, [
            'parsession' => $session_,
            'parcodcontri' => $form->codcontri,
            'paranyoini' => $form->anyo_ini,
            'paranyofin' => $form->anyo_fin,
            'parporcen' => 1,
            'parfechafin' => '',
            'parchist' => '2',
            'partributo' => 'F',
        ]);

        Log::debug(__METHOD__, ['countData' => $countData]);

        $filas = $countData->filas;

        Log::debug(__METHOD__, ['filas' => $filas]);

        if ($filas == 0) {
            return collect();
        }

        $sql = ' SELECT TIPOCONTR, CONTRATO, CUOTAPERIO, CTAIDENTIF, FECHVENC, ESTADO, CUOTAMONTO, CUOTAINTER, CUOTATIM, CUOTATOTAL, CUOTATOTAL_PPAGO, CTAORIGEN ';
        $sql = $sql.' FROM ';
        $sql = $sql." (SELECT DECODE(TRIM(A.TIPOCONTR),'NORMAL','ORDINARIO','CONTRARDD', 'FISCALIZACION','MULTA ADM', 'ADMINISTRATIVO','ORDINARIO') tipocontr,  ";
        $sql = $sql."  	    A.CONTRNUMCO || '-' || A.CONTRANYOC contrato, A.CUOTAPERIO, A.CTAIDENTIF, ";
        $sql = $sql."  	        TO_CHAR(cuotavenci, 'DD/MM/YYYY') fechvenc, TRIM(A.estado) estado, ";
        $sql = $sql."       CASE WHEN CPFESTCODI IN ('1', '3', '7') THEN ROUND(NVL(saldo_c * 100, 0), 2) ELSE ROUND(NVL((A.CUOTAMONTO) * 100, 0), 2) END cuotamonto, ";
        $sql = $sql."       CASE WHEN CPFESTCODI IN ('1', '3', '7') THEN ROUND(NVL(saldo_i * 100, 0), 2) ELSE ROUND(NVL((A.CUOTAINTER) * 100, 0), 2) END cuotainter,  ";
        $sql = $sql."       CASE WHEN CPFESTCODI IN ('1', '3', '7') THEN ROUND(NVL(saldo_m * 100, 0), 2) ELSE ROUND(NVL((A.CUOTATIM) * 100, 0), 2) END cuotatim, ";
        $sql = $sql."       CASE WHEN CPFESTCODI IN ('1', '3', '7') THEN ROUND(NVL((saldo_c + saldo_i + saldo_m ) * 100, 0), 2) ELSE 0 END cuotatotal, ";
        $sql = $sql."  	    CASE WHEN TRIM(bene_obs) = 'PPAGO' ";
        $sql = $sql.'           THEN ROUND(NVL(saldo_c * 100, 0), 2) + ROUND(NVL(moradescto * 100, 0), 2) ';
        $sql = $sql."  			ELSE 0 END cuotatotal_ppago, NVL(b.ctaorigen,' ' ) ctaorigen ";
        $sql = $sql.'       FROM DS_SALDOS.tmps_CTACTEFRAC A, DS_SALDOS.TMPS_CTASDETA B ';
        $sql = $sql."  		    WHERE A.IDSESSION = '".$form->session."' AND cuotaperio <> 'CB' AND ";
        $sql = $sql.'  		 A.IDSESSION = B.IDSESSION AND A.CODCONTRI = B.CODCONTRI AND A.CONTRNUMCO = B.CONTRNUMCO AND A.CONTRANYOC = B.CONTRANYOC AND A.CUOTAPERIO = B.PERIODO ';
        $sql = $sql.'  UNION ALL  ';
        $sql = $sql."  SELECT TRIBUTDESC, CODPREDIO, PERIODO, CTAIDENTIF, TO_CHAR(SYSDATE, 'DD/MM/YYYY'), 'Pendiente', ROUND(NVL(total * 100, 0), 2) total, 0, 0, ROUND(NVL(total * 100, 0), 2) total, 0, '' ";
        $sql = $sql.'  FROM DS_SALDOS.TMPS_CTASDETA A ';
        $sql = $sql."  WHERE A.IDSESSION = '".$form->session."' AND TRIBUTCODI = 'COSTA' AND CPF = 'T') ";
        $sql = $sql.$filtro;
        $sql = $sql.' ORDER BY tipocontr DESC, CONTRATO, CUOTAPERIO ';

        $installmentCheckingAccountData = OracleDatabase::db()->select($sql);

        return collect($installmentCheckingAccountData)->map(
            fn ($item) => InstallmentCheckingAccountData::from([
                'contrato' => $item->contrato,
                'tipocontr' => $item->tipocontr,
                'cuotaperio' => $item->cuotaperio,
                'ctaidentif' => $item->ctaidentif,
                'fechvence' => $item->fechvenc,
                'cuotamonto' => $item->cuotamonto / 100,
                'cuotainter' => $item->cuotainter / 100,
                'cuotatim' => $item->cuotatim / 100,
                'cuotatotal' => $item->cuotatotal / 100,
                'cuotatotal_ppago' => $item->cuotatotal_ppago / 100,
                'ctaorigen' => $item->ctaorigen,
            ])
        );
    }


}
