utils/database.py

import sqlite3
from contextlib import contextmanager
from typing import Generator
import logging
import os
from datetime import datetime

CREATE INDEX idx_social_accounts_user_id ON social_accounts(user_id);
CREATE INDEX idx_social_accounts_heir_id ON social_accounts(heir_id);

if not hasattr(st.session_state, 'db'):
    st.error("Database connection not established")
    return

# Initialize service
services = ServiceIntegration(st.session_state.db)

# For face verification
result = services.verify_face(image_path, stored_encoding)

# For activity monitoring
result = services.monitor_activity(activity_data)

# For document generation
result = services.generate_legal_document(user_data, assets, heirs)

cursor.execute("""
    CREATE TABLE IF NOT EXISTS security_events (
        id TEXT PRIMARY KEY,
        event_type TEXT NOT NULL,
        success BOOLEAN,
        details TEXT,
        timestamp TEXT NOT NULL
    )
""")

class DatabaseManager:
    def __init__(self, db_path: str = 'digital_heir.db'):
        self.db_path = db_path
        self._create_backup_dir()
    
    @contextmanager
    def get_connection(self) -> Generator[sqlite3.Connection, None, None]:
        conn = sqlite3.connect(self.db_path)
        try:
            yield conn
        finally:
            conn.close()
    
    def _create_backup_dir(self):
        os.makedirs('backups', exist_ok=True)
    
    def backup(self):
        backup_path = f"backups/digital_heir_{datetime.now().strftime('%Y%m%d_%H%M')}.db"
        
        try:
            with self.get_connection() as src, sqlite3.connect(backup_path) as dst:
                src.backup(dst)
            logging.info(f"Database backup created: {backup_path}")
        except Exception as e:
            logging.error(f"Database backup failed: {str(e)}")
    
    def optimize(self):
        with self.get_connection() as conn:
            conn.execute("VACUUM")
            conn.execute("ANALYZE")

db_manager = DatabaseManager()