/**
 * Team time overview query helpers — team-time-overview (wave-8 leaf8).
 *
 * Provides manager-level aggregations over time_entries:
 *   - getTeamTimeOverview: per-member totals + project breakdown
 *   - getMemberTimeDetail: per-entry detail grouped by project/day
 *
 * Verified column names from schema/time.ts:
 *   time_entries: id, tenant_id, user_id, project_id, task_id, description,
 *     started_at, stopped_at, duration_seconds, billable, locked_at
 * Verified from schema/users.ts:   id, name, email
 * Verified from schema/projects.ts: id, name
 */
import { sql } from 'drizzle-orm'
import type { Db } from '../client'

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

export interface TeamMemberProjectBreakdown {
  projectId: string
  projectName: string
  totalHours: number
  billableHours: number
}

export interface TeamMemberOverview {
  userId: string
  userName: string | null
  userEmail: string | null
  totalHours: number
  billableHours: number
  projects: TeamMemberProjectBreakdown[]
}

export interface TeamTimeOverviewResult {
  members: TeamMemberOverview[]
  totalHours: number
  billableHours: number
  startDate: string
  endDate: string
}

export interface MemberDayEntry {
  id: string
  projectId: string
  projectName: string
  taskId: string | null
  taskTitle: string | null
  description: string | null
  startedAt: string
  stoppedAt: string | null
  durationSeconds: number | null
  billable: boolean
  lockedAt: string | null
}

export interface MemberDayGroup {
  date: string // YYYY-MM-DD
  totalHours: number
  entries: MemberDayEntry[]
}

export interface MemberProjectGroup {
  projectId: string
  projectName: string
  totalHours: number
  days: MemberDayGroup[]
}

export interface MemberTimeDetailResult {
  userId: string
  userName: string | null
  userEmail: string | null
  totalHours: number
  billableHours: number
  projectGroups: MemberProjectGroup[]
}

// ── Helpers ────────────────────────────────────────────────────────────────────

function secondsToHours(seconds: number | null): number {
  if (seconds == null) return 0
  return Math.round((seconds / 3600) * 100) / 100
}

// ── getTeamTimeOverview ────────────────────────────────────────────────────────

/**
 * Returns per-member aggregated hours + project breakdown for a date range.
 * Only includes users (not contractors) with at least one entry in range.
 */
export async function getTeamTimeOverview(
  db: Db,
  tenantId: string,
  startDate: string,
  endDate: string,
): Promise<TeamTimeOverviewResult> {
  // Aggregate per-member per-project
  const rows = await db.execute<{
    user_id: string
    user_name: string | null
    user_email: string | null
    project_id: string
    project_name: string
    total_seconds: string
    billable_seconds: string
  }>(sql`
    SELECT
      te.user_id,
      u.name        AS user_name,
      u.email       AS user_email,
      te.project_id,
      p.name        AS project_name,
      COALESCE(SUM(te.duration_seconds), 0)::text                                           AS total_seconds,
      COALESCE(SUM(CASE WHEN te.billable THEN te.duration_seconds ELSE 0 END), 0)::text     AS billable_seconds
    FROM time_entries te
    JOIN users  u ON u.id = te.user_id
    JOIN projects p ON p.id = te.project_id
    WHERE
      te.tenant_id   = ${tenantId}::uuid
      AND te.user_id IS NOT NULL
      AND te.started_at >= ${startDate}::timestamptz
      AND te.started_at <  (${endDate}::date + INTERVAL '1 day')::timestamptz
    GROUP BY te.user_id, u.name, u.email, te.project_id, p.name
    ORDER BY u.name, p.name
  `)

  // Pivot into nested structure
  const memberMap = new Map<string, TeamMemberOverview>()
  let grandTotalSec = 0
  let grandBillableSec = 0

  for (const row of rows) {
    const totalSec = Number(row.total_seconds)
    const billableSec = Number(row.billable_seconds)
    grandTotalSec += totalSec
    grandBillableSec += billableSec

    let member = memberMap.get(row.user_id)
    if (!member) {
      member = {
        userId: row.user_id,
        userName: row.user_name,
        userEmail: row.user_email,
        totalHours: 0,
        billableHours: 0,
        projects: [],
      }
      memberMap.set(row.user_id, member)
    }

    member.totalHours = Math.round((member.totalHours * 100 + secondsToHours(totalSec) * 100)) / 100
    member.billableHours = Math.round((member.billableHours * 100 + secondsToHours(billableSec) * 100)) / 100
    member.projects.push({
      projectId: row.project_id,
      projectName: row.project_name,
      totalHours: secondsToHours(totalSec),
      billableHours: secondsToHours(billableSec),
    })
  }

  return {
    members: Array.from(memberMap.values()),
    totalHours: secondsToHours(grandTotalSec),
    billableHours: secondsToHours(grandBillableSec),
    startDate,
    endDate,
  }
}

