inventory systems app script

// Main Code.gs - Complete Inventory Management System

function onOpen() {
  try {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('📊 Inventory Management')
      .addItem('➕ Add New Item', 'showAddItemDialog')
      .addItem('📦 Update Stock', 'showUpdateStockDialog')
      .addItem('🚀 Initialize Dashboard', 'initializeDashboard')
      .addItem('🔄 Refresh Dashboard', 'updateDashboard')
      .addItem('📊 Create Visual Charts', 'createEmbeddedCharts')
      .addItem('📋 Generate Report', 'generateReport')
      .addItem('🛠️ Fix Dashboard Charts', 'fixDashboardCharts')
      .addSeparator()
      .addItem('🎨 Show Visual Dashboard', 'showVisualDashboard')
      .addToUi();
  } catch (error) {
    // If getUi() fails, we'll create the menu using the spreadsheet directly
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    spreadsheet.addMenu('📊 Inventory Management', [
      { name: '➕ Add New Item', functionName: 'showAddItemDialog' },
      { name: '📦 Update Stock', functionName: 'showUpdateStockDialog' },
      { name: '🚀 Initialize Dashboard', functionName: 'initializeDashboard' },
      { name: '🔄 Refresh Dashboard', functionName: 'updateDashboard' },
      { name: '📊 Create Visual Charts', functionName: 'createEmbeddedCharts' },
      { name: '📋 Generate Report', functionName: 'generateReport' },
      { name: '🛠️ Fix Dashboard Charts', functionName: 'fixDashboardCharts' },
      null, // Separator
      { name: '🎨 Show Visual Dashboard', functionName: 'showVisualDashboard' }
    ]);
  }
}

// Initialize the complete dashboard
function initializeDashboard() {
  try {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var dashboardSheet = spreadsheet.getSheetByName('Dashboard');
    if (!dashboardSheet) {
      dashboardSheet = spreadsheet.insertSheet('Dashboard');
    }
    
    // Clear and setup dashboard
    dashboardSheet.clear();
    setupDashboardLayout();
    
    // Create sample data for charts
    createSampleChartData();
    
    // Update with actual data
    updateDashboard();
    
    // Create embedded charts
    createEmbeddedCharts();
    
    SpreadsheetApp.getActiveSpreadsheet().toast('Dashboard initialized successfully!', 'Success', 5);
  } catch (error) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Error initializing dashboard: ' + error.toString(), 'Error', 10);
  }
}

function setupDashboardLayout() {
  var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  
  // Set column widths for optimal layout
  var columnWidths = [
    {column: 1, width: 150}, {column: 2, width: 120}, {column: 3, width: 150},
    {column: 4, width: 120}, {column: 5, width: 150}, {column: 6, width: 120},
    {column: 7, width: 20},  {column: 8, width: 150}, {column: 9, width: 120},
    {column: 10, width: 150}, {column: 11, width: 120}, {column: 12, width: 150}
  ];
  
  columnWidths.forEach(function(col) {
    dashboardSheet.setColumnWidth(col.column, col.width);
  });
}

