/**
 * Time Reports aggregation query helpers — time-reports spec.
 *
 * All queries are tenant-scoped and use raw SQL via Drizzle's `sql` tag.
 * No schema changes — read-only aggregation over `time_entries`.
 *
 * Verified column names from packages/db/src/schema/time.ts:
 *   time_entries: id, tenant_id, user_id, contractor_id, task_id, project_id,
 *     description, started_at, stopped_at, duration_seconds, billable
 *
 * Verified from packages/db/src/schema/projects.ts: id, name (column: 'name')
 * Verified from packages/db/src/schema/users.ts:    id, name (column: 'name'), email
 * Verified from packages/db/src/schema/tasks.ts:    id, title (column: 'title')
 */
import { sql } from 'drizzle-orm'
import type { Db } from '../client'

// ── Filter input ──────────────────────────────────────────────────────────────

export interface TimeReportFilter {
  tenantId: string
  from?: string    // YYYY-MM-DD
  to?: string      // YYYY-MM-DD
  userId?: string  // optional UUID filter
  contractorId?: string // optional UUID filter
  projectId?: string // optional UUID filter
  billable?: boolean // optional billable filter
}

// ── Row types ─────────────────────────────────────────────────────────────────

export interface TimeReportByPersonRow {
  userId: string | null
  contractorId: string | null
  userName: string | null
  userEmail: string | null
  roleLabel: string
  totalSeconds: number
  billableSeconds: number
  entryCount: number
}

export interface TimeReportByProjectRow {
  projectId: string
  projectName: string
  customerName: string | null
  memberNames: string[]
  totalSeconds: number
  billableSeconds: number
  entryCount: number
}

export interface TimeReportByTaskRow {
  taskId: string | null
  taskTitle: string | null
  projectId: string
  projectName: string
  personName: string
  totalSeconds: number
  billableSeconds: number
  entryCount: number
}

export interface TimeReportTotals {
  totalSeconds: number
  billableSeconds: number
  entryCount: number
}

export interface TimeReportPersonOption {
  id: string
  type: 'user' | 'contractor'
  name: string | null
  email: string | null
  roleLabel: string
}

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

/** Convert seconds to decimal hours (2 decimal places). */
export function secondsToHours(seconds: number): number {
  return Math.round((seconds / 3600) * 100) / 100
}

/** Format seconds as "Xh Ym" display string. */
export function formatHoursMinutes(seconds: number): string {
  const totalMinutes = Math.floor(seconds / 60)
  const h = Math.floor(totalMinutes / 60)
  const m = totalMinutes % 60
  return `${h}h ${String(m).padStart(2, '0')}m`
}

// ── Query: by person ──────────────────────────────────────────────────────────

export async function getTimeReportByPerson(
  db: Db,
  filter: TimeReportFilter,
): Promise<TimeReportByPersonRow[]> {
  const conditions = buildConditions(filter)

  const rows = await db.execute(sql`
    SELECT
      te.user_id AS "userId",
      te.contractor_id AS "contractorId",
      COALESCE(u.name, c.name) AS "userName",
      u.email AS "userEmail",
      CASE
        WHEN te.contractor_id IS NOT NULL THEN 'Contractor'
        WHEN r.name IS NOT NULL THEN INITCAP(r.name)
        ELSE 'Member'
      END AS "roleLabel",
      COALESCE(SUM(te.duration_seconds), 0)::integer AS "totalSeconds",
      COALESCE(SUM(CASE WHEN te.billable = true THEN te.duration_seconds ELSE 0 END), 0)::integer AS "billableSeconds",
      COUNT(te.id)::integer AS "entryCount"
    FROM time_entries te
    LEFT JOIN users u ON u.id = te.user_id
    LEFT JOIN contractors c ON c.id = te.contractor_id
    LEFT JOIN tenant_memberships tm ON tm.user_id = te.user_id AND tm.tenant_id = te.tenant_id
    LEFT JOIN roles r ON r.id = tm.role_id
    WHERE ${conditions}
      AND te.stopped_at IS NOT NULL
    GROUP BY te.user_id, te.contractor_id, u.name, c.name, u.email, r.name
    ORDER BY "totalSeconds" DESC
  `)

  return rows as unknown as TimeReportByPersonRow[]
}

// ── Query: by project ─────────────────────────────────────────────────────────

export async function getTimeReportByProject(
  db: Db,
  filter: TimeReportFilter,
): Promise<TimeReportByProjectRow[]> {
  const conditions = buildConditions(filter)

  const rows = await db.execute(sql`
    SELECT
      te.project_id AS "projectId",
      p.name AS "projectName",
      c.name AS "customerName",
      ARRAY_REMOVE(
        ARRAY_AGG(DISTINCT COALESCE(u.name, ctr.name)),
        NULL
      ) AS "memberNames",
      COALESCE(SUM(te.duration_seconds), 0)::integer AS "totalSeconds",
      COALESCE(SUM(CASE WHEN te.billable = true THEN te.duration_seconds ELSE 0 END), 0)::integer AS "billableSeconds",
      COUNT(te.id)::integer AS "entryCount"
    FROM time_entries te
    INNER JOIN projects p ON p.id = te.project_id
    LEFT JOIN customers c ON c.id = p.customer_id
    LEFT JOIN users u ON u.id = te.user_id
    LEFT JOIN contractors ctr ON ctr.id = te.contractor_id
    WHERE ${conditions}
      AND te.stopped_at IS NOT NULL
    GROUP BY te.project_id, p.name, c.name
    ORDER BY "totalSeconds" DESC
  `)

  return rows as unknown as TimeReportByProjectRow[]
}

