const db = require('../db');

const overview = {
    id: 36,
    title: 'Advanced SIEM',
    description: 'Deep dive into SIEM engineering: Query optimization, custom parsers, and correlation logic.',
    learning_path_id: 4
};

const tasks = [
    { id: 361, title: 'SIEM Architecture Deep Dive' },
    { id: 362, title: 'Log Parsing & Normalization' },
    { id: 363, title: 'Advanced Correlation Rules' },
    { id: 364, title: 'Query Optimization' },
    { id: 365, title: 'Building Detection-as-Code' },
    { id: 366, title: 'Final Quiz' }
];

const lessons = {
    361: {
        content: `# SIEM Architecture Deep Dive

At scale, SIEM architecture becomes critical for performance and reliability.

## Ingestion Pipeline
\`\`\`
Log Sources → Collectors → Message Queue → Parsers → Index → Hot/Warm/Cold Storage
\`\`\`

## Scaling Strategies
*   **Horizontal Scaling**: Add more indexers/search heads.
*   **Tiering**: Hot (SSD, 7 days) → Warm (HDD, 30 days) → Cold (Archive, 1 year).
*   **Data Lake Integration**: Send raw logs to S3/ADLS for long-term, cheap storage.

## High Availability Considerations
*   **Cluster Mode**: Splunk Search Head Cluster, Elastic Cross-Cluster Replication.
*   **Ingestion Buffering**: Kafka/Kinesis as a buffer to handle spikes.
*   **Geo-Redundancy**: Multi-region for disaster recovery.

## Key Metrics to Monitor
| Metric | Healthy Threshold |
|--------|--------------------|
| Ingestion Lag | < 5 minutes |
| Search Latency (p95) | < 10 seconds |
| Index Queue Size | Not growing |
| License Usage | < 90% |`,
        questions: [
            ['What is the purpose of a message queue (Kafka) in SIEM?', 'Buffer ingestion spikes and decouple collection from indexing', 'Buffer', ['Speed up queries', 'Buffer ingestion spikes and decouple collection from indexing', 'Store logs long-term', 'Replace the SIEM']],
            ['In tiered storage, where should the last 7 days of logs reside?', 'Hot tier (SSD)', 'Fast', ['Cold archive', 'Hot tier (SSD)', 'Data lake', 'Backup tapes']],
            ['What does Search Head Cluster provide in Splunk?', 'High availability and load balancing for searches', 'HA', ['Faster indexing', 'High availability and load balancing for searches', 'Better parsing', 'Cheaper storage']]
        ]
    },
    362: {
        content: `# Log Parsing & Normalization

Garbage in, garbage out. Your detections are only as good as your parsing.

## The Parsing Pipeline
1.  **Collection**: Raw log received.
2.  **Extraction**: Parse fields using regex, JSON, key-value.
3.  **Normalization**: Map to common schema (e.g., ECS, OCSF).
4.  **Enrichment**: Add context (GeoIP, asset info, threat intel).

## Common Data Models
*   **Elastic Common Schema (ECS)**: Standardized field names.
*   **OCSF (Open Cybersecurity Schema Framework)**: New AWS-backed standard.
*   **Splunk CIM**: Common Information Model.

## Parsing Example: Syslog to Structured
\`\`\`
Raw: <34>Oct 11 22:14:15 host sshd[1234]: Failed password for admin from 192.168.1.1

Parsed:
{
  "facility": "auth",
  "severity": "critical",
  "timestamp": "2024-10-11T22:14:15Z",
  "host": "host",
  "process": "sshd",
  "pid": 1234,
  "message": "Failed password for admin from 192.168.1.1",
  "user": "admin",
  "src_ip": "192.168.1.1",
  "event_type": "authentication_failure"
}
\`\`\`

## Field Naming Conventions
*   Use consistent case: \`src_ip\` not \`SrcIP\` or \`source_ip\`.
*   Avoid abbreviations users won't know.`,
        questions: [
            ['What does ECS stand for?', 'Elastic Common Schema', 'Schema', ['Event Collection System', 'Elastic Common Schema', 'Enterprise Control System', 'Endpoint Correlation Service']],
            ['Why normalize logs to a common schema?', 'Enable consistent queries across different log sources', 'Consistency', ['Make logs smaller', 'Enable consistent queries across different log sources', 'Hide sensitive data', 'Comply with regulations']],
            ['What enrichment might you add during parsing?', 'GeoIP, asset info, threat intelligence', 'Context', ['Compression', 'GeoIP, asset info, threat intelligence', 'Encryption', 'Deletion markers']]
        ]
    },
    363: {
        content: `# Advanced Correlation Rules

Simple rules catch simple attacks. Advanced rules catch advanced attackers.

## Correlation vs Single-Event Detection
*   **Single-Event**: \`event.action == "login_failure"\` → One event, one alert.
*   **Correlation**: 10 login failures from same IP in 5 minutes → Pattern detection.

## Multi-Stage Correlation
Detect attack chains:
\`\`\`
Stage 1: Phishing email delivered (email gateway)
Stage 2: Macro executed (EDR)
Stage 3: PowerShell download (Sysmon)
Stage 4: C2 beacon (Firewall)
→ Alert: "Phishing → Initial Access → C2 Chain Detected"
\`\`\`

## Temporal Logic
*   **Sequence Matters**: A then B within 30 minutes.
*   **Absence Detection**: Expected event did NOT occur (e.g., backup job didn't run).

## Threshold Tuning
\`\`\`
IF failed_logins > 5 in 1 minute from same src_ip
  AND NOT src_ip IN whitelist
  AND target_user != "service_account"
THEN ALERT
\`\`\`
*   Start strict, loosen based on FP analysis.

## Splunk Example
\`\`\`spl
index=auth action=failure 
| stats count by src_ip, user 
| where count > 5
| lookup whitelist_ips ip AS src_ip OUTPUT is_whitelisted
| where is_whitelisted != "true"
\`\`\``,
        questions: [
            ['What is "Multi-Stage Correlation"?', 'Detecting attack chains across multiple events/logs', 'Chain', ['Faster queries', 'Detecting attack chains across multiple events/logs', 'Storing more data', 'Compressing logs']],
            ['Why add whitelists to correlation rules?', 'Reduce false positives from known-good sources', 'Tune', ['Make rules slower', 'Reduce false positives from known-good sources', 'Delete logs', 'Add encryption']],
            ['What is "Absence Detection"?', 'Alerting when an expected event does NOT occur', 'Missing', ['Detecting attackers', 'Alerting when an expected event does NOT occur', 'Finding duplicates', 'Searching faster']]
        ]
    },
    364: {
        content: `# Query Optimization

Slow queries frustrate analysts and waste resources.

## Optimization Principles
1.  **Filter Early**: Reduce data volume as early as possible.
2.  **Avoid Wildcards at Start**: \`*admin\` is slow; \`admin*\` is fast.
3.  **Use Indexed Fields**: Query on indexed vs calculated fields.
4.  **Limit Time Range**: 24 hours, not 90 days.
5.  **Avoid Full-Text Search When Possible**: Use structured field queries.

## Splunk SPL Tips
| Bad | Good |
|-----|------|
| \`index=* error\` | \`index=app sourcetype=app_log error\` |
| \`| search src_ip=*\` | Move filters before pipes |
| \`| eval x=lower(y) | where x="admin"\` | \`| where lower(y)="admin"\` |

## Elastic KQL/Lucene Tips
*   Use term queries for exact matches: \`user.name: "admin"\`.
*   Avoid regex unless necessary.
*   Leverage runtime fields sparingly.

## Measuring Query Performance
*   **Splunk**: \`| rest /services/search/jobs/<sid>\` for job inspector.
*   **Elastic**: Enable slow log, use Profile API.`,
        questions: [
            ['Why filter early in a query?', 'Reduce data volume before expensive operations', 'Performance', ['Make results pretty', 'Reduce data volume before expensive operations', 'Add more logs', 'Comply with laws']],
            ['Which wildcard placement is more efficient?', 'Suffix: admin* (not *admin)', 'Suffix', ['Both equal', 'Suffix: admin* (not *admin)', 'Prefix: *admin', 'Neither']],
            ['What does "indexed field" mean?', 'A field the SIEM can search quickly without scanning all data', 'Fast lookup', ['A calculated field', 'A field the SIEM can search quickly without scanning all data', 'A deleted field', 'A hidden field']]
        ]
    },
    365: {
        content: `# Building Detection-as-Code

Treat detections like software: version control, testing, CI/CD.

## Why Detection-as-Code?
*   **Version Control**: Track changes, rollback mistakes.
*   **Peer Review**: PRs for new detections.
*   **Testing**: Unit tests for detection logic.
*   **Automation**: Deploy to SIEM via CI/CD pipeline.

## Sigma Rules
A vendor-agnostic detection format that compiles to SIEM-specific queries.
\`\`\`yaml
title: Suspicious PowerShell Encoded Command
status: production
logsource:
  product: windows
  service: sysmon
detection:
  selection:
    EventID: 1
    CommandLine|contains: '-enc'
  condition: selection
level: high
\`\`\`

## Workflow
1.  Write Sigma rule (YAML).
2.  Compile: \`sigmac -t splunk rule.yml\` → SPL query.
3.  Test against known-good and known-bad logs.
4.  Deploy via API or git sync.

## Testing Detections
*   **Atomic Red Team**: Execute real attack techniques safely.
*   **Replay Logs**: Feed known-malicious logs into test SIEM.`,
        questions: [
            ['What is Sigma?', 'Vendor-agnostic detection rule format', 'Universal', ['A SIEM product', 'Vendor-agnostic detection rule format', 'A programming language', 'A log format']],
            ['Why use version control for detections?', 'Track changes and enable rollback', 'Git', ['Make detections slower', 'Track changes and enable rollback', 'Hide detections', 'Compress rules']],
            ['What tool executes real attack techniques for testing?', 'Atomic Red Team', 'Test', ['Sigma', 'Atomic Red Team', 'Splunk', 'Wireshark']]
        ]
    },
    366: {
        content: `# Final Quiz

Test your Advanced SIEM knowledge.`,
        questions: [
            ['In a tiered storage model, where are recent logs stored?', 'Hot tier (SSD)', 'Tier', ['Cold archive', 'Hot tier (SSD)', 'Tape', 'Cloud only']],
            ['What does OCSF stand for?', 'Open Cybersecurity Schema Framework', 'Schema', ['Open Cyber Security Framework', 'Open Cybersecurity Schema Framework', 'Online Cloud Security Format', 'None']],
            ['Multi-Stage Correlation detects:', 'Attack chains across multiple events', 'Chain', ['Single events', 'Attack chains across multiple events', 'Faster searches', 'Log compression']],
            ['What is the benefit of filtering early in queries?', 'Reduce data volume for faster execution', 'Speed', ['More results', 'Reduce data volume for faster execution', 'Better colors', 'More logs']],
            ['Sigma rules compile to:', 'SIEM-specific queries (SPL, KQL, etc.)', 'Output', ['PDFs', 'SIEM-specific queries (SPL, KQL, etc.)', 'Executables', 'Emails']],
            ['Detection-as-Code enables:', 'Version control and CI/CD for detections', 'DevOps', ['Faster attacks', 'Version control and CI/CD for detections', 'More alerts', 'Less security']]
        ]
    }
};

