-- =========================================================
-- CLEANUP & FRESH INSTALL SCRIPT
-- =========================================================
-- This script will:
-- 1. DELETE all existing "Threat Intelligence" data (cleaning up duplicates)
-- 2. RE-CREATE the course from scratch (ensuring 1 clean copy)
-- =========================================================

-- 1. Delete Lesson Content
DELETE lc FROM lesson_content lc 
INNER JOIN tasks t ON lc.task_id = t.id 
INNER JOIN modules m ON t.module_id = m.id 
INNER JOIN learning_paths lp ON m.learning_path_id = lp.id 
WHERE lp.title = 'Threat Intelligence';

-- 2. Delete Quiz Questions
DELETE lq FROM lesson_questions lq
INNER JOIN tasks t ON lq.task_id = t.id 
INNER JOIN modules m ON t.module_id = m.id 
INNER JOIN learning_paths lp ON m.learning_path_id = lp.id 
WHERE lp.title = 'Threat Intelligence';

-- 3. Delete Tasks
DELETE t FROM tasks t
INNER JOIN modules m ON t.module_id = m.id 
INNER JOIN learning_paths lp ON m.learning_path_id = lp.id 
WHERE lp.title = 'Threat Intelligence';

-- 4. Delete Modules
DELETE m FROM modules m
INNER JOIN learning_paths lp ON m.learning_path_id = lp.id 
WHERE lp.title = 'Threat Intelligence';

-- 5. Delete the Learning Paths (all duplicates)
DELETE FROM learning_paths WHERE title = 'Threat Intelligence';

-- =========================================================
-- FRESH INSTALL STARTS HERE
-- =========================================================
-- Threat Intelligence Learning Path
-- Insert script for Academy database

-- 1. Insert the Learning Path
INSERT INTO learning_paths (title, description, difficulty_level, estimated_hours, icon_url, display_order, is_active)
VALUES (
    'Threat Intelligence',
    'Master the art of Cyber Threat Intelligence (CTI). Learn the intelligence cycle, OSINT techniques, malware analysis, MITRE ATT&CK, threat hunting, and how to produce actionable intelligence for your organization. Designed for security professionals who want to specialize in CTI.',
    'advanced',
    24,
    '🔍',
    10,
    TRUE
);

-- Get the ID of the newly inserted learning path
SET @path_id = LAST_INSERT_ID();

-- 2. Insert Module 1: CTI Fundamentals
INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'CTI Fundamentals',
    'Learn the foundations of Cyber Threat Intelligence including the intelligence cycle, types of intelligence (strategic, operational, tactical), and how to build an effective CTI program.',
    3,
    1,
    TRUE
);

SET @module1_id = LAST_INSERT_ID();

-- 3. Insert Tasks for Module 1

-- Task 1: Reading - What is CTI
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module1_id,
    'What is Cyber Threat Intelligence?',
    'Understand the definition of CTI and the critical difference between raw data, information, and actionable intelligence.',
    'reading',
    JSON_OBJECT(
        'content', '## What is Cyber Threat Intelligence?\n\n**Cyber Threat Intelligence (CTI)** is evidence-based knowledge about existing or emerging threats to digital assets. It provides context, mechanisms, indicators, implications, and actionable advice.\n\n### The Data → Information → Intelligence Pyramid\n\n- **Data**: Raw, unprocessed facts (IP: 185.123.45.67)\n- **Information**: Processed, organized data with context (This IP is linked to Cobalt Strike C2)\n- **Intelligence**: Analyzed, actionable insight (This IP is part of APT29 campaign targeting healthcare. Your org is at risk. Patch VPN now.)\n\n> 💡 Raw data becomes intelligence only when analyzed, contextualized, and made actionable for a specific audience.',
        'estimated_minutes', 10
    ),
    50,
    1
);

-- Task 2: Reading - Intelligence Cycle
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module1_id,
    'The Intelligence Cycle',
    'Master the six phases of the intelligence cycle: Direction, Collection, Processing, Analysis, Dissemination, and Feedback.',
    'reading',
    JSON_OBJECT(
        'content', '## The Intelligence Cycle\n\nThe Intelligence Cycle is a systematic process with **six phases**:\n\n### 1. Direction (Planning)\nStakeholders define intelligence needs. What decisions will this support?\n\n### 2. Collection\nGather raw data from sources:\n- OSINT (social media, news, forums)\n- Technical (malware, network traffic)\n- Commercial feeds (Recorded Future, Mandiant)\n- Internal (SIEM, EDR, incident reports)\n\n### 3. Processing\nConvert data to usable format: decoding, normalizing, deduplication.\n\n### 4. Analysis\nThe core intellectual work: pattern recognition, link analysis, hypothesis testing.\n\n### 5. Dissemination\nDeliver to consumers in appropriate formats:\n- Executives → Strategic briefs\n- SOC Analysts → IOC lists, detection rules\n\n### 6. Feedback\nConsumers provide feedback to improve future intelligence.',
        'estimated_minutes', 15
    ),
    75,
    2
);

-- Task 3: Reading - Types of Intelligence
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module1_id,
    'Types of Threat Intelligence',
    'Learn the three types of intelligence: Strategic (executives), Operational (campaigns), and Tactical (IOCs).',
    'reading',
    JSON_OBJECT(
        'content', '## Types of Threat Intelligence\n\n### Strategic Intelligence\n**Audience**: Executives, board members\n**Focus**: Long-term (months to years)\n**Content**: Trends, motivations, geopolitical factors\n**Example**: "Nation-state actors targeting our sector. Expect 40% increase in attacks."\n\n### Operational Intelligence\n**Audience**: Security managers, threat hunters\n**Focus**: Medium-term (weeks to months)\n**Content**: Adversary TTPs, campaign details\n**Example**: "APT28 using spearphishing with macro docs targeting defense contractors."\n\n### Tactical Intelligence\n**Audience**: SOC analysts, IR teams\n**Focus**: Short-term (hours to days)\n**Content**: IOCs, detection rules, YARA signatures\n**Example**:\n```\nIndicator: 185.123.45.67\nType: IPv4 Address\nMalware: Cobalt Strike\nAction: Block at firewall\n```',
        'estimated_minutes', 12
    ),
    75,
    3
);

-- Task 4: Quiz - Module 1 Assessment
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module1_id,
    'Module 1 Assessment',
    'Test your understanding of CTI fundamentals with this quiz.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'What is the PRIMARY difference between "data" and "intelligence" in the context of CTI?',
                'options', JSON_ARRAY(
                    'Data is collected from external sources, while intelligence comes from internal sources',
                    'Intelligence is analyzed, contextualized, and actionable, while data is raw and unprocessed',
                    'Data is more accurate than intelligence',
                    'Intelligence can only be created by automated systems'
                ),
                'correct_answer', 1,
                'explanation', 'Raw data (IP addresses, hashes, logs) only becomes intelligence when analyzed, given context, and transformed into actionable insights.'
            ),
            JSON_OBJECT(
                'question', 'A CISO asks for a report to present to the board about emerging risks over the next year. Which type of intelligence should they produce?',
                'options', JSON_ARRAY(
                    'Tactical Intelligence',
                    'Technical Intelligence',
                    'Strategic Intelligence',
                    'Operational Intelligence'
                ),
                'correct_answer', 2,
                'explanation', 'Strategic Intelligence is for executives. It focuses on long-term trends and uses non-technical language for board presentations.'
            ),
            JSON_OBJECT(
                'question', 'During which phase of the Intelligence Cycle do analysts apply pattern recognition, link analysis, and hypothesis testing?',
                'options', JSON_ARRAY(
                    'Collection',
                    'Processing',
                    'Analysis',
                    'Dissemination'
                ),
                'correct_answer', 2,
                'explanation', 'The Analysis phase is where analysts derive meaning from processed data using various analytical techniques.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    4
);

-- Verify insertion
SELECT 'Learning Path inserted:' as status, id, title FROM learning_paths WHERE id = @path_id;
SELECT 'Module inserted:' as status, id, title FROM modules WHERE id = @module1_id;
SELECT 'Tasks inserted:' as status, COUNT(*) as task_count FROM tasks WHERE module_id = @module1_id;
-- Module 2: OSINT Techniques
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

-- Insert Module 2
INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'OSINT Techniques',
    'Master Open Source Intelligence gathering techniques including domain reconnaissance, social media analysis, dark web monitoring, and using tools like Maltego, Shodan, and theHarvester.',
    4,
    2,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: Introduction to OSINT
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Introduction to OSINT',
    'Understand what Open Source Intelligence is, its legal considerations, and the OSINT framework.',
    'reading',
    JSON_OBJECT(
        'content', '## What is OSINT?\n\n**Open Source Intelligence (OSINT)** is intelligence collected from publicly available sources. In cybersecurity, it''s used to gather information about threats, adversaries, and attack infrastructure.\n\n### OSINT Sources\n\n| Category | Examples |\n|----------|----------|\n| **Web** | Websites, blogs, forums, paste sites |\n| **Social Media** | Twitter/X, LinkedIn, Telegram, Discord |\n| **Technical** | WHOIS, DNS records, SSL certificates |\n| **Dark Web** | Tor sites, markets, forums |\n| **Code Repos** | GitHub, GitLab, exposed credentials |\n| **Documents** | PDFs, Office docs (metadata) |\n\n### Legal Considerations\n\n✅ **Legal**: Viewing public websites, WHOIS lookups, search engines\n⚠️ **Gray Area**: Scraping (check ToS), creating fake profiles\n❌ **Illegal**: Unauthorized access, hacking, buying stolen data\n\n> 💡 **Golden Rule**: If you need authentication or bypass security controls, it''s not OSINT.',
        'estimated_minutes', 12
    ),
    50,
    1
);

