-- BauKalko price database schema (v1.2)
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE destatis (id INTEGER PRIMARY KEY CHECK(id=1), series TEXT, table_code TEXT, base TEXT, period TEXT, index_value REAL, yoy_percent REAL, url TEXT);
CREATE TABLE materials (material_key TEXT PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, unit TEXT NOT NULL, spec TEXT NOT NULL);
CREATE TABLE observations (id INTEGER PRIMARY KEY AUTOINCREMENT, material_key TEXT NOT NULL REFERENCES materials(material_key), retailer TEXT NOT NULL, product TEXT NOT NULL, price REAL NOT NULL CHECK(price>=0), url TEXT NOT NULL, collected TEXT NOT NULL);
CREATE INDEX idx_observations_material ON observations(material_key);
