-- ==============================================================================
-- Lab 2a: Thiết kế Star Schema cho Olist E-commerce
-- ==============================================================================
-- 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:
--   - Thiết kế Star Schema hoàn chỉnh từ dataset Olist
--   - Tạo Dimension Tables với SCD support
--   - Tạo Fact Tables (Transactional + Accumulating Snapshot)
--   - Tạo Aggregate Tables cho Gold Layer
--
-- Tương thích: PostgreSQL 14+ / Snowflake / BigQuery (with minor adjustments)
-- ==============================================================================

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

-- 1. DIM_TIME: Bảng thời gian (pre-populated)
CREATE TABLE IF NOT EXISTS dim_time (
    time_key        INTEGER PRIMARY KEY,
    full_date       DATE NOT NULL UNIQUE,
    day_of_week     SMALLINT NOT NULL,      -- 1=Mon, 7=Sun
    day_name        VARCHAR(10) NOT NULL,    -- Monday, Tuesday...
    day_of_month    SMALLINT NOT NULL,
    day_of_year     SMALLINT NOT NULL,
    week_of_year    SMALLINT NOT NULL,
    month_number    SMALLINT NOT NULL,
    month_name      VARCHAR(10) NOT NULL,
    quarter         SMALLINT NOT NULL,
    year            INTEGER NOT NULL,
    is_weekend      BOOLEAN NOT NULL,
    is_holiday      BOOLEAN DEFAULT FALSE,
    fiscal_quarter  SMALLINT,
    fiscal_year     INTEGER
);

-- Populate dim_time (Sep 2016 - Dec 2018)
INSERT INTO dim_time (time_key, full_date, day_of_week, day_name, day_of_month, 
                      day_of_year, week_of_year, month_number, month_name, 
                      quarter, year, is_weekend)
SELECT 
    TO_CHAR(d, 'YYYYMMDD')::INTEGER AS time_key,
    d AS full_date,
    EXTRACT(ISODOW FROM d)::SMALLINT AS day_of_week,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(DAY FROM d)::SMALLINT AS day_of_month,
    EXTRACT(DOY FROM d)::SMALLINT AS day_of_year,
    EXTRACT(WEEK FROM d)::SMALLINT AS week_of_year,
    EXTRACT(MONTH FROM d)::SMALLINT AS month_number,
    TO_CHAR(d, 'Month') AS month_name,
    EXTRACT(QUARTER FROM d)::SMALLINT AS quarter,
    EXTRACT(YEAR FROM d)::INTEGER AS year,
    EXTRACT(ISODOW FROM d) IN (6, 7) AS is_weekend
FROM generate_series('2016-01-01'::DATE, '2019-12-31'::DATE, '1 day'::INTERVAL) AS d;


-- 2. DIM_CUSTOMER: Khách hàng (SCD Type 1)
CREATE TABLE IF NOT EXISTS dim_customer (
    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),
    -- SCD Type 2 fields (optional, for advanced usage)
    is_current          BOOLEAN DEFAULT TRUE,
    valid_from          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valid_to            TIMESTAMP DEFAULT '9999-12-31'::TIMESTAMP,
    -- Metadata
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_dim_customer_id ON dim_customer(customer_id);
CREATE INDEX idx_dim_customer_unique ON dim_customer(customer_unique_id);


