import requests
from sqlalchemy import text
from datetime import datetime

from app.utils.prompts import gen_query_prompt, get_query_prompt
from app.config.application import settings
from app.schemas.chat_schema import regenQuery
from app.model.sql_connection import get_session

from langchain_openai.chat_models.base import ChatOpenAI

async def gen_report(standaloneQuestion:str, reportName:str = None, fromDate:str = None, toDate:str = None, callbacks = None, **kwargs):        
    
    max_retries = 3
    attempt = 0
    sqlQuery = ''
    
    if reportName == "lodging_statistics":
        data = log_report(fromDate, toDate)
    elif reportName == "advanced_deposits":
        data = advanced_deposits(fromDate, toDate)
    elif reportName == "sales_by_employee":
        data = sales_by_employee(fromDate, toDate)
    elif reportName == "tax":
        data = sales_tax(fromDate, toDate)
    elif reportName == "departure_invoice_report":
        data = departure_report(toDate)
    elif reportName == "house_keeping":
        data = house_keeping(toDate)
    else:
        sqlQuery = getQuery(standaloneQuestion,callbacks)
        while attempt < max_retries:
            result = execute_raw_query(sqlQuery)
            
            # Check if the query was successful
            if result['status']:
                data = result['data']
                break
            
            res = genQuery(query=sqlQuery, error=result['message'],callbacks=callbacks)
            if res:
                sqlQuery = res
            
            # Increment attempt counter if it failed
            attempt += 1
        
    if not data:
        while attempt < max_retries:
            result = execute_raw_query(sqlQuery)
            
            # Check if the query was successful
            if result['status']:
                data = result['data'] if result['data'] and len(result['data']) else None
                break
            
            res = genQuery(query=sqlQuery, error=result['message'],callbacks=callbacks)
            if res:
                sqlQuery = res
            
            # Increment attempt counter if it failed
            attempt += 1
    
    return {'reportName': reportName, 'sqlQuery':sqlQuery, 'fromDate':fromDate, 'toDate':toDate,  'type': 'genReportTool', 'data': data}

def getQuery(question:str, callbacks=None):
    llm = ChatOpenAI(api_key=settings.OPENAI_KEY,model="gpt-4o-2024-08-06")
    structuredLLM = llm.with_structured_output(regenQuery,strict=True)
    chain = get_query_prompt() | structuredLLM
    with open("/var/www/html/chat-with-data/test/fullSchema.txt", 'r') as file:
        schema = file.read()
    result:regenQuery = chain.invoke(input={'schema': schema, 'question': question, "date": datetime.today().strftime('%Y-%m-%d')},config={"callbacks":callbacks, "run_name":"getQuery"})
    return result.query

# SQL fucntions
def serialize_value(value):
    if isinstance(value, datetime):
        return value.isoformat()
    return value

def execute_raw_query(sql_query):
    
    # Get the session
    session = get_session()

    try:
        # Wrap the SQL query in text()
        result_proxy = session.execute(text(sql_query))

        # Fetch all rows from the result
        result_set = result_proxy.fetchall()

        # Get column names from the result metadata
        column_names = result_proxy.keys()

        # Convert the result to a list of dictionaries (JSON-like format)
        result_as_dict = [
            {column: serialize_value(value) for column, value in zip(column_names, row)}
            for row in result_set
        ]

        # Return the result as a JSON string
        return {'status': True, 'data': result_as_dict, 'message': ''}

    except Exception as e:
        # Handle any exceptions that occur during the query execution
        print(f"An error occurred: {e}")
        return {'status': False, 'data': None, 'message': e}

    finally:
        # Close the session
        session.close()

def genQuery(query:str, error:str, callbacks = None):
    
    try:
        print("retring...")
        schema = ''
        llm = ChatOpenAI(api_key=settings.OPENAI_KEY,model="gpt-4o-2024-08-06")
        structuredLLM = llm.with_structured_output(regenQuery,strict=True)
        
        chain = gen_query_prompt() | structuredLLM
        with open("/var/www/html/chat-with-data/test/fullSchema.txt", 'r') as file:
            schema = file.read()
        
        result:regenQuery = chain.invoke(input={'schema': schema,'query': query,'error': error, "date": datetime.today().strftime('%Y-%m-%d')},config={"callbacks":callbacks, "run_name":"reGenQuery"})
        
        return result.query
    
    except Exception as e:
        print(e)
        return None


