-- Jodel-style up/down votes på svar.
-- Erstatter answer_likes (som beholdes deprecated for bagudkompat).
-- answers.score holder nettoscoren (sum af value).

CREATE TABLE IF NOT EXISTS `answer_votes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `answer_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `value` TINYINT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_answer_user_vote` (`answer_id`, `user_id`),
    KEY `idx_answer_votes_user` (`user_id`),
    CONSTRAINT `fk_av_answer` FOREIGN KEY (`answer_id`) REFERENCES `answers`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_av_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `chk_av_value` CHECK (`value` IN (-1, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `answers`
    ADD COLUMN `score` INT NOT NULL DEFAULT 0 AFTER `like_count`,
    ADD KEY `idx_answers_question_score` (`question_id`, `score`);

-- Bagudkompat: bring eksisterende likes ind som +1-stemmer
INSERT IGNORE INTO `answer_votes` (`answer_id`, `user_id`, `value`, `created_at`)
SELECT `answer_id`, `user_id`, 1, `created_at` FROM `answer_likes`;

-- Initialiser score fra eksisterende like_count
UPDATE `answers` SET `score` = `like_count`;
