import type { NormalizedProduct, MatchResult } from './types.js';
import { query, log } from './db.js';
import { generateFingerprint } from './field-mapper.js';

// =============================================================================
// Token-based similarity (Jaccard)
// =============================================================================

function tokenize(s: string): Set<string> {
  return new Set(s.toLowerCase().split(/[\s\-\/\+]+/).filter(t => t.length > 0));
}

function tokenOverlap(a: string, b: string): number {
  const tokensA = tokenize(a);
  const tokensB = tokenize(b);
  if (tokensA.size === 0 && tokensB.size === 0) return 1;
  if (tokensA.size === 0 || tokensB.size === 0) return 0;
  let intersection = 0;
  for (const t of tokensA) {
    if (tokensB.has(t)) intersection++;
  }
  const union = new Set([...tokensA, ...tokensB]).size;
  return intersection / union;
}

function computeSimilarity(a: NormalizedProduct, candidate: any): number {
  const modelScore = a.model_normalized && candidate.model_normalized
    ? tokenOverlap(a.model_normalized, candidate.model_normalized)
    : 0;

  const colorScore = a.colorway_normalized && candidate.colorway_normalized
    ? tokenOverlap(a.colorway_normalized, candidate.colorway_normalized)
    : 0.5; // neutral if missing

  return modelScore * 0.7 + colorScore * 0.3;
}

// =============================================================================
// 4-tier Product Matcher
// =============================================================================

export async function matchProduct(product: NormalizedProduct): Promise<MatchResult> {
  // Tier 1: EAN match
  if (product.ean) {
    const eanResult = await query(
      'SELECT id FROM products WHERE ean = $1 OR gtin = $1 LIMIT 1',
      [product.ean]
    );
    if (eanResult.rows.length > 0) {
      return { productId: eanResult.rows[0].id, confidence: 1.0, matchType: 'ean' };
    }
  }

  // Tier 2: Exact brand + model
  if (product.brand_normalized && product.model_normalized) {
    const exactResult = await query(
      `SELECT id FROM products
       WHERE brand_normalized = $1 AND model_normalized = $2
       LIMIT 1`,
      [product.brand_normalized, product.model_normalized]
    );
    if (exactResult.rows.length > 0) {
      return { productId: exactResult.rows[0].id, confidence: 0.95, matchType: 'exact_model' };
    }
  }

  // Tier 3: Fuzzy match — brand + similar model
  if (product.brand_normalized && product.model_normalized) {
    const fuzzyResult = await query(
      `SELECT id, brand_normalized, model_normalized, colorway_normalized, match_fingerprint
       FROM products
       WHERE brand_normalized = $1
       ORDER BY similarity(COALESCE(model_normalized, ''), $2) DESC
       LIMIT 10`,
      [product.brand_normalized, product.model_normalized]
    );

    for (const candidate of fuzzyResult.rows) {
      const score = computeSimilarity(product, candidate);
      if (score >= 0.85) {
        return { productId: candidate.id, confidence: score, matchType: 'fuzzy' };
      }
    }
  }

  // Tier 4: No match — create new
  return { productId: null, confidence: 0, matchType: 'new' };
}

/** Create a new canonical product from a normalized feed product */
export async function createProduct(product: NormalizedProduct): Promise<number> {
  const result = await query(
    `INSERT INTO products (name, brand, slug, image_url, ean, gtin, brand_normalized, model_normalized, colorway_normalized, match_fingerprint, created_at, updated_at)
     VALUES ($1, $2, $3, $4, $5, $5, $6, $7, $8, $9, NOW(), NOW())
     RETURNING id`,
    [
      product.name,
      product.brand,
      generateSlug(product.name, product.brand),
      product.image_url || null,
      product.ean || null,
      product.brand_normalized,
      product.model_normalized || null,
      product.colorway_normalized || null,
      product.match_fingerprint || null,
    ]
  );
  return result.rows[0].id;
}

/** Upsert a product offer (per feed) */
export async function upsertOffer(
  productId: number,
  feedId: number,
  product: NormalizedProduct
): Promise<'new' | 'updated'> {
  const externalId = product.external_id || product.ean || `${product.brand}-${product.name}`.slice(0, 255);

  const result = await query(
    `INSERT INTO product_offers (product_id, feed_id, external_id, price, original_price, currency, product_url, image_url, in_stock, last_seen_at, updated_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
     ON CONFLICT (feed_id, external_id)
     DO UPDATE SET
       product_id = $1,
       price = $4,
       original_price = $5,
       product_url = $7,
       image_url = $8,
       in_stock = $9,
       last_seen_at = NOW(),
       updated_at = NOW()
     RETURNING (xmax = 0) AS is_new`,
    [
      productId,
      feedId,
      externalId,
      product.price,
      product.original_price || null,
      product.currency,
      product.product_url,
      product.image_url || null,
      product.in_stock,
    ]
  );

  return result.rows[0].is_new ? 'new' : 'updated';
}

function generateSlug(name: string, brand: string): string {
  return `${brand}-${name}`
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-|-$/g, '')
    .slice(0, 200);
}
