






CREATE TABLE country (
  CountryID int(11) NOT NULL AUTO_INCREMENT,
  CountryName varchar(255) NOT NULL,
  PRIMARY KEY (CountryID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


INSERT INTO country (CountryID, CountryName) VALUES(1, 'United States');
INSERT INTO country (CountryID, CountryName) VALUES(2, 'United Kingdom');
INSERT INTO country (CountryID, CountryName) VALUES(3, 'India');
INSERT INTO country (CountryID, CountryName) VALUES(4, 'Singapore');
INSERT INTO country (CountryID, CountryName) VALUES(5, 'Germany');
INSERT INTO country (CountryID, CountryName) VALUES(6, 'France');
INSERT INTO country (CountryID, CountryName) VALUES(7, 'Italy');
INSERT INTO country (CountryID, CountryName) VALUES(8, 'Spain');
INSERT INTO country (CountryID, CountryName) VALUES(9, 'Hungary');



CREATE TABLE grade (
  GradeID int(11) NOT NULL AUTO_INCREMENT,
  GradeName varchar(255) NOT NULL,
  PRIMARY KEY (GradeID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


INSERT INTO grade (GradeID, GradeName) VALUES(1, 'Very Fine');
INSERT INTO grade (GradeID, GradeName) VALUES(2, 'Fine');
INSERT INTO grade (GradeID, GradeName) VALUES(3, 'Good');
INSERT INTO grade (GradeID, GradeName) VALUES(4, 'Average');
INSERT INTO grade (GradeID, GradeName) VALUES(5, 'Poor');



CREATE TABLE item (
  RecordID int(11) NOT NULL AUTO_INCREMENT,
  RecordDate date NOT NULL,
  SellerName varchar(255) NOT NULL,
  SellerEmail varchar(255) NOT NULL,
  SellerTel varchar(50) DEFAULT NULL,
  SellerAddress text,
  Title varchar(255) NOT NULL,
  `Year` int(4) NOT NULL,
  CountryID int(4) NOT NULL,
  Denomination float NOT NULL,
  TypeID int(4) NOT NULL,
  GradeID int(4) NOT NULL,
  SalePriceMin float NOT NULL,
  SalePriceMax float NOT NULL,
  Description text NOT NULL,
  DisplayStatus tinyint(1) NOT NULL,
  DisplayUntil date DEFAULT NULL,
  PRIMARY KEY (RecordID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


INSERT INTO item (RecordID, RecordDate, SellerName, SellerEmail, SellerTel, SellerAddress, Title, Year, CountryID, Denomination, TypeID, GradeID, SalePriceMin, SalePriceMax, Description, DisplayStatus, DisplayUntil) VALUES(1, '2009-12-06', 'John Doe', 'john@example.com', '+123456789102', '12 Green House, Red Road, Blue City', 'Himalayas - Silver Jubilee', 1958, 3, 5, 1, 2, 10, 15, 'Silver jubilee issue. Aerial view of snow-capped.  \r\nHimalayan mountains. Horizontal orange stripe across  \r\ntop margin. Excellent condition, no marks.', 0, NULL);
INSERT INTO item (RecordID, RecordDate, SellerName, SellerEmail, SellerTel, SellerAddress, Title, Year, CountryID, Denomination, TypeID, GradeID, SalePriceMin, SalePriceMax, Description, DisplayStatus, DisplayUntil) VALUES(2, '2009-10-05', 'Susan Doe', 'susan@example.com', '+198765432198', '1 Tiger Place, Animal City 648392', 'Britain - WWII Fighter', 1966, 2, 1, 1, 4, 1, 2, 'WWII Fighter Plane overlaid on blue sky. Cancelled', 0, NULL);



CREATE TABLE log (
  RecordID int(11) NOT NULL AUTO_INCREMENT,
  LogMessage text NOT NULL,
  LogLevel varchar(30) NOT NULL,
  LogTime varchar(30) NOT NULL,
  Stack text,
  Request text,
  PRIMARY KEY (RecordID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TABLE `type` (
  TypeID int(11) NOT NULL AUTO_INCREMENT,
  TypeName varchar(255) NOT NULL,
  PRIMARY KEY (TypeID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


INSERT INTO type (TypeID, TypeName) VALUES(1, 'Commemorative');
INSERT INTO type (TypeID, TypeName) VALUES(2, 'Decorative');
INSERT INTO type (TypeID, TypeName) VALUES(3, 'Definitive');
INSERT INTO type (TypeID, TypeName) VALUES(4, 'Special');
INSERT INTO type (TypeID, TypeName) VALUES(5, 'Other');



CREATE TABLE `user` (
  RecordID int(4) NOT NULL AUTO_INCREMENT,
  Username varchar(10) NOT NULL,
  `Password` text NOT NULL,
  PRIMARY KEY (RecordID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user (RecordID, Username, Password) VALUES (NULL, 'john', PASSWORD('john'));