/**
 * Task adapter sync cron route — tasks-board-engine.
 *
 * POST /api/cron/tasks-sync
 *
 * Guarded by CRON_SECRET (timing-safe comparison).
 * Scheduled: every 2h via wrangler.toml cron trigger.
 */
import { Hono } from 'hono'
import { timingSafeEqual, decryptCredential } from '@zync/auth'
import {
  createDb,
  listStatuses,
  createTask,
  taskSyncSettings,
  getTenantOwnerUserId,
} from '@zync/db/queries'
import { getTaskAdapter, TASK_ADAPTER_IDS } from '@zync/integrations/tasks'
import { eq, sql } from '@zync/db'
import type { AppEnv } from '../../types'

export const tasksSyncCronRoute = new Hono<AppEnv>()

export type TaskSyncResult = {
  tenantId: string
  adapterId: string
  status: 'success' | 'error'
  itemsCount: number
  errorMsg?: string
}

export async function runTaskSync({
  db,
  env,
  tenantIds,
  force = false,
}: {
  db: ReturnType<typeof createDb>
  env: AppEnv['Bindings']
  tenantIds?: string[]
  force?: boolean
}): Promise<TaskSyncResult[]> {
  const encryptionKey = env.INTEGRATION_ENCRYPTION_KEY ?? ''
  const adapterIdsList = TASK_ADAPTER_IDS.map((id) => `'${id}'`).join(',')
  const tenantFilter =
    tenantIds && tenantIds.length > 0
      ? ` AND tenant_id IN (${tenantIds.map((id) => `'${id}'`).join(',')})`
      : ''
  const credResultRaw = await db.execute(
    sql.raw(`SELECT tenant_id, adapter_id, ciphertext, iv, auth_tag
             FROM adapter_credentials
             WHERE adapter_id IN (${adapterIdsList})${tenantFilter}`),
  )
  const credRows =
    (credResultRaw as unknown as {
      rows: Array<{
        tenant_id: string
        adapter_id: string
        ciphertext: string
        iv: string
        auth_tag: string
      }>
    }).rows ??
    (credResultRaw as unknown as Array<{
      tenant_id: string
      adapter_id: string
      ciphertext: string
      iv: string
      auth_tag: string
    }>)

  const results: TaskSyncResult[] = []

  for (const credRow of credRows) {
    const tenantId = credRow.tenant_id
    const adapterId = credRow.adapter_id
    const [syncRow] = await db
      .select()
      .from(taskSyncSettings)
      .where(eq(taskSyncSettings.tenantId, tenantId))
      .limit(1)

    let shouldSync = true
    if (!force && syncRow) {
      try {
        const lastLogResult = await db.execute(
          sql`SELECT created_at FROM integration_sync_logs
              WHERE tenant_id = ${tenantId}
                AND adapter_id = ${adapterId}
              ORDER BY created_at DESC LIMIT 1`,
        )
        const lastLogRows = ((lastLogResult as unknown as { rows?: Record<string, unknown>[] }).rows ?? lastLogResult) as Record<string, unknown>[]
        const lastLog = lastLogRows[0]
        if (lastLog?.['created_at']) {
          const lastAt = new Date(String(lastLog['created_at']))
          const intervalMs = (syncRow.syncIntervalMinutes ?? 120) * 60 * 1000
          shouldSync = Date.now() - lastAt.getTime() >= intervalMs
        }
      } catch {
        shouldSync = true
      }
    }

    if (!shouldSync) continue

    let itemsCount = 0
    let runStatus: 'success' | 'error' = 'success'
    let errorMsg: string | undefined

    try {
      const reporterId = await getTenantOwnerUserId(db, tenantId)
      if (!reporterId) {
        throw new Error(`No tenant owner found for tenant ${tenantId}`)
      }

      const rawCreds = await decryptCredential(
        { ciphertext: credRow.ciphertext, iv: credRow.iv, authTag: credRow.auth_tag },
        encryptionKey,
      )
      const credentials = JSON.parse(rawCreds) as Record<string, unknown>
      const adapter = getTaskAdapter(adapterId)
      const externalTasks = await adapter.fetchTasks(credentials)
      itemsCount = externalTasks.length

      if (itemsCount > 0) {
        const statuses = await listStatuses(db, tenantId)
        const firstNonTerminal = statuses.find((s) => !s.is_terminal) ?? statuses[0]
        if (!firstNonTerminal) continue

        const defaultProjectId = syncRow?.defaultProjectId ?? null

        for (const externalTask of externalTasks) {
          const partial = adapter.mapToTask(externalTask, tenantId, defaultProjectId ?? '')
          const matchedStatus = externalTask.status
            ? statuses.find((s) => s.name.toLowerCase() === externalTask.status?.toLowerCase())
            : undefined
          const resolvedStatusId = matchedStatus?.id ?? (partial.status_id ?? firstNonTerminal.id)

          try {
            await createTask(db, tenantId, {
              title: partial.title ?? externalTask.title,
              status_id: resolvedStatusId,
              priority: (partial.priority as 'low' | 'medium' | 'high' | 'urgent') ?? 'medium',
              reporter_id: partial.reporter_id ?? reporterId,
              project_id: partial.project_id ?? defaultProjectId,
              source: (partial.source as 'trello' | 'asana' | 'jira' | 'monday' | 'clickup' | 'slack') ?? 'manual',
              external_id: partial.external_id ?? externalTask.externalId,
              due_date: partial.due_date ?? externalTask.dueDate ?? null,
              description: partial.description,
              labels: partial.labels ?? [],
            })
          } catch (insertErr) {
            if (String(insertErr).includes('unique') || String(insertErr).includes('duplicate')) {
              continue
            }
            throw insertErr
          }
        }
      }
    } catch (err) {
      runStatus = 'error'
      errorMsg = String(err)
    }

    results.push({ tenantId, adapterId, status: runStatus, itemsCount, errorMsg })
  }

  return results
}

tasksSyncCronRoute.post('/', async (c) => {
  const secret =
    c.req.header('x-cron-secret') ??
    c.req.header('authorization')?.replace('Bearer ', '') ??
    ''
  const expected = c.env.CRON_SECRET
  if (!expected || expected.length < 32) {
    return c.json({ error: 'Server misconfigured' }, 500)
  }
  if (!secret || !timingSafeEqual(secret, expected)) {
    return c.json({ error: 'Forbidden' }, 401)
  }

  const db = createDb(c.env)

  try {
    const results = await runTaskSync({ db, env: c.env })
    for (const { tenantId, adapterId, status, errorMsg } of results) {
      try {
        await db.execute(sql`
          INSERT INTO integration_sync_logs
            (tenant_id, adapter_id, entity_type, entity_id, direction, status, error_message)
          VALUES (
            ${tenantId}::uuid,
            ${adapterId},
            'task_sync',
            gen_random_uuid(),
            'pull',
            ${status},
            ${errorMsg ?? null}
          )
        `)
      } catch {
        // Best-effort only.
      }
    }
    return c.json({ ok: true, results }, 200)
  } catch (err) {
    return c.json({ error: 'Sync failed', details: String(err) }, 500)
  }
})