database_creation.sql

-- Create identities table
CREATE TABLE identities (
    id VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    number VARCHAR(255) NOT NULL,
    issued_by VARCHAR(255) NOT NULL,
    issued_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    has_chip BOOLEAN,
    country VARCHAR(255),
    notes VARCHAR(255),
    CONSTRAINT pk_identities PRIMARY KEY (id)
);

CREATE TABLE addresses (
    id VARCHAR(255) NOT NULL,
    street VARCHAR(255),
    ward VARCHAR(255),
    district VARCHAR(255),
    province VARCHAR(255),
    country VARCHAR(255),
    CONSTRAINT pk_addresses PRIMARY KEY (id)
);

CREATE TABLE settings (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    details TEXT,
    CONSTRAINT uc_settings_name UNIQUE (name)
);

-- Create faculties table
CREATE TABLE faculties (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL UNIQUE,
    deleted_at DATE
);

-- Create programs table
CREATE TABLE programs (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL UNIQUE,
    deleted_at DATE
);

-- Create statuses table
CREATE TABLE statuses (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL UNIQUE,
    deleted_at DATE
);

-- Create students table
CREATE TABLE students (
    id VARCHAR(255) NOT NULL,
    student_id VARCHAR(255),
    name VARCHAR(255) NOT NULL,
    dob DATE,
    gender VARCHAR(255) NOT NULL,
    faculty_id INTEGER NOT NULL,
    school_year INTEGER,
    program_id INTEGER NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(12) NOT NULL,
    status_id INTEGER NOT NULL,
    permanent_address_id VARCHAR(255),
    temporary_address_id VARCHAR(255),
    mailing_address_id VARCHAR(255),
    identity_id VARCHAR(255),
    CONSTRAINT pk_students PRIMARY KEY (id),
    deleted_at DATE
);

CREATE TABLE status_transitions (
    from_status_id INTEGER NOT NULL,
    to_status_id INTEGER NOT NULL,
    CONSTRAINT pk_status_transitions PRIMARY KEY (from_status_id, to_status_id),
    CONSTRAINT fk_status_transitions_from_status FOREIGN KEY (from_status_id) REFERENCES statuses (id),
    CONSTRAINT fk_status_transitions_to_status FOREIGN KEY (to_status_id) REFERENCES statuses (id)
);

CREATE TABLE subjects (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    credits INTEGER NOT NULL,
    faculty_id INTEGER,
    deleted_at DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_subjects_faculty FOREIGN KEY (faculty_id) REFERENCES faculties (id)
);

CREATE TABLE subject_prerequisites (
    subject_id INTEGER NOT NULL,
    prerequisite_id INTEGER NOT NULL,
    CONSTRAINT pk_subject_prerequisites PRIMARY KEY (subject_id, prerequisite_id),
    CONSTRAINT fk_subject_prerequisites_subject FOREIGN KEY (subject_id) REFERENCES subjects (id),
    CONSTRAINT fk_subject_prerequisites_prerequisite FOREIGN KEY (prerequisite_id) REFERENCES subjects (id)
);

-- Create courses table
CREATE TABLE courses (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    code VARCHAR(50) NOT NULL,
    year INTEGER NOT NULL,
    semester INTEGER NOT NULL,
    lecturer VARCHAR(255) NOT NULL,
    max_student INTEGER NOT NULL,
    room VARCHAR(50) NOT NULL,
    schedule VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    program_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    CONSTRAINT fk_courses_program FOREIGN KEY (program_id) REFERENCES programs (id),
    CONSTRAINT fk_courses_subject FOREIGN KEY (subject_id) REFERENCES subjects (id),
    CONSTRAINT uc_courses_code UNIQUE (code, subject_id)
);

-- Create enrollments table
CREATE TABLE enrollments (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    student_id VARCHAR(255) NOT NULL,
    course_id INTEGER NOT NULL,
    score_id INTEGER,
    CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id) REFERENCES students (id),
    CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id) REFERENCES courses (id),
    CONSTRAINT uc_enrollments_student_course UNIQUE (student_id, course_id)
);

-- Create histories table to track enrollment actions
CREATE TABLE histories (
    id VARCHAR(255) PRIMARY KEY,
    action_type VARCHAR(50) NOT NULL, -- ENROLLED or DELETED
    created_at TIMESTAMP NOT NULL,
    student_id VARCHAR(255) NOT NULL,
    course_id INTEGER NOT NULL,
    CONSTRAINT fk_histories_student FOREIGN KEY (student_id) REFERENCES students (id),
    CONSTRAINT fk_histories_course FOREIGN KEY (course_id) REFERENCES courses (id)
);

CREATE TABLE scores
(
    id    INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    grade VARCHAR(255),
    gpa   DOUBLE PRECISION,
    CONSTRAINT pk_scores PRIMARY KEY (id)
);

-- Add constraints to students table
ALTER TABLE students
    ADD CONSTRAINT uc_students_email UNIQUE (email);

ALTER TABLE students
    ADD CONSTRAINT uc_students_mailing_address UNIQUE (mailing_address_id);

ALTER TABLE students
    ADD CONSTRAINT uc_students_permanent_address UNIQUE (permanent_address_id);

ALTER TABLE students
    ADD CONSTRAINT uc_students_phone UNIQUE (phone);

ALTER TABLE students
    ADD CONSTRAINT uc_students_student UNIQUE (student_id);

ALTER TABLE students
    ADD CONSTRAINT uc_students_temporary_address UNIQUE (temporary_address_id);

-- Add foreign key constraints
ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_FACULTY FOREIGN KEY (faculty_id) REFERENCES faculties (id);

ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_PROGRAM FOREIGN KEY (program_id) REFERENCES programs (id);

ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_STATUS FOREIGN KEY (status_id) REFERENCES statuses (id);

ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_MAILING_ADDRESS FOREIGN KEY (mailing_address_id) REFERENCES addresses (id);

ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_PERMANENT_ADDRESS FOREIGN KEY (permanent_address_id) REFERENCES addresses (id);

ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_TEMPORARY_ADDRESS FOREIGN KEY (temporary_address_id) REFERENCES addresses (id);
    
ALTER TABLE students
    ADD CONSTRAINT FK_STUDENTS_ON_IDENTITY FOREIGN KEY (identity_id) REFERENCES identities (id);
    
ALTER TABLE students
    ADD CONSTRAINT uc_students_identity UNIQUE (identity_id);

-- Add foreign key constraints for enrollments
ALTER TABLE enrollments
    ADD CONSTRAINT uc_enrollments_score UNIQUE (score_id);

ALTER TABLE enrollments
    ADD CONSTRAINT FK_ENROLLMENTS_ON_COURSE FOREIGN KEY (course_id) REFERENCES courses (id);

ALTER TABLE enrollments
    ADD CONSTRAINT FK_ENROLLMENTS_ON_STUDENT FOREIGN KEY (student_id) REFERENCES students (id);

ALTER TABLE enrollments
    ADD CONSTRAINT FK_ENROLLMENTS_ON_score FOREIGN KEY (score_id) REFERENCES scores (id);

-- Add foreign key constraints for histories

ALTER TABLE histories
    ADD CONSTRAINT FK_HISTORIES_ON_COURSE FOREIGN KEY (course_id) REFERENCES courses (id);

ALTER TABLE histories
    ADD CONSTRAINT FK_HISTORIES_ON_STUDENT FOREIGN KEY (student_id) REFERENCES students (id);


-- Insert settings records
INSERT INTO settings (name, details) VALUES
('phonenumber', '["VN"]'),
('email', '@student.hcmus.edu.vn'),
('failinggrade', '2.0'),
('adjustmentduration', '10');

