Ellis improvement

● 🔴 ROOT CAUSE ANALYSIS - Hotel 155693 Crash/Slow

---
  📊 Data Volume Summary

Data Type	Count	Memory Est.
------------------------	-----------	-------------
Contracts	3	~2 KB
Room Types	70	~28 KB
Room Plans	352	~176 KB
Daily Prices	6,560	~4.3 MB
Promotions	710	~284 KB
Promotion Daily Prices	94,308 ⚠️	~62 MB
Allotments	1,260	~504 KB
TOTAL RAW DATA	~103,263	~67 MB

So sánh với hotel trung bình:
  - Hotel trung bình: ~4,000-5,000 daily prices
  - Hotel 155693: ~100,000+ rows → Gấp 20-25 lần!

---
  🔴 ROOT CAUSES

1. MEMORY EXPLOSION - Triple Object Creation ⚠️ Critical

Phase 1: DB Query Results → 103,000+ VO objects (~67 MB)
      ↓
  Phase 2: HashMap grouping → Creates additional collections + map entries (~40 MB)
      ↓
  Phase 3: VO → JsonNode conversion → 103,000+ JsonNode objects (~80 MB)
      ↓
  Phase 4: Sorting with objectMapper.convertValue() → Creates DUPLICATE arrays

Estimated Total Memory: 200-300 MB cho 1 request!

2. Nested objectMapper.convertValue() in Loops ⚠️ Critical

// Line 1541-1570: Sorting creates NEW arrays at EVERY level
  for (contract : contracts) {                        // 3 iterations
      contractType.set("vendorRoomTypes",
          objectMapper.convertValue(...sorted..., ArrayNode.class));  // NEW ARRAY

for (roomType : vendorRoomTypes) {              // 70 iterations
          vendorRoomType.set("vendorRoomPlans",
              objectMapper.convertValue(...sorted..., ArrayNode.class));  // 70 NEW ARRAYs

for (plan : vendorRoomPlans) {              // 352 iterations
              for (adultCount : adultCounts) {        // ~6 variations
                  adultCount.set("dailyPrices",
                      objectMapper.convertValue(...sorted..., ArrayNode.class));
                  // 352 × 6 = 2,112 NEW ARRAYs với ~30 items mỗi array
              }

for (promotion : planPromotions) {      // 710 promotions
                  for (promotionDetail : details) {
                      for (adultCount : adultCounts) {
                          // NESTED CONVERSION - tạo hàng nghìn arrays
                      }
                  }
              }
          }
      }
  }

Tổng số array conversions: ~10,000+ lần gọi objectMapper.convertValue()!

3. Blocking Operations in Reactive Chain

// Line 1067-1582: ENTIRE processing is synchronous inside .map()
  return promotionCheckinRangeMono.map(promotionCheckinRange -> {
      // ~500 lines of BLOCKING code:
      // - HashMap creation
      // - Nested loops
      // - JSON manipulation
      // - Sorting
      // This blocks the thread for potentially MINUTES
  });

Vấn đề: Reactive chain bị block, thread pool exhaustion khi nhiều request đồng thời.

4. Cartesian Product in Promotion Daily Price Query

-- Query getPromotionDailyPriceList creates MASSIVE result set
  -- 352 plans × 710 promotions × 31 days × 6 adult counts = potential millions of rows
  INNER JOIN VD_VENDOR_ROOM_PLAN_PROMOTION VVRPP ON VVRPP.ROOM_PLAN_SEQ = VVRP.ROOM_PLAN_SEQ
  INNER JOIN VD_VENDOR_PLAN_PROMOTION VVPP ON VVPP.PLAN_PROMOTION_SEQ = VVRPP.PLAN_PROMOTION_SEQ
  INNER JOIN VD_VENDOR_ROOM_DAILY_PRICE VVRDP ON ...

Result: 94,308 rows chỉ cho 1 tháng!

5. No Data Limit / Pagination

// Không có LIMIT trong bất kỳ query nào
  // Load TẤT CẢ data cho hotel bất kể size
  Flux<...> dailyPriceFlux = queryWithResultHandler(conditionMap, ...);
  // conditionMap không có limit parameter

---
  📈 Timeline of Crash Scenario

T+0s:     Request received for hotel 155693
  T+0.5s:   8 parallel DB queries start
  T+2s:     Queries complete, ~103,000 objects loaded (~67 MB allocated)
  T+3s:     HashMap grouping starts, creates additional ~40 MB
  T+5s:     Nested loop processing begins
  T+10s:    objectMapper.convertValue() calls accumulating memory
  T+20s:    Memory usage hits 200+ MB for single request
  T+30s:    GC pressure increases, application slows down
  T+45s:    Request timeout (hoặc OOM nếu concurrent requests)

---
  ✅ SOLUTIONS

Immediate Fixes (Quick Wins)

