<?php
// design_core_common.php — Design workflow helpers.
// v3.3: admin approval/revision/sample tracking cleanup.

function design_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 design_status_options(): array
{
    return [
        'submitted' => 'Mağaza talebi geldi',
        'designer_review' => 'Çizimci incelemesinde',
        'in_design' => 'Çizim yapılıyor',
        'design_completed' => 'Çizim tamamlandı / admin onayına gönderildi',
        'admin_review' => 'Admin onayında',
        'revision_requested' => 'Admin revizesi çizimciye döndü',
        'production_revision' => 'Üretimden çizim revizesi döndü',
        'sample_queue' => 'Numune üretim sırasına alındı',
        'product_created' => 'Ürün kartına bağlandı',
        'rejected' => 'Reddedildi',
        'cancelled' => 'İptal edildi',
    ];
}

function design_workflow_bucket_label(string $bucket): string
{
    switch ($bucket) {
        case 'waiting': return 'Bekleyen / Üzerimdeki Çizimler';
        case 'sent': return 'Admine Gönderilenler';
        case 'admin_revision': return 'Admin Revizesi Dönenler';
        case 'production_revision': return 'Üretimden Revize Dönenler';
        case 'revision': return 'Tüm Revize Dönenler';
        case 'completed': return 'Tamamlanan / Kapananlar';
        default: return 'Tasarım Kuyruğu';
    }
}

function design_bucket_statuses(string $bucket): array
{
    switch ($bucket) {
        case 'sent': return ['design_completed','admin_review','sample_queue'];
        case 'admin_revision': return ['revision_requested'];
        case 'production_revision': return ['production_revision'];
        case 'revision': return ['revision_requested','production_revision'];
        case 'completed': return ['product_created','rejected','cancelled'];
        default: return ['submitted','designer_review','in_design'];
    }
}

function design_status_label(?string $status): string
{
    $opts = design_status_options();
    return $opts[$status ?? ''] ?? (string)$status;
}

function design_status_badge_class(?string $status): string
{
    switch ((string)$status) {
        case 'rejected':
        case 'cancelled':
            return 'badge badge-red';
        case 'design_completed':
        case 'admin_review':
        case 'sample_queue':
        case 'product_created':
            return 'badge badge-green';
        case 'in_design':
        case 'designer_review':
        case 'revision_requested':
        case 'production_revision':
            return 'badge badge-gold';
        default:
            return 'badge';
    }
}

function design_group_status_label(?string $status): string
{
    switch ((string)$status) {
        case 'open': return 'Açık çizim dosyası';
        case 'in_design': return 'Çizimde';
        case 'design_completed': return 'Çizim tamamlandı';
        case 'admin_review': return 'Admin onayında';
        case 'revision_requested': return 'Admin revizesi çizimciye döndü';
        case 'production_revision': return 'Üretimden revize döndü';
        case 'sample_queue': return 'Numune üretim sırasında';
        case 'product_created': return 'Ürün kartına bağlandı';
        case 'closed': return 'Kapandı';
        case 'cancelled': return 'İptal edildi';
        case 'rejected': return 'Reddedildi';
        default: return (string)$status;
    }
}

function design_sample_status_options(): array
{
    return [
        '' => 'Henüz numune yok',
        'queued' => 'Numune üretim sırasına alındı',
        'in_production' => 'Numune üretimde',
        'produced' => 'Numune üretildi / ürün kartı bekliyor',
        'production_revision' => 'Üretimden çizim revizesi döndü',
        'cancelled' => 'Numune iptal',
        'product_created' => 'Ürün kartına bağlandı',
    ];
}

function design_sample_status_label(?string $status): string
{
    $opts = design_sample_status_options();
    return $opts[$status ?? ''] ?? (string)$status;
}

