-- =============================================
-- Rassd News — Reactions System Database Setup
-- Run this script once in your MySQL database
-- =============================================

-- 1. Create reactions table
CREATE TABLE IF NOT EXISTS `news_reactions` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `news_id`     INT NOT NULL                        COMMENT 'ID الخبر من edit',
    `user_id`     INT DEFAULT NULL                    COMMENT 'NULL = guest',
    `session_id`  VARCHAR(64) DEFAULT NULL            COMMENT 'Session for guests',
    `ip_address`  VARCHAR(45) NOT NULL                COMMENT 'IP address',
    `reaction`    TINYINT NOT NULL                    COMMENT '1=like,2=love,3=happy,4=sad,5=angry,6=wow',
    `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_user_reaction`  (`news_id`, `user_id`),
    UNIQUE KEY `uq_guest_reaction` (`news_id`, `session_id`, `ip_address`),
    INDEX `idx_news_id` (`news_id`),
    INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Add reaction columns to edit table
ALTER TABLE `edit` ADD COLUMN `react_like`  INT DEFAULT 0 COMMENT 'Total like reactions';
ALTER TABLE `edit` ADD COLUMN `react_love`  INT DEFAULT 0 COMMENT 'Total love reactions';
ALTER TABLE `edit` ADD COLUMN `react_happy` INT DEFAULT 0 COMMENT 'Total happy reactions';
ALTER TABLE `edit` ADD COLUMN `react_sad`   INT DEFAULT 0 COMMENT 'Total sad reactions';
ALTER TABLE `edit` ADD COLUMN `react_angry` INT DEFAULT 0 COMMENT 'Total angry reactions';
ALTER TABLE `edit` ADD COLUMN `react_wow`   INT DEFAULT 0 COMMENT 'Total wow reactions';
ALTER TABLE `edit` ADD COLUMN `react_total` INT DEFAULT 0 COMMENT 'Total all reactions';
ALTER TABLE `edit` ADD COLUMN `react_top`   TINYINT DEFAULT 0 COMMENT 'Top reaction type (1-6)';