<?php
/**
 * /rooms/modules/inventory/recipe_import.php
 *
 * Recipe importer for Meal Recipes / Cocktail Recipes.
 *
 * What it does:
 * - Upload .xlsx, .xls, or .csv recipe costing files.
 * - Reads each worksheet as one recipe where possible.
 * - Matches worksheet/recipe names to restaurant_menu_items.
 * - Matches ingredient names to inventory_items.
 * - Shows a preview with matched/unmatched rows.
 * - Imports matched recipes into:
 *      inventory_recipes
 *      inventory_recipe_lines
 *
 * No Composer library required for .xlsx. It uses ZipArchive + XML parsing.
 */

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

$pageTitle = 'Recipe Import';

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('ri_flash')) {
    function ri_flash(string $type, string $message): void {
        $_SESSION['recipe_import_flash'][] = ['type' => $type, 'message' => $message];
    }
}
if (!function_exists('ri_get_flash')) {
    function ri_get_flash(): array {
        $rows = $_SESSION['recipe_import_flash'] ?? [];
        unset($_SESSION['recipe_import_flash']);
        return $rows;
    }
}
if (!function_exists('ri_table_exists')) {
    function ri_table_exists(PDO $pdo, string $table): bool {
        static $cache = [];
        if (isset($cache[$table])) return $cache[$table];
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :t");
            $stmt->execute([':t' => $table]);
            return $cache[$table] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$table] = false;
        }
    }
}
if (!function_exists('ri_column_exists')) {
    function ri_column_exists(PDO $pdo, string $table, string $column): bool {
        static $cache = [];
        $key = $table . '.' . $column;
        if (isset($cache[$key])) return $cache[$key];
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c");
            $stmt->execute([':t' => $table, ':c' => $column]);
            return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$key] = false;
        }
    }
}
if (!function_exists('ri_add_column')) {
    function ri_add_column(PDO $pdo, string $table, string $column, string $definition): void {
        if (!ri_column_exists($pdo, $table, $column)) {
            $pdo->exec("ALTER TABLE `$table` ADD COLUMN $definition");
        }
    }
}
if (!function_exists('ri_norm')) {
    function ri_norm($value): string {
        $s = strtolower(trim((string)$value));
        $s = html_entity_decode($s, ENT_QUOTES, 'UTF-8');
        $s = preg_replace('/[^\p{L}\p{N}\s]+/u', ' ', $s);
        $s = preg_replace('/\s+/', ' ', $s);
        return trim($s);
    }
}
if (!function_exists('ri_current_user_id')) {
    function ri_current_user_id(): ?int {
        foreach (['user_id', 'ss_user_id', 'id', 'uid', 'staffid'] as $key) {
            if (!empty($_SESSION[$key]) && (int)$_SESSION[$key] > 0) return (int)$_SESSION[$key];
        }
        return null;
    }
}
if (!function_exists('ri_float')) {
    function ri_float($value): float {
        if (is_numeric($value)) return (float)$value;
        $s = trim((string)$value);
        $s = str_replace([',', '$', 'USD', 'ZWG', 'ZWL'], '', $s);
        $s = preg_replace('/[^0-9.\-]/', '', $s);
        return is_numeric($s) ? (float)$s : 0.0;
    }
}
if (!function_exists('ri_best_match')) {
    function ri_best_match(string $needle, array $haystack, string $nameKey, int $minScore = 72): ?array {
        $needleNorm = ri_norm($needle);
        if ($needleNorm === '') return null;

        $best = null;
        $bestScore = 0;

        foreach ($haystack as $row) {
            $name = (string)($row[$nameKey] ?? '');
            $nameNorm = ri_norm($name);

            if ($nameNorm === '') continue;

            $score = 0;
            if ($needleNorm === $nameNorm) {
                $score = 100;
            } elseif (str_contains($nameNorm, $needleNorm) || str_contains($needleNorm, $nameNorm)) {
                $short = min(strlen($needleNorm), strlen($nameNorm));
                $long = max(strlen($needleNorm), strlen($nameNorm));
                $score = 84 + (int)round(($short / max(1, $long)) * 12);
            } else {
                similar_text($needleNorm, $nameNorm, $pct);
                $score = (int)round($pct);
            }

            $code = ri_norm((string)($row['item_code'] ?? $row['item_code'] ?? ''));
            if ($code !== '' && ($needleNorm === $code || str_contains($needleNorm, $code))) {
                $score = max($score, 92);
            }

            if ($score > $bestScore) {
                $bestScore = $score;
                $best = $row;
            }
        }

        if ($best && $bestScore >= $minScore) {
            $best['_match_score'] = $bestScore;
            return $best;
        }

        return null;
    }
}
if (!function_exists('ri_col_letters_to_index')) {
    function ri_col_letters_to_index(string $letters): int {
        $letters = strtoupper($letters);
        $num = 0;
        for ($i = 0; $i < strlen($letters); $i++) {
            $num = $num * 26 + (ord($letters[$i]) - ord('A') + 1);
        }
        return $num - 1;
    }
}
if (!function_exists('ri_xml_load')) {
    function ri_xml_load(string $xml): SimpleXMLElement {
        libxml_use_internal_errors(true);
        $node = simplexml_load_string($xml);
        if (!$node) throw new RuntimeException('Could not read Excel XML.');
        return $node;
    }
}
if (!function_exists('ri_xlsx_shared_strings')) {
    function ri_xlsx_shared_strings(ZipArchive $zip): array {
        $strings = [];
        $xml = $zip->getFromName('xl/sharedStrings.xml');
        if ($xml === false) return $strings;

        $sx = ri_xml_load($xml);
        foreach ($sx->si as $si) {
            $text = '';
            if (isset($si->t)) {
                $text = (string)$si->t;
            } elseif (isset($si->r)) {
                foreach ($si->r as $r) $text .= (string)$r->t;
            }
            $strings[] = $text;
        }
        return $strings;
    }
}
if (!function_exists('ri_parse_xlsx')) {
    function ri_parse_xlsx(string $file): array {
        if (!class_exists('ZipArchive')) {
            throw new RuntimeException('ZipArchive PHP extension is required to import .xlsx files.');
        }

        $zip = new ZipArchive();
        if ($zip->open($file) !== true) {
            throw new RuntimeException('Could not open Excel file.');
        }

        $shared = ri_xlsx_shared_strings($zip);

        $workbookXml = $zip->getFromName('xl/workbook.xml');
        $relsXml = $zip->getFromName('xl/_rels/workbook.xml.rels');

        if ($workbookXml === false || $relsXml === false) {
            throw new RuntimeException('Invalid Excel workbook structure.');
        }

        $workbook = ri_xml_load($workbookXml);
        $rels = ri_xml_load($relsXml);
        $rels->registerXPathNamespace('r', 'http://schemas.openxmlformats.org/package/2006/relationships');

        $relMap = [];
        foreach ($rels->Relationship as $rel) {
            $attrs = $rel->attributes();
            $id = (string)$attrs['Id'];
            $target = (string)$attrs['Target'];
            $relMap[$id] = 'xl/' . ltrim($target, '/');
        }

        $workbook->registerXPathNamespace('r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');

        $sheets = [];
        foreach ($workbook->sheets->sheet as $sheet) {
            $attrs = $sheet->attributes();
            $rAttrs = $sheet->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');

            $sheetName = (string)$attrs['name'];
            $rid = (string)$rAttrs['id'];
            $path = $relMap[$rid] ?? '';

            if ($path === '') continue;

            $sheetXml = $zip->getFromName($path);
            if ($sheetXml === false) continue;

            $sx = ri_xml_load($sheetXml);
            $rows = [];

            foreach ($sx->sheetData->row as $row) {
                $rowArr = [];
                foreach ($row->c as $cell) {
                    $attrs = $cell->attributes();
                    $ref = (string)$attrs['r'];
                    $type = (string)$attrs['t'];
                    preg_match('/([A-Z]+)/', $ref, $m);
                    $colIndex = isset($m[1]) ? ri_col_letters_to_index($m[1]) : count($rowArr);

                    $value = '';
                    if ($type === 's') {
                        $idx = (int)$cell->v;
                        $value = $shared[$idx] ?? '';
                    } elseif ($type === 'inlineStr') {
                        $value = (string)$cell->is->t;
                    } else {
                        $value = isset($cell->v) ? (string)$cell->v : '';
                    }

                    $rowArr[$colIndex] = trim((string)$value);
                }

                if ($rowArr) {
                    $max = max(array_keys($rowArr));
                    $clean = [];
                    for ($i = 0; $i <= $max; $i++) $clean[$i] = $rowArr[$i] ?? '';
                    if (trim(implode('', $clean)) !== '') $rows[] = $clean;
                }
            }

            if ($rows) $sheets[$sheetName] = $rows;
        }

        $zip->close();
        return $sheets;
    }
}
if (!function_exists('ri_parse_csv')) {
    function ri_parse_csv(string $file): array {
        $rows = [];
        $fh = fopen($file, 'r');
        if (!$fh) throw new RuntimeException('Could not open CSV file.');
        while (($row = fgetcsv($fh)) !== false) {
            $row = array_map(fn($v) => trim((string)$v), $row);
            if (trim(implode('', $row)) !== '') $rows[] = $row;
        }
        fclose($fh);
        return ['CSV Import' => $rows];
    }
}
if (!function_exists('ri_header_score')) {
    function ri_header_score(array $row): int {
        $score = 0;
        foreach ($row as $cell) {
            $c = ri_norm($cell);
            if (preg_match('/ingredient|item|product|description|stock/', $c)) $score += 3;
            if (preg_match('/qty|quantity|amount|portion/', $c)) $score += 3;
            if (preg_match('/unit|uom|measure/', $c)) $score += 2;
            if (preg_match('/cost|price|value/', $c)) $score += 1;
        }
        return $score;
    }
}
if (!function_exists('ri_detect_columns')) {
    function ri_detect_columns(array $header): array {
        $map = [
            'ingredient' => null,
            'qty' => null,
            'unit' => null,
            'waste' => null,
            'notes' => null,
        ];

        foreach ($header as $idx => $cell) {
            $c = ri_norm($cell);

            if ($map['ingredient'] === null && preg_match('/ingredient|product|item|description|stock/', $c)) {
                $map['ingredient'] = $idx;
            }
            if ($map['qty'] === null && preg_match('/qty|quantity|amount|portion|required/', $c)) {
                $map['qty'] = $idx;
            }
            if ($map['unit'] === null && preg_match('/unit|uom|measure/', $c)) {
                $map['unit'] = $idx;
            }
            if ($map['waste'] === null && preg_match('/waste|wastage|loss/', $c)) {
                $map['waste'] = $idx;
            }
            if ($map['notes'] === null && preg_match('/note|comment|method|prep/', $c)) {
                $map['notes'] = $idx;
            }
        }

        if ($map['ingredient'] === null) {
            // fallback: first text-heavy column
            $map['ingredient'] = 0;
        }
        if ($map['qty'] === null) {
            // fallback: first numeric-looking column after ingredient
            $map['qty'] = 1;
        }

        return $map;
    }
}
if (!function_exists('ri_guess_recipe_name')) {
    function ri_guess_recipe_name(string $sheetName, array $rows): string {
        $badSheets = ['sheet1','sheet2','sheet3','costing','recipes','recipe','menu','data','upload'];
        if (!in_array(ri_norm($sheetName), $badSheets, true)) {
            return trim($sheetName);
        }

        for ($i = 0; $i < min(8, count($rows)); $i++) {
            foreach ($rows[$i] as $j => $cell) {
                $c = ri_norm($cell);
                if (preg_match('/recipe|menu item|dish|cocktail/', $c)) {
                    $right = trim((string)($rows[$i][$j + 1] ?? ''));
                    if ($right !== '') return $right;
                }
            }
        }

        for ($i = 0; $i < min(5, count($rows)); $i++) {
            foreach ($rows[$i] as $cell) {
                $cell = trim((string)$cell);
                if ($cell !== '' && !is_numeric($cell) && strlen($cell) > 3) return $cell;
            }
        }

        return trim($sheetName);
    }
}
if (!function_exists('ri_parse_recipe_sheets')) {
    function ri_parse_recipe_sheets(array $sheets, array $menuItems, array $inventoryItems, string $defaultType): array {
        $recipes = [];

        foreach ($sheets as $sheetName => $rows) {
            if (count($rows) < 2) continue;

            $headerRowIndex = null;
            $bestScore = 0;

            for ($i = 0; $i < min(30, count($rows)); $i++) {
                $score = ri_header_score($rows[$i]);
                if ($score > $bestScore) {
                    $bestScore = $score;
                    $headerRowIndex = $i;
                }
            }

            if ($headerRowIndex === null || $bestScore < 4) {
                // fallback: try treating first rows as ingredient list
                $headerRowIndex = 0;
            }

            $header = $rows[$headerRowIndex] ?? [];
            $cols = ri_detect_columns($header);
            $recipeName = ri_guess_recipe_name($sheetName, $rows);
            $menuMatch = ri_best_match($recipeName, $menuItems, 'item_name', 68);

            $lines = [];
            for ($r = $headerRowIndex + 1; $r < count($rows); $r++) {
                $row = $rows[$r];

                $ingredientName = trim((string)($row[$cols['ingredient']] ?? ''));
                $qty = ri_float($row[$cols['qty']] ?? 0);
                $unit = $cols['unit'] !== null ? trim((string)($row[$cols['unit']] ?? '')) : '';
                $waste = $cols['waste'] !== null ? ri_float($row[$cols['waste']] ?? 0) : 0;
                $note = $cols['notes'] !== null ? trim((string)($row[$cols['notes']] ?? '')) : '';

                if ($ingredientName === '' && $qty <= 0) continue;

                $ingredientNorm = ri_norm($ingredientName);
                if ($ingredientNorm === '' || preg_match('/total|gross|net|selling|price|margin|food cost|recipe cost|yield|method/', $ingredientNorm)) {
                    continue;
                }

                if ($qty <= 0) {
                    // Do not import zero-quantity lines, but keep them visible if they look like ingredients.
                    continue;
                }

                $ingredientMatch = ri_best_match($ingredientName, $inventoryItems, 'item_name', 66);

                $lines[] = [
                    'row_no' => $r + 1,
                    'ingredient_name' => $ingredientName,
                    'qty_required' => $qty,
                    'unit_hint' => $unit,
                    'wastage_percent' => $waste,
                    'notes' => $note,
                    'matched_item_id' => $ingredientMatch['id'] ?? 0,
                    'matched_item_name' => $ingredientMatch['item_name'] ?? '',
                    'matched_item_code' => $ingredientMatch['item_code'] ?? '',
                    'match_score' => $ingredientMatch['_match_score'] ?? 0,
                ];
            }

            if (!$lines) continue;

            $recipes[] = [
                'sheet_name' => $sheetName,
                'recipe_name' => $recipeName,
                'recipe_type' => $defaultType,
                'menu_item_id' => $menuMatch['id'] ?? 0,
                'menu_item_name' => $menuMatch['item_name'] ?? '',
                'menu_item_price' => $menuMatch['price'] ?? 0,
                'menu_match_score' => $menuMatch['_match_score'] ?? 0,
                'yield_qty' => 1,
                'lines' => $lines,
            ];
        }

        return $recipes;
    }
}
if (!function_exists('ri_ensure_schema')) {
    function ri_ensure_schema(PDO $pdo): void {
        $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_recipes (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            restaurant_menu_item_id INT UNSIGNED NULL,
            recipe_item_id INT UNSIGNED NULL,
            recipe_name VARCHAR(180) NOT NULL,
            recipe_type ENUM('food','cocktail','mocktail','modifier','prep','other') NOT NULL DEFAULT 'food',
            recipe_category_id INT UNSIGNED NULL,
            yield_qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
            default_store_id INT UNSIGNED NULL,
            cost_per_portion DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            selling_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            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,
            INDEX idx_inventory_recipe_menu_item (restaurant_menu_item_id),
            INDEX idx_inventory_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");

        if (ri_table_exists($pdo, 'inventory_recipes')) {
            ri_add_column($pdo, 'inventory_recipes', 'restaurant_menu_item_id', "restaurant_menu_item_id INT UNSIGNED NULL AFTER id");
            ri_add_column($pdo, 'inventory_recipes', 'recipe_type', "recipe_type ENUM('food','cocktail','mocktail','modifier','prep','other') NOT NULL DEFAULT 'food' AFTER recipe_name");
            ri_add_column($pdo, 'inventory_recipes', 'recipe_category_id', "recipe_category_id INT UNSIGNED NULL AFTER recipe_type");
            ri_add_column($pdo, 'inventory_recipes', 'cost_per_portion', "cost_per_portion DECIMAL(14,4) NOT NULL DEFAULT 0.0000 AFTER default_store_id");
            ri_add_column($pdo, 'inventory_recipes', 'selling_price', "selling_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000 AFTER cost_per_portion");
        }

        $pdo->exec("CREATE TABLE IF NOT EXISTS recipe_import_batches (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            original_filename VARCHAR(255) NOT NULL,
            import_type VARCHAR(40) NOT NULL DEFAULT 'food',
            parsed_json LONGTEXT NOT NULL,
            created_by INT UNSIGNED NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }
}
if (!function_exists('ri_recipe_total_cost')) {
    function ri_recipe_total_cost(PDO $pdo, array $lineItemIds, array $qtys, array $wastes): float {
        $total = 0.0;

        foreach ($lineItemIds as $idx => $itemId) {
            $itemId = (int)$itemId;
            $qty = (float)($qtys[$idx] ?? 0);
            $waste = (float)($wastes[$idx] ?? 0);

            if ($itemId <= 0 || $qty <= 0) continue;

            $stmt = $pdo->prepare("SELECT avg_cost, cost_price FROM inventory_items WHERE id = :id LIMIT 1");
            $stmt->execute([':id' => $itemId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
            $cost = (float)(($row['avg_cost'] ?? 0) ?: ($row['cost_price'] ?? 0));

            $total += ($qty * (1 + ($waste / 100))) * $cost;
        }

        return round($total, 4);
    }
}

$errors = [];
$preview = null;
$batch = null;

try {
    ri_ensure_schema($pdo);
} catch (Throwable $e) {
    $errors[] = 'Could not prepare recipe import tables: ' . $e->getMessage();
}

$menuItems = [];
$inventoryItems = [];
$stores = [];

try {
    if (!ri_table_exists($pdo, 'restaurant_menu_items')) {
        throw new RuntimeException('restaurant_menu_items table was not found. Please open planner/menu setup first.');
    }

    $menuItems = $pdo->query("
        SELECT
            mi.id,
            mi.item_name,
            mi.item_code,
            mi.price,
            mi.tax_rate,
            mi.category_id,
            mi.inventory_item_id,
            COALESCE(c.category_name, '') AS category_name
        FROM restaurant_menu_items mi
        LEFT JOIN restaurant_menu_categories c ON c.id = mi.category_id
        WHERE mi.is_active = 1
        ORDER BY c.category_name ASC, mi.item_name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $inventoryItems = $pdo->query("
        SELECT
            ii.id,
            ii.item_code,
            ii.item_name,
            ii.avg_cost,
            ii.cost_price,
            ii.unit_id,
            COALESCE(u.unit_code, '') AS unit_code
        FROM inventory_items ii
        LEFT JOIN inventory_units u ON u.id = ii.unit_id
        WHERE ii.is_active = 1
          AND ii.is_stock_item = 1
        ORDER BY ii.item_name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    if (ri_table_exists($pdo, 'store_locations')) {
        $stores = $pdo->query("SELECT id, location_name FROM store_locations WHERE is_active = 1 ORDER BY location_name")->fetchAll(PDO::FETCH_ASSOC);
    }
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}

if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'POST') {
    try {
        if (function_exists('verify_csrf')) verify_csrf();

        $action = (string)($_POST['action'] ?? '');

        if ($action === 'preview_import') {
            if (empty($_FILES['recipe_file']) || ($_FILES['recipe_file']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
                throw new RuntimeException('Please upload a recipe Excel or CSV file.');
            }

            $type = strtolower(trim((string)($_POST['import_type'] ?? 'food')));
            if (!in_array($type, ['food','cocktail','mocktail','modifier','prep','other'], true)) $type = 'food';

            $tmp = (string)$_FILES['recipe_file']['tmp_name'];
            $originalName = (string)$_FILES['recipe_file']['name'];
            $ext = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));

            if (in_array($ext, ['xlsx','xlsm'], true)) {
                $sheets = ri_parse_xlsx($tmp);
            } elseif (in_array($ext, ['csv','txt'], true)) {
                $sheets = ri_parse_csv($tmp);
            } else {
                throw new RuntimeException('Unsupported file type. Please upload .xlsx or .csv.');
            }

            $recipes = ri_parse_recipe_sheets($sheets, $menuItems, $inventoryItems, $type);

            if (!$recipes) {
                throw new RuntimeException('No recipe lines could be detected. Check that your file has ingredient and quantity columns.');
            }

            $stmt = $pdo->prepare("
                INSERT INTO recipe_import_batches
                (original_filename, import_type, parsed_json, created_by)
                VALUES
                (:filename, :type, :json, :created_by)
            ");
            $stmt->execute([
                ':filename' => $originalName,
                ':type' => $type,
                ':json' => json_encode($recipes, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                ':created_by' => ri_current_user_id(),
            ]);

            $batchId = (int)$pdo->lastInsertId();

            ri_flash('success', 'File parsed successfully. Review matches before importing.');
            header('Location: recipe_import.php?batch_id=' . $batchId);
            exit;
        }

        if ($action === 'import_batch') {
            $batchId = (int)($_POST['batch_id'] ?? 0);
            if ($batchId <= 0) throw new RuntimeException('Invalid import batch.');

            $stmt = $pdo->prepare("SELECT * FROM recipe_import_batches WHERE id = :id LIMIT 1");
            $stmt->execute([':id' => $batchId]);
            $batch = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$batch) throw new RuntimeException('Import batch was not found.');

            $recipes = json_decode((string)$batch['parsed_json'], true);
            if (!is_array($recipes)) throw new RuntimeException('Import batch could not be read.');

            $selected = $_POST['recipe_selected'] ?? [];
            $menuIds = $_POST['menu_item_id'] ?? [];
            $recipeNames = $_POST['recipe_name'] ?? [];
            $recipeTypes = $_POST['recipe_type'] ?? [];
            $yieldQtys = $_POST['yield_qty'] ?? [];
            $defaultStoreIds = $_POST['default_store_id'] ?? [];
            $lineItemIds = $_POST['ingredient_item_id'] ?? [];
            $lineQtys = $_POST['qty_required'] ?? [];
            $lineWastes = $_POST['wastage_percent'] ?? [];
            $lineNotes = $_POST['line_notes'] ?? [];

            $imported = 0;
            $skipped = 0;

            $pdo->beginTransaction();

            foreach ($recipes as $rIdx => $recipe) {
                if (empty($selected[$rIdx])) {
                    $skipped++;
                    continue;
                }

                $menuItemId = (int)($menuIds[$rIdx] ?? 0);
                $recipeName = trim((string)($recipeNames[$rIdx] ?? ($recipe['recipe_name'] ?? '')));
                $recipeType = strtolower(trim((string)($recipeTypes[$rIdx] ?? ($recipe['recipe_type'] ?? 'food'))));
                $yieldQty = (float)($yieldQtys[$rIdx] ?? 1);
                $defaultStoreId = (int)($defaultStoreIds[$rIdx] ?? 0);

                if ($menuItemId <= 0) throw new RuntimeException('Recipe "' . $recipeName . '" has no matched restaurant menu item.');
                if ($recipeName === '') throw new RuntimeException('One selected recipe has no name.');
                if ($yieldQty <= 0) $yieldQty = 1;
                if (!in_array($recipeType, ['food','cocktail','mocktail','modifier','prep','other'], true)) $recipeType = 'food';

                $stmtMenu = $pdo->prepare("SELECT id, item_name, price, category_id FROM restaurant_menu_items WHERE id = :id LIMIT 1");
                $stmtMenu->execute([':id' => $menuItemId]);
                $menu = $stmtMenu->fetch(PDO::FETCH_ASSOC);
                if (!$menu) throw new RuntimeException('Selected restaurant menu item was not found for recipe "' . $recipeName . '".');

                $recipeLineItemIds = $lineItemIds[$rIdx] ?? [];
                $recipeLineQtys = $lineQtys[$rIdx] ?? [];
                $recipeLineWastes = $lineWastes[$rIdx] ?? [];
                $recipeLineNotes = $lineNotes[$rIdx] ?? [];

                if (!is_array($recipeLineItemIds)) $recipeLineItemIds = [];
                if (!is_array($recipeLineQtys)) $recipeLineQtys = [];
                if (!is_array($recipeLineWastes)) $recipeLineWastes = [];
                if (!is_array($recipeLineNotes)) $recipeLineNotes = [];

                $cleanLines = [];
                foreach ($recipeLineItemIds as $lineIdx => $itemIdRaw) {
                    $ingredientItemId = (int)$itemIdRaw;
                    $qty = (float)($recipeLineQtys[$lineIdx] ?? 0);
                    $waste = (float)($recipeLineWastes[$lineIdx] ?? 0);
                    $note = trim((string)($recipeLineNotes[$lineIdx] ?? ''));

                    if ($ingredientItemId <= 0 && $qty <= 0) continue;
                    if ($ingredientItemId <= 0) throw new RuntimeException('Recipe "' . $recipeName . '" has an unmatched ingredient line.');
                    if ($qty <= 0) throw new RuntimeException('Recipe "' . $recipeName . '" has an ingredient with zero quantity.');
                    if ($waste < 0) $waste = 0;

                    $cleanLines[] = [
                        'ingredient_item_id' => $ingredientItemId,
                        'qty_required' => $qty,
                        'wastage_percent' => $waste,
                        'notes' => $note !== '' ? $note : null,
                    ];
                }

                if (!$cleanLines) throw new RuntimeException('Recipe "' . $recipeName . '" has no valid ingredients.');

                $cost = ri_recipe_total_cost(
                    $pdo,
                    array_column($cleanLines, 'ingredient_item_id'),
                    array_column($cleanLines, 'qty_required'),
                    array_column($cleanLines, 'wastage_percent')
                );

                $costPerPortion = $yieldQty > 0 ? round($cost / $yieldQty, 4) : $cost;
                $sellingPrice = (float)($menu['price'] ?? 0);

                $stmt = $pdo->prepare("
                    SELECT id
                    FROM inventory_recipes
                    WHERE restaurant_menu_item_id = :menu_item_id
                    LIMIT 1
                ");
                $stmt->execute([':menu_item_id' => $menuItemId]);
                $recipeId = (int)$stmt->fetchColumn();

                if ($recipeId > 0) {
                    $stmt = $pdo->prepare("
                        UPDATE inventory_recipes
                        SET recipe_name = :recipe_name,
                            recipe_type = :recipe_type,
                            recipe_category_id = :recipe_category_id,
                            yield_qty = :yield_qty,
                            default_store_id = :default_store_id,
                            cost_per_portion = :cost_per_portion,
                            selling_price = :selling_price,
                            is_active = 1
                        WHERE id = :id
                    ");
                    $stmt->execute([
                        ':recipe_name' => $recipeName,
                        ':recipe_type' => $recipeType,
                        ':recipe_category_id' => !empty($menu['category_id']) ? (int)$menu['category_id'] : null,
                        ':yield_qty' => $yieldQty,
                        ':default_store_id' => $defaultStoreId > 0 ? $defaultStoreId : null,
                        ':cost_per_portion' => $costPerPortion,
                        ':selling_price' => $sellingPrice,
                        ':id' => $recipeId,
                    ]);

                    $stmt = $pdo->prepare("DELETE FROM inventory_recipe_lines WHERE recipe_id = :id");
                    $stmt->execute([':id' => $recipeId]);
                } else {
                    $stmt = $pdo->prepare("
                        INSERT INTO inventory_recipes
                        (restaurant_menu_item_id, recipe_item_id, recipe_name, recipe_type, recipe_category_id, yield_qty, default_store_id, cost_per_portion, selling_price, is_active, created_by)
                        VALUES
                        (:restaurant_menu_item_id, NULL, :recipe_name, :recipe_type, :recipe_category_id, :yield_qty, :default_store_id, :cost_per_portion, :selling_price, 1, :created_by)
                    ");
                    $stmt->execute([
                        ':restaurant_menu_item_id' => $menuItemId,
                        ':recipe_name' => $recipeName,
                        ':recipe_type' => $recipeType,
                        ':recipe_category_id' => !empty($menu['category_id']) ? (int)$menu['category_id'] : null,
                        ':yield_qty' => $yieldQty,
                        ':default_store_id' => $defaultStoreId > 0 ? $defaultStoreId : null,
                        ':cost_per_portion' => $costPerPortion,
                        ':selling_price' => $sellingPrice,
                        ':created_by' => ri_current_user_id(),
                    ]);
                    $recipeId = (int)$pdo->lastInsertId();
                }

                $stmtLine = $pdo->prepare("
                    INSERT INTO inventory_recipe_lines
                    (recipe_id, ingredient_item_id, qty_required, wastage_percent, notes)
                    VALUES
                    (:recipe_id, :ingredient_item_id, :qty_required, :wastage_percent, :notes)
                ");

                foreach ($cleanLines as $line) {
                    $stmtLine->execute([
                        ':recipe_id' => $recipeId,
                        ':ingredient_item_id' => $line['ingredient_item_id'],
                        ':qty_required' => $line['qty_required'],
                        ':wastage_percent' => $line['wastage_percent'],
                        ':notes' => $line['notes'],
                    ]);
                }

                $imported++;
            }

            $pdo->commit();

            ri_flash('success', $imported . ' recipe(s) imported/updated. ' . $skipped . ' skipped.');
            header('Location: recipes.php');
            exit;
        }
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        ri_flash('danger', $e->getMessage());
        header('Location: recipe_import.php' . (!empty($_POST['batch_id']) ? '?batch_id=' . (int)$_POST['batch_id'] : ''));
        exit;
    }
}

$batchId = (int)($_GET['batch_id'] ?? 0);
if ($batchId > 0) {
    try {
        $stmt = $pdo->prepare("SELECT * FROM recipe_import_batches WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $batchId]);
        $batch = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($batch) {
            $preview = json_decode((string)$batch['parsed_json'], true);
            if (!is_array($preview)) $preview = null;
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load import batch: ' . $e->getMessage();
    }
}

$flashes = ri_get_flash();

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

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-upload"></i> Restaurant Recipes</div>
            <div class="hero-title mt-2">Recipe Import</div>
            <div class="hero-subtitle mt-2">Import meal and cocktail recipes, match them to Restaurant POS menu items, then link ingredients to stock items.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="recipes.php" class="btn btn-light me-2">
                <i class="bi bi-journal-text me-2"></i>Recipes
            </a>
            <a href="inventory_items.php" class="btn btn-outline-light">
                <i class="bi bi-box-seam me-2"></i>Items
            </a>
        </div>
    </div>
</div>

<?php foreach ($flashes as $flash): ?>
    <div class="alert alert-<?php echo h($flash['type']); ?>"><?php echo h($flash['message']); ?></div>
<?php endforeach; ?>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <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="row g-4">
    <div class="col-xl-4">
        <div class="card panel-card mb-4">
            <div class="card-header">
                <h5 class="mb-0">Upload Recipe File</h5>
<div class="d-grid gap-2">
    <a href="recipe_template_download.php?type=food"
       class="btn btn-outline-success">
        <i class="bi bi-file-earmark-excel"></i>
        Download Food Template
    </a>

    <a href="recipe_template_download.php?type=cocktail"
       class="btn btn-outline-primary">
        <i class="bi bi-cup-straw"></i>
        Download Cocktail Template
    </a>
</div>                
            </div>
            <div class="card-body">
                <form method="post" enctype="multipart/form-data">
                    <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
                    <input type="hidden" name="action" value="preview_import">

                    <div class="mb-3">
                        <label class="form-label">Import Type</label>
                        <select name="import_type" class="form-select">
                            <option value="food">Meal / Food Recipes</option>
                            <option value="cocktail">Cocktail Recipes</option>
                            <option value="mocktail">Mocktail Recipes</option>
                            <option value="modifier">Modifiers / Add-ons</option>
                            <option value="prep">Prep / Batch Recipes</option>
                            <option value="other">Other</option>
                        </select>
                    </div>

                    <div class="mb-3">
                        <label class="form-label">Excel / CSV File</label>
                        <input type="file" name="recipe_file" class="form-control" accept=".xlsx,.xlsm,.csv,.txt" required>
                        <div class="form-text">Works best where each worksheet is one recipe and has Ingredient + Qty columns.</div>
                    </div>

                    <button class="btn btn-primary w-100">
                        <i class="bi bi-search me-2"></i>Preview & Match
                    </button>
                </form>
            </div>
        </div>

        <div class="card panel-card">
            <div class="card-header">
                <h5 class="mb-0">Import Rules</h5>
            </div>
            <div class="card-body small">
                <p class="mb-2"><strong>Sold item:</strong> matched from <code>restaurant_menu_items</code>.</p>
                <p class="mb-2"><strong>Ingredients:</strong> matched from <code>inventory_items</code>.</p>
                <p class="mb-2"><strong>Existing recipe:</strong> updated if the menu item already has a recipe.</p>
                <p class="mb-0"><strong>Stock deduction:</strong> handled later by planner.php when the POS order closes/posts.</p>
            </div>
        </div>
    </div>

    <div class="col-xl-8">
        <?php if (!$preview): ?>
            <div class="card panel-card">
                <div class="card-body text-center text-muted py-5">
                    <i class="bi bi-file-earmark-spreadsheet fs-1"></i>
                    <div class="mt-3 fw-bold">Upload a recipe workbook to preview matches.</div>
                    <div class="small">You can correct menu item and ingredient matches before importing.</div>
                </div>
            </div>
        <?php else: ?>
            <form method="post" onsubmit="return confirm('Import selected recipes and replace existing lines where matched?');">
                <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
                <input type="hidden" name="action" value="import_batch">
                <input type="hidden" name="batch_id" value="<?php echo (int)$batchId; ?>">

                <div class="card panel-card mb-3">
                    <div class="card-header d-flex justify-content-between align-items-center">
                        <div>
                            <h5 class="mb-0">Preview Matches</h5>
                            <div class="small text-muted">
                                <?php echo h($batch['original_filename'] ?? 'Uploaded file'); ?> • <?php echo count($preview); ?> recipe(s)
                            </div>
                        </div>
                        <button class="btn btn-success">
                            <i class="bi bi-check-circle me-2"></i>Import Selected
                        </button>
                    </div>
                </div>

                <?php foreach ($preview as $rIdx => $recipe): ?>
                    <?php
                        $menuMatched = (int)($recipe['menu_item_id'] ?? 0) > 0;
                        $unmatchedLines = 0;
                        foreach (($recipe['lines'] ?? []) as $ln) {
                            if ((int)($ln['matched_item_id'] ?? 0) <= 0) $unmatchedLines++;
                        }
                        $canImport = $menuMatched && $unmatchedLines === 0;
                    ?>
                    <div class="card panel-card mb-4 recipe-preview-card <?php echo $canImport ? '' : 'border-warning'; ?>">
                        <div class="card-header d-flex justify-content-between align-items-start gap-3">
                            <div>
                                <div class="d-flex align-items-center gap-2">
                                    <input type="checkbox"
                                           name="recipe_selected[<?php echo (int)$rIdx; ?>]"
                                           value="1"
                                           <?php echo $canImport ? 'checked' : ''; ?>>
                                    <h5 class="mb-0"><?php echo h($recipe['recipe_name'] ?? 'Recipe'); ?></h5>
                                </div>
                                <div class="small text-muted">
                                    Sheet: <?php echo h($recipe['sheet_name'] ?? ''); ?>
                                    <?php if (!$canImport): ?>
                                        <span class="badge text-bg-warning ms-2">Needs review</span>
                                    <?php else: ?>
                                        <span class="badge text-bg-success ms-2">Ready</span>
                                    <?php endif; ?>
                                </div>
                            </div>
                            <div class="text-end small">
                                <div>Menu match: <strong><?php echo h($recipe['menu_match_score'] ?? 0); ?>%</strong></div>
                                <div>Unmatched ingredients: <strong><?php echo (int)$unmatchedLines; ?></strong></div>
                            </div>
                        </div>

                        <div class="card-body">
                            <div class="row g-3 mb-3">
                                <div class="col-md-5">
                                    <label class="form-label">Restaurant Menu Item</label>
                                    <select name="menu_item_id[<?php echo (int)$rIdx; ?>]" class="form-select" required>
                                        <option value="">Select menu item</option>
                                        <?php foreach ($menuItems as $mi): ?>
                                            <option value="<?php echo (int)$mi['id']; ?>"
                                                <?php echo (int)($recipe['menu_item_id'] ?? 0) === (int)$mi['id'] ? 'selected' : ''; ?>>
                                                <?php echo h($mi['item_name']); ?>
                                                <?php echo $mi['category_name'] ? ' • ' . h($mi['category_name']) : ''; ?>
                                                <?php echo ' • ' . money_fmt($mi['price']); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Recipe Name</label>
                                    <input type="text" name="recipe_name[<?php echo (int)$rIdx; ?>]" class="form-control" value="<?php echo h($recipe['recipe_name'] ?? ''); ?>" required>
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label">Type</label>
                                    <select name="recipe_type[<?php echo (int)$rIdx; ?>]" class="form-select">
                                        <?php foreach (['food'=>'Food','cocktail'=>'Cocktail','mocktail'=>'Mocktail','modifier'=>'Modifier','prep'=>'Prep','other'=>'Other'] as $v => $lbl): ?>
                                            <option value="<?php echo h($v); ?>" <?php echo (($recipe['recipe_type'] ?? 'food') === $v) ? 'selected' : ''; ?>>
                                                <?php echo h($lbl); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label">Yield Qty</label>
                                    <input type="number" step="0.0001" min="0.0001" name="yield_qty[<?php echo (int)$rIdx; ?>]" class="form-control" value="<?php echo h($recipe['yield_qty'] ?? '1'); ?>" required>
                                </div>
                                <div class="col-md-12">
                                    <label class="form-label">Default Stock Store</label>
                                    <select name="default_store_id[<?php echo (int)$rIdx; ?>]" class="form-select">
                                        <option value="0">No default / deduct from system default later</option>
                                        <?php foreach ($stores as $store): ?>
                                            <option value="<?php echo (int)$store['id']; ?>">
                                                <?php echo h($store['location_name']); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                            </div>

                            <div class="table-responsive">
                                <table class="table table-sm table-modern align-middle">
                                    <thead>
                                        <tr>
                                            <th>Imported Ingredient</th>
                                            <th style="width:32%;">Matched Stock Item</th>
                                            <th class="text-end" style="width:110px;">Qty</th>
                                            <th style="width:90px;">Unit Hint</th>
                                            <th class="text-end" style="width:110px;">Waste %</th>
                                            <th>Notes</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                    <?php foreach (($recipe['lines'] ?? []) as $lIdx => $line): ?>
                                        <?php $lineMatched = (int)($line['matched_item_id'] ?? 0) > 0; ?>
                                        <tr class="<?php echo $lineMatched ? '' : 'table-warning'; ?>">
                                            <td>
                                                <strong><?php echo h($line['ingredient_name'] ?? ''); ?></strong>
                                                <div class="small text-muted">Row <?php echo (int)($line['row_no'] ?? 0); ?> • Score <?php echo (int)($line['match_score'] ?? 0); ?>%</div>
                                            </td>
                                            <td>
                                                <select name="ingredient_item_id[<?php echo (int)$rIdx; ?>][<?php echo (int)$lIdx; ?>]" class="form-select form-select-sm" required>
                                                    <option value="">Select stock item</option>
                                                    <?php foreach ($inventoryItems as $ii): ?>
                                                        <option value="<?php echo (int)$ii['id']; ?>"
                                                            <?php echo (int)($line['matched_item_id'] ?? 0) === (int)$ii['id'] ? 'selected' : ''; ?>>
                                                            <?php echo h($ii['item_name']); ?>
                                                            <?php echo $ii['item_code'] ? ' - ' . h($ii['item_code']) : ''; ?>
                                                            <?php echo $ii['unit_code'] ? ' (' . h($ii['unit_code']) . ')' : ''; ?>
                                                        </option>
                                                    <?php endforeach; ?>
                                                </select>
                                            </td>
                                            <td>
                                                <input type="number" step="0.0001" min="0.0001"
                                                       name="qty_required[<?php echo (int)$rIdx; ?>][<?php echo (int)$lIdx; ?>]"
                                                       class="form-control form-control-sm text-end"
                                                       value="<?php echo h($line['qty_required'] ?? '0'); ?>" required>
                                            </td>
                                            <td><?php echo h($line['unit_hint'] ?? ''); ?></td>
                                            <td>
                                                <input type="number" step="0.0001" min="0"
                                                       name="wastage_percent[<?php echo (int)$rIdx; ?>][<?php echo (int)$lIdx; ?>]"
                                                       class="form-control form-control-sm text-end"
                                                       value="<?php echo h($line['wastage_percent'] ?? '0'); ?>">
                                            </td>
                                            <td>
                                                <input type="text"
                                                       name="line_notes[<?php echo (int)$rIdx; ?>][<?php echo (int)$lIdx; ?>]"
                                                       class="form-control form-control-sm"
                                                       value="<?php echo h($line['notes'] ?? ''); ?>">
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                <?php endforeach; ?>

                <div class="sticky-import-bar">
                    <div>
                        <strong>Review all yellow rows before importing.</strong>
                        <div class="small text-muted">Selected recipes will replace existing ingredient lines for the same menu item.</div>
                    </div>
                    <button class="btn btn-success">
                        <i class="bi bi-check-circle me-2"></i>Import Selected
                    </button>
                </div>
            </form>
        <?php endif; ?>
    </div>
</div>

<style>
.recipe-preview-card .card-header{
    background:#fff;
}
.sticky-import-bar{
    position:sticky;
    bottom:0;
    z-index:20;
    background:#fff;
    border:1px solid rgba(15,23,42,.12);
    box-shadow:0 -10px 25px rgba(15,23,42,.08);
    border-radius:16px 16px 0 0;
    padding:14px;
    display:flex;
    justify-content:space-between;
    align-items:center;
    gap:12px;
}
@media(max-width:767px){
    .sticky-import-bar{
        display:block;
    }
    .sticky-import-bar .btn{
        width:100%;
        margin-top:10px;
    }
}
</style>

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