import Fastify from "fastify";
import cookie from "@fastify/cookie";
import cors from "@fastify/cors";
import multipart from "@fastify/multipart";
import { Server as SocketServer } from "socket.io";
import { stringify } from "csv-stringify/sync";
import { sql } from "kysely";
import { ZodError } from "zod";
import {
  generateAuthenticationOptions,
  generateRegistrationOptions,
  verifyAuthenticationResponse,
  verifyRegistrationResponse,
  type AuthenticationResponseJSON,
  type AuthenticatorTransportFuture,
  type RegistrationResponseJSON
} from "@simplewebauthn/server";
import {
  adjustmentSchema,
  assembleSchema,
  bomVersionCreateSchema,
  disassembleSchema,
  inviteAcceptSchema,
  inviteCreateSchema,
  locationCreateSchema,
  loginSchema,
  partCreateSchema,
  passwordResetConfirmSchema,
  passwordResetRequestSchema,
  receiveSchema,
  syncOperationSchema,
  transferSchema
} from "@inventory/shared";
import { db, emitSyncEvent } from "./db.js";
import {
  authenticate,
  clearSession,
  createSession,
  hashPassword,
  hasAnyUsers,
  randomToken,
  tokenHash,
  verifyPassword
} from "./auth.js";
import { adjustStock, assembleProduct, disassembleProduct, InventoryError, receiveStock, transferStock } from "./inventory.js";
import { isEmailConfigured, sendInviteEmail, sendPasswordResetEmail } from "./mailer.js";

const app = Fastify({ logger: true, trustProxy: true });
const configuredOrigins = (process.env.CORS_ORIGIN ?? "http://localhost:3000")
  .split(",")
  .map((value) => value.trim())
  .filter(Boolean);
const origin = configuredOrigins[0] ?? "http://localhost:3000";
const rpName = process.env.PASSKEY_RP_NAME ?? "SSS Inventory";

function isPrivateLanHost(hostname: string) {
  return /^(10|192\.168)\.\d{1,3}\.\d{1,3}\.\d{1,3}$/.test(hostname) || /^172\.(1[6-9]|2\d|3[0-1])\.\d{1,3}\.\d{1,3}$/.test(hostname);
}

function isAllowedCorsOrigin(requestOrigin?: string) {
  if (!requestOrigin) return true;
  if (configuredOrigins.includes(requestOrigin)) return true;
  try {
    const url = new URL(requestOrigin);
    const localHost = url.hostname === "localhost" || url.hostname === "127.0.0.1" || isPrivateLanHost(url.hostname);
    return localHost && ["3000", "3001"].includes(url.port);
  } catch {
    return false;
  }
}

function getPasskeyOrigin(requestOrigin?: string) {
  return process.env.PASSKEY_ORIGIN ?? process.env.PUBLIC_APP_URL ?? requestOrigin ?? origin;
}

function getPasskeyRpId(requestOrigin?: string) {
  return process.env.PASSKEY_RP_ID ?? new URL(getPasskeyOrigin(requestOrigin)).hostname;
}

function userIdToBytes(userId: string) {
  return Buffer.from(userId.replaceAll("-", ""), "hex");
}

async function storePasskeyChallenge(input: { userId?: string | null; challenge: string; type: "registration" | "authentication" }) {
  await db
    .insertInto("passkey_challenges")
    .values({
      user_id: input.userId ?? null,
      challenge: input.challenge,
      type: input.type,
      expires_at: new Date(Date.now() + 1000 * 60 * 5)
    })
    .execute();
}

function challengeFromClientData(clientDataJSON: string) {
  const clientData = JSON.parse(Buffer.from(clientDataJSON, "base64url").toString("utf8")) as { challenge?: string };
  return clientData.challenge;
}

await app.register(cors, {
  origin: (requestOrigin, callback) => callback(null, isAllowedCorsOrigin(requestOrigin)),
  credentials: true,
  methods: ["GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"]
});
await app.register(cookie);
await app.register(multipart);

