-- Copyright (C) 2001 YesSoftware. All rights reserved.
-- RegForm_MySQL.sql

drop table if exists lookup_ages;
create table lookup_ages
(
	age_id integer auto_increment primary key,
	age_desc varchar(15)
);

insert into lookup_ages (age_id,age_desc) values
(1,'less than 13'),
(2,'13-18'),
(3,'18-24'),
(4,'25-34'),
(5,'35-50'),
(6,'51-65'),
(7,'over 65');

drop table if exists lookup_countries;
create table lookup_countries
(
	country_id integer auto_increment primary key,
	country_desc varchar(40)
);

insert into lookup_countries (country_id,country_desc) values
(1,'United States'),
(4,'Canada'),
(5,'United Kingdom'),
(6,'Afghanistan'),
(7,'Albania'),
(8,'Algeria'),
(9,'American Samoa'),
(10,'Andorra'),
(11,'Angola'),
(12,'Anguilla'),
(13,'Antigua and Barbuda'),
(14,'Argentina'),
(15,'Armenia'),
(16,'Aruba'),
(17,'Australia'),
(18,'Austria'),
(19,'Azerbaijan Republic'),
(20,'Bahamas'),
(21,'Bahrain'),
(22,'Bangladesh'),
(23,'Barbados'),
(24,'Belarus'),
(25,'Belgium'),
(26,'Belize'),
(27,'Benin'),
(28,'Bermuda'),
(29,'Bhutan'),
(30,'Bolivia'),
(31,'Bosnia and Herzegovina'),
(32,'Botswana'),
(33,'Brazil'),
(34,'British Virgin Islands'),
(35,'Brunei Darussalam'),
(36,'Bulgaria'),
(37,'Burkina Faso'),
(38,'Burma'),
(39,'Burundi'),
(40,'Cambodia'),
(41,'Cameroon'),
(43,'Cape Verde Islands'),
(44,'Cayman Islands'),
(45,'Central African Republic'),
(46,'Chad'),
(47,'Chile'),
(48,'China'),
(49,'Colombia'),
(50,'Comoros'),
(51,'Congo, Democratic Republic of'),
(52,'Cook Islands'),
(53,'Costa Rica'),
(54,'Cote d Ivoire (Ivory Coast)'),
(55,'Croatia, Democratic Republic of the'),
(56,'Cuba'),
(57,'Cyprus'),
(58,'Czech Republic'),
(59,'Denmark'),
(60,'Djibouti'),
(61,'Dominica'),
(62,'Dominican Republic'),
(63,'Ecuador'),
(64,'Egypt'),
(65,'El Salvador'),
(66,'Equatorial Guinea'),
(67,'Eritrea'),
(68,'Estonia'),
(69,'Ethiopia'),
(70,'Falkland Islands (Islas Makvinas)'),
(71,'Fiji'),
(72,'Finland'),
(73,'France'),
(74,'French Guiana'),
(75,'French Polynesia'),
(76,'Gabon Republic'),
(77,'Gambia'),
(78,'Georgia'),
(79,'Germany'),
(80,'Ghana'),
(81,'Gibraltar'),
(82,'Greece'),
(83,'Greenland'),
(84,'Grenada'),
(85,'Guadeloupe'),
(86,'Guam'),
(87,'Guatemala'),
(88,'Guernsey'),
(89,'Guinea'),
(90,'Guinea-Bissau'),
(91,'Guyana'),
(92,'Haiti'),
(93,'Honduras'),
(94,'Hong Kong'),
(95,'Hungary'),
(96,'Iceland'),
(97,'Japan'),
(98,'Jersey'),
(99,'Jordan'),
(100,'Kazakhstan'),
(101,'Kenya Coast Republic'),
(102,'Kiribati'),
(103,'Korea, North'),
(104,'Korea, South'),
(105,'Kuwait'),
(106,'Kyrgyzstan'),
(107,'Laos'),
(108,'Latvia'),
(109,'Lebanon, South'),
(110,'Lesotho'),
(111,'Liberia'),
(112,'Libya'),
(113,'Liechtenstein'),
(114,'Lithuania'),
(115,'Luxembourg'),
(116,'Macau'),
(117,'Macedonia'),
(118,'Madagascar'),
(119,'Malawi'),
(120,'Malaysia'),
(121,'Maldives'),
(122,'Mali'),
(123,'Malta'),
(124,'Marshall Islands'),
(125,'Martinique'),
(126,'Mauritania'),
(127,'Mauritius'),
(128,'Mayotte'),
(129,'Mexico'),
(130,'Moldova'),
(131,'Monaco'),
(132,'Mongolia'),
(133,'Montserrat'),
(134,'Morocco'),
(135,'Mozambique'),
(136,'Namibia'),
(137,'Nauru'),
(138,'Nepal'),
(139,'Netherlands'),
(140,'Netherlands Antilles'),
(141,'New Caledonia'),
(142,'New Zealand'),
(143,'Nicaragua'),
(144,'Niger'),
(145,'Nigeria'),
(146,'Niue'),
(147,'Norway'),
(148,'Oman'),
(149,'Pakistan'),
(150,'Palau'),
(151,'Panama'),
(152,'Papua New Guinea'),
(153,'Paraguay'),
(154,'Peru'),
(155,'Philippines'),
(156,'Poland'),
(157,'Portugal'),
(158,'Puerto Rico'),
(159,'Qatar'),
(160,'Romania'),
(161,'Russian Federation'),
(162,'Rwanda'),
(163,'Saint Helena'),
(164,'Saint Kitts-Nevis'),
(165,'Saint Lucia'),
(166,'Saint Pierre and Miquelon'),
(167,'Saint Vincent and the Grenadines'),
(168,'San Marino'),
(169,'Saudi Arabia'),
(170,'Senegal'),
(171,'Seychelles'),
(172,'Sierra Leone'),
(173,'Singapore'),
(174,'Slovakia'),
(175,'Slovenia'),
(176,'Solomon Islands'),
(177,'Somalia'),
(178,'South Africa'),
(179,'Spain'),
(180,'Sri Lanka'),
(181,'Sudan'),
(182,'Suriname'),
(183,'Svalbard'),
(184,'Swaziland'),
(185,'Sweden'),
(186,'Switzerland'),
(187,'Syria'),
(188,'Tahiti'),
(189,'Taiwan'),
(190,'Tajikistan'),
(191,'Tanzania'),
(192,'Thailand'),
(193,'Togo'),
(194,'Tonga'),
(195,'Trinidad and Tobago'),
(196,'Tunisia'),
(197,'Turkey'),
(198,'Turkmenistan'),
(199,'Turks and Caicos Islands'),
(200,'Tuvalu'),
(201,'Uganda'),
(202,'Ukraine'),
(203,'United Arab Emirates'),
(205,'Uruguay'),
(206,'Uzbekistan'),
(207,'Vanuatu'),
(208,'Vatican City, State'),
(209,'Venezuela'),
(210,'Vietnam'),
(211,'Virgin Islands (U.S.)'),
(212,'Wallis and Futuna'),
(213,'Western Sahara'),
(214,'Western Samoa'),
(215,'Yemen'),
(216,'Yugoslavia'),
(217,'Zambia'),
(218,'Zimbabwe');

