1  -- Enable UUID extension
 2  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 3  
 4  -- Step 1: Create the enum type for student_type
 5  DO $$ BEGIN
 6      IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'student_type_enum') THEN
 7          CREATE TYPE student_type_enum AS ENUM ('A', 'B');
 8      END IF;
 9  END $$;
10  
11  -- Students Table
12  CREATE TABLE students (
13      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
14      email VARCHAR(255) NOT NULL UNIQUE,
15      full_name VARCHAR(100) NOT NULL,
16      username VARCHAR(50) NOT NULL UNIQUE,
17      password_hash VARCHAR(255) NOT NULL,
18      phone_number VARCHAR(20),
19      is_active BOOLEAN NOT NULL DEFAULT TRUE,
20      email_verified BOOLEAN NOT NULL DEFAULT FALSE,
21      metadata JSONB DEFAULT '{}'::JSONB,
22      student_type student_type_enum NOT NULL DEFAULT 'A',  -- New enum column
23  
24      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
25      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
26      deleted_at TIMESTAMP WITH TIME ZONE
27  );
28  
29  -- Function to update updated_at
30  CREATE OR REPLACE FUNCTION set_updated_at_column()
31  RETURNS TRIGGER AS $$
32  BEGIN
33      NEW.updated_at = NOW();
34      RETURN NEW;
35  END;
36  $$ LANGUAGE plpgsql;
37  
38  -- Trigger for students
39  CREATE TRIGGER trigger_set_updated_at_students
40  BEFORE UPDATE ON students
41  FOR EACH ROW
42  EXECUTE PROCEDURE set_updated_at_column();
43  
44  -- Courses Table
45  CREATE TABLE courses (
46      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
47      instructor_id UUID not null,
48      title VARCHAR(255) NOT NULL,
49      description TEXT NOT NULL,
50      tags TEXT[] DEFAULT '{}',
51      is_published BOOLEAN NOT NULL DEFAULT FALSE,
52      metadata JSONB DEFAULT '{}'::JSONB,
53  
54      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
55      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
56      deleted_at TIMESTAMP WITH TIME ZONE
57  );
58  
59  -- Trigger for courses
60  CREATE TRIGGER trigger_set_updated_at_courses
61  BEFORE UPDATE ON courses
62  FOR EACH ROW
63  EXECUTE PROCEDURE set_updated_at_column();
64  
65  -- Insert sample students with explicit student_type if desired
66  INSERT INTO students (email, full_name, username, password_hash, phone_number, metadata, student_type)
67  VALUES 
68  ('john.doe@example.com', 'John Doe', 'johnnyD', 'hashed_password_1', '+1234567890', '{"enrolled_year": 2022}', 'A'),
69  ('jane.smith@example.com', 'Jane Smith', 'janeS', 'hashed_password_2', '+1987654321', '{"major": "Computer Science"}', 'B'),
70  ('alex.lee@example.com', 'Alex Lee', 'alexL', 'hashed_password_3', NULL, '{"scholarship": true}', 'A');
71  
72  -- Insert sample courses using student IDs as instructors
73  INSERT INTO courses (instructor_id, title, description, tags, is_published, metadata)
74  VALUES 
75  ((SELECT id FROM students WHERE username = 'johnnyD'), 'Intro to Databases', 'Learn the basics of relational databases.', ARRAY['databases', 'SQL'], TRUE, '{"level": "beginner"}'),
76  ((SELECT id FROM students WHERE username = 'janeS'), 'Web Development 101', 'Introduction to web development using HTML, CSS, and JS.', ARRAY['web', 'frontend'], FALSE, '{"duration": "6 weeks"}'),
77  ((SELECT id FROM students WHERE username = 'alexL'), 'Python for Data Analysis', 'Explore data manipulation and analysis using Python.', ARRAY['python', 'data'], TRUE, '{"tools": ["pandas", "numpy"]}');