const express = require('express');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const xlsx = require('xlsx');

const app = express();
const PORT = process.env.PORT || 3000;

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

const metadataFile = path.join(uploadsDir, 'metadata.json');

function loadMetadata() {
  try {
    if (fs.existsSync(metadataFile)) {
      const data = fs.readFileSync(metadataFile, 'utf8');
      return JSON.parse(data);
    }
  } catch (err) {
    console.error('Failed reading metadata:', err);
  }
  return [];
}

function saveMetadata(metadata) {
  try {
    fs.writeFileSync(metadataFile, JSON.stringify(metadata, null, 2), 'utf8');
  } catch (err) {
    console.error('Failed saving metadata:', err);
  }
}

// Multer storage configuration
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, uploadsDir);
  },
  filename: (req, file, cb) => {
    const timestamp = Date.now();
    const safeName = file.originalname.replace(/[^a-zA-Z0-9.-]/g, '_');
    cb(null, `${timestamp}-${safeName}`);
  }
});

// File filter: accept only .xlsx and .xls
const fileFilter = (req, file, cb) => {
  const ext = path.extname(file.originalname).toLowerCase();
  if (ext === '.xlsx' || ext === '.xls') {
    cb(null, true);
  } else {
    cb(new Error('Invalid file type. Only Excel files (.xlsx and .xls) are allowed.'), false);
  }
};

const upload = multer({
  storage,
  fileFilter,
  limits: {
    fileSize: 100 * 1024 * 1024 // 100MB limit
  }
});

// Middleware
app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

// 1. Get list of Excel files
app.get('/api/files', (req, res) => {
  try {
    const metadata = loadMetadata();
    // Filter to ensure files actually exist on disk
    const existing = metadata.filter(item => {
      const filePath = path.join(uploadsDir, item.id);
      return fs.existsSync(filePath);
    });
    if (existing.length !== metadata.length) {
      saveMetadata(existing);
    }
    res.json({ files: existing });
  } catch (err) {
    res.status(500).json({ error: 'Failed to retrieve files: ' + err.message });
  }
});

// 2. Upload an Excel file
app.post('/api/upload', (req, res) => {
  upload.single('file')(req, res, (err) => {
    if (err) {
      return res.status(400).json({ error: err.message || 'File upload failed.' });
    }

    if (!req.file) {
      return res.status(400).json({ error: 'No Excel file was uploaded.' });
    }

    const filePath = req.file.path;
    let workbook;

    try {
      // Validate that it is a valid, readable Excel file
      workbook = xlsx.readFile(filePath, { cellDates: true });
      if (!workbook || !workbook.SheetNames || workbook.SheetNames.length === 0) {
        throw new Error('Excel file contains no worksheets.');
      }
    } catch (parseErr) {
      // Remove invalid/corrupt uploaded file
      try {
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      } catch (e) {}
      return res.status(400).json({
        error: 'Invalid or corrupted Excel file. Please upload a valid .xlsx or .xls file.'
      });
    }

    const fileRecord = {
      id: req.file.filename,
      originalName: req.file.originalname,
      size: req.file.size,
      uploadedAt: new Date().toISOString(),
      sheetNames: workbook.SheetNames
    };

    const metadata = loadMetadata();
    metadata.unshift(fileRecord);
    saveMetadata(metadata);

    res.json({ success: true, file: fileRecord });
  });
});

