import { type Db, projectInstructionVersions, researchProjects } from "@autoscholar/db";
import { validateInstruction } from "@autoscholar/research-contract";
import { INSTRUCTION_MAX_BYTES } from "@autoscholar/schemas";
import { desc, eq, sql } from "drizzle-orm";
import { audit } from "./audit.js";
import { invalid, notFound } from "./errors.js";

export async function createProject(
  db: Db,
  input: { title: string; discipline: string; description: string },
  actor: { id: string; ip?: string | null },
) {
  const [p] = await db
    .insert(researchProjects)
    .values({ ...input, createdBy: actor.id })
    .returning();
  await audit(
    db,
    { type: "admin", id: actor.id, ip: actor.ip },
    "project.create",
    { type: "project", id: p!.id },
    { title: input.title },
  );
  return p!;
}

export async function listProjects(db: Db) {
  const rows = await db
    .select({
      project: researchProjects,
      versions: sql<number>`(select count(*)::int from project_instruction_versions v where v.project_id = ${researchProjects.id})`,
      runs: sql<number>`(select count(*)::int from research_runs r where r.project_id = ${researchProjects.id})`,
    })
    .from(researchProjects)
    .orderBy(desc(researchProjects.createdAt));
  return rows;
}

export async function getProject(db: Db, id: string) {
  const [p] = await db.select().from(researchProjects).where(eq(researchProjects.id, id)).limit(1);
  if (!p) throw notFound("project");
  const versions = await db
    .select({
      id: projectInstructionVersions.id,
      version: projectInstructionVersions.version,
      filename: projectInstructionVersions.filename,
      sha256: projectInstructionVersions.sha256,
      byteSize: projectInstructionVersions.byteSize,
      note: projectInstructionVersions.note,
      validation: projectInstructionVersions.validation,
      frontmatter: projectInstructionVersions.frontmatter,
      createdAt: projectInstructionVersions.createdAt,
    })
    .from(projectInstructionVersions)
    .where(eq(projectInstructionVersions.projectId, id))
    .orderBy(desc(projectInstructionVersions.version));
  return { project: p, versions };
}

/** Stores the exact original bytes + SHA-256 as a new immutable version. Identical content is deduplicated. */
export async function uploadInstruction(
  db: Db,
  projectId: string,
  bytes: Buffer,
  filename: string,
  note: string | undefined,
  actor: { id: string; ip?: string | null },
) {
  if (bytes.length > INSTRUCTION_MAX_BYTES)
    throw invalid(`Instruction exceeds ${INSTRUCTION_MAX_BYTES} bytes`);
  const [p] = await db
    .select({ id: researchProjects.id })
    .from(researchProjects)
    .where(eq(researchProjects.id, projectId))
    .limit(1);
  if (!p) throw notFound("project");
  const v = validateInstruction(bytes, filename);
  if (!v.ok)
    throw invalid(`Instruction rejected: ${v.errors.join("; ")}`, { errors: v.errors, warnings: v.warnings });
  const result = await db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: projectInstructionVersions.id, version: projectInstructionVersions.version })
      .from(projectInstructionVersions)
      .where(
        sql`${projectInstructionVersions.projectId} = ${projectId} and ${projectInstructionVersions.sha256} = ${v.sha256}`,
      )
      .limit(1);
    if (existing) return { id: existing.id, version: existing.version, deduplicated: true };
    await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${projectId}))`);
    const [last] = await tx
      .select({ v: sql<number>`coalesce(max(${projectInstructionVersions.version}), 0)::int` })
      .from(projectInstructionVersions)
      .where(eq(projectInstructionVersions.projectId, projectId));
    const version = (last?.v ?? 0) + 1;
    const [row] = await tx
      .insert(projectInstructionVersions)
      .values({
        projectId,
        version,
        filename: filename.replace(/[^A-Za-z0-9._-]/g, "_").slice(0, 120) || "CLAUDE.md",
        sha256: v.sha256,
        byteSize: v.byteSize,
        contentText: v.text,
        originalBytes: bytes,
        frontmatter: v.frontmatter,
        validation: { warnings: v.warnings, headings: v.headings, sections: v.sections },
        note: note ?? null,
        createdBy: actor.id,
      })
      .returning({ id: projectInstructionVersions.id, version: projectInstructionVersions.version });
    await tx
      .update(researchProjects)
      .set({ updatedAt: new Date() })
      .where(eq(researchProjects.id, projectId));
    await audit(
      tx,
      { type: "admin", id: actor.id, ip: actor.ip },
      "instruction.upload",
      { type: "instruction_version", id: row!.id },
      { projectId, version, sha256: v.sha256, bytes: v.byteSize },
    );
    return { id: row!.id, version: row!.version, deduplicated: false };
  });
  return { ...result, validation: v };
}

export async function getInstructionVersion(db: Db, id: string) {
  const [row] = await db
    .select()
    .from(projectInstructionVersions)
    .where(eq(projectInstructionVersions.id, id))
    .limit(1);
  if (!row) throw notFound("instruction version");
  return row;
}