-- Task 2: Domain and Infrastructure Reconnaissance
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Domain & Infrastructure Reconnaissance',
    'Learn to investigate domains, IP addresses, and network infrastructure using WHOIS, DNS, and certificate transparency.',
    'reading',
    JSON_OBJECT(
        'content', '## Domain & Infrastructure OSINT\n\n### WHOIS Lookups\n\nWHOIS reveals domain registration information:\n- Registrant name/organization\n- Registration/expiration dates\n- Name servers\n- Registrar information\n\n**Tools**: `whois`, DomainTools, ViewDNS.info\n\n### DNS Reconnaissance\n\n```bash\n# Find subdomains\ndig axfr @ns1.target.com target.com\n\n# Get all DNS records\ndig target.com ANY\n\n# Reverse DNS lookup\ndig -x 192.168.1.1\n```\n\n**Tools**: DNSDumpster, SecurityTrails, Subfinder\n\n### Certificate Transparency\n\nSSL certificates are logged publicly. Search for subdomains:\n- crt.sh\n- Censys\n- Google CT logs\n\n### Shodan & Censys\n\nSearch engines for internet-connected devices:\n\n```\n# Shodan queries\norg:"Target Company"\nssl.cert.subject.CN:"target.com"\nproduct:"Apache" country:"US"\n```\n\n### Combining Intelligence\n\n1. Start with domain WHOIS\n2. Find related domains (same registrant)\n3. Enumerate subdomains via CT logs\n4. Scan infrastructure with Shodan\n5. Map the complete attack surface',
        'estimated_minutes', 18
    ),
    75,
    2
);

-- Task 3: Social Media & Human Intelligence
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Social Media & HUMINT',
    'Investigate threat actors through social media platforms, forums, and building source networks.',
    'reading',
    JSON_OBJECT(
        'content', '## Social Media Intelligence\n\n### Twitter/X Analysis\n\nThreat actors often communicate on Twitter:\n- New vulnerability disclosures\n- Malware samples shared\n- Breach announcements\n- CTI researcher community\n\n**Search Operators**:\n```\nfrom:username keyword\n"exact phrase" since:2024-01-01\n#ransomware filter:links\n```\n\n### Telegram & Discord\n\nCybercrime communities use these platforms:\n- Ransomware leak sites\n- Stolen data marketplaces\n- Malware-as-a-Service\n- Credential dumps\n\n⚠️ **Warning**: Passive observation only. Never interact or purchase.\n\n### LinkedIn Intelligence\n\n- Identify company employees\n- Technology stack (job postings)\n- Organizational structure\n- Recent hires (potential phishing targets)\n\n### Dark Web Monitoring\n\n**What to monitor**:\n- Ransomware leak sites\n- Initial Access Broker (IAB) forums\n- Credential marketplaces\n- Company mentions\n\n**Tools**: Tor Browser, Ahmia, DarkSearch, commercial services\n\n### Building a Source Network\n\n- Follow CTI researchers\n- Join ISACs (Information Sharing and Analysis Centers)\n- Participate in threat intel communities\n- Attend conferences (virtual/in-person)',
        'estimated_minutes', 15
    ),
    75,
    3
);

-- Task 4: OSINT Tools Workshop
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'OSINT Tools Workshop',
    'Hands-on overview of essential OSINT tools: Maltego, theHarvester, SpiderFoot, and Recon-ng.',
    'reading',
    JSON_OBJECT(
        'content', '## Essential OSINT Tools\n\n### Maltego\n\nGraph-based link analysis tool:\n- Visualize relationships between entities\n- Automated transforms for reconnaissance\n- Connect domains → IPs → organizations → people\n\n**Use cases**: Mapping threat actor infrastructure, phishing campaign analysis\n\n### theHarvester\n\n```bash\ntheHarvester -d target.com -b google,bing,linkedin\n```\n\nGathers:\n- Email addresses\n- Subdomains\n- Employee names\n- Open ports\n\n### SpiderFoot\n\nAutomated OSINT collection:\n- 200+ modules\n- Correlates data automatically\n- Web interface\n- Export to STIX/CSV\n\n### Recon-ng\n\nFramework for web reconnaissance:\n```\nrecon-ng\nworkspaces create target_company\nmodules search whois\nmodules load recon/domains-hosts/hackertarget\noptions set SOURCE target.com\nrun\n```\n\n### Other Useful Tools\n\n| Tool | Purpose |\n|------|--------|\n| **Amass** | Subdomain enumeration |\n| **Sherlock** | Username search across platforms |\n| **ExifTool** | Image/document metadata |\n| **Wayback Machine** | Historical website versions |\n| **Have I Been Pwned** | Breach exposure check |',
        'estimated_minutes', 20
    ),
    100,
    4
);

-- Task 5: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 2 Assessment',
    'Test your OSINT knowledge.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'Which tool would you use to search for internet-connected devices and services exposed to the internet?',
                'options', JSON_ARRAY(
                    'Maltego',
                    'Shodan',
                    'theHarvester',
                    'Burp Suite'
                ),
                'correct_answer', 1,
                'explanation', 'Shodan is a search engine for internet-connected devices. It indexes banners from services like HTTP, FTP, SSH, etc.'
            ),
            JSON_OBJECT(
                'question', 'What is Certificate Transparency and how is it useful for OSINT?',
                'options', JSON_ARRAY(
                    'It encrypts SSL certificates to prevent interception',
                    'It logs all SSL certificates publicly, revealing subdomains',
                    'It validates certificate authenticity',
                    'It blocks invalid certificates from loading'
                ),
                'correct_answer', 1,
                'explanation', 'Certificate Transparency logs all issued SSL certificates publicly. This allows researchers to discover subdomains by searching for certificates issued to a domain.'
            ),
            JSON_OBJECT(
                'question', 'When conducting OSINT on dark web forums, what is the most important rule to follow?',
                'options', JSON_ARRAY(
                    'Create multiple accounts to gather more information',
                    'Purchase small samples to verify data quality',
                    'Passive observation only - never interact or purchase',
                    'Use your real identity to build trust'
                ),
                'correct_answer', 2,
                'explanation', 'OSINT must remain passive. Interacting, purchasing, or creating accounts on criminal forums crosses legal and ethical boundaries.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    5
);

SELECT 'Module 2 inserted' as status, @module_id as module_id;
-- Module 3: Malware Analysis Basics
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

-- Insert Module 3
INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'Malware Analysis Basics',
    'Learn the fundamentals of malware analysis including static vs dynamic analysis, sandboxing, behavioral indicators, and extracting IOCs from malware samples.',
    4,
    3,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: Introduction to Malware Analysis
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Introduction to Malware Analysis',
    'Understand the goals of malware analysis for threat intelligence and the different analysis approaches.',
    'reading',
    JSON_OBJECT(
        'content', '## Malware Analysis for Threat Intelligence\n\n### Why Analyze Malware?\n\nFor CTI, malware analysis helps:\n- Extract IOCs (hashes, C2 servers, mutexes)\n- Understand adversary capabilities\n- Identify malware families and campaigns\n- Develop detection signatures\n- Attribute attacks to threat actors\n\n### Types of Analysis\n\n| Type | Description | Speed | Depth |\n|------|-------------|-------|-------|\n| **Basic Static** | File properties, strings, hashes | Fast | Low |\n| **Advanced Static** | Disassembly, decompilation | Slow | High |\n| **Basic Dynamic** | Run in sandbox, observe behavior | Medium | Medium |\n| **Advanced Dynamic** | Debugging, memory analysis | Slow | High |\n\n### Analysis Workflow\n\n```\n1. Obtain sample safely\n   ↓\n2. Calculate hashes (MD5, SHA256)\n   ↓\n3. Check reputation (VT, MalwareBazaar)\n   ↓\n4. Basic static analysis\n   ↓\n5. Sandbox detonation\n   ↓\n6. Extract IOCs\n   ↓\n7. Document findings\n```\n\n### Safety First!\n\n⚠️ **Always analyze malware in isolated environments**\n- Use dedicated VMs (snapshot before, revert after)\n- Disable network or use isolated network\n- Never analyze on production systems\n- Use FlareVM or REMnux',
        'estimated_minutes', 12
    ),
    50,
    1
);

-- Task 2: Static Analysis Techniques
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Static Analysis Techniques',
    'Learn to analyze malware without executing it: file properties, strings, imports, and PE analysis.',
    'reading',
    JSON_OBJECT(
        'content', '## Static Analysis\n\nAnalyzing malware without running it.\n\n### File Properties\n\n```bash\n# Calculate hashes\nmd5sum malware.exe\nsha256sum malware.exe\n\n# File type identification\nfile malware.exe\n```\n\n### String Analysis\n\n```bash\nstrings -n 6 malware.exe | grep -E "(http|https|\\.[a-z]{2,4}$)"\n\n# FLOSS for obfuscated strings\nfloss malware.exe\n```\n\n**What to look for**:\n- URLs and IP addresses\n- Registry keys\n- File paths\n- Crypto wallet addresses\n- Debug messages\n- Encryption keys\n\n### PE Header Analysis\n\n**Tools**: PEStudio, PE-bear, pefile (Python)\n\n**Examine**:\n- Compile timestamp\n- Imports (what Windows APIs?)\n- Sections (.text, .data, unusual names)\n- Resources (embedded files, icons)\n- Digital signatures\n\n### Import Analysis\n\nSuspicious imports indicate capabilities:\n\n| API | Likely Purpose |\n|-----|----------------|\n| CreateRemoteThread | Code injection |\n| VirtualAllocEx | Memory manipulation |\n| WriteProcessMemory | Process hollowing |\n| RegSetValueEx | Persistence |\n| InternetOpenUrl | C2 communication |\n| CryptEncrypt | Encryption/ransomware |\n\n### Packing Detection\n\nMany malware samples are packed (compressed/encrypted):\n\n```\n# High entropy (>7.0) suggests packing\n# UPX, Themida, custom packers\n```\n\n**Tools**: Detect It Easy (DIE), ExeInfo PE',
        'estimated_minutes', 18
    ),
    75,
    2
);

