schema.sql

CREATE DATABASE IF NOT EXISTS alumni_portal;
USE alumni_portal;

CREATE TABLE IF NOT EXISTS alumni_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(120) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'student',
  grad_year INT,
  department VARCHAR(80),
  password_hash VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  event_date DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  company VARCHAR(120) NOT NULL,
  posted_on DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS feedback (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(100) NOT NULL,
  message VARCHAR(255) NOT NULL,
  rating TINYINT NULL,
  created_at DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS networking_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_type VARCHAR(20) NOT NULL,
  request_target VARCHAR(120),
  requester_name VARCHAR(120) NOT NULL,
  requester_email VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS job_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_title VARCHAR(120) NOT NULL,
  job_company VARCHAR(120) NOT NULL,
  applicant_name VARCHAR(120) NOT NULL,
  applicant_email VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS event_registrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_title VARCHAR(200) NOT NULL,
  registrant_name VARCHAR(120) NOT NULL,
  registrant_email VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL
);