CREATE TABLE IF NOT EXISTS shares (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  owner_upn TEXT NOT NULL,
  created_at TEXT NOT NULL,
  state TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS principals (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type TEXT NOT NULL,
  name TEXT,
  upn TEXT,
  UNIQUE(type, name, upn)
);

CREATE TABLE IF NOT EXISTS memberships (
  share_id INTEGER NOT NULL,
  principal_id INTEGER NOT NULL,
  role TEXT NOT NULL,
  PRIMARY KEY (share_id, principal_id),
  FOREIGN KEY (share_id) REFERENCES shares(id),
  FOREIGN KEY (principal_id) REFERENCES principals(id)
);

CREATE TABLE IF NOT EXISTS group_members (
  group_id INTEGER NOT NULL,
  user_upn TEXT NOT NULL,
  PRIMARY KEY (group_id, user_upn),
  FOREIGN KEY (group_id) REFERENCES principals(id)
);