import sys import requests import json import os import time import mysql.connector import logging from datetime import datetime, timedelta, timezone from zoneinfo import ZoneInfo TZ = ZoneInfo('America/Fortaleza') FULL_SYNC_START = '2024-01-01T00:00:00Z' POLL_INTERVAL = 300 PAGE_SIZE = 50 STATUSES = [1, 2, 3, 4] def get_base_dirs(): exe_dir = os.path.dirname(os.path.abspath(__file__)) base_dir = os.path.dirname(exe_dir) data_dir = os.path.join(base_dir, 'data') log_root = os.path.join(base_dir, 'logs') return base_dir, data_dir, log_root BASE_DIR, DATA_DIR, LOG_ROOT = get_base_dirs() LOG_DIRS = { 'errors': os.path.join(LOG_ROOT, 'errors'), 'updates': os.path.join(LOG_ROOT, 'updates'), 'inserts': os.path.join(LOG_ROOT, 'inserts'), 'operations': os.path.join(LOG_ROOT, 'operations'), 'api': os.path.join(LOG_ROOT, 'api'), } FLAG_FILE = os.path.join(DATA_DIR, 'flag_fullsync.ok') os.makedirs(DATA_DIR, exist_ok=True) for path in LOG_DIRS.values(): os.makedirs(path, exist_ok=True) def setup_logger(name, path, level=logging.INFO): logger = logging.getLogger(name) if logger.hasHandlers(): return logger logger.setLevel(level) fmt = logging.Formatter('%(asctime)s [%(levelname)s] %(message)s', '%Y-%m-%d %H:%M:%S') fh = logging.FileHandler(path, encoding='utf-8') fh.setFormatter(fmt) logger.addHandler(fh) ch = logging.StreamHandler() ch.setFormatter(fmt) logger.addHandler(ch) return logger log_ops = setup_logger('operations', os.path.join(LOG_DIRS['operations'], 'operations.log')) log_err = setup_logger('errors', os.path.join(LOG_DIRS['errors'], 'errors.log'), level=logging.ERROR) log_upd = setup_logger('updates', os.path.join(LOG_DIRS['updates'], 'updates.log')) log_ins = setup_logger('inserts', os.path.join(LOG_DIRS['inserts'], 'inserts.log')) log_api = setup_logger('api', os.path.join(LOG_DIRS['api'], 'api.log')) def get_db_connection(): return mysql.connector.connect( host=os.getenv('DB_HOST', 'localhost'), user=os.getenv('DB_USER', 'root'), password=os.getenv('DB_PASS', ''), database=os.getenv('DB_NAME', 'bihits') ) def parse_iso(dt_str): if not dt_str: return None if dt_str.endswith('Z'): dt = datetime.fromisoformat(dt_str[:-1] + '+00:00') else: dt = datetime.fromisoformat(dt_str) return dt.astimezone(timezone.utc) def get_existing_reservations_map(): conn = get_db_connection() cur = conn.cursor() cur.execute("SELECT idReservation, idEntity, dateUp FROM reservations") m = { (rid, eid): du for rid, eid, du in cur } cur.close(); conn.close() return m def get_existing_details_map(): conn = get_db_connection() cur = conn.cursor() cur.execute("SELECT idReservation, idEntityCompany, dateUp FROM reservation_details") m = { (rid, eidc): du for rid, eidc, du in cur } cur.close(); conn.close() return m def load_cache(path): if not os.path.isfile(path): return {} with open(path, 'r', encoding='utf-8') as f: data = json.load(f) return data def save_cache(path, data_dict): with open(path, 'w', encoding='utf-8') as f: json.dump(list(data_dict.values()), f, ensure_ascii=False, indent=2) def truncate_tables(): conn = get_db_connection() cur = conn.cursor() cur.execute("SET FOREIGN_KEY_CHECKS=0;") cur.execute("TRUNCATE TABLE reservation_details;") cur.execute("TRUNCATE TABLE reservations;") cur.execute("SET FOREIGN_KEY_CHECKS=1;") conn.commit() cur.close(); conn.close() log_ops.info("Tabelas truncadas para full-sync inicial.") def safe_get(url, headers, params=None, timeout=30): delay = 5 for attempt in range(6): try: r = requests.get(url, headers=headers, params=params, timeout=timeout) log_api.info(f"GET {r.url} → {r.status_code}") if r.status_code == 401: raise requests.HTTPError('401 Unauthorized') if r.status_code == 429: log_api.warning(f"429 rate limit, retry em {delay}s") time.sleep(delay); delay *= 2; continue r.raise_for_status() return r except requests.HTTPError as he: log_err.error(f"HTTPError: {he}") if '401' in str(he): return None time.sleep(delay); delay *= 2 except Exception as e: log_err.error(f"Request error: {e}") time.sleep(delay); delay *= 2 return None def get_token(): url = "https://bric-investment.com/BI-HITS/dados/Hits/token/token.php" payload = {"username": os.getenv('API_USER'), "password": os.getenv('API_PASS')} try: r = requests.post(url, data=payload, timeout=30) r.raise_for_status() text = r.text.strip() if text.startswith('Token obtido:'): token = text.split(':',1)[1].strip() else: token = r.json().get('token') log_ops.info("Token obtido com sucesso.") return token except Exception as e: log_err.error(f"Erro ao obter token: {e}") return None def fetch_reservations(token, start_date, end_date, type_filter, status): url = "https://api.hitspms.net/Datashare/WebCheckinOut/Reservations" headers = { "Accept": "application/json", "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}" } page = 0 results = [] while True: params = { "Type": type_filter, "Status": status, "InitialDate": start_date, "FinalDate": end_date, "Page": page, "Size": PAGE_SIZE } resp = safe_get(url, headers, params) if resp is None: return [] data = resp.json() if not data: break results.extend(data) if len(data) < PAGE_SIZE: break page += 1 time.sleep(0.5) return results def fetch_reservation_by_id(token, reservation_id): url = f"https://api.hitspms.net/Datashare/WebCheckinOut/Reservation/{reservation_id}" headers = { "Accept": "application/json", "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}" } resp = safe_get(url, headers) if resp and resp.status_code == 200: return resp.json() return None def upsert_reservations(res_list): existing = get_existing_reservations_map() sql = """ INSERT INTO reservations (idReservation,idEntity,name,phone,main,mail, zipCode,addressDetails,address,neighborhood,number,city, country,stateName,stateCode,federalRegistrationNumber, documentType,checkIn,checkOut,dateAdd,dateUp,status,integrator,channel,raw_json) VALUES (%(idReservation)s,%(idEntity)s,%(name)s,%(phone)s,%(main)s,%(mail)s, %(zipCode)s,%(addressDetails)s,%(address)s,%(neighborhood)s,%(number)s,%(city)s, %(country)s,%(stateName)s,%(stateCode)s,%(federalRegistrationNumber)s, %(documentType)s,%(checkIn)s,%(checkOut)s,%(dateAdd)s,%(dateUp)s, %(status)s,%(integrator)s,%(channel)s,%(raw_json)s) ON DUPLICATE KEY UPDATE name=VALUES(name),phone=VALUES(phone),main=VALUES(main),mail=VALUES(mail), zipCode=VALUES(zipCode),addressDetails=VALUES(addressDetails),address=VALUES(address), neighborhood=VALUES(neighborhood),number=VALUES(number),city=VALUES(city), country=VALUES(country),stateName=VALUES(stateName),stateCode=VALUES(stateCode), federalRegistrationNumber=VALUES(federalRegistrationNumber), documentType=VALUES(documentType),checkIn=VALUES(checkIn),checkOut=VALUES(checkOut), dateUp=VALUES(dateUp),status=VALUES(status),integrator=VALUES(integrator), channel=VALUES(channel),raw_json=VALUES(raw_json) """ conn = get_db_connection() cur = conn.cursor() for r in res_list: key = (r['idReservation'], r['idEntity']) dt_api = parse_iso(r['dateUp']) if key not in existing or dt_api > existing[key]: params = {**r, 'raw_json': json.dumps(r, ensure_ascii=False)} cur.execute(sql, params) if key not in existing: log_ins.info(f"[INSERT] reservations {key}") else: log_upd.info(f"[UPDATE] reservations {key}") conn.commit() cur.close() conn.close() def upsert_reservation_details(details_list): existing = get_existing_details_map() sql = """ INSERT INTO reservation_details (idReservation,idEntityCompany,companyName,idRequesterCompany, requesterCompanyName,groupName,contactName,contact1,contact2, dateAdd,dateUp,creditState,notes,rooms,guests,commissions, revenueManagement,raw_json) VALUES (%(idReservation)s,%(idEntityCompany)s,%(companyName)s,%(idRequesterCompany)s, %(requesterCompanyName)s,%(groupName)s,%(contactName)s,%(contact1)s,%(contact2)s, %(dateAdd)s,%(dateUp)s,%(creditState)s,%(notes)s,%(rooms)s,%(guests)s, %(commissions)s,%(revenueManagement)s,%(raw_json)s) ON DUPLICATE KEY UPDATE idEntityCompany=VALUES(idEntityCompany),companyName=VALUES(companyName), idRequesterCompany=VALUES(idRequesterCompany), requesterCompanyName=VALUES(requesterCompanyName),groupName=VALUES(groupName), contactName=VALUES(contactName),contact1=VALUES(contact1),contact2=VALUES(contact2), dateUp=VALUES(dateUp),creditState=VALUES(creditState),notes=VALUES(notes), rooms=VALUES(rooms),guests=VALUES(guests),commissions=VALUES(commissions), revenueManagement=VALUES(revenueManagement),raw_json=VALUES(raw_json) """ conn = get_db_connection() cur = conn.cursor() for d in details_list: key = (d['idReservation'], d['idEntityCompany']) dt_api = parse_iso(d['dateUp']) if key not in existing or dt_api > existing[key]: payload = { **d, 'notes': json.dumps(d.get('notes', []), ensure_ascii=False), 'rooms': json.dumps(d.get('rooms', []), ensure_ascii=False), 'guests': json.dumps(d.get('guests', []), ensure_ascii=False), 'commissions': json.dumps(d.get('commissions', []), ensure_ascii=False), 'revenueManagement': json.dumps(d.get('revenueManagement', {}), ensure_ascii=False), 'raw_json': json.dumps(d, ensure_ascii=False) } cur.execute(sql, payload) if key not in existing: log_ins.info(f"[INSERT] reservation_details {key}") else: log_upd.info(f"[UPDATE] reservation_details {key}") conn.commit() cur.close() conn.close() def main(): path_res = os.path.join(DATA_DIR, 'reservations.json') path_det = os.path.join(DATA_DIR, 'reservation_details.json') initial_run = not os.path.isfile(FLAG_FILE) if initial_run: log_ops.info("Primeira execução: iniciando full-sync inicial.") truncate_tables() open(FLAG_FILE, 'w').close() old_res_data = {} old_det_data = {} else: old_res_data = { (r['idReservation'], r['idEntity']): r for r in load_cache(path_res) } old_det_data = { (d['idReservation'], d['idEntityCompany']): d for d in load_cache(path_det) } if old_res_data: last_dateUp = max(parse_iso(r['dateUp']) for r in old_res_data.values()) else: last_dateUp = datetime.fromisoformat(FULL_SYNC_START.replace('Z', '+00:00')) while True: now_utc = datetime.now(timezone.utc) type_filters = [2] start_dt = last_dateUp - timedelta(minutes=5) start_date = start_dt.strftime('%Y-%m-%dT%H:%M:%SZ') end_date = now_utc.strftime('%Y-%m-%dT%H:%M:%SZ') log_ops.info(f"Sync window: {start_date} → {end_date}") token = get_token() if not token: log_err.error("Falha ao obter token, pulando ciclo.") time.sleep(POLL_INTERVAL) continue all_api_map_data = {} all_api_map_time = {} calls = [(t, s) for t in type_filters for s in STATUSES] for i, (type_f, status) in enumerate(calls): recs = fetch_reservations(token, start_date, end_date, type_f, status) if recs is None: token = get_token() or token continue for r in recs: key = (r['idReservation'], r['idEntity']) dt_api = parse_iso(r['dateUp']) if key not in all_api_map_time or dt_api > all_api_map_time[key]: all_api_map_time[key] = dt_api all_api_map_data[key] = r if i < len(calls) - 1: time.sleep(POLL_INTERVAL) to_delete = set(old_res_data.keys()) - set(all_api_map_data.keys()) if to_delete: conn = get_db_connection(); cur = conn.cursor() for rid, eid in to_delete: cur.execute("DELETE FROM reservation_details WHERE idReservation=%s AND idEntityCompany=%s", (rid, eid)) cur.execute("DELETE FROM reservations WHERE idReservation=%s AND idEntity=%s", (rid, eid)) old_res_data.pop((rid, eid), None) old_det_data.pop((rid, eid), None) log_ops.info(f"[DELETE] reservation {rid},{eid}") conn.commit(); cur.close(); conn.close() to_fetch = [key for key, dt in all_api_map_time.items() if key not in old_res_data or dt > parse_iso(old_res_data[key]['dateUp'])] new_details = [] for num, key in enumerate(to_fetch, 1): rid, eid = key log_ops.info(f"[{num}/{len(to_fetch)}] Fetch detail {key}") det = fetch_reservation_by_id(token, rid) if det is None: token = get_token() or token det = fetch_reservation_by_id(token, rid) if det: dkey = (det['idReservation'], det['idEntityCompany']) old_det_data[dkey] = det new_details.append(det) old_res_data[key] = all_api_map_data[key] time.sleep(2.2) upsert_reservations(list(all_api_map_data.values())) upsert_reservation_details(new_details) if all_api_map_time: last_dateUp = max(all_api_map_time.values()) save_cache(path_res, old_res_data) save_cache(path_det, old_det_data) log_ops.info("Ciclo completo, aguardando próximo...") time.sleep(POLL_INTERVAL) if __name__ == '__main__': main()