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

// 1. Create Requirement
export async function createRequirement(
  db: D1,
  projectId: string,
  input: {
    title: string;
    description: string;
    pm_model?: string;
    sa_model?: string;
    ba_model?: string;
  },
): Promise<ProjectRequirement> {
  const id = newId();
  const now = new Date().toISOString();
  await queryDb(
    db,
    `INSERT INTO project_requirements (
      id, project_id, title, description, status, pm_model, sa_model, ba_model, created_at, updated_at
    ) VALUES ($1, $2, $3, $4, 'pending', $5, $6, $7, $8, $9)`,
    [id, projectId, input.title, input.description, input.pm_model ?? null, input.sa_model ?? null, input.ba_model ?? null, now, now],
  );
  const req = await getRequirement(db, id);
  if (!req) {
    throw new Error(`Failed to create requirement with id ${id}`);
  }
  return req;
}

// 2. Get Requirement
export async function getRequirement(db: D1, id: string): Promise<ProjectRequirement | null> {
  const res = await queryDb<ProjectRequirement>(db, `SELECT * FROM project_requirements WHERE id = $1`, [id]);
  return res.rows[0] || null;
}

// 3. List Requirements
export async function listRequirements(db: D1, projectId: string): Promise<ProjectRequirement[]> {
  const res = await queryDb<ProjectRequirement>(db, `SELECT * FROM project_requirements WHERE project_id = $1 ORDER BY created_at DESC`, [projectId]);
  return res.rows;
}

// 4. Update Requirement Status
export async function updateRequirementStatus(
  db: D1,
  id: string,
  status: ProjectRequirement["status"],
  errorMessage?: string,
): Promise<ProjectRequirement> {
  const now = new Date().toISOString();
  await queryDb(
    db,
    `UPDATE project_requirements 
     SET status = $1, error_message = $2, updated_at = $3 
     WHERE id = $4`,
    [status, errorMessage ?? null, now, id],
  );
  const req = await getRequirement(db, id);
  if (!req) {
    throw new Error(`Failed to update requirement status with id ${id}`);
  }
  return req;
}

// 5. Create Draft Task
export async function createDraftTask(
  db: D1,
  requirementId: string,
  input: {
    title: string;
    description?: string;
    technical_notes?: string;
    affected_files?: string[];
    steps?: { order: number; action: string; detail: string }[];
    business_context?: string;
    qc_checks?: { category: string; item: string; severity: string; automated: boolean }[];
    acceptance_tests?: { id: string; scenario: string; given: string; when: string; then: string; testType: string }[];
    priority?: RequirementDraftTask["priority"];
    status?: RequirementDraftTask["status"];
    imported_task_id?: string;
  },
): Promise<RequirementDraftTask> {
  const id = newId();
  const now = new Date().toISOString();
  await queryDb(
    db,
    `INSERT INTO requirement_draft_tasks (
      id, requirement_id, title, description, technical_notes, affected_files, steps, 
      business_context, qc_checks, acceptance_tests, priority, status, imported_task_id, created_at, updated_at
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)`,
    [
      id,
      requirementId,
      input.title,
      input.description ?? null,
      input.technical_notes ?? null,
      input.affected_files ? JSON.stringify(input.affected_files) : null,
      input.steps ? JSON.stringify(input.steps) : null,
      input.business_context ?? null,
      input.qc_checks ? JSON.stringify(input.qc_checks) : null,
      input.acceptance_tests ? JSON.stringify(input.acceptance_tests) : null,
      input.priority ?? "medium",
      input.status ?? "draft",
      input.imported_task_id ?? null,
      now,
      now,
    ],
  );

  const task = await getDraftTask(db, id);
  if (!task) {
    throw new Error(`Failed to create draft task with id ${id}`);
  }
  return task;
}

// Helper to get single draft task (not exported)
async function getDraftTask(db: D1, id: string): Promise<RequirementDraftTask | null> {
  const res = await queryDb<any>(db, `SELECT * FROM requirement_draft_tasks WHERE id = $1`, [id]);
  if (res.rows.length === 0) return null;
  return parseJsonFields(res.rows[0], ["affected_files", "steps", "qc_checks", "acceptance_tests"]);
}

// 6. List Draft Tasks
export async function listDraftTasks(db: D1, requirementId: string): Promise<RequirementDraftTask[]> {
  const res = await queryDb<any>(db, `SELECT * FROM requirement_draft_tasks WHERE requirement_id = $1 ORDER BY created_at ASC`, [requirementId]);
  return res.rows.map((row: any) => parseJsonFields(row, ["affected_files", "steps", "qc_checks", "acceptance_tests"]));
}

// 7. Update Draft Task
export async function updateDraftTask(
  db: D1,
  id: string,
  input: Partial<{
    title: string;
    description: string | null;
    technical_notes: string | null;
    affected_files: string[] | null;
    steps: { order: number; action: string; detail: string }[] | null;
    business_context: string | null;
    qc_checks: { category: string; item: string; severity: string; automated: boolean }[] | null;
    acceptance_tests: { id: string; scenario: string; given: string; when: string; then: string; testType: string }[] | null;
    priority: RequirementDraftTask["priority"];
    status: RequirementDraftTask["status"];
    imported_task_id: string | null;
  }>,
): Promise<RequirementDraftTask> {
  const now = new Date().toISOString();

  const updates: string[] = [];
  const params: any[] = [];
  let paramIndex = 1;

  for (const [key, value] of Object.entries(input)) {
    updates.push(`${key} = $${paramIndex}`);
    if (["affected_files", "steps", "qc_checks", "acceptance_tests"].includes(key) && value !== null && value !== undefined) {
      params.push(JSON.stringify(value));
    } else {
      params.push(value === undefined ? null : value);
    }
    paramIndex++;
  }

  updates.push(`updated_at = $${paramIndex}`);
  params.push(now);
  paramIndex++;

  params.push(id);

  await queryDb(db, `UPDATE requirement_draft_tasks SET ${updates.join(", ")} WHERE id = $${paramIndex}`, params);

  const task = await getDraftTask(db, id);
  if (!task) {
    throw new Error(`Failed to update draft task with id ${id}`);
  }
  return task;
}

// 8. Delete Draft Task
export async function deleteDraftTask(db: D1, id: string): Promise<boolean> {
  const res = await queryDb(db, `DELETE FROM requirement_draft_tasks WHERE id = $1`, [id]);
  return (res.rowCount ?? 0) > 0;
}