-- 3. DIM_PRODUCT: Sản phẩm
CREATE TABLE IF NOT EXISTS dim_product (
    product_key         SERIAL PRIMARY KEY,
    product_id          VARCHAR(50) NOT NULL UNIQUE,
    category_name       VARCHAR(100),
    category_name_en    VARCHAR(100),
    name_length         SMALLINT,
    description_length  SMALLINT,
    photos_qty          SMALLINT,
    weight_g            INTEGER,
    length_cm           INTEGER,
    height_cm           INTEGER,
    width_cm            INTEGER,
    -- Derived attributes
    volume_cm3          INTEGER GENERATED ALWAYS AS (length_cm * height_cm * width_cm) STORED,
    size_category       VARCHAR(20),  -- 'small', 'medium', 'large'
    -- Metadata
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- 4. DIM_SELLER: Người bán
CREATE TABLE IF NOT EXISTS dim_seller (
    seller_key          SERIAL PRIMARY KEY,
    seller_id           VARCHAR(50) NOT NULL UNIQUE,
    city                VARCHAR(100),
    state               VARCHAR(5),
    zip_code_prefix     VARCHAR(10),
    -- Derived attributes
    region              VARCHAR(20),  -- 'Southeast', 'South', 'Northeast'...
    -- Metadata
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- 5. DIM_GEOGRAPHY: Địa lý (Shared dimension)
CREATE TABLE IF NOT EXISTS dim_geography (
    geography_key       SERIAL PRIMARY KEY,
    zip_code_prefix     VARCHAR(10) NOT NULL,
    city                VARCHAR(100),
    state               VARCHAR(5),
    region              VARCHAR(20),
    latitude            DECIMAL(10, 6),
    longitude           DECIMAL(10, 6),
    UNIQUE(zip_code_prefix, city)
);

CREATE INDEX idx_dim_geo_zip ON dim_geography(zip_code_prefix);


-- 6. DIM_PAYMENT_TYPE: Loại thanh toán (Mini-dimension)
CREATE TABLE IF NOT EXISTS dim_payment_type (
    payment_type_key    SERIAL PRIMARY KEY,
    payment_type        VARCHAR(30) NOT NULL UNIQUE,
    payment_category    VARCHAR(30),  -- 'Digital', 'Traditional', 'Other'
    description         VARCHAR(200)
);

INSERT INTO dim_payment_type (payment_type, payment_category, description) VALUES
    ('credit_card', 'Digital', 'Credit card payment'),
    ('boleto', 'Traditional', 'Brazilian bank slip'),
    ('voucher', 'Digital', 'Voucher/coupon payment'),
    ('debit_card', 'Digital', 'Debit card payment'),
    ('not_defined', 'Other', 'Payment type not specified');


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

-- 1. FACT_ORDER_ITEMS: Transactional Fact (grain: 1 order item)
CREATE TABLE IF NOT EXISTS fact_order_items (
    order_item_key          SERIAL PRIMARY KEY,
    -- Foreign keys (dimension keys)
    order_id                VARCHAR(50) NOT NULL,
    time_key                INTEGER REFERENCES dim_time(time_key),
    customer_key            INTEGER REFERENCES dim_customer(customer_key),
    product_key             INTEGER REFERENCES dim_product(product_key),
    seller_key              INTEGER REFERENCES dim_seller(seller_key),
    -- Degenerate dimension
    order_item_id           SMALLINT NOT NULL,
    -- Measures (facts)
    price                   DECIMAL(10, 2) NOT NULL,
    freight_value           DECIMAL(10, 2) NOT NULL,
    total_value             DECIMAL(10, 2) GENERATED ALWAYS AS (price + freight_value) STORED,
    -- Timestamps (for analysis)
    order_purchase_ts       TIMESTAMP,
    shipping_limit_date     TIMESTAMP
);

CREATE INDEX idx_fact_items_order ON fact_order_items(order_id);
CREATE INDEX idx_fact_items_time ON fact_order_items(time_key);
CREATE INDEX idx_fact_items_customer ON fact_order_items(customer_key);
CREATE INDEX idx_fact_items_product ON fact_order_items(product_key);


-- 2. FACT_ORDER_LIFECYCLE: Accumulating Snapshot (grain: 1 order)
CREATE TABLE IF NOT EXISTS fact_order_lifecycle (
    order_key               SERIAL PRIMARY KEY,
    order_id                VARCHAR(50) NOT NULL UNIQUE,
    -- Foreign keys
    customer_key            INTEGER REFERENCES dim_customer(customer_key),
    -- Milestone date keys
    purchase_date_key       INTEGER REFERENCES dim_time(time_key),
    approved_date_key       INTEGER REFERENCES dim_time(time_key),
    shipped_date_key        INTEGER REFERENCES dim_time(time_key),
    delivered_date_key      INTEGER REFERENCES dim_time(time_key),
    estimated_delivery_key  INTEGER REFERENCES dim_time(time_key),
    -- Status
    order_status            VARCHAR(20),
    -- Lag measures (days)
    days_to_approve         SMALLINT,
    days_to_ship            SMALLINT,
    days_to_deliver         SMALLINT,
    days_vs_estimated       SMALLINT,  -- negative = early, positive = late
    -- Aggregate measures
    total_items             SMALLINT,
    total_amount            DECIMAL(12, 2),
    total_freight           DECIMAL(12, 2),
    total_payment           DECIMAL(12, 2),
    num_payment_installments SMALLINT,
    -- Review
    review_score            SMALLINT,
    has_review_comment      BOOLEAN,
    -- Metadata
    last_updated            TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- 3. FACT_PAYMENTS: Payment details (grain: 1 payment)
CREATE TABLE IF NOT EXISTS fact_payments (
    payment_key             SERIAL PRIMARY KEY,
    order_id                VARCHAR(50) NOT NULL,
    time_key                INTEGER REFERENCES dim_time(time_key),
    customer_key            INTEGER REFERENCES dim_customer(customer_key),
    payment_type_key        INTEGER REFERENCES dim_payment_type(payment_type_key),
    -- Measures
    payment_sequential      SMALLINT,
    payment_installments    SMALLINT,
    payment_value           DECIMAL(10, 2)
);


-- 4. FACT_REVIEWS: Review details (grain: 1 review)
CREATE TABLE IF NOT EXISTS fact_reviews (
    review_key              SERIAL PRIMARY KEY,
    review_id               VARCHAR(50) NOT NULL,
    order_id                VARCHAR(50) NOT NULL,
    time_key                INTEGER REFERENCES dim_time(time_key),
    customer_key            INTEGER REFERENCES dim_customer(customer_key),
    -- Measures
    review_score            SMALLINT NOT NULL,
    has_title               BOOLEAN,
    has_comment             BOOLEAN,
    comment_length          INTEGER,
    -- Timestamps
    review_creation_date    TIMESTAMP,
    review_answer_date      TIMESTAMP,
    response_time_hours     DECIMAL(10, 2)
);


-- ============================================================
-- AGGREGATE TABLES (Gold Layer Pre-computed)
-- ============================================================

-- 1. AGG_DAILY_REVENUE: Doanh thu theo ngày
CREATE TABLE IF NOT EXISTS agg_daily_revenue (
    time_key            INTEGER PRIMARY KEY REFERENCES dim_time(time_key),
    total_orders        INTEGER,
    total_items         INTEGER,
    total_revenue       DECIMAL(14, 2),
    total_freight       DECIMAL(14, 2),
    avg_order_value     DECIMAL(10, 2),
    avg_review_score    DECIMAL(3, 2),
    new_customers       INTEGER,
    returning_customers INTEGER
);

-- 2. AGG_CATEGORY_PERFORMANCE: Hiệu suất theo danh mục
CREATE TABLE IF NOT EXISTS agg_category_performance (
    category_name       VARCHAR(100),
    month_key           INTEGER,  -- YYYYMM
    total_orders        INTEGER,
    total_revenue       DECIMAL(14, 2),
    total_items_sold    INTEGER,
    avg_price           DECIMAL(10, 2),
    avg_review_score    DECIMAL(3, 2),
    avg_delivery_days   DECIMAL(5, 1),
    return_rate         DECIMAL(5, 4),
    PRIMARY KEY (category_name, month_key)
);

-- 3. AGG_SELLER_RANKING: Xếp hạng người bán
CREATE TABLE IF NOT EXISTS agg_seller_ranking (
    seller_key          INTEGER REFERENCES dim_seller(seller_key),
    month_key           INTEGER,
    total_orders        INTEGER,
    total_revenue       DECIMAL(14, 2),
    avg_review_score    DECIMAL(3, 2),
    avg_delivery_days   DECIMAL(5, 1),
    late_delivery_pct   DECIMAL(5, 4),
    revenue_rank        INTEGER,
    PRIMARY KEY (seller_key, month_key)
);

-- 4. AGG_CUSTOMER_SEGMENTS: Phân khúc khách hàng (RFM)
CREATE TABLE IF NOT EXISTS agg_customer_segments (
    customer_key        INTEGER PRIMARY KEY REFERENCES dim_customer(customer_key),
    recency_days        INTEGER,         -- Ngày kể từ đơn cuối
    frequency           INTEGER,         -- Số đơn hàng
    monetary            DECIMAL(12, 2),  -- Tổng chi tiêu
    r_score             SMALLINT,        -- 1-5
    f_score             SMALLINT,        -- 1-5
    m_score             SMALLINT,        -- 1-5
    rfm_segment         VARCHAR(30),     -- 'Champions', 'Loyal', 'At Risk'...
    last_computed       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- USEFUL VIEWS
-- ============================================================

-- View: Order summary (joins all dims)
CREATE OR REPLACE VIEW v_order_summary AS
SELECT 
    fl.order_id,
    fl.order_status,
    dt_purchase.full_date AS purchase_date,
    dt_delivery.full_date AS delivery_date,
    dc.customer_unique_id,
    dc.city AS customer_city,
    dc.state AS customer_state,
    fl.total_items,
    fl.total_amount,
    fl.total_freight,
    fl.total_payment,
    fl.days_to_deliver,
    fl.days_vs_estimated,
    fl.review_score
FROM fact_order_lifecycle fl
LEFT JOIN dim_time dt_purchase ON fl.purchase_date_key = dt_purchase.time_key
LEFT JOIN dim_time dt_delivery ON fl.delivered_date_key = dt_delivery.time_key
LEFT JOIN dim_customer dc ON fl.customer_key = dc.customer_key;

-- View: Product sales analysis
CREATE OR REPLACE VIEW v_product_sales AS
SELECT 
    dp.category_name_en AS category,
    dp.product_id,
    dt.year,
    dt.quarter,
    dt.month_number AS month,
    COUNT(*) AS items_sold,
    SUM(fi.price) AS revenue,
    AVG(fi.price) AS avg_price,
    SUM(fi.freight_value) AS total_freight
FROM fact_order_items fi
JOIN dim_product dp ON fi.product_key = dp.product_key
JOIN dim_time dt ON fi.time_key = dt.time_key
GROUP BY dp.category_name_en, dp.product_id, dt.year, dt.quarter, dt.month_number;


-- ============================================================
-- SAMPLE ANALYTICAL QUERIES
-- ============================================================

-- Q1: Top 10 categories by revenue per quarter
-- SELECT category_name_en, quarter, year,
--        SUM(total_revenue) AS revenue,
--        RANK() OVER (PARTITION BY quarter, year ORDER BY SUM(total_revenue) DESC) AS rank
-- FROM agg_category_performance acp
-- JOIN dim_time dt ON acp.month_key = dt.year * 100 + dt.month_number
-- GROUP BY category_name_en, quarter, year
-- HAVING rank <= 10;

-- Q2: Customer RFM segments distribution
-- SELECT rfm_segment, COUNT(*) AS num_customers, 
--        AVG(monetary) AS avg_spend,
--        AVG(frequency) AS avg_orders
-- FROM agg_customer_segments
-- GROUP BY rfm_segment
-- ORDER BY avg_spend DESC;

-- Q3: Delivery performance by state
-- SELECT dc.state,
--        AVG(fl.days_to_deliver) AS avg_delivery_days,
--        AVG(fl.days_vs_estimated) AS avg_vs_estimated,
--        SUM(CASE WHEN fl.days_vs_estimated > 0 THEN 1 ELSE 0 END)::DECIMAL / COUNT(*) AS late_pct
-- FROM fact_order_lifecycle fl
-- JOIN dim_customer dc ON fl.customer_key = dc.customer_key
-- WHERE fl.delivered_date_key IS NOT NULL
-- GROUP BY dc.state
-- ORDER BY late_pct DESC;
