program WorkbookReport;

// Atlas VCL data models without a window: a TAtlasTable is sorted, filtered
// and exported to CSV, TAtlasPivot summarizes it, TAtlasWorkbook computes the
// totals with formulas and cell formats, and TAtlasSpellChecker checks the
// report caption. Every result is verified; the exit code is 0 on success.

{$APPTYPE CONSOLE}

uses
  System.SysUtils, System.Classes, System.IOUtils,
  Atlas.Data, Atlas.Pivot, Atlas.Spreadsheet, Atlas.Spell;

type
  TChangeCounter = class
    Count: Integer;
    procedure Changed(Sender: TObject);
  end;

procedure TChangeCounter.Changed(Sender: TObject);
begin
  Inc(Count);
end;

procedure Expect(Condition: Boolean; const What: string);
begin
  if not Condition then
    raise Exception.Create('Check failed: ' + What);
  Writeln('  ok: ', What);
end;

procedure ExpectText(const Expected, Actual, What: string);
begin
  if Expected <> Actual then
    raise Exception.CreateFmt('%s: expected [%s], got [%s]', [What, Expected, Actual]);
  Writeln('  ok: ', What, ' = ', Actual);
end;

function BuildSales: TAtlasTable;
begin
  Result := TAtlasTable.Create(nil);
  Result.AddColumn('Region', ackText, 120, False);
  Result.AddColumn('Quarter', ackText, 80, False);
  Result.AddColumn('Sales', ackNumber, 100);
  Result.AddColumn('Closed', ackDate, 110);
  Result.AddRow(['East', 'Q1', '18000', '2026-03-31']);
  Result.AddRow(['East', 'Q2', '22000', '2026-06-30']);
  Result.AddRow(['West', 'Q1', '24500', '2026-03-31']);
  Result.AddRow(['West', 'Q2', '29800.50', '2026-06-30']);
  Result.AddRow(['North', 'Q1', '16400', '']);
end;

procedure TableSection(Sales: TAtlasTable);
var
  Copy: TAtlasTable;
  Before, FileName: string;
  Failed: Boolean;
begin
  Writeln('TAtlasTable');
  Expect((Sales.ColumnCount = 4) and (Sales.RowCount = 5), 'four columns, five rows');
  ExpectText('29800.5', Sales.Cell(2, 3), 'numbers are stored in invariant form');
  Sales.Sort(2, False);
  ExpectText('West', Sales.Cell(0, 0), 'descending numeric sort puts the largest sale first');
  Expect(Sales.SourceIndex(0) = 3, 'view row 0 maps to source row 3 after sorting');
  Sales.SetFilter('east', 0);
  Expect(Sales.RowCount = 2, 'case-insensitive filter on the Region column');
  Sales.SetCell(2, 1, '18500');
  Sales.SetFilter('');
  Sales.Sort(-1);
  ExpectText('18500', Sales.Cell(2, 0), 'an edit made through the filtered view reached the right source row');
  Failed := False;
  try
    Sales.SetCell(2, 0, 'lots');
  except
    on E: EAtlasDataError do
      Failed := True;
  end;
  Expect(Failed, 'a non-numeric value is rejected for a number column');
  Failed := False;
  try
    Sales.SetCell(0, 0, 'South');
  except
    on E: EAtlasDataError do
      Failed := True;
  end;
  Expect(Failed, 'Region is a read-only column');

  FileName := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), 'sales.csv');
  TFile.WriteAllText(FileName, Sales.ExportCSV(False), TEncoding.UTF8);
  Copy := TAtlasTable.Create(nil);
  try
    Copy.AddColumn('Region', ackText, 120, False);
    Copy.AddColumn('Quarter', ackText, 80, False);
    Copy.AddColumn('Sales', ackNumber, 100);
    Copy.AddColumn('Closed', ackDate, 110);
    Copy.ImportCSV(TFile.ReadAllText(FileName, TEncoding.UTF8));
    ExpectText(Sales.ExportCSV(False), Copy.ExportCSV(False), 'CSV round trip through ' + FileName);
    Before := Copy.ExportCSV(False);
    Failed := False;
    try
      Copy.ImportCSV('Region,Quarter,Sales,Closed' + sLineBreak + 'East,Q3,ten,' + sLineBreak);
    except
      on E: EAtlasDataError do
        Failed := True;
    end;
    Expect(Failed and (Copy.ExportCSV(False) = Before), 'a failed import leaves the previous rows in place');
  finally
    Copy.Free;
  end;
