-- Sharks Community - schema bazei de date
-- Țintă: MariaDB 10.6+ / MySQL 8+, utf8mb4, InnoDB.
-- Toate timpurile sunt UNIX timestamp (UTC) în coloane INT/BIGINT.
--
-- Utilizatori recomandați (principiul privilegiului minim):
--   CREATE USER 'sharks_web'@'localhost' IDENTIFIED BY '...';
--   CREATE USER 'sharks_bot'@'localhost' IDENTIFIED BY '...';
--   GRANT SELECT, INSERT, UPDATE, DELETE ON sharks_panel.* TO 'sharks_web'@'localhost';
--   GRANT SELECT, INSERT, UPDATE, DELETE ON sharks_panel.* TO 'sharks_bot'@'localhost';
--   -- jurnalul de audit devine doar-adaugă (nimeni nu poate șterge sau rescrie istoric):
--   REVOKE UPDATE, DELETE ON sharks_panel.audit_log FROM 'sharks_web'@'localhost';
--   REVOKE UPDATE, DELETE ON sharks_panel.audit_log FROM 'sharks_bot'@'localhost';
--   -- audit_head trebuie să rămână actualizabil (conține ultimul hash din lanț).

CREATE TABLE roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(32) NOT NULL,
  name VARCHAR(48) NOT NULL,
  level SMALLINT NOT NULL DEFAULT 0,
  color VARCHAR(7) NOT NULL DEFAULT '#8f8c8e',
  ts_group_id INT NULL,
  is_default TINYINT NOT NULL DEFAULT 0,
  perms TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE UNIQUE INDEX uq_roles_slug ON roles (slug);

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(24) NOT NULL,
  username_lc VARCHAR(24) NOT NULL,
  email VARCHAR(190) NOT NULL,
  email_lc VARCHAR(190) NOT NULL,
  pass_hash VARCHAR(255) NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  ts_uid VARCHAR(64) NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'active',
  warnings INT NOT NULL DEFAULT 0,
  totp_secret VARCHAR(255) NULL,
  totp_enabled TINYINT NOT NULL DEFAULT 0,
  totp_last_step BIGINT NOT NULL DEFAULT 0,
  recovery_codes TEXT NULL,
  notify_ts TINYINT NOT NULL DEFAULT 1,
  failed_logins INT NOT NULL DEFAULT 0,
  locked_until INT NOT NULL DEFAULT 0,
  created_at INT NOT NULL,
  last_login_at INT NULL,
  last_login_ip VARCHAR(45) NULL,
  FOREIGN KEY (role_id) REFERENCES roles (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE UNIQUE INDEX uq_users_username ON users (username_lc);
CREATE UNIQUE INDEX uq_users_email ON users (email_lc);
CREATE UNIQUE INDEX uq_users_tsuid ON users (ts_uid);

CREATE TABLE sessions (
  id CHAR(64) NOT NULL PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at INT NOT NULL,
  last_seen INT NOT NULL,
  expires_at INT NOT NULL,
  ip VARCHAR(45) NOT NULL,
  ua_hash CHAR(16) NOT NULL,
  stepup_until INT NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_sessions_user ON sessions (user_id);

CREATE TABLE rate_limits (
  k VARCHAR(160) NOT NULL PRIMARY KEY,
  hits INT NOT NULL,
  win_start INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE settings (
  k VARCHAR(48) NOT NULL PRIMARY KEY,
  v TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(12) NOT NULL,
  title VARCHAR(140) NOT NULL,
  fields TEXT NOT NULL,
  details TEXT NOT NULL,
  proof TEXT NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'pending',
  staff_id BIGINT UNSIGNED NULL,
  staff_note TEXT NULL,
  created_at INT NOT NULL,
  updated_at INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_requests_user ON requests (user_id);
CREATE INDEX ix_requests_status ON requests (status, created_at);

CREATE TABLE topics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(140) NOT NULL,
  body TEXT NOT NULL,
  pinned TINYINT NOT NULL DEFAULT 0,
  closed TINYINT NOT NULL DEFAULT 0,
  views INT NOT NULL DEFAULT 0,
  replies INT NOT NULL DEFAULT 0,
  created_at INT NOT NULL,
  last_reply_at INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_topics_order ON topics (pinned, last_reply_at);

CREATE TABLE topic_replies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  topic_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  created_at INT NOT NULL,
  FOREIGN KEY (topic_id) REFERENCES topics (id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_replies_topic ON topic_replies (topic_id, created_at);

CREATE TABLE announcements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tag VARCHAR(12) NOT NULL,
  title VARCHAR(140) NOT NULL,
  body TEXT NOT NULL,
  author_id BIGINT UNSIGNED NULL,
  created_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_announcements_date ON announcements (created_at);

-- Starea oglindită de bot din serverul TeamSpeak (snapshot, server, bot = heartbeat)
CREATE TABLE ts_state (
  k VARCHAR(32) NOT NULL PRIMARY KEY,
  v MEDIUMTEXT NOT NULL,
  updated_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ts_seen (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts_uid VARCHAR(64) NOT NULL,
  nick VARCHAR(64) NOT NULL,
  dbid INT NULL,
  first_seen INT NOT NULL,
  last_seen INT NOT NULL,
  total_seconds BIGINT NOT NULL DEFAULT 0,
  connections INT NOT NULL DEFAULT 0,
  last_groups VARCHAR(255) NOT NULL DEFAULT '',
  country VARCHAR(4) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE UNIQUE INDEX uq_seen_uid ON ts_seen (ts_uid);

CREATE TABLE ts_daily (
  day CHAR(10) NOT NULL PRIMARY KEY,
  peak INT NOT NULL DEFAULT 0,
  unique_users INT NOT NULL DEFAULT 0,
  connections INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts_banid INT NULL,
  ts_uid VARCHAR(64) NULL,
  ip VARCHAR(64) NULL,
  name VARCHAR(64) NULL,
  reason VARCHAR(512) NOT NULL DEFAULT '',
  invoker_name VARCHAR(64) NOT NULL DEFAULT '',
  invoker_uid VARCHAR(64) NULL,
  invoker_level SMALLINT NOT NULL DEFAULT 0,
  source VARCHAR(8) NOT NULL DEFAULT 'panel',
  created_at INT NOT NULL,
  duration INT NOT NULL DEFAULT 0,
  expires_at INT NOT NULL DEFAULT 0,
  active TINYINT NOT NULL DEFAULT 1,
  removed_by VARCHAR(64) NULL,
  removed_at INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_bans_active ON bans (active, created_at);
CREATE INDEX ix_bans_tsbanid ON bans (ts_banid);

-- Coada de comenzi panou -> bot. Fiecare comandă este semnată HMAC (vezi src/Commands.php).
CREATE TABLE ts_commands (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  action VARCHAR(24) NOT NULL,
  params TEXT NOT NULL,
  requested_by BIGINT UNSIGNED NULL,
  actor_name VARCHAR(64) NOT NULL,
  actor_level INT NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'pending',
  result TEXT NULL,
  created_at INT NOT NULL,
  expire_at INT NOT NULL,
  executed_at INT NULL,
  sig CHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_cmd_status ON ts_commands (status, id);

CREATE TABLE ts_timed (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  action VARCHAR(24) NOT NULL,
  params TEXT NOT NULL,
  run_at INT NOT NULL,
  done TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_timed_due ON ts_timed (done, run_at);

-- Starea de referință a grupurilor/permisiunilor, folosită de detectorul de modificări
CREATE TABLE ts_baseline (
  k VARCHAR(80) NOT NULL PRIMARY KEY,
  v MEDIUMTEXT NOT NULL,
  updated_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Legarea contului de identitatea TS prin mesajul de absență (away)
CREATE TABLE link_codes (
  user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  code_hash CHAR(64) NOT NULL,
  expires_at INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Jurnal de audit cu lanț de hash (orice modificare ulterioară rupe lanțul)
CREATE TABLE audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts INT NOT NULL,
  actor_id BIGINT UNSIGNED NULL,
  actor_name VARCHAR(64) NOT NULL,
  category VARCHAR(16) NOT NULL,
  action VARCHAR(40) NOT NULL,
  severity VARCHAR(8) NOT NULL DEFAULT 'info',
  target VARCHAR(160) NOT NULL DEFAULT '',
  detail MEDIUMTEXT NOT NULL,
  ip VARCHAR(45) NOT NULL DEFAULT '',
  prev_hash CHAR(64) NOT NULL,
  hash CHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_audit_cat ON audit_log (category, id);
CREATE INDEX ix_audit_ts ON audit_log (ts);

CREATE TABLE audit_head (
  id TINYINT NOT NULL PRIMARY KEY,
  last_hash CHAR(64) NOT NULL,
  last_id BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE guard_scripts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  lang VARCHAR(4) NOT NULL,
  code MEDIUMTEXT NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 0,
  privileged TINYINT NOT NULL DEFAULT 0,
  trig_type VARCHAR(10) NOT NULL DEFAULT 'manual',
  trig_value VARCHAR(40) NOT NULL DEFAULT '',
  code_hash CHAR(64) NOT NULL,
  sig CHAR(64) NOT NULL,
  created_by VARCHAR(64) NOT NULL,
  created_at INT NOT NULL,
  updated_by VARCHAR(64) NOT NULL,
  updated_at INT NOT NULL,
  last_run_at INT NULL,
  run_requested INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE UNIQUE INDEX uq_guard_name ON guard_scripts (name);

CREATE TABLE guard_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  script_id BIGINT UNSIGNED NOT NULL,
  started_at INT NOT NULL,
  finished_at INT NULL,
  status VARCHAR(10) NOT NULL,
  exit_code INT NULL,
  output TEXT NULL,
  error TEXT NULL,
  trigger_info VARCHAR(64) NOT NULL DEFAULT '',
  FOREIGN KEY (script_id) REFERENCES guard_scripts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_runs_script ON guard_runs (script_id, id);

-- Servere TeamSpeak la care se poate conecta botul (se gestionează din Admin > Guard).
-- Parola ServerQuery este criptată (XChaCha20-Poly1305) cu o cheie derivată din app_key; site-ul nu o decriptează niciodată.
CREATE TABLE ts_servers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(60) NOT NULL,
  host VARCHAR(253) NOT NULL,
  query_port INT NOT NULL DEFAULT 10011,
  query_user VARCHAR(64) NOT NULL,
  pass_enc TEXT NOT NULL,
  server_id INT NOT NULL DEFAULT 1,
  bot_nick VARCHAR(30) NOT NULL DEFAULT 'Sharks Guard',
  mute_group_id INT NOT NULL DEFAULT 0,
  public_host VARCHAR(253) NOT NULL DEFAULT '',
  public_port INT NOT NULL DEFAULT 9987,
  active TINYINT NOT NULL DEFAULT 0,
  created_by VARCHAR(64) NOT NULL,
  created_at INT NOT NULL,
  updated_at INT NOT NULL,
  last_test_at INT NULL,
  last_test_ok TINYINT NULL,
  last_test_msg VARCHAR(300) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cereri de testare a conexiunii, executate de bot. Datele din formular (inclusiv parola) stau aici
-- criptate doar cât durează testul și se șterg imediat după.
CREATE TABLE ts_tests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  requested_by BIGINT UNSIGNED NULL,
  profile_id BIGINT UNSIGNED NULL,
  payload TEXT NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'pending',
  ok TINYINT NULL,
  message VARCHAR(400) NULL,
  details TEXT NULL,
  created_at INT NOT NULL,
  finished_at INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE INDEX ix_tests_status ON ts_tests (status, id);

-- Date inițiale ---------------------------------------------------------------
INSERT INTO roles (slug, name, level, color, ts_group_id, is_default, perms) VALUES
 ('fondator',      'Fondator',      100, '#ff2a3a', NULL, 0, '["*"]'),
 ('administrator', 'Administrator',  80, '#d4121f', NULL, 0, '["requests.view","requests.process","users.view","users.manage","bans.view","bans.manage","grades.view","grades.grant","topics.moderate","announcements.manage","logs.view","ts.moderate","guard.view"]'),
 ('moderator',     'Moderator',      60, '#f3f1f1', NULL, 0, '["requests.view","requests.process","users.view","bans.view","bans.manage","topics.moderate","ts.moderate"]'),
 ('vip',           'VIP',            30, '#f2b705', NULL, 0, '[]'),
 ('membru',        'Membru',         10, '#8f8c8e', NULL, 1, '[]');

INSERT INTO settings (k, v) VALUES
 ('registration_open', '1'),
 ('maintenance',       '0'),
 ('auto_announce',     '1');

INSERT INTO audit_head (id, last_hash, last_id) VALUES
 (1, '0000000000000000000000000000000000000000000000000000000000000000', 0);