import { sql } from 'drizzle-orm'
import { afterAll, beforeAll, describe, expect, it } from 'vitest'
import { createPgliteClient } from '@platform-modules/db/pglite'
import type { TransactionalDatabase } from '@platform-modules/db'
import { isOversoldError } from '../errors.js'
import { pushSchema } from '../migrate.js'
import { inventorySchema, type InventorySchema } from '../schema.js'
import { startPg } from './pg-harness.js'
import { postMovement } from './post-movement.js'

const TENANT_ID = '10000000-0000-4000-8000-000000000001'
const WEIGHTED_ITEM_ID = '10000000-0000-4000-8000-000000000002'
const FIFO_ITEM_ID = '10000000-0000-4000-8000-000000000003'
const LOCATION_ID = '10000000-0000-4000-8000-000000000004'
const CONCURRENCY_ITEM_ID = '10000000-0000-4000-8000-000000000005'
const NOW = new Date('2026-07-04T12:00:00.000Z')

function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms))
}

function firstRows(res: unknown): Array<Record<string, unknown>> {
  return (Array.isArray(res) ? res : (res as { rows?: Array<Record<string, unknown>> }).rows) ?? []
}

async function freshDb() {
  const db = createPgliteClient({ schema: inventorySchema })
  await pushSchema(db)
  return db
}

async function seedLocation(db: TransactionalDatabase<InventorySchema>, locationId = LOCATION_ID): Promise<void> {
  await db.execute(sql`
    INSERT INTO stock_location (id, tenant_id, name, code)
    VALUES (${locationId}::uuid, ${TENANT_ID}::uuid, 'Main', 'MAIN')
  `)
}

async function seedItem(
  db: TransactionalDatabase<InventorySchema>,
  itemId: string,
  method: 'fifo' | 'weighted_average',
): Promise<void> {
  await db.execute(sql`
    INSERT INTO stock_item (id, tenant_id, name, method)
    VALUES (${itemId}::uuid, ${TENANT_ID}::uuid, ${itemId}, ${method})
  `)
}

describe('postMovement', () => {
  it('weighted-average receipt then issue computes avg cost and cogs', async () => {
    const db = await freshDb()
    await seedLocation(db)
    await seedItem(db, WEIGHTED_ITEM_ID, 'weighted_average')

    await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: WEIGHTED_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'receipt',
        qtyDelta: 10,
        unitCost: 5,
        occurredAt: NOW,
      }),
    )

    const result = await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: WEIGHTED_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'issue',
        qtyDelta: -4,
        occurredAt: NOW,
      }),
    )

    expect(result.pendingCost).toBe(false)
    expect(Number(result.movement.cogsAmount)).toBe(20)
    expect(Number(result.movement.unitCost)).toBe(5)

    const positionRes = await db.execute(sql`
      SELECT qty_on_hand, avg_unit_cost
      FROM stock_position
      WHERE tenant_id = ${TENANT_ID}::uuid
        AND item_id = ${WEIGHTED_ITEM_ID}::uuid
        AND location_id = ${LOCATION_ID}::uuid
    `)
    const position = firstRows(positionRes)[0]
    expect(Number(position?.qty_on_hand ?? 0)).toBe(6)
    expect(Number(position?.avg_unit_cost ?? 0)).toBe(5)
  })

  it('fifo multi-layer issue computes fifo cogs and updates layers', async () => {
    const db = await freshDb()
    await seedLocation(db)
    await seedItem(db, FIFO_ITEM_ID, 'fifo')

    await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: FIFO_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'receipt',
        qtyDelta: 2,
        unitCost: 3,
        occurredAt: new Date('2026-07-04T12:00:00.000Z'),
      }),
    )
    await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: FIFO_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'receipt',
        qtyDelta: 4,
        unitCost: 5,
        occurredAt: new Date('2026-07-04T12:05:00.000Z'),
      }),
    )

    const result = await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: FIFO_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'issue',
        qtyDelta: -5,
        occurredAt: new Date('2026-07-04T12:10:00.000Z'),
      }),
    )

    expect(result.pendingCost).toBe(false)
    expect(Number(result.movement.cogsAmount)).toBe(21)

    const layerRes = await db.execute(sql`
      SELECT unit_cost, qty_remaining
      FROM stock_cost_layer
      WHERE tenant_id = ${TENANT_ID}::uuid
        AND item_id = ${FIFO_ITEM_ID}::uuid
        AND location_id = ${LOCATION_ID}::uuid
      ORDER BY created_at ASC
    `)
    const layers = firstRows(layerRes)
    expect(layers.map((layer) => Number(layer.qty_remaining ?? 0))).toEqual([0, 1])
    expect(layers.map((layer) => Number(layer.unit_cost ?? 0))).toEqual([3, 5])
  })

  it('allowNegative=true issue against zero stock sets pendingCost', async () => {
    const db = await freshDb()
    await seedLocation(db)
    await seedItem(db, WEIGHTED_ITEM_ID, 'weighted_average')

    const result = await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: WEIGHTED_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'issue',
        qtyDelta: -2,
        occurredAt: NOW,
        allowNegative: true,
      }),
    )

    expect(result.pendingCost).toBe(true)
    expect(result.movement.pendingCost).toBe(true)
    expect(Number(result.movement.cogsAmount)).toBe(0)
  })

  it('allowNegative=false issue against insufficient stock throws OversoldError', async () => {
    const db = await freshDb()
    await seedLocation(db)
    await seedItem(db, WEIGHTED_ITEM_ID, 'weighted_average')

    await expect(
      db.transaction((tx) =>
        postMovement(tx, {
          tenantId: TENANT_ID,
          itemId: WEIGHTED_ITEM_ID,
          locationId: LOCATION_ID,
          kind: 'issue',
          qtyDelta: -1,
          occurredAt: NOW,
          allowNegative: false,
        }),
      ),
    ).rejects.toSatisfy((error) => isOversoldError(error))
  })

  it('idempotent replay returns the same movement without inserting a second row', async () => {
    const db = await freshDb()
    await seedLocation(db)
    await seedItem(db, WEIGHTED_ITEM_ID, 'weighted_average')

    const first = await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: WEIGHTED_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'receipt',
        qtyDelta: 3,
        unitCost: 9,
        holderRef: 'holder-1',
        occurredAt: NOW,
      }),
    )

    const replay = await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: WEIGHTED_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'receipt',
        qtyDelta: 3,
        unitCost: 9,
        holderRef: 'holder-1',
        occurredAt: NOW,
      }),
    )

    expect(replay.movement.id).toBe(first.movement.id)
    const countRes = await db.execute(sql`
      SELECT COUNT(*)::int AS count
      FROM stock_movement
      WHERE tenant_id = ${TENANT_ID}::uuid
        AND item_id = ${WEIGHTED_ITEM_ID}::uuid
        AND location_id = ${LOCATION_ID}::uuid
    `)
    expect(Number(firstRows(countRes)[0]?.count ?? 0)).toBe(1)
  })
})