# API function for api calls to get report.

def log_report(start, end):
    try:
        data = {
            "start": start,
            "end": end,
            "mode": "listing",
            "selected_hotel": [],
            "factReport": "new"
        }
        headers = {
            "Hotelid":"121", 
            "Current_version":"12", 
            "Authorization":settings.TOKEN
        }
        response = requests.post(
            "https://mtstaging.lodgingsystem.com/ls-api/Factreports/getlodgingStatsReport",
            json=data,
            headers=headers
            )
        if not response.ok:
            return None
        return parse_data(response.json())
    except Exception as e:
        print(e)
        return None

def advanced_deposits(fromDate:str, toDate:str):
    try:
        data = {
            "startDate": fromDate+"T00:00:00.000Z",
            "endDate": toDate+"T00:00:00.000Z",
            "requestFor": "reservation",
            "mode": "listing",
            "selected_hotel": [],
        }
        headers = {
            "Hotelid":"121", 
            "Current_version":"12", 
            "Authorization":settings.TOKEN
        }
        response = requests.post(
            "https://mtstaging.lodgingsystem.com/ls-api/Reports/getCustomerAdvancedReports",
            json=data,
            headers=headers
        )
        if not response.ok:
            return None
        return parse_advanced_deposits(response.json())
    except Exception as e:
        print(e)
        return None

def sales_by_employee(fromDate:str, toDate:str):
    try:
        data = {
            "from": fromDate,
            "to": toDate,
            "requestFor": "all",
            "selectedHotels": [],
        }
        headers = {
            "Hotelid":"121", 
            "Current_version":"12", 
            "Authorization":settings.TOKEN
        }
        response = requests.post(
            "https://mtstaging.lodgingsystem.com/ls-api/Reports/getReservationSalesByEmployee",
            json=data,
            headers=headers
            )
        if not response.ok:
            return None
        return parse_sales_by_agents(response.json())
    except Exception as e:
        print(e)
        return None

def sales_tax(fromDate:str, toDate:str):
    try:
        data = {
            "start": fromDate+"T00:00:00.000Z",
            "end": toDate+"T00:00:00.000Z",
            "mode": "listing",
            "reportFor":"hotel",
            "selected_hotel": []
        }
        headers = {
            "Hotelid":"121", 
            "Current_version":"12", 
            "Authorization":settings.TOKEN
        }
        response = requests.post(
            "https://mtstaging.lodgingsystem.com/ls-api/factreports/getFactsTaxReport",
            json=data,
            headers=headers
            )
        if not response.ok:
            return None
        return parse_tax_report(response.json(),fromDate,toDate)
    except Exception as e:
        print(e)
        return None

def departure_report(date:str):
    try:
        data = {
            "departureDate": date+"T00:00:00.000Z",
            "reportFor":"hotel",
            "selectedHotels": []
        }
        headers = {
            "Hotelid":"121", 
            "Current_version":"12", 
            "Authorization":settings.TOKEN
        }
        response = requests.post(
            "https://mtstaging.lodgingsystem.com/ls-api/Reports/DepartureReports",
            json=data,
            headers=headers
            )
        if not response.ok:
            return None
        return parse_departure_report(response.json())
    except Exception as e:
        print(e)
        return None

def house_keeping(date:str):
    try:
        data = {
            "date": date+"T00:00:00.000Z",
            "reportFor":"reservation",
            "selectedHotels": []
        }
        headers = {
            "Hotelid":"121", 
            "Current_version":"12", 
            "Authorization":settings.TOKEN
        }
        response = requests.post(
            "https://mtstaging.lodgingsystem.com/ls-api/Reports/getAllCurrentReservations",
            json=data,
            headers=headers
            )
        if not response.ok:
            return None
        return parse_house_keeping(response.json())
    except Exception as e:
        print(e)
        return None

# Parser functions For API's data

def parse_data(data):
    # Extract columns and result
    columns = data['columns']
    results = data['result']
    
    # Create a list of dictionaries, where each dictionary is a row with key-value pairs
    parsed_results = []
    for result in results:
        row = {}
        result_values = list(result.values())  # Get all values from the result
        # Loop through columns and assign values or empty strings if values are missing
        for i, column in enumerate(columns):
            col = column.replace('.','')
            if i < len(result_values):
                row[col] = result_values[i]
            else:
                row[col] = ""  # If there are more columns than values, add an empty string
        # If there are more values than columns, ignore the extra values
        parsed_results.append(row)
    
    return parsed_results

