CREATE TABLE ZacksRank
(
	symbol VARCHAR(15) NOT NULL,
	date DATETIME NOT NULL,
	zacks_rank VARCHAR(25),
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol, date)
) 
;


CREATE TABLE InsiderTransaction
(
	symbol VARCHAR(15) NOT NULL,
	filing_date DATETIME NOT NULL,
	transaction_date DATETIME NOT NULL,
	insider_name VARCHAR(255),
	ownership_type VARCHAR(55),
	securities VARCHAR(255),
	nature_of_transaction VARCHAR(255),
	number_or_value_acquired_disposed NUMERIC(19,2),
	price NUMERIC(19,2),
	form VARCHAR(55) NOT NULL,
	sec_accession_number VARCHAR(55) NOT NULL,
	form_row_number VARCHAR(55) NOT NULL,
	modified DATETIME,
	PRIMARY KEY (symbol, form, sec_accession_number, form_row_number)
) 
;


CREATE TABLE Headlines
(
	symbol VARCHAR(15) NOT NULL,
	asof DATE NOT NULL,
	headline VARCHAR(255) NOT NULL,
	source VARCHAR(25) NOT NULL,
	modified DATETIME NOT NULL
) 
;


CREATE TABLE ETFHoldings
(
	etf_symbol VARCHAR(15) NOT NULL,
	holding_symbol VARCHAR(15) NOT NULL,
	holding_symbol_sc VARCHAR(15),
	pcnt_of_assets NUMERIC(19,2),
	company VARCHAR(255),
	modified DATETIME
) 
;


CREATE TABLE DividendHistory
(
	symbol VARCHAR(15) NOT NULL,
	dividend_type VARCHAR(25),
	cash_amount NUMERIC(22,2),
	div_ex_date DATETIME NOT NULL,
	declaration_date DATETIME,
	record_date DATETIME,
	payment_date DATETIME,
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol, div_ex_date)
) 
;


CREATE TABLE CompanyProfile
(
	symbol VARCHAR(15) NOT NULL,
	description VARCHAR(255),
	pricing_source VARCHAR(10),
	can_roll_previous BOOL NOT NULL DEFAULT false,
	freeze_pricing BOOL NOT NULL DEFAULT false,
	PRIMARY KEY (symbol)
) 
;


CREATE TABLE AnalystRatings
(
	symbol VARCHAR(15) NOT NULL,
	date DATETIME NOT NULL,
	company VARCHAR(255),
	brokerage_firm VARCHAR(50) NOT NULL,
	type VARCHAR(20),
	ratings_change VARCHAR(50),
	price_target NUMERIC(19,2),
	PRIMARY KEY (symbol, date, brokerage_firm)
) 
;


CREATE TABLE AnalystPriceTarget
(
	symbol VARCHAR(15) NOT NULL,
	date DATETIME NOT NULL,
	mean_target NUMERIC(19,2),
	median_target NUMERIC(19,2),
	high_target NUMERIC(19,2),
	low_target NUMERIC(19,2),
	PRIMARY KEY (symbol, date)
) 
;


CREATE TABLE YieldCurve
(
	date DATETIME NOT NULL,
	1Mo NUMERIC(19,2),
	3Mo NUMERIC(19,2),
	6Mo NUMERIC(19,2),
	1Yr NUMERIC(19,2),
	2Yr NUMERIC(19,2),
	3Yr NUMERIC(19,2),
	5Yr NUMERIC(19,2),
	7Yr NUMERIC(19,2),
	10Yr NUMERIC(19,2),
	20Yr NUMERIC(19,2),
	30Yr NUMERIC(19,2),
	modified DATETIME,
	PRIMARY KEY (date)
) 
;


