/**
 * Contractor portal time-log CSV export — contractor-portal (wave 8, Task 6).
 *
 * Tenant + contractor scoped; dates in tenant timezone (same as list endpoint).
 */
import { and, desc, eq, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { projects } from '../schema/projects'
import { tasks } from '../schema/tasks'
import { tenants } from '../schema/tenants'
import { timeEntries } from '../schema/time'

export type ContractorPortalTimeExportRow = {
  date: string
  projectName: string
  taskTitle: string | null
  hours: number
  approvalStatus: string
  notes: string | null
}

/** CSV column headers — mirrors packages/ui i18n contractorPortal.csv.* (worker-safe, no @zync/ui). */
const CONTRACTOR_PORTAL_CSV_HEADERS = {
  en: ['Date', 'Project', 'Task', 'Hours', 'Status', 'Notes'],
  he: ['תאריך', 'פרויקט', 'משימה', 'שעות', 'סטטוס', 'הערות'],
} as const

export function contractorPortalCsvHeaders(locale: 'he' | 'en'): string[] {
  return [...CONTRACTOR_PORTAL_CSV_HEADERS[locale]]
}

const INJECTION_CHARS = new Set(['=', '+', '-', '@', '\t', '\r'])

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 sanitizeCell(value: unknown): string {
  if (value === null || value === undefined) return ''
  const str = String(value)
  if (str.length === 0) return str
  const first = str.charAt(0)
  if (INJECTION_CHARS.has(first)) return `'${str}`
  return str
}

function csvCell(value: string | number | null | undefined): string {
  const safe = sanitizeCell(value)
  if (/[",\r\n]/.test(safe)) {
    return `"${safe.replace(/"/g, '""')}"`
  }
  return safe
}

function csvRow(fields: (string | number | null | undefined)[]): string {
  return fields.map(csvCell).join(',')
}

/** Read tenant UI locale from tenants.settings JSONB (IL-first default). */
export async function resolveTenantLocale(
  db: Db,
  tenantId: string,
): Promise<'he' | 'en'> {
  const [row] = await db
    .select({ settings: tenants.settings })
    .from(tenants)
    .where(eq(tenants.id, tenantId))
    .limit(1)

  const locale =
    ((row?.settings as Record<string, unknown> | null)?.locale as string | undefined) ?? 'he'
  return locale === 'en' || locale === 'en-US' ? 'en' : 'he'
}

export async function listContractorPortalTimeExportRows(
  db: Db,
  args: { tenantId: string; contractorId: string; month: string },
): Promise<ContractorPortalTimeExportRow[]> {
  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')}`

  const rows = await db
    .select({
      entryDate: contractorEntryLocalDateSql(args.tenantId),
      projectName: projects.name,
      taskTitle: tasks.title,
      durationSeconds: timeEntries.durationSeconds,
      approvalStatus: timeEntries.approvalStatus,
      notes: timeEntries.description,
    })
    .from(timeEntries)
    .innerJoin(projects, eq(timeEntries.projectId, projects.id))
    .leftJoin(tasks, eq(timeEntries.taskId, tasks.id))
    .where(
      and(
        eq(timeEntries.tenantId, args.tenantId),
        eq(timeEntries.contractorId, args.contractorId),
        eq(timeEntries.source, 'contractor_portal'),
        sql`${contractorEntryLocalDateSql(args.tenantId)} >= ${from}::date`,
        sql`${contractorEntryLocalDateSql(args.tenantId)} <= ${to}::date`,
      ),
    )
    .orderBy(desc(timeEntries.startedAt), desc(timeEntries.id))

  return rows.map((row) => {
    const durationMin = Math.round((row.durationSeconds ?? 0) / 60)
    const hours = Math.round((durationMin / 60) * 100) / 100
    return {
      date: row.entryDate,
      projectName: row.projectName,
      taskTitle: row.taskTitle,
      hours,
      approvalStatus: row.approvalStatus,
      notes: row.notes,
    }
  })
}


export function buildContractorPortalTimeLogCsv(
  rows: ContractorPortalTimeExportRow[],
  headers: string[],
): string {
  const BOM = '\uFEFF'
  const lines: string[] = [csvRow(headers)]

  for (const row of rows) {
    lines.push(
      csvRow([
        row.date,
        row.projectName,
        row.taskTitle ?? '',
        row.hours,
        row.approvalStatus,
        row.notes ?? '',
      ]),
    )
  }

  return BOM + lines.join('\r\n')
}

export async function buildContractorPortalTimeLogCsvExport(
  db: Db,
  args: { tenantId: string; contractorId: string; month: string },
  resolveHeaders: (locale: 'he' | 'en') => string[],
): Promise<{ csv: string; filename: string; locale: 'he' | 'en' }> {
  const [locale, rows] = await Promise.all([
    resolveTenantLocale(db, args.tenantId),
    listContractorPortalTimeExportRows(db, args),
  ])

  return {
    locale,
    csv: buildContractorPortalTimeLogCsv(rows, resolveHeaders(locale)),
    filename: `contractor-time-log-${args.month}.csv`,
  }
}