# Schema: Revenue Reports
Tài liệu schema cho các báo cáo doanh thu, phục vụ mục đích dev local.
---
## 1. report_new_revenue
**Path:** `transform/models/accountant/reports/report_new_revenue.sql`
**Mô tả:** Báo cáo doanh thu tổng hợp theo account, service, billing_cycle
### Schema
| Column | Data Type | Description |
|--------|-----------|-------------|
| `email` | varchar | Email khách hàng |
| `customer_type` | varchar | Loại khách hàng (từ admin.customer_types) |
| `payment_type` | varchar | Loại thanh toán (prepaid/postpaid) |
| `type` | varchar | Empty string (legacy) |
| `service_type` | varchar | Loại dịch vụ |
| `usage_open` | numeric | Usage chưa tạo invoice |
| `total_open` | numeric | Tổng tiền chưa thanh toán |
| `total_paid` | numeric | Tổng tiền đã thanh toán |
| `billing_cycle` | varchar | Chu kỳ billing (DD-MM-YYYY) |
| `subtotal` | numeric | Tổng trước chiết khấu |
| `total` | numeric | Tổng doanh thu |
| `discount` | numeric | Chiết khấu |
| `first_cycle` | varchar | Chu kỳ đầu tiên của khách hàng |
### Service Types
```
dbaas, vod, ddos, call_center, traffic_manager, auto_scaling,
cloud_watcher, mail_inbox, cloud_server, simple_storage,
container_registry, kas, kubernetes_engine, mps, load_balancer,
cdn, lms, vpn, cloud_storage
```
### Sample Data
```sql
SELECT * FROM report_new_revenue
WHERE billing_cycle = '01-12-2025'
LIMIT 5;
```
| email | customer_type | payment_type | service_type | total | total_paid | total_open | billing_cycle |
|-------|---------------|--------------|--------------|-------|------------|------------|---------------|
| user1@example.com | Enterprise | prepaid | cloud_server | 50000000 | 50000000 | 0 | 01-12-2025 |
| user2@example.com | SMB | postpaid | dbaas | 12000000 | 0 | 12000000 | 01-12-2025 |
### DDL (PostgreSQL)
```sql
CREATE TABLE report_new_revenue (
email VARCHAR(255),
customer_type VARCHAR(100),
payment_type VARCHAR(50),
type VARCHAR(10),
service_type VARCHAR(50),
usage_open NUMERIC(20,6),
total_open NUMERIC(20,6),
total_paid NUMERIC(20,6),
billing_cycle VARCHAR(20),
subtotal NUMERIC(20,6),
total NUMERIC(20,6),
discount NUMERIC(20,6),
first_cycle VARCHAR(20)
);
-- Indexes
CREATE INDEX idx_report_new_revenue_email ON report_new_revenue(email);
CREATE INDEX idx_report_new_revenue_billing_cycle ON report_new_revenue(billing_cycle);
CREATE INDEX idx_report_new_revenue_service_type ON report_new_revenue(service_type);
```
### Sample Insert (Mock Data)
```sql
INSERT INTO report_new_revenue VALUES
('customer1@bizfly.vn', 'Enterprise', 'prepaid', '', 'cloud_server', 0, 0, 50000000, '01-12-2025', 55000000, 50000000, 5000000, '01-06-2024'),
('customer2@bizfly.vn', 'SMB', 'postpaid', '', 'dbaas', 1000000, 12000000, 0, '01-12-2025', 13000000, 12000000, 1000000, '01-09-2024'),
('customer3@bizfly.vn', NULL, 'prepaid', '', 'cdn', 0, 500000, 2000000, '01-12-2025', 2500000, 2500000, 0, '01-12-2025'),
('customer1@bizfly.vn', 'Enterprise', 'prepaid', '', 'simple_storage', 0, 0, 3000000, '01-12-2025', 3000000, 3000000, 0, '01-06-2024'),
('customer4@bizfly.vn', 'Startup', 'prepaid', '', 'kubernetes_engine', 0, 8000000, 0, '01-12-2025', 8800000, 8000000, 800000, '01-11-2025');
```
---
## 2. report_revenue_by_billing_plan
**Path:** `transform/models/sale_assistant/reports/report_revenue_by_billing_plan.sql`
**Mô tả:** Báo cáo doanh thu phân loại theo billing plan (on_demand / subscription)
### Schema
| Column | Data Type | Description |
|--------|-----------|-------------|
| `email` | varchar | Email khách hàng |
| `customer_type` | varchar | Loại khách hàng (từ admin.customer_types) |
| `payment_type` | varchar | Loại thanh toán (prepaid/postpaid) |
| `service_type` | varchar | Loại dịch vụ |
| `billing_plan_type` | varchar | **on_demand** hoặc **subscription** |
| `billing_cycle` | varchar | Chu kỳ billing (DD-MM-YYYY) |
| `subtotal` | numeric | Tổng trước chiết khấu |
| `total` | numeric | Tổng doanh thu |
| `total_paid` | numeric | Tổng tiền đã thanh toán |
| `total_open` | numeric | Tổng tiền chưa thanh toán |
| `discount` | numeric | Chiết khấu |
| `first_cycle` | varchar | Chu kỳ đầu tiên của khách hàng |
### Billing Plan Logic
```sql
CASE
WHEN billing_model_id = '9e6036a3-d537-4f0a-9ef1-1082895d2a14' THEN 'on_demand'
ELSE 'subscription'
END AS billing_plan_type
```
### Sample Data
```sql
SELECT * FROM report_revenue_by_billing_plan
WHERE billing_cycle = '01-12-2025'
LIMIT 5;
```
| email | service_type | billing_plan_type | total | total_paid | billing_cycle |
|-------|--------------|-------------------|-------|------------|---------------|
| user1@example.com | cloud_server | subscription | 45000000 | 45000000 | 01-12-2025 |
| user1@example.com | cloud_server | on_demand | 5000000 | 5000000 | 01-12-2025 |
| user2@example.com | simple_storage | on_demand | 8000000 | 8000000 | 01-12-2025 |
### DDL (PostgreSQL)
```sql
CREATE TABLE report_revenue_by_billing_plan (
email VARCHAR(255),
customer_type VARCHAR(100),
payment_type VARCHAR(50),
service_type VARCHAR(50),
billing_plan_type VARCHAR(20), -- 'on_demand' or 'subscription'
billing_cycle VARCHAR(20),
subtotal NUMERIC(20,6),
total NUMERIC(20,6),
total_paid NUMERIC(20,6),
total_open NUMERIC(20,6),
discount NUMERIC(20,6),
first_cycle VARCHAR(20)
);
-- Indexes
CREATE INDEX idx_report_revenue_bp_email ON report_revenue_by_billing_plan(email);
CREATE INDEX idx_report_revenue_bp_billing_cycle ON report_revenue_by_billing_plan(billing_cycle);
CREATE INDEX idx_report_revenue_bp_service_type ON report_revenue_by_billing_plan(service_type);
CREATE INDEX idx_report_revenue_bp_billing_plan_type ON report_revenue_by_billing_plan(billing_plan_type);
```
### Sample Insert (Mock Data)
```sql
INSERT INTO report_revenue_by_billing_plan VALUES
('customer1@bizfly.vn', 'Enterprise', 'prepaid', 'cloud_server', 'subscription', '01-12-2025', 50000000, 45000000, 45000000, 0, 5000000, '01-06-2024'),
('customer1@bizfly.vn', 'Enterprise', 'prepaid', 'cloud_server', 'on_demand', '01-12-2025', 5500000, 5000000, 5000000, 0, 500000, '01-06-2024'),
('customer2@bizfly.vn', 'SMB', 'postpaid', 'simple_storage', 'on_demand', '01-12-2025', 8800000, 8000000, 8000000, 0, 800000, '01-09-2024'),
('customer3@bizfly.vn', NULL, 'prepaid', 'cdn', 'on_demand', '01-12-2025', 2500000, 2500000, 2000000, 500000, 0, '01-12-2025'),
('customer4@bizfly.vn', 'Startup', 'prepaid', 'kubernetes_engine', 'subscription', '01-12-2025', 8800000, 8000000, 0, 8000000, 800000, '01-11-2025'),
('customer5@bizfly.vn', 'Enterprise', 'postpaid', 'dbaas', 'on_demand', '01-12-2025', 15000000, 14000000, 14000000, 0, 1000000, '01-03-2024'),
('customer6@bizfly.vn', NULL, 'prepaid', 'load_balancer', 'subscription', '01-12-2025', 3300000, 3000000, 3000000, 0, 300000, '01-10-2025');
```
---
## So sánh 2 bảng
| Đặc điểm | report_new_revenue | report_revenue_by_billing_plan |
|----------|-------------------|-------------------------------|
| **Có billing_plan_type** | ❌ Không | ✅ Có (on_demand/subscription) |
| **Có usage_open** | ✅ Có | ❌ Không |
| **Có column "type"** | ✅ Có (empty) | ❌ Không |
| **Group by** | email, service_type, billing_cycle | email, service_type, billing_cycle, **billing_plan_type** |
| **Data source** | v4_bills + v3_invoices | v4_bill_lines + v3_invoices |
---
## Query mẫu cho MCP Tools
### Query 1: Tổng hợp doanh thu theo billing_plan
```sql
SELECT
billing_plan_type,
COUNT(DISTINCT email) AS so_khach_hang,
SUM(total) AS tong_doanh_thu,
SUM(total_paid) AS da_thanh_toan,
SUM(total_open) AS chua_thanh_toan
FROM report_revenue_by_billing_plan
WHERE billing_cycle = '01-12-2025'
GROUP BY billing_plan_type;
```
### Query 2: Chi tiết theo service + billing_plan
```sql
SELECT
service_type,
billing_plan_type,
COUNT(DISTINCT email) AS so_kh,
SUM(total) AS doanh_thu
FROM report_revenue_by_billing_plan
WHERE billing_cycle = '01-12-2025'
GROUP BY service_type, billing_plan_type
ORDER BY doanh_thu DESC;
```
### Query 3: So sánh với report_new_revenue
```sql
-- Từ report_new_revenue (không có billing_plan)
SELECT
service_type,
SUM(total) AS total_revenue
FROM report_new_revenue
WHERE billing_cycle = '01-12-2025'
GROUP BY service_type;
-- Từ report_revenue_by_billing_plan (có billing_plan)
SELECT
service_type,
billing_plan_type,
SUM(total) AS total_revenue
FROM report_revenue_by_billing_plan
WHERE billing_cycle = '01-12-2025'
GROUP BY service_type, billing_plan_type;
```
---
## Dependencies
### report_new_revenue
```
Sources:
- billing.bills
- billing.services
- billing.bill_lines
- billing.v4_invoices
- billing.invoices
- admin.customer_types
Refs:
- dim_accounts
```
### report_revenue_by_billing_plan
```
Sources:
- billing.bill_lines
- billing.bills
- billing.v4_subscriptions
- billing.plans
- billing.invoices
- admin.customer_types
Refs:
- dim_accounts
```
---
**Generated:** 2026-01-19