function design_audit_action_label(?string $action): string
{
    $map = [
        'create_request' => 'Yeni tasarım talebi oluşturuldu',
        'take_review' => 'Çizimci incelemeye aldı',
        'start_design' => 'Çizime başlandı',
        'save_designer_fields' => 'Çizim bilgileri kaydedildi',
        'send_admin' => 'Admin onayına gönderildi',
        'attach_group' => 'Çizim dosyasına bağlandı',
        'attach_group_selected' => 'Seçilen çizim dosyasına bağlandı',
        'detach_group' => 'Çizim dosyasından çıkarıldı',
        'admin_revision' => 'Admin revizesi istendi',
        'production_revision' => 'Üretimden revize döndü',
        'sample_queue' => 'Numune üretim sırasına alındı',
        'link_group_product' => 'Ürün kartına bağlandı',
        'link_single_product' => 'Ürün kartına bağlandı',
        'reject_single' => 'Talep reddedildi',
        'reject_group' => 'Çizim dosyası reddedildi',
        'cancel_single' => 'Talep iptal edildi',
        'store_cancel' => 'Mağaza talebi iptal etti',
    ];
    return $map[$action ?? ''] ?? (string)$action;
}

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

function design_users_for_designer(): array
{
    try {
        return db()->query("SELECT id, full_name, role FROM users WHERE is_active=1 ORDER BY full_name")->fetchAll();
    } catch (Throwable $e) {
        return [];
    }
}

function design_product_type_options(): array
{
    if (function_exists('product_type_options')) return product_type_options();
    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();
        $out = [];
        foreach ($rows as $r) {
            $out[(string)$r['category_code']] = ['label'=>(string)$r['type_label'], 'letter'=>(string)$r['sku_prefix']];
        }
        if ($out) return $out;
    } catch (Throwable $e) {}
    return [
        'ring' => ['label'=>'Yüzük','letter'=>'R'],
        'pendant' => ['label'=>'Kolye / Pendant','letter'=>'P'],
        'earring' => ['label'=>'Küpe','letter'=>'E'],
        'bracelet' => ['label'=>'Bileklik','letter'=>'B'],
        'other' => ['label'=>'Diğer','letter'=>'O'],
    ];
}

function design_product_type_label(?string $code): string
{
    $opts = design_product_type_options();
    return $opts[$code ?? '']['label'] ?? ($code ?: '-');
}

function design_next_request_no(): string
{
    $n = (int)db()->query("SELECT COALESCE(MAX(id),0)+1 FROM design_requests")->fetchColumn();
    return 'DR' . str_pad((string)$n, 6, '0', STR_PAD_LEFT);
}

function design_next_group_no(): string
{
    try {
        $n = (int)db()->query("SELECT COALESCE(MAX(id),0)+1 FROM design_groups")->fetchColumn();
    } catch (Throwable $e) { $n = 1; }
    return 'DG' . str_pad((string)$n, 6, '0', STR_PAD_LEFT);
}

function design_normalize_url(?string $url): string
{
    $url = trim((string)$url);
    if ($url === '') return '';
    $url = preg_replace('/#.*$/', '', $url);
    $url = preg_replace('/\?.*$/', '', $url);
    $url = rtrim($url, "/ \t\n\r\0\x0B");
    return function_exists('mb_strtolower') ? mb_strtolower($url, 'UTF-8') : strtolower($url);
}

function design_find_existing_product_by_url(?string $url): ?array
{
    $url = trim((string)$url);
    if ($url === '') return null;
    try {
        $stmt = db()->prepare("SELECT p.id, p.master_code, p.product_name, s.short_name, s.source_url FROM product_core_specs s JOIN products p ON p.id=s.product_id WHERE p.is_active=1 AND s.source_url = ? LIMIT 1");
        $stmt->execute([$url]);
        $row = $stmt->fetch();
        if ($row) return $row;
        $norm = design_normalize_url($url);
        if ($norm !== '') {
            $stmt = db()->query("SELECT p.id, p.master_code, p.product_name, s.short_name, s.source_url FROM product_core_specs s JOIN products p ON p.id=s.product_id WHERE p.is_active=1 AND s.source_url IS NOT NULL AND s.source_url<>''");
            foreach ($stmt->fetchAll() as $r) {
                if (design_normalize_url((string)$r['source_url']) === $norm) return $r;
            }
        }
    } catch (Throwable $e) {}
    return null;
}

