<?php
/**
 * api/parties/get_party_ledger.php
 * Party-wise receivable/payable summary
 * Sources: sales (due), purchases (due), cash_transactions (party_id linked)
 */
ini_set('display_errors', 0);
error_reporting(E_ALL);
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';

$search   = isset($_GET['q'])       ? trim($_GET['q'])       : '';
$type     = isset($_GET['type'])    ? trim($_GET['type'])    : ''; // receivable|payable|all
$party_id = isset($_GET['party_id'])? (int)$_GET['party_id']: 0;

// ── সব parties এর due summary ──
// Sales due → আমরা পাবো (receivable)
$salesDue = $pdo->query("
    SELECT
        s.customer_id      AS party_id,
        s.customer_name_snapshot AS fallback_name,
        s.customer_phone_snapshot AS fallback_phone,
        SUM(s.grand_total) AS total_sale,
        SUM(s.paid_total)  AS total_paid,
        SUM(s.due_amount)  AS total_due,
        COUNT(*)           AS txn_count
    FROM sales s
    WHERE s.due_amount > 0
      AND s.payment_status IN ('partial','due')
    GROUP BY s.customer_id, s.customer_name_snapshot, s.customer_phone_snapshot
")->fetchAll(PDO::FETCH_ASSOC);

// Purchases due → আমরা দেবো (payable)
$purchasesDue = $pdo->query("
    SELECT
        p.party_id,
        SUM(p.total_amount) AS total_purchase,
        SUM(p.paid_amount)  AS total_paid,
        SUM(p.due_amount)   AS total_due,
        COUNT(*)            AS txn_count
    FROM purchases p
    WHERE p.due_amount > 0
      AND p.payment_status IN ('partial','due')
      AND p.deleted_at IS NULL
    GROUP BY p.party_id
")->fetchAll(PDO::FETCH_ASSOC);

// Parties info
$partiesStmt = $pdo->query("
    SELECT id, name, phone, is_customer, is_supplier
    FROM parties
    WHERE deleted_at IS NULL
");
$partiesMap = [];
while ($row = $partiesStmt->fetch(PDO::FETCH_ASSOC)) {
    $partiesMap[(int)$row['id']] = $row;
}

// ── Merge করো ──
$ledger = []; // party_id => data

// Sales due (receivable)
foreach ($salesDue as $row) {
    $pid = (int)$row['party_id'];
    $name  = '';
    $phone = '';
    if ($pid && isset($partiesMap[$pid])) {
        $name  = $partiesMap[$pid]['name'];
        $phone = $partiesMap[$pid]['phone'];
    } else {
        $name  = $row['fallback_name'] ?: 'অজানা গ্রাহক';
        $phone = $row['fallback_phone'] ?: '';
    }

    $key = $pid ?: 'sale_' . md5($name . $phone);

    if (!isset($ledger[$key])) {
        $ledger[$key] = [
            'party_id'          => $pid,
            'name'              => $name,
            'phone'             => $phone,
            'sale_due'          => 0,
            'purchase_due'      => 0,
            'sale_txn_count'    => 0,
            'purchase_txn_count'=> 0,
        ];
    }
    $ledger[$key]['sale_due']       += (float)$row['total_due'];
    $ledger[$key]['sale_txn_count'] += (int)$row['txn_count'];
}

// Purchases due (payable)
foreach ($purchasesDue as $row) {
    $pid = (int)$row['party_id'];
    if (!$pid) continue;

    $name  = isset($partiesMap[$pid]) ? $partiesMap[$pid]['name']  : 'অজানা সাপ্লায়ার';
    $phone = isset($partiesMap[$pid]) ? $partiesMap[$pid]['phone'] : '';

    if (!isset($ledger[$pid])) {
        $ledger[$pid] = [
            'party_id'          => $pid,
            'name'              => $name,
            'phone'             => $phone,
            'sale_due'          => 0,
            'purchase_due'      => 0,
            'sale_txn_count'    => 0,
            'purchase_txn_count'=> 0,
        ];
    }
    $ledger[$pid]['purchase_due']       += (float)$row['total_due'];
    $ledger[$pid]['purchase_txn_count'] += (int)$row['txn_count'];
}

// ── Format & filter ──
$result = [];
$total_receivable = 0;
$total_payable    = 0;

foreach ($ledger as $row) {
    $sale_due     = (float)$row['sale_due'];
    $purchase_due = (float)$row['purchase_due'];
    $net          = $sale_due - $purchase_due; // + = আমরা পাবো, - = আমরা দেবো

    // Search filter
    if ($search) {
        $s = mb_strtolower($search);
        if (mb_strpos(mb_strtolower($row['name']), $s) === false &&
            mb_strpos($row['phone'], $search) === false) {
            continue;
        }
    }

    // Type filter
    if ($type === 'receivable' && $sale_due <= 0) continue;
    if ($type === 'payable'    && $purchase_due <= 0) continue;

    // Specific party
    if ($party_id > 0 && (int)$row['party_id'] !== $party_id) continue;

    $total_receivable += $sale_due;
    $total_payable    += $purchase_due;

    $result[] = [
        'party_id'           => $row['party_id'],
        'name'               => $row['name'],
        'phone'              => $row['phone'],
        'sale_due'           => round($sale_due, 2),
        'purchase_due'       => round($purchase_due, 2),
        'net'                => round($net, 2),
        'status'             => $net > 0.01 ? 'receivable' : ($net < -0.01 ? 'payable' : 'settled'),
        'sale_txn_count'     => $row['sale_txn_count'],
        'purchase_txn_count' => $row['purchase_txn_count'],
    ];
}

// Sort: receivable আগে, তারপর payable, amount অনুযায়ী
usort($result, function($a, $b) {
    if ($a['status'] === $b['status']) {
        return abs($b['net']) <=> abs($a['net']);
    }
    $order = ['receivable'=>0,'payable'=>1,'settled'=>2];
    return ($order[$a['status']]??2) <=> ($order[$b['status']]??2);
});

echo json_encode([
    'success' => true,
    'summary' => [
        'total_receivable' => round($total_receivable, 2),
        'total_payable'    => round($total_payable, 2),
        'net'              => round($total_receivable - $total_payable, 2),
        'party_count'      => count($result),
    ],
    'parties' => $result,
], JSON_UNESCAPED_UNICODE);