import { sql } from "drizzle-orm";
import { check, index, integer, primaryKey, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";

import type { DavChangeType, DavResourceKind } from "@/worker/db/types";

export const cardMeta = sqliteTable("meta", {
  key: text("key").primaryKey(),
  value: text("value").notNull(),
  updatedAtMs: integer("updated_at_ms").notNull(),
});

export const addressbooks = sqliteTable(
  "addressbooks",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    displayName: text("display_name").notNull(),
    description: text("description"),
    createdAtMs: integer("created_at_ms").notNull(),
    modifiedAtMs: integer("modified_at_ms").notNull(),
    syncSeq: integer("sync_seq").notNull().default(0),
  },
  (table) => [
    uniqueIndex("addressbooks_name_unique").on(table.name),
    index("addressbooks_modified_idx").on(table.modifiedAtMs),
  ],
);

export const addressObjects = sqliteTable(
  "address_objects",
  {
    id: text("id").primaryKey(),
    addressbookId: text("addressbook_id")
      .notNull()
      .references(() => addressbooks.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    uid: text("uid").notNull(),
    body: text("body").notNull(),
    etag: text("etag").notNull(),
    size: integer("size").notNull(),
    createdAtMs: integer("created_at_ms").notNull(),
    modifiedAtMs: integer("modified_at_ms").notNull(),
    version: integer("version").notNull().default(1),
  },
  (table) => [
    uniqueIndex("address_objects_book_name_unique").on(table.addressbookId, table.name),
    uniqueIndex("address_objects_book_uid_unique").on(table.addressbookId, table.uid),
    index("address_objects_book_modified_idx").on(table.addressbookId, table.modifiedAtMs),
  ],
);

export const addressIndex = sqliteTable(
  "address_index",
  {
    objectId: text("object_id")
      .primaryKey()
      .references(() => addressObjects.id, { onDelete: "cascade" }),
    addressbookId: text("addressbook_id")
      .notNull()
      .references(() => addressbooks.id, { onDelete: "cascade" }),
    uid: text("uid").notNull(),
    fn: text("fn"),
    nFamily: text("n_family"),
    nGiven: text("n_given"),
    org: text("org"),
    emails: text("emails_json", { mode: "json" }).notNull().$type<string[]>(),
    tels: text("tels_json", { mode: "json" }).notNull().$type<string[]>(),
  },
  (table) => [
    index("address_index_uid_idx").on(table.addressbookId, table.uid),
    index("address_index_fn_idx").on(table.addressbookId, table.fn),
    index("address_index_family_idx").on(table.addressbookId, table.nFamily),
    index("address_index_given_idx").on(table.addressbookId, table.nGiven),
    index("address_index_org_idx").on(table.addressbookId, table.org),
  ],
);

export const addressDeadProps = sqliteTable(
  "address_dead_props",
  {
    resourceKind: text("resource_kind").notNull().$type<Extract<DavResourceKind, "home" | "addressbook" | "object">>(),
    resourceId: text("resource_id").notNull(),
    nsUri: text("ns_uri").notNull(),
    localName: text("local_name").notNull(),
    xmlValue: text("xml_value").notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.resourceKind, table.resourceId, table.nsUri, table.localName] }),
    check("address_dead_props_kind_check", sql`${table.resourceKind} in ('home', 'addressbook', 'object')`),
  ],
);

export const addressChanges = sqliteTable(
  "address_changes",
  {
    seq: integer("seq").primaryKey({ autoIncrement: true }),
    addressbookId: text("addressbook_id").references(() => addressbooks.id, { onDelete: "cascade" }),
    href: text("href").notNull(),
    changeType: text("change_type").notNull().$type<DavChangeType>(),
    changedAtMs: integer("changed_at_ms").notNull(),
  },
  (table) => [
    index("address_changes_book_seq_idx").on(table.addressbookId, table.seq),
    index("address_changes_changed_idx").on(table.changedAtMs),
    index("address_changes_href_idx").on(table.href),
    check("address_changes_type_check", sql`${table.changeType} in ('created', 'updated', 'deleted')`),
  ],
);

export type CardMetaRow = typeof cardMeta.$inferSelect;
export type AddressbookRow = typeof addressbooks.$inferSelect;
export type AddressObjectRow = typeof addressObjects.$inferSelect;
export type AddressIndexRow = typeof addressIndex.$inferSelect;
export type AddressDeadPropRow = typeof addressDeadProps.$inferSelect;
export type AddressChangeRow = typeof addressChanges.$inferSelect;