import { expect, test } from "vitest";
import {
  CatalogLogicalRelationshipService,
  LogicalRelationshipDuplicateError,
  LogicalRelationshipSchemaStaleError,
  LogicalRelationshipTargetNotUniqueError,
  LogicalRelationshipTypeIncompatibleError,
} from "../src/catalog/logical-relationship-service.js";
import { MemoryCatalogRepository } from "../src/catalog/memory-repository.js";
import type { ObservedSchemaSnapshot } from "../src/catalog/types.js";

const column = (
  tableName: string,
  name: string,
  ordinalPosition: number,
  dataType: string,
  primaryKeyPosition: number | null,
) => ({
  tableName, name, ordinalPosition, dataType, primaryKeyPosition,
  isNullable: false, defaultExpression: null, sourceComment: null,
});

function schema(
  tableNames: string[],
  columns: ObservedSchemaSnapshot["columns"],
  relationships: ObservedSchemaSnapshot["relationships"] = [],
): ObservedSchemaSnapshot {
  return {
    schemaVersion: 1,
    capabilities: { tables: "available", columns: "available", relationships: "available" },
    tables: tableNames.map((name) => ({ name, sourceComment: null })),
    columns,
    relationships,
  };
}

async function setup(snapshot: ObservedSchemaSnapshot) {
  const repository = new MemoryCatalogRepository();
  const database = await repository.create({
    workspaceId: "relationships", engine: "postgres", databaseName: "warehouse", schema: "public",
    binding: { transport: "postgres_direct", host: "db.internal", port: 5432, username: "reader" },
  });
  await repository.applySchemaSync(database.id, database.version, "all", [], snapshot);
  const service = new CatalogLogicalRelationshipService(repository);
  const context = (await repository.getLogicalRelationshipContext(database.id))!;
  const endpoint = (tableName: string, columnName: string) => context.endpoints.find((item) => (
    item.tableName === tableName && item.columnName === columnName
  ))!;
  return { repository, database, service, endpoint };
}

test("keeps excluded relationships across rebuild and recreates hard-deleted relationships", async () => {
  const { database, service, endpoint } = await setup(schema(
    ["users", "orders"],
    [column("users", "id", 1, "bigint", 1), column("orders", "id", 1, "bigint", 1),
      column("orders", "user_id", 2, "bigint", null)],
  ));
  const source = endpoint("orders", "user_id");
  const target = endpoint("users", "id");
  const manual = await service.addManual(database.id, source.columnId, target.columnId);
  expect(manual).toMatchObject({ origin: "manual", status: "active" });

  expect(await service.setStatus(database.id, manual.id, "excluded"))
    .toMatchObject({ status: "excluded" });
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 0, alreadyPresent: 0, excluded: 1, ambiguous: 0,
  });
  expect((await service.list(database.id)).filter((item) => item.origin !== "physical"))
    .toMatchObject([{ id: manual.id, origin: "manual", status: "excluded" }]);

  await service.deletePermanently(database.id, manual.id);
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 1, alreadyPresent: 0, excluded: 0, ambiguous: 0,
  });
  expect((await service.list(database.id)).filter((item) => item.origin !== "physical"))
    .toMatchObject([{ origin: "generated", status: "active" }]);
});

test("normalizes snake, kebab, and camel names without fuzzy matching", async () => {
  const { database, service } = await setup(schema(
    ["users", "events"],
    [column("users", "id", 1, "bigint", 1), column("events", "id", 1, "bigint", 1),
      column("events", "user_id", 2, "bigint", null),
      column("events", "user-id", 3, "bigint", null),
      column("events", "userId", 4, "bigint", null),
      column("events", "userid", 5, "bigint", null),
      column("events", "unrelated", 6, "bigint", null)],
  ));
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 4, alreadyPresent: 0, excluded: 0, ambiguous: 0,
  });
});

test("skips generic bare primary-key names while retaining table-qualified matches", async () => {
  const { database, service } = await setup(schema(
    ["users", "accounts", "events"],
    [column("users", "id", 1, "bigint", 1), column("accounts", "id", 1, "bigint", 1),
      column("events", "event_key", 1, "bigint", 1), column("events", "id", 2, "bigint", null),
      column("events", "user_id", 3, "bigint", null)],
  ));
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 1, alreadyPresent: 0, excluded: 0, ambiguous: 0,
  });
});

test("infers foreign keys from composite-primary-key sources", async () => {
  const { database, service } = await setup(schema(
    ["users", "groups", "memberships"],
    [column("users", "id", 1, "bigint", 1), column("groups", "id", 1, "bigint", 1),
      column("memberships", "user_id", 1, "bigint", 1),
      column("memberships", "group_id", 2, "bigint", 2)],
  ));
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 2, alreadyPresent: 0, excluded: 0, ambiguous: 0,
  });
});

