/**
 * Formex.ch web crawler service
 * Scrapes panel/material data and prices from the Formex catalog.
 * Authenticates with a Formex account to access pricing info.
 */
import * as cheerio from 'cheerio';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import pool, { run } from '../db/index.js';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const BASE_URL = 'https://www.formex.ch';
const ASSORTIMENT_URL = `${BASE_URL}/fr/assortiment`;
const CRAWL_DELAY = parseInt(process.env.FORMEX_CRAWL_DELAY_MS || '500', 10);

const IMAGES_DIR = path.join(__dirname, '..', '..', 'client', 'public', 'materials');

if (!fs.existsSync(IMAGES_DIR)) {
  fs.mkdirSync(IMAGES_DIR, { recursive: true });
}

function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

// ---------------------------------------------------------------------------
// Session / cookie management
// ---------------------------------------------------------------------------

class FormexSession {
  constructor() {
    this.cookies = new Map();
    this.loggedIn = false;
  }

  /** Merge Set-Cookie headers into the jar */
  captureCookies(response) {
    let rawCookies = [];

    // Preferred: getSetCookie() returns individual Set-Cookie headers (Node 19.7+)
    if (typeof response.headers.getSetCookie === 'function') {
      rawCookies = response.headers.getSetCookie();
    } else {
      // Fallback: get the raw header string. Multiple Set-Cookie values are
      // joined with ", " by the fetch API. Split carefully: cookies never
      // start with a space after the comma, so split on ", " followed by a
      // token and "=" to re-separate them.
      const raw = response.headers.get('set-cookie');
      if (raw) {
        rawCookies = raw.split(/,\s*(?=[A-Za-z0-9_\-]+=)/);
      }
    }

    for (const header of rawCookies) {
      const [pair] = header.split(';');
      const eqIdx = pair.indexOf('=');
      if (eqIdx > 0) {
        this.cookies.set(pair.slice(0, eqIdx).trim(), pair.slice(eqIdx + 1).trim());
      }
    }
  }

  cookieHeader() {
    return [...this.cookies.entries()].map(([k, v]) => `${k}=${v}`).join('; ');
  }

  /** Common headers sent with every request */
  headers(extra = {}) {
    return {
      'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36',
      'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
      'Accept-Language': 'fr-CH,fr;q=0.9,en;q=0.5',
      ...(this.cookies.size > 0 ? { 'Cookie': this.cookieHeader() } : {}),
      ...extra,
    };
  }

  /**
   * Log in to Formex.ch via the CoreShop login form.
   *
   * Form at /fr/shop/login POSTs to /fr/shop/login-check with fields:
   *   _username, _password, _remember_me (optional)
   */
  async login() {
    const email = process.env.FORMEX_EMAIL;
    const password = process.env.FORMEX_PASSWORD;
    if (!email || !password) {
      console.warn('FORMEX_EMAIL / FORMEX_PASSWORD not set – running without auth (no prices)');
      return false;
    }

    try {
      const loginPageUrl = `${BASE_URL}/fr/shop/login`;
      const loginCheckUrl = `${BASE_URL}/fr/shop/login-check`;

      // 1. GET the login page to pick up the session cookie
      const getRes = await fetch(loginPageUrl, {
        headers: this.headers(),
        redirect: 'manual',
      });
      this.captureCookies(getRes);

      // Follow any initial redirect (e.g. to set session)
      if ([301, 302, 303].includes(getRes.status)) {
        const loc = getRes.headers.get('location');
        if (loc) {
          const r = await fetch(loc.startsWith('http') ? loc : `${BASE_URL}${loc}`, {
            headers: this.headers(),
            redirect: 'manual',
          });
          this.captureCookies(r);
        }
      }

      // 2. POST credentials to /fr/shop/login-check
      const formData = new URLSearchParams();
      formData.set('_username', email);
      formData.set('_password', password);
      formData.set('_remember_me', '1');

      const postRes = await fetch(loginCheckUrl, {
        method: 'POST',
        headers: this.headers({
          'Content-Type': 'application/x-www-form-urlencoded',
          'Referer': loginPageUrl,
          'Origin': BASE_URL,
        }),
        body: formData.toString(),
        redirect: 'manual',
      });
      this.captureCookies(postRes);

      // 3. Follow the redirect chain after login (CoreShop typically redirects 302)
      let location = postRes.headers.get('location') || '';
      let hops = 0;
      while (location && hops < 5) {
        const redirectUrl = location.startsWith('http') ? location : `${BASE_URL}${location}`;
        const redirectRes = await fetch(redirectUrl, {
          headers: this.headers(),
          redirect: 'manual',
        });
        this.captureCookies(redirectRes);
        location = redirectRes.headers.get('location') || '';
        hops++;

        // If we're redirected back to the login page, credentials were wrong
        if (redirectUrl.includes('/shop/login')) {
          console.error('Formex login failed – redirected back to login page');
          return false;
        }
      }

      // 4. Verify by fetching the customer profile
      const checkRes = await fetch(`${BASE_URL}/fr/shop/customer/profile`, {
        headers: this.headers(),
        redirect: 'manual',
      });
      this.captureCookies(checkRes);

      const checkLocation = checkRes.headers.get('location') || '';
      if (checkRes.status >= 300 && checkLocation.includes('login')) {
        console.error('Formex login failed – profile redirected to login');
        return false;
      }

      // Extra verification: fetch the profile page with redirect:follow and
      // check if we end up on an actual profile (contains the user email) or
      // a login page.
      const profileRes = await fetch(`${BASE_URL}/fr/shop/customer/profile`, {
        headers: this.headers(),
        redirect: 'follow',
      });
      this.captureCookies(profileRes);
      const profileHtml = await profileRes.text();
      const profileUrl = profileRes.url || '';

      if (profileUrl.includes('login') || profileHtml.includes('_username')) {
        console.error('Formex login failed – profile page is actually login page');
        console.error(`  Cookies captured: ${this.cookies.size} (${[...this.cookies.keys()].join(', ')})`);
        return false;
      }

      this.loggedIn = true;
      console.log(`Formex login successful (${this.cookies.size} cookies: ${[...this.cookies.keys()].join(', ')})`);
      return true;
    } catch (err) {
      console.error('Formex login error:', err.message);
      return false;
    }
  }
}