function design_duplicate_requests(?string $url, int $excludeId = 0): array
{
    $url = trim((string)$url);
    if ($url === '') return [];
    $norm = design_normalize_url($url);
    try {
        $stmt = db()->query("SELECT dr.*, st.store_code, st.store_name, dg.group_no, dg.status AS group_status FROM design_requests dr JOIN stores st ON st.id=dr.store_id LEFT JOIN design_groups dg ON dg.id=dr.design_group_id WHERE dr.source_url IS NOT NULL AND dr.source_url<>'' ORDER BY dr.created_at ASC LIMIT 5000");
        $out = [];
        foreach ($stmt->fetchAll() as $r) {
            if ((int)$r['id'] === $excludeId) continue;
            if (design_normalize_url((string)$r['source_url']) === $norm) $out[] = $r;
        }
        return $out;
    } catch (Throwable $e) { return []; }
}

function design_same_link_store_summary(?string $url): string
{
    $dups = design_duplicate_requests($url, 0);
    $stores = [];
    foreach ($dups as $d) { $stores[(string)$d['store_code']] = true; }
    if (!$stores) return '';
    return implode(' + ', array_keys($stores));
}

function design_handle_image_upload(string $requestNo, array $file): ?string
{
    if (empty($file['tmp_name']) || !is_uploaded_file($file['tmp_name'])) return null;
    if (!empty($file['error'])) throw new RuntimeException('Görsel yüklenemedi. Hata kodu: '.$file['error']);
    $max = 2 * 1024 * 1024;
    if ((int)($file['size'] ?? 0) > $max) throw new RuntimeException('Görsel 2 MB üstü. Lütfen küçültüp tekrar yükleyin.');
    $info = @getimagesize($file['tmp_name']);
    if (!$info) throw new RuntimeException('Yüklenen dosya görsel değil.');
    $mime = $info['mime'] ?? '';
    $ext = null;
    if ($mime === 'image/jpeg') $ext = 'jpg';
    if ($mime === 'image/png') $ext = 'png';
    if ($mime === 'image/webp') $ext = 'webp';
    if (!$ext) throw new RuntimeException('Sadece JPG, PNG veya WEBP görsel yükleyin.');
    $dir = __DIR__ . '/uploads/design_requests';
    if (!is_dir($dir) && !mkdir($dir, 0755, true)) throw new RuntimeException('Yükleme klasörü oluşturulamadı.');
    $name = preg_replace('/[^A-Z0-9_-]/i', '_', $requestNo) . '_' . date('YmdHis') . '_' . bin2hex(random_bytes(3)) . '.' . $ext;
    $dest = $dir . '/' . $name;
    if (!move_uploaded_file($file['tmp_name'], $dest)) throw new RuntimeException('Görsel kaydedilemedi.');
    return 'uploads/design_requests/' . $name;
}

function design_current_user_can_view(array $req, array $user): bool
{
    $role = (string)($user['role'] ?? '');
    if (in_array($role, ['admin','production_manager','workshop_staff','designer'], true)) return true;
    if ($role === 'store_user') return (int)($user['store_id'] ?? 0) === (int)$req['store_id'];
    return false;
}