def parse_sales_by_agents(data):
    # Initialize a dictionary to store sales by agent names
    sales_by_agent = {}

    # Check if data is successful and contains 'salesByAgents'
    if data.get("success") and "salesByAgents" in data:
        sales_by_agents = data["salesByAgents"]

        # Loop through each agent's data
        for agent_id, agent_info in sales_by_agents.items():
            agent_name = agent_info.get("name")
            reservations = agent_info.get("reservations", [])

            # Ensure the agent name exists in the dictionary
            if agent_name not in sales_by_agent:
                sales_by_agent[agent_name] = []

            # Loop through each reservation for the current agent
            for reservation in reservations:
                # Parse reservation details
                reservation_entry = {
                    "Itinerary": reservation.get("itinerary_number", ""),
                    "Confirmation": reservation.get("confirmation_number", ""),
                    "Type": reservation.get("type_of_reservation", ""),
                    "Product": reservation.get("product_name", ""),
                    "From": reservation.get("room_stay_from", ""),
                    "To": reservation.get("room_stay_to", ""),
                    "Created": reservation.get("reservation_created_date", ""),
                    "Status": reservation.get("room_stay_status", ""),
                    "Customer": reservation.get("customer_name", ""),
                    "Subtotal": float(reservation.get("subtotal", "0.00")),
                    "Grand Total": float(reservation.get("grand_total", "0.00")),
                    "Paid": float(reservation.get("paid_amount", "0.00")),
                    "Remaining": float(reservation.get("remaining_amount", "0.00")),
                }

                # Append the reservation entry to the agent's sales list
                sales_by_agent[agent_name].append(reservation_entry)

    return sales_by_agent

def parse_advanced_deposits(data:dict):
    records = data.get('data',{}).get('records',[])
    
    parsedData = []
     
    if not len(records):
        return None
    
    for res in records:
        
        parsedData.append({
            "Itinerary": res.get('itinerary_number','--'),
            "Confirmation": res.get('confirmation_number','-'),
            "Created On": res.get('reservation_created_date','--'),
            "Check In": res.get('room_stay_from','--'),
            "Check Out": res.get('room_stay_to','--'),
            "Product": res.get('product_name','--'),
            "Customer": res.get('customer_name','--'),
            "Gross Total": res.get('total_amount','--'),
            "Paid": res.get('total_paid_amount','--'),
            "Remaining": res.get('total_remaining_amount','--'),
            "Room Status": res.get('room_stay_status','--'),
            "Advance Collected": res.get('totalAdvanceCollected','--'),
            "Advance Used": res.get('totalAdvanceUsed','--'),
        })
    
    parsedData.append({
            "Itinerary": "Total",
            "Confirmation": "",
            "Created On": "",
            "Check In": "",
            "Check Out": "",
            "Product": "",
            "Customer": "",
            "Gross Total": "",
            "Paid": "",
            "Remaining": "",
            "Room Status": "",
            "Advance Collected": "",
            "Advance Collected": data.get('data',{}).get('advance_used',''),
            "Advance Used": data.get('data',{}).get('advance_outstanding',''),
        })
          
    return parsedData

def format_date(date_str):
    # Convert "YYYY-MM-DD" to "DD Month YYYY"
    date_obj = datetime.strptime(date_str, "%Y-%m-%d")
    return date_obj.strftime("%d %B %Y")

