# -*- coding: utf-8 -*-
"""emailspamdetection.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/115Nv6A8Y7GczZGUhXjDqVT5dSDWyNNGK
"""

import nltk
import re
import joblib
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.naive_bayes import MultinomialNB
from sklearn.metrics import accuracy_score
import os
import imaplib
import email
from email.header import decode_header
from datetime import datetime
import socket
import psycopg2
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine
import numpy as np

from nltk.corpus import stopwords
from nltk.tokenize import word_tokenize
from nltk.stem import WordNetLemmatizer

try:
    nltk.data.find('tokenizers/punkt')
except LookupError:
    nltk.download('punkt', quiet=True)
try:
    nltk.data.find('corpora/wordnet')
except LookupError:
    nltk.download('wordnet', quiet=True)
try:
    nltk.data.find('corpora/stopwords')
except LookupError:
    nltk.download('stopwords', quiet=True)
try:
    nltk.data.find('tokenizers/punkt_tab')
except LookupError:
    nltk.download('punkt_tab', quiet=True)

lemmatizer = WordNetLemmatizer()
stop_words = set(stopwords.words('english'))

print("[OK] NLTK 'punkt', 'wordnet', 'punkt_tab' and 'stopwords' corpora downloaded and lemmatizer initialized.")

def clean_text(text):
    """
    Cleans, tokenizes, lemmatizes, and removes stopwords from the input text.
    """
    if not isinstance(text, str):
        return ""
    text = text.lower()
    text = re.sub(r"[^a-z0-9\s]", "", text)
    tokens = word_tokenize(text)
    lemmatized_tokens = [lemmatizer.lemmatize(word) for word in tokens if word not in stop_words]
    return " ".join(lemmatized_tokens)

def classify(score):
    # Widening the 'undecided' range to ensure more emails fall into this category
    if score >= 0.70:
        return "spam"
    elif score <= 0.30:
        return "ham"
    else:
        return "undecided"

def get_email_summary(body, max_len=200):
    if not isinstance(body, str):
        return ""
    clean_body = body.replace('\n', ' ').replace('\r', '').strip()
    if len(clean_body) > max_len:
        return clean_body[:max_len] + "..."
    return clean_body

from google.colab import userdata

SMTP_HOST = userdata.get("SMTP_HOST")
SMTP_PORT = userdata.get("SMTP_PORT")
SMTP_USERNAME = userdata.get("SMTP_USERNAME")
SMTP_PASSWORD = userdata.get("SMTP_PASSWORD")

IMAP_HOST = userdata.get("IMAP_HOST")
IMAP_PORT = userdata.get("IMAP_PORT")
IMAP_USERNAME = userdata.get("IMAP_USERNAME")
IMAP_PASSWORD = userdata.get("IMAP_PASSWORD")

SMTP_PORT = int(SMTP_PORT)
IMAP_PORT = int(IMAP_PORT)

print("Email credentials loaded and port numbers converted.")

global global_model_accuracy
global_model_accuracy = 0.0

dirs_to_create = [
    "/inbox_emails/",
    "/spam/",
    "/ham/",
    "/undecided/",
    "/models/"
]

for d in dirs_to_create:
    os.makedirs(d, exist_ok=True)
    print(f"Created directory: {d}")

print("[OK] All global setup and utilities initialized.")

import psycopg2

DB_HOST = "postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com"
DB_NAME = "email_filter"
DB_USER = "postgres"
DB_PASSWORD = "p4python"
DB_PORT = 5432

conn = psycopg2.connect(
    host=DB_HOST,
    database=DB_NAME,
    user=DB_USER,
    password=DB_PASSWORD,
    port=DB_PORT
)
cur = conn.cursor()

print(f"Connected to database: {DB_NAME}")

cur.execute("""
CREATE TABLE IF NOT EXISTS email_filter (
    id SERIAL PRIMARY KEY,
    sender_email TEXT,
    subject TEXT,
    body TEXT,
    spam_probability FLOAT,
    predicted_label TEXT,
    feedback_label TEXT,
    is_reviewed BOOLEAN DEFAULT FALSE,
    received_at TIMESTAMP DEFAULT NOW(),
    email_summary TEXT,
    model_accuracy REAL,
    imap_uid TEXT UNIQUE
);
""")
conn.commit()
print("[OK] 'email_filter' table is ready with all specified columns.")

