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

import type { FileChangeType, LockDepth, LockScope, NodeKind, PendingUploadState } from "@/worker/db/types";
import type { PendingUploadExpectedState } from "@/worker/db/file-dav-do/types";

export const fileMeta = sqliteTable("meta", {
  key: text("key").primaryKey(),
  value: text("value").notNull(),
});

export const blobs = sqliteTable(
  "blobs",
  {
    blobId: text("blob_id").primaryKey(),
    blobKey: text("blob_key").notNull(),
    size: integer("size").notNull(),
    r2Etag: text("r2_etag"),
    sha256: text("sha256"),
    refcount: integer("refcount").notNull().default(0),
    createdAtMs: integer("created_at_ms").notNull(),
  },
  (table) => [
    uniqueIndex("blobs_key_unique").on(table.blobKey),
    index("blobs_refcount_idx").on(table.refcount),
    index("blobs_created_idx").on(table.createdAtMs),
  ],
);

export const nodes = sqliteTable(
  "nodes",
  {
    id: text("id").primaryKey(),
    parentId: text("parent_id").references((): AnySQLiteColumn => nodes.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    kind: text("kind").notNull().$type<NodeKind>(),
    blobId: text("blob_id").references(() => blobs.blobId, { onDelete: "restrict" }),
    size: integer("size").notNull().default(0),
    contentType: text("content_type"),
    contentLanguage: text("content_language"),
    etag: text("etag"),
    rootMarker: text("root_marker").$type<"root">(),
    createdAtMs: integer("created_at_ms").notNull(),
    modifiedAtMs: integer("modified_at_ms").notNull(),
    version: integer("version").notNull().default(1),
  },
  (table) => [
    uniqueIndex("nodes_parent_name_unique").on(table.parentId, table.name),
    uniqueIndex("nodes_root_marker_unique").on(table.rootMarker),
    index("nodes_parent_kind_idx").on(table.parentId, table.kind),
    index("nodes_blob_idx").on(table.blobId),
    index("nodes_modified_idx").on(table.modifiedAtMs),
    check("nodes_kind_check", sql`${table.kind} in ('collection', 'file')`),
    check("nodes_root_marker_check", sql`${table.rootMarker} is null or ${table.rootMarker} = 'root'`),
    check("nodes_collection_blob_check", sql`${table.kind} != 'collection' or ${table.blobId} is null`),
  ],
);

export const deadProps = sqliteTable(
  "dead_props",
  {
    nodeId: text("node_id")
      .notNull()
      .references(() => nodes.id, { onDelete: "cascade" }),
    nsUri: text("ns_uri").notNull(),
    localName: text("local_name").notNull(),
    xmlValue: text("xml_value").notNull(),
  },
  (table) => [primaryKey({ columns: [table.nodeId, table.nsUri, table.localName] })],
);

export const locks = sqliteTable(
  "locks",
  {
    token: text("token").primaryKey(),
    rootNodeId: text("root_node_id").references(() => nodes.id, { onDelete: "cascade" }),
    rootPath: text("root_path").notNull(),
    ownerXml: text("owner_xml").notNull(),
    principalSubjectId: text("principal_subject_id").notNull(),
    scope: text("scope").notNull().$type<LockScope>(),
    depth: text("depth").notNull().$type<LockDepth>(),
    createdAtMs: integer("created_at_ms").notNull(),
    expiresAtMs: integer("expires_at_ms").notNull(),
  },
  (table) => [
    index("locks_root_node_idx").on(table.rootNodeId),
    index("locks_root_path_idx").on(table.rootPath),
    index("locks_expires_idx").on(table.expiresAtMs),
    index("locks_principal_idx").on(table.principalSubjectId),
    check("locks_scope_check", sql`${table.scope} in ('exclusive', 'shared')`),
    check("locks_depth_check", sql`${table.depth} in ('0', 'infinity')`),
  ],
);

export const pendingUploads = sqliteTable(
  "pending_uploads",
  {
    uploadId: text("upload_id").primaryKey(),
    nodeId: text("node_id").references(() => nodes.id, { onDelete: "set null" }),
    path: text("path").notNull(),
    blobId: text("blob_id").notNull(),
    blobKey: text("blob_key").notNull(),
    createdAtMs: integer("created_at_ms").notNull(),
    expiresAtMs: integer("expires_at_ms").notNull(),
    state: text("state").notNull().$type<PendingUploadState>(),
    expectedState: text("expected_state_json", { mode: "json" }).$type<PendingUploadExpectedState>(),
  },
  (table) => [
    index("pending_uploads_state_expires_idx").on(table.state, table.expiresAtMs),
    index("pending_uploads_path_idx").on(table.path),
    index("pending_uploads_blob_idx").on(table.blobId),
    check("pending_uploads_state_check", sql`${table.state} in ('pending', 'committed', 'aborted')`),
  ],
);

export const changes = sqliteTable(
  "changes",
  {
    seq: integer("seq").primaryKey({ autoIncrement: true }),
    nodeId: text("node_id").references(() => nodes.id, { onDelete: "set null" }),
    href: text("href").notNull(),
    changeType: text("change_type").notNull().$type<FileChangeType>(),
    changedAtMs: integer("changed_at_ms").notNull(),
  },
  (table) => [
    index("changes_href_idx").on(table.href),
    index("changes_changed_idx").on(table.changedAtMs),
    check("changes_type_check", sql`${table.changeType} in ('created', 'updated', 'deleted', 'moved')`),
  ],
);

export type FileMetaRow = typeof fileMeta.$inferSelect;
export type NewFileMetaRow = typeof fileMeta.$inferInsert;
export type FileBlobRow = typeof blobs.$inferSelect;
export type NewFileBlobRow = typeof blobs.$inferInsert;
export type FileNodeRow = typeof nodes.$inferSelect;
export type NewFileNodeRow = typeof nodes.$inferInsert;
export type FileDeadPropRow = typeof deadProps.$inferSelect;
export type NewFileDeadPropRow = typeof deadProps.$inferInsert;
export type FileLockRow = typeof locks.$inferSelect;
export type NewFileLockRow = typeof locks.$inferInsert;
export type PendingUploadRow = typeof pendingUploads.$inferSelect;
export type NewPendingUploadRow = typeof pendingUploads.$inferInsert;
export type FileChangeRow = typeof changes.$inferSelect;