
-- ============================================================================
-- COFFEE FUTURES DATA WAREHOUSE - SNOWFLAKE DDL
-- ============================================================================
-- Author: Mickias Ambaye
-- Date: 2025-11-09
-- Purpose: Create date warehouse, scheme, dimension and fact tables for coffee futures analysis
-- Version: 001
-- ============================================================================

-- Drop database and schema 
-- DROP DATABASE COFFEE_FUTURES;

-- Create database and schema
CREATE DATABASE IF NOT EXISTS COFFEE_FUTURES;
USE DATABASE COFFEE_FUTURES;
CREATE SCHEMA IF NOT EXISTS MAIN;
USE SCHEMA MAIN;

-- ============================================================================
-- DIMENSION TABLES
-- ============================================================================

-- ============================================================================
-- Table: DIM_CATEGORY
-- Source: dim_category.csv
-- Records: 3
-- ============================================================================
CREATE OR REPLACE TABLE DIM_CATEGORY (
    CATEGORY_ID                    INTEGER,
    CATEGORY                       VARCHAR(15),
    PRIMARY KEY (CATEGORY_ID)
);


-- ============================================================================
-- Table: DIM_COFFEE_TYPE
-- Source: dim_coffee_type.csv
-- Records: 2
-- ============================================================================
CREATE OR REPLACE TABLE DIM_COFFEE_TYPE (
    COFFEE_TYPE_ID                 INTEGER,
    COFFEE_TYPE                    CHAR(7),
    PRIMARY KEY (COFFEE_TYPE_ID)
);


-- ============================================================================
-- Table: DIM_CONTRACT
-- Source: dim_contract.csv
-- Records: 3
-- ============================================================================
CREATE OR REPLACE TABLE DIM_CONTRACT (
    CONTRACT_ID                    INTEGER,
    SYMBOL                         CHAR(5),
    NAME                           VARCHAR(15),
    COFFEE_TYPE_ID                 INTEGER,
    EXCHANGE                       CHAR(8),
    PRIMARY KEY (CONTRACT_ID)
);

-- Foreign Key Constraints for DIM_CONTRACT
ALTER TABLE DIM_CONTRACT ADD CONSTRAINT FK_DIM_CONTRACT_COFFEE_TYPE_ID
    FOREIGN KEY (COFFEE_TYPE_ID) REFERENCES DIM_COFFEE_TYPE(COFFEE_TYPE_ID);


-- ============================================================================
-- Table: DIM_COUNTRY
-- Source: dim_country.csv
-- Records: 19
-- ============================================================================
CREATE OR REPLACE TABLE DIM_COUNTRY (
    COUNTRY_ID                     INTEGER,
    COUNTRY                        VARCHAR(50),
    PRIMARY KEY (COUNTRY_ID)
);


-- ============================================================================
-- Table: DIM_DATE
-- Source: dim_date.csv
-- Records: 20,399
-- ============================================================================
CREATE OR REPLACE TABLE DIM_DATE (
    DATE_ID                        INTEGER,
    DATE                           DATE,
    YEAR                           INTEGER,
    QUARTER                        INTEGER,
    MONTH                          INTEGER,
    MONTH_NAME                     VARCHAR(10),
    MONTH_ABBR                     CHAR(3),
    WEEK                           INTEGER,
    DAY                            INTEGER,
    DAY_OF_WEEK                    INTEGER,
    DAY_NAME                       VARCHAR(10),
    DAY_ABBR                       CHAR(3),
    DAY_OF_YEAR                    INTEGER,
    IS_WEEKEND                     INTEGER,
    IS_MONTH_START                 INTEGER,
    IS_MONTH_END                   INTEGER,
    IS_QUARTER_START               INTEGER,
    IS_QUARTER_END                 INTEGER,
    IS_YEAR_START                  INTEGER,
    IS_YEAR_END                    INTEGER,
    FISCAL_YEAR                    INTEGER,
    FISCAL_QUARTER                 INTEGER,
    WEEK_OF_MONTH                  INTEGER,
    DAYS_IN_MONTH                  INTEGER,
    IS_HARVEST_SEASON_BRAZIL       INTEGER,
    IS_HARVEST_SEASON_VIETNAM      INTEGER,
    IS_HARVEST_SEASON_COLOMBIA     INTEGER,
    IS_HARVEST_SEASON_ETHIOPIA     INTEGER,
    IS_HARVEST_SEASON_INDONESIA    INTEGER,
    IS_HARVEST_SEASON_HONDURAS     INTEGER,
    IS_HARVEST_SEASON_INDIA        INTEGER,
    IS_HARVEST_SEASON_UGANDA       INTEGER,
    PRIMARY KEY (DATE_ID)
);


