dwroller/database/routes/rulesRoutes.js

const express = require('express');
const fs = require('fs');
const path = require('path');
const { rulesHelpers, logToFile } = require('../mariadb');
const { requireGM } = require('../auth');
const router = express.Router();

console.log('Rules routes registered (MariaDB)');

const rulesJsonPath = path.join(__dirname, '..', 'rules', 'rules-database.json');

function parseMaybeJson(value, fallback = []) {
  if (Array.isArray(value)) return value;
  if (!value) return fallback;
  try {
    const parsed = JSON.parse(value);
    return Array.isArray(parsed) ? parsed : fallback;
  } catch (e) {
    return fallback;
  }
}

// Helpers to clean up OCR/extracted text
function cleanText(s) {
  if (!s) return s;
  // Replace Windows newlines, collapse multi-whitespace, trim
  let out = s.replace(/\r\n/g, '\n').replace(/\n{3,}/g, '\n\n');
  out = out.replace(/[ \t]+/g, ' ');
  out = out.replace(/\n\s+/g, '\n');
  out = out.replace(/\s+\n/g, '\n');
  // Remove weird control characters
  out = out.replace(/[\x00-\x09\x0B\x0C\x0E-\x1F]/g, '');
  return out.trim();
}

function cleanTitle(t) {
  if (!t) return t;
  let s = String(t).trim();
  // If title is all-caps, make sentence case-ish
  if (s === s.toUpperCase() && s.length > 1) {
    s = s.toLowerCase();
    s = s.charAt(0).toUpperCase() + s.slice(1);
  }
  // Collapse whitespace
  s = s.replace(/\s+/g, ' ');
  return s;
}

function normalizeRule(r) {
  const ruleId = r.rule_id || r.ruleId || r.id;
  return {
    id: ruleId || r.id,
    numericId: Number.isInteger(r.id) ? r.id : undefined,
    rule_id: ruleId,
    title: cleanTitle(r.title),
    summary: cleanText(r.summary || ''),
    content: cleanText(r.content),
    examples: cleanText(r.examples || ''),
    page: r.page,
    pageEnd: r.page_end || r.pageEnd || r.page,
    source: r.source,
    sourceAbbr: r.source_abbr || r.sourceAbbr,
    category: r.category,
    tags: parseMaybeJson(r.tags),
    aliases: parseMaybeJson(r.aliases),
    relatedRules: parseMaybeJson(r.related_rules || r.relatedRules),
    sourceMethod: r.source_method || r.sourceMethod || 'unknown',
    confidence: r.confidence == null ? null : Number(r.confidence),
    midgamePriority: r.midgame_priority || r.midgamePriority || 0
  };
}

function getRulesFromJson() {
  try {
    if (!fs.existsSync(rulesJsonPath)) return [];
    const data = JSON.parse(fs.readFileSync(rulesJsonPath, 'utf8'));
    return (data.rules || []).map(normalizeRule);
  } catch (e) {
    console.error('Failed to read rules JSON fallback:', e);
    logToFile('Error reading rules JSON fallback:', e);
    return [];
  }
}

function mergeRules(dbRules, jsonRules) {
  const byId = new Map();
  for (const rule of jsonRules || []) {
    byId.set(String(rule.rule_id || rule.id), rule);
  }
  for (const rule of dbRules || []) {
    byId.set(String(rule.rule_id || rule.id), rule);
  }
  return Array.from(byId.values());
}

// Fetch all rules from MariaDB and merge in the generated JSON midgame database.
async function getAllRules() {
  try {
    const rows = await rulesHelpers.getAll();
    return mergeRules(rows && rows.length ? rows.map(normalizeRule) : [], getRulesFromJson());
  } catch (e) {
    console.error('Failed to read rules from MariaDB:', e);
    logToFile('Error getting all rules:', e);
    return getRulesFromJson();
  }
}