app.setErrorHandler((error, _request, reply) => {
  if (error instanceof InventoryError) {
    reply.code(error.statusCode).send({ error: error.message, details: error.details });
    return;
  }
  const httpError = error as { statusCode?: number; message?: string };
  if (typeof httpError.statusCode === "number" && httpError.statusCode >= 400 && httpError.statusCode < 500) {
    reply.code(httpError.statusCode).send({ error: httpError.message ?? "Invalid request" });
    return;
  }
  if (error instanceof ZodError) {
    const firstIssue = error.issues[0];
    const field = firstIssue?.path.join(".");
    const message =
      field === "password" && firstIssue?.code === "too_small"
        ? "Password must be at least 8 characters."
        : field === "email"
          ? "Enter a valid email address."
          : firstIssue?.message ?? "Invalid request";
    reply.code(400).send({ error: message, details: error.issues });
    return;
  }
  app.log.error(error);
  reply.code(500).send({ error: "Unexpected server error" });
});

app.get("/health", async () => ({ ok: true }));

app.get("/auth/bootstrap-status", async () => ({
  available: !(await hasAnyUsers())
}));

app.post("/auth/bootstrap", async (request, reply) => {
  if (await hasAnyUsers()) {
    reply.code(403).send({ error: "Bootstrap is only available before the first user exists" });
    return;
  }
  const body = loginSchema.parse(request.body);
  const passwordHash = await hashPassword(body.password);
  const user = await db
    .insertInto("users")
    .values({ email: body.email, password_hash: passwordHash })
    .returning(["id", "email"])
    .executeTakeFirstOrThrow();
  const token = await createSession(reply, user.id);
  return { user, token };
});

app.post("/auth/login", async (request, reply) => {
  const body = loginSchema.parse(request.body);
  const user = await db.selectFrom("users").selectAll().where("email", "=", body.email).executeTakeFirst();
  if (!user || !(await verifyPassword(body.password, user.password_hash))) {
    reply.code(401).send({ error: "Invalid email or password" });
    return;
  }
  const token = await createSession(reply, user.id);
  return { user: { id: user.id, email: user.email }, token };
});

app.post("/auth/passkeys/authentication-options", async (request) => {
  const requestOrigin = request.headers.origin;
  const options = await generateAuthenticationOptions({
    rpID: getPasskeyRpId(requestOrigin),
    userVerification: "preferred",
    allowCredentials: []
  });
  await storePasskeyChallenge({ challenge: options.challenge, type: "authentication" });
  return options;
});

app.post("/auth/passkeys/authenticate", async (request, reply) => {
  const response = request.body as AuthenticationResponseJSON;
  const credentialRow = await db
    .selectFrom("passkey_credentials")
    .innerJoin("users", "users.id", "passkey_credentials.user_id")
    .select([
      "passkey_credentials.id",
      "passkey_credentials.credential_id as credentialId",
      "passkey_credentials.public_key as publicKey",
      "passkey_credentials.counter",
      "passkey_credentials.transports",
      "users.id as userId",
      "users.email"
    ])
    .where("passkey_credentials.credential_id", "=", response.id)
    .executeTakeFirst();

  if (!credentialRow) {
    reply.code(401).send({ error: "Passkey is not registered for this app." });
    return;
  }

  const verification = await verifyAuthenticationResponse({
    response,
    expectedChallenge: async (signedChallenge) => {
      const row = await db
        .selectFrom("passkey_challenges")
        .select("id")
        .where("type", "=", "authentication")
        .where("challenge", "=", signedChallenge)
        .where("expires_at", ">", new Date())
        .executeTakeFirst();
      return Boolean(row);
    },
    expectedOrigin: getPasskeyOrigin(request.headers.origin),
    expectedRPID: getPasskeyRpId(request.headers.origin),
    credential: {
      id: credentialRow.credentialId,
      publicKey: new Uint8Array(credentialRow.publicKey),
      counter: Number(credentialRow.counter),
      transports: credentialRow.transports as AuthenticatorTransportFuture[]
    },
    requireUserVerification: false
  });

  if (!verification.verified) {
    reply.code(401).send({ error: "Passkey could not be verified." });
    return;
  }

  const signedChallenge = challengeFromClientData(response.response.clientDataJSON);
  await db.transaction().execute(async (tx) => {
    await tx
      .updateTable("passkey_credentials")
      .set({ counter: String(verification.authenticationInfo.newCounter), last_used_at: new Date() })
      .where("id", "=", credentialRow.id)
      .execute();
    if (signedChallenge) {
      await tx.deleteFrom("passkey_challenges").where("challenge", "=", signedChallenge).where("type", "=", "authentication").execute();
    }
    await tx.deleteFrom("passkey_challenges").where("type", "=", "authentication").where("expires_at", "<=", new Date()).execute();
  });

  const user = { id: credentialRow.userId, email: credentialRow.email };
  const token = await createSession(reply, user.id);
  return { user, token };
});