-- ============================================================================
-- Table: DIM_IMPORTANCE
-- Source: dim_importance.csv
-- Records: 3
-- ============================================================================
CREATE OR REPLACE TABLE DIM_IMPORTANCE (
    IMPORTANCE_ID                  INTEGER,
    IMPORTANCE                     VARCHAR(10),
    PRIMARY KEY (IMPORTANCE_ID)
);


-- ============================================================================
-- Table: DIM_INDICATOR
-- Source: dim_indicator.csv
-- Records: 24
-- ============================================================================
CREATE OR REPLACE TABLE DIM_INDICATOR (
    INDICATOR_ID                   INTEGER,
    INDICATOR_CODE                 VARCHAR(25),
    INDICATOR_NAME                 VARCHAR(50),
    FREQUENCY                      CHAR(6),
    NOTE                           VARCHAR(255),
    IMPORTANCE_ID                  INTEGER,
    PRIMARY KEY (INDICATOR_ID)
);

-- Foreign Key Constraints for DIM_INDICATOR
ALTER TABLE DIM_INDICATOR ADD CONSTRAINT FK_DIM_INDICATOR_IMPORTANCE_ID
    FOREIGN KEY (IMPORTANCE_ID) REFERENCES DIM_IMPORTANCE(IMPORTANCE_ID);


-- ============================================================================
-- Table: DIM_MACRO_CATEGORY
-- Source: dim_macro_category.csv
-- Records: 10
-- ============================================================================
CREATE OR REPLACE TABLE DIM_MACRO_CATEGORY (
    MACRO_CATEGORY_ID              INTEGER,
    CATEGORY                       VARCHAR(25),
    PRIMARY KEY (MACRO_CATEGORY_ID)
);


-- ============================================================================
-- Table: DIM_MACRO_DATA_SOURCE
-- Source: dim_macro_data_source.csv
-- Records: 2
-- ============================================================================
CREATE OR REPLACE TABLE DIM_MACRO_DATA_SOURCE (
    MACRO_DATA_SOURCE_ID           INTEGER,
    DATA_SOURCE                    VARCHAR(10),
    PRIMARY KEY (MACRO_DATA_SOURCE_ID)
);


-- ============================================================================
-- Table: DIM_MARKET_REGIME
-- Source: dim_market_regime.csv
-- Records: 8
-- ============================================================================
CREATE OR REPLACE TABLE DIM_MARKET_REGIME (
    MARKET_REGIME_ID               INTEGER,
    MARKET_REGIME                  VARCHAR(25),
    PRIMARY KEY (MARKET_REGIME_ID)
);


-- ============================================================================
-- Table: DIM_PRODUCTION_PCT
-- Source: dim_production_pct.csv
-- Records: 19
-- ============================================================================
CREATE OR REPLACE TABLE DIM_PRODUCTION_PCT (
    PRODUCTION_PCT_ID               INTEGER,
    PRODUCTION_PCT                  FLOAT,
    PRIMARY KEY (PRODUCTION_PCT_ID)
);

-- ============================================================================
-- Table: DIM_REGIONS
-- Source: dim_regions.csv
-- Records: 8
-- ============================================================================
CREATE OR REPLACE TABLE DIM_REGIONS (
    REGION_ID                      INTEGER,
    COUNTRY_ID                     INTEGER,
    REGION                         VARCHAR(25),
    LATITUDE                       FLOAT,
    LONGITUDE                      FLOAT,
    COFFEE_TYPE_ID                 INTEGER,
    PRODUCTION_PCT_ID              INTEGER,
    DATA_SOURCE                    VARCHAR(50),
    DATA_SOURCE_URL                VARCHAR(50)
);

