<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

/**
 * InPractice — Marketing Generic Cases [03/10]
 * Digital Marketing: MG-DM-04 to MG-DM-07
 * Market Research: MG-MR-01
 *
 * Run: php artisan db:seed --class=MarketingGenericCaseSeeder_03
 */
class MarketingGenericCaseSeeder_03 extends Seeder
{
    public function run(): void
    {
        $this->command->info('📢 [03/10] Digital Marketing (4-7) + Market Research (1) ...');

        $hub   = DB::table('hubs')->where('slug', 'marketing')->first();
        $admin = DB::table('users')->where('role', 'admin')->first();

        if (! $hub || ! $admin) {
            $this->command->error('Hub or admin not found. Run HubSeeder and AdminSeeder first.');
            return;
        }

        $hubId   = $hub->id;
        $adminId = $admin->id;
        $now     = now();


        $cases = [
        
            // ── MG-DM-04 ──────────────────────────────────────────────────
            [
                'hub_id'        => $hubId,
                'created_by'    => $adminId,
                'topic_slug'    => 'digital_marketing',
                'topic_name_en' => 'Digital Marketing',
                'topic_name_ar' => 'التسويق الرقمي',
                'layer'         => 'generic',
                'difficulty'    => 'intermediate',
                'status'        => 'published',
                'published_at'  => $now,
                'sort_order'    => 204,
                'read_time_minutes' => 13,
        
                'title_en'    => 'The Email List Nobody Opens',
                'subtitle_en' => 'Building an email strategy that people actually want to receive',
                'situation_en' => "You are the CRM and digital manager at **Cornerstone Financial Consulting**, an Egyptian firm offering accounting, tax advisory, and business registration services for SMEs. Over three years, the company has built an email list of 11,400 subscribers — a mix of business owners, CFOs, and entrepreneurs who signed up via the website, events, and referrals.\n\nCurrent email metrics:\n\n| Metric | Cornerstone | Industry Benchmark |\n|---|---|---|\n| Open rate | 9.3% | 22–28% |\n| Click rate | 0.8% | 3–5% |\n| Unsubscribe rate | 2.1%/send | <0.5% |\n| Emails sent per month | 8 | 2–4 |\n| Spam complaint rate | 0.4% | <0.1% |\n\nThe company sends 8 emails per month — mostly promotional: *'Book a consultation this month and get 20% off'*, *'Tax season is here — don't miss the deadline'*, *'Our new service: payroll management.'*\n\nThe CEO wants to increase email volume to 12/month. The marketing manager disagrees. You have been asked to settle the argument with data and a revised strategy.",
                'challenge_questions_en' => json_encode([
                    "Every metric is below benchmark. What story do the metrics tell collectively — and what is the root cause?",
                    "The unsubscribe rate is 2.1% per send. If this continues for 6 months at 8 emails/month, model what happens to the list size.",
                    "What is the difference between an email list and an email audience — and which one does Cornerstone have right now?",
                    "Design a 90-day email strategy that moves Cornerstone from promotional blasting to value-based communication — with specific content types, frequency, and success metrics.",
                ]),
                'solution_en' => "## Email Marketing: The Permission Economy\n\n### What the Metrics Are Saying Together\n\n| Signal | What It Means |\n|---|---|\n| 9.3% open rate | Most recipients have mentally unsubscribed even if they haven't clicked the button |\n| 0.8% click rate | Content is not relevant or compelling enough to act on |\n| 2.1% unsubscribe per send | The list is being actively destroyed with each email |\n| 0.4% spam rate | Recipients are flagging emails as spam — damaging deliverability |\n| 8 emails/month | Over-communication is the primary driver of the above |\n\n**Root cause:** Cornerstone treats its email list as a broadcast channel for promotions. The subscribers signed up expecting valuable information. They are receiving sales pitches. This is the classic permission violation that destroys email lists.\n\n### The List Destruction Model\n\nStarting list: 11,400 subscribers\nUnsubscribe rate: 2.1% per send\nCurrent sends: 8/month = 16.8% lost per month\n\n| Month | Subscribers Remaining |\n|---|---|\n| Month 1 | 9,484 |\n| Month 3 | 6,592 |\n| Month 6 | 3,571 |\n\nAt current trajectory, the list will be less than a third of its current size in 6 months. **Increasing to 12 emails/month accelerates this destruction.**\n\n### Email List vs. Email Audience\n\nAn **email list** is a database of contacts. An **email audience** is a community of people who want to hear from you and take action when they do.\n\nCornerstone has a list. It does not have an audience. The difference is built through consistently delivering content that subscribers value more than the inbox space it takes.\n\n### 90-Day Value-Based Email Strategy\n\n**Phase 1 — Stop the bleeding (Days 1–14)**\n- Immediately reduce to 2 emails/month\n- Send a re-engagement email: *'We've been sending too many emails. Going forward, we're changing what we send you.'* Acknowledge it honestly. Subscribers respect honesty.\n- Segment the list: active (opened in last 90 days) vs. inactive (never opened or hasn't opened in 90+ days)\n- Do not delete the inactive segment — run a reactivation campaign first\n\n**Phase 2 — Build value content (Days 15–45)**\nContent calendar for a financial consulting firm:\n\n| Email Type | Frequency | Example |\n|---|---|---|\n| Tax & Compliance Alert | As needed | 'Corporate Tax deadline: March 31 — what you must file' |\n| Practical Guide | 1×/month | 'How to read your P&L in 10 minutes — a guide for business owners' |\n| Client Success Story | 1×/month | 'How a Cairo trading company saved EGP 180,000 in unnecessary tax (with their permission)' |\n| Industry Update | 1×/month | 'What the new investment law means for your business' |\n\n**Phase 3 — Measure and optimise (Days 46–90)**\n- Target open rate: 22%+ by end of 90 days\n- Target click rate: 3%+\n- Target unsubscribe rate: below 0.3% per send\n- A/B test subject lines (the single highest-impact lever for open rates)\n- Add a preference centre: let subscribers choose which topics they want (tax, payroll, compliance, business registration)",
        
                'bigger_lesson_en' => "**Email marketing is the most profitable digital channel — when it is built on trust, not frequency.**\n\nEvery promotional email that does not serve the subscriber's interests costs you permission. And in email marketing, once permission is lost, it rarely comes back. The companies that treat their email lists as communities to serve, not audiences to sell to, are the ones that see 30–40% open rates and measurable revenue from every send.",
        
                'skills_taught_en' => json_encode([
                    'Email marketing metrics and benchmarks',
                    'List health and decay modelling',
                    'Value-based vs. promotional email strategy',
                    'Email segmentation and reactivation',
                    'Content calendar for B2B email marketing',
                ]),
        
                'title_ar'    => 'قائمة البريد الإلكتروني التي لا يفتحها أحد',
                'subtitle_ar' => 'بناء استراتيجية بريد إلكتروني يريد الناس فعلاً استقباله',
                'situation_ar' => "أنت مدير CRM والرقمي في **كورنرستون للاستشارات المالية**، شركة مصرية تقدم المحاسبة والاستشارات الضريبية وتسجيل الأعمال للشركات الصغيرة. على مدى ثلاث سنوات، بنت الشركة قائمة بريد بـ11,400 مشترك.\n\n| المقياس | كورنرستون | متوسط الصناعة |\n|---|---|---|\n| معدل الفتح | 9.3% | 22-28% |\n| معدل النقر | 0.8% | 3-5% |\n| معدل إلغاء الاشتراك | 2.1%/إرسال | <0.5% |\n| رسائل/شهر | 8 | 2-4 |\n\nترسل الشركة 8 رسائل شهرياً — معظمها ترويجية. الرئيس التنفيذي يريد الزيادة إلى 12 رسالة. مدير التسويق يعارض. طُلب منك حسم الجدل.",
                'challenge_questions_ar' => json_encode([
                    "كل مقياس أقل من المتوسط. ما القصة التي تحكيها المقاييس مجتمعةً — وما السبب الجذري؟",
                    "معدل إلغاء الاشتراك 2.1% لكل إرسال. إذا استمر هذا 6 أشهر بـ8 رسائل/شهر، نمذج ما يحدث لحجم القائمة.",
                    "ما الفرق بين قائمة بريد وجمهور بريد — وأيهما تملكه كورنرستون الآن؟",
                    "صمم استراتيجية بريد إلكتروني لـ90 يوماً تنتقل بكورنرستون من البث الترويجي للتواصل القائم على القيمة.",
                ]),
                'solution_ar' => "## التسويق بالبريد الإلكتروني: اقتصاد الإذن\n\n### نموذج تدمير القائمة\n\nقائمة بداية: 11,400\nمعدل إلغاء الاشتراك: 2.1% لكل إرسال، 8 إرسالات/شهر = 16.8% شهرياً\n\n| الشهر | المشتركون المتبقون |\n|---|---|\n| 1 | 9,484 |\n| 3 | 6,592 |\n| 6 | 3,571 |\n\n**زيادة الرسائل إلى 12/شهر يُسرّع هذا التدمير.**\n\n### استراتيجية 90 يوماً القائمة على القيمة\n\n**المرحلة 1 (1-14 يوم):** تقليل فوري إلى 2 رسائل/شهر + بريد إعادة تفاعل\n**المرحلة 2 (15-45 يوم):** تقويم محتوى: تنبيه ضريبي + دليل عملي + قصة نجاح + تحديث صناعي\n**المرحلة 3 (46-90 يوم):** هدف معدل الفتح 22%+ وإلغاء الاشتراك أقل من 0.3% لكل إرسال",
        
                'bigger_lesson_ar' => "**التسويق بالبريد الإلكتروني أكثر القنوات الرقمية ربحاً — حين يُبنى على الثقة لا التكرار.**\n\nكل بريد ترويجي لا يخدم مصلحة المشترك يُكلّفك إذناً. وفي التسويق بالبريد، حين يُفقد الإذن نادراً ما يعود.",
        
                'skills_taught_ar' => json_encode([
                    'مقاييس التسويق بالبريد الإلكتروني ومعاييره',
                    'نمذجة صحة القائمة وتدهورها',
                    'استراتيجية البريد القائمة على القيمة مقابل الترويجية',
                    'تقسيم القائمة وإعادة التفاعل',
                    'تقويم المحتوى للتسويق بالبريد B2B',
                ]),
                'created_at' => $now,
                'updated_at' => $now,
            ],
        
            // ── MG-DM-05 ──────────────────────────────────────────────────
            [
                'hub_id'        => $hubId,
                'created_by'    => $adminId,
                'topic_slug'    => 'digital_marketing',
                'topic_name_en' => 'Digital Marketing',
                'topic_name_ar' => 'التسويق الرقمي',
                'layer'         => 'generic',
                'difficulty'    => 'intermediate',
                'status'        => 'published',
                'published_at'  => $now,
                'sort_order'    => 205,
                'read_time_minutes' => 12,
        
                'title_en'    => 'The SEO That Never Delivered',
                'subtitle_en' => 'Understanding what SEO actually is — and why most agencies sell the wrong version of it',
                'situation_en' => "You are a marketing manager at **Green Shield Pest Control**, a Cairo-based pest control and fumigation company serving residential and commercial clients. Eighteen months ago, the company hired a digital agency for SEO services at EGP 6,500/month (EGP 117,000 total investment).\n\nThe agency's monthly reports consistently showed:\n- 'Domain Authority improved from 12 to 31'\n- '847 new backlinks created'\n- 'Website appears on page 2 for 14 keywords'\n- '23 blog articles published'\n\nBut when you search Google for 'pest control Cairo' or 'شركة رش مبيدات القاهرة', Green Shield does not appear on page 1. The phone still rings primarily from word-of-mouth. The agency insists the results are coming — it just takes time.\n\nYou are not convinced. You pull up Google Search Console for the first time.",
                'challenge_questions_en' => json_encode([
                    "Domain Authority, backlinks, and blog articles are the agency's key metrics. Why might these be the wrong metrics for a local service business like Green Shield?",
                    "What should Google Search Console be showing for a pest control company in Cairo — and what keyword types are most valuable for this business?",
                    "What is the difference between general SEO and local SEO — and which one does Green Shield actually need?",
                    "Design a 6-month local SEO action plan for Green Shield that prioritises ranking for the searches that generate phone calls.",
                ]),
                'solution_en' => "## SEO: The Right Strategy for the Right Business Type\n\n### Why the Agency's Metrics Are Wrong for Green Shield\n\nDomain Authority, backlinks, and long-form blog content are the right metrics for — a national e-commerce store, a media publication, a SaaS company trying to rank for informational terms.\n\nGreen Shield is a **local service business**. Its potential customers search for:\n- 'pest control Cairo'\n- 'fumigation company near me'\n- 'رش مبيدات الزمالك'\n- 'cockroach treatment Heliopolis'\n\nThese are **local intent searches** — the user wants a phone number and a same-week appointment, not a blog post about the lifecycle of the German cockroach.\n\n### What Google Search Console Should Show\n\nFor Green Shield to succeed in local search, GSC should show:\n- Impressions for 'pest control [Cairo neighbourhood]' queries\n- Clicks from mobile users (80%+ of local service searches happen on mobile)\n- Click-through rates for the company's name + service queries\n- Position tracking for the top 20 local keywords\n\nThe agency's blog content is likely ranking for zero commercial-intent queries because blog articles about pest control attract people researching the topic — not people ready to book a service.\n\n### General SEO vs. Local SEO\n\n| General SEO | Local SEO |\n|---|---|\n| Target: National/global audience | Target: People in a specific city/area |\n| Content-heavy — blog, guides | Profile-heavy — Google Business Profile |\n| Backlinks from authority sites | Citations (consistent NAP data across directories) |\n| Takes 12–24 months to see results | Can see results in 4–8 weeks |\n| Right for: e-commerce, media, SaaS | Right for: local services, restaurants, clinics |\n\n### 6-Month Local SEO Action Plan\n\n**Month 1 — Google Business Profile (GBP) Optimisation**\nThis is the single highest-impact action for a local service business.\n- Claim and verify the GBP listing if not done\n- Complete every field: service areas, hours, services offered, description in Arabic and English\n- Upload 20+ professional photos of the team, equipment, before/after treatments\n- Add all services as separate GBP service entries with pricing ranges\n- Set up automated SMS asking every completed job for a Google review\n\n**Month 2 — Local Keyword Targeting**\n- Build a list of 50 local keywords: service + neighbourhood combinations\n- 'pest control Maadi', 'fumigation Heliopolis', 'رش مبيدات المعادي'\n- Create one dedicated service page for each major Cairo district served\n- Each page: service description, local signals (neighbourhood name, landmarks), customer review quotes, phone number prominent\n\n**Month 3 — Citation Building**\nEnsure Green Shield's Name, Address, Phone (NAP) is identical across:\n- Yandex Maps, Google Maps, Foursquare\n- Egyptian business directories (Kompass Egypt, Yellow Pages Egypt)\n- Yelp, Trustpilot (if available)\n\n**Months 4–6 — Review Velocity**\n- Target: 5 new Google reviews per week\n- Process: every completed job → WhatsApp message with Google review link\n- Respond to every review (Google rewards review engagement with higher rankings)\n- Target: 50+ reviews with 4.5+ average by end of Month 6\n\n### Measuring the Right Things\n\n| Metric | Monthly Target |\n|---|---|\n| GBP profile views | 500+ |\n| Phone calls from GBP | 80+ |\n| Google reviews added | 20+ |\n| Ranking for top 10 local keywords | Page 1 |\n| Organic search calls vs. 6 months ago | +40% |",
        
                'bigger_lesson_en' => "**SEO is not one strategy — it is a family of strategies, each suited to a different type of business.**\n\nThe agency sold Green Shield the SEO strategy for a media company — high DA, backlinks, content volume. What Green Shield needed was local SEO — Google Business Profile, local keywords, and review velocity. Always match the SEO strategy to the business model and the customer's search intent.",
        
                'skills_taught_en' => json_encode([
                    'Local SEO vs. general SEO strategy',
                    'Google Business Profile optimisation',
                    'Local keyword research and targeting',
                    'Review strategy for local service businesses',
                    'SEO metric selection for business type',
                ]),
        
                'title_ar'    => 'الـSEO الذي لم يُسفر عن نتائج',
                'subtitle_ar' => 'فهم ما هو الـSEO فعلاً — ولماذا تبيع معظم الوكالات النسخة الخاطئة منه',
                'situation_ar' => "أنت مدير تسويق في **جرين شيلد لمكافحة الحشرات**، شركة مقرها القاهرة تخدم العملاء السكنيين والتجاريين. قبل 18 شهراً، استعانت الشركة بوكالة رقمية لخدمات الـSEO بـ6,500 جنيه/شهر (إجمالي 117,000 جنيه).\n\nتقارير الوكالة الشهرية تُظهر باستمرار:\n- 'تحسنت Domain Authority من 12 إلى 31'\n- '847 رابط خلفي جديد'\n- 'الموقع يظهر في الصفحة الثانية لـ14 كلمة'\n- '23 مقال مدونة نُشر'\n\nلكن حين تبحث في جوجل عن 'pest control Cairo' أو 'شركة رش مبيدات القاهرة'، لا تظهر جرين شيلد في الصفحة الأولى.",
                'challenge_questions_ar' => json_encode([
                    "Domain Authority والروابط الخلفية ومقالات المدونة هي المقاييس الرئيسية للوكالة. لماذا قد تكون هذه المقاييس الخاطئة لشركة خدمات محلية؟",
                    "ما الذي يجب أن تُظهره Google Search Console لشركة مكافحة حشرات في القاهرة؟",
                    "ما الفرق بين الـSEO العام والـSEO المحلي — وأيهما تحتاجه جرين شيلد فعلاً؟",
                    "صمم خطة عمل SEO محلي لـ6 أشهر لجرين شيلد تُعطي الأولوية للتصنيف عند عمليات البحث التي تولّد مكالمات هاتفية.",
                ]),
                'solution_ar' => "## الـSEO: الاستراتيجية الصحيحة لنوع الأعمال الصحيح\n\n### لماذا مقاييس الوكالة خاطئة لجرين شيلد\n\nالـDomain Authority والروابط الخلفية والمحتوى الطويل مناسبة لـ: متجر إلكتروني وطني، منشور إعلامي، شركة SaaS.\n\nجرين شيلد **شركة خدمات محلية**. عملاؤها يبحثون عن:\n- 'pest control Maadi'\n- 'رش مبيدات الزمالك'\n\nهذه **عمليات بحث بنية محلية** — المستخدم يريد رقم هاتف وموعداً هذا الأسبوع، لا مقالاً عن دورة حياة الصرصار.\n\n### خطة عمل SEO المحلي 6 أشهر\n\n**الشهر 1:** تحسين ملف Google Business Profile — أعلى أثراً\n**الشهر 2:** استهداف كلمات رئيسية محلية (50 مزيج خدمة + حي)\n**الشهر 3:** بناء الاستشهادات (NAP متسق عبر الدلائل)\n**الأشهر 4-6:** سرعة التقييمات — هدف 5 تقييمات/أسبوع، 50+ تقييم بنهاية الشهر 6",
        
                'bigger_lesson_ar' => "**الـSEO ليس استراتيجية واحدة — بل عائلة من الاستراتيجيات، كل منها مناسبة لنوع مختلف من الأعمال.**\n\nباعت الوكالة لجرين شيلد استراتيجية SEO لشركة إعلام. ما احتاجته كان SEO محلياً — Google Business Profile وكلمات محلية وسرعة تقييمات. دائماً طابق استراتيجية الـSEO مع نموذج الأعمال ونية بحث العميل.",
        
                'skills_taught_ar' => json_encode([
                    'الـSEO المحلي مقابل العام',
                    'تحسين ملف Google Business Profile',
                    'البحث عن الكلمات الرئيسية المحلية واستهدافها',
                    'استراتيجية التقييمات لشركات الخدمات المحلية',
                    'اختيار مقاييس الـSEO حسب نوع الأعمال',
                ]),
                'created_at' => $now,
                'updated_at' => $now,
            ],
        
            // ── MG-DM-06 ──────────────────────────────────────────────────
            [
                'hub_id'        => $hubId,
                'created_by'    => $adminId,
                'topic_slug'    => 'digital_marketing',
                'topic_name_en' => 'Digital Marketing',
                'topic_name_ar' => 'التسويق الرقمي',
                'layer'         => 'generic',
                'difficulty'    => 'advanced',
                'status'        => 'published',
                'published_at'  => $now,
                'sort_order'    => 206,
                'read_time_minutes' => 14,
        
                'title_en'    => 'The D2C Brand That Hit a Ceiling',
                'subtitle_en' => 'Scaling a direct-to-consumer brand beyond the first wave of growth',
                'situation_en' => "You are the head of marketing at **Bare Essentials**, an Egyptian D2C skincare brand that launched 2 years ago selling exclusively through its own website and Instagram Shop. The brand grew fast in Year 1:\n\n| Year 1 Metrics | Result |\n|---|---|\n| Revenue | EGP 4.2M |\n| Average order value | EGP 380 |\n| Customer acquisition cost (CAC) | EGP 65 |\n| Return customer rate | 41% |\n| Instagram followers | 89,000 |\n\nYear 2 has been painful:\n\n| Year 2 Metrics | Result |\n|---|---|\n| Revenue | EGP 4.8M (+14%) |\n| Average order value | EGP 380 (flat) |\n| CAC | EGP 142 (up 118%) |\n| Return customer rate | 39% (declining) |\n| Instagram followers | 147,000 (+65%) |\n| Ad spend as % of revenue | 31% (was 18%) |\n\nThe brand is growing in followers but stagnating in revenue and bleeding margin. The board is asking whether D2C is still the right model — or whether Bare Essentials needs to enter retail.",
                'challenge_questions_en' => json_encode([
                    "CAC has risen from EGP 65 to EGP 142 in one year. What are the three most likely causes of this — and which can marketing address vs. which are structural?",
                    "The brand's Instagram following grew 65% but revenue only grew 14%. What does this disconnect tell you about the quality of the growth?",
                    "The board is proposing entering modern trade retail (pharmacies, beauty stores). What are the three strategic risks of entering retail for a brand built on D2C identity?",
                    "Design a growth strategy that does not abandon D2C but also does not rely on paid social alone — covering at least 4 distinct growth levers.",
                ]),
                'solution_en' => "## D2C at the Growth Ceiling: The Common Pattern\n\nNearly every successful D2C brand in Egypt and globally hits the same ceiling around Year 2–3. The mechanics are always similar: the brand exhausted its 'warm' audience (people who already cared about the category) and now must pay to reach cold audiences — who cost more to convert.\n\n### The Three Causes of Rising CAC\n\n**Cause 1 — Audience saturation on Meta**\nAfter reaching most of the engaged potential buyers in Egypt's skincare market on Facebook/Instagram, the algorithm must reach less interested people — who convert at lower rates, pushing up CAC.\n\n**Cause 2 — Increased competition bidding on the same audiences**\nAs the D2C skincare category grew in Egypt, more brands entered and competed for the same audiences, driving up CPMs (cost per thousand impressions) and therefore CAC.\n\n**Cause 3 — The best customers were acquired first**\nIn any growth phase, the easiest customers — most engaged with the category, highest purchase intent — are acquired first. Later customers require more touchpoints and more ad spend to convert.\n\nMarketing can address Causes 1 and 3 through channel diversification. Cause 2 is structural — it requires differentiated positioning, not just more ad spend.\n\n### What Follower Growth Without Revenue Growth Signals\n\nInstagram followers grew 65% but revenue grew 14%. Three possible readings:\n1. New followers are not the target buyer (wrong demographic acquired through giveaways or viral content)\n2. The brand's content entertains but does not convert — brand love without purchase intent\n3. The purchase funnel from Instagram to website has friction that needs CRO work\n\n### The Risks of Entering Retail\n\n**Risk 1 — Margin erosion**\nModern trade retailers (pharmacies, beauty chains) demand 30–45% margins plus listing fees. A brand running at 55% gross margin D2C might find itself at 15–25% in retail — fundamentally changing the economics.\n\n**Risk 2 — Brand positioning dilution**\nRetail shelves commodity-ise brands. Next to 40 other skincare products, Bare Essentials loses the curated, premium feeling of its D2C experience. The brand that felt special in its own channel feels like just another product in a pharmacy.\n\n**Risk 3 — Channel conflict**\nIf Bare Essentials offers the same products in retail at the same price, D2C becomes inconvenient (why order online when I can buy it at the pharmacy?). If retail is cheaper, D2C customers feel penalised for loyalty.\n\n### A Multi-Lever Growth Strategy\n\n**Lever 1 — Retention and LTV maximisation**\nThe cheapest customer is one already acquired. A 5% improvement in retention is worth more than a 15% improvement in acquisition. Actions:\n- Launch a subscription programme ('Never run out of your serum')\n- Personalised reorder reminders at predicted replenishment dates\n- Loyalty programme with product rewards, not just discounts\n\n**Lever 2 — Email and SMS owned channels**\nReduce dependence on paid social by building direct communication.\n- Every customer becomes an email subscriber with a structured lifecycle flow\n- Target: 40% of repeat orders driven by email/SMS (currently near zero)\n\n**Lever 3 — Community and UGC**\nUser-generated content (customer before/afters, reviews, tutorials) converts at 4× the rate of brand-produced content and costs a fraction to produce.\n- Build a structured UGC programme: every package insert requests a photo review\n- Feature real customers in ads (with permission) — performance consistently beats studio photography\n\n**Lever 4 — Selective retail entry (not mass retail)**\nEnter 3–5 premium specialty beauty retailers only (not pharmacies). This preserves premium positioning, tests retail execution, and creates new discovery without the full retail margin sacrifice.",
        
                'bigger_lesson_en' => "**Paid social can launch a D2C brand. It cannot sustainably scale one.**\n\nThe brands that succeed long-term build owned audiences (email, SMS), community-driven growth (UGC, word-of-mouth), and retention engines (subscriptions, loyalty) alongside paid acquisition. The goal is a portfolio of growth channels where no single one accounts for more than 40% of new customer acquisition.",
        
                'skills_taught_en' => json_encode([
                    'D2C growth strategy and channel economics',
                    'CAC and LTV analysis',
                    'Multi-channel acquisition strategy',
                    'Retail entry risk assessment for D2C brands',
                    'Customer retention and lifecycle marketing',
                ]),
        
                'title_ar'    => 'علامة D2C وصلت إلى سقف النمو',
                'subtitle_ar' => 'توسيع علامة البيع المباشر للمستهلك بعد موجة النمو الأولى',
                'situation_ar' => "أنت رئيس التسويق في **بير إيسنشيالز**، علامة مصرية D2C للعناية بالبشرة أُطلقت قبل عامين تبيع حصرياً من خلال موقعها ومتجر إنستغرام.\n\n| مقاييس السنة الأولى | النتيجة |\n|---|---|\n| الإيرادات | 4.2 مليون جنيه |\n| تكلفة استقطاب العميل (CAC) | 65 جنيه |\n| معدل العملاء العائدين | 41% |\n\n| مقاييس السنة الثانية | النتيجة |\n|---|---|\n| الإيرادات | 4.8 مليون جنيه (+14%) |\n| CAC | 142 جنيه (+118%) |\n| الإنفاق الإعلاني كـ% من الإيرادات | 31% (كان 18%) |\n| متابعو إنستغرام | 147,000 (+65%) |\n\nالعلامة تنمو في المتابعين لكن تركد في الإيرادات وتنزف هامشها. المجلس يسأل: هل D2C لا يزال النموذج الصحيح؟",
                'challenge_questions_ar' => json_encode([
                    "ارتفع CAC من 65 إلى 142 جنيه في عام. ما الأسباب الثلاثة الأكثر احتمالاً — وأيها يمكن للتسويق معالجته؟",
                    "نمت متابعة إنستغرام 65% لكن الإيرادات نمت 14% فقط. ما الذي يخبرك هذا الانفصال عن جودة النمو؟",
                    "يقترح المجلس دخول التجزئة الحديثة. ما المخاطر الاستراتيجية الثلاثة لدخول التجزئة لعلامة مبنية على هوية D2C؟",
                    "صمم استراتيجية نمو لا تتخلى عن D2C ولا تعتمد على الإعلانات الاجتماعية وحدها — تغطي 4 روافع نمو مميزة على الأقل.",
                ]),
                'solution_ar' => "## D2C عند سقف النمو: النمط الشائع\n\nتقريباً كل علامة D2C ناجحة في مصر وعالمياً تصطدم بنفس السقف في السنة 2-3.\n\n### أسباب ارتفاع CAC\n\n1. **تشبع الجمهور على ميتا** — استُنفد المشترون المتفاعلون المحتملون، الخوارزمية تصل لأناس أقل اهتماماً\n2. **منافسة متزايدة على نفس الجمهور** — CPM أعلى إذن CAC أعلى\n3. **أفضل العملاء استُقطبوا أولاً** — العملاء اللاحقون يحتاجون نقاط تماس أكثر\n\n### استراتيجية نمو متعددة الروافع\n\n**الرافعة 1:** الاحتفاظ وتعظيم LTV — اشتراك، تذكيرات إعادة طلب مخصصة\n**الرافعة 2:** القنوات المملوكة (بريد + SMS) — هدف 40% من الطلبات المتكررة\n**الرافعة 3:** المجتمع والمحتوى من المستخدمين — يحوّل 4 أضعاف المحتوى الرسمي\n**الرافعة 4:** دخول تجزئة انتقائي — 3-5 متاجر تجميل متخصصة راقية فقط",
        
                'bigger_lesson_ar' => "**الإعلانات الاجتماعية يمكنها إطلاق علامة D2C. لا يمكنها توسيعها بشكل مستدام.**\n\nالعلامات الناجحة على المدى البعيد تبني جماهير مملوكة وقنوات مدفوعة بالمجتمع ومحركات احتفاظ جنباً إلى جنب مع الاستقطاب المدفوع.",
        
                'skills_taught_ar' => json_encode([
                    'استراتيجية نمو D2C واقتصاديات القنوات',
                    'تحليل CAC وLTV',
                    'استراتيجية استقطاب متعددة القنوات',
                    'تقييم مخاطر دخول التجزئة لعلامات D2C',
                    'الاحتفاظ بالعملاء وتسويق دورة الحياة',
                ]),
                'created_at' => $now,
                'updated_at' => $now,
            ],
        
            // ── MG-DM-07 ──────────────────────────────────────────────────
            [
                'hub_id'        => $hubId,
                'created_by'    => $adminId,
                'topic_slug'    => 'digital_marketing',
                'topic_name_en' => 'Digital Marketing',
                'topic_name_ar' => 'التسويق الرقمي',
                'layer'         => 'generic',
                'difficulty'    => 'advanced',
                'status'        => 'published',
                'published_at'  => $now,
                'sort_order'    => 207,
                'read_time_minutes' => 14,
        
                'title_en'    => 'The Attribution Problem',
                'subtitle_en' => 'When you cannot tell which marketing channel is actually driving sales',
                'situation_en' => "You are the digital marketing director at **Helios Solar**, an Egyptian company selling and installing residential solar panels. The company spends EGP 85,000/month across four digital channels.\n\nEach channel's self-reported performance:\n\n| Channel | Monthly Spend | Reported Sales | Cost/Sale |\n|---|---|---|---|\n| Google Ads | EGP 30,000 | 18 sales | EGP 1,667 |\n| Facebook Ads | EGP 25,000 | 22 sales | EGP 1,136 |\n| TikTok Ads | EGP 15,000 | 11 sales | EGP 1,364 |\n| Email Marketing | EGP 15,000 | 14 sales | EGP 1,071 |\n| **Total** | **EGP 85,000** | **65 sales** | — |\n\nBut Helios's CRM shows only 38 total sales last month. The numbers don't add up.\n\nEach platform is claiming credit for the same sales — a phenomenon called **attribution overlap**. The board needs to know where to actually invest more budget and where to cut.",
                'challenge_questions_en' => json_encode([
                    "Why do the four platforms collectively report 65 sales when the CRM shows only 38? Explain the attribution problem in simple terms.",
                    "What is 'last-click attribution' and why does it systematically undervalue awareness channels like TikTok and overvalue conversion channels like Google Search?",
                    "Design a practical attribution framework for Helios Solar that works with a limited analytics setup (no expensive tools required).",
                    "Based on the attribution problem, make a budget reallocation recommendation — which channels should get more, which should get less — and explain the logic.",
                ]),
                'solution_en' => "## The Attribution Problem: Every Channel Claims the Win\n\n### Why the Numbers Don't Add Up\n\nHere is a typical Helios Solar customer journey:\n1. Sees a TikTok video about solar savings → curious\n2. Googles 'solar panel cost Egypt' → clicks a Google Ad → visits website\n3. Doesn't convert → sees a Facebook retargeting ad → visits again\n4. Receives an email about a promotion → clicks and submits a quote request\n5. Sales team closes the deal\n\n**Who gets credit for this sale?**\n- TikTok says: *'I started the journey — that's my conversion'\n- Google says: *'I brought them to the site — that's my conversion'\n- Facebook says: *'I brought them back — that's my conversion'\n- Email says: *'I triggered the final action — that's my conversion'\n\nAll four platforms track the user via their own pixels and claim the conversion. **The CRM records one sale. The platforms collectively record four.**\n\n### Last-Click Attribution and Its Distortion\n\n**Last-click attribution** gives 100% of the credit to the final touchpoint before conversion. In practice:\n- Google Search captures most 'last clicks' because people Google a brand name or product just before buying\n- Awareness channels (TikTok, top-of-funnel Facebook) never appear as the last click — even when they started the entire journey\n\nResult: Last-click attribution makes Google look incredible, makes TikTok look like a waste, and causes marketers to cut awareness spend — which eventually starves the funnel of new prospects.\n\n### A Practical Attribution Framework for Helios Solar\n\n**Method 1 — CRM-based source tracking (simplest)**\nAdd a source question to every quote request form and train the sales team to ask: *'Where did you first hear about us?'*\nThis captures self-reported attribution — imperfect but directionally accurate.\n\nAsk: *'Where did you hear about us?'* AND *'What made you decide to contact us?'* — these two questions separate awareness attribution from conversion attribution.\n\n**Method 2 — UTM parameters + GA4 (intermediate)**\nTag every ad with UTM parameters (source, medium, campaign). Use GA4's path analysis to see the actual multi-step journeys customers take before converting.\n\n**Method 3 — Holdout testing (most accurate)**\nTurn off one channel for one month (rotate which channel) and measure total sales impact. If TikTok is turned off and overall sales drop 20% even though TikTok's last-click contribution was only 17%, TikTok is creating value that last-click attribution is hiding.\n\n### Budget Reallocation Logic\n\nBefore cutting any channel based on platform-reported performance, run the holdout test. The likely finding for a considered purchase like solar:\n\n- **Google Search** — defend the budget. High-intent searches from people ready to buy are most efficiently captured by search ads.\n- **Facebook Retargeting** — this is a bridge channel. If the top-of-funnel is healthy, retargeting is very efficient. If top-of-funnel is cut, retargeting loses its audience.\n- **TikTok** — do not cut based on last-click data alone. Solar is a considered, high-ticket purchase. TikTok creates awareness and consideration that takes weeks to appear as a Google search. Test the holdout before deciding.\n- **Email** — highest reported ROI but built on an existing lead database. It can never be the primary acquisition channel — it is a conversion and retention channel.",
        
                'bigger_lesson_en' => "**Every platform claims to win every sale. Only your CRM knows the truth.**\n\nMarketing attribution is one of the most complex problems in digital marketing. The companies that solve it — even imperfectly — make systematically better budget decisions than companies that trust each platform's self-reported numbers. Build a CRM, ask customers where they heard about you, and never optimise for one channel in isolation.",
        
                'skills_taught_en' => json_encode([
                    'Marketing attribution models',
                    'Multi-touch attribution vs. last-click',
                    'CRM-based attribution tracking',
                    'Holdout testing methodology',
                    'Cross-channel budget allocation',
                ]),
        
                'title_ar'    => 'مشكلة النسب',
                'subtitle_ar' => 'حين لا تعرف أي قناة تسويقية تدفع المبيعات فعلاً',
                'situation_ar' => "أنت مدير التسويق الرقمي في **هيليوس سولار**، شركة مصرية تبيع وتركّب ألواح شمسية سكنية. تنفق الشركة 85,000 جنيه/شهر على أربع قنوات رقمية.\n\nكل قناة تُبلّغ عن أدائها:\n\n| القناة | الإنفاق | المبيعات المُبلَّغة |\n|---|---|---|\n| إعلانات جوجل | 30,000 | 18 |\n| إعلانات فيسبوك | 25,000 | 22 |\n| إعلانات تيك توك | 15,000 | 11 |\n| البريد الإلكتروني | 15,000 | 14 |\n| **الإجمالي** | **85,000** | **65** |\n\nلكن CRM الشركة يُظهر 38 مبيعة فقط الشهر الماضي. الأرقام لا تتطابق.\n\nكل منصة تطالب بالفضل في نفس المبيعات — ظاهرة **تداخل النسب**.",
                'challenge_questions_ar' => json_encode([
                    "لماذا تُبلّغ المنصات الأربع مجتمعةً عن 65 مبيعة بينما يُظهر الـCRM 38 فقط؟ اشرح مشكلة النسب ببساطة.",
                    "ما 'نسب آخر نقرة' ولماذا تُقلّل بشكل ممنهج من قيمة قنوات الوعي مثل تيك توك وتُبالغ في قيمة قنوات التحويل؟",
                    "صمم إطار نسب عملي لهيليوس سولار يعمل مع إعداد تحليلات محدود.",
                    "بناءً على مشكلة النسب، قدم توصية لإعادة توزيع الميزانية — أي قنوات تحصل على أكثر وأيها أقل — واشرح المنطق.",
                ]),
                'solution_ar' => "## مشكلة النسب: كل قناة تطالب بالفوز\n\n### لماذا لا تتطابق الأرقام\n\nرحلة عميل هيليوس النموذجية:\n1. يرى فيديو تيك توك عن توفير الطاقة الشمسية ← فضولي\n2. يبحث 'تكلفة ألواح شمسية مصر' ← ينقر إعلان جوجل\n3. لا يتحول ← يرى إعلان فيسبوك لإعادة الاستهداف\n4. يستقبل بريداً عن عرض ← يقدم طلب عرض سعر\n\n**من يحصل على الفضل؟** الجميع يطالبون بنفس البيع.\n\n### إطار نسب عملي\n\n**الطريقة 1:** تتبع المصدر في الـCRM + سؤال 'كيف سمعت عنا؟'\n**الطريقة 2:** معاملات UTM + تحليل مسار GA4\n**الطريقة 3:** اختبار الإيقاف — أوقف قناة واحدة شهراً وقس الأثر\n\n### منطق إعادة توزيع الميزانية\n\nلا تخفّض أي قناة بناءً على بيانات المنصة وحدها. أجرِ اختبار الإيقاف أولاً. للشراء المدروس كالطاقة الشمسية، تيك توك تُنشئ وعياً يستغرق أسابيع للظهور كبحث على جوجل.",
        
                'bigger_lesson_ar' => "**كل منصة تدّعي الفوز بكل بيع. الـCRM وحده يعرف الحقيقة.**\n\nنسب التسويق من أكثر المشكلات تعقيداً في التسويق الرقمي. الشركات التي تحلها — ولو بشكل غير كامل — تتخذ قرارات ميزانية أفضل بشكل ممنهج من تلك التي تثق بأرقام كل منصة عن نفسها.",
        
                'skills_taught_ar' => json_encode([
                    'نماذج نسب التسويق',
                    'النسب متعددة اللمسات مقابل آخر نقرة',
                    'تتبع النسب بالـCRM',
                    'منهجية اختبار الإيقاف',
                    'توزيع الميزانية عبر القنوات',
                ]),
                'created_at' => $now,
                'updated_at' => $now,
            ],
        
            // ══════════════════════════════════════════════════════════════
            //  TOPIC 3 — MARKET RESEARCH (6 cases: MG-MR-01 to MG-MR-06)
            // ══════════════════════════════════════════════════════════════
        
            // ── MG-MR-01 ──────────────────────────────────────────────────
            [
                'hub_id'        => $hubId,
                'created_by'    => $adminId,
                'topic_slug'    => 'market_research',
                'topic_name_en' => 'Market Research',
                'topic_name_ar' => 'أبحاث السوق',
                'layer'         => 'generic',
                'difficulty'    => 'beginner',
                'status'        => 'published',
                'published_at'  => $now,
                'sort_order'    => 301,
                'read_time_minutes' => 10,
        
                'title_en'    => 'The Survey That Lied',
                'subtitle_en' => 'Why people say one thing in research and do another at the shelf',
                'situation_en' => "You are a junior marketing researcher at **Delta Consumer Insights**, an Egyptian market research firm. A client — a snack food company — conducted a consumer survey before launching a new 'healthy' baked chips product. The survey results were very promising:\n\n- 78% of respondents said they would 'definitely' or 'probably' buy a healthier chips alternative\n- 82% said health is 'very important' when choosing snacks\n- 71% said they would pay up to 20% more for a healthier option\n- Average desirability score: 7.8/10\n\nBased on these results, the company launched the product nationally. Six months later:\n- Market share: 1.2% (target was 8%)\n- Repeat purchase rate: 18% (industry average for snacks: 45%)\n- Most common exit interview feedback: 'I preferred the taste of the regular version'\n\nThe CEO asks: *'Our research said 78% would buy it. Why are only 1.2% actually buying it?'*",
                'challenge_questions_en' => json_encode([
                    "What is 'social desirability bias' and how does it explain the gap between the 78% survey intention and the 1.2% market share?",
                    "The survey asked about 'healthy eating' intentions. What is the problem with measuring stated preferences vs. revealed preferences?",
                    "List three research methods that would have predicted actual purchase behaviour more accurately than a stated-intention survey.",
                    "How should the company have designed the research to get commercially useful data — before spending the budget on a national launch?",
                ]),
                'solution_en' => "## The Intention-Action Gap: The Most Common Research Trap\n\n### Social Desirability Bias\n\nWhen people are asked questions about health, the environment, social responsibility, or ethics, they systematically answer in ways that make them look good — even when their actual behaviour is different.\n\nAsking 'Do you care about healthy eating?' in a survey is effectively asking 'Are you a good person?' Nobody says no. The 78% who said they'd 'probably buy' a healthy snack were not lying — they genuinely believe they would. But in the snack aisle, surrounded by familiar, delicious alternatives, the rational health intention loses to the emotional taste preference every time.\n\nThis is called the **intention-action gap** — the gap between what people say they will do and what they actually do.\n\n### Stated vs. Revealed Preferences\n\n| Stated Preference | Revealed Preference |\n|---|---|\n| What people say they want in surveys | What people actually choose when spending real money |\n| Influenced by social desirability | Influenced by actual taste, habit, price, availability |\n| Often optimistic and aspirational | Honest and behavioural |\n| Easy and cheap to collect | Requires real purchase situations |\n\nFor food products especially, taste almost always defeats health claims in a real purchase moment. The survey measured the aspiration. It should have measured the choice.\n\n### Three Methods That Would Have Predicted Better\n\n**Method 1 — Blind taste test + price test**\nGive consumers both products (regular and healthy) without labels. Measure preference on taste alone. Then reveal the health benefits and price difference. Measure willingness to switch. This separates the 'taste reality' from the 'health aspiration.'\n\n**Method 2 — Simulated purchase test**\nPlace both products on a mock shelf at different price points. Give consumers a fixed budget and let them 'shop.' Revealed choice is dramatically more predictive than stated intention.\n\n**Method 3 — In-home usage test + repeat purchase measurement**\nGive 200 consumers the product for free for 2 weeks. Measure: spontaneous repeat purchase at the end of the trial (when they have to pay for it). A 45%+ repeat rate in trial predicts commercial success.\n\n### How the Research Should Have Been Designed\n\n**Stage 1 — Qualitative (focus groups):** Understand what 'healthy snack' means to the target consumer. Is it low fat, low sugar, baked vs. fried, natural ingredients? Define the category benefit before testing the product.\n\n**Stage 2 — Product test (blind):** Measure taste preference vs. regular alternatives. If taste loses, no amount of health messaging will sustain repeat purchase.\n\n**Stage 3 — Concept + price test (sequential monadic):** Show the concept with health claims and a price range. Measure purchase intent. At what price does intent drop below 30%? That is your maximum launch price.\n\n**Stage 4 — In-home usage trial:** The most predictive tool. Real consumption in real environments with real purchase decisions at the end.\n\nThe company skipped Stages 2–4 and launched nationally on Stage 1 data alone. EGP millions spent on distribution, packaging, and marketing for a product that taste-testing would have caught in week two.",
        
                'bigger_lesson_en' => "**Consumer research is not asking people what they want. It is designing situations that reveal what they actually choose.**\n\nThe gap between aspiration and behaviour is largest in categories where people have values they aspire to (health, sustainability, ethics) but habits they actually live by (convenience, taste, price). Great market research measures the behaviour, not the aspiration.",
        
                'skills_taught_en' => json_encode([
                    'Social desirability bias in research',
                    'Stated vs. revealed preference methodology',
                    'Consumer product testing methods',
                    'Research design for commercial decisions',
                    'Intention-action gap understanding',
                ]),
        
                'title_ar'    => 'الاستطلاع الذي كذب',
                'subtitle_ar' => 'لماذا يقول الناس شيئاً في الأبحاث ويفعلون غيره عند الرف',
                'situation_ar' => "أنت باحث تسويق مبتدئ في **دلتا للاستشارات الاستهلاكية**. عميل — شركة مأكولات خفيفة — أجرى استطلاعاً قبل إطلاق رقائق مخبوزة 'صحية' جديدة:\n\n- 78% قالوا إنهم 'بالتأكيد' أو 'على الأرجح' سيشترون بديلاً صحياً\n- 82% قالوا الصحة 'مهمة جداً' عند اختيار المقبلات\n- 71% قالوا إنهم يدفعون حتى 20% أكثر لخيار صحي\n\nبناءً على هذه النتائج، أطلقت الشركة المنتج وطنياً. بعد ستة أشهر:\n- الحصة السوقية: 1.2% (الهدف كان 8%)\n- معدل الشراء المتكرر: 18% (المتوسط الصناعي: 45%)\n\nيسأل الرئيس: *'بحثنا قال 78% سيشترونه. لماذا 1.2% فقط يشترونه؟'*",
                'challenge_questions_ar' => json_encode([
                    "ما 'تحيز المرغوبية الاجتماعية' وكيف يفسر الفجوة بين نية الشراء 78% والحصة السوقية 1.2%؟",
                    "ما مشكلة قياس التفضيلات المُعلنة مقابل التفضيلات المكشوفة؟",
                    "أدرج ثلاث طرق بحثية كانت ستتنبأ بالسلوك الشرائي الفعلي بدقة أكبر.",
                    "كيف كان يجب على الشركة تصميم البحث للحصول على بيانات مفيدة تجارياً قبل الإنفاق على إطلاق وطني؟",
                ]),
                'solution_ar' => "## فجوة النية والفعل: أكثر أفخاخ الأبحاث شيوعاً\n\n### تحيز المرغوبية الاجتماعية\n\nحين يُسأل الناس عن الصحة أو البيئة أو الأخلاق، يجيبون بشكل ممنهج بطرق تجعلهم يبدون جيدين — حتى حين يختلف سلوكهم الفعلي.\n\nهذه **فجوة النية-الفعل** — الفجوة بين ما يقول الناس إنهم سيفعلونه وما يفعلونه فعلاً.\n\n### ثلاث طرق أكثر دقة في التنبؤ\n\n1. **اختبار التذوق الأعمى + اختبار السعر** — بدون ملصقات، قس التفضيل بناءً على الطعم وحده\n2. **اختبار الشراء المحاكى** — منتجان على رف وهمي بأسعار مختلفة، ميزانية ثابتة للمستهلك\n3. **اختبار الاستخدام المنزلي + قياس الشراء المتكرر** — أعطِ 200 مستهلك المنتج مجاناً لأسبوعين، قس الشراء التلقائي عند نهاية التجربة",
        
                'bigger_lesson_ar' => "**أبحاث المستهلك ليست سؤال الناس عما يريدون. بل تصميم مواقف تكشف ما يختارونه فعلاً.**\n\nالفجوة بين الطموح والسلوك أكبر في الفئات التي يحمل فيها الناس قيماً يطمحون إليها (الصحة) لكنهم يعيشون بعادات فعلية (الطعم، السعر). أبحاث السوق العظيمة تقيس السلوك لا الطموح.",
        
                'skills_taught_ar' => json_encode([
                    'تحيز المرغوبية الاجتماعية في الأبحاث',
                    'منهجية التفضيل المُعلَن مقابل المكشوف',
                    'طرق اختبار المنتجات الاستهلاكية',
                    'تصميم البحث للقرارات التجارية',
                    'فهم فجوة النية والفعل',
                ]),
                'created_at' => $now,
                'updated_at' => $now,
            ],
        
        ];

        $cases = array_map(function ($case) use ($hubId, $adminId, $now) {
            return array_merge($case, [
                'hub_id'       => $hubId,
                'created_by'   => $adminId,
                'status'       => 'published',
                'published_at' => $now,
            ]);
        }, $cases);

        DB::table('cases')->insert($cases);
        $this->command->info('  ✅ 5 cases inserted (MG-DM-04 to MG-DM-07, MG-MR-01)');
    }
}