cur.close()
conn.close()
print("PostgreSQL connection closed.")

"""The model training requires a dataset. Let's download the `SPAM text message 20170820 - Data.csv` file to proceed."""

!wget -O "/content/SPAM text message 20170820 - Data.csv" "https://archive.ics.uci.edu/ml/machine-learning-databases/00228/smsspamcollection.zip" && unzip -o "/content/SPAM text message 20170820 - Data.csv" -d /content/ && mv /content/SMSSpamCollection /content/SPAM\ text\ message\ 20170820\ -\ Data.csv

import re
import joblib
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.naive_bayes import MultinomialNB
from sklearn.metrics import accuracy_score, classification_report
import os

df_model = pd.read_csv("/content/SPAM text message 20170820 - Data.csv", sep='\t', header=None, names=['label', 'text'])
df_model['text'] = df_model['text'].apply(clean_text)
print("Dataset loaded and preprocessed with tokenization, lemmatization, and stop word removal.")

X_train, X_test, y_train, y_test = train_test_split(
    df_model['text'], df_model['label'], test_size=0.2, random_state=42
)
print("Data split into training and testing sets.")

final_vectorizer = TfidfVectorizer(
    ngram_range=(1, 2),
    min_df=5
)
X_train_vec_final = final_vectorizer.fit_transform(X_train)
X_test_vec_final = final_vectorizer.transform(X_test)
print("TF-IDF vectorizer trained and data transformed using enhanced text processing.")

final_model = MultinomialNB(alpha=0.1)
final_model.fit(X_train_vec_final, y_train)
print("Multinomial Naive Bayes model trained with enhanced text processing.")

y_pred_final = final_model.predict(X_test_vec_final)
final_accuracy = accuracy_score(y_test, y_pred_final)
print(f"\nFinal Model Accuracy (alpha=0.1, tuned vectorizer, enhanced preprocessing): {final_accuracy:.4f}")

print("\nClassification Report:")
print(classification_report(y_test, y_pred_final))

global global_model_accuracy
global_model_accuracy = final_accuracy

os.makedirs("/models/", exist_ok=True)
model_path = "/models/final_model.pkl"
vectorizer_path = "/models/final_vectorizer.pkl"

joblib.dump(final_model, model_path)
print(f"[OK] final_model saved to {model_path}")

joblib.dump(final_vectorizer, vectorizer_path)
print(f"[OK] final_vectorizer saved to {vectorizer_path}")

final_model_loaded = None
final_vectorizer_loaded = None
try:
    final_model_loaded = joblib.load(model_path)
    final_vectorizer_loaded = joblib.load(vectorizer_path)
    if "spam" in final_model_loaded.classes_:
        spam_index = list(final_model_loaded.classes_).index("spam")
    print("[OK] Model and vectorizer re-loaded successfully for immediate use.")
except FileNotFoundError:
    print("Error: Model or vectorizer files not found.")
except Exception as e:
    print(f"Error loading model components: {e}")

print("clean_text and classify functions are defined.")
print(f"global_model_accuracy is set to: {global_model_accuracy:.4f}")

import imaplib
import email
from email.header import decode_header
import psycopg2
import os
from datetime import datetime
import re
import joblib
from google.colab import userdata

IMAP_HOST = userdata.get("IMAP_HOST")
IMAP_PORT = userdata.get("IMAP_PORT")
IMAP_USERNAME = userdata.get("IMAP_USERNAME").strip() # Strip whitespace
IMAP_PASSWORD = userdata.get("IMAP_PASSWORD").strip() # Strip whitespace

IMAP_PORT = int(IMAP_PORT)

DB_HOST = "postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com"
DB_NAME = "email_filter"
DB_USER = "postgres"
DB_PASSWORD = "p4python"
DB_PORT = 5432

conn = psycopg2.connect(
    host=DB_HOST,
    database=DB_NAME,
    user=DB_USER,
    password=DB_PASSWORD,
    port=DB_PORT
)
cur = conn.cursor()

