-- Database: stockmarket

-- DROP DATABASE stockmarket;

CREATE DATABASE stockmarket
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
    
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
    
-- Table: tstock

-- DROP TABLE tstock;

CREATE TABLE tstock
(
    symbol character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT tstock_pkey PRIMARY KEY (symbol)
);

-- Table: tdaily

-- DROP TABLE tdaily;

CREATE TABLE tdaily
(
    symbol character varying(50) COLLATE pg_catalog."default" NOT NULL,
    date timestamp without time zone NOT NULL,
    close numeric(20,2),
    closeadj numeric(20,2),
    high numeric(20,2),
    low numeric(20,2),
    open numeric(20,2),
    volume numeric(20,2),
    CONSTRAINT tdaily_pkey PRIMARY KEY (symbol, date),
    CONSTRAINT fk_tdaily_symbol FOREIGN KEY (symbol)
        REFERENCES public.tstock (symbol) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

SELECT create_hypertable('tdaily', 'date', 'symbol');

-- Index: tdaily_date_idx

-- DROP INDEX public.tdaily_date_idx;

CREATE INDEX IF NOT EXISTS tdaily_date_idx
    ON tdaily USING btree
    (date DESC);

-- Table: tintraday

-- DROP TABLE tintraday;

CREATE TABLE tintraday
(
    tradenumber bigint NOT NULL,
    symbol character varying(50) COLLATE pg_catalog."default" NOT NULL,
    date timestamp without time zone NOT NULL,
    brokerbuy integer,
    brokersell integer,
    price numeric(20,2),
    tradeindicator character(1) COLLATE pg_catalog."default",
    volume bigint,
    CONSTRAINT tintraday_pkey PRIMARY KEY (tradenumber, symbol, date),
    CONSTRAINT fk_tintraday_symbol FOREIGN KEY (symbol)
        REFERENCES public.tstock (symbol) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

SELECT create_hypertable('tintraday', 'date', 'symbol');

-- Index: tintraday_date_idx

-- DROP INDEX tintraday_date_idx;

CREATE INDEX IF NOT EXISTS tintraday_date_idx
    ON tintraday USING btree
    (date DESC);

-- Index: tintraday_symbol_idx

-- DROP INDEX tintraday_symbol_idx;

CREATE INDEX IF NOT EXISTS tintraday_symbol_idx
    ON tintraday USING btree
    (symbol COLLATE pg_catalog."default");