import type { BackendContext } from '@jaspers-ai/sdk'
import type { TableInput } from './save'

type Rec = Record<string, unknown>

async function tryGet(ctx: BackendContext, path: string): Promise<unknown> {
  try {
    return await ctx.state.get(path)
  } catch {
    return null
  }
}

const isRec = (v: unknown): v is Rec => !!v && typeof v === 'object' && !Array.isArray(v)

function columnName(c: unknown): string {
  if (typeof c === 'string') return c
  if (isRec(c)) return String(c.label ?? c.title ?? c.header ?? c.name ?? c.key ?? c.field ?? '')
  return String(c)
}

function columnKey(c: unknown): string {
  if (isRec(c)) return String(c.key ?? c.field ?? c.name ?? c.id ?? columnName(c))
  return String(c)
}

/** A table from an object with rows under a usual key, and columns if it names them. */
function fromObject(o: unknown): { columns: string[]; rows: unknown[] } | null {
  if (!isRec(o)) return null
  for (const key of ['rows', 'data', 'items', 'results', 'records', 'table']) {
    const v = o[key]
    if (isRec(v)) {
      const inner = fromObject(v)
      if (inner) return { columns: inner.columns, rows: inner.rows }
    }
    if (!Array.isArray(v) || v.length === 0) continue
    const cols = (o.columns ?? o.headers ?? o.fields) as unknown[] | undefined
    if (Array.isArray(v[0])) {
      const columns = Array.isArray(cols) ? cols.map(columnName) : v[0].map((_, i) => `Column ${i + 1}`)
      return { columns, rows: v as unknown[] }
    }
    if (isRec(v[0])) {
      const keys = Array.isArray(cols) ? cols.map(columnKey) : Object.keys(v[0])
      const names = Array.isArray(cols) ? cols.map(columnName) : keys
      return { columns: names, rows: (v as Rec[]).map((r) => keys.map((k) => r[k])) }
    }
  }
  return null
}

/** A table from text: a Markdown pipe table, or tab-separated lines. */
function fromText(text: unknown): { columns: string[]; rows: unknown[] } | null {
  if (typeof text !== 'string') return null
  const lines = text.split(/\r?\n/)
  const pipe = lines.filter((l) => /^\s*\|.*\|\s*$/.test(l))
  if (pipe.length >= 2) {
    const cells = (l: string) => l.trim().replace(/^\||\|$/g, '').split('|').map((c) => c.trim())
    const body = pipe.filter((l) => !/^\s*\|[\s:|-]+\|\s*$/.test(l))
    if (body.length >= 1) return { columns: cells(body[0]!), rows: body.slice(1).map(cells) }
  }
  const tsv = lines.filter((l) => l.includes('\t'))
  if (tsv.length >= 2) return { columns: tsv[0]!.split('\t'), rows: tsv.slice(1).map((l) => l.split('\t')) }
  return null
}

/** A panel's table: its rows from the panel's output, text, or state, with a title. */
export async function readPanelTable(ctx: BackendContext, id: string, title?: string): Promise<TableInput> {
  const panel = await tryGet(ctx, `panels/${id}`)
  const state = (await tryGet(ctx, `panels/${id}/state`)) ?? (isRec(panel) ? panel.state : null)
  const output = (await tryGet(ctx, `panels/${id}/output`)) ?? (isRec(panel) ? panel.output : null)
  const text = (await tryGet(ctx, `panels/${id}/text`)) ?? (isRec(panel) ? panel.text : null)
  if (panel === null && state === null && output === null) throw new Error(`No panel ${id} on the grid.`)
  const table = fromObject(output) ?? fromObject(state) ?? fromText(text) ?? fromObject(panel)
  const named =
    title ??
    [state, output, panel].map((o) => (isRec(o) ? (o.title ?? o.name) : undefined)).find((t) => typeof t === 'string') ??
    `Panel ${id}`
  if (!table) {
    const s = isRec(state) ? state : {}
    const how = s.sql
      ? `its SQL (${String(s.sql).slice(0, 200)})`
      : s.source
        ? `source ${String(s.source)} with args ${JSON.stringify(s.args ?? {}).slice(0, 200)}`
        : 'its source'
    throw new Error(
      `Panel ${id} publishes no rows this plugin can read. Run ${how}, then pass the rows to excel-export/export-tables. Panel state: ${JSON.stringify(state).slice(0, 300)}; output keys: ${isRec(output) ? Object.keys(output).join(', ') : String(output)}`,
    )
  }
  return { title: String(named), columns: table.columns, rows: table.rows }
}