CREATE TABLE dbo.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,
)
GO

CREATE TABLE dbo.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        text  NULL ,
    qw_history      int  NULL ,
    CONSTRAINT pk_qw_employee PRIMARY KEY (qw_employeeid),
    CONSTRAINT fk_qw_employee_users FOREIGN KEY (qw_userid) REFERENCES QX_USERS ( pkey )
) 
GO

CREATE TABLE dbo.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        text  NULL ,
    CONSTRAINT pk_qw_address PRIMARY KEY (qw_addressid)
)
GO

CREATE TABLE dbo.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 text  NULL ,
    CONSTRAINT pk_qw_organization PRIMARY KEY (qw_orgid)
)
GO

CREATE TABLE dbo.QW_PRODUCT (
    qw_productid int  NOT NULL,
    qw_name varchar(100) NOT NULL,
    qw_modelnumber varchar(100) NULL,
    qw_notes text  NULL,
    CONSTRAINT pk_qw_product PRIMARY KEY (qw_productid)
)
GO

CREATE TABLE dbo.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,
    CONSTRAINT pk_qw_component 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)
)
GO

CREATE TABLE dbo.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 text  NULL,
    CONSTRAINT pk_qw_buildversion PRIMARY KEY (qw_buildid),
    CONSTRAINT fk_qw_buildversion_product FOREIGN KEY (qw_productid) REFERENCES QW_PRODUCT (qw_productid)
)
GO

CREATE TABLE dbo.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 text  NULL ,
    CONSTRAINT pk_qw_customer PRIMARY KEY (qw_customerid)
)
GO

CREATE TABLE dbo.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,
    CONSTRAINT pk_qw_environment 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)
)
GO

CREATE TABLE dbo.QW_HARDWARE (
    qw_hardwareid  int  NOT NULL,
    qw_name        varchar(255) NOT NULL ,
    qw_description varchar(2000) NULL ,
    CONSTRAINT pk_qw_hardware PRIMARY KEY (qw_hardwareid)
)
GO

CREATE TABLE dbo.QW_OS (
    qw_osid  int  NOT NULL,
    qw_name        varchar(255) NOT NULL ,
    qw_description varchar(2000) NULL ,
    CONSTRAINT pk_qw_os PRIMARY KEY (qw_osid)
)
GO

CREATE TABLE dbo.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)
)
GO

CREATE TABLE dbo.QW_EVENTTYPE (
    qw_eventid   int NOT NULL,
    qw_eventtype varchar(100)
    CONSTRAINT pk_qw_eventid PRIMARY KEY (qw_eventid)
)
GO

CREATE TABLE dbo.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
    CONSTRAINT pk_qw_ticketevent PRIMARY KEY (qw_eventid),
--  CONSTRAINT fk_qw_ticketevent_ticket FOREIGN KEY (qw_ticketid) REFERENCES QW_TICKET (qw_ticketid),
    CONSTRAINT fk_qw_eventtype FOREIGN KEY (qw_eventtype) REFERENCES QW_EVENTTYPE (qw_eventid),
)
GO

CREATE TABLE dbo.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)
)
GO

CREATE TABLE dbo.QW_INTER_TICKET(
    qw_id            int NOT NULL,
    qw_interactionid int NOT NULL,
    qw_ticketid      int NOT NULL,
    CONSTRAINT pk_inter_ticket PRIMARY KEY (qw_id)
)
GO

CREATE TABLE dbo.QW_CATEGORY (
    qw_categoryid  int  NOT NULL,
    qw_name        varchar(255) NOT NULL ,
    qw_description varchar(2000) NULL ,
    CONSTRAINT pk_qw_category PRIMARY KEY (qw_categoryid)
)
GO

CREATE TABLE dbo.QW_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_subcategory PRIMARY KEY (qw_subcategoryid),
    CONSTRAINT fk_qw_categoryid FOREIGN KEY (qw_categoryid) REFERENCES QW_CATEGORY (qw_categoryid)
)
GO

-- QW_CALLSNOTIFICATION

CREATE TABLE dbo.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                      TEXT NULL,
    QW_DISABLE                       NUMERIC(2,0),
    QW_ONANYUPDATE                   NUMERIC,
    QW_DESCRIPTION                   VARCHAR(255),
  CONSTRAINT PK_QW_TICKETNOTIFICATION PRIMARY KEY (QW_NOTIFICATIONID)
)
go

-- QW_TICKETNOTIFICATION_PRIORITY
CREATE TABLE dbo.QW_TICKETNOTIFICATION_PRIORITY (
    QW_ID                            INT,
    QW_PRIORITY                      INT,
    QW_NOTIFICATIONID                INT
    CONSTRAINT FK_QW_PRIORITY_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
)
go

-- QW_TICKETNOTIFICATION_RECIPIENT
CREATE TABLE dbo.QW_TICKETNOTIFICATION_RECIP (
    QW_ID                            INT,
    QW_RECIPIENT                     INT,
    QW_NOTIFICATIONID                INT
    CONSTRAINT FK_QW_RECIP_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
)
go

-- QW_TICKETNOTIFICATION_STATUS
CREATE TABLE dbo.QW_TICKETNOTIFICATION_STATUS (
    QW_ID                            INT,
    QW_STATUS                        INT,
    QW_NOTIFICATIONID                INT
    CONSTRAINT FK_QW_STATUS_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
)
go

-- QW_TICKETESCALATION
CREATE TABLE dbo.QW_TICKETESCALATION (
    QW_ESCALATIONID         INT NOT NULL,
    QW_PRODUCTID            INT,
    QW_PRODUCTFILTER        VARCHAR(255),
    QW_ORGANIZATIONID       INT,
    QW_SENDMODE             NUMERIC(2,0),
    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),
  CONSTRAINT PK_QW_TICKETESCALATION PRIMARY KEY (QW_ESCALATIONID)
)
go

-- QW_TICKETESCALATION_PRIORITY
CREATE TABLE dbo.QW_TICKETESCALATION_PRIORITY (
    QW_ID                          INT,
    QW_PRIORITY                    INT,
    QW_ESCALATIONID                INT
    CONSTRAINT FK_QW_PRIORITY_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
go

-- QW_TICKETESCALATION_STATUS
CREATE TABLE dbo.QW_TICKETESCALATION_STATUS (
    QW_ID                          INT,
    QW_STATUS                      INT,
    QW_ESCALATIONID                INT
    CONSTRAINT FK_QW_STATUS_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
go

-- QW_TICKETESCALATION_RECIPIENT
CREATE TABLE dbo.QW_TICKETESCALATION_RECIPIENT (
    QW_ID                          INT,
    QW_RECIPIENT                   INT,
    QW_ESCALATIONID                INT
    CONSTRAINT FK_QW_RECIPIENT_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
go

-- QW_ESCALATION_EVENT
CREATE TABLE dbo.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)
)
go

-- QW_SOLUTION
CREATE TABLE dbo.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)
)
GO

CREATE TABLE dbo.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)
)
GO

CREATE TABLE dbo.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)
)
go

CREATE TABLE dbo.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)
)
GO
