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

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

const metadataFile = path.join(uploadsDir, 'metadata.json');
let metadata = [];
try {
  if (fs.existsSync(metadataFile)) {
    metadata = JSON.parse(fs.readFileSync(metadataFile, 'utf8'));
  }
} catch (e) {
  metadata = [];
}

// 1. Student Grades Report
{
  const wb = xlsx.utils.book_new();

  const gradesData = [
    ['Roll No', 'Student Name', 'Math', 'Science', 'English', 'History', 'Total Marks', 'Grade', 'Status'],
    ['101', 'Aarav Sharma', 92, 88, 85, 79, 344, 'A', 'Passed'],
    ['102', 'Priya Patel', 95, 96, 91, 94, 376, 'A+', 'Passed'],
    ['103', 'Rohan Verma', 68, 72, 65, 70, 275, 'B', 'Passed'],
    ['104', 'Sneha Gupta', 84, 82, 88, 85, 339, 'A', 'Passed'],
    ['105', 'Vikram Singh', 58, 62, 55, 60, 235, 'C', 'Passed'],
    ['106', 'Ananya Roy', 90, 93, 89, 92, 364, 'A+', 'Passed'],
    ['107', 'Karan Mehra', 74, 76, 70, 72, 292, 'B', 'Passed']
  ];
  const ws1 = xlsx.utils.aoa_to_sheet(gradesData);
  xlsx.utils.book_append_sheet(wb, ws1, 'Term 1 Grades');

  const attendanceData = [
    ['Roll No', 'Student Name', 'Total Classes', 'Attended', 'Attendance %'],
    ['101', 'Aarav Sharma', 120, 114, '95.0%'],
    ['102', 'Priya Patel', 120, 118, '98.3%'],
    ['103', 'Rohan Verma', 120, 102, '85.0%'],
    ['104', 'Sneha Gupta', 120, 112, '93.3%'],
    ['105', 'Vikram Singh', 120, 98, '81.6%'],
    ['106', 'Ananya Roy', 120, 116, '96.7%'],
    ['107', 'Karan Mehra', 120, 108, '90.0%']
  ];
  const ws2 = xlsx.utils.aoa_to_sheet(attendanceData);
  xlsx.utils.book_append_sheet(wb, ws2, 'Attendance Record');

  const fileName = 'student_grades_report.xlsx';
  const filePath = path.join(uploadsDir, fileName);
  xlsx.writeFile(wb, filePath);

  const stats = fs.statSync(filePath);
  // remove if already exists
  metadata = metadata.filter(m => m.id !== fileName);
  metadata.unshift({
    id: fileName,
    originalName: fileName,
    size: stats.size,
    uploadedAt: new Date().toISOString(),
    sheetNames: ['Term 1 Grades', 'Attendance Record']
  });
}

// 2. Monthly Expense Budget
{
  const wb = xlsx.utils.book_new();

  const expenseData = [
    ['Category', 'Description', 'Budget ($)', 'Actual ($)', 'Variance ($)', 'Payment Mode'],
    ['Housing', 'House Rent', 1200, 1200, 0, 'Bank Transfer'],
    ['Utilities', 'Electricity & Water', 180, 165, 15, 'Credit Card'],
    ['Groceries', 'Supermarket & Food', 450, 480, -30, 'Debit Card'],
    ['Transport', 'Fuel & Metro Passes', 150, 135, 15, 'Credit Card'],
    ['Internet', 'High-Speed Fiber', 60, 60, 0, 'Auto Debit'],
    ['Entertainment', 'Movies & Streaming', 80, 95, -15, 'UPI / Wallet'],
    ['Healthcare', 'Health Insurance & Meds', 200, 190, 10, 'Debit Card']
  ];
  const ws1 = xlsx.utils.aoa_to_sheet(expenseData);
  xlsx.utils.book_append_sheet(wb, ws1, 'October Expenses');

  const fileName = 'monthly_expense_budget.xlsx';
  const filePath = path.join(uploadsDir, fileName);
  xlsx.writeFile(wb, filePath);

  const stats = fs.statSync(filePath);
  metadata = metadata.filter(m => m.id !== fileName);
  metadata.unshift({
    id: fileName,
    originalName: fileName,
    size: stats.size,
    uploadedAt: new Date().toISOString(),
    sheetNames: ['October Expenses']
  });
}

