rewrite below code properly <!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>GST Invoice Processor</title>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
  <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@300;400;500;600;700&display=swap" rel="stylesheet">
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
  <style>
    /* CSS styles here (same as provided) */
    :root {
      --primary: #4f46e5;
      --primary-dark: #4338ca;
      --secondary: #f97316;
      --success: #22c55e;
      --danger: #ef4444;
      --warning: #eab308;
      --dark: #1e293b;
      --light: #f8fafc;
      --white: #ffffff;
      --gray: #64748b;
      --gray-light: #e2e8f0;
    }
    
    * {
      margin: 0;
      padding: 0;
      box-sizing: border-box;
    }
    
    body {
      font-family: 'Poppins', sans-serif;
      background-color: #f1f5f9;
      color: var(--dark);
      line-height: 1.6;
    }
    
    /* Header styles */
    .header {
      display: flex;
      align-items: center;
      justify-content: space-between;
      padding: 1rem 2rem;
      background-color: var(--white);
      box-shadow: 0 1px 3px rgba(0,0,0,0.1);
    }
    
    .logo {
      font-size: 1.5rem;
      font-weight: 700;
      color: var(--primary);
      display: flex;
      align-items: center;
      gap: 0.5rem;
    }
    
    /* Main content styles */
    .container {
      max-width: 1280px;
      margin: 2rem auto;
      padding: 0 1rem;
    }
    
    .card {
      background-color: var(--white);
      border-radius: 0.5rem;
      box-shadow: 0 1px 3px rgba(0,0,0,0.1);
      padding: 1.5rem;
      margin-bottom: 1.5rem;
    }
    
    .card-title {
      font-size: 1.25rem;
      font-weight: 600;
      margin-bottom: 1rem;
      color: var(--dark);
      display: flex;
      align-items: center;
      gap: 0.5rem;
    }
    
    /* Upload area styles */
    .upload-area {
      border: 2px dashed var(--gray-light);
      border-radius: 0.5rem;
      padding: 2rem;
      text-align: center;
      cursor: pointer;
      transition: all 0.3s ease;
    }
    
    .upload-area:hover {
      border-color: var(--primary);
    }
    
    .upload-icon {
      font-size: 3rem;
      color: var(--primary);
      margin-bottom: 1rem;
    }
    
    .upload-text {
      font-size: 1rem;
      color: var(--gray);
      margin-bottom: 1rem;
    }
    
    .upload-btn {
      display: inline-flex;
      align-items: center;
      justify-content: center;
      gap: 0.5rem;
      background-color: var(--primary);
      color: var(--white);
      padding: 0.75rem 1.5rem;
      border-radius: 0.375rem;
      font-weight: 500;
      cursor: pointer;
      transition: background-color 0.3s ease;
      border: none;
    }
    
    .upload-btn:hover {
      background-color: var(--primary-dark);
    }
    
    /* Tab styles */
    .tabs {
      display: flex;
      border-bottom: 1px solid var(--gray-light);
      margin-bottom: 1.5rem;
    }
    
    .tab {
      padding: 0.75rem 1.5rem;
      cursor: pointer;
      font-weight: 500;
      border-bottom: 2px solid transparent;
      transition: all 0.3s ease;
    }
    
    .tab.active {
      color: var(--primary);
      border-bottom: 2px solid var(--primary);
    }
    
    /* Table styles */
    .table-container {
      overflow-x: auto;
      margin-top: 1rem;
    }
    
    table {
      width: 100%;
      border-collapse: collapse;
      text-align: left;
      font-size: 0.875rem;
    }
    
    th {
      background-color: #f8fafc;
      padding: 0.75rem 1rem;
      font-weight: 600;
      border-bottom: 1px solid var(--gray-light);
    }
    
    td {
      padding: 0.75rem 1rem;
      border-bottom: 1px solid var(--gray-light);
    }
    
    tr:last-child td {
      border-bottom: none;
    }
    
    tr:hover {
      background-color: #f1f5f9;
    }
    
    /* Button styles */
    .btn {
      display: inline-flex;
      align-items: center;
      justify-content: center;
      gap: 0.5rem;
      padding: 0.5rem 1rem;
      border-radius: 0.375rem;
      font-weight: 500;
      cursor: pointer;
      transition: all 0.3s ease;
      border: none;
    }
    
    .btn-primary {
      background-color: var(--primary);
      color: var(--white);
    }
    
    .btn-primary:hover {
      background-color: var(--primary-dark);
    }
    
    .btn-secondary {
      background-color: var(--white);
      color: var(--dark);
      border: 1px solid var(--gray-light);
    }
    
    .btn-secondary:hover {
      background-color: #f1f5f9;
    }
    
    .btn-success {
      background-color: var(--success);
      color: var(--white);
    }
    
    .btn-success:hover {
      background-color: #15803d;
    }
    
    /* Dashboard styles */
    .dashboard {
      display: grid;
      grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
      gap: 1rem;
      margin-bottom: 1.5rem;
    }
    
    .stat-card {
      background-color: var(--white);
      border-radius: 0.5rem;
      box-shadow: 0 1px 3px rgba(0,0,0,0.1);
      padding: 1.5rem;
      display: flex;
      flex-direction: column;
    }
    
    .stat-title {
      font-size: 0.875rem;
      color: var(--gray);
      margin-bottom: 0.5rem;
    }
    
    .stat-value {
      font-size: 1.5rem;
      font-weight: 700;
      margin-bottom: 0.25rem;
    }
    
    .stat-change {
      font-size: 0.875rem;
      display: flex;
      align-items: center;
      gap: 0.25rem;
    }
    
    .stat-change.positive {
      color: var(--success);
    }
    
    .stat-change.negative {
      color: var(--danger);
    }
    
    .chart-container {
      height: 300px;
      margin-top: 1rem;
    }
    
    /* Badge styles */
    .badge {
      display: inline-flex;
      align-items: center;
      justify-content: center;
      padding: 0.25rem 0.5rem;
      border-radius: 9999px;
      font-size: 0.75rem;
      font-weight: 500;
    }
    
    .badge-success {
      background-color: #dcfce7;
      color: #166534;
    }
    
    .badge-warning {
      background-color: #fef9c3;
      color: #854d0e;
    }
    
    .badge-danger {
      background-color: #fee2e2;
      color: #b91c1c;
    }
    
    /* Utility classes */
    .flex {
      display: flex;
    }
    
    .flex-col {
      flex-direction: column;
    }
    
    .items-center {
      align-items: center;
    }
    
    .justify-between {
      justify-content: space-between;
    }
    
    .gap-2 {
      gap: 0.5rem;
    }
    
    .mt-4 {
      margin-top: 1rem;
    }
    
    .mb-4 {
      margin-bottom: 1rem;
    }
    
    .hidden {
      display: none;
    }
    
    /* Loading indicator */
    .loader {
      border: 3px solid rgba(0,0,0,0.1);
      border-top: 3px solid var(--primary);
      border-radius: 50%;
      width: 24px;
      height: 24px;
      animation: spin 1s linear infinite;
      margin: 0 auto;
    }
    
    @keyframes spin {
      0% { transform: rotate(0deg); }
      100% { transform: rotate(360deg); }
    }
    
    /* Responsive table for narrow screens */
    @media (max-width: 768px) {
      .desktop-only {
        display: none;
      }
      
      .dashboard {
        grid-template-columns: 1fr;
      }
    }
  </style>
