app.py

import streamlit as st
import pandas as pd
from pymongo import MongoClient
from datetime import datetime
import requests
from openai import OpenAI
from bs4 import BeautifulSoup
import asyncio
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException, NoSuchElementException
import time

# Initialize OpenAI client
@st.cache_resource
def init_openai():
    client = OpenAI(api_key=('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'))
    return client

# Initialize MongoDB connection
@st.cache_resource
def init_mongodb():
    client = MongoClient("mongodb://linkedin_user:P4XnKOjkOaTg@18.235.17.44:27017/?authMechanism=DEFAULT")
    return client['linkedin_db']

# Get detailed address using Selenium and Google Maps
@st.cache_data(ttl=3600)
def get_location_info(company_name):
    options = webdriver.ChromeOptions()
    options.add_argument("--headless")
    options.add_argument("--no-sandbox")
    options.add_argument("--disable-dev-shm-usage")
    
    result = {
        "status": "failed",
        "address": None,
        "error": None
    }
    
    try:
        driver = webdriver.Chrome(options=options)
        driver.implicitly_wait(10)
        
        # Open Google Maps
        driver.get("https://www.google.com/maps")
        
        # Accept cookies if prompted (common in some regions)
        try:
            cookie_button = WebDriverWait(driver, 3).until(
                EC.element_to_be_clickable((By.XPATH, "//button[contains(text(), 'Accept all')]"))
            )
            cookie_button.click()
        except:
            pass  # No cookie prompt or different format
        
        # Find and use the search box
        search_box = driver.find_element(By.NAME, "q")
        search_box.clear()
        search_box.send_keys(company_name)
        search_box.send_keys(Keys.RETURN)
        
        # Wait for results and get the address
        wait = WebDriverWait(driver, 10)
        
        # First attempt with Io6YTe class
        try:
            address_element = wait.until(
                EC.presence_of_element_located((By.CLASS_NAME, "Io6YTe"))
            )
            address = address_element.text
            result["status"] = "success"
            result["address"] = address
        except:
            # Fallback to alternative selectors
            try:
                # Try looking for the address in a different format
                address_container = wait.until(
                    EC.presence_of_element_located((By.CSS_SELECTOR, "[data-section-id='addr']"))
                )
                address = address_container.text.replace("Address: ", "")
                result["status"] = "success"
                result["address"] = address
            except:
                result["error"] = "Could not find address element"
    
    except TimeoutException:
        result["error"] = "Timeout waiting for Google Maps to load"
    except NoSuchElementException:
        result["error"] = "Could not find the required elements on the page"
    except Exception as e:
        result["error"] = f"An error occurred: {str(e)}"
    finally:
        # Always close the browser
        if 'driver' in locals():
            driver.quit()
    
    return result


def search_profiles(db, search_terms, location=None, limit=100):
    # Your existing function code here
    query = {
        '$and': [
            {'search_query': {'$regex': search_terms, '$options': 'i'}},
        ]
    }
    
    if location:
        query['$and'].append({'location': {'$regex': location, '$options': 'i'}})
    
    profiles = list(db.LInkedinProfiles.find(query).limit(limit))
    
    # Get all sent emails for this campaign
    sent_emails = list(db.sent_emails.find({}, {
        'recipient_first_name': 1,
        'recipient_last_name': 1,
        'recipient_company': 1,
        'sent_date': 1
    }))
    
    # Create a lookup dictionary for sent emails
    sent_email_lookup = {
        f"{email['recipient_first_name']}_{email['recipient_last_name']}_{email['recipient_company']}": email['sent_date']
        for email in sent_emails
    }
    
    # Add email status to each profile
    for profile in profiles:
        key = f"{profile['first_name']}_{profile['last_name']}_{profile['company']}"
        if key in sent_email_lookup:
            profile['email_status'] = 'Sent'
            profile['sent_date'] = sent_email_lookup[key]
        else:
            profile['email_status'] = 'Not Sent'
            profile['sent_date'] = None
    
    return profiles