/** Module-level session, re-created each sync run */
let session = new FormexSession();

// ---------------------------------------------------------------------------
// HTTP helpers (use session cookies)
// ---------------------------------------------------------------------------

async function fetchPage(url) {
  const response = await fetch(url, {
    headers: session.headers(),
    redirect: 'follow',
  });
  session.captureCookies(response);

  if (!response.ok) {
    throw new Error(`Failed to fetch ${url}: ${response.status}`);
  }
  return response.text();
}

async function downloadImage(url, filename) {
  try {
    if (!url) return null;
    const fullUrl = url.startsWith('http') ? url : `${BASE_URL}${url}`;

    const response = await fetch(fullUrl, {
      headers: session.headers({ 'Accept': 'image/*' }),
    });
    session.captureCookies(response);

    if (!response.ok) {
      console.warn(`Failed to download image ${fullUrl}: ${response.status}`);
      return null;
    }

    const contentType = response.headers.get('content-type') || '';
    let ext = '.jpg';
    if (contentType.includes('png')) ext = '.png';
    else if (contentType.includes('webp')) ext = '.webp';

    const finalFilename = filename.replace(/\.[^.]+$/, '') + ext;
    const filepath = path.join(IMAGES_DIR, finalFilename);

    const buffer = Buffer.from(await response.arrayBuffer());
    fs.writeFileSync(filepath, buffer);

    return `/materials/${finalFilename}`;
  } catch (err) {
    console.warn(`Error downloading image ${url}:`, err.message);
    return null;
  }
}

/**
 * Extract collection URLs from the assortiment page
 */
const SHOP_UTILITY_PATHS = [
  '/fr/shop/cart', '/fr/shop/login', '/fr/shop/register',
  '/fr/shop/watchlist', '/fr/shop/customer', '/fr/shop/checkout',
  '/fr/shop/search', '/fr/shop/special-inquiry',
];

async function getCollections() {
  const html = await fetchPage(ASSORTIMENT_URL);
  const $ = cheerio.load(html);
  const collections = [];
  const seenUrls = new Set();

  $('a[href*="/fr/shop/"]').each((_, el) => {
    const href = $(el).attr('href');
    const name = $(el).text().trim();
    if (!href || !name) return;

    const isProduct = /-p\d+$/.test(href);
    const isUtility = SHOP_UTILITY_PATHS.some(u => href.startsWith(u) || href.includes(u));
    const isShopRoot = /\/fr\/shop\/?$/.test(href);

    if (!isProduct && !isUtility && !isShopRoot && href.includes('/fr/shop/')) {
      const fullUrl = href.startsWith('http') ? href : `${BASE_URL}${href}`;
      if (!seenUrls.has(fullUrl)) {
        seenUrls.add(fullUrl);
        collections.push({ name, url: fullUrl });
      }
    }
  });

  return collections;
}

/**
 * Convert a value+unit string to millimeters.
 * Supports "mm", "m", "m1", "cm". Defaults to mm if no unit given.
 */
function toMm(value, unit) {
  const v = parseFloat(value);
  if (isNaN(v)) return null;
  const u = (unit || 'mm').toLowerCase().replace(/\d/g, ''); // strip digits from units like "m1"
  if (u === 'm') return v * 1000;
  if (u === 'cm') return v * 10;
  return v; // mm or unknown
}

/**
 * Parse dimensions from strings like "4100mm x 1300mm x 0.8mm" or "100m x 43mm x 1mm".
 * Handles mixed units (m, cm, mm) and converts everything to mm.
 */
