import { query } from '../../../db/client.js'
import { mapAdminObservedProductItem } from './domain.js'

type JsonObject = Record<string, any>

export const ADMIN_OBSERVED_PRODUCTS_QUERY = `
  SELECT
    o.provider,
    o.platform,
    o.shop_id,
    MAX(CASE WHEN trim(o.shop_name) != '' THEN o.shop_name ELSE '' END) AS shop_name,
    COALESCE(NULLIF(oi.item_snapshot_json ->> 'externalItemId', ''), '') AS external_item_id,
    COALESCE(NULLIF(oi.item_snapshot_json ->> 'externalSkuCode', ''), '') AS external_sku_code,
    COALESCE(NULLIF(oi.item_snapshot_json ->> 'externalSkuName', ''), '') AS external_sku_name,
    MAX(oi.created_at) AS latest_seen_at,
    COUNT(*)::int AS order_item_count
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE
    COALESCE(NULLIF(oi.item_snapshot_json ->> 'externalItemId', ''), '') != ''
    OR COALESCE(NULLIF(oi.item_snapshot_json ->> 'externalSkuCode', ''), '') != ''
    OR COALESCE(NULLIF(oi.item_snapshot_json ->> 'externalSkuName', ''), '') != ''
  GROUP BY
    o.provider,
    o.platform,
    o.shop_id,
    external_item_id,
    external_sku_code,
    external_sku_name
  ORDER BY latest_seen_at DESC, o.platform ASC, o.shop_id ASC, external_sku_code ASC
  LIMIT 200
`

export function mapAdminObservedProductRow(row: JsonObject = {}) {
  return {
    provider: String(row.provider || '').trim(),
    platform: String(row.platform || '').trim(),
    shopId: String(row.shop_id || '').trim(),
    shopName: String(row.shop_name || '').trim(),
    externalItemId: String(row.external_item_id || '').trim(),
    externalSkuCode: String(row.external_sku_code || '').trim(),
    externalSkuName: String(row.external_sku_name || '').trim(),
    latestSeenAt: row.latest_seen_at || null,
    orderItemCount: Number(row.order_item_count || 0),
  }
}

export async function listAdminObservedProducts(bindings: JsonObject[] = [], options: JsonObject = {}) {
  const queryImpl = (options.query || query) as (sql: string) => Promise<{ rows: JsonObject[] }>
  const rowsResult = await queryImpl(ADMIN_OBSERVED_PRODUCTS_QUERY)
  return rowsResult.rows.map((row) => mapAdminObservedProductItem(mapAdminObservedProductRow(row), bindings))
}
