<?php
// product_core_common.php — Product Card Core v2 helpers.

function product_parse_decimal($value): ?float
{
    if ($value === null) return null;
    if (is_float($value) || is_int($value)) return (float)$value;
    $value = trim((string)$value);
    if ($value === '') return null;
    $value = str_replace([' ', "\xc2\xa0"], '', $value);
    if (strpos($value, ',') !== false && strpos($value, '.') === false) {
        $value = str_replace(',', '.', $value);
    } else {
        $value = str_replace(',', '', $value);
    }
    return is_numeric($value) ? (float)$value : null;
}

function product_type_options(): array
{
    try {
        $stmt = db()->query("SELECT category_code, type_label, sku_prefix FROM product_types WHERE is_active=1 ORDER BY sort_order, type_label");
        $rows = $stmt->fetchAll();
        if ($rows) {
            $out = [];
            foreach ($rows as $r) {
                $out[(string)$r['category_code']] = ['letter' => (string)$r['sku_prefix'], 'label' => (string)$r['type_label']];
            }
            return $out;
        }
    } catch (Throwable $e) {
        // SQL patch not installed yet; fall back to hardcoded MVP types.
    }
    return [
        'ring' => ['letter' => 'R', 'label' => 'Yüzük'],
        'pendant' => ['letter' => 'P', 'label' => 'Kolye / Pendant'],
        'earring' => ['letter' => 'E', 'label' => 'Küpe'],
        'bracelet' => ['letter' => 'B', 'label' => 'Bileklik'],
        'other' => ['letter' => 'O', 'label' => 'Diğer'],
    ];
}

function product_type_letter(string $category): string
{
    $opts = product_type_options();
    return $opts[$category]['letter'] ?? 'O';
}

function product_type_label(string $category): string
{
    $opts = product_type_options();
    return $opts[$category]['label'] ?? $category;
}

function product_type_rows(bool $includeInactive = false): array
{
    try {
        $sql = "SELECT * FROM product_types" . ($includeInactive ? "" : " WHERE is_active=1") . " ORDER BY sort_order, type_label";
        return db()->query($sql)->fetchAll();
    } catch (Throwable $e) {
        return [];
    }
}

function product_store_sku_prefix(string $storeCode): string
{
    $storeCode = strtoupper(trim($storeCode));
    $map = [
        'BLG' => 'B',
        'LUW' => 'L',
        'VOQ' => 'V',
        'JOLENE' => 'JO',
        'JOL' => 'JO',
    ];
    if (isset($map[$storeCode])) return $map[$storeCode];
    $letters = preg_replace('/[^A-Z0-9]/', '', $storeCode);
    if ($letters === '') return 'X';
    return mb_substr($letters, 0, min(2, mb_strlen($letters, 'UTF-8')), 'UTF-8');
}

function product_sample_materials(): array
{
    return db()->query("SELECT * FROM product_sample_materials WHERE is_active=1 ORDER BY sort_order, material_name")->fetchAll();
}

function product_sample_material_display(array $material): string
{
    $code = (string)($material['material_code'] ?? '');
    if ($code === '10K_GREEN_GOLD') return '10K';
    if ($code === '14K_YELLOW_GOLD') return '14K';
    if ($code === '18K_YELLOW_GOLD') return '18K';
    if ($code === 'AG_SILVER') return 'AG Silver';
    if ($code === 'BRONZE') return 'Bronze';
    return (string)($material['target_karat'] ?: $material['material_name'] ?: $code);
}

function product_sample_colors(): array
{
    return ['Yellow','White','Rose','Silver','Bronze'];
}

