<?php
/**
 * Supplier Payment (AP) Report System
 * 
 */
 
 


// Database configuration
$host = 'localhost';
$dbname = 'whitetil_mount';
$username = 'whitetil_mount';
$password = 'P27L4!Z)LtnrWNE5';

// Enable error reporting for development (disable in production)
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Create database connection
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Get selected supplier from combo box
$selectedSupplier = isset($_GET['supplier']) ? $_GET['supplier'] : '';
$reportType = isset($_GET['report_type']) ? $_GET['report_type'] : 'dashboard';
$dateFrom = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-01');
$dateTo = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');
$matureDate = isset($_GET['mature_date']) ? $_GET['mature_date'] : date('Y-m-d');

// Fetch suppliers for combo box
$suppliers = [];
$stmt = $pdo->query("SELECT code as VEN_CODE, name as name FROM vendor WHERE cat='S' OR cat='B' ORDER BY name");
$suppliers = $stmt->fetchAll();

// Function to execute queries safely
function executeQuery($pdo, $sql, $params = []) {
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        return [];
    }
}

// Get report data based on type
$reportData = [];
$chartData = [];

switch ($reportType) {
    case 'total_ap':
        $sql = "SELECT REFNO, SDATE, CUSCODE, AMOUNT, BALANCE, DEP, SAL_EX, payment_date 
                FROM c_bal WHERE CANCELL='0' ORDER BY SDATE";
        $reportData = executeQuery($pdo, $sql);
        break;
        
    case 'customer_order':
        $sql = "SELECT REFNO, SDATE, CUSCODE, AMOUNT, BALANCE, DEP, SAL_EX, payment_date 
                FROM c_bal WHERE CANCELL='0' AND BALANCE > 0 
                GROUP BY CUSCODE ORDER BY SDATE";
        $reportData = executeQuery($pdo, $sql);
        break;
        
    case 'date_exceed':
        $sql = "SELECT REFNO, SDATE, CUSCODE, AMOUNT, BALANCE, DEP, SAL_EX, payment_date 
                FROM c_bal WHERE payment_date > CURDATE() AND CANCELL='0' AND BALANCE > 0 
                GROUP BY CUSCODE ORDER BY SDATE";
        $reportData = executeQuery($pdo, $sql);
        break;
        
    case 'mature_report':
        $sql = "SELECT REFNO, SDATE, CUSCODE, AMOUNT, BALANCE, DEP, SAL_EX, payment_date 
                FROM c_bal WHERE payment_date > :mature_date AND CANCELL='0' AND BALANCE > 0 
                GROUP BY CUSCODE ORDER BY SDATE";
        $reportData = executeQuery($pdo, $sql, [':mature_date' => $matureDate]);
        break;
        
    case 'paid_report':
        $sql = "SELECT REFNO, BDATE, entdate, bank_name, CHENO, chqdt, Barer, VOUNO, VOUDATE, PAY_TYPE 
                FROM a_paymas WHERE entdate BETWEEN :date_from AND :date_to";
        $reportData = executeQuery($pdo, $sql, [':date_from' => $dateFrom, ':date_to' => $dateTo]);
        break;
        
    case 'supplier_wise':
        if ($selectedSupplier) {
            $sql2 = "SELECT REFNO as c_bal_no, SDATE as c_bal_date, CUSCODE, AMOUNT as c_bal_amount, 
                     totpay, BALANCE, payment_date 
                     FROM c_bal WHERE BALANCE > 0 AND CUSCODE = :supplier";
            $reportData = executeQuery($pdo, $sql2, [':supplier' => $selectedSupplier]);
        }
        break;
        
    default: // dashboard
        $sql = "SELECT REFNO, SDATE, CUSCODE, AMOUNT, BALANCE, DEP, SAL_EX, payment_date 
                FROM c_bal WHERE CANCELL='0' ORDER BY SDATE";
        $reportData = executeQuery($pdo, $sql);
        
        // Age analysis data
        $ageData = [];
        $now = new DateTime();
        foreach ($reportData as $row) {
            if ($row['BALANCE'] > 0) {
                $paymentDate = new DateTime($row['payment_date']);
                $diff = $now->diff($paymentDate)->days;
                
                if ($diff <= 30) $ageData['0-30'] = ($ageData['0-30'] ?? 0) + $row['BALANCE'];
                elseif ($diff <= 60) $ageData['31-60'] = ($ageData['31-60'] ?? 0) + $row['BALANCE'];
                elseif ($diff <= 90) $ageData['61-90'] = ($ageData['61-90'] ?? 0) + $row['BALANCE'];
                else $ageData['90+'] = ($ageData['90+'] ?? 0) + $row['BALANCE'];
            }
        }
        $chartData['age_analysis'] = $ageData;
        
        // Supplier wise outstanding for pie chart (>5%)
        $sqlSup = "SELECT CUSCODE, SUM(BALANCE) as total_balance 
                   FROM c_bal WHERE BALANCE > 0 GROUP BY CUSCODE";
        $supOutstanding = executeQuery($pdo, $sqlSup);
        $totalBalance = array_sum(array_column($supOutstanding, 'total_balance'));
        $supplierPieData = [];
        foreach ($supOutstanding as $sup) {
            $percentage = ($sup['total_balance'] / $totalBalance) * 100;
            if ($percentage > 5) {
                $supplierPieData[] = ['name' => $sup['CUSCODE'], 'value' => $sup['total_balance']];
            }
        }
        $chartData['supplier_pie'] = $supplierPieData;
        break;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes">
    <title>Supplier Payment (AP) Report System</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: linear-gradient(135deg, #e8f5e9 0%, #c8e6c9 100%);
            color: #1b5e20;
            font-size: 12px;
            min-height: 100vh;
        }
        
        /* Full Screen Layout */
        .container {
            width: 100%;
            min-height: 100vh;
            padding: 15px;
        }
        
        /* Header */
        .header {
            background: #2e7d32;
            color: white;
            padding: 15px 20px;
            border-radius: 12px;
            margin-bottom: 20px;
            box-shadow: 0 4px 15px rgba(0,0,0,0.1);
        }
        
        .header h1 {
            font-size: 1.8rem;
            margin-bottom: 10px;
        }
        
        /* Control Panel */
        .control-panel {
            background: #f1f8e9;
            padding: 20px;
            border-radius: 12px;
            margin-bottom: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            border: 1px solid #a5d6a7;
        }
        
        .filter-group {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            align-items: flex-end;
        }
        
        .filter-item {
            display: flex;
            flex-direction: column;
            gap: 5px;
        }
        
        .filter-item label {
            font-weight: 600;
            color: #1b5e20;
            font-size: 0.85rem;
        }
        
        select, input, button {
            padding: 8px 15px;
            border-radius: 8px;
            border: 1px solid #81c784;
            background: white;
            font-size: 0.85rem;
            color: #1b5e20;
        }
        
        select:focus, input:focus {
            outline: none;
            border-color: #2e7d32;
            box-shadow: 0 0 0 2px rgba(46,125,50,0.2);
        }
        
        button {
            background: #2e7d32;
            color: white;
            border: none;
            cursor: pointer;
            transition: all 0.3s;
            font-weight: 600;
        }
        
        button:hover {
            background: #1b5e20;
            transform: translateY(-1px);
        }
        
        .print-btn {
            background: #558b2f;
        }
        
        .print-btn:hover {
            background: #33691e;
        }
        
        /* Report Tabs */
        .tabs {
            display: flex;
            flex-wrap: wrap;
            gap: 5px;
            margin-bottom: 20px;
            border-bottom: 2px solid #a5d6a7;
            padding-bottom: 10px;
        }
        
        .tab-btn {
            background: #e8f5e9;
            color: #2e7d32;
            padding: 10px 20px;
            border-radius: 8px 8px 0 0;
            font-size: 0.85rem;
        }
        
        .tab-btn.active {
            background: #2e7d32;
            color: white;
        }
        
        /* Dashboard Charts */
        .dashboard-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(400px, 1fr));
            gap: 20px;
            margin-bottom: 30px;
        }
        
        .chart-card {
            background: white;
            border-radius: 12px;
            padding: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            border: 1px solid #c8e6c9;
        }
        
        .chart-card h3 {
            color: #1b5e20;
            margin-bottom: 15px;
            font-size: 1rem;
            text-align: center;
        }
        
        canvas {
            max-height: 300px;
            width: 100%;
        }
        
        /* Table Styles */
        .table-container {
            overflow-x: auto;
            background: white;
            border-radius: 12px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            border: 1px solid #c8e6c9;
        }
        
        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 0.8rem;
        }
        
        th {
            background: #2e7d32;
            color: white;
            padding: 12px 10px;
            text-align: left;
            font-weight: 600;
            position: sticky;
            top: 0;
        }
        
        td {
            padding: 10px;
            border-bottom: 1px solid #e0f2e0;
            color: #1b5e20;
        }
        
        tr:hover {
            background: #f1f8e9;
        }
        
        .summary {
            background: #e8f5e9;
            padding: 15px;
            border-radius: 8px;
            margin-top: 15px;
            font-weight: 600;
            text-align: right;
        }
        
        /* Loading Overlay */
        .loading {
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background: rgba(0,0,0,0.5);
            display: none;
            justify-content: center;
            align-items: center;
            z-index: 1000;
        }
        
        .loading.active {
            display: flex;
        }
        
        .spinner {
            width: 50px;
            height: 50px;
            border: 5px solid #fff;
            border-top-color: #2e7d32;
            border-radius: 50%;
            animation: spin 1s linear infinite;
        }
        
        @keyframes spin {
            to { transform: rotate(360deg); }
        }
        
        /* Print Styles */
        @media print {
            .control-panel, .tabs, .print-btn, button, .no-print {
                display: none !important;
            }
            body {
                background: white;
                padding: 0;
                margin: 0;
                font-size: 10px;
            }
            .container {
                padding: 0;
            }
            .table-container {
                overflow: visible;
            }
            table {
                font-size: 8px;
            }
            th {
                background: #ddd;
                color: black;
            }
        }
        
        /* Responsive */
        @media (max-width: 768px) {
            .filter-group {
                flex-direction: column;
                align-items: stretch;
            }
            .dashboard-grid {
                grid-template-columns: 1fr;
            }
            th, td {
                padding: 6px;
                font-size: 0.7rem;
            }
        }
    </style>
