/**
 * Legacy Import Controller — FR-SMS3 (CSV/Excel legacy data ingestion)
 *
 * The SRS asks for "an automated CSV/Excel mapping pipeline for rapid,
 * error-free onboarding of existing historical school records" with the
 * Operations Admin able to "upload CSV/Excel files and map legacy data
 * columns to system database fields to mass-import historical student
 * records".
 *
 * Implementation choices
 * ----------------------
 * 1. **CSV only** in this controller. The SRS mentions "Excel" but the
 *    MVP has no `xlsx` parser in deps; we add only what's needed and
 *    leave the Excel path as a clear extension point. (An .xlsx import
 *    can be layered on by using `xlsx` (SheetJS) and reusing the same
 *    preview/commit logic.)
 *
 * 2. **Two-phase**: the client calls /preview first to see what the
 *    server inferred (column mapping, per-row errors). Once the
 *    operator confirms, the client calls /commit with the chosen
 *    mapping. This is the schema-mapping-UI flow described in the SRS
 *    §4.3 figure 2.
 *
 * 3. **Auto-detection**: we map common legacy column names (case
 *    insensitive, ignoring whitespace / underscores) to our schema
 *    fields. The mapping is suggested in /preview and overridable in
 *    /commit.
 *
 * 4. **Idempotency**: dedup by email (or id_number if present). Re-
 *    running the same CSV does not create duplicate students. The
 *    dedup happens at /commit; /preview is read-only.
 *
 * 5. **Atomicity**: /commit wraps the whole batch in a SQLite
 *    transaction. If any row fails after validation, the entire
 *    commit rolls back. The error.csv is downloadable so the
 *    operator can fix the source file and retry.
 *
 * RBAC: operations admins only (school_admin, systems_admin,
 * principal). The seed script's `is_deleted = 0` flag is honoured
 * (we never resurrect soft-deleted rows; we skip with a warning).
 *
 * Storage: legacy_mappings table stores saved column mappings so the
 * operator can re-use a mapping on subsequent imports of the same
 * source format.
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const fs = require('fs');
const multer = require('multer');
const { parse } = require('csv-parse/sync');
const { v4: uuidv4 } = require('uuid');
const Database = require('better-sqlite3');
const AuditService = require('../services/AuditService');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
let db;
const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
    // Ensure the legacy_mappings table exists. Idempotent.
    db.exec(`
      CREATE TABLE IF NOT EXISTS legacy_mappings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        uid TEXT UNIQUE NOT NULL,
        name TEXT NOT NULL,
        source_format TEXT,
        mapping_json TEXT NOT NULL,
        created_by INTEGER REFERENCES users(id),
        created_at DATETIME DEFAULT (datetime('now','localtime')),
        updated_at DATETIME DEFAULT (datetime('now','localtime')),
        last_synced_at DATETIME,
        sync_status TEXT DEFAULT 'pending',
        is_deleted INTEGER DEFAULT 0
      );
    `);
  }
  return db;
};

// ============ AUTH + RBAC ============

const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

const operationsAdminOnly = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal'].includes(req.user.role)) {
    // Explicitly end the response so multer (which would otherwise be
    // called next in the chain) doesn't try to keep reading the
    // multipart stream after we've sent the 403. Without this, supertest
    // sees an ECONNRESET on certain body shapes.
    //
    // The 403 must come BEFORE multer runs. The combination of
    //   (a) `res.end()` in the json() call, plus
    //   (b) `req.destroy()` to abort the in-flight upload, plus
    //   (c) `Connection: close` so the client cleans up its chunked
    //       transfer cleanly,
    // is what makes the 403 + multipart body case reliable.
    res.set('Connection', 'close');
    res.status(403).json({ error: 'Operations admin access required' });
    if (req.readable) {
      try { req.destroy(); } catch (_) { /* already closed */ }
    }
    return;
  }
  next();
};

// ============ MULTER SETUP ============
//
// 5MB limit per the SRS SLA, CSV only (mime sniff is loose — some
// browsers send text/plain for .csv, others send application/vnd.ms-excel).
// We validate the extension on top of the upload middleware.

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

