migrations/006_create_schedule_versions.sql

-- up
CREATE TABLE schedule_versions (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id          INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  parent_id           INTEGER REFERENCES schedule_versions(id),
  kind                TEXT NOT NULL CHECK (kind IN ('BASELINE','FORECAST')),
  trigger             TEXT NOT NULL,
  trigger_ref         TEXT,
  source_activity_id  INTEGER REFERENCES activities(id),
  computed_at         TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  computed_by         INTEGER REFERENCES users(id),
  inputs_hash         TEXT NOT NULL,
  stats               TEXT NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_schedule_versions_project ON schedule_versions (project_id, computed_at DESC);

CREATE TABLE scheduled_activities (
  schedule_version_id INTEGER NOT NULL REFERENCES schedule_versions(id) ON DELETE CASCADE,
  activity_id         INTEGER NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
  earliest_start      TEXT NOT NULL,
  earliest_finish     TEXT NOT NULL,
  latest_start        TEXT,
  latest_finish       TEXT,
  total_float_min     INTEGER,
  is_critical         INTEGER NOT NULL DEFAULT 0,
  reused_from_parent  INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (schedule_version_id, activity_id)
);
CREATE INDEX idx_scheduled_activities_activity
  ON scheduled_activities (activity_id, schedule_version_id DESC);

-- down
DROP TABLE scheduled_activities;
DROP TABLE schedule_versions;