CREATE TABLE IF NOT EXISTS idear_members (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  subject        TEXT,
  email          TEXT,
  name           TEXT,
  role           TEXT NOT NULL,
  deactivated_at DATETIME,
  created_at     DATETIME,
  updated_at     DATETIME
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_idear_members_subject ON idear_members (subject);
CREATE INDEX IF NOT EXISTS idx_idear_members_email ON idear_members (email);
CREATE INDEX IF NOT EXISTS idx_idear_members_role ON idear_members (role);

CREATE TABLE IF NOT EXISTS idear_invitations (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  email       TEXT,
  role        TEXT NOT NULL,
  token_hash  TEXT,
  invited_by  INTEGER,
  created_at  DATETIME,
  expires_at  DATETIME,
  accepted_at DATETIME,
  revoked_at  DATETIME
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_idear_invitations_token_hash ON idear_invitations (token_hash);
CREATE INDEX IF NOT EXISTS idx_idear_invitations_email ON idear_invitations (email);