# mysqladmin create erikpiller_vrt
# mysqlshow erikpiller_vrt
# mysqldump -v erikpiller_vrt
# mysqlaccess erikpiller_vrt

# INT       = 4 Byte = FFFFFFFF = 4294967295
# MEDIUMINT = 3 Byte = FFFFFF   = 16777215
# SMALLINT  = 2 Byte = FFFF     = 65535
# TINYINT   = 1 Byte = FF       = 255

# TINYTEXT 	 = 255
# TEXT 		 = 65535
# MEDIUMTEXT = 16777215
# LONGTEXT	 = 4294967295

# all _id fields are numbers in tables
# names ARE case sensitive!

# show tables;

# put this in the user table of mysql database like so
# mysql -v mysql
# INSERT INTO user VALUES ('localhost', 'erikpiller_vrt', password('qw3rty'), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');
# INSERT INTO db VALUES ('localhost', 'erikpiller_vrt', 'erikpiller_vrt', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
# mysqladmin reload

# mysql -u erikpiller_vrt -p qw3rty erikpiller_vrt

/////////////////////////////////////////////////////////////////////////////////////////////
CREATE TABLE TradeShow_Table (
  tradeshow_id int(10) unsigned NOT NULL auto_increment,
  tradeshow_name varchar(100) NOT NULL default '',
  tradeshow_directory_path varchar(255) NOT NULL default '',
  tradeshow_login varchar(15) NOT NULL default '',
  tradeshow_password varchar(15) NOT NULL default '',
  tradeshow_email varchar(40) NOT NULL default '',
  tradeshow_register tinyint(1) unsigned NOT NULL default '1',
  tradeshow_adban_enable tinyint(1) unsigned NOT NULL default '1',
  tradeshow_adban_image varchar(50) NOT NULL default '',
  tradeshow_adban_url varchar(255) NOT NULL default '',
  tradeshow_banner_logo_image varchar(30) default '',
  tradeshow_style_table_id smallint(5) unsigned default '1',
  tradeshow_bg_color varchar(6) default '7DA7D9',
  tradeshow_font_color varchar(6) default '000000',
  tradeshow_map_coordinates text,
  tradeshow_floor_plan_image varchar(30) default '',
  tradeshow_max_booths smallint(5) unsigned default '1',
  tradeshow_active tinyint(1) unsigned default '1',
  tradeshow_visitors int(10) unsigned default '0',
  tradeshow_incept_date date NOT NULL default '0000-00-00',
  tradeshow_ptype enum('public','private') NOT NULL default 'public',
  tradeshow_description tinytext NOT NULL,
  tradeshow_long_description text NOT NULL,
  PRIMARY KEY  (tradeshow_id),
  KEY tradeshow_login (tradeshow_login),
  KEY tradeshow_password (tradeshow_password)
) TYPE=MyISAM;

# This allows a tradeshow to have unlimited booths associated with it
# and the same booth in multiple shows too?
CREATE TABLE TradeBooth_LinkTable (
  tradeshow_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  booth_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  booth_number smallint(4) UNSIGNED NOT NULL, # the number that the booth is for THIS tradeshow
  INDEX (tradeshow_table_id),
  INDEX (booth_number)
);

# Since booths *in theory* could belong to different tradeshows simultaneously,
# we need to exclude ad banners for them this way. 
CREATE TABLE TradeBooth_AdBanDisableTable (
  tradeshow_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  booth_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  INDEX (tradeshow_table_id),
  INDEX (booth_table_id)
);