1. Add Data Limit in Queries
  <!-- AdminVendorRateAllotmentMapper.xml -->
  <select id="getPromotionDailyPriceList" ...>
      SELECT * FROM (...)
      LIMIT 50000  <!-- Hard limit to prevent explosion -->
  </select>

2. Return Error for Large Hotels
  // Thêm check ở đầu method
  int estimatedRows = adminVendorRateAllotmentMapper.getEstimatedRowCount(conditionMap);
  if (estimatedRows > 50000) {
      return Mono.error(new OTAException("error.data.too.large",
          "Please select specific room types or contract types"));
  }

3. Sort in SQL instead of Java
  <!-- Add ORDER BY to queries instead of Java sorting -->
  ORDER BY APPLIED_DATE ASC

Medium-term Fixes

4. Lazy Loading / Pagination
  // Load data progressively
  public Mono<JsonNode> getAdminVendorRateAllotmentV2Paginated(
      JsonNode request, int page, int pageSize) {
      // Only load requested page
  }

5. Stream Processing thay vì Load All
  // Use Flux streaming instead of collectList()
  return contractFlux
      .flatMap(contract -> processContract(contract))
      .collectList();

6. Remove redundant objectMapper.convertValue()
  // Instead of:
  contractTypes = objectMapper.convertValue(
      StreamSupport.stream(contractTypes.spliterator(), false)
          .sorted(...).toList(), ArrayNode.class);

// Do in-place sort:
  List<JsonNode> sorted = StreamSupport.stream(contractTypes.spliterator(), false)
      .sorted(...).toList();
  contractTypes.removeAll();
  sorted.forEach(contractTypes::add);

Long-term Architecture Fix

7. Pre-aggregated Summary Table
  -- Create materialized view for rate/allotment summary
  CREATE TABLE VD_RATE_ALLOTMENT_SUMMARY (
      hotel_code INT,
      year_month VARCHAR(6),
      contract_seq INT,
      room_type_seq INT,
      summary_json JSON,  -- Pre-computed hierarchical data
      updated_at TIMESTAMP
  );

8. Separate API for Large Hotels
  // Different endpoint for hotels with >X room types
  @GetMapping("/admin/vendor/rate-allotment/v2/large")
  public Mono<JsonNode> getRateAllotmentLargeHotel(...) {
      // Pagination required
      // Lazy loading
      // Simplified response
  }

---
  📋 Summary

Root Cause	Impact	Priority
------------------------------------	-------------------	-------------
94,308 promotion daily prices	Memory explosion	🔴 Critical
Nested objectMapper.convertValue()	CPU + Memory	🔴 Critical
No data limits	Unbounded growth	🔴 Critical
Blocking reactive chain	Thread exhaustion	🟡 High
Cartesian product in SQL	Slow queries	🟡 High

Recommended Action: Thêm data limit check ngay lập tức để prevent crash, sau đó refactor pagination.

● ---
🎯 Kết luận

Nguyên nhân chính crash hotel 155693:

1. Data khổng lồ: 94,308 promotion daily prices (gấp 20x hotel bình thường)
  2. Memory explosion: ~200-300 MB cho 1 request do triple object creation
  3. 10,000+ lần objectMapper.convertValue() trong nested loops
  4. Không có limit - load toàn bộ data bất kể size

Fix nhanh nhất: Thêm check ở đầu getAdminVendorRateAllotmentV2:

// Estimate data size before processing
  long dailyPriceCount = adminVendorRateAllotmentMapper.countDailyPrices(conditionMap);
  if (dailyPriceCount > 50000) {
      return Mono.error(new OTAException("error.hotel.data.too.large",
          "Hotel has too much data. Please filter by contract type or room type."));
  }

1. 📋 OPTIMIZATION PLAN - rateAllotmentV2

Dựa trên phân tích Root Cause, đề xuất 5 giải pháp tối ưu với phân tích chi tiết:

---

1. 🔴 Solution 1: Data Size Check (Circuit Breaker)

Mô tả: Thêm query count ở đầu method để từ chối request khi data quá lớn (>50,000 rows)

Implementation:
```java
// Thêm vào đầu getAdminVendorRateAllotmentV2
return Mono.fromCallable(() -> adminVendorRateAllotmentMapper.getEstimatedRowCount(conditionMap))
.subscribeOn(Schedulers.boundedElastic())
.flatMap(estimatedCount -> {
if (estimatedCount > 50000) {
return Mono.error(new OTAException("error.hotel.data.too.large",
"Hotel has too much data. Please filter by contract type or room type."));
}
return executeRateAllotmentV2(request, conditionMap);
});
```

Criteria	Assessment
----------	------------
Có cải thiện performance?	Không - chỉ prevent crash
Ưu điểm	Zero risk, deploy ngay, user biết nguyên nhân lỗi
Nhược điểm	Thêm 1 query (~50-100ms), large hotel users thấy error
Input/Output Compatible	✅ Yes (error response cho large hotels)
Effort	Low (1 day)
Risk	Very Low

