CREATE TABLE "invitations" (
id TEXT PRIMARY KEY,
organisation_id TEXT NOT NULL,
email TEXT NOT NULL,
token TEXT UNIQUE NOT NULL,
role TEXT DEFAULT 'member',
invited_by TEXT,
expires_at DATETIME NOT NULL,
accepted_at DATETIME,
status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed', 'accepted', 'expired')),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE,
FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE SET NULL
)