<?php

header("Content-Type:application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: Access-Control-Allow-Origin, Accept");

include 'parameters.php';

require __DIR__ . '/vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__ . "/../");
$dotenv->load();

$host_ip = $_SERVER['HOST_IP'];
$db_user = $_SERVER['DB_USER'];
$db_pass = $_SERVER['DB_PASS'];
$db_name = $_SERVER['DB_NAME'];

$con = mysqli_connect($host_ip, $db_user, $db_pass, $db_name);

if (mysqli_connect_errno()) {
  echo "Failed to connect to SQL: " . mysqli_connect_error();
}

if (isset($_GET['sites'])) {
    $sites = $_GET['sites'];
    $listdbtables = array_column($con->query('SHOW TABLES')->fetch_all(), 0);
    $site_count = count($listdbtables);
    $result_array = array(
        'success' => 'true',
        'site_count' => $site_count,
        'site_list' => $listdbtables
    );
    $result = json_encode($result_array);
    echo $result;
} elseif (isset($_GET['data']) && $_GET['data']!="") {
    $site_name = $_GET['data'];
    $site_data = mysqli_query($con,"SELECT id, phone, mac, apmac, last_updated, video_count FROM `$site_name`");

    if ($site_data->num_rows > 0) {
        $tempArray = array();
        $myArray = array();
        while ($row = $site_data->fetch_object()) {
            $tempArray = $row;
            array_push($myArray, $tempArray);
        }
        $result_array = array(
            'success' => 'true',
            'results' => $myArray
        );
    } else {
        $result_array = array(
            'success' => 'false',
            'message' => 'No Data Exists for This Site'
        );
    }
    $result = json_encode($result_array);
    echo $result;
} elseif (isset($_GET['stats']) && $_GET['stats']!="") {
    $site_name = $_GET['stats'];
    $stats_data = mysqli_query($con,"SELECT count(distinct(mac)), count(distinct(apmac)) FROM `$site_name`");

    date_default_timezone_set("Asia/Jerusalem");
    $time_now = date('Y-m-d H:i:s');
    $client_start = date('Y-m-d H:i:s', strtotime('-1 hour'));
    $ap_start = date('Y-m-d H:i:s', strtotime('-1 day'));

    $clients_result = mysqli_query($con, "SELECT count(distinct(mac)) FROM `$site_name` WHERE last_updated >= '$client_start' AND last_updated <= '$time_now'");
    $ap_result = mysqli_query($con, "SELECT count(distinct(apmac)) FROM `$site_name` WHERE last_updated >= '$ap_start' AND last_updated <= '$time_now'");

    if ($stats_data->num_rows > 0) {
        $row_clients = mysqli_fetch_array($clients_result);
        $row_ap = mysqli_fetch_array($ap_result);

        $result_array = array(
            'success' => 'true',
            'number_of_clients' => $row_clients[0],
            'number_of_ap' => $row_ap[0]
        );
    } else {
        $result_array = array(
            'success' => 'false',
            'message' => 'No Data Exists for This Site'
        );
    }
    $result = json_encode($result_array);
    echo $result;
} elseif (isset($_GET['summary']) && $_GET['summary']!="") {
    $site_name = $_GET['summary'];

    date_default_timezone_set("Asia/Jerusalem");
    $year = date("Y");
    $month = date("m");
    $day = cal_days_in_month(CAL_GREGORIAN, $month, $year);

    $month_start = $year . "-" . $month . "-" . "01" . " " . "00:00:00";
    $month_end = $year . "-" . $month . "-" . $day . " " . "23:59:59";

    $year_start = $year . "-" . "01" . "-" . "01" . " " . "00:00:00";
    $year_end = $year . "-" . "12" . "-" . "31" . " " . "23:59:59";

    # Week is from Sunday to Saturday: get date of previous Sunday unless today happens to be Sunday,
    # in that case use today's date
    if (date('l') != 'Sunday'){
        $firstday = date('Y-m-d', strtotime("sunday -1 week"));
    } else {
        $firstday = date('Y-m-d');
    }
    $lastday = date('Y-m-d', strtotime("saturday 0 week"));
    $week_start = $firstday . " " . "00:00:00";
    $week_end = $lastday . " " . "23:59:59";

    $week_result = mysqli_query($con, "SELECT count(mac), sum(video_count) FROM (SELECT distinct mac, video_count FROM `$site_name` WHERE last_updated >= '$week_start' AND last_updated <= '$week_end') as mac_summary");
    $month_result = mysqli_query($con, "SELECT count(mac), sum(video_count) FROM (SELECT distinct mac, video_count FROM `$site_name` WHERE last_updated >= '$month_start' AND last_updated <= '$month_end') as mac_summary");
    $year_result = mysqli_query($con, "SELECT count(mac), sum(video_count) FROM (SELECT distinct mac, video_count FROM `$site_name` WHERE last_updated >= '$year_start' AND last_updated <= '$year_end') as mac_summary");

    if ($year_result->num_rows > 0) {
        $row_week = mysqli_fetch_array($week_result);
        $row_month = mysqli_fetch_array($month_result);
        $row_year = mysqli_fetch_array($year_result);

        $weekly_views = $row_week[1];
        if ($row_week[1] == null) {
            $weekly_views = "0";
        }
        $monthly_views = $row_month[1];
        if ($row_month[1] == null) {
            $monthly_views = "0";
        }
        $yearly_views = $row_year[1];
        if ($row_year[1] == null) {
            $yearly_views = "0";
        }

        $result_array = array(
            'success' => 'true',
            'weekly_clients' => $row_week[0],
            'weekly_views' => $weekly_views,
            'monthly_clients' => $row_month[0],
            'monthly_views' => $monthly_views,
            'yearly_clients' => $row_year[0],
            'yearly_views' => $yearly_views
        );
    } else {
        $result_array = array(
            'success' => 'false',
            'message' => 'No Data Exists for This Site'
        );
    }
    $result = json_encode($result_array);
    echo $result;
} elseif (isset($_GET['bot']) && $_GET['bot']!="") {
    $phone = $_GET['bot'];
    $temp = strlen($phone);
    if ($temp != 7) {
        $result_array = array(
            'success' => 'false',
            'results' => 'Digits are not equal to 7'
        );
        $result = json_encode($result_array);
        echo $result;
    } else {
        $listdbtables = array_column($con->query('SHOW TABLES')->fetch_all(), 0);
        $total_affected_rows = 0;
        for ($i=0; $i < sizeof($listdbtables); $i++) { 
            $sql = "DELETE FROM $listdbtables[$i] WHERE `$listdbtables[$i]`.`phone` LIKE '%$phone'";
            $con->query($sql);  
            $total_affected_rows= $total_affected_rows + mysqli_affected_rows($con); 
            if ($con->query($sql) === FALSE) {
                $result_array = array(
                    'success' => 'false',
                    'error' => $con->error
                );
            }
        }
        $result_array = array(
            'success' => 'true',
            'records_deleted' => $total_affected_rows,
        );
        $result = json_encode($result_array);
        echo $result;
    }
} else {
    $result_array = array(
        'success' => 'false',
        'message' => 'Invalid Query Parameter'
    );
    $result = json_encode($result_array);
    echo $result;
}

mysqli_close($con);

?>