import requests
import json
import time
import os
from datetime import datetime
import mysql.connector

def get_cached_token():
    """
    Verifica se existe um token válido armazenado em 'token_cache.json'.
    Se não existir ou estiver expirado, chama get_token() para obter um novo token.
    """
    cache_file = "token_cache.json"
    if os.path.exists(cache_file):
        with open(cache_file, "r", encoding="utf-8") as f:
            cache_data = json.load(f)
        if ("token" in cache_data and "expires" in cache_data and 
                cache_data["expires"] > time.time()):
            return cache_data["token"]
    token = get_token()
    if not token:
        raise Exception("Falha ao obter token.")
    with open(cache_file, "w", encoding="utf-8") as f:
        json.dump({"token": token, "expires": time.time() + 14400}, f)
    return token

def get_token():
    """
    Chama o endpoint de token e extrai o token da resposta.
    O endpoint retorna uma string no formato: "Token obtido: <seu_token>"
    """
    token_url = "http://localhost/BI-HITS/dados/Hits/token/token.php"
    payload = {
        "username": "seu_usuario",  # Ajuste conforme necessário
        "password": "sua_senha"       # Ajuste conforme necessário
    }
    try:
        response = requests.post(token_url, data=payload, timeout=30)
        response.raise_for_status()
        raw = response.text
        print("Raw token response:", raw)
        prefix = "Token obtido:"
        if raw.startswith(prefix):
            token = raw.replace(prefix, "").strip()
            return token
        else:
            data = response.json()
            return data.get("token")
    except Exception as e:
        print("Erro ao obter token:", e)
        return None

def fetch_rooming_nights(page, headers, ini_date, fin_date, size, retries=3, timeout=60):
    """
    Busca os registros do endpoint RoomingNights para a página especificada.
    Implementa re-tentativas em caso de erro.
    """
    url = "https://api.hitspms.net/Datashare/RevenueManagement/RoomingNights"
    params = {
        "IniDate": ini_date,
        "FinDate": fin_date,
        "Page": page,
        "Size": size
    }
    attempt = 0
    while attempt < retries:
        try:
            response = requests.get(url, headers=headers, params=params, timeout=timeout)
            response.raise_for_status()
            result = response.json()
            # Se a resposta vier encapsulada em um dicionário (ex: com chave "data"), extraia a lista
            if isinstance(result, dict) and "data" in result:
                result = result["data"]
            return result
        except Exception as e:
            print(f"Erro na página {page} na tentativa {attempt+1}: {e}")
            attempt += 1
            time.sleep(3)
    return []

