server.ts

import express from 'express';
import path from 'path';
import crypto from 'crypto';
import { fileURLToPath } from 'url';
import dotenv from 'dotenv';

dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const CFG = {
  ps: {
    url: process.env.PS_URL || 'https://tousergo.com',
    adm: process.env.PS_ADM || '/admin_ps_t_fr/',
    key: process.env.PS_KEY || '',
  },
  odoo: {
    url: process.env.ODOO_URL || 'https://tousergo.eggs-solutions.fr',
    db: process.env.ODOO_DB || '',
    usr: process.env.ODOO_USR || '',
    pwd: process.env.ODOO_PWD || '',
  },
  crisp: {
    sid: process.env.CRISP_SID || '',
    auth: process.env.CRISP_AUTH || '',
  },
  tcx: {
    url: process.env.TCX_URL || 'https://tousergo.on3cx.fr:5001',
    key: process.env.TCX_KEY || '',
  },
};

// Phone variations helper
function phoneVariants(p: string): string[] {
  // Strip all non-digit characters except +
  const cleanWithPlus = p.replace(/[^\d+]/g, '');
  const digits = p.replace(/[^\d]/g, '');
  
  const variants = new Set<string>();
  
  if (digits) {
    variants.add(digits);
  }
  if (cleanWithPlus) {
    variants.add(cleanWithPlus);
  }

  // Get country code and national part
  let countryCode = getCountryCode(p);
  let national = digits;

  if (cleanWithPlus.startsWith('+')) {
    if (cleanWithPlus.startsWith('+33')) {
      countryCode = '33';
      national = cleanWithPlus.slice(3);
    } else if (cleanWithPlus.startsWith('+32')) {
      countryCode = '32';
      national = cleanWithPlus.slice(3);
    } else if (cleanWithPlus.startsWith('+41')) {
      countryCode = '41';
      national = cleanWithPlus.slice(3);
    } else if (cleanWithPlus.startsWith('+352')) {
      countryCode = '352';
      national = cleanWithPlus.slice(4);
    } else {
      const match = cleanWithPlus.match(/^\+(\d{1,4})(.*)$/);
      if (match) {
        countryCode = match[1];
        national = match[2];
      }
    }
  } else if (digits.startsWith('0033')) {
    countryCode = '33';
    national = digits.slice(4);
  } else if (digits.startsWith('0032')) {
    countryCode = '32';
    national = digits.slice(4);
  } else if (digits.startsWith('0041')) {
    countryCode = '41';
    national = digits.slice(4);
  } else if (digits.startsWith('00352')) {
    countryCode = '352';
    national = digits.slice(5);
  } else if (digits.startsWith('0')) {
    national = digits.slice(1);
  }

  // Ensure national part doesn't have leading zero
  if (national.startsWith('0')) {
    national = national.slice(1);
  }

  if (national.length >= 5) {
    variants.add(national);
    variants.add('0' + national);
    variants.add('+' + countryCode + national);
    variants.add('00' + countryCode + national);

    // Spaced / punctuated variants based on detected country
    if (countryCode === '33' && national.length === 9) {
      // French spacing: e.g. 06 12 34 56 78
      const parts = [
        national.slice(0, 1),
        national.slice(1, 3),
        national.slice(3, 5),
        national.slice(5, 7),
        national.slice(7, 9)
      ];
      variants.add('0' + parts[0] + ' ' + parts[1] + ' ' + parts[2] + ' ' + parts[3] + ' ' + parts[4]);
      variants.add('0' + parts[0] + '.' + parts[1] + '.' + parts[2] + '.' + parts[3] + '.' + parts[4]);
      variants.add('0' + parts[0] + '-' + parts[1] + '-' + parts[2] + '-' + parts[3] + '-' + parts[4]);
      variants.add('+' + countryCode + ' ' + parts[0] + ' ' + parts[1] + ' ' + parts[2] + ' ' + parts[3] + ' ' + parts[4]);
      variants.add('+' + countryCode + parts[0] + ' ' + parts[1] + ' ' + parts[2] + ' ' + parts[3] + ' ' + parts[4]);
    } else if (countryCode === '32' && (national.length === 9 || national.startsWith('45') || national.startsWith('46') || national.startsWith('47') || national.startsWith('48') || national.startsWith('49'))) {
      // Belgian mobile spacing: e.g. 0475 12 34 56
      const p1 = national.slice(0, 3);
      const p2 = national.slice(3, 5);
      const p3 = national.slice(5, 7);
      const p4 = national.slice(7, 9);
      
      variants.add('0' + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
      variants.add('0' + p1 + '/' + p2 + '.' + p3 + '.' + p4);
      variants.add('+' + countryCode + ' ' + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
      variants.add('+' + countryCode + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
    } else if (countryCode === '32' && national.length === 8) {
      // Belgian landline spacing: e.g. 02 123 45 67
      const p1 = national.slice(0, 1);
      const p2 = national.slice(1, 4);
      const p3 = national.slice(4, 6);
      const p4 = national.slice(6, 8);
      
      variants.add('0' + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
      variants.add('+' + countryCode + ' ' + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
    } else if (countryCode === '41' && national.length === 9) {
      // Swiss spacing: e.g. 079 123 45 67
      const p1 = national.slice(0, 2);
      const p2 = national.slice(2, 5);
      const p3 = national.slice(5, 7);
      const p4 = national.slice(7, 9);
      
      variants.add('0' + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
      variants.add('+' + countryCode + ' ' + p1 + ' ' + p2 + ' ' + p3 + ' ' + p4);
    } else if (countryCode === '352' && national.length >= 6) {
      // Luxembourg spacing
      const p1 = national.slice(0, 3);
      const p2 = national.slice(3, 6);
      const p3 = national.slice(6).trim();
      const space3 = p3 ? ' ' + p3 : '';
      
      variants.add('0' + p1 + ' ' + p2 + space3);
      variants.add('+' + countryCode + ' ' + p1 + ' ' + p2 + space3);
      variants.add(p1 + ' ' + p2 + space3);
    }
  }

  return Array.from(variants);
}

// Helpers for international phone matching
function getCountryCode(phone: string): string {
  const clean = phone.replace(/[^\d+]/g, '');
  if (clean.startsWith('+33') || clean.startsWith('0033')) return '33';
  if (clean.startsWith('+32') || clean.startsWith('0032')) return '32';
  if (clean.startsWith('+41') || clean.startsWith('0041')) return '41';
  if (clean.startsWith('+352') || clean.startsWith('00352')) return '352';
  
  // If starting with 0, guess by length and prefix
  const digits = phone.replace(/[^\d]/g, '');
  if (digits.startsWith('0')) {
    if (digits.length === 10) {
      // Belgian mobile prefixes: 045, 046, 047, 048, 049
      if (digits.startsWith('045') || digits.startsWith('046') || digits.startsWith('047') || digits.startsWith('048') || digits.startsWith('049')) {
        return '32';
      }
    } else if (digits.length === 9) {
      // Belgian landlines have 9 digits
      return '32';
    }
  }
  
  return '33'; // Default to France
}

function toE164(p: string, defaultCountry = '33'): string {
  const digitsOnly = p.replace(/[^\d]/g, '');
  const cleanWithPlus = p.replace(/[^\d+]/g, '');
  if (cleanWithPlus.startsWith('+')) {
    return cleanWithPlus;
  }
  if (cleanWithPlus.startsWith('00')) {
    return '+' + cleanWithPlus.slice(2);
  }
  if (digitsOnly.startsWith('0')) {
    return '+' + defaultCountry + digitsOnly.slice(1);
  }
  return '+' + digitsOnly;
}

function phonesMatchInternationally(searchPhone: string, resultPhone: string, defaultCountry = '33'): boolean {
  if (!resultPhone) return false;
  const normSearch = toE164(searchPhone, defaultCountry);
  const normResult = toE164(resultPhone, defaultCountry);
  return normSearch === normResult;
}


// Timeout fetch wrapper
async function fetchWithTimeout(url: string, options: any = {}, timeoutMs = 12000) {
  const controller = new AbortController();
  const id = setTimeout(() => controller.abort(), timeoutMs);
  try {
    const response = await fetch(url, { ...options, signal: controller.signal });
    clearTimeout(id);
    return response;
  } catch (error: any) {
    clearTimeout(id);
    throw new Error(error.name === 'AbortError' ? `Timeout ${timeoutMs}ms` : error.message);
  }
}

// Odoo DB Finder
async function getOdooDb(): Promise<string> {
  if (CFG.odoo.db) return CFG.odoo.db;
  try {
    const r = await fetchWithTimeout(`${CFG.odoo.url}/web/database/list`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ jsonrpc: '2.0', method: 'call', id: 0, params: {} }),
    });
    const d: any = await r.json();
    if (d.result && Array.isArray(d.result) && d.result.length > 0) {
      return d.result[0];
    }
  } catch (e) {
    console.error('Failed to list Odoo databases:', e);
  }
  return new URL(CFG.odoo.url).hostname.split('.')[0];
}

// Odoo Search helper
async function searchOdoo(phone: string, email?: string, odooId?: string) {
  let sid = '';
  try {
    sid = await getOdooSessionId();
  } catch (e) {
    odooSessionId = '';
    sid = await getOdooSessionId();
  }

  let domain: any[] = [];
  if (odooId) {
    domain = [['id', '=', Number(odooId)]];
  } else if (email) {
    domain = [['email', '=', email.trim()]];
  } else {
    if (phone.includes('@')) {
      domain = [['email', '=', phone.trim()]];
    } else {
      const vv = phoneVariants(phone);
      const conds = vv.flatMap(v => [['phone', 'like', v], ['mobile', 'like', v]]);
      for (let i = 0; i < conds.length - 1; i++) {
        domain.push('|');
      }
      domain.push(...conds);
    }
  }

  let r = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      ...(sid ? { Cookie: `session_id=${sid}` } : {}),
    },
    body: JSON.stringify({
      jsonrpc: '2.0',
      method: 'call',
      id: 2,
      params: {
        model: 'res.partner',
        method: 'search_read',
        args: [domain],
        kwargs: {
          fields: ['id', 'name', 'phone', 'mobile', 'email', 'parent_id', 'city', 'street', 'zip', 'type', 'company_name', 'commercial_company_name'],
          limit: 20,
          context: {},
        },
      },
    }),
  });
  let d: any = await r.json();

  // Si la session en cache a expiré, on se reconnecte une seule fois et on reessaie
  if (d.error && /session|expired|auth/i.test(d.error.data?.message || d.error.message || '')) {
    odooSessionId = '';
    sid = await getOdooSessionId();
    r = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        ...(sid ? { Cookie: `session_id=${sid}` } : {}),
      },
      body: JSON.stringify({
        jsonrpc: '2.0',
        method: 'call',
        id: 2,
        params: {
          model: 'res.partner',
          method: 'search_read',
          args: [domain],
          kwargs: {
            fields: ['id', 'name', 'phone', 'mobile', 'email', 'parent_id', 'city', 'street', 'zip', 'type', 'company_name', 'commercial_company_name'],
            limit: 20,
            context: {},
          },
        },
      }),
    });
    d = await r.json();
  }

  if (d.error) {
    throw new Error(d.error.data?.message || d.error.message);
  }
  return d.result || [];
}

