<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_once("../php/db_connect.php");

if (!isset($_SESSION["user_id"])) {
    echo "<script>window.location='index.php';</script>";
    exit();
}

$shop_id = (int) $_SESSION["user_id"];

function h($value) {
    return htmlspecialchars((string)($value ?? ""), ENT_QUOTES, "UTF-8");
}

function formatDateOnly($date) {
    if (empty($date) || $date === "0000-00-00" || $date === "0000-00-00 00:00:00") {
        return "N/A";
    }
    return date("M d, Y", strtotime($date));
}

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

function displayStatus($status) {
    return empty($status) ? "N/A" : ucwords(str_replace("_", " ", $status));
}

function statusBadgeClass($status) {
    $status = strtolower((string)($status ?? ""));

    return match ($status) {
        "pending" => "bg-warning-subtle text-warning-emphasis border border-warning-subtle",
        "accepted" => "bg-info-subtle text-info border border-info-subtle",
        "in_progress" => "bg-primary-subtle text-primary border border-primary-subtle",
        "completed" => "bg-success-subtle text-success border border-success-subtle",
        "outsourced" => "bg-purple-subtle text-purple border border-purple-subtle",
        "assigned" => "bg-secondary-subtle text-secondary border border-secondary-subtle",
        "cancelled" => "bg-danger-subtle text-danger border border-danger-subtle",
        default => "bg-secondary-subtle text-secondary border border-secondary-subtle",
    };
}

function bindAndExecute($stmt, $types = "", $params = []) {
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    return $stmt->get_result();
}

function getCount($conn, $sql, $types, $params) {
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        die("Prepare failed: " . $conn->error);
    }

    $result = bindAndExecute($stmt, $types, $params);
    $row = $result->fetch_assoc();

    return (int)($row["total"] ?? 0);
}

$from_date = $_GET["from_date"] ?? "";
$to_date = $_GET["to_date"] ?? "";

$orderDateFilter = "";
$orderParams = [$shop_id];
$orderTypes = "i";

if (!empty($from_date) && !empty($to_date)) {
    $orderDateFilter = " AND DATE(created_at) BETWEEN ? AND ? ";
    $orderParams[] = $from_date;
    $orderParams[] = $to_date;
    $orderTypes .= "ss";
} elseif (!empty($from_date)) {
    $orderDateFilter = " AND DATE(created_at) >= ? ";
    $orderParams[] = $from_date;
    $orderTypes .= "s";
} elseif (!empty($to_date)) {
    $orderDateFilter = " AND DATE(created_at) <= ? ";
    $orderParams[] = $to_date;
    $orderTypes .= "s";
}

/* SUMMARY */
$totalOrders = getCount($conn, "SELECT COUNT(*) AS total FROM orders WHERE shop_id = ? $orderDateFilter", $orderTypes, $orderParams);

