-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 27, 2009 at 08:59 PM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT=0;
START TRANSACTION;

--
-- Database: `joju`
--

-- --------------------------------------------------------

--
-- Table structure for table `avatar`
--

DROP TABLE IF EXISTS `avatar`;
CREATE TABLE IF NOT EXISTS `avatar` (
  `avatar_aid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `avatar_uid` mediumint(8) unsigned NOT NULL COMMENT 'User Id',
  `avatar_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the user avatar ( setted by the user on the avatar creation ).',
  `avatar_body` mediumint(8) unsigned NOT NULL COMMENT 'Corpse used by the avatar ( 3d model )',
  `avatar_hair` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '3d Model of the Hair',
  `avatar_texbody` mediumint(8) unsigned NOT NULL COMMENT 'Texture of the body ( an item_variant, is the skin )',
  `avatar_texhair` mediumint(8) unsigned NOT NULL COMMENT 'Texture of the hair model',
  `avatar_texdecoration` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Some body decoration as tatoo or a scar.',
  `avatar_texeyes` mediumint(9) unsigned NOT NULL COMMENT 'Eyes Texture',
  `avatar_texmouth` mediumint(9) unsigned NOT NULL COMMENT 'Mouth Texture',
  `avatar_ears` mediumint(8) unsigned NOT NULL COMMENT 'Model of the ears',
  `avatar_texears` mediumint(8) unsigned NOT NULL COMMENT 'Texture of the ears',
  PRIMARY KEY (`avatar_aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `avatar`
--


-- --------------------------------------------------------

--
-- Table structure for table `avatar_outfit`
--

DROP TABLE IF EXISTS `avatar_outfit`;
CREATE TABLE IF NOT EXISTS `avatar_outfit` (
  `ao_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ao_avatarid` mediumint(8) unsigned NOT NULL COMMENT 'Avatar outfit of the avatar id.',
  `ao_gameid` mediumint(9) NOT NULL DEFAULT '0' COMMENT 'The id of the game that this outfit is for. 0 if the outfit is for the default avatar outfit.',
  `ao_helmet` mediumint(8) unsigned NOT NULL,
  `ao_texhelmet` mediumint(8) unsigned NOT NULL,
  `ao_texshirt` mediumint(8) unsigned NOT NULL,
  `ao_texjacket` mediumint(8) unsigned NOT NULL,
  `ao_texpants` mediumint(8) unsigned NOT NULL,
  `ao_texshoes` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`ao_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `avatar_outfit`
--


-- --------------------------------------------------------

--
-- Table structure for table `game`
--

DROP TABLE IF EXISTS `game`;
CREATE TABLE IF NOT EXISTS `game` (
  `game_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `game_name` mediumint(8) unsigned NOT NULL COMMENT 'The id of the string',
  PRIMARY KEY (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `game`
--


-- --------------------------------------------------------

--
-- Table structure for table `game_ghost`
--

DROP TABLE IF EXISTS `game_ghost`;
CREATE TABLE IF NOT EXISTS `game_ghost` (
  `ghost_gameid` mediumint(8) unsigned NOT NULL,
  `ghost_uid` mediumint(8) unsigned NOT NULL,
  `ghost_data` blob NOT NULL,
  PRIMARY KEY (`ghost_gameid`,`ghost_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `game_ghost`
--


-- --------------------------------------------------------

--
-- Table structure for table `game_hs`
--

DROP TABLE IF EXISTS `game_hs`;
CREATE TABLE IF NOT EXISTS `game_hs` (
  `ghs_gameid` mediumint(8) unsigned NOT NULL,
  `ghs_uid` mediumint(8) unsigned NOT NULL,
  `ghs_score` int(11) NOT NULL,
  UNIQUE KEY `ghs_gameid` (`ghs_gameid`,`ghs_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `game_hs`
--


-- --------------------------------------------------------

--
-- Table structure for table `item`
--

DROP TABLE IF EXISTS `item`;
CREATE TABLE IF NOT EXISTS `item` (
  `item_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `item_custom1` int(11) NOT NULL,
  `item_custom2` int(11) NOT NULL,
  `item_custom3` int(11) NOT NULL,
  `item_resourceid` mediumint(8) unsigned NOT NULL,
  `item_cost` float NOT NULL DEFAULT '0' COMMENT 'Not real money, internal currency of the game.',
  `item_type` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item`
--


-- --------------------------------------------------------

--
-- Table structure for table `item_type`
--

DROP TABLE IF EXISTS `item_type`;
CREATE TABLE IF NOT EXISTS `item_type` (
  `it_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `it_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`it_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item_type`
--


-- --------------------------------------------------------

--
-- Table structure for table `item_variant`
--

DROP TABLE IF EXISTS `item_variant`;
CREATE TABLE IF NOT EXISTS `item_variant` (
  `iv_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `iv_iid` mediumint(8) unsigned NOT NULL COMMENT 'Item ID',
  `iv_color` mediumint(8) unsigned NOT NULL,
  `iv_offset_x` smallint(6) NOT NULL DEFAULT '0',
  `iv_offset_y` smallint(6) NOT NULL DEFAULT '0',
  `iv_offset_z` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`iv_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item_variant`
--


-- --------------------------------------------------------

--
-- Table structure for table `languajes`
--

DROP TABLE IF EXISTS `languajes`;
CREATE TABLE IF NOT EXISTS `languajes` (
  `lang_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `lang_name` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`lang_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `languajes`
--

INSERT INTO `languajes` (`lang_id`, `lang_name`) VALUES
(1, 'en'),
(2, 'es'),
(3, 'pt'),
(4, 'it'),
(5, 'de'),
(6, 'fr');

-- --------------------------------------------------------

--
-- Table structure for table `lang_string`
--

DROP TABLE IF EXISTS `lang_string`;
CREATE TABLE IF NOT EXISTS `lang_string` (
  `ls_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `ls_name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ls_en` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ls_es` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ls_pt` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ls_it` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ls_de` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ls_fr` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ls_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `lang_string`
--


-- --------------------------------------------------------

--
-- Table structure for table `resource`
--

DROP TABLE IF EXISTS `resource`;
CREATE TABLE IF NOT EXISTS `resource` (
  `re_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `re_type` tinyint(3) unsigned NOT NULL,
  `re_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `re_path` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`re_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `resource`
--


-- --------------------------------------------------------

--
-- Table structure for table `resource_type`
--

DROP TABLE IF EXISTS `resource_type`;
CREATE TABLE IF NOT EXISTS `resource_type` (
  `rt_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `rt_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`rt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `resource_type`
--


-- --------------------------------------------------------

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
  `user_uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `user_fbid` bigint(20) unsigned NOT NULL COMMENT 'User Facebook ID',
  `user_name` varchar(64) CHARACTER SET utf8 NOT NULL COMMENT 'User copy of the facebook name',
  `user_points` float NOT NULL DEFAULT '0' COMMENT 'The real money that the account owns. (we need to define the currency, i think that U$S would be correct ).',
  `user_lang` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'User native languaje',
  `user_banned` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Tell if the user is banned or not',
  PRIMARY KEY (`user_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user`
--


-- --------------------------------------------------------

--
-- Table structure for table `world`
--

DROP TABLE IF EXISTS `world`;
CREATE TABLE IF NOT EXISTS `world` (
  `world_uid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `world_name` mediumint(9) NOT NULL,
  PRIMARY KEY (`world_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `world`
--


-- --------------------------------------------------------

--
-- Table structure for table `world_user`
--

DROP TABLE IF EXISTS `world_user`;
CREATE TABLE IF NOT EXISTS `world_user` (
  `wu_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `wu_uid` mediumint(8) unsigned NOT NULL,
  `wu_world` tinyint(3) unsigned NOT NULL,
  `wu_coins` float NOT NULL,
  `wu_banned` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Tell if the user is banned in this world',
  PRIMARY KEY (`wu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `world_user`
--


DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `moAvatarAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moAvatarAdd`( uId MEDIUMINT, Name VARCHAR(32), Body MEDIUMINT, Hair MEDIUMINT, TexBody MEDIUMINT, TexFace MEDIUMINT, TexHair MEDIUMINT, Hat MEDIUMINT, TexHat MEDIUMINT, TexDecoration MEDIUMINT, Skir MEDIUMINT )
BEGIN
INSERT INTO avatar (
		avatar_uid, 
		avatar_name, 
		avatar_ivbody, 
		avatar_ivhair, 
		avatar_ivtexboxy, 
		avatar_ivtexface, 
		avatar_ivtexhair, 
		avatar_ivhat, 
		avatar_ivtexhat, 
		avatar_ivtexdecoration, 
		avatar_ivskirt 
	) VALUES ( 
		uId, 
		Name, 
		Body, 
		Hair, 
		TexBody, 
		TexFace, 
		TexHair, 
		Hat, 
		TexHat, 
		TexDecoration, 
		Skirt
	);
END$$

DROP PROCEDURE IF EXISTS `moAvatarDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moAvatarDelete`( uId MEDIUMINT )
BEGIN
DELETE FROM avatar WHERE avatar_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moAvatarEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moAvatarEdit`( uId MEDIUMINT, Name VARCHAR(32), Body MEDIUMINT, Hair MEDIUMINT, TexBody MEDIUMINT, TexFace MEDIUMINT, TexHair MEDIUMINT, Hat MEDIUMINT, TexHat MEDIUMINT, TexDecoration MEDIUMINT, Skir MEDIUMINT )
BEGIN
UPDATE avatar SET
		avatar_name=Name,
		avatar_ivbody=Body, 
		avatar_ivhair=Hair, 
		avatar_ivtexboxy=TexBody, 
		avatar_ivtexface=TexFace, 
		avatar_ivtexhair=TexHair, 
		avatar_ivhat=Hat, 
		avatar_ivtexhat=TexHat, 
		avatar_ivtexdecoration=TexDecoration, 
		avatar_ivskirt=Skirt 
	WHERE avatar_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moHighScoreAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moHighScoreAdd`( gameId MEDIUMINT, uId MEDIUMINT, score INT )
BEGIN
INSERT INTO game_hs ( ghs_gameid, ghs_uid, ghs_score ) VALUE ( gameId, uId, score );
END$$

DROP PROCEDURE IF EXISTS `moHighScoreEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moHighScoreEdit`( gameId MEDIUMINT, uId MEDIUMINT, score INT )
BEGIN
UPDATE game_hs SET ghs_score=score WHERE ghs_gameid=gameId AND ghs_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moHighScoreGetGlobal`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moHighScoreGetGlobal`( `gameId` MEDIUMINT )
BEGIN
SELECT user_name, user_fbid, ghs_score FROM user, game_hs WHERE ghs_uid=user_uid AND ghs_gameid=gameId ORDER BY ghs_score DESC LIMIT 10;
END$$

DROP PROCEDURE IF EXISTS `moHighScoreSubmit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moHighScoreSubmit`( gameId MEDIUMINT, fbId BIGINT UNSIGNED, score INT )
BEGIN
	DECLARE tScore INT DEFAULT -1;
	DECLARE r INT DEFAULT 0;
	DECLARE uId MEDIUMINT DEFAULT 0;
	DECLARE n CURSOR FOR SELECT moUserExists( fbId );
	DECLARE c CURSOR FOR SELECT moHighScoreExists( gameId, fbId ); 

	OPEN n;
	FETCH n INTO uId;
	
	IF uId > 0 THEN
		OPEN c;
		FETCH c INTO tScore;
		CLOSE c;
	
		IF tScore = -1 THEN
			CALL moHighScoreAdd( gameId, uId, score );
		ELSE
			IF score > tScore THEN
				CALL moHighScoreEdit( gameId, uId, score );
			END IF;
		END IF;
	END IF;
END$$

DROP PROCEDURE IF EXISTS `moItemAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemAdd`( rcId MEDIUMINT, cost FLOAT, type SMALLINT, custom1 INTEGER, custom2 INTEGER, custom3 INTEGER )
BEGIN
INSERT INTO item ( item_resourceid, item_cost, item_type, item_custom1, item_custom2, item_custom3 ) VALUES ( rcId, cost, type, custom1, custom2, custom3 );
END$$

DROP PROCEDURE IF EXISTS `moItemDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemDelete`( itemId MEDIUMINT )
BEGIN
DELETE FROM item WHERE item_id=itemId;
END$$

DROP PROCEDURE IF EXISTS `moItemEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemEdit`( iId MEDIUMINT, rcId MEDIUMINT, cost FLOAT, type SMALLINT )
BEGIN
UPDATE item SET item_resourceid=rcId, item_cost=cost, item_type=type WHERE item_id=iId;
END$$

DROP PROCEDURE IF EXISTS `moItemEditCustom`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemEditCustom`( iId MEDIUMINT, custom1 INTEGER, custom2 INTEGER, custom3 INTEGER )
BEGIN
UPDATE item SET item_custom1=custom1, item_custom2=custom2, item_custom3=custom3 WHERE item_id=iId;
END$$

DROP PROCEDURE IF EXISTS `moItemVariantAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemVariantAdd`( iId MEDIUMINT, Color MEDIUMINT, OffX SMALLINT(6), OffY SMALLINT(6), OffZ SMALLINT(6) )
BEGIN
INSERT INTO item_variant (iv_iid, iv_color, iv_offsetx, iv_offset_y, iv_offsetz ) VALUES ( iId, Color, OffX, OffY, OffZ );
END$$

DROP PROCEDURE IF EXISTS `moItemVariantDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemVariantDelete`( ivId MEDIUMINT )
BEGIN
DELETE FROM item_variant WHERE iv_id=ivId;
END$$

DROP PROCEDURE IF EXISTS `moItemVariantEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moItemVariantEdit`( ivId MEDIUMINT, Color MEDIUMINT, OffX SMALLINT(6), OffY SMALLINT(6), OffZ SMALLINT(6) )
BEGIN
UPDATE item_variant SET iv_color=Color, iv_offset_x=OffX, iv_offset_y=OffY, iv_offset_z=OffZ WHERE iv_id=ivId;
END$$

DROP PROCEDURE IF EXISTS `moLangStringAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moLangStringAdd`( name VARCHAR(128), en VARCHAR(128), es VARCHAR(128), pt VARCHAR(128), it VARCHAR(128), de VARCHAR(128), fr VARCHAR(128) )
BEGIN
INSERT INTO lang_string ( ls_name, ls_en, ls_es, ls_pt, ls_it, ls_de, ls_fr ) VALUES ( name, en, es, pt, it, de, fr );
END$$

DROP PROCEDURE IF EXISTS `moLangStringDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moLangStringDelete`( lid MEDIUMINT )
BEGIN
DELETE FROM lang_string WHERE ls_id=lid;
END$$

DROP PROCEDURE IF EXISTS `moLangStringEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moLangStringEdit`( lid MEDIUMINT, name VARCHAR(128), en VARCHAR(128), es VARCHAR(128), pt VARCHAR(128), it VARCHAR(128), de VARCHAR(128), fr VARCHAR(128) )
BEGIN
UPDATE lang_string SET ls_name=name, ls_en=en, ls_es=es, ls_pt=pt, ls_it=it, ls_de=de, ls_fr=fr WHERE ls_id=lid;
END$$

DROP PROCEDURE IF EXISTS `moResourceAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moResourceAdd`( rcId TINYINT, rcName VARCHAR(64) )
BEGIN
INSERT INTO resource (re_type, re_name) VALUE ( rcId, rcName );
END$$

DROP PROCEDURE IF EXISTS `moResourceDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moResourceDelete`( rcId MEDIUMINT )
BEGIN
DELETE FROM resource WHERE re_id=rcId;
END$$

DROP PROCEDURE IF EXISTS `moResourceEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moResourceEdit`( rcId MEDIUMINT, rcType TINYINT, rcName VARCHAR(64) )
BEGIN
UPDATE resource SET re_type=rcType, re_name=rcName WHERE re_id=rcId;
END$$

DROP PROCEDURE IF EXISTS `moResTypeAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moResTypeAdd`( name VARCHAR(64) )
BEGIN
INSERT INTO resource_type ( rt_name ) VALUES ( name );
END$$

DROP PROCEDURE IF EXISTS `moResTypeDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moResTypeDelete`( rtid MEDIUMINT )
BEGIN
DELETE FROM resource_type WHERE rt_id=rtid;
END$$

DROP PROCEDURE IF EXISTS `moResTypeEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moResTypeEdit`( rtid MEDIUMINT, name VARCHAR(64) )
BEGIN
UPDATE resource_type SET rt_name=name WHERE rt_id=rtid;
END$$

DROP PROCEDURE IF EXISTS `moUserAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserAdd`( fbId BIGINT UNSIGNED, fbName VARCHAR(64) )
BEGIN
INSERT INTO user (user_fbid, user_name) VALUE ( fbId, fbName ); 
END$$

DROP PROCEDURE IF EXISTS `moUserBan`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserBan`( uId MEDIUMINT )
BEGIN
UPDATE user SET user_banned=1 WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserBanned`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserBanned`( uId MEDIUMINT )
BEGIN
SELECT user_banned FROM user WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserDelete`( uId MEDIUMINT )
BEGIN
DELETE FROM user WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserEdit`( uId MEDIUMINT, fbName VARCHAR(64) )
BEGIN
UPDATE user SET user_name=fbName WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserEditLang`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserEditLang`( uId MEDIUMINT, lg TINYINT )
BEGIN
UPDATE user SET user_lang=lg WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserGet`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserGet`( fbId BIGINT UNSIGNED )
BEGIN
SELECT * FROM user WHERE user_fbid=fbId;
END$$

DROP PROCEDURE IF EXISTS `moUserGetById`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserGetById`( uId MEDIUMINT )
BEGIN
SELECT * FROM user WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserPointsAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserPointsAdd`( uId MEDIUMINT, points FLOAT )
BEGIN
UPDATE user SET user_points=user_points+points WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserPointsRemove`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserPointsRemove`( uId MEDIUMINT, points FLOAT )
BEGIN
UPDATE user SET user_points=user_points-points WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moUserUnban`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moUserUnban`( uId MEDIUMINT )
BEGIN
UPDATE user SET user_banned=0 WHERE user_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moWorldAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldAdd`( woName MEDIUMINT )
BEGIN
INSERT INTO world ( world_name ) VALUES ( woName ); 
END$$

DROP PROCEDURE IF EXISTS `moWorldDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldDelete`( woUid TINYINT )
BEGIN
DELETE FROM world WHERE world_uid=woUid; 
END$$

DROP PROCEDURE IF EXISTS `moWorldEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldEdit`( woUid TINYINT, woName MEDIUMINT )
BEGIN
UPDATE world SET world_name=woName WHERE world_uid=woUid; 
END$$

DROP PROCEDURE IF EXISTS `moWorldUserAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldUserAdd`( uid MEDIUMINT, world TINYINT )
BEGIN
INSERT INTO world_user ( wu_uid, wu_world, wu_coins ) VALUES ( uid, world, 0 );
END$$

DROP PROCEDURE IF EXISTS `moWorldUserBanned`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldUserBanned`( uId MEDIUMINT, world TINYINT )
BEGIN
SELECT wu_banned FROM world_user WHERE wu_uid=uId AND wu_world=world;
END$$

DROP PROCEDURE IF EXISTS `moWorldUserCoinsAdd`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldUserCoinsAdd`( uId MEDIUMINT, coins FLOAT )
BEGIN
UPDATE world_user SET wu_coins=wu_coins+coins WHERE wu_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moWorldUserCoinsRemove`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldUserCoinsRemove`( uId MEDIUMINT, coins FLOAT )
BEGIN
UPDATE world_user SET wu_coins=wu_coins-coins WHERE wu_uid=uId;
END$$

DROP PROCEDURE IF EXISTS `moWorldUserDelete`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldUserDelete`( woId MEDIUMINT )
BEGIN
DELETE FROM world_user WHERE wu_id=wuId;
END$$

DROP PROCEDURE IF EXISTS `moWorldUserEdit`$$
CREATE DEFINER=`spartan`@`localhost` PROCEDURE `moWorldUserEdit`( wuId MEDIUMINT, coins FLOAT )
BEGIN
UPDATE world_user SET wu_coins=coins WHERE wu_id=wuId;
END$$

--
-- Functions
--
DROP FUNCTION IF EXISTS `moHighScoreExists`$$
CREATE DEFINER=`spartan`@`localhost` FUNCTION `moHighScoreExists`( gameId MEDIUMINT, fbId BIGINT UNSIGNED ) RETURNS int(11)
BEGIN
	DECLARE b INT DEFAULT -1;
	DECLARE r INT DEFAULT 1;
	DECLARE uid MEDIUMINT DEFAULT 0;

	DECLARE n CURSOR FOR SELECT moUserExists( fbId );
	DECLARE c CURSOR FOR SELECT ghs_score FROM game_hs WHERE ghs_uid=uid AND ghs_gameid=gameId;
	
	OPEN n;
	FETCH n INTO uid;

	IF uid > 0 THEN
		OPEN c;
		SELECT FOUND_ROWS() INTO r;

		IF r > 0 THEN
			FETCH c INTO b;
		END IF;

		CLOSE c;
	END IF;

	CLOSE n;
	
	RETURN b;
END$$

DROP FUNCTION IF EXISTS `moUserExists`$$
CREATE DEFINER=`spartan`@`localhost` FUNCTION `moUserExists`( fbId BIGINT UNSIGNED ) RETURNS int(2)
BEGIN
	DECLARE b INT DEFAULT 0;
	DECLARE r INT DEFAULT 0;
	DECLARE c CURSOR FOR SELECT user_uid FROM user WHERE user_fbid=fbId;
	
	OPEN c;
	SELECT FOUND_ROWS() INTO r;
	IF r > 0 THEN
		FETCH c INTO b;
	END IF;
	CLOSE c;
	
	RETURN b;
END$$

DELIMITER ;

COMMIT;
