DROP TABLE IF EXISTS users; 

CREATE TABLE users 
  ( 
	id       SMALLINT UNSIGNED NOT NULL auto_increment, 
	username VARCHAR(255) NOT NULL,
	password TEXT NOT NULL, 
	role     VARCHAR(255) NOT NULL,
	join_date DATE NULL DEFAULT NULL,
	birth_date DATE NULL DEFAULT NULL,
	gender varchar(15) NULL DEFAULT NULL,
	data text NULL DEFAULT NULL,
	email varchar(40) NULL DEFAULT NULL,
	bio varchar(180) NULL DEFAULT NULL,
	xp varchar(180) NULL DEFAULT 0,
	avatar varchar(180) NULL DEFAULT 0,
	PRIMARY KEY (id) 
  );

DROP TABLE IF EXISTS loginlogs; 

CREATE TABLE loginlogs (
	id SMALLINT UNSIGNED NOT NULL auto_increment,
	IpAddress varbinary(16) NOT NULL,
	TryTime bigint(20) NOT NULL,
	PRIMARY KEY (id) 
);

DROP TABLE IF EXISTS login_history; 

CREATE TABLE login_history (
	id SMALLINT UNSIGNED NOT NULL auto_increment,
	ip varbinary(16) NOT NULL,
	data MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	PRIMARY KEY (id) 
);

DROP TABLE IF EXISTS categories; 

CREATE TABLE categories 
  ( 
	 id          SMALLINT UNSIGNED NOT NULL auto_increment, 
	 name        VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 slug     VARCHAR(30) NOT NULL,
	 description  MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 meta_description  MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 fields  TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 extra_fields mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 priority smallint(6) NOT NULL DEFAULT '0',
	 PRIMARY KEY (id) 
  );

DROP TABLE IF EXISTS cat_links; 

CREATE TABLE cat_links 
  ( 
	 id         SMALLINT UNSIGNED NOT NULL auto_increment, 
	 gameid     SMALLINT UNSIGNED NOT NULL,
	 categoryid SMALLINT UNSIGNED NOT NULL,
	 PRIMARY KEY (id) 
  ); 

DROP TABLE IF EXISTS pages; 

CREATE TABLE pages 
  ( 
	 id          SMALLINT UNSIGNED NOT NULL auto_increment, 
	 createddate DATE NOT NULL,
	 title       VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 slug        VARCHAR(255) NOT NULL,
	 content     MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 nl2br 		tinyint(4) NOT NULL DEFAULT '1',
	 fields  TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 extra_fields mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 PRIMARY KEY (id) 
  );

DROP TABLE IF EXISTS posts; 

CREATE TABLE posts 
  ( 
	 id          SMALLINT UNSIGNED NOT NULL auto_increment, 
	 created_date DATE NOT NULL,
	 title       VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 slug        VARCHAR(255) NOT NULL,
	 thumbnail_url   VARCHAR(255) NOT NULL,
	 content     MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 fields  TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 extra_fields mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 PRIMARY KEY (id) 
  );

DROP TABLE IF EXISTS games; 

CREATE TABLE games 
( 
	 id           SMALLINT UNSIGNED NOT NULL auto_increment, 
	 createddate  DATE NOT NULL,
	 title        VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 description  MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
	 instructions MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
	 category     TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
	 source       TEXT NOT NULL, 
	 thumb_1      VARCHAR(255) NOT NULL,
	 thumb_2      VARCHAR(255) NOT NULL,
	 thumb_small  VARCHAR(255) NOT NULL,
	 url          TEXT NOT NULL, 
	 width        TEXT NOT NULL, 
	 height       TEXT NOT NULL, 
	 tags         TEXT NOT NULL, 
	 views        INT NOT NULL, 
	 upvote       INT NOT NULL, 
	 downvote     INT NOT NULL,
	 slug     VARCHAR(255) NOT NULL,
	 data MEDIUMTEXT NULL DEFAULT NULL,
	 is_mobile TINYINT(1) NOT NULL DEFAULT '1',
	 last_modified DATETIME NULL DEFAULT NULL,
	 fields  TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 extra_fields mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	 published tinyint(1) NOT NULL DEFAULT '1',
	 PRIMARY KEY (id) 
);

DROP TABLE IF EXISTS votelogs; 

