// ================== TASK 04 // TRAINING SYSTEM ==================
// Cyberpunk Edition - MVC Architecture

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

CREATE TABLE Organisations (
    orgID INT AUTO_INCREMENT PRIMARY KEY,
    orgName VARCHAR(100) NOT NULL,
    orgEmail VARCHAR(100) UNIQUE NOT NULL,
    orgPhone VARCHAR(20),
    address TEXT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    isAdmin BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Employees (
    employeeID INT AUTO_INCREMENT PRIMARY KEY,
    orgID INT NOT NULL,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(50) NOT NULL,
    employeeEmail VARCHAR(100),
    position VARCHAR(50),
    FOREIGN KEY (orgID) REFERENCES Organisations(orgID) ON DELETE CASCADE
);

CREATE TABLE Trainings (
    trainingID INT AUTO_INCREMENT PRIMARY KEY,
    trainingName VARCHAR(100) NOT NULL,
    description TEXT,
    duration VARCHAR(50),
    cost DECIMAL(10,2) NOT NULL,
    startDate DATE,
    endDate DATE,
    maxParticipants INT DEFAULT 0
);

CREATE TABLE TrainingRegistrations (
    registrationID INT AUTO_INCREMENT PRIMARY KEY,
    employeeID INT NOT NULL,
    trainingID INT NOT NULL,
    registrationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Registered',
    FOREIGN KEY (employeeID) REFERENCES Employees(employeeID) ON DELETE CASCADE,
    FOREIGN KEY (trainingID) REFERENCES Trainings(trainingID) ON DELETE CASCADE,
    UNIQUE KEY unique_reg (employeeID, trainingID)
);

// ================== MVC MODEL ==================

class TrainingModel {
    private $conn;
    public function __construct($conn) { $this->conn = $conn; }
    
    public function getOrg($id) {
        $s = $this->conn->prepare("SELECT * FROM Organisations WHERE orgID = ?");
        $s->bind_param("i", $id); $s->execute();
        return $s->get_result()->fetch_assoc();
    }
    
    public function getEmployees($oid) {
        $s = $this->conn->prepare("SELECT * FROM Employees WHERE orgID = ?");
        $s->bind_param("i", $oid); $s->execute();
        return $s->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getTrainings() {
        return $this->conn->query("SELECT * FROM Trainings")->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getRegistrations($oid) {
        $s = $this->conn->prepare("
            SELECT tr.*, e.firstName, e.lastName, t.trainingName, t.cost
            FROM TrainingRegistrations tr
            JOIN Employees e ON tr.employeeID = e.employeeID
            JOIN Trainings t ON tr.trainingID = t.trainingID
            WHERE e.orgID = ?
        ");
        $s->bind_param("i", $oid); $s->execute();
        return $s->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function addRegistration($eid, $tid) {
        $s = $this->conn->prepare("INSERT INTO TrainingRegistrations (employeeID, trainingID) VALUES (?, ?)");
        $s->bind_param("ii", $eid, $tid);
        return $s->execute();
    }
    
    public function updateRegistration($rid, $status) {
        $s = $this->conn->prepare("UPDATE TrainingRegistrations SET status = ? WHERE registrationID = ?");
        $s->bind_param("si", $status, $rid);
        return $s->execute();
    }
    
    public function deleteRegistration($rid) {
        $s = $this->conn->prepare("DELETE FROM TrainingRegistrations WHERE registrationID = ?");
        $s->bind_param("i", $rid);
        return $s->execute();
    }
    
    public function getTotalCost($oid) {
        $s = $this->conn->prepare("
            SELECT SUM(t.cost) as totalCost 
            FROM TrainingRegistrations tr 
            JOIN Employees e ON tr.employeeID = e.employeeID 
            JOIN Trainings t ON tr.trainingID = t.trainingID 
            WHERE e.orgID = ?
        ");
        $s->bind_param("i", $oid); $s->execute();
        return $s->get_result()->fetch_assoc()['totalCost'] ?? 0;
    }
}

// ================== MVC CONTROLLER ==================

class TrainingController {
    private $model; private $conn;
    public function __construct($conn) {
        $this->conn = $conn;
        $this->model = new TrainingModel($conn);
    }
    
    public function login($username, $password) {
        $s = $this->conn->prepare("SELECT * FROM Organisations WHERE username = ?");
        $s->bind_param("s", $username); $s->execute();
        $user = $s->get_result()->fetch_assoc();
        if ($user && password_verify($password, $user['password'])) return $user;
        return false;
    }
    
    public function registerOrg($data) {
        $s = $this->conn->prepare("
            INSERT INTO Organisations (orgName, orgEmail, orgPhone, address, username, password) 
            VALUES (?, ?, ?, ?, ?, ?)
        ");
        $s->bind_param("ssssss", $data['orgName'], $data['orgEmail'], $data['orgPhone'], 
                       $data['address'], $data['username'], $data['password']);
        return $s->execute();
    }
    
    public function addEmployee($oid, $data) {
        $s = $this->conn->prepare("
            INSERT INTO Employees (orgID, firstName, lastName, employeeEmail, position) 
            VALUES (?, ?, ?, ?, ?)
        ");
        $s->bind_param("issss", $oid, $data['firstName'], $data['lastName'], 
                       $data['employeeEmail'], $data['position']);
        return $s->execute();
    }
}

// ================== ADMIN QUERIES ==================

// Participants by training
SELECT t.trainingName, COUNT(tr.registrationID) as participantCount,
       GROUP_CONCAT(CONCAT(e.firstName, ' ', e.lastName) SEPARATOR ', ') as participants
FROM Trainings t
LEFT JOIN TrainingRegistrations tr ON t.trainingID = tr.trainingID
LEFT JOIN Employees e ON tr.employeeID = e.employeeID
GROUP BY t.trainingID

// Registrations by company
SELECT o.orgName, COUNT(tr.registrationID) as regCount,
       GROUP_CONCAT(CONCAT(e.firstName, ' ', e.lastName, ' (', t.trainingName, ')') SEPARATOR ', ') as details
FROM Organisations o
LEFT JOIN Employees e ON o.orgID = e.orgID
LEFT JOIN TrainingRegistrations tr ON e.employeeID = tr.employeeID
LEFT JOIN Trainings t ON tr.trainingID = t.trainingID
WHERE o.isAdmin = FALSE
GROUP BY o.orgID

// ================== TEST CREDENTIALS ==================

Admin: admin / admin123
Org1: techcorp / tech123
Org2: datasol / data123
Org3: cloudsys / cloud123