app.post("/auth/password-reset/request", async (request) => {
  const body = passwordResetRequestSchema.parse(request.body);
  const user = await db.selectFrom("users").select(["id", "email"]).where("email", "=", body.email).executeTakeFirst();
  const emailConfigured = isEmailConfigured();

  if (user) {
    const recentReset = await db
      .selectFrom("password_resets")
      .select("id")
      .where("user_id", "=", user.id)
      .where("created_at", ">", new Date(Date.now() - 1000 * 60 * 2))
      .executeTakeFirst();

    if (!recentReset) {
      const token = randomToken();
      await db
        .insertInto("password_resets")
        .values({
          user_id: user.id,
          token_hash: tokenHash(token),
          expires_at: new Date(Date.now() + 1000 * 60 * 60)
        })
        .execute();

      const appUrl = process.env.PUBLIC_APP_URL ?? origin;
      const resetUrl = `${appUrl.replace(/\/$/, "")}?reset=${encodeURIComponent(token)}`;
      sendPasswordResetEmail({ to: user.email, resetUrl }).catch((error) => {
        app.log.error({ error, email: user.email }, "Password reset email failed");
      });
    }
  }

  return { ok: true, emailSent: emailConfigured };
});

app.post("/auth/password-reset/confirm", async (request, reply) => {
  const body = passwordResetConfirmSchema.parse(request.body);
  const reset = await db
    .selectFrom("password_resets")
    .innerJoin("users", "users.id", "password_resets.user_id")
    .select(["password_resets.id", "users.id as userId", "users.email"])
    .where("password_resets.token_hash", "=", tokenHash(body.token))
    .where("password_resets.used_at", "is", null)
    .where("password_resets.expires_at", ">", new Date())
    .executeTakeFirst();

  if (!reset) {
    reply.code(400).send({ error: "Reset link is invalid or expired" });
    return;
  }

  const user = await db.transaction().execute(async (tx) => {
    await tx
      .updateTable("users")
      .set({ password_hash: await hashPassword(body.password) })
      .where("id", "=", reset.userId)
      .execute();
    await tx
      .updateTable("password_resets")
      .set({ used_at: new Date() })
      .where("user_id", "=", reset.userId)
      .where("used_at", "is", null)
      .execute();
    await tx.deleteFrom("sessions").where("user_id", "=", reset.userId).execute();
    return { id: reset.userId, email: reset.email };
  });

  const token = await createSession(reply, user.id);
  return { user, token };
});

app.post("/auth/logout", { preHandler: authenticate }, async (request, reply) => {
  await clearSession(request, reply);
  return { ok: true };
});

app.get("/auth/me", { preHandler: authenticate }, async (request) => ({ user: request.user }));

app.get("/auth/passkeys", { preHandler: authenticate }, async (request) => ({
  passkeys: await db
    .selectFrom("passkey_credentials")
    .select(["id", "created_at as createdAt", "last_used_at as lastUsedAt", "device_type as deviceType", "backed_up as backedUp"])
    .where("user_id", "=", request.user!.id)
    .orderBy("created_at", "desc")
    .execute()
}));

app.post("/auth/passkeys/registration-options", { preHandler: authenticate }, async (request) => {
  const existing = await db
    .selectFrom("passkey_credentials")
    .select(["credential_id as credentialId", "transports"])
    .where("user_id", "=", request.user!.id)
    .execute();
  const requestOrigin = request.headers.origin;
  const options = await generateRegistrationOptions({
    rpName,
    rpID: getPasskeyRpId(requestOrigin),
    userName: request.user!.email,
    userID: userIdToBytes(request.user!.id),
    userDisplayName: request.user!.email,
    attestationType: "none",
    excludeCredentials: existing.map((credential) => ({ id: credential.credentialId, transports: credential.transports as AuthenticatorTransportFuture[] })),
    authenticatorSelection: {
      residentKey: "required",
      userVerification: "preferred"
    },
    supportedAlgorithmIDs: [-7, -257]
  });
  await storePasskeyChallenge({ userId: request.user!.id, challenge: options.challenge, type: "registration" });
  return options;
});

