Financial Reporting Workflow Demo

This demo is inspired by a real-world IFRS reporting project delivered in partnership with one of the Big Four accounting firms. It showcases a spreadsheet-driven financial reporting workflow built with Keikai. While the sample uses an IFRS investment-contract reporting scenario, the same approach can be applied to many financial and regulatory reporting processes.

Policy assumptions, formulas, and journal rules can be maintained directly in the spreadsheet and updated by business users without code changes. Because Keikai is embedded in the application, users can review data, adjust rules, run calculations, and generate reporting outputs without switching to external Excel tools.

The Run Financial Reporting process reads policy inputs and journal rules, calculates accounting metrics, generates journal entries and report impacts, and produces export-ready CSV outputs for downstream systems.

<?xml version="1.0" encoding="UTF-8"?>
<zk xmlns="http://www.zkoss.org/2005/zul"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.zkoss.org/2005/zul http://keikai.io/2019/zul/zul.xsd">
    <style src="/demo/financial_reporting/style.css"/>
    <vlayout width="100%" vflex="1" apply="demo.financial_reporting.FinancialReportingComposer" spacing="0" sclass="ifrs-demo">
        <hlayout width="100%" vflex="1" spacing="10px" sclass="ifrs-page">
            <vlayout width="360px" vflex="1" spacing="10px">
                <vlayout sclass="ifrs-panel" spacing="8px">
                    <label value="Financial Reporting" sclass="ifrs-title" />
                    <hlayout spacing="6px">
                        <button id="startReportingCoachmark" label="Start Guided Tour" iconSclass="z-icon-map-signs" sclass="ifrs-action" />
                        <button id="runJournalProcess" label="Generate Journal Entries" iconSclass="z-icon-play" sclass="ifrs-action" />
                    </hlayout>
                    <vlayout sclass="ifrs-status" spacing="2px">
                        <hlayout spacing="6px" valign="middle">
                            <label value="Result:" sclass="ifrs-result-label" />
                            <label id="stage2StatusTitle" value="Not started yet." sclass="ifrs-status-title" />
                        </hlayout>
                        <label id="stage2StatusDetail" value="" sclass="ifrs-status-detail" />
                    </vlayout>
                </vlayout>
                <vlayout sclass="ifrs-panel" spacing="8px">
                    <tabbox width="100%">
                        <tabs>
                            <tab id="reportingMetricsTab" label="Metrics" selected="true" />
                            <tab id="reportingVisualizationTab" label="Data visualization" />
                        </tabs>
                        <tabpanels>
                            <tabpanel>
                                <vlayout id="stage2FlowProcess" sclass="ifrs-data-viz" spacing="5px">
                                    <label value="Calculated metrics" sclass="ifrs-viz-title" />
                                    <label id="stage2MetricPrincipal" value="IFRS 9 principal base: not calculated" sclass="ifrs-viz-metric" />
                                    <label id="stage2MetricServiceFee" value="IFRS 15 service fee base: not calculated" sclass="ifrs-viz-metric" />
                                    <label id="stage2MetricFairValue" value="Fair value movement: not calculated" sclass="ifrs-viz-metric" />
                                    <label id="stage2FlowOutputDetail" value="Before running, only Stage2_Policy is shown." sclass="ifrs-viz-detail" />
                                </vlayout>
                            </tabpanel>
                            <tabpanel>
                                <vlayout id="stage2DataVisualization" sclass="ifrs-legend" spacing="5px">
                                    <hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-blue" /><label value="Blue: source policy values before entry generation" sclass="ifrs-subtitle" /></hlayout>
                                    <hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-green" /><label value="Green: derived IFRS metrics and after-process values" sclass="ifrs-subtitle" /></hlayout>
                                    <hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-amber" /><label value="Amber: generated journal debit / credit amounts" sclass="ifrs-subtitle" /></hlayout>
                                    <hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-red" /><label value="Red: report change between before and after" sclass="ifrs-subtitle" /></hlayout>
                                    <hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-purple" /><label value="Purple: editable journal rule source" sclass="ifrs-subtitle" /></hlayout>
                                </vlayout>
                            </tabpanel>
                        </tabpanels>
                    </tabbox>
                </vlayout>
            </vlayout>
            <vlayout hflex="1" vflex="1" spacing="10px">
                <vlayout sclass="ifrs-panel" vflex="1" spacing="6px">
                    <label value="Policy input, journal rule source, and generated sheets in Keikai" sclass="ifrs-title" />
                    <spreadsheet id="journalSpreadsheet" width="100%" vflex="1"
                                 src="/WEB-INF/books/ifrs_investment_contract_close.xlsx"
                                 maxVisibleRows="24" maxVisibleColumns="8"
                                 showSheetbar="true" showToolbar="false" showFormulabar="true"/>
                </vlayout>
            </vlayout>
        </hlayout>
        <window id="reportingTourIntroWindow" width="560px" mode="modal" border="normal" closable="false"
                visible="false" sclass="ifrs-tour-intro">
            <vlayout spacing="8px">
                <label sclass="ifrs-tour-text"
                       value="This demo reads policy inputs and journal rules, derives IFRS accounting metrics, generates journal entries, updates report impact sheets, and produces CSV outputs for downstream systems." />
                <hbox width="100%" pack="end">
                    <button id="reportingCoachNextToPolicy" label="Next" iconSclass="z-icon-arrow-right" />
                </hbox>
            </vlayout>
        </window>
        <coachmark id="reportingCoachmark1" target="journalSpreadsheet" position="before_start" sclass="ifrs-coachmark" visible="false">
            <vlayout spacing="8px">
                <label value="Step 1: Stage2_Policy contains the policy inputs and journal rules. The content can be edited directly in Keikai as needed." />
                <hbox width="100%" pack="end">
                    <button id="reportingCoachNextToRun" label="Next" iconSclass="z-icon-arrow-right" />
                </hbox>
            </vlayout>
        </coachmark>
        <coachmark id="reportingCoachmark2" target="runJournalProcess" position="end_before" sclass="ifrs-coachmark" visible="false">
            <vlayout spacing="8px">
                <label value="Step 2: Click Run Financial Reporting to start the reporting process." />
                <hbox width="100%" pack="end">
                    <button id="reportingCoachRunFinancialReporting" label="Next" iconSclass="z-icon-arrow-right" />
                </hbox>
            </vlayout>
        </coachmark>
        <coachmark id="reportingCoachmark3" target="stage2FlowProcess" position="end_before" sclass="ifrs-coachmark" visible="false">
            <vlayout spacing="8px">
                <label value="Step 3: IFRS accounting metrics are calculated, including IFRS 9 principal balance and fair value movement." />
                <hbox width="100%" pack="end">
                    <button id="reportingCoachNextToJournalSheet" label="Next" iconSclass="z-icon-arrow-right" />
                </hbox>
            </vlayout>
        </coachmark>
        <coachmark id="reportingCoachmark6" target="journalSpreadsheet" position="before_start" sclass="ifrs-coachmark" visible="false">
            <vlayout spacing="8px">
                <label value="Step 4: Journal Entries and Report Impact sheets are generated, allowing users to review accounting entries and their reporting impact." />
                <hbox width="100%" pack="end">
                    <button id="reportingCoachNextToCsv" label="Next" iconSclass="z-icon-arrow-right" />
                </hbox>
            </vlayout>
        </coachmark>
        <coachmark id="reportingCoachmark7" target="journalSpreadsheet" position="before_start" sclass="ifrs-coachmark" visible="false">
            <vlayout spacing="8px">
                <label value="Step 5: Journal CSV, Report CSV, and Metric CSV are generated as separate output tables. Each sheet can be exported independently as a CSV file." />
                <hbox width="100%" pack="end">
                    <button id="reportingCoachFinish" label="Finish" iconSclass="z-icon-check" />
                </hbox>
            </vlayout>
        </coachmark>
    </vlayout>
