<?php
/**
 * Advanced Financial Statements & Revenue Reports (Pro Version - Light Premium UI)
 */
require_once 'header.php';

// কারেন্সি এবং সময় সেটআপ
$currency = defined('CURRENCY_SYMBOL') ? CURRENCY_SYMBOL : '৳';
$today = date('Y-m-d');
$this_month = date('Y-m');

try {
    // ১. আজকের আয় (Today's Earnings)
    $stmt_today = $pdo->prepare("SELECT SUM(amount) FROM orders WHERE status = 'approved' AND DATE(created_at) = ?");
    $stmt_today->execute([$today]);
    $earnings_today = $stmt_today->fetchColumn() ?: 0;

    // ২. এই মাসের আয় (Monthly Earnings)
    $stmt_month = $pdo->prepare("SELECT SUM(amount) FROM orders WHERE status = 'approved' AND created_at LIKE ?");
    $stmt_month->execute([$this_month . '%']);
    $earnings_month = $stmt_month->fetchColumn() ?: 0;

    // ৩. সর্বমোট আয় (Total Earnings)
    $earnings_total = $pdo->query("SELECT SUM(amount) FROM orders WHERE status = 'approved'")->fetchColumn() ?: 0;

    // ৪. টোটাল রিফান্ড বা ফেইল্ড ট্রানজেকশন (Failed Amount)
    $failed_total = $pdo->query("SELECT SUM(amount) FROM orders WHERE status = 'failed'")->fetchColumn() ?: 0;

    // ৫. বিস্তারিত রিপোর্ট লিস্ট (Recent Success Orders)
    $report_query = "
        SELECT o.*, u.name as user_name, p.title as product_name 
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        LEFT JOIN products p ON o.product_id = p.id
        WHERE o.status = 'approved'
        ORDER BY o.id DESC LIMIT 20
    ";
    $reports = $pdo->query($report_query)->fetchAll();

} catch (PDOException $e) {
    $error = "Reporting Error: " . $e->getMessage();
}
?>

