CREATE TABLE IF NOT EXISTS facebook_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_url VARCHAR(500) NOT NULL,
  group_name VARCHAR(300) NOT NULL,
  managed TINYINT(1) NOT NULL DEFAULT 0,
  priority_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  priority_tier ENUM('A','B','C','D') NOT NULL DEFAULT 'D',
  projects_json JSON NOT NULL,
  reasons_json JSON NOT NULL,
  publication_rules_status ENUM('known_managed','to_verify') NOT NULL DEFAULT 'to_verify',
  activity_status ENUM('measured','unknown') NOT NULL DEFAULT 'unknown',
  audited_at DATE NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY group_url (group_url),
  INDEX(priority_score), INDEX(priority_tier), INDEX(managed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