function createSampleChartData() {
  var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  
  // Sample data for Stock Levels chart
  var stockData = [
    ['Item', 'Current Stock', 'Min Stock'],
    ['Laptop Dell XPS', 15, 5],
    ['Wireless Mouse Logi', 45, 10],
    ['Mechanical Keyboard', 32, 8],
    ['Monitor 24" Samsung', 12, 4],
    ['Webcam HD 1080p', 28, 6],
    ['Docking Station USB', 8, 3],
    ['Headphones Sony', 35, 12],
    ['USB-C Cable 3.1', 67, 20],
    ['Tablet Stand Adjust', 14, 5],
    ['Power Adapter 65W', 22, 8]
  ];
  dashboardSheet.getRange('A5:C15').setValues(stockData);
  dashboardSheet.getRange('A4').setValue('Stock Levels - Top 10 Items');

  // Sample data for Category Distribution
  var categoryData = [
    ['Category', 'Total Value'],
    ['Electronics', 12500],
    ['Accessories', 3200],
    ['Peripherals', 2800],
    ['Networking', 1500],
    ['Storage', 2200]
  ];
  dashboardSheet.getRange('H5:I10').setValues(categoryData);
  dashboardSheet.getRange('H4').setValue('Value by Category');

  // Sample data for Price Distribution
  var priceData = [
    ['Price Range', 'Number of Items'],
    ['$0-50', 8],
    ['$51-100', 12],
    ['$101-500', 15],
    ['$501-1000', 5],
    ['$1000+', 3]
  ];
  dashboardSheet.getRange('A15:B20').setValues(priceData);
  dashboardSheet.getRange('A14').setValue('Price Distribution');

  // Sample data for Monthly Movement
  var monthlyData = [
    ['Month', 'Stock In', 'Stock Out'],
    ['Jan 24', 150, 120],
    ['Feb 24', 180, 145],
    ['Mar 24', 220, 180],
    ['Apr 24', 190, 165],
    ['May 24', 210, 190],
    ['Jun 24', 240, 210]
  ];
  dashboardSheet.getRange('H15:J21').setValues(monthlyData);
  dashboardSheet.getRange('H14').setValue('Monthly Stock Movement');
}

// Main dashboard update function
function updateDashboard() {
  try {
    var inventorySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
    var transactionsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Transactions');
    var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
    
    // Create dashboard if it doesn't exist
    if (!dashboardSheet) {
      dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Dashboard');
      setupDashboardLayout();
    }
    
    var inventoryData = inventorySheet.getDataRange().getValues();
    var transactionData = transactionsSheet.getDataRange().getValues();
    
    // Calculate metrics
    var metrics = calculateMetrics(inventoryData, transactionData);
    
    // Update KPI cards
    updateKPICards(dashboardSheet, metrics);
    
    // Update chart data with real data
    updateChartData(dashboardSheet, inventoryData, transactionData);
    
    // Update tables
    updateRecentActivity(dashboardSheet, transactionData);
    updateAlerts(dashboardSheet, inventoryData);
    
    // Apply styling
    applyDashboardStyling(dashboardSheet);
    
    SpreadsheetApp.getActiveSpreadsheet().toast('Dashboard updated successfully!', 'Success', 5);
  } catch (error) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Error updating dashboard: ' + error.toString(), 'Error', 10);
  }
}

function calculateMetrics(inventoryData, transactionData) {
  var totalItems = 0;
  var totalValue = 0;
  var lowStockCount = 0;
  var outOfStockCount = 0;
  var categories = {};
  var monthlyData = {};
  
  // Calculate inventory metrics
  for (var i = 1; i < inventoryData.length; i++) {
    if (inventoryData[i][0] && inventoryData[i][0] !== '') {
      var quantity = Number(inventoryData[i][3]) || 0;
      var price = Number(inventoryData[i][4]) || 0;
      var status = inventoryData[i][6];
      var category = inventoryData[i][2];
      
      totalItems += quantity;
      totalValue += quantity * price;
      
      if (status === 'Low Stock') lowStockCount++;
      if (status === 'Out of Stock') outOfStockCount++;
      
      // Category data
      if (category) {
        if (!categories[category]) {
          categories[category] = { count: 0, value: 0 };
        }
        categories[category].count += quantity;
        categories[category].value += quantity * price;
      }
    }
  }
  
  // Calculate transaction metrics (last 30 days)
  var thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
  var monthlyIn = 0;
  var monthlyOut = 0;
  
  for (var j = 1; j < transactionData.length; j++) {
    if (transactionData[j][0] && transactionData[j][0] !== '') {
      var transactionDate = new Date(transactionData[j][4]);
      if (transactionDate >= thirtyDaysAgo) {
        var type = transactionData[j][2];
        var qty = Number(transactionData[j][3]) || 0;
        
        if (type === 'IN') monthlyIn += qty;
        if (type === 'OUT') monthlyOut += qty;
        
        // Monthly data for chart
        var monthKey = Utilities.formatDate(transactionDate, Session.getScriptTimeZone(), 'MMM yy');
        if (!monthlyData[monthKey]) {
          monthlyData[monthKey] = { in: 0, out: 0 };
        }
        if (type === 'IN') monthlyData[monthKey].in += qty;
        if (type === 'OUT') monthlyData[monthKey].out += qty;
      }
    }
  }
  
  var avgItemValue = totalItems > 0 ? totalValue / totalItems : 0;
  var stockTurnover = monthlyOut > 0 ? (monthlyOut / totalItems * 12).toFixed(1) : 0;
  
  return {
    totalItems: totalItems,
    totalValue: totalValue,
    lowStockCount: lowStockCount,
    outOfStockCount: outOfStockCount,
    avgItemValue: avgItemValue,
    stockTurnover: stockTurnover,
    categories: categories,
    monthlyData: monthlyData,
    monthlyIn: monthlyIn,
    monthlyOut: monthlyOut
  };
}

