-- Delete tables if they exist (in reverse dependency order)
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS store;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS image;
DROP TABLE IF EXISTS color;
DROP TABLE IF EXISTS personal;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS boss;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS client;
DROP TABLE IF EXISTS delivery_address;
DROP TABLE IF EXISTS "order";
DROP TABLE IF EXISTS report;
DROP TABLE IF EXISTS monthly_profit;
DROP TABLE IF EXISTS request;
DROP TABLE IF EXISTS makes_request;
DROP TABLE IF EXISTS answers;
DROP TABLE IF EXISTS for_store;
DROP TABLE IF EXISTS review;
DROP TABLE IF EXISTS "change";
DROP TABLE IF EXISTS makes_change;
--  DROP TABLE IF EXISTS made_on CASCADE;
DROP TABLE IF EXISTS works_in_store;
DROP TABLE IF EXISTS worked;
DROP TABLE IF EXISTS sells;
DROP TABLE IF EXISTS includes;
DROP TABLE IF EXISTS approves;
DROP TABLE IF EXISTS exchanges_data;
DROP TABLE IF EXISTS refund;

-- Table 0
-- Create CATEGORY table
CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    parent_category_id INTEGER REFERENCES category(id)
);

-- Table 4
-- Create STORE table
CREATE TABLE store (
    store_ID VARCHAR(3) PRIMARY KEY,             -- In the format of 3 digits. Example : 001, 101, 567, 996,...
    name VARCHAR(50) UNIQUE NOT NULL,
    date_of_founding DATE NOT NULL,
    physical_address VARCHAR(100) NOT NULL,   -- ADDRESS FORMAT : st.(st-name) num.(unit-num), (city) (post-code), (country)?????
    store_email VARCHAR(40) UNIQUE NOT NULL,
    rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0) 
);


-- Table 1
-- Create PRODUCT table
CREATE TABLE product (
    code VARCHAR(8) PRIMARY KEY,                      -- In format 3 digits for store, 5 digits for product (Total 8 digits). Ex. 00100155, 01500020,...
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
    availability INTEGER NOT NULL,
    weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),    -- Shown in kg
    width_x_length_x_depth VARCHAR(20) NOT NULL, 
    aprox_production_time INTEGER NOT NULL,
    description VARCHAR(500) NOT NULL,
    cathegory_id INTEGER NOT NULL REFERENCES category(id)
        ON DELETE SET DEFAULT,
    store_id VARCHAR(3) REFERENCES store(store_ID)
);

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

-- Table 3
-- Create COLOR table
CREATE TABLE color (
    product_code VARCHAR(8) REFERENCES product(code) 
        ON DELETE CASCADE,
    color VARCHAR(50),
    PRIMARY KEY(product_code, color)
);


-- Table 5
-- Create PERSONAL table
CREATE TABLE personal (
    id CHAR(7) PRIMARY KEY,                                     -- Format 3 digits for store, 4 digits for employee ID
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    ssn CHAR(13) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR NOT NULL
);

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

-- Table 7
-- Create BOSS table
CREATE TABLE boss (
    boss_ID CHAR(7) PRIMARY KEY REFERENCES personal(id)
        ON DELETE CASCADE
);

-- Table 8
-- Create EMPLOYEES table
CREATE TABLE employees (
    employee_ID CHAR(7) PRIMARY KEY REFERENCES personal(id)
        ON DELETE CASCADE,
    date_of_hire DATE NOT NULL
);

-- Table 9
-- Create CLIENT table
CREATE TABLE client (
    client_ID SERIAL PRIMARY KEY,                     -- Format, just an order of an integer, starting from 1000. EX : 1005, 1023,  1200, ...
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR NOT NULL
);

-- Table 10
-- Create DELIVERY_ADDRESS table
CREATE TABLE delivery_address (
    client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID)
        ON DELETE CASCADE,
    address VARCHAR(200) NOT NULL, -- ADDRESS FORMAT : st.(st-name) num.(unit-num)
    city VARCHAR(30) NOT NULL,
    postcode VARCHAR(20) NOT NULL,
    country VARCHAR(40) NOT NULL,
    is_default BOOLEAN DEFAULT True
);

-- Table 11
-- Create ORDER table
CREATE TABLE "order" (
    order_num VARCHAR(11) PRIMARY KEY,                   -- In the format: 3 digits for store ID, 3 digits for year when order is placed, 5 digits for the number of order for that store in that year
    client_ID INTEGER 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 VARCHAR (250) NOT NULL,                  -- Format : "credit card, **** **** **** 6750" , or "cash", or "paypal, user@gmail.com",... Contains important information that are going to be needed when issuing a refund 
    discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<= 100.00)
    --CONSTRAINT check_status (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
);


-- Table 12
-- Create REPORT table
CREATE TABLE report (
    date TIMESTAMP NOT NULL,
    store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID)
        ON DELETE CASCADE,
    overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0),
    sales_trend VARCHAR NOT NULL,                                                    -- graph 
    marketing_growth VARCHAR NOT NULL,                                               -- graph
    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,
    store_ID VARCHAR(3) NOT NULL,
    month_and_year DATE NOT NULL,
    profit NUMERIC NOT NULL DEFAULT 0.0,
    PRIMARY KEY(report_date, store_ID),
    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) 
        ON DELETE CASCADE
);

