-- Digital Display System Database Schema

-- Users table with roles
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','editor','viewer') DEFAULT 'viewer'
);

-- Uploaded content (images/videos)
CREATE TABLE content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  type ENUM('image','video') NOT NULL,
  uploaded_by INT,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- Playlists
CREATE TABLE playlists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_by INT,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Playlist items (ordered content inside playlists)
CREATE TABLE playlist_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  playlist_id INT NOT NULL,
  content_id INT NOT NULL,
  position INT NOT NULL,
  FOREIGN KEY (playlist_id) REFERENCES playlists(id),
  FOREIGN KEY (content_id) REFERENCES content(id)
);

-- Schedules (assign playlists to time slots)
CREATE TABLE schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  playlist_id INT NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  FOREIGN KEY (playlist_id) REFERENCES playlists(id)
);

-- Activity logs (audit trail)
CREATE TABLE activity_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  action VARCHAR(255) NOT NULL,
  details TEXT,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