end;

procedure PivotSection(Sales: TAtlasTable; Workbook: TAtlasWorkbook);
var
  Pivot: TAtlasPivot;
  Summary: TAtlasTable;
  Row, Col: Integer;
begin
  Writeln('TAtlasPivot');
  Pivot := TAtlasPivot.Create(nil);
  try
    Summary := Pivot.Summarize(Sales, 0, 1, 2, aaSum); // rows: Region, columns: Quarter, values: Sales
    try
      Expect((Summary.ColumnCount = 4) and (Summary.RowCount = 4), 'three regions plus a total row; Q1, Q2 and a total column');
      ExpectText('Rows: Region', Summary.Columns[0].Name, 'row dimension header');
      ExpectText('1: Q1', Summary.Columns[1].Name, 'column keys are numbered to stay unique');
      ExpectText('40500', Summary.Cell(3, 0), 'East row total');
      ExpectText('0', Summary.Cell(2, 1), 'North has no Q2 sales, so its sum is 0');
      ExpectText('111200.5', Summary.Cell(3, 3), 'grand total');
      // Copy the summary into the workbook, then let formulas recompute the totals.
      for Row := 0 to Summary.RowCount - 2 do
        for Col := 0 to Summary.ColumnCount - 2 do
          Workbook.SetCell(AtlasCellAddress(Col + 1, Row + 2), Summary.Cell(Col, Row));
    finally
      Summary.Free; // Summarize returns a table owned by the caller
    end;
    Summary := Pivot.Summarize(Sales, 0, 1, 2, aaAverage);
    try
      ExpectText('20250', Summary.Cell(3, 0), 'East average uses the original observations');
    finally
      Summary.Free;
    end;
  finally
    Pivot.Free;
  end;
end;

procedure WorkbookSection(Workbook: TAtlasWorkbook);
var
  Counter: TChangeCounter;
  Format: TAtlasCellFormat;
  Copy: TAtlasWorkbook;
