models.py

# models.py
import sqlite3
from datetime import datetime
from typing import Optional, Dict, List
from dataclasses import dataclass
from uuid import uuid4

@dataclass
class User:
    id: str
    username: str
    email: str
    password_hash: str
    two_factor_enabled: bool = False
    two_factor_secret: Optional[str] = None
    last_login: Optional[str] = None
    login_attempts: int = 0
    account_locked: bool = False
    lock_until: Optional[str] = None
    created_at: str = datetime.now().isoformat()
    last_password_change: str = datetime.now().isoformat()
    require_password_change: bool = False

@dataclass
class SocialAccount:
    id: str
    user_id: str
    platform: str
    username: str
    encrypted_password: str
    heir_id: str
    last_check: str
    last_activity: str
    status: str = 'active'
    inactivity_threshold: int = 90
    notification_sent: bool = False
    notification_count: int = 0
    created_at: str = datetime.now().isoformat()
    last_modified: str = datetime.now().isoformat()
    access_token: Optional[str] = None
    refresh_token: Optional[str] = None
    token_expiry: Optional[str] = None

@dataclass
class Heir:
    id: str
    user_id: str
    name: str
    email: str
    relationship: str
    phone: Optional[str] = None
    verified: bool = False
    verification_token: Optional[str] = None
    verification_expiry: Optional[str] = None
    created_at: str = datetime.now().isoformat()
    last_modified: str = datetime.now().isoformat()
    notification_preference: str = 'email'

def initialize_database():
    conn = sqlite3.connect('digital_heir.db')
    cursor = conn.cursor()
    
    # Create tables with improved schemas
    cursor.executescript('''
        CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            two_factor_enabled BOOLEAN DEFAULT FALSE,
            two_factor_secret TEXT,
            last_login TEXT,
            login_attempts INTEGER DEFAULT 0,
            account_locked BOOLEAN DEFAULT FALSE,
            lock_until TEXT,
            created_at TEXT NOT NULL,
            last_password_change TEXT NOT NULL,
            require_password_change BOOLEAN DEFAULT FALSE,
            language_preference TEXT DEFAULT 'en',
            backup_email TEXT,
            recovery_codes TEXT
        );

        CREATE TABLE IF NOT EXISTS social_accounts (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            platform TEXT NOT NULL,
            username TEXT NOT NULL,
            password TEXT NOT NULL,
            heir_id TEXT NOT NULL,
            last_check TEXT NOT NULL,
            last_activity TEXT NOT NULL,
            status TEXT CHECK(status IN ('active', 'inactive', 'inherited')) DEFAULT 'active',
            inactivity_threshold INTEGER DEFAULT 90,
            notification_sent BOOLEAN DEFAULT FALSE,
            notification_count INTEGER DEFAULT 0,
            created_at TEXT NOT NULL,
            last_modified TEXT NOT NULL,
            access_token TEXT,
            refresh_token TEXT,
            token_expiry TEXT,
            backup_codes TEXT,
            metadata TEXT,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (heir_id) REFERENCES heirs(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS heirs (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            relationship TEXT NOT NULL,
            phone TEXT,
            verified BOOLEAN DEFAULT FALSE,
            verification_token TEXT,
            verification_expiry TEXT,
            created_at TEXT NOT NULL,
            last_modified TEXT NOT NULL,
            notification_preference TEXT DEFAULT 'email',
            backup_contact TEXT,
            preferences TEXT,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS audit_logs (
            id TEXT PRIMARY KEY,
            user_id TEXT,
            action TEXT NOT NULL,
            ip_address TEXT,
            user_agent TEXT,
            timestamp TEXT NOT NULL,
            details TEXT,
            severity TEXT DEFAULT 'info',
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS activity_logs (
            id TEXT PRIMARY KEY,
            account_id TEXT NOT NULL,
            check_date TEXT NOT NULL,
            was_active BOOLEAN NOT NULL,
            activity_type TEXT NOT NULL,
            details TEXT,
            metadata TEXT,
            FOREIGN KEY (account_id) REFERENCES social_accounts(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS crypto_assets (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            wallet_address TEXT NOT NULL,
            asset_type TEXT NOT NULL,
            heir_id TEXT NOT NULL,
            created_at TEXT NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (heir_id) REFERENCES heirs(id)
        );
        
        CREATE TABLE IF NOT EXISTS biometric_data (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            face_encoding BLOB,
            fingerprint_hash TEXT,
            created_at TEXT NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id)
        );
        
        CREATE TABLE IF NOT EXISTS blockchain_records (
            id TEXT PRIMARY KEY,
            transaction_hash TEXT NOT NULL,
            heir_id TEXT NOT NULL,
            assets TEXT NOT NULL,
            timestamp TEXT NOT NULL,
            FOREIGN KEY (heir_id) REFERENCES heirs(id)
        );

        CREATE TABLE IF NOT EXISTS login_attempts (
            id TEXT PRIMARY KEY,
            username TEXT NOT NULL,
            attempt_time TEXT NOT NULL,
            ip_address TEXT,
            success BOOLEAN DEFAULT FALSE,
            FOREIGN KEY (username) REFERENCES users(username)
        );

        CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
        CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
        CREATE INDEX IF NOT EXISTS idx_social_accounts_user ON social_accounts(user_id);
        CREATE INDEX IF NOT EXISTS idx_heirs_user ON heirs(user_id);
        CREATE INDEX IF NOT EXISTS idx_audit_logs_user ON audit_logs(user_id);
        CREATE INDEX IF NOT EXISTS idx_activity_logs_account ON activity_logs(account_id);
        CREATE INDEX IF NOT EXISTS idx_login_attempts_username ON login_attempts(username);
        CREATE INDEX IF NOT EXISTS idx_login_attempts_time ON login_attempts(attempt_time);
    ''')
    
    conn.commit()
    conn.close()