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

// POST VARIABLES
$cus_id = isset($_POST["cus_id"]) ? funCheckInput($_POST["cus_id"]) : "";
$cus_ent_id = isset($_POST["cus_ent_id"]) ? funCheckInput($_POST["cus_ent_id"]) : "";
$cus_pos_id = isset($_POST["cus_pos_id"]) ? funCheckInput($_POST["cus_pos_id"]) : "";
$cus_full_name = isset($_POST["cus_full_name"]) ? funCheckInput($_POST["cus_full_name"]) : "";
$cus_phone = isset($_POST["cus_phone"]) ? funCheckInput($_POST["cus_phone"]) : "";
$cus_email = isset($_POST["cus_email"]) ? funCheckInput($_POST["cus_email"]) : "";
$cus_address = isset($_POST["cus_address"]) ? funCheckInput($_POST["cus_address"]) : "";
$cus_fiscal_number = isset($_POST["cus_fiscal_number"]) ? funCheckInput($_POST["cus_fiscal_number"]) : "";
$cus_created_by = $session_user_id;

$cus_full_name = ucwords(strtolower($cus_full_name));

if (empty($cus_ent_id)) $cus_ent_id = $session_user_ent_id;
if (empty($cus_pos_id)) $cus_pos_id = $session_user_pos_id;

