DELIMITER //

-- use qwoss
-- //

SET storage_engine=InnoDB
//


CREATE TABLE QW_FIELDINFO (
    QW_TABLENAME  varchar(128) NOT NULL ,
    QW_COLUMNNAME varchar(255) NOT NULL ,
    QW_COLUMNTYPE int NOT NULL ,
    QW_ENUMVALUE  int NULL ,
    QW_ENUMLABEL  varchar(128) NULL
)
//

CREATE TABLE QW_EMPLOYEE (
    QW_EMPLOYEEID   int  NOT NULL ,
    QW_USERID       int  NULL ,
    QW_EMPLOYEENUM  varchar  (100) NULL ,
    QW_ORGID        int  NULL ,
    QW_MANAGERID    int  NULL ,
    QW_FIRSTNAME    varchar  (100) NOT NULL ,
    QW_LASTNAME     varchar  (100) NOT NULL ,
    QW_FULLNAME     varchar  (255) NULL ,
    QW_SOUNDEX      varchar  (4) NULL ,
    QW_TITLE        varchar  (40) NULL ,
    QW_DEPARTMENT   varchar  (100) NULL ,
    QW_BUILDING     varchar  (100) NULL ,
    QW_SITE         varchar  (100) NULL ,
    QW_LOCATION     varchar  (100) NULL ,
    QW_MAILSTOP     varchar  (100) NULL ,
    QW_PHONE        varchar  (30) NULL ,
    QW_ALTPHONE     varchar  (30) NULL ,
    QW_EMAIL        varchar  (255) NULL ,
    QW_FAX          varchar  (30) NULL ,
    QW_PAGER        varchar  (30) NULL ,
    QW_WEBPAGE      varchar  (255) NULL ,
    QW_REACHBY      int  NULL ,
    QW_NOTES        longtext  NULL ,
    QW_HISTORY      int  NULL ,
    PRIMARY KEY (QW_EMPLOYEEID),
    CONSTRAINT fk_qw_employee_users FOREIGN KEY (QW_USERID) REFERENCES QX_USERS ( PKEY )
) 
//

CREATE TABLE QW_ADDRESS (
    QW_ADDRESSID    int  NOT NULL ,
    QW_ADDRESSTYPE  int  NOT NULL ,
    QW_OBJECTID     int  NOT NULL ,
    QW_OBJECTTYPE   int  NOT NULL ,
    QW_ADDRESS1     varchar(255) NULL ,
    QW_ADDRESS2     varchar(255) NULL ,
    QW_CITY         varchar(100) NULL ,
    QW_STATE        int  NULL ,
    QW_COUNTRY      varchar(255) NULL ,
    QW_ZIP          varchar(40) NULL ,
    QW_PHONE        varchar(30) NULL ,
    QW_FAX          varchar(30) NULL ,
    QW_OTHER        varchar(255) NULL ,
    QW_NOTES       longtext  NULL ,
    PRIMARY KEY (QW_ADDRESSID)
)
//

CREATE TABLE QW_ORGANIZATION (
    QW_ORGID int  NOT NULL ,
    QW_PARENTID int  NULL ,
    QW_REGIONID varchar(255)  NULL ,
    QW_NAME varchar(255) NOT NULL ,
    QW_COCODE varchar(255) NULL ,
    QW_DESCRIPTION varchar(255) NULL ,
    QW_FAX varchar(30) NULL ,
    QW_PHONE varchar(30) NULL ,
    QW_ALTPHONE varchar(30) NULL ,
    QW_NOTES longtext  NULL ,
    PRIMARY KEY (QW_ORGID)
)
//

CREATE TABLE QW_PRODUCT (
    QW_PRODUCTID int  NOT NULL,
    QW_NAME varchar(100) NOT NULL,
    QW_MODELNUMBER varchar(100) NULL,
    QW_NOTES longtext  NULL,
    PRIMARY KEY (QW_PRODUCTID)
)
//

