// ── Sync Receiver Routes (run on cPanel/online server) ─────────────────────────
// Local backends call these endpoints to PUSH their changes and PULL new data.
// Authentication is via shared `X-Sync-Token` header (configured in env).
//
// Endpoints:
//   POST /api/sync/receive          — receive a batch of records for upsert
//   GET  /api/sync/pull             — return changed records since `since`/cursor
//   POST /api/sync/tombstones       — receive deletions to apply
//   GET  /api/sync/uploads/list     — list files in public/uploads (name + size + mtime)
//   GET  /api/sync/uploads/file/:n  — stream a single uploaded file
//   POST /api/sync/uploads/file     — receive an uploaded file (push from local → online)
//
// NOTE: These complement the OUTBOUND sync logic in services/sync.js. The same
// Express backend can act as both a client (when running locally) and as a
// server (when running on cPanel) — the receiver just opens DB writes via the
// shared `pool`.

const { Router } = require('express');
const path = require('path');
const fs = require('fs');
const multer = require('multer');
const { pool } = require('../db/pool');

const router = Router();

const UPLOADS_DIR = path.join(__dirname, '..', 'public', 'uploads');
if (!fs.existsSync(UPLOADS_DIR)) fs.mkdirSync(UPLOADS_DIR, { recursive: true });

const SYNC_TOKEN = process.env.SYNC_TOKEN || '';

const ALLOWED_TABLES = new Set([
  'schools', 'departments', 'users', 'courses', 'questions',
  'exams', 'exam_questions', 'exam_pins', 'exam_attempts',
  'answers', 'site_settings',
]);

const TABLES_WITH_SYNCED_FLAG = new Set([
  'schools', 'departments', 'users', 'courses', 'questions',
  'exams', 'exam_questions', 'exam_pins', 'exam_attempts', 'answers',
]);

const COMPOSITE_PK_TABLES = {
  exam_questions: ['exam_id', 'question_id'],
};

const TABLE_TS_COLUMN = {
  answers: 'saved_at',
  exam_attempts: 'created_at',
  exam_pins: 'used_at',
  site_settings: 'updated_at',
};

const TABLE_JSON_COLUMNS = {
  questions: new Set(['options', 'correct_answer']),
  site_settings: new Set(['settings']),
};

const HAS_UPDATED_AT = new Set([
  'exams', 'questions', 'users', 'courses', 'departments', 'schools', 'site_settings',
]);

const IMMUTABLE_COLS = new Set(['created_at']);

function getTsColumn(t) { return TABLE_TS_COLUMN[t] || 'updated_at'; }

function toDbValue(table, col, value) {
  if (value === undefined) return undefined;
  const jsonCols = TABLE_JSON_COLUMNS[table];
  if (jsonCols?.has(col)) return value === null ? null : JSON.stringify(value);
  return value;
}

// ── Auth middleware ──
function checkSyncToken(req, res, next) {
  if (!SYNC_TOKEN) return next(); // open in dev (no token configured)
  const provided = req.header('x-sync-token') || '';
  if (provided !== SYNC_TOKEN) {
    return res.status(401).json({ error: 'Invalid sync token' });
  }
  next();
}

// ── POST /api/sync/receive ──
router.post('/receive', checkSyncToken, async (req, res) => {
  const { table, records } = req.body || {};
  if (!table || typeof table !== 'string') return res.status(400).json({ error: "Missing 'table'" });
  if (!ALLOWED_TABLES.has(table)) return res.status(403).json({ error: `Table '${table}' not allowed` });
  if (!Array.isArray(records) || records.length === 0) {
    return res.json({ received: 0, upserted: 0, skipped: 0 });
  }

  const compositePK = COMPOSITE_PK_TABLES[table];
  const hasUpdatedAt = HAS_UPDATED_AT.has(table);

  let upserted = 0;
  let skipped = 0;
  const errors = [];

  const client = await pool.connect();
  try {
    for (const remote of records) {
      try {
        if (compositePK) {
          if (!compositePK.every((k) => remote[k])) { skipped++; continue; }
        } else if (!remote?.id) { skipped++; continue; }

        // Last-write-wins via updated_at when both sides have it
        if (hasUpdatedAt && !compositePK) {
          const { rows } = await client.query(
            `SELECT updated_at FROM ${table} WHERE id = $1`,
            [remote.id],
          );
          if (rows[0]?.updated_at && remote.updated_at) {
            const localTs = new Date(rows[0].updated_at).getTime();
            const remoteTs = new Date(remote.updated_at).getTime();
            if (localTs > remoteTs) { skipped++; continue; }
          }
        }

        // Build payload (drop immutable columns)
        const entries = Object.entries(remote).filter(([k, v]) => {
          if (v === undefined) return false;
          if (IMMUTABLE_COLS.has(k)) return false;
          return true;
        });
        if (entries.length === 0) { skipped++; continue; }

        const cols = entries.map(([k]) => k);
        const values = entries.map(([k, v]) => toDbValue(table, k, v));

        // Mark as synced on the receiving side too (so it doesn't ricochet back)
        if (TABLES_WITH_SYNCED_FLAG.has(table) && !cols.includes('synced')) {
          cols.push('synced');
          values.push(true);
        }

        const placeholders = cols.map((_, i) => `$${i + 1}`).join(', ');
        const onConflict = compositePK ? compositePK.join(', ') : 'id';
        const updateSet = cols
          .filter((c) => !compositePK || !compositePK.includes(c))
          .map((c) => `${c} = EXCLUDED.${c}`)
          .join(', ');

        const sql = `
          INSERT INTO ${table} (${cols.join(', ')})
          VALUES (${placeholders})
          ON CONFLICT (${onConflict}) DO UPDATE SET ${updateSet || `${cols[0]} = EXCLUDED.${cols[0]}`}
        `;
        await client.query(sql, values);
        upserted++;
      } catch (e) {
        errors.push({ id: remote?.id || `${remote?.exam_id}/${remote?.question_id}`, error: e.message });
        skipped++;
      }
    }
  } finally {
    client.release();
  }

  res.json({ received: records.length, upserted, skipped, ...(errors.length ? { errors } : {}) });
});