print("[OK] Connected to PostgreSQL.")

if 'final_vectorizer' not in globals() or 'final_model' not in globals() or 'spam_index' not in globals():
    print("Loading final_vectorizer and final_model from /models/...")
    try:
        final_vectorizer = joblib.load("/models/final_vectorizer.pkl")
        final_model = joblib.load("/models/final_model.pkl")
        spam_index = list(final_model.classes_).index("spam")
        print("[OK] final_vectorizer, final_model, and spam_index loaded.")
    except Exception as e:
        print(f"Error loading model components: {e}. Skipping prediction for all emails.")
        final_vectorizer = None
        final_model = None
        spam_index = None

if 'global_model_accuracy' not in globals():
    print("Warning: 'global_model_accuracy' not found. Setting to 0.0 for now.")
    global_model_accuracy = 0.0
print(f"Current global_model_accuracy: {global_model_accuracy:.4f}")

for folder in ["/inbox_emails/", "/spam/", "/ham/", "/undecided/"]:
    os.makedirs(folder, exist_ok=True)
print("[OK] Ensured classification directories exist.")

mail = imaplib.IMAP4_SSL(IMAP_HOST, IMAP_PORT)
mail.login(IMAP_USERNAME, IMAP_PASSWORD)
mail.select("INBOX")
print("[OK] IMAP connection established and INBOX selected.")

status, messages = mail.uid("search", None, "ALL")
uids = messages[0].split()

last_100_uids = uids[-100:] if len(uids) > 100 else uids
print(f"Found {len(uids)} emails, processing the last {len(last_100_uids)}.")

folder_map = {
    "spam": "/spam/",
    "ham": "/ham/",
    "undecided": "/undecided/"
}

processed_count = 0
for e_id_bytes in last_100_uids:
    e_id = e_id_bytes.decode('utf-8')
    status, msg_data = mail.uid("fetch", e_id_bytes, "(RFC822)")
    if status != 'OK':
        print(f"Error fetching UID {e_id}: {status}")
        continue

    for response_part in msg_data:
        if isinstance(response_part, tuple):
            raw_email = response_part[1]

            timestamp = datetime.now().strftime("%Y%m%d%H%M%S%f")
            raw_email_filename = f"/inbox_emails/email_{timestamp}_{e_id}.eml"

            with open(raw_email_filename, 'wb') as f:
                f.write(raw_email)

            msg = email.message_from_bytes(raw_email)
            sender = msg["From"]

            subject_header = msg.get("Subject", "(no subject)")
            try:
                decoded_subject = decode_header(subject_header)[0]
                if isinstance(decoded_subject[0], bytes):
                    subject = decoded_subject[0].decode(decoded_subject[1] or "utf-8")
                else:
                    subject = decoded_subject[0]
            except Exception:
                subject = subject_header

            body = ""
            if msg.is_multipart():
                for part in msg.walk():
                    ctype = part.get_content_type()
                    cdisposition = str(part.get("Content-Disposition"))

                    if ctype == "text/plain" and "attachment" not in cdisposition:
                        try:
                            body = part.get_payload(decode=True).decode()
                            break
                        except UnicodeDecodeError:
                            body = ""
            else:
                try:
                    body = msg.get_payload(decode=True).decode()
                except UnicodeDecodeError:
                    body = ""

            email_summary_val = get_email_summary(body)

            prob = None
            label = "unknown"
            if final_vectorizer and final_model and spam_index is not None and 'classify' in globals() and 'clean_text' in globals():
                try:
                    clean_msg_body = clean_text(body)
                    X = final_vectorizer.transform([clean_msg_body])
                    prob = final_model.predict_proba(X)[0][spam_index]
                    label = classify(prob)
                except Exception as e:
                    print(f"Error during spam prediction for UID {e_id}: {e}")
            else:
                print(f"Skipping spam prediction for UID {e_id} due to missing model components.")

            cur.execute(
                """
                INSERT INTO email_filter (imap_uid, sender_email, subject, body, spam_probability, predicted_label, is_reviewed, received_at, email_summary, model_accuracy)
                VALUES (%s, %s, %s, %s, %s, %s, FALSE, NOW(), %s, %s)
                ON CONFLICT (imap_uid) DO UPDATE
                SET sender_email = EXCLUDED.sender_email,
                    subject = EXCLUDED.subject,
                    body = EXCLUDED.body,
                    spam_probability = EXCLUDED.spam_probability,
                    predicted_label = EXCLUDED.predicted_label,
                    is_reviewed = FALSE,
                    received_at = NOW(),
                    email_summary = EXCLUDED.email_summary,
                    model_accuracy = EXCLUDED.model_accuracy
                """,
                (e_id, sender, subject, body, float(prob) if prob is not None else None, label, email_summary_val, global_model_accuracy)
            )
            conn.commit()
            print(f"Processed email UID {e_id} from {sender} as {label} to database.")

            target_folder = folder_map.get(label, "/undecided/")

            sanitized_subject = re.sub(r'[\\/:*?"<>|]', '', subject).replace(' ', '_')[:50]

            classified_filename = f"{target_folder}email_{timestamp}_{e_id}_{sanitized_subject}.txt"

            with open(classified_filename, 'w', encoding='utf-8') as f:
                f.write(f"From: {sender}\n")
                f.write(f"Subject: {subject}\n\n")
                f.write(body)
            processed_count += 1

