1
import { sql } from "drizzle-orm";
2
import { check, index, integer, primaryKey, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";
3
 
4
import type { CalendarComponentType, DavChangeType, DavResourceKind } from "@/worker/db/types";
5
 
6
export const calMeta = sqliteTable("meta", {
7
  key: text("key").primaryKey(),
8
  value: text("value").notNull(),
9
  updatedAtMs: integer("updated_at_ms").notNull(),
10
});
11
 
12
export const calendars = sqliteTable(
13
  "calendars",
14
  {
15
    id: text("id").primaryKey(),
16
    name: text("name").notNull(),
17
    displayName: text("display_name").notNull(),
18
    description: text("description"),
19
    timezoneIcal: text("timezone_ical"),
20
    color: text("color"),
21
    orderIndex: integer("order_index").notNull().default(0),
22
    createdAtMs: integer("created_at_ms").notNull(),
23
    modifiedAtMs: integer("modified_at_ms").notNull(),
24
    syncSeq: integer("sync_seq").notNull().default(0),
25
  },
26
  (table) => [
27
    uniqueIndex("calendars_name_unique").on(table.name),
28
    index("calendars_order_idx").on(table.orderIndex),
29
    index("calendars_modified_idx").on(table.modifiedAtMs),
30
  ],
31
);
32
 
33
export const calendarObjects = sqliteTable(
34
  "calendar_objects",
35
  {
36
    id: text("id").primaryKey(),
37
    calendarId: text("calendar_id")
38
      .notNull()
39
      .references(() => calendars.id, { onDelete: "cascade" }),
40
    name: text("name").notNull(),
41
    uid: text("uid").notNull(),
42
    componentType: text("component_type").notNull().$type<CalendarComponentType>(),
43
    body: text("body").notNull(),
44
    etag: text("etag").notNull(),
45
    size: integer("size").notNull(),
46
    createdAtMs: integer("created_at_ms").notNull(),
47
    modifiedAtMs: integer("modified_at_ms").notNull(),
48
    version: integer("version").notNull().default(1),
49
  },
50
  (table) => [
51
    uniqueIndex("calendar_objects_calendar_name_unique").on(table.calendarId, table.name),
52
    uniqueIndex("calendar_objects_calendar_uid_unique").on(table.calendarId, table.uid),
53
    index("calendar_objects_calendar_modified_idx").on(table.calendarId, table.modifiedAtMs),
54
    index("calendar_objects_component_idx").on(table.calendarId, table.componentType),
55
    check("calendar_objects_component_check", sql`${table.componentType} in ('VEVENT', 'VTODO', 'VJOURNAL')`),
56
  ],
57
);
58
 
59
export const calendarIndex = sqliteTable(
60
  "calendar_index",
61
  {
62
    objectId: text("object_id")
63
      .primaryKey()
64
      .references(() => calendarObjects.id, { onDelete: "cascade" }),
65
    calendarId: text("calendar_id")
66
      .notNull()
67
      .references(() => calendars.id, { onDelete: "cascade" }),
68
    uid: text("uid").notNull(),
69
    componentType: text("component_type").notNull().$type<CalendarComponentType>(),
70
    dtstartMs: integer("dtstart_ms"),
71
    dtendMs: integer("dtend_ms"),
72
    dueMs: integer("due_ms"),
73
    completedMs: integer("completed_ms"),
74
    summary: text("summary"),
75
    hasRecurrence: integer("has_recurrence", { mode: "boolean" }).notNull().default(false),
76
    recurrenceMinMs: integer("recurrence_min_ms"),
77
    recurrenceMaxMs: integer("recurrence_max_ms"),
78
  },
79
  (table) => [
80
    index("calendar_index_timerange_idx").on(table.calendarId, table.componentType, table.dtstartMs, table.dtendMs),
81
    index("calendar_index_due_idx").on(table.calendarId, table.dueMs),
82
    index("calendar_index_completed_idx").on(table.calendarId, table.completedMs),
83
    index("calendar_index_uid_idx").on(table.calendarId, table.uid),
84
    index("calendar_index_recurrence_idx").on(
85
      table.calendarId,
86
      table.hasRecurrence,
87
      table.recurrenceMinMs,
88
      table.recurrenceMaxMs,
89
    ),
90
    check("calendar_index_component_check", sql`${table.componentType} in ('VEVENT', 'VTODO', 'VJOURNAL')`),
91
  ],
92
);
93
 
94
export const calendarDeadProps = sqliteTable(
95
  "calendar_dead_props",
96
  {
97
    resourceKind: text("resource_kind").notNull().$type<Extract<DavResourceKind, "home" | "calendar" | "object">>(),
98
    resourceId: text("resource_id").notNull(),
99
    nsUri: text("ns_uri").notNull(),
100
    localName: text("local_name").notNull(),
101
    xmlValue: text("xml_value").notNull(),
102
  },
103
  (table) => [
104
    primaryKey({ columns: [table.resourceKind, table.resourceId, table.nsUri, table.localName] }),
105
    check("calendar_dead_props_kind_check", sql`${table.resourceKind} in ('home', 'calendar', 'object')`),
106
  ],
107
);
108
 
109
export const calendarChanges = sqliteTable(
110
  "calendar_changes",
111
  {
112
    seq: integer("seq").primaryKey({ autoIncrement: true }),
113
    calendarId: text("calendar_id").references(() => calendars.id, { onDelete: "cascade" }),
114
    href: text("href").notNull(),
115
    changeType: text("change_type").notNull().$type<DavChangeType>(),
116
    changedAtMs: integer("changed_at_ms").notNull(),
117
  },
118
  (table) => [
119
    index("calendar_changes_calendar_seq_idx").on(table.calendarId, table.seq),
120
    index("calendar_changes_changed_idx").on(table.changedAtMs),
121
    index("calendar_changes_href_idx").on(table.href),
122
    check("calendar_changes_type_check", sql`${table.changeType} in ('created', 'updated', 'deleted')`),
123
  ],
124
);
125
 
126
export type CalMetaRow = typeof calMeta.$inferSelect;
127
export type CalendarRow = typeof calendars.$inferSelect;
128
export type CalendarObjectRow = typeof calendarObjects.$inferSelect;
129
export type CalendarIndexRow = typeof calendarIndex.$inferSelect;
130
export type CalendarDeadPropRow = typeof calendarDeadProps.$inferSelect;
131
export type CalendarChangeRow = typeof calendarChanges.$inferSelect;