-- =====================================================================
-- 0) BASE HISTÓRICA com país (derivado do prefixo) e series_id (wh|slot)
--    Mantém granularidade por warehouse_id + slot_hour + dia
-- =====================================================================

CREATE OR REPLACE TABLE `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country` AS
WITH base AS (
  SELECT
    DATE(appointment_date)                  AS date,
    LOWER(warehouse_id)                     AS wh_id,
    slot_hour
  FROM `meli-inbound-case.meli_case_stg.stg_inbound_operations`
  WHERE min_appointment_ts IS NOT NULL
    AND site_id IS NOT NULL
)
SELECT
  date,
  wh_id                                     AS warehouse_id,
  slot_hour,
  COUNT(*)                                  AS count_bookings,
  CONCAT(wh_id, '|', CAST(slot_hour AS STRING)) AS series_id,
  CASE
    WHEN STARTS_WITH(wh_id, 'br') THEN 'BR'
    WHEN STARTS_WITH(wh_id, 'mx') THEN 'MX'
    WHEN STARTS_WITH(wh_id, 'ar') THEN 'AR'
    WHEN STARTS_WITH(wh_id, 'cl') THEN 'CL'
    WHEN STARTS_WITH(wh_id, 'co') THEN 'CO'
    ELSE 'OUTROS'
  END                                        AS country
FROM base
WHERE date >= '2025-05-01'
GROUP BY date, wh_id, slot_hour;

-- =====================================================================
-- 1) MODELOS ARIMA_PLUS por país (TIME_SERIES_ID_COL = series_id)
--    Usa HOLIDAY_REGION do respectivo país e somente séries elegíveis
-- =====================================================================

-- BR -------------------------------------------------------------------
CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_BR`
OPTIONS (
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'count_bookings',
  TIME_SERIES_ID_COL = 'series_id',
  DATA_FREQUENCY = 'DAILY',
  HOLIDAY_REGION = 'BR',
  AUTO_ARIMA = TRUE,
  CLEAN_SPIKES_AND_DIPS = TRUE,
  ADJUST_STEP_CHANGES = TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country = 'BR';

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_MX`
OPTIONS (
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'count_bookings',
  TIME_SERIES_ID_COL = 'series_id',
  DATA_FREQUENCY = 'DAILY',
  HOLIDAY_REGION = 'MX',
  AUTO_ARIMA = TRUE,
  CLEAN_SPIKES_AND_DIPS = TRUE,
  ADJUST_STEP_CHANGES = TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country = 'MX';

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_AR`
OPTIONS (
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'count_bookings',
  TIME_SERIES_ID_COL = 'series_id',
  DATA_FREQUENCY = 'DAILY',
  HOLIDAY_REGION = 'AR',
  AUTO_ARIMA = TRUE,
  CLEAN_SPIKES_AND_DIPS = TRUE,
  ADJUST_STEP_CHANGES = TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country = 'AR';

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CL`
OPTIONS (
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'count_bookings',
  TIME_SERIES_ID_COL = 'series_id',
  DATA_FREQUENCY = 'DAILY',
  HOLIDAY_REGION = 'CL',
  AUTO_ARIMA = TRUE,
  CLEAN_SPIKES_AND_DIPS = TRUE,
  ADJUST_STEP_CHANGES = TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country = 'CL';

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CO`
OPTIONS (
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'count_bookings',
  TIME_SERIES_ID_COL = 'series_id',
  DATA_FREQUENCY = 'DAILY',
  HOLIDAY_REGION = 'CO',
  AUTO_ARIMA = TRUE,
  CLEAN_SPIKES_AND_DIPS = TRUE,
  ADJUST_STEP_CHANGES = TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country = 'CO';

-- 2) PREVISÃO 30d FUTURA (unificada)
CREATE OR REPLACE TABLE `meli-inbound-case.meli_case_analytics.booking_forecast_30d_by_ws_slot` AS
SELECT
  'BR' AS country,
  DATE(forecast_timestamp) AS date,
  series_id,
  SPLIT(series_id, '|')[OFFSET(0)] AS warehouse_id,
  CAST(SPLIT(series_id, '|')[OFFSET(1)] AS INT64) AS slot_hour,
  forecast_value AS predicted_count_bookings,
  prediction_interval_lower_bound,
  prediction_interval_upper_bound
FROM ML.FORECAST(
  MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_BR`,
  STRUCT(360 AS horizon, 0.90 AS confidence_level)
)
UNION ALL
SELECT
  'MX', DATE(forecast_timestamp), series_id,
  SPLIT(series_id, '|')[OFFSET(0)], CAST(SPLIT(series_id, '|')[OFFSET(1)] AS INT64),
  forecast_value, prediction_interval_lower_bound, prediction_interval_upper_bound
FROM ML.FORECAST(
  MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_MX`,
  STRUCT(360 AS horizon, 0.90 AS confidence_level)
)
UNION ALL
SELECT
  'AR', DATE(forecast_timestamp), series_id,
  SPLIT(series_id, '|')[OFFSET(0)], CAST(SPLIT(series_id, '|')[OFFSET(1)] AS INT64),
  forecast_value, prediction_interval_lower_bound, prediction_interval_upper_bound
FROM ML.FORECAST(
  MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_AR`,
  STRUCT(360 AS horizon, 0.90 AS confidence_level)
)
UNION ALL
SELECT
  'CL', DATE(forecast_timestamp), series_id,
  SPLIT(series_id, '|')[OFFSET(0)], CAST(SPLIT(series_id, '|')[OFFSET(1)] AS INT64),
  forecast_value, prediction_interval_lower_bound, prediction_interval_upper_bound
FROM ML.FORECAST(
  MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CL`,
  STRUCT(360 AS horizon, 0.90 AS confidence_level)
)
UNION ALL
SELECT
  'CO', DATE(forecast_timestamp), series_id,
  SPLIT(series_id, '|')[OFFSET(0)], CAST(SPLIT(series_id, '|')[OFFSET(1)] AS INT64),
  forecast_value, prediction_interval_lower_bound, prediction_interval_upper_bound
FROM ML.FORECAST(
  MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CO`,
  STRUCT(360 AS horizon, 0.90 AS confidence_level)
);

-- =====================================================================
-- A) MODELOS DE BACKTEST (treino somente até D–35)
--    Mantém mesma configuração, mas com sufixo _BT
-- =====================================================================