function design_find_by_id(int $id): ?array
{
    $stmt = db()->prepare("SELECT dr.*, st.store_code, st.store_name, u.full_name AS requested_by_name, du.full_name AS designer_name, p.master_code AS matched_master_code, p.product_name AS matched_product_name, cp.master_code AS created_master_code, cp.product_name AS created_product_name, dg.group_no, dg.group_title, dg.status AS group_status, dg.sample_status AS group_sample_status, dg.sample_no AS group_sample_no, (SELECT COUNT(*) FROM design_group_members mm WHERE mm.group_id=dr.design_group_id) AS group_member_count FROM design_requests dr JOIN stores st ON st.id=dr.store_id LEFT JOIN users u ON u.id=dr.requested_by LEFT JOIN users du ON du.id=dr.designer_user_id LEFT JOIN products p ON p.id=dr.matched_product_id LEFT JOIN products cp ON cp.id=dr.created_product_id LEFT JOIN design_groups dg ON dg.id=dr.design_group_id WHERE dr.id=? LIMIT 1");
    $stmt->execute([$id]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function design_audit(int $requestId, ?int $actorUserId, string $actionType, ?string $oldStatus = null, ?string $newStatus = null, ?string $note = null): void
{
    try {
        $stmt = db()->prepare("INSERT INTO design_request_audit_logs (design_request_id, actor_user_id, action_type, old_status, new_status, note) VALUES (?,?,?,?,?,?)");
        $stmt->execute([$requestId, $actorUserId, $actionType, $oldStatus, $newStatus, $note]);
    } catch (Throwable $e) {}
}

function design_audit_rows(int $requestId): array
{
    $stmt = db()->prepare("SELECT l.*, u.full_name FROM design_request_audit_logs l LEFT JOIN users u ON u.id=l.actor_user_id WHERE l.design_request_id=? ORDER BY l.id DESC LIMIT 80");
    $stmt->execute([$requestId]);
    return $stmt->fetchAll();
}

function design_request_list_sql(array $user, array $filters = []): array
{
    $role = (string)($user['role'] ?? '');
    $params = [];
    $sql = "SELECT dr.*, st.store_code, st.store_name, u.full_name AS requested_by_name, du.full_name AS designer_name, cp.master_code AS created_master_code, dg.group_no, dg.status AS group_status, dg.sample_no AS group_sample_no, dg.sample_status AS group_sample_status, (SELECT COUNT(*) FROM design_group_members mm WHERE mm.group_id=dr.design_group_id) AS group_member_count, (SELECT COUNT(*) FROM design_requests d2 WHERE d2.source_url IS NOT NULL AND d2.source_url<>'' AND d2.source_url=dr.source_url) AS same_url_count FROM design_requests dr JOIN stores st ON st.id=dr.store_id LEFT JOIN users u ON u.id=dr.requested_by LEFT JOIN users du ON du.id=dr.designer_user_id LEFT JOIN products cp ON cp.id=dr.created_product_id LEFT JOIN design_groups dg ON dg.id=dr.design_group_id WHERE 1=1";
    if ($role === 'store_user') {
        $sql .= " AND dr.store_id = :user_store_id";
        $params['user_store_id'] = (int)($user['store_id'] ?? 0);
    }
    if (!empty($filters['statuses']) && is_array($filters['statuses'])) {
        $placeholders = [];
        foreach (array_values($filters['statuses']) as $idx => $st) {
            $key = 'status_in_' . $idx;
            $placeholders[] = ':' . $key;
            $params[$key] = $st;
        }
        if ($placeholders) $sql .= ' AND dr.status IN (' . implode(',', $placeholders) . ')';
    }
    if (!empty($filters['status'])) { $sql .= " AND dr.status = :status"; $params['status'] = $filters['status']; }
    if (!empty($filters['store_id']) && $role !== 'store_user') { $sql .= " AND dr.store_id = :store_id"; $params['store_id'] = (int)$filters['store_id']; }
    if (!empty($filters['product_type_code'])) { $sql .= " AND dr.product_type_code = :ptype"; $params['ptype'] = $filters['product_type_code']; }
    if (!empty($filters['designer_user_id'])) { $sql .= " AND dr.designer_user_id = :designer_id"; $params['designer_id'] = (int)$filters['designer_user_id']; }
    if (!empty($filters['same_link'])) { $sql .= " AND dr.source_url IS NOT NULL AND dr.source_url<>'' AND EXISTS (SELECT 1 FROM design_requests dx WHERE dx.id<>dr.id AND dx.source_url=dr.source_url)"; }
    if (!empty($filters['q'])) { $sql .= " AND (dr.request_no LIKE :q OR dr.title LIKE :q OR dr.source_url LIKE :q OR st.store_code LIKE :q OR dg.group_no LIKE :q)"; $params['q'] = '%'.$filters['q'].'%'; }
    if (!empty($filters['queue_unique_groups'])) {
        $sql .= " AND (dr.design_group_id IS NULL OR dr.id = (SELECT MIN(dgm.design_request_id) FROM design_group_members dgm JOIN design_requests qdr ON qdr.id=dgm.design_request_id WHERE dgm.group_id = dr.design_group_id AND qdr.status=dr.status))";
    }
    $sort = (string)($filters['sort'] ?? 'oldest');
    switch ($sort) {
        case 'newest': $order = 'dr.created_at DESC'; break;
        case 'request_no': $order = 'dr.request_no ASC'; break;
        case 'store': $order = 'st.store_code ASC, dr.created_at ASC'; break;
        case 'status': $order = "FIELD(dr.status,'submitted','designer_review','in_design','revision_requested','production_revision','design_completed','admin_review','sample_queue','product_created','rejected','cancelled'), dr.created_at ASC"; break;
        case 'type': $order = 'dr.product_type_code ASC, dr.created_at ASC'; break;
        case 'designer': $order = 'du.full_name ASC, dr.created_at ASC'; break;
        default: $order = 'dr.created_at ASC';
    }
    $sql .= " ORDER BY {$order} LIMIT 500";
    return [$sql, $params];
}

function design_group_find_by_id(int $id): ?array
{
    $stmt = db()->prepare("SELECT dg.*, u.full_name AS created_by_name, p.master_code AS created_master_code, p.product_name AS created_product_name, (SELECT COUNT(*) FROM design_group_members mm WHERE mm.group_id=dg.id) AS member_count FROM design_groups dg LEFT JOIN users u ON u.id=dg.created_by LEFT JOIN products p ON p.id=dg.created_product_id WHERE dg.id=? LIMIT 1");
    $stmt->execute([$id]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function design_group_members(int $groupId): array
{
    $stmt = db()->prepare("SELECT m.*, dr.request_no, dr.title, dr.status AS request_status, dr.source_url, dr.reference_image_path, dr.request_note, dr.created_at AS request_created_at, dr.width_mm, dr.length_mm, dr.height_mm, dr.band_width_mm, dr.gram_10k, dr.gram_14k, dr.gram_18k, dr.drawing_cost_actual_usd, dr.sizing_cost_actual_usd, dr.stone_slot_note, dr.designer_note, dr.sample_no, dr.sample_status, st.store_code, st.store_name, u.full_name AS requested_by_name, du.full_name AS designer_name FROM design_group_members m JOIN design_requests dr ON dr.id=m.design_request_id JOIN stores st ON st.id=m.store_id LEFT JOIN users u ON u.id=dr.requested_by LEFT JOIN users du ON du.id=dr.designer_user_id WHERE m.group_id=? ORDER BY dr.created_at ASC");
    $stmt->execute([$groupId]);
    return $stmt->fetchAll();
}

function design_group_for_request(int $requestId): ?array
{
    $stmt = db()->prepare("SELECT dg.*, (SELECT COUNT(*) FROM design_group_members mm WHERE mm.group_id=dg.id) AS member_count FROM design_groups dg JOIN design_group_members m ON m.group_id=dg.id WHERE m.design_request_id=? LIMIT 1");
    $stmt->execute([$requestId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function design_file_kind_label(?int $memberCount): string
{
    $count = (int)$memberCount;
    if ($count > 1) return 'Ortak çizim dosyası';
    if ($count === 1) return 'Tekil çizim dosyası';
    return 'Bağımsız talep';
}

function design_file_badge_html(?string $groupNo, ?int $memberCount, ?string $groupStatus = null): string
{
    if (!$groupNo) return '<span class="c-mt">Bağımsız</span>';
    $kind = design_file_kind_label($memberCount);
    return '<span class="badge badge-green">'.e($groupNo).'</span><div class="c-mt">'.e($kind).($groupStatus ? ' / '.e(design_group_status_label($groupStatus)) : '').'</div>';
}

function design_update_group_shares(int $groupId): void
{
    $group = design_group_find_by_id($groupId);
    if (!$group) return;
    $members = design_group_members($groupId);
    $storeIds = [];
    foreach ($members as $m) $storeIds[(int)$m['store_id']] = true;
    $count = max(1, count($storeIds));
    $drawing = $group['drawing_charge_total_usd'] !== null ? round(((float)$group['drawing_charge_total_usd']) / $count, 2) : null;
    $sizing = $group['sizing_charge_total_usd'] !== null ? round(((float)$group['sizing_charge_total_usd']) / $count, 2) : null;
    $stmt = db()->prepare("UPDATE design_group_members SET share_drawing_usd=?, share_sizing_usd=? WHERE group_id=?");
    $stmt->execute([$drawing, $sizing, $groupId]);
}

function design_group_cleanup_after_membership_change(int $groupId): void
{
    if ($groupId <= 0) return;
    $stmt = db()->prepare('SELECT design_request_id FROM design_group_members WHERE group_id=? ORDER BY design_request_id ASC');
    $stmt->execute([$groupId]);
    $ids = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
    if (!$ids) {
        db()->prepare('DELETE FROM design_groups WHERE id=?')->execute([$groupId]);
        return;
    }
    $refId = $ids[0];
    db()->prepare('UPDATE design_groups SET reference_design_request_id=?, updated_at=NOW() WHERE id=?')->execute([$refId, $groupId]);
    design_update_group_shares($groupId);
}

function design_create_or_attach_group_from_request(int $requestId, array $actor): int
{
    return design_group_create_or_attach_selected_requests([$requestId], $actor);
}

function design_group_create_or_attach_selected_requests(array $requestIds, array $actor): int
{
    $ids = [];
    foreach ($requestIds as $id) {
        $id = (int)$id;
        if ($id > 0) $ids[$id] = true;
    }
    $ids = array_keys($ids);
    if (!$ids) throw new RuntimeException('Çizim dosyasına bağlanacak talep seçilmedi.');

    $requests = [];
    foreach ($ids as $id) {
        $r = design_find_by_id($id);
        if ($r) $requests[$id] = $r;
    }
    if (!$requests) throw new RuntimeException('Seçilen talepler bulunamadı.');

    $groupId = 0;
    foreach ($requests as $r) {
        if (!empty($r['design_group_id'])) { $groupId = (int)$r['design_group_id']; break; }
    }

    if ($groupId <= 0) {
        $first = reset($requests);
        $groupNo = design_next_group_no();
        db()->prepare("INSERT INTO design_groups (group_no, group_title, normalized_source_url, source_url, reference_design_request_id, product_type_code, designer_user_id, status, drawing_charge_total_usd, sizing_charge_total_usd, created_by) VALUES (?,?,?,?,?,?,?,?,30,20,?)")
            ->execute([$groupNo, $first['title'], design_normalize_url((string)$first['source_url']), $first['source_url'], (int)$first['id'], $first['product_type_code'], $first['designer_user_id'] ?: null, 'open', $actor['id'] ?? null]);
        $groupId = (int)db()->lastInsertId();
    }

    foreach ($requests as $id => $r) {
        $oldGroup = (int)($r['design_group_id'] ?? 0);
        if ($oldGroup > 0 && $oldGroup !== $groupId) {
            db()->prepare('DELETE FROM design_group_members WHERE design_request_id=?')->execute([$id]);
            design_group_cleanup_after_membership_change($oldGroup);
        }
        db()->prepare('INSERT IGNORE INTO design_group_members (group_id, design_request_id, store_id) VALUES (?,?,?)')->execute([$groupId, $id, (int)$r['store_id']]);
        db()->prepare('UPDATE design_requests SET design_group_id=?, updated_at=NOW() WHERE id=?')->execute([$groupId, $id]);
        design_audit($id, $actor['id'] ?? null, 'attach_group_selected', $r['status'] ?? null, $r['status'] ?? null, 'Admin seçilen talebi çizim dosyasına bağladı');
    }
    design_group_cleanup_after_membership_change($groupId);
    design_update_group_shares($groupId);
    return $groupId;
}

function design_group_detach_request(int $groupId, int $requestId, array $actor, string $note = 'Admin talebi çizim dosyasından çıkardı'): void
{
    if ($groupId <= 0 || $requestId <= 0) throw new RuntimeException('Çizim dosyası veya talep seçilmedi.');
    $req = design_find_by_id($requestId);
    if (!$req) throw new RuntimeException('Talep bulunamadı.');
    $stmt = db()->prepare("SELECT id FROM design_group_members WHERE group_id=? AND design_request_id=? LIMIT 1");
    $stmt->execute([$groupId, $requestId]);
    $memberId = (int)($stmt->fetchColumn() ?: 0);
    if ($memberId <= 0) throw new RuntimeException('Bu talep seçili çizim dosyasında değil.');
    db()->prepare("DELETE FROM design_group_members WHERE id=?")->execute([$memberId]);
    db()->prepare("UPDATE design_requests SET design_group_id=NULL, updated_at=NOW() WHERE id=?")->execute([$requestId]);
    design_audit($requestId, $actor['id'] ?? null, 'detach_group', $req['status'] ?? null, $req['status'] ?? null, $note);
    design_group_cleanup_after_membership_change($groupId);
}

function design_group_detach_request_v32(int $groupId, int $requestId, array $actor, string $note = 'Admin talebi çizim dosyasından çıkardı'): void
{
    design_group_detach_request($groupId, $requestId, $actor, $note);
}

function design_groups_list(array $filters = []): array
{
    try {
        $where = '1=1';
        if (!empty($filters['statuses']) && is_array($filters['statuses'])) {
            $in = "'" . implode("','", array_map('addslashes', $filters['statuses'])) . "'";
            $where .= " AND dg.status IN ($in)";
        }
        if (!empty($filters['sample_statuses']) && is_array($filters['sample_statuses'])) {
            $in = "'" . implode("','", array_map('addslashes', $filters['sample_statuses'])) . "'";
            $where .= " AND dg.sample_status IN ($in)";
        }
        return db()->query("SELECT dg.*, COUNT(m.id) AS member_count, GROUP_CONCAT(DISTINCT st.store_code ORDER BY st.store_code SEPARATOR ' + ') AS stores_text, p.master_code AS created_master_code FROM design_groups dg LEFT JOIN design_group_members m ON m.group_id=dg.id LEFT JOIN stores st ON st.id=m.store_id LEFT JOIN products p ON p.id=dg.created_product_id WHERE $where GROUP BY dg.id ORDER BY FIELD(dg.status,'open','in_design','design_completed','admin_review','revision_requested','production_revision','sample_queue','product_created','closed','cancelled','rejected'), dg.created_at ASC LIMIT 500")->fetchAll();
    } catch (Throwable $e) { return []; }
}

function design_product_search_options(string $q = ''): array
{
    try {
        $q = trim($q);
        if ($q === '') {
            return db()->query("SELECT id, master_code, product_name FROM products WHERE is_active=1 ORDER BY id DESC LIMIT 80")->fetchAll();
        }
        $stmt = db()->prepare("SELECT id, master_code, product_name FROM products WHERE is_active=1 AND (master_code LIKE ? OR product_name LIKE ?) ORDER BY master_code LIMIT 80");
        $stmt->execute(['%'.$q.'%', '%'.$q.'%']);
        return $stmt->fetchAll();
    } catch (Throwable $e) { return []; }
}

function design_requests_by_source_url(?string $url): array
{
    $url = trim((string)$url);
    if ($url === '') return [];
    $norm = design_normalize_url($url);
    $stmt = db()->query("SELECT dr.*, st.store_code, st.store_name, dg.group_no, dg.status AS group_status, (SELECT COUNT(*) FROM design_group_members mm WHERE mm.group_id=dr.design_group_id) AS group_member_count FROM design_requests dr JOIN stores st ON st.id=dr.store_id LEFT JOIN design_groups dg ON dg.id=dr.design_group_id WHERE dr.source_url IS NOT NULL AND dr.source_url<>'' AND dr.status NOT IN ('cancelled','rejected','product_created') ORDER BY dr.created_at ASC, dr.id ASC LIMIT 5000");
    $out = [];
    foreach ($stmt->fetchAll() as $r) {
        if (design_normalize_url((string)$r['source_url']) === $norm) $out[] = $r;
    }
    return $out;
}

function design_same_link_candidates(): array
{
    try {
        $stmt = db()->query("SELECT dr.id, dr.request_no, dr.source_url, dr.created_at, st.store_code FROM design_requests dr JOIN stores st ON st.id=dr.store_id WHERE dr.source_url IS NOT NULL AND dr.source_url<>'' AND dr.status NOT IN ('cancelled','rejected','product_created') ORDER BY dr.created_at ASC LIMIT 5000");
        $groups = [];
        foreach ($stmt->fetchAll() as $r) {
            $key = design_normalize_url((string)$r['source_url']);
            if ($key === '') continue;
            if (!isset($groups[$key])) {
                $groups[$key] = ['source_url'=>(string)$r['source_url'], 'first_created_at'=>(string)$r['created_at'], 'request_count'=>0, 'stores'=>[], 'request_nos'=>[]];
            }
            $groups[$key]['request_count']++;
            $groups[$key]['stores'][(string)$r['store_code']] = true;
            $groups[$key]['request_nos'][] = (string)$r['request_no'];
        }
        $out = [];
        foreach ($groups as $g) {
            if ((int)$g['request_count'] < 2) continue;
            $out[] = [
                'source_url' => $g['source_url'],
                'first_created_at' => $g['first_created_at'],
                'request_count' => (int)$g['request_count'],
                'stores_text' => implode(' + ', array_keys($g['stores'])),
                'request_nos' => implode(', ', $g['request_nos']),
            ];
        }
        return $out;
    } catch (Throwable $e) { return []; }
}

function design_requests_by_statuses(array $statuses, int $limit = 500): array
{
    if (!$statuses) return [];
    $params = [];
    $ph = [];
    foreach (array_values($statuses) as $i => $st) { $ph[] = '?'; $params[] = $st; }
    $sql = "SELECT dr.*, st.store_code, st.store_name, du.full_name AS designer_name, dg.group_no, dg.status AS group_status, dg.sample_no AS group_sample_no, dg.sample_status AS group_sample_status, (SELECT COUNT(*) FROM design_group_members mm WHERE mm.group_id=dr.design_group_id) AS group_member_count FROM design_requests dr JOIN stores st ON st.id=dr.store_id LEFT JOIN users du ON du.id=dr.designer_user_id LEFT JOIN design_groups dg ON dg.id=dr.design_group_id WHERE dr.status IN (".implode(',', $ph).") ORDER BY FIELD(dr.status,'design_completed','admin_review','revision_requested','production_revision','sample_queue','rejected','cancelled'), dr.updated_at DESC, dr.created_at ASC LIMIT ".(int)$limit;
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function design_groups_by_sample_statuses(array $sampleStatuses): array
{
    if (!$sampleStatuses) return [];
    $params = [];
    $ph = [];
    foreach (array_values($sampleStatuses) as $st) { $ph[] = '?'; $params[] = $st; }
    $sql = "SELECT dg.*, COUNT(m.id) AS member_count, GROUP_CONCAT(DISTINCT st.store_code ORDER BY st.store_code SEPARATOR ' + ') AS stores_text, p.master_code AS created_master_code FROM design_groups dg LEFT JOIN design_group_members m ON m.group_id=dg.id LEFT JOIN stores st ON st.id=m.store_id LEFT JOIN products p ON p.id=dg.created_product_id WHERE dg.sample_status IN (".implode(',', $ph).") GROUP BY dg.id ORDER BY dg.updated_at DESC, dg.created_at ASC LIMIT 500";
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function design_active_admin_requests(): array
{
    return design_requests_by_statuses(['design_completed','admin_review','revision_requested','production_revision','sample_queue'], 500);
}

function design_group_request_summary_card(array $r): string
{
    $bits = [];
    foreach (['width_mm'=>'G','length_mm'=>'U','height_mm'=>'Y','band_width_mm'=>'B'] as $k=>$label) {
        if ($r[$k] !== null && $r[$k] !== '') $bits[] = $label.': '.$r[$k].'mm';
    }
    $grams = [];
    foreach (['gram_10k'=>'10K','gram_14k'=>'14K','gram_18k'=>'18K'] as $k=>$label) {
        if ($r[$k] !== null && $r[$k] !== '') $grams[] = $label.': '.$r[$k].'g';
    }
    $out = [];
    if ($bits) $out[] = implode(' / ', $bits);
    if ($grams) $out[] = implode(' / ', $grams);
    if (!empty($r['drawing_cost_actual_usd'])) $out[] = 'Çizim maliyeti: $'.number_format((float)$r['drawing_cost_actual_usd'],2);
    if (!empty($r['sizing_cost_actual_usd'])) $out[] = 'Boylama maliyeti: $'.number_format((float)$r['sizing_cost_actual_usd'],2);
    return $out ? implode('<br>', array_map('e', $out)) : '<span class="c-mt">Teknik/maliyet bilgisi girilmemiş.</span>';
}

function design_short_text(?string $text, int $limit = 220): string
{
    $text = trim((string)$text);
    if ($text === '') return '';
    if (function_exists('mb_strimwidth')) return mb_strimwidth($text, 0, $limit, '...', 'UTF-8');
    return strlen($text) > $limit ? substr($text, 0, $limit) . '...' : $text;
}
