# dynamic_data_service.py
# NEW FILE - Add this alongside your main.py
# This will gradually replace static data with dynamic database queries

import asyncio
import json
import os
from datetime import datetime, timedelta
from typing import Dict, Any, List, Optional
from loguru import logger

class DynamicDataService:
    """
    Service to get dynamic data from database instead of static hardcoded data
    This will gradually replace static data in BusinessKnowledgeGraphManager
    """
    
    def __init__(self, postgres_pool=None):
        self.postgres_pool = postgres_pool
        
        # Fallback to static data if database is not available
        self.use_fallback = not bool(postgres_pool)
        
        # Cache for performance (5 minutes cache)
        self.cache = {}
        self.cache_ttl = 300  # 5 minutes
        
        if self.use_fallback:
            logger.warning("DynamicDataService: Using fallback mode (static data)")
        else:
            logger.info("DynamicDataService: Using dynamic database mode")
    
    async def get_industry_requirements(self, industry: str) -> Dict:
        """
        Get industry requirements from database instead of hardcoded data
        Returns same structure as the original static data
        """
        try:
            cache_key = f"industry_req_{industry}"
            
            # Check cache first
            if self._is_cache_valid(cache_key):
                return self.cache[cache_key]['data']
            
            if self.postgres_pool:
                # Get dynamic data from database
                requirements = await self._fetch_industry_requirements_from_db(industry)
            else:
                # Fallback to static data
                requirements = self._get_static_industry_requirements(industry)
            
            # Cache the result
            self.cache[cache_key] = {
                'data': requirements,
                'timestamp': datetime.utcnow().timestamp()
            }
            
            return requirements
            
        except Exception as e:
            logger.error(f"Error getting industry requirements for {industry}: {e}")
            return self._get_static_industry_requirements(industry)
    
    async def get_business_model_patterns(self, business_model: str) -> Dict:
        """
        Get business model patterns from database instead of hardcoded data
        Returns same structure as the original static data
        """
        try:
            cache_key = f"business_patterns_{business_model}"
            
            # Check cache first
            if self._is_cache_valid(cache_key):
                return self.cache[cache_key]['data']
            
            if self.postgres_pool:
                # Get dynamic data from database
                patterns = await self._fetch_business_patterns_from_db(business_model)
            else:
                # Fallback to static data
                patterns = self._get_static_business_patterns(business_model)
            
            # Cache the result
            self.cache[cache_key] = {
                'data': patterns,
                'timestamp': datetime.utcnow().timestamp()
            }
            
            return patterns
            
        except Exception as e:
            logger.error(f"Error getting business patterns for {business_model}: {e}")
            return self._get_static_business_patterns(business_model)
    
    async def get_market_intelligence(self, industry: str, business_model: str = None) -> Dict:
        """
        Get latest market intelligence from database (populated by n8n workflows)
        """
        try:
            if not self.postgres_pool:
                return {}
            
            cache_key = f"market_intel_{industry}_{business_model}"
            
            # Check cache first
            if self._is_cache_valid(cache_key):
                return self.cache[cache_key]['data']
            
            # Get fresh market intelligence from database
            intelligence = await self._fetch_market_intelligence_from_db(industry, business_model)
            
            # Cache the result
            self.cache[cache_key] = {
                'data': intelligence,
                'timestamp': datetime.utcnow().timestamp()
            }
            
            return intelligence
            
        except Exception as e:
            logger.error(f"Error getting market intelligence: {e}")
            return {}
    
    async def _fetch_industry_requirements_from_db(self, industry: str) -> Dict:
        """Fetch industry requirements from database"""
        try:
            async with self.postgres_pool.acquire() as conn:
                rows = await conn.fetch("""
                    SELECT requirement_type, requirement_value, confidence_score, last_updated
                    FROM dynamic_industry_requirements 
                    WHERE industry = $1 AND is_active = true
                    ORDER BY confidence_score DESC, last_updated DESC
                """, industry)
                
                # Group by requirement type
                requirements = {
                    'mandatory_compliance': [],
                    'business_risks': [],
                    'market_characteristics': [],
                    'scaling_challenges': []
                }
                
                for row in rows:
                    req_type = row['requirement_type']
                    req_value = row['requirement_value']
                    
                    if req_type in requirements:
                        requirements[req_type].append(req_value)
                    else:
                        # Handle new requirement types that weren't in static data
                        requirements[req_type] = [req_value]
                
                # Add metadata
                requirements['_metadata'] = {
                    'source': 'dynamic_database',
                    'last_updated': datetime.utcnow().isoformat(),
                    'data_count': len(rows)
                }
                
                return requirements
                
        except Exception as e:
            logger.error(f"Database query failed for industry requirements: {e}")
            return self._get_static_industry_requirements(industry)
    
    async def _fetch_business_patterns_from_db(self, business_model: str) -> Dict:
        """Fetch business patterns from database"""
        try:
            async with self.postgres_pool.acquire() as conn:
                rows = await conn.fetch("""
                    SELECT pattern_type, pattern_value, confidence_score, last_updated
                    FROM dynamic_business_patterns 
                    WHERE business_model = $1 AND is_active = true
                    ORDER BY confidence_score DESC, last_updated DESC
                """, business_model)
                
                # Group by pattern type
                patterns = {
                    'revenue_characteristics': [],
                    'scaling_patterns': [],
                    'compliance_needs': []
                }
                
                for row in rows:
                    pattern_type = row['pattern_type']
                    pattern_value = row['pattern_value']
                    
                    if pattern_type in patterns:
                        patterns[pattern_type].append(pattern_value)
                    else:
                        # Handle new pattern types
                        patterns[pattern_type] = [pattern_value]
                
                # Add metadata
                patterns['_metadata'] = {
                    'source': 'dynamic_database',
                    'last_updated': datetime.utcnow().isoformat(),
                    'data_count': len(rows)
                }
                
                return patterns
                
        except Exception as e:
            logger.error(f"Database query failed for business patterns: {e}")
            return self._get_static_business_patterns(business_model)
    
    async def _fetch_market_intelligence_from_db(self, industry: str, business_model: str = None) -> Dict:
        """Fetch market intelligence from database (populated by n8n)"""
        try:
            async with self.postgres_pool.acquire() as conn:
                if business_model:
                    rows = await conn.fetch("""
                        SELECT intelligence_type, intelligence_data, confidence_score, last_updated, data_source
                        FROM dynamic_market_intelligence 
                        WHERE industry = $1 AND business_model = $2 AND is_active = true
                        AND last_updated > $3
                        ORDER BY confidence_score DESC, last_updated DESC
                    """, industry, business_model, datetime.utcnow() - timedelta(hours=24))
                else:
                    rows = await conn.fetch("""
                        SELECT intelligence_type, intelligence_data, confidence_score, last_updated, data_source
                        FROM dynamic_market_intelligence 
                        WHERE industry = $1 AND is_active = true
                        AND last_updated > $2
                        ORDER BY confidence_score DESC, last_updated DESC
                    """, industry, datetime.utcnow() - timedelta(hours=24))
                
                intelligence = {}
                
                for row in rows:
                    intel_type = row['intelligence_type']
                    intel_data = row['intelligence_data']
                    
                    # Parse JSON data if it's a string
                    if isinstance(intel_data, str):
                        intel_data = json.loads(intel_data)
                    
                    intelligence[intel_type] = {
                        'data': intel_data,
                        'confidence': row['confidence_score'],
                        'last_updated': row['last_updated'].isoformat(),
                        'source': row['data_source']
                    }
                
                return intelligence
                
        except Exception as e:
            logger.error(f"Database query failed for market intelligence: {e}")
            return {}
    
    def _get_static_industry_requirements(self, industry: str) -> Dict:
        """Fallback to original static industry requirements"""
        static_requirements = {
            'fintech': {
                'mandatory_compliance': ['pci_dss', 'kyc', 'aml', 'sox'],
                'business_risks': ['regulatory_changes', 'security_breaches', 'compliance_violations'],
                'market_characteristics': ['high_regulation', 'trust_critical', 'security_first'],
                'scaling_challenges': ['compliance_scaling', 'regulatory_approvals', 'trust_building']
            },
            'healthcare': {
                'mandatory_compliance': ['hipaa', 'fda', 'hl7_fhir', 'hitech'],
                'business_risks': ['patient_data_breaches', 'regulatory_violations', 'liability_issues'],
                'market_characteristics': ['highly_regulated', 'safety_critical', 'interoperability_important'],
                'scaling_challenges': ['compliance_complexity', 'integration_requirements', 'certification_processes']
            },
            'ecommerce': {
                'mandatory_compliance': ['pci_dss', 'consumer_protection', 'tax_regulations'],
                'business_risks': ['fraud', 'chargebacks', 'inventory_management', 'customer_acquisition_costs'],
                'market_characteristics': ['competitive', 'margin_sensitive', 'customer_experience_critical'],
                'scaling_challenges': ['inventory_scaling', 'logistics_complexity', 'customer_service_scaling']
            }
        }
        
        result = static_requirements.get(industry, {
            'mandatory_compliance': [],
            'business_risks': [],
            'market_characteristics': [],
            'scaling_challenges': []
        })
        
        result['_metadata'] = {
            'source': 'static_fallback',
            'last_updated': datetime.utcnow().isoformat()
        }
        
        return result
    
    def _get_static_business_patterns(self, business_model: str) -> Dict:
        """Fallback to original static business patterns"""
        static_patterns = {
            'subscription_saas': {
                'revenue_characteristics': ['recurring_revenue', 'churn_management', 'expansion_revenue'],
                'scaling_patterns': ['user_acquisition', 'feature_expansion', 'market_penetration'],
                'compliance_needs': ['data_protection', 'service_agreements', 'billing_compliance']
            },
            'marketplace': {
                'revenue_characteristics': ['commission_based', 'transaction_volume', 'network_effects'],
                'scaling_patterns': ['two_sided_growth', 'trust_building', 'supply_demand_balance'],
                'compliance_needs': ['transaction_regulations', 'vendor_compliance', 'consumer_protection']
            },
            'enterprise_software': {
                'revenue_characteristics': ['license_based', 'implementation_services', 'maintenance_contracts'],
                'scaling_patterns': ['client_expansion', 'feature_depth', 'industry_specialization'],
                'compliance_needs': ['enterprise_security', 'audit_requirements', 'integration_standards']
            }
        }
        
        result = static_patterns.get(business_model, {
            'revenue_characteristics': [],
            'scaling_patterns': [],
            'compliance_needs': []
        })
        
        result['_metadata'] = {
            'source': 'static_fallback',
            'last_updated': datetime.utcnow().isoformat()
        }
        
        return result
    
    def _is_cache_valid(self, cache_key: str) -> bool:
        """Check if cache is still valid"""
        if cache_key not in self.cache:
            return False
        
        cache_time = self.cache[cache_key]['timestamp']
        return (datetime.utcnow().timestamp() - cache_time) < self.cache_ttl
    
    async def log_n8n_execution(self, workflow_name: str, status: str, records_processed: int = 0, 
                               error_message: str = None, data_summary: Dict = None):
        """Log n8n workflow execution for monitoring"""
        try:
            if self.postgres_pool:
                async with self.postgres_pool.acquire() as conn:
                    await conn.execute("""
                        INSERT INTO n8n_data_collection_log 
                        (workflow_name, execution_status, records_processed, error_message, data_summary)
                        VALUES ($1, $2, $3, $4, $5)
                    """, workflow_name, status, records_processed, error_message, 
                    json.dumps(data_summary) if data_summary else None)
                    
        except Exception as e:
            logger.error(f"Failed to log n8n execution: {e}")
    
    async def get_data_freshness_report(self) -> Dict:
        """Get report on how fresh our dynamic data is"""
        try:
            if not self.postgres_pool:
                return {'status': 'fallback_mode', 'message': 'Using static data only'}
            
            async with self.postgres_pool.acquire() as conn:
                # Check industry requirements freshness
                industry_freshness = await conn.fetchrow("""
                    SELECT COUNT(*) as total_records, 
                           MAX(last_updated) as latest_update,
                           MIN(last_updated) as oldest_update
                    FROM dynamic_industry_requirements 
                    WHERE is_active = true
                """)
                
                # Check business patterns freshness
                patterns_freshness = await conn.fetchrow("""
                    SELECT COUNT(*) as total_records, 
                           MAX(last_updated) as latest_update,
                           MIN(last_updated) as oldest_update
                    FROM dynamic_business_patterns 
                    WHERE is_active = true
                """)
                
                # Check market intelligence freshness
                intel_freshness = await conn.fetchrow("""
                    SELECT COUNT(*) as total_records, 
                           MAX(last_updated) as latest_update,
                           MIN(last_updated) as oldest_update
                    FROM dynamic_market_intelligence 
                    WHERE is_active = true
                """)
                
                # Check n8n execution status
                n8n_status = await conn.fetchrow("""
                    SELECT COUNT(*) as total_executions,
                           SUM(CASE WHEN execution_status = 'success' THEN 1 ELSE 0 END) as successful_executions,
                           MAX(execution_time) as last_execution
                    FROM n8n_data_collection_log
                    WHERE execution_time > $1
                """, datetime.utcnow() - timedelta(hours=24))
                
                return {
                    'status': 'dynamic_mode',
                    'industry_requirements': {
                        'total_records': industry_freshness['total_records'],
                        'latest_update': industry_freshness['latest_update'].isoformat() if industry_freshness['latest_update'] else None,
                        'oldest_update': industry_freshness['oldest_update'].isoformat() if industry_freshness['oldest_update'] else None
                    },
                    'business_patterns': {
                        'total_records': patterns_freshness['total_records'],
                        'latest_update': patterns_freshness['latest_update'].isoformat() if patterns_freshness['latest_update'] else None,
                        'oldest_update': patterns_freshness['oldest_update'].isoformat() if patterns_freshness['oldest_update'] else None
                    },
                    'market_intelligence': {
                        'total_records': intel_freshness['total_records'],
                        'latest_update': intel_freshness['latest_update'].isoformat() if intel_freshness['latest_update'] else None,
                        'oldest_update': intel_freshness['oldest_update'].isoformat() if intel_freshness['oldest_update'] else None
                    },
                    'n8n_workflows': {
                        'total_executions_24h': n8n_status['total_executions'],
                        'successful_executions_24h': n8n_status['successful_executions'],
                        'last_execution': n8n_status['last_execution'].isoformat() if n8n_status['last_execution'] else None,
                        'success_rate': (n8n_status['successful_executions'] / n8n_status['total_executions'] * 100) if n8n_status['total_executions'] > 0 else 0
                    },
                    'generated_at': datetime.utcnow().isoformat()
                }
                
        except Exception as e:
            logger.error(f"Failed to generate data freshness report: {e}")
            return {'status': 'error', 'message': str(e)}