================================================================================
  Question 30: Database Web CRUD Operations using CGI (MySQL / SQLite)
================================================================================

FULL QUESTION:
Task:
Create a MySQL/SQLite database and perform INSERT, UPDATE, DELETE, and SELECT
(display) operations using the CGI concept.

Table: students
- id         : INT AUTO_INCREMENT PRIMARY KEY
- fullname   : VARCHAR(100) NOT NULL
- email      : VARCHAR(100) NOT NULL
- msc_course : VARCHAR(100) NOT NULL

SHORT QUESTION:
Web-based CGI student management interface performing INSERT, UPDATE, DELETE, and SELECT operations.

================================================================================
README & SETUP INSTRUCTIONS:
================================================================================
# Question 30: Database Web CRUD Operations using CGI (MySQL / SQLite)

- Prerequisites: Python 3.x (Standard library sqlite3/urllib/html).
- Running in CGI Environment: Place in your web server's CGI folder.
- Standalone / Terminal Testing: Run `python question_30.py` to see the generated HTML with database records.
- How to Run: python question_30.py
- Expected Behavior: Connects to database, creates table, processes submitted actions, and outputs HTML dashboard.

================================================================================
SOURCE CODE FILES:
================================================================================

1. question_30.py
--------------------------------------------------------------------------------
"""
SQL Setup (Cell 101):
CREATE DATABASE IF NOT EXISTS msc_admissions;

USE msc_admissions;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    msc_course VARCHAR(100) NOT NULL
);

Package Install (Cell 102):
pip install mysql-connector-python
"""

import sys
import os
import urllib.parse
import html
import mysql.connector

print("Content-Type: text/html\n\n")

def get_db_connection():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="",
        database="msc_admissions"
    )

message = ""

if os.environ.get('REQUEST_METHOD', 'GET') == 'POST':
    content_length = int(os.environ.get('CONTENT_LENGTH', 0))
    post_data = sys.stdin.read(content_length)
    form = urllib.parse.parse_qs(post_data)

    action = form.get("action", [""])[0]
    student_id = form.get("id", [""])[0]
    fullname = form.get("fullname", [""])[0]
    email = form.get("email", [""])[0]
    msc_course = form.get("msc_course", [""])[0]

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        if action == "INSERT":
            sql = "INSERT INTO students (fullname, email, msc_course) VALUES (%s, %s, %s)"
            cursor.execute(sql, (fullname, email, msc_course))
            conn.commit()
            message = "Record inserted successfully!"

        elif action == "UPDATE":
            sql = "UPDATE students SET fullname = %s, email = %s, msc_course = %s WHERE id = %s"
            cursor.execute(sql, (fullname, email, msc_course, student_id))
            conn.commit()
            if cursor.rowcount > 0:
                message = f"Record ID {student_id} updated successfully!"
            else:
                message = f"Record ID {student_id} not found."

        elif action == "DELETE":
            sql = "DELETE FROM students WHERE id = %s"
            cursor.execute(sql, (student_id,))
            conn.commit()
            if cursor.rowcount > 0:
                message = f"Record ID {student_id} deleted successfully!"
            else:
                message = f"Record ID {student_id} not found."

        cursor.close()
        conn.close()
    except Exception as e:
        message = f"Database Error: {html.escape(str(e))}"

students_list = []
try:
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM students ORDER BY id DESC")
    students_list = cursor.fetchall()
    cursor.close()
    conn.close()
except Exception as e:
    message += f" (Select Error: {html.escape(str(e))})"

print(f"""<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>MSc Student Management (CGI + MySQL)</title>
    <style>
        body {{ font-family: Arial, sans-serif; background-color: #f4f7f6; margin: 30px; }}
        .container {{ max-width: 800px; background: white; padding: 25px; margin: 0 auto; border-radius: 8px; box-shadow: 0 0 10px rgba(0,0,0,0.1); }}
        h2 {{ color: #2c3e50; text-align: center; }}
        .form-section {{ background: #eef2f5; padding: 15px; border-radius: 6px; margin-bottom: 20px; }}
        label {{ font-weight: bold; margin-top: 8px; display: block; }}
        input, select {{ width: 100%; padding: 8px; margin-top: 4px; box-sizing: border-box; border: 1px solid #ccc; border-radius: 4px; }}
        .btn-group {{ margin-top: 15px; display: flex; gap: 10px; }}
        .btn {{ flex: 1; padding: 10px; color: white; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; font-weight: bold; }}
        .btn-insert {{ background-color: #27ae60; }}
        .btn-update {{ background-color: #f39c12; }}
        .btn-delete {{ background-color: #e74c3c; }}
        .alert {{ padding: 10px; background-color: #d4edda; color: #155724; border-radius: 4px; margin-bottom: 15px; font-weight: bold; }}
        table {{ width: 100%; border-collapse: collapse; margin-top: 15px; }}
        th, td {{ border: 1px solid #ddd; padding: 10px; text-align: left; }}
        th {{ background-color: #2c3e50; color: white; }}
    </style>
</head>
<body>

<div class="container">
    <h2>MSc Student Management System</h2>

    {"<div class='alert'>" + message + "</div>" if message else ""}

    <!-- Form for INSERT, UPDATE, DELETE -->
    <div class="form-section">
        <form action="/cgi-bin/crud.py" method="post">
            <label for="id">Student ID (Required for Update/Delete):</label>
            <input type="number" id="id" name="id" placeholder="e.g. 1">

            <label for="fullname">Full Name:</label>
            <input type="text" id="fullname" name="fullname" placeholder="e.g. John Doe">

            <label for="email">Email Address:</label>
            <input type="email" id="email" name="email" placeholder="e.g. john@example.com">

            <label for="msc_course">MSc Specialty:</label>
            <input type="text" id="msc_course" name="msc_course" placeholder="e.g. MSc Computer Science">

            <div class="btn-group">
                <button type="submit" name="action" value="INSERT" class="btn btn-insert">INSERT</button>
                <button type="submit" name="action" value="UPDATE" class="btn btn-update">UPDATE</button>
                <button type="submit" name="action" value="DELETE" class="btn btn-delete">DELETE</button>
            </div>
        </form>
    </div>

    <!-- SELECT / Display Table -->
    <h3>Registered Students (SELECT Display)</h3>
    <table>
        <tr>
            <th>ID</th>
            <th>Full Name</th>
            <th>Email</th>
            <th>MSc Course</th>
        </tr>
""")

if students_list:
    for student in students_list:
        print(f"""
        <tr>
            <td>{student['id']}</td>
            <td>{html.escape(student['fullname'])}</td>
            <td>{html.escape(student['email'])}</td>
            <td>{html.escape(student['msc_course'])}</td>
        </tr>
        """)
else:
    print("<tr><td colspan='4' style='text-align:center;'>No records found in database.</td></tr>")

print("""
    </table>
</div>

</body>
</html>
""")
--------------------------------------------------------------------------------