switch ($_GET["op"]) {

    case 'ajaxSaveUpdate':

        try {

            $pdo->beginTransaction();

            $isNew = empty(trim($cus_id));

            if ($isNew) {

                $sql = "INSERT INTO tb_customers (cus_ent_id,cus_pos_id,cus_full_name,cus_phone,cus_email,cus_address,cus_fiscal_number,cus_created_by) 
                VALUES (:cus_ent_id,:cus_pos_id,:cus_full_name,:cus_phone,:cus_email,:cus_address,:cus_fiscal_number,:cus_created_by)";

                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    ':cus_ent_id'        => $cus_ent_id,
                    ':cus_pos_id'        => $cus_pos_id,
                    ':cus_full_name'     => $cus_full_name,
                    ':cus_phone'         => $cus_phone,
                    ':cus_email'         => $cus_email,
                    ':cus_address'       => $cus_address,
                    ':cus_fiscal_number' => $cus_fiscal_number,
                    ':cus_created_by'    => $cus_created_by
                ]);

                $response = array("message" => $translate['saved_successfully'], "status" => "success");
            } else {

                $sql = "UPDATE tb_customers SET
                cus_full_name = :cus_full_name,
                cus_phone = :cus_phone,
                cus_email = :cus_email,
                cus_address = :cus_address,
                cus_fiscal_number = :cus_fiscal_number
                WHERE cus_id = :cus_id";

                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    ':cus_full_name'     => $cus_full_name,
                    ':cus_phone'         => $cus_phone,
                    ':cus_email'         => $cus_email,
                    ':cus_address'       => $cus_address,
                    ':cus_fiscal_number' => $cus_fiscal_number,
                    ':cus_id'            => $cus_id
                ]);

                $response = array("message" => $translate['updated_successfully'], "status" => "success");
            }

            $pdo->commit();
        } catch (\RuntimeException | \InvalidArgumentException $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            $response = array("status" => "error", "message" => "Database error. {$translate['execution_failed_please_contact_the_system_vendor_for_technical_assistance']} <br>" . $e->getMessage(), "redirect" => true);
        } catch (\PDOException $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            $response = array("status" => "error", "message" => "Database error. {$translate['execution_failed_please_contact_the_system_vendor_for_technical_assistance']} <br>" . $e->getMessage(), "redirect" => true);
        }

        echo json_encode($response);

        break;


    case 'ajaxGetCustomerDataById':
        $cus_id = isset($_GET["cus_id"]) ? funCheckInput($_GET["cus_id"]) : "";
        $sql = "SELECT * FROM tb_customers WHERE cus_id = {$cus_id};";
        $cusInfo = executePDOFetchOneAPI($sql);
        echo json_encode($cusInfo);
        break;


    case 'ajaxCustomersList':

        $ent_id = isset($_GET["ent_id"]) ? funCheckInput($_GET["ent_id"]) : "";
        $status = isset($_GET["status"]) ? funCheckInput($_GET["status"]) : "";

        $whereDebt = "WHERE sale_cus_id = cus_id AND sale_status = 'Enabled' AND sale_debt_status = 'On') > 0";
        if ($status == "without_debt") $whereDebt = "WHERE sale_cus_id = cus_id AND sale_status = 'Enabled' AND sale_debt_status = 'On') <= 0";

        $sql = "SELECT 
        cus_id
        ,cus_ent_id
        ,cus_pos_id
        ,(select pos_name FROM tb_pos WHERE cus_pos_id = pos_id) AS pos_name
        ,cus_full_name
        ,cus_phone
        ,cus_address
        ,cus_email
        ,cus_fiscal_number
        ,(SELECT 
            SUM(CASE WHEN pay_det_cashback_status = 'Available' THEN pay_det_cashback ELSE 0 END) - 
            SUM(CASE WHEN pay_det_cashback_status = 'Used' THEN pay_det_cashback ELSE 0 END)
            FROM tb_payments_details
            WHERE pay_det_cus_id = cus_id AND pay_det_status = 'Enabled') AS cus_available_cashback
        ,(SELECT IFNULL(SUM(sale_debt),0) FROM tb_sales WHERE sale_cus_id = cus_id AND sale_status = 'Enabled' AND sale_debt_status = 'On') AS cus_debt
        FROM tb_customers
        WHERE cus_ent_id = '{$ent_id}' AND (SELECT IFNULL(SUM(sale_debt),0) FROM tb_sales $whereDebt;";

        // exit($sql);
        $rows = executePDOFetchAllAPI($sql);
        echo json_encode($rows);

        break;


    case 'ajaxSelectCustomer':

        $ent_id = isset($_GET["ent_id"]) ? funCheckInput($_GET["ent_id"]) : "";
        $cus_id = isset($_GET["cus_id"]) ? funCheckInput($_GET["cus_id"]) : "";

        $cus_ent_id = $ent_id ? $ent_id : $session_user_ent_id;

        $whereCusId = "";
        if (!empty($cus_id)) $whereCusId = "AND cus_id = {$cus_id}";

        $sql = "SELECT
        cus_id
        ,cus_full_name
        FROM tb_customers
        WHERE cus_ent_id = {$cus_ent_id}
        $whereCusId
        GROUP BY cus_id ORDER BY cus_full_name ASC;";

        // exit($sql);

        $rows = executePDOFetchAllAPI($sql);
        echo json_encode($rows);

        break;


    case 'ajaxShowCustomerDebt':

        $ent_id = isset($_GET["ent_id"]) ? funCheckInput($_GET["ent_id"]) : "";
        $cus_id = isset($_GET["cus_id"]) ? funCheckInput($_GET["cus_id"]) : "";

        if (!empty(trim((string) $cus_id))) {

            $sql = "SELECT 
            IFNULL(SUM(sale_debt),0) AS total_client_debt
            FROM tb_sales
            WHERE sale_ent_id = {$ent_id}
            AND sale_cus_id = {$cus_id} AND sale_status = 'Enabled' AND sale_debt_status = 'On';";
            $answer = executePDOFetchOne($sql, $translate['customer_debt_could_not_be_displayed']);
            echo $answer['total_client_debt'];
        }

        break;


    case 'ajaxShowCustomerPendingOrders':

        $cus_id = isset($_GET["cus_id"]) ? funCheckInput($_GET["cus_id"]) : "";

        $sql = "SELECT
        sale_id
        ,sale_cus_id
        ,sale_ent_id
        ,sale_pos_id
        ,(SELECT pos_name FROM tb_pos WHERE sale_pos_id = pos_id) AS pos_name
        ,sale_code
        ,DATE_FORMAT(sale_date, '%d-%b-%Y %H:%i:%s') AS sale_date
        ,(select CONCAT(user_firstname,' ',user_lastname) FROM tb_users WHERE user_id = sale_created_by) AS waiter_full_name
        ,sale_type
        ,IFNULL((SELECT IFNULL(SUM(sale_det_pro_price*sale_det_pro_qty),0) FROM tb_sales_details WHERE sale_det_sale_id = sale_id)+(IFNULL(sale_cost_delivery,0)+IFNULL(sale_tax_value,0)+IFNULL(sale_tax_law_value,0)+(IFNULL(sale_type_tax_value,0)))-IFNULL(sale_discount_value,0),0) AS final_total
        ,sale_debt
        FROM tb_sales
        LEFT JOIN tb_sales_details ON  sale_det_sale_id = sale_id
        WHERE sale_status = 'Enabled' AND sale_debt > 0 AND sale_debt_status = 'On' AND sale_cus_id = {$cus_id}
        GROUP BY sale_id ORDER BY sale_id DESC;";
        // exit($sql);
        $rows = executePDOFetchAllAPI($sql);
        echo json_encode($rows);

        break;


    case 'ajaxGetCustomerCashBack':

        $cus_id = isset($_GET["cus_id"]) ? funCheckInput($_GET["cus_id"]) : "";

        if (!empty($cus_id)) {

            $sql = "SELECT 
            SUM(CASE WHEN pay_det_cashback_status = 'Available' THEN pay_det_cashback ELSE 0 END) - 
            SUM(CASE WHEN pay_det_cashback_status = 'Used' THEN pay_det_cashback ELSE 0 END) AS available_cashback
            FROM tb_payments_details
            WHERE pay_det_cus_id = '{$cus_id}' AND pay_det_status = 'Enabled';";

            $answer = executePDOFetchOneAPI($sql);
            echo json_encode($answer);
        }

        break;
}