function updateKPICards(dashboardSheet, metrics) {
  // Header row
  dashboardSheet.getRange("A1:F1").setValues([[
    "Total Items", "Value", "Low Stock Items", "Value", "Out of Stock", "Value"
  ]]);
  
  // KPI values
  dashboardSheet.getRange("A2:F2").setValues([[
    metrics.totalItems,
    Utilities.formatString('$%s', metrics.totalValue.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2})),
    metrics.lowStockCount,
    Utilities.formatString('$%s', metrics.avgItemValue.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2})),
    metrics.outOfStockCount,
    metrics.stockTurnover
  ]]);
  
  // Second row headers
  dashboardSheet.getRange("A3:F3").setValues([[
    "", "Total Value", "", "Avg Item Value", "", "Stock Turnover (Annual)"
  ]]);
}

function updateChartData(dashboardSheet, inventoryData, transactionData) {
  updateStockLevelsChart(dashboardSheet, inventoryData);
  updateCategoryChart(dashboardSheet, inventoryData);
  updatePriceDistributionChart(dashboardSheet, inventoryData);
  updateMonthlyMovementChart(dashboardSheet, transactionData);
}

function updateStockLevelsChart(dashboardSheet, inventoryData) {
  var itemsWithStock = [];
  
  for (var i = 1; i < inventoryData.length; i++) {
    if (inventoryData[i][0] && inventoryData[i][0] !== '') {
      itemsWithStock.push({
        name: inventoryData[i][1] || 'Unnamed Item',
        stock: Number(inventoryData[i][3]) || 0,
        minStock: Number(inventoryData[i][5]) || 0
      });
    }
  }
  
  // Sort by stock quantity (descending) and take top 10
  itemsWithStock.sort(function(a, b) { return b.stock - a.stock; });
  var topItems = itemsWithStock.slice(0, 10);
  
  var stockChartData = [['Item', 'Current Stock', 'Min Stock']];
  topItems.forEach(function(item) {
    var displayName = item.name.length > 15 ? item.name.substring(0, 15) + '...' : item.name;
    stockChartData.push([displayName, item.stock, item.minStock]);
  });
  
  // Pad with empty data if needed
  while (stockChartData.length < 11) {
    stockChartData.push(['No Data', 0, 0]);
  }
  
  dashboardSheet.getRange('A5:C15').setValues(stockChartData);
}

