End-to-end: a vendor scorecard

"""Track a vendor scorecard from creation to export.

Stages:
  1. Define the schema (status as single-select, priority as int).
  2. Load five vendors.
  3. Flag low-priority EU vendors as 'reviewing'.
  4. Drop vendors whose status is 'done'.
  5. Page through the survivors with scroll().
  6. Roll up the spend by region with aggregate().
  7. Export the table to CSV.
"""

from vectorshift.table import (
    AggregationType,
    ColumnSpec,
    CompoundFilter,
    ExportFormat,
    FilterCondition,
    NumberFormat,
    NumberKind,
    SelectOption,
    SingleSelectFormat,
    StringFormat,
    Table,
    TableFilterOperator,
)


# --- 1. Schema ---
t = Table.new(
    name="Vendor scorecard",
    columns=[
        ColumnSpec(name="vendor", format=StringFormat()),
        ColumnSpec(name="region", format=StringFormat()),
        ColumnSpec(
            name="status",
            format=SingleSelectFormat(options=[
                SelectOption(id="open",      label="Open",      color="#22aa22"),
                SelectOption(id="reviewing", label="Reviewing", color="#ffaa00"),
                SelectOption(id="done",      label="Done",      color="#888888"),
            ]),
        ),
        ColumnSpec(name="priority", format=NumberFormat(number_kind=NumberKind.INT)),
    ],
)
print(f"1. Created table id={t.id} with {len(t.columns)} columns.")

# --- 2. Seed ---
t.clear()
t.insert_rows([
    {"vendor": "Acme",  "region": "EU", "status": "open", "priority": 8},
    {"vendor": "Beta",  "region": "EU", "status": "open", "priority": 1},
    {"vendor": "Gamma", "region": "US", "status": "open", "priority": 5},
    {"vendor": "Delta", "region": "US", "status": "done", "priority": 9},
    {"vendor": "Echo",  "region": "AS", "status": "done", "priority": 2},
])
print(f"2. Loaded {t.read_rows()['total']} vendors.")

# --- 3. Flag low-priority EU vendors as 'reviewing' ---
t.update_rows(
    {"status": "reviewing"},
    filters=CompoundFilter.of(
        FilterCondition("region", TableFilterOperator.EQ, "EU"),
        FilterCondition("priority", TableFilterOperator.LT, 5),
    ),
)
reviewing = {r["values"]["vendor"]
             for r in t.read_rows(filters=CompoundFilter.of(
                 FilterCondition("status", TableFilterOperator.EQ, "reviewing"),
             ))["rows"]}
print(f"3. Flagged for review: {reviewing}")

# --- 4. Archive completed work ---
t.delete_rows(filters=CompoundFilter.of(
    FilterCondition("status", TableFilterOperator.EQ, "done"),
))
print(f"4. After archiving 'done': {t.read_rows()['total']} vendors remain.")

# --- 5. Page through what's left ---
print("5. Scrolling survivors:")
for chunk in t.scroll(page_size=2):
    for r in chunk["rows"]:
        v = r["values"]
        print(f"     - {v['vendor']:6} region={v.get('region')} status={v.get('status')} priority={v.get('priority')}")

# --- 6. Roll up spend by-priority and unique vendors ---
priority_total = t.aggregate(aggregations=[("priority", AggregationType.SUM)])
unique_vendors = t.aggregate(aggregations=[("vendor", AggregationType.UNIQUE)])
print(f"6. SUM(priority) = {priority_total['aggregations']}")
print(f"   UNIQUE(vendor) = {unique_vendors['aggregations']}")

# --- 7. Export ---
out = t.export(format=ExportFormat.CSV)
print(f"7. Exported -> filename={out['filename']!r} bytes={len(out['content'])}")

# --- Cleanup ---
t.delete()
print("Done.")

1. Created table id=... with 4 columns.
2. Loaded 5 vendors.
3. Flagged for review: {'Beta'}
4. After archiving 'done': 3 vendors remain.
5. Scrolling survivors:
     - Acme   region=EU status=open      priority=8
     - Beta   region=EU status=reviewing priority=1
     - Gamma  region=US status=open      priority=5
6. SUM(priority) = {'priority': 14}
   UNIQUE(vendor) = {'vendor': 3}
7. Exported -> filename='Vendor scorecard.csv' bytes=...
Done.