import { expect, Page } from '@playwright/test'
import { releaseFileOnceCleanup, withFileOnceSetup } from '../utils/once-per-file.js'
import { deleteTable } from '../utils/table-helpers.js'
import { test } from '../utils/test.js'
import { toUrl } from '../utils/to-url.js'
import { waitForTableToLoad } from '../utils/wait-for-response.js'

const QUEUE_OPERATIONS_KEY = 'supabase-ui-queue-operations'
const tableNamePrefix = 'pw_queue_table'

const enableQueueOperations = async (page: Page) => {
  await page.evaluate((key) => {
    localStorage.setItem(key, 'true')
  }, QUEUE_OPERATIONS_KEY)
}

const runSQL = async (page: Page, ref: string, sql: string) => {
  await page.goto(toUrl(`/project/${ref}/sql/new`))
  await expect(page.getByText('Loading...')).not.toBeVisible({ timeout: 10000 })

  await page.locator('.view-lines').click()
  await page.keyboard.press('ControlOrMeta+KeyA')
  await page.evaluate((text) => navigator.clipboard.writeText(text), sql)
  await page.keyboard.press('ControlOrMeta+KeyV')

  await page.getByTestId('sql-run-button').click()

  const confirmButton = page.getByRole('button', { name: 'Run this query' })
  if (await confirmButton.isVisible({ timeout: 1000 }).catch(() => false)) {
    await confirmButton.click()
  }

  await expect(
    page.getByText('Success. No rows returned').or(page.getByText(/^\d+ rows?$/)).first()
  ).toBeVisible({ timeout: 10000 })
}

const createTableSQL = async (
  page: Page,
  ref: string,
  tableName: string,
  columnName: string,
  initialData?: { [key: string]: string }[]
) => {
  let sql = `CREATE TABLE IF NOT EXISTS ${tableName} (
    id bigint generated by default as identity primary key,
    ${columnName} text
  );`

  if (initialData && initialData.length > 0) {
    const values = initialData
      .map((row) => `('${row[columnName].replace(/'/g, "''")}')`)
      .join(', ')
    sql += `\nINSERT INTO ${tableName} (${columnName}) VALUES ${values};`
  }

  await runSQL(page, ref, sql)
}

const dropTableSQL = async (page: Page, ref: string, tableName: string) => {
  await runSQL(page, ref, `DROP TABLE IF EXISTS ${tableName} CASCADE;`)
}