def save_to_db(data):
    """
    Insere os registros na tabela RoomingNights do banco bihits.
    Se o registro já existir (mesmo id), ele atualiza os demais campos com os novos valores.
    """
    try:
        conn = mysql.connector.connect(
            host="localhost",
            user="root",      # Substitua pelo seu usuário do MySQL
            password="",    # Substitua pela sua senha
            database="bihits"
        )
    except Exception as e:
        print("Erro ao conectar com o MySQL:", e)
        return

    cursor = conn.cursor()

    sql = """
    INSERT INTO RoomingNights (
        id, propertyId, originId, type, roomId, pax, chd, chdAge1, chdAge2, chdAge3, chdAge4, age5,
        amount, endOfDayDate, date, dailyType, roomingNightCount, roomTypeId, version, requesterCity,
        companyId, companyName, companyTravelAgent, companyTravelAgentId, companyTravelAgentName,
        requesterCountry, guestId, guestName, guestMail, guestPhone, guestCellPhone, mealPlanType,
        mealPlanAmountPaxChd, qtyAgeNotDefined, qtyEightyOneToNinety, qtyElevenToFifteen, qtyFemale,
        qtyFiftyOneToSixty, qtyFortyOneToFifty, qtyFromZeroToTen, qtyGenderNotDefined, qtyGreaterThanNinety,
        qtyMale, qtySeventyOneToEighty, qtySixteenToTwenty, qtySixtyOneToSeventy, qtyThirtyOneToForty,
        qtyTwentyOneToThirty, rateId, rateName, ratePlanId, ratePlanName, requesterState, requesterId,
        requesterName, requesterTravelAgent, reservationChannelId, reservationChannelName, roomCode,
        roomTypeName, companyCity, companyCountry, companyState, guestCity, guestCountry, guestState,
        propertyName, managerId, managerName, marketSegmentId, marketSegmentName, purposeStay, purposeStayId,
        sourceChannelId, sourceChannelName, dateAdd, companyMainDoc, companyMainDocType, dailyMonth, dailyYear,
        `group`, groupName, guestMainDoc, guestMainDocType, originNumber, originType, requesterMainDoc,
        requesterMainDocType, reservationCreatedAt, reservationCreatedUser, reservationCreatedUserId,
        dateCheckin, dateCheckout, voucher, reservationStatus, reservationStatusName, feeAmount,
        chdRange1, chdRange2, chdRange3
    ) VALUES (
        %(id)s, %(propertyId)s, %(originId)s, %(type)s, %(roomId)s, %(pax)s, %(chd)s, %(chdAge1)s, %(chdAge2)s,
        %(chdAge3)s, %(chdAge4)s, %(age5)s, %(amount)s, %(endOfDayDate)s, %(date)s, %(dailyType)s,
        %(roomingNightCount)s, %(roomTypeId)s, %(version)s, %(requesterCity)s, %(companyId)s, %(companyName)s,
        %(companyTravelAgent)s, %(companyTravelAgentId)s, %(companyTravelAgentName)s, %(requesterCountry)s,
        %(guestId)s, %(guestName)s, %(guestMail)s, %(guestPhone)s, %(guestCellPhone)s, %(mealPlanType)s,
        %(mealPlanAmountPaxChd)s, %(qtyAgeNotDefined)s, %(qtyEightyOneToNinety)s, %(qtyElevenToFifteen)s,
        %(qtyFemale)s, %(qtyFiftyOneToSixty)s, %(qtyFortyOneToFifty)s, %(qtyFromZeroToTen)s, %(qtyGenderNotDefined)s,
        %(qtyGreaterThanNinety)s, %(qtyMale)s, %(qtySeventyOneToEighty)s, %(qtySixteenToTwenty)s,
        %(qtySixtyOneToSeventy)s, %(qtyThirtyOneToForty)s, %(qtyTwentyOneToThirty)s, %(rateId)s, %(rateName)s,
        %(ratePlanId)s, %(ratePlanName)s, %(requesterState)s, %(requesterId)s, %(requesterName)s,
        %(requesterTravelAgent)s, %(reservationChannelId)s, %(reservationChannelName)s, %(roomCode)s,
        %(roomTypeName)s, %(companyCity)s, %(companyCountry)s, %(companyState)s, %(guestCity)s,
        %(guestCountry)s, %(guestState)s, %(propertyName)s, %(managerId)s, %(managerName)s, %(marketSegmentId)s,
        %(marketSegmentName)s, %(purposeStay)s, %(purposeStayId)s, %(sourceChannelId)s, %(sourceChannelName)s,
        %(dateAdd)s, %(companyMainDoc)s, %(companyMainDocType)s, %(dailyMonth)s, %(dailyYear)s, %(group)s,
        %(groupName)s, %(guestMainDoc)s, %(guestMainDocType)s, %(originNumber)s, %(originType)s,
        %(requesterMainDoc)s, %(requesterMainDocType)s, %(reservationCreatedAt)s, %(reservationCreatedUser)s,
        %(reservationCreatedUserId)s, %(dateCheckin)s, %(dateCheckout)s, %(voucher)s, %(reservationStatus)s,
        %(reservationStatusName)s, %(feeAmount)s, %(chdRange1)s, %(chdRange2)s, %(chdRange3)s
    )
    ON DUPLICATE KEY UPDATE
        propertyId = VALUES(propertyId),
        originId = VALUES(originId),
        type = VALUES(type),
        roomId = VALUES(roomId),
        pax = VALUES(pax),
        chd = VALUES(chd),
        chdAge1 = VALUES(chdAge1),
        chdAge2 = VALUES(chdAge2),
        chdAge3 = VALUES(chdAge3),
        chdAge4 = VALUES(chdAge4),
        age5 = VALUES(age5),
        amount = VALUES(amount),
        endOfDayDate = VALUES(endOfDayDate),
        date = VALUES(date),
        dailyType = VALUES(dailyType),
        roomingNightCount = VALUES(roomingNightCount),
        roomTypeId = VALUES(roomTypeId),
        version = VALUES(version),
        requesterCity = VALUES(requesterCity),
        companyId = VALUES(companyId),
        companyName = VALUES(companyName),
        companyTravelAgent = VALUES(companyTravelAgent),
        companyTravelAgentId = VALUES(companyTravelAgentId),
        companyTravelAgentName = VALUES(companyTravelAgentName),
        requesterCountry = VALUES(requesterCountry),
        guestId = VALUES(guestId),
        guestName = VALUES(guestName),
        guestMail = VALUES(guestMail),
        guestPhone = VALUES(guestPhone),
        guestCellPhone = VALUES(guestCellPhone),
        mealPlanType = VALUES(mealPlanType),
        mealPlanAmountPaxChd = VALUES(mealPlanAmountPaxChd),
        qtyAgeNotDefined = VALUES(qtyAgeNotDefined),
        qtyEightyOneToNinety = VALUES(qtyEightyOneToNinety),
        qtyElevenToFifteen = VALUES(qtyElevenToFifteen),
        qtyFemale = VALUES(qtyFemale),
        qtyFiftyOneToSixty = VALUES(qtyFiftyOneToSixty),
        qtyFortyOneToFifty = VALUES(qtyFortyOneToFifty),
        qtyFromZeroToTen = VALUES(qtyFromZeroToTen),
        qtyGenderNotDefined = VALUES(qtyGenderNotDefined),
        qtyGreaterThanNinety = VALUES(qtyGreaterThanNinety),
        qtyMale = VALUES(qtyMale),
        qtySeventyOneToEighty = VALUES(qtySeventyOneToEighty),
        qtySixteenToTwenty = VALUES(qtySixteenToTwenty),
        qtySixtyOneToSeventy = VALUES(qtySixtyOneToSeventy),
        qtyThirtyOneToForty = VALUES(qtyThirtyOneToForty),
        qtyTwentyOneToThirty = VALUES(qtyTwentyOneToThirty),
        rateId = VALUES(rateId),
        rateName = VALUES(rateName),
        ratePlanId = VALUES(ratePlanId),
        ratePlanName = VALUES(ratePlanName),
        requesterState = VALUES(requesterState),
        requesterId = VALUES(requesterId),
        requesterName = VALUES(requesterName),
        requesterTravelAgent = VALUES(requesterTravelAgent),
        reservationChannelId = VALUES(reservationChannelId),
        reservationChannelName = VALUES(reservationChannelName),
        roomCode = VALUES(roomCode),
        roomTypeName = VALUES(roomTypeName),
        companyCity = VALUES(companyCity),
        companyCountry = VALUES(companyCountry),
        companyState = VALUES(companyState),
        guestCity = VALUES(guestCity),
        guestCountry = VALUES(guestCountry),
        guestState = VALUES(guestState),
        propertyName = VALUES(propertyName),
        managerId = VALUES(managerId),
        managerName = VALUES(managerName),
        marketSegmentId = VALUES(marketSegmentId),
        marketSegmentName = VALUES(marketSegmentName),
        purposeStay = VALUES(purposeStay),
        purposeStayId = VALUES(purposeStayId),
        sourceChannelId = VALUES(sourceChannelId),
        sourceChannelName = VALUES(sourceChannelName),
        dateAdd = VALUES(dateAdd),
        companyMainDoc = VALUES(companyMainDoc),
        companyMainDocType = VALUES(companyMainDocType),
        dailyMonth = VALUES(dailyMonth),
        dailyYear = VALUES(dailyYear),
        `group` = VALUES(`group`),
        groupName = VALUES(groupName),
        guestMainDoc = VALUES(guestMainDoc),
        guestMainDocType = VALUES(guestMainDocType),
        originNumber = VALUES(originNumber),
        originType = VALUES(originType),
        requesterMainDoc = VALUES(requesterMainDoc),
        requesterMainDocType = VALUES(requesterMainDocType),
        reservationCreatedAt = VALUES(reservationCreatedAt),
        reservationCreatedUser = VALUES(reservationCreatedUser),
        reservationCreatedUserId = VALUES(reservationCreatedUserId),
        dateCheckin = VALUES(dateCheckin),
        dateCheckout = VALUES(dateCheckout),
        voucher = VALUES(voucher),
        reservationStatus = VALUES(reservationStatus),
        reservationStatusName = VALUES(reservationStatusName),
        feeAmount = VALUES(feeAmount),
        chdRange1 = VALUES(chdRange1),
        chdRange2 = VALUES(chdRange2),
        chdRange3 = VALUES(chdRange3)
    """

    for registro in data:
        try:
            cursor.execute(sql, registro)
        except Exception as e:
            print("Erro ao inserir/atualizar registro:", e, registro)
    
    conn.commit()
    cursor.close()
    conn.close()
    print("Dados inseridos/atualizados com sucesso no banco de dados.")

