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

import type { DavScope, JsonObject } from "@/worker/db/types";

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

export const subjects = sqliteTable(
  "subjects",
  {
    id: text("id").primaryKey(),
    storageId: text("storage_id").notNull(),
    hostLabel: text("host_label").notNull(),
    email: text("email"),
    displayName: text("display_name"),
    createdAtMs: integer("created_at_ms").notNull(),
    lastLoginAtMs: integer("last_login_at_ms"),
    hostRotatedAtMs: integer("host_rotated_at_ms"),
    disabledAtMs: integer("disabled_at_ms"),
  },
  (table) => [
    uniqueIndex("subjects_storage_id_unique").on(table.storageId),
    uniqueIndex("subjects_host_label_unique").on(table.hostLabel),
    index("subjects_disabled_idx").on(table.disabledAtMs),
  ],
);

export const patProjections = sqliteTable(
  "pat_projections",
  {
    id: text("id").primaryKey(),
    subjectId: text("subject_id")
      .notNull()
      .references(() => subjects.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    scopes: text("scopes_json", { mode: "json" }).notNull().$type<DavScope[]>(),
    createdAtMs: integer("created_at_ms").notNull(),
    expiresAtMs: integer("expires_at_ms"),
    revokedAtMs: integer("revoked_at_ms"),
    lastUsedAtMs: integer("last_used_at_ms"),
  },
  (table) => [
    index("pat_projections_subject_created_idx").on(table.subjectId, table.createdAtMs),
    index("pat_projections_subject_active_idx").on(table.subjectId, table.revokedAtMs, table.expiresAtMs),
  ],
);

export const auditEvents = sqliteTable(
  "audit_events",
  {
    id: text("id").primaryKey(),
    subjectId: text("subject_id").references(() => subjects.id, { onDelete: "set null" }),
    eventType: text("event_type").notNull(),
    actorSubjectId: text("actor_subject_id"),
    createdAtMs: integer("created_at_ms").notNull(),
    data: text("data_json", { mode: "json" }).notNull().$type<JsonObject>(),
  },
  (table) => [
    index("audit_events_subject_created_idx").on(table.subjectId, table.createdAtMs),
    index("audit_events_type_created_idx").on(table.eventType, table.createdAtMs),
    index("audit_events_actor_created_idx").on(table.actorSubjectId, table.createdAtMs),
  ],
);

export type ControlPlaneMetaRow = typeof controlPlaneMeta.$inferSelect;
export type NewControlPlaneMetaRow = typeof controlPlaneMeta.$inferInsert;
export type SubjectRow = typeof subjects.$inferSelect;
export type NewSubjectRow = typeof subjects.$inferInsert;
export type PatProjectionRow = typeof patProjections.$inferSelect;
export type NewPatProjectionRow = typeof patProjections.$inferInsert;
export type AuditEventRow = typeof auditEvents.$inferSelect;
export type NewAuditEventRow = typeof auditEvents.$inferInsert;