CREATE TABLE QW_COMPONENT (
    QW_COMPONENTID int  NOT NULL,
    QW_NAME varchar(100) NOT NULL,
    QW_DESCRIPTION varchar(255) NULL,
    QW_WORKGROUPID int  NULL,
    QW_PRODUCTID int  NULL,
    PRIMARY KEY (QW_COMPONENTID),
    CONSTRAINT fk_qw_component_workgroup FOREIGN KEY (QW_WORKGROUPID) REFERENCES QX_WORKGROUP (PKEY),
    CONSTRAINT fk_qw_component_product FOREIGN KEY (QW_PRODUCTID) REFERENCES QW_PRODUCT (QW_PRODUCTID)
)
//

CREATE TABLE QW_BUILDVERSION (
    QW_BUILDID int  NOT NULL,
    QW_NAME varchar(100) NOT NULL,
    QW_VERSION varchar(100) NOT NULL,
    QW_PRODUCTID int  NOT NULL,
    QW_NOTES longtext  NULL,
    PRIMARY KEY (QW_BUILDID),
    CONSTRAINT fk_qw_buildversion_product FOREIGN KEY (QW_PRODUCTID) REFERENCES QW_PRODUCT (QW_PRODUCTID)
)
//

CREATE TABLE QW_CUSTOMER (
    QW_CUSTOMERID int  NOT NULL,
    QW_ORGID int  NULL,
    QW_FIRSTNAME varchar(100) NULL ,
    QW_LASTNAME varchar(100) NULL ,
    QW_FULLNAME varchar(255) NULL ,
    QW_SOUNDEX varchar(4) NULL ,
    QW_TITLE varchar(40) NULL ,
    QW_DEPARTMENT varchar(100) NULL ,
    QW_MAILSTOP varchar(100) NULL ,
    QW_PHONE varchar(30) NULL ,
    QW_ALTPHONE varchar(30) NULL ,
    QW_EMAIL varchar(255) NULL ,
    QW_FAX varchar(30) NULL ,
    QW_NOTES longtext  NULL ,
    PRIMARY KEY (QW_CUSTOMERID)
)
//

CREATE TABLE QW_ENVIRONMENT (
    QW_ENVID int  NOT NULL,
    QW_ENVCONFNAME varchar(100) NULL,
    QW_PRODUCTID int  NOT NULL,
    QW_ORGID int  NOT NULL,
    QW_APPOS int  NOT NULL,
    QW_APPOSVERSION int  NULL,
    QW_APPHARDWARE int  NOT NULL,
    QW_DBOS int  NOT NULL,
    QW_DBOSVERSION int  NULL,
    QW_DBHARDWARE int  NOT NULL,
    QW_DESCRIPTION varchar(2000) NOT NULL,
    PRIMARY KEY (QW_ENVID),
    CONSTRAINT fk_qw_productid_env FOREIGN KEY (QW_PRODUCTID) REFERENCES QW_PRODUCT (QW_PRODUCTID),
    CONSTRAINT fk_qw_orgid_env FOREIGN KEY (QW_ORGID) REFERENCES QW_ORGANIZATION (QW_ORGID)
) 
//

CREATE TABLE QW_HARDWARE (
    QW_HARDWAREID  int  NOT NULL,
    QW_NAME        varchar(255) NOT NULL ,
    QW_DESCRIPTION varchar(2000) NULL ,
    PRIMARY KEY (QW_HARDWAREID)
)
//

CREATE TABLE QW_OS (
    QW_OSID  int  NOT NULL,
    QW_NAME        varchar(255) NOT NULL ,
    QW_DESCRIPTION varchar(2000) NULL ,
    PRIMARY KEY (QW_OSID)
)
//

