CREATE TABLE symbols
(
  symbol String,
  name String,
  sector LowCardinality(String),
  subindustry LowCardinality(String),
  headquarters_location String,
  date_added Date,
  cik UInt32,
  year_founded String
)
ENGINE MergeTree()
ORDER BY (symbol);

CREATE TABLE option_contract_existences
(
  asOfDate Date,
  symbol LowCardinality(String),
  expirationDate Date,
  strike Float32,
  type ENUM('call', 'put')
)
ENGINE ReplacingMergeTree()
PRIMARY KEY (asOfDate, symbol)
ORDER BY (asOfDate, symbol, expirationDate, strike, type);
CREATE MATERIALIZED VIEW option_contract_existences_mv TO option_contract_existences
AS
SELECT 
  toDate(tsStart) as asOfDate,
  symbol,
  expirationDate,
  strike,
  type
FROM option_contract_aggregates
GROUP BY 
  asOfDate,
  symbol,
  expirationDate,
  strike,
  type;

CREATE TABLE option_contracts
(
  symbol LowCardinality(String),
  expirationDate Date,
  strike Float32,
  type ENUM('call', 'put')
)
ENGINE ReplacingMergeTree()
PRIMARY KEY (symbol, expirationDate)
ORDER BY (symbol, expirationDate, strike, type);

CREATE MATERIALIZED VIEW option_contracts_mv
TO option_contracts
AS
SELECT
  DISTINCT ON (
    symbol,
    expirationDate,
    strike,
    type
  )
  symbol,
  expirationDate,
  strike,
  type
FROM option_contract_existences;


-- BEGIN: Option Contract Quotes

-- END: Option Contract Quotes

CREATE TABLE stock_aggregates
(
  symbol LowCardinality(String), 
  tsStart DateTime32,
  open Float64, 
  close Float64, 
  low Float64, 
  high Float64, 
  volume UInt64, 
  volume_weighted_price Float64
)
ENGINE ReplacingMergeTree()
ORDER BY (symbol, tsStart)

CREATE TABLE option_contract_aggregates
(
  symbol LowCardinality(String), 
  expirationDate Date,
  strike Float32,
  type Enum('call', 'put'),

  tsStart DateTime32 CODEC(DoubleDelta(1), ZSTD),
  open Float32 CODEC(Delta(2), ZSTD), 
  close Float32 CODEC(Delta(2), ZSTD), 
  low Float32 CODEC(Delta(2), ZSTD), 
  high Float32 CODEC(Delta(2), ZSTD), 
  volume UInt32 CODEC(T64),
  volumeWeightedPrice Float32 CODEC(Delta(2), ZSTD)
)
ENGINE ReplacingMergeTree()
ORDER BY (symbol, expirationDate, strike, type, tsStart)

-- For stats about the character of this stock's options given a certain distance-from-the-money and time-to-expiration:
CREATE TABLE calendar_stats_by_symbol
(
  symbol LowCardinality(String),

  calendarSpanInDays UInt16,

  tsStart DateTime32 CODEC(Delta, ZSTD), -- included so as to assess the character of the stock's options within a given range of time; for example, if the stock got really hot for a few months.
  minutesToExpiration UInt32,

  frontMonthOpen Float32, 
  backMonthOpen Float32, 
  strikePercentageFromUnderlyingOpen Float64,
  frontMonthClose Float32, 
  backMonthClose Float32, 
  strikePercentageFromUnderlyingClose Float64
)
ENGINE MergeTree()
PRIMARY KEY (symbol, calendarSpanInDays, tsStart)
ORDER BY (symbol, calendarSpanInDays, tsStart, minutesToExpiration);
-- Populate `calendar_stats_by_symbol` by:
-- INSERT INTO calendar_stats_by_symbol
SELECT
  frontMonth.symbol,
  dateDiff('day', frontMonth.expirationDate, backMonth.expirationDate) as calendarSpanInDays,
  frontMonth.tsStart,
  dateDiff('minute', frontMonth.tsStart, addMinutes(toDateTime(expirationDate, 'America/New_York'), 60 * 16)) as minutesToExpiration,
  frontMonth.open as frontMonthOpen,
  backMonth.open as backMonthOpen,
  (frontMonth.strike-stock_aggregates.open)/stock_aggregates.open*100.0 as strikePercentageFromUnderlyingOpen,
  frontMonth.close as frontMonthClose,
  backMonth.close as backMonthClose,
  (frontMonth.strike-stock_aggregates.close)/stock_aggregates.close*100.0 as strikePercentageFromUnderlyingClose
FROM option_contract_aggregates as frontMonth
INNER JOIN stock_aggregates
ON option_contract_aggregates.symbol = stock_aggregates.symbol
AND option_contract_aggregates.tsStart = stock_aggregates.tsStart
INNER JOIN option_contract_aggregates as backMonth
ON frontMonth.symbol = backMonth.symbol
AND frontMonth.strike = backMonth.strike
AND frontMonth.type = backMonth.type
AND frontMonth.tsStart = backMonth.tsStart
WHERE backMonth.expirationDate > frontMonth.expirationDate
AND frontMonth.symbol = 'AAPL'
AND calendarSpanInDays = 14

CREATE TABLE option_histories
(
  symbol LowCardinality(String), 
  expirationDate Date,
  strike Float64,

  tsStart DateTime32,
  open Float64,
  daysToFront UInt16,
  underlyingPrice Float64,
  strikePercentageFromUnderlyingPrice Float64
)
ENGINE MergeTree()
ORDER BY (symbol, daysToFront, strikePercentageFromUnderlyingPrice)


CREATE TABLE calendar_histories
(
  symbol LowCardinality(String), 
  tsStart DateTime32,
  frontExpirationDate Date,
  backExpirationDate Date,
  daysToFrontExpiration UInt16,
  daysBetweenFrontAndBackExpiration UInt16,
  strike Float64,
  underlyingPrice Float64,
  strikePercentageFromUnderlyingPrice Float64,
  calendarPrice Float64
)
ENGINE MergeTree()
PRIMARY KEY (symbol, daysToFrontExpiration, daysBetweenFrontAndBackExpiration, strikePercentageFromUnderlyingPrice)
ORDER BY (symbol, daysToFrontExpiration, daysBetweenFrontAndBackExpiration, strikePercentageFromUnderlyingPrice, tsStart)


-- INSERT INTO calendar_histories
-- SELECT
--   front_option.symbol as symbol,
--   front_option.tsStart as tsStart,
--   front_option.expirationDate as frontExpirationDate,
--   back_option.expirationDate as backExpirationDate,
--   front_option.daysToFront as daysToFrontExpiration,
--   backExpirationDate - frontExpirationDate as daysBetweenFrontAndBackExpiration,
--   front_option.strike as strike,
--   front_option.underlyingPrice as underlyingPrice,
--   front_option.strikePercentageFromUnderlyingPrice,
--   back_option.open - front_option.open as calendarPrice
-- FROM (
--   SELECT
--     symbol,
--     tsStart,
--     expirationDate,
--     strike,
--     open,
--     daysToFront,
--     underlyingPrice,
--     strikePercentageFromUnderlyingPrice
--   FROM option_histories
-- ) AS front_option
-- LEFT JOIN option_aggregates as back_option
-- ON front_option.symbol = back_option.symbol
-- AND front_option.strike = back_option.strike
-- AND front_option.tsStart = back_option.tsStart
-- WHERE back_option.expirationDate > front_option.expirationDate
-- SETTINGS join_algorithm = 'grace_hash', grace_hash_join_initial_buckets = 16, max_bytes_in_join = 536870912