function updateCategoryChart(dashboardSheet, inventoryData) {
  var categories = {};
  
  for (var i = 1; i < inventoryData.length; i++) {
    if (inventoryData[i][0] && inventoryData[i][0] !== '') {
      var category = inventoryData[i][2] || 'Uncategorized';
      var quantity = Number(inventoryData[i][3]) || 0;
      var price = Number(inventoryData[i][4]) || 0;
      var value = quantity * price;
      
      if (!categories[category]) categories[category] = 0;
      categories[category] += value;
    }
  }
  
  var categoryChartData = [['Category', 'Total Value']];
  for (var category in categories) {
    categoryChartData.push([category, categories[category]]);
  }
  
  // Sort by value (descending) and take top 6
  categoryChartData.sort(function(a, b) {
    if (a[0] === 'Category') return -1;
    if (b[0] === 'Category') return 1;
    return b[1] - a[1];
  });
  
  // Pad category data
  while (categoryChartData.length < 7) {
    categoryChartData.push(['No Data', 0]);
  }
  
  dashboardSheet.getRange('H5:I11').setValues(categoryChartData.slice(0, 7));
}

function updatePriceDistributionChart(dashboardSheet, inventoryData) {
  var priceRanges = {
    '$0-50': 0,
    '$51-100': 0,
    '$101-500': 0,
    '$501-1000': 0,
    '$1000+': 0
  };
  
  for (var i = 1; i < inventoryData.length; i++) {
    if (inventoryData[i][0] && inventoryData[i][0] !== '') {
      var price = Number(inventoryData[i][4]) || 0;
      if (price <= 50) priceRanges['$0-50']++;
      else if (price <= 100) priceRanges['$51-100']++;
      else if (price <= 500) priceRanges['$101-500']++;
      else if (price <= 1000) priceRanges['$501-1000']++;
      else priceRanges['$1000+']++;
    }
  }
  
  var priceChartData = [['Price Range', 'Number of Items']];
  for (var range in priceRanges) {
    priceChartData.push([range, priceRanges[range]]);
  }
  
  dashboardSheet.getRange('A15:B20').setValues(priceChartData);
}

function updateMonthlyMovementChart(dashboardSheet, transactionData) {
  var monthlyData = {};
  var sixMonthsAgo = new Date();
  sixMonthsAgo.setMonth(sixMonthsAgo.getMonth() - 6);
  
  for (var i = 1; i < transactionData.length; i++) {
    if (transactionData[i][0] && transactionData[i][0] !== '') {
      var date = new Date(transactionData[i][4]);
      if (date >= sixMonthsAgo) {
        var monthKey = Utilities.formatDate(date, Session.getScriptTimeZone(), 'MMM yy');
        var type = transactionData[i][2];
        var quantity = Number(transactionData[i][3]) || 0;
        
        if (!monthlyData[monthKey]) {
          monthlyData[monthKey] = { IN: 0, OUT: 0 };
        }
        monthlyData[monthKey][type] += quantity;
      }
    }
  }
  
  var monthlyChartData = [['Month', 'Stock In', 'Stock Out']];
  var months = Object.keys(monthlyData).sort();
  
  // Get last 6 months
  var last6Months = [];
  for (var m = 5; m >= 0; m--) {
    var month = new Date();
    month.setMonth(month.getMonth() - m);
    last6Months.push(Utilities.formatDate(month, Session.getScriptTimeZone(), 'MMM yy'));
  }
  
  last6Months.forEach(function(month) {
    var inQty = monthlyData[month] ? monthlyData[month].IN : 0;
    var outQty = monthlyData[month] ? monthlyData[month].OUT : 0;
    monthlyChartData.push([month, inQty, outQty]);
  });
  
  dashboardSheet.getRange('H15:J21').setValues(monthlyChartData);
}

