import { query, withTransaction } from '../db/client.js'
import type {
  InventoryCreateItemInput,
  InventoryListQueryInput,
  InventorySkuSuggestionQueryInput,
} from '../types/repository-inputs.js'
import type {
  InventoryItemRow,
  InventoryListQueryResult,
  InventorySkuSuggestionRow,
} from '../types/repository-rows.js'

type InventoryGroupCodesInput = string | string[] | null | undefined

type InventoryGroupListInput = {
  keyword?: string
  limit?: number | string
}

const INVENTORY_ITEM_SELECT = `
  SELECT
    ii.id,
    ii.sku_code,
    ii.batch_no,
    ii.credential_type,
    ii.inventory_group_code,
    ii.display_value,
    ii.status,
    ii.invalid_reason,
    ii.consumed_at AS delivered_at,
    ii.created_at,
    ii.updated_at,
    tib.task_id AS reserved_by_task_id,
    ft.task_no AS reserved_by_task_no,
    ft.platform_order_id
  FROM inventory_items ii
  LEFT JOIN LATERAL (
    SELECT *
    FROM task_inventory_bindings
    WHERE inventory_item_id = ii.id AND binding_status IN ('reserved', 'consumed')
    ORDER BY id DESC
    LIMIT 1
  ) tib ON TRUE
  LEFT JOIN fulfillment_tasks ft ON ft.id = tib.task_id
`

export async function findFirstAvailableInventoryItemBySkuCode(
  skuCode: string,
  credentialType = 'tencent_code',
  inventoryGroupCodes: InventoryGroupCodesInput = null,
): Promise<InventoryItemRow | null> {
  const normalizedInventoryGroupCodes = normalizeInventoryGroupCodes(inventoryGroupCodes)
  if (normalizedInventoryGroupCodes && normalizedInventoryGroupCodes.length === 0) {
    return null
  }

  const params: unknown[] = [skuCode, credentialType]
  const filters = [
    'ii.sku_code = $1',
    'ii.credential_type = $2',
    `ii.status = 'available'`,
  ]

  if (normalizedInventoryGroupCodes) {
    params.push(normalizedInventoryGroupCodes)
    filters.push(`ii.inventory_group_code = ANY($${params.length}::text[])`)
  }

  const result = await query<InventoryItemRow>(
    `${INVENTORY_ITEM_SELECT}
     WHERE ${filters.join(' AND ')}
     ORDER BY ii.id ASC
     LIMIT 1`,
    params,
  )

  return result.rows[0] || null
}

export async function assignReservedInventoryItem(
  inventoryItemId: number | string,
  taskId: number | string,
  updatedAt: string,
  roleKey = 'primary_code',
): Promise<InventoryItemRow | null> {
  return withTransaction(async (client) => {
    const inventoryResult = await client.query(
      `
        UPDATE inventory_items
        SET status = 'reserved', updated_at = $1
        WHERE id = $2 AND status = 'available'
        RETURNING id
      `,
      [updatedAt, Number(inventoryItemId)],
    )

    if (!inventoryResult.rows[0]) {
      return getInventoryItemById(inventoryItemId)
    }

    await client.query(
      `
        INSERT INTO task_inventory_bindings (
          task_id,
          inventory_item_id,
          role_key,
          quantity,
          binding_status,
          metadata_json,
          created_at,
          updated_at
        ) VALUES ($1, $2, $3, 1, 'reserved', '{}'::jsonb, $4, $4)
        ON CONFLICT (task_id, role_key, inventory_item_id) DO UPDATE
        SET binding_status = 'reserved', updated_at = EXCLUDED.updated_at, released_at = NULL
      `,
      [Number(taskId), Number(inventoryItemId), roleKey, updatedAt],
    )

    return getInventoryItemById(inventoryItemId)
  })
}

export async function getInventoryItemById(inventoryItemId: number | string): Promise<InventoryItemRow | null> {
  const result = await query<InventoryItemRow>(
    `${INVENTORY_ITEM_SELECT}
     WHERE ii.id = $1
     LIMIT 1`,
    [Number(inventoryItemId)],
  )

  return result.rows[0] || null
}

export async function markInventoryItemDelivered(
  inventoryItemId: number | string,
  deliveredAt: string,
): Promise<InventoryItemRow | null> {
  return withTransaction(async (client) => {
    await client.query(
      `
        UPDATE inventory_items
        SET status = 'consumed', consumed_at = $1, updated_at = $1
        WHERE id = $2
      `,
      [deliveredAt, Number(inventoryItemId)],
    )

    await client.query(
      `
        UPDATE task_inventory_bindings
        SET binding_status = 'consumed', consumed_at = $1, updated_at = $1
        WHERE inventory_item_id = $2 AND binding_status = 'reserved'
      `,
      [deliveredAt, Number(inventoryItemId)],
    )

    return getInventoryItemById(inventoryItemId)
  })
}

