<?php
// stone_core_common.php — helper functions for stone core module.

function stone_sku_code_part(string $value): string
{
    $value = trim($value);
    $value = preg_replace('/[^A-Za-z0-9]+/', '', $value);
    return strtoupper($value);
}

function stone_generate_sku(string $speciesCode, string $cutCode, string $sizeCode, string $colorCode): string
{
    return 'ST-' . stone_sku_code_part($speciesCode) . '-' . stone_sku_code_part($cutCode) . '-' . stone_sku_code_part($sizeCode) . '-' . stone_sku_code_part($colorCode);
}

function stone_money(float $amount): string
{
    return number_format($amount, 2, '.', ',');
}

function stone_parse_decimal($value): float
{
    if ($value === null) return 0.0;
    if (is_float($value) || is_int($value)) return (float)$value;
    $value = trim((string)$value);
    if ($value === '') return 0.0;
    $value = str_replace([' ', "\xc2\xa0"], '', $value);
    // Turkish/browser inputs may arrive as 1,45. Accept both 1,45 and 1.45.
    if (strpos($value, ',') !== false && strpos($value, '.') === false) {
        $value = str_replace(',', '.', $value);
    } else {
        $value = str_replace(',', '', $value);
    }
    return is_numeric($value) ? (float)$value : 0.0;
}

function stone_qty_from_carat(float $totalCarat, float $caratEach): int
{
    if ($totalCarat <= 0 || $caratEach <= 0) {
        return 0;
    }
    return (int)floor($totalCarat / $caratEach);
}

function stone_currency_options(): array
{
    return ['USD', 'EUR', 'HAS', 'TL'];
}

