import { and, eq, inArray, isNotNull, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { timeEntries } from '../schema/time'
import { expenses } from '../schema/expenses'
import { projects } from '../schema/projects'
import { customers } from '../schema/customers'
import { users } from '../schema/users'
import { payoutBillLines, payoutBills } from '../schema/contractors'

const REVENUE_STATUSES = ['TAX_ISSUED', 'PARTIALLY_PAID', 'PAID'] as const
const PAYOUT_STATUSES = ['APPROVED', 'PAID'] as const

export interface ProfitabilityListRow {
  id: string
  name: string
  revenue: string
  cost: string
  profit: string
  margin: string | null
}

export interface ProjectProfitabilityListRow extends ProfitabilityListRow {
  customerId: string | null
  customerName: string | null
}

export interface ProfitabilitySummary {
  revenue: string
  cost: string
  profit: string
  margin: string | null
}

export interface TenantProfitabilityResult {
  from: string
  to: string
  summary: ProfitabilitySummary
  byProject: ProjectProfitabilityListRow[]
  byCustomer: ProfitabilityListRow[]
}

export interface ProjectProfitabilityRevenueLine {
  invoiceId: string
  number: string | null
  issuedAt: string | null
  amount: string
  status: string
  isCreditNote: boolean
}

export interface ProjectProfitabilityDetail {
  projectId: string
  projectName: string
  customerId: string | null
  customerName: string | null
  revenueLines: ProjectProfitabilityRevenueLine[]
  revenueTotal: string
  cost: {
    staffCost: string
    contractorCost: string
    expenseCost: string
    totalCost: string
    staffHours: string
  }
  profit: string
  margin: string | null
}

export interface CustomerProfitabilityDetail {
  customerId: string
  customerName: string
  projects: ProjectProfitabilityListRow[]
  summary: ProfitabilitySummary
}

function parseMoney(value: string | null | undefined): number {
  return Number.parseFloat(value ?? '0') || 0
}

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

function calcMargin(revenue: number, profit: number): string | null {
  if (revenue === 0) return null
  return ((profit / revenue) * 100).toFixed(2)
}

function makeSummary(rows: Array<{ revenue: string; cost: string }>): ProfitabilitySummary {
  const revenue = rows.reduce((sum, row) => sum + parseMoney(row.revenue), 0)
  const cost = rows.reduce((sum, row) => sum + parseMoney(row.cost), 0)
  const profit = revenue - cost

  return {
    revenue: formatMoney(revenue),
    cost: formatMoney(cost),
    profit: formatMoney(profit),
    margin: calcMargin(revenue, profit),
  }
}

function buildDateRangeClause(
  column: unknown,
  from: string,
  to: string,
) {
  return and(
    sql`${column} >= ${from}`,
    sql`${column} <= ${to}`,
  )
}

export async function getProjectProfitability(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<ProjectProfitabilityDetail | null> {
  const [project] = await db
    .select({
      id: projects.id,
      name: projects.name,
      customerId: projects.customerId,
      customerName: customers.name,
    })
    .from(projects)
    .leftJoin(customers, eq(projects.customerId, customers.id))
    .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
    .limit(1)

  if (!project) return null

  const revenueLines = await db
    .select({
      invoiceId: invoices.id,
      number: sql<string | null>`COALESCE(${invoices.invoiceNumber}, ${invoices.proformaNumber})`,
      issuedAt: sql<string | null>`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate})::text`,
      amount: sql<string>`${invoices.total}::text`,
      status: invoices.status,
      isCreditNote: sql<boolean>`${invoices.source} = 'credit_note'`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.projectId, projectId),
        inArray(invoices.status, REVENUE_STATUSES),
      ),
    )
    .orderBy(sql`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate}) ASC`)

  const [staffTotals] = await db
    .select({
      staffCost: sql<string>`COALESCE(SUM((${timeEntries.durationSeconds}::numeric / 3600) * ${users.hourlyCost}), 0)::text`,
      staffHours: sql<string>`COALESCE(SUM(${timeEntries.durationSeconds}::numeric) / 3600, 0)::text`,
    })
    .from(timeEntries)
    .innerJoin(users, eq(users.id, timeEntries.userId))
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
        isNotNull(users.hourlyCost),
      ),
    )

  const [contractorTotals] = await db
    .select({
      contractorCost: sql<string>`COALESCE(SUM(${payoutBillLines.lineTotal}), 0)::text`,
    })
    .from(payoutBillLines)
    .innerJoin(payoutBills, eq(payoutBills.id, payoutBillLines.billId))
    .where(
      and(
        eq(payoutBillLines.tenantId, tenantId),
        eq(payoutBillLines.projectId, projectId),
        inArray(payoutBills.status, PAYOUT_STATUSES),
      ),
    )

  const [expenseTotals] = await db
    .select({
      expenseCost: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        eq(expenses.projectId, projectId),
        eq(expenses.status, 'COMPLETED'),
      ),
    )

  const revenueTotal = revenueLines.reduce((sum, line) => sum + parseMoney(line.amount), 0)
  const staffCost = parseMoney(staffTotals?.staffCost)
  const contractorCost = parseMoney(contractorTotals?.contractorCost)
  const expenseCost = parseMoney(expenseTotals?.expenseCost)
  const totalCost = staffCost + contractorCost + expenseCost
  const profit = revenueTotal - totalCost

  return {
    projectId: project.id,
    projectName: project.name,
    customerId: project.customerId ?? null,
    customerName: project.customerName ?? null,
    revenueLines: revenueLines.map((line) => ({
      ...line,
      amount: formatMoney(parseMoney(line.amount)),
    })),
    revenueTotal: formatMoney(revenueTotal),
    cost: {
      staffCost: formatMoney(staffCost),
      contractorCost: formatMoney(contractorCost),
      expenseCost: formatMoney(expenseCost),
      totalCost: formatMoney(totalCost),
      staffHours: formatMoney(parseMoney(staffTotals?.staffHours)),
    },
    profit: formatMoney(profit),
    margin: calcMargin(revenueTotal, profit),
  }
}

