import { and, eq, sql, count } from 'drizzle-orm'
import type { Db } from '../client'
import { projects, projectMembers } from '../schema/projects'
import { timeEntries } from '../schema/time'
import { users } from '../schema/users'
import { contractors, contractorAssignments } from '../schema/contractors'
import { tasks, taskStatuses } from '../schema/tasks'
import { invoices } from '../schema/invoices'
import { expenses } from '../schema/expenses'
import type { ProjectAccess } from './projects'

export interface ProjectAnalyticsMemberRow {
  user_id: string | null
  name: string
  hours: number
  pct: number
}

export interface ProjectAnalyticsWeekRow {
  week_start: string
  hours: number
}

export interface ProjectAnalyticsBudget {
  budget_hours: number
  logged_hours: number
  over_budget: boolean
}

export interface ProjectAnalyticsFinancials {
  invoiced_total: string
  collected_total: string
  outstanding_total: string
  invoice_count: number
  expenses_total: string
  expense_count: number
  team_cost: string
  gross_margin: string
  gross_margin_pct: number | null
}

export interface ProjectAnalytics {
  time: {
    total_hours: number
    this_week_hours: number
    last_week_hours: number
    by_member: ProjectAnalyticsMemberRow[]
    weekly_trend: ProjectAnalyticsWeekRow[]
  }
  tasks: {
    total: number
    done: number
    open: number
  }
  budget?: ProjectAnalyticsBudget
  financials?: ProjectAnalyticsFinancials
}

const BUSINESS_TIERS = new Set(['business', 'enterprise', 'white_label'])

function callerIsMember(tenantId: string, userId: string) {
  return sql`EXISTS (
    SELECT 1
    FROM project_members pm
    WHERE pm.project_id = ${projects.id}
      AND pm.tenant_id = ${tenantId}
      AND pm.user_id = ${userId}
  )`
}

function roundHours(value: unknown): number {
  return Math.round(Number(value ?? 0) * 100) / 100
}

function roundMoney(value: number): string {
  return value.toFixed(2)
}

function weekStartIso(date: Date): string {
  return date.toISOString().slice(0, 10)
}

function buildWeekSeries(weeks: number, rows: Array<{ week_start: string; hours: number }>) {
  const byWeek = new Map(rows.map((row) => [row.week_start, roundHours(row.hours)]))
  const now = new Date()
  const day = now.getUTCDay()
  const diff = day === 0 ? -6 : 1 - day
  const currentWeekStart = new Date(
    Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate() + diff),
  )

  const series: ProjectAnalyticsWeekRow[] = []
  for (let index = weeks - 1; index >= 0; index -= 1) {
    const start = new Date(currentWeekStart)
    start.setUTCDate(currentWeekStart.getUTCDate() - index * 7)
    const key = weekStartIso(start)
    series.push({
      week_start: key,
      hours: byWeek.get(key) ?? 0,
    })
  }
  return series
}

