-- Phase 1 Core Schema (MySQL)
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(128) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY(role_id, permission_id),
  FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS staff (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role_id INT NULL,
  mfa_secret VARCHAR(64) NULL,
  last_active_at DATETIME NULL,
  FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS logins (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  subject_type ENUM('player','staff') NOT NULL,
  subject_id VARCHAR(64) NOT NULL,
  ip_hash VARCHAR(128) NULL,
  `at` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS players (
  id CHAR(36) PRIMARY KEY,
  current_username VARCHAR(32) NOT NULL,
  first_join_at DATETIME NULL,
  last_seen_at DATETIME NULL,
  online_status TINYINT(1) NOT NULL DEFAULT 0,
  reputation_score DECIMAL(6,2) NULL,
  trust_tier VARCHAR(32) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_usernames (
  player_id CHAR(36) NOT NULL,
  username VARCHAR(32) NOT NULL,
  changed_at DATETIME NOT NULL,
  INDEX(player_id),
  FOREIGN KEY(player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_tags (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  player_id CHAR(36) NOT NULL,
  tag VARCHAR(64) NOT NULL,
  added_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX(player_id),
  FOREIGN KEY(player_id) REFERENCES players(id) ON DELETE CASCADE,
  FOREIGN KEY(added_by) REFERENCES staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS staff_notes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  player_id CHAR(36) NOT NULL,
  author_id INT NOT NULL,
  note TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX(player_id),
  FOREIGN KEY(player_id) REFERENCES players(id) ON DELETE CASCADE,
  FOREIGN KEY(author_id) REFERENCES staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  actor_type ENUM('staff','system') NOT NULL,
  actor_id VARCHAR(64) NOT NULL,
  action VARCHAR(64) NOT NULL,
  target_type VARCHAR(64) NOT NULL,
  target_id VARCHAR(128) NOT NULL,
  metadata JSON NULL,
  `at` DATETIME NOT NULL,
  prev_sig VARCHAR(128) NOT NULL,
  sig VARCHAR(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed basic permissions and an admin role
INSERT IGNORE INTO roles (id, name) VALUES (1, 'admin');
-- Seed base permissions (idless to avoid conflicts)
INSERT IGNORE INTO permissions (`key`) VALUES
  ('dashboard.view'),
  ('players.view'),
  ('players.create'),
  ('players.note'),
  ('players.tag'),
  ('audit.view');

-- Grant all base perms to admin role
INSERT IGNORE INTO role_permissions (role_id, permission_id)
  SELECT 1, p.id FROM permissions p WHERE p.`key` IN ('dashboard.view','players.view','players.create','players.note','players.tag','audit.view');

-- Create an admin user with placeholder password (change after import)
-- Generate a hash at https://phppasswordhash.com or via PHP password_hash
INSERT IGNORE INTO staff (id, username, password_hash, role_id) VALUES
  (1, 'admin', '$2y$10$changemechangemechangemechGmQ6d5f5n6i2r9n2r9n2r9n2r9n2', 1);

-- Cases module tables
CREATE TABLE IF NOT EXISTS cases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  opened_by INT NOT NULL,
  opened_at DATETIME NOT NULL,
  closed_at DATETIME NULL,
  FOREIGN KEY(opened_by) REFERENCES staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS case_comments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  author_id INT NOT NULL,
  comment TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX(case_id),
  FOREIGN KEY(case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY(author_id) REFERENCES staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS case_participants (
  case_id INT NOT NULL,
  staff_id INT NOT NULL,
  role ENUM('participant','assignee','follower') NOT NULL DEFAULT 'participant',
  PRIMARY KEY(case_id, staff_id),
  FOREIGN KEY(case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY(staff_id) REFERENCES staff(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cases permissions
INSERT IGNORE INTO permissions (`key`) VALUES ('cases.view'),('cases.create'),('cases.comment');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
  SELECT 1, p.id FROM permissions p WHERE p.`key` IN ('cases.view','cases.create','cases.comment');

-- Enhance Cases schema for assistance workflow
ALTER TABLE cases
  ADD COLUMN player_id CHAR(36) NULL AFTER title,
  ADD COLUMN priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal' AFTER player_id,
  ADD COLUMN category VARCHAR(64) NULL AFTER priority,
  ADD COLUMN assigned_to INT NULL AFTER category,
  ADD COLUMN updated_at DATETIME NULL AFTER opened_at;

ALTER TABLE cases
  ADD CONSTRAINT fk_cases_assigned_to FOREIGN KEY (assigned_to) REFERENCES staff(id) ON DELETE SET NULL;

-- Optional relation to players (may fail if players table missing); keep comment for awareness.
-- ALTER TABLE cases ADD CONSTRAINT fk_cases_player FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE SET NULL;

-- Additional case permissions
INSERT IGNORE INTO permissions (`key`) VALUES ('cases.assign'),('cases.close'),('cases.participants');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
  SELECT 1, p.id FROM permissions p WHERE p.`key` IN ('cases.assign','cases.close','cases.participants');

-- Discord integration for player linking and role management
ALTER TABLE players
  ADD COLUMN IF NOT EXISTS discord_id VARCHAR(32) NULL AFTER current_username;

-- Permission to manage Discord roles from PLIS
INSERT IGNORE INTO permissions (`key`) VALUES ('discord.roles');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
  SELECT 1, p.id FROM permissions p WHERE p.`key` IN ('discord.roles');
