Art-gallery/server/routes/tours.js

const express = require('express');
const pool = require('../db');
const { requirePermission, loadStaffUser } = require('../middleware/auth');
const { logCuratorAction } = require('../audit-log');
const { enrichPaintingRow } = require('../image-service');
const { localizePaintings, resolveLocale, translationStatuses } = require('../translation-service');

const router = express.Router();

const INFLUENCE_LINKS_EXISTS = `EXISTS (
  SELECT 1 FROM painting_influence_sources pis WHERE pis.painting_id = p.id
)`;

function parseId(value) {
  const n = Number(value);
  return Number.isFinite(n) && n > 0 ? Math.floor(n) : null;
}

function localeContext(req) {
  return {
    locale: resolveLocale(req),
    statuses: translationStatuses(req),
  };
}

async function loadTourStops(tourId, req) {
  const { rows } = await pool.query(
    `SELECT ts.id AS stop_id, ts.sort_order, ts.body,
            p.*,
            a.name AS artist_name,
            a.id AS artist_id,
            p.checkup_checked,
            p.checkup_fixed,
            (${INFLUENCE_LINKS_EXISTS}) AS has_influence_links
     FROM tour_stops ts
     JOIN paintings p ON p.id = ts.painting_id
     JOIN artists a ON a.id = p.artist_id
     WHERE ts.tour_id = $1
     ORDER BY ts.sort_order ASC, ts.id ASC`,
    [tourId],
  );

  const { locale, statuses } = localeContext(req);
  const localized = await localizePaintings(rows, locale, statuses);
  const paintings = localized.map((row) => {
    const enriched = enrichPaintingRow(row);
    return enriched;
  });
  const stopBodies = {};
  for (const row of rows) {
    stopBodies[row.id] = row.body || '';
  }
  return { paintings, stopBodies, stopCount: rows.length };
}

function mapTourSummary(row) {
  return {
    id: row.id,
    title: row.title,
    description: row.description || '',
    status: row.status,
    coverPaintingId: row.cover_painting_id,
    coverThumbnailPath: row.cover_thumbnail_path || null,
    coverImagePath: row.cover_image_path || null,
    stopCount: Number(row.stop_count) || 0,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

router.get('/', async (req, res) => {
  try {
    const { rows } = await pool.query(
      `SELECT t.*,
              cp.thumbnail_path AS cover_thumbnail_path,
              cp.image_path AS cover_image_path,
              (SELECT COUNT(*)::int FROM tour_stops s WHERE s.tour_id = t.id) AS stop_count
       FROM tours t
       LEFT JOIN paintings cp ON cp.id = t.cover_painting_id
       WHERE t.status = 'published'
       ORDER BY t.updated_at DESC, t.title ASC`,
    );
    res.json({ tours: rows.map(mapTourSummary) });
  } catch (err) {
    console.error('Tours list error:', err.message);
    res.status(500).json({ error: 'Failed to list tours' });
  }
});

router.get('/admin', requirePermission('tours'), async (_req, res) => {
  try {
    const { rows } = await pool.query(
      `SELECT t.*,
              cp.thumbnail_path AS cover_thumbnail_path,
              cp.image_path AS cover_image_path,
              (SELECT COUNT(*)::int FROM tour_stops s WHERE s.tour_id = t.id) AS stop_count
       FROM tours t
       LEFT JOIN paintings cp ON cp.id = t.cover_painting_id
       ORDER BY t.updated_at DESC, t.title ASC`,
    );
    res.json({ tours: rows.map(mapTourSummary) });
  } catch (err) {
    console.error('Tours admin list error:', err.message);
    res.status(500).json({ error: 'Failed to list tours' });
  }
});

router.get('/:id', async (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid id' });

    const { rows } = await pool.query(
      `SELECT t.*,
              cp.thumbnail_path AS cover_thumbnail_path,
              cp.image_path AS cover_image_path,
              (SELECT COUNT(*)::int FROM tour_stops s WHERE s.tour_id = t.id) AS stop_count
       FROM tours t
       LEFT JOIN paintings cp ON cp.id = t.cover_painting_id
       WHERE t.id = $1`,
      [id],
    );
    if (!rows[0]) return res.status(404).json({ error: 'Tour not found' });

    const tour = rows[0];
    let isCurator = false;
    if (req.session?.userId) {
      const user = await loadStaffUser(req.session.userId);
      isCurator = Boolean(user && user.is_active);
    }
    if (tour.status !== 'published' && !isCurator) {
      return res.status(404).json({ error: 'Tour not found' });
    }

    const { paintings, stopBodies } = await loadTourStops(id, req);
    const { locale } = localeContext(req);

    res.json({
      locale,
      tour: mapTourSummary(tour),
      paintings,
      stopBodies,
    });
  } catch (err) {
    console.error('Tour detail error:', err.message);
    res.status(500).json({ error: 'Failed to load tour' });
  }
});

router.post('/', requirePermission('tours'), async (req, res) => {
  try {
    const title = typeof req.body?.title === 'string' ? req.body.title.trim() : '';
    if (!title) return res.status(400).json({ error: 'title required' });
    const description = typeof req.body?.description === 'string' ? req.body.description : '';
    const status = req.body?.status === 'published' ? 'published' : 'draft';

    const { rows } = await pool.query(
      `INSERT INTO tours (title, description, status)
       VALUES ($1, $2, $3)
       RETURNING *`,
      [title, description, status],
    );

    await logCuratorAction({
      userId: req.curatorUser.id,
      action: 'tour.create',
      resourceType: 'tour',
      resourceId: rows[0].id,
      details: { title, status },
      req,
    });

    res.status(201).json({ tour: mapTourSummary({ ...rows[0], stop_count: 0 }) });
  } catch (err) {
    console.error('Tour create error:', err.message);
    res.status(500).json({ error: 'Failed to create tour' });
  }
});

router.patch('/:id', requirePermission('tours'), async (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid id' });

    const { rows: existing } = await pool.query('SELECT * FROM tours WHERE id = $1', [id]);
    if (!existing[0]) return res.status(404).json({ error: 'Tour not found' });

    const title =
      typeof req.body?.title === 'string' ? req.body.title.trim() : existing[0].title;
    if (!title) return res.status(400).json({ error: 'title required' });

    const description =
      typeof req.body?.description === 'string' ? req.body.description : existing[0].description;
    let status = existing[0].status;
    if (req.body?.status === 'published' || req.body?.status === 'draft') {
      status = req.body.status;
    }

    let coverPaintingId = existing[0].cover_painting_id;
    if (req.body?.coverPaintingId === null) coverPaintingId = null;
    else if (req.body?.coverPaintingId != null) {
      const cid = parseId(req.body.coverPaintingId);
      if (!cid) return res.status(400).json({ error: 'Invalid coverPaintingId' });
      coverPaintingId = cid;
    }

    const { rows } = await pool.query(
      `UPDATE tours
       SET title = $2, description = $3, status = $4, cover_painting_id = $5
       WHERE id = $1
       RETURNING *`,
      [id, title, description, status, coverPaintingId],
    );

    await logCuratorAction({
      userId: req.curatorUser.id,
      action: 'tour.update',
      resourceType: 'tour',
      resourceId: id,
      details: { title, status, coverPaintingId },
      req,
    });

    const { rows: countRows } = await pool.query(
      'SELECT COUNT(*)::int AS n FROM tour_stops WHERE tour_id = $1',
      [id],
    );

    res.json({ tour: mapTourSummary({ ...rows[0], stop_count: countRows[0].n }) });
  } catch (err) {
    console.error('Tour update error:', err.message);
    res.status(500).json({ error: 'Failed to update tour' });
  }
});

