import array
from pickle import FALSE
from time import sleep
import mysql.connector
import datetime
import time
import threading
import json
import requests, datetime as dt
#from datetime import timedelta, datetime
API_KEY = "ee75116f5a25275d00b4de9cf87db47a"
url     = "http://data.fixer.io/api/latest"       # or /convert, /timeseries …

payload = {"access_key": API_KEY, "symbols": "USD,ARS"}   # ⚠️ no base param!




stack = []
#connect to mysql


import xmlrpc.client
server = xmlrpc.client.Server('https://wired.wubook.net/xrws/')
token = "wr_337e9f8d-b270-4b8b-a348-0b482f2faca3"
lcode = "1554898338"

rooms_ids = []
mysql_password = ""
mysql_password = "Urw8hMJjveUcTtG9"

def debug_error(error, function_name):
    """Debug MySQL connection errors and determine if reconnection is needed"""
    error_str = str(error)
    print(f"MySQL Error in {function_name}: {error_str}")
    
    # Check if it's a connection-related error
    connection_errors = [
        "Lost connection",
        "MySQL server has gone away", 
        "Can't connect to MySQL server",
        "Connection was killed",
        "connection was aborted",
        "NoneType' object has no attribute 'execute'",  # None cursor
        "NoneType' object has no attribute 'commit'",   # None connection
        "2055",  # Lost connection error code
        "2006",  # MySQL server has gone away error code
        "2013"   # Lost connection during query error code
    ]
    
    needs_reconnect = any(err_pattern in error_str for err_pattern in connection_errors)
    
    if needs_reconnect:
        print(f"🔄 Connection error detected in {function_name}, reconnection needed")
        return True
    else:
        print(f"⚠️ Non-connection error in {function_name}: {error_str}")
        return False