-- BR (BT)
CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_BR_BT`
OPTIONS (
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'count_bookings',
  TIME_SERIES_ID_COL = 'series_id',
  DATA_FREQUENCY = 'DAILY',
  HOLIDAY_REGION = 'BR',
  AUTO_ARIMA = TRUE,
  CLEAN_SPIKES_AND_DIPS = TRUE,
  ADJUST_STEP_CHANGES = TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country = 'BR'
  AND date < DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY);

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_MX_BT` OPTIONS (
  MODEL_TYPE='ARIMA_PLUS', TIME_SERIES_TIMESTAMP_COL='date', TIME_SERIES_DATA_COL='count_bookings',
  TIME_SERIES_ID_COL='series_id', DATA_FREQUENCY='DAILY', HOLIDAY_REGION='MX',
  AUTO_ARIMA=TRUE, CLEAN_SPIKES_AND_DIPS=TRUE, ADJUST_STEP_CHANGES=TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country='MX' AND date < DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY);

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_AR_BT` OPTIONS (
  MODEL_TYPE='ARIMA_PLUS', TIME_SERIES_TIMESTAMP_COL='date', TIME_SERIES_DATA_COL='count_bookings',
  TIME_SERIES_ID_COL='series_id', DATA_FREQUENCY='DAILY', HOLIDAY_REGION='AR',
  AUTO_ARIMA=TRUE, CLEAN_SPIKES_AND_DIPS=TRUE, ADJUST_STEP_CHANGES=TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country='AR' AND date < DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY);

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CL_BT` OPTIONS (
  MODEL_TYPE='ARIMA_PLUS', TIME_SERIES_TIMESTAMP_COL='date', TIME_SERIES_DATA_COL='count_bookings',
  TIME_SERIES_ID_COL='series_id', DATA_FREQUENCY='DAILY', HOLIDAY_REGION='CL',
  AUTO_ARIMA=TRUE, CLEAN_SPIKES_AND_DIPS=TRUE, ADJUST_STEP_CHANGES=TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country='CL' AND date < DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY);

