/**
 * Expense approval query helpers — expense-approval-workflow.
 *
 * All queries are tenant-scoped. Route files import from '@zync/db/queries'.
 */
import { and, eq, desc, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { expenses } from '../schema/expenses'
import { tenantSettings } from '../schema/tenants'
import { users } from '../schema/users'
import { roles, tenantMemberships } from '../schema/rbac'
import { auditLog } from './_audit-forward'

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

export type ApprovalStatus = 'not_required' | 'pending' | 'approved' | 'rejected'

export interface ApprovalQueueItem {
  id: string
  tenantId: string
  amount: string | null
  currency: string
  vendorName: string | null
  expenseDate: string | null
  approvalStatus: ApprovalStatus
  approvalNote: string | null
  approvedAt: Date | null
  approvedBy: string | null
  createdBy: string
  submitterName: string | null
  submitterEmail: string
  isNew: boolean
}

// ── resolveApprovalStatus ──────────────────────────────────────────────────────

export async function resolveApprovalStatus(args: {
  tenantId: string
  amount: number | null | undefined
  tier: string
  db: Db
}): Promise<'pending' | 'not_required'> {
  const { tenantId, amount, tier, db } = args

  // Only Business+ tenants have the approval feature
  const businessTiers = ['business', 'enterprise', 'white_label']
  if (!businessTiers.includes(tier)) return 'not_required'

  // Amount not yet known → defer; treat as not_required until finalised
  if (amount === null || amount === undefined) return 'not_required'

  const row = await db
    .select({ threshold: tenantSettings.expenseApprovalThresholdIls })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  if (!row[0]) return 'not_required'

  const threshold = row[0].threshold ?? 0
  // threshold=0 means ALL expenses require approval (amount > 0 === true for any positive amount)
  return amount > threshold ? 'pending' : 'not_required'
}

// ── listPendingApprovals ───────────────────────────────────────────────────────

export async function listPendingApprovals(args: {
  tenantId: string
  status?: ApprovalStatus
  limit?: number
  db: Db
}): Promise<{ items: ApprovalQueueItem[]; total: number }> {
  const { tenantId, status = 'pending', limit = 50, db } = args

  const now = new Date()
  const oneDayAgo = new Date(now.getTime() - 24 * 60 * 60 * 1000)

  const rows = await db
    .select({
      id: expenses.id,
      tenantId: expenses.tenantId,
      amount: expenses.amount,
      currency: expenses.currency,
      vendorName: expenses.vendorName,
      expenseDate: expenses.expenseDate,
      approvalStatus: expenses.approvalStatus,
      approvalNote: expenses.approvalNote,
      approvedAt: expenses.approvedAt,
      approvedBy: expenses.approvedBy,
      createdBy: expenses.createdBy,
      createdAt: expenses.createdAt,
      approvalNote2: expenses.approvalNote,
      submitterName: users.name,
      submitterEmail: users.email,
    })
    .from(expenses)
    .leftJoin(users, eq(expenses.createdBy, users.id))
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        eq(expenses.approvalStatus, status),
        sql`${expenses.deletedAt} IS NULL`,
      ),
    )
    .orderBy(desc(expenses.expenseDate))
    .limit(limit)

  const countRow = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        eq(expenses.approvalStatus, status),
        sql`${expenses.deletedAt} IS NULL`,
      ),
    )

  const total = countRow[0]?.count ?? 0

  const items: ApprovalQueueItem[] = rows.map((r) => ({
    id: r.id,
    tenantId: r.tenantId,
    amount: r.amount,
    currency: r.currency,
    vendorName: r.vendorName,
    expenseDate: r.expenseDate,
    approvalStatus: r.approvalStatus as ApprovalStatus,
    approvalNote: r.approvalNote,
    approvedAt: r.approvedAt,
    approvedBy: r.approvedBy,
    createdBy: r.createdBy,
    submitterName: r.submitterName ?? null,
    submitterEmail: r.submitterEmail ?? '',
    isNew: r.createdAt > oneDayAgo,
  }))

  return { items, total }
}

// ── approveExpense ─────────────────────────────────────────────────────────────

export async function approveExpense(args: {
  tenantId: string
  expenseId: string
  approverUserId: string
  note?: string
  db: Db
}): Promise<{ id: string; approvalStatus: string }> {
  const { tenantId, expenseId, approverUserId, note, db } = args

  const updated = await db.transaction(async (tx) => {
    const rows = await tx
      .update(expenses)
      .set({
        approvalStatus: 'approved',
        approvedBy: approverUserId,
        approvedAt: new Date(),
        approvalNote: note ?? null,
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          eq(expenses.id, expenseId),
          eq(expenses.approvalStatus, 'pending'),
        ),
      )
      .returning({ id: expenses.id, approvalStatus: expenses.approvalStatus })

    if (!rows[0]) {
      throw new Error('Expense not found or not in pending status')
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: approverUserId,
      actorType: 'user',
      entityType: 'expense',
      entityId: expenseId,
      action: 'approve',
      changes: null,
    })

    return rows[0]
  })

  return updated
}

// ── rejectExpense ──────────────────────────────────────────────────────────────

export async function rejectExpense(args: {
  tenantId: string
  expenseId: string
  approverUserId: string
  reason: string
  db: Db
}): Promise<{ id: string; approvalStatus: string }> {
  const { tenantId, expenseId, approverUserId, reason, db } = args

  const updated = await db.transaction(async (tx) => {
    const rows = await tx
      .update(expenses)
      .set({
        approvalStatus: 'rejected',
        approvedBy: approverUserId,
        approvedAt: new Date(),
        approvalNote: reason,
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          eq(expenses.id, expenseId),
          eq(expenses.approvalStatus, 'pending'),
        ),
      )
      .returning({ id: expenses.id, approvalStatus: expenses.approvalStatus })

    if (!rows[0]) {
      throw new Error('Expense not found or not in pending status')
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: approverUserId,
      actorType: 'user',
      entityType: 'expense',
      entityId: expenseId,
      action: 'reject',
      changes: null,
    })

    return rows[0]
  })

  return updated
}

// ── isExpenseApprover ──────────────────────────────────────────────────────────

export async function isExpenseApprover(args: {
  tenantId: string
  userId: string
  db: Db
}): Promise<boolean> {
  const { tenantId, userId, db } = args

  // Get the user's role and tenant settings
  const [membershipRow, settingsRow] = await Promise.all([
    db
      .select({ roleName: roles.name })
      .from(tenantMemberships)
      .leftJoin(roles, eq(tenantMemberships.roleId, roles.id))
      .where(
        and(
          eq(tenantMemberships.tenantId, tenantId),
          eq(tenantMemberships.userId, userId),
        ),
      )
      .limit(1),
    db
      .select({ approverRole: tenantSettings.expenseApproverRole })
      .from(tenantSettings)
      .where(eq(tenantSettings.tenantId, tenantId))
      .limit(1),
  ])

  const roleName = membershipRow[0]?.roleName ?? ''
  const approverRole = settingsRow[0]?.approverRole ?? 'any_admin'

  // OWNER always can approve
  if (roleName === 'OWNER') return true

  // any_admin: ADMIN role qualifies
  if (approverRole === 'any_admin' && roleName === 'ADMIN') return true

  // specific user_id
  if (approverRole === userId) return true

  return false
}