<?php
/**
 * api/pawn/pledge_interest.php
 * সব OPEN বন্ধকের সুদ হিসাব + due list
 * GET params: pledge_id (optional — একটার জন্য), sort (interest_desc/days_desc/amount_desc)
 */
require_once __DIR__ . '/../../config/db.php';
header('Content-Type: application/json; charset=utf-8');

$pledge_id = (int)($_GET['pledge_id'] ?? 0);
$sort      = $_GET['sort'] ?? 'days_desc';
$today     = date('Y-m-d');

// সুদ formula: outstanding × (rate/100) × (days/30)
$where = $pledge_id > 0 ? "AND p.id = $pledge_id" : "";

$sql = "
    SELECT
        p.id,
        p.pledge_code,
        p.pledge_date,
        p.loan_amount,
        p.outstanding_principal,
        p.interest_rate,
        p.holding_status,
        p.karat,
        p.gross_weight,
        pa.name  AS party_name,
        pa.phone AS party_phone,
        DATEDIFF('$today', p.pledge_date) AS days_elapsed,
        COALESCE(pm.total_interest_paid, 0) AS total_interest_paid,
        COALESCE(pm.total_principal_paid, 0) AS total_principal_paid,
        COALESCE(pm.last_payment_date, NULL) AS last_payment_date,
        COALESCE(pm.payment_count, 0) AS payment_count
    FROM pawn_pledges p
    LEFT JOIN parties pa ON pa.id = p.party_id
    LEFT JOIN (
        SELECT
            pledge_id,
            SUM(interest_paid)   AS total_interest_paid,
            SUM(principal_paid)  AS total_principal_paid,
            MAX(payment_date)    AS last_payment_date,
            COUNT(*)             AS payment_count
        FROM pawn_payments
        GROUP BY pledge_id
    ) pm ON pm.pledge_id = p.id
    WHERE p.status = 'OPEN' $where
";

// Sort
switch ($sort) {
    case 'interest_desc': $sql .= " ORDER BY (p.outstanding_principal * (p.interest_rate/100) * (DATEDIFF('$today', p.pledge_date)/30)) DESC"; break;
    case 'amount_desc':   $sql .= " ORDER BY p.outstanding_principal DESC"; break;
    case 'days_asc':      $sql .= " ORDER BY days_elapsed ASC"; break;
    default:              $sql .= " ORDER BY days_elapsed DESC";
}

$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

$result = [];
$grand_total_interest  = 0;
$grand_total_principal = 0;

foreach ($rows as $r) {
    $days     = (int)$r['days_elapsed'];
    $months   = round($days / 30, 2);
    $outstanding = (float)$r['outstanding_principal'];
    $rate     = (float)$r['interest_rate'];
    $interest = round($outstanding * ($rate / 100) * ($days / 30), 2);

    // overdue: শেষ payment-এর পর ৩০ দিনের বেশি
    $last_pay = $r['last_payment_date'];
    $days_since_payment = $last_pay
        ? (int)(new DateTime($last_pay))->diff(new DateTime($today))->days
        : $days;
    $is_overdue = $days_since_payment > 30;

    $grand_total_interest  += $interest;
    $grand_total_principal += $outstanding;

    $result[] = [
        'id'                   => (int)$r['id'],
        'pledge_code'          => $r['pledge_code'],
        'pledge_date'          => $r['pledge_date'],
        'party_name'           => $r['party_name'] ?? '—',
        'party_phone'          => $r['party_phone'] ?? '',
        'loan_amount'          => (float)$r['loan_amount'],
        'outstanding_principal'=> $outstanding,
        'interest_rate'        => $rate,
        'karat'                => $r['karat'],
        'gross_weight'         => (float)$r['gross_weight'],
        'holding_status'       => $r['holding_status'],
        'days_elapsed'         => $days,
        'months_elapsed'       => $months,
        'calc_interest'        => $interest,
        'total_due'            => round($outstanding + $interest, 2),
        'total_interest_paid'  => (float)$r['total_interest_paid'],
        'total_principal_paid' => (float)$r['total_principal_paid'],
        'last_payment_date'    => $last_pay,
        'days_since_payment'   => $days_since_payment,
        'payment_count'        => (int)$r['payment_count'],
        'is_overdue'           => $is_overdue,
    ];
}

echo json_encode([
    'success'               => true,
    'today'                 => $today,
    'count'                 => count($result),
    'grand_total_interest'  => round($grand_total_interest, 2),
    'grand_total_principal' => round($grand_total_principal, 2),
    'grand_total_due'       => round($grand_total_interest + $grand_total_principal, 2),
    'pledges'               => $result,
]);