import { afterAll, beforeAll, beforeEach, describe, expect, it, vi } from 'vitest'
import { sql } from 'drizzle-orm'
import type { Querier, TransactionalDatabase } from '@platform-modules/db'
import { InvalidFieldKeyError } from './errors.js'
import { upsertLanguage } from './languages.js'
import { pushSchema } from './migrate.js'
import { makePgHarness } from './pg-harness.js'
import { getTranslations, getTranslationsFor } from './query.js'
import { translationValue, type I18nContentSchema } from './schema.js'
import { 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 insertRow(args: {
  entityType: string
  entityId: string
  fieldKey: string
  locale: string
  value: string
  status?: string
}) {
  await db.insert(translationValue).values({
    entityType: args.entityType,
    entityId: args.entityId,
    fieldKey: args.fieldKey,
    locale: args.locale,
    value: args.value,
    status: args.status ?? 'OK',
    sourceHash: '',
    manualOverride: false,
  })
}

describe('getTranslations', () => {
  it('returns OK row value', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Hello',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'en',
      }),
    ).resolves.toEqual({ title: 'Hello' })
  })

  it('STALE row IS returned (not excluded)', async () => {
    await insertRow({
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Stale title',
      status: 'STALE',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'en',
      }),
    ).resolves.toEqual({ title: 'Stale title' })
  })

  it('FAILED row is excluded', async () => {
    await insertRow({
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Failed title',
      status: 'FAILED',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'en',
      }),
    ).resolves.toEqual({})
  })

  it('empty-value row is excluded', async () => {
    await insertRow({
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: '',
      status: 'OK',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'en',
      }),
    ).resolves.toEqual({})
  })

  it('field not found → omitted from result (key absent)', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Only title',
    })

    const result = await getTranslations(db, {
      entityType: 'deal',
      entityId: 'd1',
      locale: 'en',
    })

    expect(result).toEqual({ title: 'Only title' })
    expect(result).not.toHaveProperty('description')
  })

  it('fields filter: only requested fields returned', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Title',
    })
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'description',
      locale: 'en',
      value: 'Description',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'en',
        fields: ['title'],
      }),
    ).resolves.toEqual({ title: 'Title' })
  })

  it('fallbackToDefault=false (default): missing field in locale → omitted (no fallback)', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'English title',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'he',
      }),
    ).resolves.toEqual({})
  })

  it('fallbackToDefault=true: missing field in locale → returns default locale value', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'English title',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'he',
        fallbackToDefault: true,
      }),
    ).resolves.toEqual({ title: 'English title' })
  })

  it('fallbackToDefault=true: locale row wins over default-locale row when both exist', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'English title',
    })
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'he',
      value: 'Hebrew title',
    })

    await expect(
      getTranslations(db, {
        entityType: 'deal',
        entityId: 'd1',
        locale: 'he',
        fallbackToDefault: true,
      }),
    ).resolves.toEqual({ title: 'Hebrew title' })
  })

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

