/**
 * Portal file sharing query helpers — portal-file-sharing (wave-11 leaf C).
 *
 * All helpers are tenant-scoped. Routes MUST NOT import raw Drizzle tables.
 * Staff routes use tenant-scoped helpers (all files).
 * Portal routes use portal-scoped helpers (visible, non-expired only).
 */
import { and, eq, isNull, or, gt, desc } from 'drizzle-orm'
import type { Db } from '../client'
import { portalFiles } from '../schema/portal-files'
import {
  assertTenantOwnsCustomer,
  assertTenantOwnsOrThrow,
  assertTenantOwnsProject,
} from './tenant-guards'

// ── Errors ────────────────────────────────────────────────────────────────────

export class InvalidUploaderError extends Error {
  constructor() {
    super('Exactly one of uploadedBy or uploadedByPortalUser must be provided')
    this.name = 'InvalidUploaderError'
  }
}

// ── Types ─────────────────────────────────────────────────────────────────────

export interface PortalFile {
  id: string
  tenantId: string
  customerId: string
  projectId: string | null
  uploadedBy: string | null
  uploadedByPortalUser: string | null
  uploadedByClient: boolean
  r2Key: string
  filename: string
  fileSizeBytes: number
  mimeType: string
  description: string | null
  visibleToPortal: boolean
  createdAt: string
  expiresAt: string | null
}

export interface PortalFileListItem extends PortalFile {
  uploaderName: string | null
}

export interface PortalFilePublicItem {
  id: string
  filename: string
  fileSizeBytes: number
  mimeType: string
  projectId: string | null
  createdAt: string
}

export interface NewPortalFile {
  tenantId: string
  customerId: string
  projectId?: string | null
  uploadedBy?: string | null
  uploadedByPortalUser?: string | null
  r2Key: string
  filename: string
  fileSizeBytes: number
  mimeType: string
  description?: string | null
  visibleToPortal?: boolean
  expiresAt?: Date | null
}

export interface UpdatePortalFileInput {
  description?: string | null
  visibleToPortal?: boolean
  expiresAt?: Date | null
}

export interface PortalFileListParams {
  customerId: string
  projectId?: string | null
  limit?: number
  offset?: number
}

// ── Serializer ────────────────────────────────────────────────────────────────

function serializeRow(row: typeof portalFiles.$inferSelect): PortalFile {
  return {
    id: row.id,
    tenantId: row.tenantId,
    customerId: row.customerId,
    projectId: row.projectId ?? null,
    uploadedBy: row.uploadedBy ?? null,
    uploadedByPortalUser: row.uploadedByPortalUser ?? null,
    uploadedByClient: row.uploadedByPortalUser !== null,
    r2Key: row.r2Key,
    filename: row.filename,
    fileSizeBytes: row.fileSizeBytes,
    mimeType: row.mimeType,
    description: row.description ?? null,
    visibleToPortal: row.visibleToPortal,
    createdAt: row.createdAt.toISOString(),
    expiresAt: row.expiresAt ? row.expiresAt.toISOString() : null,
  }
}

// ── Staff queries ─────────────────────────────────────────────────────────────

export async function listPortalFilesForStaff(
  db: Db,
  tenantId: string,
  params: PortalFileListParams,
): Promise<PortalFileListItem[]> {
  const limit = params.limit ?? 50
  const offset = params.offset ?? 0

  const conditions = [
    eq(portalFiles.tenantId, tenantId),
    eq(portalFiles.customerId, params.customerId),
  ]
  if (params.projectId != null) {
    conditions.push(eq(portalFiles.projectId, params.projectId))
  }

  const rows = await db
    .select()
    .from(portalFiles)
    .where(and(...conditions))
    .orderBy(desc(portalFiles.createdAt))
    .limit(limit)
    .offset(offset)

  return rows.map((row) => ({
    ...serializeRow(row),
    uploaderName: null, // join with users in future if needed
  }))
}