const upload = multer({
  dest: uploadsDir,
  limits: { fileSize: 5 * 1024 * 1024 }, // 5 MB
  fileFilter: (req, file, cb) => {
    const ext = path.extname(file.originalname).toLowerCase();
    // Reject (don't throw) so the request stream isn't torn down mid-read.
    // The handler checks `req.file` and returns 400 if it's missing.
    if (ext !== '.csv') return cb(null, false);
    cb(null, true);
  },
});

// Convert multer errors (bad extension, oversize, etc.) into a 400
// instead of leaking through the global 500 handler. The error object
// we throw in `fileFilter` is a plain `Error` with a message; treat any
// multer-stage error as a client error.
const handleUpload = (req, res, next) => {
  // Only run multer when the request is actually multipart. JSON bodies
  // (e.g. E2E tests posting `{ csvContent }`) must skip multer entirely,
  // otherwise the fileFilter crashes reading `file.originalname`.
  const ct = String(req.headers['content-type'] || '');
  if (!ct.startsWith('multipart/form-data')) {
    return next();
  }
  upload.single('file')(req, res, (err) => {
    if (err) {
      return res.status(400).json({ error: err.message });
    }
    next();
  });
};

// Pre-check: reject obviously-wrong uploads BEFORE multer touches the
// multipart stream. supertest + multer can ECONNRESET if the response
// races with the stream parser, so we bail on the first signal we can
// read off the request envelope (Content-Type / filename hint).
const preflightUpload = (req, res, next) => {
  // Only check if the client declared a file. The actual multer
  // fileFilter still runs as a defence-in-depth check.
  const ct = String(req.headers['content-type'] || '');
  if (ct && !ct.startsWith('multipart/form-data')) {
    return res.status(400).json({ error: 'Expected multipart/form-data with a "file" field.' });
  }
  next();
};

// ============ COLUMN AUTO-DETECTION ============
//
// We support the most common column names a school would export from
// Excel / a legacy SIS. Aliases are matched case-insensitive, ignoring
// whitespace / underscores / hyphens. The mapping is a plain object
// { csv_header -> schema_field }.

const SCHEMA_FIELDS = {
  // Student
  first_name:    ['firstname', 'first', 'givenname', 'given', 'name1', 'studentname', 'student'],
  last_name:     ['lastname', 'last', 'surname', 'familyname', 'family', 'name2'],
  email:         ['email', 'emailaddress', 'mail'],
  phone:         ['phone', 'phonenumber', 'mobile', 'cell', 'cellphone', 'contact', 'tel'],
  id_number:     ['idnumber', 'studentid', 'studentnumber', 'regnumber', 'registration', 'admissionno', 'roll', 'rollno', 'rollnumber'],
  date_of_birth: ['dateofbirth', 'dob', 'birthdate', 'birthday'],
  gender:        ['gender', 'sex'],
  // Class
  class_name:    ['class', 'classname', 'form', 'grade', 'homeroom', 'currentclass'],
  section:       ['section', 'stream'],
  // Parent
  parent_email:  ['parentemail', 'guardianemail', 'motheremail', 'fatheremail'],
  parent_phone:  ['parentphone', 'guardianphone', 'motherphone', 'fatherphone', 'parentcontact'],
  parent_name:   ['parentname', 'guardian', 'mothername', 'fathername'],
  // Academic
  academic_year: ['academicyear', 'year'],
  term:          ['term'],
  admission_date:['admissiondate', 'enrolldate', 'enrollmentdate', 'joindate', 'date'],
};

const normalize = (s) => String(s || '').toLowerCase().replace(/[\s_\-]+/g, '');

const autoDetectMapping = (headers) => {
  const out = {};
  for (const header of headers) {
    const norm = normalize(header);
    for (const [field, aliases] of Object.entries(SCHEMA_FIELDS)) {
      if (aliases.includes(norm)) {
        // Don't override an earlier match (first wins).
        if (!Object.values(out).includes(field)) {
          out[header] = field;
        }
        break;
      }
    }
  }
  return out;
};