-- Task 3: Dynamic Analysis & Sandboxing
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Dynamic Analysis & Sandboxing',
    'Execute malware in controlled environments and observe runtime behavior.',
    'reading',
    JSON_OBJECT(
        'content', '## Dynamic Analysis\n\nExecuting malware to observe behavior.\n\n### Sandbox Environments\n\n**Public Sandboxes**:\n- Any.Run (interactive)\n- Hybrid Analysis\n- Joe Sandbox\n- Triage\n- VirusTotal (automated)\n\n**Private Sandboxes**:\n- Cuckoo Sandbox\n- CAPE Sandbox\n- FireEye AX\n\n### What Sandboxes Capture\n\n| Category | Examples |\n|----------|----------|\n| **Network** | DNS queries, HTTP requests, C2 traffic |\n| **Files** | Dropped files, modifications |\n| **Registry** | Persistence mechanisms |\n| **Processes** | Spawned processes, injection |\n| **Memory** | Unpacked code, strings |\n\n### Manual Dynamic Analysis\n\n**Tools for Windows VMs**:\n\n| Tool | Purpose |\n|------|--------|\n| **Process Monitor** | File/registry/network activity |\n| **Process Hacker** | Process inspection |\n| **Wireshark** | Network traffic capture |\n| **Regshot** | Registry changes |\n| **FakeNet-NG** | Simulate network services |\n\n### Sandbox Evasion\n\nMalware may detect sandboxes:\n- Check VM artifacts (VMware tools)\n- Check username/hostname\n- Time delays (sleep for 10 min)\n- Human interaction required\n- Environment checks\n\n**Countermeasures**:\n- Use realistic hostnames/usernames\n- Simulate user activity\n- Accelerate sleep calls\n- Use bare-metal analysis for sophisticated samples',
        'estimated_minutes', 18
    ),
    75,
    3
);

-- Task 4: Extracting IOCs from Malware
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Extracting IOCs from Malware',
    'Learn to identify and extract actionable indicators of compromise from malware samples.',
    'reading',
    JSON_OBJECT(
        'content', '## Extracting Indicators of Compromise\n\n### Types of IOCs\n\n| IOC Type | Example | Detection Value |\n|----------|---------|----------------|\n| **File Hash** | SHA256: abc123... | High (unique) |\n| **Domain** | evil-c2.com | Medium (can change) |\n| **IP Address** | 185.123.45.67 | Medium |\n| **URL** | /gate.php?id= | High (URI patterns) |\n| **Email** | attacker@evil.com | Medium |\n| **Mutex** | Global\\M4lw4r3 | High (unique) |\n| **Registry Key** | HKCU\\Run\\malware | Medium |\n| **User-Agent** | Mozilla/5.0 (compatible; Evil) | Medium |\n\n### Extracting Network IOCs\n\n**From sandbox reports**:\n```json\n{\n  "network": {\n    "dns": ["evil-c2.com", "update.evil.net"],\n    "http": [{"host": "185.123.45.67", "uri": "/gate.php"}],\n    "tcp": [{"dst": "185.123.45.67", "port": 443}]\n  }\n}\n```\n\n**From PCAP**:\n```bash\ntshark -r traffic.pcap -Y "http.request" -T fields -e http.host -e http.request.uri\n```\n\n### Config Extraction\n\nMany malware families have embedded configs:\n- C2 servers\n- Encryption keys\n- Campaign IDs\n- Victim identifiers\n\n**Tools**: CAPE Sandbox, malware-config extractors\n\n### Creating Detection Rules\n\n**YARA Rule Example**:\n```yara\nrule Emotet_Loader {\n    meta:\n        description = "Emotet loader"\n        author = "Analyst"\n    strings:\n        $s1 = "POST" ascii\n        $s2 = {8B 45 ?? 89 45 ?? 8B 45}\n    condition:\n        uint16(0) == 0x5A4D and all of them\n}\n```\n\n**Snort/Suricata Rule**:\n```\nalert http any any -> any any (msg:"Malware C2 Beacon"; content:"POST"; http_method; content:"/gate.php"; http_uri; sid:1000001;)\n```',
        'estimated_minutes', 20
    ),
    100,
    4
);

-- Task 5: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 3 Assessment',
    'Test your malware analysis knowledge.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'What is the primary difference between static and dynamic malware analysis?',
                'options', JSON_ARRAY(
                    'Static analysis uses commercial tools, dynamic uses open source',
                    'Static examines malware without execution, dynamic observes runtime behavior',
                    'Static is faster but less accurate than dynamic',
                    'Dynamic analysis cannot extract IOCs'
                ),
                'correct_answer', 1,
                'explanation', 'Static analysis examines malware properties (strings, imports, structure) without executing it. Dynamic analysis runs the malware in a controlled environment to observe its behavior.'
            ),
            JSON_OBJECT(
                'question', 'Which Windows API import would most strongly suggest a malware sample performs process injection?',
                'options', JSON_ARRAY(
                    'ReadFile',
                    'CreateRemoteThread',
                    'InternetOpenUrl',
                    'RegQueryValueEx'
                ),
                'correct_answer', 1,
                'explanation', 'CreateRemoteThread is commonly used for process injection - it creates a thread in another process''s address space, often to run malicious code.'
            ),
            JSON_OBJECT(
                'question', 'Why might malware detect it''s running in a sandbox and alter its behavior?',
                'options', JSON_ARRAY(
                    'Sandboxes run malware faster, causing timing issues',
                    'To evade analysis and hide its true capabilities',
                    'Sandboxes automatically patch malware vulnerabilities',
                    'To request additional resources from the sandbox'
                ),
                'correct_answer', 1,
                'explanation', 'Malware authors implement sandbox evasion to prevent analysts from observing the true malicious behavior, making analysis and detection more difficult.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    5
);

SELECT 'Module 3 inserted' as status, @module_id as module_id;
-- Module 4: Indicator Management (IOCs, YARA, STIX)
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'Indicator Management',
    'Master the management of Indicators of Compromise (IOCs) including STIX/TAXII standards, YARA rules, OpenIOC, and integration with threat intelligence platforms.',
    3,
    4,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: Understanding IOCs
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Understanding Indicators of Compromise',
    'Learn about different types of IOCs, their detection value, and lifecycle management.',
    'reading',
    JSON_OBJECT(
        'content', '## Indicators of Compromise (IOCs)\n\n### What are IOCs?\n\nIOCs are artifacts that indicate a potential intrusion or malicious activity. They are the "fingerprints" of attacks.\n\n### Pyramid of Pain\n\nDavid Bianco''s Pyramid of Pain shows the difficulty for attackers to change indicators:\n\n```\n           ┌────────────┐\n           │   TTPs     │  ← Very Hard (behavior)\n          ┌┴────────────┴┐\n          │    Tools     │  ← Hard\n         ┌┴──────────────┴┐\n         │  Network/Host  │  ← Annoying\n         │   Artifacts    │\n        ┌┴────────────────┴┐\n        │  Domain Names    │  ← Simple\n       ┌┴──────────────────┴┐\n       │   IP Addresses     │  ← Easy\n      ┌┴────────────────────┴┐\n      │     Hash Values      │  ← Trivial\n      └──────────────────────┘\n```\n\n### IOC Types & Value\n\n| Type | Example | Attacker Effort to Change |\n|------|---------|---------------------------|\n| **Hash** | SHA256 of malware | Trivial (1 byte change) |\n| **IP** | C2 server IP | Easy (new server) |\n| **Domain** | C2 domain | Simple (new domain) |\n| **Artifact** | Mutex, registry key | Annoying |\n| **Tool** | Cobalt Strike, Mimikatz | Hard (new tooling) |\n| **TTP** | Process injection | Very hard (new techniques) |\n\n### IOC Lifecycle\n\n1. **Generation**: Extract from analysis\n2. **Validation**: Verify accuracy, check false positives\n3. **Enrichment**: Add context, confidence scores\n4. **Distribution**: Share with consumers\n5. **Consumption**: Ingest into detection tools\n6. **Expiration**: Age out stale indicators',
        'estimated_minutes', 15
    ),
    75,
    1
);

-- Task 2: STIX/TAXII Standards
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'STIX/TAXII Standards',
    'Learn the industry standards for threat intelligence sharing: STIX 2.1 and TAXII 2.1.',
    'reading',
    JSON_OBJECT(
        'content', '## STIX & TAXII\n\n### What is STIX?\n\n**Structured Threat Information eXpression (STIX)** is a standardized language for describing threat intelligence.\n\n### STIX 2.1 Objects\n\n| Object | Description |\n|--------|-------------|\n| **Attack Pattern** | TTPs (maps to ATT&CK) |\n| **Campaign** | Named threat activity |\n| **Indicator** | Observable pattern |\n| **Malware** | Malicious software |\n| **Threat Actor** | Individual/group |\n| **Tool** | Legitimate software used maliciously |\n| **Vulnerability** | CVE references |\n| **Infrastructure** | Attacker systems |\n\n### STIX Example\n\n```json\n{\n  "type": "indicator",\n  "id": "indicator--8e2e2d2b-17d4-4cbf-938f-98ee46b3cd3f",\n  "created": "2024-01-15T13:00:00.000Z",\n  "name": "Malicious IP Address",\n  "pattern": "[ipv4-addr:value = ''185.123.45.67'']",\n  "pattern_type": "stix",\n  "valid_from": "2024-01-15T13:00:00Z",\n  "labels": ["malicious-activity", "c2"]\n}\n```\n\n### What is TAXII?\n\n**Trusted Automated eXchange of Intelligence Information (TAXII)** is the transport protocol for STIX.\n\n### TAXII Components\n\n- **Collections**: Sets of STIX objects\n- **Channels**: Pub/sub for real-time sharing\n- **API Root**: Entry point for TAXII server\n\n### Common TAXII Feeds\n\n- AlienVault OTX\n- MISP (via TAXII)\n- FS-ISAC\n- US-CERT',
        'estimated_minutes', 18
    ),
    75,
    2
);