-- Set Primary Key for DIM_REGIONS
ALTER TABLE DIM_REGIONS ADD CONSTRAINT PK_DIM_REGIONS PRIMARY KEY (REGION_ID);

-- Foreign Key Constraints for DIM_REGIONS
    
ALTER TABLE DIM_REGIONS ADD CONSTRAINT FK_DIM_REGIONS_COUNTRY_ID
    FOREIGN KEY (COUNTRY_ID) REFERENCES DIM_COUNTRY(COUNTRY_ID);

ALTER TABLE DIM_REGIONS ADD CONSTRAINT FK_DIM_REGIONS_COFFEE_TYPE_ID
    FOREIGN KEY (COFFEE_TYPE_ID) REFERENCES DIM_COFFEE_TYPE(COFFEE_TYPE_ID);

ALTER TABLE DIM_REGIONS ADD CONSTRAINT FK_DIM_REGIONS_PRODUCTION_PCT_ID
    FOREIGN KEY (PRODUCTION_PCT_ID) REFERENCES DIM_PRODUCTION_PCT(PRODUCTION_PCT_ID);




-- ============================================================================
-- Table: DIM_TREND_REGIME
-- Source: dim_trend_regime.csv
-- Records: 3
-- ============================================================================
CREATE OR REPLACE TABLE DIM_TREND_REGIME (
    TREND_REGIME_ID                INTEGER,
    TREND_REGIME                   CHAR(7),
    PRIMARY KEY (TREND_REGIME_ID)
);


-- ============================================================================
-- Table: DIM_VOL_REGIME
-- Source: dim_vol_regime.csv
-- Records: 3
-- ============================================================================
CREATE OR REPLACE TABLE DIM_VOL_REGIME (
    VOL_REGIME_ID                  INTEGER,
    VOL_REGIME                     CHAR(6),
    PRIMARY KEY (VOL_REGIME_ID)
);


-- ============================================================================
-- Table: DIM_COMMODITY_SYMBOL
-- Source: dim_commodity_symbol.csv
-- Records: 5
-- ============================================================================
CREATE OR REPLACE TABLE DIM_COMMODITY_SYMBOL (
    COMMODITY_SYMBOL_ID                  INTEGER,
    COMMODITY_SYMBOL                     CHAR(6),
    PRIMARY KEY (COMMODITY_SYMBOL_ID)
);


-- ============================================================================
-- Table: DIM_COMMODITY_NAME
-- Source: dim_commodity_name.csv
-- Records: 5
-- ============================================================================
CREATE OR REPLACE TABLE DIM_COMMODITY_NAME (
    COMMODITY_NAME_ID                  INTEGER,
    COMMODITY_NAME                     VARCHAR(25),
    COMMODITY_IMPORTANCE_ID            INTEGER,
    PRIMARY KEY (COMMODITY_NAME_ID)
);


-- -- Foreign Key Constraints for DIM_COMMODITY_NAME
ALTER TABLE DIM_COMMODITY_NAME ADD CONSTRAINT FK_DIM_COMMODITY_NAME_IMPORTANCE_ID
    FOREIGN KEY (COMMODITY_IMPORTANCE_ID) REFERENCES DIM_IMPORTANCE(IMPORTANCE_ID);

    
-- ============================================================================
-- Table: DIM_COMMODITY_CATEGORY
-- Source: dim_commodity_category.csv
-- Records: 3
-- ============================================================================
CREATE OR REPLACE TABLE DIM_COMMODITY_CATEGORY (
    COMMODITY_CATEGORY_ID                  INTEGER,
    COMMODITY_CATEGORY                     VARCHAR(25),
    PRIMARY KEY (COMMODITY_CATEGORY_ID)
);


