#!/usr/bin/env python3 """ Ghost Agency - AI Review Generation Engine Automated review requests via Email, WhatsApp, SMS + Response management """ import os import json import sqlite3 import hashlib import time import random from pathlib import Path from datetime import datetime, timedelta from typing import Dict, List, Any, Optional DB_FILE = Path("/home/ubuntu/GhostAgency") / "revenue_bots.db" REVIEW_DB = Path("/home/ubuntu/GhostAgency") / "ai_review_gen.db" # ============================================================ # AI REVIEW GENERATION ENGINE # ============================================================ class AIReviewGenerator: def __init__(self): self.db_path = str(REVIEW_DB) self.openrouter_api_key = os.getenv("OPENROUTER_API_KEY", "") self.google_places_api_key = os.getenv("GOOGLE_MAPS_API_KEY", "") self.gmail_app_password = os.getenv("SMTP_PASS", "") self.init_db() def init_db(self): conn = sqlite3.connect(self.db_path) c = conn.cursor() # Review Campaigns c.execute("""CREATE TABLE IF NOT EXISTS review_campaigns ( id TEXT PRIMARY KEY, client_id TEXT NOT NULL, business_name TEXT, platform TEXT, campaign_name TEXT, trigger_type TEXT, trigger_config TEXT, template_id TEXT, channel TEXT, status TEXT DEFAULT 'active', sent_count INTEGER DEFAULT 0, response_count INTEGER DEFAULT 0, review_count INTEGER DEFAULT 0, avg_rating REAL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") # Review Templates c.execute("""CREATE TABLE IF NOT EXISTS review_templates ( id TEXT PRIMARY KEY, category TEXT, platform TEXT, channel TEXT, template_name TEXT, subject TEXT, body_text TEXT, body_html TEXT, variables TEXT, cta_text TEXT, review_link_placeholder TEXT, language TEXT DEFAULT 'it', active INTEGER DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") # Review Requests c.execute("""CREATE TABLE IF NOT EXISTS review_requests ( id TEXT PRIMARY KEY, campaign_id TEXT NOT NULL, client_id TEXT NOT NULL, customer_name TEXT, customer_email TEXT, customer_phone TEXT, channel TEXT, template_id TEXT, sent_at TIMESTAMP, delivered_at TIMESTAMP, opened_at TIMESTAMP, clicked_at TIMESTAMP, reviewed_at TIMESTAMP, status TEXT DEFAULT 'pending', review_rating INTEGER, review_text TEXT, review_platform TEXT, review_url TEXT, error_message TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") # Reviews Received c.execute("""CREATE TABLE IF NOT EXISTS reviews_received ( id TEXT PRIMARY KEY, client_id TEXT NOT NULL, platform TEXT, external_review_id TEXT, author_name TEXT, author_photo_url TEXT, rating INTEGER, text TEXT, language TEXT, published_at TIMESTAMP, reply_text TEXT, replied_at TIMESTAMP, replied_by TEXT, sentiment TEXT, keywords TEXT, is_replied INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") # Review Responses c.execute("""CREATE TABLE IF NOT EXISTS review_responses ( id TEXT PRIMARY KEY, review_id TEXT NOT NULL, client_id TEXT NOT NULL, platform TEXT, response_text TEXT, response_tone TEXT, status TEXT DEFAULT 'draft', generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, approved_at TIMESTAMP, approved_by TEXT, sent_at TIMESTAMP )""") # GMB Locations c.execute("""CREATE TABLE IF NOT EXISTS gmb_locations ( id TEXT PRIMARY KEY, client_id TEXT NOT NULL, business_name TEXT, place_id TEXT, location_name TEXT, address TEXT, phone TEXT, website TEXT, category TEXT, hours TEXT, photos TEXT, verified INTEGER DEFAULT 0, access_token TEXT, refresh_token TEXT, last_synced TIMESTAMP, status TEXT DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") # Review Analytics c.execute("""CREATE TABLE IF NOT EXISTS review_analytics ( id TEXT PRIMARY KEY, client_id TEXT NOT NULL, date DATE, platform TEXT, total_reviews INTEGER DEFAULT 0, new_reviews INTEGER DEFAULT 0, avg_rating REAL DEFAULT 0, rating_distribution TEXT, response_rate REAL DEFAULT 0, avg_response_time_hours REAL DEFAULT 0, sentiment_score REAL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") # Incentives c.execute("""CREATE TABLE IF NOT EXISTS review_incentives ( id TEXT PRIMARY KEY, client_id TEXT NOT NULL, incentive_name TEXT, incentive_type TEXT, value TEXT, conditions TEXT, max_redemptions INTEGER, current_redemptions INTEGER DEFAULT 0, start_date DATE, end_date DATE, status TEXT DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""") conn.commit() conn.close() self._init_default_templates() def _init_default_templates(self): conn = sqlite3.connect(self.db_path) c = conn.cursor() c.execute("SELECT COUNT(*) FROM review_templates") if c.fetchone()[0] > 0: conn.close() return templates = [ { "category": "ristorante", "platform": "google", "channel": "email", "template_name": "Post-Cena Review Request", "subject": "Come e andata la tua cena da {business_name}?", "body_text": "Ciao {customer_name},\n\ngrazie per aver cenato da {business_name}! Speriamo che l'esperienza sia stata all'altezza delle aspettative.\n\nLa tua opinione e fondamentale per noi e aiuta altri clienti a sceglierci.\nTi va di lasciare una recensione su Google? Ci vuole 30 secondi:\n\n{review_link}\n\nCome ringraziamento, ti offriamo il 10% di sconto sulla prossima cena!\n\nGrazie di cuore!\nIl team di {business_name}\n\n---\nSe non vuoi ricevere queste email: {unsubscribe_link}", "body_html": "

