import { afterAll, beforeAll, beforeEach, describe, expect, it } from 'vitest'
import { and, eq, sql } from 'drizzle-orm'
import type { TransactionalDatabase } from '@platform-modules/db'
import { InvalidFieldKeyError, UnknownLocaleError } from './errors.js'
import { upsertLanguage } from './languages.js'
import { pushSchema } from './migrate.js'
import { makePgHarness } from './pg-harness.js'
import { translationValue, type I18nContentSchema } from './schema.js'
import { markFailed, markStale, setTranslation } from './store.js'

const h = await makePgHarness()
const db = h.db as unknown as TransactionalDatabase<I18nContentSchema>

beforeAll(async () => {
  await pushSchema(h.db)
})

beforeEach(async () => {
  await db.execute(sql`DELETE FROM translation_value`)
  await db.execute(sql`DELETE FROM languages`)
  await upsertLanguage(db, {
    code: 'en',
    isActive: true,
    isDefault: true,
    sortOrder: 1,
  })
  await upsertLanguage(db, {
    code: 'he',
    isActive: true,
    sortOrder: 2,
  })
})

afterAll(async () => {
  await h.teardown()
})

async function readRow(entityType: string, entityId: string, fieldKey: string, locale: string) {
  const [row] = await db
    .select()
    .from(translationValue)
    .where(
      and(
        eq(translationValue.entityType, entityType),
        eq(translationValue.entityId, entityId),
        eq(translationValue.fieldKey, fieldKey),
        eq(translationValue.locale, locale),
      ),
    )
  return row
}

describe('setTranslation', () => {
  it('machine write with modelId does NOT overwrite existing manualOverride=true row', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Manual title',
      sourceHash: 'hash-v1',
      manualOverride: true,
    })

    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'AI title',
      sourceHash: 'hash-v2',
      modelId: 'gpt-4',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.value).toBe('Manual title')
    expect(row?.sourceHash).toBe('hash-v1')
    expect(row?.manualOverride).toBe(true)
    expect(row?.status).toBe('OK')
  })

  it('plain write (no modelId, no manualOverride) does NOT overwrite existing manualOverride=true row', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Manual title',
      sourceHash: 'hash-v1',
      manualOverride: true,
    })

    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Plain machine title',
      sourceHash: 'hash-v2',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.value).toBe('Manual title')
    expect(row?.sourceHash).toBe('hash-v1')
    expect(row?.manualOverride).toBe(true)
    expect(row?.status).toBe('OK')
  })

  it('manual write overwrites any row including manualOverride rows', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'First manual',
      sourceHash: 'hash-v1',
      manualOverride: true,
    })

    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Second manual',
      sourceHash: 'hash-v2',
      manualOverride: true,
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.value).toBe('Second manual')
    expect(row?.sourceHash).toBe('hash-v2')
    expect(row?.manualOverride).toBe(true)
    expect(row?.status).toBe('OK')
  })

  it('throws UnknownLocaleError for unknown or inactive locale', async () => {
    await expect(
      setTranslation(db, {
        entityType: 'deal',
        entityId: 'd1',
        fieldKey: 'title',
        locale: 'xx',
        value: 'Nope',
      }),
    ).rejects.toBeInstanceOf(UnknownLocaleError)

    await upsertLanguage(db, { code: 'fr', isActive: false, sortOrder: 3 })

    await expect(
      setTranslation(db, {
        entityType: 'deal',
        entityId: 'd1',
        fieldKey: 'title',
        locale: 'fr',
        value: 'Nope',
      }),
    ).rejects.toBeInstanceOf(UnknownLocaleError)
  })

  it('throws InvalidFieldKeyError for invalid fieldKey', async () => {
    await expect(
      setTranslation(db, {
        entityType: 'deal',
        entityId: 'd1',
        fieldKey: 'Bad-Key',
        locale: 'en',
        value: 'Nope',
      }),
    ).rejects.toBeInstanceOf(InvalidFieldKeyError)
  })

  it('throws InvalidFieldKeyError for invalid entityType', async () => {
    await expect(
      setTranslation(db, {
        entityType: 'Bad-Type',
        entityId: 'd1',
        fieldKey: 'title',
        locale: 'en',
        value: 'Nope',
      }),
    ).rejects.toBeInstanceOf(InvalidFieldKeyError)
  })
})

