<?php
/*
Supplier Payment Management System - Report Center
ERP Style GUI | PHP 8.2 Compatible | Light Green Theme
Includes Advanced Filtering (Date & Vendor)
*/

session_start();

// Database configuration
$host = 'localhost';
$dbname = 'whitetil_mount';
$username = 'whitetil_mount';
$password = 'UGeC5=E)toCtUK(j';

error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database Connection Failed: " . $e->getMessage());
}

// ---------------------------------------------------------
// 1. Handle Filters
// ---------------------------------------------------------
$fromDate = $_GET['from_date'] ?? date('Y-m-01'); // Default to 1st of current month
$toDate = $_GET['to_date'] ?? date('Y-m-d');      // Default to today
$vendorCode = $_GET['ven_code'] ?? '';

// Fetch all vendors for the dropdown
$stmtVendors = $pdo->query("SELECT code, name FROM vendor WHERE cat IN ('S', 'B') ORDER BY name");
$allVendors = $stmtVendors->fetchAll(PDO::FETCH_ASSOC);

// ---------------------------------------------------------
// 2. Query: Recent Payments Ledger
// ---------------------------------------------------------
$payQuery = "
    SELECT 
        p.REFNO, 
        p.BDATE, 
        p.NAME as vendor_name, 
        p.bank_name, 
        p.CHENO, 
        p.AMOUNT, 
        p.PAY_TYPE,
        (SELECT COUNT(c_bal_no) FROM c_bal_sttr WHERE refno = p.REFNO) as invoices_settled
    FROM a_paymas p
    WHERE p.BDATE BETWEEN :from_date AND :to_date
";
$payParams = [
    ':from_date' => $fromDate,
    ':to_date' => $toDate
];

if (!empty($vendorCode)) {
    $payQuery .= " AND p.VEN_CODE = :ven_code";
    $payParams[':ven_code'] = $vendorCode;
}

$payQuery .= " ORDER BY p.BDATE DESC, p.REFNO DESC LIMIT 200";

$stmtPayments = $pdo->prepare($payQuery);
$stmtPayments->execute($payParams);
$recentPayments = $stmtPayments->fetchAll(PDO::FETCH_ASSOC);

// ---------------------------------------------------------
// 3. Query: Vendor Outstanding Liabilities
// ---------------------------------------------------------
$balQuery = "
    SELECT 
        v.name as vendor_name,
        v.code as vendor_code,
        COUNT(c.REFNO) as pending_invoices,
        SUM(c.AMOUNT) as total_billed,
        SUM(c.totpay) as total_paid,
        SUM(c.Balance) as total_outstanding
    FROM vendor v
    JOIN c_bal c ON v.code = c.CUSCODE
    WHERE c.Balance > 0 AND c.SDATE BETWEEN :from_date AND :to_date
";
$balParams = [
    ':from_date' => $fromDate,
    ':to_date' => $toDate
];

if (!empty($vendorCode)) {
    $balQuery .= " AND v.code = :ven_code";
    $balParams[':ven_code'] = $vendorCode;
}

$balQuery .= " GROUP BY v.code, v.name ORDER BY total_outstanding DESC";

$stmtBalances = $pdo->prepare($balQuery);
$stmtBalances->execute($balParams);
$vendorBalances = $stmtBalances->fetchAll(PDO::FETCH_ASSOC);