function updateRecentActivity(dashboardSheet, transactionData) {
  dashboardSheet.getRange("A23").setValue("Recent Activity");
  
  var headers = [['Date', 'Item ID', 'Type', 'Qty', 'User', 'Notes']];
  dashboardSheet.getRange("A24:F24").setValues(headers);
  
  // Get last 10 transactions
  var recentData = [];
  var startIdx = Math.max(transactionData.length - 10, 1);
  
  for (var i = startIdx; i < transactionData.length; i++) {
    if (transactionData[i][0] && transactionData[i][0] !== '') {
      var date = new Date(transactionData[i][4]);
      var dateStr = Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd/yy');
      
      recentData.push([
        dateStr,
        transactionData[i][1],
        transactionData[i][2],
        transactionData[i][3],
        transactionData[i][5],
        transactionData[i][6]
      ]);
    }
  }
  
  if (recentData.length > 0) {
    dashboardSheet.getRange("A25:F" + (25 + recentData.length - 1)).setValues(recentData);
  } else {
    // Add sample recent activity
    var sampleData = [
      [Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yy'), 'INV001', 'IN', 10, 'System', 'Initial stock'],
      [Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yy'), 'INV002', 'OUT', 5, 'Admin', 'Sales order']
    ];
    dashboardSheet.getRange("A25:F26").setValues(sampleData);
  }
}

function updateAlerts(dashboardSheet, inventoryData) {
  dashboardSheet.getRange("H23").setValue("Stock Alerts");
  
  var alerts = [['Item', 'Status', 'Current', 'Min', 'Category']];
  var alertData = [];
  
  for (var i = 1; i < inventoryData.length; i++) {
    if (inventoryData[i][0] && inventoryData[i][0] !== '') {
      var status = inventoryData[i][6];
      if (status === 'Low Stock' || status === 'Out of Stock') {
        alertData.push([
          inventoryData[i][1],
          status,
          inventoryData[i][3],
          inventoryData[i][5],
          inventoryData[i][2]
        ]);
      }
    }
  }
  
  if (alertData.length > 0) {
    dashboardSheet.getRange("H24:L24").setValues(alerts);
    dashboardSheet.getRange("H25:L" + (25 + alertData.length - 1)).setValues(alertData);
  } else {
    // Add sample alerts header
    dashboardSheet.getRange("H24:L24").setValues(alerts);
    dashboardSheet.getRange("H25").setValue("No current alerts");
  }
}

function applyDashboardStyling(dashboardSheet) {
  // Clear existing formatting by setting default styles
  var entireSheet = dashboardSheet.getRange(1, 1, dashboardSheet.getMaxRows(), dashboardSheet.getMaxColumns());
  entireSheet.setBackground('white')
    .setFontColor('black')
    .setFontWeight('normal')
    .setBorder(false, false, false, false, false, false);
  
  // KPI Cards Styling
  var kpiHeaders = dashboardSheet.getRange("A1:F1");
  kpiHeaders.setBackground('#4285f4')
    .setFontColor('white')
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true);
  
  // KPI Values
  var kpiValues = dashboardSheet.getRange("A2:F2");
  kpiValues.setBackground('#f8f9fa')
    .setFontSize(14)
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true);
  
  // KPI Labels
  var kpiLabels = dashboardSheet.getRange("A3:F3");
  kpiLabels.setBackground('#e8f0fe')
    .setFontColor('#4285f4')
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true);
  
  // Chart headers
  var chartHeaders = ['A4', 'H4', 'A14', 'H14'];
  chartHeaders.forEach(function(header) {
    dashboardSheet.getRange(header)
      .setFontWeight('bold')
      .setFontSize(12)
      .setBackground('#34a853')
      .setFontColor('white')
      .setHorizontalAlignment('center');
  });
  
  // Table headers
  var tableHeaders = ['A23', 'H23'];
  tableHeaders.forEach(function(header) {
    dashboardSheet.getRange(header)
      .setFontWeight('bold')
      .setFontSize(12)
      .setBackground('#fbbc05')
      .setFontColor('white')
      .setHorizontalAlignment('center');
  });
  
  // Data tables styling
  var dataRanges = ['A24:F34', 'H24:L34'];
  dataRanges.forEach(function(range) {
    var dataRange = dashboardSheet.getRange(range);
    dataRange.setBorder(true, true, true, true, true, true);
    
    // Header row
    if (dataRange.getNumRows() > 0) {
      dataRange.offset(0, 0, 1, dataRange.getNumColumns())
        .setBackground('#f8f9fa')
        .setFontWeight('bold');
    }
    
    // Alternating row colors
    var numRows = dataRange.getNumRows();
    for (var i = 1; i < numRows; i++) {
      if (i % 2 === 0) {
        dataRange.offset(i, 0, 1, dataRange.getNumColumns())
          .setBackground('#f8f9fa');
      }
    }
  });
  
  // Chart data styling
  var chartDataRanges = ['A5:C15', 'H5:I11', 'A15:B20', 'H15:J21'];
  chartDataRanges.forEach(function(range) {
    var rangeObj = dashboardSheet.getRange(range);
    rangeObj.setBorder(true, true, true, true, true, true)
      .setHorizontalAlignment('center');
    
    // Style header row of chart data
    if (rangeObj.getNumRows() > 0) {
      rangeObj.offset(0, 0, 1, rangeObj.getNumColumns())
        .setBackground('#e8f0fe')
        .setFontWeight('bold');
    }
  });
}

