CREATE DATABASE PCTRADE_INF3A

CREATE TABLE REGIONS(
    REGION_ID SMALLINT NOT NULL,
    NAME VARCHAR(25) NOT NULL,
    CONSTRAINT PK_REGIONS PRIMARY KEY (REGION_ID)
)

CREATE TABLE JOBS(
    JOB_ID VARCHAR(10) NOT NULL,
    JOB_TITLE VARCHAR(35) NOT NULL,
    MIN_SALARY NUMERIC(6),
    MAX_SALARY NUMERIC(6),
    CONSTRAINT PK_JOBS PRIMARY KEY (JOB_ID)
)

CREATE TABLE COUNTRIES(
    COUNTRY_ID CHAR(2) NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    REGION_ID SMALLINT,
    CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
    CONSTRAINT FK_COUNTRIES FOREIGN KEY (REGION_ID) REFERENCES REGIONS (REGION_ID)
    ON DELETE SET NULL
)

CREATE TABLE DEPARTMENTS(
    DEPARTMENT_ID INT NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    MANAGER_ID INT,
    COUNTRY_ID CHAR(2) NOT NULL DEFAULT 'DF',
    CITY VARCHAR(30) NOT NULL,
    STATE VARCHAR(25),
    ADDRESS VARCHAR(40),
    POSTAL_CODE VARCHAR(12),
    CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTMENT_ID),
    CONSTRAINT FK_DEP_COUNT FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRIES (COUNTRY_ID)
    ON DELETE SET DEFAULT
)

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID INT NOT NULL,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(25) UNIQUE,
    PHONE VARCHAR(20),
    HIRE_DATE DATETIME NOT NULL,
    SALARY NUMERIC(8,2) NOT NULL,
    JOB_ID VARCHAR(10) NOT NULL,
    MANAGER_ID INT,
    DEPARTMENT_ID INT,
    CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID),
    CONSTRAINT FK_EMP_JOBS FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID),
    CONSTRAINT FK_EMP_DEP FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS (DEPARTMENT_ID),
    CONSTRAINT FK_EMP_MANAGER FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID),
)

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

CREATE TABLE CUSTOMERS(
    CUSTOMER_ID NUMERIC(6) NOT NULL,
    COUNTRY_ID CHAR(2) NOT NULL,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(20) NOT NULL,
    ADDRESS TEXT,
    EMAIL VARCHAR(30),
    GENDER CHAR(1),
    CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMER_ID),
    CONSTRAINT FK_CUST_COUNTRIES FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRIES (COUNTRY_ID),
    CONSTRAINT CHECK_GENDER CHECK (GENDER IS NULL OR (GENDER IN ('M', 'F')))
)

CREATE TABLE PRODUCTS(
    PRODUCT_ID INT NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    PRICE NUMERIC(8,2) NOT NULL,
    DESCR VARCHAR(2000),
    CONSTRAINT PK_PRODUCTS PRIMARY KEY (PRODUCT_ID)
)

CREATE TABLE ORDERS(
    ORDER_ID INT NOT NULL,
    ORDER_DATE DATETIME NOT NULL,
    CUSTOMER_ID NUMERIC(6) NOT NULL,
    EMPLOYEE_ID INT NOT NULL,
    SHIP_ADDRESS VARCHAR(150),
    CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
    CONSTRAINT FK_ORDERS_CUST FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
    CONSTRAINT FK_ORDERS_EMPL FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID)
)