apex/server/routes/sync.js

import { sync, getSyncLogs, getMaterials } from '../services/formexCrawler.js';
import { syncHaefeleByUrls } from '../services/haefeleCrawler.js';
import { syncLeubaHiag, LEUBA_HIAG_TARGETS } from '../services/leubaHiagCrawler.js';
import { getSchedulerStatus } from '../services/scheduler.js';
import pool, { run } from '../db/index.js';

/**
 * POST /api/sync/formex
 * Trigger a manual Formex sync (non-streaming)
 */
export async function triggerFormexSync(req, res) {
  try {
    // Run sync (this may take a while)
    const result = await sync();
    
    if (result.success) {
      res.json({
        success: true,
        message: `Synchronisation terminée: ${result.itemsCount} produits`,
        itemsCount: result.itemsCount,
        duration: result.duration
      });
    } else {
      res.status(500).json({
        success: false,
        error: result.error,
        itemsCount: result.itemsCount
      });
    }
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
}

/**
 * GET /api/sync/formex/stream
 * SSE endpoint for real-time sync progress
 */
export async function streamFormexSync(req, res) {
  // Set SSE headers
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.setHeader('X-Accel-Buffering', 'no'); // Disable nginx buffering
  res.flushHeaders();

  // Emitter function that sends SSE events
  const emit = (data) => {
    res.write(`data: ${JSON.stringify(data)}\n\n`);
  };

  // Handle client disconnect
  req.on('close', () => {
    // Client disconnected, cleanup if needed
  });

  const force = req.query.force === '1' || req.query.force === 'true';

  try {
    await sync(emit, { force });
  } catch (err) {
    emit({ type: 'error', error: err.message });
  }

  // Close connection
  res.end();
}

/**
 * POST /api/sync/haefele
 * Trigger Häfele sync for a provided list of product URLs
 * Body: { urls: string[] }
 */
export async function triggerHaefeleSync(req, res) {
  try {
    const urls = Array.isArray(req.body?.urls) ? req.body.urls : [];
    const credentials = {
      username: req.body?.username,
      password: req.body?.password,
      customerNo: req.body?.customerNo
    };
    if (urls.length === 0) {
      return res.status(400).json({ error: 'Le champ urls (tableau) est requis.' });
    }

    const result = await syncHaefeleByUrls(urls, () => {}, { credentials });
    if (!result.success) {
      return res.status(500).json({
        success: false,
        error: result.error || 'Sync Häfele échouée',
        itemsCount: result.itemsCount || 0
      });
    }

    res.json({
      success: true,
      message: `Synchronisation Häfele terminée: ${result.itemsCount} produits`,
      itemsCount: result.itemsCount,
      failedCount: result.failedCount || 0,
      duration: result.duration
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
}

/**
 * GET /api/sync/haefele/stream
 * SSE endpoint for Häfele sync progress (URL list from query params)
 * Query:
 *   - url=... (repeatable)
 *   - urls=... (newline/comma/semicolon separated fallback)
 */
export async function streamHaefeleSync(req, res) {
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.setHeader('X-Accel-Buffering', 'no');
  res.flushHeaders();

  const emit = (data) => {
    res.write(`data: ${JSON.stringify(data)}\n\n`);
  };

  req.on('close', () => {
    // client disconnected
  });

  const repeatable = req.query.url;
  let urls = [];
  if (Array.isArray(repeatable)) {
    urls = repeatable;
  } else if (typeof repeatable === 'string' && repeatable.trim()) {
    urls = [repeatable];
  } else if (typeof req.query.urls === 'string') {
    urls = req.query.urls.split(/[\n,;]+/g);
  }

  try {
    await syncHaefeleByUrls(urls, emit);
  } catch (err) {
    emit({ type: 'error', error: err.message });
  }

  res.end();
}

/**
 * GET /api/haefele/materials
 * List Häfele materials that were fetched/synced.
 */
export async function getHaefeleMaterials(req, res) {
  try {
    const { search = '', active, page = '1', limit = '25' } = req.query;
    const pageNum = Math.max(1, parseInt(page, 10));
    const limitNum = Math.min(200, Math.max(1, parseInt(limit, 10)));
    const offset = (pageNum - 1) * limitNum;

    const whereClauses = [
      `(supplier_id = (SELECT id FROM suppliers WHERE code = 'haefele') OR collection = 'Hafele' OR formex_code LIKE 'HAF-%')`
    ];
    const params = [];

    if (search) {
      whereClauses.push('(name LIKE ? OR formex_code LIKE ? OR formex_url LIKE ?)');
      params.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }
    if (active === '1' || active === 'true') {
      whereClauses.push('active = 1');
    } else if (active === '0' || active === 'false') {
      whereClauses.push('active = 0');
    }

    const whereSQL = `WHERE ${whereClauses.join(' AND ')}`;
    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM formex_materials ${whereSQL}`,
      params
    );
    const total = countResult.rows[0]?.total || 0;

    const { rows } = await pool.query(
      `SELECT id, formex_code, name, category, thickness_mm, price_chf, price_unit, image_url, formex_url, active, last_synced_at
       FROM formex_materials
       ${whereSQL}
       ORDER BY last_synced_at DESC, id DESC
       LIMIT ? OFFSET ?`,
      [...params, limitNum, offset]
    );

    res.json({
      materials: rows,
      pagination: {
        page: pageNum,
        limit: limitNum,
        total,
        totalPages: Math.ceil(total / limitNum)
      }
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/sync/leubahiag/targets
 * List the curated Leuba Hiag catalog lines available for import.
 */
export function getLeubaHiagTargets(req, res) {
  res.json(LEUBA_HIAG_TARGETS.map(({ key, label, tier }) => ({ key, label, tier })));
}

function parseTargetsParam(value) {
  if (Array.isArray(value)) return value.filter(Boolean);
  if (typeof value === 'string' && value.trim()) {
    return value.split(/[\n,;]+/g).map((v) => v.trim()).filter(Boolean);
  }
  return null;
}

/**
 * POST /api/sync/leubahiag
 * Trigger a Leuba Hiag curated catalog sync (non-streaming).
 * Body: { targets?: string[], activate?: boolean, credentials?: {...} }
 */
export async function triggerLeubaHiagSync(req, res) {
  try {
    const targets = parseTargetsParam(req.body?.targets);
    const activate = req.body?.activate === true || req.body?.activate === '1';
    const fetchPrices = req.body?.fetchPrices !== false && req.body?.fetchPrices !== '0';
    const credentials = {
      username: req.body?.username || req.body?.email,
      password: req.body?.password,
    };

    const result = await syncLeubaHiag({ targets, activate, fetchPrices, credentials }, () => {});
    if (!result.success) {
      return res.status(500).json({
        success: false,
        error: result.error || 'Sync Leuba Hiag échouée',
        itemsCount: result.itemsCount || 0,
      });
    }

    res.json({
      success: true,
      message: `Synchronisation Leuba Hiag terminée: ${result.itemsCount} produits`,
      itemsCount: result.itemsCount,
      createdCount: result.createdCount || 0,
      updatedCount: result.updatedCount || 0,
      pricedCount: result.pricedCount || 0,
      duration: result.duration,
    });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
}

/**
 * GET /api/sync/leubahiag/stream
 * SSE endpoint for Leuba Hiag sync progress.
 * Query: targets=key1,key2 (optional), activate=1 (optional)
 */
export async function streamLeubaHiagSync(req, res) {
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.setHeader('X-Accel-Buffering', 'no');
  res.flushHeaders();

  const emit = (data) => {
    res.write(`data: ${JSON.stringify(data)}\n\n`);
  };

  req.on('close', () => {
    // client disconnected
  });

  const targets = parseTargetsParam(req.query.targets);
  const activate = req.query.activate === '1' || req.query.activate === 'true';
  const fetchPrices = req.query.prices !== '0' && req.query.prices !== 'false';

  try {
    await syncLeubaHiag({ targets, activate, fetchPrices }, emit);
  } catch (err) {
    emit({ type: 'error', error: err.message });
  }

  res.end();
}

/**
 * GET /api/leubahiag/materials
 * List Leuba Hiag materials that were imported.
 */
export async function getLeubaHiagMaterials(req, res) {
  try {
    const { search = '', active, page = '1', limit = '25' } = req.query;
    const pageNum = Math.max(1, parseInt(page, 10));
    const limitNum = Math.min(200, Math.max(1, parseInt(limit, 10)));
    const offset = (pageNum - 1) * limitNum;

    const whereClauses = [
      `(supplier_id = (SELECT id FROM suppliers WHERE code = 'leubahiag') OR formex_code LIKE 'LH-%')`
    ];
    const params = [];

    if (search) {
      whereClauses.push('(name LIKE ? OR formex_code LIKE ? OR collection LIKE ?)');
      params.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }
    if (active === '1' || active === 'true') {
      whereClauses.push('active = 1');
    } else if (active === '0' || active === 'false') {
      whereClauses.push('active = 0');
    }

    const whereSQL = `WHERE ${whereClauses.join(' AND ')}`;
    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM formex_materials ${whereSQL}`,
      params
    );
    const total = countResult.rows[0]?.total || 0;

    const { rows } = await pool.query(
      `SELECT id, formex_code, name, collection, category, thickness_mm, width_mm, height_mm,
              price_chf, price_unit, image_url, formex_url, active, last_synced_at
       FROM formex_materials
       ${whereSQL}
       ORDER BY collection, name
       LIMIT ? OFFSET ?`,
      [...params, limitNum, offset]
    );

    res.json({
      materials: rows,
      pagination: {
        page: pageNum,
        limit: limitNum,
        total,
        totalPages: Math.ceil(total / limitNum)
      }
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/sync/logs
 * Get sync history
 */
export async function getLogs(req, res) {
  try {
    const limit = parseInt(req.query.limit || '20', 10);
    const logs = await getSyncLogs(limit);
    res.json(logs);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/sync/status
 * Get scheduler status
 */
export function getStatus(req, res) {
  const status = getSchedulerStatus();
  res.json(status);
}

/**
 * GET /api/formex/materials
 * Get Formex materials with filtering and pagination
 */
export async function getFormexMaterials(req, res) {
  try {
    const { search, collection, category, active, supplier, page = '1', limit = '25' } = req.query;
    const pageNum = Math.max(1, parseInt(page, 10));
    const limitNum = Math.min(100, Math.max(1, parseInt(limit, 10)));
    const offset = (pageNum - 1) * limitNum;

    let whereClauses = [];
    let params = [];

    if (search) {
      whereClauses.push('(m.name LIKE ? OR m.formex_code LIKE ?)');
      params.push(`%${search}%`, `%${search}%`);
    }

    if (collection) {
      whereClauses.push('m.collection = ?');
      params.push(collection);
    }
    
    if (category) {
      whereClauses.push('m.category = ?');
      params.push(category);
    }
    
    if (active === '1' || active === 'true') {
      whereClauses.push('m.active = 1');
    }

    if (supplier) {
      whereClauses.push('s.code = ?');
      params.push(supplier);
    }

    const whereSQL = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';

    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM formex_materials m LEFT JOIN suppliers s ON m.supplier_id = s.id ${whereSQL}`,
      params
    );
    const total = countResult.rows[0]?.total || 0;

    const { rows } = await pool.query(
      `SELECT m.*, s.code AS supplier_code, s.name AS supplier_name
       FROM formex_materials m LEFT JOIN suppliers s ON m.supplier_id = s.id
       ${whereSQL} ORDER BY m.collection, m.name LIMIT ? OFFSET ?`,
      [...params, limitNum, offset]
    );

    res.json({
      materials: rows,
      pagination: {
        page: pageNum,
        limit: limitNum,
        total,
        totalPages: Math.ceil(total / limitNum)
      }
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/formex/materials/:id
 * Get a single material by ID with dimensions and variants
 */
export async function getFormexMaterial(req, res) {
  try {
    const { id } = req.params;
    
    // Get main material
    const { rows } = await pool.query(
      'SELECT * FROM formex_materials WHERE id = ?',
      [id]
    );

    if (rows.length === 0) {
      return res.status(404).json({ error: 'Matériau non trouvé' });
    }

    const material = rows[0];
    
    // Get dimension variants
    const dimensions = await pool.query(
      'SELECT id, width_mm, height_mm, thickness_mm, is_default FROM formex_dimensions WHERE material_id = ? ORDER BY is_default DESC, width_mm',
      [id]
    );
    material.dimensions = dimensions.rows;
    
    // Get color/decor variants
    const variants = await pool.query(
      'SELECT id, variant_code, variant_name, variant_url, image_url FROM formex_variants WHERE material_id = ? ORDER BY variant_name',
      [id]
    );
    material.variants = variants.rows;

    res.json(material);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * POST /api/formex/materials
 * Create a new material
 */
export async function createFormexMaterial(req, res) {
  try {
    const { formex_code, name, collection, category, width_mm, height_mm, thickness_mm,
            diameter_mm, length_mm, bore_diameter_mm, bore_depth_base_mm, bore_depth_piece_mm,
            image_url, formex_url, price_chf, price_unit, active } = req.body;

    if (!formex_code || !name || !collection) {
      return res.status(400).json({ error: 'formex_code, name et collection sont requis' });
    }

    const existing = await pool.query(
      'SELECT id FROM formex_materials WHERE formex_code = ?',
      [formex_code]
    );
    if (existing.rows.length > 0) {
      return res.status(409).json({ error: 'Un matériau avec ce code existe déjà' });
    }

    await run(
      `INSERT INTO formex_materials (formex_code, name, collection, category, width_mm, height_mm, thickness_mm,
         diameter_mm, length_mm, bore_diameter_mm, bore_depth_base_mm, bore_depth_piece_mm,
         image_url, formex_url, price_chf, price_unit, active)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [formex_code, name, collection, category || null, width_mm || null, height_mm || null, thickness_mm || null,
       diameter_mm ?? null, length_mm ?? null,
       bore_diameter_mm ?? null, bore_depth_base_mm ?? null, bore_depth_piece_mm ?? null,
       image_url || null, formex_url || null, price_chf ?? null, price_unit || null,
       active !== undefined ? (active ? 1 : 0) : 1]
    );

    const { rows } = await pool.query(
      'SELECT * FROM formex_materials WHERE formex_code = ?',
      [formex_code]
    );

    res.status(201).json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * PUT /api/formex/materials/:id
 * Update a material
 */
export async function updateFormexMaterial(req, res) {
  try {
    const { id } = req.params;
    const { formex_code, name, collection, category, width_mm, height_mm, thickness_mm,
            diameter_mm, length_mm, bore_diameter_mm, bore_depth_base_mm, bore_depth_piece_mm,
            image_url, formex_url, price_chf, price_unit, active } = req.body;

    const existing = await pool.query(
      'SELECT * FROM formex_materials WHERE id = ?',
      [id]
    );
    if (existing.rows.length === 0) {
      return res.status(404).json({ error: 'Matériau non trouvé' });
    }

    if (!formex_code || !name || !collection) {
      return res.status(400).json({ error: 'formex_code, name et collection sont requis' });
    }

    if (formex_code !== existing.rows[0].formex_code) {
      const duplicate = await pool.query(
        'SELECT id FROM formex_materials WHERE formex_code = ? AND id != ?',
        [formex_code, id]
      );
      if (duplicate.rows.length > 0) {
        return res.status(409).json({ error: 'Un matériau avec ce code existe déjà' });
      }
    }

    await run(
      `UPDATE formex_materials 
       SET formex_code = ?, name = ?, collection = ?, category = ?, width_mm = ?, height_mm = ?, thickness_mm = ?,
           diameter_mm = ?, length_mm = ?,
           bore_diameter_mm = ?, bore_depth_base_mm = ?, bore_depth_piece_mm = ?,
           image_url = ?, formex_url = ?, price_chf = ?, price_unit = ?, active = ?
       WHERE id = ?`,
      [formex_code, name, collection, category || null, width_mm || null, height_mm || null, thickness_mm || null,
       diameter_mm ?? null, length_mm ?? null,
       bore_diameter_mm ?? null, bore_depth_base_mm ?? null, bore_depth_piece_mm ?? null,
       image_url || null, formex_url || null, price_chf ?? null, price_unit || null,
       active !== undefined ? (active ? 1 : 0) : 1, id]
    );

    const { rows } = await pool.query(
      'SELECT * FROM formex_materials WHERE id = ?',
      [id]
    );

    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * PATCH /api/formex/materials/:id/active
 * Toggle only the `active` flag of a material (lossless: never touches other columns).
 * Body: { active: boolean }
 */
export async function setFormexMaterialActive(req, res) {
  try {
    const { id } = req.params;
    const active = req.body?.active === true || req.body?.active === 1 || req.body?.active === '1';

    const existing = await pool.query('SELECT id FROM formex_materials WHERE id = ?', [id]);
    if (existing.rows.length === 0) {
      return res.status(404).json({ error: 'Matériau non trouvé' });
    }

    await run('UPDATE formex_materials SET active = ? WHERE id = ?', [active ? 1 : 0, id]);

    const { rows } = await pool.query(
      'SELECT id, formex_code, name, active FROM formex_materials WHERE id = ?',
      [id]
    );
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * DELETE /api/formex/materials/:id
 * Delete a material
 */
export async function deleteFormexMaterial(req, res) {
  try {
    const { id } = req.params;

    // Check if material exists
    const existing = await pool.query(
      'SELECT * FROM formex_materials WHERE id = ?',
      [id]
    );
    if (existing.rows.length === 0) {
      return res.status(404).json({ error: 'Matériau non trouvé' });
    }

    await run('DELETE FROM formex_materials WHERE id = ?', [id]);

    res.json({ success: true, message: 'Matériau supprimé' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/formex/suppliers
 * Get suppliers that have at least one material linked
 */
export async function getFormexSuppliers(req, res) {
  try {
    const { rows } = await pool.query(
      `SELECT DISTINCT s.code, s.name FROM suppliers s
       INNER JOIN formex_materials m ON m.supplier_id = s.id
       ORDER BY s.name`
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/formex/collections
 * Get distinct collection names for filter dropdown
 */
export async function getFormexCollections(req, res) {
  try {
    const { rows } = await pool.query(
      'SELECT DISTINCT collection FROM formex_materials ORDER BY collection'
    );
    res.json(rows.map(r => r.collection));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}

/**
 * GET /api/formex/categories
 * Get distinct category names for filter dropdown
 */
export async function getFormexCategories(req, res) {
  try {
    const { rows } = await pool.query(
      'SELECT DISTINCT category FROM formex_materials WHERE category IS NOT NULL ORDER BY category'
    );
    res.json(rows.map(r => r.category));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}