-- Edu Portal - PostgreSQL Schema + Seed
-- Database: myczroxg_edu
-- Select that database in phpPgAdmin first, then paste and run this file.
-- No pgcrypto / uuid-ossp extension required.

-- ========== SCHEMA ==========

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  role VARCHAR(20) NOT NULL CHECK (role IN ('student', 'staff', 'admin', 'super_admin')),
  student_id VARCHAR(50) UNIQUE,
  staff_id VARCHAR(50) UNIQUE,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS semesters (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  name VARCHAR(100) NOT NULL,
  year INTEGER NOT NULL,
  term VARCHAR(20) NOT NULL CHECK (term IN ('first', 'second', 'third')),
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_active BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS courses (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  code VARCHAR(20) UNIQUE NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  credits INTEGER DEFAULT 3,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS classes (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  semester_id UUID NOT NULL REFERENCES semesters(id) ON DELETE CASCADE,
  staff_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  schedule VARCHAR(200),
  room VARCHAR(100),
  max_students INTEGER DEFAULT 50,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS enrollments (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  student_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(class_id, student_id)
);

CREATE TABLE IF NOT EXISTS grades (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  enrollment_id UUID NOT NULL REFERENCES enrollments(id) ON DELETE CASCADE,
  assignment_name VARCHAR(200) NOT NULL,
  score DECIMAL(5,2) NOT NULL,
  max_score DECIMAL(5,2) DEFAULT 100,
  grade_type VARCHAR(50) DEFAULT 'assignment',
  feedback TEXT,
  graded_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS exams (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  exam_type VARCHAR(50) DEFAULT 'exam',
  duration_minutes INTEGER NOT NULL DEFAULT 60,
  total_marks DECIMAL(6,2) DEFAULT 100,
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ NOT NULL,
  is_published BOOLEAN DEFAULT false,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS exam_questions (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  exam_id UUID NOT NULL REFERENCES exams(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type VARCHAR(20) DEFAULT 'mcq' CHECK (question_type IN ('mcq', 'short_answer', 'essay')),
  options JSONB,
  correct_answer TEXT,
  marks DECIMAL(5,2) DEFAULT 1,
  order_index INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS exam_submissions (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  exam_id UUID NOT NULL REFERENCES exams(id) ON DELETE CASCADE,
  student_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  answers JSONB NOT NULL DEFAULT '{}',
  score DECIMAL(6,2),
  submitted_at TIMESTAMPTZ,
  status VARCHAR(20) DEFAULT 'in_progress' CHECK (status IN ('in_progress', 'submitted', 'graded')),
  UNIQUE(exam_id, student_id)
);

CREATE TABLE IF NOT EXISTS staff_meetings (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  title VARCHAR(200) NOT NULL,
  description TEXT,
  host_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  meeting_link VARCHAR(500),
  scheduled_at TIMESTAMPTZ NOT NULL,
  duration_minutes INTEGER DEFAULT 60,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS meeting_attendees (
  id UUID PRIMARY KEY DEFAULT (md5(random()::text || clock_timestamp()::text)::uuid),
  meeting_id UUID NOT NULL REFERENCES staff_meetings(id) ON DELETE CASCADE,
  staff_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status VARCHAR(20) DEFAULT 'invited' CHECK (status IN ('invited', 'accepted', 'declined')),
  UNIQUE(meeting_id, staff_id)
);

CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_classes_semester ON classes(semester_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_student ON enrollments(student_id);
CREATE INDEX IF NOT EXISTS idx_exams_class ON exams(class_id);
CREATE INDEX IF NOT EXISTS idx_exam_submissions_student ON exam_submissions(student_id);

-- ========== SEED DATA ==========
-- Default password for all accounts: Password123!

INSERT INTO users (email, password_hash, first_name, last_name, role)
VALUES (
  'superadmin@edu.utibe.name.ng',
  '$2a$10$nAWglvfutSZUzE7ypmfADed1V9I8/Um1YyJ8uFdidjBKm4Y8E33Ie',
  'Super',
  'Admin',
  'super_admin'
)
ON CONFLICT (email) DO NOTHING;

INSERT INTO users (email, password_hash, first_name, last_name, role, staff_id)
VALUES (
  'admin@edu.utibe.name.ng',
  '$2a$10$nAWglvfutSZUzE7ypmfADed1V9I8/Um1YyJ8uFdidjBKm4Y8E33Ie',
  'System',
  'Admin',
  'admin',
  'ADM001'
)
ON CONFLICT (email) DO NOTHING;

INSERT INTO users (email, password_hash, first_name, last_name, role, staff_id)
VALUES (
  'staff@edu.utibe.name.ng',
  '$2a$10$nAWglvfutSZUzE7ypmfADed1V9I8/Um1YyJ8uFdidjBKm4Y8E33Ie',
  'Jane',
  'Teacher',
  'staff',
  'STF001'
)
ON CONFLICT (email) DO NOTHING;

INSERT INTO users (email, password_hash, first_name, last_name, role, student_id)
VALUES (
  'student@edu.utibe.name.ng',
  '$2a$10$nAWglvfutSZUzE7ypmfADed1V9I8/Um1YyJ8uFdidjBKm4Y8E33Ie',
  'John',
  'Student',
  'student',
  'STU001'
)
ON CONFLICT (email) DO NOTHING;

INSERT INTO semesters (name, year, term, start_date, end_date, is_active)
SELECT '2025/2026 First Semester', 2025, 'first', '2025-09-01', '2026-01-15', true
WHERE NOT EXISTS (
  SELECT 1 FROM semesters WHERE name = '2025/2026 First Semester'
);

INSERT INTO courses (code, title, description, credits)
VALUES ('CS101', 'Introduction to Computer Science', 'Fundamentals of computing', 3)
ON CONFLICT (code) DO NOTHING;

INSERT INTO classes (course_id, semester_id, staff_id, schedule, room)
SELECT c.id, s.id, u.id, 'Mon/Wed 10:00 AM', 'Room 201'
FROM courses c
JOIN semesters s ON s.name = '2025/2026 First Semester'
JOIN users u ON u.email = 'staff@edu.utibe.name.ng'
WHERE c.code = 'CS101'
  AND NOT EXISTS (
    SELECT 1
    FROM classes cl
    WHERE cl.course_id = c.id
      AND cl.semester_id = s.id
      AND cl.staff_id = u.id
  );

INSERT INTO enrollments (class_id, student_id)
SELECT cl.id, st.id
FROM classes cl
JOIN courses c ON c.id = cl.course_id AND c.code = 'CS101'
JOIN users st ON st.email = 'student@edu.utibe.name.ng'
JOIN users sf ON sf.id = cl.staff_id AND sf.email = 'staff@edu.utibe.name.ng'
ON CONFLICT (class_id, student_id) DO NOTHING;

INSERT INTO grades (enrollment_id, assignment_name, score, max_score, grade_type, graded_by)
SELECT e.id, 'Midterm Assignment', 85, 100, 'assignment', sf.id
FROM enrollments e
JOIN classes cl ON cl.id = e.class_id
JOIN courses c ON c.id = cl.course_id AND c.code = 'CS101'
JOIN users st ON st.id = e.student_id AND st.email = 'student@edu.utibe.name.ng'
JOIN users sf ON sf.email = 'staff@edu.utibe.name.ng'
WHERE NOT EXISTS (
  SELECT 1 FROM grades g
  WHERE g.enrollment_id = e.id
    AND g.assignment_name = 'Midterm Assignment'
);