// 3. Get Excel file details and worksheets
app.get('/api/files/:id', (req, res) => {
  const fileId = req.params.id;
  const filePath = path.join(uploadsDir, fileId);

  if (!fs.existsSync(filePath)) {
    return res.status(404).json({ error: 'File not found on server.' });
  }

  const metadata = loadMetadata();
  const fileRecord = metadata.find(f => f.id === fileId);

  try {
    const workbook = xlsx.readFile(filePath, { bookSheets: true });
    res.json({
      id: fileId,
      originalName: fileRecord ? fileRecord.originalName : fileId,
      size: fileRecord ? fileRecord.size : fs.statSync(filePath).size,
      uploadedAt: fileRecord ? fileRecord.uploadedAt : null,
      sheetNames: workbook.SheetNames
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to read Excel workbook: ' + err.message });
  }
});

// 4. Get specific sheet data from an Excel file
app.get('/api/files/:id/sheet', (req, res) => {
  const fileId = req.params.id;
  const sheetNameParam = req.query.name;
  const filePath = path.join(uploadsDir, fileId);

  if (!fs.existsSync(filePath)) {
    return res.status(404).json({ error: 'File not found on server.' });
  }

  try {
    const workbook = xlsx.readFile(filePath, {
      cellDates: true,
      cellText: true,
      raw: false
    });

    const targetSheetName = sheetNameParam || workbook.SheetNames[0];

    if (!workbook.SheetNames.includes(targetSheetName)) {
      return res.status(404).json({ error: `Sheet "${targetSheetName}" not found.` });
    }

    const worksheet = workbook.Sheets[targetSheetName];

    // Convert sheet to array of rows (header: 1 gives 2D array: [ [cellA1, cellB1...], ... ])
    const rows = xlsx.utils.sheet_to_json(worksheet, {
      header: 1,
      defval: '',
      blankrows: true,
      raw: false
    });

    // Determine max columns across all rows
    let maxCols = 0;
    for (let i = 0; i < rows.length; i++) {
      if (rows[i].length > maxCols) {
        maxCols = rows[i].length;
      }
    }

    // Standardize all rows to have the same number of columns for grid consistency
    const normalizedRows = rows.map(row => {
      const newRow = new Array(maxCols);
      for (let c = 0; c < maxCols; c++) {
        newRow[c] = (row[c] !== undefined && row[c] !== null) ? String(row[c]) : '';
      }
      return newRow;
    });

    res.json({
      sheetName: targetSheetName,
      sheetNames: workbook.SheetNames,
      totalRows: normalizedRows.length,
      totalCols: maxCols,
      rows: normalizedRows
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to parse sheet data: ' + err.message });
  }
});

// 5. Save edited sheet data back to the Excel file
app.post('/api/files/:id/sheet', (req, res) => {
  const fileId = req.params.id;
  const { sheetName, rows } = req.body;
  const filePath = path.join(uploadsDir, fileId);

  if (!fs.existsSync(filePath)) {
    return res.status(404).json({ error: 'File not found on server.' });
  }

  if (!sheetName || !Array.isArray(rows)) {
    return res.status(400).json({ error: 'Invalid sheet data. sheetName and rows array are required.' });
  }

  try {
    const workbook = xlsx.readFile(filePath);

    if (!workbook.SheetNames.includes(sheetName)) {
      return res.status(404).json({ error: `Sheet "${sheetName}" not found in workbook.` });
    }

    // Convert values to numbers if numeric, or strings
    const formattedRows = rows.map(row => {
      if (!Array.isArray(row)) return [];
      return row.map(cell => {
        if (cell === null || cell === undefined) return '';
        const str = String(cell).trim();
        if (str === '') return '';
        // If string represents a clean number, parse it
        if (/^-?\d+(\.\d+)?$/.test(str) && !isNaN(Number(str))) {
          return Number(str);
        }
        return cell;
      });
    });

    const newWorksheet = xlsx.utils.aoa_to_sheet(formattedRows);
    workbook.Sheets[sheetName] = newWorksheet;

    xlsx.writeFile(workbook, filePath);

    // Update metadata file size and timestamp
    const stats = fs.statSync(filePath);
    const metadata = loadMetadata();
    const fileRecord = metadata.find(f => f.id === fileId);
    if (fileRecord) {
      fileRecord.size = stats.size;
      fileRecord.updatedAt = new Date().toISOString();
      saveMetadata(metadata);
    }

    res.json({
      success: true,
      message: `Worksheet "${sheetName}" saved successfully!`,
      size: stats.size
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to save worksheet: ' + err.message });
  }
});

// 5. Download original Excel file
app.get('/api/files/:id/download', (req, res) => {
  const fileId = req.params.id;
  const filePath = path.join(uploadsDir, fileId);

  if (!fs.existsSync(filePath)) {
    return res.status(404).json({ error: 'File not found on server.' });
  }

  const metadata = loadMetadata();
  const fileRecord = metadata.find(f => f.id === fileId);
  const downloadName = fileRecord ? fileRecord.originalName : fileId;

  // Use res.download to preserve exact original file and content
  res.download(filePath, downloadName, (err) => {
    if (err && !res.headersSent) {
      res.status(500).json({ error: 'Failed downloading file: ' + err.message });
    }
  });
});

// Fallback to index.html
app.use((req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

// Start server
app.listen(PORT, () => {
  console.log(`Excel Management Server running at http://localhost:${PORT}`);
});