1
import { sql } from "drizzle-orm";
2
import { check, index, integer, primaryKey, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";
3
 
4
import type { DavChangeType, DavResourceKind } from "@/worker/db/types";
5
 
6
export const cardMeta = sqliteTable("meta", {
7
  key: text("key").primaryKey(),
8
  value: text("value").notNull(),
9
  updatedAtMs: integer("updated_at_ms").notNull(),
10
});
11
 
12
export const addressbooks = sqliteTable(
13
  "addressbooks",
14
  {
15
    id: text("id").primaryKey(),
16
    name: text("name").notNull(),
17
    displayName: text("display_name").notNull(),
18
    description: text("description"),
19
    createdAtMs: integer("created_at_ms").notNull(),
20
    modifiedAtMs: integer("modified_at_ms").notNull(),
21
    syncSeq: integer("sync_seq").notNull().default(0),
22
  },
23
  (table) => [
24
    uniqueIndex("addressbooks_name_unique").on(table.name),
25
    index("addressbooks_modified_idx").on(table.modifiedAtMs),
26
  ],
27
);
28
 
29
export const addressObjects = sqliteTable(
30
  "address_objects",
31
  {
32
    id: text("id").primaryKey(),
33
    addressbookId: text("addressbook_id")
34
      .notNull()
35
      .references(() => addressbooks.id, { onDelete: "cascade" }),
36
    name: text("name").notNull(),
37
    uid: text("uid").notNull(),
38
    body: text("body").notNull(),
39
    etag: text("etag").notNull(),
40
    size: integer("size").notNull(),
41
    createdAtMs: integer("created_at_ms").notNull(),
42
    modifiedAtMs: integer("modified_at_ms").notNull(),
43
    version: integer("version").notNull().default(1),
44
  },
45
  (table) => [
46
    uniqueIndex("address_objects_book_name_unique").on(table.addressbookId, table.name),
47
    uniqueIndex("address_objects_book_uid_unique").on(table.addressbookId, table.uid),
48
    index("address_objects_book_modified_idx").on(table.addressbookId, table.modifiedAtMs),
49
  ],
50
);
51
 
52
export const addressIndex = sqliteTable(
53
  "address_index",
54
  {
55
    objectId: text("object_id")
56
      .primaryKey()
57
      .references(() => addressObjects.id, { onDelete: "cascade" }),
58
    addressbookId: text("addressbook_id")
59
      .notNull()
60
      .references(() => addressbooks.id, { onDelete: "cascade" }),
61
    uid: text("uid").notNull(),
62
    fn: text("fn"),
63
    nFamily: text("n_family"),
64
    nGiven: text("n_given"),
65
    org: text("org"),
66
    emails: text("emails_json", { mode: "json" }).notNull().$type<string[]>(),
67
    tels: text("tels_json", { mode: "json" }).notNull().$type<string[]>(),
68
  },
69
  (table) => [
70
    index("address_index_uid_idx").on(table.addressbookId, table.uid),
71
    index("address_index_fn_idx").on(table.addressbookId, table.fn),
72
    index("address_index_family_idx").on(table.addressbookId, table.nFamily),
73
    index("address_index_given_idx").on(table.addressbookId, table.nGiven),
74
    index("address_index_org_idx").on(table.addressbookId, table.org),
75
  ],
76
);
77
 
78
export const addressDeadProps = sqliteTable(
79
  "address_dead_props",
80
  {
81
    resourceKind: text("resource_kind").notNull().$type<Extract<DavResourceKind, "home" | "addressbook" | "object">>(),
82
    resourceId: text("resource_id").notNull(),
83
    nsUri: text("ns_uri").notNull(),
84
    localName: text("local_name").notNull(),
85
    xmlValue: text("xml_value").notNull(),
86
  },
87
  (table) => [
88
    primaryKey({ columns: [table.resourceKind, table.resourceId, table.nsUri, table.localName] }),
89
    check("address_dead_props_kind_check", sql`${table.resourceKind} in ('home', 'addressbook', 'object')`),
90
  ],
91
);
92
 
93
export const addressChanges = sqliteTable(
94
  "address_changes",
95
  {
96
    seq: integer("seq").primaryKey({ autoIncrement: true }),
97
    addressbookId: text("addressbook_id").references(() => addressbooks.id, { onDelete: "cascade" }),
98
    href: text("href").notNull(),
99
    changeType: text("change_type").notNull().$type<DavChangeType>(),
100
    changedAtMs: integer("changed_at_ms").notNull(),
101
  },
102
  (table) => [
103
    index("address_changes_book_seq_idx").on(table.addressbookId, table.seq),
104
    index("address_changes_changed_idx").on(table.changedAtMs),
105
    index("address_changes_href_idx").on(table.href),
106
    check("address_changes_type_check", sql`${table.changeType} in ('created', 'updated', 'deleted')`),
107
  ],
108
);
109
 
110
export type CardMetaRow = typeof cardMeta.$inferSelect;
111
export type AddressbookRow = typeof addressbooks.$inferSelect;
112
export type AddressObjectRow = typeof addressObjects.$inferSelect;
113
export type AddressIndexRow = typeof addressIndex.$inferSelect;
114
export type AddressDeadPropRow = typeof addressDeadProps.$inferSelect;
115
export type AddressChangeRow = typeof addressChanges.$inferSelect;