function_app.py

import azure.functions as func
import datetime
import json
import logging
import os
import pyodbc
import azure.functions as func
from collections import defaultdict
import openai


app = func.FunctionApp()

@app.route(route="HttpClockTrigger", auth_level=func.AuthLevel.ANONYMOUS)
def HttpClockTrigger(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    openai.api_key = os.getenv("OPENAI_API_KEY")

    # Step 1: Get database connection
    server = os.getenv("SQL_SERVER")
    database = os.getenv("SQL_DATABASE")
    username = os.getenv("SQL_USERNAME")
    password = os.getenv("SQL_PASSWORD")
    driver = '{ODBC Driver 18 for SQL Server}'  # Or 'ODBC Driver 17 for SQL Server'

    try:
        connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()
        #cursor.execute("SELECT TOP 5 * FROM Employees")
        #rows = cursor.fetchall()

        cursor.execute("""
            SELECT
            e.EmployeeID, e.Name, e.Department, e.Role, e.Email,
            p.ProjectID, p.ProjectName, p.Status AS ProjectStatus,
            t.TaskID, t.Title AS TaskTitle, t.Status AS TaskStatus, t.DueDate, t.Priority,
            i.IssueID, i.Title AS IssueTitle, i.Severity
        FROM Employees e
        LEFT JOIN Tasks t ON e.EmployeeID = t.AssignedTo
        LEFT JOIN Projects p ON t.ProjectID = p.ProjectID
        LEFT JOIN Issues i ON t.TaskID = i.TaskID
        """)

    # Step 2: Build employee documents
        employees = defaultdict(lambda: {
            "projects": [],
            "tasks": [],
            "issues": [],
            "full_text": ""
        })

        for row in cursor.fetchall():
            emp = employees[row.EmployeeID]
            emp["id"] = row.EmployeeID
            emp["name"] = row.Name
            emp["department"] = row.Department
            emp["role"] = row.Role
            emp["email"] = row.Email

    # Avoid duplicates
        if row.ProjectID and not any(p["project_id"] == row.ProjectID for p in emp["projects"]):
            emp["projects"].append({
                "project_id": row.ProjectID,
                "name": row.ProjectName,
                "status": row.ProjectStatus
            })

        if row.TaskID and not any(t["task_id"] == row.TaskID for t in emp["tasks"]):
            emp["tasks"].append({
                "task_id": row.TaskID,
                "title": row.TaskTitle,
                "status": row.TaskStatus,
                "priority": row.Priority,
                "due_date": str(row.DueDate) if row.DueDate else None
        })

        if row.IssueID and not any(i["issue_id"] == row.IssueID for i in emp["issues"]):
            emp["issues"].append({
                "issue_id": row.IssueID,
                "title": row.IssueTitle,
                "severity": row.Severity
            })

        # Build full_text
        emp["full_text"] += f"{row.Name} works in {row.Department} as {row.Role}. "
        if row.ProjectName:
            emp["full_text"] += f"Working on project {row.ProjectName}. "
        if row.TaskTitle:
            emp["full_text"] += f"Task assigned: {row.TaskTitle}. "
        if row.IssueTitle:
            emp["full_text"] += f"Issue reported: {row.IssueTitle}. "

        #Step 3: Generate embeddings
        for emp in employees.values():
            response = openai.Embedding.create(
            input=emp["full_text"],
            model="text-embedding-ada-002"
        )
        emp["embedding"] = response["data"][0]["embedding"]

        cosmos_client = CosmosClient(os.getenv("COSMOS_ENDPOINT"), credential=os.getenv("COSMOS_KEY"))
        db = cosmos_client.get_database_client("genai-rag-db")
        container = db.get_container_client("documents")

        for emp in employees.values():
            container.upsert_item(emp)

        return func.HttpResponse(f"Query result:\n{emp}", status_code=200)    

    #output = "\n".join([str(row) for row in rows])
    #return func.HttpResponse(f"Query result:\n{output}", status_code=200)

    except Exception as e:
        return func.HttpResponse(f"Database connection failed:\n{str(e)}", status_code=500)



    #name = req.params.get('name')
    #if not name:
    #    try:
    #        req_body = req.get_json()
    #    except ValueError:
    #        pass
    #    else:
    #        name = req_body.get('name')

    #if name:
    #    return func.HttpResponse(f"Hello, {name}. This HTTP triggered function executed successfully.")
    #else:
    #    return func.HttpResponse(
    #         "This HTTP triggered function executed successfully. Pass a name in the query string or in the request body for a personalized response.",
    #         status_code=200
    #    )