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

const overview = {
    id: 23,
    title: 'SIEM Use Cases',
    description: 'Practical log analysis scenarios ranging from Easy to Data Exfiltration.',
    learning_path_id: 3 // SOC Analyst Level 1
};

const tasks = [
    { id: 231, title: 'Use Case 1: Brute Force Attack (Easy)' },
    { id: 232, title: 'Use Case 2: Malware Beaconing (Easy)' },
    { id: 233, title: 'Use Case 3: Impossible Travel (Medium)' },
    { id: 234, title: 'Use Case 4: Privilege Escalation (Medium)' },
    { id: 235, title: 'Use Case 5: DNS Tunneling (Hard)' },
    { id: 236, title: 'Final Quiz' }
];

const lessons = {
    231: {
        content: `# Use Case 1: Brute Force Attack (Easy)

**Scenario**: You see a spike in authentication failures on the SSH server.

## Data Source: /var/log/auth.log
\`\`\`text
Oct 10 12:00:01 server sshd[123]: Failed password for root from 192.168.1.55 port 22 ssh2
Oct 10 12:00:02 server sshd[124]: Failed password for root from 192.168.1.55 port 22 ssh2
Oct 10 12:00:03 server sshd[125]: Failed password for root from 192.168.1.55 port 22 ssh2
Oct 10 12:00:04 server sshd[126]: Accepted password for root from 192.168.1.55 port 22 ssh2
\`\`\`

## Analysis
1.  **Event**: "Failed password".
2.  **Frequency**: 3 failures in 3 seconds.
3.  **Outcome**: The 4th attempt was "Accepted". This is the critical part. It means the Brute Force was **Successful**.

## SIEM Rule Logic
*   **IF** \`event="Failed Login"\`
*   **AND** \`count > 5\` within \`1 minute\`
*   **AND** \`same source_ip\`
*   **THEN** \`Trigger Alert: Potential SSH Brute Force\``,
        questions: [
            ['What is the Attacker\'s IP address?', '192.168.1.55', 'IP', ['127.0.0.1', '192.168.1.55', '10.0.0.1', '8.8.8.8']],
            ['Did the attacker succeed?', 'Yes, the last log says "Accepted"', 'Success', ['No, all failed', 'Yes, the last log says "Accepted"', 'Maybe', 'Unknown']],
            ['Which account was targeted?', 'root', 'Admin', ['admin', 'user', 'root', 'guest']]
        ]
    },
    232: {
        content: `# Use Case 2: Malware Beaconing (Easy)

**Scenario**: A computer is infected and "calling home" to the Command & Control (C2) server.

## Data Source: Firewall Logs
\`\`\`text
Time: 14:00:00 | Src: 10.10.1.100 | Dst: 45.33.2.1 | Port: 443 | Action: Allow | Bytes: 500
Time: 14:05:00 | Src: 10.10.1.100 | Dst: 45.33.2.1 | Port: 443 | Action: Allow | Bytes: 500
Time: 14:10:00 | Src: 10.10.1.100 | Dst: 45.33.2.1 | Port: 443 | Action: Allow | Bytes: 500
Time: 14:15:00 | Src: 10.10.1.100 | Dst: 45.33.2.1 | Port: 443 | Action: Allow | Bytes: 500
\`\`\`

## Analysis
1.  **Regularity**: Connections occur exactly every **5 minutes**. Humans are random; machines are precise. This is called a **Heartbeat** or **Beacon**.
2.  **Size**: The payload size is identical (500 bytes). This is likely a "keep-alive" signal.
3.  **Destination**: \`45.33.2.1\`. A quick Reputation check would likely show this is a C2 server.

## SIEM Rule Logic
*   **IF** \`connection_count > 10\` to \`same destination_ip\`
*   **AND** \`interval IS consistent\` (standard deviation is low)
*   **THEN** \`Trigger Alert: Potential C2 Beaconing\``,
        questions: [
            ['What is the time interval between connections?', '5 Minutes', 'Regularity', ['1 Minute', '5 Minutes', 'Random', '1 Hour']],
            ['Why is "Regularity" suspicious?', 'Machines are precise, humans are random', 'Automation', ['It isn\'t', 'Machines are precise, humans are random', 'It means the network is fast', 'It is normal']],
            ['What destination IP is the victim talking to?', '45.33.2.1', 'C2', ['10.10.1.100', '127.0.0.1', '45.33.2.1', '192.168.1.1']]
        ]
    },
    233: {
        content: `# Use Case 3: Impossible Travel (Medium)

**Scenario**: A user logs in from two physically distant locations in a short time.

## Data Source: Office 365 / Azure AD
\`\`\`json
{
  "Event": "UserLogin",
  "User": "alice@company.com",
  "Time": "09:00:00 UTC",
  "IP": "203.0.113.5",
  "Country": "USA (New York)"
}
{
  "Event": "UserLogin",
  "User": "alice@company.com",
  "Time": "10:15:00 UTC",
  "IP": "198.51.100.2",
  "Country": "Germany (Berlin)"
}
\`\`\`

## Analysis
1.  **Distance**: New York to Berlin is ~4000 miles. A flight takes 8 hours.
2.  **Time Delta**: The logins occurred **1 hour and 15 minutes** apart.
3.  **Conclusion**: It is physically impossible for Alice to be in both places. Either:
    *   She is using a VPN (False Positive).
    *   Her credentials have been stolen (True Positive).

## SIEM Rule Logic
*   **IF** \`Login A (Location X)\` AND \`Login B (Location Y)\`
*   **AND** \`Time_Delta < Travel_Time(X, Y)\`
*   **THEN** \`Trigger Alert: Impossible Travel\``,
        questions: [
            ['What is the time difference between the two logins?', '1 hour 15 minutes', 'Delta', ['5 minutes', '1 hour 15 minutes', '8 hours', '1 day']],
            ['Which user account is affected?', 'alice@company.com', 'User', ['bob', 'admin', 'alice@company.com', 'root']],
            ['What is the most likely malicious explanation?', 'Credential Theft', 'Stolen Creds', ['VPN', 'Credential Theft', 'Fast Plane', 'Bug']]
        ]
    },
    234: {
        content: `# Use Case 4: Privilege Escalation (Medium)

**Scenario**: A standard user is suddenly granted Administrator rights.

## Data Source: Windows Security Log (Event ID 4728 / 4732)
\`\`\`text
EventID: 4728 (Member Added to Security-Enabled Global Group)
SubjectUserName: "hacker_compromised_account"
TargetUserName: "backup_service"
TargetGroupName: "Domain Admins"
Time: 03:00:00 AM
\`\`\`

## Analysis
1.  **The Change**: \`backup_service\` was added to \`Domain Admins\`. This is the "Keys to the Kingdom".
2.  **The Actor**: The change was made by \`hacker_compromised_account\`.
3.  **The Time**: 3:00 AM. Administrative changes at 3 AM are highly suspicious.

## SIEM Rule Logic
*   **IF** \`EventID=(4728 OR 4732 OR 4756)\`
*   **AND** \`TargetGroup="Domain Admins" OR "Enterprise Admins"\`
*   **THEN** \`Trigger Alert: User Added to Privileged Group\``,
        questions: [
            ['Which group was the user added to?', 'Domain Admins', 'Admin', ['Users', 'Guests', 'Domain Admins', 'Print Operators']],
            ['What Windows Event ID indicates "Member Added to Group"?', '4728', 'ID', ['4624', '4728', '1102', '514']],
            ['Why is the timestamp (03:00 AM) relevant?', 'Off-hours changes are suspicious', 'Night', ['It is daytime', 'Off-hours changes are suspicious', 'Servers restart then', 'It is normal']]
        ]
    },
    235: {
        content: `# Use Case 5: DNS Tunneling (Hard)

**Scenario**: An attacker is stealing data by hiding it inside DNS Queries.

## Data Source: DNS Logs (Zeek/Suricata)
\`\`\`text
Query: N2ViMzU4...verylongstring...2.evil.com (Type: A, Length: 150)
Query: ZjQ5Mz...verylongstring...3.evil.com (Type: A, Length: 150)
Query: YWRtaW4...verylongstring...4.evil.com (Type: A, Length: 150)
\`\`\`

## Analysis
1.  **Subdomain Length**: The subdomains are extremely long and look like Base64 encoded strings (\`N2ViMzU4...\`).
2.  **Volume**: Thousands of queries to the same root domain (\`evil.com\`).
3.  **The Method**: The attacker chunks a sensitive file (like a PDF), encodes it in Base64, and sends it out as "DNS Queries". The DNS Server at \`evil.com\` receives the queries and reassembles the file. Firewalls often allow DNS (Port 53), so this bypasses restrictions.

## SIEM Rule Logic
*   **IF** \`DNS Query Length > 50 chars\`
*   **AND** \`Query Volume > 100 per minute\` to \`same root domain\`
*   **AND** \`High Entropy\` (looks random)
*   **THEN** \`Trigger Alert: Potential DNS Tunneling\``,
        questions: [
            ['What is hidden inside the long subdomains?', 'Exfiltrated Data (Encoded)', 'Stolen integrity', ['Nothing', 'Exfiltrated Data (Encoded)', 'IP addresses', 'Viruses']],
            ['Why do attackers use DNS for exfiltration?', 'Firewalls usually allow Port 53 (DNS) outbound', 'Port 53', ['It is faster', 'Firewalls usually allow Port 53 (DNS) outbound', 'It is encrypted', 'It compresses data']],
            ['What characteristic of the query string indicates tunneling?', 'Length and High Entropy (Randomness)', 'Long', ['Short length', 'Length and High Entropy (Randomness)', 'It is .com', 'It is readable']]
        ]
    },
    236: {
        content: `# Final Quiz

Analyze the following log snippet to answer the questions.

**Log Source: Firewall**
\`\`\`text
Oct 31 23:55:00 firewall-1 BLOCK src=192.168.1.100 dst=8.8.8.8 port=53
Oct 31 23:55:01 firewall-1 BLOCK src=192.168.1.100 dst=8.8.8.8 port=80
Oct 31 23:55:02 firewall-1 BLOCK src=192.168.1.100 dst=8.8.8.8 port=443
Oct 31 23:55:03 firewall-1 BLOCK src=192.168.1.100 dst=8.8.8.8 port=21
Oct 31 23:55:04 firewall-1 BLOCK src=192.168.1.100 dst=8.8.8.8 port=23
\`\`\`

**Log Source: Windows Security**
\`\`\`text
EventID: 4624 (Logon Success)
User: admin
Source Network Address: 10.5.5.5
Time: 02:00 AM
\`\`\`
`,
        questions: [
            // Based on Firewall Log
            ['In the Firewall log, what is the source IP doing?', 'Port Scanning (trying different ports rapidly)', 'SCAN', ['Downloading files', 'Port Scanning (trying different ports rapidly)', 'Updating', 'Browsing web']],
            ['What action did the firewall take?', 'BLOCK', 'Action', ['ALLOW', 'BLOCK', 'PASS', 'LOG']],
            
            // Based on Windows Log
            ['What event ID represents a Successful Login?', '4624', 'ID', ['4625', '4624', '1000', '52']],
            ['What time did the login occur?', '02:00 AM', 'Time', ['23:55', '02:00 AM', 'Noon', 'Midnight']],
            
            // General
            ['In the Brute Force scenario, what indicates success?', 'A "Success" or "Accepted" log after failures', 'Win', ['More failures', 'A "Success" or "Accepted" log after failures', 'Nothing', 'A lockout']],
            ['How do you detect "Beaconing"?', 'Regular time intervals (Heartbeat)', 'Rhythm', ['High bandwidth', 'Regular time intervals (Heartbeat)', 'Random times', 'Different IPs']]
        ]
    }
};

async function createModule23() {
    try {
        console.log('🚀 Creating Module 23: SIEM Use Cases...\n');

        // Create Module (Path 3)
        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, 23, overview.learning_path_id]);

        // Create Tasks
        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), is_active=1`,
                [t.id, overview.id, t.title, t.id]);
        }

        let totalLessons = 0;
        // Populate Lessons
        for (const [idStr, lesson] of Object.entries(lessons)) {
            const taskId = parseInt(idStr);
            // Clean
            await db.query('DELETE FROM lesson_content WHERE task_id = ?', [taskId]);
            await db.query('DELETE FROM lesson_questions WHERE task_id = ?', [taskId]);

            // Insert Content
            await db.query('INSERT INTO lesson_content (task_id, content, content_type, reading_time_minutes) VALUES (?, ?, ?, ?)',
                [taskId, lesson.content, 'markdown', 15]);

            // Insert Questions
            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]);
            }
            totalLessons++;
        }

        console.log(`\n✅ Module 23 (Use Cases) Created! (${totalLessons} lessons)\n`);
        process.exit(0);

    } catch (error) {
        console.error('Error:', error);
        process.exit(1);
    }
}

createModule23();