drop table if exists lookup_educations;
create table lookup_educations
(
	education_id integer auto_increment primary key,
	education_desc varchar(15)
);

insert into lookup_educations (education_id,education_desc) values
(1,'High School'),
(2,'College'),
(3,'Graduate School'),
(4,'Other');

drop table if exists lookup_genders;
create table lookup_genders
(
	gender_id integer auto_increment primary key,
	gender_desc varchar(15)
);

insert into lookup_genders (gender_id,gender_desc) values 
(1,'Male'),
(2,'Female');

drop table if exists lookup_incomes;
create table lookup_incomes
(
	income_id integer auto_increment primary key,
	income_desc varchar(20)
);

insert into lookup_incomes (income_id,income_desc) values 
(1,'under $25,000'),
(2,'$25,000 - $34,000'),
(3,'$35,000 - $49,000'),
(4,'$50,000 - $74,000'),
(5,'over $75,000');


drop table if exists lookup_languages;
create table lookup_languages
(
	lang_id integer auto_increment primary key,
	name varchar(20)
);

insert into lookup_languages (lang_id,name) values
(1,'English'),
(2,'French'),
(3,'German'),
(4,'Chinese'),
(5,'Japanese'),
(6,'Russian'),
(7,'Polish'),
(8,'Turkish'),
(9,'Yiddish');

