#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# -----------------------------
# IMPORTS
# -----------------------------
import mysql.connector               # To connect to your MySQL database
import json                          # To format JSON output (from email_checker)
import smtplib                        # To send emails via SMTP
from email.message import EmailMessage
from datetime import datetime
import html                          # To escape HTML special characters

# -----------------------------
# CALLING YOUR EXISTING CHECKER FILES
# -----------------------------
# These are your two Python files:
# email_checker.py → contains function verify_email_strong(email)
# phone_checker.py → contains function verify_phone(phone)
# We import the functions directly so we can call them on each lead.

from email_checker import verify_email_strong
from phone_checker import verify_phone

# -----------------------------
# CONFIGURATION
# -----------------------------
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "Abdulrahman#15",  # Replace with your DB password
    "database": "facebook_leads5"
}

SENDER = "Mini.Mo.KI.System@gmail.com"          # Sender email
RECIPIENTS = [
    "m.almoazen@estador.de"
]
APP_PASSWORD = "ftousflzzcnzqzes"         # Gmail App password

# Tables to check
TABLES = ["leads", "finance_leads"]             # Both tables you provided

# -----------------------------
# FUNCTION: FETCH LAST N LEADS
# -----------------------------
def fetch_last_leads(cursor, table, limit=3):
    """
    Fetch the last `limit` rows from the table.
    This is used to get only the most recent leads for testing.
    """
    cursor.execute(f"""
        SELECT id, lead_id, created_time_utc,
               first_name, last_name, email, phone_number
        FROM {table}
        ORDER BY id DESC
        LIMIT {limit}
    """)
    # We reverse the list so oldest of the last 10 is first
    return list(reversed(cursor.fetchall()))

# -----------------------------
# FUNCTION: ANALYZE A SINGLE LEAD
# -----------------------------
def analyze_lead(lead, table):
    """
    Call email_checker and phone_checker on a single lead.
    Returns a dict if the lead is invalid/fake, otherwise None.
    """
    # Call your email_checker.py function
    email_result = verify_email_strong(lead["email"]) if lead["email"] else {
        "status": "invalid",
        "reasons": ["missing_email"],
        "confidence": 0
    }

    # Call your phone_checker.py function
    phone_result = verify_phone(lead["phone_number"]) if lead["phone_number"] else "invalid: missing_phone"

    # Determine if lead is invalid
    email_bad = email_result["status"] == "invalid"
    phone_bad = phone_result.startswith("invalid:")

    if email_bad or phone_bad:
        # Build a dict containing all info we need for report
        return {
            "table": table,
            "db_id": lead["id"],
            "lead_id": lead["lead_id"],
            "name": f"{lead['first_name']} {lead['last_name']}",
            "created": str(lead["created_time_utc"]),
            "email": lead["email"],
            "email_result": email_result,
            "phone": lead["phone_number"],
            "phone_result": phone_result
        }
    return None

# -----------------------------
# FUNCTION: BUILD HTML REPORT
# -----------------------------
def build_report_html(bad_leads):
    """
    Build a complete HTML string containing all invalid leads.
    """
    ts = datetime.now().strftime("%Y-%m-%d %H:%M")
    if not bad_leads:
        return f"<p>No invalid leads detected at {ts}.</p>"

    # Build preview text for email
    preview_lines = []
    for l in bad_leads:
        preview_lines.append("="*60)
        preview_lines.append(f"TABLE: {l['table']}")
        preview_lines.append(f"DB ID: {l['db_id']} | LEAD ID: {l['lead_id']}")
        preview_lines.append(f"NAME: {l['name']}")
        preview_lines.append(f"CREATED: {l['created']}")
        preview_lines.append("EMAIL CHECK RESULT:")
        preview_lines.append(json.dumps(l["email_result"], indent=2))
        preview_lines.append("PHONE CHECK RESULT:")
        preview_lines.append(l["phone_result"])
        preview_lines.append("\n")

    preview_text = "\n".join(preview_lines)
    safe_preview = html.escape(preview_text)  # escape for HTML display

    # Build HTML email body
    html_body = f"""
    <html>
    <body style="font-family:Arial;background:#f4f4f4;padding:20px;">
      <div style="max-width:600px;margin:auto;background:#fff;border-radius:8px;">
        <div style="background:#d9534f;color:#fff;padding:20px;text-align:center;">
          <h1>🚨 Fake / Invalid Leads Detected</h1>
          <p>Report generated: {ts}</p>
        </div>
        <div style="padding:20px;color:#333;">
          <p>This email was generated automatically by the <b>Estador KI System</b>.</p>
          <h3>🧾 Preview of invalid leads:</h3>
          <pre style="background:#f7f7f7;padding:12px;border-radius:6px;white-space:pre-wrap;">
{safe_preview}
          </pre>
        </div>
        <div style="background:#f1f1f1;padding:12px;text-align:center;font-size:12px;">
          © 2026 Estador GmbH
        </div>
      </div>
    </body>
    </html>
    """
    return html_body

# -----------------------------
# FUNCTION: SEND EMAIL
# -----------------------------
def send_email(html_body):
    """
    Send the HTML email via Gmail SMTP.
    """
    msg = EmailMessage()
    msg["From"] = SENDER
    msg["To"] = ", ".join(RECIPIENTS)
    msg["Subject"] = "⚠️ Invalid / Fake Leads Report"
    msg.set_content("HTML email – please open with a compatible client.")
    msg.add_alternative(html_body, subtype="html")

    with smtplib.SMTP("smtp.gmail.com", 587, timeout=30) as smtp:
        smtp.starttls()
        smtp.login(SENDER, APP_PASSWORD)
        smtp.send_message(msg, to_addrs=RECIPIENTS)

    print("✅ Email sent with report.")

# -----------------------------
# MAIN FUNCTION
# -----------------------------
def main():
    # Connect to database
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)

    bad_leads = []

    # Process each table
    for table in TABLES:
        leads = fetch_last_leads(cursor, table, limit=10)
        for lead in leads:
            result = analyze_lead(lead, table)  # <-- HERE we CALL your checker files
            if result:
                bad_leads.append(result)

    cursor.close()
    conn.close()

    # Build HTML report and send email
    html_body = build_report_html(bad_leads)
    send_email(html_body)

# -----------------------------
# RUN SCRIPT
# -----------------------------
if __name__ == "__main__":
    main()