import pymysql
import pandas as pd
import requests

db_config = {'host': '127.0.0.1', 'user': 'root', 'password': 'RootDb@2024', 'database': 'posacct_pos_db', 'cursorclass': pymysql.cursors.DictCursor}

def get_pos_data():
    try:
        conn = pymysql.connect(**db_config)
        with conn.cursor() as cursor:
            # Using the correct column names found via DESC command
            cursor.execute("SELECT id, sales_no, sales_date, payment_status FROM sales_master ORDER BY id DESC LIMIT 10")
            rows = cursor.fetchall()
        conn.close()
        return rows
    except Exception as e:
        print(f"DB Error: {e}")
        return []

def ask_llama(prompt):
    print("AI is matching transactions (this may take a minute)...")
    try:
        r = requests.post("http://localhost:11434/api/generate", 
                         json={"model": "llama3:latest", "prompt": prompt, "stream": False}, 
                         timeout=300)
        return r.json().get('response', 'No response')
    except Exception as e:
        return f"AI Error: {e}"

print("\n--- AI BANK RECONCILIATION ---")
data = get_pos_data()
if not data:
    prompt = "I am ready for bank reconciliation. Give me 3 best practices for automated matching."
else:
    prompt = f"Here is the POS transaction data from 'sales_master':\n{data}\n\nTask: Analyze these for any missing bank entries based on common patterns."

print(ask_llama(prompt))