import { and, desc, eq, gt, inArray, or, sql } from "drizzle-orm";

import { randomHex } from "@/worker/auth/bytes";
import { addressbookHref as bookHref, addressObjectHref as objectHref } from "@/worker/carddav/paths";
import type { CardDavFilterTest, CardDavSearchFilter } from "@/worker/carddav/types";
import type { ParsedVCard } from "@/worker/carddav/vcard";
import {
  davSyncToken,
  objectNameFromCollectionHref,
  parseDavSyncToken,
  parseDavSyncTokenStrict,
  validDavCollectionName,
} from "@/worker/db/dav-collections";
import type { CardDavDoDb } from "@/worker/db/card-dav-do/client";
import {
  addressbooks,
  addressChanges,
  addressDeadProps,
  addressIndex,
  addressObjects,
  cardMeta,
  type AddressbookRow,
  type AddressDeadPropRow,
  type AddressIndexRow,
  type AddressObjectRow,
} from "@/worker/db/card-dav-do/schema";
import type { DavChangeType } from "@/worker/db/types";

export { createCardDavDoDb } from "./client";

export interface CardDavResource {
  kind: "home" | "addressbook" | "object";
  href: string;
  id: string;
  syncToken?: string;
  book?: AddressbookRow;
  object?: AddressObjectRow;
  index?: AddressIndexRow;
  deadProps: AddressDeadPropRow[];
}

function addressbookId(): string {
  return `abook_${randomHex(16)}`;
}

export function addressObjectId(): string {
  return `addr_${randomHex(16)}`;
}

export function validAddressbookName(name: string): boolean {
  return validDavCollectionName(name);
}

export function addressSyncToken(seq: number): string {
  return davSyncToken(seq);
}

export function addressIndexRow(
  objectId: string,
  addressbookId: string,
  parsed: ParsedVCard,
): typeof addressIndex.$inferInsert {
  return {
    objectId,
    addressbookId,
    uid: parsed.uid,
    fn: parsed.fn,
    nFamily: parsed.nFamily,
    nGiven: parsed.nGiven,
    org: parsed.org,
    emails: parsed.emails,
    tels: parsed.tels,
  };
}

export function ensureDefaultAddressbook(db: CardDavDoDb, subjectId: string, nowMs: number): AddressbookRow {
  const existing = addressbookByName(db, "default");
  if (existing) return existing;
  db.insert(cardMeta).values({ key: "subject_id", value: subjectId, updatedAtMs: nowMs }).onConflictDoNothing().run();
  db.insert(addressbooks)
    .values({
      id: addressbookId(),
      name: "default",
      displayName: "Default",
      description: null,
      createdAtMs: nowMs,
      modifiedAtMs: nowMs,
      syncSeq: 0,
    })
    .run();
  return addressbookByName(db, "default")!;
}

export function ensureAddressbook(
  db: CardDavDoDb,
  subjectId: string,
  nowMs: number,
  bookName: string,
): AddressbookRow | null {
  if (bookName === "default") return ensureDefaultAddressbook(db, subjectId, nowMs);
  return addressbookByName(db, bookName) ?? null;
}

export function listAddressbooks(db: CardDavDoDb, subjectId: string, nowMs: number): AddressbookRow[] {
  ensureDefaultAddressbook(db, subjectId, nowMs);
  return db.select().from(addressbooks).orderBy(addressbooks.createdAtMs).all();
}

export function addressbookById(db: CardDavDoDb, id: string): AddressbookRow | undefined {
  return db.query.addressbooks.findFirst({ where: eq(addressbooks.id, id) }).sync();
}

export function addressbookByName(db: CardDavDoDb, name: string): AddressbookRow | undefined {
  return db.query.addressbooks.findFirst({ where: eq(addressbooks.name, name) }).sync();
}

export function createAddressbook(
  db: CardDavDoDb,
  input: {
    subjectId: string;
    name: string;
    displayName: string;
    description: string | null;
    nowMs: number;
  },
): AddressbookRow {
  ensureDefaultAddressbook(db, input.subjectId, input.nowMs);
  const id = addressbookId();
  db.insert(addressbooks)
    .values({
      id,
      name: input.name,
      displayName: input.displayName,
      description: input.description,
      createdAtMs: input.nowMs,
      modifiedAtMs: input.nowMs,
      syncSeq: 0,
    })
    .run();
  return addressbookById(db, id)!;
}

export function updateAddressbook(
  db: CardDavDoDb,
  input: { id: string; displayName?: string; description?: string | null; nowMs: number },
): AddressbookRow | undefined {
  const existing = addressbookById(db, input.id);
  if (!existing) return undefined;
  db.update(addressbooks)
    .set({
      displayName: input.displayName ?? existing.displayName,
      description: input.description === undefined ? existing.description : input.description,
      modifiedAtMs: input.nowMs,
      syncSeq: sql`${addressbooks.syncSeq} + 1`,
    })
    .where(eq(addressbooks.id, input.id))
    .run();
  return addressbookById(db, input.id);
}

