`thibaut-room-settings-api-example.js`

272 lines
/**
 * Thibaut Room Settings API Example
 *
 * Example Express.js routes showing how to use the thibaut_room_settings table
 * to enhance product displays with room setting images.
 */

const { Pool } = require('pg');
const express = require('express');
const router = express.Router();

const pool = new Pool({
  connectionString: (process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified')
});

/**
 * GET /api/thibaut/room-settings/:sku
 *
 * Get all room setting images that feature a specific product SKU
 *
 * Example: GET /api/thibaut/room-settings/T15806
 *
 * Returns: Array of room setting images showing this product
 */
router.get('/api/thibaut/room-settings/:sku', async (req, res) => {
  try {
    const { sku } = req.params;

    const result = await pool.query(`
      SELECT
        ts.id,
        ts.collection_name,
        ts.collection_url,
        ts.image_url,
        ts.image_filename,
        ts.pattern_names,
        ts.mfr_skus,
        ts.sort_order
      FROM thibaut_room_settings ts
      WHERE $1 = ANY(ts.mfr_skus)
      ORDER BY ts.sort_order
    `, [sku]);

    res.json({
      sku: sku,
      count: result.rows.length,
      roomSettings: result.rows
    });

  } catch (err) {
    console.error('Error fetching room settings:', err);
    res.status(500).json({ error: 'Failed to fetch room settings' });
  }
});

/**
 * GET /api/thibaut/collection/:name/room-settings
 *
 * Get all room settings for a specific collection
 *
 * Example: GET /api/thibaut/collection/Islander/room-settings
 *
 * Returns: Array of room setting images in this collection
 */
router.get('/api/thibaut/collection/:name/room-settings', async (req, res) => {
  try {
    const { name } = req.params;
    const { matched_only } = req.query; // Optional: only return matched images

    let query = `
      SELECT
        ts.id,
        ts.collection_name,
        ts.image_url,
        ts.image_filename,
        ts.mfr_skus,
        array_length(ts.mfr_skus, 1) as sku_count,
        ts.sort_order
      FROM thibaut_room_settings ts
      WHERE ts.collection_name ILIKE $1
    `;

    if (matched_only === 'true') {
      query += ` AND ts.mfr_skus IS NOT NULL AND array_length(ts.mfr_skus, 1) > 0`;
    }

    query += ` ORDER BY ts.sort_order`;

    const result = await pool.query(query, [name]);

    res.json({
      collection: name,
      count: result.rows.length,
      roomSettings: result.rows
    });

  } catch (err) {
    console.error('Error fetching collection room settings:', err);
    res.status(500).json({ error: 'Failed to fetch collection room settings' });
  }
});

/**
 * GET /api/thibaut/product/:sku/with-room-settings
 *
 * Get product details with all associated room setting images
 *
 * Example: GET /api/thibaut/product/T15806/with-room-settings
 *
 * Returns: Product info + room settings showing this product
 */
router.get('/api/thibaut/product/:sku/with-room-settings', async (req, res) => {
  try {
    const { sku } = req.params;

    // Get product details
    const product = await pool.query(`
      SELECT
        tc.mfr_sku,
        tc.pattern_name,
        tc.color_name,
        tc.collection,
        tc.width,
        tc.roll_length,
        tc.pattern_repeat,
        tc.image_url as product_image,
        tc.product_url,
        tc.material,
        tc.product_type
      FROM thibaut_catalog tc
      WHERE tc.mfr_sku = $1
    `, [sku]);

    if (product.rows.length === 0) {
      return res.status(404).json({ error: 'Product not found' });
    }

    // Get room settings
    const roomSettings = await pool.query(`
      SELECT
        ts.id,
        ts.collection_name,
        ts.image_url,
        ts.image_filename,
        ts.sort_order
      FROM thibaut_room_settings ts
      WHERE $1 = ANY(ts.mfr_skus)
      ORDER BY ts.sort_order
    `, [sku]);

    res.json({
      product: product.rows[0],
      roomSettings: roomSettings.rows,
      roomSettingsCount: roomSettings.rows.length
    });

  } catch (err) {
    console.error('Error fetching product with room settings:', err);
    res.status(500).json({ error: 'Failed to fetch product data' });
  }
});

/**
 * GET /api/thibaut/room-settings/stats
 *
 * Get statistics about room settings by collection
 *
 * Example: GET /api/thibaut/room-settings/stats
 *
 * Returns: Summary statistics for all collections
 */
router.get('/api/thibaut/room-settings/stats', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT
        collection_name,
        COUNT(*) as total_images,
        COUNT(CASE WHEN mfr_skus IS NOT NULL AND array_length(mfr_skus, 1) > 0 THEN 1 END) as matched_images,
        ROUND(100.0 * COUNT(CASE WHEN mfr_skus IS NOT NULL AND array_length(mfr_skus, 1) > 0 THEN 1 END) / COUNT(*), 1) as match_percentage
      FROM thibaut_room_settings
      GROUP BY collection_name
      ORDER BY total_images DESC
    `);

    // Get overall stats
    const overall = await pool.query(`
      SELECT
        COUNT(*) as total_images,
        COUNT(CASE WHEN mfr_skus IS NOT NULL AND array_length(mfr_skus, 1) > 0 THEN 1 END) as matched_images,
        ROUND(100.0 * COUNT(CASE WHEN mfr_skus IS NOT NULL AND array_length(mfr_skus, 1) > 0 THEN 1 END) / COUNT(*), 2) as match_percentage
      FROM thibaut_room_settings
    `);

    res.json({
      overall: overall.rows[0],
      byCollection: result.rows
    });

  } catch (err) {
    console.error('Error fetching room settings stats:', err);
    res.status(500).json({ error: 'Failed to fetch statistics' });
  }
});

/**
 * GET /api/thibaut/room-settings/search
 *
 * Search room settings by pattern name
 *
 * Example: GET /api/thibaut/room-settings/search?q=Grasscloth
 *
 * Returns: Room settings matching the search query
 */
router.get('/api/thibaut/room-settings/search', async (req, res) => {
  try {
    const { q } = req.query;

    if (!q || q.length < 3) {
      return res.status(400).json({ error: 'Query must be at least 3 characters' });
    }

    const result = await pool.query(`
      SELECT
        ts.id,
        ts.collection_name,
        ts.image_url,
        ts.image_filename,
        ts.pattern_names,
        ts.mfr_skus,
        array_length(ts.mfr_skus, 1) as sku_count
      FROM thibaut_room_settings ts
      WHERE
        ts.collection_name ILIKE $1
        OR ts.image_filename ILIKE $1
        OR EXISTS (
          SELECT 1 FROM unnest(ts.pattern_names) pn
          WHERE pn ILIKE $1
        )
      ORDER BY ts.collection_name, ts.sort_order
      LIMIT 50
    `, [`%${q}%`]);

    res.json({
      query: q,
      count: result.rows.length,
      results: result.rows
    });

  } catch (err) {
    console.error('Error searching room settings:', err);
    res.status(500).json({ error: 'Failed to search room settings' });
  }
});

module.exports = router;

/**
 * USAGE EXAMPLES:
 *
 * 1. Add to your Express app:
 *    const thibautRoomSettingsRoutes = require('./thibaut-room-settings-api-example');
 *    app.use(thibautRoomSettingsRoutes);
 *
 * 2. Example requests:
 *    - Get room settings for SKU: GET http://localhost:7600/api/thibaut/room-settings/T15806
 *    - Get Islander collection: GET http://localhost:7600/api/thibaut/collection/Islander/room-settings
 *    - Get matched only: GET http://localhost:7600/api/thibaut/collection/Islander/room-settings?matched_only=true
 *    - Get product with rooms: GET http://localhost:7600/api/thibaut/product/T15806/with-room-settings
 *    - Get statistics: GET http://localhost:7600/api/thibaut/room-settings/stats
 *    - Search: GET http://localhost:7600/api/thibaut/room-settings/search?q=Grasscloth
 */