import { and, asc, desc, eq, gte, inArray, lte, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { PaginatedResponse } from '@zync/types'
import type { Db } from '../client'
import { contractors } from '../schema/contractors'
import { projects } from '../schema/projects'
import { tasks } from '../schema/tasks'
import { tenantSettings } from '../schema/tenants'
import { timeEntries } from '../schema/time'
import { users } from '../schema/users'

export const timeApprovalStatusSchema = z.enum(['pending', 'approved', 'rejected', 'locked'])

export const listTimeApprovalEntriesSchema = z.object({
  contractor_id: z.string().uuid().optional(),
  project_id: z.string().uuid().optional(),
  from: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  to: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  status: z.enum(['pending', 'approved', 'rejected', 'locked', 'all']).default('pending'),
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(100).default(50),
})

export const approveTimeEntrySchema = z.object({
  note: z.string().trim().max(500).optional(),
})

export const rejectTimeEntrySchema = z.object({
  note: z.string().trim().min(1).max(500),
})

export const bulkReviewTimeEntriesSchema = z
  .object({
    action: z.enum(['approve', 'reject']),
    entry_ids: z.array(z.string().uuid()).min(1).max(200),
    note: z.string().trim().max(500).optional(),
  })
  .superRefine((value, ctx) => {
    if (value.action === 'reject' && !value.note) {
      ctx.addIssue({
        code: z.ZodIssueCode.custom,
        message: 'note is required when rejecting entries',
        path: ['note'],
      })
    }
  })

export const exportApprovedTimeEntriesSchema = z.object({
  from: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  to: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  userId: z.string().uuid().optional(),
  projectId: z.string().uuid().optional(),
})

export interface TimeApprovalListItem {
  id: string
  contractorId: string | null
  contractorName: string | null
  projectId: string
  projectName: string | null
  taskTitle: string | null
  entryDate: string
  startedAt: string
  durationSeconds: number | null
  description: string | null
  approvalStatus: 'pending' | 'approved' | 'rejected' | 'locked'
  approvalNote: string | null
  submittedAt: string | null
  approvedAt: string | null
  approvedBy: string | null
  lockedAt: string | null
}

export interface PayrollExportRow {
  id: string
  entryDate: string
  personName: string
  personEmail: string
  projectName: string
  taskTitle: string | null
  hours: number
  rate: number
  amount: number
  source: string
}

function parseCursor(cursor?: string): { startedAt: string; id: string } | null {
  if (!cursor) return null
  const [startedAt, id] = cursor.split('|')
  if (!startedAt || !id) return null
  return { startedAt, id }
}

function makeCursor(startedAt: Date, id: string): string {
  return `${startedAt.toISOString()}|${id}`
}

function csvCell(value: string | number | null): string {
  if (value === null) return ''
  const text = String(value)
  if (/[",\n]/.test(text)) return `"${text.replaceAll('"', '""')}"`
  return text
}

function serializeRow(row: {
  id: string
  contractorId: string | null
  contractorName: string | null
  projectId: string
  projectName: string | null
  taskTitle: string | null
  startedAt: Date
  durationSeconds: number | null
  description: string | null
  approvalStatus: string
  rejectionReason: string | null
  submittedAt: Date | null
  approvedAt: Date | null
  approvedBy: string | null
  lockedAt: Date | null
}): TimeApprovalListItem {
  return {
    id: row.id,
    contractorId: row.contractorId,
    contractorName: row.contractorName,
    projectId: row.projectId,
    projectName: row.projectName,
    taskTitle: row.taskTitle,
    entryDate: row.startedAt.toISOString().slice(0, 10),
    startedAt: row.startedAt.toISOString(),
    durationSeconds: row.durationSeconds,
    description: row.description,
    approvalStatus: row.approvalStatus as TimeApprovalListItem['approvalStatus'],
    approvalNote: row.rejectionReason,
    submittedAt: row.submittedAt?.toISOString() ?? null,
    approvedAt: row.approvedAt?.toISOString() ?? null,
    approvedBy: row.approvedBy,
    lockedAt: row.lockedAt?.toISOString() ?? null,
  }
}

export async function listTimeApprovalEntries(
  db: Db,
  tenantId: string,
  filter: z.infer<typeof listTimeApprovalEntriesSchema>,
): Promise<PaginatedResponse<TimeApprovalListItem>> {
  const limit = Math.min(filter.limit ?? 50, 100)
  const cursor = parseCursor(filter.cursor)
  const conditions = [
    eq(timeEntries.tenantId, tenantId),
    eq(timeEntries.source, 'contractor_portal'),
  ]

  if (filter.contractor_id) conditions.push(eq(timeEntries.contractorId, filter.contractor_id))
  if (filter.project_id) conditions.push(eq(timeEntries.projectId, filter.project_id))
  if (filter.from) conditions.push(gte(timeEntries.startedAt, new Date(`${filter.from}T00:00:00.000Z`)))
  if (filter.to) conditions.push(lte(timeEntries.startedAt, new Date(`${filter.to}T23:59:59.999Z`)))
  if (filter.status && filter.status !== 'all') conditions.push(eq(timeEntries.approvalStatus, filter.status))
  if (cursor) {
    conditions.push(
      sql`(${timeEntries.startedAt}, ${timeEntries.id}) < (${new Date(cursor.startedAt)}, ${cursor.id})`,
    )
  }

  const rows = await db
    .select({
      id: timeEntries.id,
      contractorId: timeEntries.contractorId,
      contractorName: contractors.name,
      projectId: timeEntries.projectId,
      projectName: projects.name,
      taskTitle: tasks.title,
      startedAt: timeEntries.startedAt,
      durationSeconds: timeEntries.durationSeconds,
      description: timeEntries.description,
      approvalStatus: timeEntries.approvalStatus,
      rejectionReason: timeEntries.rejectionReason,
      submittedAt: timeEntries.submittedAt,
      approvedAt: timeEntries.approvedAt,
      approvedBy: timeEntries.approvedBy,
      lockedAt: timeEntries.lockedAt,
    })
    .from(timeEntries)
    .leftJoin(contractors, eq(timeEntries.contractorId, contractors.id))
    .leftJoin(projects, eq(timeEntries.projectId, projects.id))
    .leftJoin(tasks, eq(timeEntries.taskId, tasks.id))
    .where(and(...conditions))
    .orderBy(desc(timeEntries.startedAt), desc(timeEntries.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const pageRows = hasMore ? rows.slice(0, limit) : rows
  const lastRow = pageRows.at(-1)

  return {
    items: pageRows.map(serializeRow),
    nextCursor: hasMore && lastRow ? makeCursor(lastRow.startedAt, lastRow.id) : null,
    total: pageRows.length,
  }
}

async function getEntryForReview(
  db: Db,
  tenantId: string,
  entryId: string,
  expected: Array<'pending' | 'rejected'>,
) {
  const [entry] = await db
    .select({
      id: timeEntries.id,
      approvalStatus: timeEntries.approvalStatus,
      contractorId: timeEntries.contractorId,
      userId: timeEntries.userId,
      lockedAt: timeEntries.lockedAt,
    })
    .from(timeEntries)
    .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.id, entryId)))
    .limit(1)

  if (!entry) throw Object.assign(new Error('Time entry not found'), { status: 404 })
  if (entry.lockedAt) throw Object.assign(new Error('Time entry is locked'), { status: 409 })
  if (!expected.includes(entry.approvalStatus as 'pending' | 'rejected')) {
    throw Object.assign(new Error(`Time entry is not ${expected.join(' or ')}`), { status: 409 })
  }
  return entry
}

export async function approveTimeEntry(
  db: Db,
  tenantId: string,
  managerId: string,
  entryId: string,
  _note?: string,
): Promise<{ id: string; approvalStatus: 'approved' }> {
  await getEntryForReview(db, tenantId, entryId, ['pending'])
  const now = new Date()
  await db
    .update(timeEntries)
    .set({
      approvalStatus: 'approved',
      approvedAt: now,
      approvedBy: managerId,
      rejectionReason: null,
      updatedAt: now,
    })
    .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.id, entryId)))

  return { id: entryId, approvalStatus: 'approved' }
}