export async function getProjectAnalytics(
  db: Db,
  tenantId: string,
  projectId: string,
  options?: {
    access?: ProjectAccess
    includeFinancials?: boolean
    weeks?: number
    tier?: string | null
  },
): Promise<ProjectAnalytics | null> {
  const weeks = Math.max(1, Math.min(options?.weeks ?? 8, 26))
  const includeFinancials =
    options?.includeFinancials === true &&
    BUSINESS_TIERS.has(options?.tier ?? 'freelancer')

  const projectConditions = [eq(projects.tenantId, tenantId), eq(projects.id, projectId)]
  if (options?.access && !options.access.fullVisibility) {
    projectConditions.push(callerIsMember(tenantId, options.access.userId))
  }

  const [project] = await db
    .select({
      id: projects.id,
      billingConfig: projects.billingConfig,
    })
    .from(projects)
    .where(and(...projectConditions))
    .limit(1)

  if (!project) return null

  const [timeAgg] = await db
    .select({
      totalHours: sql<string>`COALESCE(SUM(${timeEntries.durationSeconds}) / 3600.0, 0)`,
      thisWeekHours: sql<string>`COALESCE(SUM(CASE
        WHEN ${timeEntries.stoppedAt} IS NOT NULL
          AND ${timeEntries.startedAt} >= date_trunc('week', now())
        THEN ${timeEntries.durationSeconds}
        ELSE 0
      END) / 3600.0, 0)`,
      lastWeekHours: sql<string>`COALESCE(SUM(CASE
        WHEN ${timeEntries.stoppedAt} IS NOT NULL
          AND ${timeEntries.startedAt} >= date_trunc('week', now()) - interval '1 week'
          AND ${timeEntries.startedAt} < date_trunc('week', now())
        THEN ${timeEntries.durationSeconds}
        ELSE 0
      END) / 3600.0, 0)`,
    })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
        sql`${timeEntries.stoppedAt} IS NOT NULL`,
      ),
    )

  const totalHours = roundHours(timeAgg?.totalHours)

  const memberRows = await db
    .select({
      userId: sql<string | null>`COALESCE(${timeEntries.userId}::text, ${timeEntries.contractorId}::text)`,
      name: sql<string>`COALESCE(${users.name}, ${contractors.name}, 'Unknown')`,
      hours: sql<string>`COALESCE(SUM(${timeEntries.durationSeconds}) / 3600.0, 0)`,
    })
    .from(timeEntries)
    .leftJoin(users, eq(timeEntries.userId, users.id))
    .leftJoin(contractors, eq(timeEntries.contractorId, contractors.id))
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
        sql`${timeEntries.stoppedAt} IS NOT NULL`,
      ),
    )
    .groupBy(
      sql`COALESCE(${timeEntries.userId}::text, ${timeEntries.contractorId}::text)`,
      sql`COALESCE(${users.name}, ${contractors.name}, 'Unknown')`,
    )
    .orderBy(sql`COALESCE(SUM(${timeEntries.durationSeconds}), 0) DESC`)

  const weeklyRows = await db
    .select({
      week_start: sql<string>`TO_CHAR(date_trunc('week', ${timeEntries.startedAt}), 'YYYY-MM-DD')`,
      hours: sql<string>`COALESCE(SUM(${timeEntries.durationSeconds}) / 3600.0, 0)`,
    })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
        sql`${timeEntries.stoppedAt} IS NOT NULL`,
        sql`${timeEntries.startedAt} >= date_trunc('week', now()) - (${weeks - 1} * interval '1 week')`,
      ),
    )
    .groupBy(sql`date_trunc('week', ${timeEntries.startedAt})`)
    .orderBy(sql`date_trunc('week', ${timeEntries.startedAt}) ASC`)

  const [taskAgg] = await db
    .select({
      total: count(tasks.id),
      done: sql<number>`COUNT(CASE WHEN ${taskStatuses.isTerminal} = true THEN 1 END)`,
      open: sql<number>`COUNT(CASE WHEN ${taskStatuses.isTerminal} = false THEN 1 END)`,
    })
    .from(tasks)
    .innerJoin(taskStatuses, eq(tasks.statusId, taskStatuses.id))
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.projectId, projectId)))

  const billingConfig = (project.billingConfig ?? {}) as { budget_hours?: number | null }
  const budgetHours =
    typeof billingConfig.budget_hours === 'number' ? billingConfig.budget_hours : null

  const analytics: ProjectAnalytics = {
    time: {
      total_hours: totalHours,
      this_week_hours: roundHours(timeAgg?.thisWeekHours),
      last_week_hours: roundHours(timeAgg?.lastWeekHours),
      by_member: memberRows.map((row) => {
        const hours = roundHours(row.hours)
        return {
          user_id: row.userId,
          name: row.name,
          hours,
          pct: totalHours > 0 ? Math.round((hours / totalHours) * 100) : 0,
        }
      }),
      weekly_trend: buildWeekSeries(
        weeks,
        weeklyRows.map((row) => ({
          week_start: row.week_start,
          hours: roundHours(row.hours),
        })),
      ),
    },
    tasks: {
      total: Number(taskAgg?.total ?? 0),
      done: Number(taskAgg?.done ?? 0),
      open: Number(taskAgg?.open ?? 0),
    },
    ...(budgetHours && budgetHours > 0
      ? {
          budget: {
            budget_hours: budgetHours,
            logged_hours: totalHours,
            over_budget: totalHours > budgetHours,
          },
        }
      : {}),
  }

  if (!includeFinancials) {
    return analytics
  }

  const [invoiceAgg] = await db
    .select({
      invoicedTotal: sql<string>`COALESCE(SUM(${invoices.total}), 0)::text`,
      collectedTotal: sql<string>`COALESCE(SUM(${invoices.amountPaid}), 0)::text`,
      outstandingTotal: sql<string>`COALESCE(SUM(GREATEST(${invoices.total} - ${invoices.amountPaid}, 0)), 0)::text`,
      invoiceCount: count(invoices.id),
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.projectId, projectId),
        sql`${invoices.status} NOT IN ('VOID', 'WRITTEN_OFF', 'BAD_DEBT', 'REJECTED')`,
      ),
    )

  const [expenseAgg] = await db
    .select({
      expensesTotal: sql<string>`COALESCE(SUM(${expenses.amount}), 0)::text`,
      expenseCount: count(expenses.id),
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        eq(expenses.projectId, projectId),
        sql`${expenses.amount} IS NOT NULL`,
        sql`${expenses.deletedAt} IS NULL`,
      ),
    )

  const [teamCostAgg] = await db
    .select({
      teamCost: sql<string>`COALESCE(SUM(
        (${timeEntries.durationSeconds}::numeric / 3600) * COALESCE(
          ${projectMembers.hourlyRate},
          ${contractorAssignments.rateOverride},
          ${contractors.hourlyRate},
          (${projects.billingConfig}->>'rate_per_hour')::numeric,
          0
        )
      ), 0)::text`,
    })
    .from(timeEntries)
    .innerJoin(projects, eq(timeEntries.projectId, projects.id))
    .leftJoin(
      projectMembers,
      and(
        eq(projectMembers.projectId, timeEntries.projectId),
        eq(projectMembers.userId, timeEntries.userId),
        eq(projectMembers.tenantId, timeEntries.tenantId),
      ),
    )
    .leftJoin(
      contractorAssignments,
      and(
        eq(contractorAssignments.projectId, timeEntries.projectId),
        eq(contractorAssignments.contractorId, timeEntries.contractorId),
        eq(contractorAssignments.tenantId, timeEntries.tenantId),
      ),
    )
    .leftJoin(contractors, eq(contractors.id, timeEntries.contractorId))
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
        sql`${timeEntries.stoppedAt} IS NOT NULL`,
      ),
    )

  const invoicedTotal = Number(invoiceAgg?.invoicedTotal ?? '0')
  const collectedTotal = Number(invoiceAgg?.collectedTotal ?? '0')
  const outstandingTotal = Number(invoiceAgg?.outstandingTotal ?? '0')
  const expensesTotal = Number(expenseAgg?.expensesTotal ?? '0')
  const teamCost = Number(teamCostAgg?.teamCost ?? '0')
  const grossMargin = invoicedTotal - expensesTotal - teamCost
  const grossMarginPct = invoicedTotal > 0 ? Math.round((grossMargin / invoicedTotal) * 10000) / 100 : null

  analytics.financials = {
    invoiced_total: roundMoney(invoicedTotal),
    collected_total: roundMoney(collectedTotal),
    outstanding_total: roundMoney(outstandingTotal),
    invoice_count: Number(invoiceAgg?.invoiceCount ?? 0),
    expenses_total: roundMoney(expensesTotal),
    expense_count: Number(expenseAgg?.expenseCount ?? 0),
    team_cost: roundMoney(teamCost),
    gross_margin: roundMoney(grossMargin),
    gross_margin_pct: grossMarginPct,
  }

  return analytics
}