/**
 * Burndown query helpers — task-estimates-burndown spec (112).
 *
 * Aggregates time_entries by day for a given project/tenant within a date range,
 * then computes daily logged cumulative and remaining hours.
 *
 * Raw SQL used intentionally: window-function + date_trunc aggregation not
 * expressible cleanly in Drizzle ORM query builder; column names match schema
 * exactly (time_entries.duration_seconds, started_at, task_id, project_id, tenant_id).
 */
import { sql } from 'drizzle-orm'
import type { Db } from '../client'
import { z } from 'zod'

// ── Zod schemas (re-exported for route-level validation) ─────────────────────

export const burndownQuerySchema = z.object({
  from: z.string().regex(/^\d{4}-\d{2}-\d{2}$/, 'from must be YYYY-MM-DD'),
  to: z.string().regex(/^\d{4}-\d{2}-\d{2}$/, 'to must be YYYY-MM-DD'),
})

export type BurndownQuery = z.infer<typeof burndownQuerySchema>

export interface BurndownDay {
  date: string           // YYYY-MM-DD
  loggedCumulative: number
  remaining: number
}

export interface BurndownResult {
  totalEstimatedHours: number
  days: BurndownDay[]
}

// ── Raw SQL aggregation ───────────────────────────────────────────────────────

/**
 * Get total estimated hours for all tasks in a project (tenant-scoped).
 * Uses tasks.estimated_hours (numeric 6,2) — coalesces NULL to 0.
 */
export async function getProjectEstimatedHours(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<number> {
  const rows = await db.execute(
    sql`
      SELECT COALESCE(SUM(estimated_hours), 0)::float8 AS total
      FROM tasks
      WHERE tenant_id = ${tenantId}
        AND project_id = ${projectId}
    `,
  )
  const row = (rows as unknown as Array<{ total: string | number }>)[0]
  return row ? Number(row.total) : 0
}

/**
 * Get daily logged hours for a project within [from, to] (inclusive), tenant-scoped.
 *
 * Joins time_entries → tasks on task_id where project_id matches.
 * Aggregates SUM(duration_seconds)/3600.0 GROUP BY date_trunc('day', started_at).
 *
 * Returns rows sorted ascending by day.
 */
async function getDailyLoggedHours(
  db: Db,
  tenantId: string,
  projectId: string,
  from: string,
  to: string,
): Promise<Array<{ day: string; logged: number }>> {
  const rows = await db.execute(
    sql`
      SELECT
        to_char(date_trunc('day', te.started_at), 'YYYY-MM-DD') AS day,
        SUM(te.duration_seconds) / 3600.0 AS logged
      FROM time_entries te
      INNER JOIN tasks t ON t.id = te.task_id
      WHERE te.tenant_id = ${tenantId}
        AND t.project_id = ${projectId}
        AND te.started_at >= ${from}::date
        AND te.started_at <  (${to}::date + INTERVAL '1 day')
      GROUP BY date_trunc('day', te.started_at)
      ORDER BY date_trunc('day', te.started_at) ASC
    `,
  )
  return (rows as unknown as Array<{ day: string; logged: string | number }>).map((r) => ({
    day: r.day,
    logged: Number(r.logged),
  }))
}

/**
 * Build the full burndown result for a project over [from, to].
 *
 * Steps:
 *   1. Fetch total estimated hours (all tasks in project).
 *   2. Fetch daily logged hours within range.
 *   3. Walk each calendar day, accumulating logged → compute remaining.
 */
export async function getProjectBurndown(
  db: Db,
  tenantId: string,
  projectId: string,
  from: string,
  to: string,
): Promise<BurndownResult> {
  const [totalEstimatedHours, dailyRows] = await Promise.all([
    getProjectEstimatedHours(db, tenantId, projectId),
    getDailyLoggedHours(db, tenantId, projectId, from, to),
  ])

  // Index daily rows by day string for O(1) lookup
  const loggedByDay = new Map<string, number>()
  for (const r of dailyRows) {
    loggedByDay.set(r.day, r.logged)
  }

  // Walk every calendar day in [from, to]
  const days: BurndownDay[] = []
  const cursor = new Date(from + 'T00:00:00Z')
  const end = new Date(to + 'T00:00:00Z')
  let cumulative = 0

  while (cursor <= end) {
    const dateStr = cursor.toISOString().slice(0, 10)
    const dayLogged = loggedByDay.get(dateStr) ?? 0
    cumulative = Math.round((cumulative + dayLogged) * 10000) / 10000
    const remaining = Math.max(0, Math.round((totalEstimatedHours - cumulative) * 10000) / 10000)
    days.push({ date: dateStr, loggedCumulative: cumulative, remaining })
    cursor.setUTCDate(cursor.getUTCDate() + 1)
  }

  return { totalEstimatedHours, days }
}