Ciao {customer_name},

grazie per aver cenato da {business_name}!

La tua opinione e fondamentale per noi.
Ti va di lasciare una recensione su Google?

Lascia una recensione

Come ringraziamento, ti offriamo il 10% di sconto sulla prossima cena!

Grazie di cuore!
Il team di {business_name}

Se non vuoi ricevere queste email: Disiscriviti", "variables": json.dumps(["customer_name", "business_name", "review_link", "unsubscribe_link"]), "cta_text": "Lascia una recensione", "review_link_placeholder": "{review_link}", "language": "it" }, { "category": "default", "platform": "google", "channel": "email", "template_name": "Post-Servizio Review Request", "subject": "La tua opinione conta per {business_name}", "body_text": "Ciao {customer_name},\n\ngrazie per aver scelto {business_name}! Speriamo che il nostro servizio sia stato all'altezza.\n\nLa tua opinione ci aiuta a migliorare e permette ad altri di scoprirci.\nTi va di lasciare una recensione su Google? Ci vuole 30 secondi:\n\n{review_link}\n\nGrazie mille!\nIl team di {business_name}\n\n---\nSe non vuoi ricevere queste email: {unsubscribe_link}", "body_html": "

Ciao {customer_name},

grazie per aver scelto {business_name}!

La tua opinione ci aiuta a migliorare.
Ti va di lasciare una recensione su Google?

Lascia una recensione

Grazie mille!
Il team di {business_name}

