backend/database.py

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()