-- Add player notes table for admin notes on players
-- This stores admin notes about players in the UCP database

CREATE TABLE IF NOT EXISTS player_notes (
  id VARCHAR(36) PRIMARY KEY,
  player_id VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  type ENUM('INFO', 'WARNING', 'BAN_HISTORY', 'POSITIVE', 'NEGATIVE') DEFAULT 'INFO',
  is_private BOOLEAN DEFAULT FALSE,
  author_id VARCHAR(36) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_player_notes_player_id (player_id),
  INDEX idx_player_notes_author_id (author_id),
  
  CONSTRAINT fk_player_notes_author 
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);
