app.py

import gradio as gr
import sqlite3
from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import logging
import json
import transformers
import torch
from transformers import pipeline

# Database Operations Class
class DatabaseManager:
    def __init__(self, db_path='crm.db'):
        self.db_path = db_path
        self.engine = create_engine(f'sqlite:///{db_path}')
        Session = sessionmaker(bind=self.engine)
        self.session = Session()
        self.setup_database()

    def setup_database(self):
        """Create database tables if they don't exist"""
        queries = [
            """
            CREATE TABLE IF NOT EXISTS customers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                phone TEXT,
                address TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """,
            """
            CREATE TABLE IF NOT EXISTS tickets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                customer_id INTEGER,
                subject TEXT NOT NULL,
                description TEXT,
                status TEXT DEFAULT 'open',
                priority TEXT DEFAULT 'medium',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (customer_id) REFERENCES customers (id)
            )
            """,
            """
            CREATE TABLE IF NOT EXISTS interactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                customer_id INTEGER,
                ticket_id INTEGER,
                message TEXT NOT NULL,
                response TEXT,
                sentiment_score FLOAT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (customer_id) REFERENCES customers (id),
                FOREIGN KEY (ticket_id) REFERENCES tickets (id)
            )
            """
        ]

        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        for query in queries:
            cursor.execute(query)
        conn.commit()
        conn.close()

    # Customer Operations
    def add_customer(self, name, email, phone=None, address=None):
        """Add a new customer"""
        try:
            query = """
            INSERT INTO customers (name, email, phone, address)
            VALUES (?, ?, ?, ?)
            """
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(query, (name, email, phone, address))
            customer_id = cursor.lastrowid
            conn.commit()
            conn.close()
            return {"status": "success", "customer_id": customer_id}
        except sqlite3.IntegrityError:
            return {"status": "error", "message": "Email already exists"}
        except Exception as e:
            return {"status": "error", "message": str(e)}

    def get_customer(self, customer_id):
        """Get customer details"""
        query = "SELECT * FROM customers WHERE id = ?"
        conn = sqlite3.connect(self.db_path)
        result = pd.read_sql_query(query, conn, params=(customer_id,))
        conn.close()
        return result.to_dict('records')[0] if not result.empty else None

    def update_customer(self, customer_id, **kwargs):
        """Update customer details"""
        try:
            set_clause = ", ".join([f"{k} = ?" for k in kwargs.keys()])
            query = f"""
            UPDATE customers
            SET {set_clause}, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
            """
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(query, (*kwargs.values(), customer_id))
            conn.commit()
            conn.close()
            return {"status": "success"}
        except Exception as e:
            return {"status": "error", "message": str(e)}

    def delete_customer(self, customer_id):
        """Delete a customer"""
        try:
            query = "DELETE FROM customers WHERE id = ?"
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(query, (customer_id,))
            conn.commit()
            conn.close()
            return {"status": "success"}
        except Exception as e:
            return {"status": "error", "message": str(e)}

    # Ticket Operations
    def create_ticket(self, customer_id, subject, description, priority="medium"):
        """Create a new support ticket"""
        try:
            query = """
            INSERT INTO tickets (customer_id, subject, description, priority)
            VALUES (?, ?, ?, ?)
            """
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(query, (customer_id, subject, description, priority))
            ticket_id = cursor.lastrowid
            conn.commit()
            conn.close()
            return {"status": "success", "ticket_id": ticket_id}
        except Exception as e:
            return {"status": "error", "message": str(e)}

    def get_ticket(self, ticket_id):
        """Get ticket details"""
        query = """
        SELECT t.*, c.name as customer_name
        FROM tickets t
        JOIN customers c ON t.customer_id = c.id
        WHERE t.id = ?
        """
        conn = sqlite3.connect(self.db_path)
        result = pd.read_sql_query(query, conn, params=(ticket_id,))
        conn.close()
        return result.to_dict('records')[0] if not result.empty else None

    def update_ticket(self, ticket_id, **kwargs):
        """Update ticket details"""
        try:
            set_clause = ", ".join([f"{k} = ?" for k in kwargs.keys()])
            query = f"""
            UPDATE tickets
            SET {set_clause}, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
            """
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(query, (*kwargs.values(), ticket_id))
            conn.commit()
            conn.close()
            return {"status": "success"}
        except Exception as e:
            return {"status": "error", "message": str(e)}

    # Interaction Operations
    def add_interaction(self, customer_id, ticket_id, message, response=None, sentiment_score=None):
        """Record a new interaction"""
        try:
            query = """
            INSERT INTO interactions (customer_id, ticket_id, message, response, sentiment_score)
            VALUES (?, ?, ?, ?, ?)
            """
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            cursor.execute(query, (customer_id, ticket_id, message, response, sentiment_score))
            interaction_id = cursor.lastrowid
            conn.commit()
            conn.close()
            return {"status": "success", "interaction_id": interaction_id}
        except Exception as e:
            return {"status": "error", "message": str(e)}

    # Analytics Queries
    def get_customer_history(self, customer_id):
        """Get complete customer history"""
        query = """
        SELECT
            t.id as ticket_id,
            t.subject,
            t.status,
            t.priority,
            i.message,
            i.response,
            i.sentiment_score,
            i.created_at
        FROM tickets t
        LEFT JOIN interactions i ON t.id = i.ticket_id
        WHERE t.customer_id = ?
        ORDER BY i.created_at DESC
        """
        conn = sqlite3.connect(self.db_path)
        result = pd.read_sql_query(query, conn, params=(customer_id,))
        conn.close()
        return result.to_dict('records')

    def get_recent_interactions(self, customer_id, limit=10):
        """Retrieve recent interactions for a specific customer"""
        query = """
        SELECT message, response, created_at 
        FROM interactions 
        WHERE customer_id = ? 
        ORDER BY created_at DESC 
        LIMIT ?
        """
        conn = sqlite3.connect(self.db_path)
        result = pd.read_sql_query(query, conn, params=(customer_id, limit))
        conn.close()
        return result.to_dict('records')

