-- YellowPages_MySQL.sql

drop table if exists categories;
drop table if exists items;
drop table if exists users;

create table users
(
	user_id integer auto_increment primary key,
	user_login varchar(15) not null,
	user_password varchar(15) not null,
	user_level int not null default 0,
	name varchar(50) not null,
	email varchar(30),
	unique (user_login)
);

insert into users (user_login, user_password, name, user_level) values ('admin','admin','Admin',3);
insert into users (user_login, user_password, name, user_level) values ('guest','guest','Guest',1);

CREATE TABLE categories (
       category_id          int auto_increment primary key,
       name                 varchar(50) NULL,
       par_category_id      int NULL
);

insert into categories (name, par_category_id) values ('Automotive', null);
insert into categories (name, par_category_id) values ('Community', null);
insert into categories (name, par_category_id) values ('Computers and Internet', null);
insert into categories (name, par_category_id) values ('Education and Instruction', null);
insert into categories (name, par_category_id) values ('Entertainment and Arts', null);
insert into categories (name, par_category_id) values ('Food and Dining', null);
insert into categories (name, par_category_id) values ('Health and Medicine', null);
insert into categories (name, par_category_id) values ('Home and Garden', null);
insert into categories (name, par_category_id) values ('Legal and Financial', null);
insert into categories (name, par_category_id) values ('Accessories', 1);
insert into categories (name, par_category_id) values ('Auto Wrecking', 1);
insert into categories (name, par_category_id) values ('Dealers', 1);
insert into categories (name, par_category_id) values ('Motorcycles', 1);
insert into categories (name, par_category_id) values ('Racing', 1);
insert into categories (name, par_category_id) values ('Rental', 1);
insert into categories (name, par_category_id) values ('Tires', 1);
insert into categories (name, par_category_id) values ('Animal and Humane Societies', 2);
insert into categories (name, par_category_id) values ('Children and Youth', 2);
insert into categories (name, par_category_id) values ('Disabilities', 2);
insert into categories (name, par_category_id) values ('Government', 2);
insert into categories (name, par_category_id) values ('Housing', 2);
insert into categories (name, par_category_id) values ('Libraries', 2);
insert into categories (name, par_category_id) values ('Post Offices', 2);
insert into categories (name, par_category_id) values ('Religion', 2);
insert into categories (name, par_category_id) values ('Communications and Networking', 3);
insert into categories (name, par_category_id) values ('Computer Graphics', 3);
insert into categories (name, par_category_id) values ('Computer Rental', 3);
insert into categories (name, par_category_id) values ('Computer Service and Repair', 3);
insert into categories (name, par_category_id) values ('Computer Training', 3);
insert into categories (name, par_category_id) values ('Desktop Publishing', 3);
insert into categories (name, par_category_id) values ('Software', 3);
insert into categories (name, par_category_id) values ('Colleges and Universities', 4);
insert into categories (name, par_category_id) values ('K-12', 4);
insert into categories (name, par_category_id) values ('Language', 4);
insert into categories (name, par_category_id) values ('Preschools', 4);
insert into categories (name, par_category_id) values ('Tutoring', 4);
insert into categories (name, par_category_id) values ('Bars, Pubs, and Clubs', 5);
insert into categories (name, par_category_id) values ('Dance', 5);
insert into categories (name, par_category_id) values ('Entertainers', 5);
insert into categories (name, par_category_id) values ('Movies and Film', 5);
insert into categories (name, par_category_id) values ('Museums and Galleries', 5);
insert into categories (name, par_category_id) values ('Music', 5);
insert into categories (name, par_category_id) values ('Tickets', 5);
insert into categories (name, par_category_id) values ('Video', 5);
insert into categories (name, par_category_id) values ('Beverages', 6);
insert into categories (name, par_category_id) values ('Candy and Sweets', 6);
insert into categories (name, par_category_id) values ('Catering', 6);
insert into categories (name, par_category_id) values ('Culinary Schools and Classes', 6);
insert into categories (name, par_category_id) values ('Grocery Stores', 6);
insert into categories (name, par_category_id) values ('Meat', 6);
insert into categories (name, par_category_id) values ('Natural and Organic', 6);
insert into categories (name, par_category_id) values ('Poultry', 6);
insert into categories (name, par_category_id) values ('Restaurants', 6);
insert into categories (name, par_category_id) values ('Alternative Medicine', 7);
insert into categories (name, par_category_id) values ('Care Providers', 7);
insert into categories (name, par_category_id) values ('Doctors and Clinics', 7);
insert into categories (name, par_category_id) values ('Education', 7);
insert into categories (name, par_category_id) values ('Fitness', 7);
insert into categories (name, par_category_id) values ('Mental Health', 7);
insert into categories (name, par_category_id) values ('Pharmacies', 7);
insert into categories (name, par_category_id) values ('Appliances', 8);
insert into categories (name, par_category_id) values ('Bed and Bath', 8);
insert into categories (name, par_category_id) values ('Carpets and Rugs', 8);
insert into categories (name, par_category_id) values ('Cleaning Services', 8);
insert into categories (name, par_category_id) values ('Furniture', 8);
insert into categories (name, par_category_id) values ('Housewares', 8);
insert into categories (name, par_category_id) values ('Lawn and Garden', 8);
insert into categories (name, par_category_id) values ('Linens', 8);
insert into categories (name, par_category_id) values ('Accounting and Bookkeeping', 9);
insert into categories (name, par_category_id) values ('Arbitration and Mediation', 9);
insert into categories (name, par_category_id) values ('Bail Bonds', 9);
insert into categories (name, par_category_id) values ('Bankruptcy', 9);
insert into categories (name, par_category_id) values ('Banks', 9);
insert into categories (name, par_category_id) values ('Court Reporting Services', 9);
insert into categories (name, par_category_id) values ('Credit Services', 9);
insert into categories (name, par_category_id) values ('Financing', 9);
insert into categories (name, par_category_id) values ('Insurance', 9);
insert into categories (name, par_category_id) values ('Law Firms', 9);
insert into categories (name, par_category_id) values ('Taxes', 9);

CREATE TABLE items (
       item_id              int auto_increment primary key,
       name                 varchar(100) NULL,
       address              varchar(255) NULL,
       city                 varchar(50) NULL,
       phone                varchar(50) NULL,
       state                varchar(2) NULL,
       zip                  varchar(10) NULL,
       category_id          int NULL,
       notes                text NULL,
       email                varchar(50) NULL,
       url                  varchar(50) NULL
);

insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('YesSoftware', '2226 A Westborough Blvd. #405', 'San Francisco', '', 'CA', '94080', 31, 'http://www.yessoftware.com');
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('IBM', '325 Corey Way # 105', 'San Francisco', '(650) 589-5500', 'CA', '94080', '31', 'http://www.ibm.com');
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('Oracle', '500 Oracle Pkwy', 'Redwood City', '(650) 506-7000', 'CA', '94065', '31', 'http://www.oracle.com');
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('Microsoft', '185 Berry St # 6600', 'San Francisco', '(415) 817-0200', 'CA', '94107', '31', 'http://www.microsoft.com');
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('Sun Microsystems', '100 California St # 800', 'San Francisco', '(415) 781-8140', 'CA', '94111', '31', 'http://www.sun.com');