mysql.sql

/************************************
* DROP CONSTRAINTS
************************************/
ALTER TABLE CONTACT DROP FOREIGN KEY fk1_contact;
ALTER TABLE CONTACT DROP FOREIGN KEY fk2_contact;
ALTER TABLE SYSTEM_USER DROP FOREIGN KEY fk1_system_user;
ALTER TABLE SYSTEM_USER DROP FOREIGN KEY fk2_system_user;
ALTER TABLE PROJECT DROP FOREIGN KEY fk1_project;
ALTER TABLE PROJECT DROP FOREIGN KEY fk2_project;
ALTER TABLE PROJECT DROP FOREIGN KEY fk3_project;
ALTER TABLE INTERACTION DROP FOREIGN KEY fk1_interaction;
ALTER TABLE INTERACTION DROP FOREIGN KEY fk2_interaction;
ALTER TABLE INTERACTION DROP FOREIGN KEY fk3_interaction;
ALTER TABLE INTER_PRO_JUNC DROP FOREIGN KEY fk1_inter_pro_junc;
ALTER TABLE INTER_PRO_JUNC DROP FOREIGN KEY fk2_inter_pro_junc;
ALTER TABLE MESSAGE DROP FOREIGN KEY fk1_message;

/************************************
* DROP TABLES
************************************/
DROP TABLE CONTACT;
DROP TABLE COMMON_LOOKUP;
DROP TABLE SYSTEM_USER;
DROP TABLE MESSAGE;
DROP TABLE INTERACTION;
DROP TABLE PROJECT;
DROP TABLE INTER_PRO_JUNC;

/************************************
* CREATE TABLES/CONSTRAINTS
************************************/
CREATE TABLE COMMON_LOOKUP(
     COMMON_LOOKUP_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    , COMMON_LOOKUP_TABLE VARCHAR(30) NOT NULL
    , COMMON_LOOKUP_COLUMN VARCHAR(30) NOT NULL
    , COMMON_LOOKUP_TYPE VARCHAR(30)
    , COMMON_LOOKUP_CODE VARCHAR(8)
    , COMMON_LOOKUP_MEANING VARCHAR(255) 
    , CREATED_BY INT NOT NULL
    , CREATION_DATE DATE  NOT NULL
    , LAST_UPDATED_BY INT NOT NULL
    , LAST_UPDATED_DATE DATE NOT NULL
);

CREATE TABLE CONTACT(
     CONTACT_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    , FK_CONTACT_TYPE INT NOT NULL
    , LAST_NAME VARCHAR(40) NOT NULL
    , FIRST_NAME VARCHAR(40) NOT NULL
    , PRIMARY_PHONE INT NOT NULL
    , SECONDARY_PHONE INT
    , ADDRESS VARCHAR(120)
    , EMAIL VARCHAR(60) NOT NULL
    , FK_DEFAULT_CONTACT_METHOD INT NOT NULL
    , CREATED_BY INT NOT NULL
    , CREATION_DATE DATE  NOT NULL
    , LAST_UPDATED_BY INT NOT NULL
    , LAST_UPDATED_DATE DATE NOT NULL
);

ALTER TABLE CONTACT ADD CONSTRAINT fk1_contact
FOREIGN KEY (FK_CONTACT_TYPE) REFERENCES COMMON_LOOKUP(COMMON_LOOKUP_ID);

ALTER TABLE CONTACT ADD CONSTRAINT fk2_contact
FOREIGN KEY (FK_DEFAULT_CONTACT_METHOD) REFERENCES COMMON_LOOKUP(COMMON_LOOKUP_ID);

CREATE TABLE SYSTEM_USER(
     SYSTEM_USER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    , SYSTEM_USER_NAME VARCHAR(20) NOT NULL
    , FK_CONTACT_ID INT NOT NULL
    , FK_SYSTEM_USER_TYPE INT
    , CREATED_BY INT NOT NULL
    , CREATION_DATE DATE NOT NULL
    , LAST_UPDATED_BY INT NOT NULL
    , LAST_UPDATED_DATE DATE NOT NULL
);

ALTER TABLE SYSTEM_USER ADD CONSTRAINT fk1_system_user
FOREIGN KEY (FK_SYSTEM_USER_TYPE) REFERENCES COMMON_LOOKUP(COMMON_LOOKUP_ID);