export async function listPortalFilesForPortal(
  db: Db,
  tenantId: string,
  customerId: string,
  params: { limit?: number; offset?: number },
): Promise<PortalFilePublicItem[]> {
  const limit = params.limit ?? 50
  const offset = params.offset ?? 0

  const now = new Date()

  const rows = await db
    .select({
      id: portalFiles.id,
      filename: portalFiles.filename,
      fileSizeBytes: portalFiles.fileSizeBytes,
      mimeType: portalFiles.mimeType,
      projectId: portalFiles.projectId,
      createdAt: portalFiles.createdAt,
    })
    .from(portalFiles)
    .where(
      and(
        eq(portalFiles.tenantId, tenantId),
        eq(portalFiles.customerId, customerId),
        eq(portalFiles.visibleToPortal, true),
        or(isNull(portalFiles.expiresAt), gt(portalFiles.expiresAt, now)),
      ),
    )
    .orderBy(desc(portalFiles.createdAt))
    .limit(limit)
    .offset(offset)

  return rows.map((row) => ({
    id: row.id,
    filename: row.filename,
    fileSizeBytes: row.fileSizeBytes,
    mimeType: row.mimeType,
    projectId: row.projectId ?? null,
    createdAt: row.createdAt.toISOString(),
  }))
}

export async function getPortalFileById(
  db: Db,
  tenantId: string,
  id: string,
): Promise<PortalFile | null> {
  const rows = await db
    .select()
    .from(portalFiles)
    .where(and(eq(portalFiles.id, id), eq(portalFiles.tenantId, tenantId)))
    .limit(1)

  if (rows.length === 0) return null
  return serializeRow(rows[0]!)
}

export async function createPortalFile(
  db: Db,
  input: NewPortalFile,
): Promise<PortalFile> {
  // Enforce exactly-one-uploader invariant
  const hasStaff = input.uploadedBy != null
  const hasPortal = input.uploadedByPortalUser != null
  if ((hasStaff && hasPortal) || (!hasStaff && !hasPortal)) {
    throw new InvalidUploaderError()
  }

  assertTenantOwnsOrThrow(
    'customer_id',
    await assertTenantOwnsCustomer(db, input.tenantId, input.customerId),
  )
  assertTenantOwnsOrThrow(
    'project_id',
    await assertTenantOwnsProject(db, input.tenantId, input.projectId),
  )

  const rows = await db
    .insert(portalFiles)
    .values({
      tenantId: input.tenantId,
      customerId: input.customerId,
      projectId: input.projectId ?? null,
      uploadedBy: input.uploadedBy ?? null,
      uploadedByPortalUser: input.uploadedByPortalUser ?? null,
      r2Key: input.r2Key,
      filename: input.filename,
      fileSizeBytes: input.fileSizeBytes,
      mimeType: input.mimeType,
      description: input.description ?? null,
      visibleToPortal: input.visibleToPortal ?? true,
      expiresAt: input.expiresAt ?? null,
    })
    .returning()

  return serializeRow(rows[0]!)
}

export async function updatePortalFile(
  db: Db,
  tenantId: string,
  id: string,
  input: UpdatePortalFileInput,
): Promise<PortalFile | null> {
  const updates: Partial<typeof portalFiles.$inferInsert> = {}
  if ('description' in input) updates.description = input.description ?? null
  if ('visibleToPortal' in input) updates.visibleToPortal = input.visibleToPortal
  if ('expiresAt' in input) updates.expiresAt = input.expiresAt ?? null

  if (Object.keys(updates).length === 0) return getPortalFileById(db, tenantId, id)

  const rows = await db
    .update(portalFiles)
    .set(updates)
    .where(and(eq(portalFiles.id, id), eq(portalFiles.tenantId, tenantId)))
    .returning()

  if (rows.length === 0) return null
  return serializeRow(rows[0]!)
}

export async function deletePortalFile(
  db: Db,
  tenantId: string,
  id: string,
): Promise<{ r2Key: string } | null> {
  const rows = await db
    .delete(portalFiles)
    .where(and(eq(portalFiles.id, id), eq(portalFiles.tenantId, tenantId)))
    .returning({ r2Key: portalFiles.r2Key })

  if (rows.length === 0) return null
  return { r2Key: rows[0]!.r2Key }
}