def parse_tax_report(data, fromDate, toDate):
    result = {}
    fromDate = format_date(fromDate)
    toDate = format_date(toDate)
    data = data.get('data',{})
    # Flatten reservation details
    result["Room Details"] = []
    for resv in data.get("resvDetail", []):
        flat_resv = {
            "Id": resv.get("product_id", '--'),
            "Room Name": resv.get("product_name", "--"),
            "Before Tax": resv.get("subTotal", '--'),
            "Tax": resv.get("tax", '--'),
            "After Tax": resv.get("grandTotal", '--')
        }
        result["Room Details"].append(flat_resv)

    # Flatten overall sale details
    result[f"Rooms Sales for {fromDate} - {toDate}"] = []
    overall_sale = data.get("overAllSale", {})
    for key, value in overall_sale.items():
        if isinstance(value, dict):
            flat_sale = {
                "Type": value.get("type", '--'),
                "Before Tax": value.get("subTotal", '--'),
                "Tax": value.get("tax", '--'),
                "Amount": value.get("grandTotal", '--'),
            }
            result[f"Rooms Sales for {fromDate} - {toDate}"].append(flat_sale)

    # Flatten taxes
    result[f"Rooms Taxes for {fromDate} - {toDate}"] = []
    taxes = data.get("taxes", {})
    for key, value in taxes.items():
        tax = {
            "tax_name": value.get("name", '--'),
            "tax_percentage": value.get("percentage", '--'),
            "tax_amount": value.get("taxAmount", '--'),
            "category": key  # Add the category key to differentiate tax types
        }
        result[f"Rooms Taxes for {fromDate} - {toDate}"].append(tax)

    # Flatten statistics
    result[f"Rooms Occupancy Report for {fromDate} - {toDate}"] = []
    stats = data.get("statistics", {})
    for key, value in stats.items():
        stat = {
            "Type": value.get("type", '--'),
            "Amount": str(value.get("value", '--')),
        }
        result[f"Rooms Occupancy Report for {fromDate} - {toDate}"].append(stat)

    # Flatten payment details
    result[f"Rooms Payments And Refunds for {fromDate} - {toDate}"] = []
    payments = data.get("paymentDetails", {})
    for key, value in payments.items():
        payment = {
            "Payment Method": key,
            "Payment": value.get("paid", '--'),
            "Refund": value.get("refund", '--'),
            "Total": value.get("total", '--'),
        }
        result[f"Rooms Payments And Refunds for {fromDate} - {toDate}"].append(payment)

    # Add gift card and advance deposit details
    result[f"Gift Certificates for {fromDate} - {toDate}"] = [
    {
        "Type": "Gift Certificate used",
        "Amount": data.get("giftCard", {}).get("used", '--'),
    },
    {
        "Type": "Gift Certificate outstanding",
        "Amount": data.get("giftCard", {}).get("outstanding", '--'),
    }]

    result[f"Rooms Advance Deposit Liabilities for {fromDate} - {toDate}"] = [{
        "Type": "Advance Deposit Liabilities",
        "Amount": data.get("advDeposit", '--')
    }]

    return result

def parse_house_keeping(data:dict):
    parsed_data = {}
    keyMap = {
        'arrivals': 'Arrivals',
        'departure': 'Departure',
        'roomMove':'Room Moves',
        'stayovers':'Stayovers',
    }
    for key, reservations in data.items():
        if not isinstance(reservations,list) or not len(reservations):
            continue
        parsed_data[keyMap.get(key,key)] = []
        for res in reservations:
             parsed_data[keyMap.get(key,key)].append({
                 "Type": res.get('type','--'),
                 "Length": res.get('days','--'),
                 "Unit": res.get('product_name','--'),
                 "Arrival Time": res.get('arrival_time','--'),
                 "Party": res.get('party','--'),
                 "Name": res.get('name','--'),
                 "Special Request": res.get('special_request','--'),
                 "Room Status": "Full Cleaning" if res.get('product_is_clean','--') == "0" else "Clean" if res.get('product_is_clean','--') == "1" else "Light Cleaning" if res.get('product_is_clean','--') == "2" else "--",
             })
    return parsed_data

def parse_departure_report(data):
    data = data.get('data')
    if not data:
        return None
    parsed_data = []
    for res in data:
        parsed_data.append({
            'Itinerary': res.get('itinerary_number','--'),
            'Name': res.get('first_name','')+" "+res.get("last_name",''),
            'Address': res.get('address','--'),            
            'City': res.get('city','--'),            
            'State': res.get('state','--'),            
            'Telephone': res.get('phone_number','--'),            
            'Confirmation': res.get('confirmation_number','--'),
            'Room Stay From': res.get('room_stay_from','--'),
            'Room Stay To': res.get('room_stay_to','--'),
            'Description': ','.join(res.get('rate_plan_ids',[])),
            'Agent': res.get('allPayments',[{}])[0].get('cc_fname','') + res.get('allPayments',[{}])[0].get('cc_lname',''),
            'Credit Card': res.get('cc_type','--'),
            'Party': res.get('party','--'),
            'Charges': res.get('subtotal','--'),
            'Paid': res.get('total_paid_amount','--'),
            'Due': res.get('total_remaining_amount','--'),
            'Tax': res.get('tax_amount','--'),
            'Total': res.get('total_amount','--'),
        }) 
    return parsed_data