import { execute, queryAll, queryOne } from './db';
import type { Env } from './env';

const PROJECT_STATUSES = new Set(['active', 'archived']);
const TASK_STATUSES = new Set(['todo', 'in_progress', 'blocked', 'done', 'canceled']);

interface ProjectRow {
  id: string;
  name: string;
  description: string | null;
  status: string;
  owner_user_id: string | null;
  created_at: string;
  updated_at: string;
}

interface TaskRow {
  id: string;
  project_id: string | null;
  title: string;
  status: string;
  priority: number;
  due_date: string | null;
  created_at: string;
  updated_at: string;
}

function jsonError(status: number, message: string): Response {
  return Response.json({ ok: false, error: message }, { status });
}

async function readJson<T>(request: Request): Promise<T | null> {
  try {
    return (await request.json()) as T;
  } catch {
    return null;
  }
}

function requireDb(env: Env): D1Database | Response {
  if (!env.DB) {
    return jsonError(503, 'Database not configured');
  }
  return env.DB;
}

async function listProjects(db: D1Database): Promise<Response> {
  const projects = await queryAll<ProjectRow>(
    db,
    `SELECT id, name, description, status, owner_user_id, created_at, updated_at
     FROM projects
     ORDER BY created_at DESC`
  );
  return Response.json({ ok: true, projects });
}

async function getProject(db: D1Database, id: string): Promise<Response> {
  const project = await queryOne<ProjectRow>(
    db,
    `SELECT id, name, description, status, owner_user_id, created_at, updated_at
     FROM projects
     WHERE id = ?`,
    [id]
  );
  if (!project) {
    return jsonError(404, 'Project not found');
  }
  return Response.json({ ok: true, project });
}

async function createProject(db: D1Database, request: Request): Promise<Response> {
  const body = await readJson<{
    name?: string;
    description?: string | null;
    status?: string;
    owner_user_id?: string | null;
  }>(request);

  if (!body || typeof body.name !== 'string' || body.name.trim().length === 0) {
    return jsonError(400, 'Project name is required');
  }

  const status = body.status ?? 'active';
  if (!PROJECT_STATUSES.has(status)) {
    return jsonError(400, 'Invalid project status');
  }

  if (body.owner_user_id !== undefined && body.owner_user_id !== null && typeof body.owner_user_id !== 'string') {
    return jsonError(400, 'owner_user_id must be a string');
  }

  const id = crypto.randomUUID();
  await execute(
    db,
    `INSERT INTO projects (id, name, description, status, owner_user_id)
     VALUES (?, ?, ?, ?, ?)`,
    [id, body.name.trim(), body.description ?? null, status, body.owner_user_id ?? null]
  );

  return getProject(db, id);
}

async function updateProject(db: D1Database, request: Request, id: string): Promise<Response> {
  const body = await readJson<{
    name?: string;
    description?: string | null;
    status?: string;
    owner_user_id?: string | null;
  }>(request);

  if (!body) {
    return jsonError(400, 'Invalid JSON');
  }

  const updates: string[] = [];
  const params: unknown[] = [];

  if (body.name !== undefined) {
    if (typeof body.name !== 'string' || body.name.trim().length === 0) {
      return jsonError(400, 'Project name must be a non-empty string');
    }
    updates.push('name = ?');
    params.push(body.name.trim());
  }

  if (body.description !== undefined) {
    if (body.description !== null && typeof body.description !== 'string') {
      return jsonError(400, 'description must be a string or null');
    }
    updates.push('description = ?');
    params.push(body.description ?? null);
  }

  if (body.status !== undefined) {
    if (!PROJECT_STATUSES.has(body.status)) {
      return jsonError(400, 'Invalid project status');
    }
    updates.push('status = ?');
    params.push(body.status);
  }

  if (body.owner_user_id !== undefined) {
    if (body.owner_user_id !== null && typeof body.owner_user_id !== 'string') {
      return jsonError(400, 'owner_user_id must be a string');
    }
    updates.push('owner_user_id = ?');
    params.push(body.owner_user_id ?? null);
  }

  if (updates.length === 0) {
    return jsonError(400, 'No fields to update');
  }

  updates.push("updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')");
  params.push(id);

  const result = await execute(
    db,
    `UPDATE projects SET ${updates.join(', ')} WHERE id = ?`,
    params
  );

  if (!result.changes) {
    return jsonError(404, 'Project not found');
  }

  return getProject(db, id);
}

async function deleteProject(db: D1Database, id: string): Promise<Response> {
  const result = await execute(db, 'DELETE FROM projects WHERE id = ?', [id]);
  if (!result.changes) {
    return jsonError(404, 'Project not found');
  }
  return Response.json({ ok: true });
}

async function listTasks(db: D1Database, url: URL): Promise<Response> {
  const filters: string[] = [];
  const params: unknown[] = [];

  const projectId = url.searchParams.get('project_id');
  if (projectId) {
    filters.push('project_id = ?');
    params.push(projectId);
  }

  const status = url.searchParams.get('status');
  if (status) {
    if (!TASK_STATUSES.has(status)) {
      return jsonError(400, 'Invalid task status');
    }
    filters.push('status = ?');
    params.push(status);
  }

  const where = filters.length ? `WHERE ${filters.join(' AND ')}` : '';
  const tasks = await queryAll<TaskRow>(
    db,
    `SELECT id, project_id, title, status, priority, due_date, created_at, updated_at
     FROM tasks
     ${where}
     ORDER BY created_at DESC`,
    params
  );

  return Response.json({ ok: true, tasks });
}