-- Seed faculties data based on the existing student data (uppercase)
INSERT INTO faculties (name) VALUES
('Faculty of Business English'),
('Faculty of Law'),
('Faculty of Japanese'),
('Faculty of French');

-- Seed programs data based on the existing student data (uppercase)
INSERT INTO programs (name) VALUES
('Business Administration'),
('Criminal Justice'),
('Japanese Literature'),
('French Studies'),
('International Business'),
('Japanese Culture'),
('Corporate Law'),
('French Language'),
('Japanese Economics'),
('Business Communication');

-- Seed statuses data based on the existing student data (uppercase)
INSERT INTO statuses (name) VALUES
('Studying'),
('Graduated'),
('Suspended'),
('Dropped');

-- Insert sample identity data
INSERT INTO identities (id, type, number, issued_by, issued_date, expiry_date, has_chip, country, notes)
VALUES
-- Chip_Card (CCCD) examples - 12 digits
('ID001', 'Chip Card', '123456789012', 'Ministry of Public Security', '2018-05-10', '2028-05-10', true, 'Vietnam', 'Citizen Identity Card with Chip'),
('ID004', 'Chip Card', '234567891023', 'Ministry of Public Security', '2021-01-05', '2031-01-05', true, 'Vietnam', NULL),
('ID006', 'Chip Card', '345678912034', 'Ministry of Public Security', '2019-07-14', '2029-07-14', true, 'Vietnam', NULL),
('ID009', 'Chip Card', '456789123045', 'Ministry of Public Security', '2021-06-17', '2031-06-17', true, 'Vietnam', NULL),

-- Identity_Card (CMND) examples - 9 digits
('ID003', 'Identity Card', '456789123', 'Police Department', '2020-03-22', '2025-03-22', false, 'Vietnam', 'Old ID card'),
('ID005', 'Identity Card', '567891234', 'Police Department', '2017-11-30', '2027-11-30', false, 'Vietnam', NULL),
('ID007', 'Identity Card', '678912345', 'Police Department', '2022-09-08', '2027-09-08', false, 'Vietnam', NULL),

-- Passport examples - 2 uppercase letters followed by 7 digits
('ID002', 'Passport', 'AB1234567', 'Immigration Department', '2019-08-15', '2029-08-15', false, 'Vietnam', 'International travel document'),
('ID008', 'Passport', 'CD2345678', 'Immigration Department', '2020-04-25', '2030-04-25', false, 'Vietnam', NULL),
('ID010', 'Passport', 'EF3456789', 'Immigration Department', '2018-12-03', '2028-12-03', false, 'Vietnam', 'Diplomatic passport');

