schema.sql

-- Accounts tabel
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  username VARCHAR(32) UNIQUE NOT NULL,
  email VARCHAR(64) UNIQUE,
  type INTEGER NOT NULL DEFAULT 0,
  secret VARCHAR(64) DEFAULT NULL,
  password_hash VARCHAR(128) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Characters tabel
CREATE TABLE characters (
  id SERIAL PRIMARY KEY,
  account_id INTEGER REFERENCES accounts(id) ON DELETE CASCADE,
  group_id int NOT NULL DEFAULT '1',
  name VARCHAR(255) UNIQUE NOT NULL DEFAULT 'knight',
  vocation VARCHAR(32) NOT NULL,
  lookbody INTEGER NOT NULL DEFAULT 0,
  lookfeet INTEGER NOT NULL DEFAULT 0,
  lookhead INTEGER NOT NULL DEFAULT 0,
  looklegs INTEGER NOT NULL DEFAULT 0,
  looktype INTEGER NOT NULL DEFAULT 0,
  direction INTEGER NOT NULL DEFAULT 2,
  pos_x INTEGER DEFAULT 100,
  pos_y INTEGER DEFAULT 100,
  pos_z INTEGER DEFAULT 7,
  level INTEGER NOT NULL DEFAULT 1,
  experience INTEGER DEFAULT 0,
  health INTEGER NOT NULL DEFAULT 150,
  healthmax INTEGER NOT NULL DEFAULT 150,
  mana INTEGER NOT NULL DEFAULT 150,
  manamax INTEGER NOT NULL DEFAULT 150,
  maglevel INTEGER NOT NULL DEFAULT 0,
  skill_fist INTEGER NOT NULL DEFAULT 10,
  skill_fist_tries INTEGER NOT NULL DEFAULT 0,
  skill_club INTEGER NOT NULL DEFAULT 10,
  skill_club_tries INTEGER NOT NULL DEFAULT 0,
  skill_sword INTEGER NOT NULL DEFAULT 10,
  skill_sword_tries INTEGER NOT NULL DEFAULT 0,
  skill_axe INTEGER NOT NULL DEFAULT 10,
  skill_axe_tries INTEGER NOT NULL DEFAULT 0,
  skill_dist INTEGER NOT NULL DEFAULT 10,
  skill_dist_tries INTEGER NOT NULL DEFAULT 0,
  skill_shielding INTEGER NOT NULL DEFAULT 10,
  skill_shielding_tries INTEGER NOT NULL DEFAULT 0,
  skill_fishing INTEGER NOT NULL DEFAULT 10,
  skill_fishing_tries INTEGER NOT NULL DEFAULT 0
);