import { MigrateDownArgs, MigrateUpArgs, sql } from '@payloadcms/db-postgres'

const buildToRichTextSQL = (columnName: string) => `
  CASE
    WHEN "${columnName}" IS NULL OR btrim("${columnName}") = '' THEN NULL
    ELSE jsonb_build_object(
      'root',
      jsonb_build_object(
        'type', 'root',
        'format', '',
        'indent', 0,
        'version', 1,
        'direction', 'ltr',
        'children',
        jsonb_build_array(
          jsonb_build_object(
            'type', 'paragraph',
            'format', '',
            'indent', 0,
            'version', 1,
            'direction', 'ltr',
            'textFormat', 0,
            'textStyle', '',
            'children',
            jsonb_build_array(
              jsonb_build_object(
                'type', 'text',
                'text', "${columnName}",
                'detail', 0,
                'format', 0,
                'mode', 'normal',
                'style', '',
                'version', 1
              )
            )
          )
        )
      )
    )
  END
`

const buildToTextSQL = (columnName: string) => `
  CASE
    WHEN jsonb_typeof("${columnName}") = 'object' THEN COALESCE("${columnName}"#>>'{root,children,0,children,0,text}', '')
    ELSE "${columnName}"::text
  END
`

async function migrateColumnIfNeeded(
  db: any,
  tableName: string,
  columnName: string,
  toJsonb: boolean
): Promise<void> {
  try {
    const result = (await db.execute(sql`
      SELECT udt_name FROM information_schema.columns WHERE table_name = ${tableName} AND column_name = ${columnName}
    `)) as any

    if (result.rows.length === 0) return // Column doesn't exist

    const currentType = result.rows[0].udt_name
    const targetType = toJsonb ? 'jsonb' : 'varchar'

    // Skip if already the target type
    if (currentType === targetType) return

    // Drop default first to avoid casting error in Postgres due to incompatible defaults
    try {
      await db.execute(sql.raw(`ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" DROP DEFAULT`))
    } catch (_) {
      // Ignore if dropping default fails
    }

    const alterSql = toJsonb
      ? `ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" SET DATA TYPE jsonb USING ${buildToRichTextSQL(
          columnName
        )}`
      : `ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" SET DATA TYPE varchar USING ${buildToTextSQL(
          columnName
        )}`

    await db.execute(sql.raw(alterSql))
  } catch (e) {
    // Silently skip if table doesn't exist
    if ((e as any)?.message?.includes('does not exist')) {
      return
    }
    throw e
  }
}

export async function up({ db }: MigrateUpArgs): Promise<void> {
  // Convert all heading fields from text to jsonb
  const conversions = [
    ['pages_blocks_hero_offer_v1', 'headline'],
    ['pages_blocks_hero_offer_v1', 'subheadline'],
    ['_pages_v_blocks_hero_offer_v1', 'headline'],
    ['_pages_v_blocks_hero_offer_v1', 'subheadline'],
    ['pages_blocks_content_list_v1_items', 'eyebrow'],
    ['pages_blocks_content_list_v1_items', 'title'],
    ['pages_blocks_content_list_v1_items', 'subline'],
    ['_pages_v_blocks_content_list_v1_items', 'eyebrow'],
    ['_pages_v_blocks_content_list_v1_items', 'title'],
    ['_pages_v_blocks_content_list_v1_items', 'subline'],
    ['pages_blocks_cta_banner_v1', 'headline'],
    ['_pages_v_blocks_cta_banner_v1', 'headline'],
    ['pages_blocks_lead_form_v1', 'heading'],
    ['_pages_v_blocks_lead_form_v1', 'heading'],
    ['pages_blocks_testimonial', 'heading'],
    ['_pages_v_blocks_testimonial', 'heading'],
  ]

  for (const [tableName, columnName] of conversions) {
    await migrateColumnIfNeeded(db, tableName, columnName, true)
  }
}


export async function down({ db }: MigrateDownArgs): Promise<void> {
  // Convert all heading fields from jsonb back to text
  const conversions = [
    ['pages_blocks_hero_offer_v1', 'headline'],
    ['pages_blocks_hero_offer_v1', 'subheadline'],
    ['_pages_v_blocks_hero_offer_v1', 'headline'],
    ['_pages_v_blocks_hero_offer_v1', 'subheadline'],
    ['pages_blocks_content_list_v1_items', 'eyebrow'],
    ['pages_blocks_content_list_v1_items', 'title'],
    ['pages_blocks_content_list_v1_items', 'subline'],
    ['_pages_v_blocks_content_list_v1_items', 'eyebrow'],
    ['_pages_v_blocks_content_list_v1_items', 'title'],
    ['_pages_v_blocks_content_list_v1_items', 'subline'],
    ['pages_blocks_cta_banner_v1', 'headline'],
    ['_pages_v_blocks_cta_banner_v1', 'headline'],
    ['pages_blocks_lead_form_v1', 'heading'],
    ['_pages_v_blocks_lead_form_v1', 'heading'],
    ['pages_blocks_testimonial', 'heading'],
    ['_pages_v_blocks_testimonial', 'heading'],
  ]

  for (const [tableName, columnName] of conversions) {
    await migrateColumnIfNeeded(db, tableName, columnName, false)
  }
}