import type {
  ContentDefinitionKind,
  ContentDefinitionOrigin,
  ContentEntryCompletionRow,
  ContentMigrationAdapter,
  ContentSchemaValue,
  FlatContentEntry,
} from '../schema.js'

export const CONTENT_MODEL_MIGRATION_ID = 'content-model-v1' as const

export interface ContentMaterializedDefinition {
  readonly kind: ContentDefinitionKind
  readonly key: string
  readonly origin: ContentDefinitionOrigin
  readonly revision: 1
  readonly canonicalHash: string
  readonly definition: ContentSchemaValue
}

export interface ContentMigrationCorpusCheckpoint {
  readonly count: number
  readonly hash: string
  readonly lastKey: string | null
}

export interface ContentMigrationEntryProjection extends FlatContentEntry, ContentEntryCompletionRow {}

export interface ContentModelMigrationPlan {
  readonly migrationId: typeof CONTENT_MODEL_MIGRATION_ID
  readonly source: ContentMigrationCorpusCheckpoint
  readonly target: ContentMigrationCorpusCheckpoint
  readonly definitions: readonly ContentMaterializedDefinition[]
  readonly entries: readonly ContentMigrationEntryProjection[]
}

const STANDARD_STATUSES = new Set(['draft', 'pending', 'scheduled', 'published', 'trashed'])

function canonicalJson(value: unknown): string {
  if (value === null) return 'null'
  if (value instanceof Date) return JSON.stringify(value.toISOString())
  if (typeof value === 'string' || typeof value === 'boolean') return JSON.stringify(value)
  if (typeof value === 'number') {
    if (!Number.isFinite(value)) throw new TypeError('canonical migration data cannot contain non-finite numbers')
    return JSON.stringify(value)
  }
  if (Array.isArray(value)) return `[${value.map(canonicalJson).join(',')}]`
  if (typeof value === 'object' && value !== null) {
    const record = value as Record<string, unknown>
    return `{${Object.keys(record)
      .filter((key) => record[key] !== undefined)
      .sort()
      .map((key) => `${JSON.stringify(key)}:${canonicalJson(record[key])}`)
      .join(',')}}`
  }
  throw new TypeError(`unsupported canonical migration value: ${typeof value}`)
}

export async function canonicalContentMigrationHash(value: unknown): Promise<string> {
  const bytes = new TextEncoder().encode(canonicalJson(value))
  const digest = await crypto.subtle.digest('SHA-256', bytes)
  return Array.from(new Uint8Array(digest), (byte) => byte.toString(16).padStart(2, '0')).join('')
}

function plainEntry(entry: FlatContentEntry): Record<string, unknown> {
  return {
    id: entry.id,
    slug: entry.slug,
    type: entry.type,
    title: entry.title,
    body: entry.body,
    status: entry.status,
    visibility: entry.visibility,
    publishedAt: entry.publishedAt?.toISOString() ?? null,
    author: entry.author,
    createdAt: entry.createdAt.toISOString(),
    updatedAt: entry.updatedAt.toISOString(),
  }
}

export async function snapshotFlatContentCorpus(
  entries: readonly FlatContentEntry[],
): Promise<ContentMigrationCorpusCheckpoint> {
  const sorted = [...entries].sort((left, right) => left.id.localeCompare(right.id))
  return Object.freeze({
    count: sorted.length,
    hash: await canonicalContentMigrationHash(sorted.map(plainEntry)),
    lastKey: sorted.at(-1)?.id ?? null,
  })
}

function humanizeKey(key: string): string {
  const label = key.replaceAll(/[-_]+/g, ' ').trim()
  if (!label) return key
  return label.replace(/\b\p{L}/gu, (letter) => letter.toUpperCase())
}

