BillTracker/routes/import.cts

import type { Req, Res, Next } from '../types/http';
('use strict');

const express = require('express');
const { log } = require('../utils/logger.cts');
const { standardizeError } = require('../middleware/errorFormatter.cts');
const router = express.Router();
const {
  previewSpreadsheet,
  applyImportDecisions,
  getImportHistory,
} = require('../services/spreadsheetImportService.cts');
const { previewUserDbImport, applyUserDbImport } = require('../services/userDbImportService.cts');
const {
  previewCsvTransactions,
  commitCsvTransactions,
} = require('../services/csvTransactionImportService.cts');
const {
  previewOfxTransactions,
  commitOfxTransactions,
} = require('../services/ofxImportService.cts');

function dataImportEnabled() {
  return String(process.env.DATA_IMPORT_ENABLED ?? 'true').toLowerCase() !== 'false';
}

function requireDataImportEnabled(req: Req, res: Res, next: Next) {
  if (!dataImportEnabled()) {
    return res
      .status(403)
      .json(standardizeError('Data import is disabled by DATA_IMPORT_ENABLED=false', 'FORBIDDEN'));
  }
  next();
}

function makeErrorId() {
  return `imp_${Date.now().toString(36)}_${Math.random().toString(36).slice(2, 8)}`;
}

function sendImportError(res: Res, err: any, fallback: string, defaultCode: string) {
  // Only 4xx service errors carry a user-facing message; a thrown error with
  // an explicit 5xx status must fall through to the masked error-id branch.
  if (err.status && err.status < 500) {
    return res.status(err.status).json({
      error: fallback,
      message: err.message,
      code: err.code || defaultCode,
      details: Array.isArray(err.details) ? err.details : [],
    });
  }

  // Log error ID server-side only — never expose to clients
  const errorId = makeErrorId();
  log.error(`[import] ${fallback} (${errorId}):`, err.stack || err.message);
  return res.status(500).json({
    error: fallback,
    message: 'Unexpected import server error. Please try again or adjust the import decisions.',
    code: defaultCode,
  });
}

// ─── POST /api/import/spreadsheet/preview ─────────────────────────────────────
// Accepts an XLSX file as raw binary body.
// Send with Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
// or application/octet-stream. Optionally pass X-Filename header for audit logging.
// Returns a preview with proposed row mappings and bill matches; writes no data.

router.post(
  '/spreadsheet/preview',
  requireDataImportEnabled,
  express.raw({
    type: [
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'application/octet-stream',
      'application/xlsx',
      'application/vnd.ms-excel',
    ],
    limit: '10mb',
  }),
  async (req: Req, res: Res) => {
    try {
      if (!Buffer.isBuffer(req.body) || req.body.length === 0) {
        return res.status(400).json({
          error:
            'XLSX file is required. Send as raw binary with Content-Type application/octet-stream or the XLSX MIME type.',
        });
      }

      const rawFilename = req.headers['x-filename'];
      const originalFilename = rawFilename
        ? rawFilename
            .replace(/[^a-zA-Z0-9._\-\s]/g, '')
            .trim()
            .slice(0, 255)
        : null;

      const options = {
        sheet_name: req.query.sheet || null,
        parse_all_sheets: req.query.parse_all_sheets === 'true',
        default_year: req.query.year ? parseInt(req.query.year, 10) : null,
        default_month: req.query.month ? parseInt(req.query.month, 10) : null,
        original_filename: originalFilename,
      };

      if (options.default_year && (options.default_year < 2000 || options.default_year > 2100)) {
        return res
          .status(400)
          .json(
            standardizeError(
              'year must be a 4-digit integer between 2000 and 2100',
              'VALIDATION_ERROR',
              'year',
            ),
          );
      }
      if (options.default_month && (options.default_month < 1 || options.default_month > 12)) {
        return res
          .status(400)
          .json(
            standardizeError(
              'month must be an integer between 1 and 12',
              'VALIDATION_ERROR',
              'month',
            ),
          );
      }

      const result = await previewSpreadsheet(req.user.id, req.body, options);
      res.json(result);
    } catch (err) {
      return sendImportError(res, err, 'Import preview failed', 'IMPORT_PREVIEW_ERROR');
    }
  },
);

