-- CCMS SQL Script
-- Creates database, tables, constraints, sample data,
-- stored procedures, triggers and indexes for the
-- Campus Club Management System.

/* =======================
   1. DATABASE CREATION
   ======================= */
   
CREATE DATABASE ccms_db;
USE ccms_db;

/* =======================
   2. TABLE DEFINITIONS
   ======================= */

-- CLUB
CREATE TABLE Club (
    club_id INT AUTO_INCREMENT PRIMARY KEY,
    club_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    founded_date DATE NOT NULL,
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    description TEXT
);

-- STUDENT
CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    programme VARCHAR(100),
    year_of_study INT CHECK (year_of_study BETWEEN 1 AND 5)
);

-- MEMBERSHIP (associative entity)
CREATE TABLE Membership (
    student_id INT NOT NULL,
    club_id INT NOT NULL,
    joined_date DATE NOT NULL,
    role ENUM('Member', 'Committee', 'President') DEFAULT 'Member',
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (club_id) REFERENCES Club(club_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- EVENT
CREATE TABLE Event (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    club_id INT NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    event_type VARCHAR(50),
    event_date DATE NOT NULL,
    start_time TIME,
    end_time TIME,
    venue VARCHAR(100),
    capacity INT CHECK (capacity >= 0),
    attendance_restriction VARCHAR(50),
    FOREIGN KEY (club_id) REFERENCES Club(club_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- EVENT PARTICIPATION
CREATE TABLE EventParticipation (
    event_id INT NOT NULL,
    student_id INT NOT NULL,
    rsvp_status ENUM('Registered','Interested','Cancelled') DEFAULT 'Registered',
    attended TINYINT(1) DEFAULT 0,
    checked_in_at DATETIME NULL,
    PRIMARY KEY (event_id, student_id),
    FOREIGN KEY (event_id) REFERENCES Event(event_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- ANNOUNCEMENT
CREATE TABLE Announcement (
    announcement_id INT AUTO_INCREMENT PRIMARY KEY,
    club_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    body TEXT NOT NULL,
    channel VARCHAR(50) NOT NULL,
    sent_at DATETIME NOT NULL,
    FOREIGN KEY (club_id) REFERENCES Club(club_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- USER ACCOUNT (for admin/club officers)
CREATE TABLE UserAccount (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('Admin','Officer') DEFAULT 'Officer'
);

-- MEMBERSHIP AUDIT TABLE
CREATE TABLE MembershipAudit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    club_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    changed_by INT NOT NULL,
    changed_at DATETIME NOT NULL
);

/* =======================
   3. SAMPLE DATA
   ======================= */

-- Clubs
INSERT INTO Club (club_name, category, founded_date, status, description) VALUES
('Cybersecurity Society', 'Academic', '2022-10-01', 'Active', 'Ethical hacking workshops and talks'),
('International Students Club', 'Cultural', '2019-03-10', 'Active', 'Cultural exchange and social events'),
('Basketball Team', 'Sports', '2018-09-01', 'Active', 'University basketball team training and matches'),
('Film & Media Society', 'Academic', '2021-02-15', 'Active', 'Film screenings and media discussions'),
('Music Ensemble', 'Cultural', '2017-11-20', 'Active', 'Music performances and rehearsals');

-- Students
INSERT INTO Student (first_name, last_name, email, programme, year_of_study) VALUES
('Ovidiu', 'Strinu', 'ovidiu.strinu@uwtsd.ac.uk', 'Computing', 1),
('John', 'Walker', 'john.walker@uwtsd.ac.uk', 'Business', 2),
('Amira', 'Ali', 'amira.ali@uwtsd.ac.uk', 'Cybersecurity', 3),
('Lina', 'Patel', 'lina.patel@uwtsd.ac.uk', 'Engineering', 1),
('Mark', 'Evans', 'mark.evans@uwtsd.ac.uk', 'Computing', 2),
('Sara', 'Jones', 'sara.jones@uwtsd.ac.uk', 'Media', 3);

-- Memberships
INSERT INTO Membership (student_id, club_id, joined_date, role, status) VALUES
(1, 1, '2024-09-20', 'Member', 'Active'),
(1, 2, '2024-09-22', 'Committee', 'Active'),
(2, 2, '2024-09-21', 'Member', 'Active'),
(3, 1, '2024-09-25', 'Member', 'Active'),
(4, 3, '2024-09-26', 'Member', 'Active'),
(5, 1, '2024-09-27', 'Member', 'Active'),
(6, 4, '2024-09-28', 'Member', 'Active');

-- Events
INSERT INTO Event (club_id, event_name, event_type, event_date, start_time, end_time, venue, capacity, attendance_restriction)
VALUES
(1, 'Intro to Ethical Hacking', 'Workshop', '2024-11-10', '17:00:00', '19:00:00', 'Lab 3.01', 30, 'Members only'),
(1, 'CTF Evening', 'Competition', '2024-11-20', '18:00:00', '21:00:00', 'Cyber Lab', 25, 'Members only'),
(2, 'International Food Night', 'Social', '2024-11-15', '18:00:00', '21:00:00', 'Student Union Hall', 100, 'Open to all'),
(3, 'Basketball Training', 'Training', '2024-11-12', '16:00:00', '18:00:00', 'Sports Hall', 20, 'Team only'),
(4, 'Film Screening: Classic Cinema', 'Screening', '2024-11-18', '19:00:00', '21:30:00', 'Lecture Theatre 2', 80, 'Open to all');

-- Event Participation
INSERT INTO EventParticipation (event_id, student_id, rsvp_status, attended, checked_in_at) VALUES
(1, 1, 'Registered', 1, '2024-11-10 17:05:00'),
(1, 3, 'Registered', 1, '2024-11-10 17:10:00'),
(2, 1, 'Registered', 0, NULL),
(3, 2, 'Interested', 0, NULL),
(3, 4, 'Registered', 1, '2024-11-15 18:05:00');

-- Announcements
INSERT INTO Announcement (club_id, title, body, channel, sent_at) VALUES
(1, 'Welcome to Cybersecurity Society', 'Join us for our first workshop!', 'Email', '2024-10-01 10:00:00'),
(2, 'International Food Night', 'Bring a dish from your home country.', 'Email', '2024-10-05 09:00:00'),
(3, 'Team Trials', 'Basketball team trials this weekend.', 'Social Media', '2024-10-07 14:00:00');

-- User accounts (password_hash values are placeholders)
INSERT INTO UserAccount (username, password_hash, role) VALUES
('admin', '$2y$10$examplehashedpasswordstringforadminuser', 'Admin'),
('cyber_officer', '$2y$10$examplehashedpasswordstringforofficer', 'Officer');

/* =======================
   4. INDEXES
   ======================= */

CREATE INDEX idx_event_date ON Event(event_date);
CREATE INDEX idx_membership_student ON Membership(student_id);
CREATE INDEX idx_membership_club ON Membership(club_id);
CREATE INDEX idx_participation_event ON EventParticipation(event_id);
CREATE INDEX idx_announcement_club ON Announcement(club_id, sent_at);

/* =======================
   5. STORED PROCEDURES
   ======================= */

DELIMITER //

CREATE PROCEDURE sp_add_student (
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_programme VARCHAR(100),
    IN p_year_of_study INT
)
BEGIN
    INSERT INTO Student(first_name, last_name, email, programme, year_of_study)
    VALUES (p_first_name, p_last_name, p_email, p_programme, p_year_of_study);
END //

CREATE PROCEDURE sp_register_for_event (
    IN p_student_id INT,
    IN p_event_id INT,
    IN p_user_id INT
)
BEGIN
    DECLARE v_capacity INT;
    DECLARE v_current INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

    SELECT capacity INTO v_capacity
    FROM Event
    WHERE event_id = p_event_id
    FOR UPDATE;

    SELECT COUNT(*) INTO v_current
    FROM EventParticipation
    WHERE event_id = p_event_id;

    IF v_current >= v_capacity THEN
        ROLLBACK;
    ELSE
        INSERT INTO EventParticipation(event_id, student_id, rsvp_status, attended)
        VALUES (p_event_id, p_student_id, 'Registered', 0);

        INSERT INTO MembershipAudit(student_id, club_id, action, changed_by, changed_at)
        SELECT s.student_id, e.club_id, 'EventRegistration', p_user_id, NOW()
        FROM Student s
        JOIN Event e ON e.event_id = p_event_id
        WHERE s.student_id = p_student_id;

        COMMIT;
    END IF;
END //

DELIMITER ;

/* =======================
   6. TRIGGERS
   ======================= */

DELIMITER //

CREATE TRIGGER tr_membership_audit
AFTER INSERT ON Membership
FOR EACH ROW
BEGIN
    INSERT INTO MembershipAudit(student_id, club_id, action, changed_by, changed_at)
    VALUES (NEW.student_id, NEW.club_id, 'INSERT', 0, NOW());
END //

DELIMITER ;

/* =======================
   7. TEST / DEMO QUERIES
   ======================= */

-- Example query: list active clubs and their categories
SELECT club_name, category, status FROM Club WHERE status = 'Active';

-- Example query: upcoming events
SELECT e.event_name, e.event_date, c.club_name
FROM Event e
JOIN Club c ON e.club_id = c.club_id
WHERE e.event_date >= CURDATE()
ORDER BY e.event_date;

-- Example EXPLAIN to evaluate index usage
EXPLAIN SELECT event_name, event_date
FROM Event
WHERE event_date >= CURDATE()
ORDER BY event_date;