def connect_db():
    """Connect to MySQL database and return cursor and connection"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password=mysql_password,
            database="hotel",
            autocommit=True,
            connection_timeout=30,
            pool_reset_session=True,
            charset='utf8mb4',
            use_unicode=True
        )
        mycursor = mydb.cursor(dictionary=True)
        print("✓ MySQL connection established")
        return mycursor, mydb
    except mysql.connector.Error as e:
        print(f"✗ Error connecting to MySQL: {e}")
        return None, None

def reconnect_db(mydb, mycursor):
    """Reconnect to MySQL database after connection loss"""
    print("🔄 Attempting MySQL reconnection...")
    
    # Close existing connections safely
    try:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()
    except:
        pass
    
    # Wait a moment before reconnecting
    time.sleep(2)
    
    # Create new connection
    new_cursor, new_db = connect_db()
    if new_cursor and new_db:
        print("✓ MySQL reconnection successful")
        return new_cursor, new_db
    else:
        print("✗ MySQL reconnection failed")
        return None, None

def get_rooms_ids(mycursor,mydb):
    query = "select * from hotel.wubook_apartamentos where activo='1' order by avail asc"
    mycursor.execute(query)

    myresult = mycursor.fetchall()
    for result in myresult:
        id_room = str(result['id_room_wubook'])
        rooms_ids.append(id_room)
    mydb.commit()    
   
    

def get_availabilities(mycursor,mydb):
    query = "select * from hotel.wubook where descripcion='availabilities_update' and activo='1' limit 1"
    mycursor.execute(query)

    myresult = mycursor.fetchone()
    #print one of the cols by name
    #check for empty result
    if myresult is None:
        print("no hay cambios en get_availabilities")
        mydb.commit()
    else:
        checkin = myresult['checkin']
        checkout = myresult['checkout']
        id_wubook = myresult['id_wubook']
        mydb.commit()
        dfrom = checkin.strftime("%d/%m/%Y")
        dto = checkout.strftime("%d/%m/%Y")
        try:
            res, rooms = server.fetch_rooms_values(token, lcode,dfrom,dto)
        except Exception as e:
            print(e)
        else:
            if len(rooms_ids) == 0:
                get_rooms_ids(mycursor,mydb)
        
            days_in_query = len(rooms[rooms_ids[0]])
       
            availabilities_calendar = [0]*days_in_query
       
            for key,room_id in enumerate(rooms):
            
          
                for day,info in enumerate(rooms[room_id]):
                    availabilities_calendar[day] += info['booked']
            for ocupancy in availabilities_calendar:
                fecha = checkin.strftime("%Y-%m-%d")
            
                query = "insert into  hotel.wubook_rooms_booked  (checkin,checkout,booked_rooms,activo) values ('"+fecha+"','"+fecha+"',"+str(ocupancy)+",'1')"
                mycursor.execute(query)
                mydb.commit()
                checkin = checkin + datetime.timedelta(days=1)
            query = "update hotel.wubook  set activo = 0 where id_wubook='"+str(id_wubook)+"' "
            mycursor.execute(query)
            mydb.commit()



def get_key_with_room_id(array,room_id):
    for key, value in enumerate(array):
        if value['id'] == room_id:
            return key
    return -1
        

def new_roomdays(room_id,number_of_days):
    roomdays= {'id': room_id, 'days': [[]]*number_of_days}
    return roomdays


def get_next_room_id(room_id,rooms,elapsed_days,mycursor,mydb):
   
   if len(rooms_ids) == 0:
       get_rooms_ids(mycursor,mydb)
   print(rooms_ids)
   current_key = rooms_ids.index(room_id)
   if current_key == len(rooms_ids):
         return room_id
   current_key += 1
   if(current_key == len(rooms_ids)):
       return room_id
   new_room_id = rooms_ids[current_key]
   while rooms[new_room_id][elapsed_days]['avail']<1 and current_key < len(rooms_ids):
       print(rooms[new_room_id][elapsed_days]['avail']) 
       new_room_id = rooms_ids[current_key]
       current_key += 1
   return new_room_id

def number_of_days(checkin,checkout):
    delta = checkout - checkin
    return delta.days
           

def change_room_avail(dfrom,dto,room_id,value,mycursor,mydb,no_ota=True):
    
    room = [room_id]
    try:
        res, rooms = server.fetch_rooms_values(token, lcode,dfrom,dto)
        print(dfrom,"dfrom",dto,"dto")
    except Exception as e:
        print(e)
    else:
        print(res,'res')
        print(rooms,'rooms')
        if (res != 0): 
            return True
        if(len(rooms) == 0):
            return True
       
        
        if 'avail' in rooms[room_id][0]:
            print(rooms[room_id][0]['avail'])
        number_of_days = len(rooms[room_id])

        
        
        roomdays= []
        if len(rooms_ids) == 0:
            get_rooms_ids(mycursor,mydb)
        for i in rooms_ids:
            roomdays.append(new_roomdays(i,number_of_days))
        
        print(roomdays)
        
        elapsed_days = 0;
        active_rooms = [room_id];
        for info in rooms[room_id]:
            # print(info['avail'])
            
            # if(info['avail'] == 0 and value < 0):
                
            #     #roomdays[0]['days'][elapsed_days]={'avail': 0}
            #     new_room_id = get_next_room_id(room_id,rooms,elapsed_days)
            #     key = get_key_with_room_id(roomdays,new_room_id)
            #     if key == -1:
            #         roomdays.append(new_roomdays(new_room_id,number_of_days))
            #     new_avail = rooms[new_room_id][elapsed_days]['avail']+value
            #     if(new_avail < 0):
            #         roomdays[key]['days'][elapsed_days]={'avail':0}
            #     else:
            #         roomdays[key]['days'][elapsed_days]={'avail':rooms[new_room_id][elapsed_days]['avail']+value}
            #     # print(new_room_id)
            #     # print(rooms[new_room_id])
            #     # print(rooms[new_room_id][elapsed_days])
            #     # print(rooms[new_room_id][elapsed_days]['avail'])
            
            # else:
            for i in range(0,len(roomdays)):
                if no_ota:
                    roomdays[i]['days'][elapsed_days]={'avail':value,'no_ota': 0}
                else:
                    roomdays[i]['days'][elapsed_days]={'avail':value,'no_ota': 0}
            elapsed_days += 1
        print(roomdays)
        
        for roomday in roomdays:
            id_room = roomday['id']
            days = roomday['days']
            for key,day in enumerate(days):
                if 'avail' in day:
                    date = datetime.datetime.strptime(dfrom, "%d/%m/%Y") + datetime.timedelta(days=key)
                    date = date.strftime("%Y-%m-%d")
                    new_avail = str(day['avail'])
                    prev_avail = "0"
                    if 'avail' in rooms[id_room][key]:
                        prev_avail = str(rooms[id_room][key]['avail'])
                    query = "insert into  hotel.wubook_avail_changes  (fecha,id_room_wubook,new_avail,prev_avail) values ('"+date+"','"+id_room+"',"+new_avail+",'"+prev_avail+"')"
                    mycursor.execute(query)
                    mydb.commit()
                
    # dfrom = "10/03/2024"
    # roomdays= [{'id': 374788, 'days': [{'avail': 1}, {}, {'avail': 1}]}]
    
    try:
        res, info = server.update_avail(token, lcode, dfrom, roomdays)
    except Exception as e:
        print(e)
        return False
    else:
        return True
    # print(info)
    # print(res)





def get_reservations_changes(mycursor,mydb):
    query = "select * from hotel.wubook where descripcion='reservation_update' and activo='1' limit 1 "
    mycursor.execute(query)

    myresult = mycursor.fetchone()
    #print one of the cols by name
    #check for empty result
    if myresult is None:
        print("no hay cambios en reservas")
        mydb.commit()
    else:
        dfrom = myresult['checkin'].strftime("%d/%m/%Y")
        dto = myresult['checkout'].strftime("%d/%m/%Y")
        type_of_update = myresult['type_of_update']
        id_room = str(myresult['id_room'])
        id_wubook = myresult['id_wubook']
        value = myresult['value']
        no_ota = bool(myresult['no_ota'])
        mydb.commit()
        # ids_rooms_avail_less = get_rooms_ids_whith_avail_less_than(rooms_booked)
        # id_room_avail_greater_or_equal = get_room_id_whith_avail_more_or_equal(rooms_booked)
        # for id_room in ids_rooms_avail_less:
        #     change_room_avail(dfrom,dto,id_room,0)
        # if id_room_avail_greater_or_equal is not None:
        change_room_avail_resp = False
        if type_of_update == 'reservation':
            print("reservation update avail")
            print(id_room,'id_room')
            change_room_avail_resp = change_room_avail(dfrom,dto,id_room,value,mycursor,mydb,no_ota)
        elif type_of_update == 'cancelacion':
            print("cancelacion update avail")
            change_room_avail_resp = change_room_avail(dfrom,dto,id_room,value,mycursor,mydb)
        elif type_of_update == 'cierre':
            print("cierre update avail")
            if len(rooms_ids) == 0:
                get_rooms_ids(mycursor,mydb)
            for id_room in rooms_ids:
                change_room_avail_resp = change_room_avail(dfrom,dto,id_room,-100,mycursor,mydb)
        elif type_of_update == 'change_room_prices':
            change_room_avail_resp = change_plan_prices(dfrom,dto,id_room,value,mycursor,mydb)
        if(change_room_avail_resp == True):
            query = "update hotel.wubook  set activo = 0 where id_wubook='"+str(id_wubook)+"' "
            mycursor.execute(query)
            mydb.commit()



def get_precio_dolar():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=mysql_password
)
    mycursor = mydb.cursor(dictionary=True)
    query = "select * from hotel.valores where descripcion='valor_dolar' "
    mycursor.execute(query)
    myresult = mycursor.fetchone()
    if myresult is not None:
        precio_dolar = int(myresult['valor'])
        return precio_dolar
    else:
        return 1
    
precio_dolar = get_precio_dolar()


def get_room_id(bed_type, dfrom, dto, occupancy):
    """
    Call availability.php API to get available room ID
    
    Args:
        bed_type (str): Bed preference ('matrimonial' or 'individual')
        dfrom (str): Check-in date (YYYY-MM-DD format)
        dto (str): Check-out date (YYYY-MM-DD format)
        occupancy (int): Number of guests
    
    Returns:
        dict: API response with room availability info or None if error
    """
    try:
        # Prepare POST data for availability.php API
        post_data = {
            'from': dfrom,
            'to': dto,
            'guests': str(occupancy),
            'rooms': '1',
            'bed_type': bed_type if bed_type else 'matrimonial'
        }
        
        # Make HTTP POST request to availability.php
        api_url = 'http://localhost/hotel/api/availability.php'  # Adjust URL as needed
        response = requests.post(api_url, data=post_data)
        
        if response.status_code == 200:
            result = response.json()
            
            # Check if room is available
            if result.get('available', False) and result.get('room_details'):
                print(f"Room available: ID {result['room_details']['room_id']}, "
                      f"Name: {result['room_details']['room_name']}, "
                      f"Price: ${result['price']}")
                return result
            else:
                print(f"No rooms available for {occupancy} guests from {dfrom} to {dto}")
                return None
                
        else:
            print(f"API request failed with status code: {response.status_code}")
            return None
            
    except requests.exceptions.RequestException as e:
        print(f"Error calling availability API: {e}")
        return None
    except json.JSONDecodeError as e:
        print(f"Error parsing API response: {e}")
        return None
    except Exception as e:
        print(f"Unexpected error in get_room_id: {e}")
        return None 

def fetch_bookings(cursor, db):
    bookings = False
    try:
        rcode = "1752258674"
        #resp, bookings = server.fetch_booking(token, lcode, rcode)
        resp, bookings = server.fetch_new_bookings(token, lcode, 0, 1)
    except Exception as e:
        print("WuBook error:", e)          # early exit – nothing to process
        return

    if not bookings:
        print("No hay nuevas reservas")
        return

    for b in bookings:                     # use the item directly
        try:
            status = b["status"]

            # --- skip non-relevant status codes -------------------------
            if status not in (1, 4, 5):
                continue

            checkin  = datetime.datetime.strptime(b["date_arrival"],  "%d/%m/%Y").date()
            checkout = datetime.datetime.strptime(b["date_departure"], "%d/%m/%Y").date()

            delta_days = (checkout - checkin).days or 1     # avoid 0
            rooms      = b["rooms_occupancies"] or [{"occupancy": 1}]
            room_cnt   = len(rooms)

            price_per_room = b["amount"] / room_cnt if room_cnt else 0
            price_daily    = price_per_room / delta_days


            price_with_tax_dolar       = round(price_daily * 1.21, 2)
            price_with_tax_pesos = round(price_with_tax_dolar * precio_dolar, 2)

            if status == 5:                          # cancelled
                cursor.execute(
                    "UPDATE hotel.planilla_pasajeros "
                    "SET activo = 'false' "
                    "WHERE nro_reserva_wubook = %s",
                    (b["reservation_code"],)
                )
                db.commit()
                continue

            # --- insert one row per room --------------------------------
            for room in rooms:
                occupancy = room["occupancy"]
                bed_type = room["bed_type"]
                room_id = 1
                #call a function to get the room id
                try:
                    resp = get_room_id(bed_type, checkin, checkout, occupancy)
                    if resp:
                        room_id = resp['room_details']['room_id']
                except Exception as e:
                    print(f"Error getting room ID: {e}")
                cursor.execute(
                    """
                    INSERT INTO hotel.planilla_pasajeros
                      (nombre, apellido, id_apartamento, checkin, checkout,
                       activo, tarifa, reserva, hora_checkin,
                       nro_reserva_wubook, cantidad_ocupantes, id_channel, telefono)
                    VALUES
                      (%s, %s, %s, %s, %s,
                       'true', %s, 'true', %s,
                       %s, %s, %s, %s)
                    """,
                    (
                        b["customer_name"],
                        b["customer_surname"],
                        room_id,
                        checkin, checkout,
                        price_with_tax_pesos,
                        b["arrival_hour"],
                        b["reservation_code"],
                        room["occupancy"],
                        b["id_channel"],
                        b["customer_phone"]
                    )
                )
                db.commit()
                id_pasajero = cursor.lastrowid

                cursor.execute(
                    """
                    INSERT INTO hotel.tarifa_pasajero
                      (desde, hasta, tarifa, activa,
                       id_pasajero, tarifa_dolar,
                       descuento_efectivo, tiene_descuento, pasajeros)
                    VALUES
                      (%s, %s, %s, 'true',
                       %s, %s,
                       0, 'true', %s)
                    """,
                    (
                        checkin, checkout, price_with_tax_pesos,
                        id_pasajero, price_with_tax_dolar,
                        room["occupancy"]
                    )
                )
                db.commit()

        except ZeroDivisionError:
            print("⚠️ división por cero en reserva", b.get("reservation_code"))
        except KeyError as e:
            print("⚠️ campo faltante:", e, "en reserva", b.get("reservation_code"))
        except db.Error as e:
            print("⚠️ MySQL:", e)
            db.rollback()




def fetch_bookings1(mycursor,mydb):
    rcode = "1709291158"
    #resp, booking = server.fetch_booking(token, lcode, rcode)
    booking = []
    try:
        resp, booking = server.fetch_new_bookings(token, lcode,0, 1)
    except Exception as e:
        print(e)
    else:
        print(booking)
        if len(booking) == 0:
            print("no hay nuevas reservas")
        for key, value in enumerate(booking):
       
            status = booking[key]['status']
        
            checkin_datetime = datetime.datetime.strptime(booking[key]['date_arrival'],"%d/%m/%Y")
            checkin = checkin_datetime.strftime("%Y-%m-%d")
            checkout_datetime = datetime.datetime.strptime(booking[key]['date_departure'],"%d/%m/%Y")
            checkout = checkout_datetime.strftime("%Y-%m-%d")
       
            total_amount_no_tax = booking[key]['amount']
            reservation_code = booking[key]['reservation_code']
    
            arrival_hour = booking[key]['arrival_hour']
            customer_name = booking[key]['customer_name']
            customer_surname = booking[key]['customer_surname']
            customer_phone = booking[key]['customer_phone']
            men = booking[key]['men']
            children = booking[key]['children']
            rooms_ocupancies = booking[key]['rooms_occupancies']
            id_channel = booking[key]['id_channel']
            customer_phone = booking[key]['customer_phone']
            price_per_room = total_amount_no_tax/len(rooms_ocupancies)
      
            delta = checkout_datetime - checkin_datetime
            number_of_days = delta.days
            price_per_room_daily = price_per_room/number_of_days
            price_per_room_daily_with_tax = price_per_room_daily+(price_per_room_daily*0.21)
            price_per_room_daily_with_tax_dolar = round(price_per_room_daily_with_tax,2)
            price_per_room_daily_with_tax = round(price_per_room_daily_with_tax*precio_dolar,2)
        

            if status != 4 and status != 1:
                if status == 5:
                    query = "update hotel.planilla_pasajeros set activo = 'false' where nro_reserva_wubook = '"+str(reservation_code)+"'"
                    try:
                        mycursor.execute(query)
                        mydb.commit()
                        # NB : you won't get an IntegrityError when reading
                    except (mydb.Error, mydb.Warning) as e:
                        print(e)
                continue

            for room in rooms_ocupancies:
                ocupancies = room['occupancy']
                query = "insert  into hotel.planilla_pasajeros (nombre,apellido,id_apartamento,checkin,checkout,activo,tarifa,reserva,hora_checkin,nro_reserva_wubook,cantidad_ocupantes,id_channel,telefono)"
                query += " values ('"+customer_name+"','"+customer_surname+"','2','"+checkin+"','"+checkout+"','true',"+str(price_per_room_daily_with_tax)+","
                query += "'true','"+arrival_hour+"','"+str(reservation_code)+"','"+str(ocupancies)+"','"+str(id_channel)+"','"+str(customer_phone)+"')"
                mycursor.execute(query)
                mydb.commit()
                id_pasajero = mycursor.lastrowid
                print("id_pasajero",id_pasajero)
                query = "insert into hotel.tarifa_pasajero (desde,hasta,tarifa,activa,id_pasajero,tarifa_dolar,descuento_efectivo,tiene_descuento,pasajeros)"
                query += "values ('"+checkin+"','"+checkout+"',"+str(price_per_room_daily_with_tax)+",'true','"+str(id_pasajero)+"',"+str(price_per_room_daily_with_tax_dolar)+",'0','true','"+str(ocupancies)+"')"
                try:
                    mycursor.execute(query)
                    mydb.commit()
                    # NB : you won't get an IntegrityError when reading
                except (mydb.Error, mydb.Warning) as e:
                    print(e)


    
def reservation_cancelation(mycursor,mydb):
    query = "select * from hotel.wubook where descripcion='reservation_cancelation' and activo='1' limit 1 "
    mycursor.execute(query)

    myresult = mycursor.fetchone()
    #print one of the cols by name
    #check for empty result
    if myresult is None:
        print("no hay cambios en reservation cancelation")
        mydb.commit()
    else:
        nro_reserva_wubook =  myresult['id_room']
        id_wubook = myresult['id_wubook']
        mydb.commit()
        
        try:
            result, ret = server.cancel_reservation(token, lcode, nro_reserva_wubook)
            print(ret)
            print(result)
            print(nro_reserva_wubook)
        except Exception as e:
            print(e)
        else:
            query = "update hotel.wubook  set activo = 0 where id_wubook='"+str(id_wubook)+"' "
            mycursor.execute(query)
            mydb.commit()
            print("reservation cancelation")
  

def get_reservation(mycursor,mydb):
    query = "select * from hotel.wubook where descripcion='get_reservation' and activo='1' limit 1 "
    mycursor.execute(query)

    myresult = mycursor.fetchone()
    #print one of the cols by name
    #check for empty result
    if myresult is None:
        print("no hay cambios en get_reservation")
        mydb.commit()
    else:
        nro_reserva_wubook =  myresult['value']
        id_wubook = myresult['id_wubook']
        mydb.commit()
        
        try:
            result, booking = server.fetch_booking(token, lcode, nro_reserva_wubook)
            print(booking)
            print(result)
            print(nro_reserva_wubook)
        except Exception as e:
            print(e)
        else:
            
            query = "update hotel.wubook  set activo = 0 where id_wubook='"+str(id_wubook)+"' "
            mycursor.execute(query)
            mydb.commit()
            key = 0
            status = booking[key]['status']
        
            checkin_datetime = datetime.datetime.strptime(booking[key]['date_arrival'],"%d/%m/%Y")
            checkin = checkin_datetime.strftime("%Y-%m-%d")
            checkout_datetime = datetime.datetime.strptime(booking[key]['date_departure'],"%d/%m/%Y")
            checkout = checkout_datetime.strftime("%Y-%m-%d")
       
            total_amount_no_tax = booking[key]['amount']
            reservation_code = booking[key]['reservation_code']
    
            arrival_hour = booking[key]['arrival_hour']
            customer_name = booking[key]['customer_name']
            customer_surname = booking[key]['customer_surname']
            customer_phone = booking[key]['customer_phone']
            men = booking[key]['men']
            children = booking[key]['children']
            rooms_ocupancies = booking[key]['rooms_occupancies']
            id_channel = booking[key]['id_channel']
            customer_phone = booking[key]['customer_phone']
            price_per_room = total_amount_no_tax/len(rooms_ocupancies)
      
            delta = checkout_datetime - checkin_datetime
            number_of_days = delta.days
            price_per_room_daily = price_per_room/number_of_days
            price_per_room_daily_with_tax = price_per_room_daily+(price_per_room_daily*0.21)
            price_per_room_daily_with_tax_dolar = round(price_per_room_daily_with_tax,2)
            price_per_room_daily_with_tax = round(price_per_room_daily_with_tax*precio_dolar,2)
            
            

            for room in rooms_ocupancies:
                ocupancies = room['occupancy']
                query = "insert  into hotel.planilla_pasajeros (nombre,apellido,id_apartamento,checkin,checkout,activo,tarifa,reserva,hora_checkin,nro_reserva_wubook,cantidad_ocupantes,id_channel,telefono)"
                query += " values ('"+customer_name+"','"+customer_surname+"','1','"+checkin+"','"+checkout+"','true',"+str(price_per_room_daily_with_tax)+","
                query += "'true','"+arrival_hour+"','"+str(reservation_code)+"','"+str(ocupancies)+"','"+str(id_channel)+"','"+str(customer_phone)+"')"
                mycursor.execute(query)
                mydb.commit()
                id_pasajero = mycursor.lastrowid
                print("id_pasajero",id_pasajero)
                query = "insert into hotel.tarifa_pasajero (desde,hasta,tarifa,activa,id_pasajero,tarifa_dolar,descuento_efectivo,tiene_descuento,pasajeros)"
                query += "values ('"+checkin+"','"+checkout+"',"+str(price_per_room_daily_with_tax)+",'true','"+str(id_pasajero)+"',"+str(price_per_room_daily_with_tax_dolar)+",'0','true','"+str(ocupancies)+"')"
                try:
                    mycursor.execute(query)
                    mydb.commit()
                    # NB : you won't get an IntegrityError when reading
                except (mydb.Error, mydb.Warning) as e:
                    print(e)
  
            
def change_plan_prices(dfrom,dto,id_room,value,mycursor,mydb):
    
    pid = 0
    dfrom_datetime = datetime.datetime.strptime(dfrom,"%d/%m/%Y")
    dto_datetime = datetime.datetime.strptime(dto,"%d/%m/%Y")
    days = number_of_days(dfrom_datetime,dto_datetime)
    value = json.loads(value)
    prices = {
  str(id_room): value
}   
   
    print(prices)
    
    try:
        resp, update = server.update_plan_prices(token, lcode, pid, dfrom, prices)
    except Exception as e:
        print(e)
        return False
    else:
        print(resp)
        return True
    return False    
    
    

def helper_get_reservation():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=mysql_password
)
    mycursor = mydb.cursor(dictionary=True)
    while not stop_evt.is_set():
        try:
            # Validate connection before using
            if not mycursor or not mydb:
                print("Connection is None, attempting to reconnect...")
                mycursor, mydb = connect_db()
                if not mycursor or not mydb:
                    print("Failed to establish connection, waiting before retry...")
                    time.sleep(10)
                    continue
            get_reservation(mycursor,mydb)
        except Exception as e:
            if debug_error(e, "helper_get_reservation"):
                # Connection error - try to reconnect
                mycursor, mydb = reconnect_db(mydb, mycursor)
                if not mycursor or not mydb:
                    print("MySQL reconnection failed, skipping availability check")
                    time.sleep(10)  # Wait longer before next attempt
                    continue
            else:
                # Non-connection error - log and continue
                print(f"Non-connection error in helper_get_reservation: {e}")
        sleep(60)
    print("fin get helper_get_reservation")   


      

def helper_get_reservations_changes():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=mysql_password
)
    mycursor = mydb.cursor(dictionary=True)
    while not stop_evt.is_set():
        try:
            # Validate connection before using
            if not mycursor or not mydb:
                print("Connection is None, attempting to reconnect...")
                mycursor, mydb = connect_db()
                if not mycursor or not mydb:
                    print("Failed to establish connection, waiting before retry...")
                    time.sleep(10)
                    continue
            get_reservations_changes(mycursor,mydb)
        except Exception as e:
            if debug_error(e, "helper_get_reservations_changes"):
                # Connection error - try to reconnect
                mycursor, mydb = reconnect_db(mydb, mycursor)
                if not mycursor or not mydb:
                    print("MySQL reconnection failed, skipping availability check")
                    time.sleep(10)  # Wait longer before next attempt
                    continue
            else:
                # Non-connection error - log and continue
                print(f"Non-connection error in helper_get_reservations_changes: {e}")
        sleep(10)
    print("fin get get_reservations_changes")
        
def helper_reservation_cancelation():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=mysql_password
)
    mycursor = mydb.cursor(dictionary=True)
    while not stop_evt.is_set():
        try:
            reservation_cancelation(mycursor,mydb)
        except Exception as e:
            print(f"error at reservation_cancelation{e}")

        sleep(2)
    print("fin get helper_reservation_cancelation")   
        
def helper_fetch_bookings():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=mysql_password
)
    prev_time = 0;
    mycursor = mydb.cursor(dictionary=True)
    while not stop_evt.is_set():
       
        elapsed_time = time.time() - prev_time
        if(elapsed_time > 30):
            try:
                # Validate connection before using
                if not mycursor or not mydb:
                    print("Connection is None, attempting to reconnect...")
                    mycursor, mydb = connect_db()
                    if not mycursor or not mydb:
                        print("Failed to establish connection, waiting before retry...")
                        time.sleep(10)
                        continue
                fetch_bookings(mycursor,mydb)
            except Exception as e:
                if debug_error(e, "helper_fetch_bookings"):
                    # Connection error - try to reconnect
                    mycursor, mydb = reconnect_db(mydb, mycursor)
                    if not mycursor or not mydb:
                        print("MySQL reconnection failed, skipping availability check")
                        time.sleep(10)  # Wait longer before next attempt
                        continue
                else:
                    # Non-connection error - log and continue
                    print(f"Non-connection error in helper_fetch_bookings: {e}")
            prev_time = time.time()
            last_run_modulo(mycursor,mydb,"fetch_bookings")
        time.sleep(1)
    print("helper fetch bookin stop")

def last_run_modulo(cursor,db,modulo):
    try:
        cursor.execute(
                    "UPDATE hotel.last_run_modulo "
                    "SET ultima_actualizacion = now() "
                    "WHERE descripcion = %s",
                    (modulo,)
                )
        db.commit()
    except Exception as e:
        print(f"erorr{e} at update modulo{modulo}")

def update_exchange_rate(mycursor,mydb,valor_dolar):
    str_valor_dolar = f"{valor_dolar:.0f}"
    query = "update hotel.valores set valor='"+str_valor_dolar+"',fecha=NOW() where descripcion='valor_dolar'"
    mycursor.execute(query)
    mydb.commit()

def get_exchange_rate():
    data = requests.get(url, params=payload, timeout=5).json()
    if not data["success"]:
        raise RuntimeError(data["error"])

    eur_usd = data["rates"]["USD"]    # EUR → USD
    eur_ars = data["rates"]["ARS"]    # EUR → ARS

    usd_ars = eur_ars / eur_usd       # USD → ARS via cross-rate

    ts = datetime.datetime.fromtimestamp(data["timestamp"])
    print(f"{ts:%Y-%m-%d %H:%M} | 1 USD = {usd_ars:.2f} ARS")  
    return usd_ars 

def helper_availability():
    mycursor, mydb = connect_db()
    if not mycursor or not mydb:
        print("Failed to create initial MySQL connection for availability helper")
        return
   
    while not stop_evt.is_set():
        try:
            # Validate connection before using
            if not mycursor or not mydb:
                print("Connection is None, attempting to reconnect...")
                mycursor, mydb = connect_db()
                if not mycursor or not mydb:
                    print("Failed to establish connection, waiting before retry...")
                    time.sleep(10)
                    continue
            
            get_availabilities(mycursor,mydb)
        except Exception as e:
            if debug_error(e, "helper_availability"):
                # Connection error - try to reconnect
                mycursor, mydb = reconnect_db(mydb, mycursor)
                if not mycursor or not mydb:
                    print("MySQL reconnection failed, skipping availability check")
                    time.sleep(10)  # Wait longer before next attempt
                    continue
            else:
                # Non-connection error - log and continue
                print(f"Non-connection error in helper_availability: {e}")
        time.sleep(2)
    
    # Clean up connections
    try:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()
    except:
        pass
    print("fin get helper_availability")    

def helper_get_exchange_rate():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=mysql_password
)
    mycursor = mydb.cursor(dictionary=True)
    valor_dolar = get_exchange_rate()
    update_exchange_rate(mycursor,mydb,valor_dolar)
    while not stop_evt.is_set():
        try:
            valor_dolar = get_exchange_rate()
            update_exchange_rate(mycursor,mydb,valor_dolar)
        except Exception as e:
           print(f"error update_exchange_rate {e}") 
        time.sleep(3600)
    print("fin get exchange rates")

def test():
    print("working")
    while not stop_evt_1.is_set():
        time.sleep(.1)



stop_evt = threading.Event()
stop_evt_1 = threading.Event()
threads = []
threads.append(threading.Thread(target=helper_availability, args=()))
threads.append(threading.Thread(target=helper_get_reservations_changes, args=()))
#t3 = threading.Thread(target=helper_reservation_cancelation, args=())
threads.append(threading.Thread(target=helper_fetch_bookings, args=()))
threads.append(threading.Thread(target=helper_get_reservation, args=()))
threads.append(threading.Thread(target=helper_get_exchange_rate, args=()))

#threads.append(threading.Thread(target=test, args=()))
for t in threads:
    t.start()

try:
    while any(t.is_alive()for t in threads):
        time.sleep(0.2)               # idle loop
except KeyboardInterrupt:
    print("\n⌃C detected – stopping threads …")
    stop_evt.set()                    # 🔑 signal every worker to exit


for t in threads:
    t.join()                          # wait for clean shutdown
print("All done.")

# dfrom = "10/03/2024"
# dto = "10/03/2024"
# room_id = '617005'
# val = -1
# new_val = 0 + val
# print(new_val)
# change_room_avail(dfrom,dto,room_id,-1)





# def get_rooms_ids_whith_avail_less_than(rooms_booked):
#     ids = []
#     query = "select * from hotel.wubook_apartamentos where avail < rooms_booked"
#     mycursor.execute(query)
#     myresult = mycursor.fetchall()
#     if myresult is not None:
#         for result in myresult:
#             id_room = result['id_apartamento']
#             ids.append(id_room)
#     return ids
    
# def get_room_id_whith_avail_more_or_equal(rooms_booked):
#     query = "select * from hotel.wubook_apartamentos where avail >= rooms_booked order by avail asc limit 1"
#     mycursor.execute(query)
#     myresult = mycursor.fetchone()
#     if myresult is not None:
#        return myresult['id_apartamento']
        
# def get_rooms_booked():
#     rooms_booked = 0
#     query = "select * from hotel.wubook where descripcion='rooms_booked' and update = '1' "
#     mycursor.execute(query)

#     myresult = mycursor.fetchone()
#     #print one of the cols by name
#     #check for empty result
#     if myresult is None:
#         print("no hay cambios en reservas")
#     else:
#         dfrom = myresult['checkin']
#         dto = myresult['checkout']
#         res, rooms = server.fetch_rooms_values(token, lcode,dfrom,dto)
#         for room in rooms:
#             rooms_booked += room['avail']
            
#         query = "update hotel.wubook  set update = 0 where descripcion='rooms_booked' "
#         mycursor.execute(query)
#         mydb.commit()
#         insert_booked_rooms(dfrom,dto,rooms_booked)
#         get_previous_booked_rooms(dfrom,dto)
        

# def insert_booked_rooms(dfrom,dto,rooms_booked):
#     query = "insert into  hotel.wubook_rooms_booked  (checkin,checkout,booked_rooms) values ("+dfrom+","+dto+","+rooms_booked+")"
#     mycursor.execute(query)
#     mydb.commit()

# def get_previous_booked_rooms(dfrom,dto):
#     query = "select * from hotel.wubook_rooms_booked where (checkin between dfrom and dto) or (checkout between dfrom and dto) or checkin < dfrom and checkout > dto"
#     mycursor.execute(query)
#     myresult = mycursor.fetchall()
#     for result in myresult:
#         checkin = result['checkin']
#         checkout = result['checkout']
#         booked_rooms = result['booked_rooms']
#         id_rooms_booked = result['id_rooms_booked']
#         if checkin < dfrom and checkout > dto:
#             update_booked_rooms_checkin_checkout(id_rooms_booked,dfrom,dto,checkout,booked_rooms)
#         elif checkin < dto:
#             update_booked_rooms_checkin(id_rooms_booked,dto)
#         else:
#            update_booked_rooms_checkout(id_rooms_booked,dfrom)


# def update_booked_rooms_checkin(id_rooms_booked,dto):
#         query = "update hotel.wubook_rooms_booked  set checkin = dto where id_rooms_booked="+id_rooms_booked
#         mycursor.execute(query)
#         mydb.commit() 
  
# def update_booked_rooms_checkout(id_rooms_booked,dfrom):
#         query = "update hotel.wubook_rooms_booked  set checkout = dfrom where id_rooms_booked="+id_rooms_booked
#         mycursor.execute(query)
#         mydb.commit()  
        
# def update_booked_rooms_checkin_checkout(id_rooms_booked,dfrom,dto,previous_checkout,previous_booked_rooms):
#     query = "update hotel.wubook_rooms_booked  set checkout = dfrom where id_rooms_booked="+id_rooms_booked
#     mycursor.execute(query)
#     mydb.commit()
#     query = "insert into  hotel.wubook_rooms_booked  (checkin,checkout,booked_rooms) values ("+dto+","+previous_checkout+","+previous_booked_rooms+")"
#     mycursor.execute(query)
#     mydb.commit()