-- Task 3: YARA Rules
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Writing YARA Rules',
    'Master YARA rule syntax to detect malware based on patterns.',
    'reading',
    JSON_OBJECT(
        'content', '## YARA Rules\n\n### What is YARA?\n\nYARA is a pattern-matching tool used to identify and classify malware. Think of it as "grep for malware."\n\n### YARA Rule Structure\n\n```yara\nrule RuleName {\n    meta:\n        author = "Your Name"\n        description = "What it detects"\n        date = "2024-01-15"\n        \n    strings:\n        $s1 = "suspicious string" ascii\n        $s2 = { 4D 5A 90 00 }  // hex pattern\n        $s3 = /https?:\\/\\/[a-z0-9]+\\.evil\\.com/ // regex\n        \n    condition:\n        uint16(0) == 0x5A4D and  // MZ header\n        2 of ($s*)\n}\n```\n\n### String Types\n\n| Type | Syntax | Example |\n|------|--------|--------|\n| Text | "string" | "CreateRemoteThread" |\n| Hex | { XX XX } | { 4D 5A 90 00 } |\n| Regex | /pattern/ | /cmd\\.exe.*\\/c/ |\n\n### Modifiers\n\n- `ascii` - ASCII strings\n- `wide` - UTF-16 (Windows)\n- `nocase` - Case insensitive\n- `fullword` - Match whole word only\n\n### Condition Operators\n\n```yara\ncondition:\n    all of them           // All strings must match\n    any of ($a*)          // Any string starting with $a\n    2 of ($s1, $s2, $s3)  // At least 2 must match\n    #s1 > 5               // $s1 appears more than 5 times\n    @s1[1] < 100          // First occurrence before byte 100\n    filesize < 1MB        // File size condition\n```\n\n### Best Practices\n\n✅ Use unique strings (avoid common APIs)\n✅ Combine multiple indicators\n✅ Test against goodware to reduce FPs\n✅ Include metadata for context\n❌ Don''t rely on single strings\n❌ Don''t use overly broad patterns',
        'estimated_minutes', 20
    ),
    100,
    3
);

-- Task 4: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 4 Assessment',
    'Test your indicator management knowledge.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'According to the Pyramid of Pain, which indicator type is MOST difficult for attackers to change?',
                'options', JSON_ARRAY(
                    'File hashes',
                    'IP addresses',
                    'Domain names',
                    'TTPs (Tactics, Techniques, Procedures)'
                ),
                'correct_answer', 3,
                'explanation', 'TTPs represent adversary behavior patterns. Changing TTPs requires developing entirely new attack methodologies, making it the most costly for attackers.'
            ),
            JSON_OBJECT(
                'question', 'What is the relationship between STIX and TAXII?',
                'options', JSON_ARRAY(
                    'STIX is for network indicators, TAXII is for file indicators',
                    'STIX defines the data format, TAXII defines the transport protocol',
                    'TAXII is the newer version of STIX',
                    'They are competing standards from different organizations'
                ),
                'correct_answer', 1,
                'explanation', 'STIX is the standardized language/format for expressing threat intelligence. TAXII is the protocol for exchanging/transporting STIX data between systems.'
            ),
            JSON_OBJECT(
                'question', 'In a YARA rule, what does the condition "uint16(0) == 0x5A4D" check for?',
                'options', JSON_ARRAY(
                    'File size is exactly 0x5A4D bytes',
                    'The file contains the string "MZ"',
                    'The first two bytes are MZ (PE executable header)',
                    'The file hash starts with 5A4D'
                ),
                'correct_answer', 2,
                'explanation', '0x5A4D is "MZ" in hex (little-endian), the magic bytes for Windows PE executables. uint16(0) reads the first 2 bytes, checking if the file is a PE file.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    4
);

SELECT 'Module 4 inserted' as status, @module_id as module_id;
-- Module 5: MITRE ATT&CK Framework
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'MITRE ATT&CK Framework',
    'Master the MITRE ATT&CK framework for understanding adversary tactics and techniques, mapping threats, and improving defensive coverage.',
    3,
    5,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: ATT&CK Overview
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Understanding MITRE ATT&CK',
    'Learn the structure of MITRE ATT&CK: tactics, techniques, sub-techniques, and their relationships.',
    'reading',
    JSON_OBJECT(
        'content', '## MITRE ATT&CK Framework\n\n### What is ATT&CK?\n\n**Adversarial Tactics, Techniques, and Common Knowledge (ATT&CK)** is a globally-accessible knowledge base of adversary behavior based on real-world observations.\n\n### Structure\n\n```\n┌─────────────────────────────────────────────────────────────┐\n│                       TACTICS (Why)                          │\n│  Reconnaissance → Initial Access → Execution → Persistence  │\n│    → Privilege Escalation → Defense Evasion → Credential    │\n│    Access → Discovery → Lateral Movement → Collection →     │\n│    Command & Control → Exfiltration → Impact                │\n└─────────────────────────────────────────────────────────────┘\n                              │\n                              ▼\n┌─────────────────────────────────────────────────────────────┐\n│                    TECHNIQUES (How)                          │\n│  Example: T1566 - Phishing                                   │\n│    └── Sub-techniques:                                       │\n│        T1566.001 - Spearphishing Attachment                 │\n│        T1566.002 - Spearphishing Link                       │\n│        T1566.003 - Spearphishing via Service                │\n└─────────────────────────────────────────────────────────────┘\n                              │\n                              ▼\n┌─────────────────────────────────────────────────────────────┐\n│                    PROCEDURES (What)                         │\n│  "APT28 sent spearphishing emails with macro-enabled Word   │\n│   documents to defense contractors in January 2024"         │\n└─────────────────────────────────────────────────────────────┘\n```\n\n### The 14 Tactics\n\n| # | Tactic | Goal |\n|---|--------|------|\n| 1 | Reconnaissance | Gather info about target |\n| 2 | Resource Development | Build attack infrastructure |\n| 3 | Initial Access | Get into the network |\n| 4 | Execution | Run malicious code |\n| 5 | Persistence | Maintain foothold |\n| 6 | Privilege Escalation | Gain higher permissions |\n| 7 | Defense Evasion | Avoid detection |\n| 8 | Credential Access | Steal credentials |\n| 9 | Discovery | Learn the environment |\n| 10 | Lateral Movement | Move through network |\n| 11 | Collection | Gather target data |\n| 12 | Command & Control | Communicate with implants |\n| 13 | Exfiltration | Steal data out |\n| 14 | Impact | Destroy/disrupt systems |',
        'estimated_minutes', 15
    ),
    75,
    1
);

-- Task 2: Using ATT&CK Navigator
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'ATT&CK Navigator',
    'Learn to use ATT&CK Navigator for threat mapping, gap analysis, and defensive coverage assessment.',
    'reading',
    JSON_OBJECT(
        'content', '## ATT&CK Navigator\n\n### What is Navigator?\n\nATT&CK Navigator is a web application for visualizing ATT&CK matrices. It helps you:\n- Map threat actor TTPs\n- Assess detection coverage\n- Identify gaps in defenses\n- Compare groups/software\n\n**URL**: https://mitre-attack.github.io/attack-navigator/\n\n### Key Features\n\n**Layer Creation**:\n- Create custom layers\n- Color-code techniques\n- Add scores and comments\n- Import/export JSON\n\n**Use Cases**:\n\n| Use Case | How |\n|----------|-----|\n| **Threat Mapping** | Highlight techniques used by specific APT |\n| **Detection Coverage** | Mark green where you have detection |\n| **Gap Analysis** | Identify red gaps in coverage |\n| **Red Team Planning** | Plan adversary emulation |\n\n### Creating a Detection Coverage Map\n\n```json\n{\n  "name": "Detection Coverage",\n  "versions": {"attack": "14"},\n  "domain": "enterprise-attack",\n  "techniques": [\n    {\n      "techniqueID": "T1566.001",\n      "score": 75,\n      "comment": "Email gateway blocks 75% of phishing",\n      "color": "#31a354"\n    },\n    {\n      "techniqueID": "T1053.005",\n      "score": 0,\n      "comment": "No detection for scheduled tasks",\n      "color": "#de2d26"\n    }\n  ]\n}\n```\n\n### Navigator Tips\n\n✅ Layer multiple views (threat vs detection)\n✅ Export and version control your layers\n✅ Share with your team\n✅ Update regularly as coverage improves',
        'estimated_minutes', 15
    ),
    75,
    2
);