# Sentiment Analysis Class
class SentimentAnalyzer:
    def __init__(self):
        try:
            self.sentiment_pipeline = pipeline("sentiment-analysis")
        except Exception as e:
            print(f"Error loading sentiment analysis model: {e}")
            self.sentiment_pipeline = None

    def analyze_sentiment(self, text):
        """Analyze sentiment of a given text"""
        if not self.sentiment_pipeline:
            return None
        try:
            result = self.sentiment_pipeline(text)[0]
            return {
                'label': result['label'],
                'score': result['score']
            }
        except Exception as e:
            print(f"Sentiment analysis error: {e}")
            return None

# Gradio Interface
def create_database_interface():
    db = DatabaseManager()
    sentiment_analyzer = SentimentAnalyzer()

    def customer_chat_interface(customer_id, message):
        """Process customer chat message"""
        try:
            # First, check if customer exists
            customer = db.get_customer(int(customer_id))
            if not customer:
                return "Error: Customer not found"

            # Analyze sentiment of the message
            sentiment = sentiment_analyzer.analyze_sentiment(message)
            sentiment_score = sentiment['score'] if sentiment else None

            # Simulate a simple bot response (you can replace with more advanced logic)
            response = f"Thank you for your message. We'll get back to you soon about: {message}"

            # Add interaction to database
            interaction = db.add_interaction(
                customer_id=int(customer_id), 
                ticket_id=None,  # Optional: can link to an existing ticket
                message=message, 
                response=response,
                sentiment_score=sentiment_score
            )

            # Retrieve recent interactions for context
            recent_interactions = db.get_recent_interactions(int(customer_id))

            return json.dumps({
                'response': response,
                'interaction_id': interaction.get('interaction_id'),
                'sentiment': sentiment,
                'recent_interactions': recent_interactions
            }, indent=2)

        except Exception as e:
            return f"Error processing chat: {str(e)}"

    def add_customer_interface(name, email, phone, address):
        result = db.add_customer(name, email, phone, address)
        return json.dumps(result, indent=2)

    def get_customer_interface(customer_id):
        result = db.get_customer(int(customer_id))
        return json.dumps(result, indent=2) if result else "Customer not found"

    def update_customer_interface(customer_id, name, email, phone, address):
        updates = {}
        if name: updates['name'] = name
        if email: updates['email'] = email
        if phone: updates['phone'] = phone
        if address: updates['address'] = address

        result = db.update_customer(int(customer_id), **updates)
        return json.dumps(result, indent=2)

    def create_ticket_interface(customer_id, subject, description, priority):
        result = db.create_ticket(int(customer_id), subject, description, priority)
        return json.dumps(result, indent=2)

    def get_customer_history_interface(customer_id):
        result = db.get_customer_history(int(customer_id))
        return json.dumps(result, indent=2)

    # Create the interface
    with gr.Blocks() as interface:
        gr.Markdown("# CRM Database Operations")

        with gr.Tab("Customer Management"):
            gr.Markdown("### Add New Customer")
            with gr.Row():
                name_input = gr.Textbox(label="Name")
                email_input = gr.Textbox(label="Email")
                phone_input = gr.Textbox(label="Phone")
                address_input = gr.Textbox(label="Address")
            add_customer_button = gr.Button("Add Customer")
            add_customer_output = gr.Textbox(label="Result")

            gr.Markdown("### Get Customer Details")
            customer_id_input = gr.Number(label="Customer ID")
            get_customer_button = gr.Button("Get Customer")
            get_customer_output = gr.Textbox(label="Customer Details")

            gr.Markdown("### Update Customer")
            update_id_input = gr.Number(label="Customer ID")
            update_name = gr.Textbox(label="New Name (optional)")
            update_email = gr.Textbox(label="New Email (optional)")
            update_phone = gr.Textbox(label="New Phone (optional)")
            update_address = gr.Textbox(label="New Address (optional)")
            update_customer_button = gr.Button("Update Customer")
            update_customer_output = gr.Textbox(label="Update Result")

        with gr.Tab("Ticket Management"):
            gr.Markdown("### Create New Ticket")
            with gr.Row():
                ticket_customer_id = gr.Number(label="Customer ID")
                ticket_subject = gr.Textbox(label="Subject")
                ticket_description = gr.Textbox(label="Description")
                ticket_priority = gr.Dropdown(
                    choices=["low", "medium", "high"],
                    label="Priority"
                )
            create_ticket_button = gr.Button("Create Ticket")
            create_ticket_output = gr.Textbox(label="Result")

        with gr.Tab("Customer History"):
            history_customer_id = gr.Number(label="Customer ID")
            get_history_button = gr.Button("Get History")
            history_output = gr.Textbox(label="Customer History")

        with gr.Tab("Customer Chat"):
            chat_customer_id = gr.Number(label="Customer ID")
            chat_message = gr.Textbox(label="Your Message")
            chat_submit_button = gr.Button("Send Message")
            chat_output = gr.Textbox(label="Chat Response")

        # Connect the buttons to their functions
        add_customer_button.click(
            add_customer_interface,
            inputs=[name_input, email_input, phone_input, address_input],
            outputs=add_customer_output
        )

        get_customer_button.click(
            get_customer_interface,
            inputs=[customer_id_input],
            outputs=get_customer_output
        )

        update_customer_button.click(
            update_customer_interface,
            inputs=[update_id_input, update_name, update_email, update_phone, update_address],
            outputs=update_customer_output
        )

        create_ticket_button.click(
            create_ticket_interface,
            inputs=[ticket_customer_id, ticket_subject, ticket_description, ticket_priority],
            outputs=create_ticket_output
        )

        get_history_button.click(
            get_customer_history_interface,
            inputs=[history_customer_id],
            outputs=history_output
        )

        chat_submit_button.click(
            customer_chat_interface,
            inputs=[chat_customer_id, chat_message],
            outputs=chat_output
        )

    return interface

# Launch the interface
if __name__ == "__main__":
    interface = create_database_interface()
    interface.launch(share=True)