<?php
require_once __DIR__ . '/../config.php';
requireRole(['admin', 'manager', 'clerk']);

$pageTitle = 'Inventory Aging Analysis';
require_once __DIR__ . '/../includes/admin-header.php';

require_once __DIR__ . '/../lib/logic.php';

$pdo = getDBConnection();

$agingSql = "
    SELECT 
        iv.item_code,
        iv.item_description,
        iv.item_group,
        iv.qty as current_qty,
        iv.unit_cost,
        (iv.qty * iv.unit_cost) as current_value,
        COALESCE(g.last_grv_date, '1970-01-01') as last_receiving_date,
        DATEDIFF(CURDATE(), COALESCE(g.last_grv_date, CURDATE())) as days_since_receiving
    FROM inventory_valuation iv
    LEFT JOIN (
        SELECT Item_Code, MAX(GRV_Date) as last_grv_date
        FROM grv
        GROUP BY Item_Code
    ) g ON iv.item_code = g.Item_Code
    WHERE iv.valuation_date = (SELECT MAX(valuation_date) FROM inventory_valuation)
    ORDER BY iv.item_group, days_since_receiving DESC
";

$stmt = $pdo->query($agingSql);
$agingData = $stmt->fetchAll();

$agingBuckets = [
    '1-90' => ['label' => '1-90 Days (FSN: F)', 'min' => 0, 'max' => 90, 'data' => []],
    '91-180' => ['label' => '91-180 Days (FSN: S)', 'min' => 91, 'max' => 180, 'data' => []],
    '181-270' => ['label' => '181-270 Days', 'min' => 181, 'max' => 270, 'data' => []],
    '271-360' => ['label' => '271-360 Days', 'min' => 271, 'max' => 360, 'data' => []],
    'over361' => ['label' => '>361 Days (FSN: N)', 'min' => 361, 'max' => 99999, 'data' => []]
];

$groupData = [];
$totalValue = 0;
$bucketTotals = ['1-90' => 0, '91-180' => 0, '181-270' => 0, '271-360' => 0, 'over361' => 0];

foreach ($agingData as $row) {
    $days = (int)$row['days_since_receiving'];
    $value = (float)$row['current_value'];
    $group = $row['item_group'] ?: 'Ungrouped';
    
    if (!isset($groupData[$group])) {
        $groupData[$group] = [
            'name' => $group,
            '1-90' => 0,
            '91-180' => 0,
            '181-270' => 0,
            '271-360' => 0,
            'over361' => 0,
            'total' => 0
        ];
    }
    
    $bucket = 'over361';
    if ($days <= 90) $bucket = '1-90';
    elseif ($days <= 180) $bucket = '91-180';
    elseif ($days <= 270) $bucket = '181-270';
    elseif ($days <= 360) $bucket = '271-360';
    
    $groupData[$group][$bucket] += $value;
    $groupData[$group]['total'] += $value;
    $bucketTotals[$bucket] += $value;
    $totalValue += $value;
}

$bucketPercentages = [];
foreach ($bucketTotals as $key => $val) {
    $bucketPercentages[$key] = $totalValue > 0 ? round(($val / $totalValue) * 100, 1) : 0;
}

function formatCurrency($val) {
    return '$' . number_format($val, 0);
}
?>