---

1. 🟡 Solution 2: SQL ORDER BY (Replace Java Sorting) ⭐ RECOMMENDED

Mô tả: Thay thế ~10,000+ lần gọi `objectMapper.convertValue()` trong nested loops bằng ORDER BY trong SQL

Problem Code (line 1537-1556):
```java
// Mỗi lần gọi convertValue tạo array MỚI trong memory
for (ObjectNode adultCount : ...) {
adultCount.set("dailyPrices", objectMapper.convertValue(
StreamSupport.stream(adultCount.get("dailyPrices").spliterator(), false)
.sorted(Comparator.comparing(x > x.get("appliedDate").textValue()))
.collect(Collectors.toList()), ArrayNode.class)); // < NEW ARRAY mỗi lần!
}
// Total: ~10,000+ array allocations cho hotel 155693!
```

Implementation:
```xml
<!-- AdminVendorRateAllotmentMapper.xml -->
<!-- getPlanDailyPriceList -->
SELECT * FROM (...) B
ORDER BY VENDOR_ROOM_PLAN_SEQ, ADULT_COUNT, APPLIED_DATE ASC

<!-- getPromotionDailyPriceList -->
SELECT * FROM (...)
ORDER BY PLAN_PROMOTION_SEQ, ADULT_COUNT, APPLIED_DATE ASC

<!-- getRoomTypeList -->
ORDER BY VVRT.ROOM_TYPE_NAME_EN ASC

<!-- getPlanList -->
ORDER BY VVRP.ROOM_PLAN_NAME_EN ASC
```

Sau đó XÓA code sorting trong Java (line 1537-1564).

Criteria	Assessment
----------	------------
Có cải thiện performance?	✅ YES - giảm 50% response time, 60-70% memory
Ưu điểm	Loại bỏ ~10,000 lần convertValue(), MySQL dùng index hiệu quả, code đơn giản hơn
Nhược điểm	Query chậm hơn 5-10%, cần test kỹ sort order
Input/Output Compatible	✅ YES - output hoàn toàn giống (SQL sort = Java sort cho strings/dates)
Effort	Low (2-3 days)
Risk	Low

Performance Impact:
- Memory: 200-300 MB → 80-100 MB
- Response time: 30-60s → 5-10s

---

1. 🟢 Solution 3: In-place Array Sorting

Mô tả: Nếu vẫn cần Java sorting, thay `objectMapper.convertValue()` bằng in-place mutation

Implementation:
```java
// Utility method
private void sortArrayNodeInPlace(ArrayNode arrayNode, Comparator<JsonNode> comparator) {
if (arrayNode == null || arrayNode.size() <= 1) return;
List<JsonNode> sorted = StreamSupport.stream(arrayNode.spliterator(), false)
.sorted(comparator).collect(Collectors.toList());
arrayNode.removeAll();
sorted.forEach(arrayNode::add);
}

// BEFORE:
contractType.set("vendorRoomTypes", objectMapper.convertValue(sorted, ArrayNode.class));

// AFTER:
sortArrayNodeInPlace((ArrayNode) contractType.get("vendorRoomTypes"), comparator);
```

Criteria	Assessment
----------	------------
Có cải thiện performance?	✅ Yes - giảm 30-40% memory
Ưu điểm	Không tạo duplicate arrays, backward compatible, dễ implement
Nhược điểm	Vẫn dùng CPU cho sorting, mutate existing objects
Input/Output Compatible	✅ YES - output hoàn toàn giống
Effort	Medium (2 days)
Risk	Medium

---

1. 🟢 Solution 4: Optimized HashMap Grouping

Mô tả: Tối ưu grouping từ double-pass thành single-pass với parallel stream cho large data

Problem Code (line 1071-1112):
```java
// BEFORE: Double iteration
Collection<List<Vo>> collection = list.stream()
.collect(Collectors.groupingBy(item -> item.getKey())).values();
for (List<Vo> vos : collection) {
map.put(vos.get(0).getKey(), vos); // iterate again!
}
```

Implementation:
```java
// AFTER: Single pass với parallel cho large data
if (promotionDailyPriceList.size() > 10000) {
promotionDailyPriceKeyValueMap = promotionDailyPriceList.parallelStream()
.collect(Collectors.groupingByConcurrent(
vo -> String.valueOf(vo.getPlanPromotionSeq()),
ConcurrentHashMap::new,
Collectors.toList()
));
} else {
promotionDailyPriceKeyValueMap = promotionDailyPriceList.stream()
.collect(Collectors.groupingBy(
vo -> String.valueOf(vo.getPlanPromotionSeq()),
() -> new HashMap<>(list.size() / 10),
Collectors.toList()
));
}
```

Criteria	Assessment
----------	------------
Có cải thiện performance?	✅ Yes - giảm 20% processing time
Ưu điểm	Single-pass, better HashMap sizing, parallel cho large data
Nhược điểm	Marginal improvement, code phức tạp hơn
Input/Output Compatible	✅ YES - output hoàn toàn giống
Effort	Medium (1-2 days)
Risk	Low

