<?php
require_once "../../includes/db.php";
header('Content-Type: application/json');
session_start(['read_and_close' => true]);

$coid   = $_SESSION['co_id'];
$user_id   = $_SESSION['user_id'];

session_write_close();

$Pcsh_limit  = $_GET['Pcsh_limit'] ?? 10;
$Pcsh_offset = $_GET['Pcsh_offset'] ?? 0;
$storeid = $_GET['storeid'] ?? 0;

$stmt = $pdo->prepare("
    SELECT 
    d.co_id,
    d.t_date,
    DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
    d.t_tt_doc,
    d.t_doc_no,
    d.t_type,
    d.t_ct_det,
    a.acc_name,
    d.t_status,
    SUM(CASE WHEN d.t_type = 'DEL' THEN d.t_credit ELSE 0 END) +
    SUM(CASE WHEN d.t_type = 'GR' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'REC' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'EXP' THEN d.t_credit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'CS INV' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'INV RET' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'INV PMT' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'STK INV' THEN d.t_debit ELSE 0 END) AS total_result

FROM accounts a

JOIN transactions d 
    ON a.id = d.t_cr

WHERE 
-- ✅ Permission logic (safe version)
    EXISTS (
        SELECT 1
        FROM permissions p
        WHERE d.co_id = :coid
          AND p.user_id = :user_id        
        AND p.pm_acc_lvl IN (3,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 = '')
                )
          )
    )

    -- ✅ Filters
 
AND

(CASE WHEN (d.t_type = 'DEL' OR d.t_type = 'GR')  THEN d.t_copy = 'O' ELSE d.t_copy = 'D' END)
AND d.t_status = 'Pending'
AND a.acc_categ IN ('sales_person', 'customer', 'expense')
AND d.t_type IN ('REC', 'EXP', 'CS INV', 'INV PMT')


GROUP BY 
    d.co_id,
    d.t_date,
    d.t_tt_doc,
    d.t_doc_no,
    d.t_type,
    d.t_ct_det,
    a.acc_name,
    d.t_status

ORDER BY d.t_date DESC,d.t_doc_no
        LIMIT :Pcsh_limit OFFSET :Pcsh_offset
");

//$stmt->bindValue(':storeid', $storeid, PDO::PARAM_INT);
$stmt->bindValue(':coid', $coid, PDO::PARAM_INT);
$stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);
$stmt->bindValue(':Pcsh_limit', (int)$Pcsh_limit, PDO::PARAM_INT);
$stmt->bindValue(':Pcsh_offset', (int)$Pcsh_offset, PDO::PARAM_INT);
//$stmt->bindValue(':user_id1', $user_id, PDO::PARAM_INT);
$stmt->execute();

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