from DepositAPI.libs.db.RDbSql import DbMySql
from DepositAPI.model.BaseModel import BaseModel


class TruckReport(BaseModel):
    def get_report(self, start_time, end_time, license_plate=None):
        db = DbMySql()
        where_time = """o.OrderDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
            AND o.OrderDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)"""
        common_params = {'startTime': start_time, 'endTime': end_time}
        params = dict(common_params)
        filter_plate = ""
        if license_plate is not None and license_plate != "":
            filter_plate = " AND o.LicensePlate = :license_plate"
            params['license_plate'] = license_plate
        detail_sql = f"""SELECT 
                o.ID,
                o.OrderCode,
                o.LicensePlate,
                DATE_FORMAT(CONVERT_TZ(o.OrderDate, '+00:00', '+07:00'), '%Y-%m-%dT%H:%i:%s.000Z') AS OrderDate,
                DATE_FORMAT(CONVERT_TZ(o.StoreDate, '+00:00', '+07:00'), '%Y-%m-%dT%H:%i:%s.000Z') AS StoreDate,
                COALESCE(o.NumberOfProduct, 0) AS NumberOfProduct,
                COALESCE(o.NumberOfPack, 0) AS NumberOfPack,
                COALESCE(o.Weight, 0) AS Weight,
                COALESCE(o.Volume, 0) AS Volume,
                COALESCE(o.Revenue, 0) AS Revenue
            FROM tbl_truck AS o
            WHERE {where_time}{filter_plate}
            ORDER BY o.OrderDate DESC"""
        detail_rows = db.query(query=detail_sql, params=params)
        detail_rows = [dict(row) for row in detail_rows]
        chart_sql = f"""SELECT 
                DATE_FORMAT(CONVERT_TZ(o.OrderDate, '+00:00', '+07:00'), '%Y-%m-%d') AS ReportDate,
                COUNT(o.ID) AS TotalTruck,
                SUM(COALESCE(o.NumberOfProduct, 0)) AS TotalOrder,
                SUM(COALESCE(o.NumberOfPack, 0)) AS TotalPack,
                SUM(COALESCE(o.Weight, 0)) AS TotalWeight,
                SUM(COALESCE(o.Volume, 0)) AS TotalVolume,
                SUM(COALESCE(o.Revenue, 0)) AS TotalRevenue
            FROM tbl_truck AS o
            WHERE {where_time}{filter_plate}
            GROUP BY ReportDate
            ORDER BY ReportDate"""
        chart_rows = db.query(query=chart_sql, params=params)
        chart_rows = [dict(row) for row in chart_rows]
        plate_sql = f"""SELECT DISTINCT o.LicensePlate
            FROM tbl_truck AS o
            WHERE {where_time}
            ORDER BY o.LicensePlate"""
        plate_rows = db.query(query=plate_sql, params=common_params)
        plate_rows = [dict(row) for row in plate_rows]
        summary = {
            'totalTruck': len(detail_rows),
            'totalOrder': sum(row.get('NumberOfProduct') or 0 for row in detail_rows),
            'totalPack': sum(row.get('NumberOfPack') or 0 for row in detail_rows),
            'totalWeight': float(sum(row.get('Weight') or 0 for row in detail_rows)),
            'totalVolume': float(sum(row.get('Volume') or 0 for row in detail_rows)),
            'totalRevenue': float(sum(row.get('Revenue') or 0 for row in detail_rows))
        }
        plates = [row['LicensePlate'] for row in plate_rows if row.get('LicensePlate')]
        return {
            'chart': chart_rows,
            'items': detail_rows,
            'plates': plates,
            'summary': summary
        }

    def get_report_by_status(self, start_time, end_time):
        db = DbMySql()
        params = {'startTime': start_time, 'endTime': end_time}
        
        # Query for detail items grouped by status only
        detail_sql = """SELECT 
                StatusName,
                StatusValue,
                COUNT(*) AS TruckCount,
                SUM(COALESCE(NumberOfProduct, 0)) AS NumberOfProduct,
                SUM(COALESCE(NumberOfPack, 0)) AS NumberOfPack,
                SUM(COALESCE(Weight, 0)) AS Weight,
                SUM(COALESCE(Volume, 0)) AS Volume
            FROM (
                SELECT 
                    'Xếp xe' AS StatusName,
                    1 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE OrderDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND OrderDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
                
                UNION ALL
                
                SELECT 
                    'Kiểm hóa' AS StatusName,
                    3 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE CheckDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND CheckDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
                
                UNION ALL
                
                SELECT 
                    'Chờ thông quan VN' AS StatusName,
                    4 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE WaitTranferDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND WaitTranferDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
                
                UNION ALL
                
                SELECT 
                    'Nhập kho VN' AS StatusName,
                    5 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE StoreDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND StoreDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
            ) AS combined
            GROUP BY StatusName, StatusValue
            ORDER BY StatusValue"""
        
        detail_rows = db.query(query=detail_sql, params=params)
        detail_rows = [dict(row) for row in detail_rows]
        
        # Query for chart data
        chart_sql = """SELECT 
                StatusName,
                StatusValue,
                COUNT(*) AS TruckCount,
                SUM(COALESCE(NumberOfProduct, 0)) AS TotalOrder,
                SUM(COALESCE(NumberOfPack, 0)) AS TotalPack,
                SUM(COALESCE(Weight, 0)) AS TotalWeight,
                SUM(COALESCE(Volume, 0)) AS TotalVolume
            FROM (
                SELECT 
                    'Xếp xe' AS StatusName,
                    1 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE OrderDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND OrderDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
                
                UNION ALL
                
                SELECT 
                    'Kiểm hóa' AS StatusName,
                    3 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE CheckDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND CheckDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
                
                UNION ALL
                
                SELECT 
                    'Chờ thông quan VN' AS StatusName,
                    4 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE WaitTranferDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND WaitTranferDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
                
                UNION ALL
                
                SELECT 
                    'Nhập kho VN' AS StatusName,
                    5 AS StatusValue,
                    NumberOfProduct, NumberOfPack, Weight, Volume
                FROM tbl_truck
                WHERE StoreDate >= CONVERT_TZ(:startTime, '+07:00', '+00:00')
                    AND StoreDate <= DATE_ADD(CONVERT_TZ(:endTime, '+07:00', '+00:00'), INTERVAL 86399 SECOND)
            ) AS combined
            GROUP BY StatusName, StatusValue
            ORDER BY StatusValue"""
        
        chart_rows = db.query(query=chart_sql, params=params)
        chart_rows = [dict(row) for row in chart_rows]
        
        # Calculate summary
        summary = {
            'totalTruck': sum(row.get('TruckCount') or 0 for row in detail_rows),
            'totalOrder': sum(row.get('NumberOfProduct') or 0 for row in detail_rows),
            'totalPack': sum(row.get('NumberOfPack') or 0 for row in detail_rows),
            'totalWeight': float(sum(row.get('Weight') or 0 for row in detail_rows)),
            'totalVolume': float(sum(row.get('Volume') or 0 for row in detail_rows)),
        }
        
        return {
            'chart': chart_rows,
            'items': detail_rows,
            'summary': summary
        }