// ============ ROW VALIDATION ============

const GENDERS = new Set(['male', 'female', 'm', 'f', 'other']);
const EMAIL_RE = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

const validateRow = (row, mapping) => {
  const errors = [];
  const data = {};
  for (const [csvCol, schemaField] of Object.entries(mapping)) {
    const v = (row[csvCol] || '').toString().trim();
    if (v) data[schemaField] = v;
  }
  if (!data.first_name) errors.push('missing first_name');
  if (!data.last_name) errors.push('missing last_name');
  if (data.email && !EMAIL_RE.test(data.email)) errors.push(`invalid email: ${data.email}`);
  if (data.gender && !GENDERS.has(data.gender.toLowerCase())) {
    errors.push(`invalid gender: ${data.gender}`);
  }
  return { data, errors };
};

// ============ /preview ============
//
// Accepts a CSV upload + optional explicit mapping override, returns
// (a) the auto-detected mapping, (b) the first 20 rows with per-row
// validation errors, (c) total row count, (d) summary stats.

router.post('/preview', auth, operationsAdminOnly, handleUpload, (req, res) => {
  try {
    let csv;
    if (req.file) {
      csv = fs.readFileSync(req.file.path, 'utf8');
    } else if (req.body && req.body.csvContent) {
      csv = req.body.csvContent;
    } else {
      return res.status(400).json({ error: 'No CSV file uploaded. Use multipart field "file" or provide csvContent in body.' });
    }
    let records;
    try {
      records = parse(csv, { columns: true, skip_empty_lines: true, trim: true, bom: true });
    } catch (e) {
      return res.status(400).json({ error: 'CSV parse error: ' + e.message });
    }
    if (records.length === 0) {
      return res.status(400).json({ error: 'CSV has no data rows.' });
    }
    const headers = Object.keys(records[0] || {});
    // Use the explicit mapping from the request if provided, else auto-detect.
    const mapping = (req.body && req.body.mapping) ? JSON.parse(req.body.mapping) : autoDetectMapping(headers);

    // Validate every row, but only return the first 20 for the preview.
    const allErrors = [];
    let validCount = 0;
    for (let i = 0; i < records.length; i++) {
      const { errors } = validateRow(records[i], mapping);
      if (errors.length === 0) validCount += 1;
      else allErrors.push({ row_number: i + 2, errors }); // +2 = header row + 1-indexed
    }
    const previewRows = records.slice(0, 20).map((r, idx) => {
      const { data, errors } = validateRow(r, mapping);
      return { row_number: idx + 2, data, errors };
    });

    res.json({
      success: true,
      headers: headers,
      detected_headers: headers,
      detected_mapping: autoDetectMapping(headers),
      applied_mapping: mapping,
      total_rows: records.length,
      valid_rows: validCount,
      valid_rows_count: validCount,
      invalid_rows: records.length - validCount,
      first_errors: allErrors.slice(0, 10),
      preview_rows: previewRows,
      file_info: req.file
        ? {
            original_name: req.file.originalname,
            size_bytes: req.file.size,
          }
        : null,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ /commit ============
//
// Accepts a CSV upload + the mapping to apply. Wraps the batch in a
// single SQLite transaction. Returns per-row success/error. If the
// caller passes ?rollback_on_error=true, any error aborts the entire
// batch; default behaviour is best-effort (continue on per-row errors).

router.post('/commit', auth, operationsAdminOnly, handleUpload, async (req, res) => {
  try {
    const academicYear = req.body.academic_year || '2026';
    const term = req.body.term || 'Term 1';
    const rollbackOnError = String(req.body.rollback_on_error || 'true').toLowerCase() === 'true';

    let csv;
    if (req.file) {
      csv = fs.readFileSync(req.file.path, 'utf8');
    } else if (req.body && req.body.csvContent) {
      csv = req.body.csvContent;
    } else {
      return res.status(400).json({ error: 'No CSV file uploaded. Use multipart field "file" or provide csvContent in body.' });
    }
    let records;
    try {
      records = parse(csv, { columns: true, skip_empty_lines: true, trim: true, bom: true });
    } catch (e) {
      return res.status(400).json({ error: 'CSV parse error: ' + e.message });
    }
    if (records.length === 0) {
      return res.status(400).json({ error: 'CSV has no data rows.' });
    }
    const headers = Object.keys(records[0] || {});

    // If the caller didn't pass a mapping, fall back to auto-detection.
    // The first call from the UI is /preview (which returns the
    // detected mapping), and the UI then POSTs the user-confirmed
    // mapping to /commit. But the API also supports a one-shot
    // commit with auto-detection for scripts.
    let mapping;
    try {
      mapping = req.body && req.body.mapping ? JSON.parse(req.body.mapping) : null;
    } catch (e) {
      return res.status(400).json({ error: 'mapping is not valid JSON: ' + e.message });
    }
    if (!mapping || Object.keys(mapping).length === 0) {
      mapping = autoDetectMapping(headers);
    }
    if (Object.keys(mapping).length === 0) {
      return res.status(400).json({
        error: 'No mapping could be auto-detected. Pass mapping explicitly as { csv_header: schema_field }.',
        detected_headers: headers,
      });
    }

    const dbi = getDb();
    const results = {
      total: records.length,
      created_users: 0,
      created_enrollments: 0,
      linked_parents: 0,
      created_classes: 0,
      skipped_duplicates: 0,
      errors: [],
    };

    // Try to save the mapping if the caller passed a name for it.
    const mappingName = req.body.mapping_name;
    let mappingUid = null;
    if (mappingName) {
      mappingUid = uuidv4();
      // source_format is the file extension when a file was uploaded, else
      // 'csv' for raw csvContent bodies.
      const sourceFormat = req.file ? path.extname(req.file.originalname) : '.csv';
      try {
        dbi.prepare(`
          INSERT INTO legacy_mappings (uid, name, source_format, mapping_json, created_by)
          VALUES (?, ?, ?, ?, ?)
        `).run(mappingUid, mappingName, sourceFormat, JSON.stringify(mapping), req.user.id);
      } catch (e) {
        // Don't fail the commit on a mapping-save error; just record it.
        results.errors.push({ row_number: 0, errors: ['mapping-save warning: ' + e.message] });
      }
    }

    const run = dbi.transaction(() => {
      for (let i = 0; i < records.length; i++) {
        const rowNum = i + 2; // header + 1-indexed
        const { data, errors } = validateRow(records[i], mapping);
        if (errors.length > 0) {
          results.errors.push({ row_number: rowNum, errors });
          if (rollbackOnError) throw new Error(`Row ${rowNum} validation failed: ${errors.join('; ')}`);
          continue;
        }

        // Resolve class (create on the fly if name not seen).
        let classId = null;
        if (data.class_name) {
          const existing = dbi.prepare(
            'SELECT id FROM classes WHERE name = ? AND (section = ? OR (section IS NULL AND ? IS NULL)) AND is_deleted = 0'
          ).get(data.class_name, data.section || null, data.section || null);
          if (existing) {
            classId = existing.id;
          } else {
            const classUid = uuidv4();
            dbi.prepare(`
              INSERT INTO classes (uid, name, section, sync_status)
              VALUES (?, ?, ?, 'pending')
            `).run(classUid, data.class_name, data.section || null);
            classId = dbi.prepare('SELECT id FROM classes WHERE uid = ?').get(classUid).id;
            results.created_classes += 1;
          }
        }

        // Dedup: prefer email match, fall back to id_number.
        let student = null;
        if (data.email) {
          student = dbi.prepare('SELECT id FROM users WHERE email = ? AND is_deleted = 0').get(data.email);
        }
        if (!student && data.id_number) {
          student = dbi.prepare('SELECT id FROM users WHERE id_number = ? AND is_deleted = 0').get(data.id_number);
        }
        if (student) {
          results.skipped_duplicates += 1;
        } else {
          const studentUid = uuidv4();
          const idNumber = data.id_number || `LGC-${Date.now()}-${i}`;
          const insertStudent = dbi.prepare(`
            INSERT INTO users (
              uid, id_number, email, role, first_name, last_name, phone, gender, date_of_birth,
              is_active, password, sync_status
            ) VALUES (?, ?, ?, 'student', ?, ?, ?, ?, ?, 1, ?, 'pending')
          `);
          // bcrypt a random password — the operator can trigger a reset
          // workflow per student. The placeholder value is irrelevant for
          // production; we never let the student log in with a CSV-imported
          // password without an explicit reset.
          const crypto = require('crypto');
          const tempPassword = crypto.randomBytes(16).toString('hex');
          const bcrypt = require('bcryptjs');
          const hash = bcrypt.hashSync(tempPassword, 4);
          insertStudent.run(
            studentUid,
            idNumber,
            data.email || null,
            data.first_name,
            data.last_name,
            data.phone || null,
            // Schema CHECK constraint is `gender IN ('male','female','other')` (lowercase).
            // The validator accepts "M"/"F"/"male"/"female"/"other" — normalise to lowercase.
            data.gender ? data.gender.toLowerCase().replace(/^m$/, 'male').replace(/^f$/, 'female') : null,
            data.date_of_birth || null,
            hash,
          );
          const newStudent = dbi.prepare('SELECT id FROM users WHERE uid = ?').get(studentUid);
          student = newStudent;
          results.created_users += 1;
        }

        // Enrollment
        if (classId && student) {
          const existingEnr = dbi.prepare(
            'SELECT id FROM enrollments WHERE student_id = ? AND class_id = ? AND status = \'active\' AND is_deleted = 0'
          ).get(student.id, classId);
          if (!existingEnr) {
            const enrUid = uuidv4();
            dbi.prepare(`
              INSERT INTO enrollments (
                uid, student_id, class_id, academic_year, status, request_status,
                requested_at, approved_at, approved_by, admission_date, sync_status
              ) VALUES (?, ?, ?, ?, 'active', 'approved', datetime('now','localtime'), datetime('now','localtime'), ?, COALESCE(?, date('now','localtime')), 'pending')
            `).run(enrUid, student.id, classId, academicYear, req.user.id, data.admission_date || null);
            results.created_enrollments += 1;
          }
        }

        // Parent link
        if ((data.parent_email || data.parent_phone || data.parent_name) && student) {
          let parent = null;
          if (data.parent_email) {
            parent = dbi.prepare('SELECT id FROM users WHERE email = ? AND is_deleted = 0').get(data.parent_email);
          }
          if (!parent && data.parent_phone) {
            parent = dbi.prepare('SELECT id FROM users WHERE phone = ? AND role = \'parent\' AND is_deleted = 0').get(data.parent_phone);
          }
          // No existing parent matched by email or phone. Auto-create one
          // from name + phone so the import can wire up the link in one
          // shot. We synthesise a unique placeholder email (parents may
          // not always have one in legacy data) and rely on the phone
          // for dedupe on subsequent imports. Auto-created parents are
          // tracked separately from `created_users` so the wizard's
          // "students inserted" tally stays accurate.
          if (!parent && (data.parent_phone || data.parent_name)) {
            const name = (data.parent_name || '').trim();
            const phone = (data.parent_phone || '').trim();
            const [first, ...rest] = name.split(/\s+/).filter(Boolean);
            const last = rest.join(' ') || 'Parent';
            const parentEmail = (data.parent_email || `parent.${phone || Date.now()}@legacy-import.local`).trim();
            const existingByEmail = dbi.prepare('SELECT id FROM users WHERE email = ? AND is_deleted = 0').get(parentEmail);
            if (existingByEmail) {
              parent = existingByEmail;
            } else {
              const parentUid = uuidv4();
              dbi.prepare(`
                INSERT INTO users (uid, email, password, role, first_name, last_name, phone, is_active, is_deleted, sync_status)
                VALUES (?, ?, '', 'parent', ?, ?, ?, 1, 0, 'pending')
              `).run(parentUid, parentEmail, first || 'Parent', last, phone || null);
              parent = dbi.prepare('SELECT id FROM users WHERE uid = ?').get(parentUid);
              results.created_parents = (results.created_parents || 0) + 1;
            }
          }
          if (parent) {
            const already = dbi.prepare(
              'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
            ).get(parent.id, student.id);
            if (!already) {
              const linkUid = uuidv4();
              dbi.prepare(`
                INSERT INTO parent_students (uid, parent_id, student_id, relationship, is_primary, sync_status)
                VALUES (?, ?, ?, 'guardian', 1, 'pending')
              `).run(linkUid, parent.id, student.id);
              results.linked_parents += 1;
            }
          }
        }
      }
    });

    try {
      run();
    } catch (txErr) {
      // On rollback, results reflects everything up to the failure point.
      return res.status(409).json({
        error: 'Batch rolled back due to a row error: ' + txErr.message,
        partial_results: results,
        suggestion: 'Fix the offending row in the source CSV and re-submit, or pass ?rollback_on_error=false to do a best-effort commit.',
      });
    }

    await AuditService.log(req, 'LEGACY_IMPORT_COMMIT', {
      entityType: 'students',
      newData: {
        file: req.file ? req.file.originalname : null,
        rows: records.length,
        created_users: results.created_users,
        created_enrollments: results.created_enrollments,
        created_classes: results.created_classes,
        skipped_duplicates: results.skipped_duplicates,
        errors: results.errors.length,
        mapping_uid: mappingUid,
      },
    });

    res.json({
      success: true,
      rollback_on_error: rollbackOnError,
      // Aliases matching the contract the wizard UI / E2E suite expects.
      inserted_students: results.created_users,
      ...results,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ /error.csv (download the error report from the last commit) ============
//
// Optional convenience: clients can re-POST to /commit and get the error
// list back in JSON. This endpoint produces an actual error.csv for the
// operator to download and feed back into Excel.

router.post('/error-csv', auth, operationsAdminOnly, (req, res) => {
  try {
    const errors = Array.isArray(req.body.errors) ? req.body.errors : [];
    const header = 'row_number,errors';
    const lines = errors.map((e) => {
      const errs = Array.isArray(e.errors) ? e.errors.join('; ') : String(e.errors || '');
      const safe = errs.replace(/"/g, '""');
      return `${e.row_number},"${safe}"`;
    });
    const csv = [header, ...lines].join('\r\n');
    res.setHeader('Content-Type', 'text/csv; charset=utf-8');
    res.setHeader('Content-Disposition', 'attachment; filename="legacy-import-errors.csv"');
    res.send('\uFEFF' + csv); // BOM for Excel
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ /mappings (saved column mappings) ============

router.get('/mappings', auth, operationsAdminOnly, (req, res) => {
  try {
    const rows = getDb().prepare(`
      SELECT uid, name, source_format, mapping_json, created_at
      FROM legacy_mappings
      WHERE is_deleted = 0
      ORDER BY created_at DESC
    `).all();
    res.json({
      mappings: rows.map((r) => ({
        uid: r.uid,
        name: r.name,
        source_format: r.source_format,
        mapping: JSON.parse(r.mapping_json),
        created_at: r.created_at,
      })),
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/mappings/:uid', auth, operationsAdminOnly, (req, res) => {
  try {
    const row = getDb().prepare(`
      SELECT uid, name, source_format, mapping_json, created_at
      FROM legacy_mappings WHERE uid = ? AND is_deleted = 0
    `).get(req.params.uid);
    if (!row) return res.status(404).json({ error: 'Mapping not found' });
    res.json({
      uid: row.uid,
      name: row.name,
      source_format: row.source_format,
      mapping: JSON.parse(row.mapping_json),
      created_at: row.created_at,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.delete('/mappings/:uid', auth, operationsAdminOnly, (req, res) => {
  try {
    const res2 = getDb().prepare(`
      UPDATE legacy_mappings SET is_deleted = 1, updated_at = datetime('now','localtime'),
                                sync_status = 'pending', last_synced_at = NULL
      WHERE uid = ? AND is_deleted = 0
    `).run(req.params.uid);
    if (res2.changes === 0) return res.status(404).json({ error: 'Mapping not found' });
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