// ── getMemberTimeDetail ────────────────────────────────────────────────────────

/**
 * All time entries for a single member in a date range, grouped by project/day.
 */
export async function getMemberTimeDetail(
  db: Db,
  tenantId: string,
  userId: string,
  startDate: string,
  endDate: string,
): Promise<MemberTimeDetailResult> {
  // Get user info
  const userRows = await db.execute<{
    user_name: string | null
    user_email: string | null
  }>(sql`
    SELECT name AS user_name, email AS user_email
    FROM users
    WHERE id = ${userId}::uuid
    LIMIT 1
  `)

  const userInfo = userRows[0] ?? { user_name: null, user_email: null }

  // Get all entries with project + task info
  const entryRows = await db.execute<{
    id: string
    project_id: string
    project_name: string
    task_id: string | null
    task_title: string | null
    description: string | null
    started_at: string
    stopped_at: string | null
    duration_seconds: string | null
    billable: boolean
    locked_at: string | null
  }>(sql`
    SELECT
      te.id,
      te.project_id,
      p.name         AS project_name,
      te.task_id,
      t.title        AS task_title,
      te.description,
      te.started_at::text  AS started_at,
      te.stopped_at::text  AS stopped_at,
      te.duration_seconds::text AS duration_seconds,
      te.billable,
      te.locked_at::text   AS locked_at
    FROM time_entries te
    JOIN projects p ON p.id = te.project_id
    LEFT JOIN tasks t ON t.id = te.task_id
    WHERE
      te.tenant_id   = ${tenantId}::uuid
      AND te.user_id = ${userId}::uuid
      AND te.started_at >= ${startDate}::timestamptz
      AND te.started_at <  (${endDate}::date + INTERVAL '1 day')::timestamptz
    ORDER BY te.project_id, te.started_at DESC
  `)

  // Group by project then by date
  const projectMap = new Map<string, { name: string; dayMap: Map<string, MemberDayEntry[]> }>()
  let totalSec = 0
  let billableSec = 0

  for (const row of entryRows) {
    const durSec = row.duration_seconds !== null ? Number(row.duration_seconds) : null
    totalSec += durSec ?? 0
    if (row.billable) billableSec += durSec ?? 0

    const date = row.started_at.slice(0, 10) // YYYY-MM-DD

    let proj = projectMap.get(row.project_id)
    if (!proj) {
      proj = { name: row.project_name, dayMap: new Map() }
      projectMap.set(row.project_id, proj)
    }

    let dayEntries = proj.dayMap.get(date)
    if (!dayEntries) {
      dayEntries = []
      proj.dayMap.set(date, dayEntries)
    }

    dayEntries.push({
      id: row.id,
      projectId: row.project_id,
      projectName: row.project_name,
      taskId: row.task_id,
      taskTitle: row.task_title,
      description: row.description,
      startedAt: row.started_at,
      stoppedAt: row.stopped_at,
      durationSeconds: durSec,
      billable: row.billable,
      lockedAt: row.locked_at,
    })
  }

  const projectGroups: MemberProjectGroup[] = Array.from(projectMap.entries()).map(
    ([projectId, proj]) => {
      const days: MemberDayGroup[] = Array.from(proj.dayMap.entries()).map(([date, entries]) => ({
        date,
        totalHours: secondsToHours(entries.reduce((s, e) => s + (e.durationSeconds ?? 0), 0)),
        entries,
      }))
      const projTotal = days.reduce((s, d) => s + d.totalHours, 0)
      return {
        projectId,
        projectName: proj.name,
        totalHours: Math.round(projTotal * 100) / 100,
        days,
      }
    },
  )

  return {
    userId,
    userName: userInfo.user_name,
    userEmail: userInfo.user_email,
    totalHours: secondsToHours(totalSec),
    billableHours: secondsToHours(billableSec),
    projectGroups,
  }
}