// ─── POST /api/import/spreadsheet/apply ──────────────────────────────────────
// Applies confirmed import decisions from a previous preview session.
// Body: { import_session_id, decisions: [...], options: { overwrite: false } }
// Each decision must have: row_id, action, and action-specific fields.
// Only writes data for explicitly confirmed decisions; skips ambiguous rows.

router.post(
  '/spreadsheet/apply',
  requireDataImportEnabled,
  express.json({ limit: '2mb' }),
  async (req: Req, res: Res) => {
    try {
      const { import_session_id, decisions, options } = req.body || {};

      if (!import_session_id || typeof import_session_id !== 'string') {
        return res
          .status(400)
          .json(
            standardizeError(
              'import_session_id is required',
              'VALIDATION_ERROR',
              'import_session_id',
            ),
          );
      }
      if (!Array.isArray(decisions) || decisions.length === 0) {
        return res
          .status(400)
          .json(
            standardizeError(
              'decisions array is required and must not be empty',
              'VALIDATION_ERROR',
              'decisions',
            ),
          );
      }
      if (decisions.length > 5000) {
        return res
          .status(400)
          .json(
            standardizeError(
              'Too many decisions in a single apply request (max 5000)',
              'VALIDATION_ERROR',
              'decisions',
            ),
          );
      }

      const result = await applyImportDecisions(
        req.user.id,
        import_session_id,
        decisions,
        options || {},
      );
      res.json(result);
    } catch (err) {
      return sendImportError(res, err, 'Import apply failed', 'IMPORT_APPLY_ERROR');
    }
  },
);

// ─── POST /api/import/user-db/preview ────────────────────────────────────────
// Accepts a SQLite user data export created by this app. This is not an admin
// full-database restore and writes no live bill/payment/category data.

router.post(
  '/user-db/preview',
  requireDataImportEnabled,
  express.raw({
    type: [
      'application/octet-stream',
      'application/x-sqlite3',
      'application/vnd.sqlite3',
      'application/x-sqlite',
      'application/vnd.sqlite',
    ],
    limit: '50mb',
  }),
  async (req: Req, res: Res) => {
    try {
      const rawFilename = req.headers['x-filename'];
      const originalFilename = rawFilename
        ? rawFilename
            .replace(/[^a-zA-Z0-9._\-\s]/g, '')
            .trim()
            .slice(0, 255)
        : null;
      const result = await previewUserDbImport(req.user.id, req.body, {
        original_filename: originalFilename,
      });
      res.json(result);
    } catch (err) {
      return sendImportError(
        res,
        err,
        'User SQLite import preview failed',
        'USER_DB_IMPORT_PREVIEW_ERROR',
      );
    }
  },
);

// ─── POST /api/import/user-db/apply ──────────────────────────────────────────
// Applies a previously previewed user SQLite export session. User ownership is
// derived from req.user only; existing data is skipped by default.

router.post(
  '/user-db/apply',
  requireDataImportEnabled,
  express.json({ limit: '1mb' }),
  async (req: Req, res: Res) => {
    try {
      const { import_session_id, options } = req.body || {};
      if (!import_session_id || typeof import_session_id !== 'string') {
        return res
          .status(400)
          .json(
            standardizeError(
              'import_session_id is required',
              'VALIDATION_ERROR',
              'import_session_id',
            ),
          );
      }
      const result = await applyUserDbImport(req.user.id, import_session_id, options || {});
      res.json(result);
    } catch (err) {
      return sendImportError(
        res,
        err,
        'User SQLite import apply failed',
        'USER_DB_IMPORT_APPLY_ERROR',
      );
    }
  },
);

// ─── POST /api/import/csv/preview ────────────────────────────────────────────
// Accepts a transaction CSV as raw text/binary and returns headers, sample rows,
// suggested mappings, and validation issues. Writes no transactions.

