<?php

use yii\db\Migration;

/**
 * Class m230213_234921_views
 */
class m230213_234921_views extends Migration {
  /**
   * {@inheritdoc}
   */
  public function safeUp() {

    $this->execute('
      create view public."ReporteIndividual" as
      select
            U.nombre,
            U.facebook,
            U."facebookVerificado",
            U.twitter,
            U."twitterVerificado",
            U.instagram,
            U."instagramVerificado",
            U.id "idUsuario",
            count(E."redSocial") filter ( where E."redSocial" = Facebook and U."facebookVerificado") as "cantidadFacebook",
            count(E."redSocial") filter ( where E."redSocial" = Twitter and U."twitterVerificado") as "cantidadTwitter",
            count(E."redSocial") filter ( where E."redSocial" = Instagram and U."instagramVerificado") as "cantidadInstagram",
            --count(E.id) as "cantidadEventos",
            count(R.accion) as "cantidadResultados"
            --(count(E.id) - count(R.accion)) as "cantidadPendientes"
          from "Evento" E
          inner join "EventoGrupo" EG on E.id = EG."idEvento"
          inner join "Grupo" G on G.id = EG."idGrupo"
          inner join "UsuarioGrupo" UG on G.id = UG."idGrupo"
          inner join "Usuario" U on U.id = UG."idUsuario"
          left join "Resultado" R on E.id = R."idEvento" and R."idUsuario" = U.id
          group by
                  U.nombre,
                  U.facebook,
                  U."facebookVerificado",
                  U.twitter,
                  U."twitterVerificado",
                  U.instagram,
                  U."instagramVerificado",
                  U.id
      --    order by E.id
      ;
      ');
  }

  /**
   * {@inheritdoc}
   */
  public function safeDown() {
    
    $this->execute('drop view "ReporteIndividual";');
  }

  /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m230213_234921_views cannot be reverted.\n";

        return false;
    }
    */
}