/**
 * Custom analytics dashboard query helpers — reports-analytics (wave C).
 *
 * Tables: dashboards, dashboard_widgets (per-user, tenant-scoped).
 */
import { and, asc, desc, eq, ne } from 'drizzle-orm'
import type { Db } from '../client'
import { dashboards, dashboardWidgets } from '../schema/dashboards'
import type { DashboardRow, DashboardWidgetRow } from '../schema/dashboards'

export const WIDGET_TYPES = [
  'revenue_kpi',
  'invoice_status_pipeline',
  'open_invoices_aging',
  'time_by_project',
  'time_by_team_member',
  'billable_vs_nonbillable',
  'customer_revenue',
  'expense_breakdown',
  'leads_funnel',
  'lead_pipeline_by_stage',
  'ticket_resolution_time',
  'ticket_volume_by_category',
] as const

export type WidgetType = (typeof WIDGET_TYPES)[number]

export interface DashboardWithWidgets {
  dashboard: DashboardRow
  widgets: DashboardWidgetRow[]
}

export async function listDashboardsForUser(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<DashboardRow[]> {
  return db
    .select()
    .from(dashboards)
    .where(and(eq(dashboards.tenantId, tenantId), eq(dashboards.userId, userId)))
    .orderBy(desc(dashboards.isDefault), asc(dashboards.name))
}

export async function ensureDefaultDashboard(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<DashboardRow[]> {
  const existing = await listDashboardsForUser(db, tenantId, userId)
  if (existing.length > 0) return existing

  await db
    .insert(dashboards)
    .values({
      tenantId,
      userId,
      name: 'Overview',
      isDefault: true,
    })
    .onConflictDoNothing({
      target: [dashboards.tenantId, dashboards.userId],
      where: eq(dashboards.isDefault, true),
    })

  const rows = await listDashboardsForUser(db, tenantId, userId)
  if (rows.length === 0) throw new Error('Failed to create default dashboard')
  return rows
}

export async function getDashboardForUser(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
): Promise<DashboardRow | null> {
  const [row] = await db
    .select()
    .from(dashboards)
    .where(
      and(
        eq(dashboards.id, dashboardId),
        eq(dashboards.tenantId, tenantId),
        eq(dashboards.userId, userId),
      ),
    )
    .limit(1)
  return row ?? null
}

export async function getDashboardWithWidgets(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
): Promise<DashboardWithWidgets | null> {
  const dashboard = await getDashboardForUser(db, tenantId, userId, dashboardId)
  if (!dashboard) return null

  const widgets = await db
    .select()
    .from(dashboardWidgets)
    .where(
      and(
        eq(dashboardWidgets.dashboardId, dashboardId),
        eq(dashboardWidgets.tenantId, tenantId),
      ),
    )
    .orderBy(asc(dashboardWidgets.positionY), asc(dashboardWidgets.positionX))

  return { dashboard, widgets }
}

export async function createDashboard(
  db: Db,
  tenantId: string,
  userId: string,
  name: string,
): Promise<DashboardRow> {
  const [row] = await db
    .insert(dashboards)
    .values({ tenantId, userId, name, isDefault: false })
    .returning()
  if (!row) throw new Error('Dashboard insert failed')
  return row
}

export async function updateDashboard(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
  patch: { name?: string; isDefault?: boolean },
): Promise<DashboardRow | null> {
  const existing = await getDashboardForUser(db, tenantId, userId, dashboardId)
  if (!existing) return null

  if (patch.isDefault === true) {
    await db
      .update(dashboards)
      .set({ isDefault: false, updatedAt: new Date() })
      .where(
        and(
          eq(dashboards.tenantId, tenantId),
          eq(dashboards.userId, userId),
          ne(dashboards.id, dashboardId),
        ),
      )
  }

  const [row] = await db
    .update(dashboards)
    .set({
      ...(patch.name !== undefined ? { name: patch.name } : {}),
      ...(patch.isDefault !== undefined ? { isDefault: patch.isDefault } : {}),
      updatedAt: new Date(),
    })
    .where(
      and(
        eq(dashboards.id, dashboardId),
        eq(dashboards.tenantId, tenantId),
        eq(dashboards.userId, userId),
      ),
    )
    .returning()

  return row ?? null
}

export async function deleteDashboard(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
): Promise<boolean> {
  const result = await db
    .delete(dashboards)
    .where(
      and(
        eq(dashboards.id, dashboardId),
        eq(dashboards.tenantId, tenantId),
        eq(dashboards.userId, userId),
      ),
    )
    .returning({ id: dashboards.id })
  return result.length > 0
}

export interface AddWidgetInput {
  widgetType: WidgetType
  positionX: number
  positionY: number
  width: number
  height: number
  config?: Record<string, unknown>
}

export async function addDashboardWidget(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
  input: AddWidgetInput,
): Promise<DashboardWidgetRow | null> {
  const dashboard = await getDashboardForUser(db, tenantId, userId, dashboardId)
  if (!dashboard) return null

  const [row] = await db
    .insert(dashboardWidgets)
    .values({
      dashboardId,
      tenantId,
      widgetType: input.widgetType,
      positionX: input.positionX,
      positionY: input.positionY,
      width: input.width,
      height: input.height,
      config: input.config ?? {},
    })
    .returning()

  return row ?? null
}

export async function updateDashboardWidget(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
  widgetId: string,
  patch: Partial<{
    widgetType: WidgetType
    positionX: number
    positionY: number
    width: number
    height: number
    config: Record<string, unknown>
  }>,
): Promise<DashboardWidgetRow | null> {
  const dashboard = await getDashboardForUser(db, tenantId, userId, dashboardId)
  if (!dashboard) return null

  const [row] = await db
    .update(dashboardWidgets)
    .set({
      ...(patch.widgetType !== undefined ? { widgetType: patch.widgetType } : {}),
      ...(patch.positionX !== undefined ? { positionX: patch.positionX } : {}),
      ...(patch.positionY !== undefined ? { positionY: patch.positionY } : {}),
      ...(patch.width !== undefined ? { width: patch.width } : {}),
      ...(patch.height !== undefined ? { height: patch.height } : {}),
      ...(patch.config !== undefined ? { config: patch.config } : {}),
    })
    .where(
      and(
        eq(dashboardWidgets.id, widgetId),
        eq(dashboardWidgets.dashboardId, dashboardId),
        eq(dashboardWidgets.tenantId, tenantId),
      ),
    )
    .returning()

  return row ?? null
}

export async function deleteDashboardWidget(
  db: Db,
  tenantId: string,
  userId: string,
  dashboardId: string,
  widgetId: string,
): Promise<boolean> {
  const dashboard = await getDashboardForUser(db, tenantId, userId, dashboardId)
  if (!dashboard) return false

  const result = await db
    .delete(dashboardWidgets)
    .where(
      and(
        eq(dashboardWidgets.id, widgetId),
        eq(dashboardWidgets.dashboardId, dashboardId),
        eq(dashboardWidgets.tenantId, tenantId),
      ),
    )
    .returning({ id: dashboardWidgets.id })

  return result.length > 0
}