async function getRuleById(ruleId) {
  try {
    const rows = await rulesHelpers.getAll();
    const sourceRows = mergeRules(rows && rows.length ? rows.map(normalizeRule) : [], getRulesFromJson());
    const row = sourceRows.find(r => String(r.rule_id) === String(ruleId) || String(r.id) === String(ruleId) || String(r.numericId) === String(ruleId));
    if (!row) return null;
    return row;
  } catch (e) {
    console.error('Failed to read rule by id:', e);
    logToFile('Error getting rule by id:', ruleId, e);
    return getRulesFromJson().find(r => String(r.rule_id) === String(ruleId) || String(r.id) === String(ruleId)) || null;
  }
}

// Get all rule categories
router.get('/categories', async (req, res) => {
  try {
    const rows = await getAllRules();
    const categories = [...new Set(rows.map(r => r.category).filter(Boolean))];
    const categoryList = categories
      .sort((a, b) => a.localeCompare(b))
      .map(cat => ({ id: cat, name: cleanTitle(cat.replace(/_/g, ' ')) }));
    res.json([{ id: 'all', name: 'All Rules' }, ...categoryList]);
  } catch (error) {
    console.error('Categories error:', error);
    res.status(500).json({ error: 'Failed to get categories' });
  }
});

// Search rules (MariaDB)
router.get('/search', async (req, res) => {
  try {
    const { q: query, category, tag, source, midgame, limit = 20 } = req.query;
    if (!query || !query.trim()) return res.json([]);
    const limitInt = Math.max(1, parseInt(limit) || 20);
    
    const allRules = await getAllRules();
    const term = query.trim().toLowerCase();
    const tokens = term.split(/\s+/).filter(Boolean);

    const includesTerm = (value, t = term) => String(value || '').toLowerCase().includes(t);
    const arrayIncludes = (arr, t = term) => (arr || []).some(v => includesTerm(v, t));
    const tokenMatch = (rule) => {
      const haystacks = [
        rule.title,
        rule.summary,
        rule.content,
        rule.examples,
        rule.category,
        ...(rule.tags || []),
        ...(rule.aliases || [])
      ].map(v => String(v || '').toLowerCase());
      return tokens.every(token => haystacks.some(v => v.includes(token)));
    };
    
    const score = (rule, t) => {
      const title = (rule.title || '').toLowerCase();
      const summary = (rule.summary || '').toLowerCase();
      const content = (rule.content || '').toLowerCase();
      const examples = (rule.examples || '').toLowerCase();
      const tags = (rule.tags || []).map(v => String(v).toLowerCase());
      const aliases = (rule.aliases || []).map(v => String(v).toLowerCase());
      const exactTitle = title === t;
      const titleStarts = title.startsWith(t);
      const titleContains = title.includes(t);
      const exactAlias = aliases.includes(t);
      const aliasContains = aliases.some(a => a.includes(t));
      const tagContains = tags.some(a => a.includes(t));
      const summaryContains = summary.includes(t);
      const isCanonical = rule.source === 'Core Rulebook';
      const tokenHits = tokens.reduce((sum, token) => {
        return sum +
          (title.includes(token) ? 8 : 0) +
          (aliases.some(a => a.includes(token)) ? 6 : 0) +
          (tags.some(a => a.includes(token)) ? 4 : 0) +
          (summary.includes(token) ? 3 : 0) +
          (content.includes(token) ? 1 : 0) +
          (examples.includes(token) ? 1 : 0);
      }, 0);
      // Higher = shown first
      return (exactTitle ? 100 : 0) +
             (exactAlias ? 90 : 0) +
             (titleStarts ? 50 : 0) +
             (titleContains ? 20 : 0) +
             (aliasContains ? 18 : 0) +
             (tagContains ? 15 : 0) +
             (summaryContains ? 8 : 0) +
             (isCanonical ? 10 : 0) +
             (Number(rule.midgamePriority) || 0) +
             tokenHits +
             (content.includes(t) ? 1 : 0) +
             (examples.includes(t) ? 1 : 0);
    };

    let filtered = allRules.filter(rule => {
      const titleMatch = includesTerm(rule.title);
      const summaryMatch = includesTerm(rule.summary);
      const contentMatch = includesTerm(rule.content);
      const examplesMatch = includesTerm(rule.examples);
      const tagMatch = arrayIncludes(rule.tags);
      const aliasMatch = arrayIncludes(rule.aliases);
      const categoryMatch = !category || category === 'all' || rule.category === category;
      const sourceMatch = !source || source === 'all' || rule.sourceAbbr === source || rule.source === source;
      const midgameMatch = !midgame || midgame === 'false' || Number(rule.midgamePriority || 0) >= 70;
      const explicitTagMatch = !tag || tag === 'all' || (rule.tags || []).some(t => String(t).toLowerCase() === String(tag).toLowerCase());
      return (titleMatch || summaryMatch || contentMatch || examplesMatch || tagMatch || aliasMatch || tokenMatch(rule)) && categoryMatch && sourceMatch && midgameMatch && explicitTagMatch;
    });

    // If category filtering yielded no results, try without category filter
    if (filtered.length === 0 && category && category !== 'all') {
      filtered = allRules.filter(rule => {
        const sourceMatch = !source || source === 'all' || rule.sourceAbbr === source || rule.source === source;
        return sourceMatch && (includesTerm(rule.title) || includesTerm(rule.summary) || includesTerm(rule.content) || includesTerm(rule.examples) || arrayIncludes(rule.tags) || arrayIncludes(rule.aliases) || tokenMatch(rule));
      });
    }

    // Sort: title matches first, then Core Rulebook source, then content matches
    filtered.sort((a, b) => score(b, term) - score(a, term));

    // Limit results (content kept full – modal fetches via /rule/:id anyway)
    const results = filtered.slice(0, limitInt);
    
    res.json(results);
  } catch (error) {
    console.error('Search error:', error);
    res.status(500).json({ error: 'Search failed' });
  }
});

