<?php
require_once "../../includes/db.php";
header('Content-Type: application/json');
session_start();

$coid   = $_SESSION['co_id'];
$user_id   = $_SESSION['user_id'];
$comp_id = $_GET['comp_id'] ?? 0;

$stb_limit  = $_GET['stb_limit'] ?? 10;
$stb_offset = $_GET['stb_offset'] ?? 0;
$storeid = $_GET['storeid'] ?? 0;

$stmt = $pdo->prepare("
SELECT 
    d.co_id, 
    d.t_cr, 
    a.acc_name, 
    a.acc_categ,

    SUM(
        (CASE WHEN d.t_type = 'GR'  AND d.t_copy = 'O' THEN d.t_debit ELSE 0 END) +
        (CASE WHEN d.t_type = 'REC' AND d.t_copy = 'D' THEN d.t_debit ELSE 0 END) -
        (CASE WHEN d.t_type = 'DEL' AND d.t_copy = 'O' THEN d.t_credit ELSE 0 END) -
        (CASE WHEN d.t_type = 'OPBL' AND d.t_copy = 'O' THEN d.t_credit ELSE 0 END) +
        (CASE WHEN d.t_type = 'OPBL' AND d.t_copy = 'D' THEN d.t_debit ELSE 0 END)
    ) AS cr_balance,     ABS(SUM(
        (CASE WHEN d.t_type = 'GR'  AND d.t_copy = 'O' THEN d.t_debit ELSE 0 END) +
        (CASE WHEN d.t_type = 'REC' AND d.t_copy = 'D' THEN d.t_debit ELSE 0 END) -
        (CASE WHEN d.t_type = 'DEL' AND d.t_copy = 'O' THEN d.t_credit ELSE 0 END) -
        (CASE WHEN d.t_type = 'OPBL' AND d.t_copy = 'O' THEN d.t_credit ELSE 0 END) +
        (CASE WHEN d.t_type = 'OPBL' AND d.t_copy = 'D' THEN d.t_debit ELSE 0 END)
    )) AS ABS_cr_balance

FROM accounts a

JOIN transactions d 
    ON a.id = d.t_cr

WHERE 
    EXISTS (
        SELECT 1
        FROM permissions p
        WHERE p.co_id = :comp_id
          AND p.user_id = :user_id  
          AND d.co_id = :comp_id
          AND p.pm_acc_lvl IN (0,2,4,5,6,7)
          AND (
                p.pm_acc_id = a.id 
                OR (
                    p.pm_acc_categ = a.acc_categ 
                    AND (p.pm_acc_id IS NULL OR p.pm_acc_id = '')
                )
          )
    )

    AND d.t_status IN ('Confirmed','Approved')
    AND a.acc_categ = 'sales_person'
    AND d.t_type IN ('DEL', 'GR', 'REC', 'OPBL')
    AND d.t_date < DATE_SUB(NOW(), INTERVAL 9 HOUR)

GROUP BY 
    d.co_id, d.t_cr, a.acc_name, a.acc_categ


ORDER BY 
    cr_balance ASC
        LIMIT :stb_limit OFFSET :stb_offset
");

//$stmt->bindValue(':storeid', $storeid, PDO::PARAM_INT);
$stmt->bindValue(':comp_id', $comp_id, PDO::PARAM_INT);
$stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);
$stmt->bindValue(':comp_id1', $comp_id, PDO::PARAM_INT);
$stmt->bindValue(':stb_limit', (int)$stb_limit, PDO::PARAM_INT);
$stmt->bindValue(':stb_offset', (int)$stb_offset, PDO::PARAM_INT);
$stmt->execute();

echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));
exit;