function parseDimensions(dimStr) {
  if (!dimStr) return { width: null, height: null, thickness: null };

  const m = dimStr.match(/([\d.]+)\s*(m[m1]?|cm)\s*x\s*([\d.]+)\s*(m[m1]?|cm)\s*x\s*([\d.]+)\s*(m[m1]?|cm)/i);
  if (m) {
    return {
      width: toMm(m[1], m[2]),
      height: toMm(m[3], m[4]),
      thickness: toMm(m[5], m[6])
    };
  }
  return { width: null, height: null, thickness: null };
}

/**
 * Extract the CHF price from text like "26.80 CHF", "67.30&nbsp;CHF", or "CHF 1'234.50"
 * Handles non-breaking spaces, smart quotes, thousand-separators, and varied whitespace.
 */
function parsePrice(text) {
  if (!text) return null;
  const clean = text
    .replace(/[\u00a0\u202f\u2009\u200a\u00ad]/g, ' ')
    .replace(/['\u2019\u2018\u2027]/g, '')
    .replace(/\s+/g, ' ')
    .trim();

  // Pattern: "123.45 CHF", "CHF 123.45", "1234,50 CHF", "1'234.50 CHF"
  const m = clean.match(/([\d]+(?:[.,][\d]+)?)\s*CHF|CHF\s*([\d]+(?:[.,][\d]+)?)/i);
  if (m) {
    const raw = (m[1] || m[2]).replace(',', '.');
    return parseFloat(raw);
  }

  // Last resort: bare number if the text is very short (e.g. just "456.00")
  if (clean.length < 20) {
    const bare = clean.match(/^([\d]+(?:[.,][\d]+)?)$/);
    if (bare) return parseFloat(bare[1].replace(',', '.'));
  }

  return null;
}

/**
 * Extract decor code from product name or page content
 * e.g., "Panneaux mélaminés Duplex Plus 10000 Tilia" -> "10000"
 */
function extractDecorCode(text) {
  // Try numeric 4-5 digit code first (most common)
  const numMatch = text.match(/\b(\d{4,5})\b/);
  if (numMatch) return numMatch[1];

  // Also try alphanumeric codes that appear in product names (e.g. "24MP")
  const alphaMatch = text.match(/\b(\d{2,5}[A-Z]{1,4})\b/i);
  return alphaMatch ? alphaMatch[1] : null;
}

/**
 * Map of common color keywords found in Formex product names to representative hex colors
 */
const COLOR_KEYWORDS = {
  // Whites and off-whites
  'blanc': '#f5f5f5', 'white': '#f5f5f5', 'weiss': '#f5f5f5', 'bianco': '#f5f5f5',
  'crème': '#fffdd0', 'cream': '#fffdd0', 'ivoire': '#fffff0', 'ivory': '#fffff0',
  'magnolia': '#f8f4f0', 'perle': '#eae0c8',
  // Greys
  'gris': '#909090', 'grey': '#909090', 'gray': '#909090', 'grau': '#909090',
  'anthracite': '#383838', 'anthrazit': '#383838', 'graphite': '#4a4a4a',
  'platine': '#e5e4e2', 'argent': '#c0c0c0', 'silver': '#c0c0c0',
  'titane': '#878681',
  // Blacks
  'noir': '#1a1a1a', 'black': '#1a1a1a', 'schwarz': '#1a1a1a',
  // Browns and woods
  'chêne': '#b5895b', 'oak': '#b5895b', 'eiche': '#b5895b',
  'noyer': '#6f4e37', 'walnut': '#6f4e37', 'nussbaum': '#6f4e37',
  'hêtre': '#c9a46c', 'beech': '#c9a46c', 'buche': '#c9a46c',
  'érable': '#d4b572', 'maple': '#d4b572', 'ahorn': '#d4b572',
  'cerisier': '#9c3b17', 'cherry': '#9c3b17', 'kirsch': '#9c3b17',
  'teck': '#b08444', 'teak': '#b08444',
  'pin': '#dcc8a0', 'pine': '#dcc8a0',
  'orme': '#a07850', 'elm': '#a07850',
  'bouleau': '#e0c99c', 'birch': '#e0c99c',
  'frêne': '#c8b88a', 'ash': '#c8b88a',
  'acacia': '#b89244',
  'wenge': '#4a3728', 'wengé': '#4a3728',
  'mélèze': '#c4a06c', 'larch': '#c4a06c',
  'aulne': '#b07c4c', 'alder': '#b07c4c',
  'brun': '#8b4513', 'brown': '#8b4513', 'braun': '#8b4513',
  'marron': '#6d3e1c', 'chocolat': '#4a2c14',
  'cognac': '#9f381d', 'caramel': '#a67040',
  'noisette': '#ab7746',
  // Beiges and sandstones
  'beige': '#d9c8a9', 'sable': '#c2b280', 'sand': '#c2b280',
  'sahara': '#c4a661', 'champagne': '#f7e7ce',
  'vanille': '#f3e5ab', 'vanilla': '#f3e5ab',
  'cappuccino': '#a0785a', 'moka': '#6f4e37',
  // Reds
  'rouge': '#cc3333', 'red': '#cc3333', 'rot': '#cc3333',
  'bordeaux': '#722f37', 'burgundy': '#722f37',
  'terracotta': '#c65d3e', 'rouille': '#b7410e',
  // Blues
  'bleu': '#4169e1', 'blue': '#4169e1', 'blau': '#4169e1',
  'marine': '#000080', 'navy': '#000080',
  'ciel': '#87ceeb', 'azur': '#007fff',
  'cobalt': '#0047ab', 'indigo': '#4b0082',
  // Greens
  'vert': '#4caf50', 'green': '#4caf50', 'grün': '#4caf50',
  'olive': '#808000', 'sauge': '#9caf88', 'sage': '#9caf88',
  'emeraude': '#50c878', 'mousse': '#738276',
  // Yellows and golds
  'jaune': '#f0c040', 'yellow': '#f0c040',
  'or': '#d4af37', 'gold': '#d4af37',
  'miel': '#cd8032', 'honey': '#cd8032',
  // Stone and concrete
  'béton': '#a0a0a0', 'beton': '#a0a0a0', 'concrete': '#a0a0a0',
  'pierre': '#b0a898', 'stone': '#b0a898', 'stein': '#b0a898',
  'ardoise': '#5a5a5a', 'slate': '#5a5a5a',
  'marbre': '#e0d8d0', 'marble': '#e0d8d0',
  'granit': '#808080', 'granite': '#808080',
  'travertin': '#d1c6b0',
  // Others
  'rouille': '#b7410e', 'rust': '#b7410e',
  'cuivre': '#b87333', 'copper': '#b87333',
  'linen': '#e0d5c0', 'lin': '#e0d5c0',
  'taupe': '#8b7d6b',
  'mauve': '#e0b0ff', 'lilas': '#c8a2c8',
  'rose': '#ffc0cb', 'pink': '#ffc0cb', 'rosa': '#ffc0cb'
};

/**
 * Extract dominant color from product name by matching color keywords
 * @param {string} name - Product name
 * @returns {string|null} - Hex color code or null
 */
function extractDominantColor(name) {
  if (!name) return null;
  const lower = name.toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g, '');
  const nameLower = name.toLowerCase();
  
  // Try exact keyword matching (longest match first for compound words)
  const sortedKeywords = Object.entries(COLOR_KEYWORDS)
    .sort((a, b) => b[0].length - a[0].length);
  
  for (const [keyword, hex] of sortedKeywords) {
    // Check the raw name (preserving accents) and the normalized name
    if (nameLower.includes(keyword) || lower.includes(keyword.normalize('NFD').replace(/[\u0300-\u036f]/g, ''))) {
      return hex;
    }
  }
  return null;
}

/**
 * Get product details from a product detail page.
 * Selectors are based on the actual Formex.ch Pimcore/CoreShop markup.
 */
async function getProductDetails(productUrl) {
  try {
    const html = await fetchPage(productUrl);
    const $ = cheerio.load(html);

    // --- Product name ---
    const name = $('h1.product-detail__title').first().text().trim()
              || $('h1').first().text().trim();

    // --- Decor code & article number from .product-detail__identifier-item ---
    let decorCode = null;
    let articleNumber = null;
    $('.product-detail__identifier-item span').each((_, el) => {
      const text = $(el).text().trim();
      const decorMatch = text.match(/N°\s*du\s*d[ée]cor\s*:\s*(\w+)/i);
      if (decorMatch) decorCode = decorMatch[1];
      const artMatch = text.match(/No\.\s*art\.\s*:\s*(\w+)/i);
      if (artMatch) articleNumber = artMatch[1];
    });
    if (!decorCode) decorCode = extractDecorCode(name);

    // --- Price (multiple fallback strategies) ---
    let priceCHF = null;
    let priceUnit = null;

    // Strategy 1: .price-new element (most common)
    const priceNewEl = $('.price-new').first();
    if (priceNewEl.length) {
      priceCHF = parsePrice(priceNewEl.text());
    }

    // Strategy 2: search within .product-meta for any CHF amount
    if (priceCHF === null) {
      const metaText = $('.product-meta').first().text();
      priceCHF = parsePrice(metaText);
    }

    // Strategy 3: look for h5 with font-weight-bold containing CHF
    if (priceCHF === null) {
      $('h5.font-weight-bold').each((_, el) => {
        const t = $(el).text();
        if (t.includes('CHF')) {
          priceCHF = parsePrice(t);
          if (priceCHF !== null) return false;
        }
      });
    }

    // Strategy 4: any element containing "CHF"
    if (priceCHF === null) {
      $('span, div, p, h5, h4, h3, strong').each((_, el) => {
        const t = $(el).text().trim();
        if (t.includes('CHF') && t.length < 60) {
          const p = parsePrice(t);
          if (p !== null && p > 0) {
            priceCHF = p;
            return false;
          }
        }
      });
    }

    // Strategy 5: scan the full page text for any number+CHF pattern
    if (priceCHF === null) {
      const fullText = $.text().replace(/\s+/g, ' ');
      const allPrices = [...fullText.matchAll(/([\d]+(?:[.,][\d]+)?)\s*(?:\u00a0\s*)?CHF/gi)];
      if (allPrices.length > 0) {
        priceCHF = parseFloat(allPrices[0][1].replace(',', '.'));
      }
    }

    const priceHeadEl = $('.price-head').first();
    if (priceHeadEl.length) {
      priceUnit = priceHeadEl.text().trim();
    }

    // --- Stock ---
    const stockFlag = $('.stock-flag').first().text().trim() || null;
    const inStock = stockFlag ? stockFlag.toLowerCase().includes('stock') : null;

    // --- Specifications from the sidebar table ---
    const specs = {};
    $('table.table-striped tbody tr').each((_, tr) => {
      const key = $(tr).find('th').text().trim().toLowerCase();
      const val = $(tr).find('td').text().trim();
      if (key && val) specs[key] = val;
    });

    // --- Category from breadcrumbs or specs ---
    let category = specs['catégorie de produits'] || specs['produktkategorie'] || null;
    if (!category) {
      const crumbs = $('.breadcrumbs a');
      if (crumbs.length >= 2) {
        category = $(crumbs.eq(crumbs.length - 1)).text().trim();
      }
    }

    // --- Main product image (from the slider) ---
    let imageUrl = null;
    const mainPicSource = $('.product-images picture source').first();
    if (mainPicSource.length) {
      const srcset = mainPicSource.attr('srcset') || '';
      const firstSrc = srcset.split(',')[0]?.split(' ')[0]?.trim();
      if (firstSrc) {
        imageUrl = firstSrc.startsWith('http') ? firstSrc : `${BASE_URL}${firstSrc}`;
      }
    }
    if (!imageUrl) {
      $('.product-images img').each((_, el) => {
        const src = $(el).attr('src') || '';
        if (src.includes('/dekorbilder/') || src.includes('/sortiment/')) {
          imageUrl = src.startsWith('http') ? src : `${BASE_URL}${src}`;
          return false;
        }
      });
    }

    // --- Dimension variants from .variant-type-AttributeSize ---
    const dimensionVariants = [];
    const seenDimensions = new Set();
    const variantEntries = [];
    $('.variant-type-AttributeSize a.btn, .variant-type-AttributeSize button.btn').each((_, el) => {
      const text = $(el).text().trim();
      const dim = parseDimensions(text);
      if (dim.width !== null) {
        const key = `${dim.width}-${dim.height}-${dim.thickness}`;
        if (!seenDimensions.has(key)) {
          seenDimensions.add(key);
          const isActive = $(el).hasClass('active');
          const href = $(el).attr('href');
          variantEntries.push({
            ...dim,
            price_chf: isActive ? priceCHF : null,
            url: href ? (href.startsWith('http') ? href : `${BASE_URL}${href}`) : null,
            isActive,
          });
        }
      }
    });

    // Fetch individual prices for variants that don't have one yet
    for (const variant of variantEntries) {
      if (variant.price_chf === null && variant.url) {
        try {
          await sleep(CRAWL_DELAY);
          const vHtml = await fetchPage(variant.url);
          const $v = cheerio.load(vHtml);
          const vpEl = $v('.price-new').first();
          if (vpEl.length) variant.price_chf = parsePrice(vpEl.text());
          if (variant.price_chf === null) {
            $v('span, h5, div').each((_, el) => {
              const t = $v(el).text().trim();
              if (t.includes('CHF') && t.length < 60) {
                const p = parsePrice(t);
                if (p !== null && p > 0) { variant.price_chf = p; return false; }
              }
            });
          }
          if (!priceUnit) {
            const vpHead = $v('.price-head').first();
            if (vpHead.length) priceUnit = vpHead.text().trim();
          }
        } catch (e) {
          console.warn(`  [price] Failed fetching variant price: ${e.message}`);
        }
      }
      dimensionVariants.push({
        width: variant.width,
        height: variant.height,
        thickness: variant.thickness,
        price_chf: variant.price_chf,
        url: variant.url,
      });
    }

    // If main price is still null, use the active variant's price (or the first variant with a price)
    if (priceCHF === null && dimensionVariants.length > 0) {
      const activeVar = dimensionVariants.find(v => variantEntries.find(e => e.url === v.url && e.isActive));
      priceCHF = activeVar?.price_chf ?? dimensionVariants.find(v => v.price_chf != null)?.price_chf ?? null;
    }

    if (priceCHF === null) {
      const bodyText = $.text().toLowerCase();
      const isOnRequest = bodyText.includes('sur demande') || bodyText.includes('auf anfrage');
      if (isOnRequest) {
        console.log(`  [price] Price on request at ${productUrl}`);
      } else {
        console.warn(`  [price] No price found at ${productUrl}`);
      }
    }

    // Fallback: parse specs table for dimensions if no variant buttons
    let dimensions = dimensionVariants[0] || { width: null, height: null, thickness: null };
    if (!dimensions.width && (specs['longueur'] || specs['largeur'])) {
      const w = parseFloat(specs['longueur']?.replace(/[^\d.]/g, '')) || null;
      const h = parseFloat(specs['largeur']?.replace(/[^\d.]/g, '')) || null;
      const t = parseFloat((specs['épaisseur'] || specs['epaisseur'])?.replace(/[^\d.]/g, '')) || null;
      dimensions = { width: w, height: h, thickness: t };
    }

    // --- Color / decor variants from .variant-type-collection-decor ---
    const colorVariants = [];
    const seenVariants = new Set();
    const currentSlug = productUrl.split('/').pop();
    $('.variant-type-collection-decor a[href*="/fr/shop/"]').each((_, el) => {
      const href = $(el).attr('href') || '';
      if (!href.match(/-p\d+$/)) return;

      const slug = href.split('/').pop();
      if (slug === currentSlug) return;

      const variantUrl = href.startsWith('http') ? href : `${BASE_URL}${href}`;
      const caption = $(el).find('figcaption').text().trim();
      const variantCode = extractDecorCode(href) || extractDecorCode(caption);
      if (!variantCode || seenVariants.has(variantCode)) return;
      seenVariants.add(variantCode);

      let variantImage = null;
      const img = $(el).find('img').first();
      if (img.length) {
        variantImage = img.attr('src') || img.attr('data-src');
        if (variantImage && !variantImage.startsWith('http')) {
          variantImage = `${BASE_URL}${variantImage}`;
        }
      }

      colorVariants.push({
        code: variantCode,
        name: caption || variantCode,
        url: variantUrl,
        imageUrl: variantImage,
      });
    });

    // --- Download images locally ---
    let localImageUrl = null;
    if (imageUrl && decorCode) {
      localImageUrl = await downloadImage(imageUrl, `${decorCode}.jpg`);
    }
    for (const variant of colorVariants) {
      if (variant.imageUrl && variant.code) {
        const localPath = await downloadImage(variant.imageUrl, `${variant.code}_thumb.jpg`);
        if (localPath) variant.imageUrl = localPath;
      }
    }

    const dominantColor = extractDominantColor(name);

    return {
      name,
      decorCode,
      articleNumber,
      dimensions,
      dimensionVariants,
      colorVariants,
      category,
      imageUrl: localImageUrl || imageUrl,
      dominantColor,
      priceCHF,
      priceUnit,
      inStock,
    };
  } catch (err) {
    console.error(`Error fetching product details from ${productUrl}:`, err.message);
    return null;
  }
}

/**
 * Check if a material was already synced today with all critical data (name, price, image).
 * Returns true if the product can be skipped.
 */
async function isAlreadySyncedToday(formexUrl) {
  try {
    const { rows } = await pool.query(
      `SELECT id, name, price_chf, image_url, last_synced_at
       FROM formex_materials WHERE formex_url = ?`,
      [formexUrl]
    );
    if (rows.length === 0) return false;

    const mat = rows[0];
    if (!mat.last_synced_at) return false;

    const syncedDate = mat.last_synced_at.slice(0, 10); // "YYYY-MM-DD"
    const today = new Date().toISOString().slice(0, 10);
    if (syncedDate !== today) return false;

    // Synced today — skip only if critical fields are present
    return !!(mat.name && mat.price_chf != null && mat.image_url);
  } catch {
    return false;
  }
}

/**
 * Get products from a collection page
 * @param {string} collectionUrl 
 * @param {string} collectionName 
 * @param {Function} emit - Optional emitter for progress events
 */
async function getProductsFromCollection(collectionUrl, collectionName, emit = () => {}, force = false) {
  const products = [];
  const productLinks = new Set();
  const visitedPages = new Set();

  // Crawl all paginated pages of this collection
  let pageUrl = collectionUrl;
  while (pageUrl && !visitedPages.has(pageUrl)) {
    visitedPages.add(pageUrl);
    const html = await fetchPage(pageUrl);
    const $ = cheerio.load(html);

    $('a[href*="/fr/shop/"]').each((_, el) => {
      const href = $(el).attr('href');
      if (href && /-p\d+$/.test(href)) {
        const fullUrl = href.startsWith('http') ? href : `${BASE_URL}${href}`;
        productLinks.add(fullUrl);
      }
    });

    // Look for a "next page" link in pagination
    let nextPage = null;
    $('a[rel="next"], .pagination a.next, .pagination li.next a, a.page-link[aria-label="Next"]').each((_, el) => {
      const href = $(el).attr('href');
      if (href) {
        nextPage = href.startsWith('http') ? href : `${BASE_URL}${href}`;
      }
    });
    if (!nextPage) {
      // Fallback: look for numbered pagination links with ?page= or /page/
      $('a[href*="page="], a[href*="/page/"]').each((_, el) => {
        const href = $(el).attr('href');
        if (href) {
          const full = href.startsWith('http') ? href : `${BASE_URL}${href}`;
          if (!visitedPages.has(full)) {
            nextPage = nextPage || full;
          }
        }
      });
    }

    if (nextPage && !visitedPages.has(nextPage)) {
      await sleep(CRAWL_DELAY);
      pageUrl = nextPage;
    } else {
      pageUrl = null;
    }
  }
  
  const productCount = productLinks.size;
  console.log(`  Found ${productCount} products in ${collectionName}`);
  emit({ type: 'log', message: `  ${productCount} produits trouvés dans ${collectionName}` });
  
  let productIndex = 0;
  let skippedCount = 0;
  for (const productUrl of productLinks) {
    productIndex++;

    // Skip products already fully synced today (unless force override)
    const canSkip = !force && await isAlreadySyncedToday(productUrl);
    if (canSkip) {
      skippedCount++;
      emit({ type: 'log', message: `    [${productIndex}/${productCount}] (déjà à jour, ignoré)` });
      continue;
    }

    await sleep(CRAWL_DELAY);
    const details = await getProductDetails(productUrl);
    
    if (details && details.decorCode) {
      const priceLabel = details.priceCHF != null
        ? ` ${details.priceCHF} CHF`
        : ' (pas de prix)';
      products.push({
        formex_code: details.decorCode,
        article_number: details.articleNumber,
        name: details.name,
        collection: collectionName,
        category: details.category || collectionName,
        width_mm: details.dimensions.width,
        height_mm: details.dimensions.height,
        thickness_mm: details.dimensions.thickness,
        price_chf: details.priceCHF,
        price_unit: details.priceUnit,
        image_url: details.imageUrl,
        formex_url: productUrl,
        dominant_color: details.dominantColor,
        in_stock: details.inStock,
        dimensionVariants: details.dimensionVariants || [],
        colorVariants: details.colorVariants || []
      });
      const dims = details.dimensionVariants || [];
      const varCount = details.colorVariants?.length || 0;
      const dimPrices = dims.map(d => {
        const size = `${d.width}x${d.height}x${d.thickness}`;
        return d.price_chf != null ? `${size}=${d.price_chf} CHF` : `${size}=?`;
      }).join(', ');
      const priceInfo = dims.length > 0 ? ` [${dimPrices}]` : priceLabel;
      emit({ type: 'log', message: `    [${productIndex}/${productCount}] ${details.name}${priceInfo} (${varCount} couleurs)` });
    }
  }

  if (skippedCount > 0) {
    emit({ type: 'log', message: `  ${skippedCount}/${productCount} produits ignorés (déjà à jour)` });
  }
  
  return products;
}

/**
 * Upsert a material into the database with prices and enhanced data
 */
async function upsertMaterial(material) {
  const { rows } = await pool.query(
    'SELECT id FROM formex_materials WHERE formex_code = ?',
    [material.formex_code]
  );

  let materialId;

  if (rows.length > 0) {
    materialId = rows[0].id;
    await run(
      `UPDATE formex_materials
       SET name = ?, collection = ?, category = ?, width_mm = ?, height_mm = ?, thickness_mm = ?,
           price_chf = COALESCE(?, price_chf),
           price_unit = COALESCE(?, price_unit),
           image_url = ?, formex_url = ?,
           dominant_color = COALESCE(?, dominant_color),
           last_synced_at = datetime('now')
       WHERE formex_code = ?`,
      [material.name, material.collection, material.category || null,
       material.width_mm, material.height_mm, material.thickness_mm,
       material.price_chf ?? null,
       material.price_unit || null,
       material.image_url || null, material.formex_url,
       material.dominant_color || null, material.formex_code]
    );
  } else {
    await run(
      `INSERT INTO formex_materials
       (formex_code, name, collection, category, width_mm, height_mm, thickness_mm, price_chf, price_unit, image_url, formex_url, dominant_color)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [material.formex_code, material.name, material.collection, material.category || null,
       material.width_mm, material.height_mm, material.thickness_mm,
       material.price_chf ?? null,
       material.price_unit || null,
       material.image_url || null, material.formex_url, material.dominant_color || null]
    );

    const inserted = await pool.query(
      'SELECT id FROM formex_materials WHERE formex_code = ?',
      [material.formex_code]
    );
    materialId = inserted.rows[0]?.id;
  }

  // Dimension variants (with per-dimension price)
  if (materialId && material.dimensionVariants && material.dimensionVariants.length > 0) {
    await run('DELETE FROM formex_dimensions WHERE material_id = ?', [materialId]);

    for (let i = 0; i < material.dimensionVariants.length; i++) {
      const dim = material.dimensionVariants[i];
      await run(
        `INSERT INTO formex_dimensions (material_id, width_mm, height_mm, thickness_mm, price_chf, is_default)
         VALUES (?, ?, ?, ?, ?, ?)`,
        [materialId, dim.width, dim.height, dim.thickness, dim.price_chf ?? null, i === 0 ? 1 : 0]
      );
    }
  }

  // Color variants
  if (materialId && material.colorVariants && material.colorVariants.length > 0) {
    await run('DELETE FROM formex_variants WHERE material_id = ?', [materialId]);

    for (const variant of material.colorVariants) {
      await run(
        `INSERT INTO formex_variants (material_id, variant_code, variant_name, variant_url, image_url)
         VALUES (?, ?, ?, ?, ?)`,
        [materialId, variant.code, variant.name, variant.url || null, variant.imageUrl || null]
      );
    }
  }

  return materialId;
}

/**
 * Log sync result to database
 */
async function logSync(source, status, itemsCount, error = null) {
  await run(
    `INSERT INTO sync_logs (source, status, items_count, error) VALUES (?, ?, ?, ?)`,
    [source, status, itemsCount, error]
  );
}

/**
 * Main sync function - crawls Formex and updates database
 * @param {Function} emit - Optional emitter callback for SSE progress events
 *   Called with { type: 'log'|'progress'|'complete'|'error', ... }
 * @param {Object} options
 * @param {boolean} options.force - Skip the "already synced today" check
 */
export async function sync(emit = () => {}, { force = false } = {}) {
  console.log(`Starting Formex sync...${force ? ' (FORCE)' : ''}`);
  emit({ type: 'log', message: 'Démarrage de la synchronisation Formex...' });
  if (force) {
    emit({ type: 'log', message: 'Mode forcé – tous les produits seront resynchronisés' });
  }

  const startTime = Date.now();
  let totalProducts = 0;

  // Fresh session for each sync run
  session = new FormexSession();

  try {
    // Authenticate to get access to prices
    emit({ type: 'log', message: 'Connexion au compte Formex...' });
    const loggedIn = await session.login();
    if (loggedIn) {
      emit({ type: 'log', message: 'Connecté – les prix seront récupérés' });
    } else {
      emit({ type: 'log', message: 'Non connecté – synchronisation sans prix' });
    }

    console.log('Fetching collections...');
    emit({ type: 'log', message: 'Récupération des collections...' });
    const collections = await getCollections();
    const totalCollections = collections.length;
    console.log(`Found ${totalCollections} collections`);
    emit({ type: 'log', message: `${totalCollections} collections trouvées` });
    emit({ type: 'progress', current: 0, total: totalCollections, collection: '' });
    
    // Process each collection
    for (let i = 0; i < collections.length; i++) {
      const collection = collections[i];
      console.log(`Processing: ${collection.name}`);
      emit({ type: 'log', message: `Traitement: ${collection.name}` });
      emit({ type: 'progress', current: i, total: totalCollections, collection: collection.name });
      
      await sleep(CRAWL_DELAY);
      
      try {
        const products = await getProductsFromCollection(collection.url, collection.name, emit, force);
        
        // Upsert each product
        for (const product of products) {
          await upsertMaterial(product);
          totalProducts++;
        }
        
        emit({ type: 'log', message: `  ${products.length} produits enregistrés pour ${collection.name}` });
      } catch (err) {
        console.error(`  Error processing ${collection.name}:`, err.message);
        emit({ type: 'log', message: `  Erreur: ${err.message}` });
      }
    }
    
    const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
    console.log(`Sync complete: ${totalProducts} products in ${elapsed}s`);
    
    await logSync('formex', 'success', totalProducts);
    
    const result = {
      success: true,
      itemsCount: totalProducts,
      duration: elapsed
    };
    
    emit({ type: 'progress', current: totalCollections, total: totalCollections, collection: '' });
    emit({ type: 'complete', result });
    
    return result;
    
  } catch (err) {
    console.error('Sync failed:', err.message);
    await logSync('formex', 'error', totalProducts, err.message);
    
    const result = {
      success: false,
      error: err.message,
      itemsCount: totalProducts
    };
    
    emit({ type: 'error', error: err.message });
    
    return result;
  }
}

/**
 * Get sync logs from database
 */
export async function getSyncLogs(limit = 20) {
  const { rows } = await pool.query(
    'SELECT * FROM sync_logs ORDER BY created_at DESC LIMIT ?',
    [limit]
  );
  return rows;
}

/**
 * Get all scraped materials
 */
export async function getMaterials() {
  const { rows } = await pool.query(
    'SELECT * FROM formex_materials ORDER BY collection, name'
  );
  return rows;
}