// Get a specific rule by ID
router.get('/rule/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const rule = await getRuleById(id);
    if (!rule) return res.status(404).json({ error: 'Rule not found' });
    res.json(rule);
  } catch (error) {
    console.error('Get rule error:', error);
    res.status(500).json({ error: 'Failed to get rule' });
  }
});

// Get random rules for discovery
router.get('/random', async (req, res) => {
  try {
    const { count = 5, category, midgame } = req.query;
    const max = Math.max(1, parseInt(count) || 5);
    
    const allRules = await getAllRules();
    let filtered = allRules;
    
    if (category && category !== 'all') {
      filtered = allRules.filter(rule => rule.category === category);
    }
    if (midgame && midgame !== 'false') {
      filtered = filtered.filter(rule => Number(rule.midgamePriority || 0) >= 70);
    }
    
    // Shuffle and pick random rules
    const shuffled = filtered.sort(() => 0.5 - Math.random());
    const randomRules = shuffled.slice(0, max);
    
    res.json(randomRules);
  } catch (error) {
    console.error('Random rules error:', error);
    res.status(500).json({ error: 'Failed to get random rules' });
  }
});

// Get rules statistics
router.get('/stats', async (req, res) => {
  try {
    const allRules = await getAllRules();
    const totalRules = allRules.length;
    
    const categoryCount = {};
    const sourceCount = {};
    const methodCount = {};
    let midgameRules = 0;
    
    allRules.forEach(rule => {
      if (rule.category) {
        categoryCount[rule.category] = (categoryCount[rule.category] || 0) + 1;
      }
      if (rule.source) {
        sourceCount[rule.source] = (sourceCount[rule.source] || 0) + 1;
      }
      if (rule.sourceMethod) {
        methodCount[rule.sourceMethod] = (methodCount[rule.sourceMethod] || 0) + 1;
      }
      if (Number(rule.midgamePriority || 0) >= 70) midgameRules += 1;
    });
    
    const categories = Object.entries(categoryCount).map(([category, c]) => ({ category, c }));
    const sources = Object.entries(sourceCount).map(([source, c]) => ({ source, c }));
    const sourceMethods = Object.entries(methodCount).map(([sourceMethod, c]) => ({ sourceMethod, c }));
    
    res.json({ totalRules, midgameRules, categories, sources, sourceMethods, searchTerms: 0 });
  } catch (error) {
    console.error('Stats error:', error);
    res.status(500).json({ error: 'Failed to get stats' });
  }
});