CREATE OR REPLACE MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CO_BT` OPTIONS (
  MODEL_TYPE='ARIMA_PLUS', TIME_SERIES_TIMESTAMP_COL='date', TIME_SERIES_DATA_COL='count_bookings',
  TIME_SERIES_ID_COL='series_id', DATA_FREQUENCY='DAILY', HOLIDAY_REGION='CO',
  AUTO_ARIMA=TRUE, CLEAN_SPIKES_AND_DIPS=TRUE, ADJUST_STEP_CHANGES=TRUE
) AS
SELECT date, count_bookings, series_id
FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
WHERE country='CO' AND date < DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY);

-- =====================================================================
-- B) MÉTRICAS: previsões de 35 dias (após o corte) vs. observados
--    Observação: agora as datas coincidem com o holdout (D–34 ... D0)
-- =====================================================================

-- Função auxiliar (CTEs repetidas por país para clareza/simplicidade)
-- BR
WITH holdout_BR AS (
  SELECT DATE(date) AS date, series_id, count_bookings
  FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
  WHERE country = 'BR'
    AND date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY) AND CURRENT_DATE()
),
pred_BR AS (
  SELECT
    DATE(forecast_timestamp) AS date,
    series_id,
    forecast_value AS yhat,
    prediction_interval_lower_bound AS yhat_lo,
    prediction_interval_upper_bound AS yhat_hi
  FROM ML.FORECAST(
    MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_BR_BT`,
    STRUCT(35 AS horizon, 0.90 AS confidence_level)
  )
),
joined_BR AS (
  SELECT h.date, h.series_id, h.count_bookings AS y, p.yhat, p.yhat_lo, p.yhat_hi
  FROM holdout_BR h
  JOIN pred_BR p USING (date, series_id)
),
m_BR AS (
  SELECT 'BR' AS country,
         COUNT(*) AS n_points,
         AVG(ABS(y - yhat)) AS mae,
         SQRT(AVG(POW(y - yhat, 2))) AS rmse,
         SAFE_DIVIDE(SUM(ABS(y - yhat)), NULLIF(SUM(y), 0)) AS wmape,
         AVG(CASE WHEN y BETWEEN yhat_lo AND yhat_hi THEN 1 ELSE 0 END) AS pi_coverage_90pct
  FROM joined_BR
)

-- MX
, holdout_MX AS (
  SELECT DATE(date) AS date, series_id, count_bookings
  FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
  WHERE country = 'MX'
    AND date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY) AND CURRENT_DATE()
),
pred_MX AS (
  SELECT DATE(forecast_timestamp) AS date, series_id,
         forecast_value AS yhat,
         prediction_interval_lower_bound AS yhat_lo,
         prediction_interval_upper_bound AS yhat_hi
  FROM ML.FORECAST(
    MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_MX_BT`,
    STRUCT(35 AS horizon, 0.90 AS confidence_level)
  )
),
joined_MX AS (
  SELECT h.date, h.series_id, h.count_bookings AS y, p.yhat, p.yhat_lo, p.yhat_hi
  FROM holdout_MX h
  JOIN pred_MX p USING (date, series_id)
),
m_MX AS (
  SELECT 'MX' AS country,
         COUNT(*) AS n_points,
         AVG(ABS(y - yhat)) AS mae,
         SQRT(AVG(POW(y - yhat, 2))) AS rmse,
         SAFE_DIVIDE(SUM(ABS(y - yhat)), NULLIF(SUM(y), 0)) AS wmape,
         AVG(CASE WHEN y BETWEEN yhat_lo AND yhat_hi THEN 1 ELSE 0 END) AS pi_coverage_90pct
  FROM joined_MX
)

-- AR
, holdout_AR AS (
  SELECT DATE(date) AS date, series_id, count_bookings
  FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
  WHERE country = 'AR'
    AND date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY) AND CURRENT_DATE()
),
pred_AR AS (
  SELECT DATE(forecast_timestamp) AS date, series_id,
         forecast_value AS yhat,
         prediction_interval_lower_bound AS yhat_lo,
         prediction_interval_upper_bound AS yhat_hi
  FROM ML.FORECAST(
    MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_AR_BT`,
    STRUCT(35 AS horizon, 0.90 AS confidence_level)
  )
),
joined_AR AS (
  SELECT h.date, h.series_id, h.count_bookings AS y, p.yhat, p.yhat_lo, p.yhat_hi
  FROM holdout_AR h
  JOIN pred_AR p USING (date, series_id)
),
m_AR AS (
  SELECT 'AR' AS country,
         COUNT(*) AS n_points,
         AVG(ABS(y - yhat)) AS mae,
         SQRT(AVG(POW(y - yhat, 2))) AS rmse,
         SAFE_DIVIDE(SUM(ABS(y - yhat)), NULLIF(SUM(y), 0)) AS wmape,
         AVG(CASE WHEN y BETWEEN yhat_lo AND yhat_hi THEN 1 ELSE 0 END) AS pi_coverage_90pct
  FROM joined_AR
)

