aiData/Model/StationStatus.py

from sqlalchemy import text
from datetime import datetime
import json

class StationStatus:
    def __init__(self):
        pass

    async def save(self, session, id, station_hash, total_piles, free_piles, piles_detail_json, current_price, pro_price=None, market_price=None, parking_info=None, distance=None, valid_start_time=None):
        if valid_start_time is None:
            valid_start_time = datetime.now()
        
        # Ensure json is string with consistent formatting
        piles_json_str = None
        if piles_detail_json is not None:
            if isinstance(piles_detail_json, (dict, list)):
                piles_json_str = json.dumps(piles_detail_json, ensure_ascii=False, sort_keys=True)
            else:
                piles_json_str = piles_detail_json

        # 1. Check current record
        select_sql = """
        SELECT total_piles, free_piles, piles_detail_json, current_price, pro_price, market_price, parking_info, distance 
        FROM t_station_status_scd 
        WHERE station_hash = :station_hash AND is_current = 1
        """
        try:
            result = await session.execute(text(select_sql), {"station_hash": station_hash})
            current_row = result.fetchone()
        except Exception as e:
            # Check if it's a "column not found" error
            if "Unknown column 'market_price'" in str(e) or "no such column: market_price" in str(e):
                current_row = None
            else:
                raise e

        if current_row:
            # Check if changed
            row_total = current_row.total_piles
            row_free = current_row.free_piles
            row_json = current_row.piles_detail_json
            row_price = current_row.current_price
            row_pro_price = current_row.pro_price
            row_market_price = getattr(current_row, 'market_price', None)
            row_parking = getattr(current_row, 'parking_info', None)
            row_distance = getattr(current_row, 'distance', None)
            
            # Normalize row_json for comparison
            if row_json:
                try:
                    if isinstance(row_json, str):
                        row_json_obj = json.loads(row_json)
                        row_json = json.dumps(row_json_obj, ensure_ascii=False, sort_keys=True)
                except Exception:
                    pass

            # Convert prices for comparison
            if row_price is not None: row_price = float(row_price)
            if row_pro_price is not None: row_pro_price = float(row_pro_price)
            if row_market_price is not None: row_market_price = float(row_market_price)
            
            # Simple comparison
            is_same = (
                row_total == total_piles and
                row_free == free_piles and
                row_price == current_price and
                row_pro_price == pro_price and
                row_market_price == market_price and
                row_json == piles_json_str and
                row_parking == parking_info and
                row_distance == distance
            )
            
            if is_same:
                return

            # Expire old record
            update_sql = """
            UPDATE t_station_status_scd 
            SET is_current = 0, valid_end_time = :valid_start_time 
            WHERE station_hash = :station_hash AND is_current = 1
            """
            await session.execute(text(update_sql), {
                "valid_start_time": valid_start_time,
                "station_hash": station_hash
            })

        # 2. Insert new record
        sql = """
        INSERT INTO t_station_status_scd
        (id, station_hash, total_piles, free_piles, piles_detail_json, current_price, pro_price, market_price, parking_info, distance, valid_start_time, is_current)
        VALUES
        (:id, :station_hash, :total_piles, :free_piles, :piles_detail_json, :current_price, :pro_price, :market_price, :parking_info, :distance, :valid_start_time, 1)
        """
        await session.execute(text(sql), {
            "id": id,
            "station_hash": station_hash,
            "total_piles": total_piles,
            "free_piles": free_piles,
            "piles_detail_json": piles_json_str,
            "current_price": current_price,
            "pro_price": pro_price,
            "market_price": market_price,
            "parking_info": parking_info,
            "distance": distance,
            "valid_start_time": valid_start_time
        })