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

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

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

export const calendars = sqliteTable(
  "calendars",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    displayName: text("display_name").notNull(),
    description: text("description"),
    timezoneIcal: text("timezone_ical"),
    color: text("color"),
    orderIndex: integer("order_index").notNull().default(0),
    createdAtMs: integer("created_at_ms").notNull(),
    modifiedAtMs: integer("modified_at_ms").notNull(),
    syncSeq: integer("sync_seq").notNull().default(0),
  },
  (table) => [
    uniqueIndex("calendars_name_unique").on(table.name),
    index("calendars_order_idx").on(table.orderIndex),
    index("calendars_modified_idx").on(table.modifiedAtMs),
  ],
);

export const calendarObjects = sqliteTable(
  "calendar_objects",
  {
    id: text("id").primaryKey(),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    uid: text("uid").notNull(),
    componentType: text("component_type").notNull().$type<CalendarComponentType>(),
    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("calendar_objects_calendar_name_unique").on(table.calendarId, table.name),
    uniqueIndex("calendar_objects_calendar_uid_unique").on(table.calendarId, table.uid),
    index("calendar_objects_calendar_modified_idx").on(table.calendarId, table.modifiedAtMs),
    index("calendar_objects_component_idx").on(table.calendarId, table.componentType),
    check("calendar_objects_component_check", sql`${table.componentType} in ('VEVENT', 'VTODO', 'VJOURNAL')`),
  ],
);

export const calendarIndex = sqliteTable(
  "calendar_index",
  {
    objectId: text("object_id")
      .primaryKey()
      .references(() => calendarObjects.id, { onDelete: "cascade" }),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    uid: text("uid").notNull(),
    componentType: text("component_type").notNull().$type<CalendarComponentType>(),
    dtstartMs: integer("dtstart_ms"),
    dtendMs: integer("dtend_ms"),
    dueMs: integer("due_ms"),
    completedMs: integer("completed_ms"),
    summary: text("summary"),
    hasRecurrence: integer("has_recurrence", { mode: "boolean" }).notNull().default(false),
    recurrenceMinMs: integer("recurrence_min_ms"),
    recurrenceMaxMs: integer("recurrence_max_ms"),
  },
  (table) => [
    index("calendar_index_timerange_idx").on(table.calendarId, table.componentType, table.dtstartMs, table.dtendMs),
    index("calendar_index_due_idx").on(table.calendarId, table.dueMs),
    index("calendar_index_completed_idx").on(table.calendarId, table.completedMs),
    index("calendar_index_uid_idx").on(table.calendarId, table.uid),
    index("calendar_index_recurrence_idx").on(
      table.calendarId,
      table.hasRecurrence,
      table.recurrenceMinMs,
      table.recurrenceMaxMs,
    ),
    check("calendar_index_component_check", sql`${table.componentType} in ('VEVENT', 'VTODO', 'VJOURNAL')`),
  ],
);

export const calendarDeadProps = sqliteTable(
  "calendar_dead_props",
  {
    resourceKind: text("resource_kind").notNull().$type<Extract<DavResourceKind, "home" | "calendar" | "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("calendar_dead_props_kind_check", sql`${table.resourceKind} in ('home', 'calendar', 'object')`),
  ],
);

export const calendarChanges = sqliteTable(
  "calendar_changes",
  {
    seq: integer("seq").primaryKey({ autoIncrement: true }),
    calendarId: text("calendar_id").references(() => calendars.id, { onDelete: "cascade" }),
    href: text("href").notNull(),
    changeType: text("change_type").notNull().$type<DavChangeType>(),
    changedAtMs: integer("changed_at_ms").notNull(),
  },
  (table) => [
    index("calendar_changes_calendar_seq_idx").on(table.calendarId, table.seq),
    index("calendar_changes_changed_idx").on(table.changedAtMs),
    index("calendar_changes_href_idx").on(table.href),
    check("calendar_changes_type_check", sql`${table.changeType} in ('created', 'updated', 'deleted')`),
  ],
);

export type CalMetaRow = typeof calMeta.$inferSelect;
export type CalendarRow = typeof calendars.$inferSelect;
export type CalendarObjectRow = typeof calendarObjects.$inferSelect;
export type CalendarIndexRow = typeof calendarIndex.$inferSelect;
export type CalendarDeadPropRow = typeof calendarDeadProps.$inferSelect;
export type CalendarChangeRow = typeof calendarChanges.$inferSelect;