/**
 * Stocks table
 * The stocks table stores the basic information about each stock.
 * This data is scraped periodically from the IBD website.
 */
CREATE TABLE IF NOT EXISTS stocks
(
    symbol  VARCHAR(16) PRIMARY KEY, -- The stock symbol/ticker
    name    TEXT NOT NULL,           -- The full name of the stock
    ibd_url TEXT                     -- The URL to the IBD page for the stock
);

/**
 * Stock Rank table
 * The stock rank table stores the rank information for each stock.
 * This data is scraped periodically from the IBD website.
 */
CREATE TYPE RANK_TYPE AS ENUM ('ibd50', 'cap20');
CREATE TABLE IF NOT EXISTS stock_rank
(
    symbol     VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
    rank_type  RANK_TYPE   NOT NULL,                            -- The type of rank (IBD 50 or CAP 20)
    rank       SMALLINT,                                        -- The rank of the stock
    created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP   -- The datetime the rank was scraped/created
);

/**
 * Chart Analysis table
 * The chart analysis table stores the chart analysis for each stock.
 * This data is scraped periodically from the IBD website then analyzed by OpenAI's GPT-4o.
 */
CREATE TYPE CHART_ACTION AS ENUM ('buy', 'sell', 'hold', 'unknown');
CREATE TABLE IF NOT EXISTS chart_analysis
(
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Randomly generated UUID for the chart analysis
    raw_analysis TEXT    NOT NULL,                           -- The raw chart analysis text (e.g. "Flat base, 3 weeks tight")

    processed    BOOLEAN NOT NULL DEFAULT FALSE,             -- Whether the chart analysis has been processed by GPT-4o
    action       CHART_ACTION,                               -- The action to take based on the chart analysis
    price        MONEY,                                      -- The price to take the action at
    reason       TEXT,                                       -- The reason for the action
    confidence   SMALLINT                                    -- The confidence level of the action (0-100)
);

/**
 * Ratings table
 * The ratings table stores the ratings for each stock.
 * This data is scraped periodically from the IBD website.
 */
CREATE TABLE IF NOT EXISTS ratings
(
    id             UUID PRIMARY KEY     DEFAULT gen_random_uuid(),  -- Randomly generated UUID for the rating
    symbol         VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table

    composite      SMALLINT,                                        -- The composite rating (0-99)
    eps            SMALLINT,                                        -- The EPS rating (0-99)
    rel_str        SMALLINT,                                        -- The relative strength rating (0-99)
    group_rel_str  CHAR(2),                                         -- The group relative strength rating (E-A+) E=0, A+=13
    smr            CHAR(2),                                         -- The SMR rating (E-A+)
    acc_dis        CHAR(2),                                         -- The acc/dis rating (E-A+)
    chart_analysis UUID REFERENCES chart_analysis (id),             -- The ID of the chart analysis for the stock
    price          MONEY,                                           -- The price of the stock at the time of the rating

    created_at     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP   -- The datetime the rating was scraped/created
);

CREATE INDEX IF NOT EXISTS ratings_symbol_idx ON ratings (symbol);

/**
 * Stock Data table
 * The stock data table stores the historical price data for each stock.
 * This data should be retrieved from a brokerage API or other data source.
 * These should be in the format of minute and daily bars.
 */
CREATE TYPE BAR_INTERVAL AS ENUM ('1m', '1d');
CREATE TABLE IF NOT EXISTS stock_data
(
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),       -- Randomly generated UUID for the stock data
    symbol       VARCHAR(16)  NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table

    bar_interval BAR_INTERVAL NOT NULL,                            -- The interval of the bar data (1m, 1d)
    time         TIMESTAMP    NOT NULL,                            -- The timestamp of the bar

    open         MONEY        NOT NULL,                            -- The opening price of the bar
    high         MONEY        NOT NULL,                            -- The high price of the bar
    low          MONEY        NOT NULL,                            -- The low price of the bar
    close        MONEY        NOT NULL,                            -- The closing price of the bar
    volume       BIGINT       NOT NULL                             -- The volume of the bar
);