-- Workstream 6: projects (table/board views with custom fields).
CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  visibility TEXT NOT NULL DEFAULT 'private' CHECK (visibility IN ('public', 'private')),
  closed INTEGER NOT NULL DEFAULT 0,
  created_by TEXT NOT NULL REFERENCES accounts(id),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (owner_id, number)
);

-- kind: text | number | date | single_select; options is a JSON array of option names.
CREATE TABLE project_fields (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  kind TEXT NOT NULL CHECK (kind IN ('text', 'number', 'date', 'single_select')),
  options TEXT NOT NULL DEFAULT '[]',
  position INTEGER NOT NULL,
  UNIQUE (project_id, name)
);

CREATE TABLE project_items (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  issue_id TEXT REFERENCES issues(id) ON DELETE CASCADE,
  note TEXT,
  position REAL NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE (project_id, issue_id)
);
CREATE INDEX project_items_issue ON project_items(issue_id);

CREATE TABLE project_item_values (
  item_id TEXT NOT NULL REFERENCES project_items(id) ON DELETE CASCADE,
  field_id TEXT NOT NULL REFERENCES project_fields(id) ON DELETE CASCADE,
  value TEXT NOT NULL,
  PRIMARY KEY (item_id, field_id)
);