drop table if exists lookup_states;
create table lookup_states
(
	state_id varchar(2) primary key,
	state_desc varchar(25) not null
);

insert into lookup_states (state_id,state_desc) values 
('AK','Alaska'),
('AL','Alabama'),
('AR','Arkansas'),
('AS','American'),
('AZ','Arizona'),
('CA','California'),
('CO','Colorado'),
('CT','Connecticut'),
('DC','District of Columbia'),
('DE','Delaware'),
('FL','Florida'),
('GA','Georgia'),
('GU','Guam'),
('HI','Hawaii'),
('IA','Iowa'),
('ID','Idaho'),
('IL','Illinois'),
('IN','Indiana'),
('KS','Kansas'),
('KY','Kentucky'),
('LA','Louisiana'),
('MA','Massachusetts'),
('MD','Maryland'),
('ME','Maine'),
('MI','Michigan'),
('MN','Minnesota'),
('MO','Missouri'),
('MP','Northern'),
('MS','Missippi'),
('MT','Montana'),
('NC','North Carolina'),
('ND','North Dakota'),
('NE','Nebraska'),
('NH','New Hampshire'),
('NJ','New Jersey'),
('NM','New Mexico'),
('NV','Nevada'),
('NY','New York'),
('OH','Ohio'),
('OK','Oklahoma'),
('OR','Oren'),
('PA','Pennsylvania'),
('PR','Puerto Rico'),
('PW','Palau'),
('RI','Rhode Island'),
('SC','South Carolina'),
('SD','South Dakota'),
('TN','Tennessee'),
('TX','Texas'),
('UT','Utah'),
('VA','Virginia'),
('VI','Virgin Island'),
('VT','Vermont'),
('WA','Washington'),
('WI','Wisconsin'),
('WV','West Virginia'),
('WY','Wyoming');

drop table if exists users;
create table users
(
	user_id integer auto_increment primary key,
	first_name varchar(20),
	last_name varchar(20),
	user_login varchar(15) not null,
	user_password varchar(15) not null,
	email varchar(64),
	country_id integer not null,
	state_id varchar(2),
	language_id integer,
	city varchar(30),
	zip varchar(10),
	address1 varchar(50),
	address2 varchar(50),
	address3 varchar(50),
	phone_day varchar(20),
	phone_evn varchar(20),
	fax varchar(20),
	notes text,
	pic_url varchar(64),
	age integer,
	gender integer,
	education integer,
	income integer,
	date_created datetime,
	agreement_id integer,
	ip_insert varchar(50),
	ip_update varchar(50),
	last_login_date datetime,
	security_level_id integer default 1,

	UNIQUE (user_login)
);
  
insert into users (user_id,first_name,last_name,user_login,user_password,email,country_id,state_id,language_id,city,zip,address1,address2,address3,phone_day,phone_evn,fax,notes,pic_url,age,gender,education,income,date_created,agreement_id,ip_insert,ip_update,last_login_date,security_level_id) values
(1,'John','Smith','admin','admin',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2000-11-28 14:13:24',NULL,NULL,NULL,NULL,1),
(2,'Tom','Apprentice','guest','guest',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2000-11-28 14:14:32',NULL,NULL,NULL,NULL,1);