// Create embedded charts
function createEmbeddedCharts() {
  try {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var dashboardSheet = spreadsheet.getSheetByName('Dashboard');
    
    // Create dashboard if it doesn't exist
    if (!dashboardSheet) {
      initializeDashboard();
      return;
    }
    
    // Remove existing charts
    var charts = dashboardSheet.getCharts();
    charts.forEach(function(chart) {
      dashboardSheet.removeChart(chart);
    });
    
    // Create Stock Level Chart
    var stockChart = dashboardSheet.newChart()
      .setChartType(Charts.ChartType.COLUMN)
      .addRange(dashboardSheet.getRange('A5:B15'))
      .setPosition(5, 7, 0, 0)
      .setOption('title', 'Current Stock Levels')
      .setOption('legend', { position: 'none' })
      .setOption('hAxis', { 
        title: 'Items',
        slantedText: true,
        slantedTextAngle: 45
      })
      .setOption('vAxis', { title: 'Quantity' })
      .setOption('colors', ['#4285f4'])
      .setOption('width', 500)
      .setOption('height', 300)
      .build();
    
    dashboardSheet.insertChart(stockChart);
    
    // Create Category Pie Chart
    var categoryChart = dashboardSheet.newChart()
      .setChartType(Charts.ChartType.PIE)
      .addRange(dashboardSheet.getRange('H5:I11'))
      .setPosition(5, 17, 0, 0)
      .setOption('title', 'Inventory Value by Category')
      .setOption('pieHole', 0.4)
      .setOption('is3D', true)
      .setOption('width', 450)
      .setOption('height', 300)
      .build();
    
    dashboardSheet.insertChart(categoryChart);
    
    // Create Price Distribution Chart
    var priceChart = dashboardSheet.newChart()
      .setChartType(Charts.ChartType.BAR)
      .addRange(dashboardSheet.getRange('A15:B20'))
      .setPosition(20, 7, 0, 0)
      .setOption('title', 'Price Distribution')
      .setOption('legend', { position: 'none' })
      .setOption('hAxis', { title: 'Number of Items' })
      .setOption('vAxis', { title: 'Price Range' })
      .setOption('colors', ['#34a853'])
      .setOption('width', 500)
      .setOption('height', 300)
      .build();
    
    dashboardSheet.insertChart(priceChart);
    
    // Create Monthly Movement Chart
    var monthlyChart = dashboardSheet.newChart()
      .setChartType(Charts.ChartType.LINE)
      .addRange(dashboardSheet.getRange('H15:J21'))
      .setPosition(20, 17, 0, 0)
      .setOption('title', 'Monthly Stock Movement')
      .setOption('hAxis', { title: 'Month' })
      .setOption('vAxis', { title: 'Quantity' })
      .setOption('series', {
        0: { color: '#4285f4' },
        1: { color: '#ea4335' }
      })
      .setOption('width', 450)
      .setOption('height', 300)
      .build();
    
    dashboardSheet.insertChart(monthlyChart);
    
    SpreadsheetApp.getActiveSpreadsheet().toast('Visual charts created successfully!', 'Success', 5);
  } catch (error) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Error creating charts: ' + error.toString(), 'Error', 10);
  }
}