export async function getTenantProfitability(
  db: Db,
  tenantId: string,
  from: string,
  to: string,
): Promise<TenantProfitabilityResult> {
  const [projectRows, revenueRows, staffRows, contractorRows, expenseRows] = await Promise.all([
    db
      .select({
        id: projects.id,
        name: projects.name,
        customerId: projects.customerId,
        customerName: customers.name,
      })
      .from(projects)
      .leftJoin(customers, eq(projects.customerId, customers.id))
      .where(eq(projects.tenantId, tenantId)),
    db
      .select({
        projectId: invoices.projectId,
        revenue: sql<string>`COALESCE(SUM(${invoices.total}), 0)::text`,
      })
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          isNotNull(invoices.projectId),
          inArray(invoices.status, REVENUE_STATUSES),
          buildDateRangeClause(
            sql`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate})`,
            from,
            to,
          ),
        ),
      )
      .groupBy(invoices.projectId),
    db
      .select({
        projectId: timeEntries.projectId,
        staffCost: sql<string>`COALESCE(SUM((${timeEntries.durationSeconds}::numeric / 3600) * ${users.hourlyCost}), 0)::text`,
      })
      .from(timeEntries)
      .innerJoin(users, eq(users.id, timeEntries.userId))
      .where(
        and(
          eq(timeEntries.tenantId, tenantId),
          isNotNull(users.hourlyCost),
          buildDateRangeClause(sql`DATE(${timeEntries.startedAt})`, from, to),
        ),
      )
      .groupBy(timeEntries.projectId),
    db
      .select({
        projectId: payoutBillLines.projectId,
        contractorCost: sql<string>`COALESCE(SUM(${payoutBillLines.lineTotal}), 0)::text`,
      })
      .from(payoutBillLines)
      .innerJoin(payoutBills, eq(payoutBills.id, payoutBillLines.billId))
      .where(
        and(
          eq(payoutBillLines.tenantId, tenantId),
          isNotNull(payoutBillLines.projectId),
          inArray(payoutBills.status, PAYOUT_STATUSES),
          buildDateRangeClause(payoutBills.periodEnd, from, to),
        ),
      )
      .groupBy(payoutBillLines.projectId),
    db
      .select({
        projectId: expenses.projectId,
        expenseCost: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text`,
      })
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          isNotNull(expenses.projectId),
          eq(expenses.status, 'COMPLETED'),
          buildDateRangeClause(expenses.expenseDate, from, to),
        ),
      )
      .groupBy(expenses.projectId),
  ])

  const projectMap = new Map(projectRows.map((row) => [row.id, row]))
  const revenueMap = new Map(revenueRows.map((row) => [row.projectId, parseMoney(row.revenue)]))
  const staffMap = new Map(staffRows.map((row) => [row.projectId, parseMoney(row.staffCost)]))
  const contractorMap = new Map(
    contractorRows.map((row) => [row.projectId, parseMoney(row.contractorCost)]),
  )
  const expenseMap = new Map(expenseRows.map((row) => [row.projectId, parseMoney(row.expenseCost)]))

  const touchedProjectIds = new Set<string>()
  for (const key of revenueMap.keys()) if (key) touchedProjectIds.add(key)
  for (const key of staffMap.keys()) if (key) touchedProjectIds.add(key)
  for (const key of contractorMap.keys()) if (key) touchedProjectIds.add(key)
  for (const key of expenseMap.keys()) if (key) touchedProjectIds.add(key)

  const byProject: ProjectProfitabilityListRow[] = [...touchedProjectIds].map((projectId) => {
    const project = projectMap.get(projectId)
    const revenue = revenueMap.get(projectId) ?? 0
    const cost =
      (staffMap.get(projectId) ?? 0) +
      (contractorMap.get(projectId) ?? 0) +
      (expenseMap.get(projectId) ?? 0)
    const profit = revenue - cost

    return {
      id: projectId,
      name: project?.name ?? 'Unknown Project',
      customerId: project?.customerId ?? null,
      customerName: project?.customerName ?? null,
      revenue: formatMoney(revenue),
      cost: formatMoney(cost),
      profit: formatMoney(profit),
      margin: calcMargin(revenue, profit),
    }
  })

  byProject.sort((a, b) => parseMoney(b.revenue) - parseMoney(a.revenue))

  const customerAccumulator = new Map<
    string,
    { id: string; name: string; revenue: number; cost: number }
  >()

  for (const row of byProject) {
    if (!row.customerId || !row.customerName) continue
    const current = customerAccumulator.get(row.customerId) ?? {
      id: row.customerId,
      name: row.customerName,
      revenue: 0,
      cost: 0,
    }
    current.revenue += parseMoney(row.revenue)
    current.cost += parseMoney(row.cost)
    customerAccumulator.set(row.customerId, current)
  }

  const byCustomer: ProfitabilityListRow[] = [...customerAccumulator.values()]
    .map((row) => {
      const profit = row.revenue - row.cost
      return {
        id: row.id,
        name: row.name,
        revenue: formatMoney(row.revenue),
        cost: formatMoney(row.cost),
        profit: formatMoney(profit),
        margin: calcMargin(row.revenue, profit),
      }
    })
    .sort((a, b) => parseMoney(b.revenue) - parseMoney(a.revenue))

  return {
    from,
    to,
    summary: makeSummary(byProject),
    byProject,
    byCustomer,
  }
}

export async function getCustomerProfitability(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<CustomerProfitabilityDetail | null> {
  const [customer] = await db
    .select({ id: customers.id, name: customers.name })
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))
    .limit(1)

  if (!customer) return null

  const customerProjects = await db
    .select({
      id: projects.id,
      name: projects.name,
    })
    .from(projects)
    .where(and(eq(projects.tenantId, tenantId), eq(projects.customerId, customerId)))

  const projectsWithProfitability = await Promise.all(
    customerProjects.map<Promise<ProjectProfitabilityListRow | null>>(async (project) => {
      const detail = await getProjectProfitability(db, tenantId, project.id)
      if (!detail) return null
      return {
        id: detail.projectId,
        name: detail.projectName,
        customerId,
        customerName: customer.name,
        revenue: detail.revenueTotal,
        cost: detail.cost.totalCost,
        profit: detail.profit,
        margin: detail.margin,
      } satisfies ProjectProfitabilityListRow
    }),
  )

  const rows = projectsWithProfitability
    .filter((row): row is ProjectProfitabilityListRow => row !== null)
    .sort((a, b) => parseMoney(b.revenue) - parseMoney(a.revenue))

  return {
    customerId: customer.id,
    customerName: customer.name,
    projects: rows,
    summary: makeSummary(rows),
  }
}

export const getClientProfitability = getCustomerProfitability