-- ==============================================================================
-- Lab 2c: Slowly Changing Dimensions (SCD)
-- ==============================================================================
-- BIM5021 - Nhà kho dữ liệu và Tích hợp
-- Chương 2: Mô hình hóa dữ liệu cho Cloud Warehousing
--
-- Mục tiêu:
--   - Hiểu và implement SCD Type 1, 2, 3
--   - Áp dụng cho dim_customer của Olist
--   - So sánh trade-offs giữa các SCD types
-- ==============================================================================

-- ============================================================
-- SCD TYPE 1: Overwrite (Không giữ lịch sử)
-- ============================================================

-- Scenario: Khách hàng chuyển từ São Paulo sang Rio de Janeiro

-- Initial state
INSERT INTO dim_customer (customer_id, customer_unique_id, city, state, zip_code_prefix)
VALUES ('cust_001', 'unique_001', 'Sao Paulo', 'SP', '01000');

-- SCD Type 1: Direct update
UPDATE dim_customer 
SET city = 'Rio de Janeiro',
    state = 'RJ',
    zip_code_prefix = '20000',
    updated_at = CURRENT_TIMESTAMP
WHERE customer_id = 'cust_001';

-- Result: Mất lịch sử! Không biết trước đây ở São Paulo
-- Use case: Khi lịch sử không quan trọng (fix typo, update email)


-- ============================================================
-- SCD TYPE 2: Add New Row (Giữ full lịch sử)
-- ============================================================

-- Create SCD2 table
CREATE TABLE IF NOT EXISTS dim_customer_scd2 (
    customer_key        SERIAL PRIMARY KEY,
    customer_id         VARCHAR(50) NOT NULL,
    customer_unique_id  VARCHAR(50) NOT NULL,
    city                VARCHAR(100),
    state               VARCHAR(5),
    zip_code_prefix     VARCHAR(10),
    -- SCD2 columns
    is_current          BOOLEAN DEFAULT TRUE,
    valid_from          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valid_to            TIMESTAMP DEFAULT '9999-12-31 23:59:59'
);

-- Initial insert
INSERT INTO dim_customer_scd2 (customer_id, customer_unique_id, city, state, zip_code_prefix)
VALUES ('cust_002', 'unique_002', 'Sao Paulo', 'SP', '01000');

-- SCD Type 2: Close current row + Insert new row
-- Step 1: Close current record
UPDATE dim_customer_scd2 
SET is_current = FALSE,
    valid_to = CURRENT_TIMESTAMP
WHERE customer_id = 'cust_002' AND is_current = TRUE;

-- Step 2: Insert new record
INSERT INTO dim_customer_scd2 (customer_id, customer_unique_id, city, state, zip_code_prefix)
VALUES ('cust_002', 'unique_002', 'Rio de Janeiro', 'RJ', '20000');

-- Query: Tìm tất cả lịch sử của customer
SELECT customer_key, customer_id, city, state, is_current, valid_from, valid_to
FROM dim_customer_scd2
WHERE customer_id = 'cust_002'
ORDER BY valid_from;

-- Query: Tìm customer state tại thời điểm cụ thể (Point-in-time)
SELECT *
FROM dim_customer_scd2
WHERE customer_id = 'cust_002'
  AND '2024-06-01'::TIMESTAMP BETWEEN valid_from AND valid_to;

-- Use case: Khi cần phân tích theo thời gian
-- VD: "Revenue theo state TẠI THỜI ĐIỂM mua hàng, không phải state hiện tại"


-- ============================================================
-- SCD TYPE 2: Stored Procedure (automation)
-- ============================================================