export async function markInventoryItemConsumed(
  inventoryItemId: number | string,
  reason: string,
  consumedAt: string,
): Promise<InventoryItemRow | null> {
  return withTransaction(async (client) => {
    await client.query(
      `
        UPDATE inventory_items
        SET
          status = 'consumed',
          invalid_reason = $1,
          consumed_at = $2,
          updated_at = $2
        WHERE id = $3
      `,
      [String(reason || '').trim(), consumedAt, Number(inventoryItemId)],
    )

    await client.query(
      `
        UPDATE task_inventory_bindings
        SET binding_status = 'consumed', consumed_at = $1, updated_at = $1
        WHERE inventory_item_id = $2 AND binding_status = 'reserved'
      `,
      [consumedAt, Number(inventoryItemId)],
    )

    return getInventoryItemById(inventoryItemId)
  })
}

export async function listInventoryItems({
  page = 1,
  pageSize = 20,
  skuCode = '',
  credentialType = '',
  status = '',
  batchNo = '',
  inventoryGroupCode = '',
  allowedInventoryGroupCodes = null,
}: InventoryListQueryInput = {}): Promise<InventoryListQueryResult> {
  const normalizedAllowedInventoryGroupCodes = normalizeInventoryGroupCodes(allowedInventoryGroupCodes)
  if (normalizedAllowedInventoryGroupCodes && normalizedAllowedInventoryGroupCodes.length === 0) {
    return {
      items: [],
      total: 0,
    }
  }

  const offset = (page - 1) * pageSize
  const filters: string[] = []
  const params: unknown[] = []

  if (skuCode) {
    params.push(skuCode)
    filters.push(`ii.sku_code = $${params.length}`)
  }

  if (status) {
    params.push(status === 'delivered' ? 'consumed' : status)
    filters.push(`ii.status = $${params.length}`)
  }

  if (credentialType) {
    params.push(credentialType)
    filters.push(`ii.credential_type = $${params.length}`)
  }

  if (batchNo) {
    params.push(`%${batchNo}%`)
    filters.push(`ii.batch_no ILIKE $${params.length}`)
  }

  if (inventoryGroupCode) {
    params.push(inventoryGroupCode)
    filters.push(`ii.inventory_group_code = $${params.length}`)
  }

  if (normalizedAllowedInventoryGroupCodes) {
    params.push(normalizedAllowedInventoryGroupCodes)
    filters.push(`ii.inventory_group_code = ANY($${params.length}::text[])`)
  }

  const whereClause = filters.length > 0 ? `WHERE ${filters.join(' AND ')}` : ''
  const totalResult = await query<{ [column: string]: unknown, total: number }>(
    `SELECT COUNT(*)::int AS total FROM inventory_items ii ${whereClause}`,
    params,
  )

  params.push(pageSize)
  params.push(offset)
  const itemsResult = await query<InventoryItemRow>(
    `${INVENTORY_ITEM_SELECT}
     ${whereClause}
     ORDER BY ii.id DESC
     LIMIT $${params.length - 1} OFFSET $${params.length}`,
    params,
  )

  return {
    items: itemsResult.rows,
    total: Number(totalResult.rows[0]?.total || 0),
  }
}

export async function listInventorySkuSuggestions({
  credentialType = '',
  keyword = '',
  limit = 50,
  inventoryGroupCode = '',
  allowedInventoryGroupCodes = null,
}: InventorySkuSuggestionQueryInput = {}): Promise<InventorySkuSuggestionRow[]> {
  const normalizedAllowedInventoryGroupCodes = normalizeInventoryGroupCodes(allowedInventoryGroupCodes)
  if (normalizedAllowedInventoryGroupCodes && normalizedAllowedInventoryGroupCodes.length === 0) {
    return []
  }

  const filters: string[] = []
  const params: unknown[] = []

  if (credentialType) {
    params.push(credentialType)
    filters.push(`credential_type = $${params.length}`)
  }

  if (keyword) {
    params.push(`%${keyword}%`)
    filters.push(`sku_code ILIKE $${params.length}`)
  }

  if (inventoryGroupCode) {
    params.push(inventoryGroupCode)
    filters.push(`inventory_group_code = $${params.length}`)
  }

  if (normalizedAllowedInventoryGroupCodes) {
    params.push(normalizedAllowedInventoryGroupCodes)
    filters.push(`inventory_group_code = ANY($${params.length}::text[])`)
  }

  const normalizedLimit = Number.isFinite(Number(limit))
    ? Math.max(1, Math.min(Number(limit), 100))
    : 50

  params.push(normalizedLimit)
  const whereClause = filters.length > 0 ? `WHERE ${filters.join(' AND ')}` : ''
  const result = await query<InventorySkuSuggestionRow>(
    `
      SELECT
        sku_code,
        credential_type,
        inventory_group_code,
        COUNT(*)::int AS total_count,
        COUNT(*) FILTER (WHERE status = 'available')::int AS available_count,
        MAX(updated_at) AS latest_updated_at
      FROM inventory_items
      ${whereClause}
      GROUP BY sku_code, credential_type, inventory_group_code
      ORDER BY
        COUNT(*) FILTER (WHERE status = 'available') DESC,
        COUNT(*) DESC,
        MAX(updated_at) DESC,
        sku_code ASC,
        inventory_group_code ASC
      LIMIT $${params.length}
    `,
    params,
  )

  return result.rows
}