-- Insert addresses with Ward and Province data
INSERT INTO addresses (id, street, ward, district, province, country)
VALUES
-- Permanent addresses
('PERM001', '123 Main Street', 'Thanh Cong', 'Ba Dinh', 'Hanoi', 'Vietnam'),
('PERM002', '456 Oak Avenue', 'Ben Nghe', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('PERM003', '789 Pine Road', 'Hai Chau', 'Hai Chau', 'Da Nang', 'Vietnam'),
('PERM004', '321 Maple Lane', 'Thac Gian', 'Thanh Khe', 'Da Nang', 'Vietnam'),
('PERM005', '654 Elm Street', 'Phuoc My', 'Son Tra', 'Da Nang', 'Vietnam'),
('PERM006', '987 Cedar Avenue', 'Tan Dinh', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('PERM007', '246 Birch Boulevard', 'Truc Bach', 'Ba Dinh', 'Hanoi', 'Vietnam'),
('PERM008', '135 Spruce Drive', 'Pham Ngu Lao', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('PERM009', '864 Willow Way', 'Cau Giay', 'Cau Giay', 'Hanoi', 'Vietnam'),
('PERM010', '753 Aspen Place', 'Vinh Trung', 'Thanh Khe', 'Da Nang', 'Vietnam'),

-- Temporary addresses
('TEMP001', '123 Main Street', 'Thanh Cong', 'Ba Dinh', 'Hanoi', 'Vietnam'),
('TEMP002', '456 Oak Avenue', 'Ben Nghe', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('TEMP003', '789 Pine Road', 'An Hai Bac', 'Son Tra', 'Da Nang', 'Vietnam'),
('TEMP004', '321 Maple Lane', 'Thao Dien', 'District 2', 'Ho Chi Minh City', 'Vietnam'),
('TEMP005', '654 Elm Street', 'Lang Ha', 'Dong Da', 'Hanoi', 'Vietnam'),
('TEMP006', '987 Cedar Avenue', 'Tan Dinh', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('TEMP007', '246 Birch Boulevard', 'Nguyen Thai Binh', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('TEMP008', '135 Spruce Drive', 'Pham Ngu Lao', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('TEMP009', '864 Willow Way', 'Linh Trung', 'Thu Duc', 'Ho Chi Minh City', 'Vietnam'),
('TEMP010', '753 Aspen Place', 'My An', 'Ngu Hanh Son', 'Da Nang', 'Vietnam'),

-- Mailing addresses
('MAIL001', '123 Main Street', 'Thanh Cong', 'Ba Dinh', 'Hanoi', 'Vietnam'),
('MAIL002', '456 Oak Avenue', 'Ben Nghe', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('MAIL003', '789 Pine Road', 'Hai Chau', 'Hai Chau', 'Da Nang', 'Vietnam'),
('MAIL004', '321 Maple Lane', 'Thao Dien', 'District 2', 'Ho Chi Minh City', 'Vietnam'),
('MAIL005', '654 Elm Street', 'Lang Ha', 'Dong Da', 'Hanoi', 'Vietnam'),
('MAIL006', '987 Cedar Avenue', 'Tan Dinh', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('MAIL007', '246 Birch Boulevard', 'Truc Bach', 'Ba Dinh', 'Hanoi', 'Vietnam'),
('MAIL008', '135 Spruce Drive', 'Pham Ngu Lao', 'District 1', 'Ho Chi Minh City', 'Vietnam'),
('MAIL009', '864 Willow Way', 'Cau Giay', 'Cau Giay', 'Hanoi', 'Vietnam'),
('MAIL010', '753 Aspen Place', 'My An', 'Ngu Hanh Son', 'Da Nang', 'Vietnam');


-- From 'Studying' (1) status
INSERT INTO status_transitions (from_status_id, to_status_id) VALUES
(1, 2), -- Studying -> Graduated
(1, 3), -- Studying -> Suspended
(1, 4); -- Studying -> Dropped

-- From 'Suspended' (3) status
INSERT INTO status_transitions (from_status_id, to_status_id) VALUES
(3, 1), -- Suspended -> Studying (reinstatement)
(3, 4); -- Suspended -> Dropped

INSERT INTO students (
    id,
    student_id, 
    name, 
    dob, 
    gender, 
    faculty_id, 
    school_year, 
    program_id, 
    email, 
    phone, 
    status_id,
    permanent_address_id,
    temporary_address_id,
    mailing_address_id,
    identity_id
) VALUES 
-- Student 1
(
    'ST001_ID',
    'ST001',
    'John Smith',
    '2000-05-15',
    'Male',
    1, -- Faculty of Business English
    3,
    1, -- Business Administration
    'john.smith@student.hcmus.edu.vn',
    '+84903456789',
    1, -- Studying
    'PERM001',
    'TEMP001',
    'MAIL001',
    'ID001'
),
-- Student 2
(
    'ST002_ID',
    'ST002',
    'Emily Johnson',
    '2001-08-22',
    'Female',
    2, -- Faculty of Law
    2,
    2, -- Criminal Justice
    'emily.johnson@student.hcmus.edu.vn',
    '+84987654321',
    1, -- studying
    'PERM002',
    'TEMP002',
    'MAIL002',
    'ID002'
),
-- Student 3
(
    'ST003_ID',
    'ST003',
    'Michael Brown',
    '1999-03-10',
    'Male',
    3, -- Faculty of Japanese
    4,
    3, -- Japanese Literature
    'michael.brown@student.hcmus.edu.vn',
    '+84367891234',
    2, -- Graduated
    'PERM003',
    'TEMP003',
    'MAIL003',
    'ID003'
),
-- Student 4
(
    'ST004_ID',
    'ST004',
    'Sarah Davis',
    '2002-01-30',
    'Female',
    4, -- Faculty of French
    1,
    4, -- French Studies
    'sarah.davis@student.hcmus.edu.vn',
    '+84345678912',
    1, -- studying
    'PERM004',
    'TEMP004',
    'MAIL004',
    'ID004'
),
-- Student 5
(
    'ST005_ID',
    'ST005',
    'David Wilson',
    '2000-11-05',
    'Male',
    1, -- Faculty of Business English
    3,
    5, -- International Business
    'david.wilson@student.hcmus.edu.vn',
    '+84778912345',
    3, -- Suspended
    'PERM005',
    'TEMP005',
    'MAIL005',
    'ID005'
),
-- Student 6
(
    'ST006_ID',
    'ST006',
    'Jennifer Taylor',
    '2001-07-19',
    'Female',
    3, -- Faculty of Japanese
    2,
    6, -- Japanese Culture
    'jennifer.taylor@student.hcmus.edu.vn',
    '+84934567891',
    1, -- studying
    'PERM006',
    'TEMP006',
    'MAIL006',
    'ID006'
),
-- Student 7
(
    'ST007_ID',
    'ST007',
    'James Anderson',
    '1999-09-25',
    'Male',
    2, -- Faculty of Law
    4,
    7, -- Corporate Law
    'james.anderson@student.hcmus.edu.vn',
    '+84791234567',
    1, -- studying
    'PERM007',
    'TEMP007',
    'MAIL007',
    'ID007'
),
-- Student 8
(
    'ST008_ID',
    'ST008',
    'Linda Martinez',
    '2002-04-12',
    'Female',
    4, -- Faculty of French
    1,
    8, -- French Language
    'linda.martinez@student.hcmus.edu.vn',
    '+84796789123',
    4, -- Dropped
    'PERM008',
    'TEMP008',
    'MAIL008',
    'ID008'
),
-- Student 9
(
    'ST009_ID',
    'ST009',
    'Robert Thompson',
    '2000-06-28',
    'Male',
    3, -- Faculty of Japanese
    3,
    9, -- Japanese Economics
    'robert.thompson@student.hcmus.edu.vn',
    '+84789123456',
    1, -- studying
    'PERM009',
    'TEMP009',
    'MAIL009',
    'ID009'
),
-- Student 10
(
    'ST010_ID',
    'ST010',
    'Elizabeth Garcia',
    '2001-12-07',
    'Female',
    1, -- Faculty of Business English
    2,
    10, -- Business Communication
    'elizabeth.garcia@student.hcmus.edu.vn',
    '+84912345678',
    1, -- studying
    'PERM010',
    'TEMP010',
    'MAIL010',
    'ID010'
);

-- Insert sample subjects
INSERT INTO subjects (name, code, description, credits, faculty_id) VALUES
-- Computer Science subjects
('Introduction to Computer Science', 'CS101', 'A basic introduction to computer science principles', 3, 1),
('Data Structures and Algorithms', 'CS201', 'Study of data structures and fundamental algorithms', 4, 1),
('Database Management Systems', 'CS301', 'Design and implementation of database systems', 3, 1),
('Operating Systems', 'CS302', 'Principles of operating systems design and implementation', 3, 1),
('Software Engineering', 'CS401', 'Software development life cycle, project management, and best practices', 4, 1),
-- Mathematics subjects
('Calculus I', 'MATH101', 'Limits, derivatives, and basic integration', 3, 2),
('Calculus II', 'MATH102', 'Integration techniques, series, and parametric equations', 3, 2), 
('Linear Algebra', 'MATH201', 'Vector spaces, matrices, and linear transformations', 3, 2),
('Differential Equations', 'MATH301', 'Ordinary differential equations and applications', 3, 2),
('Advanced Mathematics', 'MATH401', 'Advanced topics in mathematics including calculus and linear algebra', 4, 2),
-- Physics subjects
('Introduction to Physics', 'PHYS101', 'Basic principles of physics and mechanics', 3, 3),
('Electricity and Magnetism', 'PHYS201', 'Principles of electricity, magnetism, and electromagnetic waves', 4, 3),
('Thermodynamics', 'PHYS301', 'Laws of thermodynamics and statistical mechanics', 3, 3),
-- Business subjects
('Introduction to Business', 'BUS101', 'Basic principles of business and entrepreneurship', 3, 4),
('Marketing Principles', 'BUS201', 'Core concepts of marketing and consumer behavior', 3, 4);

-- Add prerequisites relationships
-- CS201 requires CS101
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (2, 1);
-- CS301 requires CS101 and CS201
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (3, 1), (3, 2);
-- CS302 requires CS101
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (4, 1);
-- CS401 requires CS201, CS301, and CS302
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (5, 2), (5, 3), (5, 4);

-- MATH102 requires MATH101
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (7, 6);
-- MATH301 requires MATH102 and MATH201
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (9, 7), (9, 8);
-- MATH401 requires MATH301
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (10, 9);

-- PHYS201 requires PHYS101 and MATH101
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (12, 11), (12, 6);
-- PHYS301 requires PHYS201
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (13, 12);

-- BUS201 requires BUS101
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES (15, 14);

-- Add sample course data
INSERT INTO courses (code, year, semester, lecturer, max_student, room, schedule, start_date, program_id, subject_id) VALUES
-- Computer Science Courses
('CS101_01', 2025, 1, 'Dr. John Smith', 40, 'A101', 'T2(3-6)', '2025-04-15', 1, 1),
('CS101_02', 2025, 1, 'Dr. Sarah Johnson', 35, 'B202', 'T4(1-4)', '2025-04-15', 1, 1),
('CS201_01', 2025, 1, 'Dr. Emily Chen', 45, 'A105', 'T5(4-7)', '2025-04-17', 1, 2),
('CS301_01', 2025, 1, 'Prof. Robert Davis', 30, 'C303', 'T3(1-4)', '2025-04-16', 1, 3),
('CS302_01', 2025, 2, 'Dr. Michael Wilson', 25, 'A203', 'T6(3-6)', '2025-06-15', 1, 4),
-- Mathematics Courses
( 'MATH101_01', 2025, 1, 'Dr. Jennifer White', 50, 'D404', 'T2(8-11)', '2025-01-14', 2, 6),
( 'MATH201_01', 2025, 1, 'Prof. David Brown', 40, 'E501', 'T4(6-9)', '2025-01-16', 2, 8),
( 'MATH301_01', 2025, 2, 'Prof. David Williams', 30, 'C303', 'T3(6-9)', '2025-06-10', 2, 9),
-- Physics Courses
( 'PHYS101_01', 2025, 1, 'Prof. Michael Brown', 50, 'D404', 'T6(1-4)', '2025-01-18', 3, 11),
( 'PHYS201_01', 2025, 2, 'Dr. Elizabeth Taylor', 35, 'B205', 'T5(8-11)', '2025-06-12', 3, 12),
-- Business Courses
( 'BUS101_01', 2025, 1, 'Prof. Catherine Jones', 60, 'F601', 'T3(3-6)', '2025-01-15', 4, 14),
( 'BUS201_01', 2025, 2, 'Dr. James Anderson', 45, 'E505', 'T2(1-4)', '2025-06-14', 4, 15);


-- Insert sample enrollment data (students enrolling in courses)
INSERT INTO enrollments (student_id, course_id) VALUES
-- Student 1 (John Smith) enrolled in 3 courses
('ST001_ID', 1), -- Intro to Computer Science
('ST001_ID', 6), -- Calculus I
('ST001_ID', 11), -- Intro to Business
-- Student 2 (Emily Johnson) enrolled in 2 courses
('ST002_ID', 2), -- Another CS101 section
('ST002_ID', 9), -- Physics
-- Student 3 (Michael Brown) - graduated student
('ST003_ID', 3), -- Data Structures
('ST003_ID', 7), -- Linear Algebra
-- Student 4 (Sarah Davis)
('ST004_ID', 4), -- Database Management
('ST004_ID', 10), -- Electricity and Magnetism
-- Student 6 (Jennifer Taylor)
('ST006_ID', 5), -- Operating Systems
('ST006_ID', 8), -- Differential Equations
-- Student 7 (James Anderson)
('ST007_ID', 12), -- Marketing Principles
-- Student 9 (Robert Thompson)
('ST009_ID', 1), -- Intro to Computer Science (same as Student 1)
('ST009_ID', 3); -- Data Structures (same as Student 3)

-- Insert enrollment enrollmentHistory records (these would typically be generated automatically)
INSERT INTO histories (id, action_type, created_at, student_id, course_id) VALUES
-- Enrollment actions
('hist_001', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST001_ID', 1),
('hist_002', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST001_ID', 6),
('hist_003', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST001_ID', 11),
('hist_004', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '8 days', 'ST002_ID', 2),
('hist_005', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '8 days', 'ST002_ID', 9),
('hist_006', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '15 days', 'ST003_ID', 3),
('hist_007', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '15 days', 'ST003_ID', 7),
('hist_008', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '7 days', 'ST004_ID', 4),
('hist_009', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '7 days', 'ST004_ID', 10),
('hist_010', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '6 days', 'ST006_ID', 5),
('hist_011', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '6 days', 'ST006_ID', 8),
('hist_012', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '5 days', 'ST007_ID', 12),
('hist_013', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '5 days', 'ST009_ID', 1),
('hist_014', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '5 days', 'ST009_ID', 3),
-- Some enrollment deletions/drops
('hist_015', 'DELETED', CURRENT_TIMESTAMP - INTERVAL '2 days', 'ST001_ID', 6),
('hist_016', 'DELETED', CURRENT_TIMESTAMP - INTERVAL '3 days', 'ST002_ID', 9),
('hist_017', 'DELETED', CURRENT_TIMESTAMP - INTERVAL '1 day', 'ST004_ID', 10),
-- Re-enrollments
('hist_018', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '1 day', 'ST001_ID', 6);

-- Now delete the enrollments that correspond to the DELETED actions
DELETE FROM enrollments WHERE student_id = 'ST001_ID' AND course_id = 6;
DELETE FROM enrollments WHERE student_id = 'ST002_ID' AND course_id = 9;
DELETE FROM enrollments WHERE student_id = 'ST004_ID' AND course_id = 10;

-- And add back the one re-enrollment
INSERT INTO enrollments (student_id, course_id) VALUES
('ST001_ID', 6);

-- Insert unique scores for each enrollment
-- Student 1 (John Smith)
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- CS101
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST001_ID' AND course_id = 1;
INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Calculus I (re-enrolled)
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST001_ID' AND course_id = 6;
INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Intro to Business
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST001_ID' AND course_id = 11;

-- Student 2 (Emily Johnson)
INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- CS101 (different section)
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST002_ID' AND course_id = 2;

-- Student 3 (Michael Brown) - graduated with good grades
INSERT INTO scores (grade, gpa) VALUES ('A+', 4.0); -- Data Structures
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST003_ID' AND course_id = 3;
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Linear Algebra
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST003_ID' AND course_id = 7;

-- Student 4 (Sarah Davis)
INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- Database Management
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST004_ID' AND course_id = 4;

-- Student 6 (Jennifer Taylor) - struggling in technical courses
INSERT INTO scores (grade, gpa) VALUES ('C-', 1.7); -- Operating Systems
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST006_ID' AND course_id = 5;
INSERT INTO scores (grade, gpa) VALUES ('D+', 1.3); -- Differential Equations
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST006_ID' AND course_id = 8;

-- Student 7 (James Anderson)
INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Marketing Principles
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST007_ID' AND course_id = 12;

-- Student 9 (Robert Thompson) - mixed performance
INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Intro to Computer Science
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST009_ID' AND course_id = 1;
INSERT INTO scores (grade, gpa) VALUES ('D', 1.0); -- Data Structures
UPDATE enrollments SET score_id = currval('scores_id_seq') 
    WHERE student_id = 'ST009_ID' AND course_id = 3;

-- Add failing grades for additional students
-- David Wilson (suspended)
INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- Electricity & Magnetism
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST005_ID', 10, currval('scores_id_seq'));

-- Add a enrollmentHistory record for this enrollment
INSERT INTO histories (id, action_type, created_at, student_id, course_id) VALUES
('hist_019', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '20 days', 'ST005_ID', 10);

-- Linda Martinez (dropped student)
INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- Intro to Business
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST008_ID', 11, currval('scores_id_seq'));

-- Add a enrollmentHistory record for this enrollment
INSERT INTO histories (id, action_type, created_at, student_id, course_id) VALUES
('hist_020', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '25 days', 'ST008_ID', 11);





-- ===============================
-- ADDITIONAL SUBJECTS
-- ===============================

-- More subjects for Faculty of Business English (id=1)
INSERT INTO subjects (name, code, description, credits, faculty_id) VALUES
('Business Ethics', 'BUS301', 'Ethical principles and moral issues in business decisions', 3, 1),
('International Marketing', 'MKT301', 'Marketing strategies for global markets and cross-cultural consumer behavior', 4, 1),
('Organizational Behavior', 'MGT201', 'Human behavior in organizational settings', 3, 1),
('Business Communication', 'COMM201', 'Professional communication skills for business environments', 3, 1),
('Strategic Management', 'MGT401', 'Long-term planning and strategy formulation for organizations', 4, 1);

-- More subjects for Faculty of Law (id=2)
INSERT INTO subjects (name, code, description, credits, faculty_id) VALUES
('Constitutional Law', 'LAW201', 'Fundamental principles of government structure and individual rights', 4, 2),
('Criminal Law', 'LAW301', 'Principles, theories and problems of criminal law and punishment', 4, 2),
('International Law', 'LAW401', 'Legal relationships between nations and international entities', 3, 2),
('Contract Law', 'LAW202', 'Formation and enforcement of agreements between private parties', 3, 2),
('Property Law', 'LAW302', 'Legal relationships between persons and property', 3, 2);

-- More subjects for Faculty of Japanese (id=3)
INSERT INTO subjects (name, code, description, credits, faculty_id) VALUES
('Japanese Grammar', 'JPN201', 'Advanced Japanese language structure and grammar patterns', 4, 3),
('Japanese History', 'JPN301', 'Historical development of Japan from ancient times to present', 3, 3),
('Japanese Society', 'JPN302', 'Contemporary social issues and cultural aspects of Japan', 3, 3),
('Business Japanese', 'JPN401', 'Japanese language skills for business and professional settings', 4, 3),
('Japanese Film Studies', 'JPN402', 'Analysis of Japanese cinema and its cultural context', 3, 3);

-- More subjects for Faculty of French (id=4)
INSERT INTO subjects (name, code, description, credits, faculty_id) VALUES
('French Grammar', 'FRN201', 'Advanced French language structure and grammar patterns', 4, 4),
('French Literature', 'FRN301', 'Major works and movements in French literature', 3, 4),
('French Culture', 'FRN302', 'Contemporary social issues and cultural aspects of France', 3, 4),
('Business French', 'FRN401', 'French language skills for business and professional settings', 4, 4),
('French Film Studies', 'FRN402', 'Analysis of French cinema and its cultural context', 3, 4);

-- Add additional prerequisites
-- Business Ethics requires Intro to Business
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'BUS301'), (SELECT id FROM subjects WHERE code = 'BUS101'));

-- International Marketing requires Marketing Principles
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'MKT301'), (SELECT id FROM subjects WHERE code = 'BUS201'));

-- Strategic Management requires Business Ethics and Organizational Behavior
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'MGT401'), (SELECT id FROM subjects WHERE code = 'BUS301')),
((SELECT id FROM subjects WHERE code = 'MGT401'), (SELECT id FROM subjects WHERE code = 'MGT201'));

-- Criminal Law requires Constitutional Law
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'LAW301'), (SELECT id FROM subjects WHERE code = 'LAW201'));

-- International Law requires Constitutional Law
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'LAW401'), (SELECT id FROM subjects WHERE code = 'LAW201'));

-- Business Japanese requires Japanese Grammar
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'JPN401'), (SELECT id FROM subjects WHERE code = 'JPN201'));

