/**
 * Contractor portal time + profile queries — contractor-portal (wave 8, Task 5).
 *
 * All helpers are tenant-scoped AND contractor-scoped.
 */
import { and, asc, desc, eq, inArray, sql } from 'drizzle-orm'
import type {
  ContractorPortalProfile,
  ContractorPayoutBillRow,
  ContractorTimeEntryInput,
  ContractorTimeEntryRow,
} from '@zync/types'
import type { Db } from '../client'
import { contractors, contractorAssignments, payoutBills } from '../schema/contractors'
import { timeEntries } from '../schema/time'
import { projects } from '../schema/projects'
import { tasks } from '../schema/tasks'
import { tenants, tenantSettings } from '../schema/tenants'

export class ContractorPortalEntryNotFoundError extends Error {
  constructor(id: string) {
    super(`Time entry not found: ${id}`)
    this.name = 'ContractorPortalEntryNotFoundError'
  }
}

export class ContractorPortalEntryConflictError extends Error {
  constructor(message: string) {
    super(message)
    this.name = 'ContractorPortalEntryConflictError'
  }
}

export class ContractorPortalAssignmentError extends Error {
  constructor(message: string) {
    super(message)
    this.name = 'ContractorPortalAssignmentError'
  }
}

export class ContractorPortalInactiveError extends Error {
  constructor() {
    super('Contractor is inactive')
    this.name = 'ContractorPortalInactiveError'
  }
}

export async function resolveContractorApprovalRequirement(
  db: Db,
  tenantId: string,
): Promise<boolean> {
  const [row] = await db
    .select({
      contractorRequireTimeApproval: tenantSettings.contractorRequireTimeApproval,
    })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  if (!row) return true
  return row.contractorRequireTimeApproval
}

export async function getContractorProfile(
  db: Db,
  args: { tenantId: string; contractorId: string },
): Promise<ContractorPortalProfile> {
  const [contractorRow] = await db
    .select({
      contractorId: contractors.id,
      name: contractors.name,
      active: contractors.active,
      tenantName: tenants.name,
      tenantLogoUrl: tenants.logoUrl,
    })
    .from(contractors)
    .innerJoin(tenants, eq(contractors.tenantId, tenants.id))
    .where(
      and(eq(contractors.tenantId, args.tenantId), eq(contractors.id, args.contractorId)),
    )
    .limit(1)

  if (!contractorRow) {
    throw new ContractorPortalEntryNotFoundError(args.contractorId)
  }

  if (!contractorRow.active) {
    throw new ContractorPortalInactiveError()
  }

  const assignmentRows = await db
    .select({
      projectId: projects.id,
      projectName: projects.name,
    })
    .from(contractorAssignments)
    .innerJoin(projects, eq(contractorAssignments.projectId, projects.id))
    .where(
      and(
        eq(contractorAssignments.tenantId, args.tenantId),
        eq(contractorAssignments.contractorId, args.contractorId),
      ),
    )
    .orderBy(asc(projects.name))

  const projectIds = assignmentRows.map((r) => r.projectId)
  const taskRows =
    projectIds.length === 0
      ? []
      : await db
          .select({
            id: tasks.id,
            title: tasks.title,
            projectId: tasks.projectId,
          })
          .from(tasks)
          .where(
            and(eq(tasks.tenantId, args.tenantId), inArray(tasks.projectId, projectIds)),
          )
          .orderBy(asc(tasks.title))

  const tasksByProject = new Map<string, Array<{ id: string; title: string }>>()
  for (const task of taskRows) {
    if (!task.projectId) continue
    const list = tasksByProject.get(task.projectId) ?? []
    list.push({ id: task.id, title: task.title })
    tasksByProject.set(task.projectId, list)
  }

  return {
    contractorId: contractorRow.contractorId,
    name: contractorRow.name,
    tenantName: contractorRow.tenantName,
    tenantLogoUrl: contractorRow.tenantLogoUrl,
    projects: assignmentRows.map((p) => ({
      id: p.projectId,
      name: p.projectName,
      tasks: tasksByProject.get(p.projectId) ?? [],
    })),
  }
}

export async function assertContractorProjectAssignment(
  db: Db,
  args: { tenantId: string; contractorId: string; projectId: string },
): Promise<void> {
  const [row] = await db
    .select({ id: contractorAssignments.id })
    .from(contractorAssignments)
    .where(
      and(
        eq(contractorAssignments.tenantId, args.tenantId),
        eq(contractorAssignments.contractorId, args.contractorId),
        eq(contractorAssignments.projectId, args.projectId),
      ),
    )
    .limit(1)

  if (!row) {
    throw new ContractorPortalAssignmentError('Project is not assigned to this contractor')
  }
}

