migrations/004_create_boq_scopes.sql

-- up
CREATE TABLE boq_items (
  id                          INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id                  INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  category_id                 INTEGER NOT NULL REFERENCES categories(id),
  room_id                     INTEGER REFERENCES rooms(id),
  name                        TEXT NOT NULL,
  quantity                    NUMERIC(12,3) NOT NULL,
  uom                         TEXT NOT NULL,
  cost                        NUMERIC(12,2),
  price                       NUMERIC(12,2),
  procurement_type            TEXT NOT NULL
    CHECK (procurement_type IN ('SUPPLY','APPLY','SUPPLY_AND_APPLY')),
  procurement_responsibility  TEXT NOT NULL
    CHECK (procurement_responsibility IN ('CONTRACTOR','CUSTOMER','ORG')),
  is_material                 INTEGER NOT NULL,
  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_boq_items_project ON boq_items (project_id);

CREATE TABLE scopes (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id     INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name           TEXT NOT NULL,
  scope_type     TEXT NOT NULL CHECK (scope_type IN ('ROOM','CATEGORY','MIXED')),
  contractor_id  INTEGER REFERENCES contractors(id),
  status         TEXT NOT NULL DEFAULT 'DRAFT'
    CHECK (status IN ('DRAFT','BIDDING','AWARDED','IN_PROGRESS','DONE')),
  metadata       TEXT NOT NULL DEFAULT '{}'
);

CREATE TABLE scope_items (
  scope_id     INTEGER NOT NULL REFERENCES scopes(id) ON DELETE CASCADE,
  boq_item_id  INTEGER NOT NULL REFERENCES boq_items(id) ON DELETE CASCADE,
  PRIMARY KEY (scope_id, boq_item_id)
);

-- down
DROP TABLE scope_items;
DROP TABLE scopes;
DROP TABLE boq_items;