CREATE TABLE QW_TICKET(
    QW_TICKETID             int  NOT NULL,
    QW_STATUS               int  NOT NULL,
    QW_PRIORITY             int  NOT NULL,
    QW_TYPE                 int  NOT NULL,
    QW_SOURCE               int  NOT NULL,
    QW_IMPACT               int  NOT NULL,
    QW_PROBLEM              varchar(255) NOT NULL,
    QW_ACTION               varchar(255) NULL,
    QW_DESCRIPTION          varchar(2000) NOT NULL,
    QW_SOLUTION             int  NULL,
    QW_RESPONSE             varchar(2000) NULL,
    QW_INTCOMMENTS          text NULL,
    QW_NOTES                text NULL,
    QW_HISTORY              int  NULL,
    QW_DESCRIPTIONTEXT      text NULL,
    QW_RESPONSETEXT         text   NULL,
    QW_AGENTID              int  NOT NULL,
    QW_OWNERID              int  NULL,
    QW_WORKGROUPID          int  NULL,
    QW_ESCWORKGROUPID       int  NULL,
    QW_DATEREPORTED         datetime  NULL,
    QW_DATEASSIGNED         datetime  NULL,
    QW_DATEOWNED            datetime  NULL,
    QW_DATERESOLVED         datetime  NULL,
    QW_DATEREQUESTED        datetime  NULL,
    QW_ETA                  datetime  NULL,
    QW_DATEDELIVERED        datetime  NULL,
    QW_DATECLOSED           datetime  NULL,
    QW_LASTESCALATED        datetime  NULL,
    QW_LASTREOPENED         datetime  NULL,
    QW_LASTSTATUSCHANGED    datetime  NULL,
    QW_DATEMODIFIED         datetime  NULL,
    QW_HOLDTILLDATE         datetime  NULL,
    QW_PHONETIME            int  NULL,
    QW_RESEARCHTIME         int  NULL,
    QW_REOPENCNT            int  NULL,
    QW_PRODUCTID            int  NOT NULL,
    QW_COMPONENTID          int  NULL,
    QW_REPBUILDVERSIONID    int  NOT NULL,
    QW_RESBUILDVERSIONID    int  NULL,
    QW_CATEGORY             int  NULL,
    QW_SUBCATEGORY          int  NULL,
    QW_OTHERREASON          varchar(1000) NULL,
    QW_REASONCODE           int  NULL,
    QW_REPENVIRONMENTID     int  NULL,
    QW_TESTENVIRONMENTID    int  NULL,
    QW_MODIFIEDBYID         int  NULL,
    QW_CUSTOMERID           int  NULL,
    QW_EMPLOYEEID           int  NULL,
    CONSTRAINT pk_qw_ticket PRIMARY KEY (QW_TICKETID),
    CONSTRAINT fk_qw_ticket_agent FOREIGN KEY (QW_AGENTID) REFERENCES QX_USERS (PKEY),    
    CONSTRAINT fk_qw_ticket_buildversion FOREIGN KEY (QW_REPBUILDVERSIONID) REFERENCES QW_BUILDVERSION (QW_BUILDID),
    CONSTRAINT fk_qw_ticket_component FOREIGN KEY (QW_COMPONENTID) REFERENCES QW_COMPONENT (QW_COMPONENTID),
    CONSTRAINT fk_qw_ticket_escwgroup FOREIGN KEY (QW_ESCWORKGROUPID) REFERENCES QX_WORKGROUP (PKEY),
    CONSTRAINT fk_qw_ticket_owner FOREIGN KEY (QW_OWNERID) REFERENCES QX_USERS (PKEY),
    CONSTRAINT fk_qw_ticket_product FOREIGN KEY (QW_PRODUCTID) REFERENCES QW_PRODUCT (QW_PRODUCTID),
    CONSTRAINT fk_qw_ticket_resversion FOREIGN KEY (QW_RESBUILDVERSIONID) REFERENCES QW_BUILDVERSION (QW_BUILDID),
    CONSTRAINT fk_qw_ticket_wgroup FOREIGN KEY (QW_WORKGROUPID) REFERENCES QX_WORKGROUP (PKEY),
    CONSTRAINT fk_qw_ticket_modifiedby FOREIGN KEY (QW_MODIFIEDBYID) REFERENCES QX_USERS (PKEY),
    CONSTRAINT fk_qw_ticket_customer FOREIGN KEY (QW_CUSTOMERID) REFERENCES QW_CUSTOMER (QW_CUSTOMERID),
    CONSTRAINT fk_qw_ticket_employee FOREIGN KEY (QW_EMPLOYEEID) REFERENCES QW_EMPLOYEE (QW_EMPLOYEEID)
)
//

