ALTER TABLE users
  MODIFY COLUMN law_firm_id CHAR(36) NULL;

CREATE TABLE IF NOT EXISTS workspace_invitations (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  invitee_email VARCHAR(255) NOT NULL,
  invitee_user_id CHAR(36) NULL,
  invited_by_user_id CHAR(36) NULL,
  status_code VARCHAR(30) NOT NULL DEFAULT 'pending',
  responded_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workspace_invitations_law_firm_email (law_firm_id, invitee_email),
  KEY idx_workspace_invitations_invitee_status (invitee_user_id, status_code),
  KEY idx_workspace_invitations_law_firm_status (law_firm_id, status_code),
  KEY idx_workspace_invitations_invited_by (invited_by_user_id),
  CONSTRAINT fk_workspace_invitations_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workspace_invitations_invitee_user
    FOREIGN KEY (invitee_user_id) REFERENCES users (id),
  CONSTRAINT fk_workspace_invitations_invited_by
    FOREIGN KEY (invited_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