export async function rejectTimeEntry(
  db: Db,
  tenantId: string,
  managerId: string,
  entryId: string,
  note: string,
): Promise<{ id: string; approvalStatus: 'rejected' }> {
  await getEntryForReview(db, tenantId, entryId, ['pending'])
  const now = new Date()
  await db
    .update(timeEntries)
    .set({
      approvalStatus: 'rejected',
      approvedAt: null,
      approvedBy: managerId,
      rejectedAt: now,
      rejectionReason: note,
      updatedAt: now,
    })
    .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.id, entryId)))

  return { id: entryId, approvalStatus: 'rejected' }
}

export async function bulkReviewTimeEntries(
  db: Db,
  tenantId: string,
  managerId: string,
  input: z.infer<typeof bulkReviewTimeEntriesSchema>,
): Promise<{ updated: number; skippedIds: string[] }> {
  let updated = 0
  const skippedIds: string[] = []

  for (const entryId of input.entry_ids) {
    try {
      if (input.action === 'approve') {
        await approveTimeEntry(db, tenantId, managerId, entryId, input.note)
      } else {
        await rejectTimeEntry(db, tenantId, managerId, entryId, input.note ?? '')
      }
      updated++
    } catch {
      skippedIds.push(entryId)
    }
  }

  return { updated, skippedIds }
}

