PRAGMA foreign_keys=ON;
CREATE TABLE IF NOT EXISTS experiments (
 id TEXT PRIMARY KEY CHECK(length(id)=32), owner TEXT NOT NULL, name TEXT NOT NULL,
 spec TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS experiments_owner_updated ON experiments(owner, updated_at DESC);
CREATE TABLE IF NOT EXISTS jobs (
 id TEXT PRIMARY KEY CHECK(length(id)=32), experiment_id TEXT, owner TEXT NOT NULL,
 name TEXT NOT NULL, spec TEXT NOT NULL, kind TEXT NOT NULL DEFAULT 'model' CHECK(kind IN ('model','planet')), status TEXT NOT NULL CHECK(status IN ('queued','running','completed','failed','cancelled','interrupted')),
 phase TEXT NOT NULL DEFAULT 'queued', progress REAL NOT NULL DEFAULT 0,
 completed_cycles INTEGER NOT NULL DEFAULT 0, total_cycles INTEGER NOT NULL,
 created_at TEXT NOT NULL, started_at TEXT, finished_at TEXT, updated_at TEXT NOT NULL,
 error TEXT, cancel_requested INTEGER NOT NULL DEFAULT 0, pid INTEGER, log_offset INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS map_versions (
 id TEXT PRIMARY KEY CHECK(length(id)=32), owner TEXT NOT NULL, field TEXT NOT NULL,
 resolution TEXT NOT NULL, name TEXT NOT NULL, rows INTEGER NOT NULL, cols INTEGER NOT NULL,
 digest TEXT NOT NULL CHECK(length(digest)=64), data BLOB NOT NULL, created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS map_versions_owner_resolution ON map_versions(owner,resolution,created_at DESC);
CREATE TABLE IF NOT EXISTS job_maps (
 job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE, field TEXT NOT NULL,
 version_id TEXT NOT NULL, digest TEXT NOT NULL CHECK(length(digest)=64), data BLOB NOT NULL,
 PRIMARY KEY(job_id,field)
);
CREATE INDEX IF NOT EXISTS jobs_owner_created ON jobs(owner, created_at DESC);
CREATE INDEX IF NOT EXISTS jobs_state_created ON jobs(status, created_at);
CREATE INDEX IF NOT EXISTS jobs_owner_state ON jobs(owner, status, created_at DESC);