// Odoo helper to get related parent & sibling partner IDs
async function getRelatedPartnerIds(partnerIds: number[], sid: string, db: string): Promise<number[]> {
  const idsSet = new Set<number>(partnerIds);
  const rParents = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      ...(sid ? { Cookie: `session_id=${sid}` } : {}),
    },
    body: JSON.stringify({
      jsonrpc: '2.0',
      method: 'call',
      id: 5,
      params: {
        model: 'res.partner',
        method: 'search_read',
        args: [[['id', 'in', partnerIds]]],
        kwargs: {
          fields: ['id', 'parent_id'],
          context: {},
        },
      },
    }),
  }).catch(() => null);

  if (rParents) {
    const d: any = await rParents.json().catch(() => null);
    if (d?.result) {
      for (const p of d.result) {
        if (p.parent_id && Array.isArray(p.parent_id) && p.parent_id[0]) {
          idsSet.add(Number(p.parent_id[0]));
        }
      }
    }
  }

  const listWithParents = Array.from(idsSet);
  if (listWithParents.length === 0) return [];

  const rChildren = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      ...(sid ? { Cookie: `session_id=${sid}` } : {}),
    },
    body: JSON.stringify({
      jsonrpc: '2.0',
      method: 'call',
      id: 6,
      params: {
        model: 'res.partner',
        method: 'search_read',
        args: [[['parent_id', 'in', listWithParents]]],
        kwargs: {
          fields: ['id'],
          context: {},
        },
      },
    }),
  }).catch(() => null);

  if (rChildren) {
    const d: any = await rChildren.json().catch(() => null);
    if (d?.result) {
      for (const p of d.result) {
        if (p.id) idsSet.add(Number(p.id));
      }
    }
  }

  return Array.from(idsSet);
}

