<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
class ConfigureMigration extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'configure:migrate';
    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Dispatch/Attach views and function and run migrate';
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }
    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $database_name = env('DB_DATABASE');
        $this->call('migrate');
    //     \DB::unprepared("DO $$
    //     DECLARE r record;
    //     DECLARE s TEXT;
    //    BEGIN
    //        FOR r IN select specific_schema,routine_name
    //                 from information_schema.routines
    //                 WHERE routine_type = 'FUNCTION' AND routine_schema = 'public' AND  routine_catalog = '".$database_name."'
    //        LOOP
    //            s := 'DROP FUNCTION IF EXISTS ' ||  quote_ident(r.specific_schema) || '.' || quote_ident(r.routine_name) || ' CASCADE;';
    //            EXECUTE s;
    //            RAISE NOTICE 's = % ',s;
    //        END LOOP;
    //    END$$;");
  
    $this->info('Drop all functions successfully.',"\n");

        \DB::unprepared("DO $$
            DECLARE r record;
            DECLARE s TEXT;
            BEGIN
                FOR r IN select table_schema,table_name
                         from information_schema.views
                         where table_schema = 'public' and table_catalog = '".$database_name."' AND (table_name not like '%geo%' OR table_name not like '%column%')
                LOOP
                    s := 'DROP VIEW IF EXISTS ' ||  quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' CASCADE;';
                    EXECUTE s;
                    RAISE NOTICE 's = % ',s;
                END LOOP;
            END$$;");


        
        $this->info('Drop all views successfully.',"\n");

        
       
        \Artisan::call('migrate:refresh', ['--path'=>'/database/migrations/table_functions']);
        $this->info('Functions migrate successfully.',"\n");
        \Artisan::call('migrate:refresh', ['--path' => '/database/migrations/table_views']);
        $this->info('View migrate successfully.',"\n");
    }
}