CREATE TABLE IF NOT EXISTS raw.import_rows (
    import_row_id BIGSERIAL PRIMARY KEY,
    run_id BIGINT NOT NULL REFERENCES audit.ingestion_runs(run_id),
    raw_artifact_id BIGINT NOT NULL REFERENCES audit.raw_artifacts(artifact_id),
    template_name TEXT NOT NULL,
    target_table TEXT NOT NULL,
    row_number INTEGER NOT NULL CHECK (row_number >= 2),
    payload JSONB NOT NULL,
    imported_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (run_id, template_name, row_number)
);

ALTER TABLE silver.area_monthly_metrics
    ADD COLUMN IF NOT EXISTS ingestion_run_id BIGINT REFERENCES audit.ingestion_runs(run_id),
    ADD COLUMN IF NOT EXISTS raw_artifact_id BIGINT REFERENCES audit.raw_artifacts(artifact_id);

ALTER TABLE silver.neighborhood_monthly_metrics
    ADD COLUMN IF NOT EXISTS ingestion_run_id BIGINT REFERENCES audit.ingestion_runs(run_id),
    ADD COLUMN IF NOT EXISTS raw_artifact_id BIGINT REFERENCES audit.raw_artifacts(artifact_id);

CREATE INDEX IF NOT EXISTS idx_import_rows_run_id
    ON raw.import_rows(run_id);
CREATE INDEX IF NOT EXISTS idx_import_rows_raw_artifact_id
    ON raw.import_rows(raw_artifact_id);
CREATE INDEX IF NOT EXISTS idx_import_rows_template_name
    ON raw.import_rows(template_name, imported_at DESC);
CREATE INDEX IF NOT EXISTS idx_area_metrics_ingestion_run
    ON silver.area_monthly_metrics(ingestion_run_id);
CREATE INDEX IF NOT EXISTS idx_neighborhood_metrics_ingestion_run
    ON silver.neighborhood_monthly_metrics(ingestion_run_id);