-- ============================================================================
-- Table: DIM_TRADER_CATEGORY
-- Source: dim_trader_category.csv
-- Records: 5
-- ============================================================================
CREATE OR REPLACE TABLE DIM_TRADER_CATEGORY (
    TRADER_CATEGORY_ID                  INTEGER,
    TRADER_CATEGORY                     VARCHAR(25),
    PRIMARY KEY (TRADER_CATEGORY_ID)
);


-- ============================================================================
-- Table: DIM_CURRENCY_PAIR
-- Source: dim_currency_pair.csv
-- Records: 18
-- ============================================================================
CREATE OR REPLACE TABLE DIM_CURRENCY_PAIR (
    CURRENCY_PAIR_ID                  INTEGER,
    CURRENCY_PAIR                     CHAR(6),
    PRIMARY KEY (CURRENCY_PAIR_ID)
);


-- ============================================================================
-- FACT TABLES
-- ============================================================================

-- ============================================================================
-- Table: FACT_COMMODITY
-- Source: fact_commodity.csv
-- Records: 35,351
-- ============================================================================
CREATE OR REPLACE TABLE FACT_COMMODITY (
    DATE_ID                        INTEGER,
    COMMODITY_SYMBOL_ID            INTEGER,
    COMMODITY_NAME_ID              INTEGER,
    COMMODITY_CATEGORY_ID          INTEGER,
    PRICE                          FLOAT
);

-- Foreign Key Constraints for FACT_COMMODITY
ALTER TABLE FACT_COMMODITY ADD CONSTRAINT FK_FACT_COMMODITY_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_COMMODITY ADD CONSTRAINT FK_FACT_COMMODITY_COMMODITY_SYMBOL_ID
    FOREIGN KEY (COMMODITY_SYMBOL_ID) REFERENCES DIM_COMMODITY_SYMBOL(COMMODITY_SYMBOL_ID);

ALTER TABLE FACT_COMMODITY ADD CONSTRAINT FK_FACT_COMMODITY_COMMODITY_NAME_ID
    FOREIGN KEY (COMMODITY_NAME_ID) REFERENCES DIM_COMMODITY_NAME(COMMODITY_NAME_ID);

ALTER TABLE FACT_COMMODITY ADD CONSTRAINT FK_FACT_COMMODITY_COMMODITY_CATEGORY_ID
    FOREIGN KEY (COMMODITY_CATEGORY_ID) REFERENCES DIM_COMMODITY_CATEGORY(COMMODITY_CATEGORY_ID);


-- ============================================================================
-- Table: FACT_COT
-- Source: fact_cot.csv
-- Records: 2,575
-- ============================================================================
CREATE OR REPLACE TABLE FACT_COT (
    DATE_ID                        INTEGER,
    TRADER_CATEGORY_ID             INTEGER,
    CONTRACTS_LONG                 INTEGER,
    CONTRACTS_SHORT                INTEGER,
    CONTRACTS_NET                  INTEGER
);

-- Foreign Key Constraints for FACT_COT
ALTER TABLE FACT_COT ADD CONSTRAINT FK_FACT_COT_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_COT ADD CONSTRAINT FK_FACT_COT_TRADER_CATEGORY_ID
    FOREIGN KEY (TRADER_CATEGORY_ID) REFERENCES DIM_TRADER_CATEGORY(TRADER_CATEGORY_ID);


-- ============================================================================
-- Table: FACT_COT_DATA
-- Source: fact_cot_data.csv
-- Records: 515
-- ============================================================================
CREATE OR REPLACE TABLE FACT_COT_DATA (
    DATE_ID                        INTEGER,
    PROD_MERC                      INTEGER,
    SWAP                           INTEGER,
    M_MONEY                        INTEGER,
    OTHER_REPT                     INTEGER,
    NONREPT                        INTEGER,
    OPEN                           FLOAT,
    HIGH                           FLOAT,
    LOW                            FLOAT,
    CLOSE                          FLOAT
);

-- Foreign Key Constraints for FACT_COT_DATA
ALTER TABLE FACT_COT_DATA ADD CONSTRAINT FK_FACT_COT_DATA_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);