</head>
<body>
  <header class="header">
    <div class="logo">
      <i class="fas fa-file-invoice"></i>
      <span>GST Invoice Processor</span>
    </div>
    <div>
      <button class="btn btn-secondary">
        <i class="fas fa-question-circle"></i>
        Help
      </button>
    </div>
  </header>
  
  <div class="container">
    <div class="card">
      <h2 class="card-title"><i class="fas fa-cloud-upload-alt"></i> Upload Excel Data</h2>
      <div class="upload-area" id="uploadArea">
        <i class="fas fa-file-excel upload-icon"></i>
        <p class="upload-text">Drag and drop your Excel file here or click to browse</p>
        <p class="upload-text">Supported formats: .xlsx, .xls</p>
        <button class="upload-btn">
          <i class="fas fa-upload"></i>
          Choose File
        </button>
        <input type="file" id="fileInput" accept=".xlsx, .xls" style="display: none">
      </div>
      <div id="uploadStatus" class="mt-4 hidden">
        <div class="flex items-center gap-2">
          <div class="loader"></div>
          <span>Processing file...</span>
        </div>
      </div>
      <div id="uploadSuccess" class="mt-4 hidden">
        <div class="flex items-center gap-2 text-green-600">
          <i class="fas fa-check-circle"></i>
          <span>File processed successfully!</span>
        </div>
      </div>
      <div id="uploadError" class="mt-4 hidden">
        <div class="flex items-center gap-2 text-red-600">
          <i class="fas fa-exclamation-circle"></i>
          <span id="errorMessage">Error processing file.</span>
        </div>
      </div>
    </div>
    
    <div id="resultsSection" class="hidden">
      <div class="dashboard">
        <div class="stat-card">
          <div class="stat-title">Total Invoices</div>
          <div class="stat-value" id="totalInvoices">0</div>
          <div class="flex items-center justify-between">
            <div class="stat-change positive">
              <i class="fas fa-arrow-up"></i>
              <span>4.75%</span>
            </div>
            <span class="text-gray-500 text-sm">vs. last month</span>
          </div>
        </div>
        
        <div class="stat-card">
          <div class="stat-title">Total GST Amount</div>
          <div class="stat-value" id="totalGST">₹0.00</div>
          <div class="flex items-center justify-between">
            <div class="stat-change positive">
              <i class="fas fa-arrow-up"></i>
              <span>2.5%</span>
            </div>
            <span class="text-gray-500 text-sm">vs. last month</span>
          </div>
        </div>
        
        <div class="stat-card">
          <div class="stat-title">Total Taxable Value</div>
          <div class="stat-value" id="totalTaxable">₹0.00</div>
          <div class="flex items-center justify-between">
            <div class="stat-change positive">
              <i class="fas fa-arrow-up"></i>
              <span>3.2%</span>
            </div>
            <span class="text-gray-500 text-sm">vs. last month</span>
          </div>
        </div>
        
        <div class="stat-card">
          <div class="stat-title">Total Gross Amount</div>
          <div class="stat-value" id="totalGross">₹0.00</div>
          <div class="flex items-center justify-between">
            <div class="stat-change positive">
              <i class="fas fa-arrow-up"></i>
              <span>5.1%</span>
            </div>
            <span class="text-gray-500 text-sm">vs. last month</span>
          </div>
        </div>
      </div>
      
      <div class="card">
        <div class="flex items-center justify-between mb-4">
          <h2 class="card-title"><i class="fas fa-chart-pie"></i> GST Summary</h2>
          <button class="btn btn-secondary">
            <i class="fas fa-download"></i>
            Export
          </button>
        </div>
        <div class="flex flex-col md:flex-row gap-2">
          <div class="chart-container" style="flex: 1;">
            <canvas id="gstDistributionChart"></canvas>
          </div>
          <div class="chart-container" style="flex: 1;">
            <canvas id="monthlyTrendChart"></canvas>
          </div>
        </div>
      </div>
      
      <div class="card">
        <div class="tabs">
          <div class="tab active" data-tab="invoiceData">Invoice Data</div>
          <div class="tab" data-tab="gstData">GST Data</div>
          <div class="tab" data-tab="misReports">MIS Reports</div>
        </div>
        
        <div id="invoiceData" class="tab-content">
          <div class="flex items-center justify-between mb-4">
            <div class="flex items-center gap-2">
              <input type="text" placeholder="Search invoices..." class="px-3 py-2 border border-gray-300 rounded-md">
              <button class="btn btn-secondary">
                <i class="fas fa-search"></i>
                Search
              </button>
            </div>
            <div class="flex items-center gap-2">
              <button class="btn btn-secondary">
                <i class="fas fa-filter"></i>
                Filter
              </button>
              <button class="btn btn-success">
                <i class="fas fa-download"></i>
                Export
              </button>
            </div>
          </div>
          
          <div class="table-container">
            <table id="invoiceTable">
              <thead>
                <tr>
                  <th>S.No</th>
                  <th>Date</th>
                  <th>Party Name</th>
                  <th>GSTIN</th>
                  <th>Invoice No.</th>
                  <th>Commodity</th>
                  <th>Qty</th>
                  <th>Taxable Value</th>
                  <th>GST</th>
                  <th>Gross Amount</th>
                  <th>Actions</th>
                </tr>
              </thead>
              <tbody>
                <!-- This will be populated by JavaScript -->
              </tbody>
            </table>
          </div>
          
          <div class="flex items-center justify-between mt-4">
            <div>
              <span>Showing 1-10 of 100 entries</span>
            </div>
            <div class="flex items-center gap-2">
              <button class="btn btn-secondary">Previous</button>
              <button class="btn btn-primary">Next</button>
            </div>
          </div>
        </div>
        
        <div id="gstData" class="tab-content hidden">
          <div class="flex items-center justify-between mb-4">
            <div class="flex items-center gap-2">
              <select class="px-3 py-2 border border-gray-300 rounded-md">
                <option>All Tax Rates</option>
                <option>5%</option>
                <option>12%</option>
                <option>18%</option>
                <option>28%</option>
              </select>
              <button class="btn btn-secondary">
                <i class="fas fa-filter"></i>
                Apply
              </button>
            </div>
            <div>
              <button class="btn btn-success">
                <i class="fas fa-download"></i>
                Export GST Report
              </button>
            </div>
          </div>
          
          <div class="table-container">
            <table id="gstTable">
              <thead>
                <tr>
                  <th>S.No</th>
                  <th>GSTIN</th>
                  <th>Party Name</th>
                  <th>Invoice No.</th>
                  <th>Date</th>
                  <th>HSN Code</th>
                  <th>Tax Rate</th>
                  <th>Taxable Value</th>
                  <th>CGST</th>
                  <th>SGST</th>
                  <th>IGST</th>
                  <th>Total GST</th>
                </tr>
              </thead>
              <tbody>
                <!-- This will be populated by JavaScript -->
              </tbody>
            </table>
          </div>
        </div>
        
        <div id="misReports" class="tab-content hidden">
          <div class="grid grid-cols-1 md:grid-cols-2 gap-4 mb-4">
            <div class="card">
              <h3 class="card-title"><i class="fas fa-tag"></i> HSN-wise Summary</h3>
              <div class="table-container">
                <table id="hsnTable">
                  <thead>
                    <tr>
                      <th>HSN Code</th>
                      <th>Description</th>
                      <th>Total Qty</th>
                      <th>Total Value</th>
                      <th>Total GST</th>
                    </tr>
                  </thead>
                  <tbody>
                    <!-- This will be populated by JavaScript -->
                  </tbody>
                </table>
              </div>
            </div>
            
            <div class="card">
              <h3 class="card-title"><i class="fas fa-percentage"></i> Tax Rate Summary</h3>
              <div class="table-container">
                <table id="taxRateTable">
                  <thead>
                    <tr>
                      <th>Tax Rate</th>
                      <th>Taxable Value</th>
                      <th>CGST</th>
                      <th>SGST</th>
                      <th>IGST</th>
                      <th>Total Tax</th>
                    </tr>
                  </thead>
                  <tbody>
                    <!-- This will be populated by JavaScript -->
                  </tbody>
                </table>
              </div>
            </div>
          </div>
          
          <div class="card">
            <h3 class="card-title"><i class="fas fa-user"></i> Party-wise Summary</h3>
            <div class="table-container">
              <table id="partyTable">
                <thead>
                  <tr>
                    <th>Party Name</th>
                    <th>GSTIN</th>
                    <th>No. of Invoices</th>
                    <th>Total Taxable Value</th>
                    <th>Total GST</th>
                    <th>Total Gross</th>
                  </tr>
                </thead>
                <tbody>
                  <!-- This will be populated by JavaScript -->
                </tbody>
              </table>
            </div>
          </div>
        </div>
      </div>
    </div>
  </div>

  <script>
    // JavaScript functionality here
    document.addEventListener('DOMContentLoaded', function() {
      // DOM elements
      const uploadArea = document.getElementById('uploadArea');
      const fileInput = document.getElementById('fileInput');
      const uploadStatus = document.getElementById('uploadStatus');
      const uploadSuccess = document.getElementById('uploadSuccess');
      const uploadError = document.getElementById('uploadError');
      const errorMessage = document.getElementById('errorMessage');
      const resultsSection = document.getElementById('resultsSection');
      
      // Stats elements
      const totalInvoices = document.getElementById('totalInvoices');
      const totalGST = document.getElementById('totalGST');
      const totalTaxable = document.getElementById('totalTaxable');
      const totalGross = document.getElementById('totalGross');
      
      // Table elements
      const invoiceTable = document.getElementById('invoiceTable').getElementsByTagName('tbody')[0];
      const gstTable = document.getElementById('gstTable').getElementsByTagName('tbody')[0];
      const hsnTable = document.getElementById('hsnTable').getElementsByTagName('tbody')[0];
      const taxRateTable = document.getElementById('taxRateTable').getElementsByTagName('tbody')[0];
      const partyTable = document.getElementById('partyTable').getElementsByTagName('tbody')[0];
      
      // Global data storage
      let invoiceData = [];
      
      // Tab switching
      const tabs = document.querySelectorAll('.tab');
      const tabContents = document.querySelectorAll('.tab-content');
      
      tabs.forEach(tab => {
        tab.addEventListener('click', () => {
          // Remove active class from all tabs
          tabs.forEach(t => t.classList.remove('active'));
          
          // Add active class to clicked tab
          tab.classList.add('active');
          
          // Hide all tab contents
          tabContents.forEach(content => content.classList.add('hidden'));
          
          // Show the selected tab content
          const tabId = tab.getAttribute('data-tab');
          document.getElementById(tabId).classList.remove('hidden');
        });
      });
      
      // Handle file upload click
      uploadArea.addEventListener('click', () => {
        fileInput.click();
      });
      
      // Handle drag and drop
      uploadArea.addEventListener('dragover', (e) => {
        e.preventDefault();
        uploadArea.style.borderColor = 'var(--primary)';
        uploadArea.style.backgroundColor = '#f0f7ff';
      });
      
      uploadArea.addEventListener('dragleave', () => {
        uploadArea.style.borderColor = 'var(--gray-light)';
        uploadArea.style.backgroundColor = 'var(--white)';
      });
      
      uploadArea.addEventListener('drop', (e) => {
        e.preventDefault();
        uploadArea.style.borderColor = 'var(--gray-light)';
        uploadArea.style.backgroundColor = 'var(--white)';
        
        if (e.dataTransfer.files.length) {
          handleFile(e.dataTransfer.files[0]);
        }
      });
      
      // Handle file input change
      fileInput.addEventListener('change', (e) => {
        if (e.target.files.length) {
          handleFile(e.target.files[0]);
        }
      });
      
      // Add export and search/filter functionality
      
      // Invoice Data Export
      const invoiceExportBtn = document.querySelector('#invoiceData .btn-success');
      invoiceExportBtn.addEventListener('click', () => {
        exportTableToExcel('invoiceTable', 'Invoice_Data');
      });
      
      // GST Data Export
      const gstExportBtn = document.querySelector('#gstData .btn-success');
      gstExportBtn.addEventListener('click', () => {
        exportTableToExcel('gstTable', 'GST_Data');
      });
      
      // Invoice Data Search
      const invoiceSearchInput = document.querySelector('#invoiceData input[type="text"]');
      const invoiceSearchBtn = document.querySelector('#invoiceData .btn-secondary');
      invoiceSearchBtn.addEventListener('click', () => {
        const query = invoiceSearchInput.value.toLowerCase();
        const filteredData = invoiceData.filter(item => {
          return Object.values(item).some(val => String(val).toLowerCase().includes(query));
        });
        updateInvoiceTable(filteredData);
      });
      
      // GST Data Filter
      const gstFilterSelect = document.querySelector('#gstData select');
      const gstFilterBtn = document.querySelector('#gstData .btn-secondary');
      gstFilterBtn.addEventListener('click', () => {
        const selectedRate = gstFilterSelect.value;
        let filteredData = invoiceData;
        if (selectedRate !== 'All Tax Rates') {
          filteredData = invoiceData.filter(item => String(item['Tax Rate']) === selectedRate.replace('%', ''));
        }
        updateGSTTable(filteredData);
      });
      
      // Export table to Excel function
      function exportTableToExcel(tableId, filename = '') {
        const table = document.getElementById(tableId);
        const wb = XLSX.utils.table_to_book(table, { sheet: "Sheet1" });
        XLSX.writeFile(wb, filename + ".xlsx");
      }
      
      // Process the Excel file
      function handleFile(file) {
        // Reset status messages
        uploadStatus.classList.remove('hidden');
        uploadSuccess.classList.add('hidden');
        uploadError.classList.add('hidden');
        
        // Check file type
        if (!file.name.endsWith('.xlsx') && !file.name.endsWith('.xls')) {
          uploadStatus.classList.add('hidden');
          uploadError.classList.remove('hidden');
          errorMessage.textContent = 'Invalid file type. Please upload an Excel file (.xlsx or .xls).';
          return;
        }
        
        const reader = new FileReader();
        
        reader.onload = function(e) {
          try {
            const data = new Uint8Array(e.target.result);
            const workbook = XLSX.read(data, { type: 'array' });
            
            // Assume the first sheet contains our data
            const firstSheetName = workbook.SheetNames[0];
            const worksheet = workbook.Sheets[firstSheetName];
            
            // Convert to JSON
            const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
            
            // Process the data
            processExcelData(jsonData);
            
            // Show success message
            uploadStatus.classList.add('hidden');
            uploadSuccess.classList.remove('hidden');
            
            // Display results section
            resultsSection.classList.remove('hidden');
            
          } catch (err) {
            console.error(err);
            uploadStatus.classList.add('hidden');
            uploadError.classList.remove('hidden');
            errorMessage.textContent = 'Error processing file: ' + err.message;
          }
        };
        
        reader.onerror = function() {
          uploadStatus.classList.add('hidden');
          uploadError.classList.remove('hidden');
          errorMessage.textContent = 'Error reading file.';
        };
        
        reader.readAsArrayBuffer(file);
      }
      
      // Process the Excel data
      function processExcelData(data) {
        // Skip header row and process data
        if (data.length < 2) {
          throw new Error('File does not contain enough data');
        }
        
        // Get headers (assuming first row contains headers)
        const headers = data[0];
        
        // Process rows (skip header row)
        invoiceData = [];
        for (let i = 1; i < data.length; i++) {
          const row = data[i];
          if (row.length === 0) continue; // Skip empty rows
          
          // Create an object with values mapped to headers
          const rowData = {};
          for (let j = 0; j < headers.length; j++) {
            rowData[headers[j]] = row[j];
          }
          
          invoiceData.push(rowData);
        }
        
        // Update UI with the processed data
        updateUI(invoiceData);
      }
      
      // Update UI with the processed data
      function updateUI(data) {

        // Update stats
        updateStats(data);
        
        // Update tables
        updateInvoiceTable(data);
        updateGSTTable(data);
        updateMISReports(data);
        
        // Draw charts
        drawCharts(data);
      }
      
      // Update stats cards
      function updateStats(data) {
        // Calculate totals
        let totalInvoiceCount = data.length;
        let totalGSTAmount = data.reduce((sum, item) => {
          const cgst = parseFloat(item['CGST'] || 0);
          const sgst = parseFloat(item['SGST'] || 0);
          const igst = parseFloat(item['IGST'] || 0);
          return sum + cgst + sgst + igst;
        }, 0);
        let totalTaxableAmount = data.reduce((sum, item) => sum + parseFloat(item['Taxable Value'] || 0), 0);
        let totalGrossAmount = data.reduce((sum, item) => sum + parseFloat(item['Gross Amount'] || 0), 0);
        
        // Update UI
        totalInvoices.textContent = totalInvoiceCount;
        totalGST.textContent = '₹' + totalGSTAmount.toLocaleString('en-IN', { maximumFractionDigits: 2 });
        totalTaxable.textContent = '₹' + totalTaxableAmount.toLocaleString('en-IN', { maximumFractionDigits: 2 });
        totalGross.textContent = '₹' + totalGrossAmount.toLocaleString('en-IN', { maximumFractionDigits: 2 });
      }
      
      // Update invoice table
      function updateInvoiceTable(data) {
        // Clear existing rows
        invoiceTable.innerHTML = '';

        // Populate table with new data
        data.forEach((item, index) => {
          const row = invoiceTable.insertRow();

          // Insert cells
          const snoCell = row.insertCell(0);
          const dateCell = row.insertCell(1);
          const partyNameCell = row.insertCell(2);
          const gstinCell = row.insertCell(3);
          const invoiceNoCell = row.insertCell(4);
          const commodityCell = row.insertCell(5);
          const qtyCell = row.insertCell(6);
          const taxableValueCell = row.insertCell(7);
          const gstCell = row.insertCell(8);
          const grossAmountCell = row.insertCell(9);
          const actionsCell = row.insertCell(10);

          // Set cell values
          snoCell.textContent = index + 1;
          dateCell.textContent = item['Date'] || '-';
          partyNameCell.textContent = item['Party Name'] || '-';
          gstinCell.textContent = item['GSTIN'] || '-';
          invoiceNoCell.textContent = item['Invoice No.'] || '-';
          commodityCell.textContent = item['Commodity'] || '-';
          qtyCell.textContent = item['Qty'] || '-';
          taxableValueCell.textContent = '₹' + (parseFloat(item['Taxable Value'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
          gstCell.textContent = '₹' + (parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
          grossAmountCell.textContent = '₹' + (parseFloat(item['Gross Amount'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });

          // Actions button
          const viewBtn = document.createElement('button');
          viewBtn.className = 'btn btn-secondary';
          viewBtn.textContent = 'View';
          viewBtn.addEventListener('click', () => {
            alert('View details for Invoice No.: ' + item['Invoice No.']);
          });
          actionsCell.appendChild(viewBtn);
        });
      }

      // Update GST table
      function updateGSTTable(data) {
        // Clear existing rows
        gstTable.innerHTML = '';

        // Populate table with new data
        data.forEach((item, index) => {
          const row = gstTable.insertRow();

          // Insert cells
          const snoCell = row.insertCell(0);
          const gstinCell = row.insertCell(1);
          const partyNameCell = row.insertCell(2);
          const invoiceNoCell = row.insertCell(3);
          const dateCell = row.insertCell(4);
          const hsnCodeCell = row.insertCell(5);
          const taxRateCell = row.insertCell(6);
          const taxableValueCell = row.insertCell(7);
          const cgstCell = row.insertCell(8);
          const sgstCell = row.insertCell(9);
          const igstCell = row.insertCell(10);
          const totalGstCell = row.insertCell(11);

          // Set cell values
          snoCell.textContent = index + 1;
          gstinCell.textContent = item['GSTIN'] || '-';
          partyNameCell.textContent = item['Party Name'] || '-';
          invoiceNoCell.textContent = item['Invoice No.'] || '-';
          dateCell.textContent = item['Date'] || '-';
          hsnCodeCell.textContent = item['HSN Code'] || '-';
          taxRateCell.textContent = item['Tax Rate'] || '-';
          taxableValueCell.textContent = '₹' + (parseFloat(item['Taxable Value'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
          cgstCell.textContent = '₹' + (parseFloat(item['CGST'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
          sgstCell.textContent = '₹' + (parseFloat(item['SGST'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
          igstCell.textContent = '₹' + (parseFloat(item['IGST'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
          totalGstCell.textContent = '₹' + (parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0)).toLocaleString('en-IN', { maximumFractionDigits: 2 });
        });
      }

// ... (existing code)

// Update MIS reports
function updateMISReports(data) {
  // Clear existing rows
  hsnTable.innerHTML = '';
  taxRateTable.innerHTML = '';
  partyTable.innerHTML = '';

  // HSN-wise summary
  const hsnSummary = {};
  data.forEach(item => {
    const hsnCode = item['HSN Code'] || 'N/A';
    if (!hsnSummary[hsnCode]) {
      hsnSummary[hsnCode] = {
        description: item['Description'] || '-',
        totalQty: 0,
        totalValue: 0,
        totalGST: 0
      };
    }
    hsnSummary[hsnCode].totalQty += parseFloat(item['Qty'] || 0);
    hsnSummary[hsnCode].totalValue += parseFloat(item['Taxable Value'] || 0);
    hsnSummary[hsnCode].totalGST += parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0);
  });

  Object.keys(hsnSummary).forEach((hsnCode) => {
    const row = hsnTable.insertRow();
    const hsnCodeCell = row.insertCell(0);
    const descriptionCell = row.insertCell(1);
    const totalQtyCell = row.insertCell(2);
    const totalValueCell = row.insertCell(3);
    const totalGSTCell = row.insertCell(4);

    hsnCodeCell.textContent = hsnCode;
    descriptionCell.textContent = hsnSummary[hsnCode].description;
    totalQtyCell.textContent = hsnSummary[hsnCode].totalQty.toLocaleString(undefined, {maximumFractionDigits: 2});
    totalValueCell.textContent = '₹' + hsnSummary[hsnCode].totalValue.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    totalGSTCell.textContent = '₹' + hsnSummary[hsnCode].totalGST.toLocaleString('en-IN', { maximumFractionDigits: 2 });
  });

  // Tax rate summary
  const taxRateSummary = {};
  data.forEach(item => {
    const taxRate = item['Tax Rate'] || 'N/A';
    if (!taxRateSummary[taxRate]) {
      taxRateSummary[taxRate] = {
        taxableValue: 0,
        cgst: 0,
        sgst: 0,
        igst: 0,
        totalTax: 0
      };
    }
    taxRateSummary[taxRate].taxableValue += parseFloat(item['Taxable Value'] || 0);
    taxRateSummary[taxRate].cgst += parseFloat(item['CGST'] || 0);
    taxRateSummary[taxRate].sgst += parseFloat(item['SGST'] || 0);
    taxRateSummary[taxRate].igst += parseFloat(item['IGST'] || 0);
  });

  // Compute totalTax for each taxRate
  Object.keys(taxRateSummary).forEach(rate => {
    const summary = taxRateSummary[rate];
    summary.totalTax = summary.cgst + summary.sgst + summary.igst;
  });

  Object.keys(taxRateSummary).forEach((rate) => {
    const row = taxRateTable.insertRow();
    const taxRateCell = row.insertCell(0);
    const taxableCell = row.insertCell(1);
    const cgstCell = row.insertCell(2);
    const sgstCell = row.insertCell(3);
    const igstCell = row.insertCell(4);
    const totalTaxCell = row.insertCell(5);

    taxRateCell.textContent = rate;
    taxableCell.textContent = '₹' + taxRateSummary[rate].taxableValue.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    cgstCell.textContent = '₹' + taxRateSummary[rate].cgst.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    sgstCell.textContent = '₹' + taxRateSummary[rate].sgst.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    igstCell.textContent = '₹' + taxRateSummary[rate].igst.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    totalTaxCell.textContent = '₹' + taxRateSummary[rate].totalTax.toLocaleString('en-IN', { maximumFractionDigits: 2 });
  });

  // Party-wise summary
  const partySummary = {};
  data.forEach(item => {
    const partyName = item['Party Name'] || 'N/A';
    const gstin = item['GSTIN'] || 'N/A';
    const key = gstin + '|' + partyName; // Unique key to avoid duplicate parties with same name

    if (!partySummary[key]) {
      partySummary[key] = {
        partyName: partyName,
        gstin: gstin,
        invoiceCount: 0,
        totalTaxable: 0,
        totalGST: 0,
        totalGross: 0
      };
    }

    partySummary[key].invoiceCount++;
    partySummary[key].totalTaxable += parseFloat(item['Taxable Value'] || 0);
    partySummary[key].totalGST += parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0);
    partySummary[key].totalGross += parseFloat(item['Gross Amount'] || 0);
  });

  Object.keys(partySummary).forEach(key => {
    const summary = partySummary[key];
    const row = partyTable.insertRow();
    const partyNameCell = row.insertCell(0);
    const gstinCell = row.insertCell(1);
    const invoiceCountCell = row.insertCell(2);
    const totalTaxableCell = row.insertCell(3);
    const totalGSTCell = row.insertCell(4);
    const totalGrossCell = row.insertCell(5);

    partyNameCell.textContent = summary.partyName;
    gstinCell.textContent = summary.gstin;
    invoiceCountCell.textContent = summary.invoiceCount;
    totalTaxableCell.textContent = '₹' + summary.totalTaxable.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    totalGSTCell.textContent = '₹' + summary.totalGST.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    totalGrossCell.textContent = '₹' + summary.totalGross.toLocaleString('en-IN', { maximumFractionDigits: 2 });
  });
}

// Draw Charts
let gstDistributionChartInstance = null;
let monthlyTrendChartInstance = null;

function drawCharts(data) {
  // Prepare data for GST Distribution chart (Pie chart by Tax Rate)

  // Sum taxable value by tax rate
  const gstDistData = {};
  data.forEach(item => {
    const taxRateKey = item['Tax Rate'] || 'N/A';
    if (!gstDistData[taxRateKey]) {
      gstDistData[taxRateKey] = 0;
    }
    gstDistData[taxRateKey] += parseFloat(item['Taxable Value'] || 0);
  });

  const gstDistLabels = Object.keys(gstDistData);
  const gstDistValues = gstDistLabels.map(rate => gstDistData[rate]);

  const gstDistColors = [
    '#4f46e5', // primary
    '#12b886', // teal
    '#f59e0b', // yellow
    '#ef4444', // red
    '#3b82f6', // blue
    '#db2777', // pink
  ];

  // Prepare data for monthly trend chart (Bar chart)

  // Group by month-year
  const monthlyData = {};

  data.forEach(item => {
    let dateStr = item['Date'];
    if (!dateStr) return;

    // Try to convert Excel date values and strings to Date object
    let d = parseDate(dateStr);
    if (!d) return;

    let monthYear = `${d.getFullYear()}-${('0' + (d.getMonth() + 1)).slice(-2)}`;

    if (!monthlyData[monthYear]) {
      monthlyData[monthYear] = {
        totalTaxable: 0,
        totalGST: 0,
        totalGross: 0
      };
    }

    monthlyData[monthYear].totalTaxable += parseFloat(item['Taxable Value'] || 0);
    monthlyData[monthYear].totalGST += (parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0));
    monthlyData[monthYear].totalGross += parseFloat(item['Gross Amount'] || 0);
  });

  // Sort the months ascending
  const sortedMonths = Object.keys(monthlyData).sort();

  const monthlyTaxable = [];
  const monthlyGST = [];
  const monthlyGross = [];

  sortedMonths.forEach(month => {
    monthlyTaxable.push(monthlyData[month].totalTaxable);
    monthlyGST.push(monthlyData[month].totalGST);
    monthlyGross.push(monthlyData[month].totalGross);
  });

  // Format months to "MMM YYYY"
  const formattedMonths = sortedMonths.map(m => {
    const parts = m.split('-');
    const year = parts[0];
    const month = parts[1];
    return new Date(year, parseInt(month) -1).toLocaleString('en-US', { month: 'short', year: 'numeric' });
  });

  // Destroy existing charts if exist
  if (gstDistributionChartInstance) {
    gstDistributionChartInstance.destroy();
  }

  if (monthlyTrendChartInstance) {
    monthlyTrendChartInstance.destroy();
  }

  // Create GST Distribution Pie Chart
  const ctx1 = document.getElementById('gstDistributionChart').getContext('2d');
  gstDistributionChartInstance = new Chart(ctx1, {
    type: 'pie',
    data: {
      labels: gstDistLabels,
      datasets: [{
        data: gstDistValues,
        backgroundColor: gstDistColors.slice(0, gstDistLabels.length),
        borderWidth: 1,
      }]
    },
    options: {
      responsive: true,
      plugins: {
        legend: {
          position: 'bottom',
        },
        tooltip: {
          callbacks: {
            label: function(context) {
              let label = context.label || '';
              let value = context.parsed || 0;
              let percent = 0;
              const sum = context.chart._metasets[context.datasetIndex].total;
              if (sum) {
                percent = ((value / sum) * 100).toFixed(2);
              }
              return `${label}: ₹${value.toLocaleString('en-IN', {maximumFractionDigits:2})} (${percent}%)`;
            }
          }
        }
      }
    }
  });

  // Create Monthly Trend Bar Chart
  const ctx2 = document.getElementById('monthlyTrendChart').getContext('2d');
  monthlyTrendChartInstance = new Chart(ctx2, {
    type: 'bar',
    data: {
      labels: formattedMonths,
      datasets: [
        {
          label: 'Taxable Value',
          data: monthlyTaxable,
          backgroundColor: '#4f46e5'
        },
        {
          label: 'GST',
          data: monthlyGST,
          backgroundColor: '#ef4444'
        },
        {
          label: 'Gross Amount',
          data: monthlyGross,
          backgroundColor: '#f97316'
        }
      ]
    },
    options: {
      responsive: true,
      scales: {
        y: {
          beginAtZero: true,
          ticks: {
            // Use Indian Rupee format
            callback: function(value) {
              return '₹' + value.toLocaleString('en-IN');
            }
          }
        }
      },
      plugins: {
        legend: {
          position: 'bottom',
        },
        tooltip: {
          callbacks: {
            label: function(context) {
              let val = context.parsed.y || 0;
              return context.dataset.label + ': ₹' + val.toLocaleString('en-IN', {maximumFractionDigits:2});
            }
          }
        }
      }
    }
  });
}

// Utility - Parse date string or Excel date to JS Date
function parseDate(dateVal) {
  // If it's a number, treat as Excel date (days since 1900-01-01)
  if (typeof dateVal === 'number') {
    // Excel incorrectly assumes 1900 is a leap year, so subtract 1 for days after 28 Feb 1900
    const offset = (dateVal > 59) ? dateVal - 1 : dateVal;
    const excelEpoch = new Date(1899, 11, 30); // Dec 30, 1899
    const date = new Date(excelEpoch.getTime() + offset * 24 * 3600 * 1000);
    return date;
  }

  // If already Date object
  if (dateVal instanceof Date) return dateVal;

  // Otherwise, try to parse string
  const parsed = new Date(dateVal);
  if (!isNaN(parsed)) {
    return parsed;
  }

  // Could not parse
  return null;
}

// GST Summary Export button (export GST summary related data)
const gstSummaryExportBtn = document.querySelector('.card .btn-secondary i.fa-download').parentElement;

gstSummaryExportBtn.addEventListener('click', () => {
  // Prepare summary data for export (most relevant is tax rate summary)
  const wb = XLSX.utils.book_new();

  // GST Distribution sheet (tax rate summary)
  const gstDistributionData = [['Tax Rate', 'Taxable Value']];

  for (const [rate, obj] of Object.entries(getGSTDistributionData(invoiceData))) {
    gstDistributionData.push([rate, obj.toFixed(2)]);
  }

  const ws1 = XLSX.utils.aoa_to_sheet(gstDistributionData);
  XLSX.utils.book_append_sheet(wb, ws1, 'GST Distribution');

  // Monthly Trends sheet
  const monthlyTrend = getMonthlyTrendData(invoiceData);
  const ws2 = XLSX.utils.aoa_to_sheet(monthlyTrend);
  XLSX.utils.book_append_sheet(wb, ws2, 'Monthly Trend');

  XLSX.writeFile(wb, 'GST_Summary.xlsx');
});

// Helper: get GST Distribution Data for export
function getGSTDistributionData(data) {
  const gstDist = {};
  data.forEach(item => {
    const taxRateKey = item['Tax Rate'] || 'N/A';
    if (!gstDist[taxRateKey]) gstDist[taxRateKey] = 0;
    gstDist[taxRateKey] += parseFloat(item['Taxable Value'] || 0);
  });
  return gstDist;
}

// Helper: Prepare monthly trend data for export (array of arrays)
function getMonthlyTrendData(data) {
  // Group by month-year
  const monthlyData = {};
  data.forEach(item => {
    let dateStr = item['Date'];
    if (!dateStr) return;

    let d = parseDate(dateStr);
    if (!d) return;

    let monthYear = `${d.getFullYear()}-${('0' + (d.getMonth() + 1)).slice(-2)}`;
    if (!monthlyData[monthYear]) {
      monthlyData[monthYear] = {
        totalTaxable: 0,
        totalGST: 0,
        totalGross: 0
      };
    }

    monthlyData[monthYear].totalTaxable += parseFloat(item['Taxable Value'] || 0);
    monthlyData[monthYear].totalGST += (parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0));
    monthlyData[monthYear].totalGross += parseFloat(item['Gross Amount'] || 0);
  });

  const sortedMonths = Object.keys(monthlyData).sort();

  const result = [['Month-Year', 'Taxable Value', 'GST', 'Gross Amount']];

  sortedMonths.forEach(month => {
    const d = new Date(month.split('-')[0], parseInt(month.split('-')[1]) - 1);
    const label = d.toLocaleString('en-US', { month: 'short', year: 'numeric' });
    const v = monthlyData[month];
    result.push([
      label,
      v.totalTaxable.toFixed(2),
      v.totalGST.toFixed(2),
      v.totalGross.toFixed(2)
    ]);
  });

  return result;
}
// Continue from where you left off...

// Update MIS reports - fills HSN, Tax Rate, and Party summary tables
function updateMISReports(data) {
  hsnTable.innerHTML = '';
  taxRateTable.innerHTML = '';
  partyTable.innerHTML = '';

  // HSN-wise summary aggregation
  const hsnSummary = {};
  data.forEach(item => {
    const hsnCode = item['HSN Code'] || 'N/A';
    if (!hsnSummary[hsnCode]) {
      hsnSummary[hsnCode] = {
        description: item['Description'] || '-',
        totalQty: 0,
        totalValue: 0,
        totalGST: 0
      };
    }
    hsnSummary[hsnCode].totalQty += parseFloat(item['Qty'] || 0);
    hsnSummary[hsnCode].totalValue += parseFloat(item['Taxable Value'] || 0);
    hsnSummary[hsnCode].totalGST += (parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0));
  });

  Object.entries(hsnSummary).forEach(([hsnCode, summary]) => {
    const row = hsnTable.insertRow();
    row.insertCell(0).textContent = hsnCode;
    row.insertCell(1).textContent = summary.description;
    row.insertCell(2).textContent = summary.totalQty.toLocaleString(undefined, { maximumFractionDigits: 2 });
    row.insertCell(3).textContent = '₹' + summary.totalValue.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(4).textContent = '₹' + summary.totalGST.toLocaleString('en-IN', { maximumFractionDigits: 2 });
  });

  // Tax Rate summary aggregation
  const taxRateSummary = {};
  data.forEach(item => {
    const taxRate = item['Tax Rate'] || 'N/A';
    if (!taxRateSummary[taxRate]) {
      taxRateSummary[taxRate] = {
        taxableValue: 0,
        cgst: 0,
        sgst: 0,
        igst: 0,
      };
    }
    taxRateSummary[taxRate].taxableValue += parseFloat(item['Taxable Value'] || 0);
    taxRateSummary[taxRate].cgst += parseFloat(item['CGST'] || 0);
    taxRateSummary[taxRate].sgst += parseFloat(item['SGST'] || 0);
    taxRateSummary[taxRate].igst += parseFloat(item['IGST'] || 0);
  });

  Object.entries(taxRateSummary).forEach(([rate, summary]) => {
    const totalTax = summary.cgst + summary.sgst + summary.igst;
    const row = taxRateTable.insertRow();
    row.insertCell(0).textContent = rate;
    row.insertCell(1).textContent = '₹' + summary.taxableValue.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(2).textContent = '₹' + summary.cgst.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(3).textContent = '₹' + summary.sgst.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(4).textContent = '₹' + summary.igst.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(5).textContent = '₹' + totalTax.toLocaleString('en-IN', { maximumFractionDigits: 2 });
  });

  // Party-wise summary aggregation
  const partySummary = {};
  data.forEach(item => {
    const partyName = item['Party Name'] || 'N/A';
    const gstin = item['GSTIN'] || 'N/A';
    const key = gstin + '|' + partyName;

    if (!partySummary[key]) {
      partySummary[key] = {
        partyName,
        gstin,
        invoiceCount: 0,
        totalTaxable: 0,
        totalGST: 0,
        totalGross: 0
      };
    }

    partySummary[key].invoiceCount++;
    partySummary[key].totalTaxable += parseFloat(item['Taxable Value'] || 0);
    partySummary[key].totalGST += parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0);
    partySummary[key].totalGross += parseFloat(item['Gross Amount'] || 0);
  });

  Object.values(partySummary).forEach(summary => {
    const row = partyTable.insertRow();
    row.insertCell(0).textContent = summary.partyName;
    row.insertCell(1).textContent = summary.gstin;
    row.insertCell(2).textContent = summary.invoiceCount;
    row.insertCell(3).textContent = '₹' + summary.totalTaxable.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(4).textContent = '₹' + summary.totalGST.toLocaleString('en-IN', { maximumFractionDigits: 2 });
    row.insertCell(5).textContent = '₹' + summary.totalGross.toLocaleString('en-IN', { maximumFractionDigits: 2 });
  });
}

// Chart instances
let gstDistributionChartInstance = null;
let monthlyTrendChartInstance = null;

function drawCharts(data) {
  // GST Distribution by Tax Rate (Pie Chart)
  const gstDist = {};
  data.forEach(item => {
    const rate = item['Tax Rate'] || 'N/A';
    gstDist[rate] = (gstDist[rate] || 0) + parseFloat(item['Taxable Value'] || 0);
  });

  const labels = Object.keys(gstDist);
  const values = labels.map(l => gstDist[l]);

  const colors = ['#4f46e5', '#12b886', '#f59e0b', '#ef4444', '#3b82f6', '#db2777'];

  // Monthly Trend data (Bar Chart)
  const monthly = {};
  data.forEach(item => {
    const dt = parseDate(item['Date']);
    if (!dt) return;
    const key = `${dt.getFullYear()}-${String(dt.getMonth() + 1).padStart(2,'0')}`;
    if (!monthly[key]) monthly[key] = { taxable: 0, gst: 0, gross: 0 };

    monthly[key].taxable += parseFloat(item['Taxable Value'] || 0);
    monthly[key].gst += parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0);
    monthly[key].gross += parseFloat(item['Gross Amount'] || 0);
  });

  const monthsSorted = Object.keys(monthly).sort();
  const monthLabels = monthsSorted.map(m => {
    const [year, mon] = m.split('-');
    const date = new Date(year, mon - 1);
    return date.toLocaleString('en-US', { month: 'short', year: 'numeric' });
  });

  const taxableVals = monthsSorted.map(m => monthly[m].taxable);
  const gstVals = monthsSorted.map(m => monthly[m].gst);
  const grossVals = monthsSorted.map(m => monthly[m].gross);

  if (gstDistributionChartInstance) gstDistributionChartInstance.destroy();
  if (monthlyTrendChartInstance) monthlyTrendChartInstance.destroy();

  const ctxGSTDist = document.getElementById('gstDistributionChart').getContext('2d');
  gstDistributionChartInstance = new Chart(ctxGSTDist, {
    type: 'pie',
    data: {
      labels,
      datasets: [{
        data: values,
        backgroundColor: colors.slice(0, labels.length),
        borderWidth: 1
      }]
    },
    options: {
      responsive: true,
      plugins: {
        legend: { position: 'bottom' },
        tooltip: {
          callbacks: {
            label: ctx => {
              const label = ctx.label || '';
              const val = ctx.parsed || 0;
              const total = ctx.dataset.data.reduce((a,b) => a+b,0);
              const percent = total ? ((val/total)*100).toFixed(2) : 0;
              return `${label}: ₹${val.toLocaleString('en-IN', {maximumFractionDigits: 2})} (${percent}%)`;
            }
          }
        }
      }
    }
  });

  const ctxMonthly = document.getElementById('monthlyTrendChart').getContext('2d');
  monthlyTrendChartInstance = new Chart(ctxMonthly, {
    type: 'bar',
    data: {
      labels: monthLabels,
      datasets: [
        { label: 'Taxable Value', data: taxableVals, backgroundColor: '#4f46e5' },
        { label: 'GST', data: gstVals, backgroundColor: '#ef4444' },
        { label: 'Gross Amount', data: grossVals, backgroundColor: '#f97316' }
      ]
    },
    options: {
      responsive: true,
      scales: {
        y: {
          beginAtZero: true,
          ticks: {
            callback: val => '₹' + val.toLocaleString('en-IN')
          }
        }
      },
      plugins: {
        legend: { position: 'bottom' },
        tooltip: {
          callbacks: {
            label: ctx => `${ctx.dataset.label}: ₹${ctx.parsed.y.toLocaleString('en-IN', { maximumFractionDigits: 2 })}`
          }
        }
      }
    }
  });
}

function parseDate(dateVal) {
  if (typeof dateVal === 'number') {
    const offset = (dateVal > 59) ? dateVal - 1 : dateVal;  // Excel leap year bug fix
    const epoch = new Date(Date.UTC(1899, 11, 30));
    return new Date(epoch.getTime() + offset * 86400 * 1000);
  }
  if (dateVal instanceof Date) return dateVal;
  const parsed = new Date(dateVal);
  return isNaN(parsed) ? null : parsed;
}

// GST Summary Export button
const gstSummaryExportBtn = document.querySelector('.card .btn-secondary i.fa-download').parentElement;
gstSummaryExportBtn.addEventListener('click', () => {
  const wb = XLSX.utils.book_new();

  // GST Distribution
  const gstDistData = [['Tax Rate', 'Taxable Value']];
  const gstDist = {};
  invoiceData.forEach(item => {
    const rate = item['Tax Rate'] || 'N/A';
    gstDist[rate] = (gstDist[rate] || 0) + parseFloat(item['Taxable Value'] || 0);
  });
  Object.entries(gstDist).forEach(([rate, val]) => gstDistData.push([rate, val.toFixed(2)]));
  XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(gstDistData), 'GST Distribution');

  // Monthly Trend
  const monthly = {};
  invoiceData.forEach(item => {
    const dt = parseDate(item['Date']);
    if (!dt) return;
    const key = `${dt.getFullYear()}-${String(dt.getMonth() + 1).padStart(2, '0')}`;
    if (!monthly[key]) monthly[key] = { taxable: 0, gst: 0, gross: 0 };
    monthly[key].taxable += parseFloat(item['Taxable Value'] || 0);
    monthly[key].gst += parseFloat(item['CGST'] || 0) + parseFloat(item['SGST'] || 0) + parseFloat(item['IGST'] || 0);
    monthly[key].gross += parseFloat(item['Gross Amount'] || 0);
  });
  const monthsSorted = Object.keys(monthly).sort();
  const monthlyData = [['Month-Year', 'Taxable Value', 'GST', 'Gross Amount']];
  monthsSorted.forEach(m => {
    const [year, mon] = m.split('-');
    const dateLabel = new Date(year, mon - 1).toLocaleString('en-US', { month: 'short', year: 'numeric' });
    const vals = monthly[m];
    monthlyData.push([
      dateLabel,
      vals.taxable.toFixed(2),
      vals.gst.toFixed(2),
      vals.gross.toFixed(2)
    ]);
  });
  XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(monthlyData), 'Monthly Trend');

  XLSX.writeFile(wb, 'GST_Summary.xlsx');
});