Skip to content

Your favorite shift management application!

34.8 KB
Raw Blame
import {
  sqliteTable,
  text,
  integer,
  index,
  uniqueIndex,
} from "drizzle-orm/sqlite-core";
import { sql, relations } from "drizzle-orm";
import type {
  CalendarViewSettings,
  PersonalViewSettings,
} from "../view-settings";
import type { Capability } from "../permission-bundles";
import type { CustomFieldOption, CustomFieldType } from "../custom-fields";

// =====================================================
// Better Auth Tables (generated by @better-auth/cli)
// =====================================================

export const user = sqliteTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: integer("email_verified", { mode: "boolean" })
    .default(false)
    .notNull(),
  image: text("image"),
  createdAt: integer("created_at", { mode: "timestamp_ms" })
    .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
    .notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp_ms" })
    .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
    .$onUpdate(() => new Date())
    .notNull(),
  // Better Auth Admin Plugin fields
  role: text("role"),
  banned: integer("banned", { mode: "boolean" }).default(false),
  banReason: text("ban_reason"),
  banExpires: integer("ban_expires", { mode: "timestamp_ms" }),
  // Set on admin-created accounts; cleared once the user changes their password
  mustChangePassword: integer("must_change_password", { mode: "boolean" })
    .notNull()
    .default(false),
});

export const session = sqliteTable(
  "session",
  {
    id: text("id").primaryKey(),
    expiresAt: integer("expires_at", { mode: "timestamp_ms" }).notNull(),
    token: text("token").notNull().unique(),
    createdAt: integer("created_at", { mode: "timestamp_ms" })
      .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
      .notNull(),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" })
      .$onUpdate(() => new Date())
      .notNull(),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    // Better Auth Admin Plugin field
    impersonatedBy: text("impersonated_by"),
  },
  (table) => [index("session_userId_idx").on(table.userId)]
);

