-- -*-sql-*- 
--
-- Copyright (C) 2004, 2005, 2006 Mekensleep
--
-- Mekensleep
-- 24 rue vieille du temple
-- 75004 Paris
--       licensing@mekensleep.com
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301, USA.
--
-- Authors:
--  Loic Dachary <loic@gnu.org>
--

create table server (
  version varchar(16) not null
) ENGINE=InnoDB CHARSET=utf8;

insert into server (version) values ("1.0.30");


create table users_private (
  serial int unsigned not null,
  firstname varchar(255) default "",
  lastname varchar(255) default "",
  addr_street varchar(255) default "",
  addr_street2 varchar(255) default "",
  addr_zip varchar(64) default "",
  addr_town varchar(64) default "",
  addr_state varchar(128) default "",
  addr_country varchar(64) default "",
  phone varchar(64) default "",
  verified char default 'n',
  verified_time int default 0,

  primary key (serial)
) ENGINE=InnoDB CHARSET=utf8;


create table user2money (
  user_serial int unsigned not null,
  currency_serial int unsigned not null,
  amount int not null,
  rake int default 0 not null,
  points int default 0 not null,

  primary key (user_serial,currency_serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table currencies (
  serial INT NOT NULL AUTO_INCREMENT UNIQUE,
  url CHAR(255) NOT NULL UNIQUE,
  symbol CHAR(8),
  name CHAR(32),
  cash_out INT,

  primary key (serial, url)
)  ENGINE=InnoDB CHARSET=utf8;


create table safe (
  currency_serial INT NOT NULL,
  serial INT NOT NULL,
  name CHAR(40) NOT NULL,
  value INT NOT NULL,

  primary key (currency_serial, serial, value)
)  ENGINE=InnoDB CHARSET=utf8;

create table counter (
  transaction_id CHAR(40) NOT NULL,
  user_serial INT NOT NULL,
  currency_serial INT NOT NULL,
  serial INT NOT NULL,
  name CHAR(40) NOT NULL,
  value INT NOT NULL,
  status CHAR DEFAULT 'y' NOT NULL,
  application_data VARCHAR(255) DEFAULT '' NOT NULL,

  primary key (currency_serial, name, serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table user2table (
  user_serial int unsigned not null,
  table_serial int unsigned not null,
  money int default 0 not null,
  bet int default 0 not null,

  primary key (user_serial,table_serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table pokertables (
  serial int unsigned not null auto_increment,
  name varchar(32),
  currency_serial INT NOT NULL,

  primary key (serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table hands (
  serial int unsigned not null auto_increment,
  created timestamp not null,
  name varchar(32),
  description text not null,

  primary key (serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table user2hand (
  user_serial int not null,
  hand_serial int not null,

  primary key (user_serial, hand_serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table session (
  user_serial int not null,
  started int default 0,
  ended int default 0,
  ip varchar(16),

  primary key (user_serial, ip)
)  ENGINE=InnoDB CHARSET=utf8;


create table session_history (
  user_serial int not null,
  started int default 0,
  ended int default 0,
  ip varchar(16),

  key session_history_serial (user_serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table tourneys_schedule (
  serial int unsigned not null auto_increment,
  name varchar(32),
  description_short varchar(64),
  description_long text,
  players_quota int default 10,
  variant varchar(32),
  betting_structure varchar(32),
  seats_per_game int default 10,
  currency_serial int,
  buy_in int,
  rake int,
  sit_n_go char default 'y',
  breaks_interval int default 60,
  rebuy_delay int default 0,
  add_on int default 0,
  add_on_delay int default 60,
  start_time int default 0,

  register_time int default 0,
  respawn char default 'n',
  respawn_interval int default 0,

  primary key (serial)
)  ENGINE=InnoDB CHARSET=utf8;

--
-- Holdem sit and go
--
INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo2', 'Sit and Go 2 players, Holdem', 'Sit and Go 2 players', '2', 'holdem', 'level-15-30-no-limit', '2', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo3', 'Sit and Go 3 players, Holdem', 'Sit and Go 3 players', '3', 'holdem', 'level-15-30-no-limit', '3', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo4', 'Sit and Go 4 players, Holdem', 'Sit and Go 4 players', '4', 'holdem', 'level-15-30-no-limit', '4', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo5', 'Sit and Go 5 players, Holdem', 'Sit and Go 5 players', '5', 'holdem', 'level-15-30-no-limit', '5', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo6', 'Sit and Go 6 players, Holdem', 'Sit and Go 6 players', '6', 'holdem', 'level-15-30-no-limit', '6', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo7', 'Sit and Go 7 players, Holdem', 'Sit and Go 7 players', '7', 'holdem', 'level-15-30-no-limit', '7', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo8', 'Sit and Go 8 players, Holdem', 'Sit and Go 8 players', '8', 'holdem', 'level-15-30-no-limit', '8', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo9', 'Sit and Go 9 players, Holdem', 'Sit and Go 9 players', '9', 'holdem', 'level-15-30-no-limit', '9', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo10', 'Sit and Go single table, Holdem', 'Sit and Go single table', '10', 'holdem', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo20', 'Sit and Go 2 tables, Holdem', 'Sit and Go 2 tables', '20', 'holdem', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo30', 'Sit and Go 3 tables, Holdem', 'Sit and Go 3 tables', '30', 'holdem', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'sitngo50', 'Sit and Go 5 tables, Holdem', 'Sit and Go 5 tables', '50', 'holdem', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );
--
-- Omaha sit and go
--
INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo2', 'Sit and Go 2 players, Omaha', 'Sit and Go 2 players', '2', 'omaha', 'level-15-30-no-limit', '2', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo3', 'Sit and Go 3 players, Omaha', 'Sit and Go 3 players', '3', 'omaha', 'level-15-30-no-limit', '3', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo4', 'Sit and Go 4 players, Omaha', 'Sit and Go 4 players', '4', 'omaha', 'level-15-30-no-limit', '4', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo5', 'Sit and Go 5 players, Omaha', 'Sit and Go 5 players', '5', 'omaha', 'level-15-30-no-limit', '5', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo6', 'Sit and Go 6 players, Omaha', 'Sit and Go 6 players', '6', 'omaha', 'level-15-30-no-limit', '6', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo7', 'Sit and Go 7 players, Omaha', 'Sit and Go 7 players', '7', 'omaha', 'level-15-30-no-limit', '7', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo8', 'Sit and Go 8 players, Omaha', 'Sit and Go 8 players', '8', 'omaha', 'level-15-30-no-limit', '8', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo9', 'Sit and Go 9 players, Omaha', 'Sit and Go 9 players', '9', 'omaha', 'level-15-30-no-limit', '9', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo10', 'Sit and Go single table, Omaha', 'Sit and Go single table', '10', 'omaha', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo20', 'Sit and Go 2 tables, Omaha', 'Sit and Go 2 tables', '20', 'omaha', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo30', 'Sit and Go 3 tables, Omaha', 'Sit and Go 3 tables', '30', 'omaha', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` ( `name`, `description_short` , `description_long` , `players_quota` , `variant` , `betting_structure` , `seats_per_game` , `currency_serial` , `buy_in` , `rake` , `sit_n_go` , `start_time` , `register_time` , `respawn` , `respawn_interval` )
VALUES ( 'Ositngo50', 'Sit and Go 5 tables, Omaha', 'Sit and Go 5 tables', '50', 'omaha', 'level-15-30-no-limit', '10', 1, '300000', '0', 'y', '0', '0', 'y', '0' );

INSERT INTO `tourneys_schedule` (`serial`, `name`, `description_short`, `description_long`, `players_quota`, `variant`, `betting_structure`, `seats_per_game`, `currency_serial`, `buy_in`, `rake`, `sit_n_go`, `breaks_interval`, `rebuy_delay`, `add_on`, `add_on_delay`, `start_time`, `register_time`, `respawn`, `respawn_interval`) VALUES ('', 'regular1', 'Holdem No Limit, 200 + 10', 'Holdem No Limit, 200 + 10', '1000', 'holdem', 'level-15-30-no-limit', '10', 1, '20000', '1000', 'n', '60', '30', '1', '60', unix_timestamp(now() + interval 8 hour), unix_timestamp(now() + interval 7 hour), 'n', '0');
INSERT INTO `tourneys_schedule` (`serial`, `name`, `description_short`, `description_long`, `players_quota`, `variant`, `betting_structure`, `seats_per_game`, `currency_serial`, `buy_in`, `rake`, `sit_n_go`, `breaks_interval`, `rebuy_delay`, `add_on`, `add_on_delay`, `start_time`, `register_time`, `respawn`, `respawn_interval`) VALUES ('', 'regular2', 'Holdem No Limit, 2000 + 100', 'Holdem No Limit, 2000 + 100', '1000', 'holdem', 'level-15-30-no-limit', '10', 1, '200000', '10000', 'n', '60', '30', '1', '60', unix_timestamp(now() + interval 9 hour), unix_timestamp(now() + interval 8 hour), 'n', '0');
INSERT INTO `tourneys_schedule` (`serial`, `name`, `description_short`, `description_long`, `players_quota`, `variant`, `betting_structure`, `seats_per_game`, `currency_serial`, `buy_in`, `rake`, `sit_n_go`, `breaks_interval`, `rebuy_delay`, `add_on`, `add_on_delay`, `start_time`, `register_time`, `respawn`, `respawn_interval`) VALUES ('', 'regular1', 'Omaha No Limit, 200 + 10', 'Omaha No Limit, 200 + 10', '1000', 'omaha', 'level-15-30-no-limit', '10', 1, '20000', '1000', 'n', '60', '30', '1', '60', unix_timestamp(now() + interval 10 hour), unix_timestamp(now() + interval 9 hour), 'n', '0');
INSERT INTO `tourneys_schedule` (`serial`, `name`, `description_short`, `description_long`, `players_quota`, `variant`, `betting_structure`, `seats_per_game`, `currency_serial`, `buy_in`, `rake`, `sit_n_go`, `breaks_interval`, `rebuy_delay`, `add_on`, `add_on_delay`, `start_time`, `register_time`, `respawn`, `respawn_interval`) VALUES ('', 'regular2', 'Omaha No Limit, 2000 + 100', 'Omaha No Limit, 2000 + 100', '1000', 'omaha', 'level-15-30-no-limit', '10', 1, '200000', '10000', 'n', '60', '30', '1', '60', unix_timestamp(now() + interval 11 hour), unix_timestamp(now() + interval 10 hour), 'n', '0');
INSERT INTO `tourneys_schedule` (`serial`, `name`, `description_short`, `description_long`, `players_quota`, `variant`, `betting_structure`, `seats_per_game`, `currency_serial`, `buy_in`, `rake`, `sit_n_go`, `breaks_interval`, `rebuy_delay`, `add_on`, `add_on_delay`, `start_time`, `register_time`, `respawn`, `respawn_interval`) VALUES ('', 'regular1', 'Omaha8 No Limit, 200 + 10', 'Omaha8 No Limit, 200 + 10', '1000', 'omaha8', 'level-15-30-no-limit', '10', 1, '20000', '1000', 'n', '60', '30', '1', '60', unix_timestamp(now() + interval 12 hour), unix_timestamp(now() + interval 11 hour), 'n', '0');
INSERT INTO `tourneys_schedule` (`serial`, `name`, `description_short`, `description_long`, `players_quota`, `variant`, `betting_structure`, `seats_per_game`, `currency_serial`, `buy_in`, `rake`, `sit_n_go`, `breaks_interval`, `rebuy_delay`, `add_on`, `add_on_delay`, `start_time`, `register_time`, `respawn`, `respawn_interval`) VALUES ('', 'regular2', 'Omaha8 No Limit, 2000 + 100', 'Omaha8 No Limit, 2000 + 100', '1000', 'omaha8', 'level-15-30-no-limit', '10', 1, '200000', '10000', 'n', '60', '30', '1', '60', unix_timestamp(now() + interval 13 hour), unix_timestamp(now() + interval 12 hour), 'n', '0');


create table tourneys (
  serial int unsigned not null auto_increment,
  name varchar(32),
  description_short varchar(64),
  description_long text,
  players_quota int default 10,
  variant varchar(32),
  betting_structure varchar(32),
  seats_per_game int default 10,
  currency_serial int,
  buy_in int,
  rake int,
  sit_n_go char default 'y',
  breaks_interval int default 60,
  rebuy_delay int default 0,
  add_on int default 0,
  add_on_delay int default 60,
  start_time int default 0,

  finish_time int default 0,
  state varchar(32) default "registering",
  schedule_serial int,
  add_on_count int default 0,
  rebuy_count int default 0,

  primary key (serial)
)  ENGINE=InnoDB CHARSET=utf8;


create table user2tourney (
  user_serial int not null,
  tourney_serial int not null,
  table_serial int,
  rank int default -1,

  primary key (user_serial, tourney_serial)
)  ENGINE=InnoDB CHARSET=utf8;
INSERT INTO users_private (serial) VALUES (2);
INSERT INTO users_private (serial) VALUES (3);

--
-- Transactions between users
--
create table users_transactions (
  from_serial INT UNSIGNED NOT null,
  to_serial INT UNSIGNED NOT null,
  modified TIMESTAMP,
  amount INT DEFAULT 0,
  currency_serial INT NOT NULL,
  status CHAR DEFAULT 'n',
  notes TEXT,

  key (from_serial,to_serial)
);

--
-- Affiliate description
--
create table affiliates (
  serial int unsigned not null auto_increment,
  modified TIMESTAMP,
  created int unsigned not null,

  users_count int unsigned default 0,
  users_rake int unsigned default 0,
  users_points int unsigned default 0,

  --
  -- percentage of the rake assigned to the affiliate
  --
  share int unsigned default 0, 

  companyname varchar(255) default "",
  firstname varchar(255) default "",
  lastname varchar(255) default "",
  addr_street varchar(255) default "",
  addr_street2 varchar(255) default "",
  addr_zip varchar(64) default "",
  addr_town varchar(64) default "",
  addr_state varchar(128) default "",
  addr_country varchar(64) default "",
  phone varchar(64) default "",

  url TEXT,
  notes TEXT,

  primary key (serial)
);

