import { Workbook, style, Colors, NumFmt } from './src/index.js';

const wb = new Workbook();
wb.coreProperties = { title: 'Q4 Report', creator: 'Alice', language: 'en-US' };
wb.extendedProperties = { company: 'Acme Corp', appVersion: '1.0' };

const ws = wb.addSheet('Sales Data');

// Header row
ws.writeRow(1, 1, ['Product', 'Q1', 'Q2', 'Q3', 'Q4', 'Total']);
for (let c = 1; c <= 6; c++) {
  ws.setStyle(1, c, style().bold().bg(Colors.ExcelBlue).fontColor(Colors.White).center().build());
}

// Data rows
ws.writeArray(2, 1, [
  ['Widget A', 1200, 1350, 1100, 1500],
  ['Widget B',  800,  950,  870, 1020],
  ['Gadget X', 2100, 1980, 2250, 2400],
]);

// SUM formulas
for (let r = 2; r <= 4; r++) {
  ws.setFormula(r, 6, `SUM(B${r}:E${r})`);
  ws.setStyle(r, 6, style().bold().build());
}

ws.freeze(1, 0); // freeze first row

// Output — compression level 6 by default (80–85% smaller than STORE)
await wb.writeFile('./report.xlsx');          // Node.js
await wb.download('report.xlsx');             // Browser
const bytes = await wb.build();              // Uint8Array (any runtime)
const b64   = await wb.buildBase64();        // base64 string

// Node.js / Deno / Bun
const wb = await Workbook.fromFile('./existing.xlsx');

// Universal (Uint8Array)
const wb = await Workbook.fromBytes(uint8Array);

// Browser (File / Blob input element)
const wb = await Workbook.fromBlob(fileInputElement.files[0]);

// base64 string (e.g. from an API or email attachment)
const wb = await Workbook.fromBase64(base64String);

console.log(wb.getSheetNames());         // ['Sheet1', 'Summary', 'Config']

const ws = wb.getSheet('Summary');
const cell = ws.getCell(3, 2);           // row 3, col 2
console.log(cell.value);                 // 'Q4 Revenue'
console.log(cell.formula);              // 'SUM(B10:B20)'
console.log(cell.style?.font?.bold);    // true

const wb = await Workbook.fromFile('./report.xlsx');
const ws = wb.getSheet('Sales');

// Make changes
ws.setValue(5, 3, 99000);
ws.setStyle(5, 3, style().bg(Colors.LightGreen).build());
ws.writeRow(20, 1, ['TOTAL', '', '=SUM(C2:C19)']);

// Mark the sheet dirty — it will be re-serialised on write.
// Sheets NOT marked dirty are written back byte-for-byte from the original.
wb.markDirty('Sales');

// Patch properties without re-serialising any sheets
wb.coreProperties.title = 'Updated Report';
wb.setCustomProperty('Status', { type: 'string', value: 'Approved' });

await wb.writeFile('./report_updated.xlsx');

const wb = new Workbook();
wb.compressionLevel = 6;  // 0–9, default 6

import { buildZip } from './src/utils/zip.js';

const zip = buildZip([
  { name: 'xl/worksheets/sheet1.xml', data: xmlBytes },         // uses global level
  { name: 'xl/media/image1.png',      data: pngBytes, level: 0 }, // forced STORE
  { name: 'xl/styles.xml',            data: stylesBytes, level: 9 }, // max compression
], { level: 6 });

wb.coreProperties = {
  title:          'Annual Report 2024',
  subject:        'Financial Summary',
  creator:        'Finance Team',
  keywords:       'excel quarterly finance',
  description:    'Auto-generated from ERP export',
  lastModifiedBy: 'Alice',
  revision:       '3',
  language:       'en-US',
  category:       'Finance',
  contentStatus:  'Final',
  created:        new Date('2024-01-01'),
  // modified is always set to current time on write
};

wb.extendedProperties = {
  application:       'ExcelForge',
  appVersion:        '1.0.0',
  company:           'Acme Corp',
  manager:           'Bob Smith',
  hyperlinkBase:     'https://intranet.acme.com/',
  docSecurity:       0,
  linksUpToDate:     true,
  // These are computed automatically on write:
  // titlesOfParts, headingPairs
};

