-- Guardem les dades de la connexió actual
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS JOB_GRADES;
DROP TABLE IF EXISTS JOB_HISTORY;
DROP TABLE IF EXISTS EMPLOYEES;
DROP TABLE IF EXISTS JOBS;
DROP TABLE IF EXISTS DEPARTMENTS;
DROP TABLE IF EXISTS LOCATIONS;
DROP TABLE IF EXISTS COUNTRIES;
DROP TABLE IF EXISTS REGIONS;


CREATE TABLE REGIONS(
    REGION_ID INTEGER,
    REGION_NAME VARCHAR(25),
    PRIMARY KEY (REGION_ID));

            
CREATE TABLE COUNTRIES(
    COUNTRY_ID CHAR(2),
    COUNTRY_NAME VARCHAR(40),
    REGION_ID INTEGER,
    PRIMARY KEY (COUNTRY_ID),
    FOREIGN KEY(REGION_ID) REFERENCES REGIONS(REGION_ID));              


CREATE TABLE LOCATIONS (
     LOCATION_ID INT(4),
     STREET_ADDRESS VARCHAR(40),
     POSTAL_CODE VARCHAR(12),
     CITY VARCHAR(30) NOT NULL,
     STATE_PROVINCE VARCHAR(25),
     COUNTRY_ID CHAR(2),
     PRIMARY KEY(LOCATION_ID),
     FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRIES(COUNTRY_ID)); 


CREATE TABLE DEPARTMENTS(
   DEPARTMENT_ID INT(4),
   DEPARTMENT_NAME VARCHAR(30) NOT NULL,
   MANAGER_ID INT(6),
   LOCATION_ID INT(4),
   PRIMARY KEY(DEPARTMENT_ID),
   FOREIGN KEY(LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID));


CREATE TABLE JOBS(
   JOB_ID VARCHAR(10),
   JOB_TITLE VARCHAR(35) NOT NULL,
   MIN_SALARY INT(6),
   MAX_SALARY INT(6),
   PRIMARY KEY(JOB_ID));


CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID INT(6),
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(25) NOT NULL,
    PHONE_NUMBER VARCHAR(20),
    HIRE_DATE DATE NOT NULL,
    JOB_ID VARCHAR(10) NOT NULL,
    SALARY DECIMAL(8,2),
    COMMISSION_PCT DECIMAL(2,2),
    MANAGER_ID INT(6),
    DEPARTMENT_ID INT(4),
    PRIMARY KEY(EMPLOYEE_ID),
    FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
    FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
    FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID),
    CONSTRAINT EMP_EMAIL_UK UNIQUE(EMAIL));

CREATE TABLE JOB_HISTORY(
  EMPLOYEE_ID INT(6),
  START_DATE DATE NOT NULL,
  END_DATE DATE NOT NULL,
  JOB_ID VARCHAR(10) NOT NULL,
  DEPARTMENT_ID INT(4),
  PRIMARY KEY(EMPLOYEE_ID, JOB_ID),
  FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
  FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
  FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));

CREATE TABLE JOB_GRADES(
     GRADE_LEVEL VARCHAR(3),
     LOWEST_SAL INTEGER,
     HIGHEST_SAL INTEGER);


INSERT INTO REGIONS VALUES (1, 'Europe');
INSERT INTO REGIONS VALUES (2, 'Americas');
INSERT INTO REGIONS VALUES (3, 'Asia');
INSERT INTO REGIONS VALUES (4, 'Middle East and Africa');



INSERT INTO COUNTRIES VALUES( 'CA', 'Canada', 2);
INSERT INTO COUNTRIES VALUES( 'DE', 'Germany', 1);
INSERT INTO COUNTRIES VALUES( 'UK', 'United Kingdom', 1);
INSERT INTO COUNTRIES VALUES( 'US', 'United States of America', 2);



