CREATE TABLE ACCOUNT (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    LOGIN INT(100) NOT NULL UNIQUE,
    PASSWORD INT(100) NOT NULL,
    EMPLOYEE_ID INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE ACTIVITY (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    AMOUNT INT(10) NOT NULL,
    TASK_ID INT(10) NOT NULL,
    EMPLOYEE_ID INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE EMPLOYEE (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    FIRST_NAME VARCHAR(50) NOT NULL,
    LAST_NAME VARCHAR(50) NOT NULL,
    PHONE_NUMBER CHAR(12) NOT NULL,
    ROLE VARCHAR(20) NOT NULL,
    MANAGER_ID INT(10),
    PRIMARY KEY (ID)
);

CREATE TABLE EMPLOYEE_PROJECT (
    EMPLOYEE_ID INT(10) NOT NULL,
    PROJECT_ID INT(10) NOT NULL,
    PRIMARY KEY (EMPLOYEE_ID , PROJECT_ID)
);

CREATE TABLE PROJECT (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    DESCRIPTION VARCHAR(1000),
    MANAGER_ID INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE TASK (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    DESCRIPTION VARCHAR(1000),
    UNIT VARCHAR(16) NOT NULL,
    PROJECT_ID INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE TASK_TEMPLATE (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    DESCRIPTION VARCHAR(1000),
    UNIT VARCHAR(16) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE WAREHOUSE_DELIVERY (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    ITEM_ID INT(10) NOT NULL,
    AMOUNT INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE WAREHOUSE_ITEM (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL UNIQUE,
    UNIT VARCHAR(16) NOT NULL,
    AMOUNT INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE WAREHOUSE_TAKING (
    ID INT(10) NOT NULL AUTO_INCREMENT,
    AMONUT INT(10) NOT NULL,
    EMPLOYEE_ID INT(10) NOT NULL,
    ITEM_ID INT(10) NOT NULL,
    PRIMARY KEY (ID)
);

ALTER TABLE ACTIVITY
    ADD INDEX IDX_ACTIVITY_EMPLOYEE_ID (EMPLOYEE_ID),
    ADD CONSTRAINT FK_ACTIVITY_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (ID),
    ADD INDEX IDX_ACTIVITY_TASK_ID (TASK_ID),
    ADD CONSTRAINT FK_ACTIVITY_TASK_ID FOREIGN KEY (TASK_ID) REFERENCES TASK (ID);

ALTER TABLE PROJECT
    ADD INDEX IDX_PROJECT_MANAGER_ID (MANAGER_ID),
    ADD CONSTRAINT FK_PROJECT_MANAGER_ID FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE (ID);

ALTER TABLE TASK
    ADD INDEX IDX_TASK_PROJECT_ID (PROJECT_ID),
    ADD CONSTRAINT FK_TASK_PROJECT_ID FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (ID);

ALTER TABLE WAREHOUSE_TAKING
    ADD INDEX IDX_WAREHOUSE_TAKING_EMPLOYEE_ID (EMPLOYEE_ID),
    ADD CONSTRAINT FK_WAREHOUSE_TAKING_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (ID);

ALTER TABLE EMPLOYEE_PROJECT
    ADD INDEX IDX_EMPLOYEE_PROJECT_EMPLOYEE_ID (EMPLOYEE_ID),
    ADD CONSTRAINT FK_EMPLOYEE_PROJECT_EMPLOYEE_ID  FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (ID),
    ADD INDEX IDX_EMPLOYEE_PROJECT_PROJECT_ID (PROJECT_ID),
    ADD CONSTRAINT FK_EMPLOYEE_PROJECT_PROJECT_ID FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (ID);

ALTER TABLE ACCOUNT
    ADD INDEX IDX_ACCOUNT_EMPLOYEE_ID (EMPLOYEE_ID),
    ADD CONSTRAINT FK_ACCOUNT_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (ID);

ALTER TABLE WAREHOUSE_DELIVERY
    ADD INDEX IDX_WAREHOUSE_DELIVERY_ITEM_ID (ITEM_ID),
    ADD CONSTRAINT FK_WAREHOUSE_DELIVERY_ITEM_ID FOREIGN KEY (ITEM_ID) REFERENCES WAREHOUSE_ITEM (ID);

ALTER TABLE WAREHOUSE_TAKING
    ADD INDEX IDX_WAREHOUSE_TAKING_ITEM_ID (ITEM_ID),
    ADD CONSTRAINT FK_WAREHOUSE_TAKING_ITEM_ID FOREIGN KEY (ITEM_ID) REFERENCES WAREHOUSE_ITEM (ID);

ALTER TABLE EMPLOYEE
    ADD INDEX IDX_EMPLOYEE_MANAGER_ID (MANAGER_ID),
    ADD CONSTRAINT FK_EMPLOYEE_MANAGER_ID FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE (ID);