// ── Query: by task ────────────────────────────────────────────────────────────

export async function getTimeReportByTask(
  db: Db,
  filter: TimeReportFilter,
): Promise<TimeReportByTaskRow[]> {
  const conditions = buildConditions(filter)

  const rows = await db.execute(sql`
    SELECT
      te.task_id AS "taskId",
      t.title AS "taskTitle",
      te.project_id AS "projectId",
      p.name AS "projectName",
      COALESCE(u.name, c.name, 'Unknown') AS "personName",
      COALESCE(SUM(te.duration_seconds), 0)::integer AS "totalSeconds",
      COALESCE(SUM(CASE WHEN te.billable = true THEN te.duration_seconds ELSE 0 END), 0)::integer AS "billableSeconds",
      COUNT(te.id)::integer AS "entryCount"
    FROM time_entries te
    INNER JOIN projects p ON p.id = te.project_id
    LEFT JOIN tasks t ON t.id = te.task_id
    LEFT JOIN users u ON u.id = te.user_id
    LEFT JOIN contractors c ON c.id = te.contractor_id
    WHERE ${conditions}
      AND te.stopped_at IS NOT NULL
    GROUP BY te.task_id, t.title, te.project_id, p.name, u.name, c.name
    ORDER BY "totalSeconds" DESC
  `)

  return rows as unknown as TimeReportByTaskRow[]
}

// ── Query: totals ─────────────────────────────────────────────────────────────

export async function getTimeReportTotals(
  db: Db,
  filter: TimeReportFilter,
): Promise<TimeReportTotals> {
  const conditions = buildConditions(filter)

  const rows = await db.execute(sql`
    SELECT
      COALESCE(SUM(te.duration_seconds), 0)::integer AS "totalSeconds",
      COALESCE(SUM(CASE WHEN te.billable = true THEN te.duration_seconds ELSE 0 END), 0)::integer AS "billableSeconds",
      COUNT(te.id)::integer AS "entryCount"
    FROM time_entries te
    WHERE ${conditions}
      AND te.stopped_at IS NOT NULL
  `)

  const row = rows[0] as unknown as TimeReportTotals | undefined
  return row ?? { totalSeconds: 0, billableSeconds: 0, entryCount: 0 }
}

export async function getTimeReportPeopleOptions(
  db: Db,
  tenantId: string,
  options: { includeAllPeople: boolean; userId: string },
): Promise<TimeReportPersonOption[]> {
  if (!options.includeAllPeople) {
    const rows = await db.execute(sql`
      SELECT
        u.id AS "id",
        'user' AS "type",
        u.name AS "name",
        u.email AS "email",
        COALESCE(INITCAP(r.name), 'Member') AS "roleLabel"
      FROM users u
      INNER JOIN tenant_memberships tm ON tm.user_id = u.id
      LEFT JOIN roles r ON r.id = tm.role_id
      WHERE tm.tenant_id = ${tenantId}
        AND tm.user_id = ${options.userId}
        AND tm.status = 'active'
      LIMIT 1
    `)
    return rows as unknown as TimeReportPersonOption[]
  }

  const rows = await db.execute(sql`
    SELECT
      u.id AS "id",
      'user' AS "type",
      u.name AS "name",
      u.email AS "email",
      COALESCE(INITCAP(r.name), 'Member') AS "roleLabel"
    FROM tenant_memberships tm
    INNER JOIN users u ON u.id = tm.user_id
    LEFT JOIN roles r ON r.id = tm.role_id
    WHERE tm.tenant_id = ${tenantId}
      AND tm.status = 'active'

    UNION ALL

    SELECT
      c.id AS "id",
      'contractor' AS "type",
      c.name AS "name",
      c.email AS "email",
      'Contractor' AS "roleLabel"
    FROM contractors c
    WHERE c.tenant_id = ${tenantId}
      AND c.active = true

    ORDER BY "type", "name" NULLS LAST, "email" NULLS LAST
  `)

  return rows as unknown as TimeReportPersonOption[]
}

// ── Private: condition builder ────────────────────────────────────────────────

function buildConditions(filter: TimeReportFilter) {
  const parts: ReturnType<typeof sql>[] = [
    sql`te.tenant_id = ${filter.tenantId}`,
  ]

  if (filter.from) {
    parts.push(sql`te.started_at >= ${filter.from}::date`)
  }

  if (filter.to) {
    // inclusive: up to end of `to` day
    parts.push(sql`te.started_at < (${filter.to}::date + interval '1 day')`)
  }

  if (filter.userId) {
    parts.push(sql`te.user_id = ${filter.userId}`)
  }

  if (filter.contractorId) {
    parts.push(sql`te.contractor_id = ${filter.contractorId}`)
  }

  if (filter.projectId) {
    parts.push(sql`te.project_id = ${filter.projectId}`)
  }

  if (filter.billable !== undefined) {
    parts.push(sql`te.billable = ${filter.billable}`)
  }

  // Join all parts with AND
  return parts.reduce((acc, part) => sql`${acc} AND ${part}`)
}