-- Task 3: Mapping Threats to ATT&CK
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Mapping Threats to ATT&CK',
    'Learn to analyze threat reports and map adversary behavior to ATT&CK techniques.',
    'reading',
    JSON_OBJECT(
        'content', '## Mapping Threats to ATT&CK\n\n### The Mapping Process\n\n```\n1. Read threat report/intel\n         ↓\n2. Identify behaviors described\n         ↓\n3. Break into atomic actions\n         ↓\n4. Map to ATT&CK techniques\n         ↓\n5. Validate mapping\n         ↓\n6. Document with sources\n```\n\n### Example: Mapping APT Activity\n\n**Report Excerpt**:\n> "The attacker sent a spearphishing email with a malicious Word document. When opened, the document executed a PowerShell command that downloaded a second-stage payload. The payload established persistence via a scheduled task and communicated over HTTPS to a C2 server."\n\n**Mapping**:\n\n| Behavior | ATT&CK Technique |\n|----------|------------------|\n| Spearphishing email with attachment | T1566.001 |\n| Macro executes PowerShell | T1059.001 |\n| Downloads second-stage | T1105 |\n| Scheduled task persistence | T1053.005 |\n| HTTPS C2 | T1071.001 |\n\n### Common Mapping Challenges\n\n⚠️ **Too specific**: Mapping to sub-techniques when only technique is justified\n⚠️ **Too broad**: Missing sub-techniques when details exist\n⚠️ **Assumptions**: Inferring techniques without evidence\n\n### Validation Questions\n\n- Is there evidence in the report for this technique?\n- Am I choosing the most accurate technique?\n- Have I captured all observed behaviors?\n- Would another analyst map this the same way?',
        'estimated_minutes', 18
    ),
    100,
    3
);

-- Task 4: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 5 Assessment',
    'Test your MITRE ATT&CK knowledge.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'In MITRE ATT&CK, what is the relationship between Tactics and Techniques?',
                'options', JSON_ARRAY(
                    'Tactics are more technical than Techniques',
                    'Tactics describe WHY (goals), Techniques describe HOW (methods)',
                    'Techniques are categories that contain multiple Tactics',
                    'They are interchangeable terms'
                ),
                'correct_answer', 1,
                'explanation', 'Tactics represent the adversary''s tactical goals (why they do something), while Techniques describe how they achieve those goals. For example, the tactic "Persistence" has techniques like "Scheduled Task" and "Registry Run Keys."'
            ),
            JSON_OBJECT(
                'question', 'What is the primary purpose of using ATT&CK Navigator?',
                'options', JSON_ARRAY(
                    'To automatically detect attacks in your network',
                    'To visualize ATT&CK matrices and assess coverage',
                    'To generate YARA rules from techniques',
                    'To communicate with STIX/TAXII servers'
                ),
                'correct_answer', 1,
                'explanation', 'ATT&CK Navigator is a visualization tool for creating layers that show threat actor TTPs, detection coverage, gaps, and comparisons - it doesn''t perform detection itself.'
            ),
            JSON_OBJECT(
                'question', 'When mapping a threat report to ATT&CK, what is the MOST important consideration?',
                'options', JSON_ARRAY(
                    'Map to as many techniques as possible',
                    'Only use techniques from the most common tactics',
                    'Ensure evidence from the report supports each mapping',
                    'Always map to sub-techniques rather than techniques'
                ),
                'correct_answer', 2,
                'explanation', 'Accurate mapping requires evidence. Each technique mapping should be supported by specific observations in the threat report to maintain accuracy and consistency.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    4
);

SELECT 'Module 5 inserted' as status, @module_id as module_id;
-- Module 6: Threat Hunting
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'Threat Hunting',
    'Learn proactive threat hunting techniques including hypothesis-driven hunting, log analysis, anomaly detection, and using threat intelligence to guide hunting activities.',
    3,
    6,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: Introduction to Threat Hunting
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Introduction to Threat Hunting',
    'Understand the purpose of threat hunting and how it differs from reactive detection.',
    'reading',
    JSON_OBJECT(
        'content', '## What is Threat Hunting?\n\n**Threat Hunting** is the proactive, iterative search through networks to detect and isolate advanced threats that evade existing security solutions.\n\n### Reactive vs Proactive Security\n\n| Approach | Description |\n|----------|-------------|\n| **Reactive** | Wait for alerts, then investigate |\n| **Proactive** | Assume breach, actively search for threats |\n\n### Why Hunt?\n\n- Attackers have average **dwell time of 200+ days**\n- Signature-based detection misses novel attacks\n- APTs specifically evade automated detection\n- Discover gaps in detection coverage\n- Find threats before impact\n\n### The Hunting Mindset\n\n```\n"Assume compromise. Prove otherwise."\n```\n\n### Hunting Maturity Model (HMM)\n\n| Level | Description |\n|-------|-------------|\n| **HMM0** | Initial - Relies on automated alerts |\n| **HMM1** | Minimal - Uses IOC searches |\n| **HMM2** | Procedural - Follows hunting procedures |\n| **HMM3** | Innovative - Creates new hypotheses |\n| **HMM4** | Leading - Automates successful hunts |\n\n### Key Requirements\n\n✅ Quality log data (visibility)\n✅ Threat intelligence (context)\n✅ Skilled analysts (expertise)\n✅ Time and management support\n✅ Hypothesis-driven methodology',
        'estimated_minutes', 12
    ),
    50,
    1
);

-- Task 2: Hypothesis-Driven Hunting
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Hypothesis-Driven Hunting',
    'Learn to create and test threat hypotheses using intelligence-informed hunting.',
    'reading',
    JSON_OBJECT(
        'content', '## Hypothesis-Driven Hunting\n\n### The Scientific Method for Hunting\n\n```\n1. Form hypothesis (informed by intel)\n         ↓\n2. Determine data needed\n         ↓\n3. Query/analyze data\n         ↓\n4. Investigate findings\n         ↓\n5. Document results\n         ↓\n6. Improve detection/create rules\n```\n\n### Forming Hypotheses\n\n**Intelligence-Informed**:\n> "APT29 is targeting our sector using spearphishing with ISO files. We should hunt for ISO file execution followed by suspicious child processes."\n\n**ATT&CK-Based**:\n> "Attackers commonly use scheduled tasks for persistence (T1053.005). We should hunt for newly created scheduled tasks by non-admin users."\n\n**Anomaly-Based**:\n> "PowerShell is normally run by IT admins. We should hunt for PowerShell execution by non-IT users."\n\n### Hypothesis Examples\n\n| Technique | Hypothesis |\n|-----------|------------|\n| T1566.001 | "Malicious attachments executed from Outlook TEMP folders" |\n| T1059.001 | "PowerShell downloading content from external URLs" |\n| T1053.005 | "Scheduled tasks created in unusual directories" |\n| T1021.001 | "RDP connections from non-admin workstations" |\n| T1071.001 | "HTTPS connections to newly registered domains" |\n\n### Documenting Hunts\n\n```markdown\n## Hunt: Suspicious PowerShell Execution\n**Date**: 2024-01-15\n**Hypothesis**: Attackers using encoded PowerShell commands\n**ATT&CK**: T1059.001, T1027\n**Data Sources**: EDR, Windows Event Logs (4104)\n**Query**: [query here]\n**Findings**: 3 anomalous executions found\n**Outcome**: 2 false positives, 1 escalated\n**Detection Created**: Yes - Sigma rule created\n```',
        'estimated_minutes', 18
    ),
    75,
    2
);

-- Task 3: Hunting Techniques
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Hunting Techniques & Queries',
    'Learn practical hunting techniques using log analysis, stack counting, and anomaly detection.',
    'reading',
    JSON_OBJECT(
        'content', '## Hunting Techniques\n\n### Stack Counting (Least Frequency Analysis)\n\nFind rare occurrences that stand out from normal activity.\n\n**Example**: Find unusual parent-child process relationships:\n```sql\n-- Stack count of parent-child combinations\nSELECT parent_process, child_process, COUNT(*) as cnt\nFROM process_events\nWHERE timestamp > NOW() - INTERVAL 7 DAY\nGROUP BY parent_process, child_process\nORDER BY cnt ASC\nLIMIT 50;\n```\n\n### Hunting for Living-off-the-Land (LOLBins)\n\n**Common LOLBins to Hunt**:\n- certutil.exe (downloading files)\n- mshta.exe (executing HTA)\n- regsvr32.exe (loading DLLs)\n- rundll32.exe (executing code)\n- wmic.exe (remote execution)\n\n**Query Example (Splunk)**:\n```splunk\nindex=windows EventCode=1\n| where match(CommandLine, "(?i)(certutil.*-urlcache|mshta.*http)")\n| table _time, host, user, CommandLine\n```\n\n### Network Hunting\n\n**DNS Anomalies**:\n- Long domain names (DGA)\n- High entropy domains\n- Queries to rare TLDs\n- DNS tunneling (TXT records)\n\n**Beaconing Detection**:\n```python\n# Look for regular intervals in connections\n# C2 often beacons every X minutes\nfrom scipy import stats\nintervals = calculate_connection_intervals(host)\nif stats.variation(intervals) < 0.1:  # Low variance = beaconing\n    alert("Potential C2 beaconing detected")\n```\n\n### User Behavior Hunting\n\n- First-time execution of programs\n- Logins at unusual hours\n- Access to sensitive resources\n- Privilege escalation attempts\n\n### Converting Hunts to Detections\n\nWhen a hunt finds a true positive:\n1. Create detection rule (Sigma, YARA, etc.)\n2. Add to SIEM/EDR\n3. Document the TTP coverage\n4. Update ATT&CK Navigator',
        'estimated_minutes', 20
    ),
    100,
    3
);