describe('getTranslationsFor', () => {
  it('returns correct Map with Records per entityId', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Deal 1',
    })
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd2',
      fieldKey: 'title',
      locale: 'en',
      value: 'Deal 2',
    })

    const map = await getTranslationsFor(db, {
      entityType: 'deal',
      entityIds: ['d1', 'd2'],
      locale: 'en',
    })

    expect(map.get('d1')).toEqual({ title: 'Deal 1' })
    expect(map.get('d2')).toEqual({ title: 'Deal 2' })
  })

  it('STALE served, FAILED excluded', async () => {
    await insertRow({
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Stale',
      status: 'STALE',
    })
    await insertRow({
      entityType: 'deal',
      entityId: 'd2',
      fieldKey: 'title',
      locale: 'en',
      value: 'Failed',
      status: 'FAILED',
    })

    const map = await getTranslationsFor(db, {
      entityType: 'deal',
      entityIds: ['d1', 'd2'],
      locale: 'en',
    })

    expect(map.get('d1')).toEqual({ title: 'Stale' })
    expect(map.get('d2')).toEqual({})
  })

  it('missing entityId in DB → empty Record or absent from map', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Exists',
    })

    const map = await getTranslationsFor(db, {
      entityType: 'deal',
      entityIds: ['d1', 'missing'],
      locale: 'en',
    })

    expect(map.get('d1')).toEqual({ title: 'Exists' })
    expect(map.get('missing')).toEqual({})
  })

  it('fields filter works', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Title',
    })
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'description',
      locale: 'en',
      value: 'Description',
    })

    const map = await getTranslationsFor(db, {
      entityType: 'deal',
      entityIds: ['d1'],
      locale: 'en',
      fields: ['description'],
    })

    expect(map.get('d1')).toEqual({ description: 'Description' })
  })

  it('fallbackToDefault=true: batch fallback works', async () => {
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd1',
      fieldKey: 'title',
      locale: 'en',
      value: 'English 1',
    })
    await setTranslation(db, {
      entityType: 'deal',
      entityId: 'd2',
      fieldKey: 'title',
      locale: 'he',
      value: 'Hebrew 2',
    })

    const map = await getTranslationsFor(db, {
      entityType: 'deal',
      entityIds: ['d1', 'd2'],
      locale: 'he',
      fallbackToDefault: true,
    })

    expect(map.get('d1')).toEqual({ title: 'English 1' })
    expect(map.get('d2')).toEqual({ title: 'Hebrew 2' })
  })

  it('throws InvalidFieldKeyError for invalid entityType even when entityIds is empty', async () => {
    await expect(
      getTranslationsFor(db, { entityType: 'Bad-Type', entityIds: [], locale: 'en' }),
    ).rejects.toThrow()
  })

  it('empty entityIds → returns empty Map (no DB call)', async () => {
    const selectSpy = vi.spyOn(db, 'select')

    const map = await getTranslationsFor(db, {
      entityType: 'deal',
      entityIds: [],
      locale: 'en',
    })

    expect(map).toEqual(new Map())
    expect(selectSpy).not.toHaveBeenCalled()

    selectSpy.mockRestore()
  })

  it('issues exactly ONE DB query', async () => {
    const entityIds = Array.from({ length: 5 }, (_, i) => `d${i + 1}`)
    for (const entityId of entityIds) {
      await setTranslation(db, {
        entityType: 'deal',
        entityId,
        fieldKey: 'title',
        locale: 'en',
        value: `Title for ${entityId}`,
      })
    }

    let selectCount = 0
    const countingDb = new Proxy(db, {
      get(target, prop, receiver) {
        if (prop === 'select') {
          return (...args: Parameters<typeof db.select>) => {
            selectCount++
            return Reflect.apply(target.select, target, args)
          }
        }
        return Reflect.get(target, prop, receiver)
      },
    }) as Querier<I18nContentSchema>

    const map = await getTranslationsFor(countingDb, {
      entityType: 'deal',
      entityIds,
      locale: 'en',
    })

    expect(selectCount).toBe(1)
    expect(map.size).toBe(5)
    for (const entityId of entityIds) {
      expect(map.get(entityId)).toEqual({ title: `Title for ${entityId}` })
    }
  })
})

describe('entity-agnostic proof', () => {
  it('stores and reads ≥2 distinct entityTypes independently', async () => {
    await setTranslation(db, {
      entityType: 'article',
      entityId: 'a1',
      fieldKey: 'title',
      locale: 'en',
      value: 'Article Title',
      sourceHash: '',
      manualOverride: true,
    })
    await setTranslation(db, {
      entityType: 'product',
      entityId: 'p1',
      fieldKey: 'name',
      locale: 'en',
      value: 'Product Name',
      sourceHash: '',
      manualOverride: true,
    })

    const articleResult = await getTranslations(db, { entityType: 'article', entityId: 'a1', locale: 'en' })
    const productResult = await getTranslations(db, { entityType: 'product', entityId: 'p1', locale: 'en' })

    expect(articleResult).toEqual({ title: 'Article Title' })
    expect(productResult).toEqual({ name: 'Product Name' })

    expect(articleResult.name).toBeUndefined()
    expect(productResult.title).toBeUndefined()
  })
})