"""Additive Sprint 6H-A nutrition provenance schema."""
from __future__ import annotations

import sqlite3

SCHEMA_VERSION = "sprint6h_a_nutrition_units_v1"
COLUMNS = {
    "raw_unit": "TEXT",
    "canonical_unit": "TEXT",
    "conversion_factor": "REAL",
    "value_status": "TEXT CHECK(value_status IS NULL OR value_status IN ('documented_zero','documented_value','estimated','not_reported','unknown','not_applicable'))",
    "conversion_contract_version": "TEXT",
}


def apply_schema(connection: sqlite3.Connection) -> int:
    existing = {str(row[1]) for row in connection.execute("PRAGMA table_info(nutrition_item_nutrients)")}
    if not existing:
        raise RuntimeError("nutrition_item_nutrients missing")
    changed = 0
    for name, definition in COLUMNS.items():
        if name not in existing:
            connection.execute(f'ALTER TABLE nutrition_item_nutrients ADD COLUMN "{name}" {definition}')
            changed += 1
    connection.execute(
        "CREATE INDEX IF NOT EXISTS idx_nutrition_nutrient_status ON nutrition_item_nutrients(nutrient_key,value_status)"
    )
    return changed


def assert_schema(connection: sqlite3.Connection) -> None:
    columns = {str(row[1]) for row in connection.execute("PRAGMA table_info(nutrition_item_nutrients)")}
    missing = set(COLUMNS) - columns
    if missing:
        raise RuntimeError(f"missing nutrition provenance columns: {sorted(missing)}")