CREATE TABLE files (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  type ENUM('pdf','mp4') NOT NULL,
  pages_total INT NULL,
  duration_seconds INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sp_site_id VARCHAR(255) NOT NULL,
  sp_drive_id VARCHAR(255) NOT NULL,
  sp_item_id VARCHAR(255) NOT NULL
);

CREATE TABLE file_sessions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  m365_oid VARCHAR(100) NOT NULL,
  m365_email VARCHAR(255) NULL,
  m365_name VARCHAR(255) NULL,
  file_id BIGINT NOT NULL,
  opened_at DATETIME NOT NULL,
  closed_at DATETIME NULL,
  pages_read INT NOT NULL DEFAULT 0,
  watched_seconds INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (m365_oid, file_id),
  INDEX (m365_email),
  FOREIGN KEY (file_id) REFERENCES files(id)
);

CREATE TABLE file_stats (
  m365_oid VARCHAR(100) NOT NULL,
  file_id BIGINT NOT NULL,
  open_count INT NOT NULL DEFAULT 0,
  play_count INT NOT NULL DEFAULT 0,
  last_opened_at DATETIME NULL,
  PRIMARY KEY (m365_oid, file_id),
  FOREIGN KEY (file_id) REFERENCES files(id)
);
