-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 03, 2011 at 01:21 AM
-- Server version: 5.5.8
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `alexglar_njit_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `adminappintersection`
--

CREATE TABLE IF NOT EXISTS `adminappintersection` (
  `adminID` int(11) NOT NULL,
  `appID` int(11) NOT NULL,
  `adminAppComment` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`adminID`,`appID`),
  KEY `intersection_admin` (`adminID`),
  KEY `intersection_application` (`appID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `adminappintersection`
--


-- --------------------------------------------------------

--
-- Table structure for table `administrator`
--

CREATE TABLE IF NOT EXISTS `administrator` (
  `adminID` int(11) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adminComments` text COLLATE utf8_unicode_ci,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `backupPhone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `backupEmail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `userLevel` int(11) NOT NULL COMMENT 'user Level 1 = Super Admin\nuser Level 2 = Admin',
  PRIMARY KEY (`adminID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

--
-- Dumping data for table `administrator`
--

INSERT INTO `administrator` (`adminID`, `firstName`, `lastName`, `adminComments`, `email`, `phone`, `backupPhone`, `backupEmail`, `username`, `password`, `userLevel`) VALUES
(1, 'Super', 'Administrator', 'This account is reserved only for Super Administrator.', 'myEmail@domain.com', '212-212-1010', '973-344-3222', 'myBackupEmail@domain.com', 'superadmin', '5f4dcc3b5aa765d61d8327deb882cf99', 1),
(2, 'Regular', 'Admin', 'Default Admin account with password "password". Must be removed or updated after installation.', 'admin@domain.com', '973-333-3333', '845-333-3333', 'admin@domain.com', 'admin', '5f4dcc3b5aa765d61d8327deb882cf99', 2);

-- --------------------------------------------------------

--
-- Table structure for table `adminorganizationintersection`
--

CREATE TABLE IF NOT EXISTS `adminorganizationintersection` (
  `adminID` int(11) NOT NULL,
  `organizationID` int(11) NOT NULL,
  `comments` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`adminID`,`organizationID`),
  KEY `intersection_admin` (`adminID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `adminorganizationintersection`
--

INSERT INTO `adminorganizationintersection` (`adminID`, `organizationID`, `comments`) VALUES
(2, 2, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `application`
--

CREATE TABLE IF NOT EXISTS `application` (
  `appID` int(11) NOT NULL AUTO_INCREMENT,
  `appName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `appDescription` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `appComment` text COLLATE utf8_unicode_ci,
  `appCode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`appID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `application`
--

INSERT INTO `application` (`appID`, `appName`, `appDescription`, `appComment`, `appCode`) VALUES
(4, 'License Record Management Application', '', '', '4877e49debf9d058fe59dd014cbf639d');

-- --------------------------------------------------------

--
-- Table structure for table `apporganizationintersection`
--

CREATE TABLE IF NOT EXISTS `apporganizationintersection` (
  `organizationID` int(11) NOT NULL,
  `appID` int(11) NOT NULL,
  PRIMARY KEY (`organizationID`,`appID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `apporganizationintersection`
--

INSERT INTO `apporganizationintersection` (`organizationID`, `appID`) VALUES
(2, 4);

-- --------------------------------------------------------

--
-- Table structure for table `appsublocation`
--

CREATE TABLE IF NOT EXISTS `appsublocation` (
  `appSubLocationID` int(11) NOT NULL AUTO_INCREMENT,
  `appID` int(11) NOT NULL,
  `appSubLocationDescription` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `appSubLocationComment` text COLLATE utf8_unicode_ci,
  `appSubLocationHttp` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`appSubLocationID`),
  KEY `application_applicationSubLocation` (`appID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `appsublocation`
--


-- --------------------------------------------------------

--
-- Table structure for table `configtable`
--

CREATE TABLE IF NOT EXISTS `configtable` (
  `settingID` int(11) NOT NULL AUTO_INCREMENT,
  `settingName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `settingValue` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`settingID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

--
-- Dumping data for table `configtable`
--

INSERT INTO `configtable` (`settingID`, `settingName`, `settingValue`) VALUES
(1, 'defaultPublicOrPrivate', 'Public'),
(2, 'mailer', 'SMTP'),
(3, 'sendMailPath', '/usr/sbin/sendmail'),
(4, 'SMTPAuthentication', 'YES'),
(5, 'SMTPUsername', ''),
(6, 'SMTPPassword', ''),
(7, 'SMTPHost', 'smtp.gmail.com'),
(8, 'mailFromName', 'Suggestion App'),
(9, 'mailFromEmail', '');

-- --------------------------------------------------------

--
-- Table structure for table `organization`
--

CREATE TABLE IF NOT EXISTS `organization` (
  `organizationID` int(11) NOT NULL AUTO_INCREMENT,
  `organizationName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `organizationLocation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `organizationJurisdiction` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `organizationComment` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`organizationID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `organization`
--

INSERT INTO `organization` (`organizationID`, `organizationName`, `organizationLocation`, `organizationJurisdiction`, `organizationComment`) VALUES
(2, 'DMV', 'Randolph, NJ', 'Morris County', 'Department of Motor Vehicle');

-- --------------------------------------------------------

--
-- Table structure for table `organizationgroup`
--

CREATE TABLE IF NOT EXISTS `organizationgroup` (
  `groupID` int(11) NOT NULL AUTO_INCREMENT,
  `groupName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `organizationID` int(11) NOT NULL,
  `immediateParentID` int(11) NOT NULL,
  `groupDescription` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`groupID`),
  KEY `group_organization` (`organizationID`),
  KEY `group_parentOrganization` (`immediateParentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `organizationgroup`
--


-- --------------------------------------------------------

--
-- Table structure for table `suggestion`
--

CREATE TABLE IF NOT EXISTS `suggestion` (
  `suggestionID` int(11) NOT NULL AUTO_INCREMENT,
  `appID` int(11) NOT NULL COMMENT 'needed to be able to tell what app sent this suggestion to the database',
  `appSubLocationID` int(11) NOT NULL COMMENT 'needed to be able to tell which of possibly many locations in a single app, suggestion came from. It can be a URL address',
  `suggestionStatusCode` int(11) NOT NULL,
  `suggestionCategoryID` int(11) NOT NULL,
  `submissionDateTime` datetime DEFAULT NULL,
  `submitterName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `submitterEmail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `submitterCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `submitterState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `submitterZipCode` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `suggestionSubject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `suggestionDescription` text COLLATE utf8_unicode_ci,
  `submitterRequestedNotification` int(11) DEFAULT NULL,
  `submitterNotified` int(11) DEFAULT NULL,
  `suggestionDisposition` longtext COLLATE utf8_unicode_ci,
  `submitterNotifiedOfDisposition` int(11) DEFAULT NULL,
  `enablePublicView` int(11) DEFAULT NULL,
  PRIMARY KEY (`suggestionID`),
  KEY `suggestionStatus_suggestion` (`suggestionStatusCode`),
  KEY `appSublocation_suggestion` (`appSubLocationID`),
  KEY `application_suggestion` (`appID`),
  KEY `suggestionCategory_suggestion` (`suggestionCategoryID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=18 ;

--
-- Dumping data for table `suggestion`
--


-- --------------------------------------------------------

--
-- Table structure for table `suggestioncategory`
--

CREATE TABLE IF NOT EXISTS `suggestioncategory` (
  `categoryID` int(11) NOT NULL AUTO_INCREMENT,
  `categoryDescription` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `categoryComment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`categoryID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `suggestioncategory`
--

INSERT INTO `suggestioncategory` (`categoryID`, `categoryDescription`, `categoryComment`) VALUES
(1, 'Errors', 'This category includes suggestions about the errors found in the application.'),
(2, 'Feedback', 'General Feedback about this application.'),
(3, 'Enhancements', 'Suggested Enhancements'),
(4, 'General', 'Suggestions not falling in any other categories.');

-- --------------------------------------------------------

--
-- Table structure for table `suggestioncomments`
--

CREATE TABLE IF NOT EXISTS `suggestioncomments` (
  `suggestionID` int(11) NOT NULL,
  `adminID` int(11) NOT NULL,
  `comment` text COLLATE utf8_unicode_ci,
  `submissionDateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`suggestionID`,`adminID`),
  KEY `comment_administrator` (`adminID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `suggestioncomments`
--


-- --------------------------------------------------------

--
-- Table structure for table `suggestionstatus`
--

CREATE TABLE IF NOT EXISTS `suggestionstatus` (
  `statusCode` int(11) NOT NULL AUTO_INCREMENT,
  `statusDescription` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `statusCodeComment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`statusCode`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `suggestionstatus`
--

INSERT INTO `suggestionstatus` (`statusCode`, `statusDescription`, `statusCodeComment`) VALUES
(1, 'Open', NULL),
(2, 'Pending', NULL),
(3, 'Processing', NULL),
(4, 'Closed', NULL);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `adminappintersection`
--
ALTER TABLE `adminappintersection`
  ADD CONSTRAINT `intersection_admin` FOREIGN KEY (`adminID`) REFERENCES `administrator` (`adminID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `intersection_application` FOREIGN KEY (`appID`) REFERENCES `application` (`appID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `adminorganizationintersection`
--
ALTER TABLE `adminorganizationintersection`
  ADD CONSTRAINT `intersection_admin0` FOREIGN KEY (`adminID`) REFERENCES `administrator` (`adminID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `appsublocation`
--
ALTER TABLE `appsublocation`
  ADD CONSTRAINT `application_applicationSubLocation` FOREIGN KEY (`appID`) REFERENCES `application` (`appID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `organizationgroup`
--
ALTER TABLE `organizationgroup`
  ADD CONSTRAINT `group_organization` FOREIGN KEY (`organizationID`) REFERENCES `organization` (`organizationID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `group_parentOrganization` FOREIGN KEY (`immediateParentID`) REFERENCES `organization` (`organizationID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `suggestioncomments`
--
ALTER TABLE `suggestioncomments`
  ADD CONSTRAINT `comment_administrator` FOREIGN KEY (`adminID`) REFERENCES `administrator` (`adminID`) ON DELETE CASCADE ON UPDATE NO ACTION;
