#!/usr/bin/env python3
"""
Ghost Agency - Birthday/Anniversary Marketing Automation
Automated birthday wishes, anniversary offers, loyalty milestones
"""
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
BDAY_DB = Path("/home/ubuntu/GhostAgency") / "birthday_marketing.db"
# ============================================================
# BIRTHDAY/ANNIVERSARY MARKETING ENGINE
# ============================================================
class BirthdayMarketing:
def __init__(self):
self.db_path = str(BDAY_DB)
self.openrouter_api_key = os.getenv("OPENROUTER_API_KEY", "")
self.init_db()
def init_db(self):
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
# Customers/Contacts with birthdays
c.execute("""CREATE TABLE IF NOT EXISTS contacts (
id TEXT PRIMARY KEY,
client_id TEXT NOT NULL,
first_name TEXT,
last_name TEXT,
email TEXT,
phone TEXT,
birth_date DATE, -- YYYY-MM-DD
anniversary_date DATE, -- wedding, first visit, etc.
anniversary_type TEXT, -- wedding, first_visit, loyalty_join
preferences TEXT, -- JSON: dietary, favorite_dishes, allergies
tags TEXT, -- JSON array
source TEXT, -- pos, website, walkin, referral, social
opted_in_email INTEGER DEFAULT 1,
opted_in_sms INTEGER DEFAULT 1,
opted_in_whatsapp INTEGER DEFAULT 1,
opted_in_push INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
# Campaigns
c.execute("""CREATE TABLE IF NOT EXISTS birthday_campaigns (
id TEXT PRIMARY KEY,
client_id TEXT NOT NULL,
campaign_name TEXT,
campaign_type TEXT, -- birthday, anniversary, milestone, welcome_back
trigger_days_before INTEGER DEFAULT 0, -- 0 = same day, 7 = week before
trigger_days_after INTEGER DEFAULT 0,
channel TEXT, -- email, sms, whatsapp, push, all
template_id TEXT,
incentive_type TEXT, -- discount_percent, discount_fixed, free_item, free_drink, dessert, points
incentive_value TEXT, -- JSON: {type: percent, value: 20, max: 50}
incentive_conditions TEXT, -- JSON: min_spend, valid_days, excluded_items
status TEXT DEFAULT 'active',
send_time TEXT DEFAULT '10:00', -- HH:MM
timezone TEXT DEFAULT 'Europe/Rome',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
# Templates
c.execute("""CREATE TABLE IF NOT EXISTS birthday_templates (
id TEXT PRIMARY KEY,
category TEXT,
campaign_type TEXT,
channel TEXT,
template_name TEXT,
subject TEXT,
body_text TEXT,
body_html TEXT,
variables TEXT, -- JSON array
cta_text TEXT,
cta_url TEXT,
language TEXT DEFAULT 'it',
active INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
# Sent Messages
c.execute("""CREATE TABLE IF NOT EXISTS birthday_messages (
id TEXT PRIMARY KEY,
campaign_id TEXT NOT NULL,
client_id TEXT NOT NULL,
contact_id TEXT NOT NULL,
channel TEXT,
template_id TEXT,
subject TEXT,
body_text TEXT,
sent_at TIMESTAMP,
delivered_at TIMESTAMP,
opened_at TIMESTAMP,
clicked_at TIMESTAMP,
redeemed_at TIMESTAMP,
status TEXT DEFAULT 'pending', -- pending, sent, delivered, opened, clicked, redeemed, bounced, opted_out
incentive_code TEXT,
incentive_value TEXT,
error_message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
# Redemptions
c.execute("""CREATE TABLE IF NOT EXISTS birthday_redemptions (
id TEXT PRIMARY KEY,
message_id TEXT NOT NULL,
client_id TEXT NOT NULL,
contact_id TEXT NOT NULL,
campaign_id TEXT NOT NULL,
incentive_type TEXT,
incentive_value TEXT,
order_amount REAL,
discount_applied REAL,
pos_transaction_id TEXT,
redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
# Campaign Analytics
c.execute("""CREATE TABLE IF NOT EXISTS birthday_analytics (
id TEXT PRIMARY KEY,
client_id TEXT NOT NULL,
campaign_id TEXT,
date DATE,
contacts_eligible INTEGER,
messages_sent INTEGER,
messages_delivered INTEGER,
messages_opened INTEGER,
messages_clicked INTEGER,
messages_redeemed INTEGER,
revenue_generated REAL,
discount_cost REAL,
roi REAL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
# Milestone Definitions
c.execute("""CREATE TABLE IF NOT EXISTS milestone_definitions (
id TEXT PRIMARY KEY,
client_id TEXT NOT NULL,
milestone_name TEXT,
milestone_type TEXT, -- visits, spend, months, referrals, reviews
threshold_value INTEGER,
reward_type TEXT,
reward_value TEXT,
active INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""")
conn.commit()
conn.close()
self._init_default_templates()
self._init_milestone_definitions()
def _init_default_templates(self):
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
c.execute("SELECT COUNT(*) FROM birthday_templates")
if c.fetchone()[0] > 0:
conn.close()
return
templates = [
# BIRTHDAY EMAIL
{
"category": "ristorante",
"campaign_type": "birthday",
"channel": "email",
"template_name": "Compleanno Ristorante",
"subject": "���� Buon Compleanno {first_name}! Il tuo regalo da {business_name}",
"body_text": "Ciao {first_name},\n\nTANTI AUGURI DI BUON COMPLEANNO! ���\n\nDa tutto lo staff di {business_name}, ti facciamo i nostri migliori auguri per il tuo giorno speciale!\n\nCome regalo, ti offriamo: {incentive_description}\n\nValido fino al {expiry_date}. Prenota il tuo tavolo e festeggia con noi!\n\n���� Prenota ora: {cta_url}\n\nTanti auguri ancora!\n\nIl team di {business_name}\n\n---\nSe non vuoi ricevere queste email: {unsubscribe_url}",
"body_html": "���� Buon Compleanno {first_name}!
Da tutto lo staff di {business_name}, tanti auguri per il tuo giorno speciale!
Come regalo, ti offriamo: {incentive_description}
Valido fino al {expiry_date}. Prenota il tuo tavolo e festeggia con noi!
���� Prenota e Festeggia
Tanti auguri ancora!
Il team di {business_name}
Se non vuoi ricevere queste email: Disiscriviti",
"variables": json.dumps(["first_name", "business_name", "incentive_description", "expiry_date", "cta_url", "unsubscribe_url"]),
"cta_text": "���� Prenota e Festeggia",
"cta_url": "{booking_url}",
"language": "it"
},
# BIRTHDAY WHATSAPP
{
"category": "ristorante",
"campaign_type": "birthday",
"channel": "whatsapp",
"template_name": "Compleanno WhatsApp",
"subject": "",
"body_text": "���� Ciao {first_name}! TANTI AUGURI di buon compleanno da {business_name}! ���\n\nCome regalo ti offriamo: {incentive_description}\n\nValido fino al {expiry_date}. Prenota qui: {cta_url}\n\nTanti auguri! ���\n\n{business_name}",
"body_html": "",
"variables": json.dumps(["first_name", "business_name", "incentive_description", "expiry_date", "cta_url"]),
"cta_text": "",
"cta_url": "{booking_url}",
"language": "it"
},
# BIRTHDAY SMS
{
"category": "default",
"campaign_type": "birthday",
"channel": "sms",
"template_name": "Compleanno SMS",
"subject": "",
"body_text": "���� Auguri {first_name}! Da {business_name} ti regaliamo {incentive_short}. Valido fino al {expiry_date}. Prenota: {short_url} - {business_name}",
"body_html": "",
"variables": json.dumps(["first_name", "business_name", "incentive_short", "expiry_date", "short_url"]),
"cta_text": "",
"cta_url": "{short_url}",
"language": "it"
},
# ANNIVERSARY (FIRST VISIT)
{
"category": "ristorante",
"campaign_type": "anniversary",
"channel": "email",
"template_name": "Anniversario Prima Visita",
"subject": "���� {first_name}, è passato un anno dalla tua prima volta da {business_name}!",
"body_text": "Ciao {first_name},\n\nÈ passato esattamente un anno dalla tua prima visita da {business_name}! ���\n\nGrazie per averci scelto e per essere tornato. Per festeggiare questo anniversario, ti offriamo: {incentive_description}\n\nValido fino al {expiry_date}.\n\n���� Prenota ora: {cta_url}\n\nGrazie per la tua fedeltà!\n\nIl team di {business_name}\n\n---\nSe non vuoi ricevere queste email: {unsubscribe_url}",
"body_html": "���� Un anno insieme, {first_name}!
È passato esattamente un anno dalla tua prima visita da {business_name}!
Grazie per averci scelto. Per festeggiare, ti offriamo: {incentive_description}
Valido fino al {expiry_date}.
���� Festeggia con Noi
Grazie per la tua fedeltà!
Il team di {business_name}
Se non vuoi ricevere queste email: Disiscriviti",
"variables": json.dumps(["first_name", "business_name", "incentive_description", "expiry_date", "cta_url", "unsubscribe_url"]),
"cta_text": "���� Festeggia con Noi",
"cta_url": "{booking_url}",
"language": "it"
},
# WELCOME BACK (INACTIVE)
{
"category": "default",
"campaign_type": "welcome_back",
"channel": "email",
"template_name": "Welcome Back - Non ti vediamo da un po'",
"subject": "���� {first_name}, ci manchi da {business_name}!",
"body_text": "Ciao {first_name},\n\nNon ti vediamo da un po' da {business_name} e ci manchi! ���\n\nVogliamo farti tornare con un'offerta speciale: {incentive_description}\n\nValido fino al {expiry_date}.\n\n���� Torna a trovarci: {cta_url}\n\nTi aspettiamo!\n\nIl team di {business_name}\n\n---\nSe non vuoi ricevere queste email: {unsubscribe_url}",
"body_html": "���� Ci manchi, {first_name}!
Non ti vediamo da un po' da {business_name} e ci manchi!
Vogliamo farti tornare con un'offerta speciale: {incentive_description}
Valido fino al {expiry_date}.
���� Torna da Noi
Ti aspettiamo!
Il team di {business_name}
Se non vuoi ricevere queste email: Disiscriviti",
"variables": json.dumps(["first_name", "business_name", "incentive_description", "expiry_date", "cta_url", "unsubscribe_url"]),
"cta_text": "���� Torna da Noi",
"cta_url": "{booking_url}",
"language": "it"
},
# MILESTONE (VISITS/SPEND)
{
"category": "ristorante",
"campaign_type": "milestone",
"channel": "email",
"template_name": "Milestone - 10 Visite",
"subject": "���� {first_name}, hai raggiunto 10 visite da {business_name}!",
"body_text": "Ciao {first_name},\n\nCOMPLIMENTI! ��� Hai raggiunto la tua 10ª visita da {business_name}!\n\nSei uno dei nostri clienti più fedeli e vogliamo ringraziarti: {incentive_description}\n\nValido fino al {expiry_date}.\n\n���� Prenota la prossima: {cta_url}\n\nGrazie per la tua fedeltà!\n\nIl team di {business_name}\n\n---\nSe non vuoi ricevere queste email: {unsubscribe_url}",
"body_html": "���� Complimenti {first_name}!
Hai raggiunto la tua 10ª visita da {business_name}!
Sei uno dei nostri clienti più fedeli. Per ringraziarti: {incentive_description}
Valido fino al {expiry_date}.
���� Il Tuo Premio
Grazie per la tua fedeltà!
Il team di {business_name}
Se non vuoi ricevere queste email: Disiscriviti",
"variables": json.dumps(["first_name", "business_name", "incentive_description", "expiry_date", "cta_url", "unsubscribe_url"]),
"cta_text": "���� Il Tuo Premio",
"cta_url": "{booking_url}",
"language": "it"
}
]
for tmpl in templates:
tid = hashlib.md5(f"{tmpl['category']}{tmpl['campaign_type']}{tmpl['channel']}{tmpl['template_name']}".encode()).hexdigest()[:12]
c.execute("""
INSERT INTO birthday_templates
(id, category, campaign_type, channel, template_name, subject, body_text, body_html,
variables, cta_text, cta_url, language, active)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
""", (
tid, tmpl["category"], tmpl["campaign_type"], tmpl["channel"], tmpl["template_name"],
tmpl["subject"], tmpl["body_text"], tmpl["body_html"],
tmpl["variables"], tmpl["cta_text"], tmpl["cta_url"], tmpl["language"]
))
conn.commit()
conn.close()
def _init_milestone_definitions(self):
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
c.execute("SELECT COUNT(*) FROM milestone_definitions")
if c.fetchone()[0] > 0:
conn.close()
return
milestones = [
{"milestone_name": "5 Visite", "milestone_type": "visits", "threshold_value": 5, "reward_type": "free_item", "reward_value": json.dumps({"item": "Dolce o caffè", "max_value": 5})},
{"milestone_name": "10 Visite", "milestone_type": "visits", "threshold_value": 10, "reward_type": "discount_percent", "reward_value": json.dumps({"percent": 20, "max": 30})},
{"milestone_name": "25 Visite", "milestone_type": "visits", "threshold_value": 25, "reward_type": "free_item", "reward_value": json.dumps({"item": "Antipasto o bottiglia vino", "max_value": 25})},
{"milestone_name": "50 Visite", "milestone_type": "visits", "threshold_value": 50, "reward_type": "discount_percent", "reward_value": json.dumps({"percent": 30, "max": 50})},
{"milestone_name": "100 Visite", "milestone_type": "visits", "threshold_value": 100, "reward_type": "free_item", "reward_value": json.dumps({"item": "Cena completa per 2", "max_value": 100})},
{"milestone_name": "€100 Spesi", "milestone_type": "spend", "threshold_value": 100, "reward_type": "discount_percent", "reward_value": json.dumps({"percent": 10, "max": 15})},
{"milestone_name": "€500 Spesi", "milestone_type": "spend", "threshold_value": 500, "reward_type": "free_item", "reward_value": json.dumps({"item": "Bottiglia vino", "max_value": 30})},
{"milestone_name": "€1000 Spesi", "milestone_type": "spend", "threshold_value": 1000, "reward_type": "discount_percent", "reward_value": json.dumps({"percent": 20, "max": 50})},
{"milestone_name": "3 Mesi Fedeltà", "milestone_type": "months", "threshold_value": 3, "reward_type": "free_item", "reward_value": json.dumps({"item": "Caffè o dolce", "max_value": 5})},
{"milestone_name": "1 Anno Fedeltà", "milestone_type": "months", "threshold_value": 12, "reward_type": "discount_percent", "reward_value": json.dumps({"percent": 25, "max": 40})},
]
for ms in milestones:
mid = hashlib.md5(f"ms_{ms['milestone_name']}".encode()).hexdigest()[:12]
c.execute("""
INSERT INTO milestone_definitions
(id, client_id, milestone_name, milestone_type, threshold_value, reward_type, reward_value, active)
VALUES (?, 'template', ?, ?, ?, ?, ?, 1)
""", (mid, ms["milestone_name"], ms["milestone_type"], ms["threshold_value"], ms["reward_type"], ms["reward_value"]))
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])
# Create default campaigns
self._create_default_campaigns(client_id, client_data)
return client_id
def _create_default_campaigns(self, client_id: str, client_data: Dict):
business_name = client_data.get("business_name", "")
category = client_data.get("category", "").lower()
campaigns = [
{
"campaign_name": "Compleanni Automatici",
"campaign_type": "birthday",
"trigger_days_before": 0,
"channel": "email",
"incentive_type": "discount_percent",
"incentive_value": json.dumps({"percent": 20, "max": 30}),
"incentive_conditions": json.dumps({"min_spend": 20, "valid_days": 14, "excluded_items": ["bevande", "menu_bambini"]}),
"send_time": "10:00"
},
{
"campaign_name": "Compleanni WhatsApp",
"campaign_type": "birthday",
"trigger_days_before": 0,
"channel": "whatsapp",
"incentive_type": "free_item",
"incentive_value": json.dumps({"item": "Dolce della casa o calice di vino", "max_value": 15}),
"incentive_conditions": json.dumps({"min_spend": 25, "valid_days": 7}),
"send_time": "11:00"
},
{
"campaign_name": "Anniversario Prima Visita",
"campaign_type": "anniversary",
"trigger_days_before": 0,
"channel": "email",
"incentive_type": "discount_percent",
"incentive_value": json.dumps({"percent": 15, "max": 25}),
"incentive_conditions": json.dumps({"min_spend": 30, "valid_days": 30}),
"send_time": "10:00"
},
{
"campaign_name": "Welcome Back - 60 giorni inattivi",
"campaign_type": "welcome_back",
"trigger_days_before": 60,
"channel": "email",
"incentive_type": "discount_percent",
"incentive_value": json.dumps({"percent": 20, "max": 30}),
"incentive_conditions": json.dumps({"min_spend": 20, "valid_days": 14}),
"send_time": "10:00"
}
]
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
for camp in campaigns:
camp_id = hashlib.md5(f"camp_{client_id}{camp['campaign_name']}".encode()).hexdigest()[:12]
c.execute("""
INSERT INTO birthday_campaigns
(id, client_id, campaign_name, campaign_type, trigger_days_before, channel,
incentive_type, incentive_value, incentive_conditions, send_time, status)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active')
""", (
camp_id, client_id, camp["campaign_name"], camp["campaign_type"],
camp["trigger_days_before"], camp["channel"],
camp["incentive_type"], camp["incentive_value"], camp["incentive_conditions"],
camp["send_time"]
))
conn.commit()
conn.close()
def add_contacts(self, client_id: str, contacts: List[Dict]) -> int:
"""Add contacts for a client."""
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
added = 0
for contact in contacts:
contact_id = hashlib.md5(f"contact_{client_id}{contact.get('email', '')}{contact.get('phone', '')}{time.time()}".encode()).hexdigest()[:12]
try:
c.execute("""
INSERT INTO contacts
(id, client_id, first_name, last_name, email, phone, birth_date,
anniversary_date, anniversary_type, preferences, tags, source,
opted_in_email, opted_in_sms, opted_in_whatsapp)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 1, 1)
""", (
contact_id, client_id,
contact.get("first_name", ""),
contact.get("last_name", ""),
contact.get("email", ""),
contact.get("phone", ""),
contact.get("birth_date", ""),
contact.get("anniversary_date", ""),
contact.get("anniversary_type", "first_visit"),
json.dumps(contact.get("preferences", {})),
json.dumps(contact.get("tags", [])),
contact.get("source", "pos")
))
added += 1
except sqlite3.IntegrityError:
pass
conn.commit()
conn.close()
return added
def generate_daily_campaigns(self, client_id: str, category: str = "default") -> Dict:
"""Generate messages to send today for all active campaigns."""
today = datetime.now().date()
results = {"emails": 0, "whatsapp": 0, "sms": 0, "total": 0}
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
# Get active campaigns
c.execute("""
SELECT * FROM birthday_campaigns
WHERE client_id = ? AND status = 'active'
""", (client_id,))
campaigns = c.fetchall()
for campaign in campaigns:
camp_id = campaign[0]
camp_name = campaign[2]
camp_type = campaign[3]
trigger_before = campaign[4]
channel = campaign[6]
incentive_type = campaign[7]
incentive_value = campaign[8]
incentive_conditions = campaign[9]
send_time = campaign[10]
status = campaign[11]
# Find eligible contacts based on campaign type
if camp_type == "birthday":
# Contacts with birthday today (or trigger_before days before)
target_date = today + timedelta(days=trigger_before)
c.execute("""
SELECT id, first_name, last_name, email, phone, birth_date
FROM contacts
WHERE client_id = ? AND opted_in_email = 1
AND strftime('%m-%d', birth_date) = ?
""", (client_id, target_date.strftime('%m-%d')))
elif camp_type == "anniversary":
target_date = today + timedelta(days=trigger_before)
c.execute("""
SELECT id, first_name, last_name, email, phone, anniversary_date
FROM contacts
WHERE client_id = ? AND opted_in_email = 1
AND anniversary_date IS NOT NULL
AND strftime('%m-%d', anniversary_date) = ?
""", (client_id, target_date.strftime('%m-%d')))
elif camp_type == "welcome_back":
# Contacts inactive for trigger_before days
cutoff_date = today - timedelta(days=trigger_before)
c.execute("""
SELECT id, first_name, last_name, email, phone
FROM contacts
WHERE client_id = ? AND opted_in_email = 1
AND id NOT IN (
SELECT contact_id FROM birthday_messages
WHERE client_id = ? AND sent_at > ?
)
""", (client_id, client_id, cutoff_date.isoformat()))
else:
continue
eligible = c.fetchall()
for contact in eligible:
contact_id = contact[0]
first_name = contact[1]
last_name = contact[2] if len(contact) > 2 else ""
email = contact[3] if len(contact) > 3 else ""
phone = contact[4] if len(contact) > 4 else ""
# Generate incentive code
incentive_code = f"BD{hashlib.md5(f'{contact_id}{today}'.encode()).hexdigest()[:6].upper()}"
# Get template
c.execute("""
SELECT * FROM birthday_templates
WHERE (category = ? OR category = 'default')
AND campaign_type = ? AND channel = ? AND active = 1
LIMIT 1
""", (category, camp_type, channel))
template = c.fetchone()
if not template:
continue
# Fill template
expiry_date = (today + timedelta(days=30)).strftime('%d/%m/%Y')
variables = {
"first_name": first_name,
"last_name": last_name,
"business_name": "Business Name",
"incentive_description": "20% di sconto (max €30)",
"incentive_short": "20% sconto",
"expiry_date": expiry_date,
"cta_url": "https://business.it/prenota",
"short_url": "https://svoraj.me/bd123",
"booking_url": "https://business.it/prenota",
"unsubscribe_url": f"https://svoraj.me/unsubscribe?email={email}"
}
body = template[6] # body_text
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)
# Create message record
msg_id = hashlib.md5(f"msg_{camp_id}{contact_id}{today}".encode()).hexdigest()[:12]
c.execute("""
INSERT INTO birthday_messages
(id, campaign_id, client_id, contact_id, channel, template_id,
subject, body_text, sent_at, status, incentive_code, incentive_value)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, 'sent', ?, ?)
""", (
msg_id, camp_id, client_id, contact_id, channel, template[0],
subject, body, incentive_code, json.dumps(incentive_value)
))
results[channel] = results.get(channel, 0) + 1
results["total"] += 1
conn.commit()
conn.close()
return results
def check_milestones(self, client_id: str, contact_id: str, visit_count: int = None, spend_total: float = None, months_active: int = None) -> List[Dict]:
"""Check if contact reached any milestones."""
conn = sqlite3.connect(self.db_path)
c = conn.cursor()
c.execute("""
SELECT * FROM milestone_definitions
WHERE client_id IN (?, 'template') AND active = 1
""", (client_id,))
milestones = c.fetchall()
reached = []
for ms in milestones:
ms_id, _, ms_name, ms_type, threshold, reward_type, reward_value, active = ms
achieved = False
if ms_type == "visits" and visit_count is not None and visit_count >= threshold:
achieved = True
elif ms_type == "spend" and spend_total is not None and spend_total >= threshold:
achieved = True
elif ms_type == "months" and months_active is not None and months_active >= threshold:
achieved = True
if achieved:
# Check if already awarded
c.execute("""
SELECT COUNT(*) FROM birthday_messages
WHERE client_id = ? AND contact_id = ? AND incentive_code LIKE ?
""", (client_id, contact_id, f"%{ms_name[:3]}%"))
if c.fetchone()[0] == 0:
reached.append({
"milestone_id": ms_id,
"milestone_name": ms_name,
"reward_type": reward_type,
"reward_value": reward_value
})
conn.close()
return reached
def run_birthday_marketing():
print(f"\n{'='*60}")
print(f" BIRTHDAY/ANNIVERSARY MARKETING AUTOMATION")
print(f"{'='*60}\n")
marketing = BirthdayMarketing()
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 birthday marketing for {len(leads)} leads...")
total_contacts = 0
total_campaigns = 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
}
marketing.register_client(client_data)
# Generate sample contacts for this client
sample_contacts = []
for i in range(random.randint(50, 200)):
# Random birth date
birth_year = random.randint(1950, 2005)
birth_month = random.randint(1, 12)
birth_day = random.randint(1, 28)
birth_date = f"{birth_year}-{birth_month:02d}-{birth_day:02d}"
# Anniversary date (first visit) - random date in past 2 years
days_ago = random.randint(30, 730)
anniv_date = (datetime.now() - timedelta(days=days_ago)).strftime('%Y-%m-%d')
sample_contacts.append({
"first_name": f"Nome{i}",
"last_name": f"Cognome{i}",
"email": f"cliente{i}@{random.choice(['gmail.com', 'yahoo.com', 'outlook.com', 'libero.it'])}",
"phone": f"+39 3{random.randint(20, 99)} {random.randint(1000000, 9999999)}",
"birth_date": birth_date,
"anniversary_date": anniv_date,
"anniversary_type": "first_visit",
"preferences": json.dumps({"favorite_category": categoria}),
"tags": json.dumps(["vip" if i % 10 == 0 else "regular"]),
"source": random.choice(["pos", "website", "walkin", "referral"])
})
added = marketing.add_contacts(lead_id, sample_contacts)
total_contacts += added
# Count campaigns
conn = sqlite3.connect("/home/ubuntu/GhostAgency/birthday_marketing.db")
c = conn.cursor()
c.execute("SELECT COUNT(*) FROM birthday_campaigns WHERE client_id = ?", (lead_id,))
camp_count = c.fetchone()[0]
conn.close()
total_campaigns += camp_count
# Simulate today's sends
results = marketing.generate_daily_campaigns(lead_id, categoria.lower())
print(f" [+] {nome} ({categoria}) - Contatti: {added} | Campagne: {camp_count} | Invii oggi: {results['total']} (email: {results.get('email',0)}, whatsapp: {results.get('whatsapp',0)}, sms: {results.get('sms',0)})")
print(f"\n[+] Birthday Marketing setup complete")
print(f"[+] Total contacts imported: {total_contacts}")
print(f"[+] Total campaigns created: {total_campaigns}")
print(f"[+] Templates: Email, WhatsApp, SMS per compleanno, anniversario, welcome back, milestone")
print(f"[+] Next: Connect Gmail API + Twilio/Meta WhatsApp API + POS integration for real data")
if __name__ == "__main__":
run_birthday_marketing()