function fixDashboardCharts() {
  var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  
  // Create dashboard if it doesn't exist
  if (!dashboardSheet) {
    initializeDashboard();
    return;
  }
  
  // Check if we have data for charts
  var stockDataRange = dashboardSheet.getRange('A5:C15');
  var stockData = stockDataRange.getValues();
  
  var hasData = false;
  for (var i = 0; i < stockData.length; i++) {
    for (var j = 0; j < stockData[i].length; j++) {
      if (stockData[i][j] && stockData[i][j] !== '') {
        hasData = true;
        break;
      }
    }
    if (hasData) break;
  }
  
  if (!hasData) {
    createSampleChartData();
    SpreadsheetApp.getActiveSpreadsheet().toast('Sample data added to charts. Run "Create Visual Charts" to generate charts.', 'Info', 5);
  } else {
    createEmbeddedCharts();
  }
}

// Inventory Management Functions
function showAddItemDialog() {
  var html = HtmlService.createHtmlOutputFromFile('AddItemDialog')
    .setWidth(400)
    .setHeight(500);
  SpreadsheetApp.getUi().showModalDialog(html, 'Add New Item');
}

function showUpdateStockDialog() {
  var html = HtmlService.createHtmlOutputFromFile('UpdateStockDialog')
    .setWidth(400)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'Update Stock');
}

function addItem(itemName, category, quantity, price, minStock) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
    var lastRow = sheet.getLastRow();
    
    // Generate unique ID
    var itemId = 'INV' + String(lastRow).padStart(3, '0');
    
    // Determine status
    var status = 'Good';
    if (quantity == 0) {
      status = 'Out of Stock';
    } else if (quantity <= minStock) {
      status = 'Low Stock';
    }
    
    // Add item to inventory
    sheet.getRange(lastRow + 1, 1, 1, 8).setValues([[
      itemId,
      itemName,
      category,
      quantity,
      price,
      minStock,
      status,
      new Date()
    ]]);
    
    // Record transaction
    recordTransaction(itemId, 'IN', quantity, 'Initial stock');
    
    // Update dashboard
    updateDashboard();
    
    return 'Item added successfully! ID: ' + itemId;
  } catch (error) {
    return 'Error adding item: ' + error.toString();
  }
}

function updateStock(itemId, type, quantity, notes) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
    var data = sheet.getDataRange().getValues();
    
    for (var i = 1; i < data.length; i++) {
      if (data[i][0] === itemId) {
        var currentQty = Number(data[i][3]) || 0;
        var newQty = type === 'IN' ? currentQty + quantity : currentQty - quantity;
        var minStock = Number(data[i][5]) || 0;
        
        if (newQty < 0) {
          return 'Error: Cannot have negative stock! Current: ' + currentQty + ', Trying to remove: ' + quantity;
        }
        
        // Update quantity
        sheet.getRange(i + 1, 4).setValue(newQty);
        
        // Update status
        var status = 'Good';
        if (newQty == 0) {
          status = 'Out of Stock';
        } else if (newQty <= minStock) {
          status = 'Low Stock';
        }
        sheet.getRange(i + 1, 7).setValue(status);
        
        // Update timestamp
        sheet.getRange(i + 1, 8).setValue(new Date());
        
        // Record transaction
        recordTransaction(itemId, type, quantity, notes);
        
        // Update dashboard
        updateDashboard();
        
        return 'Stock updated successfully! New quantity: ' + newQty;
      }
    }
    
    return 'Item not found! Please check the Item ID.';
  } catch (error) {
    return 'Error updating stock: ' + error.toString();
  }
}

function recordTransaction(itemId, type, quantity, notes) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Transactions');
  var lastRow = sheet.getLastRow();
  var transactionId = 'T' + String(lastRow).padStart(3, '0');
  
  sheet.getRange(lastRow + 1, 1, 1, 7).setValues([[
    transactionId,
    itemId,
    type,
    quantity,
    new Date(),
    Session.getEffectiveUser().getEmail(),
    notes
  ]]);
}

