# from collections import defaultdict
# import pandas as pd
# from app.utils.regex_utils import RegularExpression


# class ComparisonService:
    
#     TRENCH_MAPPING = [
#         {
#             "label": "Marshi 0 to 1.5",
#             "client": "Client_Marshi_Muddy_Slushy_0_to_1_5_total",
#             "sub": None
#         },
#         {
#             "label": "Marshi 1.5 to 3.0",
#             "client": "Client_Marshi_Muddy_Slushy_1_5_to_3_0_total",
#             "sub": None
#         },
#         {
#             "label": "Marshi 3.0 to 4.5",
#             "client": "Client_Marshi_Muddy_Slushy_3_0_to_4_5_total",
#             "sub": None
#         },
#         {
#             "label": "Soft Murum 0 to 1.5",
#             "client": "Client_Soft_Murum_0_to_1_5_total",
#             "sub": "Sub_Soft_Murum_0_to_1_5_total"
#         },
#         {
#             "label": "Soft Murum 1.5 to 3.0",
#             "client": "Client_Soft_Murum_1_5_to_3_0_total",
#             "sub": "Sub_Soft_Murum_1_5_to_3_0_total"
#         },
#         {
#             "label": "Soft Murum 3.0 to 4.5",
#             "client": "Client_Soft_Murum_3_0_to_4_5_total",
#             "sub": "Sub_Soft_Murum_3_0_to_4_5_total"
#         },
#         {
#             "label": "Hard Murum 0 to 1.5",
#             "client": "Client_Hard_Murum_0_to_1_5_total",
#             "sub": "Sub_Hard_Murum_0_to_1_5_total"
#         },
#         {
#             "label": "Hard Murum 1.5+",
#             "client": "Client_Hard_Murum_1_5_to_3_0_total",
#             "sub": "Sub_Hard_Murum_1_5_and_above_total"
#         },
#         {
#             "label": "Soft Rock 0 to 1.5",
#             "client": "Client_Soft_Rock_0_to_1_5_total",
#             "sub": "Sub_Soft_Rock_0_to_1_5_total"
#         },
#         {
#             "label": "Soft Rock 1.5+",
#             "client": "Client_Soft_Rock_1_5_to_3_0_total",
#             "sub": "Sub_Soft_Rock_1_5_and_above_total"
#         },
#         {
#             "label": "Hard Rock 0 to 1.5",
#             "client": "Client_Hard_Rock_0_to_1_5_total",
#             "sub": "Sub_Hard_Rock_0_to_1_5_total"
#         },
#         {
#             "label": "Hard Rock 1.5 to 3.0",
#             "client": "Client_Hard_Rock_1_5_to_3_0_total",
#             "sub": "Sub_Hard_Rock_1_5_to_3_0_total"
#         },
#         {
#             "label": "Hard Rock 3.0 to 4.5",
#             "client": "Client_Hard_Rock_3_0_to_4_5_total",
#             "sub": "Sub_Hard_Rock_3_0_to_4_5_total"
#         },
#         {
#             "label": "Hard Rock 4.5 to 6.0",
#             "client": "Client_Hard_Rock_4_5_to_6_0_total",
#             "sub": "Sub_Hard_Rock_4_5_to_6_0_total"
#         },
#         {
#             "label": "Hard Rock 6.0 to 7.5",
#             "client": "Client_Hard_Rock_6_0_to_7_5_total",
#             "sub": "Sub_Hard_Rock_6_0_to_7_5_total"
#         }
#     ]
    
    

#     @staticmethod
#     def normalize_key(value):
#         if value is None:
#             return ""
#         return str(value).strip().upper()

#     @classmethod
#     def make_lookup(cls, rows, key_field):
#         """
#         Create lookup dictionary using:
#         (Location, MH_NO)
#         """

#         lookup = defaultdict(list)

#         for row in rows:

#             location = cls.normalize_key(row.get("Location"))
#             key = cls.normalize_key(row.get(key_field))

#             if location and key:
#                 lookup[(location, key)].append(row)

#         return lookup

#     @classmethod
#     def build_comparison(cls, client_rows, subcontractor_rows, key_field="MH_NO"):

#         subcontractor_lookup = cls.make_lookup(
#             subcontractor_rows,
#             key_field
#         )

#         used = defaultdict(int)

#         output = []

#         for client in client_rows:

#             location = cls.normalize_key(client.get("Location"))
#             key = cls.normalize_key(client.get(key_field))

#             if not location or not key:
#                 continue

#             rows = subcontractor_lookup.get((location, key))

#             if not rows:
#                 continue

#             index = used[(location, key)]

#             if index >= len(rows):
#                 continue

#             subcontractor = rows[index]

#             used[(location, key)] += 1

#             client_total = sum(
#                 float(v or 0)
#                 for k, v in client.items()
#                 if k.endswith("_total")
#                 or RegularExpression.D_RANGE_PATTERN.match(k)
#                 or RegularExpression.PIPE_MM_PATTERN.match(k)
#             )

#             subcontractor_total = sum(
#                 float(v or 0)
#                 for k, v in subcontractor.items()
#                 if k.endswith("_total")
#                 or RegularExpression.D_RANGE_PATTERN.match(k)
#                 or RegularExpression.PIPE_MM_PATTERN.match(k)
#             )

#             row = {

#                 "Location": location,

#                 key_field: key,

#                 "Client_Total": round(client_total, 2),

#                 "Subcontractor_Total": round(subcontractor_total, 2),

#                 "Difference": round(
#                     client_total - subcontractor_total,
#                     2
#                 )
#             }

#             # Client Columns
#             for column, value in client.items():

#                 if column in [
#                     "id",
#                     "created_at"
#                 ]:
#                     continue

#                 row[f"Client_{column}"] = value

#             # Subcontractor Columns
#             for column, value in subcontractor.items():

#                 if column in [
#                     "id",
#                     "created_at",
#                     "subcontractor_id"
#                 ]:
#                     continue

#                 row[f"Sub_{column}"] = value

#             output.append(row)

#         return pd.DataFrame(output)