-- ============================================================================
-- Table: FACT_FEATURES
-- Source: fact_features.csv
-- Records: 22,154
-- ============================================================================
CREATE OR REPLACE TABLE FACT_FEATURES (
    DATE_ID                        INTEGER,
    CONTRACT_ID                    INTEGER,
    SIMPLE_RETURN                  FLOAT,
    LOG_RETURN                     FLOAT,
    OVERNIGHT_RETURN               FLOAT,
    PRICE_CHANGE                   FLOAT,
    PRICE_CHANGE_PCT               FLOAT,
    HL_RANGE                       FLOAT,
    HL_RANGE_PCT                   FLOAT,
    HL_MIDPOINT                    FLOAT,
    CLOSE_POSITION_IN_RANGE        FLOAT,
    TRUE_RANGE                     FLOAT,
    SMA_5                          FLOAT,
    PRICE_TO_SMA_5                 FLOAT,
    EMA_5                          FLOAT,
    PRICE_TO_EMA_5                 FLOAT,
    SMA_10                         FLOAT,
    PRICE_TO_SMA_10                FLOAT,
    EMA_10                         FLOAT,
    PRICE_TO_EMA_10                FLOAT,
    SMA_20                         FLOAT,
    PRICE_TO_SMA_20                FLOAT,
    EMA_20                         FLOAT,
    PRICE_TO_EMA_20                FLOAT,
    SMA_50                         FLOAT,
    PRICE_TO_SMA_50                FLOAT,
    EMA_50                         FLOAT,
    PRICE_TO_EMA_50                FLOAT,
    SMA_100                        FLOAT,
    PRICE_TO_SMA_100               FLOAT,
    EMA_100                        FLOAT,
    PRICE_TO_EMA_100               FLOAT,
    SMA_200                        FLOAT,
    PRICE_TO_SMA_200               FLOAT,
    EMA_200                        FLOAT,
    PRICE_TO_EMA_200               FLOAT,
    SMA_5_20_CROSS                 FLOAT,
    SMA_20_50_CROSS                FLOAT,
    SMA_50_200_CROSS               FLOAT,
    EMA_5_20_CROSS                 FLOAT,
    TREND_20                       FLOAT,
    TREND_50                       FLOAT,
    VOLATILITY_5D                  FLOAT,
    PRICE_STD_5D                   FLOAT,
    ATR_5                          FLOAT,
    PARKINSON_VOL_5                FLOAT,
    VOLATILITY_10D                 FLOAT,
    PRICE_STD_10D                  FLOAT,
    ATR_10                         FLOAT,
    PARKINSON_VOL_10               FLOAT,
    VOLATILITY_20D                 FLOAT,
    PRICE_STD_20D                  FLOAT,
    ATR_20                         FLOAT,
    PARKINSON_VOL_20               FLOAT,
    VOLATILITY_50D                 FLOAT,
    PRICE_STD_50D                  FLOAT,
    ATR_50                         FLOAT,
    PARKINSON_VOL_50               FLOAT,
    VOLATILITY_100D                FLOAT,
    PRICE_STD_100D                 FLOAT,
    ATR_100                        FLOAT,
    PARKINSON_VOL_100              FLOAT,
    VOL_20D_MA                     FLOAT,
    VOL_20D_MAX_50                 FLOAT,
    VOL_20D_NORMALIZED             FLOAT,
    RSI_7                          FLOAT,
    RSI_7_OVERBOUGHT               FLOAT,
    RSI_7_OVERSOLD                 FLOAT,
    RSI_14                         FLOAT,
    RSI_14_OVERBOUGHT              FLOAT,
    RSI_14_OVERSOLD                FLOAT,
    RSI_21                         FLOAT,
    RSI_21_OVERBOUGHT              FLOAT,
    RSI_21_OVERSOLD                FLOAT,
    ROC_5                          FLOAT,
    ROC_10                         FLOAT,
    ROC_20                         FLOAT,
    ROC_50                         FLOAT,
    MACD                           FLOAT,
    MACD_SIGNAL                    FLOAT,
    MACD_HISTOGRAM                 FLOAT,
    MACD_CROSS                     FLOAT,
    BB_10_MIDDLE                   FLOAT,
    BB_10_UPPER                    FLOAT,
    BB_10_LOWER                    FLOAT,
    BB_10_WIDTH                    FLOAT,
    BB_10_POSITION                 FLOAT,
    BB_20_MIDDLE                   FLOAT,
    BB_20_UPPER                    FLOAT,
    BB_20_LOWER                    FLOAT,
    BB_20_WIDTH                    FLOAT,
    BB_20_POSITION                 FLOAT,
    BB_50_MIDDLE                   FLOAT,
    BB_50_UPPER                    FLOAT,
    BB_50_LOWER                    FLOAT,
    BB_50_WIDTH                    FLOAT,
    BB_50_POSITION                 FLOAT,
    VOLUME_MA_5                    FLOAT,
    VOLUME_RATIO_5                 FLOAT,
    VOLUME_MA_10                   FLOAT,
    VOLUME_RATIO_10                FLOAT,
    VOLUME_MA_20                   FLOAT,
    VOLUME_RATIO_20                FLOAT,
    VOLUME_MA_50                   FLOAT,
    VOLUME_RATIO_50                FLOAT,
    VOLUME_CHANGE                  FLOAT,
    OBV                            FLOAT,
    OBV_MA_20                      FLOAT,
    VPT                            FLOAT,
    FORCE_INDEX                    FLOAT,
    FORCE_INDEX_MA_13              FLOAT,
    AD_LINE                        FLOAT,
    FRAC_DIFF_OPTIMAL              FLOAT,
    FRAC_DIFF_3                    FLOAT,
    FRAC_DIFF_5                    FLOAT,
    FRAC_DIFF_7                    FLOAT,
    OPTIMAL_FFD_D                  FLOAT,
    RETURN_AUTOCORR_OPT            FLOAT,
    RETURN_AUTOCORR_1              FLOAT,
    RETURN_AUTOCORR_5              FLOAT,
    RETURN_AUTOCORR_10             FLOAT,
    SAMPLE_UNIQUENESS              FLOAT,
    OPTIMAL_AUTOCORR_WINDOW        FLOAT,
    OPTIMAL_AUTOCORR_LAG           FLOAT,
    AMIHUD_ILLIQUIDITY             FLOAT,
    AMIHUD_MA_50                   FLOAT,
    ROLL_MEASURE                   FLOAT,
    ROLL_MEASURE_MA_50             FLOAT,
    HL_SPREAD                      FLOAT,
    HL_SPREAD_MA_50                FLOAT,
    OPTIMAL_MICRO_WINDOW           FLOAT,
    RETURN_SKEW_20                 FLOAT,
    RETURN_KURT_20                 FLOAT,
    ZSCORE_20                      FLOAT,
    RETURN_SKEW_50                 FLOAT,
    RETURN_KURT_50                 FLOAT,
    ZSCORE_50                      FLOAT,
    PERCENTILE_RANK_50             FLOAT,
    PERCENTILE_RANK_200            FLOAT,
    IS_Q2                          FLOAT,
    IS_Q4                          FLOAT,
    IS_WINTER_BRAZIL               FLOAT,
    CLOSE_LAG_1                    FLOAT,
    RETURN_LAG_1                   FLOAT,
    VOLATILITY_LAG_1               FLOAT,
    CLOSE_LAG_2                    FLOAT,
    RETURN_LAG_2                   FLOAT,
    VOLATILITY_LAG_2               FLOAT,
    CLOSE_LAG_3                    FLOAT,
    RETURN_LAG_3                   FLOAT,
    VOLATILITY_LAG_3               FLOAT,
    CLOSE_LAG_5                    FLOAT,
    RETURN_LAG_5                   FLOAT,
    VOLATILITY_LAG_5               FLOAT,
    CLOSE_LAG_10                   FLOAT,
    RETURN_LAG_10                  FLOAT,
    VOLATILITY_LAG_10              FLOAT,
    CLOSE_LAG_20                   FLOAT,
    RETURN_LAG_20                  FLOAT,
    VOLATILITY_LAG_20              FLOAT,
    VOLUME_LAG_1                   FLOAT,
    VOLUME_LAG_2                   FLOAT,
    VOLUME_LAG_3                   FLOAT,
    VOLUME_LAG_5                   FLOAT,
    REGIME_CHANGE                  FLOAT,
    REGIME_DURATION                FLOAT,
    VOL_REGIME_ID                  INTEGER,
    TREND_REGIME_ID                INTEGER,
    MARKET_REGIME_ID               INTEGER
);