</zk>

package demo.financial_reporting;

import io.keikai.api.AreaRef;
import io.keikai.api.CellRef;
import io.keikai.api.Range;
import io.keikai.api.Ranges;
import io.keikai.api.model.CellStyle;
import io.keikai.api.model.Font;
import io.keikai.api.model.Sheet;
import io.keikai.ui.Spreadsheet;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zkmax.zul.Coachmark;
import org.zkoss.zul.Button;
import org.zkoss.zul.Label;
import org.zkoss.zul.Tab;
import org.zkoss.zul.Window;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class FinancialReportingComposer extends SelectorComposer<Component> {
    private static final String POLICY_SHEET = "Stage2_Policy";
    private static final String JOURNAL_SHEET = "Journal Entries";
    private static final String REPORT_SHEET = "Report Impact";
    private static final String JOURNAL_CSV_SHEET = "Journal CSV";
    private static final String REPORT_CSV_SHEET = "Report CSV";
    private static final String METRIC_CSV_SHEET = "Metric CSV";
    private static final int RULE_START_ROW = 17;

    @Wire
    private Spreadsheet journalSpreadsheet;
    @Wire
    private Tab reportingMetricsTab;
    @Wire
    private Tab reportingVisualizationTab;
    @Wire
    private Label stage2StatusTitle;
    @Wire
    private Label stage2StatusDetail;
    @Wire
    private Label stage2FlowOutputDetail;
    @Wire
    private Label stage2MetricPrincipal;
    @Wire
    private Label stage2MetricServiceFee;
    @Wire
    private Label stage2MetricFairValue;
    @Wire
    private Button startReportingCoachmark;
    @Wire
    private Window reportingTourIntroWindow;
    @Wire
    private Coachmark reportingCoachmark1;
    @Wire
    private Coachmark reportingCoachmark2;
    @Wire
    private Coachmark reportingCoachmark3;
    @Wire
    private Coachmark reportingCoachmark6;
    @Wire
    private Coachmark reportingCoachmark7;

    private static final Object[][] DEFAULT_JOURNAL_RULE_ROWS = {
            {"Journal rule source", "", "", ""},
            {"Event", "Description", "Debit metric", "Credit metric"},
            {"I150100A", "Recognize IFRS 15 service contract liability", "service_fee_base", "service_fee_base"},
            {"I0901000", "Recognize IFRS 9 FVTPL financial liability", "principal_base", "principal_base"},
            {"I150912A", "Monthly service revenue amortization", "monthly_revenue", "monthly_revenue"},
            {"I0909100", "FVTPL fair value remeasurement", "abs_fair_value_movement", "abs_fair_value_movement"}
    };

    @Override
    public void doAfterCompose(Component comp) throws Exception {
        super.doAfterCompose(comp);
        configureWorkbook(false);
        setReportingState(false);
    }

    @Listen(Events.ON_CLICK + " = #runJournalProcess")
    public void runFinancialReporting() {
        PolicyInput policy = readPolicyInput();

        double principal = (policy.premiumTotal - policy.serviceFee) * policy.fxRate;
        double serviceFee = policy.serviceFee * policy.fxRate;
        double accountValue = policy.accountValue * policy.fxRate;
        double monthlyRevenue = serviceFee / 60;
        double fairValueMovement = accountValue - principal;

        double beforeRevenue = serviceFee / 48;
        double afterRevenue = monthlyRevenue;
        double beforeFinancialLiability = principal;
        double afterFinancialLiability = accountValue;
        double beforeContractLiability = serviceFee;
        double afterContractLiability = serviceFee - monthlyRevenue;
        double beforeProfitLossImpact = beforeRevenue;
        double afterProfitLossImpact = afterRevenue - fairValueMovement;
        Map<String, Double> metrics = new HashMap<String, Double>();
        metrics.put("principal_base", principal);
        metrics.put("service_fee_base", serviceFee);
        metrics.put("account_value_base", accountValue);
        metrics.put("monthly_revenue", monthlyRevenue);
        metrics.put("fair_value_movement", fairValueMovement);
        metrics.put("abs_fair_value_movement", Math.abs(fairValueMovement));

        Object[][] journalRows = buildJournalRows(readJournalRules(), metrics);
        Object[][] reportRows = {
                {"Line", "Before", "After", "Change"},
                {"Revenue recognized", beforeRevenue, afterRevenue, afterRevenue - beforeRevenue},
                {"IFRS 9 FVTPL liability", beforeFinancialLiability, afterFinancialLiability, afterFinancialLiability - beforeFinancialLiability},
                {"IFRS 15 contract liability", beforeContractLiability, afterContractLiability, afterContractLiability - beforeContractLiability},
                {"P&L impact", beforeProfitLossImpact, afterProfitLossImpact, afterProfitLossImpact - beforeProfitLossImpact}
        };
        Object[][] metricRows = {
                {"Metric", "Value"},
                {"principal_base", principal},
                {"service_fee_base", serviceFee},
                {"account_value_base", accountValue},
                {"monthly_revenue", monthlyRevenue},
                {"fair_value_movement", fairValueMovement}
        };

        configureWorkbook(true);
        writeSheetTable(JOURNAL_SHEET, journalRows);
        writeSheetTable(REPORT_SHEET, reportRows);
        writeCsvSheet(JOURNAL_CSV_SHEET, journalRows);
        writeCsvSheet(REPORT_CSV_SHEET, reportRows);
        writeCsvSheet(METRIC_CSV_SHEET, metricRows);
        applyOutputStyles();
        journalSpreadsheet.setSelectedSheet(JOURNAL_SHEET);
        focusSingleCell(journalSpreadsheet, 0, 0);

        stage2StatusTitle.setValue("Financial Reporting Completed");
        stage2StatusDetail.setValue("Journal entries, report impact, and export-ready CSV sheets were generated.");
        closeReportingCoachmarks();
        setReportingState(true);
        setLabel(stage2MetricPrincipal, "IFRS 9 principal base: " + formatAmount(principal));
        setLabel(stage2MetricServiceFee, "IFRS 15 service fee base: " + formatAmount(serviceFee));
        setLabel(stage2MetricFairValue, "Fair value movement: " + formatAmount(fairValueMovement));
    }

    @Listen(Events.ON_CLICK + " = #startReportingCoachmark")
    public void startReportingCoachmark() {
        resetReportingDemo();
        closeReportingCoachmarks();
        if (reportingTourIntroWindow != null) {
            reportingTourIntroWindow.doModal();
        }
    }

    @Listen(Events.ON_CLICK + " = #reportingTourIntroWindow #reportingCoachNextToPolicy")
    public void nextReportingCoachmarkFromIntro() {
        if (reportingTourIntroWindow != null) {
            reportingTourIntroWindow.setVisible(false);
        }
        selectReportingMetricsTab();
        selectJournalSheet(POLICY_SHEET, 0, 0);
        openCoachmark(reportingCoachmark1);
    }

    @Listen(Events.ON_CLICK + " = #reportingCoachNextToRun")
    public void nextReportingCoachmarkToRun() {
        openNextCoachmark(reportingCoachmark1, reportingCoachmark2);
    }

    @Listen(Events.ON_CLICK + " = #reportingCoachRunFinancialReporting")
    public void runFinancialReportingFromCoachmark() {
        closeCoachmark(reportingCoachmark2);
        runFinancialReporting();
        selectReportingMetricsTab();
        openNextCoachmark(reportingCoachmark2, reportingCoachmark3);
    }

    @Listen("onTargetClick = #reportingCoachmark2")
    public void runFinancialReportingFromCoachmarkTarget() {
        runFinancialReportingFromCoachmark();
    }

    @Listen(Events.ON_CLICK + " = #reportingCoachNextToJournalSheet")
    public void nextReportingCoachmarkToJournalSheet() {
        closeCoachmark(reportingCoachmark3);
        selectJournalSheet(JOURNAL_SHEET, 0, 0);
        openCoachmark(reportingCoachmark6);
    }

    @Listen(Events.ON_CLICK + " = #reportingCoachNextToCsv")
    public void nextReportingCoachmarkToCsv() {
        closeCoachmark(reportingCoachmark6);
        selectJournalSheet(JOURNAL_CSV_SHEET, 0, 0);
        openCoachmark(reportingCoachmark7);
    }

    @Listen(Events.ON_CLICK + " = #reportingCoachFinish")
    public void finishReportingCoachmark() {
        closeCoachmark(reportingCoachmark7);
    }

    private void setReportingState(boolean generated) {
        if (!generated) {
            setLabel(stage2MetricPrincipal, "IFRS 9 principal base: not calculated");
            setLabel(stage2MetricServiceFee, "IFRS 15 service fee base: not calculated");
            setLabel(stage2MetricFairValue, "Fair value movement: not calculated");
            closeReportingCoachmarks();
        }
        setLabel(stage2FlowOutputDetail, generated
                ? "Generated CSV sheets are now visible: Journal CSV, Report CSV, and Metric CSV."
                : "Before running, only Stage2_Policy is shown.");
    }

    private void openNextCoachmark(Coachmark current, Coachmark next) {
        closeCoachmark(current);
        openCoachmark(next);
    }

    private void openCoachmark(Coachmark coachmark) {
        if (coachmark != null) {
            coachmark.setVisible(true);
            coachmark.open();
        }
    }

    private void closeCoachmark(Coachmark coachmark) {
        if (coachmark != null) {
            coachmark.setVisible(false);
            coachmark.close();
        }
    }

    private void closeReportingCoachmarks() {
        closeCoachmark(reportingCoachmark1);
        closeCoachmark(reportingCoachmark2);
        closeCoachmark(reportingCoachmark3);
        closeCoachmark(reportingCoachmark6);
        closeCoachmark(reportingCoachmark7);
    }

    private void resetReportingDemo() {
        configureWorkbook(false);
        setReportingState(false);
        setLabel(stage2StatusTitle, "Not started yet.");
        setLabel(stage2StatusDetail, "");
        selectReportingMetricsTab();
        selectJournalSheet(POLICY_SHEET, 0, 0);
    }

    private void selectJournalSheet(String sheetName, int row, int col) {
        if (journalSpreadsheet != null) {
            journalSpreadsheet.setSelectedSheet(sheetName);
            focusSingleCell(journalSpreadsheet, row, col);
        }
    }

    private void focusSingleCell(Spreadsheet spreadsheet, int row, int col) {
        if (spreadsheet == null) {
            return;
        }
        spreadsheet.focus();
        spreadsheet.focusTo(row, col);
        spreadsheet.setCellFocus(new CellRef(row, col));
        spreadsheet.setSelection(new AreaRef(row, col, row, col));
    }

    private void selectReportingMetricsTab() {
        if (reportingMetricsTab != null) {
            reportingMetricsTab.setSelected(true);
        }
    }

    private void selectReportingVisualizationTab() {
        if (reportingVisualizationTab != null) {
            reportingVisualizationTab.setSelected(true);
        }
    }

    private void configureWorkbook(boolean showOutputs) {
        if (journalSpreadsheet == null) {
            return;
        }
        ensureSheet("Journal", JOURNAL_SHEET);
        ensureSheet("Reports", REPORT_SHEET);
        ensureSheet("Export_Journal_CSV", JOURNAL_CSV_SHEET);
        ensureSheet("Export_Report_CSV", REPORT_CSV_SHEET);
        ensureSheet("Metric CSV", METRIC_CSV_SHEET);
        initializeJournalRules();
        applyPolicyStyles();

        Sheet inputSheet = journalSpreadsheet.getBook().getSheet(POLICY_SHEET);
        Sheet journalSheet = journalSpreadsheet.getBook().getSheet(JOURNAL_SHEET);
        Sheet reportSheet = journalSpreadsheet.getBook().getSheet(REPORT_SHEET);
        Sheet journalCsvSheet = journalSpreadsheet.getBook().getSheet(JOURNAL_CSV_SHEET);
        Sheet reportCsvSheet = journalSpreadsheet.getBook().getSheet(REPORT_CSV_SHEET);
        Sheet metricCsvSheet = journalSpreadsheet.getBook().getSheet(METRIC_CSV_SHEET);

        for (int index = 0; index < journalSpreadsheet.getBook().getNumberOfSheets(); index++) {
            Sheet sheet = journalSpreadsheet.getBook().getSheetAt(index);
            boolean visible = POLICY_SHEET.equals(sheet.getSheetName()) || (showOutputs && isOutputSheet(sheet.getSheetName()));
            Ranges.range(sheet).setSheetVisible(visible ? Range.SheetVisible.VISIBLE : Range.SheetVisible.HIDDEN);
        }
        if (inputSheet != null) {
            Ranges.range(inputSheet).setSheetOrder(0);
        }
        if (journalSheet != null) {
            Ranges.range(journalSheet).setSheetOrder(1);
        }
        if (reportSheet != null) {
            Ranges.range(reportSheet).setSheetOrder(2);
        }
        if (journalCsvSheet != null) {
            Ranges.range(journalCsvSheet).setSheetOrder(3);
        }
        if (reportCsvSheet != null) {
            Ranges.range(reportCsvSheet).setSheetOrder(4);
        }
        if (metricCsvSheet != null) {
            Ranges.range(metricCsvSheet).setSheetOrder(5);
        }
        journalSpreadsheet.setSelectedSheet(POLICY_SHEET);
    }

    private void ensureSheet(String sourceName, String targetName) {
        Sheet targetSheet = journalSpreadsheet.getBook().getSheet(targetName);
        if (targetSheet != null) {
            return;
        }
        Sheet sourceSheet = journalSpreadsheet.getBook().getSheet(sourceName);
        if (sourceSheet != null) {
            Ranges.range(sourceSheet).setSheetName(targetName);
            return;
        }
        Ranges.range(journalSpreadsheet.getBook()).createSheet(targetName);
    }

    private boolean isOutputSheet(String sheetName) {
        return JOURNAL_SHEET.equals(sheetName)
                || REPORT_SHEET.equals(sheetName)
                || JOURNAL_CSV_SHEET.equals(sheetName)
                || REPORT_CSV_SHEET.equals(sheetName)
                || METRIC_CSV_SHEET.equals(sheetName);
    }

    private void initializeJournalRules() {
        if ("Journal rule source".equals(readCell(journalSpreadsheet, POLICY_SHEET, RULE_START_ROW, 0))) {
            return;
        }
        Sheet sheet = journalSpreadsheet.getBook().getSheet(POLICY_SHEET);
        if (sheet == null) {
            return;
        }
        for (int row = 0; row < DEFAULT_JOURNAL_RULE_ROWS.length; row++) {
            for (int col = 0; col < DEFAULT_JOURNAL_RULE_ROWS[row].length; col++) {
                writeCellText(sheet, RULE_START_ROW + row, col, DEFAULT_JOURNAL_RULE_ROWS[row][col]);
            }
        }
        Ranges.range(sheet, RULE_START_ROW, 0, RULE_START_ROW + DEFAULT_JOURNAL_RULE_ROWS.length + 2, 4).notifyChange();
    }

    private PolicyInput readPolicyInput() {
        return new PolicyInput(
                readCell(journalSpreadsheet, POLICY_SHEET, 2, 1),
                readCell(journalSpreadsheet, POLICY_SHEET, 3, 1),
                readAmount(journalSpreadsheet, POLICY_SHEET, 4, 1),
                readAmount(journalSpreadsheet, POLICY_SHEET, 5, 1),
                readAmount(journalSpreadsheet, POLICY_SHEET, 6, 1),
                readAmount(journalSpreadsheet, POLICY_SHEET, 7, 1));
    }

    private List<JournalRule> readJournalRules() {
        List<JournalRule> rules = new ArrayList<JournalRule>();
        for (int row = RULE_START_ROW + 2; row < RULE_START_ROW + 20; row++) {
            String event = readCell(journalSpreadsheet, POLICY_SHEET, row, 0);
            String description = readCell(journalSpreadsheet, POLICY_SHEET, row, 1);
            String debitMetric = readCell(journalSpreadsheet, POLICY_SHEET, row, 2);
            String creditMetric = readCell(journalSpreadsheet, POLICY_SHEET, row, 3);
            if (event.isEmpty() && description.isEmpty() && debitMetric.isEmpty() && creditMetric.isEmpty()) {
                continue;
            }
            if (!isJournalEvent(event) || !isJournalMetric(debitMetric) || !isJournalMetric(creditMetric)) {
                continue;
            }
            rules.add(new JournalRule(event, description, debitMetric, creditMetric));
        }
        return rules;
    }

    private boolean isJournalEvent(String event) {
        return event != null && event.matches("I\\d{6,7}[A-Z]?");
    }

    private boolean isJournalMetric(String metricName) {
        return "principal_base".equals(metricName)
                || "service_fee_base".equals(metricName)
                || "account_value_base".equals(metricName)
                || "monthly_revenue".equals(metricName)
                || "fair_value_movement".equals(metricName)
                || "abs_fair_value_movement".equals(metricName);
    }

    private Object[][] buildJournalRows(List<JournalRule> rules, Map<String, Double> metrics) {
        Object[][] rows = new Object[rules.size() + 1][4];
        rows[0] = new Object[]{"Event", "Description", "Debit", "Credit"};
        for (int index = 0; index < rules.size(); index++) {
            JournalRule rule = rules.get(index);
            rows[index + 1] = new Object[]{rule.event, rule.description, metricValue(metrics, rule.debitMetric), metricValue(metrics, rule.creditMetric)};
        }
        return rows;
    }

    private double metricValue(Map<String, Double> metrics, String metricName) {
        Double value = metrics.get(metricName);
        return value == null ? 0 : value;
    }

    private void writeCellText(Sheet sheet, int row, int col, Object value) {
        Ranges.range(sheet, row, col).setCellEditText(value == null ? "" : String.valueOf(value));
    }

    private void writeSheetTable(String sheetName, Object[][] values) {
        Sheet sheet = journalSpreadsheet.getBook().getSheet(sheetName);
        if (sheet == null) {
            return;
        }
        Ranges.range(sheet, 0, 0, 40, 10).clearContents();
        for (int row = 0; row < values.length; row++) {
            for (int col = 0; col < values[row].length; col++) {
                Object value = values[row][col];
                if (value instanceof Number) {
                    Ranges.range(sheet, row, col).setCellValue(value);
                } else {
                    writeCellText(sheet, row, col, value);
                }
            }
        }
        Ranges.range(sheet, 0, 0, 40, 10).notifyChange();
    }

    private void writeCsvSheet(String sheetName, Object[][] values) {
        writeSheetTable(sheetName, values);
    }

    private void applyPolicyStyles() {
        Sheet sheet = journalSpreadsheet == null ? null : journalSpreadsheet.getBook().getSheet(POLICY_SHEET);
        if (sheet == null) {
            return;
        }
        applyPolicyColumnWidths(sheet);
        styleCell(sheet, 0, 0, "#E0E7FF", "#172033", true);
        for (int row = 2; row <= 13; row++) {
            styleCell(sheet, row, 0, "#DBEAFE", "#172033", true);
            styleCell(sheet, row, 1, "#DBEAFE", "#172033", false);
        }
        for (int row = RULE_START_ROW; row < RULE_START_ROW + DEFAULT_JOURNAL_RULE_ROWS.length; row++) {
            for (int col = 0; col < 4; col++) {
                styleCell(sheet, row, col, row == RULE_START_ROW + 1 ? "#E0E7FF" : "#F5F3FF", "#172033", row <= RULE_START_ROW + 1);
            }
        }
        Ranges.range(sheet, 0, 0, RULE_START_ROW + DEFAULT_JOURNAL_RULE_ROWS.length + 1, 4).notifyChange();
    }

    private void applyPolicyColumnWidths(Sheet sheet) {
        journalSpreadsheet.setAutofitColumnWidth(sheet.getInternalSheet(), 0, 3);
        setColumnWidth(sheet, 0, 185);
        setColumnWidth(sheet, 1, 145);
        setColumnWidth(sheet, 2, 145);
        setColumnWidth(sheet, 3, 145);
    }

    private void applyOutputStyles() {
        Sheet journalSheet = journalSpreadsheet.getBook().getSheet(JOURNAL_SHEET);
        if (journalSheet != null) {
            applySheetColumnWidths(journalSheet, 0, 3);
            for (int col = 0; col < 4; col++) {
                styleCell(journalSheet, 0, col, "#FEF3C7", "#172033", true);
            }
            for (int row = 1; row <= 8; row++) {
                styleCell(journalSheet, row, 2, "#FEF3C7", "#172033", false);
                styleCell(journalSheet, row, 3, "#FEF3C7", "#172033", false);
            }
            Ranges.range(journalSheet, 0, 0, 10, 4).notifyChange();
        }

        Sheet reportSheet = journalSpreadsheet.getBook().getSheet(REPORT_SHEET);
        if (reportSheet != null) {
            applySheetColumnWidths(reportSheet, 0, 3);
            for (int col = 0; col < 4; col++) {
                styleCell(reportSheet, 0, col, "#E0E7FF", "#172033", true);
            }
            for (int row = 1; row <= 6; row++) {
                styleCell(reportSheet, row, 1, "#DBEAFE", "#172033", false);
                styleCell(reportSheet, row, 2, "#DCFCE7", "#172033", false);
                styleCell(reportSheet, row, 3, "#FEE2E2", "#172033", false);
            }
            Ranges.range(reportSheet, 0, 0, 8, 4).notifyChange();
        }

        Sheet metricSheet = journalSpreadsheet.getBook().getSheet(METRIC_CSV_SHEET);
        if (metricSheet != null) {
            applySheetColumnWidths(metricSheet, 0, 1);
            for (int row = 0; row <= 6; row++) {
                styleCell(metricSheet, row, 0, row == 0 ? "#E0E7FF" : "#DCFCE7", "#172033", row == 0);
                styleCell(metricSheet, row, 1, row == 0 ? "#E0E7FF" : "#DCFCE7", "#172033", row == 0);
            }
            Ranges.range(metricSheet, 0, 0, 8, 2).notifyChange();
        }
        applyCsvSheetStyles(JOURNAL_CSV_SHEET, 4, "#FEF3C7");
        applyCsvSheetStyles(REPORT_CSV_SHEET, 4, "#E0E7FF");
        journalSpreadsheet.invalidate();
    }

    private void applyCsvSheetStyles(String sheetName, int columns, String headerColor) {
        Sheet sheet = journalSpreadsheet.getBook().getSheet(sheetName);
        if (sheet == null) {
            return;
        }
        applySheetColumnWidths(sheet, 0, columns - 1);
        for (int col = 0; col < columns; col++) {
            styleCell(sheet, 0, col, headerColor, "#172033", true);
        }
        Ranges.range(sheet, 0, 0, 10, columns).notifyChange();
    }

    private void applySheetColumnWidths(Sheet sheet, int startCol, int endCol) {
        journalSpreadsheet.setAutofitColumnWidth(sheet.getInternalSheet(), startCol, endCol);
    }

    private void setColumnWidth(Sheet sheet, int col, int width) {
        Ranges.range(sheet, 0, col).toColumnRange().setColumnWidth(width);
    }

    private void styleCell(Sheet sheet, int row, int col, String fillColor, String fontColor, boolean bold) {
        Range cell = Ranges.range(sheet, row, col);
        Range.CellStyleHelper helper = cell.getCellStyleHelper();
        CellStyle style = helper.builder(cell.getCellStyle())
                .fillColor(helper.createColorFromHtmlColor(fillColor))
                .backColor(helper.createColorFromHtmlColor(fillColor))
                .fillPattern(CellStyle.FillPattern.SOLID)
                .fontColor(helper.createColorFromHtmlColor(fontColor))
                .fontBoldweight(bold ? Font.Boldweight.BOLD : Font.Boldweight.NORMAL)
                .wrapText(true)
                .build();
        cell.setCellStyle(style);
    }

    private String readCell(Spreadsheet spreadsheet, String sheetName, int row, int col) {
        Sheet sheet = spreadsheet.getBook().getSheet(sheetName);
        if (sheet == null) {
            return "";
        }
        Range cell = Ranges.range(sheet, row, col);
        String value = cell.getCellEditText();
        if (value != null && value.trim().startsWith("=")) {
            Object cellValue = cell.getCellValue();
            value = cellValue == null ? "" : String.valueOf(cellValue);
        } else if (value == null || value.trim().isEmpty()) {
            Object cellValue = cell.getCellValue();
            value = cellValue == null ? "" : String.valueOf(cellValue);
        }
        return value == null ? "" : value.trim();
    }

    private double readAmount(Spreadsheet spreadsheet, String sheetName, int row, int col) {
        return parseAmount(readCell(spreadsheet, sheetName, row, col));
    }

    private double parseAmount(String text) {
        String value = (text == null ? "" : text).replace(",", "");
        if (value.isEmpty()) {
            return 0;
        }
        try {
            return Double.parseDouble(value);
        } catch (NumberFormatException e) {
            return 0;
        }
    }

    private String formatAmount(double value) {
        return String.format("%,.0f", value);
    }

    private void setLabel(Label label, String value) {
        if (label != null) {
            label.setValue(value);
        }
    }

    private static class JournalRule {
        private final String event;
        private final String description;
        private final String debitMetric;
        private final String creditMetric;

        private JournalRule(String event, String description, String debitMetric, String creditMetric) {
            this.event = event;
            this.description = description;
            this.debitMetric = debitMetric;
            this.creditMetric = creditMetric;
        }
    }

    private static class PolicyInput {
        private final String policyNo;
        private final String currency;
        private final double premiumTotal;
        private final double serviceFee;
        private final double accountValue;
        private final double fxRate;

        private PolicyInput(String policyNo, String currency, double premiumTotal, double serviceFee,
                            double accountValue, double fxRate) {
            this.policyNo = policyNo;
            this.currency = currency;
            this.premiumTotal = premiumTotal;
            this.serviceFee = serviceFee;
            this.accountValue = accountValue;
            this.fxRate = fxRate;
        }
    }
}