/**
 * Contractor portal session queries — contractor-portal (wave 8, Task 4).
 *
 * Magic-link tokens are stored as SHA-256 hex hashes; plaintext never persisted.
 */
import { and, eq, gt, isNull, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { contractorPortalSessions } from '../schema/contractor-portal'
import { contractors } from '../schema/contractors'

export async function createPortalSession(
  db: Db,
  args: { tenantId: string; contractorId: string; tokenHash: string; expiresAt: Date },
): Promise<{ id: string }> {
  const rows = await db
    .insert(contractorPortalSessions)
    .values({
      contractorId: args.contractorId,
      tokenHash: args.tokenHash,
      expiresAt: args.expiresAt,
    })
    .returning({ id: contractorPortalSessions.id })

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

export async function getPortalAccessStatus(
  db: Db,
  args: { tenantId: string; contractorId: string },
): Promise<{ hasPortalAccess: boolean; lastSeen: string | null }> {
  const [contractor] = await db
    .select({ id: contractors.id })
    .from(contractors)
    .where(
      and(eq(contractors.tenantId, args.tenantId), eq(contractors.id, args.contractorId)),
    )
    .limit(1)

  if (!contractor) {
    return { hasPortalAccess: false, lastSeen: null }
  }

  const [agg] = await db
    .select({
      lastSeen: sql<string | null>`MAX(${contractorPortalSessions.usedAt})`,
      usedCount: sql<number>`COUNT(*) FILTER (WHERE ${contractorPortalSessions.usedAt} IS NOT NULL)`,
    })
    .from(contractorPortalSessions)
    .where(eq(contractorPortalSessions.contractorId, args.contractorId))

  const hasPortalAccess = (agg?.usedCount ?? 0) > 0
  const lastSeen = agg?.lastSeen ? new Date(agg.lastSeen).toISOString() : null

  return { hasPortalAccess, lastSeen }
}

export async function findValidPortalSession(
  db: Db,
  tokenHash: string,
): Promise<{
  id: string
  contractorId: string
  tenantId: string
  expiresAt: Date
  usedAt: Date | null
  tokenHash: string
} | null> {
  const now = new Date()
  const rows = await db
    .select({
      id: contractorPortalSessions.id,
      contractorId: contractorPortalSessions.contractorId,
      tenantId: contractors.tenantId,
      expiresAt: contractorPortalSessions.expiresAt,
      usedAt: contractorPortalSessions.usedAt,
      tokenHash: contractorPortalSessions.tokenHash,
    })
    .from(contractorPortalSessions)
    .innerJoin(contractors, eq(contractorPortalSessions.contractorId, contractors.id))
    .where(
      and(
        eq(contractorPortalSessions.tokenHash, tokenHash),
        gt(contractorPortalSessions.expiresAt, now),
        isNull(contractorPortalSessions.usedAt),
      ),
    )
    .limit(1)

  return rows[0] ?? null
}

export async function markPortalSessionUsed(db: Db, sessionId: string): Promise<boolean> {
  const rows = await db
    .update(contractorPortalSessions)
    .set({ usedAt: new Date() })
    .where(
      and(
        eq(contractorPortalSessions.id, sessionId),
        isNull(contractorPortalSessions.usedAt),
      ),
    )
    .returning({ id: contractorPortalSessions.id })

  return rows.length === 1
}

export async function getContractorForPortalRedeem(
  db: Db,
  contractorId: string,
  tenantId: string,
): Promise<{ id: string; active: boolean } | null> {
  const [row] = await db
    .select({ id: contractors.id, active: contractors.active })
    .from(contractors)
    .where(and(eq(contractors.id, contractorId), eq(contractors.tenantId, tenantId)))
    .limit(1)

  return row ?? null
}