function legacyLabels(key: string): Record<string, string> {
  const singular = humanizeKey(key)
  const plural = singular.endsWith('s') ? singular : `${singular}s`
  return {
    name: plural,
    singularName: singular,
    menuName: plural,
    nameAdminBar: singular,
    addNew: 'Add New',
    addNewItem: `Add New ${singular}`,
    editItem: `Edit ${singular}`,
    newItem: `New ${singular}`,
    viewItem: `View ${singular}`,
    viewItems: `View ${plural}`,
    searchItems: `Search ${plural}`,
    notFound: `No ${plural.toLowerCase()} found`,
    notFoundInTrash: `No ${plural.toLowerCase()} found in trash`,
    parentItemColon: `Parent ${singular}:`,
    allItems: `All ${plural}`,
    archives: `${singular} Archives`,
    attributes: `${singular} Attributes`,
    insertIntoItem: `Insert into ${singular.toLowerCase()}`,
    uploadedToThisItem: `Uploaded to this ${singular.toLowerCase()}`,
    featuredImage: 'Featured image',
    setFeaturedImage: 'Set featured image',
    removeFeaturedImage: 'Remove featured image',
    useFeaturedImage: 'Use as featured image',
    filterItemsList: `Filter ${plural.toLowerCase()} list`,
    filterByDate: `Filter ${plural.toLowerCase()} by date`,
    itemsListNavigation: `${plural} list navigation`,
    itemsList: `${plural} list`,
    itemPublished: `${singular} published`,
    itemPublishedPrivately: `${singular} published privately`,
    itemRevertedToDraft: `${singular} reverted to draft`,
    itemScheduled: `${singular} scheduled`,
    itemUpdated: `${singular} updated`,
    itemLink: `${singular} link`,
    itemLinkDescription: `A link to a ${singular.toLowerCase()}`,
  }
}

function legacyCapabilities(key: string): Record<string, string> {
  const prefix = `content.${key}`
  return {
    manageType: `${prefix}.manageType`,
    migrateAll: `${prefix}.migrateAll`,
    manageTerms: `${prefix}.manageTerms`,
    create: `${prefix}.create`,
    read: `${prefix}.read`,
    readPrivate: `${prefix}.readPrivate`,
    readProtected: `${prefix}.readProtected`,
    editOwn: `${prefix}.editOwn`,
    editOthers: `${prefix}.editOthers`,
    editPrivate: `${prefix}.editPrivate`,
    editPublished: `${prefix}.editPublished`,
    publish: `${prefix}.publish`,
    deleteOwn: `${prefix}.deleteOwn`,
    deleteOthers: `${prefix}.deleteOthers`,
    deletePrivate: `${prefix}.deletePrivate`,
    deletePublished: `${prefix}.deletePublished`,
  }
}

function legacyTypeDefinition(
  key: string,
  statusKeys: readonly string[],
  defaultTemplateKey: string,
): ContentSchemaValue {
  return {
    key,
    labels: legacyLabels(key),
    public: true,
    hierarchical: false,
    excludeFromSearch: false,
    publiclyQueryable: true,
    showUi: true,
    showInMenu: true,
    showInNavMenus: true,
    showInAdminBar: true,
    capabilities: legacyCapabilities(key),
    supports: ['title', 'editor', 'author', 'revisions'],
    taxonomies: [],
    hasArchive: false,
    rewrite: false,
    queryVariable: false,
    canExport: true,
    deleteWithAuthor: null,
    rest: false,
    defaultTemplateKey,
    statusKeys,
    active: true,
  }
}

function legacyStatusDefinition(key: string): ContentSchemaValue {
  const published = key === 'published'
  const scheduled = key === 'scheduled'
  return {
    key,
    label: humanizeKey(key),
    published,
    internal: key === 'trashed',
    excludeFromSearch: !published,
    publiclyQueryable: published,
    showInAdminAll: true,
    showInAdminStatusFilter: true,
    dateLabel: published ? 'published' : scheduled ? 'scheduled' : key === 'draft' || key === 'pending' ? 'lastModified' : 'created',
    transitionInput: scheduled ? 'scheduleAt' : published ? 'publishedAt' : 'none',
  }
}