---

1. 🔵 Solution 5: Pagination API (Long-term)

Mô tả: Tạo endpoint mới cho large hotels với pagination support

Implementation:
```java
@GetMapping("/admin/vendor/rate-allotment/v2/paginated")
public Mono<JsonNode> getRateAllotmentPaginated(
@RequestBody JsonNode request,
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "5") int roomTypesPerPage) {
// Load only roomTypes for current page
// Return with pagination info
}
```

Response Format:
```json {
"result": {
"contractTypes": [...],
"pagination": {
"currentPage": 1,
"totalPages": 14,
"roomTypesPerPage": 5,
"totalRoomTypes": 70
}
}
}
```

Criteria	Assessment
----------	------------
Có cải thiện performance?	✅ YES - giảm 90% memory, scalable
Ưu điểm	Xử lý được millions of rows, progressive loading
Nhược điểm	High effort, cần frontend changes, breaking change
Input/Output Compatible	❌ NO - endpoint mới, response structure khác
Effort	High (1 sprint)
Risk	Medium

---

1. 📊 SUMMARY TABLE

Solution	Performance Gain	Memory Reduction	Effort	Risk	I/O Compatible
----------	-----------------	------------------	--------	------	----------------
1. Data Size Check	Prevent crash	N/A	Low	Very Low	✅ Yes
2. SQL ORDER BY ⭐	~50% faster	60-70%	Low	Low	✅ Yes
3. In-place Sorting	~30% faster	30-40%	Medium	Medium	✅ Yes
4. Optimized Grouping	~20% faster	10-15%	Medium	Low	✅ Yes
5. Pagination API	Scalable	90%	High	Medium	❌ No

---

1. 🎯 IMPLEMENTATION PRIORITY

Phase	Solution	Timeline	Deploy
-------	----------	----------	--------
Phase 1	1. Data Size Check	1 day	🔴 Ngay lập tức
Phase 2	2. SQL ORDER BY	2-3 days	🟡 Sprint này
Phase 3	3+4. In-place + Grouping	2 days	🟢 Nếu cần thêm
Phase 4	5. Pagination	1 sprint	🔵 Plan với FE team

---

1. ✅ EXPECTED RESULTS (Hotel 155693)

Sau khi apply Solution 1 + 2:
- ❌ Trước: Crash/timeout sau 30-60s, memory 200-300MB
- ✅ Sau: Response trong 5-10s, memory 80-100MB
- ✅ Input/Output: Hoàn toàn giống v2 hiện tại

---

📎 Full plan document: `web-api/OPTIMIZATION_PLAN_RATE_ALLOTMENT_V2.md`

Phân Tích Khả Năng Paging Frontend Rate-Allotment¶

1. Tình Trạng Hiện Tại¶

Component	Hiện Tại	Vấn Đề
Frontend (oh-admin)	✅ Đã có "gradual loading" - batch theo hotelContractSeqs, roomTypeSeqs	Client-side batching chỉ chia nhỏ requests, không giảm load backend
Backend (oh-api)	❌ Load ALL data rồi filter	Với hotel 155693 (100k+ rows) → Server crash trước khi frontend kịp batch

2. Frontend Gradual Loading Hiện Tại¶

// Điều kiện batch (rate-allotment.effects.ts)
if (totalItemCount <= 1000) {
  return { isLoadNormal: true }   // Single request
} else {
  return { isLoadNormal: false }  // Multiple batches (40% mỗi batch)
}

Giới hạn: Backend V2 vẫn phải process toàn bộ query → OOM với hotels cực lớn.

---

3. So Sánh Các Giải Pháp¶

Giải Pháp	Backend Change	Frontend Change	Effort	Risk	Performance
Solution 1+2 (Circuit Breaker + SQL ORDER BY)	✅ Có (~70 lines)	❌ Không cần	Low (1-2 days)	Very Low	Giảm 50-70% memory, prevent crash
Solution 5 (Server-Side Pagination)	✅ Có (~300 lines)	✅ Có (~100 lines)	High (5-7 days)	Medium	Scalable không giới hạn

---

4. Chi Tiết Nếu Chọn Server-Side Pagination (Solution 5)¶

Backend:

• Endpoint mới: /admin/vendor/rate-allotment/v2/paginated
• Parameters: page, roomTypesPerPage
• Response kèm pagination info

Frontend cần thay đổi:

• Thêm pagination state vào NgRx reducer
• Thêm effect gọi paginated endpoint
• Thêm pagination controls vào collection component
• Điều chỉnh data merge logic

---

5. Đề Xuất¶

Phase	Action	Timeline
Phase 1 (Ngay)	Deploy Solution 1+2 - Prevent crash, không cần frontend change	1-2 days
Phase 2 (Next Sprint)	Implement Solution 5 với frontend team	5-7 days

