# These commands will auto create and populate the pet adoption database tables.
# By Jeremy Wheeler on 2/19/2002
# Update by alicia on 2/25/02 for A5
# Updated by alicia on 3/18/02 for phase 1
# Updated by alicia on 4/07/02 for phase 2

drop table if exists p_p2_user;
drop table if exists p_p2_log;
drop table if exists p_p2_session;
drop table if exists p_p2_description;

# Table structure for table 'p_p2_user'

CREATE TABLE p_p2_user (
   userID int(10) unsigned NOT NULL auto_increment,
   firstName varchar(25) NOT NULL,
   lastName varchar(25) NOT NULL,
   userNum varchar(25) NOT NULL,
   password varchar(25) DEFAULT 'mu' NOT NULL,
   phone varchar(14) NOT NULL,
   email varchar(25),
   street varchar(50) NOT NULL,
   city varchar(25) NOT NULL,
   state char(2) NOT NULL,
   zip int(5) unsigned NOT NULL,
   type char(1) DEFAULT 'u' NOT NULL,
   PRIMARY KEY (userID)
);

# Table structure for table 'p_p2_log'

CREATE TABLE p_p2_log (
   logID int(10) unsigned NOT NULL auto_increment,
   userNum varchar(25) NOT NULL,
   timeStamp timestamp(14),
   status char(1) NOT NULL,
   PRIMARY KEY (logID)
);

# Table structure for table 'p_p2_session'

CREATE TABLE p_p2_session (
   sessionID int(10) unsigned NOT NULL auto_increment,
   userNum varchar(25) NOT NULL,
   sessionNum char(32) NOT NULL,
   timeStamp timestamp(14),
   PRIMARY KEY (sessionID)
);

# Table structure for table 'p_p2_description'

CREATE TABLE p_p2_description (
   petID int(10) unsigned NOT NULL auto_increment,
   userNum varchar(25) NOT NULL,
   name varchar(25) NOT NULL,
   species varchar(25) NOT NULL,
   breed varchar(25) NOT NULL,
   gender char(1) NOT NULL,
   age int(2) unsigned NOT NULL,
   description varchar(250) NOT NULL,
   notes varchar(250),
   fixed char(1) NOT NULL,
   status char(1) NOT NULL,
   entryDate timestamp(14),
   PRIMARY KEY (petID)
);

# Sample data for table 'p_p2_user'

INSERT INTO p_p2_user VALUES( '1', 'Jeremy', 'Wheeler', '1', 'nerds', '(570) 662-0793', 'cag570@hotmail.com', '4 N. Main St.  Apt. 4', 'Mansfield', 'PA', '16933', 'u');
INSERT INTO p_p2_user VALUES( '2', 'Nicholus', 'Ives', '2', 'avonia', '(570) 662-0684', 'n_yohan@yahoo.com', '9.5 N. Main St.', 'Mansfield', 'PA', '16933', 'u');
INSERT INTO p_p2_user VALUES( '3', 'Alicia', 'Strupp', '3', 'leish', '(570) 662-0684', 'amnesha@hotmail.com', 'Hemlock', 'Mansfield', 'PA', '16933', 'a');
INSERT INTO p_p2_user VALUES( '4', 'Jeff', 'Schmidt', '4', 'doggy', '(570) 835-5466', 'jeff2554@yahoo.com', 'RR#1 box 95', 'Tioga', 'PA', '16946', 'a');

# Sample data for table 'p_p2_description'

INSERT INTO p_p2_description VALUES( '1', '1', 'Rally', 'dog', 'greyhound', 'm', '3', 'Light grey coat with black markings on head.', 'Good with kids.', 'y', 'a', '20020219154037');
INSERT INTO p_p2_description VALUES( '2', '2', 'Tyler', 'dog', 'dalmation', 'm', '4', 'All black coat with random white markings. Likes to eat anything and enjoys playing with squeaky octopus toys.', 'History of chasing cats.', 'n', 'a', '20020219155048');