<?php

namespace App\Console\Commands;

use App\Danoral\Cuotas\Cuota;
use App\Danoral\Entidades\Entidad;
use App\Danoral\Transacciones\TransDT;
use Illuminate\Console\Command;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class InicialAno extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'inicial:ano {schema}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Executa la configuracion inicial para aperturar un año';

    private $schema = 'adminXX';

    /**
     * Execute the console command.
     * actualizado 2026-01-13
     * 
     */
    public function handle()
    {
        $this->info($this->description);

        $this->schema = $this->argument('schema');
        $this->info("schema                                 : $this->schema");
        $this->info('Connection Name                        : ' . Schema::getConnection()->getName());
        $this->info('Connection DatabaseName                : ' . Schema::getConnection()->getDatabaseName());
        $this->info('Connection TablePrefix                 : ' . Schema::getConnection()->getTablePrefix());
        $this->info('Connection DriverName                  : ' . Schema::getConnection()->getDriverName());
        $this->info('Connection NameWithReadWriteType       : ' . Schema::getConnection()->getNameWithReadWriteType());
        $this->info('');

        /*
        $tables = Schema::getTables();

        foreach($tables as $t) {
            echo $t['schema'].'.'.$t['name'] . PHP_EOL;
        }
        */

        //$this->drops();
        //$this->createTebles();
        //$this->createViews();
        //$this->inicialCuotas();
        
        $this->info('<--- FIN');

    }

    private function drops() {
        $schema = $this->schema;

        //BORRADO
        echo "Borrado...". PHP_EOL;

        //views
        echo "Drop: $schema.cobros_dt_view ........... ";
        DB::statement("DROP VIEW IF EXISTS $schema.cobros_dt_view;");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.transdt_cond_view ........ ";
        DB::statement("DROP VIEW IF EXISTS $schema.transdt_cond_view;");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.cuotas_view .............. ";
        DB::statement("DROP VIEW IF EXISTS $schema.cuotas_view;");
        echo "OK". PHP_EOL;
        
        echo "Drop: $schema.balance_view ............. ";
        DB::statement("DROP VIEW IF EXISTS $schema.balance_view;");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.cobros_lista_view ........ ";
        DB::statement("DROP VIEW IF EXISTS $schema.cobros_lista_view;");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.cobros_view .............. ";
        DB::statement("DROP VIEW IF EXISTS $schema.cobros_view;");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.transdt_view ............. ";
        DB::statement("DROP VIEW IF EXISTS $schema.transdt_view;");
        echo "OK". PHP_EOL;


        //cobros
        echo "Drop: $schema.cobros_tdt_cfp ........... ";
        Schema::dropIfExists("$schema.cobros_tdt_cfp");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.cobros_forma_pago ........ ";
        Schema::dropIfExists("$schema.cobros_forma_pago");
        echo "OK". PHP_EOL;
        
        echo "Drop: $schema.transdt_cobros ........... ";
        Schema::dropIfExists("$schema.transdt_cobros");
        echo "OK". PHP_EOL;
        
        echo "Drop: $schema.cobros ................... ";
        Schema::dropIfExists("$schema.cobros");
        echo "OK". PHP_EOL;
        
        echo "Drop: $schema.cobros_listas ............ ";
        Schema::dropIfExists("$schema.cobros_listas");
        echo "OK". PHP_EOL;
        
        echo "Drop: $schema.ingresos ................. ";
        Schema::dropIfExists("$schema.ingresos");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.cuotas ................... ";
        Schema::dropIfExists("$schema.cuotas");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.transdt_cond ............. ";
        Schema::dropIfExists("$schema.transdt_cond");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.condominios .............. ";
        Schema::dropIfExists("$schema.condominios");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.transdt_cambio ........... ";
        Schema::dropIfExists("$schema.transdt_cambio");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.transdt_des .............. ";
        Schema::dropIfExists("$schema.transdt_des");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.transdt .................. ";
        Schema::dropIfExists("$schema.transdt");
        echo "OK". PHP_EOL;

        echo "Drop: $schema.trans .................... ";
        Schema::dropIfExists("$schema.trans");
        echo "OK". PHP_EOL;

        echo PHP_EOL;
    }

    private function createTebles() {
        $schema = $this->schema;
        $sequence_restart   = '100100260000000001';
        $sequence_minvalue  = '100100260000000001';
        $sequence_maxvalue  = '100100269999999999';
        $sequence_start     = '100100260000000001';


        #TABLAS
        echo "Tablas...". PHP_EOL;

        { #trans
            echo "Create: $schema.trans .................. ";
            Schema::create("$schema.trans", function (Blueprint $table) {
                $table->bigIncrements('idT');
                $table->bigInteger('ctaT')->nullable();
                $table->smallInteger('typeT')->nullable();
                $table->string('refT', 64)->nullable();
                $table->timestamp('dateT');
                $table->string('descT', 128)->nullable();
                $table->boolean('enableT');
                $table->integer('idU');
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."trans_idT_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }
        
        { #transdt
            echo "Create: $schema.transdt ................ ";
            Schema::create("$schema.transdt", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idDT');
                $table->bigInteger('ctaDT');
                $table->tinyInteger('dhDT');
                $table->decimal('montoDT', 14, 2);
                $table->smallInteger('typeDT');
                $table->decimal('anoDT', 4, 0)->nullable();
                $table->decimal('mesDT', 2, 0)->nullable();
                $table->bigInteger('idE');
                $table->bigInteger('idT')->nullable();
                $table->smallInteger('idMoneda');

                $table->foreign('ctaDT')->references('codCTA')->on('admin.cuentas');
                $table->foreign('idE')->references('idE')->on('admin.entidades');
                $table->foreign('idMoneda')->references('idMoneda')->on('config.monedas');
                $table->foreign('idT')->references('idT')->on("$schema.trans");
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."transdt_idDT_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }

        { #transdt_des
            echo "Create: $schema.transdt_des ............ ";
            Schema::create("$schema.transdt_des", function (Blueprint $table) use ($schema) {
                $table->bigInteger('idDT');
                $table->string('descripcionDT', 128);

                $table->primary('idDT');
                $table->foreign('idDT')->references('idDT')->on("$schema.transdt");
            });
            echo "OK". PHP_EOL;
        }

        { #condominios
            echo "Create: $schema.condominios ............ ";
            Schema::create("$schema.condominios", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idCond');
                $table->bigInteger('idE');
                $table->smallInteger('typeEmisionCond');
                $table->decimal('anoCond', 4, 0)->nullable();
                $table->decimal('mesCond', 2, 0)->nullable();
                $table->bigInteger('idTCond')->nullable();
                $table->string('refCond', 32)->nullable();
                $table->bigInteger('idTC')->nullable();
                $table->timestamp('dateVenTasa')->nullable();
                $table->text('logCond')->nullable();

                $table->foreign('idE')->references('idE')->on('admin.entidades');
                $table->foreign('idTC')->references('idTC')->on('config.tasa_cambio');
                $table->foreign('idTCond')->references('idT')->on("$schema.trans");

                $table->unique(["idE", "typeEmisionCond", "anoCond", "mesCond"]);           
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."condominios_idCond_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }

        { #transdt_cond
            echo "Create: $schema.transdt_cond ........... ";
            Schema::create("$schema.transdt_cond", function (Blueprint $table) use ($schema) {
                $table->bigInteger('idDT');
                $table->bigInteger('idCond');

                $table->primary('idDT');
                $table->foreign('idDT')->references('idDT')->on("$schema.transdt");
                $table->foreign('idCond')->references('idCond')->on("$schema.condominios");
            });
            echo "OK". PHP_EOL;
        }

        { #cuotas
            echo "Create: $schema.cuotas ................. ";
            Schema::create("$schema.cuotas", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idCuota');
                $table->bigInteger('idCond')->nullable();
                $table->bigInteger('idE');
                $table->bigInteger('idDTCuota');
                $table->bigInteger('idDTCuotaCobro')->nullable();
                $table->decimal('montoOrigen', 14, 2)->nullable();
                $table->bigInteger('tcEmision_idTC')->nullable();
                $table->timestamp('tcEmision_dateVen')->nullable();
                $table->smallInteger('typeCuota')->nullable();

                $table->foreign('tcEmision_idTC')->references('idTC')->on('config.tasa_cambio');
                $table->foreign('idDTCuota')->references('idDT')->on("$schema.transdt");
                $table->foreign('idDTCuotaCobro')->references('idDT')->on("$schema.transdt");
                $table->foreign('idCond')->references('idCond')->on("$schema.condominios");
                $table->foreign('idE')->references('idE')->on('admin.entidades');
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."cuotas_idCuota_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );        
            echo "OK". PHP_EOL;
        }

        { #transdt_cambio
            echo "Create: $schema.transdt_cambio ......... ";
            Schema::create("$schema.transdt_cambio", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idDTCambio');
                $table->bigInteger('idDT_base');
                $table->bigInteger('idDT_quote');
                $table->bigInteger('idTC');

                $table->foreign('idDT_base')->references('idDT')->on("$schema.transdt");
                $table->foreign('idDT_quote')->references('idDT')->on("$schema.transdt");
                $table->foreign('idTC')->references('idTC')->on('config.tasa_cambio');
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."transdt_cambio_idDTCambio_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }

        { #ingresos 
            echo "Create: $schema.ingresos ............... ";
            Schema::create("$schema.ingresos", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idIngreso');
                $table->integer('idBanco');
                $table->string('refIngreso', 32);
                $table->smallInteger('typeIngreso');
                $table->bigInteger('idDTIngreso');
                $table->bigInteger('idDTIngresoCobro')->nullable();
                $table->bigInteger('idPlanilla')->nullable();
                $table->timestamp('dateIngreso');
                $table->bigInteger('idBT')->nullable();
                $table->timestamps();

                $table->foreign('idBanco')->references('idBanco')->on('admin.bancos');
                $table->foreign('idDTIngreso')->references('idDT')->on("$schema.transdt");
                $table->foreign('idDTIngresoCobro')->references('idDT')->on("$schema.transdt");
                $table->unique(["idBanco", "dateIngreso", "typeIngreso", "refIngreso"]);   
                $table->unique("idBT");    
                 
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."ingresos_idIngreso_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );        
            echo "OK". PHP_EOL;
        }

        #Tablas de COBROS

        { #cobros_listas
            echo "Create: $schema.cobros_listas .......... ";
            Schema::create("$schema.cobros_listas", function (Blueprint $table) {
                $table->bigIncrements('idCobrosLista');
                $table->timestamp('dateCobrosLista');
                $table->bigInteger('idEUser');
                $table->smallInteger('typeCobrosLista');
                $table->timestamp('dateCobro');

                $table->foreign('idEUser')->references('idE')->on('admin.entidades_users');
                $table->foreign('typeCobrosLista')->references('typeCobrosLista')->on("config.cobros_lista_type");
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."cobros_listas_idCobrosLista_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }

        { #cobros
            echo "Create: $schema.cobros ................. ";
            Schema::create("$schema.cobros", function (Blueprint $table) use ($schema){
                $table->bigIncrements('idCobro');
                $table->timestamp('dateCobro');
                $table->smallInteger('origenCobro');
                $table->bigInteger('idEUser');
                $table->smallInteger('typeCobro')->nullable();
                $table->bigInteger('idE')->nullable();
                $table->smallInteger('estadoCobro')->nullable();
                $table->smallInteger('errorCobro')->nullable();
                $table->bigInteger('idCobrosLista')->nullable();

                $table->foreign('idE')->references('idE')->on('admin.entidades');
                $table->foreign('idCobrosLista')->references('idCobrosLista')->on("$schema.cobros_listas");
                $table->foreign('origenCobro')->references('origenCobro')->on("config.cobro_origen");
                $table->foreign('typeCobro')->references('typeCobro')->on("config.cobro_type");
                $table->foreign('idEUser')->references('idE')->on('admin.entidades_users');

            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."cobros_idCobro_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }

        { #transdt_cobros
            echo "Create: $schema.transdt_cobros ......... ";
            Schema::create("$schema.transdt_cobros", function (Blueprint $table) use ($schema) {
                $table->bigInteger('idDT');
                $table->bigInteger('idCobro')->nullable();
                $table->bigInteger('idCuota')->nullable();
                $table->bigInteger('idCaja')->nullable();
                $table->bigInteger('idNP')->nullable();

                $table->primary('idDT');
                $table->foreign('idDT')->references('idDT')->on("$schema.transdt");
                $table->foreign('idCobro')->references('idCobro')->on("$schema.cobros");
                $table->foreign('idCuota')->references('idCuota')->on("$schema.cuotas");
                $table->foreign('idNP')->references('idNP')->on("admin.noti_pagos");
            });
            echo "OK". PHP_EOL;
        }

        { #cobros_forma_pago
            echo "Create: $schema.cobros_forma_pago ...... ";
            Schema::create("$schema.cobros_forma_pago", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idCFP');
                $table->bigInteger('idCobro');
                $table->timestamp('dateCFP');
                $table->smallInteger('typeCFP');
                $table->decimal('montoCFP', 14, 2);
                $table->smallInteger('idMoneda');
                $table->string('refCFP', 128)->nullable();
                $table->bigInteger('idDT')->nullable();
                $table->smallInteger('bancoCFP')->nullable();
                $table->smallInteger('bancoOrigenCFP')->nullable();
                $table->bigInteger('idIngreso')->nullable();
                $table->bigInteger('idPlanilla')->nullable();
                $table->jsonb('infoCFP')->nullable();

                $table->foreign('idMoneda')->references('idMoneda')->on('config.monedas');
                $table->foreign('idCobro')->references('idCobro')->on("$schema.cobros");
                $table->foreign('idIngreso')->references('idIngreso')->on("$schema.ingresos");
                $table->foreign('typeCFP')->references('typeCFP')->on('config.cfp_types');
                
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."cobros_forma_pago_idCFP_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        }

        { #cobros_tdt_cfp
            echo "Create: $schema.cobros_tdt_cfp ......... ";
            Schema::create("$schema.cobros_tdt_cfp", function (Blueprint $table) use ($schema) {
                $table->bigIncrements('idCobroCFP');
                $table->bigInteger('idCobro')->nullable();
                $table->bigInteger('idDT')->nullable();
                $table->bigInteger('idCFP')->nullable();
                $table->bigInteger('idCuota')->nullable();
                $table->decimal('montoCFP', 14, 2)->nullable();
                $table->decimal('montoDT', 14, 2)->nullable();
                $table->smallInteger('idMonedaDT')->nullable();
                $table->bigInteger('idTC')->nullable();

                $table->foreign('idMonedaDT')->references('idMoneda')->on('config.monedas');
                $table->foreign('idCobro')->references('idCobro')->on("$schema.cobros");
                $table->foreign('idCFP')->references('idCFP')->on("$schema.cobros_forma_pago");
                $table->foreign('idCuota')->references('idCuota')->on("$schema.cuotas");
                $table->foreign('idDT')->references('idDT')->on("$schema.transdt");
                $table->foreign('idTC')->references('idTC')->on('config.tasa_cambio');
            });
            DB::statement('
                ALTER SEQUENCE '.$schema.'."cobros_tdt_cfp_idCobroCFP_seq" 
                    INCREMENT   1
                    RESTART    '.$sequence_restart.'
                    MINVALUE   '.$sequence_minvalue.' 
                    MAXVALUE   '.$sequence_maxvalue.'
                    START      '.$sequence_start.'
                    CYCLE'
            );

            echo "OK". PHP_EOL;
        
        }


        echo "GRANT ALL PRIVILEGES: $schema .......... ";
        DB::statement('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA '.$schema.' TO GROUP "versio6_danoral_api_web"');
        DB::statement('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA '.$schema.' TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;

        echo PHP_EOL;
    }

    private function createViews() {
        $schema = $this->schema;

        //VIEWS
        echo "Views...". PHP_EOL;
    
        //cuotas_view
        echo "Create: $schema.cuotas_view ............ ";
        DB::statement('            
            CREATE VIEW '.$schema.'.cuotas_view AS
                SELECT 
                    cuotas."idE",
                    cuotas."idCond",
                    cuotas."idCuota",
                    cuotas."typeCuota",
                    cuotas."montoOrigen",
                    cuotas."idDTCuota",
                    cuotas."idDTCuotaCobro",
                    tdtc."idT" AS "idTCobro",
                    cond."idE" AS "idECond",
                    cond."anoCond",
                    cond."mesCond",
                    cond."refCond",
                    cond."typeEmisionCond",
                    transdt."idT",
                    transdt."dhDT",
                    transdt."anoDT",
                    transdt."ctaDT",
                    transdt."mesDT",
                    transdt."typeDT",
                    transdt."montoDT",
                    transdt."idMoneda",
                    cuotas."tcEmision_dateVen",
                    cuotas."tcEmision_idTC",
                    "tcEmision"."valorTC" AS "tcEmision_valor",
                    "tcEmision"."dateValorTC" AS "tcEmision_date",
                    npc."idNP"
                FROM '.$schema.'.cuotas 
                    JOIN '.$schema.'.transdt ON cuotas."idDTCuota" = transdt."idDT"
                    LEFT JOIN '.$schema.'.condominios cond ON cuotas."idCond" = cond."idCond"
                    LEFT JOIN '.$schema.'.transdt tdtc ON cuotas."idDTCuotaCobro" = tdtc."idDT"
                    LEFT JOIN config.tasa_cambio "tcEmision" ON cuotas."tcEmision_idTC" = "tcEmision"."idTC"
                    LEFT JOIN admin.noti_pagos_cuotas npc ON cuotas."idCuota" = npc."idCuota";
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."cuotas_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;

        //transdt_cond_view
        echo "Create: $schema.transdt_cond_view ...... ";
        DB::statement('            
            CREATE VIEW '.$schema.'.transdt_cond_view AS
                SELECT 
                    condominios."idCond",
                    condominios."anoCond",
                    condominios."mesCond",
                    condominios."typeEmisionCond",
                    condominios."idE" AS "idECond",
                    condominios."refCond",
                    condominios."idTC",
                    condominios."idTCond",
                    transdt_cond."idDT",
                    transdt."idE",
                    transdt."idT",
                    transdt."ctaDT",
                    transdt_des."descripcionDT",
                    transdt."anoDT",
                    transdt."mesDT",
                    transdt."typeDT",
                    transdt."dhDT",
                    transdt."montoDT",
                    transdt."idMoneda",
                    tasa_cambio."dateValorTC",
                    tasa_cambio."valorTC"
                FROM '.$schema.'.transdt
                    JOIN '.$schema.'.transdt_cond ON transdt_cond."idDT" = transdt."idDT"
                    JOIN '.$schema.'.condominios ON transdt_cond."idCond" = condominios."idCond"
                    LEFT JOIN '.$schema.'.transdt_des ON transdt_des."idDT" = transdt."idDT"
                    LEFT JOIN config.tasa_cambio ON condominios."idTC" = tasa_cambio."idTC";        
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."transdt_cond_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;
    
        //cobros_dt_view
        echo "Create: $schema.cobros_dt_view ......... ";
        DB::statement('            
            CREATE VIEW '.$schema.'.cobros_dt_view AS
                SELECT 
                    cobros."idCobro",
                    cobros."dateCobro",
                    cobros."origenCobro",
                    cobro_origen."nameOrigenCobro",
                    cobros."typeCobro",
                    cobro_type."nameTypeCobro",
                    cobros."idE",
                    cobros."idEUser",
                    (entidades.datose -> \'name\'::text) ->> \'value\'::text AS "nameE",
                    cobros."idCobrosLista",
                    cobros."errorCobro",
                    cobros."estadoCobro",
                    transdt_cobros."idCaja",
                    transdt_cobros."idCuota",
                    transdt_cobros."idNP",
                    cuotas."idE" AS "idECuota",
                    cuotas."idCond",
                    cuotas."montoOrigen",
                    cuotas."idDTCuotaCobro",
                    cuotas."tcEmision_idTC",
                    cuotas."tcEmision_dateVen",
                    e_cuotas."codESuper" AS "codEdifCuota",
                    e_cuotas."codE" AS "codAptoCuota",
                    cuotas."idDTCuota",
                    cuotas."typeCuota",
                    tc_emision."valorTC" AS "tcEmision_valor",
                    tc_emision."dateValorTC" AS "tcEmision_date",
                    dt_cuota."idT",
                    dt_cuota."idE" AS "idEdtCuota",
                    dt_cuota."dhDT",
                    dt_cuota."anoDT",
                    dt_cuota."ctaDT",
                    dt_cuota."mesDT",
                    dt_cuota."typeDT",
                    dt_cuota."montoDT",
                    dt_cuota."idMoneda",
                    dt_cuota_cobro."idDT" AS "idDT_cc",
                    dt_cuota_cobro."idT" AS "idT_cc",
                    dt_cuota_cobro."idE" AS "idEdtCuotaCobro",
                    dt_cuota_cobro."dhDT" AS "dhDT_cc",
                    dt_cuota_cobro."anoDT" AS "anoDT_cc",
                    dt_cuota_cobro."ctaDT" AS "ctaDT_cc",
                    dt_cuota_cobro."mesDT" AS "mesDT_cc",
                    dt_cuota_cobro."typeDT" AS "typeDT_cc",
                    dt_cuota_cobro."montoDT" AS "montoDT_cc",
                    dt_cuota_cobro."idMoneda" AS "idMoneda_cc"
                FROM '.$schema.'.cobros
                    LEFT JOIN '.$schema.'.transdt_cobros USING ("idCobro")
                    LEFT JOIN '.$schema.'.cuotas ON cuotas."idCuota" = transdt_cobros."idCuota"
                    LEFT JOIN '.$schema.'.transdt dt_cuota ON dt_cuota."idDT" = cuotas."idDTCuota"
                    LEFT JOIN '.$schema.'.transdt dt_cuota_cobro ON transdt_cobros."idDT" = dt_cuota_cobro."idDT"
                    LEFT JOIN admin.entidades_view e_cuotas ON e_cuotas."idE" = cuotas."idE"
                    LEFT JOIN config.tasa_cambio tc_emision ON cuotas."tcEmision_idTC" = tc_emision."idTC"
                    JOIN config.cobro_type ON cobros."typeCobro" = cobro_type."typeCobro"
                    JOIN config.cobro_origen ON cobros."origenCobro" = cobro_origen."origenCobro"
                    JOIN admin.entidades ON cobros."idEUser" = entidades."idE"
                ORDER BY cobros."idCobro"
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."cobros_dt_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;

        //balance_view
        echo "Create: $schema.balance_view ........... ";
        DB::statement('            
            CREATE VIEW '.$schema.'.balance_view AS
                SELECT 
                    c.cta0,
                    c.cta1,
                    dt."ctaDT",
                    c."maskCTA",
                    c."nameCTA",
                    sum(
                        CASE
                            WHEN dt."dhDT" = 0 THEN dt."montoDT"
                            ELSE 0::numeric
                        END) AS debe,
                    sum(
                        CASE
                            WHEN dt."dhDT" = 1 THEN dt."montoDT"
                            ELSE 0::numeric
                        END) AS haber,
                    dt."idE",
                    dt."idMoneda"
                FROM '.$schema.'.transdt dt
                    JOIN '.$schema.'.trans t ON t."idT" = dt."idT"
                    LEFT JOIN admin.cuentas c ON dt."ctaDT" = c."codCTA"
                WHERE t."enableT" = true AND dt."idT" IS NOT NULL
                GROUP BY c.cta0, c.cta1, dt."ctaDT", c."maskCTA", c."nameCTA", dt."idE", dt."idMoneda"
                ORDER BY c.cta1   
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."balance_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;       
        
        //cobros_lista_view
        echo "Create: $schema.cobros_lista_view ...... ";
        DB::statement('            
            CREATE VIEW '.$schema.'.cobros_lista_view AS
                SELECT 
                    cobros_listas."idCobrosLista",
                    cobros_listas."dateCobrosLista",
                    cobros_listas."typeCobrosLista",
                    cobros_lista_type."nameCobrosLista",
                    cobros_listas."idEUser",
                    (entidades.datose -> \'name\'::text) ->> \'value\'::text AS "nameE",
                    cobros_listas."dateCobro"
                FROM '.$schema.'.cobros_listas
                    JOIN config.cobros_lista_type ON cobros_listas."typeCobrosLista" = cobros_lista_type."typeCobrosLista"
                    JOIN admin.entidades ON cobros_listas."idEUser" = entidades."idE"
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."cobros_lista_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;      
        
        //cobros_view
        echo "Create: $schema.cobros_view ............ ";
        DB::statement('            
            CREATE VIEW '.$schema.'.cobros_view AS
                SELECT 
                    cobros."idCobro",
                    cobros."dateCobro",
                    cobros."origenCobro",
                    cobro_origen."nameOrigenCobro",
                    cobros."typeCobro",
                    cobro_type."nameTypeCobro",
                    cobros."idE",
                    cobros."idEUser",
                    (entidades.datose -> \'name\'::text) ->> \'value\'::text AS "nameE",
                    cobros."idCobrosLista",
                    cobros."errorCobro",
                    cobros."estadoCobro"
                FROM '.$schema.'.cobros
                    JOIN config.cobro_type ON cobros."typeCobro" = cobro_type."typeCobro"
                    JOIN config.cobro_origen ON cobros."origenCobro" = cobro_origen."origenCobro"
                    JOIN admin.entidades ON cobros."idEUser" = entidades."idE";  
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."cobros_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;      
        
        //transdt_view
        echo "Create: $schema.transdt_view ........... ";
        DB::statement('            
            CREATE VIEW '.$schema.'.transdt_view AS
                SELECT 
                    dt."idT",
                    t."ctaT",
                    t."dateT",
                    t."typeT",
                    tt."nameTypeT",
                    t."refT",
                    t."descT",
                    t."enableT",
                    t."idU",
                    (es.datose -> \'cod\'::text) ->> \'value\'::text AS "codESuper",
                    (e.datose -> \'cod\'::text) ->> \'value\'::text AS "codE",
                    es."idE" AS "idESuper",
                    dt."idE",
                    dt."dhDT",
                    dt."idDT",
                    dt."ctaDT",
                    dt."typeDT",
                    dt."anoDT",
                    dt."mesDT",
                    dt."montoDT",
                    dt."idMoneda",
                    dtd."descripcionDT",
                    dtc_q."idDT_base",
                    dt_base."typeDT" AS "typeDT_base",
                    dt_base."montoDT" AS "montoDT_base",
                    dt_base."idMoneda" AS "idMoneda_base",
                    tasa_cambio."valorTC" AS "valorTC_base",
                    tasa_cambio."dateValorTC" AS "dateValorTC_base",
                    dtd_base."descripcionDT" AS "descripcionDT_base",
                    count(dtc_b."idDT_quote") AS "countTcQuote"
                FROM '.$schema.'.transdt dt
                    LEFT JOIN '.$schema.'.trans t ON dt."idT" = t."idT"
                    LEFT JOIN '.$schema.'.transdt_des dtd ON dt."idDT" = dtd."idDT"
                    LEFT JOIN '.$schema.'.transdt_cambio dtc_q ON dt."idDT" = dtc_q."idDT_quote"
                    LEFT JOIN '.$schema.'.transdt_cambio dtc_b ON dt."idDT" = dtc_b."idDT_base"
                    LEFT JOIN '.$schema.'.transdt dt_base ON dtc_q."idDT_base" = dt_base."idDT"
                    LEFT JOIN '.$schema.'.transdt_des dtd_base ON dtc_q."idDT_base" = dtd_base."idDT"
                    LEFT JOIN config.tasa_cambio ON dtc_q."idTC" = tasa_cambio."idTC"
                    LEFT JOIN admin.entidades e ON dt."idE" = e."idE"
                    LEFT JOIN admin.entidades es ON e."idESuper" = es."idE"
                    LEFT JOIN config.trans_types tt ON t."typeT" = tt."typeT"
                GROUP BY dt."idT", t."ctaT", t."dateT", t."typeT", tt."nameTypeT", t."refT", t."descT", t."enableT", t."idU", 
                    ((es.datose -> \'cod\'::text) ->> \'value\'::text), 
                    ((e.datose -> \'cod\'::text) ->> \'value\'::text), 
                    es."idE", dt."idE", dt."dhDT", dt."idDT", dt."ctaDT", dt."typeDT", dt."anoDT", dt."mesDT", dt."montoDT", dt."idMoneda", dtd."descripcionDT", dtc_q."idDT_base", dt_base."typeDT", dt_base."montoDT", dt_base."idMoneda", dtd_base."descripcionDT", tasa_cambio."valorTC", tasa_cambio."dateValorTC"
                ORDER BY t."dateT";     
        ');
        DB::statement('GRANT ALL PRIVILEGES ON '.$schema.'."transdt_view" TO GROUP "versio6_danoral_api_web"');
        echo "OK". PHP_EOL;        


        echo PHP_EOL;

    }

    private function inicialCuotas() {
        echo "inicialCuotas...". PHP_EOL;

        $schema = $this->schema;
        $schema_anterior = 'admin25';

        $edificios = Entidad::getDolarizados();

        $i = 0;
        foreach($edificios as $e) {

            //Un Edif
            //if(intval($e->codE) != 6001){ continue; } 

            echo $i++ . "$e->codE - $e->nameE" . PHP_EOL;

            //Carga o crea la Trans Inicial
            DB::table("$schema.trans")
                ->updateOrInsert(
                    [
                        'typeT'     => 2002, //Inicial
                        'refT'      => $e->codE,
                    ],[
                        'ctaT'      => null,
                        'dateT'     => '2026-01-01 00:00:00',
                        'descT'     => 'Inicial ' . $e->codE,
                        'enableT'   => true,
                        'idU'       => 1,
                    ]
                );

            $trans = DB::table("$schema.trans")
                ->Where([
                    'typeT'     => 2002, //Inicial
                    'refT'      => $e->codE,
                ])
                ->first();
            
            if($trans) {
                echo $trans->idT . PHP_EOL;
            }

            //cuotas pendientes de edificio
            $cuotas = DB::table("$schema_anterior.cuotas_view")
                ->leftJoin('admin.entidades', 'cuotas_view.idE', '=', 'entidades.idE')
                ->Where([
                    ['idESuper', $e->idE],
                    ['idDTCuotaCobro', null],
                ])
                ->orderBy('anoDT', 'asc')
                ->orderBy('mesDT', 'asc')
                ->orderBy('cuotas_view.idE', 'asc')
                ->get();


            if($cuotas){
                echo 'cuotas: ' . count($cuotas) . PHP_EOL;
                foreach($cuotas as $c){
                    echo " --> $e->codE | $c->idE : $c->idCuota ($c->anoDT/$c->mesDT) : $c->montoDT | cobro: $c->idDTCuotaCobro";

                    //Una cuota
                    /*
                    if(!($c->anoDT==2024 && $c->mesDT==12)){ 
                        echo ' > NO' . PHP_EOL;
                        continue; 
                    } 
                    */

                    DB::beginTransaction();
                    try{
                        //crear 130  - Cuotas
                        $iDT_cuota = new TransDT;
                        $iDT_cuota->setSchema($schema);
                        $iDT_cuota->fill([
                            'ctaDT'         => 130,
                            'dhDT'          => 0,
                            'montoDT'       => $c->montoDT,
                            'typeDT'        => TransDT::$TYPE_CUOTA,
                            'anoDT'         => $c->anoDT,
                            'mesDT'         => $c->mesDT,
                            'idE'           => $c->idE,
                            'idT'           => $trans->idT,
                            'idMoneda'      => $c->idMoneda,
                        ]);
                        $iDT_cuota->save();

                        echo ' > idDT : ' . $iDT_cuota->idDT;

                        //crear la cuota
                        $cuota = new Cuota;
                        $cuota->setSchema($schema);
                        $cuota->idCuota             = $c->idCuota;
                        //$cuota->idCond              = $c->idCond;
                        $cuota->idE                 = $c->idE;
                        $cuota->idDTCuota           = $iDT_cuota->idDT;
                        $cuota->montoOrigen         = $c->montoOrigen;
                        $cuota->tcEmision_idTC      = $c->tcEmision_idTC;
                        $cuota->tcEmision_dateVen   = $c->tcEmision_dateVen;
                        $cuota->typeCuota           = $c->typeCuota;

                        $cuota->save();

                        DB::commit();
                        echo ' > OK' . PHP_EOL;

                    }catch (\Exception $e) {
                        DB::rollback();
                        echo ' > ERROR '. PHP_EOL;
                        echo '+++++++++++++++++++++++++++++++++++++++++++++++  rollback'.PHP_EOL ;
                        var_dump($e->getMessage());
                    }

                    //return;

                }
            }

            //if($i > 2) { return; }
        }


    }



}
