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

type ProductMatchRuleRow = {
  id: number
  provider: string
  platform: string
  shop_id: string
  external_item_id: string
  external_sku_code: string
  external_sku_name: string
  external_sku_name_normalized: string
  resolved_sku_code: string
  enabled: boolean
  priority: number
  config_json: string | Record<string, unknown>
  created_at: string
  updated_at: string
  matched_by?: string
  match_score?: number
}

type ProductMatchRuleUpsertInput = {
  provider?: string
  platform?: string
  shopId?: string
  externalItemId?: string
  externalSkuCode?: string
  externalSkuName?: string
  externalSkuNameNormalized?: string
  resolvedSkuCode: string
  enabled?: boolean
  priority?: number | string
  configJson?: string | Record<string, unknown>
  createdAt: string
  updatedAt: string
}

type ProductMatchRuleResolveInput = {
  provider?: string
  platform?: string
  shopId?: string
  externalItemId?: string
  externalSkuCode?: string
  externalSkuNameNormalized?: string
}

export async function upsertProductMatchRule(
  input: ProductMatchRuleUpsertInput,
): Promise<ProductMatchRuleRow | null> {
  const result = await query<ProductMatchRuleRow>(
    `
      INSERT INTO product_match_rules (
        provider,
        platform,
        shop_id,
        external_item_id,
        external_sku_code,
        external_sku_name,
        external_sku_name_normalized,
        resolved_sku_code,
        enabled,
        priority,
        config_json,
        created_at,
        updated_at
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::jsonb, $12, $13
      )
      ON CONFLICT (
        provider,
        platform,
        shop_id,
        external_item_id,
        external_sku_code,
        external_sku_name_normalized,
        resolved_sku_code
      ) DO UPDATE
      SET
        external_sku_name = EXCLUDED.external_sku_name,
        enabled = EXCLUDED.enabled,
        priority = EXCLUDED.priority,
        config_json = EXCLUDED.config_json,
        updated_at = EXCLUDED.updated_at
      RETURNING *
    `,
    [
      input.provider || '',
      input.platform || '',
      input.shopId || '',
      input.externalItemId || '',
      input.externalSkuCode || '',
      input.externalSkuName || '',
      input.externalSkuNameNormalized || '',
      input.resolvedSkuCode,
      input.enabled !== false,
      Number(input.priority || 100),
      input.configJson || '{}',
      input.createdAt,
      input.updatedAt,
    ],
  )

  return result.rows[0] || null
}

export async function resolveProductMatchRule({
  provider = '',
  platform = '',
  shopId = '',
  externalItemId = '',
  externalSkuCode = '',
  externalSkuNameNormalized = '',
}: ProductMatchRuleResolveInput): Promise<ProductMatchRuleRow | null> {
  const result = await query<ProductMatchRuleRow>(
    `
      SELECT
        pmr.*,
        CASE
          WHEN $4 != '' AND pmr.external_sku_code = $4 THEN 'external_sku_code'
          WHEN $5 != '' AND pmr.external_item_id = $5 THEN 'external_item_id'
          WHEN $6 != '' AND pmr.external_sku_name_normalized = $6 THEN 'external_sku_name_exact'
          WHEN $6 != '' AND pmr.external_sku_name_normalized != '' AND (
            position(pmr.external_sku_name_normalized in $6) > 0
            OR position($6 in pmr.external_sku_name_normalized) > 0
          ) THEN 'external_sku_name_contains'
          ELSE ''
        END AS matched_by,
        CASE
          WHEN $4 != '' AND pmr.external_sku_code = $4 THEN 400
          WHEN $5 != '' AND pmr.external_item_id = $5 THEN 300
          WHEN $6 != '' AND pmr.external_sku_name_normalized = $6 THEN 200
          WHEN $6 != '' AND pmr.external_sku_name_normalized != '' AND (
            position(pmr.external_sku_name_normalized in $6) > 0
            OR position($6 in pmr.external_sku_name_normalized) > 0
          ) THEN 100
          ELSE 0
        END AS match_score
      FROM product_match_rules pmr
      WHERE pmr.enabled = TRUE
        AND (pmr.provider = '' OR pmr.provider = $1)
        AND (pmr.platform = '' OR pmr.platform = $2)
        AND (pmr.shop_id = '' OR pmr.shop_id = $3)
        AND (
          ($4 != '' AND pmr.external_sku_code = $4)
          OR ($5 != '' AND pmr.external_item_id = $5)
          OR (
            $6 != ''
            AND pmr.external_sku_name_normalized != ''
            AND (
              pmr.external_sku_name_normalized = $6
              OR position(pmr.external_sku_name_normalized in $6) > 0
              OR position($6 in pmr.external_sku_name_normalized) > 0
            )
          )
        )
      ORDER BY
        match_score DESC,
        CASE WHEN pmr.shop_id = '' THEN 1 ELSE 0 END,
        CASE WHEN pmr.platform = '' THEN 1 ELSE 0 END,
        CASE WHEN pmr.provider = '' THEN 1 ELSE 0 END,
        pmr.priority ASC,
        pmr.id ASC
      LIMIT 1
    `,
    [provider, platform, shopId, externalSkuCode, externalItemId, externalSkuNameNormalized],
  )

  return result.rows[0] || null
}
