-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `artgallery`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `artgallery`;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `artgallery`.`ART`;
CREATE TABLE `artgallery`.`ART` (
  `ARTID` INT(10) NOT NULL AUTO_INCREMENT,
  `ARTISTID` INT(10) NULL,
  `ARTNAME` VARCHAR(50) NULL,
  `DESCRIPTION` LONGTEXT NULL,
  `PRICE` DECIMAL(19, 4) NULL,
  `LARGEIMAGE` VARCHAR(30) NULL,
  `MEDIAID` INT(10) NULL,
  `ISSOLD` TINYINT(1) NOT NULL,
  PRIMARY KEY (`ARTID`),
  INDEX `COMPANY_ID` (`ARTISTID`),
  INDEX `MEDIAID` (`MEDIAID`),
  INDEX `PRODUCT_ID` (`ARTID`)
)
ENGINE = MYISAM;

DROP TABLE IF EXISTS `artgallery`.`ARTISTS`;
CREATE TABLE `artgallery`.`ARTISTS` (
  `ARTISTID` INT(10) NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` VARCHAR(20) NULL,
  `LASTNAME` VARCHAR(20) NULL,
  `ADDRESS` VARCHAR(50) NULL,
  `CITY` VARCHAR(20) NULL,
  `STATE` VARCHAR(2) NULL,
  `POSTALCODE` VARCHAR(10) NULL,
  `EMAIL` VARCHAR(50) NULL,
  `PHONE` VARCHAR(20) NULL,
  `FAX` VARCHAR(12) NULL,
  `THEPASSWORD` VARCHAR(8) NULL,
  PRIMARY KEY (`ARTISTID`),
  INDEX `COMPANY_ID` (`ARTISTID`),
  INDEX `POSTAL_CODE` (`POSTALCODE`)
)
ENGINE = MYISAM;

DROP TABLE IF EXISTS `artgallery`.`GALLERYADMIN`;
CREATE TABLE `artgallery`.`GALLERYADMIN` (
  `GALLERYADMINID` INT(10) NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` VARCHAR(20) NULL,
  `LASTNAME` VARCHAR(20) NULL,
  `EMAIL` VARCHAR(50) NULL,
  `ADMINPASSWORD` VARCHAR(8) NULL,
  `USERROLE` VARCHAR(8) NULL,
  PRIMARY KEY (`GALLERYADMINID`),
  INDEX `GALLERYADMINID` (`GALLERYADMINID`)
)
ENGINE = MYISAM;

DROP TABLE IF EXISTS `artgallery`.`MEDIA`;
CREATE TABLE `artgallery`.`MEDIA` (
  `MEDIAID` INT(10) NOT NULL AUTO_INCREMENT,
  `MEDIATYPE` VARCHAR(50) NULL,
  PRIMARY KEY (`MEDIAID`),
  INDEX `ARTTYPEID` (`MEDIAID`)
)
ENGINE = MYISAM;

DROP TABLE IF EXISTS `artgallery`.`ORDERITEMS`;
CREATE TABLE `artgallery`.`ORDERITEMS` (
  `ORDERITEMID` INT(10) NOT NULL AUTO_INCREMENT,
  `ORDERID` INT(10) NULL,
  `ARTID` INT(10) NULL,
  `PRICE` DECIMAL(19, 4) NULL,
  PRIMARY KEY (`ORDERITEMID`),
  INDEX `ORDERID` (`ORDERID`),
  INDEX `PRODUCTID` (`ARTID`)
)
ENGINE = MYISAM;

DROP TABLE IF EXISTS `artgallery`.`ORDERS`;
CREATE TABLE `artgallery`.`ORDERS` (
  `ORDERID` INT(10) NOT NULL AUTO_INCREMENT,
  `TAX` DECIMAL(19, 4) NULL,
  `TOTAL` DECIMAL(19, 4) NULL,
  `ORDERDATE` DATETIME NULL,
  `ORDERSTATUSID` INT(10) NULL,
  `CUSTOMERFIRSTNAME` VARCHAR(50) NULL,
  `CUSTOMERLASTNAME` VARCHAR(50) NULL,
  `ADDRESS` VARCHAR(50) NULL,
  `CITY` VARCHAR(50) NULL,
  `STATE` VARCHAR(50) NULL,
  `POSTALCODE` VARCHAR(50) NULL,
  `PHONE` VARCHAR(50) NULL,
  PRIMARY KEY (`ORDERID`),
  INDEX `ORDER_ID` (`ORDERID`),
  INDEX `STATUS_ID` (`ORDERSTATUSID`)
)
ENGINE = MYISAM;