-- Task 4: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 6 Assessment',
    'Test your threat hunting knowledge.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'What is the fundamental difference between threat hunting and traditional SOC monitoring?',
                'options', JSON_ARRAY(
                    'Hunting uses more advanced tools',
                    'Hunting is proactive and assumes breach, monitoring is reactive',
                    'Hunting is automated while monitoring is manual',
                    'Hunting only looks at network traffic'
                ),
                'correct_answer', 1,
                'explanation', 'Threat hunting assumes systems may already be compromised and proactively searches for threats. Traditional SOC monitoring is reactive, waiting for alerts to investigate.'
            ),
            JSON_OBJECT(
                'question', 'In hypothesis-driven hunting, what is the BEST source for forming initial hypotheses?',
                'options', JSON_ARRAY(
                    'Random sampling of logs',
                    'User complaints about system performance',
                    'Threat intelligence about relevant adversaries',
                    'Vendor security advisories'
                ),
                'correct_answer', 2,
                'explanation', 'Intelligence about threat actors targeting your sector provides context for relevant TTPs to hunt. This makes hunts more focused and likely to find real threats.'
            ),
            JSON_OBJECT(
                'question', 'What is "stack counting" in threat hunting?',
                'options', JSON_ARRAY(
                    'Counting the number of network packets',
                    'Finding rare occurrences by counting frequencies',
                    'Measuring CPU stack memory usage',
                    'Tracking the number of security alerts'
                ),
                'correct_answer', 1,
                'explanation', 'Stack counting (least frequency analysis) identifies anomalies by finding rare events that deviate from normal patterns - the unusual items at the bottom of the stack.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    4
);

SELECT 'Module 6 inserted' as status, @module_id as module_id;
-- Module 7: Intelligence Platforms (MISP, OpenCTI)
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'Intelligence Platforms',
    'Learn to use Threat Intelligence Platforms (TIPs) including MISP, OpenCTI, and commercial solutions for managing, correlating, and sharing threat intelligence.',
    3,
    7,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: Introduction to TIPs
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Threat Intelligence Platforms Overview',
    'Understand the purpose and capabilities of Threat Intelligence Platforms.',
    'reading',
    JSON_OBJECT(
        'content', '## Threat Intelligence Platforms (TIPs)\n\n### What is a TIP?\n\nA **Threat Intelligence Platform** aggregates, correlates, and operationalizes threat intelligence from multiple sources.\n\n### Core Capabilities\n\n| Capability | Description |\n|------------|-------------|\n| **Aggregation** | Collect intel from multiple sources |\n| **Normalization** | Convert to standard formats (STIX) |\n| **Correlation** | Link related indicators and actors |\n| **Enrichment** | Add context (reputation, WHOIS) |\n| **Analysis** | Tools for investigation |\n| **Sharing** | Export and share with partners |\n| **Integration** | Push to SIEM, EDR, firewalls |\n\n### TIP Landscape\n\n**Open Source**:\n- MISP (Malware Information Sharing Platform)\n- OpenCTI\n- YETI\n- CRITs\n\n**Commercial**:\n- ThreatConnect\n- Recorded Future\n- Anomali ThreatStream\n- Mandiant Advantage\n- Palo Alto XSOAR\n\n### Choosing a TIP\n\n**Questions to Ask**:\n- What data sources do we need to integrate?\n- Do we need to share with external partners?\n- What integrations with our security stack?\n- Open source acceptable or need commercial support?\n- What is our budget?\n\n### Integration Architecture\n\n```\n┌─────────────────────────────────────────┐\n│           Threat Intel Platform          │\n│  ┌─────────┐  ┌─────────┐  ┌─────────┐  │\n│  │ OSINT   │  │ Feeds   │  │ Internal│  │\n│  └────┬────┘  └────┬────┘  └────┬────┘  │\n│       └───────────┬────────────┘        │\n│              ┌────┴────┐                 │\n│              │ Correlate│                │\n│              └────┬────┘                 │\n└───────────────────┼─────────────────────┘\n                    │\n     ┌──────────────┼──────────────┐\n     │              │              │\n┌────┴────┐   ┌────┴────┐   ┌────┴────┐\n│  SIEM   │   │   EDR   │   │Firewall │\n└─────────┘   └─────────┘   └─────────┘\n```',
        'estimated_minutes', 12
    ),
    50,
    1
);

-- Task 2: MISP Deep Dive
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'MISP Deep Dive',
    'Master MISP for threat intelligence sharing, event management, and community collaboration.',
    'reading',
    JSON_OBJECT(
        'content', '## MISP: Malware Information Sharing Platform\n\n### What is MISP?\n\nMISP is an open-source threat intelligence platform designed to improve sharing of structured threat information.\n\n### Key Concepts\n\n**Events**: Container for related intel\n```\nEvent: "APT28 Phishing Campaign Jan 2024"\n├── Attributes (IOCs)\n│   ├── IP: 185.123.45.67\n│   ├── Domain: evil-c2.com\n│   └── Hash: abc123...\n├── Objects (structured data)\n│   ├── Email object\n│   └── File object\n├── Tags\n│   ├── tlp:amber\n│   └── APT28\n└── Galaxies\n    └── Threat Actor: APT28\n```\n\n### MISP Features\n\n| Feature | Description |\n|---------|-------------|\n| **Attributes** | Individual IOCs with types |\n| **Objects** | Structured templates (file, email) |\n| **Taxonomies** | Standardized tags (TLP, PAP) |\n| **Galaxies** | Knowledge clusters (actors, malware) |\n| **Feeds** | Import external intel feeds |\n| **Sharing Groups** | Control who sees what |\n| **Correlations** | Automatic linking of related data |\n\n### MISP Workflows\n\n**Creating an Event**:\n1. New Event with title, date, threat level\n2. Add attributes (IPs, hashes, domains)\n3. Apply tags (TLP, source, actor)\n4. Link to galaxies (threat actor, malware)\n5. Publish to sharing groups\n\n**Using MISP API**:\n```python\nfrom pymisp import PyMISP\n\nmisp = PyMISP(url, key, ssl=False)\n\n# Search for IOCs\nresult = misp.search(\n    controller="attributes",\n    value="185.123.45.67"\n)\n\n# Create event\nevent = misp.new_event(\n    distribution=0,\n    info="New Phishing Campaign"\n)\nmisp.add_attribute(event, "ip-dst", "185.123.45.67")\n```',
        'estimated_minutes', 18
    ),
    75,
    2
);

-- Task 3: OpenCTI
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'OpenCTI Platform',
    'Learn OpenCTI for advanced threat intelligence management and analysis.',
    'reading',
    JSON_OBJECT(
        'content', '## OpenCTI Platform\n\n### What is OpenCTI?\n\nOpenCTI is an open-source platform for managing cyber threat intelligence with advanced visualization and analysis capabilities.\n\n### Key Features\n\n**Native STIX 2.1 Support**:\n- All data stored as STIX objects\n- Full relationship modeling\n- Import/export STIX bundles\n\n**Knowledge Graph**:\n- Visual exploration of relationships\n- Connect indicators → malware → actors\n- Timeline analysis\n\n**Connectors**:\n- MISP import/export\n- VirusTotal enrichment\n- AbuseIPDB\n- Shodan\n- MITRE ATT&CK\n- RSS feeds\n\n### OpenCTI Entities\n\n| Entity Type | Examples |\n|-------------|----------|\n| **Threat Actors** | APT28, FIN7 |\n| **Intrusion Sets** | Named operations |\n| **Campaigns** | Specific attack waves |\n| **Malware** | Emotet, Cobalt Strike |\n| **Tools** | PsExec, Mimikatz |\n| **Vulnerabilities** | CVE-2024-XXXX |\n| **Indicators** | IOCs |\n| **Attack Patterns** | MITRE techniques |\n\n### OpenCTI vs MISP\n\n| Aspect | MISP | OpenCTI |\n|--------|------|--------|\n| **Focus** | IOC sharing | Knowledge management |\n| **Data Model** | Events/Attributes | STIX objects |\n| **Visualization** | Basic | Advanced graph |\n| **Sharing** | Strong community | Connector-based |\n| **Complexity** | Moderate | Higher |\n\n### When to Use Each\n\n**MISP**: IOC sharing with partners, feed management\n**OpenCTI**: Deep analysis, knowledge base, actor tracking\n**Both**: Many organizations use both together',
        'estimated_minutes', 15
    ),
    75,
    3
);

-- Task 4: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 7 Assessment',
    'Test your knowledge of threat intelligence platforms.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'What is the primary purpose of a Threat Intelligence Platform (TIP)?',
                'options', JSON_ARRAY(
                    'To replace SIEM systems entirely',
                    'To aggregate, correlate, and operationalize threat intelligence',
                    'To perform automated incident response',
                    'To scan networks for vulnerabilities'
                ),
                'correct_answer', 1,
                'explanation', 'TIPs aggregate intel from multiple sources, correlate related data, and help operationalize it by integrating with security tools like SIEMs and firewalls.'
            ),
            JSON_OBJECT(
                'question', 'In MISP, what is the difference between Attributes and Objects?',
                'options', JSON_ARRAY(
                    'Attributes are for malware, Objects are for threat actors',
                    'Attributes are single IOCs, Objects are structured templates',
                    'Objects are more accurate than Attributes',
                    'There is no difference, they are interchangeable'
                ),
                'correct_answer', 1,
                'explanation', 'Attributes are individual IOCs (IP, hash, domain). Objects are structured templates that combine multiple related attributes (e.g., a file object with hash, filename, size).'
            ),
            JSON_OBJECT(
                'question', 'What is the key advantage of OpenCTI over MISP?',
                'options', JSON_ARRAY(
                    'Better IOC sharing with external partners',
                    'Native STIX support and advanced knowledge graph visualization',
                    'Simpler installation process',
                    'More active community'
                ),
                'correct_answer', 1,
                'explanation', 'OpenCTI excels at knowledge management with native STIX 2.1 support and powerful graph visualization for exploring relationships between entities.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    4
);

