#DROP DATABASE IF EXISTS `IEPM`;
#CREATE DATABASE `IEPM`;

DROP TABLE IF EXISTS `USERS`;
CREATE TABLE `USERS`
(
  `username`   varchar(10) NOT NULL,
  `password`   VARCHAR(32) NOT NULL,
  `email`      VARCHAR(50) NOT NULL,
  PRIMARY KEY (`username`)
);

INSERT INTO `USERS` VALUES ('kalim', md5('password'), 'kalim@slac.stanford.edu');
INSERT INTO `USERS` VALUES ('cottrell', md5('password'), 'cottrell@slac.stanford.edu');
INSERT INTO `USERS` VALUES ('fahad', md5('password'), 'fahad@slac.stanford.edu');

DROP TABLE IF EXISTS `COUNTRY`;
CREATE TABLE `COUNTRY`
(
  `COUNTRY_ID`  SMALLINT NOT NULL,
  `COUNTRY`     VARCHAR(80) NOT NULL,
  `CONTINENT`   VARCHAR(20) NOT NULL,
  `TLD`         VARCHAR(10) NOT NULL,
  `REMARK`      VARCHAR(256),
  UNIQUE (`COUNTRY`),
  PRIMARY KEY (`COUNTRY_ID`)
);

#DROP INDEX `IDX_COUNTRY` on `COUNTRY`;
CREATE INDEX `IDX_COUNTRY` on `COUNTRY` (`COUNTRY`);

DROP TABLE IF EXISTS `NODEDETAILS`;
CREATE TABLE `NODEDETAILS`
(
  `NODENAME`     VARCHAR(100) NOT NULL,
  `IPADDRESS`    VARCHAR(15) NOT NULL,
  `SITENAME`     VARCHAR(100) NOT NULL,
  `NICKNAME`     VARCHAR(35) NOT NULL,
  `FULLNAME`     VARCHAR(100) NOT NULL,
  `LOCATION`     VARCHAR(100) NOT NULL,
  `COUNTRY_ID`   SMALLINT NOT NULL,
  `LAT`          FLOAT NOT NULL,
  `LONG`         FLOAT NOT NULL,
  `PROJECTTYPE`  VARCHAR(10) NOT NULL,
  `PINGSERVER`   VARCHAR(100) NOT NULL,
  `TRACESERVER`  VARCHAR(100) NOT NULL,
  `DATASERVER`   VARCHAR(100) NOT NULL,
  `URL`          VARCHAR(100),
  `GMT`          VARCHAR(10),
  `COMMENTS`     VARCHAR(4000),
  `APP_USER`     VARCHAR(20),
  `CONTACTS`     VARCHAR(150),
  `PING_SIZE`    SMALLINT,
   PRIMARY KEY (`NODENAME`),
   FOREIGN KEY (`COUNTRY_ID`) REFERENCES COUNTRY(COUNTRY_ID)
);

#DROP INDEX `IDX_NODEDETAILS` on `NODEDETAILS`;
CREATE INDEX `IDX_NODEDETAILS` on `NODEDETAILS` (`NODENAME`);