export async function assertTaskBelongsToProject(
  db: Db,
  args: { tenantId: string; projectId: string; taskId: string },
): Promise<void> {
  const [row] = await db
    .select({ id: tasks.id })
    .from(tasks)
    .where(
      and(
        eq(tasks.tenantId, args.tenantId),
        eq(tasks.projectId, args.projectId),
        eq(tasks.id, args.taskId),
      ),
    )
    .limit(1)

  if (!row) {
    throw new ContractorPortalAssignmentError('Task does not belong to the selected project')
  }
}

export async function computeContractorEntryTimes(
  db: Db,
  tenantId: string,
  date: string,
  durationMin: number,
): Promise<{ startedAt: Date; stoppedAt: Date }> {
  const result = await db.execute(sql`
    SELECT
      ((${date}::text || ' 00:00:00')::timestamp AT TIME ZONE COALESCE(
        (SELECT default_timezone FROM tenants WHERE id = ${tenantId}::uuid),
        'Asia/Jerusalem'
      )) AS started_at,
      ((${date}::text || ' 00:00:00')::timestamp AT TIME ZONE COALESCE(
        (SELECT default_timezone FROM tenants WHERE id = ${tenantId}::uuid),
        'Asia/Jerusalem'
      ) + (${durationMin} * interval '1 minute')) AS stopped_at
  `)

  const row = result[0] as { started_at: string; stopped_at: string } | undefined
  if (!row) {
    throw new Error('Failed to compute entry times')
  }

  return {
    startedAt: new Date(row.started_at),
    stoppedAt: new Date(row.stopped_at),
  }
}

function contractorEntryLocalDateSql(tenantId: string) {
  return sql<string>`DATE(${timeEntries.startedAt} AT TIME ZONE COALESCE(
    (SELECT default_timezone FROM tenants WHERE id = ${tenantId}::uuid),
    'Asia/Jerusalem'
  ))`
}

function mapTimeEntryRow(
  row: {
    id: string
    projectId: string
    projectName: string
    taskId: string | null
    entryDate: string
    durationSeconds: number | null
    description: string | null
    approvalStatus: string
  },
): ContractorTimeEntryRow {
  const durationMin = Math.round((row.durationSeconds ?? 0) / 60)
  const hours = Math.round((durationMin / 60) * 100) / 100

  return {
    id: row.id,
    project_id: row.projectId,
    project_name: row.projectName,
    task_id: row.taskId,
    date: row.entryDate,
    duration_min: durationMin,
    hours,
    notes: row.description,
    approval_status: row.approvalStatus as ContractorTimeEntryRow['approval_status'],
  }
}

export async function listContractorTimeEntries(
  db: Db,
  args: {
    tenantId: string
    contractorId: string
    month?: string
    approvalStatus?: string
  },
): Promise<ContractorTimeEntryRow[]> {
  const conditions = [
    eq(timeEntries.tenantId, args.tenantId),
    eq(timeEntries.contractorId, args.contractorId),
    eq(timeEntries.source, 'contractor_portal'),
  ]

  if (args.month) {
    const [year, month] = args.month.split('-')
    const from = `${year}-${month}-01`
    const lastDay = new Date(Number(year), Number(month), 0).getDate()
    const to = `${year}-${month}-${String(lastDay).padStart(2, '0')}`
    conditions.push(
      sql`${contractorEntryLocalDateSql(args.tenantId)} >= ${from}::date`,
      sql`${contractorEntryLocalDateSql(args.tenantId)} <= ${to}::date`,
    )
  }

  if (args.approvalStatus) {
    conditions.push(eq(timeEntries.approvalStatus, args.approvalStatus))
  }

  const rows = await db
    .select({
      id: timeEntries.id,
      projectId: timeEntries.projectId,
      projectName: projects.name,
      taskId: timeEntries.taskId,
      entryDate: contractorEntryLocalDateSql(args.tenantId),
      durationSeconds: timeEntries.durationSeconds,
      description: timeEntries.description,
      approvalStatus: timeEntries.approvalStatus,
    })
    .from(timeEntries)
    .innerJoin(projects, eq(timeEntries.projectId, projects.id))
    .where(and(...conditions))
    .orderBy(desc(timeEntries.startedAt), desc(timeEntries.id))

  return rows.map(mapTimeEntryRow)
}

export async function createContractorTimeEntry(
  db: Db,
  args: {
    tenantId: string
    contractorId: string
    input: ContractorTimeEntryInput
    approvalStatus: 'pending' | 'auto_approved'
    startedAt: Date
    stoppedAt: Date
  },
): Promise<{ id: string }> {
  const durationSeconds = args.input.duration_min * 60

  const rows = await db
    .insert(timeEntries)
    .values({
      tenantId: args.tenantId,
      contractorId: args.contractorId,
      userId: null,
      projectId: args.input.project_id,
      taskId: args.input.task_id ?? null,
      description: args.input.notes ?? null,
      startedAt: args.startedAt,
      stoppedAt: args.stoppedAt,
      durationSeconds,
      source: 'contractor_portal',
      billable: true,
      approvalStatus: args.approvalStatus,
    })
    .returning({ id: timeEntries.id })

  return { id: rows[0]!.id }
}

