<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Recipe Profit Report';

if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2); }
}
if (!function_exists('rpr_stock_balance')) {
    function rpr_stock_balance(PDO $pdo, int $itemId, int $storeId = 0): float {
        $where = "item_id = :item_id";
        $params = [':item_id' => $itemId];

        if ($storeId > 0) {
            $where .= " AND store_id = :store_id";
            $params[':store_id'] = $storeId;
        }

        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(qty_in - qty_out),0)
            FROM stock_movements
            WHERE $where
        ");
        $stmt->execute($params);
        return (float)$stmt->fetchColumn();
    }
}

$errors = [];

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS store_locations (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        location_code VARCHAR(50) NULL,
        location_name VARCHAR(150) NOT NULL,
        location_type ENUM('main_store','bar','restaurant','curio','kitchen','housekeeping','maintenance','other') NOT NULL DEFAULT 'main_store',
        is_pos_location TINYINT(1) NOT NULL DEFAULT 0,
        allow_room_charge TINYINT(1) NOT NULL DEFAULT 1,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        notes TEXT NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_store_location_code (location_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_item_categories (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        category_name VARCHAR(120) NOT NULL,
        category_type ENUM('stock','food','bar','curio','consumable','service','other') NOT NULL DEFAULT 'stock',
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_inventory_category_name (category_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_units (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        unit_code VARCHAR(30) NOT NULL,
        unit_name VARCHAR(80) NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_inventory_unit_code (unit_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_items (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        item_code VARCHAR(60) NULL,
        item_name VARCHAR(180) NOT NULL,
        category_id INT UNSIGNED NULL,
        unit_id INT UNSIGNED NULL,
        item_type ENUM('stock','food','bar','curio','consumable','service','recipe','other') NOT NULL DEFAULT 'stock',
        barcode VARCHAR(100) NULL,
        description TEXT NULL,
        cost_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        avg_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        selling_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        tax_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
        reorder_level DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        reorder_qty DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        is_stock_item TINYINT(1) NOT NULL DEFAULT 1,
        is_pos_item TINYINT(1) NOT NULL DEFAULT 0,
        allow_room_charge TINYINT(1) NOT NULL DEFAULT 1,
        track_expiry TINYINT(1) NOT NULL DEFAULT 0,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_inventory_item_code (item_code),
        INDEX idx_inventory_item_name (item_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS stock_movements (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        item_id INT UNSIGNED NOT NULL,
        store_id INT UNSIGNED NOT NULL,
        movement_type ENUM('OPENING','GRN','ISSUE','SALE','TRANSFER_IN','TRANSFER_OUT','ADJUSTMENT_IN','ADJUSTMENT_OUT','WASTAGE','RETURN') NOT NULL,
        qty_in DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        qty_out DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        total_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        reference_type VARCHAR(60) NULL,
        reference_id BIGINT UNSIGNED NULL,
        notes TEXT NULL,
        movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_stock_item_store (item_id, store_id),
        INDEX idx_stock_reference (reference_type, reference_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_recipes (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        recipe_item_id INT UNSIGNED NOT NULL,
        recipe_name VARCHAR(180) NOT NULL,
        yield_qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
        default_store_id INT UNSIGNED NULL,
        preparation_notes TEXT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_by INT UNSIGNED NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_recipe_item (recipe_item_id),
        INDEX idx_recipe_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_recipe_lines (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        recipe_id BIGINT UNSIGNED NOT NULL,
        ingredient_item_id INT UNSIGNED NOT NULL,
        qty_required DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        wastage_percent DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
        notes TEXT NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_recipe_line_recipe (recipe_id),
        INDEX idx_recipe_line_ingredient (ingredient_item_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
} catch (Throwable $e) {
    $errors[] = 'Could not prepare recipe report tables: ' . $e->getMessage();
}

$stores = [];
$categories = [];

try {
    $stores = $pdo->query("SELECT * FROM store_locations WHERE is_active=1 ORDER BY is_pos_location DESC, location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
    $categories = $pdo->query("SELECT * FROM inventory_item_categories WHERE is_active=1 ORDER BY category_name ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $errors[] = 'Could not load filters: ' . $e->getMessage();
}

$storeId = (int)($_GET['store_id'] ?? 0);
$categoryId = (int)($_GET['category_id'] ?? 0);
$status = trim((string)($_GET['status'] ?? '1'));
$marginBand = trim((string)($_GET['margin_band'] ?? ''));
$search = trim((string)($_GET['q'] ?? ''));

$where = "1=1";
$params = [];

if ($storeId > 0) {
    $where .= " AND (r.default_store_id = :store_id OR r.default_store_id IS NULL)";
    $params[':store_id'] = $storeId;
}
if ($categoryId > 0) {
    $where .= " AND recipe_item.category_id = :category_id";
    $params[':category_id'] = $categoryId;
}
if ($status !== '') {
    $where .= " AND r.is_active = :is_active";
    $params[':is_active'] = (int)$status;
}
if ($search !== '') {
    $where .= " AND (r.recipe_name LIKE :q OR recipe_item.item_name LIKE :q OR recipe_item.item_code LIKE :q OR c.category_name LIKE :q)";
    $params[':q'] = '%' . $search . '%';
}

$rows = [];
$summary = [
    'recipes' => 0,
    'avg_food_cost' => 0,
    'avg_margin' => 0,
    'total_cost' => 0,
    'total_selling' => 0,
    'total_profit' => 0,
    'low_margin' => 0,
    'loss_items' => 0,
    'cannot_make' => 0,
];

try {
    $stmt = $pdo->prepare("
        SELECT
            r.*,
            recipe_item.item_name,
            recipe_item.item_code,
            recipe_item.selling_price,
            recipe_item.tax_rate,
            c.category_name,
            sl.location_name,
            COUNT(rl.id) AS ingredient_count,
            COALESCE(SUM((rl.qty_required + (rl.qty_required * (rl.wastage_percent / 100))) * COALESCE(NULLIF(ing.avg_cost,0), ing.cost_price, 0)),0) AS total_recipe_cost
        FROM inventory_recipes r
        INNER JOIN inventory_items recipe_item ON recipe_item.id = r.recipe_item_id
        LEFT JOIN inventory_item_categories c ON c.id = recipe_item.category_id
        LEFT JOIN store_locations sl ON sl.id = r.default_store_id
        LEFT JOIN inventory_recipe_lines rl ON rl.recipe_id = r.id
        LEFT JOIN inventory_items ing ON ing.id = rl.ingredient_item_id
        WHERE $where
        GROUP BY r.id
        ORDER BY r.is_active DESC, c.category_name ASC, r.recipe_name ASC
        LIMIT 1000
    ");
    $stmt->execute($params);
    $rawRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rawRows as $r) {
        $yieldQty = max(1, (float)$r['yield_qty']);
        $costPerSale = (float)$r['total_recipe_cost'] / $yieldQty;
        $selling = (float)$r['selling_price'];
        $profit = $selling - $costPerSale;
        $foodCostPercent = $selling > 0 ? ($costPerSale / $selling) * 100 : 0;
        $marginPercent = $selling > 0 ? ($profit / $selling) * 100 : 0;

        if ($marginBand === 'loss' && $profit >= 0) continue;
        if ($marginBand === 'low' && !($profit >= 0 && $marginPercent < 35)) continue;
        if ($marginBand === 'good' && $marginPercent < 35) continue;
        if ($marginBand === 'high_food_cost' && $foodCostPercent <= 45) continue;

        $stmtLines = $pdo->prepare("
            SELECT
                rl.*,
                ing.item_name,
                ing.avg_cost,
                ing.cost_price
            FROM inventory_recipe_lines rl
            INNER JOIN inventory_items ing ON ing.id = rl.ingredient_item_id
            WHERE rl.recipe_id = :recipe_id
            ORDER BY rl.id ASC
        ");
        $stmtLines->execute([':recipe_id' => (int)$r['id']]);
        $lines = $stmtLines->fetchAll(PDO::FETCH_ASSOC);

        $possible = [];
        $shortIngredients = 0;
        $calcStoreId = $storeId > 0 ? $storeId : (int)($r['default_store_id'] ?? 0);

        foreach ($lines as $line) {
            $qtyRequired = (float)$line['qty_required'];
            $effectiveQty = $qtyRequired + ($qtyRequired * ((float)$line['wastage_percent'] / 100));
            if ($effectiveQty <= 0) continue;

            $balance = rpr_stock_balance($pdo, (int)$line['ingredient_item_id'], $calcStoreId);
            $possible[] = floor($balance / ($effectiveQty / $yieldQty));
            if ($balance < ($effectiveQty / $yieldQty)) $shortIngredients++;
        }

        $canMake = $possible ? max(0, (int)min($possible)) : 0;

        $r['cost_per_sale'] = $costPerSale;
        $r['gross_profit'] = $profit;
        $r['food_cost_percent'] = $foodCostPercent;
        $r['margin_percent'] = $marginPercent;
        $r['can_make_qty'] = $canMake;
        $r['short_ingredients'] = $shortIngredients;

        $rows[] = $r;

        $summary['recipes']++;
        $summary['total_cost'] += $costPerSale;
        $summary['total_selling'] += $selling;
        $summary['total_profit'] += $profit;

        if ($profit < 0) $summary['loss_items']++;
        if ($selling > 0 && $marginPercent < 35) $summary['low_margin']++;
        if ($canMake <= 0) $summary['cannot_make']++;
    }

    $summary['avg_food_cost'] = $summary['total_selling'] > 0 ? ($summary['total_cost'] / $summary['total_selling']) * 100 : 0;
    $summary['avg_margin'] = $summary['total_selling'] > 0 ? ($summary['total_profit'] / $summary['total_selling']) * 100 : 0;

} catch (Throwable $e) {
    $errors[] = 'Could not load recipe profitability: ' . $e->getMessage();
}

$categoryTotals = [];
foreach ($rows as $r) {
    $cat = $r['category_name'] ?: 'Uncategorised';
    if (!isset($categoryTotals[$cat])) {
        $categoryTotals[$cat] = ['recipes'=>0,'cost'=>0,'selling'=>0,'profit'=>0,'low'=>0];
    }
    $categoryTotals[$cat]['recipes']++;
    $categoryTotals[$cat]['cost'] += (float)$r['cost_per_sale'];
    $categoryTotals[$cat]['selling'] += (float)$r['selling_price'];
    $categoryTotals[$cat]['profit'] += (float)$r['gross_profit'];
    if ((float)$r['margin_percent'] < 35) $categoryTotals[$cat]['low']++;
}

$bestRows = $rows;
usort($bestRows, function($a, $b) {
    return ((float)$b['gross_profit'] <=> (float)$a['gross_profit']);
});
$worstRows = $rows;
usort($worstRows, function($a, $b) {
    return ((float)$a['margin_percent'] <=> (float)$b['margin_percent']);
});

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band no-print">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-graph-up-arrow"></i> Recipe Intelligence</div>
            <div class="hero-title mt-2">Recipe Profit Report</div>
            <div class="hero-subtitle mt-2">
                Menu costing, food cost %, gross profit, margin alerts and can-make stock availability.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <button onclick="window.print()" class="btn btn-light me-2"><i class="bi bi-printer me-2"></i>Print</button>
            <a href="recipes.php" class="btn btn-outline-light"><i class="bi bi-egg-fried me-2"></i>Recipes</a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger no-print">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0"><?php foreach ($errors as $err): ?><li><?php echo h($err); ?></li><?php endforeach; ?></ul>
    </div>
<?php endif; ?>

<div class="print-title">
    <h3 class="mb-1">Recipe Profit Report</h3>
    <div class="text-muted">Generated <?php echo h(date('d M Y H:i')); ?></div>
</div>

<div class="card panel-card mb-4 no-print">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-2">
                <label class="form-label">Store</label>
                <select name="store_id" class="form-select">
                    <option value="0">Recipe default / all</option>
                    <?php foreach ($stores as $s): ?>
                        <option value="<?php echo (int)$s['id']; ?>" <?php echo $storeId === (int)$s['id'] ? 'selected' : ''; ?>>
                            <?php echo h($s['location_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Category</label>
                <select name="category_id" class="form-select">
                    <option value="0">All categories</option>
                    <?php foreach ($categories as $cat): ?>
                        <option value="<?php echo (int)$cat['id']; ?>" <?php echo $categoryId === (int)$cat['id'] ? 'selected' : ''; ?>>
                            <?php echo h($cat['category_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Status</label>
                <select name="status" class="form-select">
                    <option value="">All</option>
                    <option value="1" <?php echo $status === '1' ? 'selected' : ''; ?>>Active</option>
                    <option value="0" <?php echo $status === '0' ? 'selected' : ''; ?>>Inactive</option>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Margin Band</label>
                <select name="margin_band" class="form-select">
                    <option value="">All</option>
                    <option value="loss" <?php echo $marginBand === 'loss' ? 'selected' : ''; ?>>Loss Items</option>
                    <option value="low" <?php echo $marginBand === 'low' ? 'selected' : ''; ?>>Low Margin &lt; 35%</option>
                    <option value="good" <?php echo $marginBand === 'good' ? 'selected' : ''; ?>>Good Margin ≥ 35%</option>
                    <option value="high_food_cost" <?php echo $marginBand === 'high_food_cost' ? 'selected' : ''; ?>>High Food Cost &gt; 45%</option>
                </select>
            </div>
            <div class="col-md-3">
                <label class="form-label">Search</label>
                <input type="text" name="q" class="form-control" value="<?php echo h($search); ?>" placeholder="Recipe / item / code">
            </div>
            <div class="col-md-1 d-grid">
                <button class="btn btn-primary"><i class="bi bi-funnel"></i></button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-journal-richtext"></i></div>
            <div><div class="metric-label">Recipes</div><div class="metric-value"><?php echo (int)$summary['recipes']; ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-percent"></i></div>
            <div><div class="metric-label">Avg Food Cost</div><div class="metric-value small-value"><?php echo money_fmt($summary['avg_food_cost']); ?>%</div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-graph-up"></i></div>
            <div><div class="metric-label">Avg Margin</div><div class="metric-value small-value"><?php echo money_fmt($summary['avg_margin']); ?>%</div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-triangle"></i></div>
            <div><div class="metric-label">Low Margin</div><div class="metric-value"><?php echo (int)$summary['low_margin']; ?></div></div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-cash-stack"></i></div>
            <div><div class="metric-label">Total Selling</div><div class="metric-value small-value"><?php echo money_fmt($summary['total_selling']); ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-arrow-down-circle"></i></div>
            <div><div class="metric-label">Total Cost</div><div class="metric-value small-value"><?php echo money_fmt($summary['total_cost']); ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-wallet2"></i></div>
            <div><div class="metric-label">Total Profit</div><div class="metric-value small-value"><?php echo money_fmt($summary['total_profit']); ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-dark-subtle text-dark"><i class="bi bi-box-seam"></i></div>
            <div><div class="metric-label">Cannot Make</div><div class="metric-value"><?php echo (int)$summary['cannot_make']; ?></div></div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4 no-print">
    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Best Profit Items</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Recipe</th><th class="text-end">Profit</th><th class="text-end">Margin</th></tr></thead>
                    <tbody>
                    <?php foreach (array_slice($bestRows, 0, 8) as $r): ?>
                        <tr>
                            <td><strong><?php echo h($r['recipe_name']); ?></strong></td>
                            <td class="text-end fw-bold text-success"><?php echo money_fmt($r['gross_profit']); ?></td>
                            <td class="text-end"><?php echo money_fmt($r['margin_percent']); ?>%</td>
                        </tr>
                    <?php endforeach; ?>
                    <?php if (!$bestRows): ?><tr><td colspan="3" class="text-center text-muted py-4">No data.</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Worst Margin Items</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Recipe</th><th class="text-end">Food Cost</th><th class="text-end">Margin</th></tr></thead>
                    <tbody>
                    <?php foreach (array_slice($worstRows, 0, 8) as $r): ?>
                        <tr class="<?php echo (float)$r['margin_percent'] < 35 ? 'table-warning' : ''; ?>">
                            <td><strong><?php echo h($r['recipe_name']); ?></strong></td>
                            <td class="text-end"><?php echo money_fmt($r['food_cost_percent']); ?>%</td>
                            <td class="text-end fw-bold <?php echo (float)$r['margin_percent'] < 35 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($r['margin_percent']); ?>%</td>
                        </tr>
                    <?php endforeach; ?>
                    <?php if (!$worstRows): ?><tr><td colspan="3" class="text-center text-muted py-4">No data.</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Category Profitability</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Category</th><th class="text-end">Recipes</th><th class="text-end">Margin</th></tr></thead>
                    <tbody>
                    <?php if (!$categoryTotals): ?>
                        <tr><td colspan="3" class="text-center text-muted py-4">No category totals.</td></tr>
                    <?php else: foreach ($categoryTotals as $cat => $t): ?>
                        <?php $margin = $t['selling'] > 0 ? ($t['profit'] / $t['selling']) * 100 : 0; ?>
                        <tr>
                            <td><strong><?php echo h($cat); ?></strong><div class="small text-muted"><?php echo (int)$t['low']; ?> low margin</div></td>
                            <td class="text-end"><?php echo (int)$t['recipes']; ?></td>
                            <td class="text-end fw-bold <?php echo $margin < 35 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($margin); ?>%</td>
                        </tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="card panel-card">
    <div class="card-header d-flex justify-content-between align-items-center">
        <h5 class="mb-0">Recipe Profitability Detail</h5>
        <span class="text-muted small">Showing up to 1,000 recipes</span>
    </div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-modern align-middle mb-0 recipe-profit-table">
                <thead>
                <tr>
                    <th>Recipe</th>
                    <th>Category</th>
                    <th>Store</th>
                    <th class="text-end">Ingredients</th>
                    <th class="text-end">Yield</th>
                    <th class="text-end">Cost</th>
                    <th class="text-end">Selling</th>
                    <th class="text-end">Profit</th>
                    <th class="text-end">Food Cost</th>
                    <th class="text-end">Margin</th>
                    <th class="text-end">Can Make</th>
                    <th>Status</th>
                </tr>
                </thead>
                <tbody>
                <?php if (!$rows): ?>
                    <tr><td colspan="12" class="text-center text-muted py-4">No recipes found.</td></tr>
                <?php else: ?>
                    <?php foreach ($rows as $r): ?>
                        <?php
                            $profit = (float)$r['gross_profit'];
                            $margin = (float)$r['margin_percent'];
                            $foodCost = (float)$r['food_cost_percent'];
                            $rowClass = $profit < 0 ? 'table-danger' : ($margin < 35 || $foodCost > 45 ? 'table-warning' : '');
                        ?>
                        <tr class="<?php echo h($rowClass); ?>">
                            <td>
                                <strong><?php echo h($r['recipe_name']); ?></strong>
                                <div class="small text-muted">
                                    <?php echo h($r['item_name']); ?>
                                    <?php echo $r['item_code'] ? ' • ' . h($r['item_code']) : ''; ?>
                                </div>
                            </td>
                            <td><?php echo h($r['category_name'] ?: 'Uncategorised'); ?></td>
                            <td><?php echo h($storeId > 0 ? 'Selected store' : ($r['location_name'] ?: 'Recipe default')); ?></td>
                            <td class="text-end"><?php echo (int)$r['ingredient_count']; ?></td>
                            <td class="text-end"><?php echo money_fmt($r['yield_qty']); ?></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($r['cost_per_sale']); ?></td>
                            <td class="text-end"><?php echo money_fmt($r['selling_price']); ?></td>
                            <td class="text-end fw-bold <?php echo $profit < 0 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($profit); ?></td>
                            <td class="text-end <?php echo $foodCost > 45 ? 'text-danger fw-bold' : ''; ?>"><?php echo money_fmt($foodCost); ?>%</td>
                            <td class="text-end <?php echo $margin < 35 ? 'text-danger fw-bold' : 'text-success fw-bold'; ?>"><?php echo money_fmt($margin); ?>%</td>
                            <td class="text-end <?php echo (int)$r['can_make_qty'] <= 0 ? 'text-danger fw-bold' : ''; ?>"><?php echo (int)$r['can_make_qty']; ?></td>
                            <td>
                                <?php if ((int)$r['is_active'] === 1): ?>
                                    <span class="badge text-bg-success">Active</span>
                                <?php else: ?>
                                    <span class="badge text-bg-secondary">Inactive</span>
                                <?php endif; ?>

                                <?php if ($profit < 0): ?>
                                    <span class="badge text-bg-danger">Loss</span>
                                <?php elseif ($margin < 35): ?>
                                    <span class="badge text-bg-warning">Low margin</span>
                                <?php endif; ?>

                                <?php if ((int)$r['can_make_qty'] <= 0): ?>
                                    <span class="badge text-bg-dark">Cannot make</span>
                                <?php endif; ?>

                                <?php if ((int)$r['short_ingredients'] > 0): ?>
                                    <span class="badge text-bg-danger"><?php echo (int)$r['short_ingredients']; ?> short</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
                </tbody>
                <tfoot>
                <tr>
                    <th colspan="5" class="text-end">Totals / Average</th>
                    <th class="text-end"><?php echo money_fmt($summary['total_cost']); ?></th>
                    <th class="text-end"><?php echo money_fmt($summary['total_selling']); ?></th>
                    <th class="text-end"><?php echo money_fmt($summary['total_profit']); ?></th>
                    <th class="text-end"><?php echo money_fmt($summary['avg_food_cost']); ?>%</th>
                    <th class="text-end"><?php echo money_fmt($summary['avg_margin']); ?>%</th>
                    <th colspan="2"></th>
                </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

<div class="alert alert-info mt-4 no-print">
    <strong>Guide:</strong> In restaurants, food cost above 45% or margin below 35% usually needs review. This report uses ingredient average cost from inventory.
</div>

<style>
.small-value{font-size:1rem!important;letter-spacing:0!important}
.print-title{display:none}
.recipe-profit-table tfoot th{background:#0f172a!important;color:#fff!important}
@media print{
    .no-print,.sidebar,.enterprise-sidebar,.app-sidebar,.topbar,.navbar,.header,.footer{display:none!important}
    .print-title{display:block;margin-bottom:16px}
    .panel-card,.card{box-shadow:none!important;border:1px solid #ddd!important}
    body{background:#fff!important}
    .table{font-size:9.5px}
    .metric-card{box-shadow:none!important;border:1px solid #ddd!important}
}
</style>

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