print(f"Successfully processed {processed_count} emails.")

cur.close()
mail.logout()
conn.close()
print("[OK] IMAP connection closed and database connection closed.")

import re
import psycopg2
import pandas as pd
from sqlalchemy import create_engine

def clean_text(text):
    if not isinstance(text, str):
        return ""
    text = text.lower()
    text = re.sub(r"[^a-z0-9\s]", "", text)
    tokens = word_tokenize(text)
    lemmatized_tokens = [lemmatizer.lemmatize(word) for word in tokens if word not in stop_words]
    return " ".join(lemmatized_tokens)

def classify(score):
    if score >= 0.65:
        return "spam"
    elif score <= 0.40:
        return "ham"
    else:
        return "undecided"

DB_HOST = "postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com"
DB_NAME = "email_filter"
DB_USER = "postgres"
DB_PASSWORD = "p4python"
DB_PORT = 5432

db_connection_str = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(db_connection_str)

conn = psycopg2.connect(
    host=DB_HOST,
    database=DB_NAME,
    user=DB_USER,
    password=DB_PASSWORD,
    port=DB_PORT
)
cur = conn.cursor()

print("[OK] Connected to PostgreSQL for re-analysis.")

query_undecided = "SELECT id, sender_email, subject, body, spam_probability, predicted_label FROM email_filter WHERE predicted_label = 'undecided'"
undecided_emails_df = pd.read_sql(query_undecided, engine)

print(f"Fetched {len(undecided_emails_df)} emails currently classified as 'undecided' for re-analysis.")

def analyze_subject(subject):
    subject = str(subject).lower()

    strong_spam_keywords = [
        "free!!!", "win now", "guarantee money", "urgent response", "click now",
        "$$", "limited time", "act fast", "congratulations you won", "prize winner"
    ]
    mild_spam_keywords = [
        "discount", "offer", "deal", "exclusive", "advertisement", "promotion",
        "investment", "earn money", "online casino", "viagra", "sex", "loan"
    ]

    strong_score = sum(1 for kw in strong_spam_keywords if kw in subject)
    mild_score = sum(1 for kw in mild_spam_keywords if kw in subject)

    if strong_score > 0:
        return 0.8
    elif mild_score > 0:
        return 0.5
    else:
        return 0.1

def get_sender_spam_history(sender_email, conn):
    match = re.search(r'<(.*?)>', sender_email)
    clean_sender_email = match.group(1) if match else sender_email

    cur_temp = conn.cursor()

    query = """
    SELECT predicted_label, feedback_label
    FROM email_filter
    WHERE sender_email LIKE %s OR sender_email = %s
    """
    cur_temp.execute(query, (f'%<{clean_sender_email}>%', clean_sender_email))
    sender_emails = cur_temp.fetchall()
    cur_temp.close()

    if not sender_emails:
        return 0.0

    total_emails = len(sender_emails)
    spam_count = 0

    for pred_label, fb_label in sender_emails:
        if pred_label == 'spam' or fb_label == 'spam':
            spam_count += 1

    return spam_count / total_emails

