import { and, asc, eq } from "drizzle-orm";
import { getDb } from "@/core/db/client";
import { projectCategories } from "@/core/db/schema";
import { normalizeSlug } from "@/core/tenant/slug";
import type { TenantContext } from "@/core/tenant/types";

export type CategoryInput = {
  name: string;
  slug?: string;
  description?: string;
  sortOrder?: number;
};

export async function listCategories(ctx: TenantContext) {
  const db = getDb();
  return db
    .select()
    .from(projectCategories)
    .where(eq(projectCategories.tenantId, ctx.tenantId))
    .orderBy(asc(projectCategories.sortOrder), asc(projectCategories.name));
}

export async function getCategoryForTenant(
  ctx: TenantContext,
  categoryId: string,
) {
  const db = getDb();
  const [row] = await db
    .select()
    .from(projectCategories)
    .where(
      and(
        eq(projectCategories.id, categoryId),
        eq(projectCategories.tenantId, ctx.tenantId),
      ),
    )
    .limit(1);
  return row ?? null;
}

export async function createCategory(ctx: TenantContext, input: CategoryInput) {
  const db = getDb();
  const slug = normalizeSlug(input.slug || input.name);
  const [row] = await db
    .insert(projectCategories)
    .values({
      tenantId: ctx.tenantId,
      name: input.name.trim(),
      slug,
      description: input.description?.trim() || null,
      sortOrder: input.sortOrder ?? 0,
    })
    .returning();
  return row;
}

export async function updateCategory(
  ctx: TenantContext,
  categoryId: string,
  input: Partial<CategoryInput>,
) {
  const existing = await getCategoryForTenant(ctx, categoryId);
  if (!existing) return null;

  const db = getDb();
  const slug = input.slug
    ? normalizeSlug(input.slug)
    : input.name
      ? normalizeSlug(input.name)
      : undefined;

  const [updated] = await db
    .update(projectCategories)
    .set({
      ...(input.name !== undefined ? { name: input.name.trim() } : {}),
      ...(slug ? { slug } : {}),
      ...(input.description !== undefined
        ? { description: input.description.trim() || null }
        : {}),
      ...(input.sortOrder !== undefined ? { sortOrder: input.sortOrder } : {}),
      updatedAt: new Date(),
    })
    .where(
      and(
        eq(projectCategories.id, categoryId),
        eq(projectCategories.tenantId, ctx.tenantId),
      ),
    )
    .returning();

  return updated ?? null;
}

export async function deleteCategory(ctx: TenantContext, categoryId: string) {
  const db = getDb();
  const [deleted] = await db
    .delete(projectCategories)
    .where(
      and(
        eq(projectCategories.id, categoryId),
        eq(projectCategories.tenantId, ctx.tenantId),
      ),
    )
    .returning({ id: projectCategories.id });
  return deleted ?? null;
}