-- Project Invites table
CREATE TABLE IF NOT EXISTS project_invites (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id INTEGER NOT NULL,
  email TEXT NOT NULL,
  invited_by INTEGER NOT NULL,
  token TEXT UNIQUE NOT NULL,
  status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'accepted', 'expired')),
  expires_at DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  accepted_at DATETIME NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE(project_id, email, status)
);

CREATE INDEX IF NOT EXISTS idx_project_id_invites ON project_invites(project_id);
CREATE INDEX IF NOT EXISTS idx_email_invites ON project_invites(email);
CREATE INDEX IF NOT EXISTS idx_token_invites ON project_invites(token);
CREATE INDEX IF NOT EXISTS idx_status_invites ON project_invites(status);