async function getTask(db: D1Database, id: string): Promise<Response> {
  const task = await queryOne<TaskRow>(
    db,
    `SELECT id, project_id, title, status, priority, due_date, created_at, updated_at
     FROM tasks
     WHERE id = ?`,
    [id]
  );

  if (!task) {
    return jsonError(404, 'Task not found');
  }

  return Response.json({ ok: true, task });
}

async function createTask(db: D1Database, request: Request): Promise<Response> {
  const body = await readJson<{
    project_id?: string | null;
    title?: string;
    status?: string;
    priority?: number;
    due_date?: string | null;
  }>(request);

  if (!body || typeof body.title !== 'string' || body.title.trim().length === 0) {
    return jsonError(400, 'Task title is required');
  }

  const status = body.status ?? 'todo';
  if (!TASK_STATUSES.has(status)) {
    return jsonError(400, 'Invalid task status');
  }

  const priority = body.priority ?? 2;
  if (!Number.isInteger(priority)) {
    return jsonError(400, 'priority must be an integer');
  }

  if (body.project_id !== undefined && body.project_id !== null && typeof body.project_id !== 'string') {
    return jsonError(400, 'project_id must be a string');
  }

  if (body.due_date !== undefined && body.due_date !== null && typeof body.due_date !== 'string') {
    return jsonError(400, 'due_date must be a string');
  }

  const id = crypto.randomUUID();
  await execute(
    db,
    `INSERT INTO tasks (id, project_id, title, status, priority, due_date)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [id, body.project_id ?? null, body.title.trim(), status, priority, body.due_date ?? null]
  );

  return getTask(db, id);
}

async function updateTask(db: D1Database, request: Request, id: string): Promise<Response> {
  const body = await readJson<{
    project_id?: string | null;
    title?: string;
    status?: string;
    priority?: number;
    due_date?: string | null;
  }>(request);

  if (!body) {
    return jsonError(400, 'Invalid JSON');
  }

  const updates: string[] = [];
  const params: unknown[] = [];

  if (body.project_id !== undefined) {
    if (body.project_id !== null && typeof body.project_id !== 'string') {
      return jsonError(400, 'project_id must be a string');
    }
    updates.push('project_id = ?');
    params.push(body.project_id ?? null);
  }

  if (body.title !== undefined) {
    if (typeof body.title !== 'string' || body.title.trim().length === 0) {
      return jsonError(400, 'Task title must be a non-empty string');
    }
    updates.push('title = ?');
    params.push(body.title.trim());
  }

  if (body.status !== undefined) {
    if (!TASK_STATUSES.has(body.status)) {
      return jsonError(400, 'Invalid task status');
    }
    updates.push('status = ?');
    params.push(body.status);
  }

  if (body.priority !== undefined) {
    if (!Number.isInteger(body.priority)) {
      return jsonError(400, 'priority must be an integer');
    }
    updates.push('priority = ?');
    params.push(body.priority);
  }

  if (body.due_date !== undefined) {
    if (body.due_date !== null && typeof body.due_date !== 'string') {
      return jsonError(400, 'due_date must be a string');
    }
    updates.push('due_date = ?');
    params.push(body.due_date ?? null);
  }

  if (updates.length === 0) {
    return jsonError(400, 'No fields to update');
  }

  updates.push("updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')");
  params.push(id);

  const result = await execute(
    db,
    `UPDATE tasks SET ${updates.join(', ')} WHERE id = ?`,
    params
  );

  if (!result.changes) {
    return jsonError(404, 'Task not found');
  }

  return getTask(db, id);
}

async function deleteTask(db: D1Database, id: string): Promise<Response> {
  const result = await execute(db, 'DELETE FROM tasks WHERE id = ?', [id]);
  if (!result.changes) {
    return jsonError(404, 'Task not found');
  }
  return Response.json({ ok: true });
}

export async function handleApi(request: Request, env: Env): Promise<Response | null> {
  const url = new URL(request.url);
  const path = url.pathname.replace(/\/+$/, '');
  const parts = path.split('/').filter(Boolean);

  if (parts[0] !== 'api' || parts.length < 2) {
    return null;
  }

  if (parts[1] === 'status') {
    return null;
  }

  const dbOrResponse = requireDb(env);
  if (dbOrResponse instanceof Response) {
    return dbOrResponse;
  }
  const db = dbOrResponse;

  if (parts[1] === 'projects') {
    if (request.method === 'GET' && parts.length === 2) {
      return listProjects(db);
    }
    if (request.method === 'POST' && parts.length === 2) {
      return createProject(db, request);
    }
    if (parts.length === 3) {
      const id = parts[2];
      if (request.method === 'GET') {
        return getProject(db, id);
      }
      if (request.method === 'PATCH') {
        return updateProject(db, request, id);
      }
      if (request.method === 'DELETE') {
        return deleteProject(db, id);
      }
    }
  }

  if (parts[1] === 'tasks') {
    if (request.method === 'GET' && parts.length === 2) {
      return listTasks(db, url);
    }
    if (request.method === 'POST' && parts.length === 2) {
      return createTask(db, request);
    }
    if (parts.length === 3) {
      const id = parts[2];
      if (request.method === 'GET') {
        return getTask(db, id);
      }
      if (request.method === 'PATCH') {
        return updateTask(db, request, id);
      }
      if (request.method === 'DELETE') {
        return deleteTask(db, id);
      }
    }
  }

  return jsonError(404, 'Not Found');
}