CREATE TABLE PROJECT(
     PROJECT_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    , FK_CONTACT_ID INT
    , FK_SYSTEM_USER_ID INT
    , FK_SEVERITY INT
    , DUE_DATE DATE
    , NEXT_RESPONSE_DUE DATE
    , CREATED_BY INT
    , CREATION_DATE DATE
    , LAST_UPDATED_BY INT
    , LAST_UPDATED_DATE DATE
);

ALTER TABLE PROJECT ADD CONSTRAINT fk1_project
FOREIGN KEY (FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID);

ALTER TABLE PROJECT ADD CONSTRAINT fk2_project
FOREIGN KEY (FK_SYSTEM_USER_ID) REFERENCES SYSTEM_USER(SYSTEM_USER_ID);

ALTER TABLE PROJECT ADD CONSTRAINT fk3_project
FOREIGN KEY (FK_SEVERITY) REFERENCES COMMON_LOOKUP(COMMON_LOOKUP_ID);

CREATE TABLE INTERACTION(
     INTERACTION_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    , FK_SYSTEM_USER_ID INT NOT NULL
    , FK_CONTACT_ID INT NOT NULL
    , FK_INTERACTION_TYPE INT NOT NULL
    , INTERACTION_SOURCE_OBJECT TEXT NOT NULL 
    , CREATED_BY INT
    , CREATION_DATE DATE
    , LAST_UPDATED_BY INT
    , LAST_UPDATED_DATE DATE
);

ALTER TABLE INTERACTION ADD CONSTRAINT fk1_interaction
FOREIGN KEY (FK_SYSTEM_USER_ID) REFERENCES SYSTEM_USER(SYSTEM_USER_ID);

ALTER TABLE INTERACTION ADD CONSTRAINT fk2_interaction
FOREIGN KEY (FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID);

ALTER TABLE INTERACTION ADD CONSTRAINT fk3_interaction
FOREIGN KEY (FK_INTERACTION_TYPE) REFERENCES COMMON_LOOKUP(COMMON_LOOKUP_ID);

CREATE TABLE INTER_PRO_JUNC(
          INTER_PRO_JUNC_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
        , FK_INTERACTION_ID INT
        , FK_PROJECT_ID INT
);

ALTER TABLE INTER_PRO_JUNC 
        ADD CONSTRAINT fk1_inter_pro_junc
        FOREIGN KEY (FK_INTERACTION_ID) REFERENCES INTERACTION(INTERACTION_ID) ON DELETE CASCADE;