test("maps time-key columns to the single primary key of dim_time", async () => {
  const { database, service } = await setup(schema(
    ["dim_time", "admissions"],
    [column("dim_time", "day_key", 1, "integer", 1),
      column("admissions", "id", 1, "bigint", 1),
      column("admissions", "admission_time_key", 2, "integer", null),
      column("admissions", "discharge_time_key", 3, "integer", null)],
  ));
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 2, alreadyPresent: 0, excluded: 0, ambiguous: 0,
  });
});

test("skips ambiguous targets and physical foreign-key pairs", async () => {
  const ambiguous = await setup(schema(
    ["user", "users", "events"],
    [column("user", "id", 1, "bigint", 1), column("users", "id", 1, "bigint", 1),
      column("events", "id", 1, "bigint", 1), column("events", "user_id", 2, "bigint", null)],
  ));
  await expect(ambiguous.service.rebuildGenerated(ambiguous.database.id)).resolves.toEqual({
    added: 0, alreadyPresent: 0, excluded: 0, ambiguous: 1,
  });

  const physical = await setup(schema(
    ["users", "orders"],
    [column("users", "id", 1, "bigint", 1), column("orders", "id", 1, "bigint", 1),
      column("orders", "user_id", 2, "bigint", null)],
    [{
      constraintName: "orders_user_id_fkey", sourceTableName: "orders", targetTableName: "users",
      updateRule: "NO ACTION", deleteRule: "NO ACTION", deferrable: false, initiallyDeferred: false,
      columns: [{ position: 1, sourceColumnName: "user_id", targetColumnName: "id" }],
    }],
  ));
  await expect(physical.service.rebuildGenerated(physical.database.id)).resolves.toEqual({
    added: 0, alreadyPresent: 1, excluded: 0, ambiguous: 0,
  });
  await expect(physical.service.addManual(
    physical.database.id,
    physical.endpoint("orders", "user_id").columnId,
    physical.endpoint("users", "id").columnId,
  )).rejects.toBeInstanceOf(LogicalRelationshipDuplicateError);
});

test("validates target uniqueness and canonical type compatibility for manual relationships", async () => {
  const { database, service, endpoint } = await setup(schema(
    ["users", "composite", "events"],
    [column("users", "id", 1, "bigint", 1),
      column("composite", "left_id", 1, "bigint", 1), column("composite", "right_id", 2, "bigint", 2),
      column("events", "id", 1, "bigint", 1), column("events", "user_id", 2, "integer", null),
      column("events", "composite_id", 3, "bigint", null)],
  ));
  await expect(service.addManual(
    database.id, endpoint("events", "composite_id").columnId, endpoint("composite", "left_id").columnId,
  )).rejects.toBeInstanceOf(LogicalRelationshipTargetNotUniqueError);
  await expect(service.addManual(
    database.id, endpoint("events", "user_id").columnId, endpoint("users", "id").columnId,
  )).rejects.toBeInstanceOf(LogicalRelationshipTypeIncompatibleError);
});

test("rebuild is additive when an existing generated relationship stops matching", async () => {
  const initial = schema(
    ["users", "orders"],
    [column("users", "id", 1, "bigint", 1), column("orders", "id", 1, "bigint", 1),
      column("orders", "user_id", 2, "bigint", null)],
  );
  const { repository, database, service } = await setup(initial);
  await service.rebuildGenerated(database.id);
  const changed = structuredClone(initial);
  changed.columns.find((item) => item.tableName === "orders" && item.name === "user_id")!.dataType = "text";
  await repository.applySchemaSync(database.id, database.version, "columns", [], changed);
  await expect(service.rebuildGenerated(database.id)).resolves.toEqual({
    added: 0, alreadyPresent: 0, excluded: 0, ambiguous: 0,
  });
  expect((await service.list(database.id)).filter((item) => item.origin === "generated")).toHaveLength(1);
});

test("refuses inference until the current database version has a full schema sync", async () => {
  const repository = new MemoryCatalogRepository();
  const database = await repository.create({
    workspaceId: "unsynced-relationships",
    engine: "postgres",
    databaseName: "warehouse",
    schema: "public",
    binding: { transport: "postgres_direct", host: "db.internal", port: 5432, username: "reader" },
  });
  const service = new CatalogLogicalRelationshipService(repository);

  await expect(service.rebuildGenerated(database.id))
    .rejects.toBeInstanceOf(LogicalRelationshipSchemaStaleError);
});