CREATE TABLE votelogs 
( 
	id           SMALLINT UNSIGNED NOT NULL auto_increment, 
	game_id     SMALLINT UNSIGNED NOT NULL,
	ip 		varbinary(16) NOT NULL,
	action          TEXT NOT NULL, 
	PRIMARY KEY (id)
);

DROP TABLE IF EXISTS favorites; 

CREATE TABLE favorites 
( 
	id           SMALLINT UNSIGNED NOT NULL auto_increment, 
	game_id     SMALLINT UNSIGNED NOT NULL,
	user_id     SMALLINT UNSIGNED NOT NULL,
	PRIMARY KEY (id)
);

DROP TABLE IF EXISTS collections; 

CREATE TABLE collections 
  ( 
	 id          SMALLINT UNSIGNED NOT NULL auto_increment, 
	 name        VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 data  MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 PRIMARY KEY (id) 
  );

DROP TABLE IF EXISTS comments;

CREATE TABLE comments (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
 game_id int(10) NOT NULL,
 parent_id int(10) unsigned DEFAULT NULL,
 comment varchar(400) NOT NULL,
 sender_id int(40) NOT NULL,
 sender_username varchar(20) NOT NULL,
 created_date DATETIME NULL DEFAULT NULL,
 approved tinyint(4) NOT NULL DEFAULT '1',
	PRIMARY KEY (id)
);

DROP TABLE IF EXISTS scores;

CREATE TABLE scores 
  ( 
	 id          SMALLINT UNSIGNED NOT NULL auto_increment, 
	 created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	 game_id int(40) NOT NULL,
	 user_id int(40) NOT NULL,
	 score INT(6) UNSIGNED NOT NULL DEFAULT '0',
	 PRIMARY KEY (id) 
  );

CREATE TABLE IF NOT EXISTS statistics (
 id int(11) unsigned NOT NULL AUTO_INCREMENT,
 created_date date DEFAULT NULL,
 page_views varchar(255) DEFAULT NULL,
 unique_visitor varchar(255) DEFAULT NULL,
 data mediumtext DEFAULT NULL,
 PRIMARY KEY (id)
);

DROP TABLE IF EXISTS stats_ip_address; 

CREATE TABLE stats_ip_address (
 id int(11) unsigned NOT NULL AUTO_INCREMENT,
 ip_address varchar(255) DEFAULT NULL,
 created_date date DEFAULT NULL,
 PRIMARY KEY (id)
);

DROP TABLE IF EXISTS sessions; 

CREATE TABLE sessions (
  token varchar(400) NOT NULL,
  data text NOT NULL
);

DROP TABLE IF EXISTS prefs;

CREATE TABLE prefs (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  value text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS menus;

CREATE TABLE menus (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  label varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  url varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  parent_id int(11) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS trends;

CREATE TABLE trends (
  id int(11) NOT NULL AUTO_INCREMENT,
  game_id int(11) DEFAULT NULL,
  views int(11) NOT NULL,
  created date NOT NULL,
  slug varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tags;

CREATE TABLE tags (
  id smallint(11) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  usage_count int(11) NOT NULL DEFAULT '0',
  extra_fields varchar(6000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS tag_links;

CREATE TABLE tag_links (
  game_id smallint(11) unsigned NOT NULL,
  tag_id smallint(11) unsigned NOT NULL,
  PRIMARY KEY (game_id,tag_id)
);

DROP TABLE IF EXISTS settings;

CREATE TABLE settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  type VARCHAR(255) NOT NULL,
  category VARCHAR(255) NOT NULL,
  label MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  tooltip MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  description MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
  value MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
);

DROP TABLE IF EXISTS translations;

CREATE TABLE translations
(
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    content_type VARCHAR(50) NOT NULL,  -- e.g., 'game', 'post', 'category'
    content_id   INT UNSIGNED NOT NULL, -- corresponds to the relevant table but not a foreign key
    language     VARCHAR(5) NOT NULL,   -- e.g., 'en', 'fr', etc.
    field        VARCHAR(50) NOT NULL,  -- e.g., 'title', 'description', 'content'
    translation  MEDIUMTEXT NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_translations (content_type, content_id, language, field) -- An index for faster lookups
);

DROP TABLE IF EXISTS extra_fields;

CREATE TABLE extra_fields
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    content_type VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    field_key VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    title VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    type VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    placeholder VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    default_value VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    meta TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    PRIMARY KEY (id)
);