CREATE TABLE Valuations
(
	symbol VARCHAR(15) NOT NULL,
	valuation_date DATETIME NOT NULL,
	company VARCHAR(255) NOT NULL,
	next_earnings_date DATETIME,
	long_term_debt NUMERIC(22,2),
	revenue NUMERIC(22,2),
	debt_load VARCHAR(15),
	roic_avg NUMERIC(22,2),
	roic_slope NUMERIC(22,2),
	roic_dates VARCHAR(50),
	avg_equity_growth NUMERIC(22,2),
	avg_equity_growth_2y NUMERIC(22,2),
	avg_equity_growth_4y NUMERIC(22,2),
	eps_dates VARCHAR(50),
	avg_eps_growth NUMERIC(22,2),
	avg_eps_growth_2y NUMERIC(22,2),
	avg_eps_growth_4y NUMERIC(22,2),
	revenue_dates VARCHAR(50),
	avg_revenue_growth NUMERIC(22,2),
	avg_revenue_growth_qtr NUMERIC(22,2),
	avg_revenue_growth_2y NUMERIC(22,2),
	avg_revenue_growth_4y NUMERIC(22,2),
	avg_freecashflow_growth NUMERIC(22,2),
	avg_operating_cashflow NUMERIC(22,2),
	avg_working_capital NUMERIC(22,2),
	bvps NUMERIC(22,2),
	bvps_dates VARCHAR(50),
	price_to_book NUMERIC(22,2),
	eps NUMERIC(22,2),
	pe NUMERIC(22,2),
	peg NUMERIC(22,2),
	implied_earnings_growth NUMERIC(22,2),
	low_pe NUMERIC(22,2),
	trailing_pe NUMERIC(22,2),
	avg_low_trailing NUMERIC(22,2),
	current_stock_estimate_price NUMERIC(22,2),
	price_estimate_10y NUMERIC(22,2),
	todays_price_for_required_return NUMERIC(22,2),
	mos NUMERIC(22,2),
	mos_80 NUMERIC(22,2),
	intrinsic_value NUMERIC(22,2),
	rgv NUMERIC(22,2),
	latest_price NUMERIC(22,2),
	upside_pcnt NUMERIC(22,2),
	downside_pcnt NUMERIC(22,2),
	mean_target_price NUMERIC(22,2),
	low_target_price NUMERIC(22,2),
	high_target_price NUMERIC(22,2),
	bargain_mos BOOL,
	bargain_mos_80 BOOL,
	modified DATETIME,
	beta90 NUMERIC(19,2),
	beta2Y NUMERIC(19,2),
	market_cap NUMERIC(22,2),
	earnings_yield NUMERIC(23,2),
	ebit NUMERIC(22,2),
	enterprise_value NUMERIC(22,2),
	fundamental_value NUMERIC(22,2),
	net_current_asset_value_per_share NUMERIC(22,2),
	return_on_capital NUMERIC(22,2),
	roic_latest NUMERIC(22,2),
	sector VARCHAR(255),
	industry VARCHAR(255),
	debt_to_equity NUMERIC(22,2),
	sec_13 TINYINT,
	sec_13_filing_date DATETIME,
	tlb_rank_roic NUMERIC(22,2),
	roc_latest NUMERIC(22,2),
	tlb_rank_roc NUMERIC(22,4),
	operating_earnings NUMERIC(22,4),
	am_rank NUMERIC(22,4),
	acquirers_multiple NUMERIC(22,4),
	dividend_yield NUMERIC(22,4),
	shares_outstanding NUMERIC(22,4),
	operating_cashflow NUMERIC(22,4),
	PRIMARY KEY (symbol, valuation_date)
) 
;


CREATE TABLE Splits
(
	symbol VARCHAR(15) NOT NULL,
	effective_date DATETIME NOT NULL,
	ratio VARCHAR(10),
	applied TINYINT,
	applied_least_recent DATETIME,
	applied_most_recent DATETIME,
	modified DATE,
	created DATETIME,
	PRIMARY KEY (symbol, effective_date)
) 
;


CREATE TABLE SecurityMaster
(
	symbol VARCHAR(15) NOT NULL,
	company VARCHAR(255),
	cik VARCHAR(15),
	sector VARCHAR(255),
	industry VARCHAR(255),
	security_type VARCHAR(15),
	create_date DATE NOT NULL DEFAULT =now()),
	PRIMARY KEY (symbol)
) 
;


CREATE TABLE SECFilings
(
	symbol VARCHAR(15) NOT NULL,
	filing_date DATETIME NOT NULL,
	form VARCHAR(25) NOT NULL,
	form_text BLOB NOT NULL,
	description VARCHAR(255),
	sec_filing_url VARCHAR(255) NOT NULL,
	sequence BIGINT NOT NULL,
	sec_accession_number VARCHAR(50) NOT NULL,
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol, filing_date, sequence, sec_accession_number)
) 
;


CREATE TABLE Prices
(
	symbol VARCHAR(15) NOT NULL,
	date DATE NOT NULL,
	open NUMERIC(19,2),
	high NUMERIC(19,2),
	low NUMERIC(19,2),
	close NUMERIC(19,2),
	volume BIGINT,
	adjclose NUMERIC(19,2),
	source CHAR(1),
	PRIMARY KEY (symbol, date)
) 
;


