const express = require("express");
const router = express.Router();

const { Validator } = require("node-input-validator");
const multer = require("multer");
const fs = require("fs");
const path = require("path");
const { parse: csvParse } = require("csv-parse");
const XLSX = require("xlsx");
const { stringify } = require("csv-stringify");

const upload = multer({
  dest: "uploads/",
  limits: { fileSize: 5 * 1024 * 1024 }, // 5MB max size
});

const db = require("../models");
const transaction = db.transaction; // or db.Transaction

// Validation schema
const transactionValidationRules = {
  order_id: "required|integer",
  user_id: "required|integer",
  shipping_dock_id: "required|integer",
  amount: "required|numeric",
  discount: "required|numeric",
  tax: "required|numeric",
  total: "required|numeric",
  notes: "string",
  status: "required|integer",
};

const transactionValidationMessages = {
  "order_id.required": "Order ID is required",
  "user_id.required": "User ID is required",
  // add more if needed
};

// Import endpoint
router.post("/import", upload.single("file"), async (req, res) => {
  if (!req.file) {
    return res.status(400).json({ success: false, error: "No file uploaded" });
  }

  const ext = path.extname(req.file.originalname).toLowerCase();
  let records = [];

  try {
    if (ext === ".csv") {
      const fileContent = fs.readFileSync(req.file.path, "utf8");
      records = await new Promise((resolve, reject) => {
        csvParse(fileContent, { columns: true, trim: true }, (err, output) => {
          if (err) reject(err);
          else resolve(output);
        });
      });
    } else if (ext === ".xlsx" || ext === ".xls") {
      const workbook = XLSX.readFile(req.file.path);
      const sheetName = workbook.SheetNames[0];
      records = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
    } else {
      fs.unlinkSync(req.file.path);
      return res
        .status(400)
        .json({ success: false, error: "Unsupported file type" });
    }

    const errors = [];
    const inserted = [];

    // Optional: Collect valid rows for bulk insert
    const validRows = [];

    for (const [i, row] of records.entries()) {
      const v = new Validator(
        row,
        transactionValidationRules,
        transactionValidationMessages
      );
      const isValid = await v.check();

      if (!isValid) {
        errors.push({ row: i + 1, errors: v.errors });
        continue;
      }

      try {
        validRows.push(row);
      } catch (dbErr) {
        errors.push({ row: i + 1, errors: dbErr.message });
      }
    }

    if (validRows.length) {
      const created = await transaction.bulkCreate(validRows);
      inserted.push(...created);
    }

    fs.unlinkSync(req.file.path); // clean up

    if (errors.length) {
      return res
        .status(400)
        .json({ success: false, errors, insertedCount: inserted.length });
    }

    return res.status(200).json({ success: true, inserted });
  } catch (err) {
    if (req.file && fs.existsSync(req.file.path)) fs.unlinkSync(req.file.path);
    return res.status(500).json({ success: false, error: err.message });
  }
});

// Export endpoint
router.get("/export", async (req, res) => {
  try {
    const transactions = await transaction.findAll({ raw: true });
    if (!transactions.length) {
      return res
        .status(404)
        .json({ success: false, error: "No transactions found" });
    }

    stringify(transactions, { header: true }, (err, output) => {
      if (err) {
        return res.status(500).json({ success: false, error: err.message });
      }
      res.setHeader("Content-Type", "text/csv");
      res.setHeader(
        "Content-Disposition",
        'attachment; filename="transactions.csv"'
      );
      res.status(200).send(output);
    });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

module.exports = router;