begin
  Writeln('TAtlasWorkbook');
  Counter := TChangeCounter.Create;
  Copy := TAtlasWorkbook.Create(nil);
  try
    Workbook.AddListener(Counter.Changed);
    Workbook.SetCell('A1', 'Region');
    Workbook.SetCell('B1', 'Q1');
    Workbook.SetCell('C1', 'Q2');
    Workbook.SetCell('D1', 'Total');
    Workbook.SetCell('D2', '=B2+C2');
    Workbook.SetCell('D3', '=SUM(B3:C3)');
    Workbook.SetCell('D4', '=SUM(B4:C4)');
    Workbook.SetCell('A5', 'All regions');
    Workbook.SetCell('D5', '=SUM(D2:D4)');
    Workbook.SetCell('A6', 'Best quarter');
    Workbook.SetCell('D6', '=MAX(B2:C4)');
    Workbook.SetCell('A7', 'East share');
    Workbook.SetCell('D7', '=D2/D5');
    Expect(Counter.Count = 13, 'every SetCell notified the listener once');
    ExpectText('40500', Workbook.Display('D2'), 'East total from a cell formula');
    ExpectText('111200.5', Workbook.Display('D5'), 'grand total from SUM over formula cells');
    ExpectText('29800.5', Workbook.Display('D6'), 'MAX over a two-column range');
    Expect(Abs(Workbook.Value('D7') - 40500 / 111200.5) < 1E-12, 'Value returns the Double behind Display');
    Format := TAtlasCellFormat.Default;
    Format.NumberFormat := anfCurrency;
    Format.Bold := True;
    Workbook.SetCellFormat('D5', Format);
    ExpectText('$111200.50', Workbook.Display('D5'), 'currency format with two decimals');
    Format := TAtlasCellFormat.Default;
    Format.NumberFormat := anfPercent;
    Format.Decimals := 1;
    Workbook.SetCellFormat('D7', Format);
    ExpectText('36.4%', Workbook.Display('D7'), 'percent format');
    ExpectText('=D2/D5', Workbook.Raw('D7'), 'Raw returns the formula text');
    Workbook.SetCell('E1', '=B2/0');
    ExpectText('#DIV/0!', Workbook.Display('E1'), 'division by zero shows an error code');
    Workbook.SetCell('E2', '=E3');
    Workbook.SetCell('E3', '=E2');
    ExpectText('#CYCLE!', Workbook.Display('E2'), 'a dependency cycle shows an error code');
    Workbook.SetCell('E4', '=A1+1');
    ExpectText('#VALUE!', Workbook.Display('E4'), 'arithmetic on text shows an error code');
    Workbook.SetCell('E1', '');
    Workbook.SetCell('E2', '');
    Workbook.SetCell('E3', '');
    Workbook.SetCell('E4', '');
    Copy.LoadJSON(Workbook.ToJSON);
    Copy.LoadFormatsJSON(Workbook.FormatsJSON);
    ExpectText(Workbook.ToJSON, Copy.ToJSON, 'cell JSON round trip');
    ExpectText(Workbook.FormatsJSON, Copy.FormatsJSON, 'format JSON round trip');
    ExpectText('$111200.50', Copy.Display('D5'), 'the copy computes and formats the same total');
    Workbook.RemoveListener(Counter.Changed);
  finally
    Copy.Free;
    Counter.Free;
  end;
end;

procedure SpellingSection;
var
  Spell: TAtlasSpellChecker;
  Issues: TArray<TAtlasSpellingIssue>;
  Suggestions: TArray<string>;
begin
  Writeln('TAtlasSpellChecker');
  Spell := TAtlasSpellChecker.Create(nil);
  try
    Spell.LoadDictionary('# report vocabulary' + sLineBreak +
      'regional' + sLineBreak + 'sales' + sLineBreak + 'report' + sLineBreak +
      'total' + sLineBreak + 'quarter' + sLineBreak + 'east' + sLineBreak + 'west' + sLineBreak +
      'and' + sLineBreak + 'by');
    Spell.AddWord('north');
    Issues := Spell.Check('Regional sales reprot: East, West and North by quater');
    Expect(Length(Issues) = 2, 'two unknown words found');
    ExpectText('reprot', Issues[0].Word, 'first issue word');
    Expect((Issues[0].StartIndex = 16) and (Issues[0].Length = 6), 'one-based start index and length');
    Suggestions := Spell.Suggest('reprot');
    ExpectText('report', Suggestions[0], 'best suggestion by edit distance');
    Spell.IgnoreWord('quater');
    Expect(Length(Spell.Check('by quater')) = 0, 'ignored words are not reported');
    Expect(Spell.Known('NORTH'), 'lookups ignore case');
  finally
    Spell.Free;
  end;
end;

var
  Sales: TAtlasTable;
  Workbook: TAtlasWorkbook;
begin
  try
    Sales := BuildSales;
    Workbook := TAtlasWorkbook.Create(nil);
    try
      TableSection(Sales);
      PivotSection(Sales, Workbook);
      WorkbookSection(Workbook);
      SpellingSection;
    finally
      Workbook.Free;
      Sales.Free;
    end;
    Writeln('PASS: WorkbookReport');
  except
    on E: Exception do
    begin
      Writeln('FAIL: ', E.ClassName, ': ', E.Message);
      Halt(1);
    end;
  end;
end.