CREATE TABLE IF NOT EXISTS facebook_group_decision_state (
    facebook_group_id BIGINT UNSIGNED NOT NULL,
    suggested_decision ENUM('keep','watch','archive') NOT NULL,
    human_decision ENUM('keep','watch','archive') NULL,
    human_reason TEXT NULL,
    facebook_membership ENUM('unknown','member','left','not_member') NOT NULL DEFAULT 'unknown',
    decided_at DATETIME NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (facebook_group_id),
    KEY idx_facebook_human_decision (human_decision),
    CONSTRAINT fk_facebook_decision_group FOREIGN KEY (facebook_group_id) REFERENCES facebook_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS facebook_group_decision_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    facebook_group_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(30) NOT NULL,
    previous_decision VARCHAR(20) NULL,
    new_decision VARCHAR(20) NULL,
    reason TEXT NULL,
    created_at DATETIME NOT NULL,
    reversed_at DATETIME NULL,
    KEY idx_facebook_decision_history (facebook_group_id,created_at),
    CONSTRAINT fk_facebook_decision_history_group FOREIGN KEY (facebook_group_id) REFERENCES facebook_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO facebook_group_decision_state (facebook_group_id,suggested_decision,updated_at)
SELECT facebook_group_id,
       CASE recommendation WHEN 'watch' THEN 'keep' WHEN 'remove' THEN 'archive' ELSE 'watch' END,
       UTC_TIMESTAMP()
FROM facebook_group_recommendations
ON DUPLICATE KEY UPDATE suggested_decision=VALUES(suggested_decision),updated_at=UTC_TIMESTAMP();