router.delete('/:id', requirePermission('tours'), async (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid id' });

    const { rows } = await pool.query('DELETE FROM tours WHERE id = $1 RETURNING id, title', [id]);
    if (!rows[0]) return res.status(404).json({ error: 'Tour not found' });

    await logCuratorAction({
      userId: req.curatorUser.id,
      action: 'tour.delete',
      resourceType: 'tour',
      resourceId: id,
      details: { title: rows[0].title },
      req,
    });

    res.json({ ok: true });
  } catch (err) {
    console.error('Tour delete error:', err.message);
    res.status(500).json({ error: 'Failed to delete tour' });
  }
});

router.put('/:id/stops', requirePermission('tours'), async (req, res) => {
  const client = await pool.connect();
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid id' });

    const { rows: tours } = await client.query('SELECT id FROM tours WHERE id = $1', [id]);
    if (!tours[0]) return res.status(404).json({ error: 'Tour not found' });

    const stops = Array.isArray(req.body?.stops) ? req.body.stops : null;
    if (!stops) return res.status(400).json({ error: 'stops array required' });
    if (stops.length > 200) return res.status(400).json({ error: 'Too many stops (max 200)' });

    const normalized = [];
    const seen = new Set();
    for (let i = 0; i < stops.length; i += 1) {
      const paintingId = parseId(stops[i]?.paintingId ?? stops[i]?.painting_id);
      if (!paintingId) {
        return res.status(400).json({ error: `Invalid paintingId at index ${i}` });
      }
      if (seen.has(paintingId)) continue;
      seen.add(paintingId);
      normalized.push({
        paintingId,
        body: typeof stops[i]?.body === 'string' ? stops[i].body : '',
        sortOrder: i,
      });
    }

    if (normalized.length) {
      const ids = normalized.map((s) => s.paintingId);
      const { rows: found } = await client.query(
        'SELECT id FROM paintings WHERE id = ANY($1::int[])',
        [ids],
      );
      if (found.length !== ids.length) {
        return res.status(400).json({ error: 'One or more paintings not found' });
      }
    }

    await client.query('BEGIN');
    await client.query('DELETE FROM tour_stops WHERE tour_id = $1', [id]);
    for (const stop of normalized) {
      await client.query(
        `INSERT INTO tour_stops (tour_id, painting_id, sort_order, body)
         VALUES ($1, $2, $3, $4)`,
        [id, stop.paintingId, stop.sortOrder, stop.body],
      );
    }

    // Auto-set cover from first stop when cover empty
    const { rows: tourRows } = await client.query(
      'SELECT cover_painting_id FROM tours WHERE id = $1',
      [id],
    );
    if (!tourRows[0].cover_painting_id && normalized[0]) {
      await client.query('UPDATE tours SET cover_painting_id = $2 WHERE id = $1', [
        id,
        normalized[0].paintingId,
      ]);
    }

    await client.query('COMMIT');

    await logCuratorAction({
      userId: req.curatorUser.id,
      action: 'tour.stops',
      resourceType: 'tour',
      resourceId: id,
      details: { stopCount: normalized.length },
      req,
    });

    const detail = await loadTourStops(id, req);
    res.json({
      ok: true,
      stopCount: detail.stopCount,
      paintings: detail.paintings,
      stopBodies: detail.stopBodies,
    });
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    console.error('Tour stops error:', err.message);
    res.status(500).json({ error: 'Failed to save tour stops' });
  } finally {
    client.release();
  }
});

module.exports = router;