CREATE TABLE IF NOT EXISTS sessions (
  session_token VARCHAR(64) PRIMARY KEY,
  created_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX idx_sessions_expires_at (expires_at)
);

CREATE TABLE IF NOT EXISTS torrents (
  torrent_id VARCHAR(40) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  state VARCHAR(32) NOT NULL,
  progress DECIMAL(6,2) NOT NULL DEFAULT 0,
  download_payload_rate BIGINT NOT NULL DEFAULT 0,
  upload_payload_rate BIGINT NOT NULL DEFAULT 0,
  ratio DECIMAL(12,6) NOT NULL DEFAULT 0,
  eta BIGINT NOT NULL DEFAULT -1,
  total_size BIGINT NOT NULL DEFAULT 0,
  total_wanted BIGINT NOT NULL DEFAULT 0,
  save_path VARCHAR(255) NOT NULL DEFAULT '/data/deluge',
  tracker_host VARCHAR(255) DEFAULT NULL,
  tracker_status VARCHAR(255) DEFAULT NULL,
  num_seeds INT NOT NULL DEFAULT 0,
  total_seeds INT NOT NULL DEFAULT 0,
  num_peers INT NOT NULL DEFAULT 0,
  total_peers INT NOT NULL DEFAULT 0,
  max_download_speed DECIMAL(12,2) NOT NULL DEFAULT -1,
  max_upload_speed DECIMAL(12,2) NOT NULL DEFAULT -1,
  max_connections INT NOT NULL DEFAULT -1,
  max_upload_slots INT NOT NULL DEFAULT -1,
  is_auto_managed TINYINT(1) NOT NULL DEFAULT 1,
  stop_at_ratio TINYINT(1) NOT NULL DEFAULT 0,
  stop_ratio DECIMAL(10,2) NOT NULL DEFAULT 2.0,
  remove_at_ratio TINYINT(1) NOT NULL DEFAULT 0,
  move_completed TINYINT(1) NOT NULL DEFAULT 0,
  move_completed_path VARCHAR(255) NOT NULL DEFAULT '',
  prioritize_first_last_pieces TINYINT(1) NOT NULL DEFAULT 0,
  queue_position INT NOT NULL DEFAULT 0,
  is_finished TINYINT(1) NOT NULL DEFAULT 0,
  source_kind VARCHAR(32) DEFAULT NULL,
  source_value TEXT DEFAULT NULL,
  time_added DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_torrents_queue_position (queue_position),
  INDEX idx_torrents_state (state)
);

CREATE TABLE IF NOT EXISTS torrent_files (
  file_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  torrent_id VARCHAR(40) NOT NULL,
  file_index INT NOT NULL,
  file_path VARCHAR(512) NOT NULL,
  file_size BIGINT NOT NULL DEFAULT 0,
  progress DECIMAL(6,2) NOT NULL DEFAULT 0,
  priority INT NOT NULL DEFAULT 1,
  UNIQUE KEY uniq_torrent_file (torrent_id, file_index),
  CONSTRAINT fk_torrent_files_torrent FOREIGN KEY (torrent_id) REFERENCES torrents (torrent_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS torrent_trackers (
  tracker_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  torrent_id VARCHAR(40) NOT NULL,
  tracker_tier INT NOT NULL DEFAULT 0,
  tracker_url VARCHAR(512) NOT NULL,
  status INT NOT NULL DEFAULT 2,
  msg VARCHAR(255) DEFAULT NULL,
  CONSTRAINT fk_torrent_trackers_torrent FOREIGN KEY (torrent_id) REFERENCES torrents (torrent_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS torrent_peers (
  peer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  torrent_id VARCHAR(40) NOT NULL,
  peer_ip VARCHAR(64) NOT NULL,
  client_name VARCHAR(128) NOT NULL,
  progress DECIMAL(6,2) NOT NULL DEFAULT 0,
  down_speed BIGINT NOT NULL DEFAULT 0,
  up_speed BIGINT NOT NULL DEFAULT 0,
  country VARCHAR(64) DEFAULT NULL,
  CONSTRAINT fk_torrent_peers_torrent FOREIGN KEY (torrent_id) REFERENCES torrents (torrent_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS config (
  config_key VARCHAR(128) PRIMARY KEY,
  config_value LONGTEXT NOT NULL,
  value_type ENUM('string', 'number', 'boolean', 'json') NOT NULL DEFAULT 'string'
);

CREATE TABLE IF NOT EXISTS events (
  event_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(128) NOT NULL,
  payload_json JSON DEFAULT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_events_created_at (created_at)
);