import { and, eq } from 'drizzle-orm'
import type { Querier, TransactionalDatabase } from '@platform-modules/db'
import type {
  Actor,
  EntityRef,
  FieldDefinition,
  FieldGroup,
  FieldValue,
  FieldValueMap,
  MediaRef,
  RelRef,
  RelRefResolver,
  ResolvedFieldGroup,
} from './model.js'
import { assertCanEditFields, assertCanManageGroups } from './authz.js'
import { FieldStoreError, FieldValidationError } from './errors.js'
import {
  assertNoCodeKeyShadow,
  assertNoCrossGroupFieldKeyConflict,
  defineFieldGroup,
  resolveGroups,
  type CodeFieldGroup,
} from './registry.js'
import { validateValues } from './validate.js'
import { fieldGroups, fieldValues, type FieldsSchema } from './schema.js'

type ValueRow = typeof fieldValues.$inferSelect
type ValueInsert = typeof fieldValues.$inferInsert

function fieldDefByKey(groups: ResolvedFieldGroup[]): Map<string, FieldDefinition> {
  const m = new Map<string, FieldDefinition>()
  for (const g of groups) {
    for (const f of g.fields) m.set(f.key, f)
  }
  return m
}

function valueToRows(
  ref: EntityRef,
  groupId: string,
  field: FieldDefinition,
  value: FieldValue,
): ValueInsert[] {
  const multiple = 'multiple' in field && field.multiple === true
  if (multiple && Array.isArray(value)) {
    return value.map((item, ordinal) => scalarToRow(ref, groupId, field, item, ordinal))
  }
  return [scalarToRow(ref, groupId, field, value as FieldValue, 0)]
}

function scalarToRow(
  ref: EntityRef,
  groupId: string,
  field: FieldDefinition,
  value: FieldValue,
  ordinal: number,
): ValueInsert {
  const base = {
    entityType: ref.entityType,
    entityId: ref.entityId,
    groupId,
    fieldKey: field.key,
    ordinal,
  }
  switch (field.type) {
    case 'text':
    case 'textarea':
    case 'select':
    case 'color':
    case 'url':
    case 'email':
      return { ...base, valueText: String(value) }
    case 'number':
      return { ...base, valueNum: String(value) }
    case 'boolean':
      return { ...base, valueBool: value as boolean }
    case 'date': {
      const d = value instanceof Date ? value : new Date(value as string)
      return { ...base, valueDate: d }
    }
    case 'media': {
      const m = value as MediaRef
      const { key, url, mime, meta } = m
      const refMeta: Record<string, unknown> = {}
      if (url !== undefined) refMeta.url = url
      if (mime !== undefined) refMeta.mime = mime
      if (meta !== undefined) refMeta.meta = meta
      return {
        ...base,
        refType: 'media',
        refId: key,
        refMeta: Object.keys(refMeta).length > 0 ? refMeta : null,
      }
    }
    case 'relationship': {
      const r = value as RelRef
      return { ...base, refType: r.entityType, refId: r.entityId }
    }
  }
}

function rowToValue(field: FieldDefinition, rows: ValueRow[]): FieldValue | undefined {
  if (rows.length === 0) return undefined
  const multiple = 'multiple' in field && field.multiple === true
  const sorted = [...rows].sort((a, b) => a.ordinal - b.ordinal)
  if (multiple) return sorted.map((r) => rowScalar(field, r)) as FieldValue
  return rowScalar(field, sorted[0]!)
}

function rowScalar(field: FieldDefinition, row: ValueRow): FieldValue {
  switch (field.type) {
    case 'text':
    case 'textarea':
    case 'select':
    case 'color':
    case 'url':
    case 'email':
      return row.valueText ?? ''
    case 'number':
      return row.valueNum !== null && row.valueNum !== undefined ? Number(row.valueNum) : 0
    case 'boolean':
      return row.valueBool ?? false
    case 'date':
      return row.valueDate ?? new Date(0)
    case 'media': {
      const meta = (row.refMeta ?? {}) as Record<string, unknown>
      const out: MediaRef = { key: row.refId ?? '' }
      if (typeof meta.url === 'string') out.url = meta.url
      if (typeof meta.mime === 'string') out.mime = meta.mime
      if (meta.meta !== undefined && typeof meta.meta === 'object' && meta.meta !== null) {
        out.meta = meta.meta as Record<string, unknown>
      }
      return out
    }
    case 'relationship':
      return { entityType: row.refType ?? '', entityId: row.refId ?? '' }
  }
}

