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

const overview = {
    id: 19,
    title: 'Phishing & Email Analysis',
    description: 'Learn to dissect email headers and investigate phishing attacks.'
};

const tasks = [
    { id: 191, title: 'Email Anatomy: RFC 5322' },
    { id: 192, title: 'Authentication: SPF, DKIM, DMARC' },
    { id: 193, title: 'Investigation 1: The CEO Fraud' },
    { id: 194, title: 'Investigation 2: Malicious Attachment' },
    { id: 195, title: 'Investigation 3: The Credential Harvest' },
    { id: 196, title: 'Module 19 Final Quiz' }
];

const lessons = {
    191: {
        content: `# Email Anatomy: RFC 5322

To analyze phishing, you must read the **Headers**. Most modern email clients hide them by default.

## The Envelope vs The Letter
*   **SMTP Envelope**: The actual routing instructions used by servers (MAIL FROM, RCPT TO). The user NEVER sees this.
*   **Message Headers**: What the user sees (From, To, Subject).
*   **Spoofing**: Attackers can put *anything* in the Headers. They can write "From: CEO" in the header, while the Envelope says "From: hacker@evil.com".

## Key Header Fields
1.  **Return-Path**: This usually matches the Envelope Sender. It is where bounce messages go. **Comparing 'From' vs 'Return-Path' is the #1 way to spot simple spoofing.**
2.  **Received**: Taking from bottom-to-top, these trace the server hops.
    *   \`Received: from mail.evil.com ([1.2.3.4]) by mail.company.com\`
3.  **X-Mailer**: Identifies the software used to send it.
    *   *Legit*: Outlook, Gmail, iPhone.
    *   *Suspicious*: PHP/5.6, Kali Linux, Python scripts.
4.  **Message-ID**: A unique string generated by the sending server.
    *   *Legit*: \`<sadf876s87f6@google.com>\`
    *   *Suspicious*: \`<12345@localhost.localdomain>\`

## Finding Headers
*   **Outlook**: File -> Properties -> Internet Headers.
*   **Gmail**: Three dots -> Show Original.`,
        questions: [
            ['Which header field usually matches the actual Envelope Sender?', 'Return-Path', 'Return', ['Reply-To', 'Return-Path', 'From', 'Subject']],
            ['If "From" is "ceo@company.com" but "Return-Path" is "hacker@gmail.com", what is this called?', 'Spoofing', 'Mismatch', ['Phishing', 'Spoofing', 'Spam', 'Relaying']],
            ['Are "Message Headers" authenticated by default?', 'No, they can be easily faked', 'No', ['Yes, strictly', 'No, they can be easily faked', 'Only on Gmail', 'Only on Outlook']]
        ]
    },
    192: {
        content: `# Authentication: SPF, DKIM, DMARC

The internet's defense against spoofing.

## SPF (Sender Policy Framework)
"Who is allowed to send email for \`google.com\`?"
*   It is a DNS TXT record.
*   *Record*: \`v=spf1 include:_spf.google.com -all\`
*   *Check*: The receiving server looks at the Sender IP (1.2.3.4). Is 1.2.3.4 in the list?
    *   **Pass**: Yes.
    *   **Fail**: No.
    *   **SoftFail**: Maybe (marked as Spam).

## DKIM (DomainKeys Identified Mail)
"Did this email content change in transit?"
*   It is a **Digital Signature** attached to the email headers.
*   The sender signs the header with a Private Key.
*   The receiver verifies it with the Public Key in DNS.
*   If a hacker modifies the "Subject" or "Body", the signature breaks (DKIM Fail).

## DMARC (Domain-based Message Authentication)
"What should I do if SPF or DKIM fail?"
*   It is a policy set by the domain owner.
*   \`p=none\`: Just tell me (Monitoring).
*   \`p=quarantine\`: Put it in Junk.
*   \`p=reject\`: DELETE IT. (The gold standard).`,
        questions: [
            ['Which protocol uses a DNS list of allowed Sender IPs?', 'SPF', 'Sender Policy', ['SPF', 'DKIM', 'DMARC', 'POP3']],
            ['What does DKIM prevent?', 'Tampering / Modification', 'Integrity', ['Spoofing IP', 'Tampering / Modification', 'Spam', 'Viruses']],
            ['If DMARC policy is "p=reject", what happens to spoofed emails?', 'They are blocked/deleted', 'Reject', ['Delivered to Inbox', 'Sent to Spam', 'They are blocked/deleted', 'Returned to sender']]
        ]
    },
    193: {
        content: `# Investigation 1: The CEO Fraud

**Scenario**: The CFO receives an urgent email from the CEO asking for a wire transfer.

## Step 1: Analyze the Headers
\`\`\`
Return-Path: <ceo-private-email@gmail.com>
Received: from mail-sor-f41.google.com (209.85.220.41)
From: "Elon Musk" <elon.musk@tesla-corp.io>
Subject: URGENT WIRE TRANSFER
X-Mailer: iPhone Mail (16E227)
\`\`\`

## Step 2: Spot the Discrepancies
1.  **Display Name Spoofing**: The name says "Elon Musk", but look at the email address: \`tesla-corp.io\`.
    *   Is that the real domain? No, the real domain is \`tesla.com\`. This is **Typosquatting**.
2.  **Return-Path Mismatch**: The From address claims to be corporate (\`tesla-corp.io\`), but the Return-Path is a personal Gmail (\`ceo-private-email@gmail.com\`).
3.  **Urgency**: The Subject line "URGENT" is a classic social engineering psychological trigger.

## Step 3: Forensic Conclusion
This is a **Business Email Compromise (BEC)** attempt using a typosquatted domain. The attacker is likely using a free Gmail account to collect the reply.`,
        questions: [
            ['What is "Typosquatting"?', 'Registering a domain that looks like the real one', 'Misspelling', ['Hacking DNS', 'Registering a domain that looks like the real one', 'Stealing passwords', 'Deleting data']],
            ['The "From" header says "tesla-corp.io". Why is this suspicious?', 'It is not the official trusted domain', 'Fake domain', ['It is too long', 'It is not the official trusted domain', 'It uses hyphens', 'It is fine']],
            ['What psychological trigger is used in "URGENT WIRE TRANSFER"?', 'Urgency / Panic', 'Fear', ['Greed', 'Urgency / Panic', 'Curiosity', 'Authority']]
        ]
    },
    194: {
        content: `# Investigation 2: Malicious Attachment

**Scenario**: Employee receives "Invoice_9921.pdf.exe".

## Step 1: Attachment Analysis
*   **Double Extension**: \`Invoice.pdf.exe\`. Windows hides known extensions by default, so the user sees "Invoice.pdf".
*   **The Icon**: The malware author manually changed the file icon to look like a PDF document.

## Step 2: Hashing
Always hash attachments before analyzing them.
*   **MD5**: \`8743b52063cd84097a54d1ad2f5e98d5\`
*   **Action**: Paste this into **VirusTotal**.

## Step 3: Sandbox Analysis
If VirusTotal has no hits (Zero-Day?), execute it in a Sandbox (Any.Run, Hybrid-Analysis).
*   *Observation*: The '.exe' spawned \`powershell.exe\`, which connected to \`103.22.1.5\` (Russia) and downloaded a payload.

## Step 4: Header Analysis
\`\`\`
Received: from unknown (HELO marketing-mailer) ([185.100.1.55])
X-PHP-Originating-Script: 1001:mailer.php
Subject: Your Unpaid Invoice
\`\`\`
*   **X-PHP-Originating-Script**: This email wasn't sent by Outlook; it was sent by a PHP script on a compromised web server.`,
        questions: [
            ['Why is "file.pdf.exe" dangerous?', 'Windows hides the .exe extension', 'Double extension', ['It is a PDF', 'Windows hides the .exe extension', 'It is corrupt', 'It is encrypted']],
            ['What is the first safe step when you have a suspicious file?', 'Hash it and check VirusTotal', 'Hashing', ['Open it', 'Hash it and check VirusTotal', 'Email it to IT', 'Rename it']],
            ['What does "X-PHP-Originating-Script" usually indicate?', 'Sent via a web script/bot', 'Automated', ['Sent by Outlook', 'Sent via a web script/bot', 'Sent by Gmail', 'Legitimate marketing']]
        ]
    },
    195: {
        content: `# Investigation 3: The Credential Harvest

**Scenario**: "Your Office 365 password creates today. Click here to keep it."

## Step 1: URL Analysis
The button says "Login to Microsoft".
*   **Hover**: You hover your mouse over the button.
*   **Link**: \`http://login-microsoft-secure-update.com.xyz/auth.php\`
*   *Analysis*:
    1.  **Protocol**: HTTP (Not HTTPS). Microsoft would never use HTTP for login.
    2.  **Top Level Domain**: \`.xyz\` (Cheap, often used by spammers).
    3.  **Root Domain**: \`com.xyz\` isn't Microsoft.

## Step 2: Header Checks
\`\`\`
Authentication-Results: spf=fail (sender IP is 45.1.1.1); dkim=fail
From: "Microsoft Security" <security@microsoft.com>
\`\`\`
*   **SPF Fail**: The IP 45.1.1.1 is NOT authorized to send mail for \`microsoft.com\`.
*   **Conclusion**: Direct spoofing. The attacker didn't even try to hide it, hoping the user ignores the "Spam" warning.

## Step 3: Defanging
When reporting this URL, **Defang** it so nobody accidentally clicks it.
*   *Live*: \`http://evil.com\`
*   *Defanged*: \`hxxp://evil[.]com\``,
        questions: [
            ['What does "Defanging" a URL mean?', 'Making it unclickable', 'Safety', ['Deleting it', 'Making it unclickable', 'Encrypting it', 'Clicking it']],
            ['If SPF fails for "microsoft.com", what does it mean?', 'The sender IP is not Microsoft', 'Spoofing', ['Microsoft is down', 'The sender IP is not Microsoft', 'The email is legitimate', 'DNS is broken']],
            ['What is suspicious about "login.microsoft.com.xyz"?', 'The TLD is .xyz, not .com', 'Fake domain', ['It is too short', 'The TLD is .xyz, not .com', 'It contains login', 'It is secure']]
        ]
    },
    196: {
        content: `# Module 19 Final Quiz

Analyze the following Email Header and answer the questions.

\`\`\`
Delivered-To: victim@company.com
Received: from mail.attacker-infrastructure.net (mail.attacker-infrastructure.net. [192.0.2.55])
        by mx.company.com with ESMTPS id 12345
        for <victim@company.com>;
        Tue, 26 Dec 2023 10:00:00 -0800
Authentication-Results: mx.company.com;
        spf=pass (google.com: domain of support-ticket-882@gmail.com designates 192.0.2.55 as permitted sender)
Return-Path: <support-ticket-882@gmail.com>
From: "IT Service Desk" <admin@company.com>
To: "Employee" <victim@company.com>
Subject: URGENT: Password Expiry Notification
Message-ID: <001501c56b9a$2d3e4f00$local@random-pc>
X-Mailer: OstroMail v1.2 (Mass Mailer)
Content-Type: text/html;
\`\`\`

## Instructions
*   Review the header block above carefully.
*   Answer the 5 questions below based *only* on the evidence in the header.`,
        questions: [
            ['What is the TRUE sender address (Return-Path)?', 'support-ticket-882@gmail.com', 'Return Path', ['admin@company.com', 'support-ticket-882@gmail.com', 'victim@company.com', 'mail.attacker-infrastructure.net']],
            ['Who does the email CLAIM to be from (Display Name)?', 'IT Service Desk', 'Fake Name', ['Support Ticket', 'IT Service Desk', 'Gmail Team', 'HR']],
            ['Look at the X-Mailer. What software sent this?', 'OstroMail v1.2 (Mass Mailer)', 'Mass Mailer', ['Outlook', 'Gmail', 'OstroMail v1.2 (Mass Mailer)', 'iPhone Mail']],
            ['Did SPF pass or fail?', 'Pass', 'Pass', ['Fail', 'SoftFail', 'Pass', 'Neutral']],
            ['Why is this suspicious, despite SPF passing?', 'Sender authenticated as Gmail, but spoofed Company header', 'Mismatch', ['SPF failed', 'Sender authenticated as Gmail, but spoofed Company header', 'It came from Microsoft', 'The date is wrong']]
        ]
    }
};

async function createModule19() {
    try {
        console.log('🚀 Creating Module 19: Email Analysis...\n');

        // Create Module (Path 4: Advanced SOC)
        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, 19, 2]);

        // 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 19 (Email Analysis) Created! (${totalLessons} lessons with Final Header Quiz)\n`);
        process.exit(0);

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

createModule19();