DROP TABLE IF EXISTS `artgallery`.`ORDERSTATUS`;
CREATE TABLE `artgallery`.`ORDERSTATUS` (
  `ORDERSTATUSID` INT(10) NOT NULL AUTO_INCREMENT,
  `STATUS` VARCHAR(15) NULL,
  PRIMARY KEY (`ORDERSTATUSID`),
  INDEX `STATUSID` (`ORDERSTATUSID`)
)
ENGINE = MYISAM;



SET FOREIGN_KEY_CHECKS = 1;


-- ----------------------------------------------------------------------
-- SQL data bulk transfer script generated by the MySQL Migration Toolkit
-- ----------------------------------------------------------------------

-- Disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

INSERT INTO `artgallery`.`ART`(`ARTID`, `ARTISTID`, `ARTNAME`, `DESCRIPTION`, `PRICE`, `LARGEIMAGE`, `MEDIAID`, `ISSOLD`)
VALUES
  (1, 1, 'charles1', 'Pastels/Charcoal', 10000.0000, 'aiden01.jpg', 1, -1),
  (2, 1, 'Michael', 'Pastels/Charcoal', 13900.0000, 'aiden02.jpg', 1, 0),
  (3, 1, 'Freddy', 'Pastels/Charcoal', 12500.0000, 'aiden03.jpg', 1, -1),
  (4, 1, 'Paulo', 'Pastels/Charcoal', 11100.0000, 'aiden04.jpg', 1, -1),
  (5, 1, 'Mary', 'Pastels/Charcoal', 13550.0000, 'aiden05.jpg', 1, -1),
  (6, 3, 'Space', 'Mixed Media', 9800.0000, 'elecia01.jpg', 2, -1),
  (7, 3, 'Leaning House', 'Mixed Media', 7800.0000, 'elecia02.jpg', 2, -1),
  (8, 3, 'Dude', 'Mixed Media', 5600.0000, 'elecia03.jpg', 2, -1),
  (9, 3, 'Hang Ten', 'Mixed Media', 8900.0000, 'elecia04.jpg', 2, 0),
  (10, 3, 'Life is a Horse', 'Mixed Media', 10500.0000, 'elecia05.jpg', 2, 0),
  (11, 2, '1958', 'Charcoal ', 75000.0000, 'austin01.jpg', 1, -1),
  (12, 2, 'Toxic', 'Charcoal', 22000.0000, 'austin02.jpg', 1, -1),
  (13, 2, 'Prize Fight', 'Charcoal ', 25000.0000, 'austin03.jpg', 1, -1),
  (14, 2, 'You Don\'t Know Me', 'Charcoal', 42700.0000, 'austin04.jpg', 1, 0),
  (15, 2, 'Do it', 'Charcoal', 30000.0000, 'austin05.jpg', 1, -1),
  (16, 4, 'Bowl of Flowers', 'Acrylic', 11800.0000, 'jeff01.jpg', 1, -1),
  (17, 4, '60 Vibe', 'Acrylic ', 25000.0000, 'jeff02.jpg', 1, 0),
  (18, 4, 'Naked', 'Acrylic', 30000.0000, 'jeff03.jpg', 1, -1),
  (19, 4, 'Sky', 'Acrylic', 15000.0000, 'jeff04.jpg', 1, -1),
  (20, 4, 'Slices of Life', 'Acrylic', 20000.0000, 'jeff05.jpg', 1, 0),
  (21, 6, 'Morning Forest', 'Oil', 250000.0000, 'maxwell01.jpg', 1, 0),
  (22, 6, 'Things', 'Oil', 300000.0000, 'maxwell02.jpg', 1, -1),
  (23, 6, 'The Lake', 'Oil', 150000.0000, 'maxwell03.jpg', 1, 0),
  (24, 6, 'Morph', 'Oil', 10500.0000, 'maxwell04.jpg', 1, 0),
  (25, 6, 'Ideas', 'Oil', 250000.0000, 'maxwell05.jpg', 1, -1),
  (26, 5, 'Christmas', 'Pastels', 54000.0000, 'lori01.jpg', 1, -1),
  (27, 5, 'Happiness', 'Pastels', 65000.0000, 'lori02.jpg', 1, -1),
  (28, 5, 'Closed', 'Pastels', 40000.0000, 'lori03.jpg', 1, 0),
  (29, 5, 'Enchanted Tree', 'Pastels', 350000.0000, 'lori04.jpg', 1, 0),
  (30, 5, 'Melon', 'Pastels', 200000.0000, 'lori05.jpg', 1, 0),
  (31, 7, 'Music', 'Oils', 72000.0000, 'paul01.jpg', 1, 0),
  (32, 7, 'Empty', 'Oils', 35000.0000, 'paul02.jpg', 1, -1),
  (33, 7, 'My Venus', 'Oils', 58000.0000, 'paul03.jpg', 1, 0),
  (34, 7, 'Man in Jeans', 'Oils', 100000.0000, 'paul04.jpg', 1, 0),
  (35, 7, 'Man on Stool', 'Oils', 90000.0000, 'paul05.jpg', 1, -1),
  (36, 8, 'Mystery', 'Pastels', 250000.0000, 'raquel01.jpg', 1, 0),
  (37, 8, 'Paradise', 'Pastels', 300000.0000, 'raquel02.jpg', 1, 0),
  (38, 8, 'Mountains', 'Pastels', 150000.0000, 'raquel03.jpg', 1, -1),
  (39, 8, 'Mom', 'Pastels', 85000.0000, 'raquel04.jpg', 1, 0),
  (40, 8, 'Beauty', 'Pastels', 100000.0000, 'raquel05.jpg', 1, 0),
  (41, 9, 'Cowboy', 'Watercolor', 40000.0000, 'viata01.jpg', 1, 0),
  (42, 9, 'Pretty Life', 'Watercolor', 35000.0000, 'viata02.jpg', 1, 0),
  (43, 9, 'Singer', 'Watercolor', 56500.0000, 'viata03.jpg', 1, 0),
  (44, 9, 'Windy', 'Watercolor', 36000.0000, 'viata04.jpg', 1, -1),
  (45, 9, 'Yellow to Me', 'Watercolor', 20000.0000, 'viata05.jpg', 1, -1);