-- Japanese Film Studies requires Japanese Society
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'JPN402'), (SELECT id FROM subjects WHERE code = 'JPN302'));

-- Business French requires French Grammar
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'FRN401'), (SELECT id FROM subjects WHERE code = 'FRN201'));

-- French Film Studies requires French Culture
INSERT INTO subject_prerequisites (subject_id, prerequisite_id) VALUES 
((SELECT id FROM subjects WHERE code = 'FRN402'), (SELECT id FROM subjects WHERE code = 'FRN302'));

-- ===============================
-- ADDITIONAL COURSES
-- ===============================

-- More courses for existing subjects (additional sections)
INSERT INTO courses (code, year, semester, lecturer, max_student, room, schedule, start_date, program_id, subject_id) VALUES
-- Additional Computer Science course sections
('CS101_03', 2025, 2, 'Dr. Alice Cooper', 35, 'B204', 'T3(7-10)', '2025-09-10', 1, 1),
('CS201_02', 2025, 2, 'Prof. Mark Johnson', 40, 'A102', 'T4(7-10)', '2025-09-12', 1, 2),
-- Additional Mathematics course sections
('MATH101_02', 2025, 2, 'Dr. Thomas Lee', 45, 'C101', 'T5(1-4)', '2025-09-15', 2, 6),
('MATH201_02', 2025, 2, 'Prof. Susan Kim', 35, 'D303', 'T2(4-7)', '2025-09-11', 2, 8);

