#!/usr/bin/env python3 """KAR-957 Aggregat-Layer (Master-Prompt ยง6): laedt alle profiles/*.json in eine DuckDB-Datenbank profiles/corpus.duckdb mit Tabellen workbooks, sheets, regions, capabilities, findings. Read-only auf profiles/, keine Zellwerte -- die Profile enthalten schon nur Koordinaten/Labels. """ import json import glob import sys import duckdb PROFILES_DIR = "/home/aria/work/qaf-corpus/profiles" DB_PATH = "/home/aria/work/qaf-corpus/profiles/corpus.duckdb" REGION_KEYS = [ "headerRows", "labelValueRegions", "variantRegion", "materialRegion", "manufacturingRegion", "setupCostRegion", "summaryRegion", ] def main(): con = duckdb.connect(DB_PATH) con.execute("DROP TABLE IF EXISTS workbooks") con.execute("DROP TABLE IF EXISTS sheets") con.execute("DROP TABLE IF EXISTS regions") con.execute("DROP TABLE IF EXISTS capabilities") con.execute("DROP TABLE IF EXISTS findings") con.execute( """ CREATE TABLE workbooks ( sha256_16 VARCHAR PRIMARY KEY, sha256 VARCHAR, relative_path VARCHAR, filename VARCHAR, size_bytes BIGINT, mtime VARCHAR, extension VARCHAR, detected_format VARCHAR, macro_status VARCHAR, external_links_count INTEGER, manifest_processing_status VARCHAR, profile_status VARCHAR, primary_classification VARCHAR, g60_detected BOOLEAN, multi_qaf_classification VARCHAR, standard_template_classification VARCHAR, matched_profile VARCHAR, potential_qaf_family VARCHAR, language_primary VARCHAR, de_signal_count INTEGER, en_signal_count INTEGER, formula_fingerprint VARCHAR, label_fingerprint VARCHAR, structural_fingerprint VARCHAR, extraction_confidence DOUBLE, sheet_count INTEGER, formula_count INTEGER, error_count INTEGER, merged_cells_count INTEGER, hidden_rows_count INTEGER, hidden_cols_count INTEGER, tables_count INTEGER, named_ranges_count INTEGER, multiple_currencies_detected BOOLEAN, error_message VARCHAR ) """ ) con.execute( """ CREATE TABLE sheets ( sha256_16 VARCHAR, sheet_index INTEGER, name VARCHAR, state VARCHAR, used_top INTEGER, used_left INTEGER, used_bottom INTEGER, used_right INTEGER, row_count INTEGER, column_count INTEGER, merged_cells_count INTEGER, hidden_rows_count INTEGER, hidden_cols_count INTEGER, tables_count INTEGER, data_validations_count INTEGER, formula_count INTEGER, error_count INTEGER, is_unknown_region BOOLEAN ) """ ) con.execute( """ CREATE TABLE regions ( sha256_16 VARCHAR, sheet_index INTEGER, sheet_name VARCHAR, region_type VARCHAR, confidence DOUBLE, sheet_name_signal BOOLEAN, matched_row_count INTEGER, top_row INTEGER, sample_labels VARCHAR ) """ ) con.execute( """ CREATE TABLE capabilities ( sha256_16 VARCHAR, capability_key VARCHAR, present BOOLEAN, confidence DOUBLE, label_hits_de INTEGER, label_hits_en INTEGER, sheet_count INTEGER ) """ ) con.execute( """ CREATE TABLE findings ( sha256_16 VARCHAR, finding_text VARCHAR ) """ ) workbook_rows = [] sheet_rows = [] region_rows = [] capability_rows = [] finding_rows = [] files = sorted(glob.glob(f"{PROFILES_DIR}/*.json")) print(f"Lade {len(files)} Profile...") for fp in files: with open(fp, encoding="utf-8") as f: p = json.load(f) sha16 = p.get("sha256_16") status = p.get("status") cls = p.get("classification") or {} std = cls.get("standardTemplateFingerprint") or {} multi = cls.get("multiQaf") or {} langs = p.get("languages") or {} fps = p.get("fingerprints") or {} totals = p.get("totals") or {} currencies = p.get("currencies") or {} ext_links_raw = p.get("externalLinksCount") ext_links = ext_links_raw if isinstance(ext_links_raw, int) else None workbook_rows.append( ( sha16, p.get("sha256"), p.get("relativePath"), p.get("filename"), p.get("sizeBytes"), str(p.get("mtime")), p.get("extension"), p.get("detectedFormat"), p.get("macroStatus"), ext_links, p.get("manifestProcessingStatus"), status, cls.get("primaryClassification"), cls.get("g60Detected"), multi.get("classification"), std.get("classification"), std.get("matchedProfile"), p.get("potentialQafFamily"), langs.get("primary"), langs.get("deSignalCount"), langs.get("enSignalCount"), fps.get("formulaFingerprint"), fps.get("labelFingerprint"), fps.get("structuralFingerprint"), p.get("extractionConfidence"), totals.get("sheetCount"), totals.get("formulaCount"), totals.get("errorCount"), totals.get("mergedCellsCount"), totals.get("hiddenRowsCount"), totals.get("hiddenColsCount"), totals.get("tablesCount"), totals.get("namedRangesCount"), currencies.get("multipleCurrenciesDetected"), (p.get("error") or {}).get("message") if isinstance(p.get("error"), dict) else None, ) ) for s in p.get("sheetInventory") or []: ur = s.get("usedRange") or {} sheet_rows.append( ( sha16, s.get("index"), s.get("name"), s.get("state"), ur.get("top"), ur.get("left"), ur.get("bottom"), ur.get("right"), s.get("rowCount"), s.get("columnCount"), s.get("mergedCellsCount"), s.get("hiddenRowsCount"), s.get("hiddenColsCount"), s.get("tablesCount"), s.get("dataValidationsCount"), s.get("formulaCount"), s.get("errorCount"), s.get("isUnknownRegion"), ) ) cand = s.get("candidateRegions") or {} for rk in REGION_KEYS: r = cand.get(rk) if not r: continue rows = r.get("rows") or [] top_row = rows[0]["row"] if rows else None sample_labels = ",".join(sorted({lbl for row in rows for lbl in row.get("matchedLabels", [])})[:15]) region_rows.append( ( sha16, s.get("index"), s.get("name"), rk, r.get("confidence"), r.get("sheetNameSignal"), len(rows), top_row, sample_labels, ) ) for cap_key, cap in (p.get("capabilities") or {}).items(): capability_rows.append( ( sha16, cap_key, cap.get("present"), cap.get("confidence"), cap.get("labelHitsDe"), cap.get("labelHitsEn"), len(cap.get("sheets") or []), ) ) for finding in p.get("dataQualityFindings") or []: finding_rows.append((sha16, finding)) con.executemany(f"INSERT INTO workbooks VALUES ({','.join(['?'] * 35)})", workbook_rows) con.executemany(f"INSERT INTO sheets VALUES ({','.join(['?'] * 18)})", sheet_rows) con.executemany(f"INSERT INTO regions VALUES ({','.join(['?'] * 9)})", region_rows) con.executemany(f"INSERT INTO capabilities VALUES ({','.join(['?'] * 7)})", capability_rows) con.executemany(f"INSERT INTO findings VALUES ({','.join(['?'] * 2)})", finding_rows) print("workbooks:", con.execute("SELECT count(*) FROM workbooks").fetchone()[0]) print("sheets:", con.execute("SELECT count(*) FROM sheets").fetchone()[0]) print("regions:", con.execute("SELECT count(*) FROM regions").fetchone()[0]) print("capabilities:", con.execute("SELECT count(*) FROM capabilities").fetchone()[0]) print("findings:", con.execute("SELECT count(*) FROM findings").fetchone()[0]) con.close() if __name__ == "__main__": sys.exit(main())