SELECT 'Module 7 inserted' as status, @module_id as module_id;
-- Module 8: Reporting & Dissemination
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'Reporting & Dissemination',
    'Learn to create effective threat intelligence reports for different audiences, use Traffic Light Protocol (TLP) for sharing controls, and establish intelligence dissemination workflows.',
    2,
    8,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Task 1: Intelligence Report Writing
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Writing Effective Intel Reports',
    'Learn the structure and best practices for creating threat intelligence reports.',
    'reading',
    JSON_OBJECT(
        'content', '## Intelligence Report Writing\n\n### Report Types\n\n| Type | Audience | Frequency | Length |\n|------|----------|-----------|--------|\n| **Flash Alert** | All stakeholders | As needed | 1 page |\n| **Threat Advisory** | Security teams | Weekly | 2-3 pages |\n| **Campaign Report** | Management | Monthly | 5-10 pages |\n| **Strategic Assessment** | Executives | Quarterly | Executive summary |\n\n### Report Structure\n\n```\n┌─────────────────────────────────────┐\n│         EXECUTIVE SUMMARY           │  ← Key findings, TL;DR\n├─────────────────────────────────────┤\n│         KEY FINDINGS                │  ← 3-5 bullet points\n├─────────────────────────────────────┤\n│         THREAT DETAILS              │  ← Full analysis\n│  • Actor Profile                    │\n│  • TTPs Observed                    │\n│  • Indicators                       │\n├─────────────────────────────────────┤\n│         RECOMMENDATIONS             │  ← Actionable steps\n├─────────────────────────────────────┤\n│         APPENDIX                    │  ← IOCs, references\n└─────────────────────────────────────┘\n```\n\n### Writing Tips\n\n✅ **Lead with conclusions** - Busy readers need key points first\n✅ **Be specific** - "Patch CVE-2024-1234" not "Apply patches"\n✅ **Know your audience** - Technical vs non-technical\n✅ **Cite sources** - Attribution builds trust\n✅ **Use visuals** - Timelines, charts, screenshots\n✅ **Provide confidence levels** - "High confidence" vs "Possibly"\n\n### Confidence Language\n\n| Term | Probability |\n|------|-------------|\n| Almost certainly | >90% |\n| Highly likely | 75-90% |\n| Likely | 55-75% |\n| Possible | 25-55% |\n| Unlikely | 10-25% |\n| Remote | <10% |',
        'estimated_minutes', 15
    ),
    75,
    1
);

-- Task 2: TLP and Sharing Controls
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Traffic Light Protocol (TLP)',
    'Master the Traffic Light Protocol for controlling intelligence sharing.',
    'reading',
    JSON_OBJECT(
        'content', '## Traffic Light Protocol (TLP)\n\n### What is TLP?\n\nTLP is a set of designations used to ensure that sensitive information is shared with the appropriate audience.\n\n### TLP 2.0 Designations\n\n| Color | Sharing | Example Use |\n|-------|---------|-------------|\n| 🔴 **TLP:RED** | Named recipients only | Active investigation details |\n| 🟠 **TLP:AMBER** | Organization + clients | Sector-specific threats |\n| 🟠 **TLP:AMBER+STRICT** | Organization only | Internal incidents |\n| 🟢 **TLP:GREEN** | Community | General threat advisories |\n| ⚪ **TLP:CLEAR** | Public | Press releases |\n\n### When to Use Each Level\n\n**TLP:RED** 🔴\n- Source protection required\n- Active investigation\n- Named individuals\n- Ongoing operations\n\n**TLP:AMBER** 🟠\n- Client-relevant threats\n- Sector intelligence\n- Limited external sharing\n\n**TLP:GREEN** 🟢\n- General awareness\n- Widely applicable\n- Community benefit\n\n**TLP:CLEAR** ⚪\n- Public information\n- Already published\n- Marketing/awareness\n\n### Applying TLP\n\n```markdown\n# Threat Advisory: New Ransomware Campaign\n**TLP:AMBER** - For organizational use and clients\n\n## Summary\nA new ransomware variant has been observed targeting...\n```\n\n### Common Mistakes\n\n❌ Over-classifying (everything TLP:RED)\n❌ Under-classifying (sensitive data as CLEAR)\n❌ Not marking at all\n❌ Ignoring TLP when sharing',
        'estimated_minutes', 12
    ),
    75,
    2
);

-- Task 3: Dissemination Workflows
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Intelligence Dissemination',
    'Establish workflows for distributing intelligence to different stakeholders.',
    'reading',
    JSON_OBJECT(
        'content', '## Intelligence Dissemination\n\n### Dissemination Channels\n\n| Channel | Use Case | TLP |\n|---------|----------|-----|\n| **Email** | Reports, advisories | All |\n| **TIP Portal** | IOCs, events | AMBER/GREEN |\n| **TAXII Feed** | Automated sharing | GREEN/CLEAR |\n| **SIEM Integration** | Detection rules | N/A |\n| **Slack/Teams** | Flash alerts | RED/AMBER |\n| **ISAC Portal** | Sector sharing | AMBER/GREEN |\n\n### Stakeholder Matrix\n\n```\n┌──────────────┬──────────────┬──────────────┬──────────────┐\n│              │  Strategic   │ Operational  │   Tactical   │\n├──────────────┼──────────────┼──────────────┼──────────────┤\n│ Executives   │     ✅       │      ○       │      ✗       │\n│ Sec Managers │     ✅       │     ✅        │      ○       │\n│ SOC Analysts │     ○        │     ✅        │     ✅        │\n│ IT Ops       │     ✗        │      ○       │     ✅        │\n│ Red Team     │     ○        │     ✅        │     ✅        │\n└──────────────┴──────────────┴──────────────┴──────────────┘\n✅ = Primary recipient  ○ = FYI  ✗ = Not applicable\n```\n\n### Automated Dissemination\n\n**SIEM Integration**:\n- Push IOCs to SIEM for correlation\n- Create detection rules from TTPs\n- Alert on matches\n\n**EDR Integration**:\n- Push file hashes for blocking\n- Behavioral indicators\n\n**Firewall/Proxy**:\n- Block malicious IPs/domains\n- URL categories\n\n### Feedback Collection\n\nComplete the intelligence cycle:\n\n```\n1. Send intel to consumers\n         ↓\n2. Track delivery/read rates\n         ↓\n3. Collect feedback surveys\n         ↓\n4. Measure action taken\n         ↓\n5. Refine future products\n```',
        'estimated_minutes', 15
    ),
    100,
    3
);

-- Task 4: Quiz
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Module 8 Assessment',
    'Test your knowledge of intelligence reporting and dissemination.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            JSON_OBJECT(
                'question', 'What should be the FIRST section of a threat intelligence report?',
                'options', JSON_ARRAY(
                    'Detailed technical analysis',
                    'Executive summary with key findings',
                    'Full list of IOCs',
                    'Methodology description'
                ),
                'correct_answer', 1,
                'explanation', 'Lead with conclusions. Busy executives and stakeholders need key findings upfront. Detailed analysis follows for those who want to dive deeper.'
            ),
            JSON_OBJECT(
                'question', 'You have intelligence about an active intrusion at a specific organization. What TLP should you use?',
                'options', JSON_ARRAY(
                    'TLP:CLEAR - to warn everyone',
                    'TLP:GREEN - for community awareness',
                    'TLP:RED - named recipients only',
                    'TLP:AMBER - for the sector'
                ),
                'correct_answer', 2,
                'explanation', 'TLP:RED is for highly sensitive information that should only go to named recipients. Active intrusion details could endanger ongoing response if shared broadly.'
            ),
            JSON_OBJECT(
                'question', 'Which stakeholder should receive primarily TACTICAL intelligence?',
                'options', JSON_ARRAY(
                    'Board of Directors',
                    'CISO',
                    'SOC Analysts',
                    'HR Department'
                ),
                'correct_answer', 2,
                'explanation', 'SOC Analysts need tactical intelligence (IOCs, detection rules) to identify and respond to threats. Executives need strategic intelligence about trends and risks.'
            )
        ),
        'passing_score', 70,
        'time_limit_minutes', 10
    ),
    100,
    4
);

SELECT 'Module 8 inserted' as status, @module_id as module_id;
-- Final Exam: Threat Intelligence Certification
-- Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

-- Insert Final Exam as a module
INSERT INTO modules (learning_path_id, title, description, estimated_hours, display_order, is_active)
VALUES (
    @path_id,
    'Final Certification Exam',
    'Comprehensive exam covering all Threat Intelligence modules. Score 80% or higher to earn your Threat Intelligence Analyst certification.',
    1,
    9,
    TRUE
);

SET @module_id = LAST_INSERT_ID();

