-- Sector

-- Primary Key : id_sector

CREATE TABLE sectors(
    id_sector SERIAL PRIMARY KEY,
    sector_name VARCHAR(100) NOT NULL
);

-- Symbols

-- Primary Key : id_symbol

-- Foreign Key : id_sector -> sectors

CREATE TABLE symbols(
    id_symbol SERIAL PRIMARY KEY,
    symbol VARCHAR(50) NOT NULL,
    symbol_name VARCHAR(255) NOT NULL,
    id_sector INTEGER NOT NULL,
    marketcap NUMERIC(40,0),
    employees NUMERIC(20,0),
    CONSTRAINT fk_id_sector FOREIGN KEY (id_sector) REFERENCES sectors (id_sector) 
);

-- prices_latest

-- Primary Key : id_symbol, trade_date

-- Foreign Key : id_symbol -> symbols

CREATE TABLE prices_latest (
    id SERIAL,
    id_symbol INTEGER NOT NULL,
    trade_date DATE NOT NULL,
    open_price NUMERIC NOT NULL,
    high_price NUMERIC NOT NULL,
    low_price NUMERIC NOT NULL,
    close_price NUMERIC NOT NULL,
    volume NUMERIC NOT NULL,
    chg_1d NUMERIC,
    return_1d NUMERIC,	
    PRIMARY KEY (id_symbol),
    CONSTRAINT fk_id_symbol FOREIGN KEY (id_symbol) REFERENCES symbols (id_symbol)
);

-- prices_historical

-- Primary Key : id_symbol, trade_date

-- Foreign Key : id_symbol -> symbols

CREATE TABLE prices_historical (
    id SERIAL,
    id_symbol INTEGER NOT NULL,
    trade_date DATE NOT NULL,
    open_price NUMERIC NOT NULL,
    high_price NUMERIC NOT NULL,
    low_price NUMERIC NOT NULL,
    close_price NUMERIC NOT NULL,
    volume NUMERIC NOT NULL,
    chg_1d NUMERIC,
    return_1d NUMERIC,	
    PRIMARY KEY (id_symbol,trade_date),
    CONSTRAINT fk_id_symbol FOREIGN KEY (id_symbol) REFERENCES symbols (id_symbol)
);