CREATE TABLE Booth_Table (
  booth_id int(10) unsigned NOT NULL auto_increment,
  booth_name varchar(30) NOT NULL default '',
  booth_company_email varchar(255) NOT NULL default '',
  booth_company_url varchar(255) NOT NULL default '',
  booth_company_name varchar(30) default '',
  booth_company_logo varchar(50) default '',
  booth_directory_path varchar(255) NOT NULL default '',
  booth_login varchar(15) NOT NULL default '',
  booth_password varchar(15) NOT NULL default '',
  booth_tsadban_disable tinyint(1) unsigned NOT NULL default '0',
  booth_adban_enable tinyint(1) unsigned NOT NULL default '1',
  booth_adban_image varchar(50) NOT NULL default '',
  booth_adban_url varchar(255) NOT NULL default '',
  booth_banner_logo_image varchar(30) default '',
  booth_style_table_id smallint(5) unsigned default '0',
  booth_color_table_id smallint(5) unsigned default '0',
  booth_about_text text,
  booth_about_image varchar(255) default '',
  booth_contest_headline varchar(255) default '',
  booth_contest_prize_image varchar(255) default '',
  booth_contest_description text,
  booth_contest_rules mediumtext,
  booth_about_active tinyint(1) unsigned default '1',
  booth_contest_active tinyint(1) unsigned default '1',
  booth_product_active tinyint(1) unsigned default '1',
  booth_brochure_active tinyint(1) unsigned default '1',
  booth_demo_active tinyint(1) unsigned default '1',
  booth_news_active tinyint(1) unsigned default '1',
  booth_contact_active tinyint(1) unsigned default '1',
  booth_vrflag enum('true','false') NOT NULL default 'true',
  booth_showlink enum('true','false') NOT NULL default 'true',
  booth_visitors int(10) unsigned default '0',
  booth_description tinytext NOT NULL,
  PRIMARY KEY  (booth_id),
  KEY booth_login (booth_login),
  KEY booth_password (booth_password),
  KEY booth_vrflag (booth_vrflag)
) TYPE=MyISAM;

INSERT INTO Booth_Table (booth_id, booth_number, booth_name, booth_company_name, booth_directory_path, booth_login, booth_password) VALUES (null, '1', 'test booth', 'some company', 'this path', 'login', 'password');

CREATE TABLE Product_Table (
  product_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
  product_both_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  product_title varchar(255),
  product_image varchar(255),
  product_short_description varchar(150),
  product_long_description TEXT,
  product_active tinyint(1) UNSIGNED DEFAULT '1',
  INDEX (product_both_table_id)
);

INSERT INTO Product_Table VALUES (null, '4', 'product_title', 'image', 'product_short_description', 'product_long_description', '1');
INSERT INTO Product_Table VALUES (null, '4', 'product two', 'image.jpg', 'this is a groovy product', 'this product is soo groovey that you want to buy it now!', '1');

CREATE TABLE Brochure_Table (
  brochure_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
  brochure_both_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  brochure_title varchar(255),
  brochure_file varchar(255),
  brochure_short_description varchar(150),
  brochure_long_description TEXT,
  brochure_active tinyint(1) UNSIGNED DEFAULT '1',
  INDEX (brochure_both_table_id)
);
INSERT INTO Brochure_Table VALUES (null, '4', 'brochure two', 'mybrochure.doc', 'this is a groovy brochure', 'this brochure is soo groovey that you want to buy it now!', '1');

CREATE TABLE Demo_Table (
  demo_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
  demo_both_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  demo_title varchar(255),
  demo_url varchar(255),
  demo_short_description varchar(255),
  demo_long_description TEXT,
  demo_active tinyint(1) UNSIGNED DEFAULT '1',
  INDEX (demo_both_table_id)
);
INSERT INTO Demo_Table VALUES (null, '4', 'demo two', 'http://www.demo.org', 'this is a groovy demo', 'this demo is soo groovey that you want to buy it now!', '1');

CREATE TABLE News_Table (
  news_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
  news_both_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  news_headline varchar(255),
  news_date DATE DEFAULT '000000' NOT NULL,
  news_story MEDIUMTEXT,
  news_active tinyint(1) UNSIGNED DEFAULT '1',
  INDEX (news_both_table_id)
);
INSERT INTO News_Table VALUES (null, '4', 'news headline', '000000', 'some news story goes here', '1');