---

6. Files Reference¶

Backend:

• AdminVendorRateAllotmentService.java
• AdminVendorRateAllotmentMapper.xml

Frontend:

• oh-admin/src/app/routes/rate-allotment/effects/rate-allotment.effects.ts
• oh-admin/src/app/routes/rate-allotment/reducers/rate-allotment.reducer.ts
• oh-admin/src/app/routes/rate-allotment/components/collection/collection.component.ts

---

Cần thảo luận với team để quyết định approach.

1. Debug Log Analysis - RateAllotment V3

1. Timeline từ logs-debug.md

Timestamp	Thread	Query	Duration from start
-----------	--------	-------	---------------------
11:21:35.382	boundedElastic-9	PromotionDailyPrice	0ms
11:21:35.567	boundedElastic-3	ContractList	185ms
11:21:35.663	boundedElastic-10	AllotmentList	281ms
11:21:36.137	boundedElastic-6	PlanDailyPrice	755ms
11:21:36.869	boundedElastic-8	PromotionList	1487ms
11:21:36.986	boundedElastic-5	PlanList	1604ms
11:21:37.357	boundedElastic-7	SellerList	1975ms
11:21:38.910	-	Redis Reconnecting	3528ms
11:21:46.785	boundedElastic-6	PromotionCheckinRange	11403ms

---

1. ✅ ĐIỂM MẠNH

1. Parallel Query Execution
- 8 queries chạy song song trên các thread khác nhau (boundedElastic-3 đến boundedElastic-10)
- Sử dụng Reactive pattern (Flux, Mono.zip) để tận dụng đa luồng
- Giảm tổng thời gian so với sequential execution

2. SQL với ORDER BY (V3)
- Queries đã có ORDER BY để pre-sort data tại database
- Giảm sorting overhead trong Java application

3. Query Optimization
- Sử dụng INNER JOIN thay vì subquery trong nhiều trường hợp
- Index-friendly conditions

---

1. ❌ ĐIỂM YẾU

1. Sequential Dependency - PromotionCheckinRange (Critical)
- Query cuối cùng phải đợi tất cả queries trước hoàn thành
- Gap 9+ giây (từ 11:21:37.357 đến 11:21:46.785)
- Đây là bottleneck chính: promotionCheckinRangeMono phụ thuộc vào promotionSeqList

2. N+1 Query Pattern trong SQL
```sql
(SELECT COMP_NAME_OPERATION FROM US_COMP_MASTER WHERE COMP_CODE = ...)
(SELECT DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE WHERE ...)
```
- Mỗi row sẽ execute subquery → performance impact với large datasets

3. Complex Nested Subqueries
- PromotionDailyPrice query có 3 levels nested subquery (A → B → C)
- Query phức tạp khó optimize và debug

4. TRIM Function trên WHERE clause
```sql
WHERE VVHC.HOTEL_CODE = TRIM
```
- TRIM có thể ngăn cản sử dụng index

5. DATE_FORMAT trong WHERE clause
```sql
AND DATE_FORMAT(VVRDP.APPLIED_DATE, '%Y%m') = TRIM
```
- Không sử dụng được index trên APPLIED_DATE
- Nên dùng: APPLIED_DATE BETWEEN '2025-12-01' AND '2025-12-31'

6. Large IN Clause
- PromotionCheckinRange query có 150+ promotion IDs trong IN (...)
- Có thể gây performance issue với list lớn hơn

7. Redis Connection Issue
- Redis reconnection trong quá trình xử lý (11:21:38.910)
- Có thể gây delay nếu có cache miss

---

1. 📊 RECOMMENDATIONS

1. Refactor PromotionCheckinRange - Fetch song song hoặc batch với main queries
2. Replace subqueries với JOIN - Pre-fetch lookup tables (US_COMP_MASTER, BS_DETAIL_CODE)
3. Optimize date filtering - Dùng BETWEEN thay DATE_FORMAT
4. Remove TRIM - Clean data at input, không tại query time
5. Consider caching - Cache lookup data cho các bảng reference

1. Optimization Plan V3 Created

Đã tạo plan chi tiết tại: `web-api/OPTIMIZATION_PLAN_RATE_ALLOTMENT_V3.md`

1. Summary - 6 Solutions

#	Solution	Effort	Expected Gain	Priority
---	----------	--------	---------------	----------
1	PromotionCheckinRange Parallel	Medium	75-80%	Critical
2	Replace Subqueries with JOIN	Medium	30%	High
3	Optimize Date Filtering	Low	20%	High
4	Remove TRIM Functions	Low	10%	Medium
5	Batch Large IN Clauses	Medium	15%	Medium
6	Cache Lookup Tables	Medium	25%	Medium

1. Critical Finding

PromotionCheckinRange là bottleneck chính:
- Chiếm 79% tổng thời gian (9+ seconds / 11.4 seconds)
- Do sequential dependency: phải đợi tất cả queries khác hoàn thành trước

