<?php
include_once('functions.php');
?>

<?php
$function = new functions;
$sql_query = "SELECT n.*, 
                    (SELECT SUM(link1_name) FROM tbl_link_count l WHERE n.id=l.channel_id) link1_count,
                    (SELECT SUM(link2_name) FROM tbl_link_count l WHERE n.id=l.channel_id) link2_count,
                    (SELECT COUNT(link1_name) FROM tbl_link_count l WHERE n.id=l.channel_id) total_count, 
                    n.active AS channel_status FROM tbl_channel n
                    GROUP BY id ORDER BY total_count DESC";
$results = mysqli_query($connect, $sql_query);
$recordCount = $results->num_rows;
?>

<?php

//Total Channel List count
$sql_query = "SELECT COUNT(*) as num FROM tbl_channel";
$total_channels = mysqli_query($connect, $sql_query);
$total_channels = mysqli_fetch_array($total_channels);
$total_channels = $total_channels['num'];

//Total Users
$sql_query = "SELECT COUNT(*) as num FROM tbl_user";
$total_users = mysqli_query($connect, $sql_query);
$total_users = mysqli_fetch_array($total_users);
$total_users = $total_users['num'];

//Total Users
$sql_query = "SELECT (SELECT SUM(link1_name) FROM tbl_link_count l WHERE n.id=l.channel_id) link1_count,
  (SELECT SUM(link2_name) FROM tbl_link_count l WHERE n.id=l.channel_id) link2_count FROM tbl_channel n";
$total_link_count = mysqli_query($connect, $sql_query);
$total_link_count = mysqli_fetch_array($total_link_count);
$link1_count = $total_link_count['link1_count'];
$link2_count = $total_link_count['link2_count'];
?>
<!--breadcrumbs start-->
<!--
<div id="breadcrumbs-wrapper" class=" grey lighten-3">
    <div class="container">
        <div class="row">
            <div class="col s12 m12 l12">
            <h5 class="breadcrumbs-title">Dashboard</h5>
            <ol class="breadcrumb">
                <li><a href="dashboard.php" class="deep-orange-text">Dashboard</a>
                </li>
                <li><a class="active">Home</a>
            </ol>
            </div>
        </div>
    </div>
</div>
-->
<!--breadcrumbs end-->

<!--start container-->
<div class="container">
    <div class="section">l̥
        <!--card stats start-->
        <div id="card-stats" class="seaction">
            <div class="row">
                <div class="col s12 m6 l3 waves-effect waves-light">
                    <a href="channel.php">
                        <div class="card hoverable">
                            <div class="card-content white-text deep-orange-g">
                                <br>
                                <i class="mdi-hardware-desktop-mac small"></i>
                                <p class="card-stats-title">CHANNEL LIST</p>
                                <h4 class="card-stats-number"><?php echo $total_channels; ?></h4>
                                <br>
                            </div>
                        </div>
                    </a>
                </div>

                <div class="col s12 m6 l3 waves-effect waves-light">
                    <a href="users.php">
                        <div class="card hoverable">
                            <div class="card-content white-text deep-orange-g">
                                <br />
                                <i class="mdi-social-person small"></i>
                                <p class="card-stats-title">USERS</p>
                                <h4 class="card-stats-number"><?php echo $total_users; ?></h4>
                                <br />
                            </div>
                        </div>
                    </a>
                </div>

                <div class="col s12 m6 l3 waves-effect waves-light">
                    <a href="#">
                        <div class="card hoverable">
                            <div class="card-content white-text deep-orange-g">
                                <br />
                                <i class="mdi-content-link small"></i>
                                <p class="card-stats-title">Link 1 Count</p>
                                <p class="card-stats-desc"></p>
                                <p class="card-stats-number"><?php echo $link1_count; ?></p>
                                <br />
                            </div>
                        </div>
                    </a>
                </div>

                <div class="col s12 m6 l3 waves-effect waves-light">
                    <a href="#">
                        <div class="card hoverable">
                            <div class="card-content white-text deep-orange-g">
                                <br>
                                <i class="mdi-content-link small"></i>
                                <p class="card-stats-title">Link 2 Count</p>
                                <h4 class="card-stats-number"><?php echo $link2_count; ?></h4>
                                <br>
                            </div>
                        </div>
                    </a>
                </div>

            </div>
            <!-- row completed -->
        </div>
        <!--card stats end-->
    </div>

    <section id="chart">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="card-panel borderTop">
                    <div id="chart-container">
                        <canvas id="graphCanvas" class="chartjs-render-monitor" height="350"></canvas>
                    </div>
                </div>
            </div>
        </div>
    </section>

</div>


<?php
//Update from the v2.1 (Only work whenever query find the channel_image is blank for YouTube category)
//Update YouTube Thumbnail URL when channel_image is blank for YouTube category only.
$sql_category = "SELECT id, channel_name, channel_type, channel_url, channel_image FROM tbl_channel WHERE channel_image NOT LIKE 'https://%' AND channel_type='" . YOUTUBE . "'";
//echo $sql_category . " \n\t";
$results = mysqli_query($connect, $sql_category);
$recordCount = $results->num_rows;
//echo $recordCount. "\n\t";
//$total_category = mysqli_fetch_array($results);
//var_dump($total_category);

if ($recordCount > 0) {
    while ($data = mysqli_fetch_array($results)) {
        $date = date('Y-m-d H:i:s', time());
        $ID = $data['id'];
        $channel_url = $data['channel_url'];

        $ytData = $function->getInfo($channel_url);
        $thumbNail = $ytData->thumbnail_url;
        //echo $data['channel_name'] . " \n\t " . $thumbNail;

        $sql_query = "UPDATE tbl_channel
							SET channel_image = ?, updatedAt = ?
							WHERE id = ?";

        $stmt = $connect->stmt_init();
        if ($stmt->prepare($sql_query)) {
            // Bind your variables to replace the ?s
            $stmt->bind_param(
                'sss',
                $thumbNail,
                $date,
                $ID
            );
            // Execute query
            $stmt->execute();
            // store result
            $update_result = $stmt->store_result();
            $stmt->close();
        }
    }
} else {
    //echo "blank";
}
?>