<div class="container-fluid py-4">
    <div class="d-flex justify-content-between align-items-center mb-4">
        <div>
            <h4 class="mb-0">Inventory Aging Analysis</h4>
            <small class="text-muted">Based on Last Receiving Date from GRV & Current Valuation</small>
        </div>
        <div class="d-flex gap-2">
            <select class="form-select form-select-sm" id="monthSelect" style="width: 150px;">
                <option value="Apr" selected>April 2026</option>
                <option value="Mar">March 2026</option>
            </select>
            <button class="btn btn-sm btn-outline-dark" onclick="window.print()">
                <i class="bi bi-printer"></i> Export PDF
            </button>
        </div>
    </div>

    <div class="row g-3 mb-4">
        <div class="col-md-3">
            <div class="card h-100">
                <div class="card-body">
                    <small class="text-muted text-uppercase fw-bold" style="font-size: 10px;">Total Valuation</small>
                    <h4 class="mb-0 mt-1"><?php echo formatCurrency($totalValue); ?></h4>
                    <small class="text-success">● System Live Data</small>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card h-100">
                <div class="card-body">
                    <small class="text-muted text-uppercase fw-bold" style="font-size: 10px;">1-90 Days (FSN: F)</small>
                    <h4 class="mb-0 mt-1"><?php echo formatCurrency($bucketTotals['1-90']); ?></h4>
                    <small class="text-muted"><?php echo $bucketPercentages['1-90']; ?>% of total</small>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card h-100">
                <div class="card-body">
                    <small class="text-muted text-uppercase fw-bold" style="font-size: 10px;">91-180 Days (FSN: S)</small>
                    <h4 class="mb-0 mt-1"><?php echo formatCurrency($bucketTotals['91-180']); ?></h4>
                    <small class="text-muted"><?php echo $bucketPercentages['91-180']; ?>% of total</small>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card h-100 border-danger">
                <div class="card-body">
                    <small class="text-muted text-uppercase fw-bold" style="font-size: 10px;">>361 Days (FSN: N)</small>
                    <h4 class="mb-0 mt-1 text-danger"><?php echo formatCurrency($bucketTotals['over361']); ?></h4>
                    <small class="text-danger fw-bold">⚠️ High Risk Zone</small>
                </div>
            </div>
        </div>
    </div>

    <div class="row g-4 mb-4">
        <div class="col-lg-8">
            <div class="card h-100">
                <div class="card-header">
                    <h6 class="mb-0 text-uppercase" style="font-size: 12px;">Aging Distribution % (Actual vs Target)</h6>
                </div>
                <div class="card-body">
                    <canvas id="agingChart" height="280"></canvas>
                </div>
            </div>
        </div>
        <div class="col-lg-4">
            <div class="card h-100">
                <div class="card-header">
                    <h6 class="mb-0 text-uppercase" style="font-size: 12px;">Top 5 Aged Categories</h6>
                </div>
                <div class="card-body" id="topAgedList">
                    <?php
                    $topAged = array_filter($groupData, function($g) { return $g['over361'] > 0; });
                    usort($topAged, function($a, $b) { return $b['over361'] - $a['over361']; });
                    $top5 = array_slice($topAged, 0, 5);
                    
                    foreach ($top5 as $g):
                        $pct = $g['total'] > 0 ? ($g['over361'] / $g['total'] * 100) : 0;
                    ?>
                    <div class="mb-3">
                        <div class="d-flex justify-content-between mb-1">
                            <span class="small text-truncate" style="max-width: 150px;"><?php echo htmlspecialchars($g['name']); ?></span>
                            <span class="small fw-bold"><?php echo formatCurrency($g['over361']); ?></span>
                        </div>
                        <div class="progress" style="height: 6px;">
                            <div class="progress-bar bg-danger" style="width: <?php echo $pct; ?>%"></div>
                        </div>
                    </div>
                    <?php endforeach; ?>
                </div>
            </div>
        </div>
    </div>

    <div class="card">
        <div class="card-header d-flex justify-content-between align-items-center">
            <div>
                <h6 class="mb-0">Detailed Item Group Aging Report</h6>
                <small class="text-muted">Breakdown by valuation and aging bucket</small>
            </div>
            <div class="position-relative" style="width: 250px;">
                <input type="text" id="tableSearch" class="form-control form-control-sm" placeholder="Search Item Group...">
                <i class="bi bi-search position-absolute" style="left: 10px; top: 50%; transform: translateY(-50%); color: #6c757d; font-size: 14px;"></i>
            </div>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-sm table-hover mb-0" id="agingTable">
                    <thead class="table-light">
                        <tr>
                            <th class="px-3 py-3" style="font-size: 10px; text-transform: uppercase;">Item Group</th>
                            <th class="px-3 py-3" style="font-size: 10px; text-transform: uppercase;">1-90 Days</th>
                            <th class="px-3 py-3" style="font-size: 10px; text-transform: uppercase;">91-180 Days</th>
                            <th class="px-3 py-3" style="font-size: 10px; text-transform: uppercase;">181-270 Days</th>
                            <th class="px-3 py-3" style="font-size: 10px; text-transform: uppercase;">271-360 Days</th>
                            <th class="px-3 py-3" style="font-size: 10px; text-transform: uppercase;">>361 Days</th>
                            <th class="px-3 py-3 text-end" style="font-size: 10px; text-transform: uppercase;">Grand Total</th>
                        </tr>
                    </thead>
                    <tbody id="detailTableBody">
                        <?php foreach ($groupData as $g): ?>
                        <tr>
                            <td class="px-3 fw-semibold"><?php echo htmlspecialchars($g['name']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($g['1-90']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($g['91-180']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($g['181-270']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($g['271-360']); ?></td>
                            <td class="px-3 <?php echo $g['over361'] > 50000 ? 'text-danger fw-medium' : ''; ?>"><?php echo formatCurrency($g['over361']); ?></td>
                            <td class="px-3 text-end fw-bold"><?php echo formatCurrency($g['total']); ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                    <tfoot class="table-dark">
                        <tr>
                            <td class="px-3">TOTAL</td>
                            <td class="px-3"><?php echo formatCurrency($bucketTotals['1-90']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($bucketTotals['91-180']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($bucketTotals['181-270']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($bucketTotals['271-360']); ?></td>
                            <td class="px-3"><?php echo formatCurrency($bucketTotals['over361']); ?></td>
                            <td class="px-3 text-end"><?php echo formatCurrency($totalValue); ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const bucketLabels = ['1-90 Days', '91-180 Days', '181-270 Days', '271-360 Days', '>361 Days'];
const actualData = [<?php echo implode(', ', [$bucketPercentages['1-90'], $bucketPercentages['91-180'], $bucketPercentages['181-270'], $bucketPercentages['271-360'], $bucketPercentages['over361']]); ?>];
const targetData = [60, 20, 10, 5, 5];

const ctx = document.getElementById('agingChart').getContext('2d');
new Chart(ctx, {
    type: 'bar',
    data: {
        labels: bucketLabels,
        datasets: [
            {
                label: 'Actual %',
                data: actualData,
                backgroundColor: '#3b82f6',
                borderRadius: 4
            },
            {
                label: 'Target %',
                data: targetData,
                backgroundColor: '#e2e8f0',
                borderRadius: 4
            }
        ]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: {
                position: 'bottom',
                labels: { boxWidth: 12, usePointStyle: true }
            }
        },
        scales: {
            y: { beginAtZero: true, max: 70 },
            x: { grid: { display: false } }
        }
    }
});

document.getElementById('tableSearch').addEventListener('input', function(e) {
    const searchTerm = e.target.value.toLowerCase();
    const rows = document.querySelectorAll('#detailTableBody tr');
    rows.forEach(row => {
        const text = row.textContent.toLowerCase();
        row.style.display = text.includes(searchTerm) ? '' : 'none';
    });
});
</script>

<?php require_once __DIR__ . '/../includes/admin-footer.php'; ?>