CREATE TABLE QW_EVENTTYPE (
    QW_EVENTID   int NOT NULL,
    QW_EVENTTYPE varchar(100),
    PRIMARY KEY (QW_EVENTID)
)
//

CREATE TABLE QW_TICKETEVENT (
    QW_EVENTID int NOT NULL ,
    QW_TICKETID int NOT NULL ,
    QW_EVENTTYPE int NOT NULL,
    QW_EVENTDATE datetime NULL ,
    QW_MADEBY int NOT NULL ,
    QW_CUSTOMER int NULL ,
    QW_OLDVALUE varchar(100) NULL,
    QW_NEWVALUE varchar(100) NULL,
    PRIMARY KEY (QW_EVENTID),
    CONSTRAINT fk_qw_eventtype FOREIGN KEY (QW_EVENTTYPE) REFERENCES QW_EVENTTYPE (QW_EVENTID)
)
//

CREATE TABLE QW_INTERACTION(
    QW_INTERACTIONID   int  NOT NULL,
    QW_SOURCE          int  NOT NULL,
    QW_ACTION          int  NULL,
    QW_PROBLEM         varchar(255) NOT NULL,
    QW_DESCRIPTION     varchar(2000) NOT NULL,
    QW_INTCOMMENTS     text NULL,
    QW_NOTES           text NULL,
    QW_HISTORY         int  NULL,
    QW_AGENTID         int  NOT NULL,
    QW_DATERECEIVED    datetime  NULL,
    QW_DATEREPORTED    datetime  NULL,
    QW_PHONETIME       int  NULL,
    QW_RESEARCHTIME    int  NULL,
    QW_CUSTOMERID      int  NULL,
    QW_EMPLOYEEID      int  NULL,
    QW_DATEMODIFIED    datetime  NULL,
    QW_MODIFIEDBYID    int  NULL,
    CONSTRAINT pk_qw_interaction PRIMARY KEY (QW_INTERACTIONID),
    CONSTRAINT fk_qw_inter_agent FOREIGN KEY (QW_AGENTID) REFERENCES QX_USERS (PKEY),
    CONSTRAINT fk_qw_inter_modifiedby FOREIGN KEY (QW_MODIFIEDBYID) REFERENCES QX_USERS (PKEY),
    CONSTRAINT fk_qw_inter_customer FOREIGN KEY (QW_CUSTOMERID) REFERENCES QW_CUSTOMER (QW_CUSTOMERID),
    CONSTRAINT fk_qw_inter_employee FOREIGN KEY (QW_EMPLOYEEID) REFERENCES QW_EMPLOYEE (QW_EMPLOYEEID)
)
//

CREATE TABLE QW_INTER_TICKET(
    QW_ID            int NOT NULL,
    QW_INTERACTIONID int NOT NULL,
    QW_TICKETID      int NOT NULL,
    PRIMARY KEY (QW_ID)
)
//

CREATE TABLE QW_CATEGORY (
    QW_CATEGORYID  int  NOT NULL,
    QW_NAME        varchar(255) NOT NULL ,
    QW_DESCRIPTION varchar(2000) NULL ,
    PRIMARY KEY (QW_CATEGORYID)
)
//

CREATE TABLE QW_SUBCATEGORY (
    QW_SUBCATEGORYID int  NOT NULL,
    QW_CATEGORYID    int  NOT NULL,
    QW_NAME          varchar(255) NOT NULL ,
    QW_DESCRIPTION   varchar(2000) NULL ,
    PRIMARY KEY (QW_SUBCATEGORYID),
    CONSTRAINT fk_qw_categoryid FOREIGN KEY (QW_CATEGORYID) REFERENCES QW_CATEGORY (QW_CATEGORYID)
)
//