// Odoo helper to get the shipping address of the latest sale.order
async function getLatestOdooShippingAddress(partnerIds: number[]) {
  if (!partnerIds || partnerIds.length === 0) return null;
  try {
    let sid = '';
    try {
      sid = await getOdooSessionId();
    } catch (e) {
      odooSessionId = ''; // force re-auth
      sid = await getOdooSessionId();
    }

    const db = await getOdooDb();
    const expandedIds = await getRelatedPartnerIds(partnerIds, sid, db).catch(err => {
      console.error('getRelatedPartnerIds error:', err);
      return partnerIds;
    });

    const r = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        ...(sid ? { Cookie: `session_id=${sid}` } : {}),
      },
      body: JSON.stringify({
        jsonrpc: '2.0',
        method: 'call',
        id: 3,
        params: {
          model: 'sale.order',
          method: 'search_read',
          args: [[
            '|', '|',
            ['partner_id', 'in', expandedIds],
            ['partner_shipping_id', 'in', expandedIds],
            ['partner_invoice_id', 'in', expandedIds]
          ]],
          kwargs: {
            fields: ['id', 'name', 'partner_shipping_id', 'date_order', 'partner_id'],
            order: 'date_order desc, id desc',
            limit: 1,
            context: {},
          },
        },
      }),
    });
    const d: any = await r.json();
    if (d.error) {
      console.error('Odoo sale.order query error:', d.error);
      return null;
    }
    const orders = d.result || [];
    if (orders.length === 0) {
      return null;
    }
    const latestOrder = orders[0];
    const shipping = latestOrder.partner_shipping_id || latestOrder.partner_id;
    let shippingId = 0;
    if (shipping && Array.isArray(shipping) && shipping[0]) {
      shippingId = Number(shipping[0]);
    } else if (shipping && typeof shipping === 'number') {
      shippingId = shipping;
    }

    if (shippingId) {
      const r2 = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          ...(sid ? { Cookie: `session_id=${sid}` } : {}),
        },
        body: JSON.stringify({
          jsonrpc: '2.0',
          method: 'call',
          id: 4,
          params: {
            model: 'res.partner',
            method: 'search_read',
            args: [[['id', '=', shippingId]]],
            kwargs: {
              fields: ['id', 'name', 'street', 'street2', 'zip', 'city', 'parent_id', 'company_name', 'commercial_company_name'],
              limit: 1,
              context: {},
            },
          },
        }),
      });
      const d2: any = await r2.json();
      if (d2.error) {
        console.error('Odoo shipping partner query error:', d2.error);
        return null;
      }
      const partners = d2.result || [];
      if (partners.length > 0) {
        const part = partners[0];
        let latestCompany = '';
        if (part.company_name) {
          latestCompany = part.company_name.trim();
        } else if (part.commercial_company_name) {
          latestCompany = part.commercial_company_name.trim();
        } else if (part.parent_id && part.parent_id[1]) {
          latestCompany = part.parent_id[1].trim();
        } else if (part.name) {
          latestCompany = part.name.trim();
        }

        let streetVal = part.street ? String(part.street).trim() : '';
        const street2Val = part.street2 ? String(part.street2).trim() : '';
        if (street2Val) {
          streetVal = streetVal ? `${streetVal}\n${street2Val}` : street2Val;
        }

        return {
          id: part.id,
          street: streetVal,
          zip: part.zip ? String(part.zip).trim() : '',
          city: part.city ? String(part.city).trim() : '',
          company: latestCompany
        };
      }
    }
  } catch (err) {
    console.error('getLatestOdooShippingAddress error:', err);
  }
  return null;
}

let odooSessionId = '';
async function getOdooSessionId(): Promise<string> {
  if (odooSessionId) return odooSessionId;
  const db = await getOdooDb();
  const authR = await fetchWithTimeout(`${CFG.odoo.url}/web/session/authenticate`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      jsonrpc: '2.0',
      method: 'call',
      id: 1,
      params: { db, login: CFG.odoo.usr, password: CFG.odoo.pwd },
    }),
  });
  const authD: any = await authR.json();
  if (!authD.result?.uid) {
    throw new Error(`Auth Odoo échouée`);
  }
  const setCookie = authR.headers.get('set-cookie') || '';
  odooSessionId = setCookie.match(/session_id=([^;,\s]+)/)?.[1] || '';
  return odooSessionId;
}

async function getOdooStock(reference: string): Promise<{ total: number; details: any[] }> {
  if (!reference) return { total: 0, details: [] };
  try {
    let sid = '';
    try {
      sid = await getOdooSessionId();
    } catch (e) {
      odooSessionId = ''; // force re-auth
      sid = await getOdooSessionId();
    }

    const cleanRef = reference.trim();
    const r = await fetchWithTimeout(`${CFG.odoo.url}/web/dataset/call_kw`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        ...(sid ? { Cookie: `session_id=${sid}` } : {}),
      },
      body: JSON.stringify({
        jsonrpc: '2.0',
        method: 'call',
        id: 2,
        params: {
          model: 'product.product',
          method: 'search_read',
          args: [[['default_code', 'ilike', cleanRef]]],
          kwargs: {
            fields: ['id', 'name', 'default_code', 'qty_available', 'virtual_available'],
            limit: 50,
            context: {},
          },
        },
      }),
    });
    const d: any = await r.json();
    if (d.error) {
      console.error('Odoo product query error:', d.error);
      return { total: 0, details: [] };
    }
    const products = d.result || [];
    const matched = products.filter((p: any) => {
      const code = (p.default_code || '').trim().toLowerCase();
      const target = cleanRef.toLowerCase();
      return code === target || code.startsWith(target + '-') || code.startsWith(target + '_');
    });
    
    const finalProducts = matched.length > 0 ? matched : products;

    let total = 0;
    const details = finalProducts.map((p: any) => {
      const qty = Math.max(0, Math.floor(p.qty_available || 0));
      total += qty;
      return {
        id: p.id,
        name: p.name,
        code: p.default_code,
        qty: qty,
        virtual_qty: Math.max(0, Math.floor(p.virtual_available || 0))
      };
    });

    return { total, details };
  } catch (err) {
    console.error('Failed to fetch stock from Odoo for', reference, err);
    return { total: 0, details: [] };
  }
}

function parseTechnicalSheet(tableHtml: string): { name: string, value: string }[] {
  const result: { name: string, value: string }[] = [];
  const trRegex = /<tr>([\s\S]*?)<\/tr>/gi;
  let trMatch;
  while ((trMatch = trRegex.exec(tableHtml)) !== null) {
    const trContent = trMatch[1];
    const nameMatch = trContent.match(/<th[^>]*class="name"[^>]*>([\s\S]*?)<\/(?:th|td)>/i) || 
                      trContent.match(/<th[^>]*>([\s\S]*?)<\/th>/i) ||
                      trContent.match(/<td>([\s\S]*?)<\/td>/i);
    const valueMatch = trContent.match(/<td[^>]*class="value"[^>]*>([\s\S]*?)<\/td>/i) || 
                       trContent.match(/<td[^>]*>([\s\S]*?)<\/td>/i);
    if (nameMatch && valueMatch) {
      const name = nameMatch[1].replace(/<[^>]+>/g, '').replace(/&nbsp;/g, ' ').replace(/\s+/g, ' ').trim();
      const value = valueMatch[1].replace(/<[^>]+>/g, '').replace(/&nbsp;/g, ' ').replace(/\s+/g, ' ').trim();
      if (name && value) {
        result.push({ name, value });
      }
    }
  }
  return result;
}

// PrestaShop fetch helper
async function psGet(endpoint: string) {
  const sep = endpoint.includes('?') ? '&' : '?';
  const url = `${CFG.ps.url}/api/${endpoint}${sep}ws_key=${CFG.ps.key}`;
  const r = await fetchWithTimeout(url, { method: 'GET' });
  if (!r.ok) return null;
  const d: any = await r.json();
  return d?.errors ? null : d;
}

// Multilingual field resolver for PrestaShop Web Service
function resolveLangField(field: any): string {
  if (!field) return '';
  if (typeof field === 'string') return field;
  if (Array.isArray(field)) {
    const fr = field.find((l: any) => String(l.id) === '1' || String(l.id_lang) === '1');
    if (fr) return fr.value || fr.language || '';
    return field[0]?.value || '';
  }
  if (typeof field === 'object') {
    return field.value || '';
  }
  return String(field);
}

