/*
    createdb.sql
    Tables created:
        Business
        Business_Hours
        Business_Neighborhoods
        Business_Attributes
        Business_Category
        User
        Review
*/


-- Business Table
-- Separate tables for category, hours, neighborhoods, attributes
CREATE TABLE Business(
                        business_id VARCHAR2(100) PRIMARY KEY,
                        -- encrypted business id
                        full_address VARCHAR2(200) NOT NULL,
                        -- localized address
                        open VARCHAR2(5),
                        -- true/false if the business is open/closed
                        city VARCHAR2(50) NOT NULL,
                        state VARCHAR2 (50) NOT NULL,
                        latitude NUMBER NOT NULL,
                        longitude NUMBER NOT NULL,
                        review_count NUMBER,
                        business_name VARCHAR2(100),
                        stars NUMBER,
                        -- star rating, rounded to half-stars
                        type VARCHAR2(30)
                        -- 'business'
                        );

-- Business Hours Table
CREATE TABLE Business_Hours(
                            business_id VARCHAR2(100) PRIMARY KEY,
                            business_day VARCHAR2(20),
                            -- the days of the week
                            open_time TIMESTAMP,
                            close_time TIMESTAMP,
                            FOREIGN KEY business_id REFERENCES Business(business_id) ON DELETE CASCADE
                            );

-- Business Neighborhoods Table
CREATE TABLE Business_Neighborhoods(
                                    business_id VARCHAR2(100) PRIMARY KEY,
                                    neighborhoods VARCHAR2(100),
                                    -- hood names
                                    FOREIGN KEY business_id REFERENCES Business(business_id) ON DELETE CASCADE
                                    );

-- Business Attributes Table
CREATE TABLE Business_Attributes(
                                    business_id VARCHAR2(100) PRIMARY KEY,
                                    attributes VARCHAR2(100),
                                    -- business properties
                                    FOREIGN KEY business_id REFERENCES Business(business_id) ON DELETE CASCADE
                                );

-- Business Category Table
CREATE TABLE Business_Category(
                                business_id VARCHAR2(100),
                                main_category VARCHAR2(100) NOT NULL,
                                sub_category VARCHAR2(100),
                                FOREIGN KEY business_id REFERENCES Business(business_id) ON DELETE CASCADE
);


-- User Table
CREATE TABLE User(
                    yelping_since DATE NOT NULL,
                    useful_votes NUMBER,
                    funny_votes NUMBER,
                    cool_votes NUMBER,
                    review_count NUMBER,
                    user_name VARCHAR2(100) NOT NULL,
                    user_id VARCHAR2(100) PRIMARY KEY,
                    --friends -- "friends": ["abcdeedgs", "dsaggagrtrtgr", ...]
                    fans NUMBER,
                    average_stars NUMBER,
                    type VARCHAR2(30),
                    --compliments -- "compliments": {"note": 1, "photo": 2, ...}
                    --elite -- "elite": [2012, 2013, 2014]
                    );


-- Review Table
CREATE TABLE Review(
                    useful_votes NUMBER,
                    funny_votes NUMBER,
                    cool_votes NUMBER,
                    user_id VARCHAR2(100) NOT NULL,
                    review_id VARCHAR2(100) PRIMARY KEY,
                    stars NUMBER,
                    review_date DATE NOT NULL,
                    review_text VARCHAR2(3000),
                    type VARCHAR2(30)
                    business_id VARCHAR2(100),
                    FOREIGN KEY user_id REFERENCES User(user_id) ON DELETE CASCADE,
                    FOREIGN KEY business_id REFERENCES Business(business_id) ON DELETE CASCADE
                    );