Hệ thống Đồng bộ Order & Thống kê Daily

1. Tổng quan Architecture

MongoDB (Raw Data)           PostgreSQL (Normalized)        PostgreSQL (Analytics)
┌──────────────┐            ┌──────────────┐              ┌──────────────┐
│ Tiktok Orders│            │              │              │              │
│ Shopee Orders│ ──Sync──>  │ orders       │ ──Aggregate──> │ daily_stats  │
│ Lazada Orders│  Module    │ (OLTP)       │    Nightly   │ (OLAP)       │
└──────────────┘            └──────────────┘              └──────────────┘
   (Mỗi sàn      →          (Chuẩn hóa      →             (Pre-computed
    format khác)             chung 3 sàn)                  metrics)

Mục tiêu:

• ✅ Chuẩn hóa data từ 3 platforms về 1 format chung
• ✅ Tạo bảng thống kê daily để query nhanh (tránh scan millions records)
• ✅ Hỗ trợ báo cáo, dashboard real-time

---

2. Database Schema

2.1. Bảng OLTP: orders (Đã có)

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_code VARCHAR(100) UNIQUE NOT NULL,  -- Mã đơn từ platform
    platform VARCHAR(20) NOT NULL,             -- 'tiktok', 'shopee', 'lazada'
    
    -- Thông tin đơn hàng
    customer_id BIGINT,
    status VARCHAR(50) NOT NULL,               -- 'pending', 'completed', 'cancelled'
    
    -- Giá trị
    total_amount DECIMAL(12,2) NOT NULL,       -- Tổng giá trị đơn
    discount_amount DECIMAL(12,2) DEFAULT 0,   -- Giảm giá
    final_amount DECIMAL(12,2) NOT NULL,       -- Số tiền thực tế
    
    -- Timestamps
    order_date TIMESTAMP NOT NULL,             -- Ngày đặt hàng
    completed_at TIMESTAMP,                    -- Ngày hoàn thành
    cancelled_at TIMESTAMP,                    -- Ngày hủy
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_date_platform ON orders(order_date, platform);
CREATE INDEX idx_orders_status ON orders(status);

2.2. Bảng OLAP: daily_order_statistics (Mới)

CREATE TABLE daily_order_statistics (
    id BIGSERIAL PRIMARY KEY,
    
    -- Dimensions (2 chiều: ngày + sàn)
    stat_date DATE NOT NULL,
    platform VARCHAR(20) NOT NULL,  -- 'tiktok', 'shopee', 'lazada'
    
    -- Metrics: Số lượng đơn
    total_orders INT NOT NULL DEFAULT 0,
    completed_orders INT NOT NULL DEFAULT 0,
    cancelled_orders INT NOT NULL DEFAULT 0,
    pending_orders INT NOT NULL DEFAULT 0,
    
    -- Metrics: Giá trị
    gross_merchandise_value DECIMAL(15,2) NOT NULL DEFAULT 0,  -- GMV = tổng giá trị đơn
    total_revenue DECIMAL(15,2) NOT NULL DEFAULT 0,            -- Doanh thu thực (sau giảm giá)
    total_discount DECIMAL(15,2) NOT NULL DEFAULT 0,
    
    -- Metrics: Tỷ lệ
    cancellation_rate DECIMAL(5,2),  -- % đơn hủy = (cancelled/total) * 100
    avg_order_value DECIMAL(12,2),   -- Giá trị TB = revenue/completed_orders
    
    -- Metadata
    is_final BOOLEAN DEFAULT FALSE,  -- TRUE nếu ngày đã kết thúc
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    UNIQUE(stat_date, platform)
);

CREATE INDEX idx_stats_date_desc ON daily_order_statistics(stat_date DESC);
CREATE INDEX idx_stats_platform ON daily_order_statistics(platform);
CREATE INDEX idx_stats_date_platform ON daily_order_statistics(stat_date, platform);

2.3. Bảng hỗ trợ: stats_rebuild_queue (Mới)

