/**
 * Project hourly-budget query helpers — project-hourly-budget spec.
 *
 * All helpers are tenant-filtered. Route files import via @zync/db/queries.
 * This file NEVER imports from schema/* raw tables directly — it imports
 * via drizzle-orm operators and the typed schema objects.
 *
 * Budget state lives entirely in billing_config JSONB:
 *   budget_hours         — total project lifetime hours (null = no budget)
 *   budget_alert_pct     — alert threshold percent (default 80)
 *   budget_alert_fired_at — ISO timestamp of when the threshold alert fired,
 *                           null when not yet fired or reset by budget increase.
 *
 * Alert fires once: when logged_hours/budget_hours >= budget_alert_pct/100
 * for the first time. Resetting budget_hours above current usage clears the
 * flag (achieved by sending budget_alert_fired_at: null in the billing_config
 * PATCH from the editor UI).
 */

import { and, eq, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { timeEntries } from '../schema/time'
import { projects, projectMembers } from '../schema/projects'

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

export interface HourlyBudgetConfig {
  rate_per_hour?: number
  overtime_enabled?: boolean
  overtime_threshold_hours?: number
  overtime_multiplier?: number
  budget_hours?: number | null
  budget_alert_pct?: number
  /** ISO timestamp — set internally on alert fire; cleared on budget reset. */
  budget_alert_fired_at?: string | null
}

export interface BudgetSummary {
  budget_hours: number | null
  logged_hours: number
  billable_hours: number
  alert_pct: number
  over_budget: boolean
}

// ── Aggregation ──────────────────────────────────────────────────────────────

/**
 * Compute logged_hours and billable_hours from time_entries for a project.
 * Uses raw SQL aggregation — duration_seconds is INTEGER (nullable per schema;
 * SUM ignores NULLs). All statuses, all team members + contractors.
 */
export async function getProjectLoggedHours(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<{ logged_hours: number; billable_hours: number }> {
  const rows = await db
    .select({
      logged_seconds: sql<string>`COALESCE(SUM(${timeEntries.durationSeconds}), 0)`,
      billable_seconds: sql<string>`COALESCE(SUM(CASE WHEN ${timeEntries.billable} = true THEN ${timeEntries.durationSeconds} ELSE 0 END), 0)`,
    })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
      ),
    )

  const row = rows[0]
  if (!row) return { logged_hours: 0, billable_hours: 0 }

  return {
    logged_hours: Number(row.logged_seconds) / 3600,
    billable_hours: Number(row.billable_seconds) / 3600,
  }
}

/**
 * Read billing_config for a project, validating it's an hourly project.
 * Returns null if project not found or billing_type !== 'hourly'.
 */
