import array
from pickle import FALSE
from time import sleep
import mysql.connector
import datetime
import time
import threading
import json

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 = []

def get_rooms_ids(mycursor,mydb):
    query = "select * from cyber.wubook_apartamentos 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 cyber.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  cyber.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 cyber.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):
   
   if len(rooms_ids) == 0:
       get_rooms_ids()
   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):
    
    room = [room_id]
    try:
        res, rooms = server.fetch_rooms_values(token, lcode,dfrom,dto)
    except Exception as e:
        print(e)
    else:
        number_of_days = len(rooms[room_id])
        print(rooms)
        print(rooms[room_id][0]['avail'])
        roomdays= [new_roomdays(room_id,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:
                roomdays[0]['days'][elapsed_days]={'avail': info['avail']+value}
            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 = str(rooms[id_room][key]['avail'])
                    query = "insert into  cyber.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 cyber.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']
        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")
            change_room_avail_resp = change_room_avail(dfrom,dto,id_room,-1,mycursor,mydb)
        elif type_of_update == 'cancelacion':
            print("cancelacion update avail")
            change_room_avail_resp = change_room_avail(dfrom,dto,id_room,1,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 cyber.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="Urw8hMJjveUcTtG9"
)
    mycursor = mydb.cursor(dictionary=True)
    query = "select * from cyber.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 fetch_bookings(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']
            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 cyber.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 cyber.planilla_pasajeros (nombre,apellido,id_apartamento,checkin,checkout,activo,tarifa,reserva,hora_checkin,nro_reserva_wubook,cantidad_ocupantes,id_channel)"
                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)+"')"
                mycursor.execute(query)
                mydb.commit()
                id_pasajero = mycursor.lastrowid
                print("id_pasajero",id_pasajero)
                query = "insert into cyber.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 cyber.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 cyber.wubook  set activo = 0 where id_wubook='"+str(id_wubook)+"' "
            mycursor.execute(query)
            mydb.commit()
            print("reservation ccanccelation")
  
            


def helper_availability():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="Urw8hMJjveUcTtG9"
)
    mycursor = mydb.cursor(dictionary=True)
    while True:
        get_availabilities(mycursor,mydb)
        sleep(2)
       

def helper_get_reservations_changes():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="Urw8hMJjveUcTtG9"
)
    mycursor = mydb.cursor(dictionary=True)
    while True:
        get_reservations_changes(mycursor,mydb)
        sleep(10)
        
def helper_reservation_cancelation():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="Urw8hMJjveUcTtG9"
)
    mycursor = mydb.cursor(dictionary=True)
    while True:
        reservation_cancelation(mycursor,mydb)
        sleep(2)
       
        
def helper_fetch_bookings():
    mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="Urw8hMJjveUcTtG9"
)
    prev_time = 0;
    mycursor = mydb.cursor(dictionary=True)
    while True:
       
        elapsed_time = time.time() - prev_time
        if(elapsed_time > 300):
            fetch_bookings(mycursor,mydb)
            prev_time = time.time()




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    
    
    


t1 = threading.Thread(target=helper_availability, args=())
t2 = threading.Thread(target=helper_get_reservations_changes, args=())
t3 = threading.Thread(target=helper_reservation_cancelation, args=())
t4 = threading.Thread(target=helper_fetch_bookings, args=())
t1.start()
t2.start()
t3.start()
t4.start()

def check_alive(t):
    sleep(1)
    if t.is_alive() == False:
        t.start()
        
while True:
    alive_t1 = check_alive(t1)
    alive_t2 = check_alive(t2)
    alive_t3 = check_alive(t3)
    alive_t4 = check_alive(t4)
    


# 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 cyber.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 cyber.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 cyber.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 cyber.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  cyber.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 cyber.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 cyber.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 cyber.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 cyber.wubook_rooms_booked  set checkout = dfrom where id_rooms_booked="+id_rooms_booked
#     mycursor.execute(query)
#     mydb.commit()
#     query = "insert into  cyber.wubook_rooms_booked  (checkin,checkout,booked_rooms) values ("+dto+","+previous_checkout+","+previous_booked_rooms+")"
#     mycursor.execute(query)
#     mydb.commit()