export function deleteAddressbook(db: CardDavDoDb, id: string): boolean {
  const existing = addressbookById(db, id);
  if (!existing) return false;
  const objectIds = db
    .select({ id: addressObjects.id })
    .from(addressObjects)
    .where(eq(addressObjects.addressbookId, id))
    .all();
  db.transaction((tx) => {
    tx.delete(addressDeadProps)
      .where(and(eq(addressDeadProps.resourceKind, "addressbook"), eq(addressDeadProps.resourceId, id)))
      .run();
    if (objectIds.length > 0) {
      tx.delete(addressDeadProps)
        .where(
          and(
            eq(addressDeadProps.resourceKind, "object"),
            inArray(
              addressDeadProps.resourceId,
              objectIds.map((row) => row.id),
            ),
          ),
        )
        .run();
    }
    tx.delete(addressbooks).where(eq(addressbooks.id, id)).run();
  });
  return true;
}

export function homeResource(db: CardDavDoDb): CardDavResource {
  return { kind: "home", href: "/addressbooks/", id: "home", deadProps: deadProps(db, "home", "home") };
}

export function bookResource(db: CardDavDoDb, book: AddressbookRow): CardDavResource {
  return {
    kind: "addressbook",
    href: bookHref(book.name),
    id: book.id,
    syncToken: addressSyncToken(latestChangeSeq(db)),
    book,
    deadProps: deadProps(db, "addressbook", book.id),
  };
}

export function objectResource(db: CardDavDoDb, book: AddressbookRow, object: AddressObjectRow): CardDavResource {
  return {
    kind: "object",
    href: objectHref(book.name, object.name),
    id: object.id,
    book,
    object,
    index: db.query.addressIndex.findFirst({ where: eq(addressIndex.objectId, object.id) }).sync(),
    deadProps: deadProps(db, "object", object.id),
  };
}

export interface ObjectResourcesResult {
  resources: CardDavResource[];
  truncated: boolean;
}

export function objectResources(
  db: CardDavDoDb,
  book: AddressbookRow,
  maxResults: number,
  filters: CardDavSearchFilter[] = [],
  filterTest: CardDavFilterTest = "anyof",
): ObjectResourcesResult {
  const { rows, truncated } = filteredObjects(db, book.id, maxResults, filters, filterTest);
  return { resources: rows.map((row) => objectResource(db, book, row)), truncated };
}

export function objectByName(db: CardDavDoDb, bookId: string, name: string): AddressObjectRow | undefined {
  return db.query.addressObjects
    .findFirst({ where: and(eq(addressObjects.addressbookId, bookId), eq(addressObjects.name, name)) })
    .sync();
}

export function objectByUid(db: CardDavDoDb, bookId: string, uid: string): AddressObjectRow | undefined {
  return db.query.addressObjects
    .findFirst({ where: and(eq(addressObjects.addressbookId, bookId), eq(addressObjects.uid, uid)) })
    .sync();
}

export function objectNameFromHref(book: AddressbookRow, href: string, acceptedOrigins?: string[]): string | null {
  return objectNameFromCollectionHref(bookHref(book.name), href, acceptedOrigins);
}

export function objectResourceFromHref(
  db: CardDavDoDb,
  book: AddressbookRow,
  href: string,
  acceptedOrigins?: string[],
): CardDavResource | null {
  const name = objectNameFromHref(book, href, acceptedOrigins);
  if (!name) return null;
  const object = objectByName(db, book.id, name);
  return object ? objectResource(db, book, object) : null;
}

export function latestChangeSeq(db: CardDavDoDb): number {
  return (
    db.select({ seq: addressChanges.seq }).from(addressChanges).orderBy(desc(addressChanges.seq)).limit(1).all()[0]
      ?.seq ?? 0
  );
}

export interface ChangesSinceResult {
  changes: {
    seq: number;
    addressbookId: string | null;
    href: string;
    changeType: DavChangeType;
    changedAtMs: number;
  }[];
  truncated: boolean;
}

export function changesSince(
  db: CardDavDoDb,
  book: AddressbookRow,
  token: string | null,
  maxResults: number,
): ChangesSinceResult {
  const since = parseDavSyncToken(token);
  const rows = db
    .select()
    .from(addressChanges)
    .where(and(eq(addressChanges.addressbookId, book.id), gt(addressChanges.seq, since)))
    .orderBy(addressChanges.seq)
    .limit(maxResults + 1)
    .all();
  const truncated = rows.length > maxResults;
  return { changes: truncated ? rows.slice(0, maxResults) : rows, truncated };
}

export function syncTokenSeq(token: string | null): number | null {
  return parseDavSyncTokenStrict(token);
}

