cache_manager.py

"""
Smart Caching System (Feature 15)
=================================
Intelligent cache management for efficient incremental scraping.
Reduces bandwidth usage by 60-80% through staleness detection.

Synergizes with:
- Feature 5 (Change Detection): Cache enables efficient diff comparison
- Feature 13 (Proxy Pool): Cache hits reduce proxy bandwidth usage
"""

import hashlib
import logging
from datetime import datetime, timedelta
from typing import Dict, List, Optional, Any, Set
from dataclasses import dataclass, field

logger = logging.getLogger(__name__)


@dataclass
class CacheConfig:
    """Configuration for cache behavior."""
    default_ttl_days: int = 7
    stale_threshold_days: int = 14
    category_ttl: Dict[str, int] = field(default_factory=lambda: {
        'restaurant': 3,
        'food': 3,
        'cafe': 3,
        'bar': 3,
        'pet cremation': 30,
        'funeral': 30,
        'cemetery': 30,
        'lawyer': 14,
        'attorney': 14,
        'dentist': 14,
        'doctor': 14,
        'plumber': 7,
        'electrician': 7,
        'hvac': 7,
    })
    refresh_strategy: str = 'stale_first'  # stale_first, random_sample, priority_based


@dataclass
class CacheEntry:
    """Represents a cached business entry."""
    dedup_hash: str
    table_name: str
    cached_at: datetime
    ttl_days: int
    last_verified: Optional[datetime] = None
    refresh_priority: int = 0  # Higher = more urgent to refresh
    data_snapshot: Optional[Dict] = None

    @property
    def expires_at(self) -> datetime:
        return self.cached_at + timedelta(days=self.ttl_days)

    @property
    def is_expired(self) -> bool:
        return datetime.now() > self.expires_at

    @property
    def is_stale(self) -> bool:
        """Stale = past TTL but within stale threshold (soft expiry)."""
        stale_at = self.cached_at + timedelta(days=self.ttl_days + 7)
        return datetime.now() > self.expires_at and datetime.now() <= stale_at

    @property
    def freshness_score(self) -> float:
        """0.0 = expired, 1.0 = fresh, value between indicates staleness."""
        age = (datetime.now() - self.cached_at).days
        if age <= 0:
            return 1.0
        if age >= self.ttl_days * 2:
            return 0.0
        return max(0.0, 1.0 - (age / (self.ttl_days * 2)))