// Set custom properties at workbook level
wb.customProperties = [
  { name: 'ProjectCode',  value: { type: 'string',  value: 'PRJ-2024-007' } },
  { name: 'Revision',     value: { type: 'int',     value: 5             } },
  { name: 'Budget',       value: { type: 'decimal', value: 125000.00     } },
  { name: 'IsApproved',   value: { type: 'bool',    value: true          } },
  { name: 'ReviewDate',   value: { type: 'date',    value: new Date()    } },
];

// Or use the helper methods
wb.setCustomProperty('Status', { type: 'string', value: 'In Review' });
wb.setCustomProperty('Score',  { type: 'decimal', value: 9.7 });
wb.removeCustomProperty('OldField');

// Read back
const proj = wb.getCustomProperty('ProjectCode');
console.log(proj?.value.value);  // 'PRJ-2024-007'

// Full list
for (const p of wb.customProperties) {
  console.log(p.name, p.value.type, p.value.value);
}

ws.setValue(row, col, value);          // string | number | boolean | Date
ws.setFormula(row, col, 'SUM(A1:A5)');
ws.setArrayFormula(row, col, 'row*col formula', 'A1:C3');
ws.setStyle(row, col, cellStyle);
ws.setCell(row, col, { value, formula, style, comment, hyperlink });

// Bulk writes
ws.writeRow(row, startCol, [v1, v2, v3]);
ws.writeArray(startRow, startCol, [[...], [...], ...]);

const cell = ws.getCell(row, col);
cell.value     // the stored value (string | number | boolean | undefined)
cell.formula   // formula string if present
cell.style     // CellStyle object

import { style, Colors, NumFmt, Styles } from './src/index.js';

// Fluent builder
const headerStyle = style()
  .bold()
  .italic()
  .fontSize(13)
  .fontColor(Colors.White)
  .bg(Colors.ExcelBlue)
  .border('thin')
  .center()
  .wrapText()
  .numFmt(NumFmt.Currency)
  .build();

// Built-in presets
ws.setStyle(1, 1, Styles.bold);
ws.setStyle(1, 2, Styles.headerBlue);
ws.setStyle(2, 3, Styles.currency);
ws.setStyle(3, 4, Styles.percent);

NumFmt.General      // General
NumFmt.Integer      // 0
NumFmt.Decimal2     // #,##0.00
NumFmt.Currency     // $#,##0.00
NumFmt.Percent      // 0%
NumFmt.Percent2     // 0.00%
NumFmt.Scientific   // 0.00E+00
NumFmt.ShortDate    // mm-dd-yy
NumFmt.LongDate     // d-mmm-yy
NumFmt.Time         // h:mm:ss AM/PM
NumFmt.DateTime     // m/d/yy h:mm
NumFmt.Accounting   // _($* #,##0.00_)
NumFmt.Text         // @

ws.merge(r1, c1, r2, c2);              // merge a range
ws.mergeByRef('A1:D1');
ws.freeze(rows, cols);                 // freeze panes
ws.setColumn(colIndex, { width: 20, hidden: false, style });
ws.setRow(rowIndex, { height: 30, hidden: false });
ws.autoFilter = { ref: 'A1:E1' };

ws.addConditionalFormat({
  sqref: 'C2:C100',
  type: 'colorScale',
  colorScale: {
    min: { type: 'min', color: 'FFF8696B' },
    max: { type: 'max', color: 'FF63BE7B' },
  },
  priority: 1,
});

ws.addConditionalFormat({
  sqref: 'D2:D100',
  type: 'dataBar',
  dataBar: { color: 'FF638EC6' },
  priority: 2,
});

ws.addDataValidation({
  sqref: 'B2:B100',
  type: 'list',
  formula1: '"North,South,East,West"',
  showDropDown: false,
  errorTitle: 'Invalid Region',
  error: 'Please select a valid region.',
});

ws.addChart({
  type: 'bar',
  title: 'Sales by Region',
  series: [{ name: 'Q1 Sales', dataRange: 'Sheet1!B2:B6', catRange: 'Sheet1!A2:A6' }],
  position: { from: { row: 1, col: 8 }, to: { row: 20, col: 16 } },
  legend: { position: 'bottom' },
});

import { readFileSync } from 'fs';
const imgData = readFileSync('./logo.png');

// Floating image (two-cell anchor)
ws.addImage({
  data:   imgData,          // Buffer, Uint8Array, or base64 string
  format: 'png',
  from:   { row: 1, col: 1 },
  to:     { row: 8, col: 4 },
});

// One-cell anchor with explicit pixel size
ws.addImage({
  data:   readFileSync('./icon.svg'),
  format: 'svg',
  from:   { row: 1, col: 6 },
  width:  80,
  height: 80,
  altText: 'Company icon',
});