export function recordChange(
  db: CardDavDoDb,
  bookId: string,
  href: string,
  changeType: DavChangeType,
  nowMs: number,
): void {
  db.insert(addressChanges).values({ addressbookId: bookId, href, changeType, changedAtMs: nowMs }).run();
  db.update(addressbooks)
    .set({ modifiedAtMs: nowMs, syncSeq: sql`${addressbooks.syncSeq} + 1` })
    .where(eq(addressbooks.id, bookId))
    .run();
}

function deadProps(db: CardDavDoDb, kind: "home" | "addressbook" | "object", resourceId: string): AddressDeadPropRow[] {
  return db
    .select()
    .from(addressDeadProps)
    .where(and(eq(addressDeadProps.resourceKind, kind), eq(addressDeadProps.resourceId, resourceId)))
    .all();
}

function filteredObjects(
  db: CardDavDoDb,
  bookId: string,
  maxResults: number,
  filters: CardDavSearchFilter[],
  filterTest: CardDavFilterTest = "anyof",
): { rows: AddressObjectRow[]; truncated: boolean } {
  if (filters.length === 0) {
    const rows = db
      .select()
      .from(addressObjects)
      .where(eq(addressObjects.addressbookId, bookId))
      .limit(maxResults + 1)
      .all();
    const truncated = rows.length > maxResults;
    return { rows: truncated ? rows.slice(0, maxResults) : rows, truncated };
  }
  const clauses = filters.map((filter) => filterClause(filter));
  // RFC 6352 10.5: combine prop-filter clauses with anyof (OR) or allof (AND).
  const combined = filterTest === "anyof" ? or(...clauses) : and(...clauses);
  const indexes = db
    .select()
    .from(addressIndex)
    .where(and(eq(addressIndex.addressbookId, bookId), combined))
    .limit(maxResults + 1)
    .all();
  const truncated = indexes.length > maxResults;
  const limited = truncated ? indexes.slice(0, maxResults) : indexes;
  if (limited.length === 0) return { rows: [], truncated };
  const rows = db
    .select()
    .from(addressObjects)
    .where(
      inArray(
        addressObjects.id,
        limited.map((row) => row.objectId),
      ),
    )
    .all();
  return { rows, truncated };
}

function containsCaseInsensitive(column: unknown, text: string) {
  return sql`instr(lower(coalesce(${column}, '')), ${text.toLowerCase()}) > 0`;
}

function notNullAndNotEmpty(column: unknown) {
  return sql`coalesce(${column}, '') != ''`;
}

function isNullOrEmpty(column: unknown) {
  return sql`coalesce(${column}, '') = ''`;
}

function jsonArrayHasValues(column: unknown) {
  return sql`coalesce(${column}, '[]') not in ('[]', '')`;
}

function jsonArrayIsEmpty(column: unknown) {
  return sql`coalesce(${column}, '[]') in ('[]', '')`;
}

function filterClause(filter: CardDavSearchFilter) {
  if (filter.kind === "exists") return existsClause(filter.prop, filter.defined);
  return textClause(filter.prop, filter.text);
}

function textClause(prop: CardDavSearchFilter["prop"], text: string) {
  switch (prop) {
    case "UID":
      return containsCaseInsensitive(addressIndex.uid, text);
    case "FN":
      return containsCaseInsensitive(addressIndex.fn, text);
    case "N":
      return or(
        containsCaseInsensitive(addressIndex.nFamily, text),
        containsCaseInsensitive(addressIndex.nGiven, text),
      );
    case "ORG":
      return containsCaseInsensitive(addressIndex.org, text);
    case "EMAIL":
      return containsCaseInsensitive(addressIndex.emails, text);
    case "TEL":
      return containsCaseInsensitive(addressIndex.tels, text);
  }
}

function existsClause(prop: CardDavSearchFilter["prop"], defined: boolean) {
  switch (prop) {
    case "UID":
      return defined ? notNullAndNotEmpty(addressIndex.uid) : isNullOrEmpty(addressIndex.uid);
    case "FN":
      return defined ? notNullAndNotEmpty(addressIndex.fn) : isNullOrEmpty(addressIndex.fn);
    case "N":
      return defined
        ? or(notNullAndNotEmpty(addressIndex.nFamily), notNullAndNotEmpty(addressIndex.nGiven))
        : and(isNullOrEmpty(addressIndex.nFamily), isNullOrEmpty(addressIndex.nGiven));
    case "ORG":
      return defined ? notNullAndNotEmpty(addressIndex.org) : isNullOrEmpty(addressIndex.org);
    case "EMAIL":
      return defined ? jsonArrayHasValues(addressIndex.emails) : jsonArrayIsEmpty(addressIndex.emails);
    case "TEL":
      return defined ? jsonArrayHasValues(addressIndex.tels) : jsonArrayIsEmpty(addressIndex.tels);
  }
}