/**
 * Project Gantt query helpers — project-gantt (wave-8 leaf 6).
 *
 * All helpers are tenant-filtered. Route files MUST NOT import raw Drizzle tables.
 *
 * Notes on schema alignment:
 * - Milestones have only dueDate (no startDate). Rendered as point markers on timeline.
 * - Tasks have only dueDate (no startDate). Rendered as point markers on timeline.
 * - Project has startDate and endDate which bound the visible span.
 */
import { and, eq, desc } from 'drizzle-orm'
import type { Db } from '../client'
import { projects } from '../schema/projects'
import { projectMilestones } from '../schema/project-milestones'
import { tasks } from '../schema/tasks'

// ── Types ──────────────────────────────────────────────────────────────────────

export interface GanttProject {
  id: string
  name: string
  start_date: string | null
  end_date: string | null
}

export interface GanttMilestone {
  id: string
  name: string
  due_date: string | null
  status: string
  completed_at: string | null
}

export interface GanttTask {
  id: string
  name: string
  due_date: string | null
  status_id: string
  assignee_id: string | null
}

export interface GanttData {
  project: GanttProject
  milestones: GanttMilestone[]
  tasks: GanttTask[]
}

// ── getProjectGanttData ───────────────────────────────────────────────────────

export async function getProjectGanttData(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<GanttData | null> {
  const [projectRow] = await db
    .select()
    .from(projects)
    .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
    .limit(1)

  if (!projectRow) return null

  const milestoneRows = await db
    .select()
    .from(projectMilestones)
    .where(
      and(
        eq(projectMilestones.tenantId, tenantId),
        eq(projectMilestones.projectId, projectId),
      ),
    )
    .orderBy(projectMilestones.dueDate, desc(projectMilestones.createdAt))

  const taskRows = await db
    .select()
    .from(tasks)
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.projectId, projectId)))
    .orderBy(tasks.dueDate)

  const ganttProject: GanttProject = {
    id: projectRow.id,
    name: projectRow.name,
    start_date: projectRow.startDate ?? null,
    end_date: projectRow.endDate ?? null,
  }

  const ganttMilestones: GanttMilestone[] = milestoneRows.map((m) => ({
    id: m.id,
    name: m.name,
    due_date: m.dueDate ?? null,
    status: m.status,
    completed_at: m.completedAt ? m.completedAt.toISOString() : null,
  }))

  const ganttTasks: GanttTask[] = taskRows.map((t) => ({
    id: t.id,
    name: t.title,
    due_date: t.dueDate ?? null,
    status_id: t.statusId,
    assignee_id: t.assigneeId ?? null,
  }))

  return { project: ganttProject, milestones: ganttMilestones, tasks: ganttTasks }
}

// ── updateMilestoneDueDate ────────────────────────────────────────────────────

export async function updateMilestoneDueDate(
  db: Db,
  tenantId: string,
  milestoneId: string,
  dueDate: string | null,
): Promise<GanttMilestone | null> {
  const [updated] = await db
    .update(projectMilestones)
    .set({ dueDate: dueDate ?? null, updatedAt: new Date() })
    .where(
      and(
        eq(projectMilestones.tenantId, tenantId),
        eq(projectMilestones.id, milestoneId),
      ),
    )
    .returning()

  if (!updated) return null

  return {
    id: updated.id,
    name: updated.name,
    due_date: updated.dueDate ?? null,
    status: updated.status,
    completed_at: updated.completedAt ? updated.completedAt.toISOString() : null,
  }
}

// ── updateTaskDueDate ─────────────────────────────────────────────────────────

export async function updateTaskDueDate(
  db: Db,
  tenantId: string,
  taskId: string,
  dueDate: string | null,
): Promise<GanttTask | null> {
  const [updated] = await db
    .update(tasks)
    .set({ dueDate: dueDate ?? null, updatedAt: new Date() })
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.id, taskId)))
    .returning()

  if (!updated) return null

  return {
    id: updated.id,
    name: updated.title,
    due_date: updated.dueDate ?? null,
    status_id: updated.statusId,
    assignee_id: updated.assigneeId ?? null,
  }
}