INSERT INTO LOCATIONS VALUES( 1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO LOCATIONS VALUES( 1500, '2001 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO LOCATIONS VALUES( 1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO LOCATIONS VALUES( 1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'Ontario', 'CA');
INSERT INTO LOCATIONS VALUES( 2500, 'Magdalen Centre, The Ofxord Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');




INSERT INTO DEPARTMENTS VALUES (10, 'Administration', 200, 1700);
INSERT INTO DEPARTMENTS VALUES (20, 'Marketing', 201, 1800);
INSERT INTO DEPARTMENTS VALUES (50, 'Shipping', 124, 1500);
INSERT INTO DEPARTMENTS VALUES (60, 'IT', 103, 1400);
INSERT INTO DEPARTMENTS VALUES (80, 'Sales', 149, 2500);
INSERT INTO DEPARTMENTS VALUES (90, 'Executive', 100, 1700);
INSERT INTO DEPARTMENTS VALUES (110, 'Accounting', 205, 1700);
INSERT INTO DEPARTMENTS VALUES (190, 'Contracting', NULL, 1700);



INSERT INTO JOBS VALUES('AD_PRES', 'President', 20000, 40000);
INSERT INTO JOBS VALUES('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO JOBS VALUES('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO JOBS VALUES('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO JOBS VALUES('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO JOBS VALUES('SA_MAN', 'Sales Manager', 10000, 20000);
INSERT INTO JOBS VALUES('SA_REP', 'Sales Representative', 6000, 12000);
INSERT INTO JOBS VALUES('ST_MAN', 'Stock Manager', 5500, 8500);
INSERT INTO JOBS VALUES('ST_CLERK', 'Stock Clerk', 2000, 5000);
INSERT INTO JOBS VALUES('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO JOBS VALUES('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO JOBS VALUES('MK_REP', 'Marketing Representative', 4000, 9000);



INSERT INTO EMPLOYEES VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567','1987-06-17','AD_PRES', 24000, NULL, NULL, 90);
INSERT INTO EMPLOYEES VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21','AD_VP', 17000, NULL, 100, 90); 
INSERT INTO EMPLOYEES VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1993-01-13','AD_VP', 17000, NULL, 100, 90); 
INSERT INTO EMPLOYEES VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1990-01-03','IT_PROG', 9000, NULL, 102, 60); 
INSERT INTO EMPLOYEES VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1991-05-21','IT_PROG', 6000, NULL, 103, 60); 
INSERT INTO EMPLOYEES VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1999-02-07','IT_PROG', 4200, NULL, 103, 60); 
INSERT INTO EMPLOYEES VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '1999-11-19','ST_MAN', 5800, NULL, 100, 50); 
INSERT INTO EMPLOYEES VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '1995-10-17','ST_CLERK', 3500, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '1997-01-29','ST_CLERK', 3100, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', '1998-03-15','ST_CLERK', 2600, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '1998-07-09','ST_CLERK', 2500, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '2000-01-29','SA_MAN', 10500, .2, 100, 80); 
INSERT INTO EMPLOYEES VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '1996-05-11','SA_REP', 11000, .3, 149, 80); 
INSERT INTO EMPLOYEES VALUES (176, 'Jonathan', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '1998-03-24','SA_REP', 8600, .2, 149, 80); 
INSERT INTO EMPLOYEES VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '1999-05-24','SA_REP', 7000, .15, 149, NULL); 
INSERT INTO EMPLOYEES VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '1987-09-17','AD_ASST', 4400, NULL, 101, 10); 
INSERT INTO EMPLOYEES VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '1996-02-17','MK_MAN', 13000, NULL, 100, 20); 
INSERT INTO EMPLOYEES VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '1997-08-17','MK_REP', 6000, NULL, 201, 20); 
INSERT INTO EMPLOYEES VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '1994-06-07','AC_MGR', 12000, NULL, 101, 110); 
INSERT INTO EMPLOYEES VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', '1994-06-07','AC_ACCOUNT', 8300, NULL, 205, 110);  

ALTER TABLE DEPARTMENTS ADD CONSTRAINT FK_DEPARTMENTS_EMPLOYEES
   FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID);


INSERT INTO JOB_HISTORY VALUES( 102, '1993-01-13', '1998-07-24', 'IT_PROG', 60);
INSERT INTO JOB_HISTORY VALUES( 101, '1989-09-21', '1993-10-27', 'AC_ACCOUNT', 110);
INSERT INTO JOB_HISTORY VALUES( 101, '1993-10-28', '1997-03-15', 'AC_MGR', 110);
INSERT INTO JOB_HISTORY VALUES( 201, '1996-02-17', '1999-12-19', 'MK_REP', 20);
INSERT INTO JOB_HISTORY VALUES( 144, '1998-03-24', '1999-12-31', 'ST_CLERK', 50);
INSERT INTO JOB_HISTORY VALUES( 142, '1999-01-01', '1999-12-31', 'ST_CLERK', 50);
INSERT INTO JOB_HISTORY VALUES( 200, '1987-09-17', '1993-06-17', 'AD_ASST', 90);
INSERT INTO JOB_HISTORY VALUES( 176, '1998-03-24', '1998-12-31', 'SA_REP', 80);
INSERT INTO JOB_HISTORY VALUES( 176, '1999-01-01', '1999-12-31', 'SA_MAN', 80);
INSERT INTO JOB_HISTORY VALUES( 200, '1994-07-01', '1998-12-31', 'AC_ACCOUNT', 90);





INSERT INTO JOB_GRADES VALUES('A', 1000, 2999);
INSERT INTO JOB_GRADES VALUES('B', 3000, 5999);
INSERT INTO JOB_GRADES VALUES('C', 6000, 9999);
INSERT INTO JOB_GRADES VALUES('D', 10000, 14999);
INSERT INTO JOB_GRADES VALUES('E', 15000, 24999);
INSERT INTO JOB_GRADES VALUES('F', 25000, 40000);

SET FOREIGN_KEY_CHECKS=1;

-- Restaurem les dades de la connexió actual
SET TIME_ZONE=@OLD_TIME_ZONE;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;