-- Đánh dấu ngày nào cần tính lại stats
CREATE TABLE stats_rebuild_queue (
    stat_date DATE PRIMARY KEY,
    needs_rebuild BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

---

3. Luồng thực hiện

3.1. Module 1: Sync Orders (Đã có)

┌─────────────────────────────────────────┐
│  Chạy theo schedule: Mỗi 15-30 phút     │
└─────────────────────────────────────────┘
          │
          ↓
    ┌─────────────────────────┐
    │ 1. Fetch raw data từ   │
    │    MongoDB (3 platforms)│
    └───────────┬─────────────┘
                │
                ↓
    ┌─────────────────────────┐
    │ 2. Transform sang       │
    │    format chuẩn         │
    └───────────┬─────────────┘
                │
                ↓
    ┌─────────────────────────┐
    │ 3. Upsert vào orders    │
    │    table (PostgreSQL)   │
    └───────────┬─────────────┘
                │
                ↓
    ┌─────────────────────────┐
    │ 4. Trigger: Mark ngày   │
    │    cần rebuild stats    │
    └─────────────────────────┘

Code trigger (PostgreSQL):

-- Function: Đánh dấu ngày cần tính lại
CREATE OR REPLACE FUNCTION mark_stats_for_rebuild()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO stats_rebuild_queue (stat_date)
    VALUES (DATE(NEW.order_date))
    ON CONFLICT (stat_date) DO UPDATE 
    SET needs_rebuild = TRUE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger: Tự động chạy khi INSERT/UPDATE orders
CREATE TRIGGER trigger_mark_stats
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION mark_stats_for_rebuild();

3.2. Module 2: Daily Statistics Aggregation (Mới)

┌─────────────────────────────────────────┐
│  Chạy theo schedule: Mỗi giờ hoặc EOD  │
└─────────────────────────────────────────┘
          │
          ↓
    ┌─────────────────────────┐
    │ 1. Lấy danh sách ngày   │
    │    cần rebuild từ queue │
    └───────────┬─────────────┘
                │
                ↓
    ┌─────────────────────────┐
    │ 2. Với mỗi ngày:        │
    │    - Xóa stats cũ       │
    │    - Aggregate từ orders│
    │    - Insert stats mới   │
    └───────────┬─────────────┘
                │
                ↓
    ┌─────────────────────────┐
    │ 3. Xóa ngày khỏi queue  │
    └─────────────────────────┘

Pseudo-code Python:

def rebuild_daily_statistics():
    """Chạy mỗi giờ hoặc cuối ngày"""
    
    # 1. Lấy danh sách ngày cần tính lại
    dates_to_rebuild = db.query("""
        SELECT stat_date 
        FROM stats_rebuild_queue 
        WHERE needs_rebuild = TRUE
        ORDER BY stat_date
    """)
    
    for date in dates_to_rebuild:
        # 2. Xóa stats cũ của ngày này
        db.execute("""
            DELETE FROM daily_order_statistics 
            WHERE stat_date = ?
        """, date)
        
        # 3. Aggregate từ orders
        stats = db.query("""
            SELECT 
                DATE(order_date) as stat_date,
                platform,
                
                -- Số lượng đơn
                COUNT(*) as total_orders,
                COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
                COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders,
                COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_orders,
                
                -- Giá trị
                SUM(total_amount) as gross_merchandise_value,
                SUM(CASE WHEN status = 'completed' THEN final_amount ELSE 0 END) as total_revenue,
                SUM(discount_amount) as total_discount,
                
                -- Tỷ lệ
                ROUND((COUNT(CASE WHEN status = 'cancelled' THEN 1 END) * 100.0 / COUNT(*)), 2) as cancellation_rate,
                ROUND(
                    SUM(CASE WHEN status = 'completed' THEN final_amount ELSE 0 END) / 
                    NULLIF(COUNT(CASE WHEN status = 'completed' THEN 1 END), 0),
                    2
                ) as avg_order_value,
                
                -- Check nếu là ngày trong quá khứ
                CASE WHEN DATE(order_date) < CURRENT_DATE THEN TRUE ELSE FALSE END as is_final
                
            FROM orders
            WHERE DATE(order_date) = ?
            GROUP BY DATE(order_date), platform
        """, date)
        
        # 4. Insert vào statistics table
        db.bulk_insert('daily_order_statistics', stats)
        
        # 5. Xóa khỏi rebuild queue
        db.execute("""
            DELETE FROM stats_rebuild_queue WHERE stat_date = ?
        """, date)
        
        print(f"✅ Rebuilt stats for {date}")

---

4. Query Examples

4.1. Dashboard: Doanh thu 7 ngày

SELECT 
    stat_date,
    platform,
    total_orders,
    total_revenue,
    cancellation_rate
FROM daily_order_statistics
WHERE stat_date >= CURRENT_DATE - 7
ORDER BY stat_date DESC, platform;

4.2. So sánh Performance 3 Platforms

SELECT 
    platform,
    SUM(total_orders) as total_orders,
    SUM(total_revenue) as total_revenue,
    AVG(cancellation_rate) as avg_cancel_rate,
    AVG(avg_order_value) as avg_order_value
FROM daily_order_statistics
WHERE stat_date BETWEEN '2024-11-01' AND '2024-11-30'
GROUP BY platform
ORDER BY total_revenue DESC;

4.3. Trend Analysis (Monthly)

SELECT 
    DATE_TRUNC('month', stat_date) as month,
    platform,
    SUM(total_orders) as monthly_orders,
    SUM(total_revenue) as monthly_revenue
FROM daily_order_statistics
WHERE stat_date >= '2024-01-01'
GROUP BY month, platform
ORDER BY month DESC, platform;

---

5. Deployment Checklist

Phase 1: Setup

• Tạo bảng daily_order_statistics
• Tạo bảng stats_rebuild_queue
• Tạo trigger mark_stats_for_rebuild()
• Test trigger với 1 order mẫu

Phase 2: Initial Load

• Viết script full rebuild tất cả historical data
• Chạy script rebuild (có thể mất vài giờ)
• Validate: So sánh tổng stats vs raw orders

-- Query validation
SELECT 
    s.stat_date,
    s.platform,
    s.total_orders as stats_count,
    COUNT(o.id) as actual_count,
    s.total_orders - COUNT(o.id) as difference
FROM daily_order_statistics s
LEFT JOIN orders o 
    ON DATE(o.order_date) = s.stat_date 
    AND o.platform = s.platform
GROUP BY s.stat_date, s.platform, s.total_orders
HAVING s.total_orders != COUNT(o.id);

Phase 3: Automation

• Setup cron job: Chạy aggregation script mỗi giờ
• Monitor queue size: Alert nếu > 100 ngày
• Setup logging cho rebuild process

Phase 4: Monitoring

• Dashboard hiển thị data từ daily_order_statistics
• Alert nếu stats ngày hôm qua chưa được tính
• Weekly review data accuracy

---

6. Edge Cases & Solutions

Tình huống	Giải pháp
Order được update sau nhiều ngày	Trigger tự động đánh dấu ngày gốc cần rebuild
Ngày hôm nay chưa kết thúc	is_final = FALSE, rebuild lại vào 00:00
Rebuild queue quá dài	Batch process 100 ngày/lần, chạy parallel
Data sai lệch	Script validate + manual fix script

---

7. Performance Expectations

Metric	Before (Query raw orders)	After (Query stats)
Query time	5-10s (scan millions)	<100ms (scan thousands)
Data size	10M+ rows	~3,000 rows/year
Dashboard load	Heavy DB load	Minimal load
Aggregate query	Complex JOIN + GROUP BY	Simple SELECT + SUM

---

8. Next Steps (Future)

Sau khi ổn định Phase 1, có thể mở rộng:

• Thêm dimension: category_id (tỷ trọng danh mục)
• Thêm dimension: region (khu vực)
• Tạo bảng weekly_statistics, monthly_statistics
• Real-time aggregation (incremental update thay vì full rebuild)

---

Bienpx224