File size: 6,615 Bytes
d712cef | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 | import sqlite3
import json
import sys
import os
import re
import base64
def approve_and_move_email(email_id):
# Connect to the source and destination databases
review_db_path = os.path.join(os.environ.get('WORKSPACE_ROOT', '.'), 'Database/EmailsUnderReview/emailsUnderReview.db')
outbox_db_path = os.path.join(os.environ.get('WORKSPACE_ROOT', '.'), 'Database/EmailsSent/email_to_be_sent.db') # The new database
try:
# Open connections
conn_review = sqlite3.connect(review_db_path)
conn_outbox = sqlite3.connect(outbox_db_path)
cursor_review = conn_review.cursor()
cursor_outbox = conn_outbox.cursor()
# 1. Ensure the destination table exists in the new database
# We use the exact same schema, but change the default status
cursor_outbox.execute('''
CREATE TABLE IF NOT EXISTS ready_emails (
id INTEGER PRIMARY KEY,
body_json TEXT,
timestamp DATETIME,
followup_date DATETIME,
status TEXT DEFAULT 'ready to be sent',
company_name TEXT,
generated_subject TEXT,
company_email TEXT,
Unique_application_id TEXT
)
''')
# 2. Fetch the email from the review database
cursor_review.execute("""
SELECT id, body_json, timestamp, followup_date, status,
company_name, generated_subject, company_email, Unique_application_id
FROM tracking
WHERE id = ?
""", (email_id,))
row = cursor_review.fetchone()
if not row:
print(json.dumps({"ok": False, "error": f"Email ID {email_id} not found."}))
return False
# Unpack the row data
(row_id, body_json, timestamp, followup_date, current_status,
company_name, generated_subject, company_email, unique_app_id) = row
# 3. Process attachments, links, and clean the body_json payload
def process_email_attachments(row_id, body_str):
output_dir = os.path.join(os.environ.get('WORKSPACE_ROOT', '.'), f'metadatatracking/{row_id}')
os.makedirs(output_dir, exist_ok=True)
try:
data = json.loads(body_str)
except Exception:
data = {}
html_content = data.get("body", {}).get("generated_content", body_str)
if not isinstance(html_content, str):
html_content = str(html_content)
img_pattern = re.compile(r'<img[^>]+src="data:image/([^;]+);base64,([^"]+)"[^>]*>')
images_saved = []
def replacer(match):
ext = match.group(1)
b64_data = match.group(2)
idx = len(images_saved)
filename = f"image_{idx}.{ext}"
filepath = os.path.join(output_dir, filename)
try:
with open(filepath, "wb") as f:
f.write(base64.b64decode(b64_data))
images_saved.append(filename)
# Replace src payload entirely with local path
full_tag = match.group(0)
original_src = f'data:image/{ext};base64,{b64_data}'
return full_tag.replace(original_src, f'file://{filepath}')
except Exception as e:
return match.group(0)
modified_html = img_pattern.sub(replacer, html_content)
link_pattern = re.compile(r'<a[^>]+href="([^"]+)"')
links_found = link_pattern.findall(modified_html)
if "body" in data and isinstance(data["body"], dict):
data["body"]["generated_content"] = modified_html
else:
data = modified_html # fallback
final_body_str = json.dumps(data) if isinstance(data, dict) else data
with open(os.path.join(output_dir, 'email_data.json'), "w") as f:
json.dump({
"cleaned_html_body": modified_html,
"images_attached": images_saved,
"links_found": links_found
}, f, indent=4)
return final_body_str
cleaned_body_json = process_email_attachments(row_id, body_json)
# 4. Change status to the new requirement
new_status = 'ready to be sent'
# 5. Insert the complete row into the new database
cursor_outbox.execute("""
INSERT INTO ready_emails
(id, body_json, timestamp, followup_date, status,
company_name, generated_subject, company_email, Unique_application_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (row_id, cleaned_body_json, timestamp, followup_date, new_status,
company_name, generated_subject, company_email, unique_app_id))
# 6. Delete the entry from the original database
cursor_review.execute("DELETE FROM tracking WHERE id = ?", (email_id,))
# 7. Commit transactions on BOTH databases
conn_outbox.commit()
conn_review.commit()
print(json.dumps({"ok": True, "message": f"Success: Email {email_id} has been moved to the outbox."}))
return True
except sqlite3.Error as e:
# If any database error occurs, roll back both databases so data isn't lost
print(json.dumps({"ok": False, "error": f"Database error occurred: {e}"}))
if 'conn_outbox' in locals(): conn_outbox.rollback()
if 'conn_review' in locals(): conn_review.rollback()
return False
finally:
# Always close connections to free up the DB locks
if 'conn_outbox' in locals(): conn_outbox.close()
if 'conn_review' in locals(): conn_review.close()
if __name__ == '__main__':
try:
input_data = sys.stdin.read().strip()
if input_data:
payload = json.loads(input_data)
email_id = payload.get('id')
if email_id is not None:
approve_and_move_email(int(email_id))
else:
print(json.dumps({"ok": False, "error": "No 'id' provided in input"}))
else:
print(json.dumps({"ok": False, "error": "No input provided"}))
except Exception as e:
print(json.dumps({"ok": False, "error": str(e)})) |