<?php

use yii\db\Migration;

class m161223_090716_entity_avg_review extends Migration
{
    public function up()
    {
        $this->addColumn('{{%entity}}', 'reviewCount', $this->integer());
        $this->addColumn('{{%entity}}', 'ratingValue', $this->float());
        $this->addColumn('{{%entity}}', 'ratingInfrastructure', $this->float());
        $this->addColumn('{{%entity}}', 'ratingGuiding', $this->float());
        $this->addColumn('{{%entity}}', 'ratingService', $this->float());
        $this->addColumn('{{%entity}}', 'ratingSealife', $this->float());

        $createTriggerSql = <<< SQL
            CREATE
                TRIGGER `avg_review` AFTER UPDATE ON `review`
                FOR EACH ROW BEGIN
            
                UPDATE entity e
                JOIN
                    ( SELECT 
                        r.entityId, 
                        COUNT(*) as reviewCount, 
                        SUM(r.rating) AS ratingValue,
                        SUM(r.ratingInfrastructure) AS ratingInfrastructure,
                        SUM(r.ratingGuiding) AS ratingGuiding,
                        SUM(r.ratingService) AS ratingService,
                        SUM(r.ratingSealife) AS ratingSealife
                      FROM review r
                      WHERE r.status = 3 AND r.entityId = NEW.entityId
                    ) nw
                ON nw.entityId = e.id
                SET 
                  e.reviewCount = nw.reviewCount, 
                  e.ratingValue = round(nw.ratingValue / nw.reviewCount, 2),
                  e.ratingInfrastructure = round(nw.ratingInfrastructure / nw.reviewCount, 2),
                  e.ratingGuiding = round(nw.ratingGuiding / nw.reviewCount, 2),
                  e.ratingService = round(nw.ratingService / nw.reviewCount, 2),
                  e.ratingSealife = round(nw.ratingSealife / nw.reviewCount, 2)
                WHERE e.id = NEW.entityId;
            END;
SQL;

        $this->execute('DROP TRIGGER /*!50032 IF EXISTS */ `avg_review`');
        $this->execute($createTriggerSql);
    }

    public function down()
    {
        $this->execute('DROP TRIGGER /*!50032 IF EXISTS */ `avg_review`');
        $this->dropColumn('{{%entity}}', 'reviewCount');
        $this->dropColumn('{{%entity}}', 'ratingValue');
        $this->dropColumn('{{%entity}}', 'ratingInfrastructure');
        $this->dropColumn('{{%entity}}', 'ratingGuiding');
        $this->dropColumn('{{%entity}}', 'ratingService');
        $this->dropColumn('{{%entity}}', 'ratingSealife');
    }
}