// ================== TASK 4 ==================
// Training Registration System - Database Application

// ================== 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_registration (employeeID, trainingID)
);

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

INSERT INTO Organisations (orgName, orgEmail, username, password, isAdmin) 
VALUES ('Admin', 'admin@system.com', 'admin', 'admin123', TRUE);

INSERT INTO Organisations (orgName, orgEmail, username, password, address) VALUES
('TechCorp', 'info@techcorp.com', 'techcorp', 'tech123', '123 Tech Street'),
('DataSolutions', 'info@datasolutions.com', 'datasol', 'data123', '456 Data Ave');

INSERT INTO Trainings (trainingName, description, duration, cost, startDate, endDate, maxParticipants) VALUES
('Python Programming', 'Learn Python from basics to advanced', '5 days', 1500.00, '2026-01-15', '2026-01-19', 20),
('Data Science', 'Data analysis and machine learning', '10 days', 2500.00, '2026-02-01', '2026-02-10', 15),
('Web Development', 'Full-stack web development', '8 days', 2000.00, '2026-03-01', '2026-03-08', 25);

// ================== MVC STRUCTURE ==================

// Model: Database operations
class TrainingModel {
    private $conn;
    
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    public function getOrganisation($id) {
        $stmt = $this->conn->prepare("SELECT * FROM Organisations WHERE orgID = ?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    
    public function getEmployees($orgId) {
        $stmt = $this->conn->prepare("SELECT * FROM Employees WHERE orgID = ?");
        $stmt->bind_param("i", $orgId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getTrainings() {
        return $this->conn->query("SELECT * FROM Trainings")->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getRegistrations($orgId) {
        $stmt = $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 = ?
        ");
        $stmt->bind_param("i", $orgId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function addRegistration($employeeID, $trainingID) {
        $stmt = $this->conn->prepare("INSERT INTO TrainingRegistrations (employeeID, trainingID) VALUES (?, ?)");
        $stmt->bind_param("ii", $employeeID, $trainingID);
        return $stmt->execute();
    }
    
    public function updateRegistration($registrationID, $status) {
        $stmt = $this->conn->prepare("UPDATE TrainingRegistrations SET status = ? WHERE registrationID = ?");
        $stmt->bind_param("si", $status, $registrationID);
        return $stmt->execute();
    }
    
    public function deleteRegistration($registrationID) {
        $stmt = $this->conn->prepare("DELETE FROM TrainingRegistrations WHERE registrationID = ?");
        $stmt->bind_param("i", $registrationID);
        return $stmt->execute();
    }
    
    public function getTotalCost($orgId) {
        $stmt = $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 = ?
        ");
        $stmt->bind_param("i", $orgId);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc()['totalCost'] ?? 0;
    }
}

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

// View: HTML templates are rendered in the main page

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

// List 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

// List 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