reclassified_to_spam = 0
reclassified_to_ham = 0

if not undecided_emails_df.empty:
    for index, email_row in undecided_emails_df.iterrows():
        original_id = email_row['id']
        original_prob = email_row['spam_probability']

        subject_spam_score = analyze_subject(email_row['subject'])

        sender_spam_ratio = get_sender_spam_history(email_row['sender_email'], conn)

        combined_score = (
            0.5 * subject_spam_score +
            0.4 * sender_spam_ratio +
            0.1 * (original_prob if original_prob is not None else 0.5)
        )

        new_predicted_label = 'undecided'
        if combined_score >= 0.7:
            new_predicted_label = 'spam'
        elif combined_score <= 0.3:
            new_predicted_label = 'ham'

        if new_predicted_label != 'undecided':
            cur.execute(
                """
                UPDATE email_filter
                SET predicted_label = %s,
                    spam_probability = %s
                WHERE id = %s
                """,
                (new_predicted_label, float(combined_score), original_id)
            )
            if new_predicted_label == 'spam':
                reclassified_to_spam += 1
            else:
                reclassified_to_ham += 1
            print(f"Email ID {original_id} re-classified from 'undecided' to '{new_predicted_label}' with score {combined_score:.2f}")

conn.commit()
cur.close()
conn.close()

print("\n--- Re-classification Summary ---")
print(f"Total undecided emails processed: {len(undecided_emails_df)}")
print(f"Re-classified to SPAM: {reclassified_to_spam}")
print(f"Re-classified to HAM: {reclassified_to_ham}")
print(f"Remaining undecided: {len(undecided_emails_df) - (reclassified_to_spam + reclassified_to_ham)}")
print("[OK] Re-analysis of undecided emails complete and database updated.")

import re
import psycopg2
import pandas as pd
from sqlalchemy import create_engine
import joblib
from sklearn.model_selection import train_test_split
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.naive_bayes import MultinomialNB
import os
import nltk
from nltk.corpus import stopwords
from nltk.tokenize import word_tokenize
from nltk.stem import WordNetLemmatizer

try:
    nltk.data.find('tokenizers/punkt')
except LookupError:
    nltk.download('punkt', quiet=True)
try:
    nltk.data.find('corpora/wordnet')
except LookupError:
    nltk.download('wordnet', quiet=True)
try:
    nltk.data.find('corpora/stopwords')
except LookupError:
    nltk.download('stopwords', quiet=True)
try:
    nltk.data.find('tokenizers/punkt_tab')
except LookupError:
    nltk.download('punkt_tab', quiet=True)

lemmatizer = WordNetLemmatizer()
stop_words = set(stopwords.words('english'))

print("[OK] NLTK 'punkt', 'wordnet', 'punkt_tab' and 'stopwords' corpora downloaded and lemmatizer initialized for this cell.")

def clean_text(text):
    """
    Cleans, tokenizes, lemmatizes, and removes stopwords from the input text.
    """
    if not isinstance(text, str):
        return ""
    text = text.lower()
    text = re.sub(r"[^a-z0-9\s]", "", text)
    tokens = word_tokenize(text)
    lemmatized_tokens = [lemmatizer.lemmatize(word) for word in tokens if word not in stop_words] # Lemmatize and remove stop words
    return " ".join(lemmatized_tokens)

def classify(score):
    if score >= 0.65:
        return "spam"
    elif score <= 0.40:
        return "ham"
    else:
        return "undecided"

DB_HOST = "postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com"
DB_NAME = "email_filter"
DB_USER = "postgres"
DB_PASSWORD = "p4python"
DB_PORT = 5432

db_connection_str = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(db_connection_str)

conn = psycopg2.connect(
    host=DB_HOST,
    database=DB_NAME,
    user=DB_USER,
    password=DB_PASSWORD,
    port=DB_PORT
)
cur = conn.cursor()

print("[OK] Connected to PostgreSQL for user feedback simulation and retraining.")

