CREATE TABLE IF NOT EXISTS facebook_group_top_posts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 facebook_group_id BIGINT UNSIGNED NOT NULL,
 post_key CHAR(64) NOT NULL,
 post_url TEXT NULL,
 author_label VARCHAR(190) NULL,
 post_excerpt TEXT NOT NULL,
 published_at DATETIME NULL,
 reaction_count INT UNSIGNED NOT NULL DEFAULT 0,
 comment_count INT UNSIGNED NOT NULL DEFAULT 0,
 share_count INT UNSIGNED NULL,
 absolute_score DECIMAL(8,2) NOT NULL DEFAULT 0,
 relative_score DECIMAL(8,2) NULL,
 selection_reason TEXT NOT NULL,
 observed_at DATETIME NOT NULL,
 UNIQUE KEY group_post (facebook_group_id,post_key),
 INDEX group_ranking (facebook_group_id,absolute_score),
 INDEX observed_at (observed_at),
 CONSTRAINT fk_facebook_top_posts_group FOREIGN KEY (facebook_group_id) REFERENCES facebook_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