CREATE TABLE PriceIndex
(
	index_code VARCHAR(50) NOT NULL,
	index_name VARCHAR(50) NOT NULL,
	index_value NUMERIC(19,2),
	as_of DATETIME NOT NULL,
	source VARCHAR(50) NOT NULL,
	modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (index_code, as_of)
) 
;


CREATE TABLE Premarket
(
	market VARCHAR(25) NOT NULL,
	change_value NUMERIC(19,2) NOT NULL,
	change_percent NUMERIC(19,2) NOT NULL,
	timestamp VARCHAR(50) NOT NULL,
	PRIMARY KEY (market, timestamp)
) 
;


CREATE TABLE Options
(
	symbol VARCHAR(15) NOT NULL,
	expiration_date DATE NOT NULL,
	option_type CHAR(1) NOT NULL,
	strike NUMERIC(19,2) NOT NULL,
	last_price NUMERIC(19,2),
	change_price NUMERIC(19,2),
	bid NUMERIC(19,2),
	ask NUMERIC(19,2),
	volume INTEGER,
	open_interest NUMERIC(19,2),
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol, expiration_date, option_type, strike)
) 
;


CREATE TABLE MStarSecurityId
(
	symbol VARCHAR(15) NOT NULL,
	security_identifier VARCHAR(25) NOT NULL,
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol)
) 
;


CREATE TABLE MarketHolidays
(
	Date DATE NOT NULL,
	Description VARCHAR(50),
	PRIMARY KEY (Date)
) 
;


CREATE TABLE Lexicon
(
	word VARCHAR(55) NOT NULL,
	sentiment VARCHAR(10) NOT NULL,
	part_of_speech VARCHAR(15),
	PRIMARY KEY (word, sentiment)
) 
;


CREATE TABLE Indices
(
	index_name VARCHAR(55) NOT NULL,
	symbol VARCHAR(15) NOT NULL,
	company VARCHAR(255),
	cik VARCHAR(15),
	sector VARCHAR(255),
	industry VARCHAR(255),
	PRIMARY KEY (index_name, symbol)
) 
;


CREATE TABLE IncomeStatement
(
	symbol VARCHAR(15) NOT NULL,
	asof DATETIME NOT NULL,
	total_revenue NUMERIC(19,2),
	cost_of_revenue NUMERIC(19,2),
	income_tax_expense NUMERIC(19,2),
	gross_profit NUMERIC(19,2),
	net_income NUMERIC(19,2),
	sga NUMERIC(19,2),
	net_income_applicable_to_common_shares NUMERIC(19,2),
	ebit NUMERIC(19,2),
	operating_expenses NUMERIC(19,2),
	research_and_development NUMERIC(19,2),
	interest_expense NUMERIC(19,2),
	period INTEGER NOT NULL,
	modified DATETIME,
	PRIMARY KEY (symbol, asof, period)
) 
;


CREATE TABLE Historical
(
	symbol VARCHAR(15) NOT NULL,
	asof DATETIME NOT NULL,
	type VARCHAR(55) NOT NULL,
	value NUMERIC(19,2),
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol, asof, type)
) 
;


CREATE TABLE Fundamentals
(
	symbol VARCHAR(15) NOT NULL,
	asof DATE NOT NULL,
	source VARCHAR(15),
	next_earnings_date DATE,
	beta NUMERIC(19,2),
	beta_calc_36 NUMERIC(19,2),
	beta_calc_06 NUMERIC(19,2),
	low52 NUMERIC(19,2),
	high52 NUMERIC(19,2),
	volume BIGINT,
	market_cap NUMERIC(19,2),
	pe NUMERIC(19,2),
	eps NUMERIC(19,2),
	peg NUMERIC(19,2),
	return_on_assets NUMERIC(19,2),
	return_on_equity NUMERIC(19,2),
	total_cash NUMERIC(19,2),
	total_debt NUMERIC(19,2),
	average_volume_3m NUMERIC(19,2),
	average_volume_10d NUMERIC(19,2),
	shares_outstanding NUMERIC(19,2),
	revenue NUMERIC(19,2),
	revenue_per_share NUMERIC(19,2),
	qtrly_revenue_growth NUMERIC(19,2),
	gross_profit NUMERIC(19,2),
	ebitda NUMERIC(19,2),
	ebit NUMERIC(19,2),
	net_income_available_to_common NUMERIC(19,2),
	book_value_per_share NUMERIC(19,2),
	operating_cashflow NUMERIC(19,2),
	leveraged_free_cashflow NUMERIC(19,2),
	trailing_pe NUMERIC(19,2),
	enterprise_value NUMERIC(19,2),
	PRIMARY KEY (symbol, asof)
) 
;