INSERT INTO `artgallery`.`ARTISTS`(`ARTISTID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS`, `CITY`, `STATE`, `POSTALCODE`, `EMAIL`, `PHONE`, `FAX`, `THEPASSWORD`)
VALUES (22, 'Taylor Webb', 'Frazier', '58 Plaza Ct', 'Santa Fe', 'NM', '34453', 'taylor.webb@myart.com', '444-333-9876', NULL, 'icecream'),
  (23, 'Mike', 'nimer', 'qwed qdsa', 'asd  da', 'ca', '01231', 'me@m.com', '', '', 'admin'),
  (1, 'Aiden', 'Donolan', '352 Corporate Ave.', 'Denver', 'CO', '80206-4526', 'aiden.donolan@donolan.com', '555-751-8464', '555-751-8463', 'peapod'),
  (2, 'Austin', 'Weber', '25463 Main Street, Suite C', 'Berkeley', 'CA', '94707-4513', 'austin@life.com', '555-513-4318', '510-513-4888', 'nopolyes'),
  (3, 'Elicia', 'Kim', '2523 National Blvd', 'Los Angeles', 'CA', '90064-5134', 'eleciakim@newmedia.com', '555-846-4613', '', 'longtail'),
  (4, 'Jeff', 'Baclawski', '903 Boardwalk Ave', 'Hollywood', 'FL', '33021-8894', 'user@demodata.com', '239-213-4561', '239-213-7465', 'demo'),
  (5, 'Lori', 'Johnson', '6462 Cowtown Rd', 'Pierre', 'SD', '57501-7782', 'lb@bovinas.com', '605-776-8461', '605-776-8462', 'nowhere'),
  (6, 'Maxwell', 'Wilson', '72500 MLK Blvd', 'Tulsa', 'OK', '74116-4613', 'max@mypaintings.com', '918-347-4513', '918-456-1315', 'eyeball'),
  (7, 'Paul', 'Trani', '3320 Fashion Dr', 'New York', 'NY', '10017-1231', 'paul.trani@trani.com', '212-630-5345', NULL, 'ouchy'),
  (8, 'Raquel', 'Young', '1120 Presidential Ave', 'Atlanta', 'GA', '39901-4813', 'raquel@soulpaints.com', '770-397-9486', '770-391-8435', 'cmyk'),
  (9, 'Viata', 'Trenton', '4563 42nd St', 'New York', 'NY', '10012-4562', 'trenton.v@trenton.com', '212-456-8468', '212-468-8765', 'pillow'),
  (16, 'Diane', 'Demo', '123 Demo Lane', 'Denver', 'CO', '55555', 'diane@demo.com', '555-555-5555', '', 'demo'),
  (19, 'Anthony', 'Kunovic', '111 94th Ave', 'Aspen', 'CO', '90809', 'aj@ajgalleries.com', '970-555-1373', NULL, 'overlord'),
  (20, 'Ellery', 'Buntel', '23 Elm St', 'Washington', 'DC', '77893', 'ellery.buntel@myart.com', '637-902-7200', '637-300-2888', 'crayon'),
  (21, 'Emma', 'Buntel', '789 Main St', 'Washington', 'DC', '77834', 'emma.buntel@myart.com', '637-930-2999', NULL, 'bluebird');