// Tax rules and rate cache
let taxCache: { [id_tax: string]: number } = {};
let taxGroupCache: { [id_group: string]: number } = {
  '1': 20.0,
  '2': 5.5,
  '3': 10.0,
  '4': 2.1,
};

async function getTaxRate(idTaxRulesGroup: string): Promise<number> {
  if (!idTaxRulesGroup || idTaxRulesGroup === '0') return 20.0;
  
  // Hardcoded mapping of PrestaShop Tax Rules Groups to French VAT rates.
  // This is required because the webservice API key lacks read permissions for 'tax_rules' and 'taxes'.
  const group = String(idTaxRulesGroup).trim();
  switch (group) {
    case '68':
    case '67':
      return 20.0; // Standard VAT (20%)
    case '66':
    case '65':
      return 10.0; // Intermediate VAT (10%)
    case '64':
    case '69':
      return 5.5;  // Reduced VAT (5.5%)
    default:
      return 20.0; // Default VAT (20%)
  }
}

async function enrichProduct(p: any, scrapedInfo?: { priceTTC: number; priceHT?: number }) {
  const id = p.id;
  const name = resolveLangField(p.name);
  const reference = (p.reference || '').trim();
  
  let taxRate = await getTaxRate(p.id_tax_rules_group);
  let priceHT = parseFloat(p.price || '0');
  let priceTTC = Math.round(priceHT * (1 + taxRate / 100) * 100) / 100;

  // Apply scraped live price from tousergo.com if available
  if (scrapedInfo) {
    if (scrapedInfo.priceTTC && scrapedInfo.priceTTC > 0) {
      priceTTC = scrapedInfo.priceTTC;
      priceHT = scrapedInfo.priceHT || Math.round((priceTTC / (1 + taxRate / 100)) * 100) / 100;
    }
  }

  // Fetch live Odoo stock in real-time
  let quantity = parseInt(p.quantity || '0', 10);
  if (reference) {
    try {
      const odooStock = await getOdooStock(reference);
      quantity = odooStock.total;
    } catch (err) {
      console.error(`Failed to fetch live Odoo stock for ${reference} in enrichProduct:`, err);
    }
  }

  // If the product has combinations and quantity is reported as 0, 
  // let's default to a virtual positive quantity if the product is active
  const hasCombinations = p.associations?.stock_availables && p.associations.stock_availables.some((s: any) => s.id_product_attribute && s.id_product_attribute !== '0');
  if (quantity === 0 && hasCombinations && p.active === '1') {
    quantity = 15; // Set a virtual positive quantity to indicate available stock in combinations
  }

  return {
    id,
    name,
    reference,
    priceHT,
    taxRate,
    priceTTC,
    quantity,
    id_default_image: p.id_default_image || '',
    link_rewrite: resolveLangField(p.link_rewrite),
    description: resolveLangField(p.description),
    description_short: resolveLangField(p.description_short),
    active: p.active === '1',
  };
}

// PrestaShop customer search
async function psSearch(emails: string[]) {
  const seen = new Set<number>();
  const out: any[] = [];
  await Promise.allSettled(
    emails.map(async email => {
      if (!email) return;
      const d = await psGet(`customers?filter[email]=[${encodeURIComponent(email)}]&display=[id,firstname,lastname,email,id_default_group]&output_format=JSON`);
      const customers = d?.customers || [];
      for (const c of customers) {
        const id = parseInt(c.id);
        if (!seen.has(id)) {
          seen.add(id);
          
          let groupName = "Client Standard";
          if (c.id_default_group) {
            try {
              const gData = await psGet(`groups/${c.id_default_group}?display=[name]&output_format=JSON`);
              if (gData && gData.group) {
                groupName = resolveLangField(gData.group.name) || "Client Standard";
              } else if (gData && gData.groups && gData.groups[0]) {
                groupName = resolveLangField(gData.groups[0].name) || "Client Standard";
              }
            } catch (err) {
              console.error('Failed to fetch group name from PrestaShop:', err);
            }
          }
          c.group_name = groupName;

          let phones: string[] = [];
          try {
            const addrData = await psGet(`addresses?filter[id_customer]=[${id}]&display=[phone,phone_mobile]&output_format=JSON`);
            const addrs = addrData?.addresses || [];
            const phoneSet = new Set<string>();
            for (const a of addrs) {
              if (a.phone) phoneSet.add(String(a.phone).trim());
              if (a.phone_mobile) phoneSet.add(String(a.phone_mobile).trim());
            }
            phones = Array.from(phoneSet).filter(Boolean);
          } catch (addrErr) {
            console.error(`Failed to fetch addresses for customer ${id}:`, addrErr);
          }
          c.phones = phones;
          if (phones.length > 0) {
            c.phone = phones[0];
          }

          out.push(c);
        }
      }
    })
  );
  return out;
}

function getNationalSuffix(phone: string): string {
  const clean = phone.replace(/[^\d]/g, '');
  
  // Known country codes in Western Europe
  const countryCodes = ['33', '32', '41', '352', '49', '44', '31'];
  for (const cc of countryCodes) {
    if (clean.startsWith(cc) && clean.length > cc.length + 4) {
      return clean.slice(cc.length);
    }
  }
  
  if (clean.startsWith('00')) {
    const withoutDoubleZero = clean.slice(2);
    for (const cc of countryCodes) {
      if (withoutDoubleZero.startsWith(cc) && withoutDoubleZero.length > cc.length + 4) {
        return withoutDoubleZero.slice(cc.length);
      }
    }
  }

  if (clean.startsWith('0') && clean.length > 1) {
    return clean.slice(1);
  }
  return clean;
}