app.post("/auth/passkeys/register", { preHandler: authenticate }, async (request, reply) => {
  const response = request.body as RegistrationResponseJSON;
  const verification = await verifyRegistrationResponse({
    response,
    expectedChallenge: async (signedChallenge) => {
      const row = await db
        .selectFrom("passkey_challenges")
        .select("id")
        .where("user_id", "=", request.user!.id)
        .where("type", "=", "registration")
        .where("challenge", "=", signedChallenge)
        .where("expires_at", ">", new Date())
        .executeTakeFirst();
      return Boolean(row);
    },
    expectedOrigin: getPasskeyOrigin(request.headers.origin),
    expectedRPID: getPasskeyRpId(request.headers.origin),
    requireUserVerification: false,
    supportedAlgorithmIDs: [-7, -257]
  });

  if (!verification.verified || !verification.registrationInfo) {
    reply.code(400).send({ error: "Passkey registration could not be verified." });
    return;
  }

  const { credential, credentialDeviceType, credentialBackedUp } = verification.registrationInfo;
  await db.transaction().execute(async (tx) => {
    await tx
      .insertInto("passkey_credentials")
      .values({
        user_id: request.user!.id,
        credential_id: credential.id,
        public_key: Buffer.from(credential.publicKey),
        counter: String(credential.counter),
        transports: response.response.transports ?? [],
        device_type: credentialDeviceType,
        backed_up: credentialBackedUp
      })
      .execute();
    await tx.deleteFrom("passkey_challenges").where("user_id", "=", request.user!.id).where("type", "=", "registration").execute();
  });
  await emitSyncEvent("users.updated", { userId: request.user!.id, action: "passkey-registered" });
  return { ok: true };
});

app.get("/users", { preHandler: authenticate }, async () => ({
  users: await db.selectFrom("users").select(["id", "email", "created_at as createdAt"]).orderBy("created_at", "desc").execute()
}));

app.delete("/users/:id", { preHandler: authenticate }, async (request, reply) => {
  const userId = (request.params as { id: string }).id;
  if (userId === request.user!.id) {
    reply.code(400).send({ error: "You cannot delete your own account while signed in." });
    return;
  }
  const existing = await db.selectFrom("users").select("id").where("id", "=", userId).executeTakeFirst();
  if (!existing) {
    reply.code(404).send({ error: "User not found" });
    return;
  }
  await db.transaction().execute(async (tx) => {
    await tx.updateTable("inventory_operations").set({ user_id: null }).where("user_id", "=", userId).execute();
    await tx.updateTable("invites").set({ created_by: null }).where("created_by", "=", userId).execute();
    await tx.deleteFrom("users").where("id", "=", userId).execute();
  });
  await emitSyncEvent("users.updated", { userId, action: "deleted" });
  return { ok: true };
});

app.get("/invites", { preHandler: authenticate }, async () => ({
  invites: await db
    .selectFrom("invites")
    .leftJoin("users", "users.id", "invites.created_by")
    .select([
      "invites.id",
      "invites.email",
      "invites.accepted_at as acceptedAt",
      "invites.expires_at as expiresAt",
      "invites.created_at as createdAt",
      "users.email as createdByEmail"
    ])
    .orderBy("invites.created_at", "desc")
    .limit(50)
    .execute()
}));

app.post("/invites", { preHandler: authenticate }, async (request) => {
  const body = inviteCreateSchema.parse(request.body);
  const token = randomToken();
  const invite = await db
    .insertInto("invites")
    .values({
      email: body.email,
      token_hash: tokenHash(token),
      expires_at: new Date(Date.now() + 1000 * 60 * 60 * 24 * 14),
      created_by: request.user!.id
    })
    .onConflict((oc) =>
      oc.column("email").doUpdateSet({
        token_hash: tokenHash(token),
        expires_at: new Date(Date.now() + 1000 * 60 * 60 * 24 * 14),
        accepted_at: null,
        created_by: request.user!.id
      })
    )
    .returning(["id", "email", "expires_at"])
    .executeTakeFirstOrThrow();

  const appUrl = process.env.PUBLIC_APP_URL ?? origin;
  const inviteUrl = `${appUrl.replace(/\/$/, "")}?invite=${encodeURIComponent(token)}`;
  const emailResult = await sendInviteEmail({
    to: invite.email,
    inviteUrl,
    invitedBy: request.user!.email
  });

  await emitSyncEvent("users.updated", { inviteId: invite.id, action: "invited" });
  return { invite, emailSent: emailResult.sent };
});

