CREATE TABLE IF NOT EXISTS material_consumption_snapshots (
    timestamp timestamptz NOT NULL,
    calculation_id text NOT NULL,
    machine_id text NOT NULL,
    production_order text NOT NULL,
    run_id text NOT NULL,
    consumption_kg double precision NOT NULL,
    PRIMARY KEY (calculation_id, machine_id, production_order, timestamp)
);

-- Machine time series across orders; the primary key covers a specific order.
CREATE INDEX IF NOT EXISTS material_consumption_snapshots_machine_time_idx
    ON material_consumption_snapshots (calculation_id, machine_id, timestamp);

CREATE TABLE IF NOT EXISTS material_efficiency_snapshots (
    erp_feedback_timestamp timestamptz NOT NULL,
    material_snapshot_timestamp timestamptz NOT NULL,
    calculation_id text NOT NULL,
    machine_id text NOT NULL,
    workplace text NOT NULL,
    erp_production_order text NOT NULL,
    enlyze_production_order text NOT NULL,
    article_number text NULL,
    article_description text NULL,
    nominal_width_m double precision NULL,
    good_quantity_m2 double precision NOT NULL,
    material_consumption_kg double precision NOT NULL,
    material_consumption_kg_per_m2 double precision NOT NULL,
    material_consumption_g_per_m2 double precision NOT NULL,
    PRIMARY KEY (calculation_id, machine_id, enlyze_production_order, erp_feedback_timestamp)
);

CREATE INDEX IF NOT EXISTS material_efficiency_snapshots_machine_time_idx
    ON material_efficiency_snapshots (calculation_id, machine_id, erp_feedback_timestamp);

-- Instantaneous process measurements; no ERP good-area denominator.
CREATE TABLE IF NOT EXISTS material_application_snapshots (
    timestamp timestamptz NOT NULL,
    calculation_id text NOT NULL,
    machine_id text NOT NULL,
    production_order text NOT NULL,
    run_id text NOT NULL,
    application_g_m2 double precision NOT NULL,
    PRIMARY KEY (calculation_id, machine_id, production_order, timestamp)
);

CREATE INDEX IF NOT EXISTS material_application_snapshots_machine_time_idx
    ON material_application_snapshots (calculation_id, machine_id, timestamp);