-- ---------------------------------------------------------------------------
--  FiveRP - database schema
--  mysql fiverp < sql/schema.sql   (install.sh applies this for you)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS accounts (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email         VARCHAR(190) NOT NULL,
  username      VARCHAR(32)  NOT NULL,
  password_hash VARCHAR(255) NOT NULL,   -- scrypt, salt and cost live inside the hash
  license       VARCHAR(64)  DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login    DATETIME     DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email),
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS characters (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id  INT UNSIGNED NOT NULL,
  slot        TINYINT UNSIGNED NOT NULL DEFAULT 1,
  first_name  VARCHAR(32) NOT NULL,
  last_name   VARCHAR(32) NOT NULL,
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  model       VARCHAR(32) NOT NULL DEFAULT 'mp_m_freemode_01',
  appearance  LONGTEXT    DEFAULT NULL,   -- JSON, written by fiverp-characters
  pos_x       FLOAT       DEFAULT NULL,
  pos_y       FLOAT       DEFAULT NULL,
  pos_z       FLOAT       DEFAULT NULL,
  heading     FLOAT       DEFAULT NULL,
  last_played DATETIME    DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_slot (account_id, slot),
  CONSTRAINT fk_char_account FOREIGN KEY (account_id)
    REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;