-- Foreign Key Constraints for FACT_FEATURES
ALTER TABLE FACT_FEATURES ADD CONSTRAINT FK_FACT_FEATURES_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_FEATURES ADD CONSTRAINT FK_FACT_FEATURES_CONTRACT_ID
    FOREIGN KEY (CONTRACT_ID) REFERENCES DIM_CONTRACT(CONTRACT_ID);

ALTER TABLE FACT_FEATURES ADD CONSTRAINT FK_FACT_FEATURES_VOL_REGIME_ID
    FOREIGN KEY (VOL_REGIME_ID) REFERENCES DIM_VOL_REGIME(VOL_REGIME_ID);

ALTER TABLE FACT_FEATURES ADD CONSTRAINT FK_FACT_FEATURES_TREND_REGIME_ID
    FOREIGN KEY (TREND_REGIME_ID) REFERENCES DIM_TREND_REGIME(TREND_REGIME_ID);

ALTER TABLE FACT_FEATURES ADD CONSTRAINT FK_FACT_FEATURES_MARKET_REGIME_ID
    FOREIGN KEY (MARKET_REGIME_ID) REFERENCES DIM_MARKET_REGIME(MARKET_REGIME_ID);


-- ============================================================================
-- Table: FACT_FX
-- Source: fact_fx.csv
-- Records: 942,510
-- ============================================================================
CREATE OR REPLACE TABLE FACT_FX (
    DATE_ID                        INTEGER,
    CURRENCY_PAIR_ID               INTEGER,
    COUNTRY_ID                     INTEGER,
    IMPORTANCE_ID                  INTEGER,
    PRODUCTION_PCT_ID              INTEGER,
    CATEGORY_ID                    INTEGER,
    RATE                           FLOAT
);