# Modified: Directly query for genuinely undecided emails for feedback simulation.
# Removed the step that re-labels 'ham' emails as 'undecided'.
query_undecided = "SELECT id, sender_email, subject, body, spam_probability, predicted_label FROM email_filter WHERE predicted_label = 'undecided' AND is_reviewed = FALSE LIMIT 3"
undecided_emails_for_feedback = pd.read_sql(query_undecided, engine)

print(f"Fetched {len(undecided_emails_for_feedback)} genuinely undecided emails for user feedback simulation.")

updated_emails_summary = []

if not undecided_emails_for_feedback.empty:
    print("Simulating user feedback...")
    for index, email_row in undecided_emails_for_feedback.iterrows():
        email_id = email_row['id']
        current_predicted_label = email_row['predicted_label']

        # Simulate user feedback: alternate between spam and ham
        if index % 2 == 0:
            simulated_feedback_label = 'spam'
        else:
            simulated_feedback_label = 'ham'

        cur.execute(
            """
            UPDATE email_filter
            SET feedback_label = %s,
                is_reviewed = TRUE
            WHERE id = %s
            """,
            (simulated_feedback_label, email_id)
        )
        updated_emails_summary.append({
            'id': email_id,
            'original_predicted_label': current_predicted_label,
            'new_feedback_label': simulated_feedback_label
        })

    conn.commit()
    print("[OK] Simulated user feedback and updated database.")

    print("\n--- User Feedback Simulation Summary ---")
    for item in updated_emails_summary:
        print(f"Email ID: {item['id']}, Original Predicted: {item['original_predicted_label']}, Feedback: {item['new_feedback_label']}")

else:
    print("No genuinely undecided emails available to simulate user feedback on. Ensure some emails are classified as 'undecided' by the model or re-analysis.")

FEEDBACK_THRESHOLD = 3
query_reviewed_emails = "SELECT id, body, feedback_label FROM email_filter WHERE is_reviewed = TRUE AND feedback_label IS NOT NULL"
reviewed_emails_df = pd.read_sql(query_reviewed_emails, engine)

print(f"Fetched {len(reviewed_emails_df)} reviewed emails with feedback.")

if len(reviewed_emails_df) < FEEDBACK_THRESHOLD:
    print(f"Insufficient feedback for retraining. Need at least {FEEDBACK_THRESHOLD} reviewed emails, but found {len(reviewed_emails_df)}.")
else:
    print(f"Sufficient feedback ({len(reviewed_emails_df)} emails) collected for retraining. Proceeding with retraining...")

    feedback_df = reviewed_emails_df[['body', 'feedback_label']].copy()
    feedback_df.rename(columns={'feedback_label': 'label'}, inplace=True)
    feedback_df['body'] = feedback_df['body'].apply(clean_text) # Apply clean_text
    feedback_df.rename(columns={'body': 'text'}, inplace=True)
    print("Prepared feedback data.")

    try:
        if 'df_model' not in globals() or df_model.empty:
            df_model_original = pd.read_csv("/content/SPAM text message 20170820 - Data.csv", sep='\t', header=None, names=['label', 'text'])
            df_model_original['text'] = df_model_original['text'].apply(clean_text) # Apply clean_text
            print("Loaded and preprocessed original training data from CSV.")
        else:
            df_model_original = df_model.copy()
            print("Using existing df_model from previous training step.")
    except NameError:
        df_model_original = pd.read_csv("/content/SPAM text message 20170820 - Data.csv", sep='\t', header=None, names=['label', 'text'])
        df_model_original['text'] = df_model_original['text'].apply(clean_text) # Apply clean_text
        print("Loaded and preprocessed original training data from CSV (NameError fallback).")

    combined_training_df = pd.concat([df_model_original[['text', 'label']], feedback_df], ignore_index=True)
    print(f"Combined original training data with feedback data. Total samples: {len(combined_training_df)}.")

    X_retrain = combined_training_df['text']
    y_retrain = combined_training_df['label']

    final_vectorizer = TfidfVectorizer(
        ngram_range=(1, 2),
        min_df=5
    )
    X_retrain_vec = final_vectorizer.fit_transform(X_retrain)
    print("Re-vectorized combined training data.")

    final_model = MultinomialNB(alpha=0.1)
    final_model.fit(X_retrain_vec, y_retrain)
    print("[OK] Retrained final_model with combined data.")
    print(f"Model retraining complete. Current global_model_accuracy (from previous step): {globals().get('global_model_accuracy', 0.0):.4f}")

    model_path = "/models/final_model.pkl"
    vectorizer_path = "/models/final_vectorizer.pkl"
    joblib.dump(final_model, model_path)
    joblib.dump(final_vectorizer, vectorizer_path)
    print(f"[OK] Updated final_model and final_vectorizer saved to {model_path} and {vectorizer_path}.")

    for email_id in reviewed_emails_df['id']:
        cur.execute(
            """
            UPDATE email_filter
            SET is_reviewed = FALSE,
                feedback_label = NULL
            WHERE id = %s
            """,
            (email_id,)
        )
    conn.commit()
    print(f"Reset is_reviewed and feedback_label for {len(reviewed_emails_df)} emails in the database.")

