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

export async function listPublishedTeam(ctx: TenantContext) {
  const db = getDb();
  return db
    .select()
    .from(teamMembers)
    .where(
      and(
        eq(teamMembers.tenantId, ctx.tenantId),
        eq(teamMembers.isPublished, true),
      ),
    )
    .orderBy(asc(teamMembers.sortOrder));
}

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

export async function getPublishedTeamMemberBySlug(
  ctx: TenantContext,
  slug: string,
) {
  const db = getDb();
  const [row] = await db
    .select()
    .from(teamMembers)
    .where(
      and(
        eq(teamMembers.tenantId, ctx.tenantId),
        eq(teamMembers.slug, slug),
        eq(teamMembers.isPublished, true),
      ),
    )
    .limit(1);
  return row ?? null;
}

export async function createTeamMember(
  ctx: TenantContext,
  input: {
    name: string;
    slug?: string;
    role?: string;
    bio?: string;
    email?: string;
    sortOrder?: number;
    isPublished?: boolean;
  },
) {
  const db = getDb();
  const [row] = await db
    .insert(teamMembers)
    .values({
      tenantId: ctx.tenantId,
      name: input.name.trim(),
      slug: normalizeSlug(input.slug || input.name),
      role: input.role?.trim() || null,
      bio: input.bio?.trim() || null,
      email: input.email?.trim() || null,
      sortOrder: input.sortOrder ?? 0,
      isPublished: input.isPublished ?? true,
    })
    .returning();
  return row;
}

export async function deleteTeamMember(ctx: TenantContext, id: string) {
  const db = getDb();
  await db
    .delete(teamMembers)
    .where(and(eq(teamMembers.id, id), eq(teamMembers.tenantId, ctx.tenantId)));
}