Final Project: Build a Complete PostgreSQL Database System

-- 1. The Users Table (Students & Instructors)
CREATE TABLE users (
    user_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    email VARCHAR(150) UNIQUE NOT NULL,
    role VARCHAR(50) DEFAULT 'Student' CHECK (role IN ('Student', 'Instructor', 'Admin')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. The Courses Table
CREATE TABLE courses (
    course_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    instructor_id UUID REFERENCES users(user_id) ON DELETE RESTRICT,
    title VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    is_published BOOLEAN DEFAULT FALSE,
    quiz_metadata JSONB -- Flexible storage for complex quiz structures!
);

-- 3. The Enrollments Table (Pivot: Many Students -> Many Courses)
CREATE TABLE enrollments (
    enrollment_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id UUID REFERENCES users(user_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE RESTRICT,
    amount_paid NUMERIC(10, 2) NOT NULL, -- Freezes the historical purchase price!
    enrolled_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (student_id, course_id) -- A student cannot buy the exact same course twice!
);

-- Insert an Instructor
INSERT INTO users (email, role) VALUES ('teacher@techcourse.com', 'Instructor');

-- Insert a Student
INSERT INTO users (email, role) VALUES ('student@gmail.com', 'Student');

-- (Assume the backend PHP retrieved the UUIDs. We'll use placeholders here)
-- Insert a Course with JSONB data
INSERT INTO courses (instructor_id, title, price, is_published, quiz_metadata)
VALUES (
    (SELECT user_id FROM users WHERE role = 'Instructor'), 
    'Advanced PostgreSQL Masterclass', 
    99.99, 
    TRUE, 
    '{"questions": 50, "difficulty": "Hard", "passing_score": 80}'
);

-- Enroll the student
INSERT INTO enrollments (student_id, course_id, amount_paid)
VALUES (
    (SELECT user_id FROM users WHERE role = 'Student'),
    1,
    99.99
);

-- 1. Index the Foreign Keys to speed up JOINs!
CREATE INDEX idx_courses_instructor ON courses(instructor_id);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

-- 2. Create a GIN Index for the JSONB data
CREATE INDEX idx_courses_metadata ON courses USING GIN (quiz_metadata);

-- Create a View for Accounting
CREATE VIEW daily_sales_report AS
SELECT 
    c.title AS course_name,
    COUNT(e.enrollment_id) AS total_enrollments,
    SUM(e.amount_paid) AS gross_revenue
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.title;

-- Now Accounting just runs:
SELECT * FROM daily_sales_report;

-- Create the Log Table
CREATE TABLE price_changes (
    log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_id INT NOT NULL,
    old_price NUMERIC,
    new_price NUMERIC,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create the Trigger Function
CREATE OR REPLACE FUNCTION audit_course_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_changes (course_id, old_price, new_price)
        VALUES (OLD.course_id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Bind the Trigger to the Table
CREATE TRIGGER trigger_audit_price
AFTER UPDATE ON courses
FOR EACH ROW
EXECUTE FUNCTION audit_course_price();

WITH HardestCourse AS (
    SELECT instructor_id
    FROM courses
    ORDER BY CAST(quiz_metadata->>'passing_score' AS INTEGER) DESC
    LIMIT 1
)
SELECT u.email 
FROM users u
JOIN HardestCourse h ON u.user_id = h.instructor_id;