1. Expected Results

Phase	Total Time	Improvement
-------	------------	-------------
Before	~11.4 seconds	-
After Phase 1	~2-3 seconds	75-80%
After All Phases	~1-2 seconds	85-90%

1. Implementation Priority

Phase 1 (This Week): Solution 1 - PromotionCheckinRange Parallel
Phase 2 (Next Week): Solutions 3, 4 - Date/TRIM optimization
Phase 3 (Sprint+1): Solutions 2, 5, 6 - JOIN/Batch/Cache

📊 Phân Tích Top 20 Hotels có dữ liệu Rate/Allotment NẶNG nhất¶

Dựa trên cơ chế phân tích Root Cause của task này, đã tìm được danh sách hotels có nguy cơ gây crash/slow tương tự hotel 155693.

---

🔍 SQL Query - Lấy danh sách hotels nặng¶

Bước 1: Query cơ bản - Room Types, Room Plans, Contracts

SELECT 
    rt.HOTEL_CODE,
    hm.HOTEL_NAME_EN,
    COUNT(DISTINCT rt.VENDOR_ROOM_TYPE_SEQ) as ROOM_TYPE_COUNT,
    COUNT(DISTINCT rp.ROOM_PLAN_SEQ) as ROOM_PLAN_COUNT,
    COUNT(DISTINCT hc.HOTEL_CONTRACT_SEQ) as CONTRACT_COUNT
FROM VD_VENDOR_ROOM_TYPE rt
INNER JOIN VD_VENDOR_ROOM_PLAN rp ON rp.VENDOR_ROOM_TYPE_SEQ = rt.VENDOR_ROOM_TYPE_SEQ
INNER JOIN VD_VENDOR_HOTEL_CONTRACT hc ON hc.HOTEL_CONTRACT_SEQ = rp.HOTEL_CONTRACT_SEQ
LEFT JOIN HO_HOTEL_MASTER hm ON hm.HOTEL_CODE = rt.HOTEL_CODE
GROUP BY rt.HOTEL_CODE, hm.HOTEL_NAME_EN
ORDER BY ROOM_PLAN_COUNT DESC
LIMIT 25;

Bước 2: Query đầy đủ - Tính Estimated Data Load

SELECT 
    stats.HOTEL_CODE,
    stats.HOTEL_NAME_EN,
    stats.ROOM_TYPE_COUNT,
    stats.ROOM_PLAN_COUNT,
    stats.CONTRACT_COUNT,
    IFNULL(promo.PROMOTION_COUNT, 0) as PROMOTION_COUNT,
    IFNULL(dp.DAILY_PRICE_COUNT, 0) as DAILY_PRICE_COUNT,
    (stats.ROOM_PLAN_COUNT * IFNULL(promo.PROMOTION_COUNT, 1) * 31 * 3) as ESTIMATED_PROMO_DAILY_LOAD,
    (stats.ROOM_PLAN_COUNT * 31 * 4) as ESTIMATED_PRICE_LOAD
FROM (
    SELECT 
        rt.HOTEL_CODE, hm.HOTEL_NAME_EN,
        COUNT(DISTINCT rt.VENDOR_ROOM_TYPE_SEQ) as ROOM_TYPE_COUNT,
        COUNT(DISTINCT rp.ROOM_PLAN_SEQ) as ROOM_PLAN_COUNT,
        COUNT(DISTINCT hc.HOTEL_CONTRACT_SEQ) as CONTRACT_COUNT
    FROM VD_VENDOR_ROOM_TYPE rt
    INNER JOIN VD_VENDOR_ROOM_PLAN rp ON rp.VENDOR_ROOM_TYPE_SEQ = rt.VENDOR_ROOM_TYPE_SEQ
    INNER JOIN VD_VENDOR_HOTEL_CONTRACT hc ON hc.HOTEL_CONTRACT_SEQ = rp.HOTEL_CONTRACT_SEQ
    LEFT JOIN HO_HOTEL_MASTER hm ON hm.HOTEL_CODE = rt.HOTEL_CODE
    GROUP BY rt.HOTEL_CODE, hm.HOTEL_NAME_EN
    ORDER BY ROOM_PLAN_COUNT DESC
    LIMIT 25
) stats
LEFT JOIN (
    SELECT rt2.HOTEL_CODE, COUNT(DISTINCT pp.PLAN_PROMOTION_SEQ) as PROMOTION_COUNT
    FROM VD_VENDOR_PLAN_PROMOTION pp
    INNER JOIN VD_VENDOR_ROOM_TYPE rt2 ON rt2.VENDOR_ROOM_TYPE_SEQ = pp.VENDOR_ROOM_TYPE_SEQ
    WHERE pp.SALES_STOP_YN = 'N'
    GROUP BY rt2.HOTEL_CODE
) promo ON promo.HOTEL_CODE = stats.HOTEL_CODE
LEFT JOIN (
    SELECT HOTEL_CODE, COUNT(*) as DAILY_PRICE_COUNT
    FROM VD_VENDOR_ROOM_DAILY_PRICE
    WHERE APPLIED_DATE BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND DATE_ADD(CURDATE(), INTERVAL 2 MONTH)
    GROUP BY HOTEL_CODE
) dp ON dp.HOTEL_CODE = stats.HOTEL_CODE
ORDER BY ESTIMATED_PROMO_DAILY_LOAD DESC
LIMIT 20;