async function psSearchByPhone(phone: string): Promise<any[]> {
  const suffix = getNationalSuffix(phone);
  if (!suffix || suffix.length < 7) return [];

  const seenCustomerIds = new Set<string>();
  const customers: any[] = [];

  try {
    const resPhone = await psGet(`addresses?filter[phone]=%[${encodeURIComponent(suffix)}]%&display=[id,id_customer,phone,phone_mobile]&output_format=JSON`);
    const addrs1 = resPhone?.addresses || [];
    
    const resMobile = await psGet(`addresses?filter[phone_mobile]=%[${encodeURIComponent(suffix)}]%&display=[id,id_customer,phone,phone_mobile]&output_format=JSON`);
    const addrs2 = resMobile?.addresses || [];

    const searchCountry = getCountryCode(phone);
    const allAddrs = [...addrs1, ...addrs2];

    for (const addr of allAddrs) {
      if (addr.id_customer && addr.id_customer !== '0') {
        const p1 = addr.phone ? String(addr.phone) : '';
        const p2 = addr.phone_mobile ? String(addr.phone_mobile) : '';
        if (phonesMatchInternationally(phone, p1, searchCountry) || 
            phonesMatchInternationally(phone, p2, searchCountry)) {
          seenCustomerIds.add(String(addr.id_customer));
        }
      }
    }
  } catch (e) {
    console.error('Error searching PrestaShop addresses:', e);
  }

  for (const cid of seenCustomerIds) {
    try {
      const cData = await psGet(`customers/${cid}?display=[id,firstname,lastname,email,id_default_group]&output_format=JSON`);
      const c = cData?.customer || cData?.customers?.[0];
      if (c) {
        let groupName = "Client Standard";
        if (c.id_default_group) {
          try {
            const gData = await psGet(`groups/${c.id_default_group}?display=[name]&output_format=JSON`);
            if (gData && gData.group) {
              groupName = resolveLangField(gData.group.name) || "Client Standard";
            } else if (gData && gData.groups && gData.groups[0]) {
              groupName = resolveLangField(gData.groups[0].name) || "Client Standard";
            }
          } catch (err) {
            console.error('Failed to fetch group name from PrestaShop:', err);
          }
        }
        c.group_name = groupName;

        let phones: string[] = [];
        try {
          const addrData = await psGet(`addresses?filter[id_customer]=[${cid}]&display=[phone,phone_mobile]&output_format=JSON`);
          const addrs = addrData?.addresses || [];
          const phoneSet = new Set<string>();
          for (const a of addrs) {
            if (a.phone) phoneSet.add(String(a.phone).trim());
            if (a.phone_mobile) phoneSet.add(String(a.phone_mobile).trim());
          }
          phones = Array.from(phoneSet).filter(Boolean);
        } catch (addrErr) {
          console.error(`Failed to fetch addresses for customer ${cid}:`, addrErr);
        }
        c.phones = phones;
        if (phones.length > 0) {
          c.phone = phones[0];
        }

        customers.push(c);
      }
    } catch (e) {
      console.error(`Error fetching PrestaShop customer ${cid}:`, e);
    }
  }

  return customers;
}

