-- Phase 4: persistent audit log of every OSM sync run.
DO $$ BEGIN
  CREATE TYPE "OsmSyncStatus" AS ENUM ('RUNNING','SUCCESS','PARTIAL','FAILED');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS "OsmSyncRun" (
  "id"                TEXT PRIMARY KEY,
  "layer"             TEXT NOT NULL,
  "category"          TEXT,
  "chunk"             TEXT,
  "startedAt"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "finishedAt"        TIMESTAMP(3),
  "status"            "OsmSyncStatus" NOT NULL DEFAULT 'RUNNING',
  "rowsAdded"         INTEGER NOT NULL DEFAULT 0,
  "rowsUpdated"       INTEGER NOT NULL DEFAULT 0,
  "rowsSkipped"       INTEGER NOT NULL DEFAULT 0,
  "rowsLocked"        INTEGER NOT NULL DEFAULT 0,
  "errorMessage"      TEXT,
  "overpassQueryHash" TEXT,
  "metadata"          JSONB
);

CREATE INDEX IF NOT EXISTS "OsmSyncRun_layer_started" ON "OsmSyncRun"("layer","startedAt");
CREATE INDEX IF NOT EXISTS "OsmSyncRun_status_idx"    ON "OsmSyncRun"("status");
CREATE INDEX IF NOT EXISTS "OsmSyncRun_started_idx"   ON "OsmSyncRun"("startedAt");