export const account = sqliteTable(
  "account",
  {
    id: text("id").primaryKey(),
    accountId: text("account_id").notNull(),
    providerId: text("provider_id").notNull(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    accessTokenExpiresAt: integer("access_token_expires_at", {
      mode: "timestamp_ms",
    }),
    refreshTokenExpiresAt: integer("refresh_token_expires_at", {
      mode: "timestamp_ms",
    }),
    scope: text("scope"),
    password: text("password"),
    createdAt: integer("created_at", { mode: "timestamp_ms" })
      .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
      .notNull(),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" })
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [index("account_userId_idx").on(table.userId)]
);

export const verification = sqliteTable("verification", {
  id: text("id").primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: integer("expires_at", { mode: "timestamp_ms" }).notNull(),
  createdAt: integer("created_at", { mode: "timestamp_ms" })
    .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
    .notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp_ms" }).$onUpdate(
    () => new Date()
  ),
});

// =====================================================
// BetterShift Application Tables
// =====================================================

export const calendars = sqliteTable(
  "calendars",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    name: text("name").notNull(),
    color: text("color").notNull().default("#3b82f6"),
    ownerId: text("owner_id").references(() => user.id, {
      onDelete: "set null",
    }),
    // Bundle granted to guests/subscribed users via the public-access link;
    // null = no guest access (replaces the old guestPermission enum below).
    // Not a typed FK — calendarPermissionBundles.calendarId already points
    // back at this table, and Drizzle/TS can't infer a two-table reference
    // cycle. Bundles can't be deleted while assigned (enforced at the app
    // layer), so integrity holds without a DB-level constraint here.
    guestBundleId: text("guest_bundle_id"),
    // Master switch for the whole shift-signup feature on this calendar.
    // Owner/admin only; off hides capacity, signup lists and badges everywhere.
    signupsEnabled: integer("signups_enabled", { mode: "boolean" })
      .notNull()
      .default(true),
    // Master switch for split shifts (multiple time ranges per shift/preset)
    // on this calendar. Owner/admin only; off hides extra ranges everywhere,
    // same contract as signupsEnabled above.
    splitShiftsEnabled: integer("split_shifts_enabled", { mode: "boolean" })
      .notNull()
      .default(false),
    // null = no own view; every user sees their personal view
    viewSettings: text("view_settings", {
      mode: "json",
    }).$type<CalendarViewSettings>(),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: integer("updated_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [index("calendars_ownerId_idx").on(table.ownerId)]
);

// Owner-defined, per-calendar capability bundle. Replaces the old fixed
// read/write/admin ladder — see lib/permission-bundles.ts for the catalog.
export const calendarPermissionBundles = sqliteTable(
  "calendar_permission_bundles",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    // Set for the four seeded bundles (read/contribute/manage/admin), null
    // once renamed. While set, the UI shows the translated standard name
    // instead of `name` (which stays as an English fallback).
    seedKey: text("seed_key", {
      enum: ["read", "contribute", "manage", "admin"],
    }),
    capabilities: text("capabilities", { mode: "json" })
      .$type<Capability[]>()
      .notNull(),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: integer("updated_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`)
      .$onUpdate(() => new Date()),
  },
  (table) => [
    index("calendar_permission_bundles_calendarId_idx").on(table.calendarId),
    uniqueIndex("calendar_permission_bundles_calendarId_seedKey_idx")
      .on(table.calendarId, table.seedKey)
      .where(sql`${table.seedKey} is not null`),
  ]
);

export const calendarShares = sqliteTable(
  "calendar_shares",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    // restrict, not cascade: deleting a bundle must never silently delete
    // shares — the app checks usage first and rejects deletion (see plan).
    bundleId: text("bundle_id")
      .notNull()
      .references(() => calendarPermissionBundles.id, {
        onDelete: "restrict",
      }),
    sharedBy: text("shared_by")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("calendar_shares_calendarId_idx").on(table.calendarId),
    index("calendar_shares_userId_idx").on(table.userId),
    index("calendar_shares_bundleId_idx").on(table.bundleId),
  ]
);

export const userCalendarSubscriptions = sqliteTable(
  "user_calendar_subscriptions",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    status: text("status", { enum: ["subscribed", "dismissed"] })
      .notNull()
      .default("subscribed"),
    source: text("source", { enum: ["guest", "shared"] })
      .notNull()
      .default("guest"),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: integer("updated_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`)
      .$onUpdate(() => new Date()),
  },
  (table) => [
    index("user_calendar_subscriptions_userId_idx").on(table.userId),
    index("user_calendar_subscriptions_calendarId_idx").on(table.calendarId),
    index("user_calendar_subscriptions_status_idx").on(table.status),
  ]
);

export const userPreferences = sqliteTable("user_preferences", {
  userId: text("user_id")
    .primaryKey()
    .references(() => user.id, { onDelete: "cascade" }),
  viewSettings: text("view_settings", {
    mode: "json",
  }).$type<PersonalViewSettings>(),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`)
    .$onUpdate(() => new Date()),
});

// Single-row table (id is always "default") for instance-wide toggles.
export const systemSettings = sqliteTable("system_settings", {
  id: text("id").primaryKey().default("default"),
  updateCheckEnabled: integer("update_check_enabled", { mode: "boolean" })
    .notNull()
    .default(true),
  updateBannerVisibility: text("update_banner_visibility", {
    enum: ["all", "admins"],
  })
    .notNull()
    .default("all"),
  // Nullable: existing rows from before this column existed must fall back
  // to ALLOW_GUEST_ACCESS (see lib/system-settings.ts), not silently to
  // false -- a NOT NULL DEFAULT would force that on every upgrade.
  allowGuestAccess: integer("allow_guest_access", { mode: "boolean" }),
  // Nullable without default: telemetryEnabled null means "not decided yet",
  // and no instance id exists until telemetry is actually on.
  telemetryEnabled: integer("telemetry_enabled", { mode: "boolean" }),
  telemetryInstanceId: text("telemetry_instance_id"),
  telemetryConsentedSchema: integer("telemetry_consented_schema"),
  telemetryDecidedAt: integer("telemetry_decided_at", { mode: "timestamp" }),
  // Drives the daily send: the scheduler compares it against now instead of
  // counting 24h from server start, so restarts do not reset the rhythm.
  telemetryLastSentAt: integer("telemetry_last_sent_at", { mode: "timestamp" }),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`)
    .$onUpdate(() => new Date()),
});

