<?php
header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/../../config/db.php';

function out($ok,$msg='',$data=[],$code=200){
    http_response_code($code);
    echo json_encode(['success'=>$ok,'message'=>$msg,'data'=>$data],JSON_UNESCAPED_UNICODE);
    exit;
}

try {
    /* ==================== ইনপুট ==================== */
    $from_date   = trim($_GET['from_date'] ?? '');
    $to_date     = trim($_GET['to_date'] ?? '');
    $search      = trim($_GET['search'] ?? '');        // নাম / মোবাইল
    $pledge_code = trim($_GET['pledge_code'] ?? '');   // বন্ধক কোড
    $barcode     = trim($_GET['barcode'] ?? '');       // বারকোড / নোট
    $status      = trim($_GET['status'] ?? '');        // OPEN / LOCKER / CLOSED / ALL
    $limit       = (int)($_GET['limit'] ?? 100);

    // নতুন প্যারামিটার
    $loan_min   = isset($_GET['loan_min'])   && $_GET['loan_min']   !== '' ? (float)$_GET['loan_min']   : null;
    $loan_max   = isset($_GET['loan_max'])   && $_GET['loan_max']   !== '' ? (float)$_GET['loan_max']   : null;
    $karat      = trim($_GET['karat']      ?? '');    // 24K/22K/21K/18K/SANATAN
    $quick_tab  = trim($_GET['quick_tab']  ?? '');    // today/this_month/over_90days/risky
    $sort_by    = trim($_GET['sort_by']    ?? 'date_desc'); // date_desc/date_asc/loan_desc/loan_asc/name_asc

    if($limit <= 0)   $limit = 100;
    if($limit > 500)  $limit = 500;

    /* ==================== তারিখ লজিক ==================== */
    // সার্চ/কোড/বারকোড/কুইক ট্যাব থাকলে তারিখ ফিল্টার করব না
    $has_other_filter = (
        $search !== '' ||
        $pledge_code !== '' ||
        $barcode !== '' ||
        $quick_tab !== ''
    );
    $use_date = !$has_other_filter;

    if($use_date){
        if($from_date === '') $from_date = date('Y-m-01');
        if($to_date === '')   $to_date   = date('Y-m-d');
    }

    $where  = [];
    $params = [];

    // তারিখ ফিল্টার
    if($use_date){
        $where[]  = 'p.pledge_date BETWEEN :from_date AND :to_date';
        $params[':from_date'] = $from_date;
        $params[':to_date']   = $to_date;
    }

    // নাম / মোবাইল সার্চ
    if($search !== ''){
        $where[]  = '(pa.name LIKE :search OR pa.phone LIKE :search2)';
        $params[':search']  = '%'.$search.'%';
        $params[':search2'] = '%'.$search.'%';
    }

    // বারকোড / নোট সার্চ
    if($barcode !== ''){
        $where[]  = '(p.manual_barcode LIKE :barcode OR p.note LIKE :barcode2)';
        $params[':barcode']  = '%'.$barcode.'%';
        $params[':barcode2'] = '%'.$barcode.'%';
    }

    // বন্ধক কোড সার্চ
    if($pledge_code !== ''){
        $where[]  = 'p.pledge_code LIKE :pcode';
        $params[':pcode'] = '%'.$pledge_code.'%';
    }

    // স্ট্যাটাস ফিল্টার
    if($status !== '' && $status !== 'ALL'){
        if($status === 'LOCKER'){
            $where[] = "(p.status != 'CLOSED' AND p.holding_status = 'LOCKER')";
        } elseif($status === 'OPEN'){
            $where[] = "(p.status != 'CLOSED' AND p.holding_status != 'LOCKER')";
        } elseif($status === 'CLOSED'){
            $where[] = "p.status = 'CLOSED'";
        }
    }

    // ঋণের রেঞ্জ
    if($loan_min !== null){
        $where[] = 'p.loan_amount >= :loan_min';
        $params[':loan_min'] = $loan_min;
    }
    if($loan_max !== null){
        $where[] = 'p.loan_amount <= :loan_max';
        $params[':loan_max'] = $loan_max;
    }

    // ক্যারেট ফিল্টার
    if($karat !== ''){
        $where[] = 'p.karat = :karat';
        $params[':karat'] = $karat;
    }

    // দ্রুত ট্যাব ফিল্টার
    if($quick_tab !== ''){
        $today_str       = date('Y-m-d');
        $month_start_str = date('Y-m-01');
        $days_90_ago     = date('Y-m-d', strtotime('-90 days'));

        switch($quick_tab){
            case 'today':
                $where[] = 'p.pledge_date = :q_today';
                $params[':q_today'] = $today_str;
                break;

            case 'this_month':
                $where[] = 'p.pledge_date BETWEEN :q_mstart AND :q_mend';
                $params[':q_mstart'] = $month_start_str;
                $params[':q_mend']   = $today_str;
                break;

            case 'over_90days':
                // ৯০ দিনের বেশি পুরানো + এখনো CLOSED হয়নি
                $where[] = "p.pledge_date <= :q_90 AND p.status != 'CLOSED'";
                $params[':q_90'] = $days_90_ago;
                break;

            case 'risky':
                // নিরাপত্তা মার্জিন ঋণাত্মক: outstanding > gold_value
                $where[] = "p.outstanding_principal > p.gold_value AND p.status != 'CLOSED'";
                break;
        }
    }

    $whereSQL = count($where) ? 'WHERE '.implode(' AND ', $where) : '';

    /* ==================== সর্টিং ==================== */
    $orderBy = 'p.id DESC'; // default
    switch($sort_by){
        case 'date_asc':   $orderBy = 'p.pledge_date ASC, p.id ASC'; break;
        case 'date_desc':  $orderBy = 'p.pledge_date DESC, p.id DESC'; break;
        case 'loan_desc':  $orderBy = 'p.loan_amount DESC, p.id DESC'; break;
        case 'loan_asc':   $orderBy = 'p.loan_amount ASC, p.id ASC'; break;
        case 'name_asc':   $orderBy = 'pa.name ASC, p.id DESC'; break;
        case 'name_desc':  $orderBy = 'pa.name DESC, p.id DESC'; break;
    }

    $sql = "
        SELECT
            p.id, p.pledge_code, p.manual_barcode, p.party_id,
            p.pledge_date, p.karat, p.gross_weight, p.system_purity,
            p.deduction, p.actual_purity, p.pure_weight, p.gold_value,
            p.loan_amount, p.outstanding_principal, p.interest_rate,
            p.location_id, p.status, p.holding_status, p.note,
            p.closed_at, p.created_at,
            DATEDIFF(CURDATE(), p.pledge_date) AS days_elapsed,
            (p.gold_value - p.outstanding_principal) AS safety_margin,
            COALESCE(pa.name,'')  AS party_name,
            COALESCE(pa.phone,'') AS party_phone,
            COALESCE(sl.name,'')  AS location_name,
            COALESCE(pay.total_payment_count,0)   AS total_payment_count,
            COALESCE(pay.total_payment_amount,0)  AS total_payment_amount,
            COALESCE(pay.total_interest_paid,0)   AS total_interest_paid,
            COALESCE(pay.total_principal_paid,0)  AS total_principal_paid
        FROM pawn_pledges p
        LEFT JOIN parties pa ON pa.id = p.party_id
        LEFT JOIN stock_locations sl ON sl.id = p.location_id
        LEFT JOIN (
            SELECT pledge_id,
                COUNT(*) AS total_payment_count,
                SUM(COALESCE(amount,0)) AS total_payment_amount,
                SUM(COALESCE(interest_paid,0)) AS total_interest_paid,
                SUM(COALESCE(principal_paid,0)) AS total_principal_paid
            FROM pawn_payments GROUP BY pledge_id
        ) pay ON pay.pledge_id = p.id
        $whereSQL
        ORDER BY $orderBy
        LIMIT $limit
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $pledges = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $summary = [
        'total_entries'=>0,'total_loan_amount'=>0,'total_gold_value'=>0,
        'total_interest_paid'=>0,'total_principal_paid'=>0,'total_outstanding'=>0,
        'count_over_90days'=>0,'count_risky'=>0
    ];

    foreach($pledges as &$row){
        $pa = (float)($row['total_payment_amount']??0);
        $pr = (float)($row['total_principal_paid']??0);
        $in = (float)($row['total_interest_paid']??0);
        if($pr<=0 && $in<=0 && $pa>0){ $pr=$pa; $row['total_principal_paid']=$pr; }

        $summary['total_entries']++;
        $summary['total_loan_amount']     += (float)($row['loan_amount']??0);
        $summary['total_gold_value']      += (float)($row['gold_value']??0);
        $summary['total_interest_paid']   += $in;
        $summary['total_principal_paid']  += $pr;
        $summary['total_outstanding']     += (float)($row['outstanding_principal']??0);

        // সারাংশে ৯০ দিন পার ও ঝুঁকিপূর্ণ গণনা
        $days = (int)($row['days_elapsed']??0);
        $margin = (float)($row['safety_margin']??0);
        $st = strtoupper((string)($row['status']??''));
        if($st !== 'CLOSED'){
            if($days >= 90)    $summary['count_over_90days']++;
            if($margin < 0)    $summary['count_risky']++;
        }
    }
    unset($row);

    out(true,'OK',['pledges'=>$pledges,'summary'=>$summary]);

} catch(Throwable $e){
    out(false,'Server error: '.$e->getMessage(),[
        'pledges'=>[],'summary'=>[
            'total_entries'=>0,'total_loan_amount'=>0,'total_gold_value'=>0,
            'total_interest_paid'=>0,'total_principal_paid'=>0,'total_outstanding'=>0,
            'count_over_90days'=>0,'count_risky'=>0
        ]
    ],500);
}
?>