import { describe, expect, it } from 'vitest';
import type { CanonicalAssessmentModel } from '../../src/domain/canonical-model';
import { canonicalModelToDataset, canonicalModelsToDataset, pingCastleSectionSheetId } from '../../src/features/project-import/canonical-model-to-dataset';
import { linkResultSheets } from '../../src/profiles/purple-knight/result-linker';
import { linkPingCastleSectionSheets, pingCastleSectionIds } from '../../src/profiles/pingcastle/pingcastle-section-links';

const timestamp = '2026-07-18T08:00:00.000Z';

function model(): CanonicalAssessmentModel {
  return {
    schemaVersion: 1,
    project: { id: 'project-1', name: 'Test', createdAt: timestamp, updatedAt: timestamp, attributes: {} },
    assessment: { id: 'assessment-1', projectId: 'project-1', name: 'Assessment', createdAt: timestamp, updatedAt: timestamp },
    scanRuns: [{ id: 'run-1', assessmentId: 'assessment-1', sequence: 1, label: 'Import 1', tool: { id: 'purple-knight', name: 'PurpleKnight' }, importedAt: timestamp }],
    importFiles: [{
      id: 'file-1', scanRunId: 'run-1', fileName: 'Indicators_results.csv', format: 'CSV', sizeBytes: 100,
      importedAt: timestamp, detectedTool: { id: 'purple-knight', name: 'PurpleKnight' },
      detection: { level: 'high', score: .9, reasons: [] },
      processedWith: { parserId: 'csv', parserVersion: '1', profileId: 'purple-knight', profileVersion: '1', mappingVersion: '1' },
      sourceArtifact: { kind: 'not-retained', reason: 'test' }
    }],
    sourceTables: [{ id: 'table-1', importFileId: 'file-1', name: 'Indicators', index: 0, rowCount: 1, columnCount: 2, attributes: {} }],
    findingDefinitions: [{ id: 'definition-1', canonicalKey: 'pk:1', title: 'Finding', frameworkReferences: [], createdAt: timestamp, updatedAt: timestamp }],
    findingOccurrences: [{
      id: 'occurrence-1', definitionId: 'definition-1', scanRunId: 'run-1', importFileId: 'file-1', sourceTableId: 'table-1',
      sourceIdentity: { tool: { id: 'purple-knight', name: 'PurpleKnight' }, originalRuleId: 'PK-1', originalTitle: 'Finding' },
      sourcePosition: { rowIndex: 0 },
      originalFields: [{ key: 'rule', label: 'Rule ID', value: 'PK-1', columnIndex: 0 }, { key: 'title', label: 'Title', value: 'Finding', columnIndex: 1 }],
      fieldMappings: [],
      severity: { originalValue: 'High', normalizedValue: 'high', provenance: { decisionSource: 'automatic', confidence: { level: 'high', score: .9, reasons: [] }, mappingVersion: '1' } },
      status: { originalValue: 'Open', normalizedValue: 'open', provenance: { decisionSource: 'automatic', confidence: { level: 'high', score: .9, reasons: [] }, mappingVersion: '1' } }
    }],
    evidence: [], reviews: [], diagnostics: []
  };
}

