<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;

class CreateViewPopularArticles extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
	DB::statement("
		CREATE VIEW view_popular AS
		SELECT 
			article_id as id,
			article_id,
			a.created_at,
			a.updated_at,
			a.url,
			a.title,
			a.excerpt_html,
			sum(upvotes)+sum(comments) AS impact,
			sum(upvotes) AS upvotes,
			sum(comments) as comments,
			count(article_id) AS repost
		FROM 
			discussion as d 
		JOIN 
			article AS a ON d.article_id = a.id 
		GROUP BY 
			article_id, 
			a.created_at,
			a.updated_at,
			a.url,
			a.title,
			a.excerpt_html
			HAVING (impact > 300) OR (count(article_id) > 2) 
		ORDER BY ((sum(upvotes)+sum(comments))*count(article_id)) desc, repost desc, impact desc"
	);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
	DB::statement("DROP VIEW view_popular;");
    }
}