CREATE TABLE COMPANY_EMPLOYEE(
    SSN NUMBER(20) CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY,
    name NVARCHAR2(50) NOT NULL,
    surname NVARCHAR2(50) NOT NULL,
    email NVARCHAR2(50) NOT NULL,
    street NVARCHAR2(100) NOT NULL,
    city NVARCHAR2(50) NOT NULL,
    "stnumber" NVARCHAR2(10) NOT NULL
);
CREATE TABLE COMPANY_TELNUMS(
    TelephoneNumber NVARCHAR2(50) NOT NULL,
    EmployeeSSN NUMBER(20) NOT NULL,
    CONSTRAINT "COMPANY_TELNUMS_EMPLOYEE_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_EMPLOYEE(SSN),
    CONSTRAINT "COMPANY_TELNUMS_PK" PRIMARY KEY(TelephoneNumber, EmployeeSSN)
);
CREATE TABLE COMPANY_SELLER(
    EmployeeSSN NUMBER(20) CONSTRAINT "COMPANY_SELLER_PK" PRIMARY KEY,
    WorkExperience NUMBER NOT NULL,
    NumberWorkPlaces NUMBER NOT NULL,
    CONSTRAINT "COMPANY_SELLER_EMPLOYEE_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_EMPLOYEE(SSN)
);
CREATE TABLE COMPANY_AGENT(
    EmployeeSSN NUMBER(20) CONSTRAINT "COMPANY_AGENT_PK" PRIMARY KEY,
    CONSTRAINT "COMPANY_AGENT_EMPLOYEE_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_EMPLOYEE(SSN)
);
CREATE TABLE COMPANY_AGENTMANAGER(
    EmployeeSSN NUMBER(20) CONSTRAINT "COMPANY_AGENTMANAGER_PK" PRIMARY KEY,
    CONSTRAINT "COMPANY_AGENTMANAGER_AGENT_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_AGENT(EmployeeSSN)
);
CREATE TABLE COMPANY_AGENTSELLER(
    EmployeeSSN NUMBER(20) CONSTRAINT "COMPANY_AGENTSELLER_PK" PRIMARY KEY,
    CONSTRAINT "COMPANY_AGENTSELLER_AGENT_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_AGENT(EmployeeSSN)
);
ALTER TABLE COMPANY_SELLER ADD CONSTRAINT "COMPANY_SELLER_AGENTMANAGER_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_AGENTMANAGER(EmployeeSSN)
CREATE TABLE COMPANY_STORE(
    ID NUMBER CONSTRAINT "COMPANY_STORE_PK" PRIMARY KEY,
    name NVARCHAR2(50) NOT NULL,
    address NVARCHAR2(100) NOT NULL
);
ALTER TABLE COMPANY_SELLER ADD StoreID NUMBER
ALTER TABLE COMPANY_SELLER ADD CONSTRAINT "COMPANY_SELLER_STORE_FK" FOREIGN KEY(StoreID) REFERENCES COMPANY_STORE(ID)
CREATE TABLE COMPANY_AGENTSELLERLANGUAGES(
    Language NVARCHAR2(50) NOT NULL,
    EmployeeSSN NUMBER(20) NOT NULL,
    CONSTRAINT "COMPANY_AGENTSELLERLANGUAGES_PK" PRIMARY KEY(Language, EmployeeSSN),
    CONSTRAINT "COMPANY_AGENTSELLERLANGUAGES_AGENTSELLER_FK" FOREIGN KEY(EmployeeSSN) REFERENCES COMPANY_AGENTSELLER(EmployeeSSN)
);
CREATE TABLE COMPANY_CUSTOMER(
    SSN NUMBER(20) CONSTRAINT "CUSTOMER_PK" PRIMARY KEY,
    Name NVARCHAR2(60) NOT NULL,
    Surname NVARCHAR2(60) NOT NULL,
    DeliveryAddress NVARCHAR2(200) NOT NULL,
    TelephoneNumber NVARCHAR2(30) NOT NULL,
    Email NVARCHAR2(60) NOT NULL,
    AlternativeEmail NVARCHAR2(60) NOT NULL,
    PaymentType NVARCHAR2(60) NOT NULL,
    AgentSellerSSN NUMBER(20) NOT NULL,
    CONSTRAINT "COMPANY_CUSTOMER_AGENTSELLER_FK" FOREIGN KEY(AgentSellerSSN) REFERENCES COMPANY_AGENTSELLER(EmployeeSSN)
);
CREATE TABLE COMPANY_TALK(
    ID NUMBER CONSTRAINT "COMPANY_TALK_PK" PRIMARY KEY,
    Goal NVARCHAR2(200) NOT NULL,
    "Date" DATE NOT NULL,
    Duration NUMBER NOT NULL,
    AgentSellerSSN NUMBER(20) NOT NULL,
    CustomerSSN NUMBER(20) NOT NULL,
    CONSTRAINT "COMPANY_TALK_AGENTSELLER_FK" FOREIGN KEY(AgentSellerSSN) REFERENCES COMPANY_AGENTSELLER(EmployeeSSN),
    CONSTRAINT "COMPANY_TALK_CUSTOMER_FK" FOREIGN KEY(CustomerSSN) REFERENCES COMPANY_CUSTOMER(SSN)
);
CREATE TABLE COMPANY_CATEGORY(
    Name NVARCHAR2(50) NOT NULL,
    ID NUMBER CONSTRAINT "COMPANY_CATEGORY_PK" PRIMARY KEY,
    SupercategoryID NUMBER,
    CONSTRAINT "COMPANY_CATEGORY_SUPERCATEGORY_FK" FOREIGN KEY(SupercategoryID) REFERENCES COMPANY_CATEGORY(ID)
);
CREATE TABLE COMPANY_PRODUCT(
    Code NVARCHAR2(50) CONSTRAINT "COMPANY_PRODUCT_PK" PRIMARY KEY,
    CountryOfProduction NVARCHAR2(50) NOT NULL,
    Price NUMBER NOT NULL,
    CategoryID NUMBER NOT NULL,
    CONSTRAINT "COMPANY_PRODUCT_CATEGORY_FK" FOREIGN KEY(CategoryID) REFERENCES COMPANY_CATEGORY(ID)
);
CREATE TABLE COMPANY_STOCK(
    "Count" NUMBER NOT NULL,
    StoreID NUMBER NOT NULL,
    ProductCode NVARCHAR2(50) NOT NULL,
    CONSTRAINT "COMPANY_STOCK_PK" PRIMARY KEY(ProductCode, StoreID),
    CONSTRAINT "COMPANY_STOCK_STORE_FK" FOREIGN KEY(StoreID) REFERENCES COMPANY_STORE(ID),
    CONSTRAINT "COMPANY_STOCK_PRODUCT_FK" FOREIGN KEY(ProductCode) REFERENCES COMPANY_PRODUCT(Code)
);
CREATE TABLE COMPANY_CP_REVIEW(
    Grade NUMBER NOT NULL,
    "Comment" NVARCHAR2(200) NOT NULL,
    CustomerSSN NUMBER(20) NOT NULL,
    ProductCode NVARCHAR2(50) NOT NULL,
    CONSTRAINT "COMPANY_CP_REVIEW_PK" PRIMARY KEY(ProductCode, CustomerSSN),
    CONSTRAINT "COMPANY_CP_REVIEW_CUSTOMER_FK" FOREIGN KEY(CustomerSSN) REFERENCES COMPANY_CUSTOMER(SSN),
    CONSTRAINT "COMPANY_CP_REVIEW_PRODUCT_FK" FOREIGN KEY(ProductCode) REFERENCES COMPANY_PRODUCT(Code)
);
CREATE TABLE COMPANY_CS_REVIEW(
    Grade NUMBER NOT NULL,
    "Comment" NVARCHAR2(200) NOT NULL,
    CustomerSSN NUMBER(20) NOT NULL,
    StoreID NUMBER NOT NULL,
    CONSTRAINT "COMPANY_CS_REVIEW_PK" PRIMARY KEY(StoreID, CustomerSSN),
    CONSTRAINT "COMPANY_CS_REVIEW_CUSTOMER_FK" FOREIGN KEY(CustomerSSN) REFERENCES COMPANY_CUSTOMER(SSN),
    CONSTRAINT "COMPANY_CS_REVIEW_STORE_FK" FOREIGN KEY(StoreID) REFERENCES COMPANY_STORE(ID)
);
CREATE TABLE COMPANY_PURCHASE(
    Quantity NUMBER NOT NULL,
    SellerSSN NUMBER(20) NOT NULL,
    CustomerSSN NUMBER(20) NOT NULL,
    ProductCode NVARCHAR2(50) NOT NULL,
    CONSTRAINT "COMPANY_PURCHASE_PK" PRIMARY KEY(ProductCode, CustomerSSN, SellerSSN),
    CONSTRAINT "COMPANY_PURCHASE_SELLER_FK" FOREIGN KEY(SellerSSN) REFERENCES COMPANY_SELLER(EmployeeSSN),
    CONSTRAINT "COMPANY_PURCHASE_CUSTOMER_FK" FOREIGN KEY(CustomerSSN) REFERENCES COMPANY_CUSTOMER(SSN),
    CONSTRAINT "COMPANY_PURCHASE_PRODUCT_FK" FOREIGN KEY(ProductCode) REFERENCES COMPANY_PRODUCT(Code)
);




ALTER TABLE COMPANY_PRODUCT ADD Name NVARCHAR2(100) UNIQUE NOT NULL

ALTER TABLE COMPANY_CP_REVIEW ADD CONSTRAINT "Product_Grade_Validation" CHECK(Grade IN(1, 2, 3))
ALTER TABLE COMPANY_CS_REVIEW ADD CONSTRAINT "Store_Grade_Validation" CHECK(Grade IN(1, 2, 3, 4 ,5))