async function materializeDefinitions(
  entries: readonly FlatContentEntry[],
  defaultTemplateKey: string,
): Promise<readonly ContentMaterializedDefinition[]> {
  const statusKeys = [...new Set(entries.map((entry) => entry.status))].sort()
  const typeKeys = [...new Set(entries.map((entry) => entry.type))].sort()
  const allStatusKeys = [...new Set([...STANDARD_STATUSES, ...statusKeys])].sort()
  const definitions: ContentMaterializedDefinition[] = []

  for (const key of typeKeys) {
    const definition = legacyTypeDefinition(key, allStatusKeys, defaultTemplateKey)
    definitions.push({
      kind: 'type',
      key,
      origin: 'db',
      revision: 1,
      canonicalHash: await canonicalContentMigrationHash(definition),
      definition,
    })
  }

  for (const key of allStatusKeys) {
    const definition = legacyStatusDefinition(key)
    definitions.push({
      kind: 'status',
      key,
      origin: STANDARD_STATUSES.has(key) ? 'code' : 'db',
      revision: 1,
      canonicalHash: await canonicalContentMigrationHash(definition),
      definition,
    })
  }

  return Object.freeze(definitions.sort((left, right) =>
    left.kind.localeCompare(right.kind) || left.key.localeCompare(right.key),
  ))
}

export async function planContentModelMigration(
  entries: readonly FlatContentEntry[],
  options: { readonly defaultTemplateKey: string },
): Promise<ContentModelMigrationPlan> {
  const defaultTemplateKey = options.defaultTemplateKey.trim()
  if (!defaultTemplateKey || defaultTemplateKey.length > 128) {
    throw new TypeError('content migration defaultTemplateKey must be a bounded non-empty key')
  }

  const source = await snapshotFlatContentCorpus(entries)
  const definitions = await materializeDefinitions(entries, defaultTemplateKey)
  const entriesById = [...entries].sort((left, right) => left.id.localeCompare(right.id))
  const projected = Object.freeze(entriesById.map((entry): ContentMigrationEntryProjection => Object.freeze({
    ...entry,
    parentId: null,
    menuOrder: 0,
    templateKey: null,
    excerpt: '',
    featuredMedia: null,
    commentStatus: 'open',
    pingStatus: 'open',
    sticky: false,
    format: null,
    deletedAt: null,
    lastEditedBy: entry.author,
    typeDefinitionRevision: 1,
    statusDefinitionRevision: 1,
  })))
  const target = Object.freeze({
    count: projected.length,
    hash: await canonicalContentMigrationHash(projected.map((entry) => ({
      ...plainEntry(entry),
      parentId: entry.parentId,
      menuOrder: entry.menuOrder,
      templateKey: entry.templateKey,
      excerpt: entry.excerpt,
      featuredMedia: entry.featuredMedia,
      commentStatus: entry.commentStatus,
      pingStatus: entry.pingStatus,
      sticky: entry.sticky,
      format: entry.format,
      deletedAt: entry.deletedAt,
      lastEditedBy: entry.lastEditedBy,
      typeDefinitionRevision: entry.typeDefinitionRevision,
      statusDefinitionRevision: entry.statusDefinitionRevision,
    }))),
    lastKey: projected.at(-1)?.id ?? null,
  })

  return Object.freeze({
    migrationId: CONTENT_MODEL_MIGRATION_ID,
    source,
    target,
    definitions,
    entries: projected,
  })
}

const PG_DEFINITION_TABLE = (table: string) => `
CREATE TABLE IF NOT EXISTS ${table} (
  key text PRIMARY KEY,
  origin text NOT NULL,
  version integer NOT NULL DEFAULT 1 CHECK (version > 0),
  active boolean NOT NULL DEFAULT true,
  current_revision integer NOT NULL DEFAULT 1 CHECK (current_revision > 0),
  canonical_hash text NOT NULL,
  definition jsonb NOT NULL,
  shadowed_db_version integer,
  host_session_id text,
  created_at timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at timestamptz(3) NOT NULL DEFAULT NOW()
)`

