<?php

namespace App\Helpers;

use Illuminate\Support\Facades\DB;
use Request;
use carbon\carbon;

class TotalWidgets
{
    public static function getTotalDurationOFProductPlay($product_id, $user_unique_id)
    {
        $SQL = "SELECT SUM(video_histories.duration) as totalDuration, COUNT(video_histories.id) as totalVideos
        from contents
        JOIN products ON products.id=contents.product_id
        JOIN categories ON categories.id=contents.cat_id
        JOIN re_s3buckets ON re_s3buckets.etag=contents.video_id
        JOIN video_histories ON video_histories.video_id=contents.video_id
        WHERE contents.cat_id IN 
        (SELECT categories.id FROM categories
        WHERE categories.parent_id IN (SELECT categories.id FROM `categories`
        JOIN products ON products.id=categories.product_id
        WHERE products.id='$product_id' AND categories.parent_id=0)) 
        AND contents.type='video' 
        AND video_histories.user_unique_id='$user_unique_id'
        GROUP BY video_histories.video_id
        order by contents.cat_id,contents.type,contents.sort_order";
        $query = DB::select($SQL);
        $totalArrayPlay['totalVideosPlay'] = 0;
        $totalArrayPlay['totalVideosPlayDuration'] = 0;
        if (count($query) > 0) {
            foreach ($query as $value) {
                $totalArrayPlay['totalVideosPlay']++;
                $totalArrayPlay['totalVideosPlayDuration'] += $value->totalDuration;
            }
        }
        return $totalArrayPlay;
    }

    public static function getTotalDurationOFProduct($product_id)
    {
        $SQL = "SELECT SUM(re_s3buckets.duration) as totalDuration, COUNT(re_s3buckets.public_name) as totalVideos 
        from contents
        JOIN products ON products.id=contents.product_id
        JOIN categories ON categories.id=contents.cat_id
        JOIN re_s3buckets ON re_s3buckets.etag=contents.video_id
        WHERE contents.cat_id IN 
        (SELECT categories.id FROM categories
        WHERE categories.parent_id IN (SELECT categories.id FROM `categories`
        JOIN products ON products.id=categories.product_id
        WHERE products.id='$product_id' AND categories.parent_id=0)) 
        AND contents.type='video' 
        order by contents.cat_id,contents.type,contents.sort_order";
        $query = DB::select($SQL);

        $totalArray['totalVideos'] = 0;
        $totalArray['totalVideosDuration'] = 0;

        if (count($query) > 0) {
            $totalArray['totalVideos'] = $query[0]->totalVideos;
            $totalArray['totalVideosDuration'] = empty($query[0]->totalDuration) ? 0 : $query[0]->totalDuration;
        }

        return $totalArray;
    }
}