-- New courses for Business subjects
INSERT INTO courses (code, year, semester, lecturer, max_student, room, schedule, start_date, program_id, subject_id) VALUES
('BUS301_01', 2025, 1, 'Dr. Patricia Adams', 50, 'E201', 'T3(4-7)', '2025-04-16', 1, (SELECT id FROM subjects WHERE code = 'BUS301')),
('BUS301_02', 2025, 2, 'Prof. Richard Scott', 45, 'F302', 'T6(7-10)', '2025-09-14', 5, (SELECT id FROM subjects WHERE code = 'BUS301')),
('MKT301_01', 2025, 1, 'Dr. William Chen', 40, 'E202', 'T4(4-7)', '2025-04-17', 5, (SELECT id FROM subjects WHERE code = 'MKT301')),
('MGT201_01', 2025, 1, 'Prof. Lisa Wang', 55, 'F101', 'T5(1-4)', '2025-04-18', 10, (SELECT id FROM subjects WHERE code = 'MGT201')),
('COMM201_01', 2025, 2, 'Dr. Kevin Rodriguez', 50, 'E101', 'T2(7-10)', '2025-09-16', 10, (SELECT id FROM subjects WHERE code = 'COMM201')),
('MGT401_01', 2025, 2, 'Prof. Emma Wilson', 35, 'F303', 'T3(1-4)', '2025-09-17', 1, (SELECT id FROM subjects WHERE code = 'MGT401'));

-- New courses for Law subjects
INSERT INTO courses (code, year, semester, lecturer, max_student, room, schedule, start_date, program_id, subject_id) VALUES
('LAW201_01', 2025, 1, 'Prof. George Martinez', 60, 'G201', 'T2(4-7)', '2025-04-15', 2, (SELECT id FROM subjects WHERE code = 'LAW201')),
('LAW301_01', 2025, 1, 'Dr. Victoria Taylor', 50, 'G301', 'T4(1-4)', '2025-04-17', 2, (SELECT id FROM subjects WHERE code = 'LAW301')),
('LAW401_01', 2025, 2, 'Prof. Charles Wright', 40, 'G401', 'T6(4-7)', '2025-09-13', 7, (SELECT id FROM subjects WHERE code = 'LAW401')),
('LAW202_01', 2025, 2, 'Dr. Olivia Davis', 55, 'G202', 'T5(7-10)', '2025-09-18', 7, (SELECT id FROM subjects WHERE code = 'LAW202')),
('LAW302_01', 2025, 2, 'Prof. James Lee', 45, 'G302', 'T3(4-7)', '2025-09-19', 2, (SELECT id FROM subjects WHERE code = 'LAW302'));

-- New courses for Japanese subjects
INSERT INTO courses (code, year, semester, lecturer, max_student, room, schedule, start_date, program_id, subject_id) VALUES
('JPN201_01', 2025, 1, 'Prof. Hiroshi Tanaka', 40, 'H201', 'T3(1-4)', '2025-04-16', 3, (SELECT id FROM subjects WHERE code = 'JPN201')),
('JPN301_01', 2025, 1, 'Dr. Akiko Sato', 35, 'H301', 'T5(4-7)', '2025-04-18', 3, (SELECT id FROM subjects WHERE code = 'JPN301')),
('JPN302_01', 2025, 2, 'Prof. Takashi Yamamoto', 45, 'H302', 'T2(1-4)', '2025-09-15', 6, (SELECT id FROM subjects WHERE code = 'JPN302')),
('JPN401_01', 2025, 2, 'Dr. Yuki Nakamura', 30, 'H401', 'T4(4-7)', '2025-09-17', 9, (SELECT id FROM subjects WHERE code = 'JPN401')),
('JPN402_01', 2025, 2, 'Prof. Keiko Suzuki', 25, 'H402', 'T6(1-4)', '2025-09-19', 3, (SELECT id FROM subjects WHERE code = 'JPN402'));

-- New courses for French subjects
INSERT INTO courses (code, year, semester, lecturer, max_student, room, schedule, start_date, program_id, subject_id) VALUES
('FRN201_01', 2025, 1, 'Prof. Pierre Dubois', 40, 'J201', 'T4(4-7)', '2025-04-17', 4, (SELECT id FROM subjects WHERE code = 'FRN201')),
('FRN301_01', 2025, 1, 'Dr. Sophie Martin', 35, 'J301', 'T6(7-10)', '2025-04-19', 4, (SELECT id FROM subjects WHERE code = 'FRN301')),
('FRN302_01', 2025, 2, 'Prof. Jean Lefebvre', 45, 'J302', 'T3(7-10)', '2025-09-16', 8, (SELECT id FROM subjects WHERE code = 'FRN302')),
('FRN401_01', 2025, 2, 'Dr. Marie Roux', 30, 'J401', 'T5(4-7)', '2025-09-18', 4, (SELECT id FROM subjects WHERE code = 'FRN401')),
('FRN402_01', 2025, 2, 'Prof. Claude Moreau', 25, 'J402', 'T2(7-10)', '2025-09-20', 8, (SELECT id FROM subjects WHERE code = 'FRN402'));