async function assertRefsExist(
  resolved: ResolvedFieldGroup,
  values: FieldValueMap,
  resolveRef?: RelRefResolver,
): Promise<void> {
  if (!resolveRef) return
  for (const f of resolved.fields) {
    if (f.type !== 'relationship') continue
    const v = values[f.key]
    if (v === undefined || v === null) continue
    const refs = Array.isArray(v) ? v : [v]
    for (const ref of refs) {
      const r = ref as RelRef
      const ok = await resolveRef(r)
      if (!ok) throw new FieldValidationError(f.key, 'referenced entity not found')
    }
  }
}

function wrapStoreError(e: unknown): never {
  if (e instanceof FieldValidationError || e instanceof FieldStoreError) throw e
  const detail = e instanceof Error ? e.message : String(e)
  throw new FieldStoreError(detail)
}

export async function getEntityValues(
  db: Querier<FieldsSchema>,
  ref: EntityRef,
  opts: { entityType: string; subType?: string; codeGroups?: CodeFieldGroup[] },
): Promise<FieldValueMap> {
  const groups = await resolveGroups(db, {
    entityType: opts.entityType,
    subType: opts.subType,
    codeGroups: opts.codeGroups,
  })
  const defs = fieldDefByKey(groups)
  const rows = await db
    .select()
    .from(fieldValues)
    .where(and(eq(fieldValues.entityType, ref.entityType), eq(fieldValues.entityId, ref.entityId)))

  const byKey = new Map<string, ValueRow[]>()
  for (const row of rows) {
    const list = byKey.get(row.fieldKey) ?? []
    list.push(row)
    byKey.set(row.fieldKey, list)
  }

  const out: FieldValueMap = {}
  for (const [key, fieldRows] of byKey) {
    const def = defs.get(key)
    if (!def) continue
    out[key] = rowToValue(def, fieldRows)
  }
  return out
}

export async function setEntityValues(
  db: TransactionalDatabase<FieldsSchema>,
  actor: Actor,
  input: {
    ref: EntityRef
    groupId: string
    values: FieldValueMap
    resolved: ResolvedFieldGroup
    resolveRef?: RelRefResolver
  },
): Promise<void> {
  assertCanEditFields(actor)
  validateValues(input.resolved, input.values)
  await assertRefsExist(input.resolved, input.values, input.resolveRef)

  const rows: ValueInsert[] = []
  for (const f of input.resolved.fields) {
    const v = input.values[f.key]
    if (v === undefined || v === null) continue
    rows.push(...valueToRows(input.ref, input.groupId, f, v))
  }

  try {
    await db.transaction(async (tx) => {
      await tx
        .delete(fieldValues)
        .where(
          and(
            eq(fieldValues.entityType, input.ref.entityType),
            eq(fieldValues.entityId, input.ref.entityId),
            eq(fieldValues.groupId, input.groupId),
          ),
        )
      if (rows.length > 0) await tx.insert(fieldValues).values(rows)
    })
  } catch (e) {
    wrapStoreError(e)
  }
}

/**
 * Host-cascade op: removes ALL field values for an entity (call on parent-entity delete, spec §8.1).
 * Takes NO `Actor` and runs NO capability check by design — so the HOST MUST gate BOTH capability
 * AND object-level ownership BEFORE calling this. The engine never knows who owns `entityId`
 * (no owner column; spec §5). Never assume the engine authorizes this delete.
 */
export async function deleteEntityValues(
  db: Querier<FieldsSchema>,
  ref: EntityRef,
): Promise<void> {
  await db
    .delete(fieldValues)
    .where(and(eq(fieldValues.entityType, ref.entityType), eq(fieldValues.entityId, ref.entityId)))
}