</head>
<body>
<div class="loading" id="loading">
    <div class="spinner"></div>
</div>

<div class="container">
    <div class="header">
        <h1>📊 Supplier Payment (AP) Report System</h1>
        <p>Accounts Payable Management | Report View</p>
    </div>
    
    <div class="control-panel no-print">
        <form method="GET" id="reportForm" style="width: 100%;">
            <div class="filter-group">
                <div class="filter-item">
                    <label>📌 Supplier</label>
                    <select name="supplier" id="supplier">
                        <option value="">-- All Suppliers --</option>
                        <?php foreach ($suppliers as $sup): ?>
                        <option value="<?php echo htmlspecialchars($sup['VEN_CODE']); ?>" 
                            <?php echo $selectedSupplier == $sup['VEN_CODE'] ? 'selected' : ''; ?>>
                            <?php echo htmlspecialchars($sup['VEN_CODE'] . ' - ' . $sup['name']); ?>
                        </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="filter-item">
                    <label>📅 Date From</label>
                    <input type="date" name="date_from" value="<?php echo $dateFrom; ?>">
                </div>
                
                <div class="filter-item">
                    <label>📅 Date To</label>
                    <input type="date" name="date_to" value="<?php echo $dateTo; ?>">
                </div>
                
                <div class="filter-item">
                    <label>📅 Mature Date</label>
                    <input type="date" name="mature_date" value="<?php echo $matureDate; ?>">
                </div>
                
                <div class="filter-item">
                    <label>&nbsp;</label>
                    <button type="submit" name="report_type" value="<?php echo $reportType; ?>">🔄 Refresh</button>
                </div>
                
                <div class="filter-item">
                    <label>&nbsp;</label>
                    <button type="button" class="print-btn" onclick="window.print()">🖨️ Print Report</button>
                </div>
            </div>
            
            <div class="tabs" style="margin-top: 20px;">
                <button type="submit" name="report_type" value="dashboard" class="tab-btn <?php echo $reportType == 'dashboard' ? 'active' : ''; ?>">📈 Dashboard</button>
                <button type="submit" name="report_type" value="total_ap" class="tab-btn <?php echo $reportType == 'total_ap' ? 'active' : ''; ?>">💰 Total AP Report</button>
                <button type="submit" name="report_type" value="customer_order" class="tab-btn <?php echo $reportType == 'customer_order' ? 'active' : ''; ?>">📋 Customer Order</button>
                <button type="submit" name="report_type" value="date_exceed" class="tab-btn <?php echo $reportType == 'date_exceed' ? 'active' : ''; ?>">⚠️ Date Exceed</button>
                <button type="submit" name="report_type" value="mature_report" class="tab-btn <?php echo $reportType == 'mature_report' ? 'active' : ''; ?>">📅 Mature Report</button>
                <button type="submit" name="report_type" value="paid_report" class="tab-btn <?php echo $reportType == 'paid_report' ? 'active' : ''; ?>">✅ Paid Report</button>
                <button type="submit" name="report_type" value="supplier_wise" class="tab-btn <?php echo $reportType == 'supplier_wise' ? 'active' : ''; ?>">🏢 Supplier Wise</button>
            </div>
        </form>
    </div>
    
    <!-- Dashboard Section -->
    <?php if ($reportType == 'dashboard'): ?>
    <div class="dashboard-grid">
        <div class="chart-card">
            <h3>📊 Age Analysis (Aging Buckets)</h3>
            <canvas id="ageChart" width="400" height="300"></canvas>
        </div>
        <div class="chart-card">
            <h3>🥧 Supplier Outstanding (>5%)</h3>
            <canvas id="supplierPieChart" width="400" height="300"></canvas>
        </div>
    </div>
    <?php endif; ?>
    
    <!-- Report Table Section -->
    <div class="table-container">
        <?php if (!empty($reportData)): ?>
        <table>
            <thead>
                <tr>
                    <?php if (!empty($reportData[0])): ?>
                        <?php foreach (array_keys($reportData[0]) as $column): ?>
                            <th><?php echo strtoupper(str_replace('_', ' ', $column)); ?></th>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <th>No Data Available</th>
                    <?php endif; ?>
                </tr>
            </thead>
            <tbody>
                <?php 
                $totalBalance = 0;
                foreach ($reportData as $row): 
                    if (isset($row['BALANCE'])) $totalBalance += $row['BALANCE'];
                ?>
                <tr>
                    <?php foreach ($row as $value): ?>
                        <td><?php echo htmlspecialchars($value ?? '-'); ?></td>
                    <?php endforeach; ?>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        <div class="summary">
            Total Records: <?php echo count($reportData); ?> | 
            Total Balance: <?php echo isset($totalBalance) ? number_format($totalBalance, 2) : '0.00'; ?>
        </div>
        <?php else: ?>
        <div style="padding: 40px; text-align: center; color: #2e7d32;">
            <p>📭 No records found for the selected criteria.</p>
        </div>
        <?php endif; ?>
    </div>
