CREATE TABLE "Role" (
  "id" TEXT PRIMARY KEY,
  "name" TEXT UNIQUE,
  "permissions" TEXT,
  "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME
);

CREATE TABLE "Vacc" (
  "id" TEXT PRIMARY KEY,
  "name" TEXT UNIQUE,
  "website" TEXT,
  "contactEmail" TEXT,
  "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME
);

CREATE TABLE "User" (
  "id" TEXT PRIMARY KEY,
  "name" TEXT,
  "ratingId" INTEGER,
  "ratingShort" TEXT,
  "ratingLong" TEXT,
  "region" TEXT,
  "division" TEXT,
  "roleIds" TEXT,
  "vaccId" TEXT,
  "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME,
  FOREIGN KEY ("vaccId") REFERENCES "Vacc" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "AuditLogEntry" (
  "id" TEXT PRIMARY KEY,
  "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "actor" TEXT,
  "item" TEXT,
  "before" TEXT,
  "after" TEXT,
  "message" TEXT
);

CREATE TABLE "TrainingQueue" (
  "id" TEXT PRIMARY KEY,
  "vaccId" TEXT,
  "name" TEXT,
  "description" TEXT,
  "joinableByDefault" BOOLEAN,
  "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME,
  FOREIGN KEY ("vaccId") REFERENCES "Vacc" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "TrainingQueueMembership" (
  "userId" TEXT,
  "queueId" TEXT,
  "joinedAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("userId", "queueId"),
  FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY ("queueId") REFERENCES "TrainingQueue" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "Certificate" (
  "holderId" TEXT,
  "instructorId" TEXT,
  "position" TEXT,
  "expires" DATETIME,
  "instructorComments" TEXT,
  "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("holderId", "instructorId", "position"),
  FOREIGN KEY ("holderId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY ("instructorId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "Resource" (
  "id" TEXT PRIMARY KEY,
  "vaccId" TEXT,
  "isStaffOnly" BOOLEAN,
  "name" TEXT,
  "description" TEXT,
  "link" TEXT,
  FOREIGN KEY ("vaccId") REFERENCES "Vacc" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "Session" (
  "id" TEXT PRIMARY KEY,
  "studentId" TEXT,
  "instructorId" TEXT,
  "sessionType" TEXT,
  "date" DATETIME,
  "studentComments" TEXT,
  "instructorComments" TEXT,
  "createdAt" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME,
  FOREIGN KEY ("studentId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY ("instructorId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "Connection" (
  "id" TEXT PRIMARY KEY,
  "userId" TEXT,
  "callsign" TEXT,
  "isAuthorized" BOOLEAN,
  "startTime" DATETIME DEFAULT CURRENT_TIMESTAMP,
  "endTime" DATETIME,
  FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

INSERT INTO Role (id, name, permissions) VALUES 
('1', 'Admin', 'manage_users'),
('2', 'Instructor', 'manage_queues'),
('3', 'Student', 'join_queues'),
('4', 'Staff', 'access_staff_resources'),
('5', 'Monitor', 'monitor_connections'),
('6', 'Guest', ''),
('7', 'Trainee', 'join_trainee_queues');

INSERT INTO Vacc (id, name, website, contactEmail) VALUES 
('v1', 'Virtual ATC Club', 'https://[Log in to view URL]', 'contact@virtualatcclub.com'),
('v2', 'SkyZone ATC', 'https://[Log in to view URL]', 'skyzone@example.com'),
('v3', 'Virtual Air Traffic Controllers Association', 'https://[Log in to view URL]', 'vata@vatsim.net'),
('v4', 'Online Air Traffic Control Training', 'https://[Log in to view URL]', 'trainers@oact.org'),
('v5', 'Cyber ATC', 'https://[Log in to view URL]', 'support@cybertc.com');

INSERT INTO User (id, name, ratingId, ratingShort, ratingLong, region, division, roleIds, vaccId, createdAt, updatedAt) 
VALUES 
('u1', 'Alice Johnson', 12345, 'C1', 'Controller Grade 1', 'North', 'Pacific', '1,3', 'v1', '2023-08-01 10:00:00', '2023-08-01 10:05:00'),
('u2', 'Bob Smith', 67890, 'C2', 'Controller Grade 2', 'South', 'Atlantic', '3,7', 'v2', '2023-08-02 08:00:00', '2023-08-02 08:15:00'),
('u3', 'Charlie Brown', 23415, 'T3', 'Trainee Level 3', 'Central', 'Great Lakes', '7', 'v4', '2023-07-25 14:30:00', '2023-07-25 14:35:00'),
('u4', 'David Lee', 56789, 'I', 'Instructor', 'West', 'Mountain', '2', 'v3', '2023-07-10 18:45:00', '2023-07-10 18:50:00'),
('u5', 'Eva Martinez', 34567, 'C1', 'Controller Grade 1', 'North', 'Gulf', '3', 'v1', '2023-08-03 09:00:00', '2023-08-03 09:10:00'),
('u6', 'Frank Wilson', 67891, 'T1', 'Trainee Level 1', 'South', 'Atlantic', '7', 'v2', '2023-08-15 11:20:00', '2023-08-15 11:25:00'),
('u7', 'Grace Huang', 13579, 'C3', 'Controller Grade 3', 'Central', 'Great Lakes', '3,4', 'v4', '2023-07-18 15:50:00', '2023-07-18 16:00:00'),
('u8', 'Henry Black', 45687, 'T2', 'Trainee Level 2', 'West', 'Pacific', '7', 'v1', '2023-07-20 12:30:00', '2023-07-20 12:35:00'),
('u9', 'Ivy Taylor', 78902, 'C2', 'Controller Grade 2', 'North', 'Gulf', '3,5', 'v3', '2023-08-08 07:45:00', '2023-08-08 08:00:00'),
('u10', 'Jack Moore', 24680, 'T1', 'Trainee Level 1', 'South', 'Atlantic', '3,7', 'v2', '2023-08-12 10:40:00', '2023-08-12 10:45:00'),
('u11', 'Jane White', 56791, 'I', 'Instructor', 'Central', 'Mountain', '2,4', 'v4', '2023-07-12 14:10:00', '2023-07-12 14:15:00'),
('u12', 'John Doe', 87654, 'C3', 'Controller Grade 3', 'West', 'Pacific', '3', 'v3', '2023-07-22 16:30:00', '2023-07-22 16:35:00'),
('u13', 'Karen Young', 45876, 'T2', 'Trainee Level 2', 'North', 'Atlantic', '7', 'v1', '2023-08-06 09:50:00', '2023-08-06 10:00:00'),
('u14', 'Lucas Green', 78965, 'C1', 'Controller Grade 1', 'South', 'Gulf', '3,5', 'v2', '2023-08-01 08:00:00', '2023-08-01 08:10:00'),
('u15', 'Marta Lopez', 23458, 'T3', 'Trainee Level 3', 'Central', 'Mountain', '7', 'v4', '2023-07-15 13:20:00', '2023-07-15 13:25:00'),
('u16', 'Michael Blue', 56781, 'I', 'Instructor', 'West', 'Pacific', '2,4', 'v3', '2023-07-08 17:30:00', '2023-07-08 17:35:00'),
('u17', 'Nora Red', 87651, 'C2', 'Controller Grade 2', 'North', 'Atlantic', '3,5', 'v1', '2023-08-18 07:00:00', '2023-08-18 07:10:00'),
('u18', 'Oliver Orange', 12378, 'T3', 'Trainee Level 3', 'South', 'Gulf', '7', 'v2', '2023-08-10 11:45:00', '2023-08-10 11:50:00'),
('u19', 'Paula Sky', 45681, 'T1', 'Trainee Level 1', 'Central', 'Mountain', '3,7', 'v4', '2023-07-28 14:00:00', '2023-07-28 14:05:00'),
('u20', 'Queen Violet', 78951, 'G', 'Guest', 'West', 'Pacific', '6', 'v3', '2023-07-01 18:00:00', '2023-07-01 18:05:00');

INSERT INTO AuditLogEntry (id, timestamp, actor, item, before, after, message) 
VALUES 
('al1', '2023-09-05 10:00:00', 'Admin', 'User u1', 'Role IDs: 3', 'Role IDs: 1,3', 'Alice Johnson''s roles updated. Added: Admin'),
('al2', '2023-09-05 10:10:00', 'Instructor David Lee', 'User u4', 'RatingShort: I', 'RatingShort: C4', 'David Lee changed his own rating to Controller Grade 4'),
('al3', '2023-09-05 11:30:00', 'Admin', 'TrainingQueue q1', 'JoinableByDefault: true', 'JoinableByDefault: false', 'Queue made non-joinable'),
('al4', '2023-09-06 08:00:00', 'Instructor Bob Smith', 'Session s1', 'SessionType: Training', 'SessionType: Evaluation', 'Bob Smith changed session type.'),
('al5', '2023-09-06 08:45:00', 'Instructor', 'Certificate c2', 'HolderID: u2', 'HolderID: u3', 'Certificate transferred from User u2 to User u3'),
('al6', '2023-09-07 10:20:00', 'Admin', 'Role r5', 'Name: Monitor', 'Name: Supervisor', 'Renamed Monitor role to Supervisor'),
('al7', '2023-09-07 11:10:00', 'User u1', 'User u1', 'Region: North', 'Region: South', 'Alice Johnson changed her own region.'),
('al8', '2023-09-08 09:00:00', 'Instructor', 'TrainingQueue q2', 'Name: Old Queue', 'Name: New Queue', 'Renamed queue.'),
('al9', '2023-09-08 09:30:00', 'User u3', 'TrainingQueueMembership m1', 'QueueID: q1', 'QueueID: q2', 'Charlie Brown left queue q1 and joined queue q2'),
('al10', '2023-09-09 08:02:00', 'Admin', 'User u2', 'Role IDs: 3,7', 'Role IDs: 1,3,7', 'Added Admin role to Bob Smith''s existing roles.'),
('al11', '2023-09-09 08:20:00', 'Instructor', 'Resource r1', 'Link: https://[Log in to view URL]', 'Link: https://[Log in to view URL]', 'Updated resource link.'),
('al12', '2023-09-10 10:30:00', 'User u5', 'Connection c5', 'Authorized: false', 'Authorized: true', 'Eva Martinez authorized her connection.'),
('al13', '2023-09-11 07:45:00', 'Admin', 'User u6', 'RatingShort: T1', 'RatingShort: C1', 'Frank Wilson''s rating upgraded to Controller Grade 1.'),
('al14', '2023-09-12 09:05:00', 'Instructor', 'Session s6', 'Date: 2023-09-13T10:00:00', 'Date: 2023-09-14T14:00:00', 'Rescheduled session.'),
('al15', '2023-09-12 14:15:00', 'User u4', 'User u4', 'Division: Great Lakes', 'Division: Pacific', 'David Lee changed his own division.');

INSERT INTO TrainingQueue (id, vaccId, name, description, joinableByDefault, createdAt, updatedAt) 
VALUES 
('q1', 'v1', 'Beginner''s Queue', 'For new trainees', 1, '2023-08-01 09:00:00', '2023-08-01 09:05:00'),
('q2', 'v1', 'Advanced Training', 'For controllers with Rating C2', 0, '2023-08-01 09:15:00', '2023-08-01 09:20:00'),
('q3', 'v2', 'SkyZone Academy', 'Exclusive training for SkyZone members', 1, '2023-08-02 08:00:00', '2023-08-02 08:05:00'),
('q4', 'v3', 'VATSIM North Region', 'For controllers in the North region', 0, '2023-08-03 07:00:00', '2023-08-03 07:05:00'),
('q5', 'v3', 'Guest Practice', 'For guests to get hands-on practice', 1, '2023-08-03 07:20:00', '2023-08-03 07:25:00'),
('q6', 'v4', 'Trainee Bootcamp', 'Intensive training for trainees', 0, '2023-07-25 14:00:00', '2023-07-25 14:10:00'),
('q7', 'v4', 'Instructor Led', 'Queue for sessions led by instructors', 0, '2023-07-26 10:00:00', '2023-07-26 10:10:00'),
('q8', 'v1', 'Regional Training', 'For controllers in the South region', 1, '2023-08-15 09:00:00', '2023-08-15 09:05:00'),
('q9', 'v2', 'Advanced Strategies', 'For controllers with Rating C3', 0, '2023-08-16 08:00:00', '2023-08-16 08:10:00'),
('q10', 'v3', 'West Coast Hub', 'For controllers in the West division', 1, '2023-08-08 16:00:00', '2023-08-08 16:05:00'),
('q11', 'v1', 'Specialty Training', 'For specific specialty training', 0, '2023-08-09 09:00:00', '2023-08-09 09:10:00'),
('q12', 'v2', 'Member Refresher', 'For members to refresh their skills', 1, '2023-08-10 08:00:00', '2023-08-10 08:05:00'),
('q13', 'v4', 'Evaluation Center', 'For final evaluation of trainees', 0, '2023-07-12 14:00:00', '2023-07-12 14:15:00'),
('q14', 'v3', 'Pacific Division', 'For controllers in the Pacific division', 1, '2023-08-18 07:00:00', '2023-08-18 07:05:00'),
('q15', 'v2', 'Bonus Round', 'For extra practice sessions', 1, '2023-08-19 08:00:00', '2023-08-19 08:05:00');

INSERT INTO TrainingQueueMembership (userId, queueId, joinedAt) 
VALUES 
('u3', 'q1', '2023-08-01 09:10:00'),
('u6', 'q1', '2023-08-02 09:25:00'),
('u1', 'q2', '2023-08-01 09:30:00'),
('u2', 'q3', '2023-08-02 08:15:00'),
('u5', 'q4', '2023-08-03 07:30:00'),
('u19', 'q6', '2023-07-25 14:15:00'),
('u8', 'q7', '2023-07-26 10:15:00'),
('u13', 'q8', '2023-08-15 09:10:00'),
('u10', 'q9', '2023-08-16 08:20:00'),
('u4', 'q10', '2023-08-08 16:10:00'),
('u7', 'q11', '2023-08-09 09:20:00'),
('u9', 'q12', '2023-08-10 08:15:00'),
('u16', 'q13', '2023-07-12 14:25:00'),
('u12', 'q14', '2023-08-18 07:15:00'),
('u18', 'q15', '2023-08-19 08:25:00'),
('u15', 'q3', '2023-08-02 08:30:00');

INSERT INTO Certificate (holderId, instructorId, position, expires, instructorComments, createdAt) 
VALUES 
('u3', 'u4', 'ATCO', '2024-03-01', 'Well done!', '2023-08-01T10:00:00'),
('u6', 'u2', 'Trainee', '2023-12-31', 'Great progress!', '2023-08-02T08:00:00'),
('u1', 'u4', 'Instructor', '2024-06-01', 'Welcome to the team!', '2023-08-01T09:30:00'),
('u8', 'u11', 'Controller', '2024-04-01', 'Keep up the good work!', '2023-07-26T10:30:00'),
('u10', 'u2', 'ATCO', '2024-02-28', 'Proud of your achievement!', '2023-08-16T08:30:00'),
('u14', 'u1', 'Senior Controller', '2024-05-01', 'Excellent performance.', '2023-08-01T08:45:00'),
('u17', 'u16', 'Instructor', '2024-07-01', 'Happy to have you on board.', '2023-08-18T07:30:00'),
('u5', 'u11', 'Controller', '2024-03-31', 'Good job!', '2023-08-03T07:15:00'),
('u12', 'u16', 'ATCO', '2025-03-01', 'Outstanding achievement.', '2023-07-22T16:45:00'),
('u9', 'u4', 'Senior Instructor', '2024-08-01', 'Congratulations!', '2023-08-08T08:00:00');

INSERT INTO Resource (id, vaccId, isStaffOnly, name, description, link) 
VALUES 
('r1', 'v1', 0, 'ATC Manual', 'Basic manual for air traffic control', 'https://[Log in to view URL]'),
('r2', 'v1', 1, 'Advanced Procedures Guide', 'Detailed guide for advanced ATC procedures', 'https://[Log in to view URL] (password: <PASSWORD>)'),
('r3', 'v2', 0, 'SkyZone Academy Videos', 'Video tutorials for trainees', 'https://[Log in to view URL]'),
('r4', 'v3', 1, 'Instructor Handbook', 'Handbook for VATSIM instructors', 'https://[Log in to view URL]'),
('r5', 'v3', 0, 'New Controller Welcome Guide', 'A guide to help new controllers get started', 'https://[Log in to view URL]'),
('r6', 'v4', 1, 'Trainee Resources', 'Exclusive resources for trainees on our platform', 'https://[Log in to view URL]'),
('r7', 'v4', 0, 'ATC Glossary', 'Glossary of commonly used ATC terms', 'https://[Log in to view URL]'),
('r8', 'v1', 0, 'Regional Training Materials', 'Training materials specific to the North region', 'https://[Log in to view URL]'),
('r9', 'v2', 1, 'Instructor Training Videos', 'Private video library for instructor training', 'https://[Log in to view URL]'),
('r10', 'v3', 0, 'VATSIM Forum', 'Official forum for discussions', 'https://[Log in to view URL]'),
('r11', 'v1', 1, 'Staff Handbook', 'Handbook for VACC staff members', 'https://[Log in to view URL] (password: staff123)'),
('r12', 'v2', 0, 'Member Resources', 'Various resources for VACC members', 'https://[Log in to view URL]'),
('r13', 'v4', 1, 'Trainee Assessment Tools', 'Tools for evaluating trainees', 'https://[Log in to view URL]'),
('r14', 'v3', 0, 'Controller Tips and Tricks', 'Helpful tips for new and experienced controllers', 'https://[Log in to view URL]'),
('r15', 'v2', 0, 'SkyZone Blog', 'Blog with articles and updates', 'https://[Log in to view URL]');

INSERT INTO Session (id, studentId, instructorId, sessionType, date, studentComments, instructorComments, createdAt, updatedAt) 
VALUES 
('s1', 'u3', 'u4', 'Training', '2023-08-01T10:30:00', 'Great session! Learned a lot.', 'Pleased with Charlie''s progress.', '2023-08-01T10:00:00', '2023-08-01T10:45:00'),
('s2', 'u6', 'u2', 'Evaluation', '2023-08-02T08:30:00', 'Ready for the next level!', 'Frank is ready for C2.', '2023-08-02T08:00:00', '2023-08-02T08:45:00'),
('s3', 'u1', 'u11', 'Mentorship', '2023-08-01T09:45:00', 'Helpful mentorship session.', 'Alice is improving!', '2023-08-01T09:00:00', '2023-08-01T10:00:00'),
('s4', 'u8', 'u4', 'Training', '2023-07-26T10:45:00', 'Enjoying the training.', 'Henry is a quick learner.', '2023-07-26T10:00:00', '2023-07-26T11:00:00'),
('s5', 'u10', 'u2', 'Evaluation', '2023-08-16T08:45:00', 'Feel ready for the challenge!', 'Bob recommends moving to the next level.', '2023-08-16T08:00:00', '2023-08-16T09:00:00'),
('s6', 'u14', 'u1', 'Mentorship', '2023-08-01T08:15:00', 'Learned some great tips!', 'Productive mentorship session.', '2023-08-01T08:00:00', '2023-08-01T08:30:00'),
('s7', 'u17', 'u16', 'Training', '2023-08-18T07:45:00', 'Fantastic session, as always!', 'Nora is doing exceptionally well.', '2023-08-18T07:00:00', '2023-08-18T08:00:00'),
('s8', 'u5', 'u11', 'Evaluation', '2023-08-03T07:30:00', 'Proud of my progress!', 'Eva is ready for her C1 evaluation.', '2023-08-03T07:00:00', '2023-08-03T07:45:00'),
('s9', 'u12', 'u16', 'Training', '2023-07-22T17:00:00', 'Good session, need to work on X.', 'John struggled with a specific area.', '2023-07-22T16:30:00', '2023-07-22T16:45:00'),
('s10', 'u9', 'u4', 'Mentorship', '2023-08-08T08:15:00', 'Incredibly insightful!', 'Ivy appreciated the mentorship.', '2023-08-08T08:00:00', '2023-08-08T08:30:00'),
('s11', 'u18', 'u2', 'Evaluation', '2023-08-10T12:00:00', 'Feel confident about my skills.', 'Oliver is recommended for promotion.', '2023-08-10T11:45:00', '2023-08-10T12:15:00'),
('s12', 'u7', 'u11', 'Training', '2023-07-12T14:30:00', 'Training went smoothly.', 'Grace is improving her skills.', '2023-07-12T14:00:00', '2023-07-12T14:45:00'),
('s13', 'u15', 'u16', 'Evaluation', '2023-07-15T13:45:00', 'Nervous but ready!', 'Marta did well in her evaluation.', '2023-07-15T13:00:00', '2023-07-15T14:00:00'),
('s14', 'u4', 'u2', 'Mentorship', '2023-08-03T18:00:00', 'Beneficial mentorship, thanks!', 'David appreciated the guidance.', '2023-08-03T17:30:00', '2023-08-03T18:15:00'),
('s15', 'u13', 'u1', 'Training', '2023-08-06T10:00:00', 'Fun and productive!', 'Karen enjoyed the session.', '2023-08-06T09:50:00', '2023-08-06T10:15:00');

INSERT INTO Connection (id, userId, callsign, isAuthorized, startTime, endTime) 
VALUES 
('c1', 'u3', 'N12345', 1, '2023-08-01T11:00:00', '2023-08-01T11:45:00'),
('c2', 'u6', 'S56789', 1, '2023-08-02T09:00:00', '2023-08-02T09:30:00'),
('c3', 'u1', 'A1B2C3', 1, '2023-08-01T10:00:00', '2023-08-01T10:25:00'),
('c4', 'u8', 'H45678', 0, '2023-07-26T11:00:00', '2023-07-26T11:15:00'),
('c5', 'u5', 'E54321', 1, '2023-08-03T07:45:00', '2023-08-03T08:30:00'),
('c6', 'u12', 'J67890', 0, '2023-07-22T17:15:00', '2023-07-22T17:45:00'),
('c7', 'u9', 'I90123', 1, '2023-08-08T08:30:00', '2023-08-08T09:15:00'),
('c8', 'u10', 'O09876', 1, '2023-08-16T09:00:00', '2023-08-16T09:45:00'),
('c9', 'u14', 'Q12345', 1, '2023-08-01T08:30:00', '2023-08-01T09:00:00'),
('c10', 'u17', 'N65432', 1, '2023-08-18T08:00:00', '2023-08-18T08:30:00'),
('c11', 'u7', 'G78901', 1, '2023-07-12T14:45:00', '2023-07-12T15:30:00'),
('c12', 'u15', 'M56789', 0, '2023-07-15T14:15:00', '2023-07-15T14:45:00'),
('c13', 'u4', 'D45678', 1, '2023-08-03T18:30:00', '2023-08-03T19:15:00'),
('c14', 'u2', 'B23456', 1, '2023-08-02T08:45:00', '2023-08-02T09:20:00'),
('c15', 'u13', 'K12345', 0, '2023-08-06T10:30:00', '2023-08-06T11:00:00'),
('c16', 'u18', 'O87654', 1, '2023-08-10T12:30:00', '2023-08-10T13:15:00'),
('c17', 'u11', 'J10987', 1, '2023-07-12T14:25:00', '2023-07-12T14:55:00'),
('c18', 'u16', 'M43210', 1, '2023-07-08T17:45:00', '2023-07-08T18:30:00');

-- Unused Certification
-- SELECT
--   u.name AS UserName,
--   c.position AS CertificatePosition,
--   c.createdAt AS CertificateIssuedDate
-- FROM
--   Certificate c
-- JOIN
--   User u ON c.holderId = u.id
-- LEFT JOIN
--   Session s ON s.studentId = c.holderId AND s.date > c.createdAt
-- WHERE
--   s.id IS NULL
-- GROUP BY
--   u.name, c.position, c.createdAt
-- ORDER BY
--   CertificateIssuedDate;

-- Trainee Session Data
-- WITH TraineeSessions AS (
--   SELECT 
--     u.id AS userId,
--     COUNT(s.id) AS TotalSessions,
--     COUNT(DISTINCT s.instructorId) AS UniqueInstructors,
--     MAX(s.date) AS LatestSessionDate
--   FROM User u
--   LEFT JOIN Session s ON u.id = s.studentId
--   WHERE u.roleIds LIKE '%7%'  -- Assuming '7' is part of a comma-separated list and represents the 'Trainee' role
--   GROUP BY u.id
-- )
-- SELECT 
--   u.id,
--   u.name,
--   IFNULL(ts.TotalSessions, 0) AS TotalSessions,
--   IFNULL(ts.UniqueInstructors, 0) AS UniqueInstructors,
--   ts.LatestSessionDate
-- FROM User u
-- LEFT JOIN TraineeSessions ts ON u.id = ts.userId
-- WHERE u.roleIds LIKE '%7%'
-- ORDER BY u.name;

-- Most Active Users
-- WITH StudentSessionsCTE AS (
--   SELECT 
--     studentId AS userId, 
--     COUNT(*) AS SessionsAttended
--   FROM Session
--   GROUP BY studentId
-- ),
-- InstructorSessionsCTE AS (
--   SELECT 
--     instructorId AS userId, 
--     COUNT(*) AS SessionsConducted
--   FROM Session
--   GROUP BY instructorId
-- ),
-- AuditActionsCTE AS (
--   SELECT 
--     actor, 
--     COUNT(*) AS AuditLogActions
--   FROM AuditLogEntry
--   GROUP BY actor
-- ),
-- CombinedScores AS (
--   SELECT 
--     u.id AS userId,
--     u.name,
--     COALESCE(ss.SessionsAttended, 0) AS SessionsAttended,
--     COALESCE(iscte.SessionsConducted, 0) AS SessionsConducted,
--     COALESCE(aa.AuditLogActions, 0) AS AuditLogActions,
--     (COALESCE(ss.SessionsAttended, 0) + COALESCE(iscte.SessionsConducted, 0) + COALESCE(aa.AuditLogActions, 0)) AS CombinedScore
--   FROM User u
--   LEFT JOIN StudentSessionsCTE ss ON u.id = ss.userId
--   LEFT JOIN InstructorSessionsCTE iscte ON u.id = iscte.userId
--   LEFT JOIN AuditActionsCTE aa ON u.name = aa.actor  -- Assuming 'actor' in AuditLogEntry matches the user's 'name'
-- )
-- SELECT 
--   userId,
--   name,
--   SessionsAttended,
--   SessionsConducted,
--   AuditLogActions,
--   CombinedScore
-- FROM CombinedScores
-- ORDER BY CombinedScore DESC, name ASC
-- LIMIT 5;

-- Identify the "Vacc" organizations with the highest number of issued certificates. 
-- Include the total number of certificates and the percentage of these certificates that were awarded 
-- to 'Instructors'. Also list the most common 'position' for which certificates were issued within 
-- each organization.

-- Correlation: Sessions -> Certificate
-- WITH InstructorSessions AS (
--   SELECT
--     instructorId,
--     COUNT(*) AS SessionsConducted
--   FROM Session
--   GROUP BY instructorId
-- ),
-- InstructorCertificates AS (
--   SELECT
--     instructorId,
--     COUNT(*) AS CertificatesIssued
--   FROM Certificate
--   GROUP BY instructorId
-- ),
-- InstructorRatios AS (
--   SELECT
--     ic.instructorId,
--     COALESCE(CAST(ic.CertificatesIssued AS FLOAT) / NULLIF(ins.SessionsConducted, 0), 0) AS CertificatesPerSession
--   FROM InstructorCertificates ic
--   LEFT JOIN InstructorSessions ins ON ic.instructorId = ins.instructorId
-- )
-- SELECT 
--   ROUND(AVG(CertificatesPerSession), 3) AS AvgCertificatesPerSessionRatio
-- FROM InstructorRatios;

-- Time Series
-- WITH UserSessions AS (
--   SELECT
--     u.vaccId,
--     strftime('%Y-%m', s.date) AS Month,
--     COUNT(*) AS SessionCount
--   FROM Session s
--   JOIN User u ON s.studentId = u.id
--   GROUP BY u.vaccId, Month
-- ),
-- MovingAverages AS (
--   SELECT
--     vaccId,
--     Month,
--     SessionCount,
--     AVG(SessionCount) OVER (PARTITION BY vaccId ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
--   FROM UserSessions
-- ),
-- TrendAnalysis AS (
--   SELECT
--     vaccId,
--     Month,
--     MovingAvg,
--     LEAD(MovingAvg, 1) OVER (PARTITION BY vaccId ORDER BY Month) AS NextMonthMovingAvg
--   FROM MovingAverages
-- ),
-- IncreasingTrend AS (
--   SELECT
--     vaccId,
--     COUNT(*) AS CountOfIncreasingMonths
--   FROM TrendAnalysis
--   WHERE MovingAvg < NextMonthMovingAvg
--   GROUP BY vaccId
--   HAVING COUNT(*) >= 1 -- Looking for an increasing trend over at least 1 consecutive records
-- )
-- SELECT DISTINCT
--   vaccId
-- FROM IncreasingTrend
-- ORDER BY vaccId;

-- Most Active Users
-- WITH UserSessionsAttended AS (
--   SELECT
--     studentId AS userId,
--     COUNT(*) AS SessionsAttended
--   FROM Session
--   GROUP BY studentId
-- ),
-- UserSessionsConducted AS (
--   SELECT
--     instructorId AS userId,
--     COUNT(*) AS SessionsConducted
--   FROM Session
--   GROUP BY instructorId
-- ),
-- UserCertificates AS (
--   SELECT
--     instructorId AS userId,
--     COUNT(*) AS CertificatesEarned
--   FROM Certificate
--   GROUP BY instructorId
-- ),
-- UserAuditLogs AS (
--   SELECT
--     actor AS userId,
--     COUNT(*) AS LogEntriesCreated
--   FROM AuditLogEntry
--   GROUP BY actor
-- ),
-- UserActivity AS (
--   SELECT
--     u.id AS userId,
--     u.name AS userName,
--     COALESCE(sa.SessionsAttended, 0) AS SessionsAttended,
--     COALESCE(sc.SessionsConducted, 0) AS SessionsConducted,
--     COALESCE(c.CertificatesEarned, 0) AS CertificatesEarned,
--     COALESCE(a.LogEntriesCreated, 0) AS LogEntriesCreated
--   FROM User u
--   LEFT JOIN UserSessionsAttended sa ON u.id = sa.userId
--   LEFT JOIN UserSessionsConducted sc ON u.id = sc.userId
--   LEFT JOIN UserCertificates c ON u.id = c.userId
--   LEFT JOIN UserAuditLogs a ON u.id = a.userId
-- ),
-- ActivityQuartiles AS (
--   SELECT
--     userId,
--     userName,
--     NTILE(4) OVER (ORDER BY SessionsAttended) AS AttendedQuartile,
--     NTILE(4) OVER (ORDER BY SessionsConducted) AS ConductedQuartile,
--     NTILE(4) OVER (ORDER BY CertificatesEarned) AS CertificatesQuartile,
--     NTILE(4) OVER (ORDER BY LogEntriesCreated) AS LogsQuartile
--   FROM UserActivity
-- )
-- SELECT
--   userId,
--   userName,
--   CASE WHEN AttendedQuartile = 1 THEN 'Low'
--        WHEN AttendedQuartile = 2 THEN 'Medium'
--        WHEN AttendedQuartile = 3 THEN 'High'
--        ELSE 'Very High' END AS SessionsAttendedLevel,
--   CASE WHEN ConductedQuartile = 1 THEN 'Low'
--        WHEN ConductedQuartile = 2 THEN 'Medium'
--        WHEN ConductedQuartile = 3 THEN 'High'
--        ELSE 'Very High' END AS SessionsConductedLevel,
--   CASE WHEN CertificatesQuartile = 1 THEN 'Low'
--        WHEN CertificatesQuartile = 2 THEN 'Medium'
--        WHEN CertificatesQuartile = 3 THEN 'High'
--        ELSE 'Very High' END AS CertificatesEarnedLevel,
--   CASE WHEN LogsQuartile = 1 THEN 'Low'
--        WHEN LogsQuartile = 2 THEN 'Medium'
--        WHEN LogsQuartile = 3 THEN 'High'
--        ELSE 'Very High' END AS LogEntriesCreatedLevel
-- FROM ActivityQuartiles
-- ORDER BY userId ASC, userName ASC;