-- ==============================================================================
-- In-Database Machine Learning (Ch.6)
-- CLO6: ML bằng SQL, Feature Store, Model Drift Detection
-- ==============================================================================
-- Syntax: BigQuery ML compatible (dùng cho presentation/documentation)
-- PostgreSQL equivalent comments included
-- ==============================================================================

-- ============================================================
-- 1. FEATURE STORE TABLE
-- ============================================================

CREATE TABLE IF NOT EXISTS feature_store_customer (
    customer_key        INTEGER PRIMARY KEY,
    -- RFM features
    recency_days        INTEGER,
    frequency           INTEGER,
    monetary            DECIMAL(12, 2),
    -- Behavioral features
    avg_order_value     DECIMAL(10, 2),
    avg_delivery_days   DECIMAL(5, 1),
    late_delivery_pct   DECIMAL(5, 4),
    avg_review_score    DECIMAL(3, 2),
    pct_credit_card     DECIMAL(5, 4),
    avg_installments    DECIMAL(4, 1),
    avg_items_per_order DECIMAL(4, 1),
    -- Temporal features
    days_since_first    INTEGER,
    preferred_hour      SMALLINT,
    preferred_day       SMALLINT,
    weekend_order_pct   DECIMAL(5, 4),
    -- Segment
    rfm_segment         VARCHAR(30),
    -- Metadata
    feature_version     VARCHAR(10) DEFAULT 'v1.0',
    computed_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Populate feature store
INSERT INTO feature_store_customer (
    customer_key, recency_days, frequency, monetary,
    avg_order_value, avg_delivery_days, late_delivery_pct, avg_review_score,
    pct_credit_card, avg_installments, avg_items_per_order,
    days_since_first, preferred_hour, preferred_day, weekend_order_pct
)
SELECT 
    f.customer_key,
    -- RFM
    EXTRACT(DAY FROM (MAX(f.purchase_timestamp) - 
        (SELECT MAX(purchase_timestamp) FROM fact_orders)))::INTEGER * -1 AS recency_days,
    COUNT(DISTINCT f.order_id) AS frequency,
    SUM(f.total_price + f.total_freight) AS monetary,
    -- Behavioral
    AVG(f.total_price + f.total_freight) AS avg_order_value,
    AVG(f.delivery_days) AS avg_delivery_days,
    SUM(CASE WHEN f.is_late_delivery THEN 1 ELSE 0 END)::DECIMAL / NULLIF(COUNT(*), 0) AS late_delivery_pct,
    AVG(f.review_score) AS avg_review_score,
    SUM(CASE WHEN f.payment_type = 'credit_card' THEN 1 ELSE 0 END)::DECIMAL / NULLIF(COUNT(*), 0) AS pct_credit_card,
    AVG(f.payment_installments) AS avg_installments,
    AVG(f.item_count) AS avg_items_per_order,
    -- Temporal
    EXTRACT(DAY FROM (MAX(f.purchase_timestamp) - MIN(f.purchase_timestamp)))::INTEGER AS days_since_first,
    MODE() WITHIN GROUP (ORDER BY EXTRACT(HOUR FROM f.purchase_timestamp)) AS preferred_hour,
    MODE() WITHIN GROUP (ORDER BY EXTRACT(DOW FROM f.purchase_timestamp)) AS preferred_day,
    SUM(CASE WHEN EXTRACT(DOW FROM f.purchase_timestamp) IN (0, 6) THEN 1 ELSE 0 END)::DECIMAL 
        / NULLIF(COUNT(*), 0) AS weekend_order_pct
FROM fact_orders f
WHERE f.customer_key IS NOT NULL
GROUP BY f.customer_key
ON CONFLICT (customer_key) DO UPDATE SET
    recency_days = EXCLUDED.recency_days,
    frequency = EXCLUDED.frequency,
    monetary = EXCLUDED.monetary,
    computed_at = CURRENT_TIMESTAMP;


-- ============================================================
-- 2. IN-DATABASE ML: Satisfaction Prediction (BigQuery ML syntax)
-- ============================================================

-- Train logistic regression model
-- BigQuery ML:
/*
CREATE OR REPLACE MODEL `ecommerce.satisfaction_model`
OPTIONS(
    model_type = 'LOGISTIC_REG',
    input_label_cols = ['is_satisfied'],
    auto_class_weights = TRUE,
    max_iterations = 20,
    l2_reg = 0.01
) AS
SELECT
    delivery_days,
    delivery_delay_days,
    CAST(is_late_delivery AS INT64) AS is_late,
    total_price,
    total_freight,
    total_freight / NULLIF(total_price, 0) AS freight_ratio,
    item_count,
    payment_installments,
    CASE WHEN review_score >= 4 THEN 1 ELSE 0 END AS is_satisfied
FROM `ecommerce.fact_orders`
WHERE delivery_days IS NOT NULL AND review_score IS NOT NULL;

-- Evaluate
SELECT * FROM ML.EVALUATE(MODEL `ecommerce.satisfaction_model`);

-- Predict on new orders
SELECT
    order_id,
    predicted_is_satisfied,
    predicted_is_satisfied_probs
FROM ML.PREDICT(
    MODEL `ecommerce.satisfaction_model`,
    (SELECT * FROM `ecommerce.new_orders_features`)
);

-- Feature importance
SELECT * FROM ML.WEIGHTS(MODEL `ecommerce.satisfaction_model`)
ORDER BY ABS(weight) DESC;
*/

-- PostgreSQL equivalent: Use PL/Python or external model
-- Here we simulate with a scoring function:

CREATE OR REPLACE FUNCTION predict_satisfaction(
    p_delivery_days DECIMAL,
    p_price DECIMAL,
    p_freight_ratio DECIMAL,
    p_is_late BOOLEAN
) RETURNS DECIMAL AS $$
DECLARE
    score DECIMAL;
BEGIN
    -- Simple logistic regression approximation
    -- (In production, load sklearn model via PL/Python)
    score := 1.0 / (1.0 + EXP(-(
        2.5 
        - 0.05 * LEAST(p_delivery_days, 30)
        - 0.001 * LEAST(p_price, 1000) 
        - 1.5 * LEAST(p_freight_ratio, 1.0)
        - 1.0 * CASE WHEN p_is_late THEN 1 ELSE 0 END
    )));
    RETURN ROUND(score, 4);
END;
$$ LANGUAGE plpgsql;

-- Usage: Score all orders
-- SELECT order_id, delivery_days, total_price,
--        predict_satisfaction(delivery_days, total_price, 
--            total_freight/NULLIF(total_price,0), is_late_delivery) AS satisfaction_prob
-- FROM fact_orders
-- WHERE satisfaction_prob < 0.3;  -- Flag at-risk orders


-- ============================================================
-- 3. MODEL DRIFT DETECTION
-- ============================================================

CREATE TABLE IF NOT EXISTS model_monitoring (
    monitoring_id   SERIAL PRIMARY KEY,
    model_name      VARCHAR(50),
    check_date      DATE,
    metric_name     VARCHAR(50),
    metric_value    DECIMAL(10, 4),
    baseline_value  DECIMAL(10, 4),
    drift_detected  BOOLEAN DEFAULT FALSE,
    alert_message   TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Monthly drift check: compare feature distributions
-- PSI (Population Stability Index) approximation
/*
WITH baseline AS (
    -- Training period features
    SELECT 
        AVG(delivery_days) AS avg_delivery,
        STDDEV(delivery_days) AS std_delivery,
        AVG(total_price) AS avg_price,
        AVG(CASE WHEN is_late_delivery THEN 1 ELSE 0 END) AS late_rate
    FROM fact_orders
    WHERE purchase_timestamp < '2018-06-01'
),
current_period AS (
    -- Recent period
    SELECT 
        AVG(delivery_days) AS avg_delivery,
        STDDEV(delivery_days) AS std_delivery,
        AVG(total_price) AS avg_price,
        AVG(CASE WHEN is_late_delivery THEN 1 ELSE 0 END) AS late_rate
    FROM fact_orders
    WHERE purchase_timestamp >= '2018-06-01'
)
INSERT INTO model_monitoring (model_name, check_date, metric_name, metric_value, baseline_value, drift_detected, alert_message)
SELECT 
    'satisfaction_model', CURRENT_DATE, 'delivery_days_mean',
    c.avg_delivery, b.avg_delivery,
    ABS(c.avg_delivery - b.avg_delivery) / NULLIF(b.std_delivery, 0) > 2,
    CASE WHEN ABS(c.avg_delivery - b.avg_delivery) / NULLIF(b.std_delivery, 0) > 2
         THEN 'DRIFT DETECTED: delivery_days shifted by ' || 
              ROUND(ABS(c.avg_delivery - b.avg_delivery), 1) || ' days'
         ELSE 'OK'
    END
FROM baseline b, current_period c;
*/

-- ============================================================
-- 4. AUTOML STYLE: Try multiple model types
-- ============================================================

-- BigQuery ML supports auto model selection:
/*
CREATE OR REPLACE MODEL `ecommerce.auto_satisfaction_model`
OPTIONS(
    model_type = 'AUTOML_CLASSIFIER',
    input_label_cols = ['is_satisfied'],
    budget_hours = 1.0,
    optimization_objective = 'MAXIMIZE_AU_ROC'
) AS
SELECT ... FROM training_data;
*/

-- ============================================================
-- 5. DEPLOY AS UDF
-- ============================================================

-- The predict_satisfaction function above serves as UDF
-- Can be used directly in SQL queries:

-- SELECT order_id, customer_key,
--        predict_satisfaction(delivery_days, total_price,
--            total_freight / NULLIF(total_price, 0), is_late_delivery) AS sat_prob
-- FROM fact_orders
-- WHERE order_status = 'shipped'  -- Score in-flight orders
-- ORDER BY sat_prob ASC
-- LIMIT 50;
-- → Top 50 orders likely to get bad reviews → proactive customer service