app.post("/invites/accept", async (request, reply) => {
  const body = inviteAcceptSchema.parse(request.body);
  const invite = await db
    .selectFrom("invites")
    .selectAll()
    .where("token_hash", "=", tokenHash(body.token))
    .where("accepted_at", "is", null)
    .where("expires_at", ">", new Date())
    .executeTakeFirst();
  if (!invite) {
    reply.code(400).send({ error: "Invite is invalid or expired" });
    return;
  }

  const user = await db.transaction().execute(async (tx) => {
    const created = await tx
      .insertInto("users")
      .values({ email: invite.email, password_hash: await hashPassword(body.password) })
      .returning(["id", "email"])
      .executeTakeFirstOrThrow();
    await tx.updateTable("invites").set({ accepted_at: new Date() }).where("id", "=", invite.id).execute();
    return created;
  });
  const token = await createSession(reply, user.id);
  await emitSyncEvent("users.updated", { userId: user.id, action: "accepted-invite" });
  return { user, token };
});

app.get("/parts", { preHandler: authenticate }, async (request) => {
  const search = typeof request.query === "object" && request.query && "q" in request.query ? String(request.query.q ?? "") : "";
  let query = db.selectFrom("parts").selectAll().where("active", "=", true).orderBy("name", "asc");
  if (search) query = query.where("name", "ilike", `%${search}%`);
  return { parts: await query.execute() };
});

app.post("/parts", { preHandler: authenticate }, async (request) => {
  const body = partCreateSchema.parse(request.body);
  const part = await db
    .insertInto("parts")
    .values({
      name: body.name,
      sku: body.sku ?? `INV-${Date.now()}`,
      type: body.type,
      average_cost: String(body.defaultUnitCost)
    })
    .returningAll()
    .executeTakeFirstOrThrow();
  await db
    .insertInto("part_aliases")
    .values({ part_id: part.id, code: part.sku ?? part.id, label: "Primary" })
    .onConflict((oc) => oc.column("code").doNothing())
    .execute();
  await emitSyncEvent("catalog.updated", { partId: part.id });
  return { part };
});

app.get("/locations", { preHandler: authenticate }, async () => ({
  locations: await db.selectFrom("locations").selectAll().where("active", "=", true).orderBy("priority", "asc").orderBy("name", "asc").execute()
}));

app.post("/locations", { preHandler: authenticate }, async (request) => {
  const body = locationCreateSchema.parse(request.body);
  const location = await db
    .insertInto("locations")
    .values({ name: body.name, kind: body.kind, priority: body.priority })
    .returningAll()
    .executeTakeFirstOrThrow();
  await emitSyncEvent("catalog.updated", { locationId: location.id });
  return { location };
});

app.get("/inventory", { preHandler: authenticate }, async (request) => {
  const locationId = typeof request.query === "object" && request.query && "locationId" in request.query ? String(request.query.locationId ?? "") : "";
  let query = db
    .selectFrom("inventory_balances")
    .innerJoin("parts", "parts.id", "inventory_balances.part_id")
    .innerJoin("locations", "locations.id", "inventory_balances.location_id")
    .leftJoin("stock_thresholds", (join) =>
      join
        .onRef("stock_thresholds.part_id", "=", "inventory_balances.part_id")
        .onRef("stock_thresholds.location_id", "=", "inventory_balances.location_id")
    )
    .select([
      "parts.id as partId",
      "parts.name as partName",
      "parts.sku as sku",
      "parts.type as partType",
      "parts.average_cost as averageCost",
      "locations.id as locationId",
      "locations.name as locationName",
      "inventory_balances.quantity as quantity",
      "stock_thresholds.min_quantity as minQuantity"
    ])
    .orderBy("parts.name", "asc");
  if (locationId) query = query.where("locations.id", "=", locationId);
  return { inventory: await query.execute() };
});

app.post("/inventory/receive", { preHandler: authenticate }, async (request) => ({
  operation: await receiveStock(receiveSchema.parse(request.body), request.user!.id)
}));

