<?php
session_start();
require_once '../config.php';

// Check login & role
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'sales_executive') {
    header('Location: ../login.php');
    exit;
}

$user_id = (int)$_SESSION['user_id'];

function money($amount) {
    return "₹" . number_format((float)($amount ?? 0), 2);
}

// Total Bills
$total_bills = 0;
$stmt = $conn->prepare("SELECT COUNT(*) FROM bills WHERE created_by = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$stmt->bind_result($total_bills);
$stmt->fetch();
$stmt->close();

// Today Sales
$today_sales = 0;
$stmt = $conn->prepare("
    SELECT COALESCE(SUM(total_amount), 0)
    FROM bills
    WHERE created_by = ? AND DATE(created_at) = CURDATE()
");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$stmt->bind_result($today_sales);
$stmt->fetch();
$stmt->close();

// Paid / Due / Advance
$total_paid = 0;
$total_due = 0;
$total_advance = 0;

$stmt = $conn->prepare("
    SELECT
        COALESCE(SUM(
            CASE
                WHEN payment_status = 'paid' THEN total_amount
                WHEN payment_status = 'advance' THEN advance_amount
                ELSE 0
            END
        ), 0) AS paid,
        COALESCE(SUM(due_amount), 0) AS due,
        COALESCE(SUM(CASE WHEN payment_status = 'advance' THEN advance_amount ELSE 0 END), 0) AS advance
    FROM bills
    WHERE created_by = ?
");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();

$total_paid    = $result['paid'] ?? 0;
$total_due     = $result['due'] ?? 0;
$total_advance = $result['advance'] ?? 0;
$stmt->close();

// Overdue Bills
$overdue_count = 0;
$stmt = $conn->prepare("
    SELECT COUNT(*)
    FROM bills
    WHERE created_by = ?
      AND unpaid = 1
      AND unpaid_due_date < CURDATE()
");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$stmt->bind_result($overdue_count);
$stmt->fetch();
$stmt->close();

// Recent Bills
$stmt = $conn->prepare("
    SELECT
        id,
        customer_name,
        total_amount,
        advance_amount,
        due_amount,
        payment_status,
        due_date,
        created_at
    FROM bills
    WHERE created_by = ?
    ORDER BY id DESC
    LIMIT 10
");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$recent_bills = $stmt->get_result();

// Top Products
$stmt = $conn->prepare("
    SELECT
        bi.product_name,
        COALESCE(SUM(bi.qty), 0) AS qty
    FROM bill_items bi
    INNER JOIN bills b ON bi.bill_id = b.id
    WHERE b.created_by = ?
    GROUP BY bi.product_name
    ORDER BY qty DESC
    LIMIT 5
");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$top_products = $stmt->get_result();
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Sales Executive Dashboard</title>
    <style>
        * { box-sizing: border-box; }

        body {
            font-family: Arial, sans-serif;
            background: #f5f6fa;
            margin: 0;
            color: #222;
        }

        /* Main content area after sidebar + header */
        .page-content {
            margin-left: 250px;
            padding: 90px 20px 20px 20px;
            min-height: 100vh;
        }

        .container {
            width: 100%;
        }

        .header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            gap: 12px;
            flex-wrap: wrap;
            margin-bottom: 18px;
        }

        h1 {
            margin: 0;
            font-size: 28px;
        }

        .subtitle {
            margin-top: 6px;
            color: #666;
            font-size: 14px;
        }

        .cards {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(210px, 1fr));
            gap: 15px;
            margin-bottom: 20px;
        }

        .card {
            background: #fff;
            padding: 18px;
            border-radius: 14px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.08);
            border: 1px solid #eee;
            min-height: 110px;
            display: flex;
            flex-direction: column;
            justify-content: center;
        }

        .card h3 {
            margin: 0;
            font-size: 15px;
            color: #777;
            font-weight: 600;
        }

        .card h2 {
            margin: 10px 0 0;
            font-size: 26px;
            line-height: 1.2;
        }

        .green { color: #198754; }
        .red { color: #dc3545; }
        .blue { color: #0d6efd; }
        .orange { color: #fd7e14; }

        .actions {
            display: flex;
            gap: 10px;
            flex-wrap: wrap;
            margin: 10px 0 24px;
        }

        .btn {
            display: inline-block;
            padding: 10px 14px;
            background: #0d6efd;
            color: #fff;
            text-decoration: none;
            border-radius: 10px;
            font-size: 14px;
            font-weight: 600;
        }

        .btn.secondary { background: #6c757d; }
        .btn.success { background: #198754; }
        .btn.warning { background: #fd7e14; }

        .section {
            margin-top: 22px;
            background: #fff;
            border-radius: 14px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.08);
            border: 1px solid #eee;
            overflow: hidden;
        }

        .section h2 {
            margin: 0;
            padding: 16px 18px;
            border-bottom: 1px solid #eee;
            font-size: 18px;
        }

        .table-wrap {
            width: 100%;
            overflow-x: auto;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            min-width: 750px;
        }

        th, td {
            padding: 12px 14px;
            border-bottom: 1px solid #f0f0f0;
            text-align: left;
            font-size: 14px;
            white-space: nowrap;
        }

        th {
            background: #fafafa;
            color: #555;
            font-weight: 700;
        }

        tr:hover td {
            background: #fcfcfc;
        }

        .badge {
            display: inline-block;
            padding: 5px 10px;
            border-radius: 999px;
            font-size: 12px;
            font-weight: 700;
        }

        .badge-paid { background: #e8f7ee; color: #198754; }
        .badge-advance { background: #fff4e5; color: #fd7e14; }
        .badge-unpaid { background: #fdecec; color: #dc3545; }

        .empty {
            padding: 18px;
            color: #777;
        }

        @media (max-width: 992px) {
            .page-content {
                margin-left: 0;
                padding-top: 80px;
            }
        }

        @media (max-width: 768px) {
            h1 {
                font-size: 22px;
            }

            .card h2 {
                font-size: 22px;
            }

            .page-content {
                padding: 75px 12px 12px 12px;
            }
        }
    </style>
</head>
<body>

<?php include "salesexecutivesidebar.php"; ?>
<?php include "../header.php"; ?>

<div class="page-content">
    <div class="container">

        <div class="header">
            <div>
                <h1>Sales Executive Dashboard</h1>
                <div class="subtitle">Live summary of your bills, payments, dues, and products</div>
            </div>
        </div>

        <div class="cards">
            <div class="card">
                <h3>Total Bills</h3>
                <h2><?php echo (int)$total_bills; ?></h2>
            </div>

            <div class="card">
                <h3>Today Sales</h3>
                <h2 class="blue"><?php echo money($today_sales); ?></h2>
            </div>

            <div class="card">
                <h3>Total Paid</h3>
                <h2 class="green"><?php echo money($total_paid); ?></h2>
            </div>

            <div class="card">
                <h3>Total Due</h3>
                <h2 class="red"><?php echo money($total_due); ?></h2>
            </div>

            <div class="card">
                <h3>Advance</h3>
                <h2 class="orange"><?php echo money($total_advance); ?></h2>
            </div>

            <div class="card">
                <h3>Overdue Alerts</h3>
                <h2 class="red"><?php echo (int)$overdue_count; ?></h2>
            </div>
        </div>

        <div class="actions">
            <a href="create_bill.php" class="btn">➕ Create Bill</a>
            <a href="due_alerts.php" class="btn warning">⚠ View Due Alerts</a>
            <a href="sold_products.php" class="btn success">📦 Sold Products</a>
            <a href="mr_dashboard.php" class="btn secondary">🔄 Refresh</a>
        </div>

        <div class="section">
            <h2>Recent Bills</h2>
            <div class="table-wrap">
                <table>
                    <tr>
                        <th>ID</th>
                        <th>Customer</th>
                        <th>Total</th>
                        <th>Paid</th>
                        <th>Due</th>
                        <th>Status</th>
                        <th>Due Date</th>
                    </tr>

                    <?php if ($recent_bills && $recent_bills->num_rows > 0): ?>
                        <?php while ($row = $recent_bills->fetch_assoc()): 
                            $total   = (float)($row['total_amount'] ?? 0);
                            $advance = (float)($row['advance_amount'] ?? 0);
                            $due     = (float)($row['due_amount'] ?? 0);
                            $status  = $row['payment_status'] ?? 'unpaid';

                            if ($status === 'paid') {
                                $paid_display = $total;
                            } elseif ($status === 'advance') {
                                $paid_display = $advance;
                            } else {
                                $paid_display = 0;
                            }

                            if ($due <= 0 && $total > $paid_display) {
                                $due = $total - $paid_display;
                            }
                        ?>
                            <tr>
                                <td><?php echo (int)$row['id']; ?></td>
                                <td><?php echo htmlspecialchars($row['customer_name'] ?? '', ENT_QUOTES, 'UTF-8'); ?></td>
                                <td><?php echo money($total); ?></td>
                                <td class="green"><?php echo money($paid_display); ?></td>
                                <td class="red"><?php echo money($due); ?></td>
                                <td>
                                    <?php if ($status === 'paid'): ?>
                                        <span class="badge badge-paid">Paid</span>
                                    <?php elseif ($status === 'advance'): ?>
                                        <span class="badge badge-advance">Advance</span>
                                    <?php else: ?>
                                        <span class="badge badge-unpaid">Unpaid</span>
                                    <?php endif; ?>
                                </td>
                                <td><?php echo htmlspecialchars($row['due_date'] ?? '-', ENT_QUOTES, 'UTF-8'); ?></td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="7" class="empty">No bills found.</td>
                        </tr>
                    <?php endif; ?>
                </table>
            </div>
        </div>

        <div class="section">
            <h2>Top Products</h2>
            <div class="table-wrap">
                <table>
                    <tr>
                        <th>Product</th>
                        <th>Quantity Sold</th>
                    </tr>

                    <?php if ($top_products && $top_products->num_rows > 0): ?>
                        <?php while ($row = $top_products->fetch_assoc()): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($row['product_name'] ?? '', ENT_QUOTES, 'UTF-8'); ?></td>
                                <td><?php echo (int)($row['qty'] ?? 0); ?></td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="2" class="empty">No product sales found.</td>
                        </tr>
                    <?php endif; ?>
                </table>
            </div>
        </div>

    </div>
</div>

</body>
</html>