CREATE TABLE IF NOT EXISTS document_views (
    id INT NOT NULL AUTO_INCREMENT,
    document_id INT NOT NULL,
    user_id INT NOT NULL,
    user_role ENUM('Superadmin', 'Admin', 'Viewer', 'Spurrinadmin', 'AppUser') NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE interaction_logs
ADD COLUMN is_flagged BOOLEAN DEFAULT FALSE;

ALTER TABLE interaction_logs
ADD COLUMN report_text TEXT AFTER is_flagged;

CREATE TABLE IF NOT EXISTS app_versions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  platform ENUM('android', 'ios') NOT NULL,
  latest_version VARCHAR(20) NOT NULL,
  stable_version VARCHAR(20) NOT NULL,
  under_maintenance BOOLEAN DEFAULT FALSE,
  last_updated DATE NOT NULL,
  app_url VARCHAR(500) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_platform (platform)
);