function stone_active_options(): array
{
    $pdo = db();
    return [
        'species' => $pdo->query("SELECT * FROM stone_species WHERE is_active=1 ORDER BY species_name")->fetchAll(),
        'cuts' => $pdo->query("SELECT * FROM stone_cuts WHERE is_active=1 ORDER BY cut_name")->fetchAll(),
        'colors' => $pdo->query("SELECT * FROM stone_colors WHERE is_active=1 ORDER BY color_name")->fetchAll(),
        'sizes' => $pdo->query("SELECT ss.*, sc.cut_code, 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(),
        'suppliers' => $pdo->query("SELECT * FROM suppliers WHERE is_active=1 ORDER BY supplier_name")->fetchAll(),
    ];
}

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

function stone_get_by_id(int $id): ?array
{
    $stmt = db()->prepare("\n        SELECT st.*, sp.species_code, sp.species_name, cu.cut_code, cu.cut_name, ss.size_code, ss.size_label, co.color_code, co.color_name\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        WHERE st.id=?\n        LIMIT 1\n    ");
    $stmt->execute([$id]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function stone_get_by_sku(string $sku): ?array
{
    $stmt = db()->prepare("\n        SELECT st.*, sp.species_code, sp.species_name, cu.cut_code, cu.cut_name, ss.size_code, ss.size_label, co.color_code, co.color_name\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        WHERE st.stone_sku=?\n        LIMIT 1\n    ");
    $stmt->execute([$sku]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function stone_get_summary_rows(array $filters = []): array
{
    $sql = "\n        SELECT\n            st.*, sp.species_name, sp.species_code, cu.cut_name, cu.cut_code, ss.size_label, ss.size_code, co.color_name, co.color_code,\n            COALESCE(SUM(m.qty_change), 0) AS on_hand_qty,\n            COALESCE(SUM(m.carat_change), 0) AS on_hand_carat\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        LEFT JOIN stone_stock_movements m ON m.stone_id=st.id\n        WHERE st.is_active=1\n    ";
    $params = [];
    $q = trim((string)($filters['q'] ?? ''));
    if ($q !== '') {
        // Use unique placeholders; repeated named placeholders can fail on some PDO/MySQL settings.
        $sql .= " AND (st.stone_sku LIKE :q1 OR sp.species_name LIKE :q2 OR cu.cut_name LIKE :q3 OR ss.size_label LIKE :q4 OR co.color_name LIKE :q5) ";
        $like = '%' . $q . '%';
        $params['q1'] = $like;
        $params['q2'] = $like;
        $params['q3'] = $like;
        $params['q4'] = $like;
        $params['q5'] = $like;
    }
    foreach (['species_id' => 'st.species_id', 'cut_id' => 'st.cut_id', 'size_id' => 'st.size_id', 'color_id' => 'st.color_id'] as $key => $col) {
        $val = (int)($filters[$key] ?? 0);
        if ($val > 0) {
            $sql .= " AND {$col} = :{$key} ";
            $params[$key] = $val;
        }
    }
    $sql .= " GROUP BY st.id ORDER BY st.stone_sku LIMIT 500";
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function stone_balance_for_stone(int $stoneId): array
{
    $stmt = db()->prepare("SELECT COALESCE(SUM(qty_change),0) qty, COALESCE(SUM(carat_change),0) carat FROM stone_stock_movements WHERE stone_id=?");
    $stmt->execute([$stoneId]);
    $row = $stmt->fetch() ?: ['qty'=>0,'carat'=>0];
    return ['qty'=>(int)$row['qty'], 'carat'=>(float)$row['carat']];
}

function stone_avg_costs_for_stone(int $stoneId): array
{
    $stmt = db()->prepare("\n        SELECT currency, SUM(total_carat) total_carat, SUM(total_cost) total_cost,\n               CASE WHEN SUM(total_carat)>0 THEN SUM(total_cost)/SUM(total_carat) ELSE 0 END avg_per_carat\n        FROM stone_purchase_lots\n        WHERE stone_id=? AND COALESCE(is_voided,0)=0\n        GROUP BY currency\n        ORDER BY currency\n    ");
    $stmt->execute([$stoneId]);
    return $stmt->fetchAll();
}

function stone_create_missing_stones_for_size(int $cutId, int $sizeId): int
{
    $pdo = db();
    $stmt = $pdo->prepare("SELECT c.cut_code, s.size_code, r.carat_each FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id JOIN stone_carat_references r ON r.cut_id=c.id AND r.size_id=s.id WHERE c.id=? AND s.id=? LIMIT 1");
    $stmt->execute([$cutId, $sizeId]);
    $base = $stmt->fetch();
    if (!$base) return 0;

    $sql = "\n        INSERT IGNORE INTO stones (stone_sku, species_id, cut_id, size_id, color_id, carat_each, critical_stock_qty)\n        SELECT CONCAT('ST-', sp.species_code, '-', cu.cut_code, '-', sz.size_code, '-', co.color_code),\n               sp.id, cu.id, sz.id, co.id, ref.carat_each, 0\n        FROM stone_species sp\n        JOIN stone_cuts cu ON cu.id = ?\n        JOIN stone_sizes sz ON sz.id = ?\n        JOIN stone_carat_references ref ON ref.cut_id=cu.id AND ref.size_id=sz.id AND ref.is_active=1\n        JOIN stone_colors co ON co.is_active=1\n        WHERE sp.is_active=1 AND (\n            (sp.species_code IN ('DIA','LD','MO') AND co.color_code='CLR')\n            OR (sp.species_code='ZR')\n            OR (sp.species_code NOT IN ('DIA','LD','MO','ZR') AND sp.species_code = co.color_code)\n        )\n    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$cutId, $sizeId]);
    return $stmt->rowCount();
}

function stone_size_code_from_values(?float $diameter, ?float $length, ?float $width): string
{
    if ($diameter && $diameter > 0) {
        return str_pad((string)round($diameter * 100), 4, '0', STR_PAD_LEFT);
    }
    if ($length && $width && $length > 0 && $width > 0) {
        return str_pad((string)round($length * 100), 4, '0', STR_PAD_LEFT) . 'X' . str_pad((string)round($width * 100), 4, '0', STR_PAD_LEFT);
    }
    return '';
}

function stone_size_label_from_values(?float $diameter, ?float $length, ?float $width): string
{
    if ($diameter && $diameter > 0) {
        return number_format($diameter, 2) . ' mm';
    }
    if ($length && $width && $length > 0 && $width > 0) {
        return number_format($length, 2) . ' × ' . number_format($width, 2) . ' mm';
    }
    return '';
}


function stone_admin_audit(string $actionType, string $entityType, ?int $entityId, $oldData, $newData, string $note = ''): void
{
    try {
        $user = current_user();
        $stmt = db()->prepare("INSERT INTO stone_admin_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 never block operational work.
    }
}

function stone_purchase_lot_full(int $lotId): ?array
{
    $stmt = db()->prepare("\n        SELECT l.*, st.stone_sku, st.species_id, st.cut_id, st.size_id, st.color_id, st.carat_each,\n               sp.species_name, cu.cut_name, ss.size_label, co.color_name, su.supplier_name\n        FROM stone_purchase_lots l\n        JOIN stones st ON st.id=l.stone_id\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        JOIN suppliers su ON su.id=l.supplier_id\n        WHERE l.id=?\n        LIMIT 1\n    ");
    $stmt->execute([$lotId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function stone_insert_purchase_lot_in_tx(PDO $pdo, array $data, int $userId, ?int $correctedFromLotId = null): int
{
    $stone = stone_lookup_by_combo((int)$data['species_id'], (int)$data['cut_id'], (int)$data['size_id'], (int)$data['color_id']);
    if (!$stone) {
        throw new RuntimeException('Bu cins/kesim/ölçü/renk kombinasyonu için taş kartı yok.');
    }
    $totalCarat = stone_parse_decimal($data['total_carat'] ?? 0);
    $pricePerCarat = stone_parse_decimal($data['price_per_carat'] ?? 0);
    if ($totalCarat <= 0 || $pricePerCarat < 0) {
        throw new RuntimeException('Toplam carat sıfırdan büyük olmalı; carat fiyatı negatif olamaz.');
    }
    $qty = stone_qty_from_carat($totalCarat, (float)$stone['carat_each']);
    $totalCost = $totalCarat * $pricePerCarat;
    $currency = $data['currency'] ?? 'USD';
    if (!in_array($currency, stone_currency_options(), true)) $currency = 'USD';
    $supplierId = (int)($data['supplier_id'] ?? 0);
    if ($supplierId <= 0) throw new RuntimeException('Tedarikçi seçilmedi.');
    $purchaseDate = $data['purchase_date'] ?? date('Y-m-d');
    $invoiceNo = trim((string)($data['invoice_no'] ?? ''));
    $note = trim((string)($data['note'] ?? ''));

    $stmt = $pdo->prepare("INSERT INTO stone_purchase_lots (stone_id, supplier_id, purchase_date, total_carat, price_per_carat, total_cost, calculated_qty, currency, invoice_no, note, created_by, corrected_from_lot_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
    $stmt->execute([(int)$stone['id'], $supplierId, $purchaseDate, $totalCarat, $pricePerCarat, $totalCost, $qty, $currency, $invoiceNo, $note, $userId, $correctedFromLotId]);
    $lotId = (int)$pdo->lastInsertId();

    $stmt = $pdo->prepare("INSERT INTO stone_stock_movements (stone_id, purchase_lot_id, movement_type, qty_change, carat_change, unit_cost_per_carat, reference_type, reference_id, note, created_by) VALUES (?,?,?,?,?,?,?,?,?,?)");
    $stmt->execute([(int)$stone['id'], $lotId, 'purchase_in', $qty, $totalCarat, $pricePerCarat, 'stone_purchase_lot', $lotId, 'Taş alımı', $userId]);

    $stmt = $pdo->prepare("INSERT INTO supplier_transactions (supplier_id, transaction_date, transaction_type, debit_amount, credit_amount, currency, reference_type, reference_id, note, created_by) VALUES (?,?,?,?,?,?,?,?,?,?)");
    $stmt->execute([$supplierId, $purchaseDate, 'stone_purchase', $totalCost, 0, $currency, 'stone_purchase_lot', $lotId, 'Taş alımı: '.$stone['stone_sku'], $userId]);

    stone_admin_audit($correctedFromLotId ? 'purchase_correction_new' : 'purchase_create', 'stone_purchase_lot', $lotId, null, ['lot_id'=>$lotId,'stone_sku'=>$stone['stone_sku'],'qty'=>$qty,'total_carat'=>$totalCarat,'total_cost'=>$totalCost], $note);
    return $lotId;
}

function stone_void_purchase_lot_in_tx(PDO $pdo, int $lotId, string $reason, int $userId): void
{
    $stmt = $pdo->prepare("\n        SELECT l.*, st.stone_sku\n        FROM stone_purchase_lots l\n        JOIN stones st ON st.id=l.stone_id\n        WHERE l.id=?\n        FOR UPDATE\n    ");
    $stmt->execute([$lotId]);
    $lot = $stmt->fetch();
    if (!$lot) throw new RuntimeException('Alım kaydı bulunamadı.');
    if ((int)($lot['is_voided'] ?? 0) === 1) throw new RuntimeException('Bu alım kaydı zaten iptal edilmiş.');
    $reason = trim($reason);
    if ($reason === '') throw new RuntimeException('İptal/düzeltme nedeni zorunlu.');

    $stmt = $pdo->prepare("INSERT INTO stone_stock_movements (stone_id, purchase_lot_id, movement_type, qty_change, carat_change, unit_cost_per_carat, reference_type, reference_id, note, created_by) VALUES (?,?,?,?,?,?,?,?,?,?)");
    $stmt->execute([(int)$lot['stone_id'], $lotId, 'purchase_void', -1 * (int)$lot['calculated_qty'], -1 * (float)$lot['total_carat'], (float)$lot['price_per_carat'], 'stone_purchase_lot_void', $lotId, 'Alım iptali/düzeltme: '.$reason, $userId]);

    $stmt = $pdo->prepare("INSERT INTO supplier_transactions (supplier_id, transaction_date, transaction_type, debit_amount, credit_amount, currency, reference_type, reference_id, note, created_by) VALUES (?,?,?,?,?,?,?,?,?,?)");
    $stmt->execute([(int)$lot['supplier_id'], date('Y-m-d'), 'stone_purchase_void', 0, (float)$lot['total_cost'], $lot['currency'], 'stone_purchase_lot_void', $lotId, 'Taş alımı iptali: '.$lot['stone_sku'].' — '.$reason, $userId]);

    $stmt = $pdo->prepare("UPDATE stone_purchase_lots SET is_voided=1, voided_at=NOW(), voided_by=?, void_reason=? WHERE id=?");
    $stmt->execute([$userId, $reason, $lotId]);
    stone_admin_audit('purchase_void', 'stone_purchase_lot', $lotId, $lot, null, $reason);
}

function stone_void_purchase_lot(int $lotId, string $reason, int $userId): void
{
    $pdo = db();
    $pdo->beginTransaction();
    try {
        stone_void_purchase_lot_in_tx($pdo, $lotId, $reason, $userId);
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
}

function stone_db_has_column(string $table, string $column): bool
{
    static $cache = [];
    $key = $table.'.'.$column;
    if (array_key_exists($key, $cache)) return $cache[$key];
    $stmt = db()->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?");
    $stmt->execute([$table, $column]);
    $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    return $cache[$key];
}

function stone_find_or_create_target_stone_in_tx(PDO $pdo, array $sourceStone, int $targetSizeId): array
{
    $stmt = $pdo->prepare("\n        SELECT st.*, sp.species_code, sp.species_name, cu.cut_code, cu.cut_name, ss.size_code, ss.size_label, co.color_code, co.color_name\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        WHERE st.species_id=? AND st.cut_id=? AND st.size_id=? AND st.color_id=?\n        LIMIT 1\n    ");
    $stmt->execute([(int)$sourceStone['species_id'], (int)$sourceStone['cut_id'], $targetSizeId, (int)$sourceStone['color_id']]);
    $target = $stmt->fetch();
    if ($target) return $target;

    $stmt = $pdo->prepare("\n        SELECT cu.cut_code, ss.size_code, ss.size_label, co.color_code, ref.carat_each\n        FROM stone_cuts cu\n        JOIN stone_sizes ss ON ss.cut_id=cu.id AND ss.id=?\n        JOIN stone_colors co ON co.id=?\n        LEFT JOIN stone_carat_references ref ON ref.cut_id=cu.id AND ref.size_id=ss.id AND ref.is_active=1\n        WHERE cu.id=?\n        LIMIT 1\n    ");
    $stmt->execute([$targetSizeId, (int)$sourceStone['color_id'], (int)$sourceStone['cut_id']]);
    $base = $stmt->fetch();
    if (!$base) throw new RuntimeException('Doğru hedef ölçü/carat bilgisi bulunamadı. Önce Taş Ölçüleri ekranından doğru ölçüyü aç.');
    $caratEach = (float)($base['carat_each'] ?? 0);
    if ($caratEach <= 0) throw new RuntimeException('Hedef ölçü için 1 adet carat bilgisi yok. Önce Taş Ölçüleri ekranından carat gir.');

    $sku = stone_generate_sku((string)$sourceStone['species_code'], (string)$base['cut_code'], (string)$base['size_code'], (string)$base['color_code']);
    $stmt = $pdo->prepare("INSERT INTO stones (stone_sku, species_id, cut_id, size_id, color_id, carat_each, critical_stock_qty, created_by, is_active) VALUES (?,?,?,?,?,?,0,?,1) ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), is_active=1");
    $actor = current_user();
    $stmt->execute([$sku, (int)$sourceStone['species_id'], (int)$sourceStone['cut_id'], $targetSizeId, (int)$sourceStone['color_id'], $caratEach, $actor['id'] ?? null]);

    $stmt = $pdo->prepare("\n        SELECT st.*, sp.species_code, sp.species_name, cu.cut_code, cu.cut_name, ss.size_code, ss.size_label, co.color_code, co.color_name\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        WHERE st.stone_sku=?\n        LIMIT 1\n    ");
    $stmt->execute([$sku]);
    $target = $stmt->fetch();
    if (!$target) throw new RuntimeException('Hedef taş kartı oluşturulamadı.');
    return $target;
}

function stone_merge_single_stone_in_tx(PDO $pdo, int $sourceStoneId, int $targetStoneId, string $reason, int $userId): array
{
    $reason = trim($reason);
    if ($reason === '') throw new RuntimeException('Düzeltme nedeni zorunlu.');
    if ($sourceStoneId <= 0 || $targetStoneId <= 0 || $sourceStoneId === $targetStoneId) {
        throw new RuntimeException('Kaynak ve hedef taş kartı farklı olmalı.');
    }

    $stmt = $pdo->prepare("\n        SELECT st.*, sp.species_code, sp.species_name, cu.cut_code, cu.cut_name, ss.size_code, ss.size_label, co.color_code, co.color_name\n        FROM stones st\n        JOIN stone_species sp ON sp.id=st.species_id\n        JOIN stone_cuts cu ON cu.id=st.cut_id\n        JOIN stone_sizes ss ON ss.id=st.size_id\n        JOIN stone_colors co ON co.id=st.color_id\n        WHERE st.id=?\n        FOR UPDATE\n    ");
    $stmt->execute([$sourceStoneId]);
    $source = $stmt->fetch();
    $stmt->execute([$targetStoneId]);
    $target = $stmt->fetch();
    if (!$source || !$target) throw new RuntimeException('Kaynak veya hedef taş bulunamadı.');

    $stmt = $pdo->prepare("SELECT id FROM stone_purchase_lots WHERE stone_id=?");
    $stmt->execute([$sourceStoneId]);
    $lotIds = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));

    $stmt = $pdo->prepare("SELECT COUNT(*) FROM stone_stock_movements WHERE stone_id=?");
    $stmt->execute([$sourceStoneId]);
    $movementCount = (int)$stmt->fetchColumn();
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM stone_purchase_lots WHERE stone_id=?");
    $stmt->execute([$sourceStoneId]);
    $lotCount = (int)$stmt->fetchColumn();

    // If the target stone has a different carat_each, purchase quantities must be recalculated.
    // Financial total carat/cost remains unchanged; only piece count changes.
    if ($lotIds) {
        $lotStmt = $pdo->prepare("SELECT id, total_carat FROM stone_purchase_lots WHERE id=? FOR UPDATE");
        $qtyStmt = $pdo->prepare("UPDATE stone_purchase_lots SET calculated_qty=? WHERE id=?");
        $moveStmt = $pdo->prepare("UPDATE stone_stock_movements SET qty_change=? WHERE purchase_lot_id=? AND movement_type=?");
        foreach ($lotIds as $lotIdForQty) {
            $lotStmt->execute([$lotIdForQty]);
            $lotRow = $lotStmt->fetch();
            if (!$lotRow) continue;
            $newQty = stone_qty_from_carat((float)$lotRow['total_carat'], (float)$target['carat_each']);
            $qtyStmt->execute([$newQty, $lotIdForQty]);
            $moveStmt->execute([$newQty, $lotIdForQty, 'purchase_in']);
            $moveStmt->execute([-1 * $newQty, $lotIdForQty, 'purchase_void']);
        }
    }

    $stmt = $pdo->prepare("UPDATE stone_purchase_lots SET stone_id=? WHERE stone_id=?");
    $stmt->execute([$targetStoneId, $sourceStoneId]);
    $stmt = $pdo->prepare("UPDATE stone_stock_movements SET stone_id=? WHERE stone_id=?");
    $stmt->execute([$targetStoneId, $sourceStoneId]);
    $stmt = $pdo->prepare("UPDATE stone_open_requests SET resolved_stone_id=? WHERE resolved_stone_id=?");
    $stmt->execute([$targetStoneId, $sourceStoneId]);

    if ($lotIds) {
        $placeholders = implode(',', array_fill(0, count($lotIds), '?'));
        $params = array_merge([' | Taş kartı düzeltildi: '.$source['stone_sku'].' → '.$target['stone_sku']], $lotIds);
        $sql = "UPDATE supplier_transactions SET note = CONCAT(COALESCE(note,''), ?) WHERE reference_id IN ($placeholders) AND reference_type IN ('stone_purchase_lot','stone_purchase_lot_void')";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
    }

    $stmt = $pdo->prepare("UPDATE stones SET is_active=0, merged_into_stone_id=?, archived_at=NOW(), archived_by=?, archived_reason=? WHERE id=?");
    $stmt->execute([$targetStoneId, $userId, $reason, $sourceStoneId]);

    stone_admin_audit('stone_merge', 'stone', $sourceStoneId, $source, ['target_stone_id'=>$targetStoneId, 'target_sku'=>$target['stone_sku'], 'moved_lots'=>$lotCount, 'moved_movements'=>$movementCount], $reason);
    return ['source'=>$source, 'target'=>$target, 'lot_count'=>$lotCount, 'movement_count'=>$movementCount];
}

function stone_merge_single_stone(int $sourceStoneId, int $targetStoneId, string $reason, int $userId): array
{
    $pdo = db();
    $pdo->beginTransaction();
    try {
        $result = stone_merge_single_stone_in_tx($pdo, $sourceStoneId, $targetStoneId, $reason, $userId);
        $pdo->commit();
        return $result;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
}

function stone_merge_size_series(int $cutId, int $sourceSizeId, int $targetSizeId, string $reason, int $userId): array
{
    $reason = trim($reason);
    if ($reason === '') throw new RuntimeException('Düzeltme nedeni zorunlu.');
    if ($cutId <= 0 || $sourceSizeId <= 0 || $targetSizeId <= 0 || $sourceSizeId === $targetSizeId) {
        throw new RuntimeException('Kesim, yanlış ölçü ve doğru ölçü seçilmeli; yanlış/doğru ölçü farklı olmalı.');
    }
    $pdo = db();
    $pdo->beginTransaction();
    try {
        $stmt = $pdo->prepare("SELECT * FROM stone_sizes WHERE id=? AND cut_id=? FOR UPDATE");
        $stmt->execute([$sourceSizeId, $cutId]);
        $sourceSize = $stmt->fetch();
        $stmt->execute([$targetSizeId, $cutId]);
        $targetSize = $stmt->fetch();
        if (!$sourceSize || !$targetSize) throw new RuntimeException('Kaynak veya hedef ölçü bu kesimde bulunamadı.');

        $stmt = $pdo->prepare("\n            SELECT st.*, sp.species_code, sp.species_name, cu.cut_code, cu.cut_name, ss.size_code, ss.size_label, co.color_code, co.color_name\n            FROM stones st\n            JOIN stone_species sp ON sp.id=st.species_id\n            JOIN stone_cuts cu ON cu.id=st.cut_id\n            JOIN stone_sizes ss ON ss.id=st.size_id\n            JOIN stone_colors co ON co.id=st.color_id\n            WHERE st.cut_id=? AND st.size_id=?\n            ORDER BY st.stone_sku\n            FOR UPDATE\n        ");
        $stmt->execute([$cutId, $sourceSizeId]);
        $sources = $stmt->fetchAll();
        $movedStones = 0;
        $movedLots = 0;
        $movedMovements = 0;
        foreach ($sources as $sourceStone) {
            $targetStone = stone_find_or_create_target_stone_in_tx($pdo, $sourceStone, $targetSizeId);
            if ((int)$targetStone['id'] === (int)$sourceStone['id']) continue;
            $res = stone_merge_single_stone_in_tx($pdo, (int)$sourceStone['id'], (int)$targetStone['id'], 'Ölçü serisi düzeltmesi: '.$sourceSize['size_label'].' → '.$targetSize['size_label'].' | '.$reason, $userId);
            $movedStones++;
            $movedLots += (int)$res['lot_count'];
            $movedMovements += (int)$res['movement_count'];
        }

        $stmt = $pdo->prepare("UPDATE stone_sizes SET is_active=0 WHERE id=?");
        $stmt->execute([$sourceSizeId]);
        $stmt = $pdo->prepare("UPDATE stone_carat_references SET is_active=0 WHERE size_id=?");
        $stmt->execute([$sourceSizeId]);
        stone_admin_audit('size_series_merge', 'stone_size', $sourceSizeId, $sourceSize, ['target_size_id'=>$targetSizeId, 'source_label'=>$sourceSize['size_label'], 'target_label'=>$targetSize['size_label'], 'moved_stones'=>$movedStones, 'moved_lots'=>$movedLots, 'moved_movements'=>$movedMovements], $reason);
        $pdo->commit();
        return ['moved_stones'=>$movedStones, 'moved_lots'=>$movedLots, 'moved_movements'=>$movedMovements, 'source_size'=>$sourceSize, 'target_size'=>$targetSize];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
}