export async function getProjectBillingConfig(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<{ billingConfig: HourlyBudgetConfig; projectName: string } | null> {
  const rows = await db
    .select({
      billingType: projects.billingType,
      billingConfig: projects.billingConfig,
      name: projects.name,
    })
    .from(projects)
    .where(
      and(
        eq(projects.tenantId, tenantId),
        eq(projects.id, projectId),
      ),
    )
    .limit(1)

  const row = rows[0]
  if (!row || row.billingType !== 'hourly') return null

  return {
    billingConfig: (row.billingConfig ?? {}) as HourlyBudgetConfig,
    projectName: row.name,
  }
}

/**
 * Compute budget summary for a project.
 * Returns null if the project doesn't exist or isn't hourly.
 */
export async function getProjectBudgetSummary(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<BudgetSummary | null> {
  const configResult = await getProjectBillingConfig(db, tenantId, projectId)
  if (!configResult) return null

  const { billingConfig } = configResult
  const budgetHours = billingConfig.budget_hours ?? null
  const alertPct = billingConfig.budget_alert_pct ?? 80

  const { logged_hours, billable_hours } = await getProjectLoggedHours(
    db,
    tenantId,
    projectId,
  )

  const overBudget =
    budgetHours !== null && budgetHours > 0
      ? logged_hours >= budgetHours
      : false

  return {
    budget_hours: budgetHours,
    logged_hours: Math.round(logged_hours * 100) / 100,
    billable_hours: Math.round(billable_hours * 100) / 100,
    alert_pct: alertPct,
    over_budget: overBudget,
  }
}

/**
 * Mark the budget alert as fired by writing budget_alert_fired_at into
 * billing_config. Uses a JSON merge to preserve existing fields.
 */
export async function markBudgetAlertFired(
  db: Db,
  tenantId: string,
  projectId: string,
  firedAt: string,
): Promise<void> {
  await db
    .update(projects)
    .set({
      billingConfig: sql`${projects.billingConfig} || jsonb_build_object('budget_alert_fired_at', ${firedAt}::text)`,
      updatedAt: sql`NOW()`,
    })
    .where(
      and(
        eq(projects.tenantId, tenantId),
        eq(projects.id, projectId),
      ),
    )
}

/**
 * Get project members with role 'owner' (or all owners+admins in project_members).
 * Used to determine alert recipients.
 */
export async function getProjectOwnerIds(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<string[]> {
  const rows = await db
    .select({ userId: projectMembers.userId })
    .from(projectMembers)
    .where(
      and(
        eq(projectMembers.tenantId, tenantId),
        eq(projectMembers.projectId, projectId),
        sql`${projectMembers.role} IN ('owner', 'admin')`,
      ),
    )

  return rows.map((r) => r.userId)
}

/**
 * Evaluate whether the budget alert threshold has been crossed for the first
 * time and fire a notification if so.
 *
 * Designed to be called from the time route after a time entry is saved.
 * The `createNotif` and `pushWS` deps are injected by the caller to avoid
 * circular package dependencies.
 *
 * Alert lifecycle:
 *   1. Check budget_hours set and budget_alert_fired_at not set
 *   2. Compute logged_hours
 *   3. If crossed → mark fired → createNotification for each owner
 */
export async function evaluateBudgetAlert(
  db: Db,
  tenantId: string,
  projectId: string,
  deps: {
    createNotif: (
      db: Db,
      input: {
        tenantId: string
        userId: string
        type: string
        titleKey: string
        bodyKey?: string
        params?: Record<string, unknown>
        entityType?: string
        entityId?: string
      },
      opts?: {
        pushWS?: (userId: string, tenantId: string, type: string) => Promise<void>
        deliver?: (userId: string, type: string, title: string, body: string) => void
      },
    ) => Promise<{ id: string }>
    pushWS?: (userId: string, tenantId: string, type: string) => Promise<void>
    deliver?: (userId: string, type: string, title: string, body: string) => void
  },
): Promise<void> {
  const configResult = await getProjectBillingConfig(db, tenantId, projectId)
  if (!configResult) return

  const { billingConfig, projectName } = configResult
  const budgetHours = billingConfig.budget_hours
  const alertPct = billingConfig.budget_alert_pct ?? 80

  // No budget set → no alert
  if (!budgetHours || budgetHours <= 0) return

  // Alert already fired → skip
  if (billingConfig.budget_alert_fired_at) return

  const { logged_hours } = await getProjectLoggedHours(db, tenantId, projectId)
  const threshold = budgetHours * (alertPct / 100)

  if (logged_hours < threshold) return

  // Mark as fired before sending notifications (prevents double-fire on race)
  const firedAt = new Date().toISOString()
  await markBudgetAlertFired(db, tenantId, projectId, firedAt)

  const ownerIds = await getProjectOwnerIds(db, tenantId, projectId)
  if (ownerIds.length === 0) return

  const hoursRemaining = Math.max(budgetHours - logged_hours, 0)
  const notifParams = {
    project_name: projectName,
    project_id: projectId,
    logged_hours: logged_hours.toFixed(1),
    budget_hours: budgetHours,
    hours_remaining: hoursRemaining.toFixed(1),
    alert_pct: alertPct,
  }

  await Promise.allSettled(
    ownerIds.map((userId) =>
      deps.createNotif(
        db,
        {
          tenantId,
          userId,
          type: 'project_budget_alert',
          titleKey: 'notification.project_budget_alert.title',
          bodyKey: 'notification.project_budget_alert.body',
          params: notifParams,
          entityType: 'project',
          entityId: projectId,
        },
        {
          pushWS: deps.pushWS,
          deliver: deps.deliver,
        },
      ),
    ),
  )
}