// Absolute positioning (not tied to any cell)
ws.addImage({
  data:   imgData,
  format: 'png',
  position: { x: 200, y: 100 },   // pixels from top-left of sheet
  width:  120,
  height: 80,
});

import type { CellImage } from '@node-projects/excelforge';

ws.addCellImage({
  data:    readFileSync('./photo.png'),
  format:  'png',
  cell:    'B2',          // cell reference
  altText: 'Product photo',
});

const wb = new Workbook();

// Source data sheet
const wsData = wb.addSheet('Data');
wsData.writeRow(1, 1, ['Region', 'Product', 'Sales', 'Units']);
wsData.writeArray(2, 1, [
  ['North', 'Widget', 12000, 150],
  ['South', 'Widget', 9500,  120],
  ['North', 'Gadget', 8700,  90],
  ['South', 'Gadget', 11200, 140],
]);

// Pivot table on a separate sheet
const wsPivot = wb.addSheet('Summary');
wsPivot.addPivotTable({
  name:        'SalesBreakdown',
  sourceSheet: 'Data',
  sourceRef:   'A1:D5',
  targetCell:  'A1',
  rowFields:   ['Region'],
  colFields:   ['Product'],
  dataFields:  [{ field: 'Sales', name: 'Sum of Sales', func: 'sum' }],
  style:       'PivotStyleMedium9',
  rowGrandTotals: true,
  colGrandTotals: true,
});

await wb.writeFile('./pivot_report.xlsx');

import { Workbook, VbaProject } from './src/index.js';

const wb = new Workbook();
const ws = wb.addSheet('Sheet1');
ws.setValue(1, 1, 'Hello');

const vba = new VbaProject();

// Standard module
vba.addModule({
  name: 'Module1',
  type: 'standard',
  code: 'Sub HelloWorld()\r\n    MsgBox "Hello from VBA!"\r\nEnd Sub\r\n',
});

// Class module
vba.addModule({
  name: 'MyClass',
  type: 'class',
  code: [
    'Private pValue As String',
    'Public Property Get Value() As String',
    '    Value = pValue',
    'End Property',
    'Public Property Let Value(v As String)',
    '    pValue = v',
    'End Property',
  ].join('\r\n') + '\r\n',
});

wb.vbaProject = vba;
await wb.writeFile('./macros.xlsm');  // must use .xlsm extension

const wb = await Workbook.fromFile('./macros.xlsm');
if (wb.vbaProject) {
  for (const mod of wb.vbaProject.modules) {
    console.log(`${mod.name} (${mod.type}): ${mod.code.length} chars`);
  }
}

// Modify and re-save — existing modules are preserved
wb.vbaProject.addModule({ name: 'Module2', type: 'standard', code: '...' });
wb.vbaProject.removeModule('OldModule');
await wb.writeFile('./macros_updated.xlsm');

ws.pageSetup = {
  paperSize:   9,             // A4
  orientation: 'landscape',
  scale:       90,
  fitToPage:   true,
  fitToWidth:  1,
  fitToHeight: 0,
};

ws.pageMargins = {
  left: 0.5, right: 0.5, top: 0.75, bottom: 0.75,
  header: 0.3, footer: 0.3,
};

ws.headerFooter = {
  oddHeader: '&C&BQ4 Report&B',
  oddFooter: '&LExcelForge&RPage &P of &N',
};

// Add manual page breaks for printing
ws.addRowBreak(20);    // page break after row 20
ws.addRowBreak(40);    // page break after row 40
ws.addColBreak(5);     // page break after column E

// Read page breaks from an existing file
const wb = await Workbook.fromBytes(data);
const ws = wb.getSheet('Sheet1')!;
for (const brk of ws.getRowBreaks()) {
  console.log(`Row break at ${brk.id}, manual: ${brk.manual}`);
}
for (const brk of ws.getColBreaks()) {
  console.log(`Col break at ${brk.id}, manual: ${brk.manual}`);
}

// Define workbook-scoped named ranges
wb.addNamedRange({ name: 'SalesData', ref: 'Data!$A$1:$A$5' });
wb.addNamedRange({ name: 'Products', ref: 'Data!$B$1:$B$5', comment: 'Product list' });

// Define sheet-scoped named range
wb.addNamedRange({ name: 'LocalTotal', ref: 'Data!$A$6', scope: 'Data' });