function getItems() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
  var data = sheet.getDataRange().getValues();
  var items = [];
  
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] && data[i][0] !== '') {
      items.push({
        id: data[i][0],
        name: data[i][1],
        quantity: data[i][3],
        category: data[i][2]
      });
    }
  }
  
  return items;
}

function generateReport() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var inventoryData = spreadsheet.getSheetByName('Inventory').getDataRange().getValues();
  
  // Create report sheet or clear existing
  var reportSheet = spreadsheet.getSheetByName('Inventory Report');
  if (!reportSheet) {
    reportSheet = spreadsheet.insertSheet('Inventory Report');
  } else {
    reportSheet.clear();
  }
  
  // Add headers and data
  reportSheet.getRange(1, 1, inventoryData.length, inventoryData[0].length)
    .setValues(inventoryData);
  
  // Add summary
  var summaryRow = inventoryData.length + 2;
  var totalItems = 0;
  var totalValue = 0;
  
  for (var i = 1; i < inventoryData.length; i++) {
    if (inventoryData[i][0]) {
      totalItems += Number(inventoryData[i][3]) || 0;
      totalValue += (Number(inventoryData[i][3]) || 0) * (Number(inventoryData[i][4]) || 0);
    }
  }
  
  reportSheet.getRange(summaryRow, 1).setValue('Report Summary:');
  reportSheet.getRange(summaryRow, 2).setValue('Generated on: ' + new Date());
  reportSheet.getRange(summaryRow + 1, 1).setValue('Total Items:');
  reportSheet.getRange(summaryRow + 1, 2).setValue(totalItems);
  reportSheet.getRange(summaryRow + 2, 1).setValue('Total Value:');
  reportSheet.getRange(summaryRow + 2, 2).setValue('$' + totalValue.toFixed(2));
  
  // Auto-resize columns
  reportSheet.autoResizeColumns(1, inventoryData[0].length);
  
  SpreadsheetApp.getActiveSpreadsheet().toast('Report generated in "Inventory Report" sheet!', 'Success', 5);
}

// Visual Dashboard HTML
function showVisualDashboard() {
  var html = HtmlService.createHtmlOutputFromFile('VisualDashboard')
    .setWidth(1200)
    .setHeight(800);
  SpreadsheetApp.getUi().showModalDialog(html, '📊 Visual Dashboard - PowerBI Style');
}

function getDashboardData() {
  var inventorySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
  var transactionsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Transactions');
  
  var inventoryData = inventorySheet.getDataRange().getValues();
  var transactionData = transactionsSheet.getDataRange().getValues();
  
  var metrics = calculateMetrics(inventoryData, transactionData);
  
  // Get recent activity
  var recentActivity = [];
  var startIdx = Math.max(transactionData.length - 5, 1);
  for (var i = startIdx; i < transactionData.length; i++) {
    if (transactionData[i][0]) {
      var date = new Date(transactionData[i][4]);
      recentActivity.push({
        date: Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd'),
        item: transactionData[i][1],
        type: transactionData[i][2],
        quantity: transactionData[i][3]
      });
    }
  }
  
  // Get alerts
  var alerts = [];
  for (var j = 1; j < inventoryData.length; j++) {
    if (inventoryData[j][0] && (inventoryData[j][6] === 'Low Stock' || inventoryData[j][6] === 'Out of Stock')) {
      alerts.push({
        item: inventoryData[j][1],
        status: inventoryData[j][6],
        current: inventoryData[j][3],
        min: inventoryData[j][5]
      });
    }
  }
  
  return {
    totalItems: metrics.totalItems,
    totalValue: metrics.totalValue,
    lowStockCount: metrics.lowStockCount,
    outOfStockCount: metrics.outOfStockCount,
    recentActivity: recentActivity,
    alerts: alerts
  };
}