describe('markFailed', () => {
  it('persists FAILED row with empty value and correct provenance', async () => {
    await markFailed(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      sourceHash: 'hash-v1',
      modelId: 'gpt-4',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.value).toBe('')
    expect(row?.status).toBe('FAILED')
    expect(row?.sourceHash).toBe('hash-v1')
    expect(row?.modelId).toBe('gpt-4')
    expect(row?.manualOverride).toBe(false)
  })

  it('does NOT overwrite an existing manualOverride=true row', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Manual title',
      sourceHash: 'hash-v1',
      manualOverride: true,
    })

    await markFailed(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      sourceHash: 'hash-v2',
      modelId: 'gpt-4',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.value).toBe('Manual title')
    expect(row?.status).toBe('OK')
    expect(row?.manualOverride).toBe(true)
  })

  it('does NOT overwrite an existing OK row (non-downgrade guard — transient error must not destroy published content)', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Published title',
      sourceHash: 'hash-v1',
    })

    // Simulates a transient provider error after the translation was already published OK
    await markFailed(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      sourceHash: 'hash-v1',
      modelId: 'gpt-4',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    // The OK row must be preserved — FAILED write is a no-op against a good row
    expect(row?.value).toBe('Published title')
    expect(row?.status).toBe('OK')
    expect(row?.manualOverride).toBe(false)
  })

  it('overwrites an existing STALE row', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'EN title',
      sourceHash: 'hash-v1',
    })

    await markStale(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldHashes: { title: 'hash-v2' },
    })

    await markFailed(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      sourceHash: 'hash-v2',
      modelId: 'gpt-4',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.status).toBe('FAILED')
    expect(row?.value).toBe('')
  })

  it('sets translatedAt to null', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'EN title',
      sourceHash: 'hash-v1',
    })

    await markStale(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldHashes: { title: 'hash-v2' },
    })

    await markFailed(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      sourceHash: 'hash-v2',
      modelId: 'gpt-4',
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.status).toBe('FAILED')
    expect(row?.translatedAt).toBeNull()
  })

  it('throws UnknownLocaleError for unknown or inactive locale', async () => {
    await expect(
      markFailed(db, {
        entityType: 'deal',
        entityId: 'd1',
        fieldKey: 'title',
        locale: 'xx',
      }),
    ).rejects.toBeInstanceOf(UnknownLocaleError)
  })

  it('throws InvalidFieldKeyError for invalid fieldKey', async () => {
    await expect(
      markFailed(db, {
        entityType: 'deal',
        entityId: 'd1',
        fieldKey: 'Bad-Key',
        locale: 'en',
      }),
    ).rejects.toBeInstanceOf(InvalidFieldKeyError)
  })
})

describe('markStale', () => {
  it('flips STALE only on non-manual rows where sourceHash differs, across locales', async () => {
    const entityType = 'deal'
    const entityId = 'd1'
    const fieldKey = 'title'
    const newHash = 'hash-v2'

    await setTranslation(db, {
      entityType,
      entityId,
      fieldKey,
      locale: 'en',
      value: 'EN stale candidate',
      sourceHash: 'hash-v1',
    })
    await setTranslation(db, {
      entityType,
      entityId,
      fieldKey,
      locale: 'he',
      value: 'HE stale candidate',
      sourceHash: 'hash-v1',
    })
    await setTranslation(db, {
      entityType,
      entityId,
      fieldKey,
      locale: 'en',
      value: 'EN already current',
      sourceHash: newHash,
      manualOverride: false,
    })
    await setTranslation(db, {
      entityType,
      entityId,
      fieldKey: 'description',
      locale: 'en',
      value: 'Manual desc',
      sourceHash: 'hash-v1',
      manualOverride: true,
    })

    await markStale(db, {
      entityType,
      entityId,
      fieldHashes: { title: newHash, description: newHash },
    })

    const enTitle = await readRow(entityType, entityId, 'title', 'en')
    const heTitle = await readRow(entityType, entityId, 'title', 'he')
    const enDesc = await readRow(entityType, entityId, 'description', 'en')

    expect(enTitle?.status).toBe('OK')
    expect(heTitle?.status).toBe('STALE')
    expect(enDesc?.status).toBe('OK')
    expect(enDesc?.manualOverride).toBe(true)
  })

  it('does NOT transition FAILED rows to STALE', async () => {
    await markFailed(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      sourceHash: 'hash-v1',
      modelId: 'gpt-4',
    })

    await markStale(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldHashes: { title: 'hash-v2' },
    })

    const row = await readRow('deal', 'd1', 'title', 'en')
    expect(row?.status).toBe('FAILED')
  })

  it('marks only fields whose hash changed as STALE', async () => {
    const entityType = 'deal'
    const entityId = 'd1'
    const hash1 = 'hash-v1'
    const hash2 = 'hash-v2'
    const newHash1 = 'hash-v1-new'

    await setTranslation(db, {
      entityType,
      entityId,
      fieldKey: 'title',
      locale: 'en',
      value: 'Title',
      sourceHash: hash1,
    })
    await setTranslation(db, {
      entityType,
      entityId,
      fieldKey: 'description',
      locale: 'en',
      value: 'Description',
      sourceHash: hash2,
    })

    await markStale(db, {
      entityType,
      entityId,
      fieldHashes: { title: newHash1, description: hash2 },
    })

    const titleRow = await readRow(entityType, entityId, 'title', 'en')
    const descRow = await readRow(entityType, entityId, 'description', 'en')

    expect(titleRow?.status).toBe('STALE')
    expect(descRow?.status).toBe('OK')
  })

  it('throws InvalidFieldKeyError for invalid entityType', async () => {
    await expect(
      markStale(db, {
        entityType: 'Bad-Type',
        entityId: 'd1',
        fieldHashes: { title: 'hash-v2' },
      }),
    ).rejects.toBeInstanceOf(InvalidFieldKeyError)
  })

  it('throws InvalidFieldKeyError for invalid fieldKey', async () => {
    await expect(
      markStale(db, {
        entityType: 'deal',
        entityId: 'd1',
        fieldHashes: { 'Bad-Key': 'hash-v2' },
      }),
    ).rejects.toBeInstanceOf(InvalidFieldKeyError)
  })
})