-- BFU RenderFlow - cloud control database
-- phpMyAdmin -> apna database select karein -> Import -> yeh file

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  pass_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','user') NOT NULL DEFAULT 'user',
  blocked TINYINT(1) NOT NULL DEFAULT 0,
  created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  token CHAR(64) PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  machine VARCHAR(128) NOT NULL DEFAULT '',
  remember TINYINT(1) NOT NULL DEFAULT 0,
  created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires DATETIME NOT NULL,
  last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  etype VARCHAR(32) NOT NULL,           -- login / logout / project / render_start / render_done / factory / blocked_attempt
  title VARCHAR(255) NOT NULL DEFAULT '',
  detail VARCHAR(500) NOT NULL DEFAULT '',
  machine VARCHAR(128) NOT NULL DEFAULT '',
  ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (username), INDEX (etype), INDEX (ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS html_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT '',
  filename VARCHAR(255) NOT NULL,
  stored_as VARCHAR(255) NOT NULL,
  uploaded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- seeded accounts (passwords hashed - PBKDF2-SHA256, 200k iterations)
INSERT INTO users (username, pass_hash, role) VALUES
('BFUBilal',  'pbkdf2$200000$d4bc485ced862ffac10e4b6e6c8fdde4$6e07be33164a75669cedbfb915e63b9c3761fb77b7afe3a656be2aad144be6ae', 'user'),
('BFUSohaib', 'pbkdf2$200000$3a5ffdec219389346b69bacbe2684844$3daf4d4ef20d5de58e27d361ff7e3fa3f34d7fa358aec474c1051f1cce8901c2', 'user'),
('BFUAdmin',  'pbkdf2$200000$c8058163ebad3bf85805dcf875cfdb49$e0a9d387ecabebf1fe7812996ab66855a135e1729ca7c691dd7b38888432a74b', 'admin')
ON DUPLICATE KEY UPDATE username=username;