const D1_DEFINITION_TABLE = (table: string) => `
CREATE TABLE IF NOT EXISTS ${table} (
  key TEXT PRIMARY KEY,
  origin TEXT NOT NULL,
  version INTEGER NOT NULL DEFAULT 1 CHECK (version > 0),
  active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  current_revision INTEGER NOT NULL DEFAULT 1 CHECK (current_revision > 0),
  canonical_hash TEXT NOT NULL,
  definition TEXT NOT NULL,
  shadowed_db_version INTEGER,
  host_session_id TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)`

function postgresForwardSql(): readonly string[] {
  return Object.freeze([
    PG_DEFINITION_TABLE('content_type_definitions'),
    PG_DEFINITION_TABLE('content_status_definitions'),
    `CREATE TABLE IF NOT EXISTS content_definition_versions (
      definition_kind text NOT NULL,
      definition_key text NOT NULL,
      revision integer NOT NULL CHECK (revision > 0),
      canonical_hash text NOT NULL,
      definition jsonb NOT NULL,
      origin text NOT NULL,
      host_session_id text,
      created_at timestamptz(3) NOT NULL DEFAULT NOW(),
      PRIMARY KEY (definition_kind, definition_key, revision)
    )`,
    `CREATE TABLE IF NOT EXISTS content_password_credentials (
      entry_id uuid PRIMARY KEY REFERENCES content_entries(id) ON DELETE CASCADE,
      credential_version text NOT NULL,
      credential text NOT NULL,
      created_at timestamptz(3) NOT NULL DEFAULT NOW(),
      updated_at timestamptz(3) NOT NULL DEFAULT NOW()
    )`,
    `CREATE TABLE IF NOT EXISTS content_lifecycle_journal (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(), operation_id text NOT NULL UNIQUE, kind text NOT NULL,
      entry_id uuid, definition_kind text, definition_key text, definition_revision integer, state text NOT NULL,
      payload jsonb, created_at timestamptz(3) NOT NULL DEFAULT NOW(), updated_at timestamptz(3) NOT NULL DEFAULT NOW()
    )`,
    `CREATE TABLE IF NOT EXISTS content_import_journal (
      import_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), host_session_id text NOT NULL UNIQUE,
      manifest_hash text NOT NULL, plan_hash text NOT NULL, state text NOT NULL, version integer NOT NULL DEFAULT 0 CHECK (version >= 0),
      next_section text, next_offset integer, high_water_mark text, payload jsonb,
      created_at timestamptz(3) NOT NULL DEFAULT NOW(), updated_at timestamptz(3) NOT NULL DEFAULT NOW()
    )`,
    `CREATE TABLE IF NOT EXISTS content_tombstones (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(), entry_id uuid NOT NULL, operation_id text NOT NULL,
      type_key text NOT NULL, type_definition_revision integer NOT NULL, status_key text NOT NULL,
      status_definition_revision integer NOT NULL, snapshot jsonb NOT NULL,
      deleted_at timestamptz(3) NOT NULL DEFAULT NOW(), UNIQUE(operation_id, entry_id)
    )`,
    `CREATE TABLE IF NOT EXISTS content_migration_checkpoints (
      migration_id text NOT NULL, adapter text NOT NULL, phase text NOT NULL, batch_ordinal integer NOT NULL DEFAULT 0,
      version integer NOT NULL DEFAULT 0, last_key text, source_count integer NOT NULL, target_count integer,
      source_hash text NOT NULL, target_hash text, high_water_mark text,
      created_at timestamptz(3) NOT NULL DEFAULT NOW(), updated_at timestamptz(3) NOT NULL DEFAULT NOW(),
      PRIMARY KEY (migration_id, adapter, phase, batch_ordinal)
    )`,
    `CREATE TABLE IF NOT EXISTS content_migration_write_journal (
      seq bigserial PRIMARY KEY, migration_id text NOT NULL, operation text NOT NULL, entry_id text NOT NULL,
      before_value jsonb, after_value jsonb, created_at timestamptz(3) NOT NULL DEFAULT NOW()
    )`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS parent_id uuid`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS menu_order integer NOT NULL DEFAULT 0`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS template_key text`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS excerpt text NOT NULL DEFAULT ''`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS featured_media jsonb`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS comment_status text NOT NULL DEFAULT 'open' CHECK (comment_status IN ('open','closed'))`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS ping_status text NOT NULL DEFAULT 'open' CHECK (ping_status IN ('open','closed'))`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS sticky boolean NOT NULL DEFAULT false`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS format text`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS deleted_at timestamptz(3)`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS last_edited_by text`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS type_definition_revision integer NOT NULL DEFAULT 1`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS status_definition_revision integer NOT NULL DEFAULT 1`,
    `ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS host_session_id text`,
    `UPDATE content_entries SET last_edited_by = author WHERE last_edited_by IS NULL`,
    `ALTER TABLE content_entries ALTER COLUMN last_edited_by SET NOT NULL`,
    `CREATE INDEX IF NOT EXISTS content_entries_parent_order_idx ON content_entries (type, parent_id, menu_order, id)`,
    `CREATE INDEX IF NOT EXISTS content_entries_deleted_idx ON content_entries (deleted_at)`,
    `CREATE INDEX IF NOT EXISTS content_entries_type_definition_idx ON content_entries (type, type_definition_revision)`,
    `CREATE INDEX IF NOT EXISTS content_entries_status_definition_idx ON content_entries (status, status_definition_revision)`,
    `CREATE OR REPLACE FUNCTION content_model_migration_journal_fn() RETURNS trigger AS $$
      BEGIN
        INSERT INTO content_migration_write_journal (migration_id, operation, entry_id, before_value, after_value)
        VALUES (
          '${CONTENT_MODEL_MIGRATION_ID}',
          lower(TG_OP),
          CASE WHEN TG_OP = 'DELETE' THEN OLD.id::text ELSE NEW.id::text END,
          CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
          CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
        );
        RETURN NULL;
      END;
    $$ LANGUAGE plpgsql`,
    `DROP TRIGGER IF EXISTS content_model_migration_write_trigger ON content_entries`,
    `CREATE TRIGGER content_model_migration_write_trigger AFTER INSERT OR UPDATE OR DELETE ON content_entries
      FOR EACH ROW EXECUTE FUNCTION content_model_migration_journal_fn()`,
  ])
}

function d1JsonRow(prefix: 'OLD' | 'NEW'): string {
  return `json_object(
    'id', ${prefix}.id, 'slug', ${prefix}.slug, 'type', ${prefix}.type, 'title', ${prefix}.title,
    'body', ${prefix}.body, 'status', ${prefix}.status, 'visibility', ${prefix}.visibility,
    'published_at', ${prefix}.published_at, 'author', ${prefix}.author,
    'created_at', ${prefix}.created_at, 'updated_at', ${prefix}.updated_at
  )`
}

function d1ForwardSql(): readonly string[] {
  const createdAt = "strftime('%Y-%m-%dT%H:%M:%fZ','now')"
  return Object.freeze([
    D1_DEFINITION_TABLE('content_type_definitions'),
    D1_DEFINITION_TABLE('content_status_definitions'),
    `CREATE TABLE IF NOT EXISTS content_definition_versions (
      definition_kind TEXT NOT NULL, definition_key TEXT NOT NULL, revision INTEGER NOT NULL CHECK (revision > 0),
      canonical_hash TEXT NOT NULL, definition TEXT NOT NULL, origin TEXT NOT NULL, host_session_id TEXT,
      created_at TEXT NOT NULL, PRIMARY KEY (definition_kind, definition_key, revision)
    )`,
    `CREATE TABLE IF NOT EXISTS content_password_credentials (
      entry_id TEXT PRIMARY KEY REFERENCES content_entries(id) ON DELETE CASCADE,
      credential_version TEXT NOT NULL, credential TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )`,
    `CREATE TABLE IF NOT EXISTS content_lifecycle_journal (
      id TEXT PRIMARY KEY, operation_id TEXT NOT NULL UNIQUE, kind TEXT NOT NULL, entry_id TEXT,
      definition_kind TEXT, definition_key TEXT, definition_revision INTEGER, state TEXT NOT NULL,
      payload TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )`,
    `CREATE TABLE IF NOT EXISTS content_import_journal (
      import_id TEXT PRIMARY KEY, host_session_id TEXT NOT NULL UNIQUE, manifest_hash TEXT NOT NULL,
      plan_hash TEXT NOT NULL, state TEXT NOT NULL, version INTEGER NOT NULL DEFAULT 0 CHECK (version >= 0),
      next_section TEXT, next_offset INTEGER, high_water_mark TEXT, payload TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )`,
    `CREATE TABLE IF NOT EXISTS content_tombstones (
      id TEXT PRIMARY KEY, entry_id TEXT NOT NULL, operation_id TEXT NOT NULL, type_key TEXT NOT NULL,
      type_definition_revision INTEGER NOT NULL, status_key TEXT NOT NULL, status_definition_revision INTEGER NOT NULL,
      snapshot TEXT NOT NULL, deleted_at TEXT NOT NULL, UNIQUE(operation_id, entry_id)
    )`,
    `CREATE TABLE IF NOT EXISTS content_migration_checkpoints (
      migration_id TEXT NOT NULL, adapter TEXT NOT NULL, phase TEXT NOT NULL, batch_ordinal INTEGER NOT NULL DEFAULT 0,
      version INTEGER NOT NULL DEFAULT 0, last_key TEXT, source_count INTEGER NOT NULL, target_count INTEGER,
      source_hash TEXT NOT NULL, target_hash TEXT, high_water_mark TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
      PRIMARY KEY (migration_id, adapter, phase, batch_ordinal)
    )`,
    `CREATE TABLE IF NOT EXISTS content_migration_write_journal (
      seq INTEGER PRIMARY KEY AUTOINCREMENT, migration_id TEXT NOT NULL, operation TEXT NOT NULL, entry_id TEXT NOT NULL,
      before_value TEXT, after_value TEXT, created_at TEXT NOT NULL DEFAULT (${createdAt})
    )`,
    `ALTER TABLE content_entries ADD COLUMN parent_id TEXT`,
    `ALTER TABLE content_entries ADD COLUMN menu_order INTEGER NOT NULL DEFAULT 0`,
    `ALTER TABLE content_entries ADD COLUMN template_key TEXT`,
    `ALTER TABLE content_entries ADD COLUMN excerpt TEXT NOT NULL DEFAULT ''`,
    `ALTER TABLE content_entries ADD COLUMN featured_media TEXT`,
    `ALTER TABLE content_entries ADD COLUMN comment_status TEXT NOT NULL DEFAULT 'open' CHECK (comment_status IN ('open','closed'))`,
    `ALTER TABLE content_entries ADD COLUMN ping_status TEXT NOT NULL DEFAULT 'open' CHECK (ping_status IN ('open','closed'))`,
    `ALTER TABLE content_entries ADD COLUMN sticky INTEGER NOT NULL DEFAULT 0 CHECK (sticky IN (0,1))`,
    `ALTER TABLE content_entries ADD COLUMN format TEXT`,
    `ALTER TABLE content_entries ADD COLUMN deleted_at TEXT`,
    `ALTER TABLE content_entries ADD COLUMN last_edited_by TEXT`,
    `ALTER TABLE content_entries ADD COLUMN type_definition_revision INTEGER NOT NULL DEFAULT 1`,
    `ALTER TABLE content_entries ADD COLUMN status_definition_revision INTEGER NOT NULL DEFAULT 1`,
    `ALTER TABLE content_entries ADD COLUMN host_session_id TEXT`,
    `UPDATE content_entries SET last_edited_by = author WHERE last_edited_by IS NULL`,
    `CREATE INDEX IF NOT EXISTS content_entries_parent_order_idx ON content_entries (type, parent_id, menu_order, id)`,
    `CREATE INDEX IF NOT EXISTS content_entries_deleted_idx ON content_entries (deleted_at)`,
    `CREATE INDEX IF NOT EXISTS content_entries_type_definition_idx ON content_entries (type, type_definition_revision)`,
    `CREATE INDEX IF NOT EXISTS content_entries_status_definition_idx ON content_entries (status, status_definition_revision)`,
    `DROP TRIGGER IF EXISTS content_model_migration_write_insert`,
    `DROP TRIGGER IF EXISTS content_model_migration_write_update`,
    `DROP TRIGGER IF EXISTS content_model_migration_write_delete`,
    `CREATE TRIGGER content_model_migration_write_insert AFTER INSERT ON content_entries BEGIN
      INSERT INTO content_migration_write_journal (migration_id, operation, entry_id, before_value, after_value)
      VALUES ('${CONTENT_MODEL_MIGRATION_ID}', 'insert', NEW.id, NULL, ${d1JsonRow('NEW')});
    END`,
    `CREATE TRIGGER content_model_migration_write_update AFTER UPDATE ON content_entries BEGIN
      INSERT INTO content_migration_write_journal (migration_id, operation, entry_id, before_value, after_value)
      VALUES ('${CONTENT_MODEL_MIGRATION_ID}', 'update', NEW.id, ${d1JsonRow('OLD')}, ${d1JsonRow('NEW')});
    END`,
    `CREATE TRIGGER content_model_migration_write_delete AFTER DELETE ON content_entries BEGIN
      INSERT INTO content_migration_write_journal (migration_id, operation, entry_id, before_value, after_value)
      VALUES ('${CONTENT_MODEL_MIGRATION_ID}', 'delete', OLD.id, ${d1JsonRow('OLD')}, NULL);
    END`,
  ])
}

export function contentModelForwardMigrationSql(adapter: ContentMigrationAdapter): readonly string[] {
  return adapter === 'postgres' ? postgresForwardSql() : d1ForwardSql()
}

export function contentModelReverseMigrationSql(adapter: ContentMigrationAdapter): readonly string[] {
  const triggerSql = adapter === 'postgres'
    ? [
        `DROP TRIGGER IF EXISTS content_model_migration_write_trigger ON content_entries`,
        `DROP FUNCTION IF EXISTS content_model_migration_journal_fn()`,
      ]
    : [
        `DROP TRIGGER IF EXISTS content_model_migration_write_insert`,
        `DROP TRIGGER IF EXISTS content_model_migration_write_update`,
        `DROP TRIGGER IF EXISTS content_model_migration_write_delete`,
      ]
  return Object.freeze([
    ...triggerSql,
    `DROP INDEX IF EXISTS content_entries_parent_order_idx`,
    `DROP INDEX IF EXISTS content_entries_deleted_idx`,
    `DROP INDEX IF EXISTS content_entries_type_definition_idx`,
    `DROP INDEX IF EXISTS content_entries_status_definition_idx`,
    `ALTER TABLE content_entries DROP COLUMN host_session_id`,
    `ALTER TABLE content_entries DROP COLUMN status_definition_revision`,
    `ALTER TABLE content_entries DROP COLUMN type_definition_revision`,
    `ALTER TABLE content_entries DROP COLUMN last_edited_by`,
    `ALTER TABLE content_entries DROP COLUMN deleted_at`,
    `ALTER TABLE content_entries DROP COLUMN format`,
    `ALTER TABLE content_entries DROP COLUMN sticky`,
    `ALTER TABLE content_entries DROP COLUMN ping_status`,
    `ALTER TABLE content_entries DROP COLUMN comment_status`,
    `ALTER TABLE content_entries DROP COLUMN featured_media`,
    `ALTER TABLE content_entries DROP COLUMN excerpt`,
    `ALTER TABLE content_entries DROP COLUMN template_key`,
    `ALTER TABLE content_entries DROP COLUMN menu_order`,
    `ALTER TABLE content_entries DROP COLUMN parent_id`,
  ])
}