CREATE TABLE QW_TICKETNOTIFICATION (
    QW_NOTIFICATIONID                INT NOT NULL,
    QW_PRODUCTID                     INT,
    QW_ORGANIZATIONID                INT,
    QW_EMAIL                         VARCHAR(255),
    QW_INCLUDERECORD                 NUMERIC(2,0),
    QW_SENDMODE                      NUMERIC(2,0),
    QW_SUBJECT                       VARCHAR(255) NOT NULL,
    QW_MAILBODY                      LONGTEXT NULL,
    QW_DISABLE                       NUMERIC(2,0),
    QW_ONANYUPDATE                   NUMERIC,
    QW_DESCRIPTION                   VARCHAR(255),
    PRIMARY KEY (QW_NOTIFICATIONID)
)
//

-- QW_TICKETNOTIFICATION_PRIORITY
CREATE TABLE QW_TICKETNOTIFICATION_PRIORITY (
    QW_ID                            INT NOT NULL,
    QW_PRIORITY                      INT NOT NULL,
    QW_NOTIFICATIONID                INT NOT NULL,
    CONSTRAINT FK_QW_PRIORITY_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID) ON UPDATE RESTRICT
)
//

-- QW_TICKETNOTIFICATION_RECIP
CREATE TABLE QW_TICKETNOTIFICATION_RECIP (
    QW_ID                            INT NOT NULL,
    QW_RECIPIENT                     INT NOT NULL,
    QW_NOTIFICATIONID                INT NOT NULL,
    CONSTRAINT FK_QW_RECIP_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID) ON UPDATE RESTRICT
)
//

-- QW_TICKETNOTIFICATION_STATUS
CREATE TABLE QW_TICKETNOTIFICATION_STATUS (
   QW_ID                              INT NOT NULL,
    QW_STATUS                         INT NOT NULL,
    QW_NOTIFICATIONID                 INT NOT NULL,
   CONSTRAINT FK_QW_STATUS_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID) ON UPDATE RESTRICT
)
//

-- QW_TICKETESCALATION
CREATE TABLE QW_TICKETESCALATION (
    QW_ESCALATIONID					INT NOT NULL,
    QW_PRODUCTID					INT,
	QW_PRODUCTFILTER				VARCHAR(255),
    QW_ORGANIZATIONID				INT,
	QW_EMPLOYEEID					INT,
	QW_WORKGROUPID					INT,
    QW_EMAIL						VARCHAR(255),
	QW_WAITTIME		                INT NOT NULL,
    QW_TIMEUNIT		                NUMERIC(2,0) NOT NULL,
	QW_FIREIF						INT NOT NULL,
    QW_REPEAT						NUMERIC(2,0),
    QW_SUBJECT                      VARCHAR(255),
    QW_MAILBODY						TEXT NULL,
    QW_DISABLE                      NUMERIC(2,0),
    QW_DESCRIPTION                  VARCHAR(255),
    QW_SENDMODE 		    NUMERIC(2,0) NULL,
  CONSTRAINT PK_QW_TICKETESCALATION PRIMARY KEY (QW_ESCALATIONID)
)
//