-- ===============================
-- ADDITIONAL ENROLLMENTS AND SCORES
-- ===============================

-- Student 1 (John Smith - Business English) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Business Ethics
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'BUS301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Organizational Behavior
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'MGT201_01'), currval('scores_id_seq'));

-- Student 2 (Emily Johnson - Law) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Constitutional Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'LAW201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Criminal Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'LAW301_01'), currval('scores_id_seq'));

-- Student 3 (Michael Brown - Japanese) - additional enrollments (graduated)
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Japanese Grammar
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'JPN201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Japanese History
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'JPN301_01'), currval('scores_id_seq'));

-- Student 4 (Sarah Davis - French) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- French Grammar
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'FRN201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- French Literature
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'FRN301_01'), currval('scores_id_seq'));

-- Student 5 (David Wilson - Business English, Suspended) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('D', 1.0); -- International Marketing
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST005_ID', (SELECT id FROM courses WHERE code = 'MKT301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- Business Ethics
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST005_ID', (SELECT id FROM courses WHERE code = 'BUS301_01'), currval('scores_id_seq'));

-- Student 6 (Jennifer Taylor - Japanese) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('C', 2.0); -- Japanese Grammar
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'JPN201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Japanese Society
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'JPN302_01'), currval('scores_id_seq'));

-- Student 7 (James Anderson - Law) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- Constitutional Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'LAW201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B-', 2.7); -- Property Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'LAW302_01'), currval('scores_id_seq'));

-- Student 8 (Linda Martinez - French, Dropped) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- French Grammar
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST008_ID', (SELECT id FROM courses WHERE code = 'FRN201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('D-', 0.7); -- French Culture
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST008_ID', (SELECT id FROM courses WHERE code = 'FRN302_01'), currval('scores_id_seq'));

-- Student 9 (Robert Thompson - Japanese) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('C', 2.0); -- Japanese History
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'JPN301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B-', 2.7); -- Japanese Society
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'JPN302_01'), currval('scores_id_seq'));

-- Student 10 (Elizabeth Garcia - Business English) - additional enrollments
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Business Ethics
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'BUS301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- International Marketing
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'MKT301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Organizational Behavior
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'MGT201_01'), currval('scores_id_seq'));

-- ===============================
-- HISTORY RECORDS FOR NEW ENROLLMENTS
-- ===============================

-- Add enrollmentHistory records for all new enrollments
INSERT INTO histories (id, action_type, created_at, student_id, course_id) VALUES
-- Student 1 new enrollments enrollmentHistory
('hist_021', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '12 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'BUS301_01')),
('hist_022', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '12 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'MGT201_01')),

-- Student 2 new enrollments enrollmentHistory
('hist_023', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '11 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'LAW201_01')),
('hist_024', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '11 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'LAW301_01')),

-- Student 3 new enrollments enrollmentHistory
('hist_025', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '14 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'JPN201_01')),
('hist_026', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '14 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'JPN301_01')),

-- Student 4 new enrollments enrollmentHistory
('hist_027', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'FRN201_01')),
('hist_028', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'FRN301_01')),

-- Student 5 new enrollments enrollmentHistory
('hist_029', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '18 days', 'ST005_ID', (SELECT id FROM courses WHERE code = 'MKT301_01')),
('hist_030', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '18 days', 'ST005_ID', (SELECT id FROM courses WHERE code = 'BUS301_01')),

-- Student 6 new enrollments enrollmentHistory
('hist_031', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'JPN201_01')),
('hist_032', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'JPN302_01')),

-- Student 7 new enrollments enrollmentHistory
('hist_033', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'LAW201_01')),
('hist_034', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'LAW302_01')),

-- Student 8 new enrollments enrollmentHistory
('hist_035', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '22 days', 'ST008_ID', (SELECT id FROM courses WHERE code = 'FRN201_01')),
('hist_036', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '22 days', 'ST008_ID', (SELECT id FROM courses WHERE code = 'FRN302_01')),

-- Student 9 new enrollments enrollmentHistory
('hist_037', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '8 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'JPN301_01')),
('hist_038', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '8 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'JPN302_01')),

-- Student 10 new enrollments enrollmentHistory
('hist_039', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '7 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'BUS301_01')),
('hist_040', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '7 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'MKT301_01')),
('hist_041', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '7 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'MGT201_01'));

-- ===============================
-- ADDITIONAL ENROLLMENTS AND SCORES FOR EXISTING STUDENTS
-- ===============================

-- More enrollments for second semester courses
-- Student 1 (John Smith - Business English)
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Strategic Management
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'MGT401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Business Communication
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'COMM201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- CS201 (second section)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'CS201_02'), currval('scores_id_seq'));

-- Student 2 (Emily Johnson - Law)
INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- International Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'LAW401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- Contract Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'LAW202_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Property Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'LAW302_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Business Ethics
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'BUS301_02'), currval('scores_id_seq'));

-- Student 3 (Michael Brown - Japanese, graduated)
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Business Japanese
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'JPN401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Japanese Film Studies
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'JPN402_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Japanese Society
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'JPN302_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Organizational Behavior
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'MGT201_01'), currval('scores_id_seq'));

-- Student 4 (Sarah Davis - French)
INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- French Culture
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'FRN302_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Business French
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'FRN401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- French Film Studies
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'FRN402_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Calculus I (additional section)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'MATH101_02'), currval('scores_id_seq'));

-- Student 5 (David Wilson - Business English, Suspended)
INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- Business Communication
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST005_ID', (SELECT id FROM courses WHERE code = 'COMM201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- Strategic Management
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST005_ID', (SELECT id FROM courses WHERE code = 'MGT401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('D-', 0.7); -- CS101 (third section)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST005_ID', (SELECT id FROM courses WHERE code = 'CS101_03'), currval('scores_id_seq'));

-- Student 6 (Jennifer Taylor - Japanese)
INSERT INTO scores (grade, gpa) VALUES ('D+', 1.3); -- Business Japanese
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'JPN401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('C', 2.0); -- Japanese Film Studies
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'JPN402_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Japanese History
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'JPN301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('D', 1.0); -- Intro to Business
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'BUS101_01'), currval('scores_id_seq'));

-- Student 7 (James Anderson - Law)
INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- Criminal Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'LAW301_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B-', 2.7); -- International Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'LAW401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Contract Law
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'LAW202_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Intro to Computer Science (section 3)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'CS101_03'), currval('scores_id_seq'));

-- Student 8 (Linda Martinez - French, Dropped)
INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- French Film Studies
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST008_ID', (SELECT id FROM courses WHERE code = 'FRN402_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('F', 0.0); -- Business French
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST008_ID', (SELECT id FROM courses WHERE code = 'FRN401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('D-', 0.7); -- Intro to Computer Science (section 3)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST008_ID', (SELECT id FROM courses WHERE code = 'CS101_03'), currval('scores_id_seq'));

-- Student 9 (Robert Thompson - Japanese)
INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Business Japanese
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'JPN401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B-', 2.7); -- Japanese Film Studies
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'JPN402_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('C', 2.0); -- Linear Algebra (section 2)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'MATH201_02'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B-', 2.7); -- Intro to Business
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'BUS101_01'), currval('scores_id_seq'));

-- Student 10 (Elizabeth Garcia - Business English)
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Business Communication
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'COMM201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Strategic Management
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'MGT401_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Intro to Computer Science (section 3)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'CS101_03'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Linear Algebra (section 2)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'MATH201_02'), currval('scores_id_seq'));

