import "server-only";
import { and, asc, desc, eq, inArray, isNull, sql } from "drizzle-orm";
import { getDb, projects, projectFiles, prompts, conversations, type Project } from "@/db";
import { newId } from "@/lib/ids";
import { ApiError } from "@/lib/api";
import { listConversations } from "@/lib/conversations/service";
import { toPublicFile } from "@/lib/library/service";
import { toPublicPrompt } from "@/lib/prompts/service";

/** Public shape (dates as ISO strings, counts for cards). */
export function toPublicProject(p: Project, counts?: { conversationCount?: number; fileCount?: number; promptCount?: number }) {
  return {
    id: p.id,
    name: p.name,
    description: p.description,
    icon: p.icon,
    color: p.color,
    instructions: p.instructions,
    preferredModelKeys: p.preferredModelKeys ?? [],
    defaultSettings: p.defaultSettings ?? {},
    notes: p.notes,
    archived: p.archived,
    sortOrder: p.sortOrder,
    conversationCount: counts?.conversationCount ?? 0,
    fileCount: counts?.fileCount ?? 0,
    promptCount: counts?.promptCount ?? 0,
    createdAt: p.createdAt.toISOString(),
    updatedAt: p.updatedAt.toISOString(),
  };
}
export type PublicProject = ReturnType<typeof toPublicProject>;

export interface ProjectInput {
  name: string;
  description?: string | null;
  icon?: string | null;
  color?: string | null;
  instructions?: string | null;
  preferredModelKeys?: string[];
  defaultSettings?: Record<string, unknown>;
  notes?: string | null;
  archived?: boolean;
  sortOrder?: number;
}

async function countsFor(userId: string) {
  const db = getDb();
  const [convs, files, prms] = await Promise.all([
    db
      .select({ projectId: conversations.projectId, n: sql<number>`count(*)::int` })
      .from(conversations)
      .where(and(eq(conversations.userId, userId), sql`${conversations.projectId} is not null`))
      .groupBy(conversations.projectId),
    db
      .select({ projectId: projectFiles.projectId, n: sql<number>`count(*)::int` })
      .from(projectFiles)
      .where(and(eq(projectFiles.userId, userId), sql`${projectFiles.projectId} is not null`))
      .groupBy(projectFiles.projectId),
    db
      .select({ projectId: prompts.projectId, n: sql<number>`count(*)::int` })
      .from(prompts)
      .where(and(eq(prompts.userId, userId), sql`${prompts.projectId} is not null`))
      .groupBy(prompts.projectId),
  ]);
  const map = new Map<string, { conversationCount: number; fileCount: number; promptCount: number }>();
  const get = (id: string) => {
    let v = map.get(id);
    if (!v) {
      v = { conversationCount: 0, fileCount: 0, promptCount: 0 };
      map.set(id, v);
    }
    return v;
  };
  for (const r of convs) if (r.projectId) get(r.projectId).conversationCount = r.n;
  for (const r of files) if (r.projectId) get(r.projectId).fileCount = r.n;
  for (const r of prms) if (r.projectId) get(r.projectId).promptCount = r.n;
  return map;
}

export async function listProjects(userId: string, opts: { includeArchived?: boolean } = {}) {
  const conds = [eq(projects.userId, userId)];
  if (!opts.includeArchived) conds.push(eq(projects.archived, false));
  const [rows, counts] = await Promise.all([
    getDb()
      .select()
      .from(projects)
      .where(and(...conds))
      .orderBy(asc(projects.sortOrder), desc(projects.updatedAt)),
    countsFor(userId),
  ]);
  return rows.map((p) => toPublicProject(p, counts.get(p.id)));
}

export async function getProject(userId: string, id: string): Promise<Project> {
  const [p] = await getDb()
    .select()
    .from(projects)
    .where(and(eq(projects.id, id), eq(projects.userId, userId)))
    .limit(1);
  if (!p) throw new ApiError(404, "Project not found", "NOT_FOUND");
  return p;
}