// Calculate Totals for Summary
$totalBilledAll = 0;
$totalPaidAll = 0;
$totalOutAll = 0;
foreach($vendorBalances as $vb) {
    $totalBilledAll += $vb['total_billed'];
    $totalPaidAll += $vb['total_paid'];
    $totalOutAll += $vb['total_outstanding'];
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Supplier Report Center | ERP System</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
        }
        
        body {
            background: linear-gradient(135deg, #e8f5e9 0%, #c8e6c9 100%);
            min-height: 100vh;
            color: #1a3a1a;
            padding: 20px;
        }
        
        .header {
            background: #2e5c2e;
            padding: 15px 20px;
            border-radius: 12px;
            color: white;
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 15px;
            box-shadow: 0 4px 12px rgba(0,0,0,0.15);
        }

        .header h1 { font-size: 18px; }

        .btn {
            padding: 8px 18px;
            border: none;
            border-radius: 40px;
            font-weight: bold;
            cursor: pointer;
            text-decoration: none;
            font-size: 12px;
            color: #1e2a1e;
            transition: all 0.2s;
        }

        .btn-warning { background: #ffd966; border-bottom: 3px solid #b8860b; }
        .btn-warning:hover { background: #ffedb3; transform: translateY(-1px); }
        
        .btn-success { background: #81c784; border-bottom: 3px solid #2e7d32; color: #fff;}
        .btn-success:hover { background: #a5d6a7; transform: translateY(-1px); }

        .filter-bar {
            background: rgba(255,255,245,0.95);
            padding: 15px 20px;
            border-radius: 12px;
            margin-bottom: 20px;
            box-shadow: 0 4px 15px rgba(0,0,0,0.1);
            display: flex;
            gap: 15px;
            align-items: center;
            flex-wrap: wrap;
        }

        .filter-group {
            display: flex;
            flex-direction: column;
            gap: 5px;
        }

        .filter-group label {
            font-size: 11px;
            font-weight: bold;
            color: #2c5e2c;
            text-transform: uppercase;
        }

        .filter-group input, .filter-group select {
            padding: 8px 12px;
            border: 1px solid #b9f6ca;
            border-radius: 20px;
            font-size: 12px;
            background: #fff;
            outline: none;
            min-width: 150px;
        }

        .filter-group input:focus, .filter-group select:focus {
            border-color: #4caf50;
            box-shadow: 0 0 0 2px rgba(76,175,80,0.2);
        }

        .dashboard-grid {
            display: grid;
            grid-template-columns: 1fr;
            gap: 20px;
        }

        .card {
            background: rgba(255,255,245,0.95);
            border-radius: 12px;
            box-shadow: 0 4px 15px rgba(0,0,0,0.1);
            overflow: hidden;
        }

        .card-header {
            background: #3c6e3c;
            padding: 12px 15px;
            color: white;
            font-weight: bold;
            font-size: 14px;
            display: flex;
            justify-content: space-between;
        }

        .table-container {
            overflow-x: auto;
            max-height: 400px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 12px;
        }

        th, td {
            padding: 10px;
            text-align: left;
            border-bottom: 1px solid #c8e6c9;
        }

        th {
            background: #dcedc8;
            color: #1b5e1b;
            position: sticky;
            top: 0;
            z-index: 10;
        }

        tr:hover { background-color: #f1f8e9; }

        .text-right { text-align: right; }
        .text-bold { font-weight: bold; }
        
        .badge {
            padding: 3px 8px;
            border-radius: 12px;
            font-size: 10px;
            font-weight: bold;
            background: #81c784;
            color: #fff;
        }
        .badge-warning { background: #ffb74d; color: #fff;}
        
        .summary-row {
            background: #e8f5e9;
            font-weight: bold;
            border-top: 2px solid #a5d6a7;
        }
    </style>
</head>
<body>

    <div class="header">
        <h1>📊 ERP Report Center - Supplier Analysis</h1>
        <a href="Account.php" class="btn btn-warning">🔙 Back to Payments</a>
    </div>

    <!-- Filter Bar -->
    <form method="GET" action="ac_report.php" class="filter-bar">
        <div class="filter-group">
            <label>From Date</label>
            <input type="date" name="from_date" value="<?= htmlspecialchars($fromDate) ?>">
        </div>
        <div class="filter-group">
            <label>To Date</label>
            <input type="date" name="to_date" value="<?= htmlspecialchars($toDate) ?>">
        </div>
        <div class="filter-group">
            <label>Vendor</label>
            <select name="ven_code">
                <option value="">-- All Vendors --</option>
                <?php foreach($allVendors as $v): ?>
                    <option value="<?= htmlspecialchars($v['code']) ?>" <?= ($vendorCode == $v['code']) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($v['name']) ?> (<?= htmlspecialchars($v['code']) ?>)
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="filter-group" style="justify-content: flex-end; padding-top: 15px;">
            <button type="submit" class="btn btn-success">🔍 Apply Filters</button>
        </div>
        <div class="filter-group" style="justify-content: flex-end; padding-top: 15px;">
            <a href="ac_report.php" class="btn btn-warning" style="text-align:center;">Reset</a>
        </div>
    </form>

    <div class="dashboard-grid">
        
        <!-- Outstanding Balances Report -->
        <div class="card">
            <div class="card-header">
                <span>🔴 Vendor Outstanding Liabilities (Filtered)</span>
            </div>
            <div class="table-container">
                <table>
                    <thead>
                        <tr>
                            <th>Vendor Name</th>
                            <th class="text-right">Pending Invoices</th>
                            <th class="text-right">Total Billed</th>
                            <th class="text-right">Total Paid</th>
                            <th class="text-right">Total Outstanding</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if(empty($vendorBalances)): ?>
                            <tr><td colspan="5" style="text-align:center; padding: 20px;">No outstanding balances found for this filter.</td></tr>
                        <?php else: ?>
                            <?php foreach($vendorBalances as $vb): ?>
                                <tr>
                                    <td><strong><?= htmlspecialchars($vb['vendor_name']) ?></strong> <br><small><?= htmlspecialchars($vb['vendor_code']) ?></small></td>
                                    <td class="text-right"><span class="badge badge-warning"><?= $vb['pending_invoices'] ?></span></td>
                                    <td class="text-right"><?= number_format($vb['total_billed'], 2) ?></td>
                                    <td class="text-right"><?= number_format($vb['total_paid'], 2) ?></td>
                                    <td class="text-right" style="color:#c62828; font-weight:bold;">
                                        <?= number_format($vb['total_outstanding'], 2) ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                            <!-- Summary Row -->
                            <tr class="summary-row">
                                <td colspan="2" class="text-right">REPORT TOTALS:</td>
                                <td class="text-right"><?= number_format($totalBilledAll, 2) ?></td>
                                <td class="text-right"><?= number_format($totalPaidAll, 2) ?></td>
                                <td class="text-right" style="color:#c62828;"><?= number_format($totalOutAll, 2) ?></td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <!-- Recent Payments Report -->
        <div class="card">
            <div class="card-header">
                <span>🟢 Payment Vouchers Ledger (Filtered)</span>
            </div>
            <div class="table-container">
                <table>
                    <thead>
                        <tr>
                            <th>Ref No</th>
                            <th>Date</th>
                            <th>Vendor</th>
                            <th>Bank & Cheque</th>
                            <th>Type</th>
                            <th class="text-right">Invoices Settled</th>
                            <th class="text-right">Amount</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if(empty($recentPayments)): ?>
                            <tr><td colspan="7" style="text-align:center; padding: 20px;">No payments found for this filter.</td></tr>
                        <?php else: ?>
                            <?php 
                            $totalPaymentAmount = 0;
                            foreach($recentPayments as $rp): 
                                $totalPaymentAmount += $rp['AMOUNT'];
                            ?>
                                <tr>
                                    <td><strong><?= htmlspecialchars($rp['REFNO']) ?></strong></td>
                                    <td><?= htmlspecialchars($rp['BDATE']) ?></td>
                                    <td><?= htmlspecialchars($rp['vendor_name']) ?></td>
                                    <td>
                                        <?= htmlspecialchars($rp['bank_name'] ?? 'N/A') ?> 
                                        <br><small style="color: #666;">Cheque: <?= htmlspecialchars($rp['CHENO'] ?? 'N/A') ?></small>
                                    </td>
                                    <td><?= htmlspecialchars($rp['PAY_TYPE']) ?></td>
                                    <td class="text-right">
                                        <span class="badge"><?= $rp['invoices_settled'] ?></span>
                                    </td>
                                    <td class="text-right" style="font-weight:bold; color:#2e7d32;">
                                        <?= number_format($rp['AMOUNT'], 2) ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                            <tr class="summary-row">
                                <td colspan="6" class="text-right">TOTAL PAYMENTS:</td>
                                <td class="text-right" style="color:#2e7d32;"><?= number_format($totalPaymentAmount, 2) ?></td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

    </div>

</body>
</html>