-- ===============================
-- EVEN MORE ENROLLMENTS (THIRD ROUND)
-- ===============================

-- Student 1 (John Smith - Business English)
INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Marketing Principles
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'BUS201_01'), currval('scores_id_seq'));

INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Linear Algebra (section 2)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST001_ID', (SELECT id FROM courses WHERE code = 'MATH201_02'), currval('scores_id_seq'));

-- Student 2 (Emily Johnson - Law)
INSERT INTO scores (grade, gpa) VALUES ('A-', 3.7); -- Marketing Principles
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST002_ID', (SELECT id FROM courses WHERE code = 'BUS201_01'), currval('scores_id_seq'));

-- Student 3 (Michael Brown - Japanese, graduated)
INSERT INTO scores (grade, gpa) VALUES ('A', 4.0); -- Intro to Computer Science (section 3)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST003_ID', (SELECT id FROM courses WHERE code = 'CS101_03'), currval('scores_id_seq'));

-- Student 4 (Sarah Davis - French)
INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- Intro to Computer Science (section 3)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST004_ID', (SELECT id FROM courses WHERE code = 'CS101_03'), currval('scores_id_seq'));

-- Student 6 (Jennifer Taylor - Japanese)
INSERT INTO scores (grade, gpa) VALUES ('C-', 1.7); -- Linear Algebra (section 2)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST006_ID', (SELECT id FROM courses WHERE code = 'MATH201_02'), currval('scores_id_seq'));

-- Student 7 (James Anderson - Law)
INSERT INTO scores (grade, gpa) VALUES ('B', 3.0); -- Business Ethics (section 2)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST007_ID', (SELECT id FROM courses WHERE code = 'BUS301_02'), currval('scores_id_seq'));

-- Student 9 (Robert Thompson - Japanese)
INSERT INTO scores (grade, gpa) VALUES ('C+', 2.3); -- Marketing Principles
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST009_ID', (SELECT id FROM courses WHERE code = 'BUS201_01'), currval('scores_id_seq'));

-- Student 10 (Elizabeth Garcia - Business English)
INSERT INTO scores (grade, gpa) VALUES ('B+', 3.3); -- Data Structures (section 2)
INSERT INTO enrollments (student_id, course_id, score_id) VALUES
('ST010_ID', (SELECT id FROM courses WHERE code = 'CS201_02'), currval('scores_id_seq'));

-- ===============================
-- HISTORY RECORDS FOR ALL NEW ENROLLMENTS
-- ===============================

-- Add enrollmentHistory records for the second batch of enrollments
INSERT INTO histories (id, action_type, created_at, student_id, course_id) VALUES
-- Student 1 new enrollments enrollmentHistory
('hist_042', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '15 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'MGT401_01')),
('hist_043', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '15 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'COMM201_01')),
('hist_044', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '15 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'CS201_02')),

-- Student 2 new enrollments enrollmentHistory
('hist_045', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '14 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'LAW401_01')),
('hist_046', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '14 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'LAW202_01')),
('hist_047', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '14 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'LAW302_01')),
('hist_048', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '14 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'BUS301_02')),

-- Student 3 new enrollments enrollmentHistory
('hist_049', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '17 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'JPN401_01')),
('hist_050', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '17 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'JPN402_01')),
('hist_051', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '17 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'JPN302_01')),
('hist_052', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '17 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'MGT201_01')),

-- Student 4 new enrollments enrollmentHistory
('hist_053', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '13 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'FRN302_01')),
('hist_054', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '13 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'FRN401_01')),
('hist_055', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '13 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'FRN402_01')),
('hist_056', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '13 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'MATH101_02')),

-- Student 5 new enrollments enrollmentHistory
('hist_057', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '19 days', 'ST005_ID', (SELECT id FROM courses WHERE code = 'COMM201_01')),
('hist_058', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '19 days', 'ST005_ID', (SELECT id FROM courses WHERE code = 'MGT401_01')),
('hist_059', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '19 days', 'ST005_ID', (SELECT id FROM courses WHERE code = 'CS101_03')),

-- Student 6 new enrollments enrollmentHistory
('hist_060', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '12 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'JPN401_01')),
('hist_061', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '12 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'JPN402_01')),
('hist_062', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '12 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'JPN301_01')),
('hist_063', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '12 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'BUS101_01')),

-- Student 7 new enrollments enrollmentHistory
('hist_064', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '11 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'LAW301_01')),
('hist_065', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '11 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'LAW401_01')),
('hist_066', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '11 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'LAW202_01')),
('hist_067', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '11 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'CS101_03')),

-- Student 8 new enrollments enrollmentHistory
('hist_068', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '21 days', 'ST008_ID', (SELECT id FROM courses WHERE code = 'FRN402_01')),
('hist_069', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '21 days', 'ST008_ID', (SELECT id FROM courses WHERE code = 'FRN401_01')),
('hist_070', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '21 days', 'ST008_ID', (SELECT id FROM courses WHERE code = 'CS101_03')),

-- Student 9 new enrollments enrollmentHistory
('hist_071', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'JPN401_01')),
('hist_072', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'JPN402_01')),
('hist_073', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'MATH201_02')),
('hist_074', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '10 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'BUS101_01')),

-- Student 10 new enrollments enrollmentHistory
('hist_075', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'COMM201_01')),
('hist_076', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'MGT401_01')),
('hist_077', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'CS101_03')),
('hist_078', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '9 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'MATH201_02'));

-- Add enrollmentHistory records for the third batch of enrollments
INSERT INTO histories (id, action_type, created_at, student_id, course_id) VALUES
('hist_079', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '8 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'BUS201_01')),
('hist_080', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '8 days', 'ST001_ID', (SELECT id FROM courses WHERE code = 'MATH201_02')),
('hist_081', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '7 days', 'ST002_ID', (SELECT id FROM courses WHERE code = 'BUS201_01')),
('hist_082', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '6 days', 'ST003_ID', (SELECT id FROM courses WHERE code = 'CS101_03')),
('hist_083', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '6 days', 'ST004_ID', (SELECT id FROM courses WHERE code = 'CS101_03')),
('hist_084', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '5 days', 'ST006_ID', (SELECT id FROM courses WHERE code = 'MATH201_02')),
('hist_085', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '5 days', 'ST007_ID', (SELECT id FROM courses WHERE code = 'BUS301_02')),
('hist_086', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '4 days', 'ST009_ID', (SELECT id FROM courses WHERE code = 'BUS201_01')),
('hist_087', 'ENROLLED', CURRENT_TIMESTAMP - INTERVAL '4 days', 'ST010_ID', (SELECT id FROM courses WHERE code = 'CS201_02'));

-- Create text_contents table
CREATE TABLE text_contents (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMP
);

-- Create translations table
CREATE TABLE translations (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    text TEXT,
    is_original BOOLEAN,
    language_code VARCHAR(10),
    text_content_id INTEGER,
    CONSTRAINT fk_translations_text_content FOREIGN KEY (text_content_id) REFERENCES text_contents (id),
    CONSTRAINT uq_translations_text_language UNIQUE (language_code, text_content_id)
);

-- Alter faculties table to add name_id column
ALTER TABLE faculties
ADD COLUMN name_id INTEGER,
ADD CONSTRAINT fk_faculties_name FOREIGN KEY (name_id) REFERENCES text_contents (id);

-- Alter programs table to add name_id column
ALTER TABLE programs
ADD COLUMN name_id INTEGER,
ADD CONSTRAINT fk_programs_name FOREIGN KEY (name_id) REFERENCES text_contents (id);

-- Alter subjects table to add name_id and description_id columns
ALTER TABLE subjects
ADD COLUMN name_id INTEGER,
ADD COLUMN description_id INTEGER,
ADD CONSTRAINT fk_subjects_name FOREIGN KEY (name_id) REFERENCES text_contents (id),
ADD CONSTRAINT fk_subjects_description FOREIGN KEY (description_id) REFERENCES text_contents (id);

-- Alter statuses table to add name_id column
ALTER TABLE statuses
ADD COLUMN name_id INTEGER,
ADD CONSTRAINT fk_statuses_name FOREIGN KEY (name_id) REFERENCES text_contents (id);

-- Populate text_contents and translations for faculties
INSERT INTO text_contents (created_at)
SELECT CURRENT_TIMESTAMP FROM faculties;

-- Insert translations for faculties (English original)
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT f.name, TRUE, 'en', tc.id
FROM faculties f
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents ORDER BY id LIMIT 1 OFFSET (f.id - 1)
);

-- Add Vietnamese translations for faculties
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT 
    CASE f.name
        WHEN 'Faculty of Business English' THEN 'Khoa Tiếng Anh Thương mại'
        WHEN 'Faculty of Law' THEN 'Khoa Luật'
        WHEN 'Faculty of Japanese' THEN 'Khoa Tiếng Nhật'
        WHEN 'Faculty of French' THEN 'Khoa Tiếng Pháp'
    END,
    FALSE, 'vi', tc.id
FROM faculties f
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents ORDER BY id LIMIT 1 OFFSET (f.id - 1)
);

-- Update faculties with references to text_contents
UPDATE faculties f
SET name_id = (
    SELECT id FROM text_contents ORDER BY id LIMIT 1 OFFSET (f.id - 1)
);

-- Populate text_contents and translations for programs
INSERT INTO text_contents (created_at)
SELECT CURRENT_TIMESTAMP FROM programs;

-- Insert translations for programs (English original)
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT p.name, TRUE, 'en', tc.id
FROM programs p
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + p.id - 1)
);

-- Add Vietnamese translations for programs
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT 
    CASE p.name
        WHEN 'Business Administration' THEN 'Quản trị Kinh doanh'
        WHEN 'Criminal Justice' THEN 'Tư pháp Hình sự'
        WHEN 'Japanese Literature' THEN 'Văn học Nhật Bản'
        WHEN 'French Studies' THEN 'Nghiên cứu Pháp'
        WHEN 'International Business' THEN 'Kinh doanh Quốc tế'
        WHEN 'Japanese Culture' THEN 'Văn hóa Nhật Bản'
        WHEN 'Corporate Law' THEN 'Luật Doanh nghiệp'
        WHEN 'French Language' THEN 'Ngôn ngữ Pháp'
        WHEN 'Japanese Economics' THEN 'Kinh tế học Nhật Bản'
        WHEN 'Business Communication' THEN 'Giao tiếp Kinh doanh'
    END,
    FALSE, 'vi', tc.id
FROM programs p
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + p.id - 1)
);

-- Update programs with references to text_contents
UPDATE programs p
SET name_id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + p.id - 1)
);

-- Populate text_contents and translations for statuses
INSERT INTO text_contents (created_at)
SELECT CURRENT_TIMESTAMP FROM statuses;

-- Insert translations for statuses (English original)
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT s.name, TRUE, 'en', tc.id
FROM statuses s
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + s.id - 1)
);

-- Add Vietnamese translations for statuses
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT 
    CASE s.name
        WHEN 'Studying' THEN 'Đang học'
        WHEN 'Graduated' THEN 'Đã tốt nghiệp'
        WHEN 'Suspended' THEN 'Đình chỉ'
        WHEN 'Dropped' THEN 'Đã thôi học'
    END,
    FALSE, 'vi', tc.id
FROM statuses s
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + s.id - 1)
);

-- Update statuses with references to text_contents
UPDATE statuses s
SET name_id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + s.id - 1)
);

-- Create text_contents for subjects (names)
INSERT INTO text_contents (created_at)
SELECT CURRENT_TIMESTAMP FROM subjects;

-- Insert translations for subject names (English original)
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT sub.name, TRUE, 'en', tc.id
FROM subjects sub
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + (SELECT COUNT(*) FROM statuses) + sub.id - 1)
);

-- Update subjects with references to text_contents for names
UPDATE subjects sub
SET name_id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + (SELECT COUNT(*) FROM statuses) + sub.id - 1)
);

-- Create text_contents for subjects (descriptions)
INSERT INTO text_contents (created_at)
SELECT CURRENT_TIMESTAMP FROM subjects;

-- Insert translations for subject descriptions (English original)
INSERT INTO translations (text, is_original, language_code, text_content_id)
SELECT 
    COALESCE(sub.description, 'No description available'), 
    TRUE, 
    'en', 
    tc.id
FROM subjects sub
JOIN text_contents tc ON tc.id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + (SELECT COUNT(*) FROM statuses) + (SELECT COUNT(*) FROM subjects) + sub.id - 1)
);