router.post(
  '/csv/preview',
  requireDataImportEnabled,
  express.raw({
    type: [
      'text/csv',
      'application/csv',
      'application/vnd.ms-excel',
      'text/plain',
      'application/octet-stream',
    ],
    limit: '10mb',
  }),
  async (req: Req, res: Res) => {
    try {
      const rawFilename = req.headers['x-filename'];
      const originalFilename = rawFilename
        ? rawFilename
            .replace(/[^a-zA-Z0-9._\-\s]/g, '')
            .trim()
            .slice(0, 255)
        : null;
      const result = previewCsvTransactions(req.user.id, req.body, {
        original_filename: originalFilename,
      });
      res.json(result);
    } catch (err) {
      return sendImportError(
        res,
        err,
        'CSV transaction preview failed',
        'CSV_TRANSACTION_PREVIEW_ERROR',
      );
    }
  },
);

// ─── POST /api/import/csv/commit ─────────────────────────────────────────────
// Commits a previously-previewed CSV import session using the confirmed column
// mapping. Writes normalized rows into the shared transactions table.

router.post(
  '/csv/commit',
  requireDataImportEnabled,
  express.json({ limit: '1mb' }),
  async (req: Req, res: Res) => {
    try {
      const { import_session_id, mapping, options } = req.body || {};
      if (!import_session_id || typeof import_session_id !== 'string') {
        return res
          .status(400)
          .json(
            standardizeError(
              'import_session_id is required',
              'VALIDATION_ERROR',
              'import_session_id',
            ),
          );
      }
      if (!mapping || typeof mapping !== 'object' || Array.isArray(mapping)) {
        return res
          .status(400)
          .json(standardizeError('mapping is required', 'VALIDATION_ERROR', 'mapping'));
      }
      const result = commitCsvTransactions(req.user.id, import_session_id, mapping, options || {});
      res.json(result);
    } catch (err) {
      return sendImportError(
        res,
        err,
        'CSV transaction import failed',
        'CSV_TRANSACTION_COMMIT_ERROR',
      );
    }
  },
);

// ─── POST /api/import/ofx/preview ────────────────────────────────────────────
// Accepts an OFX/QFX file as raw bytes and returns a parsed transaction sample +
// count. Writes no transactions.
router.post(
  '/ofx/preview',
  requireDataImportEnabled,
  express.raw({
    type: [
      'application/x-ofx',
      'application/vnd.intu.qfx',
      'application/octet-stream',
      'text/plain',
      'text/xml',
      'application/xml',
    ],
    limit: '10mb',
  }),
  (req: Req, res: Res) => {
    try {
      const rawFilename = req.headers['x-filename'];
      const originalFilename = rawFilename
        ? rawFilename
            .replace(/[^a-zA-Z0-9._\-\s]/g, '')
            .trim()
            .slice(0, 255)
        : null;
      const result = previewOfxTransactions(req.user.id, req.body, {
        original_filename: originalFilename,
      });
      res.json(result);
    } catch (err) {
      return sendImportError(res, err, 'OFX/QFX preview failed', 'OFX_PREVIEW_ERROR');
    }
  },
);

// ─── POST /api/import/ofx/commit ─────────────────────────────────────────────
// Commits a previewed OFX/QFX session (no mapping — the file is structured).
router.post(
  '/ofx/commit',
  requireDataImportEnabled,
  express.json({ limit: '1mb' }),
  (req: Req, res: Res) => {
    try {
      const { import_session_id, options } = req.body || {};
      if (!import_session_id || typeof import_session_id !== 'string') {
        return res
          .status(400)
          .json(
            standardizeError(
              'import_session_id is required',
              'VALIDATION_ERROR',
              'import_session_id',
            ),
          );
      }
      const result = commitOfxTransactions(req.user.id, import_session_id, options || {});
      res.json(result);
    } catch (err) {
      return sendImportError(res, err, 'OFX/QFX import failed', 'OFX_COMMIT_ERROR');
    }
  },
);

// ─── GET /api/import/history ──────────────────────────────────────────────────
// Returns the authenticated user's import history (last 100 imports).

router.get('/history', requireDataImportEnabled, (req: Req, res: Res) => {
  try {
    const history = getImportHistory(req.user.id);
    res.json({ history });
  } catch (err: any) {
    log.error('[import] history error:', err.message);
    res.status(500).json({ error: 'Failed to load import history' });
  }
});

module.exports = router;