---

🔴 Top 20 Hotels có dữ liệu NẶNG nhất¶

#	Hotel Code	Hotel Name	Room Types	Room Plans	Promotions	Daily Prices	Est. Load
1	251940	Sunshine City Prince Hotel	26	1,053	747	0	73.1M ⚠️
2	130959	Sotetsu Grand Fresa Osaka - Namba	35	665	321	16,554	19.8M
3	102887	Namba Oriental Hotel	18	889	215	10,722	17.7M
4	560495	Citadines Namba Osaka	20	664	217	0	13.4M
5	209463	HakoBA Hakodate by THE SHARE HOTELS	11	816	128	0	9.7M
6	733770	Hotel Monterey Okinawa Spa & Resort	11	486	178	0	8.0M
7	142240	Hotel Wing International Select Hakata	10	487	164	0	7.4M
8	838105	Citadines Harbour Front Yokohama	12	419	182	0	7.0M
9	470259	Oriental Hotel Kyoto Rokujo	16	374	201	1,512	6.9M
10	437838	Livemax Resort Kusatsuonsen	19	367	168	0	5.7M
11	862798	Tmark City Hotel Sapporo	10	424	145	0	5.7M
12	445266	Citadines Karasuma-Gojo Kyoto	11	411	126	0	4.8M
13	586543	Hotel Nikko Saigon	6	431	105	0	4.2M
14	662313	Star Gate Hotel Kansai Airport	20	406	64	1,776	2.4M
15	327376	Sotetsu Grand Fresa Tokyo - Bay Ariake	38	1,136	0	2,950	140K
16	246896	Oriental Hotel Hiroshima	25	618	0	0	76K
17	671165	Sotetsu Fresa Inn Yokohama Sakuragicho	20	499	0	0	61K
18	506309	Sotetsu Fresa Inn Kawasaki-Higashiguchi	12	486	0	0	60K
19	148117	Hotel Nikko Oita Oasis Tower	15	421	0	0	52K
20	546278	Miyako City Osaka Hommachi	20	427	17	2,769	675K

---

📈 So sánh với Hotel 155693 (gây crash)¶

Metric	Hotel 155693 (Keio Plaza)	Hotel 251940 (Top 1)	Nguy cơ
Room Types	54	26	-
Room Plans	354	1,053	3x
Promotions	428	747	1.7x
Est. Promo Daily Load	~14M	~73M	5x ⚠️

---

⚠️ Hotels có nguy cơ crash CAO¶

1. 251940 - Sunshine City Prince Hotel: 73M estimated load - Gấp 5x hotel 155693!
2. 130959 - Sotetsu Grand Fresa Osaka - Namba: 19.8M load + 16K daily prices
3. 102887 - Namba Oriental Hotel: 17.7M load + 10K daily prices
4. 560495 - Citadines Namba Osaka: 13.4M load, 664 room plans
5. 327376 - Sotetsu Grand Fresa Tokyo - Bay Ariake: 1,136 room plans - nhiều nhất!

---

💡 Khuyến nghị¶

Các hotel này nên được test với Rate Allotment V3 để đảm bảo không bị crash/timeout:

• Hotel 251940 - Ưu tiên cao nhất, estimated load gấp 5 lần hotel đã gây crash
• Hotel 327376 - Có số room plans nhiều nhất (1,136)

📊 Top 20 Hotels có Rate & Allotment nhiều nhất (3 tháng gần nhất)¶

---

🔍 SQL Query¶

SELECT 
    rt.HOTEL_CODE,
    hm.HOTEL_NAME_EN,
    COUNT(DISTINCT rt.VENDOR_ROOM_TYPE_SEQ) as ROOM_TYPE_COUNT,
    COUNT(DISTINCT rp.ROOM_PLAN_SEQ) as ROOM_PLAN_COUNT,
    IFNULL(dp.DAILY_PRICE_COUNT, 0) as RATE_COUNT,
    IFNULL(al.ALLOTMENT_COUNT, 0) as ALLOTMENT_COUNT,
    (IFNULL(dp.DAILY_PRICE_COUNT, 0) + IFNULL(al.ALLOTMENT_COUNT, 0)) as TOTAL_RATE_ALLOTMENT
