// ================== TASK 2 ==================
// Database CRUD Operations

// ================== DATABASE SCHEMA ==================

CREATE TABLE Modules (
    moduleID INT AUTO_INCREMENT PRIMARY KEY,
    moduleCode VARCHAR(20) UNIQUE NOT NULL,
    moduleName VARCHAR(100) NOT NULL
);

CREATE TABLE Lecturers (
    lecturerID INT AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Roles (
    roleID INT AUTO_INCREMENT PRIMARY KEY,
    roleName VARCHAR(50) NOT NULL,
    roleDescription TEXT
);

CREATE TABLE ModuleLecturerRole (
    moduleLecturerRoleID INT AUTO_INCREMENT PRIMARY KEY,
    moduleID INT NOT NULL,
    lecturerID INT NOT NULL,
    roleID INT NOT NULL,
    FOREIGN KEY (moduleID) REFERENCES Modules(moduleID) ON DELETE CASCADE,
    FOREIGN KEY (lecturerID) REFERENCES Lecturers(lecturerID) ON DELETE CASCADE,
    FOREIGN KEY (roleID) REFERENCES Roles(roleID) ON DELETE CASCADE,
    UNIQUE KEY unique_assignment (moduleID, lecturerID, roleID)
);

// ================== SAMPLE DATA ==================

INSERT INTO Modules (moduleCode, moduleName) VALUES
('ICT2611', 'Internet Programming'),
('ICT2613', 'Internet Programming'),
('ICT3612', 'Advanced Internet Programming'),
('ICT3715', 'Web Application Development'),
('ICT3721', 'Database Design');

INSERT INTO Lecturers (firstName, lastName, email) VALUES
('John', 'Smith', 'smithj@unisa.ac.za'),
('Mary', 'Johnson', 'johnsonm@unisa.ac.za'),
('Peter', 'Williams', 'williamsp@unisa.ac.za'),
('Susan', 'Brown', 'browns@unisa.ac.za'),
('David', 'Jones', 'jonesd@unisa.ac.za');

INSERT INTO Roles (roleName, roleDescription) VALUES
('Primary Lecturer', 'Main lecturer responsible for the module'),
('Secondary Lecturer', 'Supporting lecturer for the module'),
('Internal Moderator', 'Internal quality assurance'),
('External Moderator', 'External quality assurance'),
('Critical Reader', 'Reviews module content');

// ================== CRUD OPERATIONS ==================

// Add Module
if (isset($_POST['add_module'])) {
    $code = $_POST['moduleCode'];
    $name = $_POST['moduleName'];
    $stmt = $conn->prepare("INSERT INTO Modules (moduleCode, moduleName) VALUES (?, ?)");
    $stmt->bind_param("ss", $code, $name);
    $stmt->execute();
}

// Update ModuleLecturerRole
if (isset($_POST['update_assignment'])) {
    $id = $_POST['assignment_id'];
    $moduleID = $_POST['moduleID'];
    $lecturerID = $_POST['lecturerID'];
    $roleID = $_POST['roleID'];
    $stmt = $conn->prepare("UPDATE ModuleLecturerRole SET moduleID=?, lecturerID=?, roleID=? WHERE moduleLecturerRoleID=?");
    $stmt->bind_param("iiii", $moduleID, $lecturerID, $roleID, $id);
    $stmt->execute();
}

// Delete Lecturer
if (isset($_POST['delete_lecturer'])) {
    $id = $_POST['lecturerID'];
    $stmt = $conn->prepare("DELETE FROM Lecturers WHERE lecturerID = ?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
}

// ================== SELECT QUERIES ==================

// 1. ORDER BY
$q1 = "SELECT * FROM Modules ORDER BY moduleCode";

// 2. LIKE
$q2 = "SELECT * FROM Lecturers WHERE email LIKE '%@unisa.ac.za'";

// 3. INNER JOIN
$q3 = "SELECT m.moduleCode, CONCAT(l.firstName, ' ', l.lastName) as lecturer, r.roleName 
       FROM ModuleLecturerRole mlr 
       JOIN Modules m ON mlr.moduleID = m.moduleID 
       JOIN Lecturers l ON mlr.lecturerID = l.lecturerID 
       JOIN Roles r ON mlr.roleID = r.roleID";

// 4. WHERE AND
$q4 = "SELECT * FROM Lecturers WHERE firstName LIKE 'J%' AND email LIKE '%@unisa.ac.za'";

// 5. MAX
$q5 = "SELECT MAX(moduleID) as maxModuleID FROM Modules";