crew/tools/government_db_tools.py

# tools/government_db_tools.py

from crew.tools.db_utils import run_query

GOVERNMENT_DB_PATH = "database/government.db"

def get_pr_status(customer_id: str):
    """
    Fetch PR (Permanent Resident) status from government DB.
    Returns:
    - True/False (if record exists)
    - "NOT_FOUND" string (if ID is invalid)
    """
    query = """
        SELECT pr_status
        FROM pr_details
        WHERE id=?
    """
    try:
        result = run_query(GOVERNMENT_DB_PATH, query, (customer_id,))
        
        if result and len(result) > 0:
            # Return the actual status (True/False)
            return bool(result[0][0])
        else:
            # Return text signal so the agent knows the ID is wrong/missing
            return f"NOT_FOUND: No government record found for ID '{customer_id}'."

    except Exception as e:
        return f"ERROR: Government DB failure for ID '{customer_id}': {str(e)}"


def get_government_record(customer_id: str):
    """
    Fetch the full record: name, email, PR status.
    Useful for debugging or extended compliance checks.
    """
    query = """
        SELECT name, email, pr_status
        FROM pr_details
        WHERE id=?
    """
    try:
        result = run_query(GOVERNMENT_DB_PATH, query, (customer_id,))
        
        if result and len(result) > 0:
            name, email, pr_status = result[0]
            return {
                "customer_id": customer_id,
                "name": name,
                "email": email,
                "pr_status": bool(pr_status),
            }
        else:
            return f"NOT_FOUND: Government record unavailable for ID '{customer_id}'."

    except Exception as e:
        return f"ERROR: Failed to fetch government record for ID '{customer_id}': {str(e)}"