CREATE OR REPLACE PROCEDURE upsert_customer_scd2(
    p_customer_id VARCHAR(50),
    p_unique_id VARCHAR(50),
    p_city VARCHAR(100),
    p_state VARCHAR(5),
    p_zip VARCHAR(10)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_city VARCHAR(100);
    v_current_state VARCHAR(5);
    v_changed BOOLEAN := FALSE;
BEGIN
    -- Check if customer exists and if changed
    SELECT city, state INTO v_current_city, v_current_state
    FROM dim_customer_scd2
    WHERE customer_id = p_customer_id AND is_current = TRUE;
    
    IF NOT FOUND THEN
        -- New customer: INSERT
        INSERT INTO dim_customer_scd2 (customer_id, customer_unique_id, city, state, zip_code_prefix)
        VALUES (p_customer_id, p_unique_id, p_city, p_state, p_zip);
        RAISE NOTICE 'NEW customer: %', p_customer_id;
    ELSE
        -- Check if anything changed
        IF v_current_city != p_city OR v_current_state != p_state THEN
            -- Close current record
            UPDATE dim_customer_scd2 
            SET is_current = FALSE, valid_to = CURRENT_TIMESTAMP
            WHERE customer_id = p_customer_id AND is_current = TRUE;
            
            -- Insert new record
            INSERT INTO dim_customer_scd2 (customer_id, customer_unique_id, city, state, zip_code_prefix)
            VALUES (p_customer_id, p_unique_id, p_city, p_state, p_zip);
            
            RAISE NOTICE 'UPDATED customer: % (% → %)', p_customer_id, v_current_city, p_city;
        ELSE
            RAISE NOTICE 'NO CHANGE for customer: %', p_customer_id;
        END IF;
    END IF;
END;
$$;

-- Usage:
-- CALL upsert_customer_scd2('cust_003', 'unique_003', 'Belo Horizonte', 'MG', '30000');
-- CALL upsert_customer_scd2('cust_003', 'unique_003', 'Curitiba', 'PR', '80000');


-- ============================================================
-- SCD TYPE 3: Add Column (Giữ 1 giá trị trước đó)
-- ============================================================

CREATE TABLE IF NOT EXISTS dim_customer_scd3 (
    customer_key        SERIAL PRIMARY KEY,
    customer_id         VARCHAR(50) NOT NULL UNIQUE,
    customer_unique_id  VARCHAR(50) NOT NULL,
    -- Current values
    city                VARCHAR(100),
    state               VARCHAR(5),
    -- Previous values (SCD3)
    previous_city       VARCHAR(100),
    previous_state      VARCHAR(5),
    change_date         TIMESTAMP,
    -- Metadata
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Initial insert
INSERT INTO dim_customer_scd3 (customer_id, customer_unique_id, city, state)
VALUES ('cust_004', 'unique_004', 'Sao Paulo', 'SP');

-- SCD Type 3: Save previous + update current
UPDATE dim_customer_scd3
SET previous_city = city,
    previous_state = state,
    city = 'Brasilia',
    state = 'DF',
    change_date = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE customer_id = 'cust_004';

-- Result:
-- city = 'Brasilia', previous_city = 'Sao Paulo'
-- Chỉ giữ được 1 phiên bản trước!
-- Use case: Khi chỉ cần so sánh "trước" và "hiện tại"


-- ============================================================
-- SO SÁNH SCD TYPES
-- ============================================================

/*
┌────────────┬──────────────────┬────────────────────────┬──────────────────┐
│ Feature    │ SCD Type 1       │ SCD Type 2             │ SCD Type 3       │
├────────────┼──────────────────┼────────────────────────┼──────────────────┤
│ History    │ Không giữ        │ Giữ full               │ Giữ 1 version    │
│ Storage    │ Thấp nhất        │ Cao (nhiều rows)       │ Trung bình       │
│ Complexity │ Đơn giản         │ Phức tạp               │ Trung bình       │
│ Query      │ Đơn giản         │ Cần WHERE is_current   │ Đơn giản         │
│ Use case   │ Fix lỗi, email   │ Thay đổi địa chỉ,     │ So sánh before/  │
│            │ không quan trọng │ giá cả, trạng thái     │ after             │
└────────────┴──────────────────┴────────────────────────┴──────────────────┘
*/