async function startServer() {
  const app = express();
  app.use(express.json());

  // CORS headers
  app.use((req, res, next) => {
    res.header('Access-Control-Allow-Origin', '*');
    res.header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS');
    res.header('Access-Control-Allow-Headers', 'Content-Type');
    if (req.method === 'OPTIONS') {
      return res.sendStatus(200);
    }
    next();
  });

  // ==========================================
  // PROTECTION PAR MOT DE PASSE PARTAGE
  // ==========================================
  const APP_PASSWORD = process.env.APP_PASSWORD || '';
  const AUTH_SECRET = crypto
    .createHash('sha256')
    .update('tousergo-crm-' + APP_PASSWORD)
    .digest('hex');
  const AUTH_COOKIE_NAME = 'crm_auth';

  function parseCookies(req: express.Request): Record<string, string> {
    const header = req.headers.cookie;
    const out: Record<string, string> = {};
    if (!header) return out;
    header.split(';').forEach((part) => {
      const idx = part.indexOf('=');
      if (idx === -1) return;
      const key = part.slice(0, idx).trim();
      const value = part.slice(idx + 1).trim();
      out[key] = decodeURIComponent(value);
    });
    return out;
  }

  function isAuthenticated(req: express.Request): boolean {
    if (!APP_PASSWORD) return true; // si aucun mot de passe configure, ne bloque rien
    const cookies = parseCookies(req);
    return cookies[AUTH_COOKIE_NAME] === AUTH_SECRET;
  }

  app.use(express.urlencoded({ extended: true }));

  app.post('/login', (req, res) => {
    const submitted = String(req.body?.password || '');
    const redirectTo = String(req.body?.redirect || '/');
    if (APP_PASSWORD && submitted === APP_PASSWORD) {
      res.setHeader(
        'Set-Cookie',
        `${AUTH_COOKIE_NAME}=${encodeURIComponent(AUTH_SECRET)}; Path=/; Max-Age=2592000; HttpOnly; SameSite=Lax`
      );
      return res.redirect(302, redirectTo || '/');
    }
    return res.send(renderLoginPage(redirectTo, true));
  });

  function renderLoginPage(redirectTo: string, showError: boolean): string {
    return `<!DOCTYPE html>
<html lang="fr">
<head>
  <meta charset="UTF-8" />
  <title>CRM Levee de Fiche - Connexion</title>
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <style>
    body { font-family: -apple-system, sans-serif; background: #f1f5f9; display: flex; align-items: center; justify-content: center; height: 100vh; margin: 0; }
    .box { background: white; padding: 32px; border-radius: 16px; box-shadow: 0 4px 16px rgba(0,0,0,0.08); width: 320px; }
    h1 { font-size: 16px; margin: 0 0 16px; color: #26295A; }
    input { width: 100%; padding: 10px; border: 1px solid #cbd5e1; border-radius: 8px; box-sizing: border-box; margin-bottom: 12px; font-size: 14px; }
    button { width: 100%; padding: 10px; background: #B63E19; color: white; border: none; border-radius: 8px; font-weight: bold; cursor: pointer; }
    .error { color: #B63E19; font-size: 12px; margin-bottom: 12px; }
  </style>
</head>
<body>
  <div class="box">
    <h1>CRM Service Client - Acces protege</h1>
    ${showError ? '<div class="error">Mot de passe incorrect.</div>' : ''}
    <form method="POST" action="/login">
      <input type="hidden" name="redirect" value="${redirectTo.replace(/"/g, '&quot;')}" />
      <input type="password" name="password" placeholder="Mot de passe" autofocus />
      <button type="submit">Se connecter</button>
    </form>
  </div>
</body>
</html>`;
  }

  app.use((req, res, next) => {
    if (req.path === '/login') return next();
    if (req.path === '/api/crm-lookup') return next();
    if (isAuthenticated(req)) return next();

    if (req.path.startsWith('/api/')) {
      return res.status(401).json({ error: 'Non authentifie' });
    }

    const originalUrl = req.originalUrl || '/';
    return res.send(renderLoginPage(originalUrl, false));
  });

  // API: Search Odoo + PrestaShop contacts
  // API dediee a l'integration CRM 3CX : repond toujours en JSON avec une
  // ContactUrl pointant vers la fiche, pour que 3CX considere l'appel "trouve"
  app.get('/api/crm-lookup', (req, res) => {
    const phone = String(req.query.phone || '').trim();
    if (!phone) {
      return res.json({});
    }
    res.json({
      id: phone,
      firstname: 'Fiche',
      lastname: 'Client',
      phone: phone,
    });
  });

  app.get('/api/search', async (req, res) => {
    const phone = String(req.query.phone || '');
    const email = String(req.query.email || '');
    const odooId = String(req.query.odooId || '');

    if (!phone && !email && !odooId) {
      return res.json({ odoo: [], ps: null, odooErr: null });
    }

    try {
      // Lance la recherche Odoo et la recherche PrestaShop par telephone en parallele
      // (elles sont independantes l'une de l'autre)
      const odooPromise: Promise<any> = (async () => {
        if (odooId) {
          return searchOdoo('', '', odooId).catch(e => ({ error: true, message: e.message }));
        } else if (email) {
          return searchOdoo('', email, '').catch(e => ({ error: true, message: e.message }));
        } else if (phone) {
          return searchOdoo(phone, '', '').catch(e => ({ error: true, message: e.message }));
        }
        return [];
      })();

      const psByPhonePromise: Promise<any[]> = phone
        ? psSearchByPhone(phone).catch(() => [])
        : Promise.resolve([]);

      const [odooRes, psByPhoneList] = await Promise.all([odooPromise, psByPhonePromise]);

      if (odooRes && 'error' in odooRes) {
        return res.json({ odoo: [], ps: null, odooErr: (odooRes as any).message });
      }

      let filteredOdoo = odooRes;
      if (phone && !email && !odooId) {
        // Filter Odoo results strictly using international matching
        const searchCountry = getCountryCode(phone);
        filteredOdoo = odooRes.filter((partner: any) => {
          const p1 = partner.phone ? String(partner.phone) : '';
          const p2 = partner.mobile ? String(partner.mobile) : '';
          return phonesMatchInternationally(phone, p1, searchCountry) || 
                 phonesMatchInternationally(phone, p2, searchCountry);
        });
      }

      // Recherche PrestaShop par emails Odoo + recuperation derniere adresse de
      // livraison Odoo : independantes l'une de l'autre, lancees en parallele
      const emails = Array.from(new Set(filteredOdoo.map((c: any) => c.email).filter(Boolean))) as string[];
      if (email && !emails.includes(email)) {
        emails.push(email);
      }
      const partnerIds = filteredOdoo.map((p: any) => Number(p.id)).filter(Boolean);

      const [psList, latestOdooOrderAddress] = await Promise.all([
        psSearch(emails).catch(() => []),
        getLatestOdooShippingAddress(partnerIds).catch(err => {
          console.error('Error fetching latest shipping address:', err);
          return null;
        })
      ]);

      // Merge results and deduplicate PrestaShop customers
      const mergedPs = [...psList, ...psByPhoneList];
      const seenPs = new Set<string>();
      const uniquePs: any[] = [];
      for (const p of mergedPs) {
        if (p && p.id && !seenPs.has(String(p.id))) {
          seenPs.add(String(p.id));
          uniquePs.push(p);
        }
      }

      res.json({
        odoo: filteredOdoo,
        ps: uniquePs[0] || null,
        psList: uniquePs,
        odooErr: null,
        latestOdooOrderAddress
      });
    } catch (e: any) {
      res.status(500).json({ error: e.message });
    }
  });

  // API: PrestaShop Product Search (by name and reference)
  app.get('/api/products', async (req, res) => {
    const q = String(req.query.q || '').trim();
    if (!q) {
      return res.json({ products: [] });
    }
    try {
      const nameUrl = `products?filter[name]=%[${encodeURIComponent(q)}]%&display=full&output_format=JSON`;
      const refUrl = `products?filter[reference]=%[${encodeURIComponent(q)}]%&display=full&output_format=JSON`;
      const scrapeUrl = `https://www.tousergo.com/recherche?s=${encodeURIComponent(q)}`;
      
      const [nameRes, refRes, scrapeRes] = await Promise.all([
        psGet(nameUrl).catch(() => null),
        psGet(refUrl).catch(() => null),
        fetchWithTimeout(scrapeUrl, {
          headers: {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36'
          }
        }, 5000).catch(() => null)
      ]);
      
      const rawName = nameRes?.products || [];
      const rawRef = refRes?.products || [];
      
      const scrapedPrices = new Map<string, { priceTTC: number; priceHT?: number }>();
      if (scrapeRes && scrapeRes.ok) {
        try {
          const html = await scrapeRes.text();
          const match = html.match(/"items":\s*(\[[\s\S]*?\])\s*,?\s*"/i) || 
                        html.match(/'items':\s*(\[[\s\S]*?\])/i) ||
                        html.match(/items:\s*(\[[\s\S]*?\])/i) ||
                        html.match(/"items":\s*(\[[\s\S]*?\])/i);
          if (match) {
            let items: any[] = [];
            try {
              items = JSON.parse(match[1]);
            } catch (e) {
              const objRegex = /\{"id":"\d+","item_id":"\d+"[\s\S]*?\}/g;
              const objs = match[1].match(objRegex);
              if (objs) {
                for (const objStr of objs) {
                  try {
                    items.push(JSON.parse(objStr));
                  } catch (_) {}
                }
              }
            }
            for (const item of items) {
              const itemId = String(item.id_product || item.item_id_product || item.id || '');
              if (itemId) {
                const priceTTC = parseFloat(item.price_tax_inc || item.price || '0');
                const priceHT = parseFloat(item.price_tax_exc || '0');
                if (priceTTC > 0) {
                  scrapedPrices.set(itemId, {
                    priceTTC,
                    priceHT: priceHT > 0 ? priceHT : undefined
                  });
                }
              }
            }
          }
        } catch (e) {
          console.error("Error parsing scraped search items:", e);
        }
      }

      const mergedMap = new Map<string, any>();
      const allRaw = [...rawName, ...rawRef];
      for (const p of allRaw) {
        if (p && p.id && !mergedMap.has(p.id)) {
          mergedMap.set(p.id, p);
        }
      }
      
      const uniqueProducts = Array.from(mergedMap.values())
        .filter((p: any) => p.active !== '0')
        .slice(0, 25);

      const enriched = await Promise.all(
        uniqueProducts.map(p => {
          const sInfo = scrapedPrices.get(String(p.id));
          return enrichProduct(p, sInfo).catch(err => {
            console.error(`Error enriching product ${p?.id}:`, err);
            return null;
          });
        })
      );
      
      res.json({ products: enriched.filter(Boolean) });
    } catch (e: any) {
      res.status(500).json({ error: e.message });
    }
  });

  // API: Proxy product main image
  app.get('/api/product-image/:id/:imgId', async (req, res) => {
    const { id, imgId } = req.params;
    if (!id || !imgId || imgId === '0') {
      return res.status(400).send('Invalid params');
    }
    const imageUrl = `${CFG.ps.url}/api/images/products/${id}/${imgId}?ws_key=${CFG.ps.key}`;
    try {
      const r = await fetchWithTimeout(imageUrl, { method: 'GET' }, 8000);
      if (!r.ok) {
        return res.status(r.status).send('Failed to fetch image');
      }
      const contentType = r.headers.get('content-type') || 'image/jpeg';
      res.setHeader('Content-Type', contentType);
      const buffer = await r.arrayBuffer();
      res.send(Buffer.from(buffer));
    } catch (e: any) {
      res.status(500).send(`Error: ${e.message}`);
    }
  });

  // API: Proxy product live image from tousergo.com website
  app.get('/api/product-image-live/:imgId/:linkRewrite', async (req, res) => {
    const { imgId, linkRewrite } = req.params;
    if (!imgId || imgId === '0' || !linkRewrite) {
      return res.status(400).send('Invalid params');
    }
    const isLarge = req.query.size === 'large';
    const sizeTag = isLarge ? 'large_default' : 'medium_default';
    const imageUrl = `https://www.tousergo.com/${imgId}-${sizeTag}/${linkRewrite}.jpg`;
    try {
      const r = await fetchWithTimeout(imageUrl, {
        method: 'GET',
        headers: {
          'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36'
        }
      }, 8000);
      if (!r.ok) {
        return res.status(r.status).send('Failed to fetch live image');
      }
      const contentType = r.headers.get('content-type') || 'image/jpeg';
      res.setHeader('Content-Type', contentType);
      const buffer = await r.arrayBuffer();
      res.send(Buffer.from(buffer));
    } catch (e: any) {
      res.status(500).send(`Error: ${e.message}`);
    }
  });

  // API: PrestaShop Product Detailed Enrichment (Scrapes LPP Code, Base de remboursement, Price, Reviews, Shipping, Descriptions, Technical Sheets and gets live Odoo Stock)
  app.get('/api/product-enrich/:id', async (req, res) => {
    const { id } = req.params;
    if (!id) {
      return res.status(400).json({ error: 'Missing ID' });
    }
    const qLinkRewrite = req.query.link_rewrite as string | undefined;
    const qName = req.query.name as string | undefined;
    const qReference = req.query.reference as string | undefined;

    try {
      // Always fetch product details from PrestaShop to have accurate metadata
      const d = await psGet(`products/${id}?output_format=JSON`);
      const p = d?.product;
      if (!p) {
        return res.status(404).json({ error: 'Product not found in PrestaShop' });
      }

      const name = resolveLangField(p.name) || qName || '';
      const reference = p.reference || qReference || '';
      const linkRewrite = resolveLangField(p.link_rewrite) || qLinkRewrite || '';

      let lppCodes: string[] = [];
      let baseRemboursement = '';
      let scrapedPriceTTC = '';
      let reviewsCount = 0;
      let shippingText = '';
      let technicalSheet: { name: string; value: string }[] = [];

      // Determine the tax rate and base price from PrestaShop
      let priceHT = parseFloat(p.price || '0');
      let taxRate = await getTaxRate(p.id_tax_rules_group);

      let scrapedShortDesc = '';
      let scrapedPlusDesc = '';
      let scrapedMainDesc = '';

      if (linkRewrite) {
        let url = `https://www.tousergo.com/${id}-${linkRewrite}.html`;
        try {
          let fetchRes = await fetchWithTimeout(url, {
            method: 'GET',
            headers: {
              'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36'
            }
          }, 8000);
          
          if (!fetchRes.ok) {
            const fallbackUrl = `https://www.tousergo.com/p/${id}-${linkRewrite}.html`;
            fetchRes = await fetchWithTimeout(fallbackUrl, {
              method: 'GET',
              headers: {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36'
              }
            }, 8000);
          }
          
          if (fetchRes.ok) {
            const html = await fetchRes.text();
            
            // 1. Precise SS Refund Base Scraper
            const ssMatch = html.match(/<span class="c-tag--refund-ss">([\s\S]*?)<\/span>/i);
            if (ssMatch) {
              baseRemboursement = ssMatch[1].replace(/&nbsp;/g, ' ').replace(/\s+/g, ' ').trim();
            }

            // 2. Precise LPP Codes Scraper
            const lppTagMatch = html.match(/<span>Code LPP<\/span>\s*<span class="u-font-weight-bold">(\d{7})<\/span>/i);
            if (lppTagMatch) {
              lppCodes.push(lppTagMatch[1]);
            }
            const regex = /(?:code\s*lppr?|lppr?)\s*[^0-9\w]*(\d{7})/gi;
            let m;
            while ((m = regex.exec(html)) !== null) {
              if (!lppCodes.includes(m[1])) {
                lppCodes.push(m[1]);
              }
            }

            // 3. Scraped Price TTC Scanner
            let foundPrice = '';

            // Priority 1: itemprop="price" with a content attribute (very common, usually holds the active/discounted price)
            const itempropContentMatch = html.match(/<[^>]*itemprop="price"[^>]*content="([^"]+)"/i) ||
                                         html.match(/content="([^"]+)"[^>]*itemprop="price"/i);
            if (itempropContentMatch) {
              const val = parseFloat(itempropContentMatch[1].replace(',', '.'));
              if (!isNaN(val) && val > 0) {
                foundPrice = `${val.toFixed(2)} €`;
              }
            }

            // Priority 2: Precise current-price class or itemprop span
            if (!foundPrice) {
              const currentPriceMatch = html.match(/<span[^>]*class="[^"]*current-price[^"]*"[^>]*>([\s\S]*?)<\/span>/i) ||
                                        html.match(/<[^>]*itemprop="price"[^>]*>([^<]+)<\//i);
              if (currentPriceMatch) {
                foundPrice = currentPriceMatch[1].replace(/<[^>]+>/g, '').trim();
              }
            }

            // Priority 3: Standard price class, excluding classes with regular, old, discount, strike, etc.
            if (!foundPrice) {
              const priceClassMatch = html.match(/<span[^>]*class="(?![^"]*(?:regular|old|discount|strike|reduction|line-through))[^"]*price[^"]*"[^>]*>([^<]+)/i) ||
                                      html.match(/id="our_price_display"[^>]*>([^<]+)/i);
              if (priceClassMatch) {
                foundPrice = priceClassMatch[1].trim();
              }
            }

            // Priority 4: Any span containing class "price" as fallback
            if (!foundPrice) {
              const genericPriceMatch = html.match(/<span[^>]*class="[^"]*price[^"]*"[^>]*>([^<]+)/i);
              if (genericPriceMatch) {
                foundPrice = genericPriceMatch[1].trim();
              }
            }

            if (foundPrice) {
              scrapedPriceTTC = foundPrice.replace(/&nbsp;/g, ' ').replace(/\s+/g, ' ').trim();
            }

            // 4. Scraped Reviews Count Scanner
            const reviewsMatch = html.match(/(\d+)\s*avis/i) || html.match(/"reviewCount":\s*"(\d+)"/i);
            if (reviewsMatch) {
              reviewsCount = parseInt(reviewsMatch[1]);
            }

            // 5. Shipping delay / Stock detail
            const expIdx = html.toLowerCase().indexOf('expédié le');
            if (expIdx !== -1) {
              const segment = html.slice(expIdx - 100, expIdx + 150);
              const cleaned = segment.replace(/<[^>]+>/g, '').replace(/\s+/g, ' ').trim();
              const sm = cleaned.match(/expédié le\s*[^.]+/i) || cleaned.match(/expédié le\s*[^<\s]+(?:\s+[^<\s]+){1,5}/i);
              if (sm) {
                shippingText = sm[0].trim();
              } else {
                shippingText = cleaned;
              }
            }

            // 6. Complete Description & Technical Sheet Scrapers from tousergo.com
            const shortDescMatch = html.match(/<div[^>]*id="product-description-short[^"]*"[^>]*>([\s\S]*?)<\/div>/i);
            if (shortDescMatch) {
              scrapedShortDesc = shortDescMatch[1].trim();
            }

            const plusDescMatch = html.match(/<div[^>]*class="[^"]*c-pdt__desc-plus[^"]*"[^>]*>([\s\S]*?)<\/div>/i);
            if (plusDescMatch) {
              scrapedPlusDesc = plusDescMatch[1].trim();
            }

            const mainDescMatch = html.match(/<div[^>]*class="[^"]*product-description[^"]*"[^>]*>([\s\S]*?)<\/div>/i);
            if (mainDescMatch) {
              scrapedMainDesc = mainDescMatch[1].trim();
            }

            const tableMatch = html.match(/<table[^>]*class="[^"]*c-pdt__table[^"]*"[^>]*>([\s\S]*?)<\/table>/i) || 
                               html.match(/<table[^>]*>([\s\S]*?)<\/table>/i);
            if (tableMatch) {
              technicalSheet = parseTechnicalSheet(tableMatch[0]);
            }
          }
        } catch (err: any) {
          console.error(`Failed to scrape live page for product ${id}:`, err);
        }
      }

      // Calculate precise PrestaShop Price TTC from the PrestaShop HT and resolved tax rate
      const psPriceHT = priceHT;
      const psPriceTTC = Math.round(priceHT * (1 + taxRate / 100) * 100) / 100;

      let finalPriceTTC = psPriceTTC;
      let finalPriceHT = psPriceHT;

      if (scrapedPriceTTC) {
        const cleanStr = scrapedPriceTTC.replace(/[^\d,.]/g, '').replace(',', '.');
        const parsedTTC = parseFloat(cleanStr);
        if (!isNaN(parsedTTC) && parsedTTC > 0) {
          finalPriceTTC = parsedTTC;
          finalPriceHT = Math.round((finalPriceTTC / (1 + taxRate / 100)) * 100) / 100;
        }
      }

      // Construct a fully complete description combining scraped items from tousergo.com
      let combinedDescription = '';
      if (scrapedShortDesc) {
        combinedDescription += `<div class="scraped-short-desc mb-5">${scrapedShortDesc}</div>`;
      }
      if (scrapedPlusDesc) {
        combinedDescription += `<div class="scraped-plus-desc mb-5 p-4 bg-indigo-50/40 rounded-xl border border-indigo-100/50 text-slate-700 leading-relaxed font-medium">${scrapedPlusDesc}</div>`;
      }
      if (scrapedMainDesc) {
        combinedDescription += `<div class="scraped-main-desc space-y-4 text-slate-700 leading-relaxed">${scrapedMainDesc}</div>`;
      }

      const description = combinedDescription || resolveLangField(p.description) || resolveLangField(p.description_short) || '';

      // 7. Live Odoo Stock Query
      const odooStock = await getOdooStock(reference);
      const quantity = odooStock.total;
      const inStock = quantity > 0;
      const odooStockDetails = odooStock.details;

      res.json({
        id,
        name,
        reference,
        lppCodes,
        baseRemboursement,
        scrapedPriceTTC,
        priceTTC: finalPriceTTC,
        priceHT: finalPriceHT,
        psPriceTTC,
        psPriceHT,
        taxRate,
        reviewsCount,
        shippingText,
        inStock,
        quantity,
        description,
        technicalSheet,
        odooStockDetails,
      });
    } catch (e: any) {
      res.status(500).json({ error: e.message });
    }
  });

  // API: PrestaShop Orders
  app.get('/api/orders', async (req, res) => {
    const psId = req.query.psId;
    if (!psId) return res.json({ orders: [] });
    try {
      const d = await psGet(`orders?filter[id_customer]=[${psId}]&display=full&sort=[date_add_DESC]&limit=15&output_format=JSON`);
      const orders = d?.orders || [];
      
      // En parallèle, récupérer les détails des 6 premières commandes récentes
      await Promise.allSettled(
        orders.slice(0, 6).map(async (o: any) => {
          const [det, car] = await Promise.all([
            psGet(`order_details?filter[id_order]=[${o.id}]&display=full&output_format=JSON`),
            o.id_carrier ? psGet(`carriers/${o.id_carrier}?display=[id,name,url]&output_format=JSON`) : Promise.resolve(null),
          ]);
          o._det = det?.order_details || [];
          o._car = car?.carrier || null;
        })
      );
      res.json({ orders });
    } catch (e: any) {
      res.status(500).json({ error: e.message });
    }
  });

  // API: PrestaShop Cart
  app.get('/api/cart', async (req, res) => {
    const psId = req.query.psId;
    if (!psId) return res.json({ cart: null });
    try {
      const d = await psGet(`carts?filter[id_customer]=[${psId}]&display=full&sort=[date_upd_DESC]&limit=3&output_format=JSON`);
      const carts = d?.carts || [];
      const cart = carts.find((c: any) => {
        const rows = c.associations?.cart_rows;
        return rows && (Array.isArray(rows) ? rows : Object.values(rows)).some((r: any) => parseInt(r.quantity) > 0);
      }) || null;

      if (cart) {
        const rows = cart.associations?.cart_rows;
        cart._rows = (Array.isArray(rows) ? rows : (rows ? Object.values(rows) : [])).filter((r: any) => parseInt(r.quantity) > 0);
      }
      res.json({ cart });
    } catch (e: any) {
      res.status(500).json({ error: e.message });
    }
  });

  // API: Crisp Conversation
  app.get('/api/crisp', async (req, res) => {
    const email = String(req.query.email || '');
    if (!email) return res.json({ convs: [] });
    try {
      const r = await fetchWithTimeout(
        `https://api.crisp.chat/v1/website/${CFG.crisp.sid}/conversations/1?search_query=${encodeURIComponent(email)}`,
        {
          headers: {
            Authorization: CFG.crisp.auth,
            'X-Crisp-Tier': 'user',
          },
        }
      );
      if (!r.ok) {
        return res.json({ convs: [], err: `Crisp HTTP ${r.status}` });
      }
      const d: any = await r.json();
      res.json({ convs: d.data || [] });
    } catch (e: any) {
      res.json({ convs: [], err: e.message });
    }
  });

  // API: 3CX Call Logs
  app.get('/api/calls', async (req, res) => {
    const phone = String(req.query.phone || '');
    const cleanPhone = phone.replace(/\D/g, '');
    const suf = cleanPhone.slice(-9); // On prend les 9 derniers chiffres pour être flexible sur les formats internationaux/locaux
    try {
      const r = await fetchWithTimeout(`${CFG.tcx.url}/xapi/v1/CallLog?top=50`, {
        headers: { Authorization: `Bearer ${CFG.tcx.key}` },
      });
      if (!r.ok) {
        return res.json({ calls: [], err: `3CX HTTP ${r.status}` });
      }
      const d: any = await r.json();
      const all = d.value || d.calls || [];
      const filtered = (Array.isArray(all) ? all : []).filter((c: any) => {
        const f = String(c.CallerNumber || '').replace(/\D/g, '');
        const t = String(c.CalleeNumber || '').replace(/\D/g, '');
        return suf && (f.endsWith(suf) || t.endsWith(suf));
      }).slice(0, 30);
      res.json({ calls: filtered });
    } catch (e: any) {
      res.json({ calls: [], err: e.message });
    }
  });

  // Vite Integration / Serve Frontend
  if (process.env.NODE_ENV === 'production') {
    app.use(express.static(path.join(__dirname, 'dist')));
    app.get('*', (req, res) => {
      res.sendFile(path.join(__dirname, 'dist', 'index.html'));
    });
  } else {
    const { createServer: createViteServer } = await import('vite');
    const vite = await createViteServer({
      server: { middlewareMode: true },
      appType: 'spa',
    });
    app.use(vite.middlewares);
  }

  const PORT = process.env.PORT || 7860;
  app.listen(PORT, '0.0.0.0', () => {
    console.log(`Server running at http://localhost:${PORT}`);
  });
}

startServer();