<style>
    :root {
        --primary: #84cc16;
        --primary-hover: #65a30d;
        --bg-light: #f8fafc;
        --card-bg: #ffffff;
        --border: #e2e8f0;
        --text-main: #1e293b;
        --text-muted: #64748b;
    }

    body {
        background-color: var(--bg-light) !important;
        color: var(--text-main) !important;
        font-family: 'Inter', sans-serif;
        margin: 0;
    }

    .dashboard-wrapper {
        max-width: 1100px;
        margin: 0 auto;
        padding: 20px;
    }

    .page-title {
        font-size: 1.5rem;
        font-weight: 800;
        margin-bottom: 25px;
        color: var(--text-main);
    }

    /* Premium Stats Grid for Mobile */
    .statement-grid { 
        display: grid; 
        grid-template-columns: repeat(auto-fit, minmax(240px, 1fr)); 
        gap: 20px; 
        margin-bottom: 30px; 
    }
    
    .stat-box { 
        background: var(--card-bg); 
        padding: 24px; 
        border-radius: 12px; 
        border: 1px solid var(--border); 
        position: relative; 
        overflow: hidden; 
        box-shadow: 0 1px 3px rgba(0,0,0,0.02);
    }
    
    .stat-label { 
        font-size: 0.85rem; 
        font-weight: 700; 
        color: var(--text-muted); 
        text-transform: uppercase; 
        margin-bottom: 8px; 
        display: block;
        letter-spacing: 0.5px;
    }
    
    .stat-price { 
        font-size: 2rem; 
        font-weight: 800; 
        color: var(--text-main); 
        display: block; 
        margin-bottom: 12px;
    }
    
    .stat-diff { 
        font-size: 0.85rem; 
        font-weight: 600; 
        display: flex; 
        align-items: center; 
        gap: 6px; 
    }
    
    .diff-up { color: #10b981; }

    /* Report Table */
    .report-card { 
        background: var(--card-bg); 
        border-radius: 12px; 
        border: 1px solid var(--border); 
        padding: 24px; 
        box-shadow: 0 1px 3px rgba(0,0,0,0.02); 
    }
    
    .report-table-title {
        font-size: 1.2rem;
        font-weight: 700;
        color: var(--text-main);
        margin: 0 0 20px 0;
    }

    .table-responsive { 
        width: 100%; 
        overflow-x: auto; 
        -webkit-overflow-scrolling: touch; 
    }
    
    .report-table { 
        width: 100%; 
        border-collapse: collapse; 
        text-align: left; 
        min-width: 600px; 
    }
    
    .report-table th { 
        padding: 14px 15px; 
        font-size: 0.8rem; 
        color: var(--text-muted); 
        border-bottom: 1px solid var(--border); 
        text-transform: uppercase; 
        font-weight: 700;
        background: #fafafa;
    }
    
    .report-table td { 
        padding: 16px 15px; 
        border-bottom: 1px solid var(--border); 
        font-size: 0.9rem; 
        font-weight: 500; 
        color: var(--text-main); 
        vertical-align: middle;
    }
    
    .report-table tr:last-child td { border-bottom: none; }
    .report-table tr:hover { background: #fdfdfd; }
    
    .text-success { color: #10b981; font-weight: 800; }
    .text-muted-sm { color: var(--text-muted); font-size: 0.8rem; margin-top: 4px; }
    .prod-title { font-weight: 700; color: var(--text-main); margin-bottom: 4px; }
    .invoice-text { font-family: monospace; font-weight: 600; color: var(--primary); }

    @media (max-width: 768px) {
        .statement-grid { grid-template-columns: 1fr 1fr; gap: 15px; }
        .stat-box { padding: 20px; }
        .stat-price { font-size: 1.5rem; }
        .dashboard-wrapper { padding: 15px 10px; }
        .report-card { padding: 20px; }
    }
    @media (max-width: 480px) {
        .statement-grid { grid-template-columns: 1fr; }
    }
</style>

<div class="dashboard-wrapper">
    <div class="page-header">
        <h1 class="page-title">Financial Statements</h1>
    </div>

    <div class="statement-grid">
        <div class="stat-box">
            <span class="stat-label">Today's Revenue</span>
            <span class="stat-price"><?php echo $currency . number_format($earnings_today, 2); ?></span>
            <div class="stat-diff diff-up">Live tracking active</div>
        </div>
        
        <div class="stat-box">
            <span class="stat-label">This Month</span>
            <span class="stat-price"><?php echo $currency . number_format($earnings_month, 2); ?></span>
            <div class="stat-diff" style="color:#3b82f6;">For <?php echo date('F Y'); ?></div>
        </div>

        <div class="stat-box">
            <span class="stat-label">Total Earnings</span>
            <span class="stat-price"><?php echo $currency . number_format($earnings_total, 2); ?></span>
            <div class="stat-diff" style="color:var(--primary);">Lifetime profit</div>
        </div>

        <div class="stat-box">
            <span class="stat-label">Failed/Refunded</span>
            <span class="stat-price"><?php echo $currency . number_format($failed_total, 2); ?></span>
            <div class="stat-diff" style="color:#ef4444;">Lost opportunities</div>
        </div>
    </div>

    <div class="report-card">
        <h3 class="report-table-title">Recent Success Transactions</h3>
        
        <div class="table-responsive">
            <table class="report-table">
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Invoice</th>
                        <th>Product & Customer</th>
                        <th style="text-align: right;">Net Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach($reports as $r): ?>
                    <tr>
                        <td>
                            <div style="font-weight: 600;"><?php echo date('d M, Y', strtotime($r['created_at'])); ?></div>
                            <div class="text-muted-sm"><?php echo date('h:i A', strtotime($r['created_at'])); ?></div>
                        </td>
                        <td class="invoice-text"><?php echo htmlspecialchars($r['invoice_no']); ?></td>
                        <td>
                            <div class="prod-title"><?php echo htmlspecialchars($r['product_name'] ?? 'Digital Item'); ?></div>
                            <div class="text-muted-sm">Purchased by: <?php echo htmlspecialchars($r['user_name'] ?? 'Guest'); ?></div>
                        </td>
                        <td style="text-align: right;" class="text-success">
                            + <?php echo $currency . number_format($r['amount'], 2); ?>
                        </td>
                    </tr>
                    <?php endforeach; ?>

                    <?php if(empty($reports)): ?>
                        <tr><td colspan="4" style="text-align: center; padding: 40px; color: var(--text-muted); font-weight: 500;">No successful transactions yet.</td></tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<?php echo '</div></div></body></html>'; ?>