export async function createInventoryItems(rows: InventoryCreateItemInput[]): Promise<number> {
  let created = 0

  for (const row of rows) {
    const payloadJson = JSON.stringify(row.payload || { code: row.displayValue })
    const displayValue = String(row.displayValue || '').trim()
    const inventoryGroupCode = String(row.inventoryGroupCode || '').trim()
    const result = await query<{ [column: string]: unknown, id: number }>(
      `
        INSERT INTO inventory_items (
          batch_no,
          sku_code,
          credential_type,
          inventory_group_code,
          display_value,
          payload_json,
          source_type,
          status,
          invalid_reason,
          metadata_json,
          created_at,
          updated_at
        ) VALUES ($1, $2, $3, $4, $5, $6::jsonb, 'static_import', 'available', '', '{}'::jsonb, $7, $8)
        ON CONFLICT DO NOTHING
        RETURNING id
      `,
      [
        row.batchNo || '',
        row.skuCode,
        row.credentialType || 'tencent_code',
        inventoryGroupCode,
        displayValue,
        payloadJson,
        row.createdAt,
        row.updatedAt,
      ],
    )

    if (result.rows[0]?.id) {
      created += 1
    }
  }

  return created
}

export async function listInventoryGroupCodes({
  keyword = '',
  limit = 100,
}: InventoryGroupListInput = {}): Promise<string[]> {
  const params: unknown[] = []
  const filters = [`inventory_group_code <> ''`]

  if (keyword) {
    params.push(`%${String(keyword || '').trim()}%`)
    filters.push(`inventory_group_code ILIKE $${params.length}`)
  }

  const normalizedLimit = Number.isFinite(Number(limit))
    ? Math.max(1, Math.min(Number(limit), 200))
    : 100

  params.push(normalizedLimit)
  const result = await query<{ [column: string]: unknown, inventory_group_code: string }>(
    `
      SELECT inventory_group_code
      FROM inventory_items
      WHERE ${filters.join(' AND ')}
      GROUP BY inventory_group_code
      ORDER BY inventory_group_code ASC
      LIMIT $${params.length}
    `,
    params,
  )

  return result.rows.map((row) => String(row.inventory_group_code || '').trim()).filter(Boolean)
}

function normalizeInventoryGroupCodes(inventoryGroupCodes: InventoryGroupCodesInput): string[] | null {
  if (inventoryGroupCodes == null) {
    return null
  }

  return Array.from(new Set((Array.isArray(inventoryGroupCodes) ? inventoryGroupCodes : [inventoryGroupCodes])
    .map((value) => String(value || '').trim())
    .filter(Boolean)))
}

export async function releaseReservedInventoryItem(
  inventoryItemId: number | string,
  updatedAt: string,
): Promise<InventoryItemRow | null> {
  return withTransaction(async (client) => {
    await client.query(
      `
        UPDATE task_inventory_bindings
        SET binding_status = 'released', released_at = $1, updated_at = $1
        WHERE inventory_item_id = $2 AND binding_status = 'reserved'
      `,
      [updatedAt, Number(inventoryItemId)],
    )

    await client.query(
      `
        UPDATE inventory_items
        SET status = 'available', updated_at = $1
        WHERE id = $2 AND status = 'reserved'
      `,
      [updatedAt, Number(inventoryItemId)],
    )

    return getInventoryItemById(inventoryItemId)
  })
}

export async function invalidateInventoryItem(
  inventoryItemId: number | string,
  invalidReason: string,
  updatedAt: string,
): Promise<InventoryItemRow | null> {
  const result = await query<{ [column: string]: unknown, id: number }>(
    `
      UPDATE inventory_items
      SET status = 'invalid', invalid_reason = $1, updated_at = $2
      WHERE id = $3 AND status = 'available'
      RETURNING id
    `,
    [invalidReason, updatedAt, Number(inventoryItemId)],
  )

  if (!result.rows[0]) {
    return getInventoryItemById(inventoryItemId)
  }

  return getInventoryItemById(inventoryItemId)
}

export async function invalidateReservedInventoryItem(
  inventoryItemId: number | string,
  invalidReason: string,
  updatedAt: string,
): Promise<InventoryItemRow | null> {
  return withTransaction(async (client) => {
    await client.query(
      `
        UPDATE task_inventory_bindings
        SET binding_status = 'released', released_at = $1, updated_at = $1
        WHERE inventory_item_id = $2 AND binding_status = 'reserved'
      `,
      [updatedAt, Number(inventoryItemId)],
    )

    await client.query(
      `
        UPDATE inventory_items
        SET status = 'invalid', invalid_reason = $1, updated_at = $2
        WHERE id = $3
      `,
      [String(invalidReason || '').trim(), updatedAt, Number(inventoryItemId)],
    )

    return getInventoryItemById(inventoryItemId)
  })
}
