-- schema.sql
-- Run this in MySQL to create the required tables.

CREATE TABLE userdb (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

CREATE TABLE file_sessions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT 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 (user_id, file_id),
  FOREIGN KEY (user_id) REFERENCES userdb(id),
  FOREIGN KEY (file_id) REFERENCES files(id)
);

CREATE TABLE file_stats (
  user_id BIGINT 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 (user_id, file_id),
  FOREIGN KEY (user_id) REFERENCES userdb(id),
  FOREIGN KEY (file_id) REFERENCES files(id)
);