async def get_coffee_shops(company_address):
    if not company_address:
        return []
        
    url = f"https://www.google.com/search?q=coffee%20shops%20near%20{company_address}&sca_esv=2621f7b39c394d4e&tbm=lcl"
    
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36"
    }
    
    try:
        response = requests.get(url, headers=headers)
        soup = BeautifulSoup(response.text, "html.parser")
        shops = soup.find_all("div", class_="VkpGBb")
        
        rated_shops = []
        
        for shop in shops:
            name = shop.find("div", class_="dbg0pd").text if shop.find("div", class_="dbg0pd") else "N/A"
            rating_elem = shop.find("span", class_="yi40Hd YrbPuc")
            rating = rating_elem.text if rating_elem else "0"
            address_divs = shop.find_all("div")
            address = address_divs[-4].text.strip() if len(address_divs) > 2 else "N/A"
            
            try:
                rating_value = float(rating.replace("/5", ""))
                rated_shops.append({
                    "name": name,
                    "rating": rating,
                    "rating_value": rating_value,
                    "address": address
                })
            except ValueError:
                continue
        
        top_shops = sorted(rated_shops, key=lambda x: x["rating_value"], reverse=True)[:3]
        
        for shop in top_shops:
            del shop["rating_value"]
            
        return top_shops
    except Exception as e:
        print(f"Error fetching shops for {company_address}: {str(e)}")
        return []

def get_email_for_profile(profile_url):
    try:
        response = requests.get(
            "http://127.0.0.1:8000/get_emails",
            params={"profile_url": profile_url},
            timeout=10
        )
        
        if response.status_code == 200:
            data = response.json()
            return data.get('email')
        else:
            st.error(f"API Error: Status {response.status_code}")
            return None
            
    except requests.exceptions.Timeout:
        st.error("Request timed out. Please try again.")
        return None
    except requests.exceptions.ConnectionError:
        st.error("Could not connect to the email service. Please check if the API server is running.")
        return None
    except Exception as e:
        st.error(f"Error fetching email: {str(e)}")
        return None

def update_template_with_coffee_shop(template, shop_name, shop_address):
    paragraphs = template.split('\n\n')
    
    meeting_loc_idx = -1
    for i, para in enumerate(paragraphs):
        if any(keyword in para.lower() for keyword in ['meet', 'coffee', 'discuss']):
            meeting_loc_idx = i
            break
    
    meeting_text = f"I would love to meet you at {shop_name} ({shop_address}) to discuss this further."
    
    if meeting_loc_idx >= 0:
        paragraphs[meeting_loc_idx] = meeting_text
    else:
        paragraphs.insert(-1, meeting_text)
    
    return '\n\n'.join(paragraphs)