export async function createGroup(
  db: Querier<FieldsSchema>,
  actor: Actor,
  group: FieldGroup,
  codeGroups?: CodeFieldGroup[],
): Promise<ResolvedFieldGroup> {
  assertCanManageGroups(actor)
  defineFieldGroup(group)
  assertNoCodeKeyShadow(group.key, group.location.entityType, codeGroups)

  const existing = await resolveGroups(db, {
    entityType: group.location.entityType,
    subType: group.location.subType,
    codeGroups,
  })
  const candidate: ResolvedFieldGroup = { ...group, origin: 'db' }
  assertNoCrossGroupFieldKeyConflict([...existing, candidate])

  try {
    const [row] = await db
      .insert(fieldGroups)
      .values({
        entityType: group.location.entityType,
        subType: group.location.subType ?? null,
        key: group.key,
        label: group.label,
        fields: group.fields,
        position: group.position ?? 0,
      })
      .returning()
    return {
      key: row!.key,
      label: row!.label,
      location: { entityType: row!.entityType, subType: row!.subType ?? undefined },
      fields: row!.fields as FieldDefinition[],
      position: row!.position,
      origin: 'db',
      id: row!.id,
    }
  } catch (e) {
    wrapStoreError(e)
  }
}

export async function updateGroup(
  db: Querier<FieldsSchema>,
  actor: Actor,
  id: string,
  patch: Partial<FieldGroup>,
  codeGroups?: CodeFieldGroup[],
): Promise<ResolvedFieldGroup> {
  assertCanManageGroups(actor)
  const [current] = await db.select().from(fieldGroups).where(eq(fieldGroups.id, id)).limit(1)
  if (!current) throw new FieldStoreError(`group not found: ${id}`)

  const merged: FieldGroup = {
    key: patch.key ?? current.key,
    label: patch.label ?? current.label,
    location: patch.location ?? { entityType: current.entityType, subType: current.subType ?? undefined },
    fields: patch.fields ?? (current.fields as FieldDefinition[]),
    position: patch.position ?? current.position,
  }
  defineFieldGroup(merged)
  if (merged.key !== current.key) {
    assertNoCodeKeyShadow(merged.key, merged.location.entityType, codeGroups)
  }

  const others = (await resolveGroups(db, {
    entityType: merged.location.entityType,
    subType: merged.location.subType,
    codeGroups,
  })).filter((g) => g.id !== id)
  assertNoCrossGroupFieldKeyConflict([...others, { ...merged, origin: 'db', id }])

  try {
    const [row] = await db
      .update(fieldGroups)
      .set({
        entityType: merged.location.entityType,
        subType: merged.location.subType ?? null,
        key: merged.key,
        label: merged.label,
        fields: merged.fields,
        position: merged.position ?? 0,
        updatedAt: new Date(),
      })
      .where(eq(fieldGroups.id, id))
      .returning()
    return {
      key: row!.key,
      label: row!.label,
      location: { entityType: row!.entityType, subType: row!.subType ?? undefined },
      fields: row!.fields as FieldDefinition[],
      position: row!.position,
      origin: 'db',
      id: row!.id,
    }
  } catch (e) {
    wrapStoreError(e)
  }
}

export async function deleteGroup(
  db: Querier<FieldsSchema>,
  actor: Actor,
  id: string,
): Promise<void> {
  assertCanManageGroups(actor)
  await db.delete(fieldGroups).where(eq(fieldGroups.id, id))
}

export async function listGroups(
  db: Querier<FieldsSchema>,
  opts: { entityType?: string; subType?: string },
): Promise<ResolvedFieldGroup[]> {
  const filters = []
  if (opts.entityType !== undefined) filters.push(eq(fieldGroups.entityType, opts.entityType))
  if (opts.subType !== undefined) filters.push(eq(fieldGroups.subType, opts.subType))

  const rows = await db
    .select()
    .from(fieldGroups)
    .where(filters.length > 0 ? and(...filters) : undefined)

  return rows.map((row) => ({
    key: row.key,
    label: row.label,
    location: { entityType: row.entityType, subType: row.subType ?? undefined },
    fields: row.fields as FieldDefinition[],
    position: row.position,
    origin: 'db' as const,
    id: row.id,
  }))
}