// Use in formulas
ws.setFormula(1, 1, 'SUM(SalesData)');

// Read named ranges from an existing file
const wb2 = await Workbook.fromBytes(data);
const ranges = wb2.getNamedRanges();         // all named ranges
const sales = wb2.getNamedRange('SalesData'); // find by name
console.log(sales?.ref);                      // "Data!$A$1:$A$5"

// Remove a named range
wb2.removeNamedRange('SalesData');

// Add a data connection (OLEDB, ODBC, text/CSV, web, etc.)
wb.addConnection({
  id: 1,
  name: 'SalesDB',
  type: 'oledb',  // 'odbc' | 'dao' | 'file' | 'web' | 'oledb' | 'text' | 'dsp'
  connectionString: 'Provider=SQLOLEDB;Data Source=server;Initial Catalog=Sales;',
  command: 'SELECT * FROM Orders',
  commandType: 'sql',  // 'sql' | 'table' | 'default' | 'web' | 'oledb'
  description: 'Sales database connection',
  background: true,
  saveData: true,
});

// Read connections from an existing file
const wb2 = await Workbook.fromBytes(data);
const conns = wb2.getConnections();           // all connections
const sales = wb2.getConnection('SalesDB');   // find by name
wb2.removeConnection('SalesDB');              // remove by name

// Read Power Query M formulas (extracted from DataMashup)
const queries = wb2.getPowerQueries();        // all queries
const q = wb2.getPowerQuery('MyQuery');       // find by name
console.log(q?.formula);                       // Power Query M code

// Add a button with a macro
ws.addFormControl({
  type: 'button',
  from: { col: 1, row: 2 },
  to:   { col: 3, row: 4 },
  text: 'Run Report',
  macro: 'Sheet1.RunReport',
});

// Button sized by width/height (no 'to' anchor needed)
ws.addFormControl({
  type: 'button',
  from: { col: 1, row: 5 },
  width: 120, height: 30,    // pixels
  text: 'Compact Button',
});

// CheckBox linked to a cell
ws.addFormControl({
  type: 'checkBox',
  from: { col: 1, row: 7 },
  to:   { col: 3, row: 8 },
  text: 'Enable Feature',
  linkedCell: '$B$10',
  checked: 'checked',   // 'checked' | 'unchecked' | 'mixed'
});

// ComboBox (dropdown) with input range
ws.addFormControl({
  type: 'comboBox',
  from: { col: 1, row: 7 },
  to:   { col: 3, row: 8 },
  linkedCell: '$B$11',
  inputRange: '$D$1:$D$5',
  dropLines: 5,
});

// ListBox, OptionButton, GroupBox, Label, ScrollBar, Spinner
ws.addFormControl({
  type: 'scrollBar',
  from: { col: 4, row: 6 },
  to:   { col: 6, row: 7 },
  linkedCell: '$B$14',
  min: 0, max: 100, inc: 1, page: 10, val: 50,
});

// Read form controls from an existing file
const wb2 = await Workbook.fromBytes(data);
const controls = ws.getFormControls();
for (const ctrl of controls) {
  console.log(ctrl.type, ctrl.linkedCell, ctrl.macro);
}

ws.protect('mypassword', {
  formatCells:   false,   // allow formatting
  insertRows:    false,   // allow inserting rows
  deleteRows:    false,
  sort:          false,
  autoFilter:    false,
});

// Lock individual cells (requires sheet protection to take effect)
ws.setCell(1, 1, { value: 'Locked', style: { locked: true } });
ws.setCell(2, 1, { value: 'Editable', style: { locked: false } });

// Node.js: write to file
await wb.writeFile('./output.xlsx');

// Browser: trigger download
await wb.download('report.xlsx');

// Any runtime: get bytes
const bytes: Uint8Array = await wb.build();
const b64: string       = await wb.buildBase64();

import { buildZip, deflateRaw, type ZipEntry, type ZipOptions } from './src/utils/zip.js';

// deflateRaw: compress bytes with raw DEFLATE (no zlib header)
const compressed = deflateRaw(data, 6);  // level 0–9

// buildZip: assemble a ZIP archive
const zip = buildZip(entries, { level: 6 });

// ZipEntry shape
interface ZipEntry {
  name:   string;
  data:   Uint8Array;
  level?: number;  // per-entry override
}

// ZipOptions shape
interface ZipOptions {
  level?:       number;    // global default (0–9)
  noCompress?:  string[];  // extensions to always STORE
}