// 3. Inventory Stock Tracker
{
  const wb = xlsx.utils.book_new();

  const stockData = [
    ['SKU Code', 'Product Name', 'Category', 'Unit Price ($)', 'Stock Qty', 'Reorder Level', 'Stock Status'],
    ['TECH-001', 'Wireless Mouse M350', 'Peripherals', 24.99, 142, 30, 'In Stock'],
    ['TECH-002', 'Mechanical Keyboard K12', 'Peripherals', 79.99, 58, 20, 'In Stock'],
    ['TECH-003', 'USB-C Fast Charger 65W', 'Accessories', 29.50, 18, 25, 'Low Stock'],
    ['TECH-004', '4K Ultra-HD Monitor 27"', 'Displays', 299.00, 35, 10, 'In Stock'],
    ['TECH-005', 'Noise-Cancelling Headphones', 'Audio', 149.00, 12, 15, 'Low Stock'],
    ['TECH-006', 'HDMI 2.1 Braided Cable 2m', 'Cables', 14.50, 210, 50, 'In Stock']
  ];
  const ws1 = xlsx.utils.aoa_to_sheet(stockData);
  xlsx.utils.book_append_sheet(wb, ws1, 'Warehouse Stock');

  const supplierData = [
    ['Supplier ID', 'Supplier Name', 'Contact Person', 'Email', 'Phone'],
    ['SUP-01', 'TechLogix Distribution', 'Amit Trivedi', 'amit@techlogix.com', '+91 98765 43210'],
    ['SUP-02', 'Global Peripheral Co.', 'Sarah Jenkins', 'sales@globalperi.com', '+1 555 019 2831'],
    ['SUP-03', 'Prime Wire Electronics', 'Rajesh Nair', 'orders@primewire.in', '+91 91234 56789']
  ];
  const ws2 = xlsx.utils.aoa_to_sheet(supplierData);
  xlsx.utils.book_append_sheet(wb, ws2, 'Suppliers');

  const fileName = 'inventory_stock_tracker.xlsx';
  const filePath = path.join(uploadsDir, fileName);
  xlsx.writeFile(wb, filePath);

  const stats = fs.statSync(filePath);
  metadata = metadata.filter(m => m.id !== fileName);
  metadata.unshift({
    id: fileName,
    originalName: fileName,
    size: stats.size,
    uploadedAt: new Date().toISOString(),
    sheetNames: ['Warehouse Stock', 'Suppliers']
  });
}

// 4. Customer Orders 2026
{
  const wb = xlsx.utils.book_new();

  const ordersData = [
    ['Order ID', 'Customer Name', 'City', 'Order Date', 'Items Count', 'Total ($)', 'Payment Status', 'Delivery'],
    ['ORD-9021', 'Ramesh Kumar', 'Mumbai', '2026-10-01', 3, 245.50, 'Paid', 'Delivered'],
    ['ORD-9022', 'Deepa Nair', 'Bengaluru', '2026-10-02', 1, 79.99, 'Paid', 'Delivered'],
    ['ORD-9023', 'John Doe', 'Delhi', '2026-10-04', 5, 412.00, 'Paid', 'In Transit'],
    ['ORD-9024', 'Neha Sharma', 'Pune', '2026-10-05', 2, 104.49, 'Pending', 'Processing'],
    ['ORD-9025', 'Mohit Agarwal', 'Hyderabad', '2026-10-08', 4, 385.00, 'Paid', 'Shipped'],
    ['ORD-9026', 'Kavita Joshi', 'Jaipur', '2026-10-10', 1, 29.50, 'Paid', 'In Transit']
  ];
  const ws = xlsx.utils.aoa_to_sheet(ordersData);
  xlsx.utils.book_append_sheet(wb, ws, 'All Orders');

  const fileName = 'customer_orders_2026.xlsx';
  const filePath = path.join(uploadsDir, fileName);
  xlsx.writeFile(wb, filePath);

  const stats = fs.statSync(filePath);
  metadata = metadata.filter(m => m.id !== fileName);
  metadata.unshift({
    id: fileName,
    originalName: fileName,
    size: stats.size,
    uploadedAt: new Date().toISOString(),
    sheetNames: ['All Orders']
  });
}

// Write updated metadata
fs.writeFileSync(metadataFile, JSON.stringify(metadata, null, 2), 'utf8');
console.log('Sample Excel files created successfully. Total files in metadata:', metadata.length);