import { and, asc, eq, isNull, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { projectMilestones, type NewProjectMilestone, type ProjectMilestoneRow } from '../schema/project-milestones'
import { invoices } from '../schema/invoices'
import type {
  BillingConfig,
  CreateMilestoneInput,
  MilestoneObject,
  MilestoneSummary,
  UpdateMilestoneInput,
} from '@zync/types'
export type { CreateMilestoneInput, MilestoneObject, UpdateMilestoneInput } from '@zync/types'

type QueryDb = Db | DbTx

type MilestoneRowWithInvoice = ProjectMilestoneRow & { invoiceStatus: string | null }

function toAmountNumber(value: string | number | null | undefined): number {
  if (typeof value === 'number') return value
  if (typeof value === 'string' && value.length > 0) return Number(value)
  return 0
}

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

function normalizeDescription(value: string | undefined): string | null {
  if (value === undefined) return null
  const trimmed = value.trim()
  return trimmed.length > 0 ? trimmed : null
}

function milestoneStatusFrom(row: Pick<ProjectMilestoneRow, 'completedAt'>): MilestoneObject['status'] {
  return row.completedAt ? 'complete' : 'pending'
}

export class MilestoneInvoicedError extends Error {
  readonly code = 'milestone_invoiced' as const

  constructor(message = 'Void or delete the linked invoice before reopening this milestone.') {
    super(message)
    this.name = 'MilestoneInvoicedError'
  }
}

export function serializeMilestone(row: MilestoneRowWithInvoice): MilestoneObject {
  return {
    id: row.id as MilestoneObject['id'],
    projectId: row.projectId,
    name: row.name,
    description: row.description ?? null,
    amount: String(row.amount),
    dueDate: row.dueDate ?? null,
    completedAt: row.completedAt ? row.completedAt.toISOString() : null,
    status: milestoneStatusFrom(row),
    invoiceId: row.invoiceId ?? null,
    invoiceStatus: (row.invoiceStatus as MilestoneObject['invoiceStatus']) ?? null,
    position: row.position,
    createdAt: row.createdAt.toISOString(),
    createdBy: row.createdBy,
  }
}

async function getMilestoneRow(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
): Promise<MilestoneRowWithInvoice | null> {
  const [row] = await db
    .select({
      id: projectMilestones.id,
      tenantId: projectMilestones.tenantId,
      projectId: projectMilestones.projectId,
      name: projectMilestones.name,
      description: projectMilestones.description,
      amount: projectMilestones.amount,
      status: projectMilestones.status,
      dueDate: projectMilestones.dueDate,
      completedAt: projectMilestones.completedAt,
      invoiceId: projectMilestones.invoiceId,
      position: projectMilestones.position,
      createdAt: projectMilestones.createdAt,
      createdBy: projectMilestones.createdBy,
      updatedAt: projectMilestones.updatedAt,
      invoiceStatus: invoices.status,
    })
    .from(projectMilestones)
    .leftJoin(invoices, eq(projectMilestones.invoiceId, invoices.id))
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.id, milestoneId)))
    .limit(1)

  return row ?? null
}

export async function listMilestones(
  db: QueryDb,
  tenantId: string,
  projectId: string,
): Promise<MilestoneObject[]> {
  const rows = await db
    .select({
      id: projectMilestones.id,
      tenantId: projectMilestones.tenantId,
      projectId: projectMilestones.projectId,
      name: projectMilestones.name,
      description: projectMilestones.description,
      amount: projectMilestones.amount,
      status: projectMilestones.status,
      dueDate: projectMilestones.dueDate,
      completedAt: projectMilestones.completedAt,
      invoiceId: projectMilestones.invoiceId,
      position: projectMilestones.position,
      createdAt: projectMilestones.createdAt,
      createdBy: projectMilestones.createdBy,
      updatedAt: projectMilestones.updatedAt,
      invoiceStatus: invoices.status,
    })
    .from(projectMilestones)
    .leftJoin(invoices, eq(projectMilestones.invoiceId, invoices.id))
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.projectId, projectId)))
    .orderBy(asc(projectMilestones.position), asc(projectMilestones.createdAt))

  return rows.map(serializeMilestone)
}

export async function getMilestone(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
): Promise<MilestoneObject | null> {
  const row = await getMilestoneRow(db, tenantId, milestoneId)
  return row ? serializeMilestone(row) : null
}

async function nextPosition(db: QueryDb, tenantId: string, projectId: string): Promise<number> {
  const [row] = await db
    .select({ position: sql<number>`COALESCE(MAX(${projectMilestones.position}), -1)` })
    .from(projectMilestones)
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.projectId, projectId)))

  return Number(row?.position ?? -1) + 1
}

export async function createMilestone(
  db: QueryDb,
  tenantId: string,
  projectId: string,
  input: CreateMilestoneInput,
  createdBy: string,
): Promise<MilestoneObject> {
  const position = input.position ?? (await nextPosition(db, tenantId, projectId))
  const values: NewProjectMilestone = {
    tenantId,
    projectId,
    name: input.name.trim(),
    description: normalizeDescription(input.description),
    amount: formatAmount(input.amount),
    dueDate: input.due_date ?? null,
    completedAt: null,
    invoiceId: null,
    position,
    createdBy,
  }

  const [row] = await db.insert(projectMilestones).values(values).returning()
  if (!row) throw new Error('Failed to create milestone')
  return serializeMilestone({ ...row, invoiceStatus: null })
}

