-- Ratings table for SaniBook
-- Run this on the live database after uploading files

CREATE TABLE IF NOT EXISTS ratings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  item_type ENUM('submission','content') NOT NULL,
  item_id INT NOT NULL,
  rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_user_item (user_id, item_type, item_id),
  KEY idx_item (item_type, item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