// Reload the rules database (GM only)
router.post('/reload', async (req, res) => {
  try {
    if (!(await requireGM(req, res))) return;
    
    const allRules = await getAllRules();
    const totalRules = allRules.length;
    
    res.json({ success: true, totalRules, message: 'Rules are MariaDB-backed; already loaded' });
  } catch (error) {
    console.error('Reload error:', error);
    res.status(500).json({ error: 'Failed to reload database' });
  }
});

// ── Dedup rules (GM-only) ─────────────────────────────────────────────────────
router.post('/admin/tag-and-dedup', async (req, res) => {
  if (!(await requireGM(req, res))) return;

  const { pool } = require('../mariadb');

  try {
    const results = {};

    // 1. Re-tag sanitized → Core Rulebook
    const [r1] = await pool.execute(
      `UPDATE rules SET source='Core Rulebook', source_abbr='CR' WHERE source='sanitized'`
    );
    results.retagged_sanitized = r1.affectedRows;

    // 2. Re-tag fandom/url → Core Rulebook
    const [r2] = await pool.execute(
      `UPDATE rules SET source='Core Rulebook', source_abbr='CR' WHERE source LIKE '%fandom%' OR source LIKE '%40k-rpg%'`
    );
    results.retagged_fandom = r2.affectedRows;

    // 3. Dedup in JS — load titles+ids, group by normalised title, delete inferior copies
    const SOURCE_PRIORITY = ['Core Rulebook','First Founding','Rites of Battle','Mark of the Xenos','Honour the Chapter'];
    const srcPrio = s => { const i = SOURCE_PRIORITY.indexOf(s); return i === -1 ? 99 : i; };
    const normTitle = t => (t||'').toLowerCase().replace(/[^a-z0-9 ]/g,' ').replace(/\s+/g,' ').trim();

    const [allRows] = await pool.execute('SELECT id, title, source, LENGTH(content) as clen FROM rules');
    const byTitle = {};
    for (const r of allRows) {
      const k = normTitle(r.title);
      if (!k) continue;
      if (!byTitle[k]) byTitle[k] = [];
      byTitle[k].push(r);
    }

    const toDelete = [];
    for (const group of Object.values(byTitle)) {
      if (group.length < 2) continue;
      group.sort((a, b) => {
        const pd = srcPrio(a.source) - srcPrio(b.source);
        return pd !== 0 ? pd : b.clen - a.clen;
      });
      for (const r of group.slice(1)) toDelete.push(r.id);
    }
    results.duplicates_found = toDelete.length;

    if (toDelete.length > 0) {
      for (let i = 0; i < toDelete.length; i += 500) {
        const batch = toDelete.slice(i, i + 500);
        const ph = batch.map(() => '?').join(',');
        await pool.execute(`DELETE FROM rules WHERE id IN (${ph})`, batch);
      }
    }
    results.deleted = toDelete.length;

    // 4. Final counts
    const [stats] = await pool.execute(
      `SELECT source, COUNT(*) as cnt FROM rules GROUP BY source ORDER BY cnt DESC`
    );
    results.sources = stats;

    const [total] = await pool.execute('SELECT COUNT(*) as cnt FROM rules');
    results.total = total[0].cnt;

    res.json({ success: true, ...results });
  } catch (e) {
    console.error('tag-and-dedup error:', e);
    res.status(500).json({ error: e.message });
  }
});

console.log('Rules routes registered');
module.exports = router;