import pandas as pd
import numpy as np

xlsx = pd.ExcelFile('financial_file.xlsx')
# Common sheets: Financials_mensal, KPI_Real, Análise_Resultado_*

# Find 2025 columns (format: 202501, 202502, etc.)
df = pd.read_excel(xlsx, sheet_name='Financials_mensal', header=None)

Annual Savings = Current_Cost × Reduction_%
Net Year 1 = Savings - Implementation_Cost
Payback (months) = Implementation_Cost / Savings × 12
NPV = -Impl_Cost + Savings/(1+r) + Savings/(1+r)^2 + Savings/(1+r)^3
New EBITDA Margin = (Current_EBITDA + Savings) / Revenue

1. Performance Summary
   - Overview paragraph
   - Key metrics table (Revenue, Gross Profit, EBITDA, Net Income with margins and variances)

2. Critical Issues Identified
   2.1 Operating Expenses Analysis
       - Breakdown of problem areas
       - Specific cost categories with YoY changes
   2.2 Revenue Trends
       - Growth/stagnation analysis
   2.3 Margin Compression
       - EBITDA and Net margin trends

3. Recommended Adjustments
   3.1 Immediate Actions (0-90 days)
       - Numbered list of priority actions with context
   3.2 Medium-Term Actions (90-180 days)
       - Strategic initiatives

4. AI-Driven Cost Reduction Scenarios
   4.1 Current State Baseline
       - Cost categories table with annual amounts, % revenue, AI opportunity rating
   4.2 Scenario Comparison
       - Three-column comparison table (Conservative/Moderate/Aggressive)
       - Include: Annual Savings, Implementation Cost, Year 1 Net Benefit
       - EBITDA Margin Impact, Payback Period, 3-Year NPV

5. Key AI Initiatives by Impact
   5.1 Highest ROI (Implement First)
       - Top 3 initiatives with expected outcomes
   5.2 Medium-Term Wins
       - Additional opportunities

6. Recommended Implementation Path
   - Phased approach recommendation
   - Month-by-month breakdown with investment and savings

7. Target KPIs to Track
   - Table with Current, Target, and Gap columns

8. Conclusion
   - Summary of findings and priority actions
   - Reference to attached Excel model

const { Document, Packer, Paragraph, TextRun, Table, TableRow, TableCell,
        HeadingLevel, AlignmentType, BorderStyle, WidthType, ShadingType,
        VerticalAlign, LevelFormat } = require('docx');
const fs = require('fs');

// Professional styling
const tableBorder = { style: BorderStyle.SINGLE, size: 1, color: "CCCCCC" };
const cellBorders = { top: tableBorder, bottom: tableBorder, left: tableBorder, right: tableBorder };
const headerShading = { fill: "D5E8F0", type: ShadingType.CLEAR };

// Helper for metric tables
function createMetricTable(headers, rows) {
  const colWidth = Math.floor(9360 / headers.length);
  return new Table({
    columnWidths: headers.map(() => colWidth),
    rows: [
      new TableRow({
        tableHeader: true,
        children: headers.map(h => new TableCell({
          borders: cellBorders,
          width: { size: colWidth, type: WidthType.DXA },
          shading: headerShading,
          children: [new Paragraph({
            alignment: AlignmentType.CENTER,
            children: [new TextRun({ text: h, bold: true, size: 22 })]
          })]
        }))
      }),
      ...rows.map(row => new TableRow({
        children: row.map((cell, i) => new TableCell({
          borders: cellBorders,
          width: { size: colWidth, type: WidthType.DXA },
          children: [new Paragraph({
            alignment: i === 0 ? AlignmentType.LEFT : AlignmentType.CENTER,
            children: [new TextRun({ text: String(cell), size: 22 })]
          })]
        }))
      }))
    ]
  });
}

const doc = new Document({
  styles: {
    default: { document: { run: { font: "Arial", size: 24 } } },
    paragraphStyles: [
      { id: "Title", name: "Title", basedOn: "Normal",
        run: { size: 48, bold: true, color: "000000", font: "Arial" },
        paragraph: { spacing: { before: 0, after: 240 }, alignment: AlignmentType.CENTER } },
      { id: "Heading1", name: "Heading 1", basedOn: "Normal", next: "Normal", quickFormat: true,
        run: { size: 32, bold: true, color: "000000", font: "Arial" },
        paragraph: { spacing: { before: 360, after: 120 }, outlineLevel: 0 } },
      { id: "Heading2", name: "Heading 2", basedOn: "Normal", next: "Normal", quickFormat: true,
        run: { size: 26, bold: true, color: "333333", font: "Arial" },
        paragraph: { spacing: { before: 240, after: 120 }, outlineLevel: 1 } }
    ]
  },
  sections: [{
    properties: { page: { margin: { top: 1440, right: 1440, bottom: 1440, left: 1440 } } },
    children: [
      // Title
      new Paragraph({ heading: HeadingLevel.TITLE, 
        children: [new TextRun("Financial Analysis Report")] }),
      new Paragraph({ alignment: AlignmentType.CENTER,
        children: [new TextRun({ text: "B2B SaaS Company - AI-Driven Cost Reduction Scenarios", size: 24, color: "666666" })] }),
      new Paragraph({ alignment: AlignmentType.CENTER, spacing: { after: 480 },
        children: [new TextRun({ text: "December 2025", size: 22, color: "666666" })] }),
      
      // 1. Performance Summary
      new Paragraph({ heading: HeadingLevel.HEADING_1,
        children: [new TextRun("1. Performance Summary")] }),
      new Paragraph({ spacing: { after: 240 },
        children: [new TextRun("Analysis of the financial data reveals...")] }),
      
      // Performance metrics table
      createMetricTable(
        ["Metric", "Value", "Margin", "vs Budget", "vs Prior Year"],
        [
          ["Net Revenue", "R$ 2.53M", "100%", "-2.1%", "-2.1%"],
          ["Gross Profit", "R$ 2.18M", "86.2%", "-2.4%", "-5.6%"],
          ["EBITDA", "R$ 1.39M", "54.9%", "-9.1%", "-12.7%"],
          ["Net Income", "R$ 1.13M", "44.8%", "-8.9%", "-19.9%"]
        ]
      ),
      
      // Continue with remaining sections...
      // 2. Critical Issues, 3. Recommendations, 4. Scenarios, etc.
    ]
  }]
});

Packer.toBuffer(doc).then(buffer => {
  fs.writeFileSync("company_financial_analysis_report.docx", buffer);
});