-- CL
, holdout_CL AS (
  SELECT DATE(date) AS date, series_id, count_bookings
  FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
  WHERE country = 'CL'
    AND date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY) AND CURRENT_DATE()
),
pred_CL AS (
  SELECT DATE(forecast_timestamp) AS date, series_id,
         forecast_value AS yhat,
         prediction_interval_lower_bound AS yhat_lo,
         prediction_interval_upper_bound AS yhat_hi
  FROM ML.FORECAST(
    MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CL_BT`,
    STRUCT(35 AS horizon, 0.90 AS confidence_level)
  )
),
joined_CL AS (
  SELECT h.date, h.series_id, h.count_bookings AS y, p.yhat, p.yhat_lo, p.yhat_hi
  FROM holdout_CL h
  JOIN pred_CL p USING (date, series_id)
),
m_CL AS (
  SELECT 'CL' AS country,
         COUNT(*) AS n_points,
         AVG(ABS(y - yhat)) AS mae,
         SQRT(AVG(POW(y - yhat, 2))) AS rmse,
         SAFE_DIVIDE(SUM(ABS(y - yhat)), NULLIF(SUM(y), 0)) AS wmape,
         AVG(CASE WHEN y BETWEEN yhat_lo AND yhat_hi THEN 1 ELSE 0 END) AS pi_coverage_90pct
  FROM joined_CL
)

-- CO
, holdout_CO AS (
  SELECT DATE(date) AS date, series_id, count_bookings
  FROM `meli-inbound-case.meli_case_analytics.historical_demand_by_day_slot_with_country`
  WHERE country = 'CO'
    AND date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY) AND CURRENT_DATE()
),
pred_CO AS (
  SELECT DATE(forecast_timestamp) AS date, series_id,
         forecast_value AS yhat,
         prediction_interval_lower_bound AS yhat_lo,
         prediction_interval_upper_bound AS yhat_hi
  FROM ML.FORECAST(
    MODEL `meli-inbound-case.meli_case_analytics.booking_forecast_ws_slot_CO_BT`,
    STRUCT(35 AS horizon, 0.90 AS confidence_level)
  )
),
joined_CO AS (
  SELECT h.date, h.series_id, h.count_bookings AS y, p.yhat, p.yhat_lo, p.yhat_hi
  FROM holdout_CO h
  JOIN pred_CO p USING (date, series_id)
),
m_CO AS (
  SELECT 'CO' AS country,
         COUNT(*) AS n_points,
         AVG(ABS(y - yhat)) AS mae,
         SQRT(AVG(POW(y - yhat, 2))) AS rmse,
         SAFE_DIVIDE(SUM(ABS(y - yhat)), NULLIF(SUM(y), 0)) AS wmape,
         AVG(CASE WHEN y BETWEEN yhat_lo AND yhat_hi THEN 1 ELSE 0 END) AS pi_coverage_90pct
  FROM joined_CO
)

SELECT * FROM m_BR
UNION ALL SELECT * FROM m_MX
UNION ALL SELECT * FROM m_AR
UNION ALL SELECT * FROM m_CL
UNION ALL SELECT * FROM m_CO;