app.post("/inventory/transfer", { preHandler: authenticate }, async (request) => ({
  operation: await transferStock(transferSchema.parse(request.body), request.user!.id)
}));

app.post("/inventory/adjust", { preHandler: authenticate }, async (request) => ({
  operation: await adjustStock(adjustmentSchema.parse(request.body), request.user!.id)
}));

app.post("/assemble", { preHandler: authenticate }, async (request) => ({
  operation: await assembleProduct(assembleSchema.parse(request.body), request.user!.id)
}));

app.post("/disassemble", { preHandler: authenticate }, async (request) => ({
  operation: await disassembleProduct(disassembleSchema.parse(request.body), request.user!.id)
}));

app.get("/bom/:assemblyId", { preHandler: authenticate }, async (request) => {
  const assemblyId = (request.params as { assemblyId: string }).assemblyId;
  const version = await db
    .selectFrom("bom_versions")
    .selectAll()
    .where("assembly_part_id", "=", assemblyId)
    .where("active", "=", true)
    .orderBy("version", "desc")
    .executeTakeFirst();
  const lines = version
    ? await db
        .selectFrom("bom_lines")
        .innerJoin("parts", "parts.id", "bom_lines.component_part_id")
        .select([
          "bom_lines.id",
          "bom_lines.component_part_id as componentPartId",
          "parts.name as componentName",
          "bom_lines.quantity_required as quantityRequired"
        ])
        .where("bom_version_id", "=", version.id)
        .execute()
    : [];
  return { version, lines };
});

app.get("/bom-components", { preHandler: authenticate }, async () => ({
  componentPartIds: await db
    .selectFrom("bom_lines")
    .innerJoin("bom_versions", "bom_versions.id", "bom_lines.bom_version_id")
    .select("bom_lines.component_part_id as componentPartId")
    .where("bom_versions.active", "=", true)
    .groupBy("bom_lines.component_part_id")
    .execute()
}));

app.post("/bom", { preHandler: authenticate }, async (request) => {
  const body = bomVersionCreateSchema.parse(request.body);
  const version = await db.transaction().execute(async (tx) => {
    await tx.updateTable("bom_versions").set({ active: false }).where("assembly_part_id", "=", body.assemblyPartId).execute();
    const current = await tx
      .selectFrom("bom_versions")
      .select("version")
      .where("assembly_part_id", "=", body.assemblyPartId)
      .orderBy("version", "desc")
      .executeTakeFirst();
    const created = await tx
      .insertInto("bom_versions")
      .values({
        assembly_part_id: body.assemblyPartId,
        name: body.name,
        version: Number(current?.version ?? 0) + 1,
        active: true
      })
      .returningAll()
      .executeTakeFirstOrThrow();
    await tx
      .insertInto("bom_lines")
      .values(body.lines.map((line) => ({
        bom_version_id: created.id,
        component_part_id: line.componentPartId,
        quantity_required: String(line.quantityRequired)
      })))
      .execute();
    return created;
  });
  await emitSyncEvent("catalog.updated", { bomVersionId: version.id });
  return { version };
});

app.get("/transactions", { preHandler: authenticate }, async () => ({
  transactions: await db
    .selectFrom("inventory_ledger")
    .innerJoin("inventory_operations", "inventory_operations.id", "inventory_ledger.operation_id")
    .innerJoin("parts", "parts.id", "inventory_ledger.part_id")
    .innerJoin("locations", "locations.id", "inventory_ledger.location_id")
    .leftJoin("users", "users.id", "inventory_operations.user_id")
    .select([
      "inventory_ledger.id",
      "inventory_ledger.created_at as createdAt",
      "inventory_ledger.quantity_delta as quantityDelta",
      "inventory_ledger.unit_cost_snapshot as unitCost",
      "inventory_ledger.action",
      "inventory_operations.id as operationId",
      "parts.name as partName",
      "locations.name as locationName",
      "users.email as userEmail"
    ])
    .orderBy("inventory_ledger.created_at", "desc")
    .limit(250)
    .execute()
}));

