<?php
require "global.ajax.php";

switch ($_GET["op"]) {

    case 'ajaxShowStatisticsData':

        // POST VARIABLES
        $post_ent_id = isset($_GET["ent_id"]) ? funCheckInput($_GET["ent_id"]) : "";
        $post_pos_id = isset($_GET["pos_id"]) ? funCheckInput($_GET["pos_id"]) : "";
        $post_date = isset($_GET["date"]) ? funCheckInput($_GET["date"]) : date('Y-m-d');

        if (empty($post_date)) $post_date = date('Y-m-d');

        $posInfo = getBranchData($post_pos_id);

        $currentDayStart = "{$post_date} 07:00:00";
        $currentDayEnd = date("Y-m-d", strtotime("+1 day", strtotime($post_date))) . " 06:59:59";

        // Convert the post date to a timestamp
        $timestamp = strtotime($post_date);
        // Calculate the day of the week (0 = Sunday, 1 = Monday, ...)
        $dayOfWeek = date('w', $timestamp);
        // Calculate the difference between the current day and Monday (0 = Sunday)
        $daysToMonday = $dayOfWeek - 1;
        $daysToMonday = $daysToMonday < 0 ? $daysToMonday + 7 : $daysToMonday;
        // Calculate the timestamp for the start of the week (Monday 07:00:00 AM)
        $weekStartTimestamp = $timestamp - ($daysToMonday * 86400) + (8 * 3600);
        // Calculate the week number and format the timestamps
        $weekNumber = date('W', $weekStartTimestamp);
        $weekStart = date('Y-m-d', $weekStartTimestamp);
        $weekEnd = date('Y-m-d', $weekStartTimestamp + (7 * 86400) + (359 * 60));


        //GET THE FIST AND LAST DAY OF THE CURRENT MONTH
        $current_post_date_month = date("Y-m", strtotime($post_date));
        $current_date_year = date("Y", strtotime($post_date));
        $monthStart = date("Y-m-01 07:00:00", strtotime($post_date));
        $monthEnd = date("Y-m-01 06:59:59", strtotime("+1 month", strtotime($post_date)));

        // TOTAL DEBT COLLECTED
        function getDebtCollected($post_ent_id, $post_pos_id, $post_date)
        {
            $pdo = Databases::connect();

            $paySql = $pdo->query("SELECT
            DATE_FORMAT(DATE_ADD(pay_det_date, INTERVAL -8 HOUR), '%Y-%m-%d') AS pay_det_date_interval
            ,DATE_FORMAT(DATE_ADD(pay_det_sale_close, INTERVAL -8 HOUR), '%Y-%m-%d') AS pay_det_sale_close_interval
            ,IFNULL( pay_det_income, 0 ) AS pay_det_income 
            FROM tb_payments_details 
            WHERE pay_det_ent_id = {$post_ent_id} AND pay_det_pos_id = {$post_pos_id} AND pay_det_status = 'Enabled'
            AND DATE_FORMAT(DATE_ADD(pay_det_date, INTERVAL -8 HOUR), '%Y-%m-%d') = '{$post_date}'
            AND pay_det_payment_type != 'CASHBACK'
            GROUP BY pay_det_id;")->fetchAll(PDO::FETCH_ASSOC);

            $debtCollectedToday = 0;

            foreach ($paySql as $key => $paySqlValue) {

                if ($paySqlValue["pay_det_date_interval"] > $paySqlValue["pay_det_sale_close_interval"]) {
                    $debtCollectedToday += $paySqlValue["pay_det_income"];
                }
            }
            return $debtCollectedToday;
        }

        $debtCollectedToday = getDebtCollected($post_ent_id, $post_pos_id, $post_date);

        // WIDGETS

        // TODAY INCOMES
        $sqlTodayIncome = $pdo->query("SELECT
        IFNULL(SUM(pay_det_income),0) AS total_today_incomes
        FROM tb_payments_details
        WHERE pay_det_ent_id = {$post_ent_id} AND pay_det_pos_id = {$post_pos_id} AND pay_det_status = 'Enabled'
        AND DATE_FORMAT(DATE_ADD(pay_det_date, INTERVAL -8 HOUR), '%Y-%m-%d') = '{$post_date}'
        AND pay_det_payment_type != 'CASHBACK';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sqlTodayIncome)) {
            $total_today_incomes = $sqlTodayIncome['total_today_incomes'];
        } else {
            $total_today_incomes = '0.00';
        }

        $todayProfitTotal = ($total_today_incomes - $debtCollectedToday);

        // CURRENT WEEK INCOMES
        $sql = $pdo->query("SELECT
        IFNULL(SUM(pay_det_income), 0) AS total_incomes_week
        FROM tb_payments_details
        WHERE pay_det_ent_id = {$post_ent_id}
        AND pay_det_pos_id = {$post_pos_id}
        AND pay_det_status = 'Enabled'
        AND pay_det_date BETWEEN '{$weekStart} 07:00:00' AND '{$weekEnd} 06:59:59'
        AND pay_det_payment_type != 'CASHBACK';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_incomes_week = $sql['total_incomes_week'];
        } else {
            $total_incomes_week = '0.00';
        }

        // CURRENT MONTH INCOMES
        $sql = $pdo->query("SELECT
        IFNULL(SUM(pay_det_income),0) AS total_incomes_month
        FROM tb_payments_details
        WHERE pay_det_ent_id = {$post_ent_id} AND pay_det_pos_id = {$post_pos_id} AND pay_det_status = 'Enabled'
        AND pay_det_date BETWEEN '{$monthStart}' AND '{$monthEnd}'
        -- AND DATE_FORMAT(DATE_ADD(pay_det_date, INTERVAL -8 HOUR), '%Y-%m') = '{$current_post_date_month}'
        -- AND pay_det_date BETWEEN '{$monthStart}' AND '{$monthEnd}'
        AND pay_det_payment_type != 'CASHBACK';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_incomes_month = $sql['total_incomes_month'];
        } else {
            $total_incomes_month = '0.00';
        }

        //TOTAL DEBT TODAY
        $sql = $pdo->query("SELECT IFNULL (SUM(sale_debt),0) AS total_debt_today FROM tb_sales WHERE sale_ent_id = {$post_ent_id} AND sale_pos_id = {$post_pos_id} AND sale_status = 'Enabled' AND sale_debt_status = 'On'
        AND DATE_FORMAT(DATE_ADD(sale_date, INTERVAL -8 HOUR), '%Y-%m-%d') = '{$post_date}';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_debt_today = $sql['total_debt_today'];
        } else {
            $total_debt_today = '0.00';
        }


        // EXPENSES WIDGETS
        function getDailyExpenses($post_ent_id, $post_pos_id, $post_date, $type)
        {
            $pdo = Databases::connect();

            $sql = $pdo->query("SELECT IFNULL(SUM(exp_total),0) AS total_expense_today FROM tb_expenses WHERE exp_ent_id = {$post_ent_id} AND exp_pos_id = {$post_pos_id} AND exp_status = 'Enabled' AND exp_type ='{$type}'
            AND DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%Y-%m-%d') = '{$post_date}';")->fetch(PDO::FETCH_ASSOC);
            if (!empty($sql)) {
                return $sql['total_expense_today'];
            } else {
                return '0.00';
            }
        }

        //TOTAL DAILY EXPENSE
        $total_expense_today = getDailyExpenses($post_ent_id, $post_pos_id, $post_date, 'Daily');


        // MONTHLY EXPENSES
        function getMonthlyExpenses($post_ent_id, $post_pos_id, $type, $current_post_date_month)
        {
            $pdo = Databases::connect();

            $sql = $pdo->query("SELECT IFNULL (SUM(exp_total),0) AS totalMonthlyExpense FROM tb_expenses WHERE exp_ent_id = {$post_ent_id} AND exp_pos_id = {$post_pos_id} AND exp_status = 'Enabled' AND exp_type ='{$type}'
            AND DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%Y-%m') = '{$current_post_date_month}';")->fetch(PDO::FETCH_ASSOC);

            if (!empty($sql)) {
                return $sql['totalMonthlyExpense'];
            } else {
                return '0.00';
            }
        }

        //TOTAL MONTHLY DAILY EXPENSES
        $totalMonthlyDailyExpense = getMonthlyExpenses($post_ent_id, $post_pos_id, 'Daily', $current_post_date_month);
        //TOTAL MONTHLY FIXED EXPENSES
        $totalMonthlyFixedExpense = getMonthlyExpenses($post_ent_id, $post_pos_id, 'Fixed', $current_post_date_month);

        $totalMonthlyExpenses = $totalMonthlyDailyExpense + $totalMonthlyFixedExpense;


        //TOTAL COMPRAS DEL MES
        $sql = $pdo->query("SELECT IFNULL (SUM(pur_cost),0) AS total_cost_purchases_monthly FROM tb_purchases WHERE pur_ent_id = {$post_ent_id} AND pur_pos_id = {$post_pos_id} AND pur_status = 'Enabled'
        AND DATE_FORMAT(DATE_ADD(pur_date, INTERVAL -8 HOUR), '%Y-%m') = '{$current_post_date_month}'
        ;")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_cost_purchases_monthly = $sql['total_cost_purchases_monthly'];
        } else {
            $total_cost_purchases_monthly = '0.00';
        }


        //TOTAL COMPRAS GENERAL
        $sql = $pdo->query("SELECT IFNULL (SUM(pur_cost),0) AS total_cost_purchases FROM tb_purchases WHERE pur_ent_id = {$post_ent_id} AND pur_pos_id = {$post_pos_id} AND pur_status = 'Enabled';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_cost_purchases = $sql['total_cost_purchases'];
        } else {
            $total_cost_purchases = '0.00';
        }

        //TOTAL DE GASTOS GENERAL
        $sql = $pdo->query("SELECT IFNULL (SUM(exp_total),0) AS total_expenses FROM tb_expenses WHERE exp_ent_id = {$post_ent_id} AND exp_pos_id = {$post_pos_id} AND exp_status = 'Enabled';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_expenses = $sql['total_expenses'];
        } else {
            $total_expenses = '0.00';
        }

        //TOTAL DEBTS GENERAL
        $sql = $pdo->query("SELECT IFNULL (SUM(sale_debt),0) AS total_debt FROM tb_sales WHERE sale_ent_id = {$post_ent_id} AND sale_pos_id = {$post_pos_id} AND sale_status = 'Enabled' AND sale_debt_status = 'On';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_debt = $sql['total_debt'];
        } else {
            $total_debt = '0.00';
        }

        // TOTAL INCOMES GENERAL
        $sql = $pdo->query("SELECT
        IFNULL(SUM(pay_det_income),0) AS total_incomes_general
        FROM tb_payments_details
        WHERE pay_det_ent_id = {$post_ent_id} AND pay_det_pos_id = {$post_pos_id} AND pay_det_status = 'Enabled' AND pay_det_payment_type != 'CASHBACK';")->fetch(PDO::FETCH_ASSOC);
        if (!empty($sql)) {
            $total_incomes_general = $sql['total_incomes_general'];
        } else {
            $total_incomes_general = '0.00';
        }


        // CHARTS

        //PURCHASE MONTHLY CHART
        $sqlMonthlyPurchase = "SELECT
        DATE_FORMAT(DATE_ADD(pur_date, INTERVAL -8 HOUR), '%b %Y') AS pur_date,
        SUM(pur_cost) as total_monthly_cost
        FROM tb_purchases
        WHERE pur_ent_id = {$post_ent_id} AND pur_pos_id = {$post_pos_id} AND pur_status = 'Enabled'
        AND DATE_FORMAT(DATE_ADD(pur_date, INTERVAL -8 HOUR), '%Y') = '{$current_date_year}'
        GROUP BY MONTH(DATE_ADD(pur_date, INTERVAL -8 HOUR))
        ORDER BY pur_id ASC;";

        $montlhyPurchaseChart = executePDOFetchAll($sqlMonthlyPurchase);

        $arrayPurchaseDate = array();
        $arrayTotalPurchases = array();

        foreach ($montlhyPurchaseChart as $key => $value) {
            array_push($arrayPurchaseDate, $value['pur_date']);
            array_push($arrayTotalPurchases, $value["total_monthly_cost"]);
        }


        //SALES MONTHLY CHART
        $sqlMonthlySale = "SELECT
            DATE_FORMAT(DATE_ADD(pay_det_date, INTERVAL -8 HOUR), '%b %Y') AS pay_det_date,
            SUM(CASE WHEN pay_det_payment_type != 'CASHBACK' THEN pay_det_income ELSE 0 END) AS total_monthly_sales_incomes,
            SUM(CASE WHEN pay_det_payment_type = 'CASHBACK' THEN pay_det_income ELSE 0 END) AS total_monthly_cashback_incomes
            FROM tb_payments_details
            WHERE pay_det_ent_id = '{$post_ent_id}' AND pay_det_pos_id = '{$post_pos_id}' AND pay_det_status = 'Enabled'
            AND DATE_FORMAT(DATE_ADD(pay_det_date, INTERVAL -8 HOUR), '%Y') = '{$current_date_year}'
            GROUP BY MONTH(DATE_ADD(pay_det_date, INTERVAL -8 HOUR))
            ORDER BY pay_det_id ASC;";
        // exit($sqlMonthlySale);

        $resMonthlySale = executePDOFetchAll($sqlMonthlySale);

        $arraySalesMontlhyDate = array();
        $arraySalesMonthlyIncomes = array();
        $arraySalesMonthlyCashBackIncomes = array();

        foreach ($resMonthlySale as $key => $value) {
            array_push($arraySalesMontlhyDate, $value['pay_det_date']);
            array_push($arraySalesMonthlyIncomes, $value["total_monthly_sales_incomes"]);
            array_push($arraySalesMonthlyCashBackIncomes, $value["total_monthly_cashback_incomes"]);
        }


        //DAILY EXPENSE MONTHLY CHART
        $sqlMonthlyDailyExpense = "SELECT
        DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%b %Y') AS daily_exp_date,
        SUM(exp_total) as daily_monthly_expense
        FROM tb_expenses
        WHERE exp_ent_id = '{$post_ent_id}' AND exp_pos_id = '{$post_pos_id}' AND exp_status = 'Enabled' AND exp_type = 'Daily'
        AND DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%Y') = '{$current_date_year}'
        GROUP BY MONTH(DATE_ADD(exp_date, INTERVAL -8 HOUR))
        ORDER BY exp_id ASC;";

        $montlhyDailyExpenseChart = executePDOFetchAll($sqlMonthlyDailyExpense);

        $arrayDailyExpenseDate = array();
        $arrayTotalDailyExpenses = array();

        foreach ($montlhyDailyExpenseChart as $key => $value) {
            array_push($arrayDailyExpenseDate, $value['daily_exp_date']);
            array_push($arrayTotalDailyExpenses, $value["daily_monthly_expense"]);
        }

        //FIXED EXPENSE MONTHLY CHART
        $sqlMonthlyFixedExpense = "SELECT
        DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%b %Y') AS fixed_exp_date,
        SUM(exp_total) as fixed_monthly_expense
        FROM tb_expenses
        WHERE exp_ent_id = '{$post_ent_id}' AND exp_pos_id = '{$post_pos_id}' AND exp_status = 'Enabled' AND exp_type = 'Fixed'
        AND DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%Y') = '{$current_date_year}'
        GROUP BY MONTH(DATE_ADD(exp_date, INTERVAL -8 HOUR))
        ORDER BY exp_id ASC;";

        $montlhyFixedExpenseChart = executePDOFetchAll($sqlMonthlyFixedExpense);

        $arrayFixedExpenseDate = array();
        $arrayTotalFixedExpenses = array();

        foreach ($montlhyFixedExpenseChart as $key => $value) {
            array_push($arrayFixedExpenseDate, $value['fixed_exp_date']);
            array_push($arrayTotalFixedExpenses, $value["fixed_monthly_expense"]);
        }

        //MONTHLY EXPENSE CHART
        $sqlMonthlyExpense = "SELECT
        DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%b %Y') AS fixed_exp_date,
        SUM(exp_total) as fixed_monthly_expense
        FROM tb_expenses
        WHERE exp_ent_id = '{$post_ent_id}' AND exp_pos_id = '{$post_pos_id}' AND exp_status = 'Enabled'
        AND DATE_FORMAT(DATE_ADD(exp_date, INTERVAL -8 HOUR), '%Y') = '{$current_date_year}'
        GROUP BY MONTH(DATE_ADD(exp_date, INTERVAL -8 HOUR))
        ORDER BY exp_id ASC;";

        $montlhyExpenseChart = executePDOFetchAll($sqlMonthlyExpense);

        $arrayMonthlyExpenseDate = array();
        $arrayTotalMonthlyExpenses = array();

        foreach ($montlhyExpenseChart as $key => $value) {
            array_push($arrayMonthlyExpenseDate, $value['fixed_exp_date']);
            array_push($arrayTotalMonthlyExpenses, $value["fixed_monthly_expense"]);
        }

        // RESPONSE
        $response = array("today_sales" => $todayProfitTotal, "sales_of_the_week" => $total_incomes_week, "sales_of_the_month" => $total_incomes_month, "debts_collected_today" => $debtCollectedToday, "pending_accounts" => $total_debt_today, "monthly_daily_expenses" => $totalMonthlyDailyExpense, "monthly_fixed_expenses" => $totalMonthlyFixedExpense, "purchases_of_the_month" => $total_cost_purchases_monthly, "general_purchases" => $total_cost_purchases, "general_expenses" => $total_expenses, "general_pending_payments" => $total_debt, "general_sale" => $total_incomes_general, "arrayPurchaseDate" => $arrayPurchaseDate, "arrayTotalPurchases" => $arrayTotalPurchases, "arraySalesMontlhyDate" => $arraySalesMontlhyDate, "arraySalesMonthlyIncomes" => $arraySalesMonthlyIncomes, "arraySalesMonthlyCashBackIncomes" => $arraySalesMonthlyCashBackIncomes, "arrayDailyExpenseDate" => $arrayDailyExpenseDate, "arrayTotalDailyExpenses" => $arrayTotalDailyExpenses, "arrayFixedExpenseDate" => $arrayFixedExpenseDate, "arrayTotalFixedExpenses" => $arrayTotalFixedExpenses, "arrayMonthlyExpenseDate" => $arrayMonthlyExpenseDate, "arrayTotalMonthlyExpenses" => $arrayTotalMonthlyExpenses);

        echo json_encode($response);

        break;
}