async function createModule() {
    try {
        console.log('🚀 Creating Module 36: Advanced SIEM...\n');
        console.log(`Processing Module ${overview.id}: ${overview.title}`);
        await db.query(`INSERT INTO modules (id, title, description, display_order, learning_path_id) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE title = VALUES(title), description = VALUES(description), learning_path_id = VALUES(learning_path_id)`, 
            [overview.id, overview.title, overview.description, overview.id, overview.learning_path_id]);
        for (const t of tasks) {
            await db.query(`INSERT INTO tasks (id, module_id, title, is_active, display_order) VALUES (?, ?, ?, 1, ?) ON DUPLICATE KEY UPDATE title = VALUES(title), module_id = VALUES(module_id), is_active=1`,
                [t.id, overview.id, t.title, t.id]);
        }
        for (const [idStr, lesson] of Object.entries(lessons)) {
            const taskId = parseInt(idStr);
            await db.query('DELETE FROM lesson_content WHERE task_id = ?', [taskId]);
            await db.query('DELETE FROM lesson_questions WHERE task_id = ?', [taskId]);
            await db.query('INSERT INTO lesson_content (task_id, content, content_type, reading_time_minutes) VALUES (?, ?, ?, ?)',
                [taskId, lesson.content, 'markdown', 20]);
            for (let j = 0; j < lesson.questions.length; j++) {
                const q = lesson.questions[j];
                const options = q[3] ? JSON.stringify(q[3]) : null;
                await db.query('INSERT INTO lesson_questions (task_id, question_text, correct_answer, question_order, hint, options, case_sensitive) VALUES (?, ?, ?, ?, ?, ?, ?)',
                    [taskId, q[0], q[1], j + 1, q[2], options, 0]);
            }
        }
        console.log(`✅ Module 36 Created!\n`);
        process.exit(0);
    } catch (error) {
        console.error('Error:', error);
        process.exit(1);
    }
}
createModule();