-- QW_TICKETESCALATION_PRIORITY
CREATE TABLE QW_TICKETESCALATION_PRIORITY (
    QW_ID                          INT NOT NULL,
    QW_PRIORITY                    INT,
    QW_ESCALATIONID                INT,
    CONSTRAINT FK_QW_PRIORITY_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
//

-- QW_TICKETESCALATION_STATUS
CREATE TABLE QW_TICKETESCALATION_STATUS (
    QW_ID                          INT NOT NULL,
    QW_STATUS                      INT,
    QW_ESCALATIONID                INT,
    CONSTRAINT FK_QW_STATUS_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
//

-- QW_TICKETESCALATION_RECIPIENT
CREATE TABLE QW_TICKETESCALATION_RECIPIENT (
    QW_ID                          INT NOT NULL,  
    QW_RECIPIENT                   INT,
    QW_ESCALATIONID                INT,
    CONSTRAINT FK_QW_RECIPIENT_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
//

-- QW_ESCALATION_EVENT
CREATE TABLE QW_ESCALATION_EVENT (
    QW_ESCEVENTID		INT NOT NULL,
    QW_ESCALATIONID		INT NOT NULL,
    QW_TICKETID			INT NOT NULL,
    QW_NEXT_CHECK		DATETIME  NOT NULL,
    QW_DATEMODIFIED		DATETIME  NULL,
    CONSTRAINT PK_QW_ESCALATION_EVENT PRIMARY KEY (QW_ESCEVENTID)
)
//

-- QW_SOLUTION
CREATE TABLE QW_SOLUTION_CATEGORY (
    QW_CATEGORYID  int  NOT NULL,
    QW_NAME        varchar(255) NOT NULL ,
    QW_DESCRIPTION varchar(2000) NULL ,
    CONSTRAINT pk_qw_sol_category PRIMARY KEY (QW_CATEGORYID)
)
//

CREATE TABLE QW_SOLUTION_SUBCATEGORY (
    QW_SUBCATEGORYID int  NOT NULL,
    QW_CATEGORYID    int  NOT NULL,
    QW_NAME          varchar(255) NOT NULL ,
    QW_DESCRIPTION   varchar(2000) NULL ,
    CONSTRAINT pk_qw_sol_subcategory PRIMARY KEY (QW_SUBCATEGORYID),
    CONSTRAINT fk_qw_sol_categoryid FOREIGN KEY (QW_CATEGORYID) REFERENCES QW_SOLUTION_CATEGORY (QW_CATEGORYID)
)
//


CREATE TABLE QW_SOLUTION (
    QW_SOLUTIONID                  INT NOT NULL,
    QW_TOPIC                       NUMERIC(2,0),
    QW_SOLUTIONSTATUS              NUMERIC(2,0),
    QW_CATEGORYID                  int NULL,
    QW_SUBCATEGORYID               int NULL,
    QW_PRODUCTID                   int NULL,
    QW_COMPONENTID                 int NULL,
    QW_SUMMARY                     VARCHAR(255) NOT NULL,
    QW_PROBLEM                     VARCHAR(2000) NOT NULL,
    QW_PROBLEMTEXT                 text NULL,
    QW_SOLUTION                    VARCHAR(2000) NOT NULL,
    QW_SOLUTIONTEXT                text NULL,
    QW_AUDIT                       int NULL,
    QW_INTCOMMENTS                 text NULL,
    QW_CREATOR                     INT NOT NULL,
    QW_REVIEWER                    INT NULL,
    QW_PUBLISHER                   INT NULL,
    QW_MODIFIEDBY                  INT NOT NULL,
    QW_LASTMODIFIED                DATETIME NOT NULL,
    QW_DATECREATED                 DATETIME NOT NULL,
    QW_DATEREVIEWED                DATETIME NULL,
    QW_DATEPUBLISHED               DATETIME NULL,
    QW_DATEEXPIRATION              DATETIME NOT NULL,
    QW_DOCUMENTID                  VARCHAR(100),
  CONSTRAINT PK_SOLUTION PRIMARY KEY (QW_SOLUTIONID),
  CONSTRAINT FK_QW_SOLUTION_CREATOR FOREIGN KEY (QW_CREATOR) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_PUBLISHER FOREIGN KEY (QW_PUBLISHER) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_REVIEWER FOREIGN KEY (QW_REVIEWER) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_MODIFIEDBY FOREIGN KEY (QW_MODIFIEDBY) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_CATEGORY FOREIGN KEY (QW_CATEGORYID) REFERENCES QW_SOLUTION_CATEGORY (QW_CATEGORYID),
  CONSTRAINT FK_QW_SOLUTION_SUBCATEGORY FOREIGN KEY (QW_SUBCATEGORYID) REFERENCES QW_SOLUTION_SUBCATEGORY (QW_SUBCATEGORYID)
)
//

CREATE TABLE QW_WORKFLOW (
    QW_WORKFLOWID  int  NOT NULL,
    QW_OBJECTTYPE  int  NOT NULL,
    QW_STATUSFROM  int  NULL,
    QW_STATUSTO    int  NULL,
    QW_ROLEID      int  NULL,
    CONSTRAINT pk_qw_workflow PRIMARY KEY (QW_WORKFLOWID)
)
//