$activeOrders = getCount($conn, "
    SELECT COUNT(*) AS total 
    FROM orders 
    WHERE shop_id = ? 
    AND status IN ('pending', 'accepted', 'in_progress') 
    $orderDateFilter
", $orderTypes, $orderParams);

$completedOrders = getCount($conn, "
    SELECT COUNT(*) AS total 
    FROM orders 
    WHERE shop_id = ? 
    AND status = 'completed' 
    $orderDateFilter
", $orderTypes, $orderParams);

$outsourcedOrders = getCount($conn, "
    SELECT COUNT(*) AS total 
    FROM orders 
    WHERE shop_id = ? 
    AND status = 'outsourced' 
    $orderDateFilter
", $orderTypes, $orderParams);

$overdueOrders = getCount($conn, "
    SELECT COUNT(*) AS total 
    FROM orders 
    WHERE shop_id = ? 
    AND due_date IS NOT NULL
    AND due_date != '0000-00-00'
    AND DATE(due_date) < CURDATE()
    AND status NOT IN ('completed', 'cancelled')
    $orderDateFilter
", $orderTypes, $orderParams);

/* SALES TRENDS */
$salesStmt = $conn->prepare("
    SELECT 
        COUNT(*) AS paid_orders,
        COALESCE(SUM(total_price), 0) AS total_sales
    FROM orders
    WHERE shop_id = ?
    AND LOWER(payment_status) = 'paid'
    $orderDateFilter
");

if (!$salesStmt) {
    die("Prepare failed: " . $conn->error);
}

$sales = bindAndExecute($salesStmt, $orderTypes, $orderParams)->fetch_assoc();
$totalSales = (float)($sales["total_sales"] ?? 0);
$paidOrders = (int)($sales["paid_orders"] ?? 0);

/* FEEDBACK ANALYSIS */
$feedbackStmt = $conn->prepare("
    SELECT
        COUNT(*) AS total_feedback,
        COALESCE(AVG(feedback_rating), 0) AS average_rating,
        SUM(CASE WHEN feedback_rating <= 2 THEN 1 ELSE 0 END) AS low_ratings
    FROM orders
    WHERE shop_id = ?
    AND feedback_rating IS NOT NULL
    $orderDateFilter
");

if (!$feedbackStmt) {
    die("Prepare failed: " . $conn->error);
}

$feedbackSummary = bindAndExecute($feedbackStmt, $orderTypes, $orderParams)->fetch_assoc();
$totalFeedback = (int)($feedbackSummary["total_feedback"] ?? 0);
$averageRating = (float)($feedbackSummary["average_rating"] ?? 0);
$lowRatings = (int)($feedbackSummary["low_ratings"] ?? 0);

/* WORKFLOW METRICS */
$completionRate = $totalOrders > 0 ? ($completedOrders / $totalOrders) * 100 : 0;
$outsourceRate = $totalOrders > 0 ? ($outsourcedOrders / $totalOrders) * 100 : 0;
$overdueRate = $totalOrders > 0 ? ($overdueOrders / $totalOrders) * 100 : 0;

/* ORDER REPORT */
$orderStmt = $conn->prepare("
    SELECT 
        order_code,
        customer_name,
        service_type,
        item_name,
        quantity,
        size,
        due_date,
        payment_status,
        status,
        total_price,
        created_at
    FROM orders
    WHERE shop_id = ?
    $orderDateFilter
    ORDER BY created_at DESC
");

if (!$orderStmt) {
    die("Prepare failed: " . $conn->error);
}

$orders = bindAndExecute($orderStmt, $orderTypes, $orderParams);

/* SERVICE PERFORMANCE */
$serviceSql = "
    SELECT
        COALESCE(NULLIF(oi.service_type, ''), NULLIF(o.service_type, ''), 'N/A') AS service_type,
        COALESCE(NULLIF(oi.sub_type, ''), NULLIF(o.item_name, ''), 'N/A') AS item_name,
        COUNT(DISTINCT o.id) AS total_orders,
        SUM(COALESCE(oi.quantity, o.quantity, 0)) AS total_quantity
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.id
    WHERE o.shop_id = ?
";

$serviceParams = [$shop_id];
$serviceTypes = "i";

if (!empty($from_date) && !empty($to_date)) {
    $serviceSql .= " AND DATE(o.created_at) BETWEEN ? AND ? ";
    $serviceParams[] = $from_date;
    $serviceParams[] = $to_date;
    $serviceTypes .= "ss";
} elseif (!empty($from_date)) {
    $serviceSql .= " AND DATE(o.created_at) >= ? ";
    $serviceParams[] = $from_date;
    $serviceTypes .= "s";
} elseif (!empty($to_date)) {
    $serviceSql .= " AND DATE(o.created_at) <= ? ";
    $serviceParams[] = $to_date;
    $serviceTypes .= "s";
}

$serviceSql .= "
    GROUP BY service_type, item_name
    ORDER BY total_orders DESC, total_quantity DESC
";

$serviceStmt = $conn->prepare($serviceSql);
if (!$serviceStmt) {
    die("Prepare failed: " . $conn->error);
}

$serviceReport = bindAndExecute($serviceStmt, $serviceTypes, $serviceParams);

/* OUTSOURCING EFFICIENCY */
$outsourcingSql = "
    SELECT
        j.status AS job_status,
        j.awarded_price,
        j.awarded_at,
        j.completion_date,
        o.order_code,
        o.customer_name,
        o.item_name,
        o.quantity,
        o.size,
        executor_shop.shop_name AS executor_shop_name
    FROM outsourced_jobs j
    LEFT JOIN orders o ON j.order_id = o.id
    LEFT JOIN users executor_shop ON j.executor_shop_id = executor_shop.id
    WHERE j.original_shop_id = ?
";

$outsourceParams = [$shop_id];
$outsourceTypes = "i";

if (!empty($from_date) && !empty($to_date)) {
    $outsourcingSql .= " AND DATE(j.created_at) BETWEEN ? AND ? ";
    $outsourceParams[] = $from_date;
    $outsourceParams[] = $to_date;
    $outsourceTypes .= "ss";
} elseif (!empty($from_date)) {
    $outsourcingSql .= " AND DATE(j.created_at) >= ? ";
    $outsourceParams[] = $from_date;
    $outsourceTypes .= "s";
} elseif (!empty($to_date)) {
    $outsourcingSql .= " AND DATE(j.created_at) <= ? ";
    $outsourceParams[] = $to_date;
    $outsourceTypes .= "s";
}

$outsourcingSql .= " ORDER BY j.created_at DESC";

$outsourceStmt = $conn->prepare($outsourcingSql);
if (!$outsourceStmt) {
    die("Prepare failed: " . $conn->error);
}

$outsourcingReport = bindAndExecute($outsourceStmt, $outsourceTypes, $outsourceParams);
?>

<div class="container-fluid px-0 reports-page">

    <div class="d-flex justify-content-between align-items-center flex-wrap gap-3 mb-4 no-print">
        <div>
            <h1 class="fw-bold mb-1">Reports</h1>
            <p class="text-muted mb-0">
                Consolidated sales, outsourcing, feedback, and workflow performance reports for audits, management reviews, and decision-making.
            </p>
        </div>

        <button type="button" onclick="window.print()" class="btn btn-dark rounded-pill px-4">
            <i class="fa-solid fa-print me-1"></i> Print Report
        </button>
    </div>

    <div class="card border-0 shadow-sm rounded-4 mb-4 no-print">
        <div class="card-body p-4">
            <form method="GET" class="row g-3 align-items-end">
                <input type="hidden" name="page" value="reports">

                <div class="col-md-4">
                    <label class="form-label fw-semibold">From Date</label>
                    <input type="date" name="from_date" class="form-control" value="<?php echo h($from_date); ?>">
                </div>

                <div class="col-md-4">
                    <label class="form-label fw-semibold">To Date</label>
                    <input type="date" name="to_date" class="form-control" value="<?php echo h($to_date); ?>">
                </div>

                <div class="col-md-4 d-flex gap-2">
                    <button type="submit" class="btn btn-primary px-4">
                        <i class="fa-solid fa-filter me-1"></i> Filter
                    </button>

                    <a href="admin.php?page=reports" class="btn btn-light border px-4">
                        Reset
                    </a>
                </div>
            </form>
        </div>
    </div>

    <div class="print-header mb-4">
        <h2 class="fw-bold mb-1">Printing Shop Collaboration System Report</h2>
        <p class="mb-0">Shop: <?php echo h($_SESSION["shop_name"] ?? "Shop Admin"); ?></p>
        <p class="mb-0">
            Date Range:
            <?php echo !empty($from_date) ? h(formatDateOnly($from_date)) : "Start"; ?>
            to
            <?php echo !empty($to_date) ? h(formatDateOnly($to_date)) : "Today"; ?>
        </p>
        <p class="mb-0">Generated On: <?php echo date("M d, Y h:i A"); ?></p>
    </div>

    <div class="row g-3 mb-4">
        <div class="col-12 col-md-4 col-xl-2">
            <div class="report-stat-card">
                <div class="stat-icon bg-success-subtle text-success">
                    <i class="fa-solid fa-chart-line"></i>
                </div>
                <div>
                    <p>Total Sales</p>
                    <h3><?php echo formatMoney($totalSales); ?></h3>
                </div>
            </div>
        </div>

        <div class="col-12 col-md-4 col-xl-2">
            <div class="report-stat-card">
                <div class="stat-icon bg-primary-subtle text-primary">
                    <i class="fa-solid fa-receipt"></i>
                </div>
                <div>
                    <p>Total Orders</p>
                    <h3><?php echo $totalOrders; ?></h3>
                </div>
            </div>
        </div>

        <div class="col-12 col-md-4 col-xl-2">
            <div class="report-stat-card">
                <div class="stat-icon bg-info-subtle text-info">
                    <i class="fa-solid fa-share-nodes"></i>
                </div>
                <div>
                    <p>Outsourced</p>
                    <h3><?php echo $outsourcedOrders; ?></h3>
                </div>
            </div>
        </div>

        <div class="col-12 col-md-4 col-xl-2">
            <div class="report-stat-card">
                <div class="stat-icon bg-warning-subtle text-warning-emphasis">
                    <i class="fa-solid fa-star"></i>
                </div>
                <div>
                    <p>Avg. Rating</p>
                    <h3><?php echo number_format($averageRating, 1); ?>/5</h3>
                </div>
            </div>
        </div>

        <div class="col-12 col-md-4 col-xl-2">
            <div class="report-stat-card">
                <div class="stat-icon bg-success-subtle text-success">
                    <i class="fa-solid fa-circle-check"></i>
                </div>
                <div>
                    <p>Completion</p>
                    <h3><?php echo number_format($completionRate, 1); ?>%</h3>
                </div>
            </div>
        </div>

        <div class="col-12 col-md-4 col-xl-2">
            <div class="report-stat-card">
                <div class="stat-icon bg-danger-subtle text-danger">
                    <i class="fa-solid fa-triangle-exclamation"></i>
                </div>
                <div>
                    <p>Overdue</p>
                    <h3><?php echo $overdueOrders; ?></h3>
                </div>
            </div>
        </div>
    </div>

    <div class="card border-0 shadow-sm rounded-4 mb-4">
        <div class="card-body p-4">
            <h4 class="fw-bold mb-3">Operational Insights</h4>

            <div class="row g-3">
                <div class="col-12 col-md-4">
                    <div class="insight-box">
                        <span>Sales Trend</span>
                        <h4><?php echo formatMoney($totalSales); ?></h4>
                        <small><?php echo $paidOrders; ?> paid order(s) recorded.</small>
                    </div>
                </div>

                <div class="col-12 col-md-4">
                    <div class="insight-box">
                        <span>Outsourced Job Efficiency</span>
                        <h4><?php echo number_format($outsourceRate, 1); ?>%</h4>
                        <small>Percentage of orders outsourced to partner shops.</small>
                    </div>
                </div>

                <div class="col-12 col-md-4">
                    <div class="insight-box">
                        <span>Workflow Performance</span>
                        <h4><?php echo number_format($completionRate, 1); ?>%</h4>
                        <small>Completed orders compared to total orders.</small>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="card border-0 shadow-sm rounded-4 mb-4 report-card">
        <div class="card-body p-4">
            <h4 class="fw-bold mb-3">Order Report</h4>

            <div class="table-responsive">
                <table class="table table-hover align-middle report-table">
                    <thead>
                        <tr>
                            <th>Order Code</th>
                            <th>Customer</th>
                            <th>Service</th>
                            <th>Item</th>
                            <th>Qty</th>
                            <th>Due Date</th>
                            <th>Payment</th>
                            <th>Total Price</th>
                            <th>Status</th>
                            <th>Date Created</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if ($orders && $orders->num_rows > 0): ?>
                            <?php while ($order = $orders->fetch_assoc()): ?>
                                <tr>
                                    <td class="fw-semibold"><?php echo h($order["order_code"]); ?></td>
                                    <td><?php echo h($order["customer_name"]); ?></td>
                                    <td><?php echo h($order["service_type"]); ?></td>
                                    <td><?php echo h($order["item_name"]); ?></td>
                                    <td><?php echo (int)$order["quantity"]; ?></td>
                                    <td><?php echo h(formatDateOnly($order["due_date"])); ?></td>
                                    <td><?php echo h(displayStatus($order["payment_status"])); ?></td>
                                    <td><?php echo formatMoney($order["total_price"]); ?></td>
                                    <td>
                                        <span class="badge rounded-pill px-3 py-2 <?php echo statusBadgeClass($order["status"]); ?>">
                                            <?php echo h(displayStatus($order["status"])); ?>
                                        </span>
                                    </td>
                                    <td><?php echo h(formatDateOnly($order["created_at"])); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="10" class="text-center text-muted py-4">No order records found.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="card border-0 shadow-sm rounded-4 mb-4 report-card">
        <div class="card-body p-4">
            <h4 class="fw-bold mb-3">Service Performance Report</h4>

            <div class="table-responsive">
                <table class="table table-hover align-middle report-table">
                    <thead>
                        <tr>
                            <th>Service Type</th>
                            <th>Item / Subtype</th>
                            <th>Total Orders</th>
                            <th>Total Quantity</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if ($serviceReport && $serviceReport->num_rows > 0): ?>
                            <?php while ($service = $serviceReport->fetch_assoc()): ?>
                                <tr>
                                    <td><?php echo h($service["service_type"]); ?></td>
                                    <td class="fw-semibold"><?php echo h($service["item_name"]); ?></td>
                                    <td><?php echo (int)$service["total_orders"]; ?></td>
                                    <td><?php echo (int)$service["total_quantity"]; ?></td>
                                </tr>
                            <?php endwhile; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="4" class="text-center text-muted py-4">No service performance data found.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="card border-0 shadow-sm rounded-4 report-card">
        <div class="card-body p-4">
            <h4 class="fw-bold mb-3">Outsourced Job Efficiency Report</h4>

            <div class="table-responsive">
                <table class="table table-hover align-middle report-table">
                    <thead>
                        <tr>
                            <th>Order Code</th>
                            <th>Customer</th>
                            <th>Executor Shop</th>
                            <th>Item</th>
                            <th>Qty</th>
                            <th>Awarded Price</th>
                            <th>Job Status</th>
                            <th>Awarded At</th>
                            <th>Completion Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if ($outsourcingReport && $outsourcingReport->num_rows > 0): ?>
                            <?php while ($job = $outsourcingReport->fetch_assoc()): ?>
                                <tr>
                                    <td class="fw-semibold"><?php echo h($job["order_code"] ?: "N/A"); ?></td>
                                    <td><?php echo h($job["customer_name"] ?: "N/A"); ?></td>
                                    <td><?php echo h($job["executor_shop_name"] ?: "N/A"); ?></td>
                                    <td><?php echo h($job["item_name"] ?: "N/A"); ?></td>
                                    <td><?php echo (int)($job["quantity"] ?? 0); ?></td>
                                    <td><?php echo formatMoney($job["awarded_price"]); ?></td>
                                    <td>
                                        <span class="badge rounded-pill px-3 py-2 <?php echo statusBadgeClass($job["job_status"]); ?>">
                                            <?php echo h(displayStatus($job["job_status"])); ?>
                                        </span>
                                    </td>
                                    <td><?php echo h(formatDateOnly($job["awarded_at"])); ?></td>
                                    <td><?php echo h(formatDateOnly($job["completion_date"])); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="9" class="text-center text-muted py-4">No outsourcing records found.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

</div>

<style>
.reports-page h1 {
    font-size: clamp(28px, 2.2vw, 40px);
    color: #111827;
}

.form-control {
    border-radius: 12px;
    padding: 11px 13px;
}

.btn {
    border-radius: 12px;
}

.report-stat-card {
    background: #ffffff;
    border-radius: 20px;
    padding: 18px;
    box-shadow: 0 6px 18px rgba(0,0,0,0.05);
    display: flex;
    align-items: center;
    gap: 13px;
    height: 100%;
}

.stat-icon {
    width: 52px;
    height: 52px;
    border-radius: 16px;
    display: grid;
    place-items: center;
    font-size: 21px;
    flex-shrink: 0;
}

.report-stat-card p {
    margin: 0 0 3px;
    color: #6b7280;
    font-size: 13px;
}

.report-stat-card h3 {
    margin: 0;
    font-size: 24px;
    font-weight: 800;
    color: #111827;
}

.insight-box {
    background: #f8fafc;
    border: 1px solid #eef2f7;
    border-radius: 18px;
    padding: 20px;
    height: 100%;
}

.insight-box span {
    color: #6b7280;
    font-size: 14px;
}

.insight-box h4 {
    font-size: 26px;
    font-weight: 800;
    margin: 8px 0 4px;
    color: #111827;
}

.insight-box small {
    color: #6b7280;
}

.report-table {
    min-width: 1000px;
}

.report-table thead th {
    background: #f8fafc;
    color: #111827;
    font-size: 13px;
    font-weight: 700;
    padding: 14px;
    white-space: nowrap;
}

.report-table tbody td {
    padding: 14px;
    font-size: 13px;
    white-space: nowrap;
    border-bottom: 1px solid #f0f0f0;
}

.report-table tbody tr:hover {
    background: #f9fafb;
}

.bg-purple-subtle {
    background: #f3e8ff;
}

.text-purple {
    color: #7e22ce;
}

.print-header {
    display: none;
}

@media print {
    .no-print,
    .desktop-sidebar,
    .mobile-topbar,
    .admin-topbar,
    .dashboard-right-panel {
        display: none !important;
    }

    .admin-shell,
    .admin-main,
    .admin-content-wrap,
    .admin-content {
        display: block !important;
        width: 100% !important;
        height: auto !important;
        overflow: visible !important;
        padding: 0 !important;
        margin: 0 !important;
    }

    .print-header {
        display: block !important;
    }

    .card,
    .report-stat-card {
        box-shadow: none !important;
        border: 1px solid #ddd !important;
        page-break-inside: avoid;
    }

    .report-table {
        min-width: 100% !important;
    }

    .report-table th,
    .report-table td {
        font-size: 11px !important;
        padding: 7px !important;
        white-space: normal !important;
    }
}
</style>