migrations/003_create_templates.sql

-- up
CREATE TABLE activity_templates (
  id                      INTEGER PRIMARY KEY AUTOINCREMENT,
  category_id             INTEGER NOT NULL REFERENCES categories(id),
  procurement_type        TEXT NOT NULL
    CHECK (procurement_type IN ('SUPPLY','APPLY','SUPPLY_AND_APPLY')),
  kind                    TEXT NOT NULL
    CHECK (kind IN ('PROCUREMENT','INSTALLATION','COMMISSIONING','INSPECTION','CUSTOM')),
  name_template           TEXT NOT NULL,
  default_effort_days     NUMERIC(6,2),
  default_lead_time_days  NUMERIC(6,2),
  clock                   TEXT NOT NULL CHECK (clock IN ('WALL','WORKING')),
  noisy                   INTEGER NOT NULL DEFAULT 0,
  responsibility_default  TEXT
    CHECK (responsibility_default IN ('CONTRACTOR','CUSTOMER','ORG')),
  active                  INTEGER NOT NULL DEFAULT 1,
  metadata                TEXT NOT NULL DEFAULT '{}',
  created_at              TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  UNIQUE (category_id, procurement_type, kind)
);

CREATE TABLE dependency_templates (
  id                       INTEGER PRIMARY KEY AUTOINCREMENT,
  predecessor_category_id  INTEGER NOT NULL REFERENCES categories(id),
  successor_category_id    INTEGER NOT NULL REFERENCES categories(id),
  predecessor_kind         TEXT NOT NULL,
  successor_kind           TEXT NOT NULL,
  dep_type                 TEXT NOT NULL DEFAULT 'FS'
    CHECK (dep_type IN ('FS','SS','FF','SF')),
  lag_days                 NUMERIC(6,2) NOT NULL DEFAULT 0,
  active                   INTEGER NOT NULL DEFAULT 1,
  metadata                 TEXT NOT NULL DEFAULT '{}',
  created_at               TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  UNIQUE (predecessor_category_id, successor_category_id, predecessor_kind, successor_kind)
);
-- For intra-item rules, predecessor_category_id == successor_category_id.
-- For cross-category rules (e.g. "demo before flooring"), they differ.

-- down
DROP TABLE dependency_templates;
DROP TABLE activity_templates;