import type { ProjectDocument } from "@vtit-agent-coding/shared";
import { type D1, newId, queryDb } from "./db";

export async function createProjectDocument(
  db: D1,
  projectId: string,
  category: string,
  fileName: string,
  title: string,
  content: string,
): Promise<ProjectDocument> {
  const id = newId();
  await queryDb(
    db,
    `INSERT INTO project_documents (id, project_id, category, file_name, title, content, created_at, updated_at)
     VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())`,
    [id, projectId, category || "general", fileName, title, content],
  );

  const doc = await getProjectDocument(db, projectId, id);
  if (!doc) throw new Error("Failed to create project document");
  return doc;
}

export async function getProjectDocument(db: D1, projectId: string, docId: string): Promise<ProjectDocument | null> {
  const res = await queryDb<ProjectDocument>(db, "SELECT * FROM project_documents WHERE id = $1 AND project_id = $2", [docId, projectId]);
  return res.rows[0] || null;
}

export async function listProjectDocuments(db: D1, projectId: string): Promise<ProjectDocument[]> {
  const res = await queryDb<ProjectDocument>(db, "SELECT * FROM project_documents WHERE project_id = $1 ORDER BY category ASC, file_name ASC", [
    projectId,
  ]);
  return res.rows;
}

export async function updateProjectDocument(
  db: D1,
  projectId: string,
  docId: string,
  updates: { category?: string; file_name?: string; title?: string; content?: string },
): Promise<ProjectDocument | null> {
  const sets: string[] = ["updated_at = NOW()"];
  const params: unknown[] = [];
  let idx = 1;

  if (updates.category !== undefined) {
    sets.push(`category = $${idx++}`);
    params.push(updates.category);
  }
  if (updates.file_name !== undefined) {
    sets.push(`file_name = $${idx++}`);
    params.push(updates.file_name);
  }
  if (updates.title !== undefined) {
    sets.push(`title = $${idx++}`);
    params.push(updates.title);
  }
  if (updates.content !== undefined) {
    sets.push(`content = $${idx++}`);
    params.push(updates.content);
  }

  params.push(docId);
  params.push(projectId);

  await queryDb(db, `UPDATE project_documents SET ${sets.join(", ")} WHERE id = $${idx++} AND project_id = $${idx}`, params);

  return getProjectDocument(db, projectId, docId);
}

export async function deleteProjectDocument(db: D1, projectId: string, docId: string): Promise<boolean> {
  const res = await queryDb(db, "DELETE FROM project_documents WHERE id = $1 AND project_id = $2", [docId, projectId]);
  return (res.rowCount ?? 0) > 0;
}