app.get("/transactions/export.csv", { preHandler: authenticate }, async (_request, reply) => {
  const rows = await db
    .selectFrom("inventory_ledger")
    .innerJoin("inventory_operations", "inventory_operations.id", "inventory_ledger.operation_id")
    .innerJoin("parts", "parts.id", "inventory_ledger.part_id")
    .innerJoin("locations", "locations.id", "inventory_ledger.location_id")
    .leftJoin("users", "users.id", "inventory_operations.user_id")
    .select([
      "inventory_ledger.created_at as createdAt",
      "inventory_ledger.action",
      "parts.name as part",
      "locations.name as location",
      "inventory_ledger.quantity_delta as quantityDelta",
      "inventory_ledger.unit_cost_snapshot as unitCost",
      "users.email as user"
    ])
    .orderBy("inventory_ledger.created_at", "desc")
    .execute();
  reply.header("content-type", "text/csv");
  reply.header("content-disposition", "attachment; filename=inventory-transactions.csv");
  return stringify(rows, { header: true });
});

app.get("/reports/valuation", { preHandler: authenticate }, async () => ({
  valuation: await db
    .selectFrom("inventory_balances")
    .innerJoin("parts", "parts.id", "inventory_balances.part_id")
    .innerJoin("locations", "locations.id", "inventory_balances.location_id")
    .select([
      "locations.name as locationName",
      "parts.type as partType",
      (eb) => eb.fn.sum<string>(sql`inventory_balances.quantity * parts.average_cost`).as("value")
    ])
    .groupBy(["locations.name", "parts.type"])
    .orderBy("locations.name")
    .execute()
}));

app.get("/reports/low-stock", { preHandler: authenticate }, async () => ({
  items: await db
    .selectFrom("inventory_balances")
    .innerJoin("parts", "parts.id", "inventory_balances.part_id")
    .innerJoin("locations", "locations.id", "inventory_balances.location_id")
    .innerJoin("stock_thresholds", (join) =>
      join
        .onRef("stock_thresholds.part_id", "=", "inventory_balances.part_id")
        .onRef("stock_thresholds.location_id", "=", "inventory_balances.location_id")
    )
    .select([
      "parts.name as partName",
      "locations.name as locationName",
      "inventory_balances.quantity as quantity",
      "stock_thresholds.min_quantity as minQuantity",
      "stock_thresholds.reorder_quantity as reorderQuantity"
    ])
    .whereRef("inventory_balances.quantity", "<", "stock_thresholds.min_quantity")
    .orderBy("parts.name")
    .execute()
}));

app.get("/sync", { preHandler: authenticate }, async (request) => {
  const since = typeof request.query === "object" && request.query && "since" in request.query ? Number(request.query.since ?? 0) : 0;
  const events = await db.selectFrom("sync_events").selectAll().where("id", ">", since).orderBy("id", "asc").limit(500).execute();
  return { events, cursor: events.at(-1)?.id ?? since };
});

app.post("/sync/operations", { preHandler: authenticate }, async (request) => {
  const body = Array.isArray(request.body) ? request.body : [];
  const results = [];
  for (const candidate of body) {
    const operation = syncOperationSchema.parse(candidate);
    try {
      if (operation.type === "receive") results.push({ ok: true, result: await receiveStock(operation.payload, request.user!.id) });
      if (operation.type === "transfer") results.push({ ok: true, result: await transferStock(operation.payload, request.user!.id) });
      if (operation.type === "assemble") results.push({ ok: true, result: await assembleProduct(operation.payload, request.user!.id) });
      if (operation.type === "disassemble") results.push({ ok: true, result: await disassembleProduct(operation.payload, request.user!.id) });
      if (operation.type === "adjust") results.push({ ok: true, result: await adjustStock(operation.payload, request.user!.id) });
    } catch (error) {
      if (error instanceof InventoryError) results.push({ ok: false, error: error.message, details: error.details });
      else throw error;
    }
  }
  return { results };
});

const io = new SocketServer(app.server, {
  cors: { origin: (requestOrigin, callback) => callback(null, isAllowedCorsOrigin(requestOrigin)), credentials: true }
});

io.on("connection", (socket) => {
  socket.emit("connected", { ok: true });
});

let lastEventId = 0;
setInterval(async () => {
  const events = await db.selectFrom("sync_events").selectAll().where("id", ">", lastEventId).orderBy("id", "asc").execute();
  for (const event of events) {
    lastEventId = event.id;
    io.emit(event.topic, event.payload);
  }
}, 1000).unref();

const port = Number(process.env.API_PORT ?? 4000);
await app.listen({ port, host: process.env.API_HOST ?? "0.0.0.0" });