-- Delete tables if they exist
DROP TABLE IF EXISTS product CASCADE;
DROP TABLE IF EXISTS image CASCADE;
DROP TABLE IF EXISTS colors CASCADE;
DROP TABLE IF EXISTS store CASCADE;
DROP TABLE IF EXISTS personal CASCADE;
DROP TABLE IF EXISTS permissions CASCADE;
DROP TABLE IF EXISTS boss CASCADE;
DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS client CASCADE;
DROP TABLE IF EXISTS delivery_address CASCADE;
DROP TABLE IF EXISTS order CASCADE;
DROP TABLE IF EXISTS report CASCADE;
DROP TABLE IF EXISTS monthly_profit CASCADE;
DROP TABLE IF EXISTS request CASCADE;
DROP TABLE IF EXISTS makes_request CASCADE;
DROP TABLE IF EXISTS answers CASCADE;
DROP TABLE IF EXISTS for_store CASCADE;
DROP TABLE IF EXISTS review CASCADE;
DROP TABLE IF EXISTS change CASCADE;
DROP TABLE IF EXISTS makes_change CASCADE;
DROP TABLE IF EXISTS made_od CASCADE;
DROP TABLE IF EXISTS works_in_store CASCADE;
DROP TABLE IF EXISTS worked CASCADE;
DROP TABLE IF EXISTS sells CASCADE;
DROP TABLE IF EXISTS includes CASCADE;
DROP TABLE IF EXISTS approves CASCADE;
DROP TABLE IF EXISTS exchange_data CASCADE;


-- Table 1
-- Create PRODUCT table
CREATE TABLE product (
    code SERIAL UNIQUE NOT NULL PRIMARY KEY,
    price NUMBER NOT NULL,
    availability INTEGER NOT NULL,
    weight NUMBER NOT NULL,
    width_X_length_X_depth VARCHAR(20) NOT NULL,
    aprox_production_time INTEGER NOT NULL,
    description VARCHAR NOT NULL
);


-- Table 2
-- Create IMAGE table
CREATE TABLE image(
    product_code SERIAL UNIQUE NOT NULL REFERENCES product(code)
        ON DELETE CASCADE,
    image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
);


-- Table 3
-- Create COLOR table
CREATE TABLE color (
    product_code SERIAL UNIQUE NOT NULL REFERENCES product(code) 
        ON DELETE CASCADE,
    color VARCHAR(10)
);


-- Table 4
-- Create STORE table
CREATE TABLE store (
    store_ID SERIAL UNIQUE NOT NULL PRIMARY KEY
        ON DELETE CASCADE,
    name VARCHAR(50) UNIQUE NOT NULL,
    date_of_founding DATE NOT NULL,
    physical_address VARCHAR(100) NOT NULL,
    store_email VARCHAR(20) UNIQUE NOT NULL,
    rating NUMBER NOT NULL DEFAULT '0'
);


-- Table 5
-- Create PERSONAL table
CREATE TABLE personal (
    ssn NUMBER UNIQUE NOT NULL PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    email VARCHAR(20) UNIQUE NOT NULL,
    password VARCAHR NOT NULL
);


-- Table 6
-- Create PERMISSIONS table
CREATE TABLE permissions (
    personal_SSN NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
        ON DELETE CASCADE,
    type VARCHAR(100) UNIQUE NOT NULL,
    authorisation VARCHAR (50) NOT NULL
);


-- Table 7
-- Create BOSS table
CREATE TABLE boss (
    boss_SSN NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
        ON DELETE CASCADE
);


-- Table 8
-- Create EMPLOYEES table
CREATE TABLE employees (
    employee_SSN NUMBER UNIQUE NOT NULL PRIMARY KEY REFERENCES personal(ssn)
        ON DELETE CASCADE,
    date_of_hire DATE NOT NULL
);


-- Table 9
-- Create CLIENT table
CREATE TABLE client (
    client_ID SERIAL UNIQUE NOT NULL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR NOT NULL
);


-- Table 10
-- Create DELIVERY_ADDRESS table
CREATE TABLE delivery_address (
    client_ID SERIAL UNIQUE NOT NULL PRIMARY KEY REFERENCES client(client_ID)
        ON DELETE CASCADE,
    address VARCHAR NOT NULL
);


-- Table 11
-- Create ORDER table
CREATE TABLE order (
    order_num SERIAL UNIQUE NOT NULL PRIMARY KEY,
    client_ID SERIAL UNIQUE NOT NULL REFERENCES client(client_ID),
        ON DELETE CASCADE,
    quantity INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'placed order',
    last_date_mod TIMESTAMP NOT NULL,
    payment_method VARHAR (250) NOT NULL,
    discount NUMBER DEFAULT 0.0,
);