-- Table 14
-- Create REQUEST table
CREATE TABLE request (
    request_num VARCHAR(14) PRIMARY KEY,  -- Format: 3 digits for store ID, 2 digits for month in which the request is placed, 3 digits for year, 4 digits for client, 2 digits for request number 
    date_and_time TIMESTAMP NOT NULL,
    problem VARCHAR(300) NOT NULL,
    notes_of_communication VARCHAR,
    customer_satisfaction NUMERIC NOT NULL  -- Fixed from "costumer" to "customer"
);

-- Table 15
-- Create MAKES_REQUEST table
CREATE TABLE makes_request (
    client_ID INTEGER NOT NULL REFERENCES client(client_ID)
        ON DELETE CASCADE,
    request_num VARCHAR(11) UNIQUE NOT NULL REFERENCES request(request_num)
        ON DELETE CASCADE,
    PRIMARY KEY(client_ID, order_num)
);

-- Table 16
-- Create ANSWERS table
CREATE TABLE answers (
    request_num VARCHAR(14) REFERENCES request(request_num)
        ON DELETE CASCADE,
    personal_ID CHAR(7) NOT NULL REFERENCES personal(id)
        ON DELETE CASCADE,
    PRIMARY KEY(request_num, personal_ID)
);

-- Table 17
-- Create FOR_STORE table
CREATE TABLE for_store (
    request_num VARCHAR(14) REFERENCES request(request_num)
        ON DELETE CASCADE,
    store_ID VARCHAR(3) REFERENCES store(store_ID)
        ON DELETE CASCADE,
    PRIMARY KEY(request_num, store_ID)
);

-- Table 18
-- Create REVIEW table
CREATE TABLE review (
    order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num)
        ON DELETE CASCADE,               -- Should the review be deleted when the order is deleted?
    comment VARCHAR(300),
    rating NUMERIC NOT NULL,
    last_mod_date TIMESTAMP NOT NULL
);

-- Table 19
-- Create CHANGE table
CREATE TABLE "change" (
    date_and_time TIMESTAMP NOT NULL,
    product_code VARCHAR(8) REFERENCES product(code)
        ON DELETE CASCADE,
    changes VARCHAR NOT NULL,
    PRIMARY KEY (date_and_time, product_code)
);

-- Table 20
-- Create MAKES_CHANGE table
CREATE TABLE makes_change (
    personal_ID CHAR(7) REFERENCES personal(id)
        ON DELETE CASCADE,
    change_date_time TIMESTAMP,
    product_code VARCHAR(8),
    PRIMARY KEY(personal_ID, change_date_time, product_code),
    FOREIGN KEY (change_date_time, product_code) REFERENCES "change"(date_and_time, product_code)
        ON DELETE CASCADE
);

-- Table 22
-- Create WORKS_IN_STORE table
CREATE TABLE works_in_store (
    personal_ID VARCHAR(5) REFERENCES personal(id)
        ON DELETE CASCADE,
    store_ID VARCHAR(3) REFERENCES store(store_ID)
        ON DELETE CASCADE,
    PRIMARY KEY(personal_ID, store_ID)
);

-- Table 23
-- Create WORKED table
CREATE TABLE worked (
    personal_ID CHAR(7) REFERENCES personal(id)
        ON DELETE CASCADE,                   
    report_date TIMESTAMP,
    store_ID VARCHAR(3),     
    wage NUMERIC NOT NULL CHECK (wage>=62),
    pay_method VARCHAR DEFAULT 'hourly',
    total_hours NUMERIC NOT NULL,
    week VARCHAR(23) NOT NULL,                       -- Format : 15.12.2025 - 21.12.2025
    PRIMARY KEY (personal_ID, report_date, store_ID),
    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
        ON DELETE CASCADE
);

-- Table 24
-- Create SELLS table
CREATE TABLE sells (
    product_code VARCHAR(8) REFERENCES product(code)
        ON DELETE CASCADE,
    store_ID VARCHAR(3) REFERENCES store(store_ID)
        ON DELETE CASCADE,
    discount NUMERIC NOT NULL DEFAULT 0.0,
    PRIMARY KEY (product_code, store_ID)
);

-- Table 25
-- Create INCLUDES table
CREATE TABLE includes (
    order_num VARCHAR(11) REFERENCES "order"(order_num)
        ON DELETE CASCADE,
    product_code VARCHAR(8) REFERENCES product(code)
        ON DELETE CASCADE,
    PRIMARY KEY (order_num, product_code)
);

-- Table 26
-- Create APPROVES table
CREATE TABLE approves (
    boss_ID CHAR(7) REFERENCES boss(boss_ID)
        ON DELETE CASCADE,
    report_date TIMESTAMP,
    store_ID VARCHAR(3),
    owner_signature VARCHAR NOT NULL, 
    PRIMARY KEY (boss_ID, report_date, store_ID),
    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID)
        ON DELETE CASCADE
);

-- Table 27
-- Create EXCHANGES_DATA table
CREATE TABLE exchanges_data (
    report_date TIMESTAMP NOT NULL,
    store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID) 
        ON DELETE CASCADE,
    monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
    date TIMESTAMP NOT NULL,
    sales NUMERIC NOT NULL,                                          -- Total profit achieved in sales (not "sells")                   
    damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages>=0),         -- Total loss in damages/refunds
    FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) 
        ON DELETE CASCADE,
    PRIMARY KEY (report_date, store_ID)
);

-- Table 28
-- Create REFUND table
CREATE TABLE refund (
    refund_id INTEGER PRIMARY KEY,
    order_num VARCHAR(11) REFERENCES "order"(order_num)
        ON DELETE CASCADE,
    amount DECIMAL(10,2) NOT NULL CHECK(amount > 0.0),
    reason VARCHAR(200),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'processed'))
);