import type { D1Database } from '@cloudflare/workers-types'
import { clampLimit, decodeCursor, encodeCursor } from '../cursor.js'
import { deriveStored } from '../derive.js'
import type { ListFilter, Page, RecordStore, Stored } from '../types.js'

export type D1RecordStoreOptions = {
  now?: () => string
  makeId?: () => string
}

type D1Row = {
  seq: number
  id: string
  created_at_ms: number
  record: string
}

export function recordStoreD1TableSql(table: string): string {
  // CF D1's `.exec()` splits a multi-statement string on NEWLINES, so each
  // statement must be one complete, newline-free, semicolon-terminated string;
  // join the ARRAY with '\n' (never join an already-concatenated string, which
  // would split a statement mid-line). The host applies this via its migration.
  const createTableSql =
    `CREATE TABLE IF NOT EXISTS ${table} (` +
    `seq INTEGER PRIMARY KEY AUTOINCREMENT, ` +
    `id text NOT NULL, ` +
    `created_at_ms integer NOT NULL, ` +
    `record text NOT NULL` +
    `);`
  const createIndexSql = `CREATE INDEX IF NOT EXISTS ${table}_created_at_ms_seq_idx ON ${table} (created_at_ms DESC, seq DESC);`
  return [createTableSql, createIndexSql].join('\n')
}

function rowToStored<T>(row: D1Row): Stored<T> {
  return {
    id: row.id,
    createdAt: new Date(row.created_at_ms).toISOString(),
    record: JSON.parse(row.record) as T,
  }
}

function snapshotStored<T>(stored: Stored<T>): Stored<T> {
  return {
    id: stored.id,
    createdAt: stored.createdAt,
    record: JSON.parse(JSON.stringify(stored.record)) as T,
  }
}

export function makeD1RecordStore<T>(
  d1: D1Database,
  table: string,
  opts: D1RecordStoreOptions = {},
): RecordStore<T> {
  const now = opts.now ?? (() => new Date().toISOString())
  const makeId = opts.makeId ?? (() => crypto.randomUUID())

  return {
    async append(record: T): Promise<Stored<T>> {
      const derived = deriveStored(record, now, makeId)

      const result = await d1
        .prepare(`INSERT INTO ${table} (id, created_at_ms, record) VALUES (?, ?, ?)`)
        .bind(derived.id, derived.createdAtMs, JSON.stringify(derived.record))
        .run()

      if (result.meta.last_row_id === undefined || result.meta.last_row_id === null) {
        throw new Error('makeD1RecordStore.append: insert returned no last_row_id')
      }

      return snapshotStored({
        id: derived.id,
        createdAt: derived.createdAt,
        record: derived.record,
      })
    },

    async list(filter: ListFilter = {}): Promise<Page<Stored<T>>> {
      const limit = clampLimit(filter.limit)

      let rows: D1Row[]

      if (filter.cursor !== undefined) {
        const cursor = decodeCursor(filter.cursor)
        const result = await d1
          .prepare(
            `SELECT seq, id, created_at_ms, record FROM ${table}
             WHERE created_at_ms < ? OR (created_at_ms = ? AND seq < ?)
             ORDER BY created_at_ms DESC, seq DESC
             LIMIT ?`,
          )
          .bind(cursor.createdAtMs, cursor.createdAtMs, cursor.seq, limit + 1)
          .all<D1Row>()

        rows = result.results ?? []
      } else {
        const result = await d1
          .prepare(
            `SELECT seq, id, created_at_ms, record FROM ${table}
             ORDER BY created_at_ms DESC, seq DESC
             LIMIT ?`,
          )
          .bind(limit + 1)
          .all<D1Row>()

        rows = result.results ?? []
      }

      if (rows.length <= limit) {
        return {
          items: rows.map((row) => snapshotStored(rowToStored<T>(row))),
          nextCursor: null,
        }
      }

      const kept = rows.slice(0, limit)
      const last = kept[kept.length - 1]!

      return {
        items: kept.map((row) => snapshotStored(rowToStored<T>(row))),
        nextCursor: encodeCursor({ createdAtMs: last.created_at_ms, seq: last.seq }),
      }
    },
  }
}