// Coerce arbitrary timestamp input into ISO-8601 (Postgres-friendly).
function normalizeTs(value) {
  if (!value) return null;
  if (typeof value === 'string' && /^\d{4}-\d{2}-\d{2}T/.test(value)) return value;
  const d = new Date(value);
  return Number.isNaN(d.getTime()) ? null : d.toISOString();
}

// ── GET /api/sync/pull ──
router.get('/pull', checkSyncToken, async (req, res) => {
  const table = String(req.query.table || '');
  const sinceRaw = req.query.since ? String(req.query.since) : null;
  const cursorRaw = req.query.cursor ? String(req.query.cursor) : null;
  const limit = Math.min(parseInt(String(req.query.limit || '500'), 10) || 500, 1000);

  if (!table) return res.status(400).json({ error: "Missing 'table'" });
  if (!ALLOWED_TABLES.has(table)) return res.status(403).json({ error: `Table '${table}' not allowed` });

  const compositePK = COMPOSITE_PK_TABLES[table];
  const tsCol = getTsColumn(table);

  try {
    let sql;
    let params = [];
    if (table === 'exam_pins') {
      sql = `SELECT * FROM exam_pins`;
      const where = [];
      if (cursorRaw) { params.push(cursorRaw); where.push(`id > $${params.length}`); }
      if (where.length) sql += ` WHERE ${where.join(' AND ')}`;
      sql += ` ORDER BY id ASC LIMIT ${limit}`;
    } else if (compositePK) {
      const pk = compositePK[0];
      sql = `SELECT * FROM ${table}`;
      const where = [];
      if (cursorRaw) { params.push(cursorRaw); where.push(`${pk} > $${params.length}`); }
      if (where.length) sql += ` WHERE ${where.join(' AND ')}`;
      sql += ` ORDER BY ${pk} ASC LIMIT ${limit}`;
    } else {
      // Normalize timestamp inputs — clients sometimes send Date.toString() which
      // Postgres cannot parse (e.g. "Sun Apr 19 2026 21:03:08 GMT+0100 (West Africa Standard Time)").
      const since = normalizeTs(sinceRaw);
      const cursor = normalizeTs(cursorRaw);
      sql = `SELECT * FROM ${table}`;
      const where = [];
      if (since) { params.push(since); where.push(`${tsCol} > $${params.length}::timestamptz`); }
      if (cursor) { params.push(cursor); where.push(`${tsCol} > $${params.length}::timestamptz`); }
      if (where.length) sql += ` WHERE ${where.join(' AND ')}`;
      sql += ` ORDER BY ${tsCol} ASC LIMIT ${limit}`;
    }

    const { rows } = await pool.query(sql, params);

    let nextCursor = null;
    if (rows.length === limit) {
      const last = rows[rows.length - 1];
      if (table === 'exam_pins') nextCursor = String(last.id || '');
      else if (compositePK) nextCursor = String(last[compositePK[0]] || '');
      else nextCursor = last[tsCol] ? new Date(last[tsCol]).toISOString() : null;
    }

    res.json({
      table,
      records: rows,
      count: rows.length,
      nextCursor,
      pulledAt: new Date().toISOString(),
    });
  } catch (err) {
    console.error(`[SYNC PULL] ${table}:`, err.message);
    res.status(500).json({ error: `Pull failed for ${table}: ${err.message}` });
  }
});

