/**
 * Project archive/complete query helpers — project-archive-complete (P059).
 *
 * Lifecycle transitions for projects:
 *   complete:  active | on_hold → completed  (sets completed_at)
 *   archive:   completed → archived  (sets archived_at)
 *   reopen:    archived → completed; completed → active
 *
 * Business rules enforced here:
 *   - Completed projects remain writable for finalization.
 *   - Archived projects require a prior completion transition.
 *   - All transitions write an audit row in the same transaction.
 */
import { and, eq, inArray, isNull, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { projects } from '../schema/projects'
import type { ProjectRow } from '../schema/projects'
import { tasks, taskStatuses } from '../schema/tasks'
import { timeEntries } from '../schema/time'
import { invoices } from '../schema/invoices'
import type { CompletionSummary } from '@zync/types'
import { auditLog } from './_audit-forward'

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

export type ProjectLifecycleStatus = 'active' | 'on_hold' | 'completed' | 'archived'

export interface LifecycleError {
  code: 'NOT_FOUND' | 'INVALID_TRANSITION'
  message: string
}

export interface LifecycleResult {
  ok: boolean
  project?: ProjectRow
  error?: LifecycleError
}

// ── Helpers ───────────────────────────────────────────────────────────────────

const COMPLETE_ALLOWED_FROM: ProjectLifecycleStatus[] = ['active', 'on_hold']
const ARCHIVE_ALLOWED_FROM: ProjectLifecycleStatus[] = ['completed']
const REOPEN_ALLOWED_FROM: ProjectLifecycleStatus[] = ['completed', 'archived']

export async function getCompletionSummary(
  db: Db,
  tenantId: string,
  projectId: string,
): Promise<CompletionSummary> {
  const [project] = await db
    .select({
      billingType: projects.billingType,
      billingConfig: projects.billingConfig,
    })
    .from(projects)
    .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
    .limit(1)

  if (!project) throw new Error('Project not found')

  const [openTaskRow] = await db
    .select({ openTasks: sql<number>`COUNT(*)` })
    .from(tasks)
    .innerJoin(taskStatuses, eq(tasks.statusId, taskStatuses.id))
    .where(
      and(
        eq(tasks.tenantId, tenantId),
        eq(tasks.projectId, projectId),
        eq(taskStatuses.isTerminal, false),
      ),
    )

  const [timeRow] = await db
    .select({
      unbilledHours:
        sql<string>`COALESCE(ROUND(SUM(${timeEntries.durationSeconds})::numeric / 3600.0, 2), 0)`,
    })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.projectId, projectId),
        eq(timeEntries.billable, true),
        isNull(timeEntries.invoiceId),
        sql`${timeEntries.stoppedAt} IS NOT NULL`,
      ),
    )

  const [invoiceRow] = await db
    .select({
      outstandingInvoiceAmount: sql<string>`COALESCE(SUM(${invoices.total}), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.projectId, projectId),
        inArray(invoices.status, ['SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID']),
      ),
    )

  const unbilledHours = Number.parseFloat(String(timeRow?.unbilledHours ?? '0'))
  const ratePerHour =
    project.billingType === 'hourly' &&
    project.billingConfig &&
    typeof project.billingConfig === 'object' &&
    'rate_per_hour' in project.billingConfig
      ? Number((project.billingConfig as { rate_per_hour?: unknown }).rate_per_hour ?? 0)
      : 0

  return {
    open_tasks: Number(openTaskRow?.openTasks ?? 0),
    unbilled_hours: unbilledHours,
    unbilled_amount: project.billingType === 'hourly' ? Number((unbilledHours * ratePerHour).toFixed(2)) : 0,
    outstanding_invoice_amount: Number.parseFloat(String(invoiceRow?.outstandingInvoiceAmount ?? '0')),
  }
}

// ── Complete project ──────────────────────────────────────────────────────────

export async function completeProject(
  db: Db,
  tenantId: string,
  projectId: string,
  actorId: string,
  opts: { closeOpenTasks?: boolean } = {},
): Promise<LifecycleResult> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(projects)
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .limit(1)

    if (!existing) {
      return { ok: false, error: { code: 'NOT_FOUND', message: 'Project not found' } }
    }

    if (!COMPLETE_ALLOWED_FROM.includes(existing.status as ProjectLifecycleStatus)) {
      return {
        ok: false,
        error: {
          code: 'INVALID_TRANSITION',
          message: `Cannot complete a project in '${existing.status}' status`,
        },
      }
    }

    const now = new Date()
    const [updated] = await tx
      .update(projects)
      .set({ status: 'completed', completedAt: now, updatedAt: now })
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .returning()

    if (!updated) {
      return { ok: false, error: { code: 'NOT_FOUND', message: 'Project not found after update' } }
    }

    if (opts.closeOpenTasks) {
      const [terminalStatus] = await tx
        .select({ id: taskStatuses.id })
        .from(taskStatuses)
        .where(
          and(
            eq(taskStatuses.tenantId, tenantId),
            eq(taskStatuses.isTerminal, true),
            sql`(${taskStatuses.projectId} = ${projectId} OR ${taskStatuses.projectId} IS NULL)`,
          ),
        )
        .orderBy(taskStatuses.position, taskStatuses.id)
        .limit(1)

      if (terminalStatus) {
        await tx
          .update(tasks)
          .set({ statusId: terminalStatus.id, updatedAt: now })
          .where(
            and(
              eq(tasks.tenantId, tenantId),
              eq(tasks.projectId, projectId),
              sql`EXISTS (
                SELECT 1
                FROM task_statuses ts
                WHERE ts.id = ${tasks.statusId}
                  AND ts.tenant_id = ${tenantId}
                  AND ts.is_terminal = false
              )`,
            ),
          )
      }
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'project',
      entityId: projectId,
      action: 'project.completed',
      changes: { status: [existing.status, 'completed'] },
    })

    return { ok: true, project: updated }
  })
}

// ── Archive project ───────────────────────────────────────────────────────────

export async function archiveProject(
  db: Db,
  tenantId: string,
  projectId: string,
  actorId: string,
): Promise<LifecycleResult> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(projects)
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .limit(1)

    if (!existing) {
      return { ok: false, error: { code: 'NOT_FOUND', message: 'Project not found' } }
    }

    if (!ARCHIVE_ALLOWED_FROM.includes(existing.status as ProjectLifecycleStatus)) {
      return {
        ok: false,
        error: {
          code: 'INVALID_TRANSITION',
          message: `Cannot archive a project in '${existing.status}' status`,
        },
      }
    }

    const now = new Date()
    const [updated] = await tx
      .update(projects)
      .set({ status: 'archived', archivedAt: now, updatedAt: now })
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .returning()

    if (!updated) {
      return { ok: false, error: { code: 'NOT_FOUND', message: 'Project not found after update' } }
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'project',
      entityId: projectId,
      action: 'project.archived',
      changes: { status: [existing.status, 'archived'] },
    })

    return { ok: true, project: updated }
  })
}

// ── Reopen project ────────────────────────────────────────────────────────────

export async function reopenProject(
  db: Db,
  tenantId: string,
  projectId: string,
  actorId: string,
): Promise<LifecycleResult> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(projects)
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .limit(1)

    if (!existing) {
      return { ok: false, error: { code: 'NOT_FOUND', message: 'Project not found' } }
    }

    if (!REOPEN_ALLOWED_FROM.includes(existing.status as ProjectLifecycleStatus)) {
      return {
        ok: false,
        error: {
          code: 'INVALID_TRANSITION',
          message: `Cannot reopen a project in '${existing.status}' status`,
        },
      }
    }

    const now = new Date()
    const [updated] = await tx
      .update(projects)
      .set({
        status: existing.status === 'archived' ? 'completed' : 'active',
        archivedAt: null,
        updatedAt: now,
      })
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .returning()

    if (!updated) {
      return { ok: false, error: { code: 'NOT_FOUND', message: 'Project not found after update' } }
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'project',
      entityId: projectId,
      action: existing.status === 'archived' ? 'project.unarchived' : 'project.reopened',
      changes: {
        status: [existing.status, existing.status === 'archived' ? 'completed' : 'active'],
      },
    })

    return { ok: true, project: updated }
  })
}