</div>

<script>
    // Show loading on form submit
    document.querySelectorAll('button[type="submit"], .tab-btn').forEach(btn => {
        btn.addEventListener('click', function() {
            document.getElementById('loading').classList.add('active');
        });
    });
    
    // Initialize charts
    <?php if ($reportType == 'dashboard' && !empty($chartData['age_analysis'])): ?>
    const ageCtx = document.getElementById('ageChart')?.getContext('2d');
    if (ageCtx) {
        new Chart(ageCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode(array_keys($chartData['age_analysis'])); ?>,
                datasets: [{
                    label: 'Outstanding Amount',
                    data: <?php echo json_encode(array_values($chartData['age_analysis'])); ?>,
                    backgroundColor: ['#66bb6a', '#4caf50', '#2e7d32', '#1b5e20'],
                    borderColor: '#1b5e20',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: { position: 'top' },
                    tooltip: { callbacks: { label: (ctx) => '₹ ' + ctx.raw.toLocaleString() } }
                }
            }
        });
    }
    <?php endif; ?>
    
    <?php if ($reportType == 'dashboard' && !empty($chartData['supplier_pie'])): ?>
    const pieCtx = document.getElementById('supplierPieChart')?.getContext('2d');
    if (pieCtx) {
        new Chart(pieCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode(array_column($chartData['supplier_pie'], 'name')); ?>,
                datasets: [{
                    data: <?php echo json_encode(array_column($chartData['supplier_pie'], 'value')); ?>,
                    backgroundColor: ['#66bb6a', '#81c784', '#a5d6a7', '#c8e6c9', '#e8f5e9', '#2e7d32', '#1b5e20', '#388e3c']
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    tooltip: { callbacks: { label: (ctx) => ctx.label + ': ₹ ' + ctx.raw.toLocaleString() } }
                }
            }
        });
    }
    <?php endif; ?>
    
</script>
</body>
</html>