// ── GET /api/sync/ids ──
// Returns the FULL set of primary-key identifiers for a table on the hub.
// Local nodes use this to detect rows that were deleted online (rows the local
// has but the hub no longer has) and remove them locally.
router.get('/ids', checkSyncToken, async (req, res) => {
  const table = String(req.query.table || '');
  if (!table) return res.status(400).json({ error: "Missing 'table'" });
  if (!ALLOWED_TABLES.has(table)) return res.status(403).json({ error: `Table '${table}' not allowed` });

  const compositePK = COMPOSITE_PK_TABLES[table];
  try {
    let sql;
    if (compositePK) {
      sql = `SELECT ${compositePK.join(', ')} FROM ${table}`;
    } else {
      sql = `SELECT id FROM ${table}`;
    }
    const { rows } = await pool.query(sql);
    res.json({ table, ids: rows, count: rows.length });
  } catch (err) {
    console.error(`[SYNC IDS] ${table}:`, err.message);
    res.status(500).json({ error: `IDs fetch failed for ${table}: ${err.message}` });
  }
});

// ── POST /api/sync/tombstones ──
router.post('/tombstones', checkSyncToken, async (req, res) => {
  const tombstones = Array.isArray(req.body?.tombstones) ? req.body.tombstones : [];
  if (tombstones.length === 0) return res.json({ deleted: 0 });

  let deleted = 0;
  const errors = [];
  const client = await pool.connect();
  try {
    for (const t of tombstones) {
      if (!t?.table || !t?.id) continue;
      if (!ALLOWED_TABLES.has(t.table)) continue;
      try {
        await client.query(`DELETE FROM ${t.table} WHERE id = $1`, [t.id]);
        deleted++;
      } catch (e) {
        errors.push({ id: t.id, error: e.message });
      }
    }
  } finally {
    client.release();
  }

  res.json({ received: tombstones.length, deleted, ...(errors.length ? { errors } : {}) });
});

// ── Uploaded-file sync ────────────────────────────────────────────────────────
// Filenames are restricted to a safe charset to prevent path traversal.
const SAFE_NAME_RE = /^[A-Za-z0-9._-]+$/;
function isSafeName(name) {
  return typeof name === 'string' && name.length > 0 && name.length < 256 && SAFE_NAME_RE.test(name);
}

// GET /api/sync/uploads/list
router.get('/uploads/list', checkSyncToken, (_req, res) => {
  try {
    const entries = fs.readdirSync(UPLOADS_DIR, { withFileTypes: true });
    const files = [];
    for (const ent of entries) {
      if (!ent.isFile()) continue;
      if (!isSafeName(ent.name)) continue;
      try {
        const st = fs.statSync(path.join(UPLOADS_DIR, ent.name));
        files.push({ name: ent.name, size: st.size, mtime: st.mtimeMs });
      } catch { /* skip */ }
    }
    res.json({ files, count: files.length });
  } catch (err) {
    res.status(500).json({ error: 'Failed to list uploads', message: err.message });
  }
});

// GET /api/sync/uploads/file/:name
router.get('/uploads/file/:name', checkSyncToken, (req, res) => {
  const name = req.params.name;
  if (!isSafeName(name)) return res.status(400).json({ error: 'Invalid filename' });
  const filePath = path.join(UPLOADS_DIR, name);
  // Ensure the resolved path stays within UPLOADS_DIR
  if (!filePath.startsWith(UPLOADS_DIR + path.sep)) {
    return res.status(400).json({ error: 'Invalid filename' });
  }
  if (!fs.existsSync(filePath)) return res.status(404).json({ error: 'Not found' });
  res.sendFile(filePath);
});

// POST /api/sync/uploads/file  (multipart: field `file`, optional field `name`)
const uploadStorage = multer.diskStorage({
  destination: (_req, _file, cb) => cb(null, UPLOADS_DIR),
  filename: (req, file, cb) => {
    const requested = (req.body && req.body.name) || file.originalname;
    if (!isSafeName(requested)) return cb(new Error('Invalid filename'));
    cb(null, requested);
  },
});
const uploader = multer({
  storage: uploadStorage,
  limits: { fileSize: 10 * 1024 * 1024 }, // 10MB
});

router.post('/uploads/file', checkSyncToken, (req, res) => {
  uploader.single('file')(req, res, (err) => {
    if (err) return res.status(400).json({ error: err.message });
    if (!req.file) return res.status(400).json({ error: 'No file uploaded' });
    res.json({ ok: true, name: req.file.filename, size: req.file.size });
  });
});

module.exports = router;