-- Final Exam Task (10 questions covering all modules)
INSERT INTO tasks (module_id, title, description, task_type, task_data, xp_reward, display_order)
VALUES (
    @module_id,
    'Threat Intelligence Certification Exam',
    'Final exam covering CTI fundamentals, OSINT, malware analysis, indicators, MITRE ATT&CK, threat hunting, intelligence platforms, and reporting.',
    'quiz',
    JSON_OBJECT(
        'questions', JSON_ARRAY(
            -- Q1: CTI Fundamentals
            JSON_OBJECT(
                'question', 'What distinguishes cyber threat intelligence from raw security data?',
                'options', JSON_ARRAY(
                    'Intelligence is collected automatically, data requires human input',
                    'Intelligence is analyzed, contextualized, and actionable for decision-making',
                    'Data is more reliable than intelligence',
                    'Intelligence only comes from commercial sources'
                ),
                'correct_answer', 1,
                'explanation', 'CTI transforms raw data into actionable insights through analysis and contextualization, enabling informed security decisions.'
            ),
            -- Q2: Intelligence Cycle
            JSON_OBJECT(
                'question', 'A threat intelligence team receives feedback that their reports arrive too late to be useful. Which phase of the intelligence cycle needs improvement?',
                'options', JSON_ARRAY(
                    'Collection',
                    'Analysis',
                    'Dissemination',
                    'Direction'
                ),
                'correct_answer', 2,
                'explanation', 'Dissemination involves delivering intelligence to consumers in a timely manner. Late delivery indicates process issues in the dissemination phase.'
            ),
            -- Q3: OSINT
            JSON_OBJECT(
                'question', 'Which technique would reveal all subdomains protected by SSL certificates for a target domain?',
                'options', JSON_ARRAY(
                    'WHOIS lookup',
                    'Certificate Transparency log search',
                    'Reverse DNS lookup',
                    'Traceroute'
                ),
                'correct_answer', 1,
                'explanation', 'Certificate Transparency logs record all issued SSL certificates. Searching these logs (via crt.sh) reveals subdomains with certificates.'
            ),
            -- Q4: Malware Analysis
            JSON_OBJECT(
                'question', 'During static analysis, you find the malware imports VirtualAllocEx and WriteProcessMemory. What capability does this suggest?',
                'options', JSON_ARRAY(
                    'File encryption for ransomware',
                    'Process injection or code injection',
                    'Network communication',
                    'Registry persistence'
                ),
                'correct_answer', 1,
                'explanation', 'VirtualAllocEx allocates memory in another process, WriteProcessMemory writes to it - classic process injection technique.'
            ),
            -- Q5: Pyramid of Pain
            JSON_OBJECT(
                'question', 'According to the Pyramid of Pain, which indicator type causes the MOST difficulty for attackers when blocked?',
                'options', JSON_ARRAY(
                    'File hashes (MD5/SHA256)',
                    'IP addresses',
                    'TTPs (Tactics, Techniques, Procedures)',
                    'Domain names'
                ),
                'correct_answer', 2,
                'explanation', 'TTPs represent adversary behavior. Changing TTPs requires developing entirely new methodologies - the most costly change for attackers.'
            ),
            -- Q6: STIX/TAXII
            JSON_OBJECT(
                'question', 'An organization wants to automatically ingest threat intelligence from external feeds. Which standard defines the transport protocol?',
                'options', JSON_ARRAY(
                    'STIX',
                    'YARA',
                    'TAXII',
                    'OpenIOC'
                ),
                'correct_answer', 2,
                'explanation', 'TAXII (Trusted Automated eXchange of Intelligence Information) is the transport protocol for exchanging STIX-formatted intelligence.'
            ),
            -- Q7: YARA Rules
            JSON_OBJECT(
                'question', 'In a YARA rule, what does the condition "uint16(0) == 0x5A4D" verify?',
                'options', JSON_ARRAY(
                    'The file size is exactly 0x5A4D bytes',
                    'The file is a Windows PE executable (MZ header)',
                    'The file contains the string "Admin"',
                    'The file hash matches a known sample'
                ),
                'correct_answer', 1,
                'explanation', '0x5A4D is "MZ" in little-endian hex - the magic bytes that identify Windows PE executables. uint16(0) reads bytes 0-1.'
            ),
            -- Q8: MITRE ATT&CK
            JSON_OBJECT(
                'question', 'An attacker uses certutil.exe to download a malicious payload. Which ATT&CK technique does this represent?',
                'options', JSON_ARRAY(
                    'T1105 - Ingress Tool Transfer',
                    'T1566 - Phishing',
                    'T1078 - Valid Accounts',
                    'T1053 - Scheduled Task'
                ),
                'correct_answer', 0,
                'explanation', 'Using certutil to download files is a form of Ingress Tool Transfer (T1105), where legitimate tools transfer malicious payloads.'
            ),
            -- Q9: Threat Hunting
            JSON_OBJECT(
                'question', 'What is the PRIMARY advantage of hypothesis-driven threat hunting over IOC-based searching?',
                'options', JSON_ARRAY(
                    'It requires less skilled analysts',
                    'It can detect threats without prior knowledge of specific indicators',
                    'It is completely automated',
                    'It only uses network data'
                ),
                'correct_answer', 1,
                'explanation', 'Hypothesis-driven hunting focuses on adversary behaviors (TTPs), allowing detection of threats even when specific IOCs are unknown.'
            ),
            -- Q10: TLP
            JSON_OBJECT(
                'question', 'Intelligence about an ongoing ransomware negotiation with specific victim details should be shared as:',
                'options', JSON_ARRAY(
                    'TLP:CLEAR - for public awareness',
                    'TLP:GREEN - for the security community',
                    'TLP:AMBER - for the sector',
                    'TLP:RED - for named recipients only'
                ),
                'correct_answer', 3,
                'explanation', 'Active incidents with victim details require TLP:RED to protect the victim and ongoing response activities.'
            ),
            -- Q11: Intelligence Platforms
            JSON_OBJECT(
                'question', 'What is the key difference between MISP and OpenCTI?',
                'options', JSON_ARRAY(
                    'MISP is commercial, OpenCTI is open source',
                    'MISP focuses on IOC sharing, OpenCTI provides knowledge graph analysis',
                    'OpenCTI only works with YARA rules',
                    'MISP cannot import STIX data'
                ),
                'correct_answer', 1,
                'explanation', 'MISP excels at IOC sharing with partners, while OpenCTI provides advanced knowledge management with native STIX and graph visualization.'
            ),
            -- Q12: Report Writing
            JSON_OBJECT(
                'question', 'When writing a threat intelligence report for executives, the FIRST section should contain:',
                'options', JSON_ARRAY(
                    'Complete technical methodology',
                    'Full list of IOCs',
                    'Executive summary with key findings and recommendations',
                    'Raw data from sandboxes'
                ),
                'correct_answer', 2,
                'explanation', 'Lead with conclusions. Executives need key findings and recommendations upfront; technical details belong in appendices.'
            )
        ),
        'passing_score', 80,
        'time_limit_minutes', 30,
        'is_certification_exam', true
    ),
    500,
    1
);

-- Update learning path estimated hours to reflect all modules
UPDATE learning_paths 
SET estimated_hours = 26
WHERE id = @path_id;

SELECT 'Final Exam inserted' as status, @module_id as module_id;

-- Summary query
SELECT 
    'Threat Intelligence Path Complete!' as status,
    (SELECT COUNT(*) FROM modules WHERE learning_path_id = @path_id) as total_modules,
    (SELECT COUNT(*) FROM tasks t JOIN modules m ON t.module_id = m.id WHERE m.learning_path_id = @path_id) as total_tasks;
-- Migrate quiz questions from task_data to lesson_questions table
-- For Threat Intelligence Learning Path

SET @path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);

-- First, insert dummy lesson_content for quiz tasks (required by frontend)
INSERT INTO lesson_content (task_id, content, content_type, reading_time_minutes)
SELECT 
    t.id,
    CONCAT('# ', t.title, '\n\nComplete the assessment below to test your knowledge.'),
    'markdown',
    5
FROM tasks t
JOIN modules m ON t.module_id = m.id
WHERE m.learning_path_id = @path_id
AND t.task_type = 'quiz'
AND t.id NOT IN (SELECT task_id FROM lesson_content)
ON DUPLICATE KEY UPDATE content = VALUES(content);

-- Now insert questions from task_data JSON
-- We need to handle this with a procedure since MySQL doesn't have good JSON array iteration

DELIMITER //

DROP PROCEDURE IF EXISTS migrate_quiz_questions//

CREATE PROCEDURE migrate_quiz_questions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_task_id INT;
    DECLARE v_questions JSON;
    DECLARE v_question JSON;
    DECLARE v_options JSON;
    DECLARE v_question_text TEXT;
    DECLARE v_correct_answer TEXT;
    DECLARE v_explanation TEXT;
    DECLARE v_i INT;
    DECLARE v_len INT;
    DECLARE v_correct_idx INT;
    
    DECLARE task_cursor CURSOR FOR
        SELECT t.id, JSON_EXTRACT(t.task_data, '$.questions')
        FROM tasks t
        JOIN modules m ON t.module_id = m.id
        WHERE m.learning_path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1)
        AND t.task_type = 'quiz'
        AND JSON_EXTRACT(t.task_data, '$.questions') IS NOT NULL;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN task_cursor;
    
    task_loop: LOOP
        FETCH task_cursor INTO v_task_id, v_questions;
        IF done THEN
            LEAVE task_loop;
        END IF;
        
        -- Delete existing questions for this task
        DELETE FROM lesson_questions WHERE task_id = v_task_id;
        
        SET v_len = JSON_LENGTH(v_questions);
        SET v_i = 0;
        
        WHILE v_i < v_len DO
            SET v_question = JSON_EXTRACT(v_questions, CONCAT('$[', v_i, ']'));
            SET v_question_text = JSON_UNQUOTE(JSON_EXTRACT(v_question, '$.question'));
            SET v_options = JSON_EXTRACT(v_question, '$.options');
            SET v_correct_idx = JSON_EXTRACT(v_question, '$.correct_answer');
            SET v_correct_answer = JSON_UNQUOTE(JSON_EXTRACT(v_options, CONCAT('$[', v_correct_idx, ']')));
            SET v_explanation = JSON_UNQUOTE(JSON_EXTRACT(v_question, '$.explanation'));
            
            INSERT INTO lesson_questions (task_id, question_text, question_order, correct_answer, options, hint)
            VALUES (v_task_id, v_question_text, v_i + 1, v_correct_answer, v_options, v_explanation);
            
            SET v_i = v_i + 1;
        END WHILE;
        
    END LOOP;
    
    CLOSE task_cursor;
END//

DELIMITER ;

CALL migrate_quiz_questions();

DROP PROCEDURE IF EXISTS migrate_quiz_questions;

-- Verify migration
SELECT 'Questions migrated' as status, COUNT(*) as count FROM lesson_questions lq
JOIN tasks t ON lq.task_id = t.id
JOIN modules m ON t.module_id = m.id
WHERE m.learning_path_id = (SELECT id FROM learning_paths WHERE title = 'Threat Intelligence' LIMIT 1);