function product_sample_material_by_code(?string $code): ?array
{
    if (!$code) return null;
    $stmt = db()->prepare("SELECT * FROM product_sample_materials WHERE material_code=? AND is_active=1 LIMIT 1");
    $stmt->execute([$code]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function product_sample_stock_options(): array
{
    return [
        'atelier' => 'Atölye / numune dolabında',
        'store' => 'Mağazada / zimmette',
        'scrap' => 'Hurda / eritildi',
        'none' => 'Numune yok / takip edilmiyor',
    ];
}

function product_sample_stock_help(): array
{
    return [
        'atelier' => 'Fiziksel numune atölyede duruyor.',
        'store' => 'Fiziksel numune bir mağazaya teslim/zimmet edildi.',
        'scrap' => 'Numune eritildi, hurdaya ayrıldı veya artık fiziksel ürün olarak yok.',
        'none' => 'Bu kart için fiziksel numune takibi yapılmayacak.',
    ];
}

function product_sample_stock_label(?string $status): string
{
    $opts = product_sample_stock_options();
    return $opts[$status ?: 'atelier'] ?? (string)$status;
}

function product_target_materials(): array
{
    static $targets = null;
    if ($targets !== null) return $targets;
    $targets = [];
    $stmt = db()->query("SELECT * FROM product_sample_materials WHERE material_code IN ('10K_GREEN_GOLD','14K_YELLOW_GOLD','18K_YELLOW_GOLD')");
    foreach ($stmt->fetchAll() as $r) {
        $targets[$r['material_code']] = $r;
    }
    return $targets;
}

function product_calculate_grams(?string $sampleMaterialCode, ?float $sampleGram): array
{
    $empty = ['gram_10k'=>null, 'gram_14k'=>null, 'gram_18k'=>null, 'sample_density'=>null];
    if (!$sampleMaterialCode || $sampleGram === null || $sampleGram <= 0) return $empty;
    $sample = product_sample_material_by_code($sampleMaterialCode);
    if (!$sample || (float)$sample['density'] <= 0) return $empty;
    $density = (float)$sample['density'];
    $targets = product_target_materials();
    $calc = $empty;
    $calc['sample_density'] = $density;
    if (isset($targets['10K_GREEN_GOLD'])) $calc['gram_10k'] = round($sampleGram * ((float)$targets['10K_GREEN_GOLD']['density']) / $density, 3);
    if (isset($targets['14K_YELLOW_GOLD'])) $calc['gram_14k'] = round($sampleGram * ((float)$targets['14K_YELLOW_GOLD']['density']) / $density, 3);
    if (isset($targets['18K_YELLOW_GOLD'])) $calc['gram_18k'] = round($sampleGram * ((float)$targets['18K_YELLOW_GOLD']['density']) / $density, 3);
    return $calc;
}

function product_next_sequence(string $category): int
{
    $stmt = db()->prepare("SELECT COALESCE(MAX(sequence_number),0) FROM products WHERE category=?");
    $stmt->execute([$category]);
    return ((int)$stmt->fetchColumn()) + 1;
}

function product_master_code(string $category, int $sequence): string
{
    return product_type_letter($category) . str_pad((string)$sequence, 4, '0', STR_PAD_LEFT);
}

function product_find_by_id(int $productId): ?array
{
    $stmt = db()->prepare("\n        SELECT p.*, s.short_name, s.width_mm, s.length_mm, s.height_mm, s.band_width_mm,\n               s.gram_10k, s.gram_14k, s.gram_18k, s.source_url,\n               s.sample_material_code, sm.material_name AS sample_material_name, sm.density AS sample_density,\n               s.sample_karat, s.sample_color, s.sample_actual_gram, s.sample_stock_status,\n               s.sample_owner_store_id, st.store_code AS sample_owner_store_code, st.store_name AS sample_owner_store_name,\n               s.sample_charge_to_store, s.sample_note, s.design_note\n        FROM products p\n        LEFT JOIN product_core_specs s ON s.product_id=p.id\n        LEFT JOIN product_sample_materials sm ON sm.material_code=s.sample_material_code\n        LEFT JOIN stores st ON st.id=s.sample_owner_store_id\n        WHERE p.id=?\n        LIMIT 1\n    ");
    $stmt->execute([$productId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function product_ensure_specs(int $productId): void
{
    $stmt = db()->prepare("INSERT IGNORE INTO product_core_specs (product_id) VALUES (?)");
    $stmt->execute([$productId]);
}

function product_images(int $productId): array
{
    $stmt = db()->prepare("SELECT * FROM product_images WHERE product_id=? ORDER BY is_primary DESC, sort_order ASC, id ASC");
    $stmt->execute([$productId]);
    return $stmt->fetchAll();
}

function product_primary_image(int $productId): ?array
{
    $stmt = db()->prepare("SELECT * FROM product_images WHERE product_id=? ORDER BY is_primary DESC, sort_order ASC, id ASC LIMIT 1");
    $stmt->execute([$productId]);
    $row = $stmt->fetch();
    return $row ?: null;
}


function product_image_max_bytes(): int
{
    // Paylaşımlı hostinglerde büyük görsel POST/503 hatasına yol açabiliyor.
    // Önce küçük güvenli limit ile başlıyoruz; gerekirse sonra yükseltiriz.
    return 2 * 1024 * 1024;
}

function product_image_max_mb_label(): string
{
    return '2 MB';
}

function product_handle_image_upload(int $productId, string $masterCode, string $productName, array $file, ?int $userId): ?int
{
    if (empty($file) || (int)($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return null;
    }
    $err = (int)($file['error'] ?? UPLOAD_ERR_OK);
    if ($err !== UPLOAD_ERR_OK) {
        if ($err === UPLOAD_ERR_INI_SIZE || $err === UPLOAD_ERR_FORM_SIZE) {
            throw new RuntimeException('Fotoğraf çok büyük. Lütfen '.product_image_max_mb_label().' altında bir görsel yükleyin.');
        }
        throw new RuntimeException('Fotoğraf yüklenemedi. Hata kodu: '.$err);
    }
    $size = (int)($file['size'] ?? 0);
    if ($size <= 0 || empty($file['tmp_name']) || !is_uploaded_file($file['tmp_name'])) {
        return null;
    }
    if ($size > product_image_max_bytes()) {
        throw new RuntimeException('Fotoğraf çok büyük. Lütfen '.product_image_max_mb_label().' altında bir görsel yükleyin.');
    }
    $orig = (string)($file['name'] ?? 'image');
    $ext = strtolower(pathinfo($orig, PATHINFO_EXTENSION));
    if (!in_array($ext, ['jpg','jpeg','png','webp','gif'], true)) {
        throw new RuntimeException('Sadece jpg, png, webp veya gif yüklenebilir.');
    }
    $dir = __DIR__ . '/uploads/products';
    if (!is_dir($dir) && !mkdir($dir, 0755, true)) {
        throw new RuntimeException('Ürün fotoğraf klasörü oluşturulamadı.');
    }
    if (!is_writable($dir)) {
        throw new RuntimeException('Ürün fotoğraf klasörüne yazılamıyor.');
    }
    $safeCode = preg_replace('/[^A-Z0-9_-]/i', '_', $masterCode);
    $filename = $safeCode . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(3)) . '.' . $ext;
    $dest = $dir . '/' . $filename;
    if (!move_uploaded_file($file['tmp_name'], $dest)) {
        throw new RuntimeException('Fotoğraf yüklenemedi.');
    }
    $path = 'uploads/products/' . $filename;
    $isPrimary = product_primary_image($productId) ? 0 : 1;
    $stmt = db()->prepare("INSERT INTO product_images (product_id, file_path, original_filename, image_alt, is_primary, uploaded_by) VALUES (?,?,?,?,?,?)");
    $stmt->execute([$productId, $path, $orig, $masterCode . ' ' . $productName, $isPrimary, $userId]);
    return (int)db()->lastInsertId();
}

function product_stone_slots(int $productId, bool $activeOnly = true): array
{
    $sql = "\n        SELECT sl.*, cu.cut_name, cu.cut_code, sz.size_label, sz.size_code, ref.carat_each\n        FROM product_stone_slots sl\n        JOIN stone_cuts cu ON cu.id=sl.cut_id\n        JOIN stone_sizes sz ON sz.id=sl.size_id\n        LEFT JOIN stone_carat_references ref ON ref.cut_id=sl.cut_id AND ref.size_id=sl.size_id AND ref.is_active=1\n        WHERE sl.product_id=?\n    ";
    if ($activeOnly) $sql .= " AND sl.is_active=1";
    $sql .= " ORDER BY sl.slot_no ASC, sl.id ASC";
    $stmt = db()->prepare($sql);
    $stmt->execute([$productId]);
    return $stmt->fetchAll();
}

function product_active_slot_count(int $productId): int
{
    $stmt = db()->prepare("SELECT COUNT(*) FROM product_stone_slots WHERE product_id=? AND is_active=1");
    $stmt->execute([$productId]);
    return (int)$stmt->fetchColumn();
}

function product_active_recipe_exists(int $productId, string $recipeCode): bool
{
    $stmt = db()->prepare("SELECT COUNT(*) FROM product_recipes WHERE product_id=? AND recipe_code=? AND is_active=1");
    $stmt->execute([$productId, strtoupper($recipeCode)]);
    return (int)$stmt->fetchColumn() > 0;
}

function product_recipes(int $productId): array
{
    $stmt = db()->prepare("\n        SELECT r.*,\n               COUNT(DISTINCT rs.id) AS stone_line_count,\n               COALESCE(SUM(rs.qty_per_product),0) AS total_stone_qty\n        FROM product_recipes r\n        LEFT JOIN product_recipe_stones rs ON rs.recipe_id=r.id AND rs.is_active=1\n        WHERE r.product_id=? AND r.is_active=1\n        GROUP BY r.id\n        ORDER BY r.recipe_code ASC\n    ");
    $stmt->execute([$productId]);
    return $stmt->fetchAll();
}

function product_recipe_detail(int $recipeId): ?array
{
    $stmt = db()->prepare("\n        SELECT r.*, p.master_code, p.product_name, p.category\n        FROM product_recipes r\n        JOIN products p ON p.id=r.product_id\n        WHERE r.id=?\n        LIMIT 1\n    ");
    $stmt->execute([$recipeId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function product_recipe_stones(int $recipeId): array
{
    $stmt = db()->prepare("\n        SELECT rs.*, sl.slot_no, sl.qty_per_product AS slot_qty, cu.cut_name, sz.size_label, st.stone_sku, st.species_id, st.color_id, sp.species_name, co.color_name\n        FROM product_recipe_stones rs\n        JOIN product_stone_slots sl ON sl.id=rs.product_stone_slot_id\n        JOIN stone_cuts cu ON cu.id=sl.cut_id\n        JOIN stone_sizes sz ON sz.id=sl.size_id\n        JOIN stones st ON st.id=rs.stone_id\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_colors co ON co.id=st.color_id\n        WHERE rs.recipe_id=? AND rs.is_active=1\n        ORDER BY sl.slot_no ASC, rs.id ASC\n    ");
    $stmt->execute([$recipeId]);
    return $stmt->fetchAll();
}

function product_store_skus(int $productId): array
{
    $stmt = db()->prepare("\n        SELECT ps.*, st.store_code, st.store_name, r.recipe_code, r.recipe_name\n        FROM product_store_recipe_skus ps\n        JOIN stores st ON st.id=ps.store_id\n        JOIN product_recipes r ON r.id=ps.recipe_id\n        WHERE ps.product_id=?\n        ORDER BY st.store_code, r.recipe_code\n    ");
    $stmt->execute([$productId]);
    return $stmt->fetchAll();
}

function product_stores(): array
{
    return db()->query("SELECT * FROM stores WHERE is_active=1 ORDER BY store_code")->fetchAll();
}


function product_store_usage(int $productId): array
{
    $stmt = db()->prepare("SELECT psu.*, st.store_code, st.store_name FROM product_store_usage psu JOIN stores st ON st.id=psu.store_id WHERE psu.product_id=? ORDER BY st.store_code");
    $stmt->execute([$productId]);
    return $stmt->fetchAll();
}

function product_store_usage_ids(int $productId): array
{
    return array_map('intval', array_column(product_store_usage($productId), 'store_id'));
}

function product_sample_charge_stores(int $productId): array
{
    $stmt = db()->prepare("SELECT pcs.*, st.store_code, st.store_name FROM product_sample_charge_stores pcs JOIN stores st ON st.id=pcs.store_id WHERE pcs.product_id=? ORDER BY st.store_code");
    $stmt->execute([$productId]);
    return $stmt->fetchAll();
}

function product_sample_charge_store_ids(int $productId): array
{
    return array_map('intval', array_column(product_sample_charge_stores($productId), 'store_id'));
}

function product_replace_store_usage(PDO $pdo, int $productId, array $storeIds, ?int $userId = null): void
{
    $ids = [];
    foreach ($storeIds as $v) { $v = (int)$v; if ($v > 0) $ids[$v] = $v; }
    $ids = array_values($ids);
    $pdo->prepare("DELETE FROM product_store_usage WHERE product_id=?")->execute([$productId]);
    if (!$ids) return;
    $stmt = $pdo->prepare("INSERT INTO product_store_usage (product_id, store_id, added_by) VALUES (?,?,?)");
    foreach ($ids as $sid) $stmt->execute([$productId, $sid, $userId]);
}

function product_replace_sample_charge_stores(PDO $pdo, int $productId, array $storeIds): void
{
    $ids = [];
    foreach ($storeIds as $v) { $v = (int)$v; if ($v > 0) $ids[$v] = $v; }
    $ids = array_values($ids);
    $pdo->prepare("DELETE FROM product_sample_charge_stores WHERE product_id=?")->execute([$productId]);
    if (!$ids) return;
    $stmt = $pdo->prepare("INSERT INTO product_sample_charge_stores (product_id, store_id) VALUES (?,?)");
    foreach ($ids as $sid) $stmt->execute([$productId, $sid]);
}

function product_store_codes_summary(array $rows): string
{
    if (!$rows) return '-';
    $codes = [];
    foreach ($rows as $r) { $codes[] = (string)$r['store_code']; }
    return implode(', ', $codes);
}

function product_stone_options(): array
{
    return [
        'species' => db()->query("SELECT * FROM stone_species WHERE is_active=1 ORDER BY species_name")->fetchAll(),
        'cuts' => db()->query("SELECT * FROM stone_cuts WHERE is_active=1 ORDER BY cut_name")->fetchAll(),
        'colors' => db()->query("SELECT * FROM stone_colors WHERE is_active=1 ORDER BY color_name")->fetchAll(),
        'sizes' => db()->query("SELECT ss.*, sc.cut_name FROM stone_sizes ss JOIN stone_cuts sc ON sc.id=ss.cut_id WHERE ss.is_active=1 ORDER BY sc.cut_name, COALESCE(ss.diameter_mm, ss.length_mm), ss.width_mm")->fetchAll(),
    ];
}

function product_next_stone_slot_no(int $productId): int
{
    $stmt = db()->prepare("SELECT COALESCE(MAX(slot_no),0) FROM product_stone_slots WHERE product_id=?");
    $stmt->execute([$productId]);
    return ((int)$stmt->fetchColumn()) + 1;
}

function product_next_stone_recipe_code(int $productId): string
{
    $stmt = db()->prepare("SELECT recipe_code FROM product_recipes WHERE product_id=? AND recipe_code REGEXP '^V[0-9][0-9]$' AND recipe_code <> 'V00' ORDER BY recipe_code DESC LIMIT 1");
    $stmt->execute([$productId]);
    $last = (string)($stmt->fetchColumn() ?: 'V00');
    $n = (int)substr($last, 1);
    if ($n < 1) $n = 1; else $n++;
    return 'V' . str_pad((string)$n, 2, '0', STR_PAD_LEFT);
}

function product_create_or_update_recipe(int $productId, string $recipeCode, string $recipeName, bool $stoneFree, int $userId): int
{
    $recipeCode = strtoupper(trim($recipeCode));
    if (!preg_match('/^V[0-9]{2}$/', $recipeCode)) {
        throw new RuntimeException('Reçete kodu V00, V01 gibi olmalı.');
    }
    $slotCount = product_active_slot_count($productId);
    if ($recipeCode === 'V00' && $slotCount > 0) {
        throw new RuntimeException('Bu üründe taş slotu var. Taşlı ürünlerde V00 kullanılamaz; V01/V02 kullanılır. Taşsız satış istenirse ayrı ürün kartı açılmalı.');
    }
    if ($recipeCode !== 'V00' && $slotCount <= 0) {
        throw new RuntimeException('Bu üründe taş slotu yok. Taşsız ürünlerde sadece V00 kullanılır.');
    }
    if ($recipeCode === 'V00') $stoneFree = true;
    if ($recipeCode !== 'V00') $stoneFree = false;

    $stmt = db()->prepare("SELECT id FROM product_recipes WHERE product_id=? AND recipe_code=? LIMIT 1");
    $stmt->execute([$productId, $recipeCode]);
    $id = (int)$stmt->fetchColumn();
    if ($id > 0) {
        $stmt = db()->prepare("UPDATE product_recipes SET recipe_name=?, is_stone_free=?, is_active=1, updated_at=NOW() WHERE id=?");
        $stmt->execute([$recipeName, $stoneFree ? 1 : 0, $id]);
        return $id;
    }
    $isDefault = ($recipeCode === 'V00' || $recipeCode === 'V01') ? 1 : 0;
    $stmt = db()->prepare("INSERT INTO product_recipes (product_id, recipe_code, recipe_name, is_stone_free, is_default, created_by) VALUES (?,?,?,?,?,?)");
    $stmt->execute([$productId, $recipeCode, $recipeName, $stoneFree ? 1 : 0, $isDefault, $userId]);
    return (int)db()->lastInsertId();
}

function product_lookup_stone_for_slot(int $speciesId, int $slotCutId, int $slotSizeId, int $colorId): ?array
{
    $stmt = db()->prepare("\n        SELECT st.*, sp.species_name, co.color_name, cu.cut_name, sz.size_label\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_colors co ON co.id=st.color_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes sz ON sz.id=st.size_id\n        WHERE st.species_id=? AND st.cut_id=? AND st.size_id=? AND st.color_id=? AND st.is_active=1\n        LIMIT 1\n    ");
    $stmt->execute([$speciesId, $slotCutId, $slotSizeId, $colorId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function product_assign_recipe_to_store(int $productId, int $recipeId, int $storeId): string
{
    $pdo = db();
    $product = product_find_by_id($productId);
    $recipe = product_recipe_detail($recipeId);
    if (!$product || !$recipe) throw new RuntimeException('Ürün veya reçete bulunamadı.');
    $stmt = $pdo->prepare("SELECT * FROM stores WHERE id=? LIMIT 1");
    $stmt->execute([$storeId]);
    $store = $stmt->fetch();
    if (!$store) throw new RuntimeException('Mağaza bulunamadı.');

    $prefix = product_store_sku_prefix((string)$store['store_code']);
    $salesSku = $prefix . $product['master_code'] . $recipe['recipe_code'];

    $stmt = $pdo->prepare("INSERT INTO product_store_recipe_skus (product_id, recipe_id, store_id, sales_sku) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE is_active=1, sales_sku=VALUES(sales_sku), updated_at=NOW()");
    $stmt->execute([$productId, $recipeId, $storeId, $salesSku]);

    $categoryLetter = product_type_letter((string)$product['category']);
    $sequencePart = preg_replace('/^[A-Z]+/', '', (string)$product['master_code']);
    $stmt = $pdo->prepare("INSERT INTO product_store_skus (product_id, store_id, sku, sku_prefix, category_letter, sequence_part, suffix_code, is_primary, is_active) VALUES (?,?,?,?,?,?,?,0,1) ON DUPLICATE KEY UPDATE is_active=1, suffix_code=VALUES(suffix_code)");
    $stmt->execute([$productId, $storeId, $salesSku, $prefix, $categoryLetter, $sequencePart, $recipe['recipe_code']]);
    return $salesSku;
}


function product_store_recipe_sku_order_count(string $salesSku): int
{
    $salesSku = trim($salesSku);
    if ($salesSku === '') return 0;
    try {
        $stmt = db()->prepare("SELECT COUNT(*) FROM order_items oi LEFT JOIN product_store_skus pss ON pss.id=oi.product_store_sku_id WHERE oi.sku=? OR pss.sku=?");
        $stmt->execute([$salesSku, $salesSku]);
        return (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        return 0;
    }
}

function product_recipe_store_sku_count(int $recipeId): int
{
    $stmt = db()->prepare("SELECT COUNT(*) FROM product_store_recipe_skus WHERE recipe_id=?");
    $stmt->execute([$recipeId]);
    return (int)$stmt->fetchColumn();
}

function product_recipe_order_usage_count(int $recipeId): int
{
    try {
        $stmt = db()->prepare("\n            SELECT COUNT(*)\n            FROM order_items oi\n            JOIN product_store_recipe_skus ps ON ps.sales_sku=oi.sku\n            WHERE ps.recipe_id=?\n        ");
        $stmt->execute([$recipeId]);
        return (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        return 0;
    }
}

function product_delete_unused_store_recipe_sku(int $storeRecipeSkuId): string
{
    $pdo = db();
    $stmt = $pdo->prepare("SELECT * FROM product_store_recipe_skus WHERE id=? LIMIT 1");
    $stmt->execute([$storeRecipeSkuId]);
    $row = $stmt->fetch();
    if (!$row) throw new RuntimeException('Mağaza SKU kaydı bulunamadı.');
    $sku = (string)$row['sales_sku'];
    $orderCount = product_store_recipe_sku_order_count($sku);
    if ($orderCount > 0) {
        throw new RuntimeException('Bu SKU sipariş/CSV/üretim tarafında kullanılmış görünüyor. Silinemez; pasife alma/düzeltme akışı gerekir.');
    }
    $pdo->beginTransaction();
    $pdo->prepare("DELETE FROM product_store_recipe_skus WHERE id=?")->execute([$storeRecipeSkuId]);
    $pdo->prepare("DELETE FROM product_store_skus WHERE sku=?")->execute([$sku]);
    $pdo->commit();
    return $sku;
}

function product_delete_unused_recipe(int $productId, int $recipeId): string
{
    $pdo = db();
    $recipe = product_recipe_detail($recipeId);
    if (!$recipe || (int)$recipe['product_id'] !== $productId) throw new RuntimeException('Reçete bulunamadı.');
    $skuCount = product_recipe_store_sku_count($recipeId);
    if ($skuCount > 0) {
        throw new RuntimeException('Bu reçeteye bağlı mağaza SKU var. Önce Mağaza SKU bölümünden kullanılmamış SKU kayıtlarını silmelisin.');
    }
    $orderCount = product_recipe_order_usage_count($recipeId);
    if ($orderCount > 0) {
        throw new RuntimeException('Bu reçete siparişte kullanılmış görünüyor. Silinemez; ileride pasife alma/düzeltme akışı kullanılır.');
    }
    $code = (string)$recipe['recipe_code'];
    $pdo->beginTransaction();
    $pdo->prepare("DELETE FROM product_recipe_stones WHERE recipe_id=?")->execute([$recipeId]);
    $pdo->prepare("DELETE FROM product_recipes WHERE id=? AND product_id=?")->execute([$recipeId, $productId]);
    $pdo->commit();
    return $code;
}

function product_update_existing_recipe(int $productId, int $recipeId, string $recipeName, array $slotSpecies, array $slotColors): string
{
    $recipe = product_recipe_detail($recipeId);
    if (!$recipe || (int)$recipe['product_id'] !== $productId) throw new RuntimeException('Reçete bulunamadı.');
    $recipeName = trim($recipeName);
    if ($recipeName === '') $recipeName = (string)$recipe['recipe_code'] . ' reçete';

    $pdo = db();
    if ((int)$recipe['is_stone_free'] === 1 || (string)$recipe['recipe_code'] === 'V00') {
        $stmt = $pdo->prepare("UPDATE product_recipes SET recipe_name=?, updated_at=NOW() WHERE id=? AND product_id=?");
        $stmt->execute([$recipeName, $recipeId, $productId]);
        return (string)$recipe['recipe_code'];
    }

    $slots = product_stone_slots($productId, true);
    if (!$slots) throw new RuntimeException('Bu ürün için aktif taş slotu yok.');
    $recipeLines = [];
    $missing = [];
    foreach ($slots as $slot) {
        $sid = (int)$slot['id'];
        $speciesId = (int)($slotSpecies[$sid] ?? 0);
        $colorId = (int)($slotColors[$sid] ?? 0);
        if ($speciesId <= 0 || $colorId <= 0) throw new RuntimeException('Her slot için taş cinsi ve renk seçmelisin.');
        $stone = product_lookup_stone_for_slot($speciesId, (int)$slot['cut_id'], (int)$slot['size_id'], $colorId);
        if (!$stone) {
            $missing[] = '#'.$slot['slot_no'].' '.$slot['cut_name'].' '.$slot['size_label'];
            continue;
        }
        $recipeLines[] = ['slot'=>$slot, 'stone'=>$stone];
    }
    if ($missing) throw new RuntimeException('Bazı slotlar için taş kartı bulunamadı: '.implode(', ', $missing));

    $pdo->beginTransaction();
    $pdo->prepare("UPDATE product_recipes SET recipe_name=?, updated_at=NOW() WHERE id=? AND product_id=?")->execute([$recipeName, $recipeId, $productId]);
    $pdo->prepare("DELETE FROM product_recipe_stones WHERE recipe_id=?")->execute([$recipeId]);
    $stmt = $pdo->prepare("INSERT INTO product_recipe_stones (recipe_id, product_stone_slot_id, stone_id, qty_per_product) VALUES (?,?,?,?)");
    foreach ($recipeLines as $line) {
        $stmt->execute([$recipeId, (int)$line['slot']['id'], (int)$line['stone']['id'], (int)$line['slot']['qty_per_product']]);
    }
    $pdo->commit();
    return (string)$recipe['recipe_code'];
}


function product_product_order_usage_count(int $productId): int
{
    try {
        $stmt = db()->prepare("SELECT COUNT(*) FROM order_items WHERE product_id=?");
        $stmt->execute([$productId]);
        $direct = (int)$stmt->fetchColumn();

        $stmt = db()->prepare("SELECT sku FROM product_store_skus WHERE product_id=?");
        $stmt->execute([$productId]);
        $skus = array_values(array_filter(array_map('strval', array_column($stmt->fetchAll(), 'sku'))));
        $skuCount = 0;
        if ($skus) {
            $ph = implode(',', array_fill(0, count($skus), '?'));
            $stmt = db()->prepare("SELECT COUNT(*) FROM order_items WHERE sku IN ($ph)");
            $stmt->execute($skus);
            $skuCount = (int)$stmt->fetchColumn();
        }
        return $direct + $skuCount;
    } catch (Throwable $e) {
        return 0;
    }
}

function product_normalize_master_code_input(string $input, string $category): string
{
    $input = strtoupper(trim($input));
    $input = preg_replace('/[^A-Z0-9]/', '', $input);
    if ($input === '') throw new RuntimeException('Ana SKU boş olamaz.');
    $prefix = strtoupper(product_type_letter($category));
    if (!preg_match('/^'.preg_quote($prefix, '/').'([0-9]{1,6})$/', $input, $m)) {
        throw new RuntimeException('Ana SKU '.$prefix.'0036 gibi olmalı. Mağaza prefix’i burada yazılmaz; BLG için B otomatik eklenir.');
    }
    $n = (int)$m[1];
    if ($n <= 0) throw new RuntimeException('Ana SKU sıra numarası 0 olamaz.');
    return $prefix . str_pad((string)$n, 4, '0', STR_PAD_LEFT);
}

function product_sequence_from_master_code(string $masterCode, string $category): int
{
    $prefix = strtoupper(product_type_letter($category));
    $clean = strtoupper(trim($masterCode));
    $digits = preg_replace('/^'.preg_quote($prefix, '/').'/', '', $clean);
    $digits = preg_replace('/[^0-9]/', '', (string)$digits);
    return (int)$digits;
}

function product_available_master_code_suggestions(string $category, int $limit = 20): array
{
    $prefix = strtoupper(product_type_letter($category));
    $stmt = db()->prepare("SELECT sequence_number, master_code FROM products WHERE category=? AND master_code IS NOT NULL ORDER BY sequence_number ASC, master_code ASC");
    $stmt->execute([$category]);
    $used = [];
    $max = 0;
    foreach ($stmt->fetchAll() as $r) {
        $n = (int)($r['sequence_number'] ?: product_sequence_from_master_code((string)$r['master_code'], $category));
        if ($n > 0) {
            $used[$n] = true;
            if ($n > $max) $max = $n;
        }
    }
    $out = [];
    $scanTo = max($max + $limit + 10, $limit + 10);
    for ($i = 1; $i <= $scanTo && count($out) < $limit; $i++) {
        if (empty($used[$i])) {
            $out[] = $prefix . str_pad((string)$i, 4, '0', STR_PAD_LEFT);
        }
    }
    // En son dolu sıradan sonraki doğal öneriyi görünür tut.
    $next = $prefix . str_pad((string)($max + 1), 4, '0', STR_PAD_LEFT);
    if (!in_array($next, $out, true)) $out[] = $next;
    return array_slice(array_values(array_unique($out)), 0, $limit + 1);
}

function product_change_master_code(int $productId, string $newMasterCodeInput): string
{
    $pdo = db();
    $product = product_find_by_id($productId);
    if (!$product) throw new RuntimeException('Ürün kartı bulunamadı.');
    $oldMaster = (string)$product['master_code'];
    $newMaster = product_normalize_master_code_input($newMasterCodeInput, (string)$product['category']);
    if ($newMaster === $oldMaster) return $newMaster;

    $orderCount = product_product_order_usage_count($productId);
    if ($orderCount > 0) {
        throw new RuntimeException('Bu ürün veya mağaza SKU’ları siparişte kullanılmış. Ana SKU artık değiştirilemez; pasif/yeni ürün akışı gerekir.');
    }

    $stmt = $pdo->prepare("SELECT COUNT(*) FROM products WHERE master_code=? AND id<>?");
    $stmt->execute([$newMaster, $productId]);
    if ((int)$stmt->fetchColumn() > 0) throw new RuntimeException($newMaster.' başka bir ürün kartında kullanılıyor.');

    $stmt = $pdo->prepare("SELECT ps.*, st.store_code, r.recipe_code FROM product_store_recipe_skus ps JOIN stores st ON st.id=ps.store_id JOIN product_recipes r ON r.id=ps.recipe_id WHERE ps.product_id=? ORDER BY ps.id");
    $stmt->execute([$productId]);
    $storeRecipeRows = $stmt->fetchAll();

    $updates = [];
    foreach ($storeRecipeRows as $row) {
        $newSalesSku = product_store_sku_prefix((string)$row['store_code']) . $newMaster . (string)$row['recipe_code'];
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM product_store_recipe_skus WHERE sales_sku=? AND id<>?");
        $stmt->execute([$newSalesSku, (int)$row['id']]);
        if ((int)$stmt->fetchColumn() > 0) throw new RuntimeException($newSalesSku.' başka bir mağaza SKU kaydında kullanılıyor.');
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM product_store_skus WHERE sku=? AND product_id<>?");
        $stmt->execute([$newSalesSku, $productId]);
        if ((int)$stmt->fetchColumn() > 0) throw new RuntimeException($newSalesSku.' eski ürün SKU tablosunda başka bir üründe kullanılıyor.');
        $updates[] = [$row, $newSalesSku];
    }

    $sequence = product_sequence_from_master_code($newMaster, (string)$product['category']);
    $categoryLetter = product_type_letter((string)$product['category']);
    $sequencePart = preg_replace('/^[A-Z]+/', '', $newMaster);

    $pdo->beginTransaction();
    $pdo->prepare("UPDATE products SET master_code=?, sequence_number=?, updated_at=NOW() WHERE id=?")->execute([$newMaster, $sequence, $productId]);
    foreach ($updates as [$row, $newSalesSku]) {
        $oldSku = (string)$row['sales_sku'];
        $pdo->prepare("UPDATE product_store_recipe_skus SET sales_sku=?, updated_at=NOW() WHERE id=?")->execute([$newSalesSku, (int)$row['id']]);
        $pdo->prepare("UPDATE product_store_skus SET sku=?, category_letter=?, sequence_part=? WHERE product_id=? AND store_id=? AND sku=?")->execute([$newSalesSku, $categoryLetter, $sequencePart, $productId, (int)$row['store_id'], $oldSku]);
    }
    $pdo->commit();

    product_audit('product_change_master_code', 'product', $productId, ['master_code'=>$oldMaster], ['master_code'=>$newMaster], 'Ana SKU değiştirildi; bağlı kullanılmamış mağaza SKU’ları yeniden oluşturuldu.');
    return $newMaster;
}

function product_audit(string $actionType, string $entityType, ?int $entityId, $oldData, $newData, string $note = ''): void
{
    try {
        $user = current_user();
        $stmt = db()->prepare("INSERT INTO product_core_audit_logs (actor_user_id, action_type, entity_type, entity_id, old_data, new_data, note) VALUES (?,?,?,?,?,?,?)");
        $stmt->execute([
            $user['id'] ?? null,
            $actionType,
            $entityType,
            $entityId,
            $oldData === null ? null : json_encode($oldData, JSON_UNESCAPED_UNICODE),
            $newData === null ? null : json_encode($newData, JSON_UNESCAPED_UNICODE),
            $note,
        ]);
    } catch (Throwable $e) {
        // Audit must not block work.
    }
}