cur.close()
conn.close()
print("PostgreSQL connection closed.")

import pandas as pd
import psycopg2
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.metrics import confusion_matrix, classification_report

# Ensure y_test and y_pred_final are defined for the confusion matrix and classification report
# If they are not defined from a previous successful run of 9c33b4a6, this part might error.
# For continuous execution, we assume 9c33b4a6 runs first and successfully sets these.
if 'y_test' in globals() and 'y_pred_final' in globals():
    cm = confusion_matrix(y_test, y_pred_final)
    print("\nConfusion Matrix:")
    print(cm)

    plt.figure(figsize=(8, 6))
    sns.heatmap(cm, annot=True, fmt='d', cmap='Blues',
                xticklabels=['ham', 'spam'], yticklabels=['ham', 'spam'])
    plt.xlabel('Predicted Label')
    plt.ylabel('True Label')
    plt.title('Confusion Matrix for Final Model')
    plt.show()

    print("\nClassification Report:")
    print(classification_report(y_test, y_pred_final))
else:
    print("Skipping confusion matrix and classification report: y_test or y_pred_final not found. Ensure model training cell (9c33b4a6) ran successfully.")

DB_HOST = "postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com"
DB_NAME = "email_filter"
DB_USER = "postgres"
DB_PASSWORD = "p4python"
DB_PORT = 5432

db_connection_str = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(db_connection_str)

conn = None
cur = None

try:
    conn = psycopg2.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        port=DB_PORT
    )
    cur = conn.cursor()
    print("\n[OK] Connected to PostgreSQL database for visualization.")

    query_labels = "SELECT predicted_label FROM email_filter;"
    df_labels = pd.read_sql(query_labels, engine)
    print(f"Fetched {len(df_labels)} email classification results from DB.")

    if not df_labels.empty:
        label_counts = df_labels['predicted_label'].value_counts()

        plt.figure(figsize=(8, 6))
        sns.barplot(x=label_counts.index, y=label_counts.values, palette='viridis', hue=label_counts.index, legend=False)
        plt.xlabel('Predicted Label')
        plt.ylabel('Number of Emails')
        plt.title('Distribution of Classified Emails in Database')
        plt.show()
        print("[OK] Displayed distribution of classified emails from database.")
    else:
        print("No email classification data found in database to display.")

    query_probabilities = "SELECT spam_probability FROM email_filter WHERE spam_probability IS NOT NULL;"
    df_probabilities = pd.read_sql(query_probabilities, engine)
    print(f"Fetched {len(df_probabilities)} spam probability scores from DB.")

    if not df_probabilities.empty:
        plt.figure(figsize=(10, 6))
        sns.histplot(df_probabilities['spam_probability'], bins=20, kde=True, color='skyblue')
        plt.xlabel('Spam Probability')
        plt.ylabel('Frequency / Density')
        plt.title('Distribution of Spam Probabilities in Database')
        plt.show()
        print("[OK] Displayed distribution of spam probabilities from database.")
    else:
        print("No spam probability data found in database to display.")

except Exception as e:
    print(f"An error occurred during visualization steps: {e}")
finally:
    if cur:
        cur.close()
    if conn:
        conn.close()
    print("PostgreSQL connection closed.")