<?php

namespace Database\Seeders;

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

/**
 * InPractice — Marketing Generic Cases [10/10]
 * Marketing Analytics & Reporting: MG-MA-02 to MG-MA-06
 *
 * Run: php artisan db:seed --class=MarketingGenericCaseSeeder_10
 */
class MarketingGenericCaseSeeder_10 extends Seeder
{
    public function run(): void
    {
        $this->command->info('📢 [10/10] Marketing Analytics (2-6) ...');

        $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-MA-02 ──────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'marketing_analytics',
        'topic_name_en' => 'Marketing Analytics & Reporting',
        'topic_name_ar' => 'تحليلات التسويق والتقارير',
        'layer'         => 'generic',
        'difficulty'    => 'beginner',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 802,
        'read_time_minutes' => 10,

        'title_en'    => 'The A/B Test That Proved Nothing',
        'subtitle_en' => 'Why most A/B tests are statistically meaningless — and how to run ones that are not',
        'situation_en' => "You are a digital marketing executive at **Cairo Health Insurance Brokers**, an Egyptian insurance intermediary. Your team ran an A/B test on your Google Ads headline:\n\n- Version A (control): 'Comprehensive Health Insurance Plans — Get a Free Quote Today'\n- Version B (variant): 'Health Insurance from EGP 299/month — Compare 12 Providers'\n\nResults after 8 days:\n\n| | Version A | Version B |\n|---|---|---|\n| Impressions | 3,200 | 3,180 |\n| Clicks | 128 | 156 |\n| CTR | 4.0% | 4.9% |\n| Leads submitted | 14 | 19 |\n\nYour manager declares: *'Version B wins by 22.5% CTR — let's switch everything to Version B immediately.'*\n\nYou have a feeling this conclusion is premature. You are right.",
        'challenge_questions_en' => json_encode([
            "What is statistical significance and why does it matter before declaring a winner in an A/B test?",
            "The test ran for 8 days with 3,200 impressions per variant. Is this enough data to conclude Version B is better? How would you calculate whether it is?",
            "Your manager wants to switch to Version B immediately. What risks does this create — and what would you recommend instead?",
            "Design a proper A/B test framework for future headline tests: how long to run, how much data to collect, what significance threshold to use, and how to interpret the results.",
        ]),
        'solution_en' => "## A/B Testing: The Science Behind the Simple Concept\n\n### Statistical Significance\n\nStatistical significance answers the question: *'How confident are we that the difference we see is real — and not just random chance?'*\n\nIf you flip a coin 10 times and get 7 heads, you cannot conclude the coin is unfair. The sample is too small. The same logic applies to A/B tests: a small sample means a small difference could be entirely due to chance.\n\nConvention: a test result is statistically significant when you are **95% confident** the difference is real (not random). This is expressed as p < 0.05.\n\n### Was 8 Days Enough?\n\nFor statistical significance at 95% confidence with these numbers:\n- Baseline CTR: 4.0%\n- Observed CTR difference: 0.9 percentage points (22.5% relative lift)\n- Sample size needed (for 80% statistical power at 95% confidence): approximately 13,000 impressions per variant\n\nThe test ran with 3,200 impressions per variant — 24% of what is needed. The result is **statistically underpowered**. There is a high probability the observed difference is random variation, not a real effect.\n\n**Practical check:** Use an online statistical significance calculator. Input:\n- Control: 128 clicks / 3,200 impressions\n- Variant: 156 clicks / 3,180 impressions\nResult: p ≈ 0.14. Not significant (p needs to be < 0.05 for 95% confidence).\n\n### The Risk of Switching Too Early\n\nIf the test result is due to random variation and Version B is not actually better:\n- You permanently switch to Version B\n- The 'better' performance was a statistical artefact\n- Future performance regresses to the same level as Version A\n- You lost the chance to find a genuinely superior variant\n\nThis is called a **false positive** — concluding there is an effect when there is none.\n\n### A Proper A/B Test Framework\n\n**Step 1 — Define success before the test starts**\nDecide: what metric are we testing? CTR? Conversion rate? Cost per lead? The primary metric must be decided before seeing any results — changing the metric after seeing partial results is called 'p-hacking.'\n\n**Step 2 — Calculate required sample size**\nUse a sample size calculator:\n- Baseline conversion rate (current CTR or conversion rate)\n- Minimum detectable effect (the smallest difference worth knowing about — e.g., 15% lift in CTR)\n- Statistical power: 80%\n- Significance level: 95%\n\n**Step 3 — Run the test for the required duration**\nFor Google Ads, the minimum is typically 2 full weeks (to capture weekly variations in user behaviour) and minimum 1,000 clicks per variant.\n\n**Step 4 — Read the results correctly**\n- Significant at p < 0.05: the variant is genuinely better with 95% confidence. Implement.\n- Not significant: the difference is within noise. Do not implement. Either continue the test or design a new one.\n- Winner confirmed: document the insight (what changed, why it likely worked) and apply the learning to future tests.",

        'bigger_lesson_en' => "**An A/B test with insufficient data is not a test — it is a coin flip with extra steps.**\n\nThe discipline of A/B testing is not in the technology — it is in the statistics. Running a test for 8 days and declaring a winner is as arbitrary as stopping after 3 flips of a coin. The most valuable thing a marketer can learn about testing is: patience is the most important variable.",

        'skills_taught_en' => json_encode([
            'Statistical significance in A/B testing',
            'Sample size calculation',
            'P-value interpretation',
            'A/B test framework design',
            'Avoiding false positives in marketing tests',
        ]),

        'title_ar'    => 'اختبار A/B الذي لم يُثبت شيئاً',
        'subtitle_ar' => 'لماذا معظم اختبارات A/B لا معنى إحصائياً لها — وكيف تُجري اختبارات ذات معنى',
        'situation_ar' => "أنت مسؤول تسويق رقمي في **وسطاء القاهرة للتأمين الصحي**. أجرى فريقك اختبار A/B على عنوان إعلان جوجل:\n\n- النسخة أ: 'خطط تأمين صحي شاملة — احصل على عرض سعر مجاني اليوم'\n- النسخة ب: 'تأمين صحي من 299 جنيه/شهر — قارن 12 مزوداً'\n\nبعد 8 أيام: النسخة ب حققت CTR 4.9% مقابل 4.0%.\n\nيُعلن مديرك: *'النسخة ب تفوز بـ22.5% — دعنا نحول كل شيء للنسخة ب فوراً.'*\n\nلديك شعور أن هذا الاستنتاج سابق لأوانه. أنت محق.",
        'challenge_questions_ar' => json_encode([
            "ما الأهمية الإحصائية ولماذا تهم قبل إعلان الفائز في اختبار A/B؟",
            "الاختبار استمر 8 أيام بـ3,200 ظهور لكل نسخة. هل هذا يكفي للاستنتاج بأن النسخة ب أفضل؟",
            "مديرك يريد التحول للنسخة ب فوراً. ما المخاطر التي يخلقها هذا؟",
            "صمم إطار اختبار A/B سليم: كم يستمر، كم من البيانات يجمع، ما عتبة الأهمية المستخدمة.",
        ]),
        'solution_ar' => "## اختبار A/B: العلم وراء المفهوم البسيط\n\n### الأهمية الإحصائية\n\nتجيب على: *'ما مستوى ثقتنا في أن الفرق الذي نراه حقيقي — لا مجرد صدفة؟'*\n\nالاصطلاح: النتيجة معنوية إحصائياً حين تكون **95% واثقاً** أن الفرق حقيقي (p < 0.05).\n\n### هل 8 أيام كافية؟\n\nللأهمية الإحصائية بمستوى ثقة 95%، الحجم المطلوب: ~13,000 ظهور لكل نسخة.\nالاختبار استمر بـ3,200 — 24% فقط مما يلزم.\n\nاستخدام حاسبة الأهمية الإحصائية: p ≈ 0.14 — **غير معنوي** (يجب أن يكون p < 0.05).\n\n### إطار اختبار A/B السليم\n\n1. حدد مقياس النجاح قبل بدء الاختبار\n2. احسب حجم العينة المطلوب (حاسبة حجم العينة)\n3. استمر للمدة الكاملة المطلوبة (أسبوعان كحد أدنى + 1,000 نقرة/نسخة)\n4. اقرأ النتائج بشكل صحيح: p < 0.05 → نفّذ | غير معنوي → لا تنفّذ",

        'bigger_lesson_ar' => "**اختبار A/B ببيانات غير كافية ليس اختباراً — بل رمي عملة بخطوات إضافية.**\n\nضبط A/B ليس في التقنية — بل في الإحصاء. أجرى اختبار 8 أيام وإعلان الفائز تعسفي كإيقاف العملة بعد 3 رميات. أهم ما يتعلمه المسوّق عن الاختبار: الصبر هو المتغير الأهم.",

        'skills_taught_ar' => json_encode([
            'الأهمية الإحصائية في اختبار A/B',
            'حساب حجم العينة',
            'تفسير قيمة p',
            'تصميم إطار اختبار A/B',
            'تجنب الإيجابيات الزائفة في اختبارات التسويق',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-MA-03 ──────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'marketing_analytics',
        'topic_name_en' => 'Marketing Analytics & Reporting',
        'topic_name_ar' => 'تحليلات التسويق والتقارير',
        'layer'         => 'generic',
        'difficulty'    => 'intermediate',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 803,
        'read_time_minutes' => 13,

        'title_en'    => 'The Marketing Budget Nobody Could Justify',
        'subtitle_en' => 'How to build a marketing budget that links spend to business outcomes',
        'situation_en' => "You are the marketing director at **Summit Real Estate**, a mid-sized Egyptian property developer with projects in New Cairo, the North Coast, and 6th of October. Annual marketing budget: EGP 12.4 million.\n\nAt the annual budget planning meeting, the CFO challenges your budget request for next year (EGP 14.2 million, a 14.5% increase):\n\n*'You are asking for 14.2 million. Last year you spent 12.4 million. I can see that you ran campaigns, attended exhibitions, and produced materials. I cannot see how those 12.4 million contributed to the EGP 340 million in sales we closed. What is the connection?'*\n\nYou do not have a clear answer. You have channel performance data (CPL by channel, conversion rates, lead volumes) but you have never connected it to the final sales numbers in a way the CFO would accept.",
        'challenge_questions_en' => json_encode([
            "Why is it difficult to connect marketing spend to real estate sales — and what data would you need to build that connection?",
            "Walk through the full funnel from marketing spend to closed sale in real estate. At each stage, what is the conversion rate and what does it cost?",
            "The CFO uses the phrase 'what is the connection?' Design a marketing ROI model that answers this question specifically for Summit Real Estate.",
            "How should the EGP 14.2M budget be allocated across channels to maximise the probability of achieving the next year's sales target — and how do you defend that allocation to a CFO?",
        ]),
        'solution_en' => "## Marketing Budget Justification: The Language the CFO Speaks\n\n### Why Real Estate Marketing Attribution Is Hard\n\nReal estate has one of the longest and most complex customer journeys in any industry:\n- Average decision timeline: 6–18 months\n- Multiple marketing touchpoints before a purchase decision\n- High involvement — buyers research extensively, visit multiple projects\n- Sales team involvement is significant — a lead marketed in Q1 may close in Q3\n- Personal referrals and word-of-mouth play a large role\n\nThis means a strict last-click attribution model will always undervalue awareness campaigns and always give too much credit to the final touchpoint (often a Google Search or WhatsApp message to a sales rep).\n\n### The Full Funnel Model for Real Estate\n\n| Stage | Volume (Example) | Conversion Rate | Cost |\n|---|---|---|---|\n| Impressions (awareness) | 4,200,000 | — | EGP 1.8M |\n| Leads generated | 6,800 | 0.16% of impressions | EGP 2.2M |\n| Qualified leads (site visit booked) | 1,020 | 15% of leads | EGP 0.9M |\n| Site visits completed | 680 | 67% of qualified leads | EGP 1.4M |\n| Contracts signed | 204 | 30% of site visitors | EGP 2.1M |\n| **Total marketing spend** | — | — | **EGP 12.4M** |\n\n**Cost per contract: EGP 12,400,000 ÷ 204 = EGP 60,784**\n\nIf the average sale value is EGP 340M ÷ 204 sales = EGP 1,666,667 per unit, then marketing cost represents **3.6% of sale value** — a defensible number in real estate.\n\n### The CFO-Ready Marketing ROI Model\n\nPresent the CFO with this framework:\n\n> *'For every EGP 1 we spend on marketing, we generate EGP X in sales. Here is how we measured it: we tracked leads by source, measured conversion at each stage, and attributed final sales to their originating campaign. Our marketing-attributed revenue last year was EGP [Y] million. Marketing cost was EGP 12.4M. Marketing ROI = [Y-12.4]/12.4 × 100.'*\n\nThis requires a CRM that tracks lead source from the first touchpoint to final sale. If Summit does not have this, the first recommendation is to implement a CRM before the next budget cycle.\n\n### EGP 14.2M Budget Allocation\n\n| Channel | Allocation | Rationale |\n|---|---|---|\n| Digital (Google, Facebook, Instagram) | EGP 5.8M (41%) | Highest measurable CPL, scalable |\n| Real estate exhibitions (Cityscape etc.) | EGP 2.1M (15%) | High-intent audience, live conversion |\n| Outdoor & billboard | EGP 1.9M (13%) | Brand awareness in target catchment areas |\n| Content & SEO | EGP 1.2M (8%) | Long-term organic lead generation |\n| CRM & marketing tech | EGP 0.8M (6%) | Enables attribution and data quality |\n| Events & show home | EGP 1.4M (10%) | High conversion at point of visit |\n| Reserve / test budget | EGP 1.0M (7%) | New channels, A/B testing |\n\n**CFO defence for the increase:** The EGP 1.8M increase (14.5%) is allocated primarily to CRM implementation and digital scaling on the two channels with proven CPL performance. The expected outcome: 15% more leads at the same or lower CPL, resulting in 25–30 additional contracted units.",

        'bigger_lesson_en' => "**Every marketing budget conversation is a CFO asking: 'Show me the connection between this spend and this revenue.'**\n\nThe marketers who win budget fights are not the ones who can name every channel in their mix — they are the ones who can trace every pound spent to a pipeline stage, a qualified lead, and ultimately a closed deal. Build the funnel model, implement the CRM, and never let a CFO ask 'what is the connection' twice.",

        'skills_taught_en' => json_encode([
            'Marketing budget justification framework',
            'Full funnel modelling for real estate',
            'Marketing ROI calculation for CFOs',
            'Budget allocation by channel performance',
            'CRM-based marketing attribution',
        ]),

        'title_ar'    => 'ميزانية التسويق التي لم يستطع أحد تبريرها',
        'subtitle_ar' => 'كيف تبني ميزانية تسويقية تربط الإنفاق بنتائج الأعمال',
        'situation_ar' => "أنت مدير التسويق في **سميت للتطوير العقاري**، مطور عقاري مصري متوسط الحجم. ميزانية التسويق السنوية: 12.4 مليون جنيه.\n\nفي اجتماع تخطيط الميزانية السنوية، يتحدى المدير المالي طلبك للعام القادم (14.2 مليون جنيه):\n\n*'أنت تطلب 14.2 مليون. العام الماضي أنفقت 12.4 مليون. أرى أنك أجريت حملات وحضرت معارض. لا أرى كيف ساهمت تلك الـ12.4 مليون في المبيعات 340 مليون التي أغلقناها. ما العلاقة؟'*",
        'challenge_questions_ar' => json_encode([
            "لماذا يصعب ربط إنفاق التسويق بمبيعات العقارات — وما البيانات التي تحتاجها لبناء تلك العلاقة؟",
            "سِر عبر القمع الكامل من إنفاق التسويق إلى البيع المكتمل في العقارات. في كل مرحلة، ما معدل التحويل وتكلفته؟",
            "صمم نموذج ROI تسويقي يجيب على سؤال المدير المالي تحديداً لسميت للتطوير.",
            "كيف يجب توزيع ميزانية 14.2 مليون جنيه عبر القنوات لتعظيم احتمال تحقيق هدف المبيعات؟",
        ]),
        'solution_ar' => "## تبرير ميزانية التسويق: لغة المدير المالي\n\n### نموذج القمع الكامل للعقارات\n\n| المرحلة | الحجم | معدل التحويل | التكلفة |\n|---|---|---|---|\n| انطباعات | 4,200,000 | — | 1.8 مليون |\n| عملاء محتملون | 6,800 | 0.16% | 2.2 مليون |\n| مؤهَّلون (زيارة محجوزة) | 1,020 | 15% | 0.9 مليون |\n| عقود موقَّعة | 204 | 30% من الزوار | 2.1 مليون |\n\n**تكلفة لكل عقد: 60,784 جنيه = 3.6% من قيمة المبيع — رقم مبرَّر في العقارات**\n\n### توزيع ميزانية 14.2 مليون\n\n| القناة | التخصيص | المبرر |\n|---|---|---|\n| رقمي | 5.8 مليون (41%) | أعلى CPL قابل للقياس |\n| معارض عقارية | 2.1 مليون (15%) | جمهور عالي النية |\n| خارجي ولوحات | 1.9 مليون (13%) | وعي بالعلامة |\n| CRM وتقنية | 0.8 مليون (6%) | يمكّن النسب والبيانات |",

        'bigger_lesson_ar' => "**كل محادثة ميزانية تسويقية هي مدير مالي يسأل: 'أرني العلاقة بين هذا الإنفاق وهذه الإيرادات.'**\n\nالمسوّقون الذين يفوزون في نزاعات الميزانية ليسوا من يستطيعون تسمية كل قناة — بل من يستطيعون تتبع كل جنيه إلى مرحلة في القمع وعميل محتمل مؤهَّل وفي نهاية المطاف صفقة مكتملة.",

        'skills_taught_ar' => json_encode([
            'إطار تبرير ميزانية التسويق',
            'نمذجة القمع الكامل للعقارات',
            'حساب ROI التسويقي للمديرين الماليين',
            'توزيع الميزانية حسب أداء القناة',
            'نسب التسويق بالـCRM',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-MA-04 ──────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'marketing_analytics',
        'topic_name_en' => 'Marketing Analytics & Reporting',
        'topic_name_ar' => 'تحليلات التسويق والتقارير',
        'layer'         => 'generic',
        'difficulty'    => 'intermediate',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 804,
        'read_time_minutes' => 12,

        'title_en'    => 'The Cohort That Told a Different Story',
        'subtitle_en' => 'How cohort analysis reveals what averages hide about customer behaviour',
        'situation_en' => "You are a marketing analyst at **Fateha**, an Egyptian subscription meal-kit service delivering weekly boxes of pre-portioned ingredients with recipes. Average revenue per subscriber: EGP 480/month. Total subscribers: 2,800.\n\nThe CEO is pleased: monthly subscriber count is growing, average order value is stable, and monthly revenue is trending up.\n\nYou build a cohort analysis — tracking each group of subscribers by the month they first subscribed, and measuring their retention over time. The data tells a very different story:\n\n| Cohort (First Month) | Month 0 | Month 1 | Month 2 | Month 3 | Month 6 |\n|---|---|---|---|---|---|\n| Jan 2024 | 100% | 74% | 58% | 41% | 19% |\n| Apr 2024 | 100% | 71% | 53% | 38% | 17% |\n| Jul 2024 | 100% | 68% | 49% | 34% | — |\n| Oct 2024 | 100% | 65% | 44% | — | — |\n| Jan 2025 | 100% | 61% | — | — | — |\n\nThe CEO does not understand cohort analysis. You need to explain it — and its implications.",
        'challenge_questions_en' => json_encode([
            "Explain cohort analysis to the CEO in plain language — using one sentence, one metaphor, and one number from the data.",
            "What is the trend you can see in the cohort table — and what does it mean for the future of the business if it continues?",
            "The business looks healthy when you look at total subscribers and total revenue. Why does cohort analysis reveal a hidden problem that these aggregate numbers conceal?",
            "Based on the cohort data, identify the most critical intervention point — the month where the most subscribers leave — and design a retention strategy targeting that exact moment.",
        ]),
        'solution_en' => "## Cohort Analysis: When the Average Lies\n\n### Explaining Cohort Analysis Simply\n\n**One sentence:** A cohort analysis tracks a group of customers who started at the same time and shows how many are still around each month.\n\n**One metaphor:** Imagine a leaking bucket. Total subscribers (the water level) looks fine because you keep adding new water. Cohort analysis measures the holes in the bucket — how fast is each group draining? The water level going up does not mean the holes are getting smaller.\n\n**One number from the data:** Of every 100 subscribers who join Fateha, only 19 are still subscribers 6 months later.\n\n### The Trend: Getting Worse, Not Better\n\n| Cohort | Month 1 Retention | Month 3 Retention |\n|---|---|---|\n| Jan 2024 | 74% | 41% |\n| Apr 2024 | 71% | 38% |\n| Jul 2024 | 68% | 34% |\n| Oct 2024 | 65% | — |\n| Jan 2025 | 61% | — |\n\nMonth 1 retention is declining: 74% → 61% over four cohorts. This means each new batch of customers is churning faster than the previous one.\n\nPossible causes: the acquisition audience is getting less targeted (ads reaching less-interested people), product quality or delivery reliability has declined, competition has entered, or the novelty has worn off.\n\n### Why Aggregate Numbers Hide the Problem\n\nFateha currently acquires 300+ new subscribers per month. Even with 80% churn in 6 months, if new subscribers are added fast enough, total subscriber count grows — masking the retention collapse.\n\n| Month | New Subscribers | Churned | Net Change | Total |\n|---|---|---|---|---|\n| M1 | +320 | -190 | +130 | 2,800 |\n| M2 | +340 | -210 | +130 | 2,930 |\n\nThe total grows. The CEO sees a healthy business. The cohort analysis reveals that growth is entirely dependent on acquisition volume — and if acquisition slows for any reason (ad costs rise, market saturates), the subscriber base will collapse quickly.\n\n### The Critical Intervention: Month 1\n\nMonth 1 has the highest absolute churn (26–39% depending on cohort). This is where the most subscribers leave.\n\n**Why do subscribers leave in Month 1?**\nCommon reasons for meal-kit churn in Month 1:\n- Delivery was late or packaging was damaged\n- Recipe difficulty did not match expectation\n- Portions too small/large for their household\n- Forgot to pause before travel/busy week — automatic charge created resentment\n\n**Month 1 Retention Programme:**\n\nDay 3 after first delivery: WhatsApp check-in — 'How was your first box? Was the delivery on time and packaging intact?'\n\nDay 7: Recipe feedback — 'Which recipe was your favourite? We'll include more like it in your next box.'\n\nDay 14 (before Month 2 renewal): Personalisation moment — 'Based on your first box, we've selected 3 options for next week. Which one would you like?' (active choice = higher commitment)\n\nPause reminder: 'Going away next week? Pause your subscription with one tap — your slot will be waiting when you're back.' (preventing resentment from forgotten charges)\n\nDay 21: Social proof — 'Here's what subscribers in your neighbourhood cooked this week [photos]'",

        'bigger_lesson_en' => "**Averages are lies that tell a comfortable story. Cohorts tell the truth.**\n\nEvery subscription business that scales eventually faces the same choice: grow by fixing the leaky bucket (improve retention) or grow by pouring more water faster (increase acquisition). Fixing the leak is always cheaper and more sustainable — but you can only see the leak if you measure it with cohorts.",

        'skills_taught_en' => json_encode([
            'Cohort analysis methodology',
            'Retention curve interpretation',
            'Cohort trend analysis',
            'Aggregate metrics vs. cohort metrics',
            'Month 1 retention programme design',
        ]),

        'title_ar'    => 'المجموعة التي حكت قصة مختلفة',
        'subtitle_ar' => 'كيف يكشف تحليل المجموعات ما تخفيه المتوسطات عن سلوك العملاء',
        'situation_ar' => "أنت محلل تسويق في **فاتحة**، خدمة اشتراك بصناديق وجبات مصرية أسبوعية. إجمالي المشتركين: 2,800. الرئيس التنفيذي سعيد: عدد المشتركين الشهري ينمو وقيمة الطلب الوسطى ثابتة.\n\nتبني تحليل مجموعات — تتبع كل مجموعة مشتركين حسب شهر أول اشتراك لهم:\n\n| المجموعة | الشهر 0 | الشهر 1 | الشهر 3 | الشهر 6 |\n|---|---|---|---|---|\n| يناير 2024 | 100% | 74% | 41% | 19% |\n| يوليو 2024 | 100% | 68% | 34% | — |\n| يناير 2025 | 100% | 61% | — | — |\n\nالاتجاه نزولي. الرئيس لا يفهم تحليل المجموعات. تحتاج شرحه — وتوضيح تبعاته.",
        'challenge_questions_ar' => json_encode([
            "اشرح تحليل المجموعات للرئيس التنفيذي بلغة بسيطة — جملة واحدة واستعارة واحدة ورقم واحد من البيانات.",
            "ما الاتجاه الذي تراه في جدول المجموعات — وما معناه لمستقبل الأعمال إذا استمر؟",
            "الأعمال تبدو صحية حين تنظر لإجمالي المشتركين. لماذا يكشف تحليل المجموعات مشكلة خفية تخفيها هذه الأرقام المجمّعة؟",
            "بناءً على بيانات المجموعات، حدد أهم نقطة تدخل — الشهر الذي يغادر فيه أكثر المشتركين — وصمم استراتيجية احتفاظ.",
        ]),
        'solution_ar' => "## تحليل المجموعات: حين تكذب المتوسطات\n\n### شرح التحليل ببساطة\n\n**جملة واحدة:** تحليل المجموعات يتبع مجموعة عملاء بدأوا في نفس الوقت ويُظهر كم منهم لا يزال موجوداً كل شهر.\n\n**استعارة:** تخيل دلواً يتسرب. مستوى الماء (إجمالي المشتركين) يبدو بخير لأنك تضيف ماءً جديداً باستمرار. تحليل المجموعات يقيس الثقوب.\n\n**رقم واحد:** من كل 100 مشترك، 19 فقط لا يزالون مشتركين بعد 6 أشهر.\n\n### الاتجاه: يتفاقم لا يتحسن\n\nاحتفاظ الشهر الأول ينخفض: 74% ← 61% عبر أربع مجموعات. كل دفعة جديدة ترحل أسرع من السابقة.\n\n### برنامج احتفاظ الشهر الأول (نقطة التدخل الأهم)\n\n- اليوم 3: واتساب 'كيف كان صندوقك الأول؟'\n- اليوم 7: ملاحظات وصفة 'أي وصفة أحببت؟'\n- اليوم 14: لحظة تخصيص 'اخترنا 3 خيارات لأسبوعك القادم'\n- تذكير إيقاف مؤقت قبل التجديد: 'مسافر الأسبوع القادم؟ أوقف اشتراكك بضغطة واحدة'",

        'bigger_lesson_ar' => "**المتوسطات أكاذيب تحكي قصة مريحة. المجموعات تحكي الحقيقة.**\n\nكل أعمال اشتراك تتوسع تواجه في نهاية المطاف نفس الخيار: النمو بإصلاح الدلو المتسرب أو النمو بصب الماء بشكل أسرع. إصلاح التسرب دائماً أرخص وأكثر استدامة — لكن لا يمكن رؤيته إلا بقياسه بالمجموعات.",

        'skills_taught_ar' => json_encode([
            'منهجية تحليل المجموعات',
            'تفسير منحنى الاحتفاظ',
            'تحليل اتجاه المجموعات',
            'المقاييس المجمّعة مقابل مقاييس المجموعات',
            'تصميم برنامج احتفاظ الشهر الأول',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-MA-05 ──────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'marketing_analytics',
        'topic_name_en' => 'Marketing Analytics & Reporting',
        'topic_name_ar' => 'تحليلات التسويق والتقارير',
        'layer'         => 'generic',
        'difficulty'    => 'advanced',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 805,
        'read_time_minutes' => 14,

        'title_en'    => 'The Customer Lifetime Value Nobody Calculated',
        'subtitle_en' => 'Why LTV is the most important number in marketing — and how to use it to make better decisions',
        'situation_en' => "You are the VP of Marketing at **Madar Academy**, an Egyptian EdTech company offering online professional development courses priced at EGP 1,200–3,500 per course. Courses are purchased individually — no subscription model.\n\nCurrent acquisition metrics:\n- Average CAC (blended): EGP 480\n- Average first-purchase revenue: EGP 1,650\n- Gross margin: 62%\n- First purchase gross profit: EGP 1,023\n\nThe board sees CAC of EGP 480 and first-purchase gross profit of EGP 1,023 and concludes: 'Marketing ROI is 2.1× — that's acceptable.'\n\nYou believe this is wrong — not because the calculation is incorrect, but because it only measures the first purchase. Madar's customers often buy multiple courses, refer colleagues, and some become corporate clients. The board is making budget decisions based on an incomplete picture.",
        'challenge_questions_en' => json_encode([
            "What is Customer Lifetime Value (LTV) and why is measuring only first-purchase profitability a strategically dangerous way to evaluate marketing?",
            "You need to calculate a 3-year LTV for Madar Academy customers. What data do you need to collect, and how do you build the model?",
            "LTV:CAC ratio is the most important metric in subscription and repeat-purchase businesses. What does a 3:1 ratio mean — and what does a 1.5:1 ratio tell you?",
            "Using LTV, make the case to the board that the marketing budget should be increased — even though the simple first-purchase ROI of 2.1× already looks acceptable.",
        ]),
        'solution_en' => "## Customer Lifetime Value: The Number That Changes How You See Everything\n\n### Why First-Purchase Profitability Misses the Point\n\nDeciding marketing investment based on first-purchase ROI is like evaluating a fruit tree by whether the first apple covered the cost of the seedling. The tree produces apples for 20 years.\n\nA Madar Academy customer who buys their first course for EGP 1,650 and never returns is worth EGP 1,023 gross profit. A customer who buys 4 courses over 3 years, refers 2 colleagues who each buy 1 course, and eventually upgrades their employer to a corporate licence is worth tens of thousands of pounds.\n\nThe board is optimising for the seedling, not the tree.\n\n### Building the 3-Year LTV Model\n\n**Data needed:**\n1. Repeat purchase rate: what % of first-time buyers purchase a second course?\n2. Average purchases per year for repeat buyers\n3. Average order value over time (does it increase as customers buy more advanced courses?)\n4. Referral rate: what % of buyers refer someone else?\n5. Churn rate by year: what % of active buyers are still buying in Year 2 vs. Year 3?\n\n**Sample LTV model (with illustrative assumptions):**\n\n| Year | % Still Active | Courses Purchased | Revenue | Gross Profit |\n|---|---|---|---|---|\n| Year 1 | 100% | 1.4 | EGP 2,310 | EGP 1,432 |\n| Year 2 | 42% | 1.8 | EGP 2,970 | EGP 1,841 (×42%) = EGP 773 |\n| Year 3 | 28% | 2.1 | EGP 3,465 | EGP 2,148 (×28%) = EGP 601 |\n| Referral value | +0.6 referrals × EGP 1,023 | = EGP 614 | | |\n\n**3-Year LTV = EGP 1,432 + EGP 773 + EGP 601 + EGP 614 = EGP 3,420**\n\nVs. CAC of EGP 480.\n\n**LTV:CAC ratio = 3,420 ÷ 480 = 7.1×**\n\nThis is not a 2.1× return. This is a 7.1× return over 3 years. The first purchase is only 42% of the customer's total value.\n\n### LTV:CAC Ratio Benchmarks\n\n| LTV:CAC | Interpretation |\n|---|---|\n| < 1× | Destroying value — acquiring customers costs more than they are worth |\n| 1–2× | Barely viable — every customer barely covers their acquisition cost |\n| 3× | Industry standard minimum for healthy unit economics |\n| 5–7× | Excellent — can afford to increase acquisition investment significantly |\n| 10×+ | Either exceptional retention or underinvesting in acquisition |\n\n### The Budget Case Using LTV\n\n> *'The board sees a 2.1× first-purchase ROI and says it is acceptable. But this measures only 42% of the customer's real value. Our 3-year LTV:CAC is 7.1× — meaning every EGP 480 we spend in acquisition generates EGP 3,420 over the life of the customer.*\n> *\n> *'At 7.1×, we are underinvesting in marketing. A business with this unit economics should be spending aggressively to acquire customers while the LTV:CAC ratio is this strong. Our recommendation is to increase the acquisition budget by 40% and reinvest in retention programmes that improve the Year 2 retention rate from 42% to 55% — which would increase 3-year LTV to approximately EGP 4,100, improving LTV:CAC to 8.5×.'*",

        'bigger_lesson_en' => "**CAC tells you what a customer costs. LTV tells you what a customer is worth. Neither number alone is enough.**\n\nThe LTV:CAC ratio is the single most important metric in any business with repeat purchase potential. Companies that understand their LTV can outspend competitors on acquisition — because they know they will earn it back. Companies that only see first-purchase profitability will always be cautious about marketing spend — and will lose market share to those who see the full picture.",

        'skills_taught_en' => json_encode([
            'Customer Lifetime Value calculation',
            'LTV:CAC ratio analysis',
            'Multi-year customer profitability modelling',
            'LTV-based budget justification',
            'Retention impact on LTV',
        ]),

        'title_ar'    => 'القيمة الدائمة للعميل التي لم يحسبها أحد',
        'subtitle_ar' => 'لماذا LTV أهم رقم في التسويق — وكيف تستخدمه لاتخاذ قرارات أفضل',
        'situation_ar' => "أنت نائب رئيس التسويق في **مدار أكاديمي**، شركة مصرية EdTech تقدم دورات تطوير مهني بأسعار 1,200-3,500 جنيه للدورة. الشراء فردي — لا اشتراك.\n\nالمجلس يرى CAC بـ480 جنيه وربح إجمالي للشراء الأول بـ1,023 جنيه ويستنتج: 'ROI التسويق 2.1× — مقبول.'\n\nأنت تعتقد هذا خاطئ — ليس لأن الحساب غلط، بل لأنه يقيس الشراء الأول فقط. عملاء مدار كثيراً ما يشترون دورات متعددة ويحيلون زملاء.",
        'challenge_questions_ar' => json_encode([
            "ما القيمة الدائمة للعميل (LTV) ولماذا قياس ربحية الشراء الأول فقط طريقة استراتيجياً خطيرة لتقييم التسويق؟",
            "تحتاج حساب LTV لـ3 سنوات لعملاء مدار. ما البيانات التي تحتاج جمعها وكيف تبني النموذج؟",
            "نسبة LTV:CAC أهم مقياس في أعمال الاشتراك والشراء المتكرر. ماذا تعني نسبة 3:1 — وماذا تخبرك نسبة 1.5:1؟",
            "باستخدام LTV، اصنع حجة للمجلس لزيادة ميزانية التسويق — حتى لو ROI الشراء الأول البسيط 2.1× يبدو مقبولاً.",
        ]),
        'solution_ar' => "## القيمة الدائمة للعميل: الرقم الذي يغير كيف ترى كل شيء\n\n### نموذج LTV لـ3 سنوات (بافتراضات توضيحية)\n\n| السنة | % لا يزال نشطاً | الإيرادات | الربح الإجمالي |\n|---|---|---|---|\n| السنة 1 | 100% | 2,310 | 1,432 |\n| السنة 2 | 42% | 2,970 | 773 |\n| السنة 3 | 28% | 3,465 | 601 |\n| قيمة الإحالة | +0.6 إحالة | | 614 |\n\n**LTV لـ3 سنوات = 3,420 جنيه**\n\n**نسبة LTV:CAC = 3,420 ÷ 480 = 7.1×**\n\nهذا ليس عائد 2.1×. بل عائد 7.1× على 3 سنوات. الشراء الأول 42% فقط من القيمة الكاملة للعميل.\n\n### معايير نسبة LTV:CAC\n\n- < 1×: تدمير قيمة\n- 3×: الحد الأدنى الصناعي لاقتصاديات الوحدة الصحية\n- 5-7×: ممتاز\n- عند 7.1×: الشركة تقل استثمارها في التسويق",

        'bigger_lesson_ar' => "**CAC يخبرك بتكلفة العميل. LTV يخبرك بقيمة العميل. لا رقم وحده كافٍ.**\n\nنسبة LTV:CAC هي المقياس الأهم في أي أعمال بها إمكانية شراء متكرر. الشركات التي تفهم LTV تستطيع الإنفاق بقدر أكبر من المنافسين في الاستقطاب — لأنها تعرف أنها ستستعيده.",

        'skills_taught_ar' => json_encode([
            'حساب القيمة الدائمة للعميل',
            'تحليل نسبة LTV:CAC',
            'نمذجة ربحية العميل متعددة السنوات',
            'تبرير الميزانية بناءً على LTV',
            'أثر الاحتفاظ على LTV',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-MA-06 ──────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'marketing_analytics',
        'topic_name_en' => 'Marketing Analytics & Reporting',
        'topic_name_ar' => 'تحليلات التسويق والتقارير',
        'layer'         => 'generic',
        'difficulty'    => 'advanced',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 806,
        'read_time_minutes' => 14,

        'title_en'    => 'The Forecast That Was Always Wrong',
        'subtitle_en' => 'Building marketing forecasts that planning teams can actually rely on',
        'situation_en' => "You are the marketing director at **Prestige Auto**, an Egyptian multi-brand automotive dealership. Every quarter, you submit a marketing forecast to the finance team: projected leads, projected test drives, projected sales.\n\nYour forecasting track record over the last 8 quarters:\n\n| Quarter | Projected Leads | Actual Leads | Error | Projected Sales | Actual Sales | Error |\n|---|---|---|---|---|---|---|\n| Q1 2024 | 2,400 | 1,890 | -21% | 78 | 58 | -26% |\n| Q2 2024 | 2,200 | 2,470 | +12% | 72 | 81 | +13% |\n| Q3 2024 | 2,600 | 1,720 | -34% | 84 | 55 | -35% |\n| Q4 2024 | 2,800 | 3,010 | +8% | 91 | 97 | +7% |\n| Q1 2025 | 2,500 | 1,960 | -22% | 81 | 63 | -22% |\n| Q2 2025 | 2,300 | 2,650 | +15% | 75 | 86 | +15% |\n| Q3 2025 | 2,700 | 1,800 | -33% | 88 | 58 | -34% |\n| Q4 2025 | 2,900 | 3,100 | +7% | 94 | 100 | +6% |\n\nThe CFO has noticed a pattern: Q1 and Q3 are always over-estimated; Q2 and Q4 are always under-estimated. The question is: why — and how do you fix the forecasting methodology?",
        'challenge_questions_en' => json_encode([
            "Look at the pattern: Q1/Q3 always over-estimated, Q2/Q4 always under-estimated. What is the most likely cause of this systematic bias?",
            "What is the difference between a judgement-based forecast and a model-based forecast — and which one is Prestige Auto currently using?",
            "Build a simple seasonal adjustment model that would have significantly improved forecast accuracy — using only the 8 quarters of historical data provided.",
            "What additional data sources and inputs would make Prestige Auto's marketing forecast more accurate — beyond internal historical data?",
        ]),
        'solution_en' => "## Marketing Forecasting: Systematising What Was Intuitive\n\n### The Pattern: Systematic Seasonal Bias\n\nThe error is not random — it is perfectly predictable:\n- Q1: -21%, -22% (always over-estimated)\n- Q2: +12%, +15% (always under-estimated)\n- Q3: -34%, -33% (always over-estimated)\n- Q4: +8%, +7% (always under-estimated)\n\nThis is a **seasonal pattern** that the forecasting model is not accounting for.\n\nLikely cause: Automotive sales in Egypt are seasonal. Q2 and Q4 are strong quarters for car purchases (post-Ramadan bonuses in Q2, year-end purchases in Q4). Q1 (post-New Year, post-holiday spending) and Q3 (summer, holiday travel, pre-back-to-school budget pressure) are traditionally weaker.\n\nThe marketing director is not adjusting forecasts for seasonality — and is therefore repeating the same systematic error every quarter.\n\n### Judgement-Based vs. Model-Based Forecasting\n\n| Judgement-Based | Model-Based |\n|---|---|\n| Forecast based on 'what feels right' | Forecast based on historical patterns + quantified factors |\n| No documented logic | Transparent, auditable methodology |\n| Cannot be improved systematically | Improved as more data is added |\n| Biased by recent events and optimism | Corrected for known biases (seasonality, economic factors) |\n| Accuracy depends on forecaster's experience | Accuracy improves over time |\n\nPrestige Auto is using judgement-based forecasting. The result: the same seasonal errors repeated 8 times in a row.\n\n### Building a Seasonal Adjustment Model\n\n**Step 1 — Calculate the seasonal index**\nFor each quarter, divide actual leads by the yearly average:\n\n| Quarter | Avg Actual Leads (2 years) | Annual Average | Seasonal Index |\n|---|---|---|---|\n| Q1 | (1,890 + 1,960) / 2 = 1,925 | 2,355 | 0.82 |\n| Q2 | (2,470 + 2,650) / 2 = 2,560 | 2,355 | 1.09 |\n| Q3 | (1,720 + 1,800) / 2 = 1,760 | 2,355 | 0.75 |\n| Q4 | (3,010 + 3,100) / 2 = 3,055 | 2,355 | 1.30 |\n\n*(Annual average = (1,925 + 2,560 + 1,760 + 3,055) / 4 = 2,325 — using actual, not projected)*\n\n**Step 2 — Apply seasonal adjustment to base forecast**\nTo forecast Q1 2026:\n1. Estimate annual 2026 leads based on trend: e.g., 2,400/quarter annual average\n2. Apply Q1 seasonal index: 2,400 × 0.82 = **1,968 leads** (vs. the unjusted 2,500 the director would have forecast)\n\nThis method would have produced dramatically better forecasts:\n\n| Quarter | Old Forecast | Old Error | Seasonal-Adjusted Forecast | Adjusted Error |\n|---|---|---|---|---|\n| Q1 2025 | 2,500 | -22% | 2,500 × 0.82 = 2,050 | -5% |\n| Q2 2025 | 2,300 | +15% | 2,300 × 1.09 = 2,507 | -5% |\n| Q3 2025 | 2,700 | -33% | 2,700 × 0.75 = 2,025 | +13% |\n\n### Additional Forecast Inputs\n\n| Data Source | What It Adds |\n|---|---|\n| Egypt car import/sale statistics (CODA) | Industry-level demand signal |\n| USD/EGP exchange rate trend | Price sensitivity — car prices track USD |\n| Consumer confidence index | Forward-looking sentiment |\n| Competitor promotional activity | Market share shifts |\n| Fuel price changes | Affects total cost of ownership perception |\n| Enquiries pipeline (current quarter) | Leading indicator for next quarter closings |",

        'bigger_lesson_en' => "**A forecast that makes the same mistake 8 times in a row is not a forecast — it is optimism with a spreadsheet.**\n\nGood forecasting is not about being right every time. It is about being systematically less wrong over time — by documenting assumptions, incorporating historical patterns, and updating the model with every new data point. The most valuable forecasting skill is not predicting the future — it is knowing which patterns from the past reliably repeat.",

        'skills_taught_en' => json_encode([
            'Seasonal adjustment in marketing forecasting',
            'Systematic vs. random forecast error',
            'Model-based vs. judgement-based forecasting',
            'Seasonal index calculation',
            'Leading indicator identification for marketing',
        ]),

        'title_ar'    => 'التوقعات التي كانت دائماً خاطئة',
        'subtitle_ar' => 'بناء توقعات تسويقية يمكن لفرق التخطيط الاعتماد عليها فعلاً',
        'situation_ar' => "أنت مدير التسويق في **بريستيج أوتو**، وكالة سيارات مصرية متعددة العلامات. كل ربع سنة تقدم توقعات للعملاء المحتملين والمبيعات.\n\nسجل توقعاتك على 8 أرباع:\n\n| الربع | خطأ العملاء | خطأ المبيعات |\n|---|---|---|\n| R1 2024 | -21% | -26% |\n| R2 2024 | +12% | +13% |\n| R3 2024 | -34% | -35% |\n| R4 2024 | +8% | +7% |\n\nلاحظ المدير المالي نمطاً: R1 وR3 دائماً مُقدَّران بزيادة؛ R2 وR4 دائماً بنقص.",
        'challenge_questions_ar' => json_encode([
            "انظر للنمط: R1/R3 دائماً مُبالَغ في التقدير، R2/R4 دائماً أقل من التقدير. ما السبب الأكثر احتمالاً لهذا التحيز المنهجي؟",
            "ما الفرق بين التوقع المبني على الحكم والمبني على النموذج — وأيهما تستخدم بريستيج أوتو حالياً؟",
            "ابنِ نموذج تعديل موسمي بسيط يكون قد حسّن دقة التوقع بشكل كبير — باستخدام 8 أرباع البيانات التاريخية فقط.",
            "ما مصادر البيانات الإضافية التي ستجعل توقعات تسويق بريستيج أوتو أكثر دقة؟",
        ]),
        'solution_ar' => "## توقعات التسويق: تنظيم ما كان حدسياً\n\n### النمط: تحيز موسمي منهجي\n\nالأسباب المحتملة: مبيعات السيارات في مصر موسمية. R2 وR4 أقوى (مكافآت ما بعد رمضان، مشتريات نهاية العام). R1 وR3 تقليدياً أضعف.\n\n### بناء نموذج التعديل الموسمي\n\n**المؤشر الموسمي لكل ربع:**\n- R1: 0.82\n- R2: 1.09\n- R3: 0.75\n- R4: 1.30\n\n**تطبيق التعديل على التوقع:**\nبدلاً من 2,500 في R1 2025 (خطأ -22%):\n2,500 × 0.82 = **2,050 عميل محتمل** (خطأ -5%)\n\nهذا الأسلوب يُقلّص الخطأ من 22-34% إلى 5-13%.",

        'bigger_lesson_ar' => "**توقع يكرر نفس الخطأ 8 مرات متتالية ليس توقعاً — بل تفاؤل بجدول بيانات.**\n\nالتوقع الجيد لا يعني الصواب في كل مرة. بل يعني الأقل خطأً بشكل منهجي بمرور الوقت — بتوثيق الافتراضات وإدماج الأنماط التاريخية وتحديث النموذج مع كل نقطة بيانات جديدة.",

        'skills_taught_ar' => json_encode([
            'التعديل الموسمي في توقعات التسويق',
            'الخطأ المنهجي مقابل العشوائي في التوقعات',
            'التوقع المبني على النموذج مقابل الحكم',
            'حساب المؤشر الموسمي',
            'تحديد المؤشرات القيادية للتسويق',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        ]; // end file 10

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

        DB::table('cases')->insert($cases);
        $this->command->info('  ✅ 5 cases inserted (MG-MA-02 to MG-MA-06)');
    }
}