describe('canonical project loading for Work', () => {
  it('reconstructs a usable dataset from a stored assessment model', () => {
    const dataset = canonicalModelToDataset(model());
    expect(dataset).toMatchObject({ fileName: 'Test · All Scans', scanTool: 'PurpleKnight', profileId: 'purple-knight' });
    expect(dataset?.sheets[0].analysis.records).toHaveLength(1);
    expect(dataset?.sheets[0].analysis.records[0].title).toBe('Finding');
    expect(dataset?.sheets[0].analysis.headers).toEqual(['Source: File', 'Source: Sheet', 'Source: Scan', 'Rule ID', 'Title', 'Severity']);
    expect(dataset?.sheets[0].analysis.records[0].fields.find(field => field.label === 'Severity')?.value).toBe('high');
  });

  it('reconstructs readable CIS-CAT details and exact source-table links from structured originals', () => {
    const stored = model();
    stored.scanRuns[0].tool = { id: 'cis-cat', name: 'CIS-CAT Pro' };
    stored.importFiles[0] = {
      ...stored.importFiles[0],
      fileName: 'CIS-CAT-SYNTHETIC.html',
      format: 'HTML',
      detectedTool: { id: 'cis-cat', name: 'CIS-CAT Pro' },
      processedWith: {
        ...stored.importFiles[0].processedWith,
        profileId: 'cis-cat',
        profileVersion: '1.2.0',
        mappingVersion: '1.1.0'
      }
    };
    stored.sourceTables[0] = { ...stored.sourceTables[0], name: 'assessmentResultTable', index: 0 };
    stored.sourceTables.push({
      id: 'table-evidence', importFileId: 'file-1', name: 'Synthetic Item', index: 1, rowCount: 1, columnCount: 2,
      attributes: {},
      rows: [{
        index: 1,
        fields: [
          { key: 'column-0', label: 'Name', value: 'Setting', columnIndex: 0 },
          { key: 'column-1', label: 'Value', value: 'Disabled', columnIndex: 1 }
        ]
      }]
    });
    stored.findingOccurrences[0] = {
      ...stored.findingOccurrences[0],
      sourceIdentity: {
        tool: { id: 'cis-cat', name: 'CIS-CAT Pro' },
        originalRuleId: '1.1.1',
        canonicalRuleId: '1.1.1',
        originalTitle: '1.1.1 Ensure the synthetic setting is enabled'
      },
      originalFields: [
        { key: 'rule-id', label: 'Rule ID', value: '1.1.1', columnIndex: 0 },
        { key: 'benchmark-item', label: 'Benchmark Item', value: '1.1.1 Ensure the synthetic setting is enabled', columnIndex: 1 },
        { key: 'result', label: 'Result', value: 'Fail', columnIndex: 2 },
        {
          key: 'results', label: 'Results',
          value: { fragmentId: 'detail-synthetic', tables: [{ tableIndex: 1, label: 'Assessment Evidence · Synthetic Item' }] },
          rawValue: 'source-table:1 "Assessment Evidence · Synthetic Item"'
        },
        {
          key: 'rule-result-xml', label: 'Rule Result XML',
          value: { fragmentId: 'detail-synthetic', ruleResultXml: '<result>fail</result>' },
          rawValue: '```xml\n<result>fail</result>\n```'
        }
      ]
    };

    const dataset = canonicalModelToDataset(stored)!;
    const findingSheet = dataset.sheets.find(sheet => sheet.sourceSheet === 'assessmentResultTable')!;
    const finding = findingSheet.analysis.records[0];
    const field = (label: string) => finding.fields.find(item => item.label === label)?.value;

    expect(field('Results')).toBe('source-table:1 "Assessment Evidence · Synthetic Item"');
    expect(field('Rule Result XML')).toContain('<result>fail</result>');
    expect(field('Rule Result XML')).not.toContain('"fragmentId"');
    expect(linkResultSheets(finding, dataset.sheets, findingSheet.id)).toMatchObject([
      { name: 'Assessment Evidence · Synthetic Item', sourceTableIndex: 1 }
    ]);
  });
  /**
   * Regression: CIS-CAT markiert in einer Belegtabelle die konkret
   * ausgewertete Zeile selbst (`<tr class="evaluated">`). Diese Markierung
   * wurde beim Import erkannt, aber beim Aufbau der persistierten
   * Quelltabelle (`SourceTableRow`) verworfen - jede spaeter aus der
   * Datenbank geladene Projektansicht zeigte deshalb keine Markierung mehr,
   * obwohl der frische Import sie noch hatte.
   */
  it('bewahrt die vom Bericht markierte Zeile ueber die persistierte Quelltabelle hinweg', () => {
    const stored = model();
    stored.sourceTables[0] = { ...stored.sourceTables[0], name: 'assessmentResultTable', index: 0 };
    stored.sourceTables.push({
      id: 'table-evidence', importFileId: 'file-1', name: 'Registry Item', index: 1, rowCount: 2, columnCount: 2,
      attributes: {},
      rows: [
        {
          index: 0,
          fields: [
            { key: 'column-0', label: 'Name', value: 'Hive', columnIndex: 0 },
            { key: 'column-1', label: 'Value', value: 'HKEY_LOCAL_MACHINE', columnIndex: 1 }
          ],
          highlighted: false
        },
        {
          index: 1,
          fields: [
            { key: 'column-0', label: 'Name', value: 'Value', columnIndex: 0 },
            { key: 'column-1', label: 'Value', value: '5', columnIndex: 1 }
          ],
          highlighted: true
        }
      ]
    });

    const dataset = canonicalModelToDataset(stored)!;
    const evidenceSheet = dataset.sheets.find(sheet => sheet.sourceSheet === 'Registry Item')!;

    expect(evidenceSheet.analysis.records.map(record => ({
      name: record.fields.find(field => field.label === 'Name')?.value,
      highlighted: record.highlighted ?? false
    }))).toEqual([
      { name: 'Hive', highlighted: false },
      { name: 'Value', highlighted: true }
    ]);
  });

  it('combines synthetic CSV and Excel assessments from the same tenant', () => {
    const csvModel = model();
    const excelModel = model();
    excelModel.assessment = { ...excelModel.assessment, id: 'assessment-2', name: 'Synthetic Excel assessment' };
    excelModel.scanRuns = [{ ...excelModel.scanRuns[0], id: 'run-2', assessmentId: 'assessment-2', importedAt: '2026-07-17T08:00:00.000Z' }];
    excelModel.importFiles = [{ ...excelModel.importFiles[0], id: 'file-2', scanRunId: 'run-2', fileName: 'synthetic-history.xlsx', format: 'Excel' }];
    excelModel.sourceTables = [{ ...excelModel.sourceTables[0], id: 'table-2', importFileId: 'file-2', name: 'Synthetic History' }];
    excelModel.findingDefinitions = [{ ...excelModel.findingDefinitions[0], id: 'definition-2', canonicalKey: 'pk:2', title: 'Synthetic historical finding' }];
    excelModel.findingOccurrences = [{
      ...excelModel.findingOccurrences[0],
      id: 'occurrence-2',
      definitionId: 'definition-2',
      scanRunId: 'run-2',
      importFileId: 'file-2',
      sourceTableId: 'table-2',
      sourceIdentity: { ...excelModel.findingOccurrences[0].sourceIdentity, originalRuleId: 'PK-2', originalTitle: 'Synthetic historical finding' },
      originalFields: [
        { key: 'rule', label: 'Rule ID', value: 'PK-2', columnIndex: 0 },
        { key: 'title', label: 'Title', value: 'Synthetic historical finding', columnIndex: 1 }
      ]
    }];

    const dataset = canonicalModelsToDataset([csvModel, excelModel]);
    expect(dataset).toMatchObject({ fileName: 'Test · All Scans', scanTool: 'PurpleKnight' });
    expect(dataset?.sheets[0].analysis.records).toHaveLength(2);
    expect(dataset?.sheets).toHaveLength(3);
    expect(dataset?.sheets.some(sheet => sheet.sourceFile === 'synthetic-history.xlsx')).toBe(true);
  });
  it('restores persisted PurpleKnight result tables and links them to the finding', () => {
    const stored = model();
    stored.findingOccurrences[0].originalFields.push({
      key: 'result', label: 'Result', value: 'SI000055', columnIndex: 2
    });
    stored.sourceTables.push({
      id: 'table-results', importFileId: 'file-1', name: 'SI000055', index: 1, rowCount: 1, columnCount: 4,
      attributes: { sourceFile: 'assessment.xlsx', sourceSheet: 'SI000055' },
      rows: [{
        index: 1,
        fields: [
          { key: 'column-0', label: 'Attribute', value: 'nTSecurityDescriptor', columnIndex: 0 },
          { key: 'column-1', label: 'DistinguishedName', value: 'CN=Synthetic', columnIndex: 1 },
          { key: 'column-2', label: 'EventTimestamp', value: '2026-07-18T08:00:00Z', columnIndex: 2 },
          { key: 'column-3', label: 'Ignored', value: false, columnIndex: 3 }
        ]
      }]
    });

    const dataset = canonicalModelToDataset(stored)!;
    const findingSheet = dataset.sheets.find(sheet => sheet.sourceSheet === 'Indicators')!;
    const resultSheet = dataset.sheets.find(sheet => sheet.sourceSheet === 'SI000055')!;

    expect(resultSheet.analysis.records).toHaveLength(1);
    expect(linkResultSheets(findingSheet.analysis.records[0], dataset.sheets, findingSheet.id)).toEqual([resultSheet]);
  });
  it('creates a selectable file view for native findings without a source table', () => {
    const native = model();
    native.scanRuns = [{
      ...native.scanRuns[0],
      tool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    native.importFiles = [{
      ...native.importFiles[0],
      fileName: 'ad_hc_synthetic.example.html',
      format: 'HTML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' },
      processedWith: {
        ...native.importFiles[0].processedWith,
        parserId: 'html',
        profileId: 'pingcastle'
      }
    }];
    native.sourceTables = [];
    native.findingOccurrences = [{
      ...native.findingOccurrences[0],
      sourceTableId: undefined,
      sourcePosition: { objectPath: 'html/rules/S-Synthetic' },
      sourceIdentity: {
        tool: { id: 'pingcastle', name: 'PingCastle' },
        originalRuleId: 'S-Synthetic',
        originalTitle: 'Synthetic PingCastle finding'
      }
    }];

    const dataset = canonicalModelToDataset(native);

    expect(dataset?.sheets).toHaveLength(2);
    expect(dataset?.sheets[1]).toMatchObject({
      name: 'PingCastle · 2026-07-18 · HTML',
      sourceFile: 'ad_hc_synthetic.example.html',
      sourceSheet: 'HTML'
    });
    expect(dataset?.sheets[1].analysis.records).toHaveLength(1);
  });

  it('keeps PingCastle HTML and XML findings separate even when file stem and Rule ID match', () => {
    const html = model();
    html.scanRuns = [{ ...html.scanRuns[0], tool: { id: 'pingcastle', name: 'PingCastle' } }];
    html.importFiles = [{
      ...html.importFiles[0],
      fileName: 'ad_hc_synthetic.example.html',
      format: 'HTML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    html.sourceTables = [];
    html.findingOccurrences = [{
      ...html.findingOccurrences[0],
      sourceTableId: undefined,
      sourceIdentity: {
        tool: { id: 'pingcastle', name: 'PingCastle' },
        originalRuleId: 'S-Synthetic',
        canonicalRuleId: 'S-Synthetic',
        originalTitle: 'Synthetic finding'
      },
      originalFields: [
        { key: 'risk-id', label: 'Rule ID', value: 'S-Synthetic' },
        { key: 'title', label: 'Title', value: 'Synthetic finding' },
        { key: 'description', label: 'Description', value: 'Synthetic manufacturer description.' },
        { key: 'technical', label: 'Technical explanation', value: 'Synthetic technical explanation.' },
        { key: 'solution', label: 'Advised solution', value: 'Synthetic remediation.' },
        { key: 'documentation', label: 'Documentation', value: 'https://example.invalid/rule' },
        {
          key: 'linked-section',
          label: 'Verwandte Daten',
          value: {
            sections: [{
              sectionId: 'syntheticsection',
              sectionLabel: 'Synthetic Section',
              prose: '',
              headers: ['Name'],
              rows: [['Synthetic Evidence']]
            }]
          }
        }
      ]
    }];

    const xml = model();
    xml.assessment = { ...xml.assessment, id: 'assessment-xml' };
    xml.scanRuns = [{
      ...xml.scanRuns[0],
      id: 'run-xml',
      assessmentId: 'assessment-xml',
      tool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    xml.importFiles = [{
      ...xml.importFiles[0],
      id: 'file-xml',
      scanRunId: 'run-xml',
      fileName: 'ad_hc_synthetic.example.xml',
      format: 'XML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    xml.sourceTables = [];
    xml.findingOccurrences = [{
      ...xml.findingOccurrences[0],
      id: 'occurrence-xml',
      scanRunId: 'run-xml',
      importFileId: 'file-xml',
      sourceTableId: undefined,
      sourceIdentity: {
        tool: { id: 'pingcastle', name: 'PingCastle' },
        originalRuleId: 'S-Synthetic',
        canonicalRuleId: 'S-Synthetic',
        originalTitle: 'Synthetic finding'
      },
      originalFields: [
        { key: 'risk-id', label: 'Rule ID', value: 'S-Synthetic' },
        { key: 'title', label: 'Title', value: 'Synthetic finding' },
        { key: 'points', label: 'Points', value: '20' }
      ]
    }];

    const dataset = canonicalModelsToDataset([html, xml]);
    const xmlSheet = dataset?.sheets.find(sheet => sheet.sourceFile === 'ad_hc_synthetic.example.xml');
    const field = (label: string) => xmlSheet?.analysis.records[0].fields.find(item => item.label === label)?.value;

    expect(field('Points')).toBe('20');
    expect(field('Description')).toBeUndefined();
    expect(field('Technical explanation')).toBeUndefined();
    expect(field('Advised solution')).toBeUndefined();
    expect(field('Documentation')).toBeUndefined();
    expect(field('Rule Text Source')).toBeUndefined();
    expect(field('Verwandte Daten')).toBeUndefined();
    const xmlRecord = xmlSheet!.analysis.records[0];
    expect(linkPingCastleSectionSheets(xmlRecord, dataset!.sheets)).toEqual([]);
    const htmlSheet = dataset?.sheets.find(sheet => sheet.sourceFile === 'ad_hc_synthetic.example.html');
    const htmlRecord = htmlSheet!.analysis.records[0];
    expect(linkPingCastleSectionSheets(htmlRecord, dataset!.sheets)).toHaveLength(1);
    expect(xml.findingOccurrences[0].originalFields).toHaveLength(3);
  });
  it('keeps equal PingCastle section IDs isolated between import files', () => {
    const first = model();
    first.scanRuns = [{ ...first.scanRuns[0], tool: { id: 'pingcastle', name: 'PingCastle' } }];
    first.importFiles = [{
      ...first.importFiles[0],
      fileName: 'ad_hc_synthetic.example.html',
      format: 'HTML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    first.sourceTables = [];
    first.findingOccurrences = [{
      ...first.findingOccurrences[0],
      sourceTableId: undefined,
      sourceIdentity: { tool: { id: 'pingcastle', name: 'PingCastle' }, originalRuleId: 'S-First', originalTitle: 'First synthetic finding' },
      originalFields: [
        { key: 'risk-id', label: 'Rule ID', value: 'S-First' },
        { key: 'title', label: 'Title', value: 'First synthetic finding' },
        { key: 'details', label: 'Details', value: 'The detail can be found in Synthetic Section' },
        {
          key: 'linked-section',
          label: 'Verwandte Daten',
          value: {
            sections: [{
              sectionId: 'syntheticsection',
              sectionLabel: 'Synthetic Section',
              prose: '',
              headers: ['Name'],
              rows: [['Synthetic Entry']]
            }]
          }
        }
      ]
    }];

    const second = model();
    second.assessment = { ...second.assessment, id: 'assessment-2' };
    second.scanRuns = [{ ...second.scanRuns[0], id: 'run-2', assessmentId: 'assessment-2', tool: { id: 'pingcastle', name: 'PingCastle' } }];
    second.importFiles = [{
      ...second.importFiles[0],
      id: 'file-2',
      scanRunId: 'run-2',
      fileName: 'ad_hc_synthetic.example.html',
      format: 'HTML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    second.sourceTables = [];
    second.findingOccurrences = [{
      ...second.findingOccurrences[0],
      id: 'occurrence-2',
      scanRunId: 'run-2',
      importFileId: 'file-2',
      sourceTableId: undefined,
      sourceIdentity: { tool: { id: 'pingcastle', name: 'PingCastle' }, originalRuleId: 'S-Second', originalTitle: 'Second synthetic finding' },
      originalFields: [
        { key: 'risk-id', label: 'Rule ID', value: 'S-Second' },
        { key: 'title', label: 'Title', value: 'Second synthetic finding' },
        { key: 'details', label: 'Details', value: 'The detail can be found in Synthetic Section' },
        {
          key: 'linked-section',
          label: 'Verwandte Daten',
          value: {
            sections: [{
              sectionId: 'syntheticsection',
              sectionLabel: 'Synthetic Section',
              prose: '',
              headers: ['Name'],
              rows: [['Synthetic Entry']]
            }]
          }
        }
      ]
    }];

    const dataset = canonicalModelsToDataset([first, second])!;
    const expectedReferences = new Map([
      ['S-First', 'file-1::syntheticsection'],
      ['S-Second', 'file-2::syntheticsection']
    ]);
    const sectionSheets = dataset.sheets.filter(sheet =>
      [...expectedReferences.values()].map(pingCastleSectionSheetId).includes(sheet.id)
    );
    expect(sectionSheets).toHaveLength(2);

    for (const [ruleId, referenceId] of expectedReferences) {
      const sheet = dataset.sheets.find(item => item.analysis.records.some(record => record.fields.find(field => field.label === 'Rule ID')?.value === ruleId))!;
      const record = sheet.analysis.records.find(item => item.fields.find(field => field.label === 'Rule ID')?.value === ruleId)!;
      expect(pingCastleSectionIds(record)).toEqual([referenceId]);
      expect(linkPingCastleSectionSheets(record, dataset.sheets).map(item => item.id)).toEqual([
        pingCastleSectionSheetId(referenceId)
      ]);
      expect(record.fields.find(field => field.label === 'Verwandte Daten')?.value).toBe(referenceId);
    }
  });

  it('renders a prose-only section sheet when the resolved target table has no data rows', () => {
    const stored = model();
    stored.scanRuns = [{ ...stored.scanRuns[0], tool: { id: 'pingcastle', name: 'PingCastle' } }];
    stored.importFiles = [{
      ...stored.importFiles[0],
      fileName: 'ad_hc_synthetic.example.html',
      format: 'HTML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    stored.sourceTables = [];
    stored.findingOccurrences = [{
      ...stored.findingOccurrences[0],
      sourceTableId: undefined,
      sourceIdentity: { tool: { id: 'pingcastle', name: 'PingCastle' }, originalRuleId: 'S-EmptyTable', originalTitle: 'Synthetic empty-table finding' },
      originalFields: [
        { key: 'risk-id', label: 'Rule ID', value: 'S-EmptyTable' },
        { key: 'title', label: 'Title', value: 'Synthetic empty-table finding' },
        {
          key: 'linked-section',
          label: 'Verwandte Daten',
          value: {
            sections: [{
              sectionId: 'emptysection',
              sectionLabel: 'Empty Section',
              prose: 'Synthetic explanation because no occurrences were found.',
              headers: ['Policy Name', 'Setting', 'Value'],
              rows: []
            }]
          }
        }
      ]
    }];

    const dataset = canonicalModelToDataset(stored)!;
    const sheet = dataset.sheets.find(item => item.id === pingCastleSectionSheetId('file-1::emptysection'))!;
    expect(sheet.analysis.records).toHaveLength(1);
    expect(sheet.analysis.records[0].fields.find(field => field.label === 'Inhalt')?.value)
      .toBe('Synthetic explanation because no occurrences were found.');
    expect(sheet.resultPreamble).toBeUndefined();
  });

  it('keeps the finding-specific PingCastle detail order and projects prose before table sheets', () => {
    const stored = model();
    stored.scanRuns = [{ ...stored.scanRuns[0], tool: { id: 'pingcastle', name: 'PingCastle' } }];
    stored.importFiles = [{
      ...stored.importFiles[0],
      fileName: 'ad_hc_synthetic.example.html',
      format: 'HTML',
      detectedTool: { id: 'pingcastle', name: 'PingCastle' }
    }];
    stored.sourceTables = [];
    stored.findingOccurrences = [{
      ...stored.findingOccurrences[0],
      sourceTableId: undefined,
      sourceIdentity: { tool: { id: 'pingcastle', name: 'PingCastle' }, originalRuleId: 'S-Ordered', originalTitle: 'Ordered synthetic finding' },
      originalFields: [{
        key: 'linked-section',
        label: 'Verwandte Daten',
        value: {
          sections: [
            { sectionId: 'overview', sectionLabel: 'Admin Groups', prose: 'Synthetic group explanation.', headers: ['Group'], rows: [['Administrators']] },
            { sectionId: 'members', sectionLabel: 'Admin Groups → Administrators', prose: 'Administrators Close', headers: ['Account'], rows: [['Synthetic Admin']] }
          ]
        }
      }]
    }];

    const dataset = canonicalModelToDataset(stored)!;
    const findingSheet = dataset.sheets.find(item => item.sourceFile === 'ad_hc_synthetic.example.html')!;
    const record = findingSheet.analysis.records[0];
    const globallyReversed = [...dataset.sheets].reverse();
    const linked = linkPingCastleSectionSheets(record, globallyReversed);

    expect(linked.map(item => item.name)).toEqual([
      'PingCastle · Admin Groups',
      'PingCastle · Admin Groups → Administrators'
    ]);
    expect(linked[0].resultPreamble).toBe('Synthetic group explanation.');
    expect(linked[1].resultPreamble).toBeUndefined();
  });

  /**
   * Regression: Blatt-IDs entstanden allein aus Dateiname und Blattname. Ein
   * Bericht traegt dieselbe Tabelle aber mehrfach (PingCastle legt "Account
   * list" je Regel erneut an), und derselbe Dateiname erscheint in mehreren
   * Scanlaeufen. Die Blaetter bekamen dadurch dieselbe ID: React meldete
   * doppelte Keys in Blattauswahl und Ergebnisfenster, und die Blattauswahl
   * haette auf mehrere Blaetter zugleich gezeigt.
   */
  it('vergibt eindeutige Blatt-IDs fuer gleichnamige Tabellen derselben Datei', () => {
    const stored = model();
    stored.sourceTables = [
      { id: 'table-a', importFileId: 'file-1', name: 'Account list', index: 1, rowCount: 1, columnCount: 2, attributes: {} },
      { id: 'table-b', importFileId: 'file-1', name: 'Account list', index: 2, rowCount: 1, columnCount: 2, attributes: {} }
    ];
    stored.findingOccurrences = ['table-a', 'table-b'].map((tableId, index) => ({
      ...stored.findingOccurrences[0],
      id: `occurrence-${tableId}`,
      sourceTableId: tableId,
      originalFields: [
        { key: 'rule', label: 'Rule ID', value: `PK-${index}`, columnIndex: 0 },
        { key: 'title', label: 'Title', value: `Finding ${index}`, columnIndex: 1 }
      ]
    }));

    const dataset = canonicalModelToDataset(stored)!;
    const accountSheets = dataset.sheets.filter(sheet => sheet.sourceSheet === 'Account list');

    expect(accountSheets).toHaveLength(2);
    expect(new Set(accountSheets.map(sheet => sheet.id)).size).toBe(2);
    expect(new Set(dataset.sheets.map(sheet => sheet.id)).size).toBe(dataset.sheets.length);
  });

  it('vergibt eindeutige Blatt-IDs fuer denselben Dateinamen in zwei Scanlaeufen', () => {
    const first = model();
    first.scanRuns = [{ ...first.scanRuns[0], tool: { id: 'pingcastle', name: 'PingCastle' } }];
    first.importFiles = [{ ...first.importFiles[0], fileName: 'ad_hc_synthetic.example.html', format: 'HTML' }];
    first.sourceTables = [];
    first.findingOccurrences = [{ ...first.findingOccurrences[0], sourceTableId: undefined }];

    const second = model();
    second.assessment = { ...second.assessment, id: 'assessment-2' };
    second.scanRuns = [{ ...first.scanRuns[0], id: 'run-2', assessmentId: 'assessment-2' }];
    second.importFiles = [{ ...first.importFiles[0], id: 'file-2', scanRunId: 'run-2' }];
    second.sourceTables = [];
    second.findingOccurrences = [{
      ...first.findingOccurrences[0],
      id: 'occurrence-2',
      scanRunId: 'run-2',
      importFileId: 'file-2',
      sourceTableId: undefined
    }];

    const dataset = canonicalModelsToDataset([first, second])!;
    const fileSheets = dataset.sheets.filter(sheet => sheet.sourceFile === 'ad_hc_synthetic.example.html');

    expect(fileSheets).toHaveLength(2);
    expect(fileSheets[0].name).toBe(fileSheets[1].name);
    expect(new Set(dataset.sheets.map(sheet => sheet.id)).size).toBe(dataset.sheets.length);
  });

  it('returns null when the stored assessment has no findings', () => {
    const empty = model();
    empty.findingOccurrences = [];
    expect(canonicalModelToDataset(empty)).toBeNull();
  });
});