-- Foreign Key Constraints for FACT_FX
ALTER TABLE FACT_FX ADD CONSTRAINT FK_FACT_FX_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_FX ADD CONSTRAINT FK_FACT_FX_CURRENCY_PAIR_ID
    FOREIGN KEY (CURRENCY_PAIR_ID) REFERENCES DIM_CURRENCY_PAIR(CURRENCY_PAIR_ID);

ALTER TABLE FACT_FX ADD CONSTRAINT FK_FACT_FX_COUNTRY_ID
    FOREIGN KEY (COUNTRY_ID) REFERENCES DIM_COUNTRY(COUNTRY_ID);

ALTER TABLE FACT_FX ADD CONSTRAINT FK_FACT_FX_IMPORTANCE_ID
    FOREIGN KEY (IMPORTANCE_ID) REFERENCES DIM_IMPORTANCE(IMPORTANCE_ID);

ALTER TABLE FACT_FX ADD CONSTRAINT FK_FACT_FX_PRODUCTION_PCT_ID
    FOREIGN KEY (PRODUCTION_PCT_ID) REFERENCES DIM_PRODUCTION_PCT(PRODUCTION_PCT_ID);

ALTER TABLE FACT_FX ADD CONSTRAINT FK_FACT_FX_CATEGORY_ID
    FOREIGN KEY (CATEGORY_ID) REFERENCES DIM_CATEGORY(CATEGORY_ID);


-- ============================================================================
-- Table: FACT_MACRO
-- Source: fact_macro.csv
-- Records: 67,421
-- ============================================================================
CREATE OR REPLACE TABLE FACT_MACRO (
    DATE_ID                        INTEGER,
    INDICATOR_ID                   INTEGER,
    MACRO_CATEGORY_ID              INTEGER,
    COUNTRY_ID                     INTEGER,
    MACRO_DATA_SOURCE_ID           INTEGER,
    VALUE                          FLOAT
);

