apex/server/routes/finishes.js

import pool from '../db/index.js';

const CONFIGURATOR_MATERIAL_FILTER = `
  active = 1
  AND NOT (
    supplier_id = (SELECT id FROM suppliers WHERE code = 'haefele')
    OR LOWER(COALESCE(collection, '')) = 'hafele'
    OR formex_code LIKE 'HAF-%'
  )
`;

/**
 * GET /api/finishes
 * Returns active panel materials as finishes for the configurator
 * Falls back to legacy finishes if no Formex materials exist
 */
export async function getFinishes(req, res) {
  try {
    // Hardware suppliers can share formex_materials for catalog synchronization,
    // but their products must never be offered as panel finishes.
    const formexResult = await pool.query(
      `SELECT COUNT(*) as count FROM formex_materials WHERE ${CONFIGURATOR_MATERIAL_FILTER}`
    );
    const hasFormexMaterials = (formexResult.rows[0]?.count || 0) > 0;
    
    if (hasFormexMaterials) {
      // Return Formex materials as finishes
      const { rows } = await pool.query(`
        SELECT 
          id,
          formex_code as code,
          name as label,
          name,
          collection,
          category,
          width_mm,
          height_mm,
          thickness_mm,
          image_url,
          formex_url,
          price_chf,
          dominant_color as hexColor,
          'formex' as source
        FROM formex_materials 
        WHERE ${CONFIGURATOR_MATERIAL_FILTER}
        ORDER BY collection, name
      `);
      
      // For each material, get its dimension variants
      for (const material of rows) {
        const dims = 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`,
          [material.id]
        );
        material.dimensions = dims.rows;
      }
      
      res.json(rows);
    } else {
      // Fallback to legacy finishes
      const { rows } = await pool.query(`
        SELECT f.id, f.code, f.label, f.panel_format_id,
               pf.width_mm, pf.height_mm, pf.price_chf,
               'legacy' as source
        FROM finishes f
        JOIN panel_formats pf ON pf.id = f.panel_format_id
        WHERE f.active = 1
        ORDER BY f.id
      `);
      res.json(rows);
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
}