test.describe('Queue Table Operations', () => {
  test.beforeAll(async ({ browser, ref }) => {
    await withFileOnceSetup(import.meta.url, async () => {
      const ctx = await browser.newContext()
      const page = await ctx.newPage()

      const loadPromise = waitForTableToLoad(page, ref)
      await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
      await loadPromise

      const viewButtons = page.getByRole('button', { name: /^View / })
      const names = await Promise.all(
        (await viewButtons.all()).map(async (btn) => {
          const ariaLabel = await btn.getAttribute('aria-label')
          const name = ariaLabel ? ariaLabel.replace(/^View\s+/, '').trim() : ''
          return name
        })
      )
      const tablesToDelete = names.filter((tableName) => tableName.startsWith(tableNamePrefix))

      for (const tableName of tablesToDelete) {
        await deleteTable(page, ref, tableName)
        await expect
          .poll(async () => {
            return await page.getByLabel(`View ${tableName}`, { exact: true }).count()
          })
          .toBe(0)
      }

      await ctx.close()
    })
  })

  test.beforeEach(async ({ page, ref }) => {
    const loadPromise = waitForTableToLoad(page, ref)
    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await loadPromise
    await enableQueueOperations(page)
    await page.reload({ waitUntil: 'networkidle' })
  })

  test.afterAll(async () => {
    await releaseFileOnceCleanup(import.meta.url)
  })

  test('cell edits are queued and can be saved', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_cell_edit`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName, [{ name: 'original value' }])

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await expect(page.getByRole('gridcell', { name: 'original value' })).toBeVisible()

    const cell = page.getByRole('gridcell', { name: 'original value' })
    await cell.dblclick()

    const editor = page.getByRole('textbox', { name: /Editor content/ })
    await expect(editor).toBeVisible()
    await editor.fill('edited value')
    await page.keyboard.press('Enter')

    await expect(page.getByText('1 pending change')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()

    const sidePanel = page.getByRole('dialog')
    await expect(sidePanel.getByText('Pending changes')).toBeVisible()
    await expect(sidePanel.getByText('Cell Edits (1)')).toBeVisible()
    await expect(sidePanel.getByTitle('original value')).toBeVisible()
    await expect(sidePanel.getByTitle('edited value')).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'edited value' })).toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'original value' })).not.toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('cell edits can be cancelled', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_cell_cancel`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName, [{ name: 'keep this value' }])

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await expect(page.getByRole('gridcell', { name: 'keep this value' })).toBeVisible()

    const cell = page.getByRole('gridcell', { name: 'keep this value' })
    await cell.dblclick()
    const editor = page.getByRole('textbox', { name: /Editor content/ })
    await editor.fill('should be cancelled')
    await page.keyboard.press('Enter')

    await expect(page.getByText('1 pending change')).toBeVisible()
    await page.getByRole('button', { name: 'View Details' }).click()

    await page.getByRole('button', { name: 'Cancel All' }).click()

    await expect(page.getByRole('gridcell', { name: 'keep this value' })).toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'should be cancelled' })).not.toBeVisible()

    await expect(page.getByText('pending change')).not.toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('row inserts are queued and can be saved', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_row_insert`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName)

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('new row value')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('1 pending change')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'new row value' })).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()

    const sidePanel = page.getByRole('dialog')
    await expect(sidePanel.getByText('Pending changes')).toBeVisible()
    await expect(sidePanel.getByText('Rows to Add (1)')).toBeVisible()
    await expect(sidePanel.getByText('New row', { exact: true })).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'new row value' })).toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('multiple operations can be batched and saved together', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_batch_ops`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName)

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('row one')
    await page.getByTestId('action-bar-save-row').click()

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('row two')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('2 pending changes')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()

    const sidePanel = page.getByRole('dialog')
    await expect(sidePanel.getByText('2 operations')).toBeVisible()
    await expect(sidePanel.getByText('Rows to Add (2)')).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'row one' })).toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'row two' })).toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('individual operations can be removed from the queue', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_remove_op`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName)

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('keep this row')
    await page.getByTestId('action-bar-save-row').click()

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('remove this row')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('2 pending changes')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()

    const sidePanel = page.getByRole('dialog')
    const removeButtons = sidePanel.getByRole('button', { name: 'Remove operation' })
    await removeButtons.last().click()

    await expect(sidePanel.getByText('1 operation')).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'keep this row' })).toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'remove this row' })).not.toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('keyboard shortcuts work for queue operations', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_shortcuts`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName)

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('shortcut test')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('1 pending change')).toBeVisible()

    await page.keyboard.press('ControlOrMeta+.')
    await expect(page.getByRole('dialog').getByText('Pending changes')).toBeVisible()

    await page.keyboard.press('ControlOrMeta+.')
    await expect(page.getByRole('button', { name: 'View Details' })).toBeVisible()

    await page.keyboard.press('ControlOrMeta+s')
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'shortcut test' })).toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('row deletes via context menu are queued', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_row_delete`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName, [{ name: 'row to delete' }])

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await expect(page.getByRole('gridcell', { name: 'row to delete' })).toBeVisible()

    const cell = page.getByRole('gridcell', { name: 'row to delete' })
    await cell.click({ button: 'right' })

    await page.getByRole('menuitem', { name: 'Delete row' }).click()

    await expect(page.getByText('1 pending change')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()

    const sidePanel = page.getByRole('dialog')
    await expect(sidePanel.getByText('Pending changes')).toBeVisible()
    await expect(sidePanel.getByText('Rows to Delete (1)')).toBeVisible()
    await expect(sidePanel.getByText('Delete row', { exact: true })).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'row to delete' })).not.toBeVisible()
    await expect(page.getByText('0 records')).toBeVisible()

    await deleteTable(page, ref, tableName)
  })

  test('row deletes can be cancelled', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_delete_cancel`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName, [{ name: 'should not be deleted' }])

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    const cell = page.getByRole('gridcell', { name: 'should not be deleted' })
    await cell.click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Delete row' }).click()

    await expect(page.getByText('1 pending change')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()
    await page.getByRole('button', { name: 'Cancel All' }).click()

    await expect(page.getByRole('gridcell', { name: 'should not be deleted' })).toBeVisible()
    await expect(page.getByText('pending change')).not.toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('mixed operations (add, edit, delete) can be batched', async ({ page, ref }) => {
    const tableName = `${tableNamePrefix}_mixed_ops`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName, columnName, [
      { name: 'row to edit' },
      { name: 'row to delete' },
    ])

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    const cellToEdit = page.getByRole('gridcell', { name: 'row to edit' })
    await cellToEdit.dblclick()
    const editor = page.getByRole('textbox', { name: /Editor content/ })
    await editor.fill('edited row')
    await page.keyboard.press('Enter')

    const cellToDelete = page.getByRole('gridcell', { name: 'row to delete' })
    await cellToDelete.click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Delete row' }).click()

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('new row')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('3 pending changes')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()
    const sidePanel = page.getByRole('dialog')
    await expect(sidePanel.getByText('3 operations')).toBeVisible()
    await expect(sidePanel.getByText('Rows to Delete (1)')).toBeVisible()
    await expect(sidePanel.getByText('Rows to Add (1)')).toBeVisible()
    await expect(sidePanel.getByText('Cell Edits (1)')).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await expect(page.getByRole('gridcell', { name: 'edited row' })).toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'new row' })).toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'row to delete' })).not.toBeVisible()
    await expect(page.getByRole('gridcell', { name: 'row to edit' })).not.toBeVisible()

    await dropTableSQL(page, ref, tableName)
  })

  test('pending changes persist when switching between tables', async ({ page, ref }) => {
    const tableName1 = `${tableNamePrefix}_persist1`
    const tableName2 = `${tableNamePrefix}_persist2`
    const columnName = 'name'

    await createTableSQL(page, ref, tableName1, columnName)
    await createTableSQL(page, ref, tableName2, columnName)

    await page.goto(toUrl(`/project/${ref}/editor?schema=public`))
    await enableQueueOperations(page)
    await page.reload()
    await waitForTableToLoad(page, ref)

    await page.getByRole('button', { name: `View ${tableName1}`, exact: true }).click()
    await page.waitForURL(/\/editor\/\d+\?schema=public$/)

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('pending in table 1')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('1 pending change')).toBeVisible()

    await page.getByRole('button', { name: `View ${tableName2}`, exact: true }).click()

    await page.getByTestId('table-editor-insert-new-row').click()
    await page.getByRole('menuitem', { name: 'Insert row Insert a new row' }).click()
    await page.getByTestId(`${columnName}-input`).fill('pending in table 2')
    await page.getByTestId('action-bar-save-row').click()

    await expect(page.getByText('2 pending changes')).toBeVisible()

    await page.getByRole('button', { name: 'View Details' }).click()
    const sidePanel = page.getByRole('dialog')
    await expect(sidePanel.getByText('2 operations')).toBeVisible()

    await sidePanel.getByRole('button', { name: /Save All/ }).click()
    await expect(page.getByText('Changes saved successfully')).toBeVisible()

    await page.getByRole('button', { name: `View ${tableName1}`, exact: true }).click()
    await expect(page.getByRole('gridcell', { name: 'pending in table 1' })).toBeVisible()

    await page.getByRole('button', { name: `View ${tableName2}`, exact: true }).click()
    await expect(page.getByRole('gridcell', { name: 'pending in table 2' })).toBeVisible()

    await dropTableSQL(page, ref, tableName1)
    await dropTableSQL(page, ref, tableName2)
  })
})