#<pre>
-- phpMyAdmin SQL Dump
-- version 3.1.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- modified by PTD on 3/8/2014
-- Generation Time: Mar 20, 2009 at 11:22 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `casino`
--

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

--
-- Table structure for table `agent`
--

CREATE TABLE IF NOT EXISTS `agent` (
  `agentID` smallint unsigned NOT NULL auto_increment COMMENT 'Primary Key',
  `gameID` smallint unsigned NOT NULL COMMENT 'Foreign Key to game',
  `playerID` smallint unsigned NOT NULL COMMENT 'Foreign Key to Player',
  `version` float NOT NULL COMMENT 'SW Version',
  `className` varchar(32) NOT NULL COMMENT 'Java Class Name',
  `creator` varchar(32) NOT NULL COMMENT 'Programmer ',
  PRIMARY KEY  (`agentID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1002 ;

--
-- Dumping data for table `agent`
--

INSERT INTO `agent` (`agentID`, `gameID`, `playerID`, `version`, `className`, `creator`) VALUES
(1000, 1, 5, 1, 'swAgent', 'Joe Student'),
(1001, 1, 6, 0.5, 'swAgentMinnie', 'MInnie Mouse');

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

--
-- Table structure for table `casino`
--

CREATE TABLE IF NOT EXISTS `casino` (
  `casinoID` smallint unsigned NOT NULL COMMENT 'primary key',
  `name` varchar(32) NOT NULL COMMENT 'Casino Name',
  `url` varchar(128) NOT NULL COMMENT 'Web Page cim based Casino',
  `playerID` smallint unsigned NOT NULL COMMENT 'guest account for Casino',
  `description` text NOT NULL COMMENT 'HTML Casino Description',
  PRIMARY KEY  (`casinoID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `casino`
--

INSERT INTO `casino` (`casinoID`, `name`, `url`, `playerID`, `description`) VALUES
(100, 'Greatest Ever', 'http://cim.saddleback.edu/~jstudent0/casino', 3, 'Joe Students Great Casino'),
(101, 'Disney', 'http://cim.saddleback.edu/~mmouse0', 4, 'Disneylands only Casino - Except for Cruises');

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

--
-- Table structure for table `casinoGame`
--

CREATE TABLE IF NOT EXISTS `casinoGame` (
  `casinoID` smallint unsigned NOT NULL COMMENT 'foreign key to casino',
  `gameID` smallint unsigned NOT NULL COMMENT 'foreign key to game'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `casinoGame`
--

INSERT INTO `casinoGame` (`casinoID`, `gameID`) VALUES
(100, 1),
(100, 2),
(101, 1),
(100, 3);

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

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

CREATE TABLE IF NOT EXISTS `game` (
  `gameID` smallint unsigned NOT NULL auto_increment COMMENT 'primary key',
  `name` varchar(16) NOT NULL COMMENT 'unique game name may change this to support various game rules',
  `description` text NOT NULL COMMENT 'Descriptive game name',
  `rules` varchar(64) NOT NULL COMMENT 'rules -payout',
  PRIMARY KEY  (`gameID`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

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

INSERT INTO `game` (`gameID`, `name`, `description`, `rules`) VALUES
(1, 'blackjack21', 'Blackjack 21', 'Push on Ties, no 5 Card Charlie, pays 2:1 on 21 (two cards)'),
(2, 'videoPoker', 'Single Player Casino Video Poker', 'Standard Vegas Rules'),
(3, 'craps', 'Craps 7 - 11, 2 - 3 or 12', 'Standard Rules'),
(4, 'texasHoldem', '7 Card Text holdem', 'standard rules' ),
(5, 'chat', 'chat window active w video poker', 'just chat' ),
(6, 'count', 'Count server, sends count then disconnects', 'just count' );

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

--
-- Table structure for table `gamePlayer`
--

CREATE TABLE IF NOT EXISTS `gamePlayer` (
  `gameTableID` smallint unsigned NOT NULL COMMENT 'foreign key to gameTable',
  `playerID` smallint unsigned NOT NULL COMMENT 'foreign key to player',
  `netGain` double NOT NULL COMMENT 'net money gain for this table-game',
  `rounds` smallint unsigned NOT NULL COMMENT 'number of rounds played so far',
  `gamesWon` smallint NOT NULL COMMENT 'games won so far',
  `startTime` timestamp default CURRENT_TIMESTAMP not  NULL COMMENT 'time player started game',
  `endTime` timestamp null default null COMMENT 'time player left or NULL if player still in game',
  `clientIP` int unsigned NOT NULL COMMENT 'client IP address for player using inet_aton'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `gamePlayer`
--

INSERT INTO `gamePlayer` (`gameTableID`, `playerID`, `netGain`, `rounds`, `gamesWon`, `clientIP`) VALUES
(2000, 1, 100, 5, 1, inet_aton('127.0.0.1') ),
(2000, 1, 500, 100, 50, inet_aton( '127.0.0.1') ),
(2000, 5, -500, 2000, 700, inet_aton( '128.196.52.101') ),
(2002, 5, -500, 20, 5, inet_aton( '127.0.0.1' ) ),
(2002, 3, 250, 1000, 100, inet_aton( '255.255.255.255' ) ),
(2003, 7, -500, 1000, 100, inet_aton( '128.169.21.152') ),
(2003, 3, 100, 10, 5, inet_aton( '128.169.21.151' ) );

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

--
-- Table structure for table `gameTable`
--

CREATE TABLE IF NOT EXISTS `gameTable` (
  `gameTableID` int unsigned NOT NULL auto_increment COMMENT 'primary key',
  `gameID` smallint unsigned NOT NULL COMMENT 'foreign key to specific game, IP could change',
  `casinoID` smallint default 100 NOT NULL COMMENT 'ID or casino hosting table',
  `IP` int unsigned default 2130706433 # value from inet_aton( '127.0.0.1' ) 
   NOT NULL COMMENT 'mysql functions inet_aton, and inet_ntoa',
  `port` smallint unsigned default 8000 NOT NULL COMMENT 'game port number default 8000',
  `chairs` smallint unsigned default 1 NOT NULL COMMENT 'number of chairs at table',
  `startTime` timestamp default CURRENT_TIMESTAMP not  NULL COMMENT 'time table started',
  `endTime` timestamp null default null COMMENT 'time table closed or NULL if table is still active',
  PRIMARY KEY  (`gameTableID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2004 ;

--
-- Dumping data for table `gameTable`
--  For NetBeans MySQL Drop the gameTableID, and Values

INSERT INTO `gameTable` (`gameTableID`, `gameID`, `casinoID`, `IP`, `chairs`, `startTime`, `endTime`) VALUES
(2000, 1, 0, inet_aton( '10.151.151.151' ), 1, '2014-03-08 22:07:18', '2014-03-08 22:07:22'),
(2002, 1, 0, inet_aton( '10.128.128.128' ), 5, '2014-03-08 23:19:51', NULL),
(2003, 2, 0, inet_aton( '192.28.192.192' ), 3, '2014-03-09 12:40:42', '2014-03-09 14:40:47');

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

--
-- Table structure for table `player`
--

CREATE TABLE IF NOT EXISTS `player` (
  `playerID` smallint unsigned NOT NULL auto_increment COMMENT 'primary key',
  `name` varchar(32) NOT NULL COMMENT 'first name aka username name',
  `credit` double NOT NULL default '100000' COMMENT 'amount of credit player has to gamble',
  `password` char(32) NOT NULL COMMENT 'md5 lastname w salt encrypted password',
  `email` varchar(32) NOT NULL COMMENT 'player email address',
  PRIMARY KEY  (`playerID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `player`
--

INSERT INTO `player` (`playerID`, `name`, `credit`,  `password`, `email`) VALUES
(1, 'Minnie', 10000, md5( concat( "mouse" 'cs4bSpring14' )), 'mmouse@disney.com'),
(2, 'Joe', 20000, md5( concat( "student", 'cs4bSpring14' )), 'jstudent0@cim.saddleback.edu' ),
(3, 'guestJoe', 10000, md5( concat( "guest", 'cs4bSpring14' )), 'unknown'),
(4, 'guestMinnie', 10000, md5( concat( "mouse", 'cs4bSpring14' )), 'guestMinnie@sb.edu' ),

(5, 'BJSWAgent', 10000, md5( concat( "bjsw", 'cs4bSpring14' )), 'agent@sb.edu' ),

(6, 'MinnieAgent', 15000, md5( concat( "agent", 'cs4bSpring14' ) ),  ''),
(7, 'Joe Loser', -500, md5( concat( "loser", 'cs4bSpring14' ) ), 'jloser@saddleback.edu');

drop view if exists activeGamePlayer;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `activeGamePlayer` 

AS select `gamePlayer`.`playerID` AS `playerID`,
          `player`.`name` AS `playerName`,
          `gameTable`.`gameTableID` AS `gameTableID` 

from ((`gamePlayer` join `player`) join `gameTable`) 
where ((`gamePlayer`.`gameTableID` = `gameTable`.`gameTableID`)
       and isnull(`gameTable`.`endTime`)
       and isnull( `gamePlayer`.`endTime`)
       and (`gamePlayer`.`playerID` = `player`.`playerID`));

drop function if exists availableSeats;
delimiter $$
create function availableSeats( gTID smallint )
RETURNS int 
READS SQL DATA SQL SECURITY DEFINER
BEGIN
       declare n int;
       set n =
        cast(
        cast( ( select chairs from gameTable where gameTableID = gTID) as SIGNED ) 
	- 
        cast( ( select count( playerID ) from gamePlayer
          where gameTableID = gTID and endTime is null ) as SIGNED )
	as SIGNED );
        

    return n;
END;
$$
delimiter ;

drop view if exists activeGameTable;

CREATE ALGORITHM = UNDEFINED VIEW `activeGameTable`
 (gameTableID, name, chairs, availableChairs, IP, port)
 AS select gameTableID, game.name, chairs,
           availableSeats( gameTableID ), inet_ntoa(IP), port
from gameTable, game
where gameTable.gameID = game.gameID and endTime is null;