def generate_email_template(openai_client, profile_data, coffee_shops):
    try:
        shops_text = ""
        if coffee_shops:
            shops_text = "Nearby recommended meeting spots:\n"
            for i, shop in enumerate(coffee_shops, 1):
                shops_text += f"{i}. {shop['name']} (Rating: {shop['rating']}) - {shop['address']}\n"

        first_name = profile_data.get('first_name', '')
        company = profile_data.get('company', '')
        location = profile_data.get('location', '')
        description = profile_data.get('description', '')
        company_address = profile_data.get('company_address', '')

        # First, detect the actual company name
        company_detection_prompt = f"""
        I need to identify the most likely company name from the following LinkedIn profile data. The company name might be in any of these fields:
        
        Company field: "{company}"
        Description field: "{description}"
        Location field: "{location}"
        
        Analyze all three fields and identify the most likely company name. Return ONLY the company name, nothing else.
        """
        
        company_detection_response = openai_client.chat.completions.create(
            model="gpt-4-turbo-preview",
            messages=[
                {"role": "system", "content": "You extract the most likely company name from LinkedIn profile data."},
                {"role": "user", "content": company_detection_prompt}
            ],
            temperature=0.3
        )
        
        detected_company = company_detection_response.choices[0].message.content.strip()
        
        # Print the detected company name and address
        print(f"Original company field: {company}")
        print(f"Detected company name: {detected_company}")
        print(f"Company address: {company_address}")
        
        system_message = """
        You are a professional email writer crafting meeting request templates.
        IMPORTANT: 
        1. Always use the recipient's actual first name in the greeting (e.g., "Dear John," not "Dear [Name]")
        2. Always specifically mention their company name in the first paragraph
        3. Always sign the email with "Best regards,\nAdil"
        4. Never use placeholders like [Name] or [CEO's Name]
        """

        prompt = f"""
        Based on the following professional's information and nearby coffee shops, write a formal email template requesting a meeting:
        
        First Name: {first_name}
        Company: {detected_company}
        Location: {location}
        Company Address: {company_address}
        Description: {description}
        
        {shops_text}
        
        The email should:
        1. Begin with "Dear {first_name},"
        2. Be professional and formal
        3. Reference their current role at {detected_company} specifically in the first paragraph
        4. Suggest meeting at one of the nearby coffee shops (if available)
        5. Be concise but personal
        6. Include a clear call to action for a meeting
        7. End with "Best regards,\nAdil"
        
        Write only the email body without additional subject line or formatting.
        """
        
        response = openai_client.chat.completions.create(
            model="gpt-4-turbo-preview",
            messages=[
                {"role": "system", "content": system_message},
                {"role": "user", "content": prompt}
            ],
            temperature=0.7
        )
        
        template = response.choices[0].message.content.strip()
        
        if "Dear " + first_name not in template:
            template = f"Dear {first_name},\n\n" + template.split('\n', 1)[1] if '\n' in template else template
            
        if "Best regards,\nAdil" not in template:
            template = template.rsplit('\n', 2)[0] + "\n\nBest regards,\nAdil"
        
        # Add the detected company name and address at the top of the email for your reference    
        template = f"[Detected Company: {detected_company}]\n[Company Address: {company_address}]\n\n" + template
            
        return template
    except Exception as e:
        st.error(f"Error generating email template: {str(e)}")
        return None


def save_email_record(db, profile_data, template):
    try:
        email_record = {
            'recipient_first_name': profile_data['First Name'],
            'recipient_last_name': profile_data['Last Name'],
            'recipient_company': profile_data['Company'],
            'email_template': template,
            'sent_date': datetime.now(),
            'status': 'sent'
        }
        
        result = db.sent_emails.insert_one(email_record)
        return result.inserted_id
    except Exception as e:
        st.error(f"Error saving email record: {str(e)}")
        return None

def send_email(template, db, profile_data):
    try:
        sender_email = "adilinbox4@gmail.com"
        sender_password = "pulv dnov zzfg etcg"
        recipient_email = "adilinbox4@gmail.com"
        
        msg = MIMEMultipart()
        msg['From'] = sender_email
        msg['To'] = recipient_email
        msg['Subject'] = "Meeting Request"

        msg.attach(MIMEText(template, 'plain'))

        server = smtplib.SMTP('smtp.gmail.com', 587)
        server.starttls()
        server.login(sender_email, sender_password)
        text = msg.as_string()
        server.sendmail(sender_email, recipient_email, text)
        server.quit()

        # Save record to database after successful send
        record_id = save_email_record(db, profile_data, template)
        
        if record_id:
            # Update profile status in session state
            for profile in st.session_state.search_results:
                if (profile['first_name'] == profile_data['First Name'] and 
                    profile['last_name'] == profile_data['Last Name'] and 
                    profile['company'] == profile_data['Company']):
                    profile['email_status'] = 'Sent'
                    profile['sent_date'] = datetime.now()
            return True
            
        return False
    except Exception as e:
        st.error(f"Failed to send email: {str(e)}")
        return False

