1  import { sqliteTable, text, integer, real } from "drizzle-orm/sqlite-core";
  2  
  3  export enum UserRole {
  4    Student = "student",
  5    Instructor = "instructor",
  6    Admin = "admin",
  7  }
  8  
  9  export enum CourseStatus {
 10    Draft = "draft",
 11    Published = "published",
 12    Archived = "archived",
 13  }
 14  
 15  export enum LessonProgressStatus {
 16    NotStarted = "not_started",
 17    InProgress = "in_progress",
 18    Completed = "completed",
 19  }
 20  
 21  export enum QuestionType {
 22    MultipleChoice = "multiple_choice",
 23    TrueFalse = "true_false",
 24  }
 25  
 26  export enum TeamMemberRole {
 27    Admin = "admin",
 28    Member = "member",
 29  }
 30  
 31  // ─── Tables ───
 32  
 33  export const users = sqliteTable("users", {
 34    id: integer("id").primaryKey({ autoIncrement: true }),
 35    name: text("name").notNull(),
 36    email: text("email").notNull().unique(),
 37    role: text("role").notNull().$type<UserRole>(),
 38    avatarUrl: text("avatar_url"),
 39    bio: text("bio"),
 40    createdAt: text("created_at")
 41      .notNull()
 42      .$defaultFn(() => new Date().toISOString()),
 43  });
 44  
 45  export const categories = sqliteTable("categories", {
 46    id: integer("id").primaryKey({ autoIncrement: true }),
 47    name: text("name").notNull(),
 48    slug: text("slug").notNull().unique(),
 49  });
 50  
 51  export const courses = sqliteTable("courses", {
 52    id: integer("id").primaryKey({ autoIncrement: true }),
 53    title: text("title").notNull(),
 54    slug: text("slug").notNull().unique(),
 55    description: text("description").notNull(),
 56    salesCopy: text("sales_copy"),
 57    instructorId: integer("instructor_id")
 58      .notNull()
 59      .references(() => users.id),
 60    categoryId: integer("category_id")
 61      .notNull()
 62      .references(() => categories.id),
 63    status: text("status").notNull().$type<CourseStatus>(),
 64    coverImageUrl: text("cover_image_url"),
 65    price: integer("price").notNull().default(0),
 66    pppEnabled: integer("ppp_enabled", { mode: "boolean" })
 67      .notNull()
 68      .default(true),
 69    createdAt: text("created_at")
 70      .notNull()
 71      .$defaultFn(() => new Date().toISOString()),
 72    updatedAt: text("updated_at")
 73      .notNull()
 74      .$defaultFn(() => new Date().toISOString()),
 75  });
 76  
 77  export const modules = sqliteTable("modules", {
 78    id: integer("id").primaryKey({ autoIncrement: true }),
 79    courseId: integer("course_id")
 80      .notNull()
 81      .references(() => courses.id),
 82    title: text("title").notNull(),
 83    position: integer("position").notNull(),
 84    createdAt: text("created_at")
 85      .notNull()
 86      .$defaultFn(() => new Date().toISOString()),
 87  });
 88  
 89  export const lessons = sqliteTable("lessons", {
 90    id: integer("id").primaryKey({ autoIncrement: true }),
 91    moduleId: integer("module_id")
 92      .notNull()
 93      .references(() => modules.id),
 94    title: text("title").notNull(),
 95    content: text("content"),
 96    videoUrl: text("video_url"),
 97    githubRepoUrl: text("github_repo_url"),
 98    position: integer("position").notNull(),
 99    durationMinutes: integer("duration_minutes"),
100    createdAt: text("created_at")
101      .notNull()
102      .$defaultFn(() => new Date().toISOString()),
103  });
104  
105  export const enrollments = sqliteTable("enrollments", {
106    id: integer("id").primaryKey({ autoIncrement: true }),
107    userId: integer("user_id")
108      .notNull()
109      .references(() => users.id),
110    courseId: integer("course_id")
111      .notNull()
112      .references(() => courses.id),
113    enrolledAt: text("enrolled_at")
114      .notNull()
115      .$defaultFn(() => new Date().toISOString()),
116    completedAt: text("completed_at"),
117  });
118  
119  export const lessonProgress = sqliteTable("lesson_progress", {
120    id: integer("id").primaryKey({ autoIncrement: true }),
121    userId: integer("user_id")
122      .notNull()
123      .references(() => users.id),
124    lessonId: integer("lesson_id")
125      .notNull()
126      .references(() => lessons.id),
127    status: text("status").notNull().$type<LessonProgressStatus>(),
128    completedAt: text("completed_at"),
129  });
130  
131  export const quizzes = sqliteTable("quizzes", {
132    id: integer("id").primaryKey({ autoIncrement: true }),
133    lessonId: integer("lesson_id")
134      .notNull()
135      .references(() => lessons.id),
136    title: text("title").notNull(),
137    passingScore: real("passing_score").notNull(),
138  });
139  
140  export const quizQuestions = sqliteTable("quiz_questions", {
141    id: integer("id").primaryKey({ autoIncrement: true }),
142    quizId: integer("quiz_id")
143      .notNull()
144      .references(() => quizzes.id),
145    questionText: text("question_text").notNull(),
146    questionType: text("question_type").notNull().$type<QuestionType>(),
147    position: integer("position").notNull(),
148  });
149  
150  export const quizOptions = sqliteTable("quiz_options", {
151    id: integer("id").primaryKey({ autoIncrement: true }),
152    questionId: integer("question_id")
153      .notNull()
154      .references(() => quizQuestions.id),
155    optionText: text("option_text").notNull(),
156    isCorrect: integer("is_correct", { mode: "boolean" }).notNull(),
157  });
158  
159  export const quizAttempts = sqliteTable("quiz_attempts", {
160    id: integer("id").primaryKey({ autoIncrement: true }),
161    userId: integer("user_id")
162      .notNull()
163      .references(() => users.id),
164    quizId: integer("quiz_id")
165      .notNull()
166      .references(() => quizzes.id),
167    score: real("score").notNull(),
168    passed: integer("passed", { mode: "boolean" }).notNull(),
169    attemptedAt: text("attempted_at")
170      .notNull()
171      .$defaultFn(() => new Date().toISOString()),
172  });
173  
174  export const quizAnswers = sqliteTable("quiz_answers", {
175    id: integer("id").primaryKey({ autoIncrement: true }),
176    attemptId: integer("attempt_id")
177      .notNull()
178      .references(() => quizAttempts.id),
179    questionId: integer("question_id")
180      .notNull()
181      .references(() => quizQuestions.id),
182    selectedOptionId: integer("selected_option_id")
183      .notNull()
184      .references(() => quizOptions.id),
185  });
186  
187  export const purchases = sqliteTable("purchases", {
188    id: integer("id").primaryKey({ autoIncrement: true }),
189    userId: integer("user_id")
190      .notNull()
191      .references(() => users.id),
192    courseId: integer("course_id")
193      .notNull()
194      .references(() => courses.id),
195    pricePaid: integer("price_paid").notNull(),
196    country: text("country"),
197    createdAt: text("created_at")
198      .notNull()
199      .$defaultFn(() => new Date().toISOString()),
200  });
201  
202  export const teams = sqliteTable("teams", {
203    id: integer("id").primaryKey({ autoIncrement: true }),
204    createdAt: text("created_at")
205      .notNull()
206      .$defaultFn(() => new Date().toISOString()),
207  });
208  
209  export const teamMembers = sqliteTable("team_members", {
210    id: integer("id").primaryKey({ autoIncrement: true }),
211    teamId: integer("team_id")
212      .notNull()
213      .references(() => teams.id),
214    userId: integer("user_id")
215      .notNull()
216      .references(() => users.id),
217    role: text("role").notNull().$type<TeamMemberRole>(),
218    createdAt: text("created_at")
219      .notNull()
220      .$defaultFn(() => new Date().toISOString()),
221  });
222  
223  export const coupons = sqliteTable("coupons", {
224    id: integer("id").primaryKey({ autoIncrement: true }),
225    teamId: integer("team_id")
226      .notNull()
227      .references(() => teams.id),
228    courseId: integer("course_id")
229      .notNull()
230      .references(() => courses.id),
231    code: text("code").notNull().unique(),
232    purchaseId: integer("purchase_id")
233      .notNull()
234      .references(() => purchases.id),
235    redeemedByUserId: integer("redeemed_by_user_id").references(() => users.id),
236    redeemedAt: text("redeemed_at"),
237    createdAt: text("created_at")
238      .notNull()
239      .$defaultFn(() => new Date().toISOString()),
240  });
241  
242  export const videoWatchEvents = sqliteTable("video_watch_events", {
243    id: integer("id").primaryKey({ autoIncrement: true }),
244    userId: integer("user_id")
245      .notNull()
246      .references(() => users.id),
247    lessonId: integer("lesson_id")
248      .notNull()
249      .references(() => lessons.id),
250    eventType: text("event_type").notNull(),
251    positionSeconds: real("position_seconds").notNull(),
252    createdAt: text("created_at")
253      .notNull()
254      .$defaultFn(() => new Date().toISOString()),
255  });