export async function resubmitTimeEntry(
  db: Db,
  tenantId: string,
  actorId: string,
  entryId: string,
): Promise<{ id: string; approvalStatus: 'pending' }> {
  const entry = await getEntryForReview(db, tenantId, entryId, ['rejected'])
  if (entry.userId && entry.userId !== actorId) {
    throw Object.assign(new Error('Forbidden'), { status: 403 })
  }

  const now = new Date()
  await db
    .update(timeEntries)
    .set({
      approvalStatus: 'pending',
      submittedAt: now,
      approvedAt: null,
      approvedBy: null,
      rejectedAt: null,
      rejectionReason: null,
      updatedAt: now,
    })
    .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.id, entryId)))

  return { id: entryId, approvalStatus: 'pending' }
}

export function buildPayrollExportCsv(rows: PayrollExportRow[]): string {
  const header = 'date,person_name,person_email,project,task,hours,rate,amount,source,entry_id'
  const body = rows.map((row) =>
    [
      row.entryDate,
      row.personName,
      row.personEmail,
      row.projectName,
      row.taskTitle ?? '',
      row.hours,
      row.rate,
      row.amount,
      row.source,
      row.id,
    ].map(csvCell).join(','),
  )
  return [header, ...body].join('\n')
}

export async function exportApprovedTimeEntries(
  db: Db,
  tenantId: string,
  _managerId: string,
  filter: z.infer<typeof exportApprovedTimeEntriesSchema>,
): Promise<{ csv: string; filename: string; exportedIds: string[] }> {
  const [settings] = await db
    .select({ contractorRequireTimeApproval: tenantSettings.contractorRequireTimeApproval })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  if (!settings?.contractorRequireTimeApproval) {
    throw Object.assign(new Error('Time approval is disabled for this tenant'), { status: 409 })
  }

  const conditions = [
    eq(timeEntries.tenantId, tenantId),
    eq(timeEntries.approvalStatus, 'approved'),
    gte(timeEntries.startedAt, new Date(`${filter.from}T00:00:00.000Z`)),
    lte(timeEntries.startedAt, new Date(`${filter.to}T23:59:59.999Z`)),
  ]

  if (filter.userId) conditions.push(eq(timeEntries.userId, filter.userId))
  if (filter.projectId) conditions.push(eq(timeEntries.projectId, filter.projectId))

  const rows = await db
    .select({
      id: timeEntries.id,
      startedAt: timeEntries.startedAt,
      projectName: projects.name,
      taskTitle: tasks.title,
      source: timeEntries.source,
      durationSeconds: timeEntries.durationSeconds,
      personName: sql<string>`COALESCE(${contractors.name}, ${users.name}, '')`,
      personEmail: sql<string>`COALESCE(${users.email}, '')`,
    })
    .from(timeEntries)
    .leftJoin(contractors, eq(timeEntries.contractorId, contractors.id))
    .leftJoin(users, eq(timeEntries.userId, users.id))
    .leftJoin(projects, eq(timeEntries.projectId, projects.id))
    .leftJoin(tasks, eq(timeEntries.taskId, tasks.id))
    .where(and(...conditions))
    .orderBy(asc(timeEntries.startedAt), asc(timeEntries.id))

  const exportRows: PayrollExportRow[] = rows.map((row) => {
    const hours = Math.round((((row.durationSeconds ?? 0) / 3600) + Number.EPSILON) * 100) / 100
    return {
      id: row.id,
      entryDate: row.startedAt.toISOString().slice(0, 10),
      personName: row.personName,
      personEmail: row.personEmail,
      projectName: row.projectName ?? '',
      taskTitle: row.taskTitle,
      hours,
      rate: 0,
      amount: 0,
      source: row.source,
    }
  })

  if (rows.length > 0) {
    const now = new Date()
    const ids = rows.map((row) => row.id)
    await db
      .update(timeEntries)
      .set({
        approvalStatus: 'locked',
        lockedAt: now,
        lockedReason: 'approved',
        updatedAt: now,
      })
      .where(and(eq(timeEntries.tenantId, tenantId), inArray(timeEntries.id, ids)))
  }

  return {
    csv: buildPayrollExportCsv(exportRows),
    filename: `payroll-${filter.from}_${filter.to}.csv`,
    exportedIds: rows.map((row) => row.id),
  }
}