migrations/005_create_activities_dependencies.sql

-- up
CREATE TABLE activities (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id        INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  boq_item_id       INTEGER REFERENCES boq_items(id) ON DELETE CASCADE,
  scope_id          INTEGER REFERENCES scopes(id),
  template_id       INTEGER REFERENCES activity_templates(id),
  kind              TEXT NOT NULL,
  name              TEXT NOT NULL,
  effort_days       NUMERIC(6,2),
  lead_time_days    NUMERIC(6,2),
  clock             TEXT NOT NULL CHECK (clock IN ('WALL','WORKING')),
  noisy             INTEGER NOT NULL DEFAULT 0,
  contractor_id     INTEGER REFERENCES contractors(id),
  responsibility    TEXT CHECK (responsibility IN ('CONTRACTOR','CUSTOMER','ORG')),
  status            TEXT NOT NULL DEFAULT 'PLANNED'
    CHECK (status IN ('PLANNED','READY','IN_PROGRESS','BLOCKED','DONE','CANCELLED')),
  percent_complete  NUMERIC(5,2) NOT NULL DEFAULT 0
    CHECK (percent_complete BETWEEN 0 AND 100),
  metadata          TEXT NOT NULL DEFAULT '{}',
  created_at        TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  updated_at        TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);
CREATE INDEX idx_activities_project ON activities (project_id);
CREATE INDEX idx_activities_boq_item ON activities (boq_item_id);

CREATE TABLE activity_dependencies (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  predecessor_id  INTEGER NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
  successor_id    INTEGER NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
  dep_type        TEXT NOT NULL DEFAULT 'FS'
    CHECK (dep_type IN ('FS','SS','FF','SF')),
  lag_days        NUMERIC(6,2) NOT NULL DEFAULT 0,
  origin          TEXT NOT NULL
    CHECK (origin IN ('TEMPLATE','MANUAL','SCOPE','SYSTEM')),
  metadata        TEXT NOT NULL DEFAULT '{}',
  created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  UNIQUE (predecessor_id, successor_id, dep_type),
  CHECK (predecessor_id <> successor_id)   -- self-loop guard
);
CREATE INDEX idx_dep_predecessor ON activity_dependencies (predecessor_id);
CREATE INDEX idx_dep_successor   ON activity_dependencies (successor_id);

-- down
DROP TABLE activity_dependencies;
DROP TABLE activities;