-- Table 12
-- Create REPORT table
CREATE TABLE report (
    date TIMESTAMP NOT NULL,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    overall_profit NUMBER NOT NULL DEFAULT 0.0,
    sales_trend VARCHAR(max) NOT NULL,
    marketing_growth VARCHAR(max) NOT NULL,
    owner_signature VARCHAR(30) NOT NULL,
    PRIMARY KEY (date, store_ID)
);


-- Table 13
-- Create MONTHLY_PROFIT table
CREATE TABLE monthly_profit (
    report_date TIMESTAMP NOT NULL REFERENCES report(date)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID),
        ON DELETE CASCADE,
    month_and_year DATE NOT NULL,
    profit NUMBER NOT NULL DEFAULT 0.0,
    PRIMARY KEY(report_date, store_ID)
);


-- Table 14
-- Create REQUEST table
CREATE TABLE request (
    request_num SERIAL UNIQUE NOT NULL PRIMARY KEY,
    date_and_time TIMESTAMP NOT NULL,
    problem VARCHAR(1000) NOT NULL,
    notes_of_communication VARCHAR(300),
    costumer_satisfaction NUMBER NOT NULL 
);


-- Table 15
-- Create MAKES_REQUEST table
CREATE TABLE makes_request (
    client_ID SERIAL NOT NULL REFERENCES client(client_ID)
        ON DELETE CASCADE,
    order_num SERIAL UNIQUE NOT NULL REFERENCES order(order_num)
        ON DELETE CASCADE,
);


-- Table 16
-- Create ANSWERS table
CREATE TABLE answers (
    request_num SERIAL NOT NULL REFERENCES request(request_num)
        ON DELETE CASCADE,
    personal_SSN VARCHAR NOT NULL REFERENCES personal(ssn)
        ON DELETE CASCADE,    
);


-- Table 17
-- Create FOR_STORE table
CREATE TABLE for_store (
    request_num SERIAL NOT NULL REFERENCES request(request_num)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE
);


-- Table 18
-- Create REVIEW table
CREATE TABLE review (
    order_num SERIAL NOT NULL UNIQUE REFERENCES order(order_num)
        ON DELETE CASCADE,
    comment VARCHAR(300),
    rating NUMBER NOT NULL,
    last_mod_date TIMESTAMP NOT NULL
);


-- Table 19
-- Create CHANGE table
CREATE TABLE change (
    date_and_time TIMESTAMP NOT NULL,
    changes VARCHAR(max) NOT NULL
);


-- Table 20
-- Create MAKES_CHANGE table
CREATE TABLE makes_change (
    personal_SSN NUMBER NOT NULL REFERENCES personal(ssn)
        ON DELETE CASCADE,
    change_date_time TIMESTAMP NOT NULL,
    PRIMARY KEY(personal_SSN, change_date_time)
);


-- Table 21
-- Create MADE_ON table
CREATE TABLE made_on (
    product_code SERIAL NOT NULL REFERENCES product(code)
        ON DELETE CASCADE,
    change_date_time TIMESTAMP NOT NULL,
    PRIMARY KEY(product_SSN, change_date_time)
);


-- Table 22
-- Create WORKS_IN_STORE table
CREATE TABLE works_in_store (
    personal_SSN NUMBER NOT NULL REFERENCES personal(ssn)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    PRIMARY KEY(personak_SSN, store_ID)
);


-- Table 23
-- Create WORKED table
CREATE TABLE worked (
    personal_SSN NUMBER NOT NULL REFERENCES personal(ssn)
        ON DELETE CASCADE,
    report_date TIMESTAMP NOT NULL REFERENCES report(date)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    wage NUMBER NOT NULL,
    pay_method VARCHAR(20) DEFAULT 'hourly',
    total_hours NUMBER NOT NULL,
    week VARCHAR(24) NOT NULL
);


-- Table 24
-- Create SELLS table
CREATE TABLE sells (
    product_code SERIAL NOT NULL REFERENCES product(code)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    discount NUMBER NOT NULL DEFAULT 0.0
);


-- Table 25
-- Create INCLUDES table
CREATE TABLE includes (
    order_num SERIAL NOT NULL REFERENCES order(order_num)
        ON DELETE CASCADE,
    product_code SERIAL NOT NULL REFERENCES product(code)
        ON DELETE CASCADE,
);


-- Table 26
-- Create APPROVES table
CREATE TABLE approves (
    boss_SSN NUMBER NOT NULL REFERENCES boss(boss_SSN)
        ON DELETE CASCADE,
    report_date TIMESTAMP NOT NULL REFERENCES report(date)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    owner_signature VARCHAR(30) NOT NULL
);


-- Table 27
-- Create EXCHANGES_DATA table
CREATE TABLE exchanges_date (
    report_date TIMESTAMP NOT NULL references report(report_date)
        ON DELETE CASCADE,
    store_ID SERIAL NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    monthly_profit NUMBER NOT NULL DEFAULT 0.0,
    date TIMESTAMP NOT NULL,
    sales VARCHAR(max) NOT NULL,
    damages VARCHAR(max)
);