Se non vuoi ricevere queste email: Disiscriviti", "variables": json.dumps(["customer_name", "business_name", "review_link", "unsubscribe_link"]), "cta_text": "Lascia una recensione", "review_link_placeholder": "{review_link}", "language": "it" }, { "category": "ristorante", "platform": "google", "channel": "whatsapp", "template_name": "Post-Cena WhatsApp", "subject": "", "body_text": "Ciao {customer_name}! Grazie per essere venuto da {business_name}!\n\nCome e andata la cena? Se ti e piaciuto, ci faresti un favore enorme lasciando una recensione su Google?\n\nLink diretto: {review_link}\n\nGrazie mille!\n\n{business_name}", "body_html": "", "variables": json.dumps(["customer_name", "business_name", "review_link"]), "cta_text": "", "review_link_placeholder": "{review_link}", "language": "it" }, { "category": "default", "platform": "google", "channel": "whatsapp", "template_name": "Post-Servizio WhatsApp", "subject": "", "body_text": "Ciao {customer_name}! Grazie per aver scelto {business_name}!\n\nCome e andata? La tua opinione ci aiuta a migliorare.\n\nLascia una recensione su Google: {review_link}\n\nGrazie!\n\n{business_name}", "body_html": "", "variables": json.dumps(["customer_name", "business_name", "review_link"]), "cta_text": "", "review_link_placeholder": "{review_link}", "language": "it" }, { "category": "default", "platform": "google", "channel": "sms", "template_name": "Post-Servizio SMS", "subject": "", "body_text": "Grazie per aver scelto {business_name}! Lasci una recensione su Google? {short_link} Grazie! - {business_name}", "body_html": "", "variables": json.dumps(["business_name", "short_link"]), "cta_text": "", "review_link_placeholder": "{short_link}", "language": "it" } ] for tmpl in templates: tid = hashlib.md5(f"{tmpl['category']}{tmpl['platform']}{tmpl['channel']}{tmpl['template_name']}".encode()).hexdigest()[:12] c.execute(""" INSERT INTO review_templates (id, category, platform, channel, template_name, subject, body_text, body_html, variables, cta_text, review_link_placeholder, language, active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1) """, ( tid, tmpl["category"], tmpl["platform"], tmpl["channel"], tmpl["template_name"], tmpl["subject"], tmpl["body_text"], tmpl["body_html"], tmpl["variables"], tmpl["cta_text"], tmpl["review_link_placeholder"], tmpl["language"] )) conn.commit() conn.close() def register_client(self, client_data: Dict) -> str: client_id = client_data.get("client_id", hashlib.md5(f"{client_data.get('business_name', '')}{time.time()}".encode()).hexdigest()[:12]) conn = sqlite3.connect(self.db_path) c = conn.cursor() campaign_id = hashlib.md5(f"camp_{client_id}{time.time()}".encode()).hexdigest()[:12] c.execute(""" INSERT INTO review_campaigns (id, client_id, business_name, platform, campaign_name, trigger_type, trigger_config, channel, status) VALUES (?, ?, ?, 'google', 'Post-Servizio Automatico', 'post_service', '{"delay_hours": 2, "min_rating": 4}', 'email', 'active') """, ( campaign_id, client_id, client_data.get("business_name", ""), )) conn.commit() conn.close() return client_id def create_review_campaign(self, client_data: Dict, campaign_config: Dict) -> str: client_id = client_data.get("client_id", "") campaign_id = hashlib.md5(f"camp_{client_id}{campaign_config.get('name', '')}{time.time()}".encode()).hexdigest()[:12] conn = sqlite3.connect(self.db_path) c = conn.cursor() c.execute(""" INSERT INTO review_campaigns (id, client_id, business_name, platform, campaign_name, trigger_type, trigger_config, template_id, channel, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'active') """, ( campaign_id, client_id, client_data.get("business_name", ""), campaign_config.get("platform", "google"), campaign_config.get("name", "Campagna Recensioni"), campaign_config.get("trigger_type", "post_service"), json.dumps(campaign_config.get("trigger_config", {"delay_hours": 2})), campaign_config.get("template_id", ""), campaign_config.get("channel", "email"), )) conn.commit() conn.close() return campaign_id def send_review_request(self, client_id: str, customer_data: Dict, channel: str = "email", template_id: str = None, client_data: Dict = None) -> Dict: if client_data is None: client_data = {"business_name": "La nostra attività", "category": "default"} conn = sqlite3.connect(self.db_path) c = conn.cursor() if template_id: c.execute("SELECT * FROM review_templates WHERE id=?", (template_id,)) else: c.execute(""" SELECT * FROM review_templates WHERE (category=? OR category='default') AND platform='google' AND channel=? AND active=1 ORDER BY CASE WHEN category='default' THEN 1 ELSE 0 END LIMIT 1 """, (client_data.get("category", "").lower(), channel)) template = c.fetchone() conn.close() if not template: return {"error": "No template found"} review_link = f"https://g.page/r/{client_id}/review" email = customer_data.get("email", "") short_link = f"https://svoraj.me/r/{hashlib.md5(f'{client_id}{email}'.encode()).hexdigest()[:8]}" variables = { "customer_name": customer_data.get("name", "Cliente"), "business_name": client_data.get("business_name", "La nostra attività"), "review_link": review_link, "short_link": short_link, "unsubscribe_link": f"https://svoraj.me/unsubscribe?email={customer_data.get('email', '')}" } body = template[6] for var, value in variables.items(): body = body.replace(f"{{{var}}}", value) subject = template[5] if template[5] else "" for var, value in variables.items(): subject = subject.replace(f"{{{var}}}", value) request_id = hashlib.md5(f"req_{client_id}{customer_data.get('email', '')}{time.time()}".encode()).hexdigest()[:12] conn = sqlite3.connect(self.db_path) c = conn.cursor() c.execute(""" INSERT INTO review_requests (id, campaign_id, client_id, customer_name, customer_email, customer_phone, channel, template_id, sent_at, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, 'sent') """, ( request_id, f"camp_{client_id}", client_id, customer_data.get("name", ""), customer_data.get("email", ""), customer_data.get("phone", ""), channel, template[0], )) conn.commit() conn.close() print(f" [SIMULATED] {channel.upper()} sent to {customer_data.get('email', customer_data.get('phone', 'unknown'))}") return { "request_id": request_id, "status": "sent", "channel": channel, "template_used": template[4], "review_link": review_link } def generate_ai_response(self, review_data: Dict) -> str: rating = review_data.get("rating", 5) text = review_data.get("text", "") business_name = review_data.get("business_name", "") if rating >= 4: responses = [ f"Grazie mille per la bellissima recensione! Siamo felici che {business_name} ti sia piaciuto. La tua soddisfazione e la nostra priorita. A presto!", f"Grazie di cuore per le 5 stelle! Fa piacere sapere che hai apprezzato la tua esperienza da {business_name}. Torna a trovarci presto!", f"Grazie per il feedback positivo! Il team di {business_name} lavora ogni giorno per offrire il meglio. Alla prossima!" ] elif rating == 3: responses = [ f"Grazie per la tua recensione. Ci dispiace che l'esperienza non sia stata perfetta. Il tuo feedback ci aiuta a migliorare. Speriamo di rifarci la prossima volta!", f"Grazie per il tuo feedback onesto. Prendiamo nota dei tuoi suggerimenti per migliorare. Ci auguriamo di rivederti presto da {business_name}!" ] else: responses = [ f"Ci dispiace molto per la tua esperienza. La tua soddisfazione e importante per noi. Vorremmo capire meglio cosa e andato storto: contattaci direttamente e faremo il possibile per rimediare.", f"Ci scusiamo per il disagio. Prendiamo molto sul serio il tuo feedback. Il team di {business_name} lavorera per migliorare. Contattaci per parlarne." ] return random.choice(responses) def process_incoming_reviews(self, client_id: str) -> int: conn = sqlite3.connect(self.db_path) c = conn.cursor() platforms = ["google", "facebook", "tripadvisor"] processed = 0 for i in range(random.randint(1, 3)): review_id = hashlib.md5(f"review_{client_id}{time.time()}{i}".encode()).hexdigest()[:12] rating = random.randint(3, 5) sentiment = "positive" if rating >= 4 else "neutral" if rating == 3 else "negative" c.execute(""" INSERT INTO reviews_received (id, client_id, platform, external_review_id, author_name, rating, text, language, published_at, sentiment, is_replied) VALUES (?, ?, ?, ?, ?, ?, ?, 'it', CURRENT_TIMESTAMP, ?, 0) """, ( review_id, client_id, random.choice(platforms), f"ext_{hashlib.md5(f'{client_id}{i}'.encode()).hexdigest()[:12]}", f"Cliente {random.randint(1, 100)}", rating, f"Esperienza {'ottima' if rating >= 4 else 'buona' if rating == 3 else 'deludente'} da questo locale.", sentiment )) response_text = self.generate_ai_response({ "rating": rating, "text": f"Esperienza {'ottima' if rating >= 4 else 'buona' if rating == 3 else 'deludente'}", "business_name": "Business" }) resp_id = hashlib.md5(f"resp_{review_id}{time.time()}".encode()).hexdigest()[:12] c.execute(""" INSERT INTO review_responses (id, review_id, client_id, platform, response_text, response_tone, status) VALUES (?, ?, ?, ?, ?, ?, 'draft') """, ( resp_id, review_id, client_id, random.choice(platforms), response_text, "grateful" if rating >= 4 else "professional" if rating == 3 else "apologetic" )) processed += 1 conn.commit() conn.close() return processed def run_ai_review_generation(): print(f"\n{'='*60}") print(f" AI REVIEW GENERATION ENGINE") print(f"{'='*60}\n") generator = AIReviewGenerator() conn = sqlite3.connect("/home/ubuntu/GhostAgency/lead_broker.db") c = conn.cursor() c.execute(""" SELECT id, nome, categoria, citta, email, telefono FROM leads_inventory WHERE venduto = 0 AND email IS NOT NULL AND email != '' LIMIT 10 """) leads = c.fetchall() conn.close() if not leads: print("[!] No leads with email found") return print(f"[*] Setting up review campaigns for {len(leads)} leads...") total_requests = 0 total_responses = 0 for lead in leads: lead_id, nome, categoria, citta, email, telefono = lead client_data = { "client_id": lead_id, "business_name": nome, "category": categoria, "location": citta, "email": email, "phone": telefono } generator.register_client(client_data) campaign_id = generator.create_review_campaign(client_data, { "name": "Post-Servizio Automatico", "platform": "google", "trigger_type": "post_service", "trigger_config": {"delay_hours": 2}, "channel": "email" }) for i in range(random.randint(3, 8)): customer_data = { "name": f"Cliente {i+1}", "email": f"cliente{i+1}@example.com", "phone": f"+39 3XX XXXXXXX" } result = generator.send_review_request(lead_id, customer_data, "email", client_data=client_data) if "error" not in result: total_requests += 1 processed = generator.process_incoming_reviews(lead_id) total_responses += processed print(f" [+] {nome} ({categoria}) - Campaign: {campaign_id[:8]} | Requests sent: {random.randint(3,8)} | AI responses generated: {processed}") print(f"\n[+] AI Review Generation setup complete") print(f"[+] Total review requests simulated: {total_requests}") print(f"[+] Total AI responses generated: {total_responses}") print(f"[+] Templates: Email, WhatsApp, SMS per categoria") print(f"[+] Next: Connect Google Places API + Gmail API + WhatsApp Business API for live deployment") if __name__ == "__main__": run_ai_review_generation()