from __future__ import annotations import logging from decimal import Decimal from typing import Any from .comparison_market_data import ACTUAL_MARKET_MAPPINGS logger = logging.getLogger("dscons.state_price_sync.comparison") def round_decimal(val: Any, places: int = 2) -> Decimal: if val is None: return Decimal("0.00") try: return Decimal(str(val)).quantize(Decimal("1." + "0" * places)) except Exception: return Decimal("0.00") class StatePriceComparisonMixin: """Mixin for side-by-side comparison between State Price & Market Purchase Invoices.""" def get_price_comparison( self, publish_period: str = "2026-08", region_code: str = "HAI_PHONG_KHU_VUC_2", material_group: str | None = None, ) -> dict[str, Any]: actual_market_mappings = ACTUAL_MARKET_MAPPINGS # Query State Published Prices with self.get_connection() as conn, conn.cursor() as cur: where_clauses = ["publish_period = %s", "region_code = %s"] params = [publish_period, region_code] if material_group: where_clauses.append("material_group = %s") params.append(material_group) cur.execute( f""" SELECT id, publish_period, region_code, material_group, material_code, material_name, specifications, unit, state_unit_price_vnd, document_reference, effective_date, notes FROM erp_state_published_prices WHERE {" AND ".join(where_clauses)} ORDER BY material_group ASC, material_code ASC; """, params, ) state_items = cur.fetchall() # If database is empty for this period, auto-seed and query again if not state_items: self.seed_official_state_prices() return self.get_price_comparison( publish_period, region_code, material_group ) comparison_list = [] profitable_count = 0 loss_risk_count = 0 unmatched_count = 0 total_state_sum = Decimal("0.00") total_market_sum = Decimal("0.00") margin_sum = Decimal("0.00") benchmarked_count = 0 for s in state_items: m_code = s["material_code"] p_state = Decimal(str(s["state_unit_price_vnd"])) total_state_sum += p_state # Lookup actual market benchmark from authentic ground truth m_data = actual_market_mappings.get(m_code) if m_data: p_market = Decimal(str(m_data["market_unit_price_vnd"])) supplier = m_data["supplier_name"] inv_ref = m_data["invoice_ref"] m_date = m_data.get("market_date") m_notes = m_data.get("notes") total_market_sum += p_market benchmarked_count += 1 # Compute variance and profit margin % variance_vnd = p_state - p_market if p_state > 0: profit_margin_pct = round_decimal( (variance_vnd / p_state) * Decimal("100.00"), 2 ) else: profit_margin_pct = Decimal("0.00") margin_sum += profit_margin_pct # Determine risk badge if profit_margin_pct >= Decimal("15.00"): risk_level = "HIGH_PROFIT" risk_label = "LÃI CAO (An toàn)" badge_class = "badge-success" profitable_count += 1 elif profit_margin_pct >= Decimal("0.00"): risk_level = "NORMAL_PROFIT" risk_label = "LÃI VỪA (Kiểm soát)" badge_class = "badge-warning" profitable_count += 1 else: risk_level = "LOSS_RISK" risk_label = "NGUY CƠ LỖ (Vượt giá)" badge_class = "badge-danger" loss_risk_count += 1 comparison_list.append( { "id": str(s["id"]), "material_group": s["material_group"], "material_code": s["material_code"], "material_name": s["material_name"], "specifications": s["specifications"], "unit": s["unit"], "state_unit_price_vnd": float(p_state), "market_unit_price_vnd": float(p_market), "variance_vnd": float(variance_vnd), "profit_margin_percent": float(profit_margin_pct), "risk_level": risk_level, "risk_label": risk_label, "badge_class": badge_class, "document_reference": s["document_reference"], "supplier_name": supplier, "invoice_reference": inv_ref, "market_date": m_date, "notes": m_notes or s.get("notes"), "has_real_invoice": True, } ) else: # Strictly NO fake suppliers or fabricated 95% price discounts unmatched_count += 1 comparison_list.append( { "id": str(s["id"]), "material_group": s["material_group"], "material_code": s["material_code"], "material_name": s["material_name"], "specifications": s["specifications"], "unit": s["unit"], "state_unit_price_vnd": float(p_state), "market_unit_price_vnd": None, "variance_vnd": None, "profit_margin_percent": None, "risk_level": "PENDING_DATA", "risk_label": "CHƯA ĐỐI SOÁT HĐ", "badge_class": "badge-secondary", "document_reference": s["document_reference"], "supplier_name": "Chưa có HĐ mua sắm thực tế", "invoice_reference": "Cần bổ sung HĐ mua sắm / Tra cứu Web", "market_date": None, "notes": "Chưa có dữ liệu hóa đơn đối tác thực tế trong CSDL GDT", "has_real_invoice": False, } ) total_items = len(comparison_list) avg_margin = ( round_decimal(margin_sum / Decimal(str(benchmarked_count)), 2) if benchmarked_count > 0 else Decimal("0.00") ) total_variance = ( total_state_sum - total_market_sum if benchmarked_count > 0 else Decimal("0.00") ) overall_margin = ( round_decimal((total_variance / total_state_sum) * Decimal("100.00"), 2) if total_state_sum > 0 and benchmarked_count > 0 else Decimal("0.00") ) return { "period": publish_period, "region_code": region_code, "region_name": "Khu vực II: Kiến Thụy, Tiên Lãng, An Lão, Thủy Nguyên (Hải Phòng)" if "2" in region_code else "Khu vực I: Trung tâm TP Hải Phòng", "document_title": state_items[0]["document_reference"] if state_items else "Thông báo số 558/TB-SXD ngày 07/08/2026 của Sở Xây dựng TP. Hải Phòng", "kpis": { "total_items": total_items, "profitable_count": profitable_count, "loss_risk_count": loss_risk_count, "average_margin_percent": float(avg_margin), "overall_margin_percent": float(overall_margin), "total_state_value_vnd": float(total_state_sum), "total_market_value_vnd": float(total_market_sum), "total_variance_vnd": float(total_variance), }, "items": comparison_list, }