-- Foreign Key Constraints for FACT_MACRO
ALTER TABLE FACT_MACRO ADD CONSTRAINT FK_FACT_MACRO_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_MACRO ADD CONSTRAINT FK_FACT_MACRO_INDICATOR_ID
    FOREIGN KEY (INDICATOR_ID) REFERENCES DIM_INDICATOR(INDICATOR_ID);

ALTER TABLE FACT_MACRO ADD CONSTRAINT FK_FACT_MACRO_MACRO_CATEGORY_ID
    FOREIGN KEY (MACRO_CATEGORY_ID) REFERENCES DIM_MACRO_CATEGORY(MACRO_CATEGORY_ID);

ALTER TABLE FACT_MACRO ADD CONSTRAINT FK_FACT_MACRO_COUNTRY_ID
    FOREIGN KEY (COUNTRY_ID) REFERENCES DIM_COUNTRY(COUNTRY_ID);

ALTER TABLE FACT_MACRO ADD CONSTRAINT FK_FACT_MACRO_MACRO_DATA_SOURCE_ID
    FOREIGN KEY (MACRO_DATA_SOURCE_ID) REFERENCES DIM_MACRO_DATA_SOURCE(MACRO_DATA_SOURCE_ID);


-- ============================================================================
-- Table: FACT_PRICES
-- Source: fact_prices.csv
-- Records: 22,154
-- ============================================================================
CREATE OR REPLACE TABLE FACT_PRICES (
    DATE_ID                        INTEGER,
    CONTRACT_ID                    INTEGER,
    OPEN                           FLOAT,
    HIGH                           FLOAT,
    LOW                            FLOAT,
    CLOSE                          FLOAT,
    ADJCLOSE                       FLOAT,
    VOLUME                         INTEGER,
    UNADJUSTEDVOLUME               INTEGER,
    VWAP                           FLOAT
);

-- Foreign Key Constraints for FACT_PRICES
ALTER TABLE FACT_PRICES ADD CONSTRAINT FK_FACT_PRICES_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_PRICES ADD CONSTRAINT FK_FACT_PRICES_CONTRACT_ID
    FOREIGN KEY (CONTRACT_ID) REFERENCES DIM_CONTRACT(CONTRACT_ID);


-- ============================================================================
-- Table: FACT_WEATHER_LONG
-- Source: fact_weather_long.csv
-- Records: 104,044
-- ============================================================================
CREATE OR REPLACE TABLE FACT_WEATHER_LONG (
    DATE_ID                        INTEGER,
    REGION_ID                      INTEGER,
    COUNTRY_ID                     INTEGER,
    TEMP_MAX_C                     FLOAT,
    TEMP_MIN_C                     FLOAT,
    TEMP_MEAN_C                    FLOAT,
    PRECIPITATION_MM               FLOAT,
    RAIN_MM                        FLOAT,
    HUMIDITY_MEAN_PCT              FLOAT,
    HUMIDITY_MAX_PCT               FLOAT,
    HUMIDITY_MIN_PCT               FLOAT
);

-- Foreign Key Constraints for FACT_WEATHER_LONG
ALTER TABLE FACT_WEATHER_LONG ADD CONSTRAINT FK_FACT_WEATHER_LONG_DATE_ID
    FOREIGN KEY (DATE_ID) REFERENCES DIM_DATE(DATE_ID);

ALTER TABLE FACT_WEATHER_LONG ADD CONSTRAINT FK_FACT_WEATHER_LONG_COUNTRY_ID
    FOREIGN KEY (COUNTRY_ID) REFERENCES DIM_COUNTRY(COUNTRY_ID);


ALTER TABLE FACT_WEATHER_LONG ADD CONSTRAINT FK_FACT_WEATHER_LONG_REGION_ID
    FOREIGN KEY (REGION_ID) REFERENCES DIM_REGIONS(REGION_ID);