/** Full detail for the project page: project, its conversations, files, prompts and aggregate stats. */
export async function getProjectDetail(userId: string, id: string) {
  const db = getDb();
  const p = await getProject(userId, id);
  const [convs, files, prms, [stats]] = await Promise.all([
    listConversations(userId, { projectId: id, limit: 200 }),
    db.select().from(projectFiles).where(and(eq(projectFiles.userId, userId), eq(projectFiles.projectId, id))).orderBy(desc(projectFiles.createdAt)),
    db.select().from(prompts).where(and(eq(prompts.userId, userId), eq(prompts.projectId, id))).orderBy(desc(prompts.favorite), desc(prompts.updatedAt)),
    db
      .select({
        conversations: sql<number>`count(*)::int`,
        messages: sql<number>`coalesce(sum(${conversations.messageCount}), 0)::int`,
        totalCostUsd: sql<number>`coalesce(sum(${conversations.totalCostUsd}), 0)::float8`,
        totalInputTokens: sql<number>`coalesce(sum(${conversations.totalInputTokens}), 0)::int`,
        totalOutputTokens: sql<number>`coalesce(sum(${conversations.totalOutputTokens}), 0)::int`,
        lastActivityAt: sql<Date | null>`max(${conversations.updatedAt})`,
      })
      .from(conversations)
      .where(and(eq(conversations.userId, userId), eq(conversations.projectId, id))),
  ]);
  const publicFiles = files.map(toPublicFile);
  const last = stats?.lastActivityAt ? new Date(stats.lastActivityAt) : null;
  return {
    project: toPublicProject(p, { conversationCount: stats?.conversations ?? 0, fileCount: files.length, promptCount: prms.length }),
    conversations: convs.conversations,
    files: publicFiles,
    prompts: prms.map(toPublicPrompt),
    stats: {
      conversations: stats?.conversations ?? 0,
      messages: stats?.messages ?? 0,
      files: files.length,
      prompts: prms.length,
      totalCostUsd: stats?.totalCostUsd ?? 0,
      totalInputTokens: stats?.totalInputTokens ?? 0,
      totalOutputTokens: stats?.totalOutputTokens ?? 0,
      fileBytes: files.reduce((n, f) => n + f.sizeBytes, 0),
      fileTokens: files.reduce((n, f) => n + (f.estimatedTokens ?? 0), 0),
      lastActivityAt: last && !Number.isNaN(last.getTime()) ? last.toISOString() : null,
    },
  };
}
export type ProjectDetail = Awaited<ReturnType<typeof getProjectDetail>>;

function clean(input: Partial<ProjectInput>): Partial<typeof projects.$inferInsert> {
  const set: Partial<typeof projects.$inferInsert> = {};
  if (input.name !== undefined) set.name = input.name.trim().slice(0, 80) || "Untitled project";
  if (input.description !== undefined) set.description = input.description?.trim() || null;
  if (input.icon !== undefined) set.icon = input.icon?.trim().slice(0, 8) || null;
  if (input.color !== undefined) set.color = input.color?.trim().slice(0, 24) || null;
  if (input.instructions !== undefined) set.instructions = input.instructions?.trim() ? input.instructions : null;
  if (input.preferredModelKeys !== undefined) set.preferredModelKeys = Array.from(new Set(input.preferredModelKeys.map((k) => k.trim()).filter(Boolean))).slice(0, 8);
  if (input.defaultSettings !== undefined) set.defaultSettings = input.defaultSettings;
  if (input.notes !== undefined) set.notes = input.notes?.trim() ? input.notes : null;
  if (input.archived !== undefined) set.archived = input.archived;
  if (input.sortOrder !== undefined) set.sortOrder = input.sortOrder;
  return set;
}

export async function createProject(userId: string, input: ProjectInput) {
  const [maxRow] = await getDb()
    .select({ max: sql<number>`coalesce(max(${projects.sortOrder}), -1)::int` })
    .from(projects)
    .where(eq(projects.userId, userId));
  const [p] = await getDb()
    .insert(projects)
    .values({ id: newId("prj"), userId, ...clean({ ...input, sortOrder: input.sortOrder ?? (maxRow?.max ?? -1) + 1 }), name: input.name.trim().slice(0, 80) || "Untitled project" })
    .returning();
  return toPublicProject(p);
}

export async function updateProject(userId: string, id: string, patch: Partial<ProjectInput>) {
  const [p] = await getDb()
    .update(projects)
    .set({ ...clean(patch), updatedAt: new Date() })
    .where(and(eq(projects.id, id), eq(projects.userId, userId)))
    .returning();
  if (!p) throw new ApiError(404, "Project not found", "NOT_FOUND");
  const counts = await countsFor(userId);
  return toPublicProject(p, counts.get(p.id));
}

/** Deletes the project. Conversations are kept (project_id → null); project files cascade; prompts are detached. */
export async function deleteProject(userId: string, id: string) {
  const db = getDb();
  await getProject(userId, id);
  await db.update(conversations).set({ projectId: null }).where(and(eq(conversations.userId, userId), eq(conversations.projectId, id)));
  await db.delete(projects).where(and(eq(projects.id, id), eq(projects.userId, userId)));
}

/** Move a set of conversations into (or out of, with `null`) a project. Only the caller's conversations are touched. */
export async function moveConversations(userId: string, conversationIds: string[], projectId: string | null) {
  if (!conversationIds.length) return 0;
  if (projectId) await getProject(userId, projectId);
  const res = await getDb()
    .update(conversations)
    .set({ projectId, updatedAt: new Date() })
    .where(and(eq(conversations.userId, userId), inArray(conversations.id, conversationIds)))
    .returning({ id: conversations.id });
  return res.length;
}

/** Conversations not attached to any project (for the "Add existing chats" picker). */
export async function listUnassignedConversations(userId: string, limit = 100) {
  const rows = await getDb()
    .select({ id: conversations.id, title: conversations.title, modelKey: conversations.modelKey, updatedAt: conversations.updatedAt, messageCount: conversations.messageCount })
    .from(conversations)
    .where(and(eq(conversations.userId, userId), isNull(conversations.projectId), eq(conversations.archived, false)))
    .orderBy(desc(conversations.updatedAt))
    .limit(limit);
  return rows.map((r) => ({ ...r, updatedAt: r.updatedAt.toISOString() }));
}