satellite.py

from flask import Flask
from flask_cors import CORS
from flask_socketio import SocketIO, emit, join_room, leave_room
import os
from dotenv import load_dotenv
import uuid

from langchain.chains import ConversationChain
from langchain.memory import ConversationBufferMemory
from langchain_core.prompts import (
    ChatPromptTemplate,
    HumanMessagePromptTemplate,
    MessagesPlaceholder,
)
from langchain_core.messages import SystemMessage
from langchain_groq import ChatGroq

from groq import Groq
import sqlparse

from supabase import create_client, Client
load_dotenv()
app = Flask(__name__)
CORS(app)
app.config['SECRET_KEY'] = 'secret!'
socketio = SocketIO(app, cors_allowed_origins="*", async_mode='gevent')

llm = ChatGroq(
    groq_api_key=os.environ.get("GROQ_API_KEY"),
    model_name="llama3-70b-8192",
    temperature=0.1,
)

satellite_image_db_schema = '''
CREATE TABLE satellite_capture (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    date DATE NOT NULL,
    site TEXT NOT NULL,
    country TEXT NOT NULL,
    continent TEXT NOT NULL,
    lat_lon TEXT NOT NULL,
    long DOUBLE PRECISION NOT NULL,
    roll DOUBLE PRECISION NOT NULL,
    maxstrip REAL NOT NULL,
    status TEXT NOT NULL,
    image_state TEXT NOT NULL,
    comments TEXT NULL,
    capturedtrip REAL NOT NULL
);

'''

def query_conv(user_query):
    client = Groq(
        api_key=os.environ.get("GROQ_API_KEY")
    )
    query_prompt = f"""
### Task
Generate a SQL query to answer [QUESTION]{user_query}[/QUESTION]

### Instructions
- If you cannot answer the question with the available database schema, return 'I don't know'

## Information about Data attributes:
1. date: Date of the satellite image capture.
2. site: Name of the location where the satellite captured images.
3. country: Country where the capture site is located.
4. continent: Continent of the site.
5. lat_lon: Latitude and Longitude (GPS coordinates) of the site.
6. long: Distance measurement related to capture.
7. roll: Roll angle of the satellite during image capture.
8. maxstrip: Maximum Number of image strip captured for a site.
9. status: Capture success (OK) or failure (NOK).
10. image_state: Clarity of the image (Clear/Cloudy)
11. comments: Additional information on the capture, particularly for failed (NOK)
captures.
12. capturedtrip: Number of image strips captured for a site. Multiple strips may be needed for
full capture.

        
### Database Schema
This query will run on a database whose schema is represented in this string:
{satellite_image_db_schema}

### Answer
Given the database schema, here is the SQL query that answers [QUESTION]{user_query}[/QUESTION]
Response only in [SQL] format and do not include any other information.
"""

    chat_completion = client.chat.completions.create(
        messages=[
            {
                "role": "system",
                "content": query_prompt,
            },
            {
                "role": "user",
                "content": user_query,
            },
        ],
        model="llama3-70b-8192",
    )

    query = chat_completion.choices[0].message.content
    sql_query = sqlparse.format(query, reindent=True)
    return sql_query


SUPABASE_URL = os.environ.get('SUPABASE_URL')
SUPABASE_KEY = os.environ.get('SUPABASE_KEY')
ACCESS_TOKEN = os.environ.get('ACCESS_TOKEN')
supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)


# Use your Database API endpoint here to query the database and return result
def execute_sql_query(sql_query):
    if sql_query == "I don't know":
        return "I don't know"

    # #API here  
    response = supabase.table("satellite_capture").select("*", count="exact").execute()
    return response


def generate_nl_response(user_query, sql_query, query_results):
    client = Groq(
        api_key=os.environ.get("GROQ_API_KEY"),
    )
    prompt = f"""
### Task
Generate a natural language response to the user's query based on the SQL query results.

### User Query
{user_query}

### SQL Query
{sql_query}

### Query Results
{query_results}

## Database Schema:
{satellite_image_db_schema}

## Information about Data attributes:
1. date: Date of the satellite image capture.
2. site: Name of the location where the satellite captured images.
3. country: Country where the capture site is located.
4. continent: Continent of the site.
5. lat_lon: Latitude and Longitude (GPS coordinates) of the site.
6. long: Distance measurement related to capture.
7. roll: Roll angle of the satellite during image capture.
8. maxstrip: Maximum Number of image strip captured for a site.
9. status: Capture success (OK) or failure (NOK).
10. image_state: Clarity of the image (Clear/Cloudy)
11. comments: Additional information on the capture, particularly for failed (NOK)
captures.
12. capturedtrip: Number of image strips captured for a site. Multiple strips may be needed for
full capture.

## Instructions
- Give your answer short and to the point. 
- Do not include any SQL or database operations in your response. 
- Don't tell anything about the database schema. 
- If you cannot answer the question with the available database schema, return 'I don't know'.
- If user asks for a specific data, provide the data in a clear and concise manner.

"""

    chat_completion = client.chat.completions.create(
        messages=[
            {
                "role": "system",
                "content": prompt,
            }
        ],
        model="llama3-70b-8192",
    )

    return chat_completion.choices[0].message.content

system_prompt = """You are an expert in satellite image data analysis. Your role is to assist users by answering questions about satellite captures using the available database. 
"""

user_conversations = {}

@socketio.on('connect')
def handle_connect():
    user_id = str(uuid.uuid4())
    join_room(user_id)
    emit('set_user_id', {'user_id': user_id})
    print(f'Client connected with ID: {user_id}')

@socketio.on('join')
def on_join(data):
    user_id = data['user_id']
    join_room(user_id)
    print(f'User {user_id} joined their room')

@socketio.on('disconnect')
def handle_disconnect():
    print('Client disconnected')

@socketio.on('user_message')
def handle_message(message):
    user_id = message['user_id']
    user_query = message['data']
    print(f'User {user_id}: {user_query}')

    if user_id not in user_conversations:
        memory = ConversationBufferMemory(return_messages=True)
        prompt = ChatPromptTemplate.from_messages([
            SystemMessage(content=system_prompt),
            MessagesPlaceholder(variable_name="history"),
            HumanMessagePromptTemplate.from_template("{input}")
        ])
        user_conversations[user_id] = ConversationChain(
            llm=llm,
            memory=memory,
            prompt=prompt,
            verbose=False
        )

    conversation = user_conversations[user_id]
    
    sql_query = query_conv(user_query)
    print('Generated SQL query:', sql_query)
    
    query_results = execute_sql_query(sql_query)
    print('Query results:', query_results)
    
    nl_response = generate_nl_response(user_query, sql_query, query_results)
    print('Natural language response:', nl_response)
    
    conversation.predict(input=f"User: {user_query}\nAI: {nl_response}")
    
    emit('bot_response', {'data': nl_response}, room=user_id)

if __name__ == '__main__':
    socketio.run(app, debug=True)