CREATE TABLE EconomicIndicators
(
	country_code VARCHAR(25) NOT NULL,
	country_name VARCHAR(255),
	indicator_name VARCHAR(128) NOT NULL,
	indicator_code VARCHAR(50),
	indicator_value NUMERIC(19,2),
	year INTEGER NOT NULL,
	source VARCHAR(50) NOT NULL,
	modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (country_code, indicator_name, year)
) 
;


CREATE TABLE EarningsAnnouncements
(
	symbol VARCHAR(15) NOT NULL,
	date DATETIME NOT NULL,
	period_ending DATETIME NOT NULL,
	estimate NUMERIC(19,2),
	reported NUMERIC(19,2),
	surprise NUMERIC(19,2),
	surprise_pct NUMERIC(10,2),
	time VARCHAR(25),
	modified DATETIME NOT NULL,
	PRIMARY KEY (symbol, date)
) 
;


CREATE TABLE Dividends
(
	symbol VARCHAR(15) NOT NULL,
	payment_date DATETIME NOT NULL,
	account VARCHAR(50) NOT NULL,
	amount NUMERIC(16,4) NOT NULL,
	PRIMARY KEY (symbol, payment_date, account)
) 
;


CREATE TABLE Dictionary
(
	word VARCHAR(55) NOT NULL,
	part_of_speech VARCHAR(15) NOT NULL,
	PRIMARY KEY (word, part_of_speech)
) 
;


CREATE TABLE CurrencyConversion
(
	asof DATETIME NOT NULL,
	source_currency VARCHAR(10) NOT NULL,
	destination_currency VARCHAR(10) NOT NULL,
	destination_currency_name VARCHAR(255),
	units_per_source DOUBLE NOT NULL,
	source_per_unit DOUBLE NOT NULL,
	PRIMARY KEY (asof, source_currency, destination_currency)
) 
;


CREATE TABLE CashflowStatement
(
	symbol VARCHAR(15) NOT NULL,
	asof DATETIME NOT NULL,
	depreciation_and_amortization NUMERIC(19,2),
	deferred_income_taxes NUMERIC(19,2),
	stock_based_compensation NUMERIC(19,2),
	accounts_receivable NUMERIC(19,2),
	inventory NUMERIC(19,2),
	accounts_payable NUMERIC(19,2),
	accrued_liabilities NUMERIC(19,2),
	operating_cashflow NUMERIC(19,2),
	free_cashflow NUMERIC(19,2),
	period INTEGER NOT NULL,
	modified DATETIME,
	PRIMARY KEY (symbol, asof, period)
) 
;


CREATE TABLE Cash
(
	account VARCHAR(25) NOT NULL,
	date DATETIME NOT NULL,
	description VARCHAR(255),
	credit NUMERIC(19,2),
	debit NUMERIC(19,2),
	balance NUMERIC(19,2),
	transaction_id INTEGER NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (account, date, transaction_id)
) 
;


CREATE TABLE BalanceSheet
(
	symbol VARCHAR(15) NOT NULL,
	asof DATETIME NOT NULL,
	long_term_debt NUMERIC(19,2),
	other_liabilities NUMERIC(19,2),
	deferred_long_term_liabilities NUMERIC(19,2),
	minority_interest NUMERIC(19,2),
	negative_goodwill NUMERIC(19,2),
	total_stockholder_equity NUMERIC(19,2),
	inventory NUMERIC(19,2),
	property_plant_and_equipment NUMERIC(19,2),
	intangible_assets NUMERIC(19,2),
	accumulated_amortization NUMERIC(19,2),
	goodwill NUMERIC(19,2),
	period BIT NOT NULL,
	total_current_assets NUMERIC(19,2),
	total_assets NUMERIC(19,2),
	total_current_liabilities NUMERIC(19,2),
	total_liabilities NUMERIC(19,2),
	cash_and_cash_equivalents NUMERIC(19,2),
	modified DATETIME,
	PRIMARY KEY (symbol, asof, period)
) 
;