import type { RawProduct, NormalizedProduct, FieldMappingRow } from './types.js';
import { query } from './db.js';
import { log } from './db.js';
import crypto from 'crypto';

// =============================================================================
// Transform functions
// =============================================================================

function stripHtml(value: string): string {
  return value.replace(/<[^>]*>/g, '').replace(/&[a-z]+;/gi, ' ').trim();
}

function parsePrice(value: string): string {
  // Handle European comma decimals: "49,99" → "49.99"
  let cleaned = String(value).replace(/[^\d,.\-]/g, '');
  // If has comma but no dot, treat comma as decimal
  if (cleaned.includes(',') && !cleaned.includes('.')) {
    cleaned = cleaned.replace(',', '.');
  }
  // If has both, comma is thousand separator: "1.234,56" → "1234.56"
  else if (cleaned.includes(',') && cleaned.includes('.')) {
    if (cleaned.indexOf(',') > cleaned.indexOf('.')) {
      cleaned = cleaned.replace(/\./g, '').replace(',', '.');
    } else {
      cleaned = cleaned.replace(/,/g, '');
    }
  }
  const num = parseFloat(cleaned);
  return isNaN(num) ? '0' : num.toFixed(2);
}

function parseBoolean(value: string): string {
  const v = String(value).toLowerCase().trim();
  return ['true', '1', 'yes', 'in stock', 'available', 'ja'].includes(v) ? 'true' : 'false';
}

const TRANSFORMS: Record<string, (v: string, args?: any) => string> = {
  lowercase: (v) => v.toLowerCase(),
  uppercase: (v) => v.toUpperCase(),
  strip_html: stripHtml,
  strip_whitespace: (v) => v.replace(/\s+/g, '').trim(),
  parse_price: parsePrice,
  parse_boolean: parseBoolean,
  trim: (v) => v.trim(),
  regex: (v, args) => {
    if (!args?.pattern) return v;
    const match = v.match(new RegExp(args.pattern));
    return match ? (match[1] || match[0]) : v;
  },
};

// =============================================================================
// Brand normalization
// =============================================================================

const BRAND_ALIASES: Record<string, string> = {
  'nike': 'Nike', 'NIKE': 'Nike',
  'adidas': 'adidas', 'Adidas': 'adidas', 'ADIDAS': 'adidas',
  'new balance': 'New Balance', 'new-balance': 'New Balance', 'nb': 'New Balance',
  'asics': 'ASICS', 'Asics': 'ASICS',
  'puma': 'PUMA', 'Puma': 'PUMA',
  'converse': 'Converse', 'CONVERSE': 'Converse',
  'reebok': 'Reebok', 'REEBOK': 'Reebok',
  'vans': 'Vans', 'VANS': 'Vans',
  'jordan': 'Jordan', 'JORDAN': 'Jordan', 'air jordan': 'Jordan',
  'saucony': 'Saucony', 'SAUCONY': 'Saucony',
  'on': 'On Running', 'on running': 'On Running', 'On': 'On Running',
  'hoka': 'HOKA', 'hoka one one': 'HOKA', 'HOKA ONE ONE': 'HOKA',
};

export function normalizeBrand(brand: string): string {
  const trimmed = brand.trim();
  return BRAND_ALIASES[trimmed] || BRAND_ALIASES[trimmed.toLowerCase()] || trimmed;
}

export function normalizeModel(name: string, brand: string): string {
  let model = name.replace(new RegExp(brand, 'gi'), '').trim();
  // Remove size info, gender labels
  model = model.replace(/\b(maat|size|eu|us|uk)\s*\d+[\.\d]*/gi, '');
  model = model.replace(/\b(heren|damen|men'?s?|women'?s?|unisex|kids?|junior|gs|td|ps)\b/gi, '');
  model = model.replace(/\s+/g, ' ').trim();
  return model;
}

export function generateFingerprint(product: Partial<NormalizedProduct>): string {
  const input = [
    product.brand_normalized,
    product.model_normalized,
    product.colorway_normalized,
  ].filter(Boolean).join('|').toLowerCase();
  return crypto.createHash('sha256').update(input).digest('hex').slice(0, 16);
}

// =============================================================================
// Field Mapper
// =============================================================================

export async function getMappings(providerId: number, feedId?: number): Promise<FieldMappingRow[]> {
  const result = await query(
    `SELECT * FROM field_mappings
     WHERE is_active = true
       AND (
         (provider_id = $1 AND feed_id IS NULL)
         OR (feed_id = $2)
       )
     ORDER BY feed_id NULLS LAST`,
    [providerId, feedId || null]
  );

  // Feed-level overrides provider-level
  const mapped = new Map<string, FieldMappingRow>();
  // First add provider-level (will be overridden)
  for (const m of result.rows.filter(r => r.feed_id === null)) {
    mapped.set(m.source_field, m);
  }
  // Then feed-level overrides
  for (const m of result.rows.filter(r => r.feed_id !== null)) {
    mapped.set(m.source_field, m);
  }
  return Array.from(mapped.values());
}

export function applyMappings(raw: RawProduct, mappings: FieldMappingRow[]): Record<string, string> {
  const result: Record<string, string> = {};

  for (const mapping of mappings) {
    const sourceValue = raw[mapping.source_field];
    if (sourceValue == null || sourceValue === '') continue;

    let value = String(sourceValue);

    if (mapping.transform && TRANSFORMS[mapping.transform]) {
      try {
        value = TRANSFORMS[mapping.transform](value, mapping.transform_args);
      } catch {
        // Skip transform errors, keep raw value
      }
    }

    result[mapping.target_field] = value;
  }

  return result;
}

export function normalizeProduct(mapped: Record<string, string>, externalId?: string): NormalizedProduct | null {
  const name = mapped.name?.trim();
  const brand = mapped.brand?.trim();
  const price = parseFloat(mapped.price || '0');

  if (!name || !brand || price <= 0) return null;
  if (!mapped.product_url) return null;

  const brandNorm = normalizeBrand(brand).toLowerCase();
  const modelRaw = mapped.model || normalizeModel(name, brand);
  const modelNorm = modelRaw.toLowerCase().replace(/[^\w\s]/g, '').replace(/\s+/g, ' ').trim();
  const colorNorm = mapped.colorway?.toLowerCase().trim() || undefined;

  const product: NormalizedProduct = {
    name,
    brand: normalizeBrand(brand),
    model: modelRaw || undefined,
    colorway: mapped.colorway || undefined,
    ean: mapped.ean && mapped.ean.length >= 8 ? mapped.ean : undefined,
    sku: mapped.sku || undefined,
    price,
    original_price: mapped.original_price ? parseFloat(mapped.original_price) || undefined : undefined,
    currency: mapped.currency || 'EUR',
    product_url: mapped.product_url,
    image_url: mapped.image_url || undefined,
    description: mapped.description || undefined,
    category: mapped.category || undefined,
    size: mapped.size || undefined,
    in_stock: mapped.in_stock !== 'false',
    brand_normalized: brandNorm,
    model_normalized: modelNorm || undefined,
    colorway_normalized: colorNorm,
    external_id: externalId || mapped.sku || undefined,
  };

  product.match_fingerprint = generateFingerprint(product);
  return product;
}