export async function updateMilestone(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
  patch: UpdateMilestoneInput,
): Promise<MilestoneObject | null> {
  const setValues: Partial<NewProjectMilestone> = {}
  if (patch.name !== undefined) setValues.name = patch.name.trim()
  if (patch.description !== undefined) setValues.description = normalizeDescription(patch.description)
  if (patch.amount !== undefined) setValues.amount = formatAmount(patch.amount)
  if (patch.due_date !== undefined) setValues.dueDate = patch.due_date ?? null
  if (patch.position !== undefined) setValues.position = patch.position
  if (Object.keys(setValues).length === 0) {
    return getMilestone(db, tenantId, milestoneId)
  }

  const [updated] = await db
    .update(projectMilestones)
    .set(setValues)
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.id, milestoneId)))
    .returning()

  if (!updated) return null
  return serializeMilestone({ ...updated, invoiceStatus: null })
}

export async function completeMilestone(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
): Promise<MilestoneObject | null> {
  const row = await getMilestoneRow(db, tenantId, milestoneId)
  if (!row) return null
  if (!row.completedAt) {
    await db
      .update(projectMilestones)
      .set({ completedAt: new Date(), status: 'completed' })
      .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.id, milestoneId)))
  }
  return getMilestone(db, tenantId, milestoneId)
}

export async function reopenMilestone(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
): Promise<MilestoneObject | null> {
  const row = await getMilestoneRow(db, tenantId, milestoneId)
  if (!row) return null
  if (row.invoiceId && row.invoiceStatus && row.invoiceStatus !== 'VOID') {
    throw new MilestoneInvoicedError()
  }

  await db
    .update(projectMilestones)
    .set({ completedAt: null, status: 'pending' })
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.id, milestoneId)))

  return getMilestone(db, tenantId, milestoneId)
}

export async function setMilestoneInvoice(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
  invoiceId: string,
): Promise<MilestoneObject | null> {
  const [updated] = await db
    .update(projectMilestones)
    .set({ invoiceId })
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.id, milestoneId)))
    .returning()

  if (!updated) return null
  return getMilestone(db, tenantId, milestoneId)
}

export async function deleteMilestone(
  db: QueryDb,
  tenantId: string,
  milestoneId: string,
): Promise<void> {
  const row = await getMilestoneRow(db, tenantId, milestoneId)
  if (!row) return
  if (row.invoiceId) {
    throw new MilestoneInvoicedError('Delete blocked for invoiced milestone.')
  }

  await db
    .delete(projectMilestones)
    .where(and(eq(projectMilestones.tenantId, tenantId), eq(projectMilestones.id, milestoneId)))
}

export async function countIncompleteMilestones(
  db: QueryDb,
  tenantId: string,
  projectId: string,
): Promise<number> {
  const rows = await db
    .select({ id: projectMilestones.id })
    .from(projectMilestones)
    .where(
      and(
        eq(projectMilestones.tenantId, tenantId),
        eq(projectMilestones.projectId, projectId),
        isNull(projectMilestones.completedAt),
      ),
    )

  return rows.length
}

export async function getMilestoneSummary(
  db: QueryDb,
  tenantId: string,
  projectId: string,
  billingConfig: BillingConfig | null,
): Promise<MilestoneSummary> {
  const milestones = await listMilestones(db, tenantId, projectId)
  const totalDefined = milestones.reduce((sum, item) => sum + toAmountNumber(item.amount), 0)
  const invoiced = milestones.reduce(
    (sum, item) => (item.invoiceId && item.invoiceStatus !== 'VOID' ? sum + toAmountNumber(item.amount) : sum),
    0,
  )
  const completeCount = milestones.filter((item) => item.completedAt !== null).length
  const expectedTotal =
    billingConfig && 'total_amount' in billingConfig ? Number(billingConfig.total_amount ?? 0) : null

  return {
    totalDefined: formatAmount(totalDefined),
    invoiced: formatAmount(invoiced),
    remaining: formatAmount(totalDefined - invoiced),
    completeCount,
    totalCount: milestones.length,
    matchesBillingConfig: expectedTotal === null ? true : totalDefined === expectedTotal,
  }
}

export async function createDepositMilestone(
  db: QueryDb,
  input: {
    tenantId: string
    projectId: string
    billingConfig: BillingConfig | null
    createdBy: string
  },
): Promise<MilestoneObject | null> {
  const config = input.billingConfig
  if (!config || !('total_amount' in config) || !('deposit_pct' in config)) return null
  if (!config.deposit_pct || config.deposit_pct <= 0) return null

  const existing = await db
    .select({ id: projectMilestones.id })
    .from(projectMilestones)
    .where(
      and(
        eq(projectMilestones.tenantId, input.tenantId),
        eq(projectMilestones.projectId, input.projectId),
        eq(projectMilestones.name, 'Project deposit'),
      ),
    )
    .limit(1)

  if (existing.length > 0) return null

  const amount = Number(((config.total_amount * config.deposit_pct) / 100).toFixed(2))
  if (amount <= 0) return null

  return createMilestone(db, input.tenantId, input.projectId, {
    name: 'Project deposit',
    amount,
    position: 0,
  }, input.createdBy)
}

export const listProjectMilestones = listMilestones
export const getMilestoneById = getMilestone