-- Update subjects with references to text_contents for descriptions
UPDATE subjects sub
SET description_id = (
    SELECT id FROM text_contents 
    ORDER BY id 
    LIMIT 1 
    OFFSET ((SELECT COUNT(*) FROM faculties) + (SELECT COUNT(*) FROM programs) + (SELECT COUNT(*) FROM statuses) + (SELECT COUNT(*) FROM subjects) + sub.id - 1)
);

-- Add Vietnamese translations for select subjects (just a few examples)
INSERT INTO translations (text, is_original, language_code, text_content_id)
VALUES
-- CS101 name
(N'Nhập môn Khoa học Máy tính', FALSE, 'vi', 
 (SELECT tc.id FROM subjects s JOIN text_contents tc ON s.name_id = tc.id WHERE s.code = 'CS101')),
-- CS101 description
(N'Giới thiệu cơ bản về các nguyên lý khoa học máy tính', FALSE, 'vi',
 (SELECT tc.id FROM subjects s JOIN text_contents tc ON s.description_id = tc.id WHERE s.code = 'CS101')),

-- MATH101 name
(N'Giải tích I', FALSE, 'vi',
 (SELECT tc.id FROM subjects s JOIN text_contents tc ON s.name_id = tc.id WHERE s.code = 'MATH101')),
-- MATH101 description
(N'Giới hạn, đạo hàm và tích phân cơ bản', FALSE, 'vi',
 (SELECT tc.id FROM subjects s JOIN text_contents tc ON s.description_id = tc.id WHERE s.code = 'MATH101')),

-- BUS101 name
(N'Nhập môn Kinh doanh', FALSE, 'vi',
 (SELECT tc.id FROM subjects s JOIN text_contents tc ON s.name_id = tc.id WHERE s.code = 'BUS101')),
-- BUS101 description
(N'Các nguyên tắc cơ bản của kinh doanh và khởi nghiệp', FALSE, 'vi',
 (SELECT tc.id FROM subjects s JOIN text_contents tc ON s.description_id = tc.id WHERE s.code = 'BUS101'));

 -- Remove the duplicate text_contents and translations table creation
-- Since these tables are already created earlier in the script

-- Alter faculties table to remove name column (since we're using name_id now)
ALTER TABLE faculties 
DROP COLUMN name CASCADE;

-- Alter programs table to remove name column (since we're using name_id now)
ALTER TABLE programs
DROP COLUMN name CASCADE;

-- Alter subjects table to remove name and description columns (since we're using name_id and description_id now)
ALTER TABLE subjects
DROP COLUMN name CASCADE,
DROP COLUMN description CASCADE;

-- Alter statuses table to remove name column (since we're using name_id now)
ALTER TABLE statuses
DROP COLUMN name CASCADE;