File size: 4,211 Bytes
771f178 | 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 | import sqlite3
import os
import hashlib
import uuid
import datetime
DB_PATH = os.path.join(os.path.dirname(__file__), "interactions.db")
def init_db():
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS interactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
property_id TEXT NOT NULL,
interaction_type TEXT NOT NULL,
score INTEGER NOT NULL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS chat_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
role TEXT NOT NULL,
content TEXT NOT NULL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_user ON interactions(user_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_prop ON interactions(property_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_chat_user ON chat_history(user_id)')
conn.commit()
conn.close()
def hash_password(password):
return hashlib.sha256(password.encode('utf-8')).hexdigest()
def create_user(username, password):
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
try:
user_id = str(uuid.uuid4())
cursor.execute("INSERT INTO users (id, username, password_hash) VALUES (?, ?, ?)",
(user_id, username, hash_password(password)))
conn.commit()
return user_id
except sqlite3.IntegrityError:
return None
finally:
conn.close()
def verify_user(username, password):
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT id FROM users WHERE username=? AND password_hash=?",
(username, hash_password(password)))
row = cursor.fetchone()
conn.close()
return row[0] if row else None
def save_chat_message(user_id, role, content):
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("INSERT INTO chat_history (user_id, role, content) VALUES (?, ?, ?)",
(user_id, role, content))
conn.commit()
conn.close()
def get_chat_history(user_id, limit=20):
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT role, content FROM chat_history WHERE user_id=? ORDER BY timestamp ASC LIMIT ?",
(user_id, limit))
rows = cursor.fetchall()
conn.close()
return [{"role": row[0], "content": row[1]} for row in rows]
def log_interaction(user_id, property_id, interaction_type):
score = 5 if interaction_type == 'like' else 1
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
if interaction_type == 'like':
cursor.execute('''
SELECT id FROM interactions WHERE user_id=? AND property_id=? AND interaction_type='like'
''', (user_id, property_id))
if cursor.fetchone():
conn.close()
return
cursor.execute('''
INSERT INTO interactions (user_id, property_id, interaction_type, score)
VALUES (?, ?, ?, ?)
''', (user_id, property_id, interaction_type, score))
conn.commit()
conn.close()
def get_all_interactions():
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('''
SELECT user_id, property_id, SUM(score) as total_score
FROM interactions
GROUP BY user_id, property_id
''')
rows = cursor.fetchall()
conn.close()
return rows
def get_user_interactions(user_id):
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('SELECT property_id FROM interactions WHERE user_id=?', (user_id,))
rows = [r[0] for r in cursor.fetchall()]
conn.close()
return rows
# Initialize DB when imported
init_db()
|