describe('postMovement concurrency (real Postgres)', () => {
  let db: TransactionalDatabase<InventorySchema>
  let stop: (() => Promise<void>) | undefined

  beforeAll(async () => {
    const pg = await startPg()
    db = pg.db
    stop = pg.stop
  }, 120_000)

  afterAll(async () => {
    await stop?.()
  }, 30_000)

  it('two concurrent allowNegative=false issues cannot both succeed when only one unit exists', async () => {
    await seedLocation(db)
    await seedItem(db, CONCURRENCY_ITEM_ID, 'weighted_average')
    await db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: CONCURRENCY_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'receipt',
        qtyDelta: 1,
        unitCost: 10,
        occurredAt: NOW,
      }),
    )

    let resolveHold!: () => void
    const hold = new Promise<void>((resolve) => {
      resolveHold = resolve
    })

    let txALocked!: () => void
    const txAHasLock = new Promise<void>((resolve) => {
      txALocked = resolve
    })

    const txA = db.transaction(async (tx) => {
      await postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: CONCURRENCY_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'issue',
        qtyDelta: -1,
        occurredAt: NOW,
        allowNegative: false,
      })
      txALocked()
      await hold
    })

    await txAHasLock

    const txB = db.transaction((tx) =>
      postMovement(tx, {
        tenantId: TENANT_ID,
        itemId: CONCURRENCY_ITEM_ID,
        locationId: LOCATION_ID,
        kind: 'issue',
        qtyDelta: -1,
        occurredAt: NOW,
        allowNegative: false,
      }),
    )

    await sleep(400)
    resolveHold()

    const results = await Promise.allSettled([txA, txB])
    const fulfilled = results.filter((result) => result.status === 'fulfilled')
    const rejected = results.filter((result) => result.status === 'rejected')

    expect(fulfilled).toHaveLength(1)
    expect(rejected).toHaveLength(1)
    expect(isOversoldError((rejected[0] as PromiseRejectedResult).reason)).toBe(true)

    const countRes = await db.execute(sql`
      SELECT COUNT(*)::int AS count
      FROM stock_movement
      WHERE tenant_id = ${TENANT_ID}::uuid
        AND item_id = ${CONCURRENCY_ITEM_ID}::uuid
        AND location_id = ${LOCATION_ID}::uuid
    `)
    expect(Number(firstRows(countRes)[0]?.count ?? 0)).toBe(2)
  })
})