from flask import Flask, request, jsonify import gspread from google.oauth2.service_account import Credentials import logging app = Flask(__name__) logging.basicConfig(level=logging.INFO) # --- Google Sheets connection with proper scopes --- # NOTE: Ensure 'credentials.json' is in the same directory as this script. scope = [ "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive" ] try: creds = Credentials.from_service_account_file("credentials.json", scopes=scope) client = gspread.authorize(creds) # Open the sheet by URL and get the first worksheet (sheet1) SHEET_URL = "https://docs.google.com/spreadsheets/d/1Rx-pKhpMuwZOCrhyEDeBx39X1OFboblJiDOt8Rbtl0U/edit#gid=0" sheet = client.open_by_url(SHEET_URL).sheet1 logging.info("Google Sheets connection successful.") except Exception as e: logging.error(f"Failed to connect to Google Sheets: {e}") # In a production app, you might stop execution here or handle it gracefully. def find_company_row_index(company_name, sheet): """ Finds the 1-based row index for a company name (case-insensitive). Assumes company names are in the first column (Column A). """ try: # Get all values from the first column (where company names are expected) company_column_values = sheet.col_values(1) target_name = company_name.strip().lower() # Iterate from the second row (index 1) since the first row (index 0) is headers for i, name in enumerate(company_column_values): if i == 0: continue # Skip the header row if name and name.strip().lower() == target_name: # Return 1-based index (i + 1) return i + 1 return None except Exception as e: logging.error(f"Error finding company row: {e}") return None def get_column_indices(sheet): """ Dynamically maps column headers to their 1-based indices. """ try: headers = sheet.row_values(1) col_map = { "company name": None, "status": None, "nextcalldate": None, } for i, header in enumerate(headers): key = header.strip().lower() if key in col_map: # 1-based column index col_map[key] = i + 1 return col_map except Exception as e: logging.error(f"Error reading column headers: {e}") return {} # --- NEW FUNCTION TO GET ALL COMPANY NAMES --- @app.route("/get_all_names", methods=["GET"]) def get_all_names(): """ READ: Returns a list of all non-empty company names from the sheet (Column A). """ logging.info("📝 Received get_all_names request.") try: # Get all values from the first column (where company names are assumed to be) company_column_values = sheet.col_values(1) # Slice to skip the header (index 0) and filter out empty strings names = [ name.strip() for name in company_column_values[1:] if name and name.strip() ] return jsonify({ "success": True, "company_names": names, "count": len(names) }), 200 except Exception as e: logging.error(f"Error processing get_all_names: {e}") return jsonify({"error": "An internal error occurred while fetching company names."}), 500 # --------------------------------------------- @app.route("/check_sheet", methods=["POST"]) def check_sheet(): """ READ: Checks the current status and next call date for a company. """ data = request.get_json() logging.info("⚡ Received check_sheet request from Vapi: %s", data) name = data.get("name") if not name: return jsonify({"error": "Missing 'name' in request payload"}), 400 try: # Using get_all_records() is simple for reading for row in sheet.get_all_records(): if str(row.get("company name", "")).strip().lower() == name.strip().lower(): return jsonify({ "company": row.get("company name"), "status": row.get("status"), "nextcalldate": row.get("nextcalldate") }) return jsonify({"error": f"No matching record found for company: {name}"}), 404 except Exception as e: logging.error(f"Error processing check_sheet: {e}") return jsonify({"error": "An internal error occurred during lookup."}), 500 @app.route("/update_call_details", methods=["POST"]) def update_call_details(): """ UPDATE or CHECK: Checks if the new nextcalldate is already present. If it is, returns 'Already Booked'. If not, updates the sheet. """ data = request.get_json() logging.info("📝 Received update_call_details request: %s", data) name = data.get("name") new_nextcalldate = data.get("nextcalldate") # e.g., "2024-05-30 14:00" new_status = data.get("status") # e.g., "Meeting Booked" if not name: return jsonify({"error": "Missing 'name' to identify the company."}), 400 # Only proceed if there's data to update or a new date to check/set if not new_nextcalldate and not new_status: return jsonify({"message": "No update data provided (need 'nextcalldate' or 'status')."}), 200 try: # 1. Find the company's row index (1-based) row_index = find_company_row_index(name, sheet) if not row_index: return jsonify({"error": f"No matching company record found for update: {name}"}), 404 # 2. Get the column indices dynamically col_map = get_column_indices(sheet) nextcalldate_col_index = col_map.get("nextcalldate") status_col_index = col_map.get("status") updates = {} # --- CONDITIONAL CHECK AND UPDATE LOGIC for nextcalldate --- if new_nextcalldate and nextcalldate_col_index: # Read existing nextcalldate from the sheet existing_nextcalldate = sheet.cell(row_index, nextcalldate_col_index).value # Normalize dates for case-insensitive comparison normalized_new_date = new_nextcalldate.strip().lower() # If the cell is empty (None or ''), normalized_existing_date will be an empty string normalized_existing_date = str(existing_nextcalldate or "").strip().lower() # Check if the new date matches the existing one if normalized_new_date and normalized_new_date == normalized_existing_date: # 1. The date is already booked as this exact time. logging.info(f"Next call date for {name} is already booked as {new_nextcalldate}. No update needed.") return jsonify({ "success": True, "message": f"This call date/time is already booked as {new_nextcalldate}. No update performed.", "status_code": "ALREADY_BOOKED" }), 200 else: # 2. The date is new or different. Proceed to schedule/update it. sheet.update_cell(row_index, nextcalldate_col_index, new_nextcalldate) updates["nextcalldate"] = new_nextcalldate logging.info(f"Updated nextcalldate for {name} to {new_nextcalldate} (Scheduled).") # --- STATUS UPDATE LOGIC --- # Update status only if a new status is provided OR if we just scheduled a new date, # and the status column index is known. if new_status and status_col_index: col_index = col_map["status"] sheet.update_cell(row_index, col_index, new_status) updates["status"] = new_status logging.info(f"Updated status for {name} to {new_status}") if not updates: # This handles the case where only 'new_nextcalldate' was provided but 'nextcalldate' column was not found. return jsonify({"message": "Update failed: Column headers not found or data was not processed."}), 400 # Return success for scheduling return jsonify({ "success": True, "message": f"Successfully scheduled new date/time and updated details for {name}.", "updates": updates, "status_code": "SCHEDULED" }), 200 except Exception as e: logging.error(f"Error processing update_call_details: {e}") return jsonify({"error": "An internal error occurred during the update."}), 500 if __name__ == "__main__": app.run(port=5000)