/**
 * Integration status-derivation helper — integration-hub (wave-11 leaf-D).
 *
 * Queries all upstream tables tenant-scoped and derives the 3-state
 * IntegrationStatus (connected | error | not_connected) plus optional details.
 *
 * Note: tenant_email_config (custom-smtp-email-whitelabel) table is not yet
 * present in this corpus; smtp/dkim entries default to not_connected until
 * that spec's schema is applied.
 */
import { and, desc, eq, sql } from '@zync/db'
import type { Db } from '@zync/db'
import type { IntegrationStatus, IntegrationDetails } from '@zync/types'
import {
  calendarConnections,
  schedulingConnections,
  adapterCredentials,
  integrationSyncLogs,
  paymentGatewayConfigs,
} from '@zync/db/schema'

export interface DerivedStatus {
  status: IntegrationStatus
  details: IntegrationDetails
}

const SEVEN_DAYS_MS = 7 * 24 * 60 * 60 * 1000

export async function deriveIntegrationStatuses(
  db: Db,
  tenantId: string,
): Promise<Map<string, DerivedStatus>> {
  const map = new Map<string, DerivedStatus>()

  // Run all queries in parallel
  const [calendarRows, schedulingRows, adapterRows, paymentRows] = await Promise.all([
    db
      .select({
        provider: calendarConnections.provider,
        tokenExpiresAt: calendarConnections.tokenExpiresAt,
        syncEnabled: calendarConnections.syncEnabled,
        lastSyncedAt: calendarConnections.lastSyncedAt,
      })
      .from(calendarConnections)
      .where(eq(calendarConnections.tenantId, tenantId)),

    db
      .select({ provider: schedulingConnections.provider })
      .from(schedulingConnections)
      .where(eq(schedulingConnections.tenantId, tenantId)),

    db
      .select({
        adapterId: adapterCredentials.adapterId,
        updatedAt: adapterCredentials.updatedAt,
      })
      .from(adapterCredentials)
      .where(eq(adapterCredentials.tenantId, tenantId)),

    db
      .select({
        gateway: paymentGatewayConfigs.gateway,
        isActive: paymentGatewayConfigs.isActive,
      })
      .from(paymentGatewayConfigs)
      .where(eq(paymentGatewayConfigs.tenantId, tenantId)),
  ])

  // ── Calendar connections ───────────────────────────────────────────────────
  for (const row of calendarRows) {
    const key = `calendar:${row.provider}`
    const now = new Date()
    let status: IntegrationStatus = 'connected'
    const details: IntegrationDetails = {}

    if (row.tokenExpiresAt) {
      if (row.tokenExpiresAt < now) {
        status = 'error'
      } else if (row.tokenExpiresAt.getTime() - now.getTime() < SEVEN_DAYS_MS) {
        details.token_expiring = true
      }
    }

    if (row.lastSyncedAt) {
      details.last_sync_at = row.lastSyncedAt.toISOString()
    }

    map.set(key, { status, details })
  }

  // ── Scheduling connections ─────────────────────────────────────────────────
  for (const row of schedulingRows) {
    map.set(`scheduling:${row.provider}`, { status: 'connected', details: {} })
  }

  // ── Adapter credentials (invoice adapters, task sync, comms bots) ──────────
  for (const row of adapterRows) {
    // Adapter ids: 'gmail','outlook','telegram','slack','whatsapp','smtp','trello','asana',
    //              'jira','monday','clickup' or 'invoice:*'
    let catalogKey: string
    if (row.adapterId.startsWith('invoice:')) {
      // e.g. 'invoice:morning' → catalog id 'morning'
      catalogKey = `invoice:${row.adapterId.slice('invoice:'.length)}`
    } else {
      catalogKey = `adapter:${row.adapterId}`
    }

    // Fetch the most recent sync log for this adapter
    const [latestLog] = await db
      .select({
        status: integrationSyncLogs.status,
        errorPayload: integrationSyncLogs.errorPayload,
        createdAt: integrationSyncLogs.createdAt,
      })
      .from(integrationSyncLogs)
      .where(
        and(
          eq(integrationSyncLogs.tenantId, tenantId),
          sql`${integrationSyncLogs.provider} = ${row.adapterId.startsWith('invoice:') ? row.adapterId.slice('invoice:'.length) : row.adapterId}`,
        ),
      )
      .orderBy(desc(integrationSyncLogs.createdAt))
      .limit(1)

    let status: IntegrationStatus = 'connected'
    const details: IntegrationDetails = {}

    if (latestLog) {
      if (latestLog.status === 'failure') {
        status = 'error'
        const payload = latestLog.errorPayload as Record<string, unknown> | null
        if (payload?.message && typeof payload.message === 'string') {
          details.error_message = payload.message
        } else if (payload?.error && typeof payload.error === 'string') {
          details.error_message = payload.error
        }
      } else if (latestLog.status === 'success') {
        details.last_sync_at = latestLog.createdAt.toISOString()
      }
    }

    map.set(catalogKey, { status, details })
  }

  // ── Payment gateways ───────────────────────────────────────────────────────
  for (const row of paymentRows) {
    const key = `payment:${row.gateway}`
    const status: IntegrationStatus = row.isActive ? 'connected' : 'not_connected'
    map.set(key, { status, details: {} })
  }

  // smtp / dkim entries default to not_connected (tenant_email_config table
  // is owned by custom-smtp-email-whitelabel spec, not yet applied in corpus)
  // map.set('email:smtp', ...) and map.set('email:dkim', ...) are omitted;
  // they will fall through to the catalog default of not_connected.

  return map
}