def main():
    st.set_page_config(page_title="LinkedIn Profile Explorer", layout="wide")
    
    # Initialize session state variables
    if 'search_results' not in st.session_state:
        st.session_state.search_results = None
    if 'edited_df' not in st.session_state:
        st.session_state.edited_df = None
    if 'email_results' not in st.session_state:
        st.session_state.email_results = []
    if 'selected_templates' not in st.session_state:
        st.session_state.selected_templates = {}
    if 'templates_generated' not in st.session_state:
        st.session_state.templates_generated = False
    if 'edited_templates' not in st.session_state:
        st.session_state.edited_templates = {}
    if 'deleted_templates' not in st.session_state:
        st.session_state.deleted_templates = set()
    if 'selected_coffee_shops' not in st.session_state:
        st.session_state.selected_coffee_shops = {}
    if 'show_templates' not in st.session_state:
        st.session_state.show_templates = False
    if 'company_addresses' not in st.session_state:
        st.session_state.company_addresses = {}
    
    # Initialize OpenAI and MongoDB clients
    openai_client = init_openai()
    db = init_mongodb()
    
    if 'coffee_shop_selections' not in st.session_state:
        st.session_state.coffee_shop_selections = {}
        
    # Add callback function for radio button changes
    def on_coffee_shop_change(template_key, selected_shop, result):
        st.session_state.coffee_shop_selections[template_key] = selected_shop
        current_template = st.session_state.edited_templates.get(template_key, result['Email Template'])
        
        if selected_shop != "No specific coffee shop":
            shop_name = selected_shop.split(" (Rating")[0]
            shop_address = selected_shop.split(" - ")[-1]
            current_template = update_template_with_coffee_shop(current_template, shop_name, shop_address)
        st.session_state.edited_templates[template_key] = current_template

    # Sidebar
    st.sidebar.title("LinkedIn Profile Explorer")
    
    # Search Interface
    st.sidebar.header("Search Profiles")
    
    search_terms = st.sidebar.text_input("Search Keywords (e.g., Healthcare CEO)")
    location = st.sidebar.text_input("Location (Optional)")
    limit = st.sidebar.slider("Number of results", 10, 500, 100)
    
    if st.sidebar.button("Search"):
        with st.spinner("Searching profiles..."):
            profiles = search_profiles(db, search_terms, location, limit)
            if profiles:
                st.session_state.search_results = profiles
                st.session_state.email_results = []
                st.session_state.selected_templates = {}
                st.session_state.templates_generated = False
                st.session_state.edited_templates = {}
                st.session_state.deleted_templates = set()
                st.session_state.show_templates = False
                st.session_state.company_addresses = {}
            else:
                st.session_state.search_results = None
                st.warning("No profiles found matching your search criteria.")
    
    if st.session_state.search_results:
        st.title("Search Results")
        st.success(f"Found {len(st.session_state.search_results)} matching profiles")
        
        df = pd.DataFrame(st.session_state.search_results)
        
        if not df.empty:
            display_cols = {
                'first_name': 'First Name',
                'last_name': 'Last Name',
                'description': 'Description',
                'company': 'Company',
                'location': 'Location',
                'url': 'Profile URL',
                'email_status': 'Email Status',
                'sent_date': 'Sent Date'
            }
            
            df_display = df[display_cols.keys()].rename(columns=display_cols)
            df_display['Description'] = df_display['Description'].apply(
                lambda x: x[:100] + '...' if isinstance(x, str) and len(x) > 100 else x
            )
            
            def format_date(x):
                try:
                    return x.strftime("%Y-%m-%d %H:%M:%S") if pd.notnull(x) and hasattr(x, 'strftime') else ''
                except:
                    return ''

            df_display['Sent Date'] = df_display['Sent Date'].apply(format_date)
            
            # Add Select column and set initial values
            df_display.insert(0, 'Select', False)
            mask = df_display['Email Status'].fillna('').astype(str) == 'Sent'
            df_display.loc[mask, 'Select'] = False

            # Apply conditional styling
            def highlight_sent_rows(row):
                if row['Email Status'] == 'Sent':
                    return ['background-color: #4CAF50; color: black'] * len(row)
                else:
                    return [''] * len(row)

            styled_df = df_display.style.apply(highlight_sent_rows, axis=1)

            st.session_state.edited_df = st.data_editor(
                styled_df,
                hide_index=True,
                disabled=list(display_cols.values()),
                column_config={
                    "Select": st.column_config.CheckboxColumn(
                        "Select",
                        help="Select profiles to fetch emails",
                        default=False,
                    ),
                    "Email Status": st.column_config.Column(
                        "Email Status",
                        help="Shows if an email has been sent to this profile",
                        width="medium"
                    ),
                    "Sent Date": st.column_config.Column(
                        "Sent Date",
                        help="When the email was sent",
                        width="medium"
                    )
                }
            )

        # Generate templates button
        if st.button("Get Emails and Generate Templates"):
            selected_profiles = st.session_state.edited_df[st.session_state.edited_df['Select'] == True]
            
            if selected_profiles.empty:
                st.warning("Please select at least one profile")
            else:
                st.session_state.show_templates = True
                progress_placeholder = st.empty()
                email_results = []
                
                total_profiles = len(selected_profiles)
                
                for idx, (i, row) in enumerate(selected_profiles.iterrows()):
                    progress = min(idx / (total_profiles - 1) if total_profiles > 1 else 1.0, 1.0)
                    progress_placeholder.progress(progress)
                    
                    # First, use GPT to detect the company name
                    company_detection_prompt = f"""
                    I need to identify the most likely company name from the following LinkedIn profile data:
                    
                    Company field: "{row['Company']}"
                    Description field: "{row['Description']}"
                    Location field: "{row['Location']}"
                    
                    Analyze all fields and identify the most likely company name. Return ONLY the company name, nothing else.
                    """
                    
                    company_detection_response = openai_client.chat.completions.create(
                        model="gpt-4-turbo-preview",
                        messages=[
                            {"role": "system", "content": "You extract the most likely company name from LinkedIn profile data."},
                            {"role": "user", "content": company_detection_prompt}
                        ],
                        temperature=0.3
                    )
                    
                    detected_company = company_detection_response.choices[0].message.content.strip()
                    
                    # Use Selenium to get the company's detailed address
                    with st.spinner(f"Looking up address for {detected_company}..."):
                        location_info = get_location_info(detected_company)
                        company_address = location_info.get("address", "")
                        
                        # Store in session state for reuse
                        key = f"{row['First Name']}_{row['Last Name']}_{row['Company']}"
                        st.session_state.company_addresses[key] = company_address
                    
                    # Get coffee shops near the company address
                    try:
                        with st.spinner(f"Finding coffee shops near {detected_company}..."):
                            coffee_shops = asyncio.run(get_coffee_shops(company_address))
                    except Exception as e:
                        coffee_shops = []
                        st.warning(f"Could not fetch coffee shops: {str(e)}")
                    
                    # Generate email template
                    template = generate_email_template(
                        openai_client,
                        {
                            'first_name': row['First Name'],
                            'company': row['Company'],
                            'location': row['Location'],
                            'description': row['Description'],
                            'company_address': company_address
                        },
                        coffee_shops
                    )
                    
                    result = {
                        'First Name': row['First Name'],
                        'Last Name': row['Last Name'],
                        'Company': row['Company'],
                        'Detected Company': detected_company,
                        'Company Address': company_address,
                        'Email Template': template if template else 'Template generation failed',
                        'Nearby Coffee Shops': coffee_shops
                    }
                    
                    email_results.append(result)
                
                progress_placeholder.empty()
                st.session_state.email_results = email_results
                st.session_state.templates_generated = True
                
                # Initialize edited templates with generated content
                for idx, result in enumerate(email_results):
                    template_key = f"template_{idx}"
                    if template_key not in st.session_state.edited_templates:
                        st.session_state.edited_templates[template_key] = result['Email Template']
                    
        # Display templates section
        if st.session_state.show_templates and st.session_state.email_results:
            st.write("### Select Templates to Send")
            
            templates_to_display = [
                (idx, result) for idx, result in enumerate(st.session_state.email_results)
                if f"template_{idx}" not in st.session_state.deleted_templates
            ]

            for idx, result in templates_to_display:
                template_key = f"template_{idx}"
                
                with st.expander(f"📧 {result['First Name']} {result['Last Name']} - {result['Company']}", expanded=True):
                    col1, col2, col3 = st.columns([0.2, 1.6, 0.2])
                    
                    with col1:
                        st.session_state.selected_templates[template_key] = True
                        
                        if result.get('Company Address'):
                            st.info(f"**Company Address:** {result['Company Address']}")

                    with col2:
                        if result.get('Nearby Coffee Shops'):
                            st.write("**Select Coffee Shop for Meeting:**")
                            
                            coffee_shops = result['Nearby Coffee Shops']
                            coffee_shop_options = [
                                f"{shop['name']} (Rating: {shop['rating']}) - {shop['address']}"
                                for shop in coffee_shops
                            ]
                            coffee_shop_options.insert(0, "No specific coffee shop")
                            
                            if template_key not in st.session_state.coffee_shop_selections and coffee_shop_options:
                                st.session_state.coffee_shop_selections[template_key] = coffee_shop_options[1] if len(coffee_shop_options) > 1 else coffee_shop_options[0]
                            
                            if coffee_shop_options:
                                selected_shop = st.radio(
                                    "Choose a coffee shop:",
                                    options=coffee_shop_options,
                                    key=f"coffee_shop_{template_key}",
                                    index=coffee_shop_options.index(st.session_state.coffee_shop_selections.get(template_key, coffee_shop_options[0]))
                                )
                                
                                if selected_shop != st.session_state.coffee_shop_selections.get(template_key):
                                    on_coffee_shop_change(template_key, selected_shop, result)
                        
                        st.write("**Generated Email Template:**")
                        current_template = st.session_state.edited_templates.get(template_key, result['Email Template'])
                        edited_template = st.text_area(
                            "",
                            value=current_template,
                            height=300,
                            key=f"edit_{template_key}"
                        )
                        st.session_state.edited_templates[template_key] = edited_template

                    with col3:
                        if st.button("🗑️", key=f"delete_{template_key}"):
                            st.session_state.deleted_templates.add(template_key)
                            st.rerun()


            if templates_to_display and st.button("Send Selected Templates"):
                success_count = 0
                send_progress = st.progress(0)
                status_text = st.empty()
                
                total_selected = len(templates_to_display)
                
                for i, (idx, result) in enumerate(templates_to_display):
                    template_key = f"template_{idx}"
                    template = st.session_state.edited_templates[template_key]
                    profile_data = st.session_state.email_results[idx]
                    
                    if send_email(template, db, profile_data):
                        success_count += 1
                        st.session_state.deleted_templates.add(template_key)
                    
                    progress = (i + 1) / total_selected
                    send_progress.progress(progress)
                    status_text.text(f"Sending emails: {i + 1}/{total_selected}")
                
                send_progress.empty()
                status_text.empty()
                
                if success_count > 0:
                    st.success(f"Successfully sent {success_count} out of {total_selected} templates!")
                    st.rerun()
                if success_count < total_selected:
                    st.warning(f"Failed to send {total_selected - success_count} templates. Please check the errors above.")

        st.sidebar.markdown("---")
        st.sidebar.markdown("### About")
        st.sidebar.info(
            "This application allows you to search LinkedIn profiles and generate meeting request templates."
        )


    
if __name__ == "__main__":
    main()