class CacheManager:
    """
    Smart caching system for Google Maps scraper.

    Features:
    - TTL-based staleness detection
    - Category-specific cache durations
    - Refresh priority scoring
    - Bandwidth savings tracking
    """

    def __init__(self, db, config: Optional[CacheConfig] = None):
        """
        Initialize cache manager.

        Args:
            db: DatabaseManager instance
            config: Optional CacheConfig, uses defaults if not provided
        """
        self.db = db
        self.config = config or CacheConfig()
        self._init_cache_table()

        # Statistics
        self.stats = {
            'cache_hits': 0,
            'cache_misses': 0,
            'bandwidth_saved_kb': 0,
            'entries_refreshed': 0,
        }

    def _init_cache_table(self):
        """Create cache metadata table if not exists."""
        self.db.conn.execute("""
            CREATE TABLE IF NOT EXISTS cache_metadata (
                dedup_hash TEXT PRIMARY KEY,
                table_name TEXT NOT NULL,
                cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                ttl_days INTEGER DEFAULT 7,
                last_verified TIMESTAMP,
                refresh_priority INTEGER DEFAULT 0,
                category TEXT,
                rating REAL,
                review_count INTEGER,
                has_website INTEGER DEFAULT 0,
                data_checksum TEXT,
                UNIQUE(dedup_hash)
            )
        """)

        # Index for efficient queries
        self.db.conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_cache_table
            ON cache_metadata(table_name)
        """)
        self.db.conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_cache_priority
            ON cache_metadata(refresh_priority DESC)
        """)
        self.db.conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_cache_freshness
            ON cache_metadata(cached_at)
        """)

        self.db.conn.commit()
        logger.info("Cache metadata table initialized")

    def get_ttl_for_category(self, category: Optional[str]) -> int:
        """Get appropriate TTL based on business category."""
        if not category:
            return self.config.default_ttl_days

        category_lower = category.lower()
        for key, ttl in self.config.category_ttl.items():
            if key in category_lower:
                return ttl

        return self.config.default_ttl_days

    def _compute_checksum(self, data: Dict) -> str:
        """Compute checksum of business data for change detection."""
        # Include key fields that indicate meaningful changes
        check_fields = ['name', 'phone_number', 'rating', 'review_count',
                        'website_url', 'address_text', 'open_status']
        check_data = {k: data.get(k) for k in check_fields}
        return hashlib.md5(str(check_data).encode()).hexdigest()

    def add_to_cache(self, dedup_hash: str, table_name: str,
                     business_data: Dict) -> bool:
        """
        Add or update a business in the cache.

        Args:
            dedup_hash: Unique business identifier
            table_name: Source table name
            business_data: Full business data dict

        Returns:
            True if added/updated successfully
        """
        try:
            category = business_data.get('category')
            ttl = self.get_ttl_for_category(category)
            checksum = self._compute_checksum(business_data)

            self.db.conn.execute("""
                INSERT OR REPLACE INTO cache_metadata
                (dedup_hash, table_name, cached_at, ttl_days, category,
                 rating, review_count, has_website, data_checksum, refresh_priority)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0)
            """, (
                dedup_hash,
                table_name,
                datetime.now().isoformat(),
                ttl,
                category,
                business_data.get('rating'),
                business_data.get('review_count'),
                1 if business_data.get('website_url') else 0,
                checksum
            ))
            self.db.conn.commit()
            return True

        except Exception as e:
            logger.error(f"Failed to add to cache: {e}")
            return False

    def bulk_add_to_cache(self, table_name: str,
                          businesses: List[Dict]) -> int:
        """
        Bulk add businesses to cache.

        Args:
            table_name: Source table name
            businesses: List of business data dicts

        Returns:
            Number of entries added
        """
        added = 0
        for biz in businesses:
            dedup_hash = biz.get('dedup_hash')
            if dedup_hash and self.add_to_cache(dedup_hash, table_name, biz):
                added += 1
        return added

    def is_cached(self, dedup_hash: str) -> bool:
        """Check if a business is in cache (regardless of freshness)."""
        cursor = self.db.conn.execute(
            "SELECT 1 FROM cache_metadata WHERE dedup_hash = ?",
            (dedup_hash,)
        )
        return cursor.fetchone() is not None

    def is_fresh(self, dedup_hash: str) -> bool:
        """
        Check if a cached business is still fresh (within TTL).

        Args:
            dedup_hash: Business identifier

        Returns:
            True if cached and fresh, False otherwise
        """
        cursor = self.db.conn.execute("""
            SELECT cached_at, ttl_days
            FROM cache_metadata
            WHERE dedup_hash = ?
        """, (dedup_hash,))

        row = cursor.fetchone()
        if not row:
            self.stats['cache_misses'] += 1
            return False

        cached_at = datetime.fromisoformat(row[0])
        ttl_days = row[1]
        expires_at = cached_at + timedelta(days=ttl_days)

        if datetime.now() <= expires_at:
            self.stats['cache_hits'] += 1
            # Estimate ~5KB saved per cache hit
            self.stats['bandwidth_saved_kb'] += 5
            return True

        self.stats['cache_misses'] += 1
        return False

    def get_fresh_hashes(self, table_name: str) -> Set[str]:
        """
        Get all fresh (non-expired) hashes for a table.

        Args:
            table_name: Table to check

        Returns:
            Set of dedup_hashes that are still fresh
        """
        cursor = self.db.conn.execute("""
            SELECT dedup_hash, cached_at, ttl_days
            FROM cache_metadata
            WHERE table_name = ?
        """, (table_name,))

        fresh_hashes = set()
        now = datetime.now()

        for row in cursor.fetchall():
            cached_at = datetime.fromisoformat(row[1])
            ttl_days = row[2]
            if now <= cached_at + timedelta(days=ttl_days):
                fresh_hashes.add(row[0])

        return fresh_hashes

    def get_stale_entries(self, table_name: Optional[str] = None,
                          limit: int = 100) -> List[Dict]:
        """
        Get entries that need refreshing (expired but within stale threshold).

        Args:
            table_name: Optional filter by table
            limit: Max entries to return

        Returns:
            List of stale entries sorted by priority
        """
        query = """
            SELECT dedup_hash, table_name, cached_at, ttl_days,
                   refresh_priority, category, rating, review_count
            FROM cache_metadata
            WHERE datetime(cached_at, '+' || ttl_days || ' days') < datetime('now')
        """
        params = []

        if table_name:
            query += " AND table_name = ?"
            params.append(table_name)

        query += " ORDER BY refresh_priority DESC, cached_at ASC LIMIT ?"
        params.append(limit)

        cursor = self.db.conn.execute(query, params)

        return [
            {
                'dedup_hash': row[0],
                'table_name': row[1],
                'cached_at': row[2],
                'ttl_days': row[3],
                'refresh_priority': row[4],
                'category': row[5],
                'rating': row[6],
                'review_count': row[7],
            }
            for row in cursor.fetchall()
        ]

    def get_entries_to_refresh(self, strategy: Optional[str] = None,
                               limit: int = 100) -> List[str]:
        """
        Get list of dedup_hashes that should be refreshed.

        Args:
            strategy: Override default refresh strategy
            limit: Max entries to return

        Returns:
            List of dedup_hashes to refresh
        """
        strategy = strategy or self.config.refresh_strategy

        if strategy == 'stale_first':
            # Prioritize oldest expired entries
            entries = self.get_stale_entries(limit=limit)
            return [e['dedup_hash'] for e in entries]

        elif strategy == 'priority_based':
            # Prioritize high-priority entries
            cursor = self.db.conn.execute("""
                SELECT dedup_hash FROM cache_metadata
                WHERE datetime(cached_at, '+' || ttl_days || ' days') < datetime('now')
                ORDER BY refresh_priority DESC
                LIMIT ?
            """, (limit,))
            return [row[0] for row in cursor.fetchall()]

        elif strategy == 'random_sample':
            # Random selection from expired
            cursor = self.db.conn.execute("""
                SELECT dedup_hash FROM cache_metadata
                WHERE datetime(cached_at, '+' || ttl_days || ' days') < datetime('now')
                ORDER BY RANDOM()
                LIMIT ?
            """, (limit,))
            return [row[0] for row in cursor.fetchall()]

        else:
            logger.warning(f"Unknown strategy: {strategy}, using stale_first")
            return self.get_entries_to_refresh('stale_first', limit)

    def update_refresh_priority(self, dedup_hash: str, priority: int):
        """Update refresh priority for an entry."""
        self.db.conn.execute("""
            UPDATE cache_metadata
            SET refresh_priority = ?
            WHERE dedup_hash = ?
        """, (priority, dedup_hash))
        self.db.conn.commit()

    def mark_refreshed(self, dedup_hash: str, new_data: Optional[Dict] = None):
        """
        Mark an entry as refreshed (reset TTL).

        Args:
            dedup_hash: Business identifier
            new_data: Optional new data to update checksum
        """
        updates = ["cached_at = ?", "refresh_priority = 0"]
        params = [datetime.now().isoformat()]

        if new_data:
            checksum = self._compute_checksum(new_data)
            updates.append("data_checksum = ?")
            params.append(checksum)

            if new_data.get('rating'):
                updates.append("rating = ?")
                params.append(new_data['rating'])
            if new_data.get('review_count'):
                updates.append("review_count = ?")
                params.append(new_data['review_count'])

        params.append(dedup_hash)

        self.db.conn.execute(f"""
            UPDATE cache_metadata
            SET {', '.join(updates)}
            WHERE dedup_hash = ?
        """, params)
        self.db.conn.commit()
        self.stats['entries_refreshed'] += 1

    def detect_changes(self, dedup_hash: str, new_data: Dict) -> Dict[str, Any]:
        """
        Detect what changed between cached and new data.

        Args:
            dedup_hash: Business identifier
            new_data: New scraped data

        Returns:
            Dict of changes: {field: (old_value, new_value)}
        """
        cursor = self.db.conn.execute("""
            SELECT rating, review_count, has_website, data_checksum
            FROM cache_metadata
            WHERE dedup_hash = ?
        """, (dedup_hash,))

        row = cursor.fetchone()
        if not row:
            return {'status': 'new'}

        old_rating, old_reviews, old_has_website, old_checksum = row
        new_checksum = self._compute_checksum(new_data)

        if old_checksum == new_checksum:
            return {'status': 'unchanged'}

        changes = {'status': 'changed'}

        new_rating = new_data.get('rating')
        if old_rating != new_rating and new_rating is not None:
            changes['rating'] = (old_rating, new_rating)

        new_reviews = new_data.get('review_count')
        if old_reviews != new_reviews and new_reviews is not None:
            changes['review_count'] = (old_reviews, new_reviews)

        new_has_website = 1 if new_data.get('website_url') else 0
        if old_has_website != new_has_website:
            changes['has_website'] = (bool(old_has_website), bool(new_has_website))

        return changes

    def get_cache_stats(self) -> Dict[str, Any]:
        """Get cache statistics."""
        stale_threshold = self.config.stale_threshold_days

        cursor = self.db.conn.execute("""
            SELECT
                COUNT(*) as total_entries,
                SUM(CASE WHEN datetime(cached_at, '+' || ttl_days || ' days') > datetime('now')
                    THEN 1 ELSE 0 END) as fresh_entries,
                SUM(CASE WHEN datetime(cached_at, '+' || ttl_days || ' days') <= datetime('now')
                    AND datetime(cached_at, '+' || (ttl_days + ?) || ' days') > datetime('now')
                    THEN 1 ELSE 0 END) as stale_entries,
                SUM(CASE WHEN datetime(cached_at, '+' || (ttl_days + ?) || ' days') <= datetime('now')
                    THEN 1 ELSE 0 END) as expired_entries,
                COUNT(DISTINCT table_name) as tables_cached
            FROM cache_metadata
        """, (stale_threshold, stale_threshold))

        row = cursor.fetchone()

        hit_rate = 0
        total_lookups = self.stats['cache_hits'] + self.stats['cache_misses']
        if total_lookups > 0:
            hit_rate = self.stats['cache_hits'] / total_lookups * 100

        # Get per-table stats
        tables_stats = {}
        cursor2 = self.db.conn.execute("""
            SELECT
                table_name,
                COUNT(*) as total,
                SUM(CASE WHEN datetime(cached_at, '+' || ttl_days || ' days') > datetime('now')
                    THEN 1 ELSE 0 END) as fresh,
                SUM(CASE WHEN datetime(cached_at, '+' || ttl_days || ' days') <= datetime('now')
                    THEN 1 ELSE 0 END) as stale
            FROM cache_metadata
            GROUP BY table_name
        """)
        for tbl_row in cursor2.fetchall():
            tables_stats[tbl_row[0]] = {
                'total': tbl_row[1],
                'fresh': tbl_row[2] or 0,
                'stale': tbl_row[3] or 0,
            }

        return {
            'total_entries': row[0] or 0,
            'fresh': row[1] or 0,
            'stale': row[2] or 0,
            'expired': row[3] or 0,
            'tables_cached': row[4] or 0,
            'tables': tables_stats,
            'cache_hits': self.stats['cache_hits'],
            'cache_misses': self.stats['cache_misses'],
            'hit_rate_percent': round(hit_rate, 1),
            'bandwidth_saved_kb': self.stats['bandwidth_saved_kb'],
            'entries_refreshed': self.stats['entries_refreshed'],
        }

    def sync_table_to_cache(self, table_name: str) -> Dict[str, int]:
        """
        Sync all entries from a table into the cache.

        Args:
            table_name: Table to sync

        Returns:
            Dict with 'added', 'updated', 'skipped' counts
        """
        stats = {'added': 0, 'updated': 0, 'skipped': 0}

        # Get all entries from table
        try:
            cursor = self.db.conn.execute(f"""
                SELECT dedup_hash, name, category, rating, review_count,
                       website_url, phone_number, address_text
                FROM "{table_name}"
                WHERE dedup_hash IS NOT NULL
            """)
        except Exception as e:
            logger.error(f"Error reading table {table_name}: {e}")
            return stats

        for row in cursor.fetchall():
            dedup_hash = row[0]
            if not dedup_hash:
                continue

            business_data = {
                'name': row[1],
                'category': row[2],
                'rating': row[3],
                'review_count': row[4],
                'website_url': row[5],
                'phone_number': row[6],
                'address_text': row[7],
            }

            # Check if already cached
            existing = self.db.conn.execute(
                "SELECT 1 FROM cache_metadata WHERE dedup_hash = ?",
                (dedup_hash,)
            ).fetchone()

            if existing:
                # Update existing entry
                self.mark_refreshed(dedup_hash, business_data)
                stats['updated'] += 1
            else:
                # Add new entry
                if self.add_to_cache(dedup_hash, table_name, business_data):
                    stats['added'] += 1
                else:
                    stats['skipped'] += 1

        return stats

    def clear_cache(self, table_name: Optional[str] = None):
        """
        Clear cache entries.

        Args:
            table_name: If provided, only clear entries for this table
        """
        if table_name:
            self.db.conn.execute(
                "DELETE FROM cache_metadata WHERE table_name = ?",
                (table_name,)
            )
        else:
            self.db.conn.execute("DELETE FROM cache_metadata")

        self.db.conn.commit()
        logger.info(f"Cache cleared: {table_name or 'all'}")

    def prune_expired(self, days_past_expiry: int = 30) -> int:
        """
        Remove entries that have been expired for too long.

        Args:
            days_past_expiry: Remove entries expired longer than this

        Returns:
            Number of entries removed
        """
        cursor = self.db.conn.execute("""
            DELETE FROM cache_metadata
            WHERE datetime(cached_at, '+' || (ttl_days + ?) || ' days') < datetime('now')
        """, (days_past_expiry,))

        deleted = cursor.rowcount
        self.db.conn.commit()

        if deleted > 0:
            logger.info(f"Pruned {deleted} expired cache entries")

        return deleted


def create_cache_manager(db, config_dict: Optional[Dict] = None) -> CacheManager:
    """
    Factory function to create CacheManager from config dict.

    Args:
        db: DatabaseManager instance
        config_dict: Optional config from YAML

    Returns:
        Configured CacheManager
    """
    if config_dict:
        config = CacheConfig(
            default_ttl_days=config_dict.get('default_ttl_days', 7),
            stale_threshold_days=config_dict.get('stale_threshold_days', 14),
            category_ttl=config_dict.get('category_ttl', {}),
            refresh_strategy=config_dict.get('refresh_strategy', 'stale_first')
        )
    else:
        config = CacheConfig()

    return CacheManager(db, config)