def main():
    # Define o período de busca
    ini_date = "2024-01-01T00:00:00"
    fin_date = "2025-12-31T23:59:59"
    size = 50  # Número máximo de registros por página
    page = 0
    all_data = []
    debug_paginas = []
    
    # Obtém o token (utilizando cache)
    token = get_cached_token()
    
    headers = {
        "X-API-VERSION": "1",
        "X-API-TENANT-NAME": "thecoralbeachresort",
        "X-API-PROPERTY-CODE": "1",
        "X-API-PARTNER-USERID": "0",
        "X-API-LANGUAGE-CODE": "pt-br",
        "X-Client-Id": "THECORALBR",
        "Authorization": f"Bearer {token}"
    }
    
    # Paginação: busca os dados página a página
    while True:
        data = fetch_rooming_nights(page, headers, ini_date, fin_date, size)
        if not isinstance(data, list):
            print(f"Página {page} retornou uma estrutura inesperada: {data}")
            break
        
        count = len(data)
        debug_paginas.append(f"Página {page}: {count} registros carregados.")
        print(f"[Página {page}] {count} registros carregados.")
        
        if count == 0:
            break
        
        all_data.extend(data)
        
        # Se os registros retornados forem menores que o tamanho solicitado, encerra a busca
        if count < size:
            break
        
        page += 1
    
    print("\nLogs de Paginação:")
    for log in debug_paginas:
        print(log)
    
    # Insere ou atualiza os dados diretamente no banco de dados
    save_to_db(all_data)

if __name__ == '__main__':
    main()