CREATE TABLE Contact_Table (
  contact_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
  contact_both_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  contact_title varchar(255),
  contact_name varchar(255),
  contact_email varchar(255),
  contact_url varchar(255),
  contact_phone varchar(20),
  contact_fax varchar(20),
  contact_street1 varchar(255),
  contact_street2 varchar(255),
  contact_city varchar(255),
  contact_state varchar(255),
  contact_zip varchar(50),
  contact_country varchar(255),
  contact_active tinyint(1) UNSIGNED DEFAULT '1',
  INDEX (contact_both_table_id)
);
INSERT INTO Contact_Table VALUES (null, '4', 'President', 'Daevid Vincent', 'daevid@daevid.com', 'http://daevid.com', '(425)555-1212', 'NA', 'street1', 'street2', 'Redmond', 'WA', '98052-1111', 'USA', '1');

# this table links what styles a given booth(manager) has access to
CREATE TABLE BoothStyle_LinkTable (
  booth_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  style_table_id SMALLINT(5) UNSIGNED DEFAULT '0' NOT NULL,
  INDEX (booth_table_id)
);

# this table links what styles a given tradeshow(manager) has access to
CREATE TABLE TradeStyle_LinkTable (
  tradeshow_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  style_table_id SMALLINT(5) UNSIGNED DEFAULT '0' NOT NULL,
  INDEX (tradeshow_table_id)
);
 
# drop table Style_Table;  
CREATE TABLE Style_Table (
   style_id SMALLINT(5) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
   style_name varchar(30) NOT NULL,
   style_description varchar(50) NOT NULL,
   #style_home_image varchar(30) NOT NULL DEFAULT 'home.jpg',
   #style_about_image varchar(30) NOT NULL DEFAULT 'about.jpg',
   #style_news_image varchar(30) NOT NULL DEFAULT 'news.jpg',
   #style_brochure_image varchar(30) NOT NULL DEFAULT 'brochure.jpg',
   #style_demo_image varchar(30) NOT NULL DEFAULT 'demo.jpg',
   #style_contest_image varchar(30) NOT NULL DEFAULT 'contest.jpg',
   #style_contact_image varchar(30) NOT NULL DEFAULT 'contact.jpg',
   #style_product_image varchar(30) NOT NULL DEFAULT 'product.jpg',
   #style_email_image varchar(30) NOT NULL DEFAULT 'email.jpg',
   style_custom_flag tinyint(1) UNSIGNED DEFAULT '0' NOT NULL
);

insert into Style_Table (style_id, style_name, style_description) values (null,'sketch','This is the default sketch type');
insert into Style_Table (style_id, style_name, style_description) values (null,'sketch-green','This is the default sketch type but in a greenish hue');
insert into Style_Table (style_id, style_name, style_description, style_custom_flag) values (null,'Custom_1','This is a custom style', 1);

CREATE TABLE Color_Table (
   color_id TINYINT(3) UNSIGNED DEFAULT '1' NOT NULL PRIMARY KEY auto_increment,
   color_name varchar(20) NOT NULL,
   color_rgb_value varchar(11) NOT NULL,
   color_hex_value varchar(6) NOT NULL
);
   
