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

async function createNetworkingLessons() {
    try {
        console.log('🚀 Creating Detailed Networking Essentials Lessons (Module 4)\n');

        // Define content mapping by ID strictly to ensure correctness
        const lessons = {
            22: {
                title: 'OSI Model Explained',
                content: `# The OSI Model: A Deep Dive

The **Open Systems Interconnection (OSI)** model is the universal language of computer networking. It splits communication into 7 distinct layers, helping professionals troubleshoot and understand networks.

## The 7 Layers (Top to Bottom)

### 7. Application Layer (User Interface)
*   **Role**: Where users interact with the network. Software applications like browsers (Chrome) or Email clients (Outlook) operate here.
*   **Protocols**: HTTP, HTTPS, FTP, SMTP, DNS.
*   **PDU**: Data.

### 6. Presentation Layer (Translator)
*   **Role**: Formats and encrypts data. It ensures data is readable by the receiving system.
*   **Functions**: Encryption (TLS), Compression (ZIP), Translation (ASCII to EBCDIC).
*   **PDU**: Data.

### 5. Session Layer (Coordinator)
*   **Role**: Establishes, maintains, and terminates connections (sessions) between devices.
*   **Functions**: Authentication, Reconnection.
*   **PDU**: Data.

### 4. Transport Layer (Postman)
*   **Role**: Ensures data transfer reliability and flow control. deciding *how* much data to send.
*   **Protocols**: **TCP** (Reliable, Slow) vs **UDP** (Unreliable, Fast).
*   **PDU**: Segments (TCP) or Datagrams (UDP).

### 3. Network Layer (Router)
*   **Role**: Logical addressing and routing. Determines the best path for data.
*   **Protocols**: IP (IPv4, IPv6), ICMP, IPsec.
*   **Devices**: Routers, Layer 3 Switches.
*   **PDU**: Packets.

### 2. Data Link Layer (Switch)
*   **Role**: Physical addressing and error detection on the local link.
*   **Addresses**: **MAC Address** (Physical, burned into hardware).
*   **Devices**: Switches, Network Cards (NICs), Bridges.
*   **PDU**: Frames.

### 1. Physical Layer (Cables)
*   **Role**: Transmission of raw bitstream over a physical medium.
*   **Components**: Ethernet cables (Cat6), Fiber optics, WiFi (Radio waves), Hubs.
*   **PDU**: Bits (1s and 0s).

## Summary Mnemonic
*   **P**lease **D**o **N**ot **T**hrow **S**ausage **P**izza **A**way
*   (Physical, Data Link, Network, Transport, Session, Presentation, Application)`,
                questions: [
                    ['Which layer is responsible for logical addressing (IPs)?', 'Network', 'Layer 3', ['Data Link', 'Network', 'Transport', 'Session']],
                    ['What is the PDU of Layer 4 (Transport)?', 'Segments', 'TCP Data unit', ['Bits', 'Frames', 'Packets', 'Segments']],
                    ['Which layer handles encryption and compression?', 'Presentation', 'Layer 6', ['Application', 'Presentation', 'Session', 'Transport']]
                ]
            },
            23: {
                title: 'TCP/IP Protocol Suite',
                content: `# TCP/IP vs OSI

While OSI is a theoretical model, **TCP/IP** is the practical model used by the Internet. It condenses the 7 OSI layers into 4:

1.  **Network Access** (Physical + Data Link)
2.  **Internet** (Network)
3.  **Transport** (Transport)
4.  **Application** (Session + Presentation + Application)

## TCP vs UDP: The Transport Rivals

### TCP (Transmission Control Protocol)
*   **Type**: Connection-oriented.
*   **Reliability**: **High**. Guarantees delivery. If a packet is lost, it resends it.
*   **Speed**: Slower due to overhead.
*   **Mechanism**: **3-Way Handshake**.
    1.  **SYN**: Client sends "Hello, can we talk?"
    2.  **SYN-ACK**: Server says "Yes, I'm ready."
    3.  **ACK**: Client says "Great, sending data now."
*   **Use Cases**: Web browsing (HTTP), Email (SMTP), File Transfer (FTP).

### UDP (User Datagram Protocol)
*   **Type**: Connectionless ("Fire and Forget").
*   **Reliability**: **Low**. No guarantee. Use it when speed matters more than perfection.
*   **Speed**: Very Fast.
*   **Use Cases**: Streaming Video (YouTube/Netflix), VoIP calls (Zoom), Online Gaming, DNS lookups.

## Key Takeaway
*   **TCP**: "I need to be sure you got this message."
*   **UDP**: "I hope you get this, but I can't wait!"`,
                questions: [
                    ['Which protocol uses a 3-way handshake?', 'TCP', 'Connection-oriented', ['UDP', 'IP', 'TCP', 'ICMP']],
                    ['Why is UDP used for video streaming?', 'Speed', 'Low overhead', ['Reliability', 'Encryption', 'Speed', 'Guaranteed Delivery']],
                    ['Which TCP/IP layer corresponds to OSI Layer 3 (Network)?', 'Internet', 'IP Layer', ['Network Access', 'Internet', 'Transport', 'Application']]
                ]
            },
            24: {
                title: 'IP Addressing and Subnetting',
                content: `# IP Addressing: The Identity on the Network

An **IP Address** is a unique label assigned to each device.

## IPv4 Breakdown
*   **Format**: Four decimal numbers separated by dots (dotted-decimal).
*   **Example**: \`192.168.1.50\`
*   **Size**: 32-bit addresses (approx 4.3 billion total).

## IP Classes (Legacy but important)
| Class | Range | Use |
| :--- | :--- | :--- |
| **Class A** | 1.0.0.0 - 126.x.x.x | Huge networks (ISPs, Gov). Default /8 |
| **Class B** | 128.0.0.0 - 191.x.x.x | Medium/Large Companies. Default /16 |
| **Class C** | 192.0.0.0 - 223.x.x.x | Small LANs/Home. Default /24 |

## Public vs Private IPs
*   **Public IP**: Routable on the internet. Unique worldwide. Provided by your ISP.
*   **Private IP** (RFC 1918): Used internally within a LAN. Not routable online.
    *   **Class A Private**: \`10.0.0.0\` to \`10.255.255.255\`
    *   **Class B Private**: \`172.16.0.0\` to \`172.31.255.255\`
    *   **Class C Private**: \`192.168.0.0\` to \`192.168.255.255\`

## NAT (Network Address Translation)
Since private IPs can't go on the internet, your Router uses **NAT** to translate your private internal IP (192.168.1.5) to its single Public IP when you browse the web.`,
                questions: [
                    ['Which IP address is a private Class C address?', '192.168.1.10', 'Home router default', ['8.8.8.8', '172.16.0.1', '192.168.1.10', '10.0.0.5']],
                    ['How many bits are in an IPv4 address?', '32', 'Binary count', ['128', '64', '32', '16']],
                    ['What allows private IPs to access the internet?', 'NAT', 'Translation', ['DHCP', 'DNS', 'NAT', 'ARP']]
                ]
            },
            25: {
                title: 'Subnetting Practice',
                content: `# Subnetting Demystified

**Subnetting** is the practice of dividing a network into smaller, more efficient sub-networks.

## The Subnet Mask
The mask tells the computer: "Which part of this IP is the **Network ID** and which part is the **Host ID**?"

*   **IP**: \`192.168.1.10\`
*   **Mask**: \`255.255.255.0\`
*   **Result**:
    *   Network: \`192.168.1\`
    *   Host: \`.10\`

## CIDR Notation (Slash Notation)
Instead of writing 255.255.255.0, we count the number of "1" bits in the mask.
*   255.255.255.0 = 24 bits on => **/24**
*   255.255.0.0 = 16 bits on => **/16**
*   255.0.0.0 = 8 bits on => **/8**

## Calculating Hosts
Formula: **2^h - 2** (where h is number of host bits remaining).
*   **/24** network: 8 bits/host. 2^8 = 256. Minus 2 (Network + Broadcast) = **254 usable hosts**.
*   **/30** network: 2 bits/host. 2^2 = 4. Minus 2 = **2 usable hosts** (Point-to-Point links).

## Why Minus 2?
1.  **First Address**: Network Address (e.g., 192.168.1.0) - Identifies the network.
2.  **Last Address**: Broadcast Address (e.g., 192.168.1.255) - Talks to everyone.`,
                questions: [
                    ['What is the CIDR notation for 255.255.255.0?', '/24', 'Count the bits', ['/8', '/16', '/24', '/32']],
                    ['How many usable hosts are in a /24 network?', '254', '256 minus 2', ['256', '255', '254', '250']],
                    ['The first address in a subnet is used for what?', 'Network ID', 'Identification', ['Broadcast', 'Gateway', 'Network ID', 'DNS']]
                ]
            },
            26: {
                title: 'DNS Fundamentals',
                content: `# DNS: The Internet's Phonebook

**DNS (Domain Name System)** translates human-readable names (like \`google.com\`) into computer-readable IP addresses (\`142.250.190.46\`).

## The Resolution Journey
1.  **You**: Type "example.com".
2.  **Resolving Server**: Your ISP or local DNS checks its cache. If missing...
3.  **Root Server (.)**: Directs to the correct Top-Level Domain (TLD) server.
4.  **TLD Server (.com)**: Directs to the Authoritative Name Server for "example".
5.  **Authoritative Server**: "I know! The IP is 93.184.216.34."
6.  **Result**: Your browser loads the page.

## Key DNS Records
*   **A Record**: Maps Hostname -> **IPv4 Address**.
*   **AAAA Record**: Maps Hostname -> **IPv6 Address**.
*   **CNAME (Canonical Name)**: Alias (e.g., \`www.site.com\` -> \`site.com\`).
*   **MX (Mail Exchange)**: Tells email where to go.
*   **TXT**: Text notes, used for verification (SPF, DKIM).

## Security Note
Attackers use **DNS Spoofing/Poisoning** to make a legitimate URL point to a fake phishing site IP.`,
                questions: [
                    ['Which record maps a name to an IPv4 address?', 'A', 'Address', ['AAAA', 'CNAME', 'A', 'MX']],
                    ['Which record is used for email server delivery?', 'MX', 'Mail Exchange', ['TXT', 'PTR', 'MX', 'NS']],
                    ['What translates domain names to IPs?', 'DNS', 'System name', ['DHCP', 'ARP', 'DNS', 'NAT']]
                ]
            },
            27: {
                title: 'DHCP and ARP',
                content: `# DHCP & ARP: Network Automation

## DHCP (Dynamic Host Configuration Protocol)
Imagine setting IP addresses manually for 1000 phones in an office. Nightmare. DHCP does this automatically.

### The DORA Process
How a device gets an IP:
1.  **D**iscover: Client yells "Is there a DHCP server here??"
2.  **O**ffer: Server says "Yes! Here is an IP: 192.168.1.50."
3.  **R**equest: Client says "Great, I'll take that IP."
4.  **A**cknowledge: Server says "It's yours. Lease expires in 24 hours."

## ARP (Address Resolution Protocol)
IP addresses are for routing across networks. Inside a LAN, devices speak **MAC Addresses**.
**ARP** maps **IPs** to **MACs**.

*   **Process**:
    *   Computer A wants to talk to 192.168.1.5.
    *   It shouts (Broadcast): "Who has 192.168.1.5? Tell 192.168.1.2!"
    *   Computer B (192.168.1.5) replies: "That's me! My MAC is AA:BB:CC:DD:EE:FF."
    *   Computer A saves this in its **ARP Table**.

## Security Warning
**ARP Spoofing**: An attacker lies and says "I am the Router!" All traffic then flows through the attacker (Man-in-the-Middle).`,
                questions: [
                    ['What is the first step in the DHCP process?', 'Discover', 'Looking for server', ['Offer', 'Request', 'Discover', 'Acknowledge']],
                    ['What does ARP resolution map?', 'IP to MAC', 'Logical to Physical', ['MAC to IP', 'IP to MAC', 'URL to IP', 'Port to Protocol']],
                    ['What attack relies on faking ARP replies?', 'ARP Spoofing', 'MITM', ['DNS Poisoning', 'ARP Spoofing', 'DDoS', 'Phishing']]
                ]
            },
            28: {
                title: 'Common Network Protocols',
                content: `# Essential Network Protocols & Ports

A SOC Analyst must memorize common ports to spot anomalies.

## Web
*   **HTTP (80)**: Unsecured web traffic.
*   **HTTPS (443)**: Secured (TLS) web traffic.

## File Transfer
*   **FTP (20/21)**: File Transfer Protocol. Unsecure.
*   **SFTP/SCP (22)**: Secure File Transfer (runs over SSH).
*   **SMB (445)**: Server Message Block. Windows file sharing. *Target for EternalBlue exploit.*

## Administration
*   **SSH (22)**: Secure Shell. Encrypted remote command line.
*   **Telnet (23)**: Remote command line. **Unencrypted!** (Never use this).
*   **RDP (3389)**: Remote Desktop Protocol. GUI remote access.

## Infrastructure
*   **DNS (53)**: UDP (mostly). Name resolution.
*   **DHCP (67/68)**: UPD. IP assignment.
*   **NTP (123)**: Time synchronization.

## Email
*   **SMTP (25)**: Sending email.
*   **POP3 (110)**: Receiving email (downloads locally).
*   **IMAP (143)**: Receiving email (syncs with server).`,
                questions: [
                    ['Which port is used for Secure Shell (SSH)?', '22', 'Encrypted CLI', ['21', '22', '23', '25']],
                    ['Which protocol is UNSAFE for remote administration?', 'Telnet', 'Cleartext', ['SSH', 'RDP', 'HTTPS', 'Telnet']],
                    ['Port 445 is associated with which service?', 'SMB', 'Windows Sharing', ['FTP', 'SMB', 'DNS', 'HTTP']]
                ]
            },
            29: {
                title: 'Wireshark Introduction',
                content: `# Introduction to Wireshark

**Wireshark** is the world's most popular network protocol analyzer. It captures packets in real-time and displays them in valid, readable format.

## The GUI Layout
1.  **Packet List**: Top pane. Shows a summary of all captured packets (No., Time, Source, Destination, Protocol, Info).
2.  **Packet Details**: Middle pane. Hierarchical view of the selected packet. You can drill down into layers (Frame, Ethernet, IP, TCP, HTTP).
3.  **Packet Bytes**: Bottom pane. Valid hex dump of the packet data.

## Filtering
Wireshark captures *everything*. Display filters are crucial.
*   \`ip.addr == 192.168.1.5\` (Show traffic for this IP)
*   \`http\` (Show only HTTP traffic)
*   \`tcp.port == 80\` (Show restricted port traffic)
*   \`dns\` (Show DNS queries)

## Common Analysis
*   **Follow TCP Stream**: Reassembles the conversation between client and server to see readable data (like HTML or credentials).
*   **Color Rules**: Wireshark colors packets (e.g., Red on Black usually means connection errors or resets).`,
                questions: [
                    ['What is Wireshark used for?', 'Packet Analysis', 'Capturing traffic', ['Password Cracking', 'Packet Analysis', 'Virus Scanning', 'DDOS Attacks']],
                    ['Which filter shows only web traffic?', 'http', 'Protocol name', ['tcp', 'udp', 'http', 'ip']],
                    ['Where do you see the hex dump of data?', 'Packet Bytes', 'Bottom pane', ['Packet List', 'Packet Details', 'Packet Bytes', 'Toolbar']]
                ]
            },
            30: {
                title: 'Network Traffic Analysis Challenge',
                content: `# Scenario: The Suspicious Login

**Background**: You are a SOC Analyst. You received an alert about "Cleartext Credentials" on the network. You open the PCAP file in Wireshark.

## Investigation Data
*   **Packet 12**: Protocol: HTTP. Source: 192.168.1.105. Dest: 104.21.55.2.
*   **Info**: POST /login.php HTTP/1.1
*   **Packet 12 Details**:
    *   Form item: "username" = "admin"
    *   Form item: "password" = "SuperSecret123"

## Analysis
1.  The user logged into a website using **HTTP**, not HTTPS.
2.  Because it was HTTP, the data was sent in **Plaintext**.
3.  Anyone on the same WiFi could have captured this packet and stolen the credentials.

## Mitigation
*   Always enforce **HTTPS** (TLS/SSL).
*   Use certificate pinning.
*   Educate users to look for the "Padlock" icon.`,
                questions: [
                    ['What protocol exposed the password?', 'HTTP', 'Unsecure', ['HTTPS', 'SSH', 'SFTP', 'HTTP']],
                    ['What was the stolen password in the scenario?', 'SuperSecret123', 'Plaintext', ['admin', 'password', '123456', 'SuperSecret123']],
                    ['How should this login be secured?', 'Use HTTPS', 'Encryption', ['Use Telnet', 'Use HTTPS', 'Change Port', 'Use UDP']]
                ]
            },
            31: {
                title: 'Quiz: Networking Essentials',
                content: `# Final Module Quiz

Review the key concepts from this module:
*   OSI Layers & TCP/IP
*   IP Addressing & Subnets
*   Common Protocols (SSH, HTTP, DNS)
*   Packet Analysis Basics

You are now ready to tackle real-world networking scenarios.`,
                questions: [
                    ['Which device connects different networks (Layer 3)?', 'Router', 'Routing decisions', ['Switch', 'Hub', 'Router', 'Bridge']],
                    ['What is the loopback address for localhost?', '127.0.0.1', 'Home', ['192.168.1.1', '0.0.0.0', '127.0.0.1', '10.0.0.1']],
                    ['Which command typically checks connectivity?', 'ping', 'ICMP Echo', ['ipconfig', 'ping', 'netstat', 'nslookup']],
                    ['What is the subnet mask for a /24 network?', '255.255.255.0', 'Class C default', ['255.0.0.0', '255.255.0.0', '255.255.255.0', '255.255.255.255']],
                    ['Which port is used by HTTP?', '80', 'Unencrypted Web', ['21', '80', '443', '25']],
                    ['Which port is used by HTTPS?', '443', 'Encrypted Web', ['80', '8080', '443', '8443']],
                    ['Which port is used by DNS?', '53', 'Name Resolution', ['23', '53', '67', '110']],
                    ['What is Layer 1 of the OSI Model?', 'Physical', 'Cables and bits', ['Application', 'Data Link', 'Physical', 'Transport']],
                    ['How long is a standard MAC address?', '48 bits', 'Hardware address', ['32 bits', '48 bits', '64 bits', '128 bits']],
                    ['How long is an IPv6 address?', '128 bits', 'Modern IP', ['32 bits', '64 bits', '128 bits', '256 bits']],
                    ['Which protocol guarantees delivery of data?', 'TCP', 'Reliable', ['UDP', 'IP', 'TCP', 'ICMP']],
                    ['What is the first step in the TCP Handshake?', 'SYN', 'Hello', ['ACK', 'SYN', 'SYN-ACK', 'FIN']],
                    ['What is the first step in DHCP?', 'Discover', 'Looking for IP', ['Offer', 'Request', 'Acknowledge', 'Discover']],
                    ['Which of the following is a private IP address?', '192.168.1.50', 'RFC 1918', ['8.8.8.8', '1.1.1.1', '172.50.1.1', '192.168.1.50']],
                    ['What does ARP do?', 'Resolves IP to MAC', 'Address Resolution', ['Resolves MAC to IP', 'Resolves Name to IP', 'Resolves IP to MAC', 'Routes Packets']],
                    ['Which protocol is used for ping?', 'ICMP', 'Internet Control Message', ['TCP', 'UDP', 'ICMP', 'ARP']],
                    ['Which Layer 2 device uses MAC addresses to forward frames?', 'Switch', 'Intelligent forwarding', ['Hub', 'Router', 'Switch', 'Repeater']],
                    ['Which DNS record type stores IPv6 addresses?', 'AAAA', 'Quad A', ['A', 'MX', 'AAAA', 'CNAME']],
                    ['What port does standard FTP use?', '21', 'File Transfer', ['20', '21', '22', '23']],
                    ['What is the primary purpose of Wireshark?', 'Packet Capture', 'Analysis', ['Password Cracking', 'Packet Capture', 'Virus Scanning', 'Routing']]
                ]
            }
        };

        let totalCreated = 0;
        
        // Iterate through the lesson keys (IDs)
        for (const [idStr, lesson] of Object.entries(lessons)) {
            const taskId = parseInt(idStr);
            console.log(`Processing Task ${taskId}: ${lesson.title}`);

            // Clean up existing content
            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', 10]);

            // Insert Questions (Max 3 for normal lessons, 20 for Quiz)
            const maxQs = taskId === 31 ? lesson.questions.length : Math.min(lesson.questions.length, 3);
            
            for (let j = 0; j < maxQs; 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]);
            }
            totalCreated++;
        }

        console.log(`\n✅ Module 4 Detailed Content Created! (${totalCreated} lessons processed)\n`);
        process.exit(0);
    } catch (error) {
        console.error('Error:', error);
        process.exit(1);
    }
}

createNetworkingLessons();