export const externalSyncs = sqliteTable("external_syncs", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  calendarId: text("calendar_id")
    .notNull()
    .references(() => calendars.id, { onDelete: "cascade" }),
  name: text("name").notNull(),
  syncType: text("sync_type").notNull().default("icloud"), // icloud, google, custom
  calendarUrl: text("calendar_url").notNull(),
  color: text("color").notNull().default("#3b82f6"),
  displayMode: text("display_mode").notNull().default("normal"),
  isHidden: integer("is_hidden", { mode: "boolean" }).notNull().default(false),
  hideFromStats: integer("hide_from_stats", { mode: "boolean" })
    .notNull()
    .default(false),
  autoSyncInterval: integer("auto_sync_interval").notNull().default(0), // 0 = manual, otherwise minutes
  isOneTimeImport: integer("is_one_time_import", { mode: "boolean" })
    .notNull()
    .default(false), // true for uploaded files (no sync), false for URLs (can sync)
  lastSyncedAt: integer("last_synced_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const shifts = sqliteTable("shifts", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  calendarId: text("calendar_id")
    .notNull()
    .references(() => calendars.id, { onDelete: "cascade" }),
  presetId: text("preset_id").references(() => shiftPresets.id, {
    onDelete: "set null",
  }),
  date: integer("date", { mode: "timestamp" }).notNull(),
  startTime: text("start_time").notNull(),
  endTime: text("end_time").notNull(),
  title: text("title").notNull(),
  color: text("color").notNull().default("#3b82f6"),
  notes: text("notes"),
  isAllDay: integer("is_all_day", { mode: "boolean" }).notNull().default(false),
  isSecondary: integer("is_secondary", { mode: "boolean" })
    .notNull()
    .default(false),
  // Target headcount for shift signups; null = unlimited
  signupCapacity: integer("signup_capacity"),
  externalEventId: text("external_event_id"),
  externalSyncId: text("external_sync_id").references(() => externalSyncs.id, {
    onDelete: "cascade",
  }),
  syncedFromExternal: integer("synced_from_external", { mode: "boolean" })
    .notNull()
    .default(false),
  // null = no known creator (pre-migration data, anonymous guest, or the
  // creating user was later deleted) — counts as "own" for anyone with the
  // Own capability, see hasOwnedCapability().
  createdBy: text("created_by").references(() => user.id, {
    onDelete: "set null",
  }),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const shiftPresets = sqliteTable("shift_presets", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  calendarId: text("calendar_id")
    .notNull()
    .references(() => calendars.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  startTime: text("start_time").notNull(),
  endTime: text("end_time").notNull(),
  color: text("color").notNull().default("#3b82f6"),
  notes: text("notes"),
  groupName: text("group_name"),
  isSecondary: integer("is_secondary", { mode: "boolean" })
    .notNull()
    .default(false),
  isAllDay: integer("is_all_day", { mode: "boolean" }).notNull().default(false),
  hideFromStats: integer("hide_from_stats", { mode: "boolean" })
    .notNull()
    .default(false),
  // Copied into signupCapacity on shifts created from this preset
  defaultSignupCapacity: integer("default_signup_capacity"),
  order: integer("order").notNull().default(0),
  // null = active. Archived presets can no longer create shifts, but shifts
  // already stamped from them stay untouched.
  archivedAt: integer("archived_at", { mode: "timestamp" }),
  // null = no known creator, see shifts.createdBy above.
  createdBy: text("created_by").references(() => user.id, {
    onDelete: "set null",
  }),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const shiftTimeSegments = sqliteTable(
  "shift_time_segments",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    shiftId: text("shift_id")
      .notNull()
      .references(() => shifts.id, { onDelete: "cascade" }),
    startTime: text("start_time").notNull(),
    endTime: text("end_time").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [index("shift_time_segments_shiftId_idx").on(table.shiftId)]
);

export const presetTimeSegments = sqliteTable(
  "preset_time_segments",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    presetId: text("preset_id")
      .notNull()
      .references(() => shiftPresets.id, { onDelete: "cascade" }),
    startTime: text("start_time").notNull(),
    endTime: text("end_time").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [index("preset_time_segments_presetId_idx").on(table.presetId)]
);

export const calendarCustomFields = sqliteTable(
  "calendar_custom_fields",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    // Stable API identifier: `shift.customFields[key]`. Immutable once created,
    // because renaming it would break every consumer reading that key.
    key: text("key").notNull(),
    label: text("label").notNull(),
    type: text("type").notNull().$type<CustomFieldType>(),
    // Only set for type "select"; null for every other type.
    options: text("options", { mode: "json" }).$type<CustomFieldOption[]>(),
    required: integer("required", { mode: "boolean" }).notNull().default(false),
    showInCalendar: integer("show_in_calendar", { mode: "boolean" })
      .notNull()
      .default(false),
    order: integer("order").notNull().default(0),
    // null = no known creator, see shifts.createdBy above.
    createdBy: text("created_by").references(() => user.id, {
      onDelete: "set null",
    }),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: integer("updated_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`)
      .$onUpdate(() => new Date()),
  },
  (table) => [
    index("calendar_custom_fields_calendarId_idx").on(table.calendarId),
    uniqueIndex("calendar_custom_fields_calendarId_key_idx").on(
      table.calendarId,
      table.key
    ),
  ]
);

export const shiftCustomFieldValues = sqliteTable(
  "shift_custom_field_values",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    shiftId: text("shift_id")
      .notNull()
      .references(() => shifts.id, { onDelete: "cascade" }),
    fieldId: text("field_id")
      .notNull()
      .references(() => calendarCustomFields.id, { onDelete: "cascade" }),
    value: text("value").notNull(),
  },
  (table) => [
    index("shift_custom_field_values_shiftId_idx").on(table.shiftId),
    uniqueIndex("shift_custom_field_values_shiftId_fieldId_idx").on(
      table.shiftId,
      table.fieldId
    ),
  ]
);

export const presetCustomFieldValues = sqliteTable(
  "preset_custom_field_values",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    presetId: text("preset_id")
      .notNull()
      .references(() => shiftPresets.id, { onDelete: "cascade" }),
    fieldId: text("field_id")
      .notNull()
      .references(() => calendarCustomFields.id, { onDelete: "cascade" }),
    value: text("value").notNull(),
  },
  (table) => [
    index("preset_custom_field_values_presetId_idx").on(table.presetId),
    uniqueIndex("preset_custom_field_values_presetId_fieldId_idx").on(
      table.presetId,
      table.fieldId
    ),
  ]
);

export const calendarNotes = sqliteTable("calendar_notes", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  calendarId: text("calendar_id")
    .notNull()
    .references(() => calendars.id, { onDelete: "cascade" }),
  date: integer("date", { mode: "timestamp" }).notNull(),
  note: text("note").notNull(),
  type: text("type").notNull().default("note"), // note or event
  color: text("color"), // for events only
  recurringPattern: text("recurring_pattern").notNull().default("none"), // none, custom-weeks, custom-months
  recurringInterval: integer("recurring_interval"), // for custom pattern (e.g., every 3 months)
  // null = no known creator, see shifts.createdBy above.
  createdBy: text("created_by").references(() => user.id, {
    onDelete: "set null",
  }),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const announcements = sqliteTable("announcements", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  title: text("title").notNull(),
  body: text("body"),
  tone: text("tone", { enum: ["info", "warning", "danger"] })
    .notNull()
    .default("info"),
  showOnAuth: integer("show_on_auth", { mode: "boolean" }).notNull().default(true),
  showOnDashboard: integer("show_on_dashboard", { mode: "boolean" })
    .notNull()
    .default(true),
  enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
  // Both null means the enabled flag alone decides visibility.
  startsAt: integer("starts_at", { mode: "timestamp" }),
  endsAt: integer("ends_at", { mode: "timestamp" }),
  // null = no known creator, see calendarNotes.createdBy above.
  createdBy: text("created_by").references(() => user.id, {
    onDelete: "set null",
  }),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`)
    .$onUpdate(() => new Date()),
});

export const syncLogs = sqliteTable("sync_logs", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  calendarId: text("calendar_id")
    .notNull()
    .references(() => calendars.id, { onDelete: "cascade" }),
  externalSyncId: text("external_sync_id")
    .notNull()
    .references(() => externalSyncs.id, { onDelete: "cascade" }),
  externalSyncName: text("external_sync_name").notNull(), // Denormalized for history
  status: text("status").notNull(), // success, error
  errorMessage: text("error_message"),
  shiftsCreated: integer("shifts_created").notNull().default(0),
  shiftsUpdated: integer("shifts_updated").notNull().default(0),
  shiftsDeleted: integer("shifts_deleted").notNull().default(0),
  syncType: text("sync_type").notNull().default("auto"), // auto, manual
  isRead: integer("is_read", { mode: "boolean" }).notNull().default(false),
  syncedAt: integer("synced_at", { mode: "timestamp" })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
});

export const auditLogs = sqliteTable(
  "audit_logs",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    userId: text("user_id").references(() => user.id, {
      onDelete: "set null",
    }),
    action: text("action").notNull(),
    resourceType: text("resource_type"),
    resourceId: text("resource_id"),
    metadata: text("metadata"), // JSON string
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    severity: text("severity").notNull().default("info"), // info, warning, error, critical
    isUserVisible: integer("is_user_visible", { mode: "boolean" })
      .notNull()
      .default(false),
    timestamp: integer("timestamp", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("audit_logs_userId_timestamp_idx").on(table.userId, table.timestamp),
    index("audit_logs_action_timestamp_idx").on(table.action, table.timestamp),
    index("audit_logs_userVisible_userId_timestamp_idx").on(
      table.isUserVisible,
      table.userId,
      table.timestamp
    ),
  ]
);

export const calendarAccessTokens = sqliteTable(
  "calendar_access_tokens",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    token: text("token").notNull().unique(), // base64url encoded, 43 chars
    name: text("name"), // Optional label (e.g., "Family Link")
    // restrict, not cascade: see calendarShares.bundleId above.
    bundleId: text("bundle_id")
      .notNull()
      .references(() => calendarPermissionBundles.id, {
        onDelete: "restrict",
      }),
    expiresAt: integer("expires_at", { mode: "timestamp" }), // null = never expires
    createdBy: text("created_by")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    lastUsedAt: integer("last_used_at", { mode: "timestamp" }),
    usageCount: integer("usage_count").notNull().default(0),
    isActive: integer("is_active", { mode: "boolean" }).notNull().default(true),
  },
  (table) => [
    index("calendar_access_tokens_token_idx").on(table.token),
    index("calendar_access_tokens_calendarId_isActive_idx").on(
      table.calendarId,
      table.isActive
    ),
    index("calendar_access_tokens_createdBy_idx").on(table.createdBy),
    index("calendar_access_tokens_bundleId_idx").on(table.bundleId),
  ]
);

export const calendarFeedTokens = sqliteTable(
  "calendar_feed_tokens",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    calendarId: text("calendar_id")
      .notNull()
      .references(() => calendars.id, { onDelete: "cascade" }),
    // null only while AUTH_ENABLED=false, where there are no accounts
    userId: text("user_id").references(() => user.id, { onDelete: "cascade" }),
    token: text("token").notNull().unique(),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    lastUsedAt: integer("last_used_at", { mode: "timestamp" }),
  },
  (table) => [
    index("calendar_feed_tokens_calendarId_userId_idx").on(
      table.calendarId,
      table.userId
    ),
  ]
);

export const shiftSignups = sqliteTable(
  "shift_signups",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    shiftId: text("shift_id")
      .notNull()
      .references(() => shifts.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    // Who created this signup: the user themself, or an admin/write-permission
    // user who assigned someone else. Distinct from userId for that reason.
    signedUpBy: text("signed_up_by")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("shift_signups_shiftId_idx").on(table.shiftId),
    index("shift_signups_userId_idx").on(table.userId),
    uniqueIndex("shift_signups_shiftId_userId_idx").on(
      table.shiftId,
      table.userId
    ),
  ]
);

export type Calendar = typeof calendars.$inferSelect;
export type NewCalendar = typeof calendars.$inferInsert;

export type CalendarAccessToken = typeof calendarAccessTokens.$inferSelect;
export type NewCalendarAccessToken = typeof calendarAccessTokens.$inferInsert;

export type CalendarFeedToken = typeof calendarFeedTokens.$inferSelect;

export type CalendarPermissionBundle =
  typeof calendarPermissionBundles.$inferSelect;
export type NewCalendarPermissionBundle =
  typeof calendarPermissionBundles.$inferInsert;

export type ExternalSync = typeof externalSyncs.$inferSelect;
export type NewExternalSync = typeof externalSyncs.$inferInsert;

export type AuditLog = typeof auditLogs.$inferSelect;
export type NewAuditLog = typeof auditLogs.$inferInsert;
export type Shift = typeof shifts.$inferSelect;
export type NewShift = typeof shifts.$inferInsert;
export type ShiftPreset = typeof shiftPresets.$inferSelect;
export type NewShiftPreset = typeof shiftPresets.$inferInsert;
export type ShiftTimeSegment = typeof shiftTimeSegments.$inferSelect;
export type NewShiftTimeSegment = typeof shiftTimeSegments.$inferInsert;
export type PresetTimeSegment = typeof presetTimeSegments.$inferSelect;
export type NewPresetTimeSegment = typeof presetTimeSegments.$inferInsert;
export type CalendarNote = typeof calendarNotes.$inferSelect;
export type NewCalendarNote = typeof calendarNotes.$inferInsert;
export type SyncLog = typeof syncLogs.$inferSelect;
export type NewSyncLog = typeof syncLogs.$inferInsert;
export type ShiftSignup = typeof shiftSignups.$inferSelect;
export type NewShiftSignup = typeof shiftSignups.$inferInsert;

// Better Auth types
export type User = typeof user.$inferSelect;
export type NewUser = typeof user.$inferInsert;
export type Session = typeof session.$inferSelect;
export type Account = typeof account.$inferSelect;
export type CalendarShare = typeof calendarShares.$inferSelect;
export type NewCalendarShare = typeof calendarShares.$inferInsert;
export type UserPreferences = typeof userPreferences.$inferSelect;
export type NewUserPreferences = typeof userPreferences.$inferInsert;
export type UserCalendarSubscription =
  typeof userCalendarSubscriptions.$inferSelect;
export type NewUserCalendarSubscription =
  typeof userCalendarSubscriptions.$inferInsert;

// =====================================================
// Drizzle Relations (for experimental joins)
// =====================================================

export const userRelations = relations(user, ({ one, many }) => ({
  preferences: one(userPreferences),
  sessions: many(session),
  accounts: many(account),
  ownedCalendars: many(calendars),
  calendarShares: many(calendarShares),
  calendarSubscriptions: many(userCalendarSubscriptions),
  feedTokens: many(calendarFeedTokens),
  auditLogs: many(auditLogs),
}));

export const sessionRelations = relations(session, ({ one }) => ({
  user: one(user, {
    fields: [session.userId],
    references: [user.id],
  }),
}));

export const accountRelations = relations(account, ({ one }) => ({
  user: one(user, {
    fields: [account.userId],
    references: [user.id],
  }),
}));

export const calendarsRelations = relations(calendars, ({ one, many }) => ({
  owner: one(user, {
    fields: [calendars.ownerId],
    references: [user.id],
  }),
  shares: many(calendarShares),
  subscriptions: many(userCalendarSubscriptions),
  accessTokens: many(calendarAccessTokens),
  feedTokens: many(calendarFeedTokens),
  permissionBundles: many(calendarPermissionBundles),
  guestBundle: one(calendarPermissionBundles, {
    fields: [calendars.guestBundleId],
    references: [calendarPermissionBundles.id],
  }),
  shifts: many(shifts),
  presets: many(shiftPresets),
  notes: many(calendarNotes),
  externalSyncs: many(externalSyncs),
  syncLogs: many(syncLogs),
}));

export const calendarPermissionBundlesRelations = relations(
  calendarPermissionBundles,
  ({ one, many }) => ({
    calendar: one(calendars, {
      fields: [calendarPermissionBundles.calendarId],
      references: [calendars.id],
    }),
    shares: many(calendarShares),
    accessTokens: many(calendarAccessTokens),
  })
);

export const shiftsRelations = relations(shifts, ({ one, many }) => ({
  calendar: one(calendars, {
    fields: [shifts.calendarId],
    references: [calendars.id],
  }),
  preset: one(shiftPresets, {
    fields: [shifts.presetId],
    references: [shiftPresets.id],
  }),
  signups: many(shiftSignups),
  segments: many(shiftTimeSegments),
  creator: one(user, {
    fields: [shifts.createdBy],
    references: [user.id],
  }),
}));

export const shiftPresetsRelations = relations(shiftPresets, ({ one, many }) => ({
  calendar: one(calendars, {
    fields: [shiftPresets.calendarId],
    references: [calendars.id],
  }),
  segments: many(presetTimeSegments),
  creator: one(user, {
    fields: [shiftPresets.createdBy],
    references: [user.id],
  }),
}));

export const shiftTimeSegmentsRelations = relations(
  shiftTimeSegments,
  ({ one }) => ({
    shift: one(shifts, {
      fields: [shiftTimeSegments.shiftId],
      references: [shifts.id],
    }),
  })
);

export const presetTimeSegmentsRelations = relations(
  presetTimeSegments,
  ({ one }) => ({
    preset: one(shiftPresets, {
      fields: [presetTimeSegments.presetId],
      references: [shiftPresets.id],
    }),
  })
);

export const calendarNotesRelations = relations(calendarNotes, ({ one }) => ({
  calendar: one(calendars, {
    fields: [calendarNotes.calendarId],
    references: [calendars.id],
  }),
  creator: one(user, {
    fields: [calendarNotes.createdBy],
    references: [user.id],
  }),
}));

export const shiftSignupsRelations = relations(shiftSignups, ({ one }) => ({
  shift: one(shifts, {
    fields: [shiftSignups.shiftId],
    references: [shifts.id],
  }),
  user: one(user, {
    fields: [shiftSignups.userId],
    references: [user.id],
  }),
  signedUpByUser: one(user, {
    fields: [shiftSignups.signedUpBy],
    references: [user.id],
  }),
}));

export const calendarSharesRelations = relations(calendarShares, ({ one }) => ({
  calendar: one(calendars, {
    fields: [calendarShares.calendarId],
    references: [calendars.id],
  }),
  user: one(user, {
    fields: [calendarShares.userId],
    references: [user.id],
  }),
  sharedByUser: one(user, {
    fields: [calendarShares.sharedBy],
    references: [user.id],
  }),
  bundle: one(calendarPermissionBundles, {
    fields: [calendarShares.bundleId],
    references: [calendarPermissionBundles.id],
  }),
}));

export const userCalendarSubscriptionsRelations = relations(
  userCalendarSubscriptions,
  ({ one }) => ({
    user: one(user, {
      fields: [userCalendarSubscriptions.userId],
      references: [user.id],
    }),
    calendar: one(calendars, {
      fields: [userCalendarSubscriptions.calendarId],
      references: [calendars.id],
    }),
  })
);

export const userPreferencesRelations = relations(
  userPreferences,
  ({ one }) => ({
    user: one(user, {
      fields: [userPreferences.userId],
      references: [user.id],
    }),
  })
);

export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
  user: one(user, {
    fields: [auditLogs.userId],
    references: [user.id],
  }),
}));

export const announcementsRelations = relations(announcements, ({ one }) => ({
  createdByUser: one(user, {
    fields: [announcements.createdBy],
    references: [user.id],
  }),
}));

export const calendarAccessTokensRelations = relations(
  calendarAccessTokens,
  ({ one }) => ({
    calendar: one(calendars, {
      fields: [calendarAccessTokens.calendarId],
      references: [calendars.id],
    }),
    createdByUser: one(user, {
      fields: [calendarAccessTokens.createdBy],
      references: [user.id],
    }),
    bundle: one(calendarPermissionBundles, {
      fields: [calendarAccessTokens.bundleId],
      references: [calendarPermissionBundles.id],
    }),
  })
);

export const calendarFeedTokensRelations = relations(
  calendarFeedTokens,
  ({ one }) => ({
    calendar: one(calendars, {
      fields: [calendarFeedTokens.calendarId],
      references: [calendars.id],
    }),
    user: one(user, {
      fields: [calendarFeedTokens.userId],
      references: [user.id],
    }),
  })
);