FROM VD_VENDOR_ROOM_TYPE rt
INNER JOIN VD_VENDOR_ROOM_PLAN rp ON rp.VENDOR_ROOM_TYPE_SEQ = rt.VENDOR_ROOM_TYPE_SEQ
LEFT JOIN HO_HOTEL_MASTER hm ON hm.HOTEL_CODE = rt.HOTEL_CODE
LEFT JOIN (
    SELECT HOTEL_CODE, COUNT(*) as DAILY_PRICE_COUNT
    FROM VD_VENDOR_ROOM_DAILY_PRICE
    WHERE APPLIED_DATE BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
                           AND DATE_ADD(CURDATE(), INTERVAL 2 MONTH)
    GROUP BY HOTEL_CODE
) dp ON dp.HOTEL_CODE = rt.HOTEL_CODE
LEFT JOIN (
    SELECT rt2.HOTEL_CODE, COUNT(*) as ALLOTMENT_COUNT
    FROM VD_VENDOR_ROOM_ALLOTMENT al
    INNER JOIN VD_VENDOR_ROOM_TYPE rt2 ON rt2.VENDOR_ROOM_TYPE_SEQ = al.VENDOR_ROOM_TYPE_SEQ
    WHERE al.APPLIED_DATE BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
                              AND DATE_ADD(CURDATE(), INTERVAL 2 MONTH)
    GROUP BY rt2.HOTEL_CODE
) al ON al.HOTEL_CODE = rt.HOTEL_CODE
GROUP BY rt.HOTEL_CODE, hm.HOTEL_NAME_EN, dp.DAILY_PRICE_COUNT, al.ALLOTMENT_COUNT
ORDER BY TOTAL_RATE_ALLOTMENT DESC
LIMIT 20;

---

📋 Danh sách Top 20 Hotels¶

#	Hotel Code	Hotel Name	Room Types	Room Plans	Rate Count	Allotment	Total
1	130959	Sotetsu Grand Fresa Osaka - Namba	35	665	16,554	2,883	19,437 ⚠️
2	102887	Namba Oriental Hotel	18	889	10,722	1,488	12,210
3	139237	The Prince Park Tower Tokyo	34	301	9,486	76	9,562
4	188088	Hotel La'gent Plaza Hakodate Hokuto	8	114	7,940	78	8,018
5	438410	Quintessa Hotel Sapporo Susukino 63	6	72	7,790	124	7,914
6	693407	Hotel Oriental Express Fukuoka Tenjin	16	301	6,703	368	7,071
7	768491	KOKO HOTEL Osaka Shinsaibashi	14	102	5,166	154	5,320
8	437833	Miyako Hotel Amagasaki	14	181	4,541	84	4,625
9	785448	KOKO HOTEL Residence Asakusa Tawaramachi	7	33	4,440	158	4,598
10	419874	Lavalse Hotel	14	59	2,697	1,302	3,999
11	693092	The Yokohama Bay Hotel Tokyu	15	205	3,553	253	3,806
12	232020	Quintessa Hotel Kagoshima Tenmonkan	4	59	3,606	84	3,690
13	675050	Quintessa Hotel Fukuoka Tenjin Comic & Books	3	55	3,602	70	3,672
14	857634	Ramada Plaza Jeju Ocean Front	19	69	3,234	429	3,663
15	546278	Miyako City Osaka Hommachi	20	427	2,769	868	3,637
16	327376	Sotetsu Grand Fresa Tokyo - Bay Ariake	38	1,136	2,950	637	3,587
17	908119	Quintessa Hotel Kokura Comic&Books	3	71	3,219	110	3,329
18	605837	Lahan Select Gyeongju	9	22	2,418	837	3,255
19	314853	Mercure Hotel Sapporo	8	78	3,152	32	3,184
20	396280	Rembrandt Hotel Ebina	6	105	3,015	24	3,039

---

📈 Phân tích¶

Metric	Hotel	Giá trị
Hotel nặng nhất	130959 - Sotetsu Grand Fresa Osaka - Namba	19,437 records
Nhiều Room Plans nhất	327376 - Sotetsu Grand Fresa Tokyo	1,136 plans
Nhiều Rate nhất	130959 - Sotetsu Grand Fresa Osaka	16,554 rates
Nhiều Allotment nhất	130959 - Sotetsu Grand Fresa Osaka	2,883 allotments

---

⚠️ Hotels cần lưu ý khi test Rate Allotment V3¶

Hotel Code	Hotel Name	Lý do
130959	Sotetsu Grand Fresa Osaka - Namba	Top 1 cả Rate lẫn Allotment, xuất hiện nhiều lần trong các danh sách nặng
102887	Namba Oriental Hotel	Top 2 tổng hợp + 889 room plans
327376	Sotetsu Grand Fresa Tokyo - Bay Ariake	Room Plans nhiều nhất (1,136) dù rate/allotment ít hơn
419874	Lavalse Hotel	Allotment cao bất thường (1,302) so với rate (2,697)

---

Dữ liệu lấy ngày: 2025-12-24