insert into Color_Table values (1, 'Charcoal','83,71,65','534741');  
insert into Color_Table values (null, 'Light Grey','193,193,193','C1C1C1');  
insert into Color_Table values (null, 'Dark Purple','51,0,102','330066');  
insert into Color_Table values (null, 'Light Purple','161,134,190','A186BE');  
insert into Color_Table values (null, 'Bright Green','141,198,63','8DC63F');  
insert into Color_Table values (null, 'Dark Green','64,102,24','406618');  
insert into Color_Table values (null, 'Bright Yellow','255,242,0','FFF200');  
insert into Color_Table values (null, 'Soft Yellow','255,247,153','FFF799');  
insert into Color_Table values (null, 'Dark Orange','242,101,34','F26522');  
insert into Color_Table values (null, 'Light Orange','251,175,93','FBAF5D');  
insert into Color_Table values (null, 'Bright Red','237,28,36','ED1C24');  
insert into Color_Table values (null, 'Pink','245,152,157','F5989D');  
insert into Color_Table values (null, 'Dark Blue','0,74,128','004A80');  
insert into Color_Table values (null, 'Light Blue','125,167,217','7DA7D9');  
insert into Color_Table values (null, 'Brown','117,76,36','754C24');  
insert into Color_Table values (null, 'Light Brown','198,156,109','C69C6D');  

# drop table Customer_Table;
CREATE TABLE Customer_Table (
  customer_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment,
  customer_link_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL,
  customer_link_type enum("booth", "tradeshow") NOT NULL,
  customer_name varchar(30),
  customer_company varchar(30),
  customer_title varchar(30),
  customer_street1 varchar(30),
  customer_street2 varchar(30),
  customer_city varchar(30),
  customer_state varchar(30),
  customer_zip varchar(10),
  customer_country varchar(30),
  customer_phone varchar(17),
  customer_email varchar(30),
  customer_url varchar(100),
  customer_date DATE DEFAULT '000000' NOT NULL,
  customer_type enum("new", "more info", "contest"),
  customer_status enum("new", "viewed") DEFAULT "new" NOT NULL,
  customer_password varchar(15),
  customer_registered tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
  customer_last_visit_date DATE DEFAULT '000000' NOT NULL,
  customer_num_visits MEDIUMINT(8) UNSIGNED DEFAULT '0' NOT NULL,
  customer_mail_list tinyint(1) UNSIGNED DEFAULT '0',
  customer_comment TEXT,
  INDEX (customer_link_table_id)
);

# delete from Customer_Table;
INSERT INTO Customer_Table VALUES (null, '4', 'booth', 'Daevid Vincent', 'the Matrix', 'Prez', 'street1', 'street2', 'Redmond', 'WA', '98052-1111', 'USA', '(425)555-1212', 'daevid@daevid.com', 'http://daevid.com', '020601', 'contest', 'new', 'secret', '1', '020601', '5', 'This booth is really swell and I hope I win the contest.');
INSERT INTO Customer_Table VALUES (null, '4', 'booth', 'Joe Mamma', 'BootyTime, Inc.', 'CTO', '123 Anystreet', '#222', 'Anytown', 'XX', '12345', 'USA', '(800)555-1212', 'joe@bootytime.net', 'http://bootytime.net', '020520', 'more info', 'new', 'secret pass', '0', '020520', '23', 'My company needs a new name. Can you suggest one for us?');
INSERT INTO Customer_Table VALUES (null, '1951776704', 'tradeshow', 'Jeff Jones', 'ABC Company', 'Marketing Director', '123 Main Street', 'Suite 101', 'Anytown', 'California', '94583', 'USA', '212-555-1212 ext123', 'jeff@abc.com', 'http://www.abc.com', '020604', 'new', 'new', 'jjpass', '0', '020604', '345', '');
INSERT INTO Customer_Table VALUES (null, '1951776704', 'tradeshow', 'The Devil', 'Torture-R-Us', 'PR Person', '666 Hell\'s Highway', 'cell 9', 'SatanTown', 'Hell', '66666', 'Lower Purgatory', '666-666-6666 x666', 'satan@satan.org', 'http://www.satan.org', '020606', 'new', 'new', 'i rule', '1', '020606', '666', '');
/////////////////////////////////////////////////////////////////////////////////////////////


// show customers in the past 5 days
SELECT COUNT(1) FROM Customer_Table WHERE customer_link_table_id = 1951776704 AND customer_link_type = 'tradeshow' AND  TO_DAYS(NOW()) - TO_DAYS(customer_last_visit_date) <= 5;