INSERT INTO `artgallery`.`GALLERYADMIN`(`GALLERYADMINID`, `FIRSTNAME`, `LASTNAME`, `EMAIL`, `ADMINPASSWORD`, `USERROLE`)
VALUES (1, 'Suzanne', 'White', 'swhite@fashiongalleria.com', 'bludress', 'user'),
  (2, 'Mike', 'Green', 'mgreen@fashiongalleria.com', 'organza', 'user'),
  (3, 'Darrell', 'Demo', 'admin@demodata.com', 'demo', 'admin');

INSERT INTO `artgallery`.`MEDIA`(`MEDIAID`, `MEDIATYPE`)
VALUES (1, 'Painting'),
  (2, 'Sculpure'),
  (3, 'Pottery'),
  (4, 'Jewelry'),
  (5, 'Tapestry'),
  (6, 'Photography'),
  (7, 'Functional'),
  (8, 'Wearable');

INSERT INTO `artgallery`.`ORDERITEMS`(`ORDERITEMID`, `ORDERID`, `ARTID`, `PRICE`)
VALUES (1, 1, 1, 10000.0000),
  (2, 2, 3, 12400.0000),
  (3, 2, 5, 13550.0000),
  (4, 3, 6, 9800.0000),
  (5, 4, 7, 7800.0000),
  (6, 5, 8, 5600.0000),
  (7, 6, 11, 75000.0000),
  (8, 7, 12, 22000.0000),
  (9, 8, 15, 30000.0000),
  (11, 9, 16, 11800.0000),
  (12, 10, 18, 30000.0000),
  (14, 11, 44, 36000.0000),
  (15, 12, 45, 20000.0000),
  (16, 13, 38, 150000.0000),
  (17, 14, 35, 90000.0000),
  (18, 15, 32, 35000.0000),
  (19, 16, 19, 15000.0000),
  (20, 17, 25, 250000.0000),
  (21, 18, 4, 11100.0000),
  (22, 19, 22, 300000.0000),
  (23, 20, 27, 65000.0000),
  (24, 21, 46, 35000.0000),
  (25, 22, 48, 45000.0000),
  (26, 23, 50, 30000.0000);