export async function getContractorTimeEntry(
  db: Db,
  args: { tenantId: string; contractorId: string; entryId: string },
): Promise<{
  id: string
  projectId: string
  taskId: string | null
  approvalStatus: string
  startedAt: Date
  durationSeconds: number | null
} | null> {
  const [existing] = await db
    .select({
      id: timeEntries.id,
      projectId: timeEntries.projectId,
      taskId: timeEntries.taskId,
      approvalStatus: timeEntries.approvalStatus,
      startedAt: timeEntries.startedAt,
      durationSeconds: timeEntries.durationSeconds,
    })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, args.tenantId),
        eq(timeEntries.contractorId, args.contractorId),
        eq(timeEntries.id, args.entryId),
      ),
    )
    .limit(1)

  return existing ?? null
}

export async function updateContractorTimeEntry(
  db: Db,
  args: {
    tenantId: string
    contractorId: string
    entryId: string
    patch: Partial<ContractorTimeEntryInput>
    startedAt?: Date
    stoppedAt?: Date
  },
): Promise<void> {
  const [existing] = await db
    .select({
      id: timeEntries.id,
      approvalStatus: timeEntries.approvalStatus,
      projectId: timeEntries.projectId,
      taskId: timeEntries.taskId,
      durationSeconds: timeEntries.durationSeconds,
      description: timeEntries.description,
      startedAt: timeEntries.startedAt,
    })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, args.tenantId),
        eq(timeEntries.contractorId, args.contractorId),
        eq(timeEntries.id, args.entryId),
      ),
    )
    .limit(1)

  if (!existing) {
    throw new ContractorPortalEntryNotFoundError(args.entryId)
  }

  if (existing.approvalStatus !== 'pending') {
    throw new ContractorPortalEntryConflictError(
      `Cannot edit entry with approval_status=${existing.approvalStatus}`,
    )
  }

  const projectId = args.patch.project_id ?? existing.projectId
  const taskId = args.patch.task_id !== undefined ? args.patch.task_id ?? null : existing.taskId
  const durationMin =
    args.patch.duration_min ?? Math.round((existing.durationSeconds ?? 0) / 60)
  const durationSeconds = durationMin * 60

  const updates: Record<string, unknown> = {
    updatedAt: new Date(),
    projectId,
    taskId,
    durationSeconds,
  }

  if (args.patch.notes !== undefined) {
    updates['description'] = args.patch.notes ?? null
  }

  if (args.startedAt) updates['startedAt'] = args.startedAt
  if (args.stoppedAt) updates['stoppedAt'] = args.stoppedAt

  await db
    .update(timeEntries)
    .set(updates)
    .where(
      and(
        eq(timeEntries.tenantId, args.tenantId),
        eq(timeEntries.contractorId, args.contractorId),
        eq(timeEntries.id, args.entryId),
        eq(timeEntries.approvalStatus, 'pending'),
      ),
    )
}

export async function deleteContractorTimeEntry(
  db: Db,
  args: { tenantId: string; contractorId: string; entryId: string },
): Promise<void> {
  const [existing] = await db
    .select({ id: timeEntries.id, approvalStatus: timeEntries.approvalStatus })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, args.tenantId),
        eq(timeEntries.contractorId, args.contractorId),
        eq(timeEntries.id, args.entryId),
      ),
    )
    .limit(1)

  if (!existing) {
    throw new ContractorPortalEntryNotFoundError(args.entryId)
  }

  if (existing.approvalStatus !== 'pending') {
    throw new ContractorPortalEntryConflictError(
      `Cannot delete entry with approval_status=${existing.approvalStatus}`,
    )
  }

  await db
    .delete(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, args.tenantId),
        eq(timeEntries.contractorId, args.contractorId),
        eq(timeEntries.id, args.entryId),
        eq(timeEntries.approvalStatus, 'pending'),
      ),
    )
}

export async function listContractorBills(
  db: Db,
  args: { tenantId: string; contractorId: string },
): Promise<ContractorPayoutBillRow[]> {
  const rows = await db
    .select()
    .from(payoutBills)
    .where(
      and(
        eq(payoutBills.tenantId, args.tenantId),
        eq(payoutBills.contractorId, args.contractorId),
      ),
    )
    .orderBy(desc(payoutBills.periodEnd), desc(payoutBills.createdAt))

  return rows.map((bill) => ({
    id: bill.id,
    period_start: bill.periodStart,
    period_end: bill.periodEnd,
    amount: parseFloat(bill.amount),
    currency: bill.currency,
    status: bill.status as ContractorPayoutBillRow['status'],
    paid_at: bill.paidAt ? bill.paidAt.toISOString() : null,
  }))
}