ALTER TABLE INTER_PRO_JUNC 
        ADD CONSTRAINT fk2_inter_pro_junc
        FOREIGN KEY (FK_PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE;

CREATE TABLE MESSAGE(
     MESSAGE_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    , FK_INTERACTION_ID INT
    , MESSAGE VARCHAR(1000) NOT NULL
    , CREATED_BY INT NOT NULL
    , CREATION_DATE DATE NOT NULL
    , LAST_UPDATED_BY INT NOT NULL
    , LAST_UPDATED_DATE DATE NOT NULL
);

ALTER TABLE MESSAGE ADD CONSTRAINT fk1_message
FOREIGN KEY (FK_INTERACTION_ID) REFERENCES INTERACTION(INTERACTION_ID);


INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'PROJECT'
    , 'FK_SEVERITY'
    , 'Severity'
    , 1
    , 'One'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'



/************************************
* INERST DATA
************************************/

/*
COMMON_LOOKUP INSERTS
*/
INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'SYSTEM_USER'
    , 'FK_SYSTEM_USER_TYPE'
    , 'System User'
    , 1
    , 'System User'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'CONTACT'
    , 'FK_CONTACT_TYPE'
    , 'Contact Type'
    , 'CT'
    , 'Customer'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'CONTACT'
    , 'FK_DEFAULT_CONTACT_METHOD'
    , 'Contact Method'
    , 'CM'
    , 'Phone'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'CONTACT'
    , 'FK_DEFAULT_CONTACT_METHOD'
    , 'Contact Method'
    , 'CM'
    , 'Email'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'CONTACT'
    , 'FK_DEFAULT_CONTACT_METHOD'
    , 'Contact Method'
    , 'CM'
    , 'Mail'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'PROJECT'
    , 'FK_SEVERITY'
    , 'Severity'
    , 1
    , 'One'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'PROJECT'
    , 'FK_SEVERITY'
    , 'Severity'
    , 2
    , 'Two'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

INSERT INTO COMMON_LOOKUP(
      COMMON_LOOKUP_ID 
    , COMMON_LOOKUP_TABLE 
    , COMMON_LOOKUP_COLUMN 
    , COMMON_LOOKUP_TYPE 
    , COMMON_LOOKUP_CODE 
    , COMMON_LOOKUP_MEANING 
    , CREATED_BY
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE
)
VALUES(
      NULL
    , 'PROJECT'
    , 'FK_SEVERITY'
    , 'Severity'
    , 3
    , 'Three'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

/*
SYSTEM_USER INSERTS
*/

INSERT INTO SYSTEM_USER(
    SYSTEM_USER_ID
    , SYSTEM_USER_NAME
    , FK_CONTACT_ID
    , FK_SYSTEM_USER_TYPE
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATED_DATE) 
VALUES(
      NULL
    , 'SYSTEM'
    , 1
    , 1
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

/*
CONTACT INSERTS
*/

INSERT INTO CONTACT(
      CONTACT_ID
    , FK_CONTACT_TYPE 
    , LAST_NAME 
    , FIRST_NAME 
    , PRIMARY_PHONE 
    , SECONDARY_PHONE 
    , ADDRESS 
    , EMAIL 
    , FK_DEFAULT_CONTACT_METHOD
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATED_DATE)
VALUES(
      NULL
    , (SELECT COMMON_LOOKUP_ID FROM COMMON_LOOKUP WHERE COMMON_LOOKUP_MEANING = 'Customer')
    , 'Rob'
    , 'Page'
    , 12
    ,  NULL
    , '3727 West Mountain View Drive Rexburg, ID 83440'
    , 'pager925@gmail.com'
    , (SELECT COMMON_LOOKUP_ID FROM COMMON_LOOKUP WHERE COMMON_LOOKUP_MEANING = 'Phone')
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

ALTER TABLE SYSTEM_USER ADD CONSTRAINT fk2_system_user
FOREIGN KEY (FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID);


/*
PROJECT INSERTS
*/

INSERT INTO PROJECT(
      PROJECT_ID 
    , FK_CONTACT_ID 
    , FK_SYSTEM_USER_ID 
    , FK_SEVERITY 
    , DUE_DATE 
    , NEXT_RESPONSE_DUE 
    , CREATED_BY 
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE)
VALUES(
     NULL
    , (SELECT CONTACT_ID FROM CONTACT WHERE PRIMARY_PHONE = '12082065802')
    , (SELECT SYSTEM_USER_ID FROM SYSTEM_USER WHERE SYSTEM_USER_ID  = 1)
    , (SELECT COMMON_LOOKUP_ID FROM COMMON_LOOKUP WHERE COMMON_LOOKUP_ID = 4)
    , '2001-09-10'
    , '2001-09-10'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

/*
INTERACTION INSERTS
*/

INSERT INTO INTERACTION(
      INTERACTION_ID
    , FK_SYSTEM_USER_ID 
    , FK_CONTACT_ID 
    , FK_INTERACTION_TYPE 
    , INTERACTION_SOURCE_OBJECT
    , CREATED_BY 
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE)
VALUES(
      NULL
    , (SELECT SYSTEM_USER_ID FROM SYSTEM_USER WHERE SYSTEM_USER_ID = 1)
    , (SELECT CONTACT_ID FROM CONTACT WHERE CONTACT_ID = 1)
    , (SELECT COMMON_LOOKUP_ID FROM COMMON_LOOKUP WHERE COMMON_LOOKUP_MEANING = 'Phone')
    , 'njkjn'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);

/*
INTER_PRO_JUNC INSERTS
*/


INSERT INTO INTER_PRO_JUNC(
          INTER_PRO_JUNC_ID 
        , FK_INTERACTION_ID 
        , FK_PROJECT_ID)
VALUES(
      NULL
    , (SELECT INTERACTION_ID FROM INTERACTION WHERE INTERACTION_ID = 1)
    , (SELECT PROJECT_ID FROM PROJECT WHERE PROJECT_ID = 1)
);

/*
MESSAGE INSERTS
*/

INSERT INTO MESSAGE(
     MESSAGE_ID 
    , FK_INTERACTION_ID 
    , MESSAGE 
    , CREATED_BY 
    , CREATION_DATE 
    , LAST_UPDATED_BY 
    , LAST_UPDATED_DATE)
VALUES(
      NULL
    , (SELECT INTERACTION_ID FROM INTERACTION WHERE INTERACTION_ID = 1)
    , 'TEST MESSAGE ON FIRST INTERACTION'
    , 1
    , '2001-09-10'
    , 1
    , '2001-09-10'
);


/*TODO:
 * Change phone number data type

*/