INSERT INTO `artgallery`.`ORDERS`(`ORDERID`, `TAX`, `TOTAL`, `ORDERDATE`, `ORDERSTATUSID`, `CUSTOMERFIRSTNAME`, `CUSTOMERLASTNAME`, `ADDRESS`, `CITY`, `STATE`, `POSTALCODE`, `PHONE`)
VALUES (1, 400.0000, 10400.0000, '2004-06-10 00:00:00', 1, 'Bob', 'Green', '12 Gover St', 'Oakland', 'CA', '45794', NULL),
  (2, 1038.0000, 26988.0000, '2003-07-02 00:00:00', 5, 'Sue', 'White', '123 4th St', 'Greeley', 'CO', '55555', NULL),
  (3, 392.0000, 10192.0000, '2002-06-05 00:00:00', 5, 'Tiffany', 'Rose', '11267 Green St', 'Seattle', 'WA', '34567', NULL),
  (4, 312.0000, 8112.0000, '2004-06-18 00:00:00', 5, 'Frank', 'Smith', '78 45th Ave', 'Las Vegas', 'NV', '68689', NULL),
  (5, 224.0000, 5824.0000, '2002-05-22 00:00:00', 5, 'Anthony', 'Shumacher', '988 Brownlee Way', 'Denver', 'CO', '30604', NULL),
  (6, 3000.0000, 78000.0000, '2005-04-01 00:00:00', 1, 'Jerry', 'Day', '689 Dell Ave', 'Santa Fe', 'NM', '39087', NULL),
  (7, 880.0000, 22880.0000, '2002-08-28 00:00:00', 5, 'Dave', 'Cardinal', '234 Phoenix Way', 'Phoenix', 'AZ', '33399', NULL),
  (8, 1200.0000, 31200.0000, '2003-12-25 00:00:00', 5, 'Mary', 'Carter', '944 Eaton Way', 'Colorado Springs', 'CO', '90004', NULL),
  (9, 472.0000, 12272.0000, '2002-01-13 00:00:00', 5, 'Cheryl', 'Masters', '6 23rd', 'Ogden', 'UT', '56555', NULL),
  (10, 1200.0000, 31200.0000, '2005-02-05 00:00:00', 2, 'Louahn', 'Lloyd', 'Scottsdale Circle', 'Scottsdale', 'AZ', '45677', NULL),
  (11, 1440.0000, 37440.0000, '2005-09-19 00:00:00', 2, 'Nancy', 'Smithers', '4 Palm Dr', 'Houston', 'TX', '34500', NULL),
  (12, 800.0000, 20800.0000, '2004-05-25 00:00:00', 5, 'Bob', 'Franks', '456 W. 6th', 'Deadwood', 'OK', '98999', NULL),
  (13, 6000.0000, 156000.0000, '2004-07-09 00:00:00', 5, 'Michael', 'Harvey', '23 Land Park', 'Cheyenne', 'WY', '80906', NULL),
  (14, 3600.0000, 93600.0000, '2004-12-11 00:00:00', 5, 'Jane', 'Downs', '234 54th St', 'New York', 'NY', '20405', NULL),
  (15, 1400.0000, 36400.0000, '2005-01-30 00:00:00', 2, 'Kerry', 'Kerns', '34 Lincoln Way', 'Boston', 'MA', '30445', NULL),
  (16, 600.0000, 15600.0000, '2005-03-16 00:00:00', 2, 'Emily', 'Michaels', '8999 1st Ave', 'Anytown', 'AK', '90806', NULL),
  (17, 10000.0000, 260000.0000, '2005-05-25 00:00:00', 2, 'Joe', 'Anthony', '1555 Quail St', 'Denver', 'CO', '80215', NULL),
  (18, 444.0000, 11544.0000, '2005-06-13 00:00:00', 2, 'Pauette', 'Crow', '45 54th Drive', 'Dallas', 'TX', '34120', NULL),
  (19, 12000.0000, 312000.0000, '2005-07-25 00:00:00', 2, 'David', 'Kissler', '5673 Summit Ave', 'Los Angeles', 'CA', '80910', NULL),
  (20, 2600.0000, 67600.0000, '2005-08-12 00:00:00', 2, 'Matt', 'Armstrong', '76 120th', 'Kansas City', 'KS', '44980', NULL),
  (21, 1400.0000, 36400.0000, '2004-03-16 00:00:00', 5, 'Joe', 'Kunovic', '3987 Frontage Road', 'Boston', 'MA', '39500', NULL),
  (22, 1800.0000, 46800.0000, '2005-03-16 00:00:00', 3, 'Annette', 'Kunovic', '4589 Main St', 'New York', 'NY', '89993', NULL),
  (23, 1200.0000, 31200.0000, '2005-06-19 00:00:00', 1, 'Allen', 'Rice', '34 First Lane', 'Los Angeles', 'CA', '67839', NULL);

INSERT INTO `artgallery`.`ORDERSTATUS`(`ORDERSTATUSID`, `STATUS`)
VALUES (1, 'pending'),
  (2, 'complete'),
  (3, 'shipped'),
  (4, 'billed'),
  (5, 'paid');

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

-- End of script