const { Router } = require('express');
const { db, newId, h, bad, conflict, notFound, scope, ownerFor } = require('./core.js');

const r = Router();

/**
 * Teams and jockeys are the same shape of resource, so one factory builds both
 * routers. Four endpoints each: list, create, update, delete — eight in total.
 */
function crud({ table, prefix, label, fields, shape }) {
  const rt = Router();

  /* list */
  rt.get('/', h((req, res) => {
    const s = scope(req.admin, 'x');
    const where = [s.sql], params = [...s.params];
    if (req.query.q) { where.push('x.name LIKE ?'); params.push(`%${req.query.q}%`); }
    if (req.query.active === 'true') where.push('x.is_active = 1');

    const rows = db.prepare(`SELECT x.*, a.label AS operator FROM ${table} x
      JOIN admins a ON a.id = x.owner_admin_id
      WHERE ${where.join(' AND ')} ORDER BY x.name`).all(...params);
    res.json({ data: rows.map(shape), meta: { count: rows.length } });
  }));

  /* create */
  rt.post('/', h((req, res) => {
    const b = req.body || {};
    if (!b.name) bad('name is required.');
    const owner = ownerFor(req.admin, b.owner_admin_id);

    if (db.prepare(`SELECT 1 FROM ${table} WHERE owner_admin_id = ? AND name = ?`).get(owner, b.name))
      conflict(`This operator already has a ${label} called "${b.name}".`);

    const id = newId(prefix);
    const cols = ['id', 'owner_admin_id', 'name', ...fields, 'stats_json'];
    const vals = [id, owner, b.name, ...fields.map(f => b[f] ?? null), JSON.stringify(b.stats || {})];
    db.prepare(`INSERT INTO ${table} (${cols.join(',')}) VALUES (${cols.map(() => '?').join(',')})`).run(...vals);

    res.status(201).json({ data: shape(db.prepare(`SELECT * FROM ${table} WHERE id = ?`).get(id)) });
  }));

  /* update */
  rt.put('/:id', h((req, res) => {
    const s = scope(req.admin, 'x');
    const row = db.prepare(`SELECT x.* FROM ${table} x WHERE x.id = ? AND ${s.sql}`).get(req.params.id, ...s.params);
    if (!row) notFound(`${label} not found.`);

    const sets = [], params = [];
    for (const f of ['name', ...fields, 'is_active'])
      if (f in (req.body || {})) { sets.push(`${f} = ?`); params.push(req.body[f]); }
    if ('stats' in (req.body || {})) { sets.push('stats_json = ?'); params.push(JSON.stringify(req.body.stats)); }
    if (!sets.length) bad('No editable fields supplied.', { allowed: ['name', ...fields, 'is_active', 'stats'] });

    db.prepare(`UPDATE ${table} SET ${sets.join(', ')} WHERE id = ?`).run(...params, row.id);
    res.json({ data: shape(db.prepare(`SELECT * FROM ${table} WHERE id = ?`).get(row.id)) });
  }));

  /* delete */
  rt.delete('/:id', h((req, res) => {
    const s = scope(req.admin, 'x');
    const row = db.prepare(`SELECT x.* FROM ${table} x WHERE x.id = ? AND ${s.sql}`).get(req.params.id, ...s.params);
    if (!row) notFound(`${label} not found.`);

    const col = table === 'teams' ? 'team_id' : 'jockey_id';
    const used = db.prepare(`SELECT count(*) n FROM race_participants WHERE ${col} = ?`).get(row.id).n;

    // Deleting would orphan historical racecards, so a used record is retired instead.
    if (used) {
      db.prepare(`UPDATE ${table} SET is_active = 0 WHERE id = ?`).run(row.id);
      return res.json({ data: { id: row.id, deactivated: true,
        message: `This ${label} appears on ${used} racecard${used > 1 ? 's' : ''}, so it was retired rather than deleted.` } });
    }
    db.prepare(`DELETE FROM ${table} WHERE id = ?`).run(row.id);
    res.json({ data: { id: row.id, deleted: true } });
  }));

  return rt;
}

/* ── 1-4: teams ──────────────────────────────────────────────────────────── */
r.use('/teams', crud({
  table: 'teams', prefix: 'tm', label: 'stable', fields: ['silks', 'base'],
  shape: t => ({ id: t.id, owner_admin_id: t.owner_admin_id, operator: t.operator,
    name: t.name, silks: t.silks, base: t.base, is_active: !!t.is_active,
    stats: JSON.parse(t.stats_json || '{}'), created_at: t.created_at })
}));

/* ── 5-8: jockeys ────────────────────────────────────────────────────────── */
r.use('/jockeys', crud({
  table: 'jockeys', prefix: 'jk', label: 'jockey', fields: ['claim_kg', 'riding_weight'],
  shape: j => ({ id: j.id, owner_admin_id: j.owner_admin_id, operator: j.operator,
    name: j.name, claim_kg: j.claim_kg, riding_weight: j.riding_weight, is_active: !!j.is_active,
    stats: JSON.parse(j.stats_json || '{}'), created_at: j.created_at })
}));

module.exports = r;