website_analyzer.py

"""
Website Intelligence Engine (Feature 1)
=======================================
Crawls and analyzes business websites to extract actionable intelligence.

Synergizes with:
- Feature 3 (Health Score): Website quality feeds overall health score
- Feature 9 (Lead Scoring): No/poor website = hot lead for web dev
- Connector A (Report Generator): Provides technical gaps for reports
"""

import re
import ssl
import socket
import logging
import hashlib
import time
from datetime import datetime
from typing import Dict, List, Optional, Any, Tuple
from dataclasses import dataclass, field
from urllib.parse import urlparse
from concurrent.futures import ThreadPoolExecutor, as_completed

import requests
from requests.exceptions import RequestException, Timeout, SSLError

logger = logging.getLogger(__name__)

# Technology detection patterns
TECH_PATTERNS = {
    'wordpress': [
        r'wp-content', r'wp-includes', r'/wp-json/',
        r'wordpress', r'<meta name="generator" content="WordPress'
    ],
    'shopify': [
        r'cdn\.shopify\.com', r'shopify\.com/s/', r'Shopify\.theme'
    ],
    'wix': [
        r'wix\.com', r'wixsite\.com', r'wix-code-sdk',
        r'static\.wixstatic\.com'
    ],
    'squarespace': [
        r'squarespace\.com', r'static1\.squarespace\.com',
        r'<meta name="generator" content="Squarespace'
    ],
    'webflow': [
        r'webflow\.com', r'assets\.website-files\.com'
    ],
    'godaddy': [
        r'godaddy\.com', r'secureserver\.net'
    ],
    'weebly': [
        r'weebly\.com', r'editmysite\.com'
    ],
    'bootstrap': [r'bootstrap\.min\.css', r'bootstrap\.min\.js'],
    'jquery': [r'jquery\.min\.js', r'jquery-\d+\.\d+'],
    'react': [r'react\.production\.min\.js', r'__REACT_DEVTOOLS_GLOBAL_HOOK__'],
    'angular': [r'angular\.min\.js', r'ng-app', r'ng-controller'],
    'vue': [r'vue\.min\.js', r'Vue\.use', r'v-bind', r'v-model'],
}

# Social media patterns
SOCIAL_PATTERNS = {
    'facebook': r'(?:facebook\.com|fb\.com)/[\w.-]+',
    'instagram': r'instagram\.com/[\w.-]+',
    'twitter': r'(?:twitter\.com|x\.com)/[\w.-]+',
    'linkedin': r'linkedin\.com/(?:company|in)/[\w.-]+',
    'youtube': r'youtube\.com/(?:channel|c|user)/[\w.-]+',
    'tiktok': r'tiktok\.com/@[\w.-]+',
    'yelp': r'yelp\.com/biz/[\w.-]+',
}


@dataclass
class WebsiteAnalysis:
    """Results of website analysis."""
    url: str
    analyzed_at: datetime = field(default_factory=datetime.now)

    # Accessibility
    is_reachable: bool = False
    response_time_ms: int = 0
    status_code: int = 0
    error_message: Optional[str] = None

    # Security
    has_ssl: bool = False
    ssl_valid: bool = False
    ssl_expiry: Optional[datetime] = None

    # Technology
    tech_stack: List[str] = field(default_factory=list)
    cms_detected: Optional[str] = None
    framework_detected: Optional[str] = None

    # Mobile/Performance
    mobile_friendly: bool = False
    has_viewport_meta: bool = False
    page_size_kb: float = 0.0

    # Features
    has_contact_form: bool = False
    has_phone_number: bool = False
    has_email: bool = False
    has_online_booking: bool = False
    has_live_chat: bool = False
    has_google_maps: bool = False

    # Social
    social_links: Dict[str, str] = field(default_factory=dict)

    # SEO
    has_meta_description: bool = False
    has_title: bool = False
    title_text: Optional[str] = None
    meta_description: Optional[str] = None

    # Computed scores
    website_score: int = 0  # 0-100
    opportunity_score: int = 0  # Higher = better lead opportunity

    def to_dict(self) -> Dict:
        return {
            'url': self.url,
            'analyzed_at': self.analyzed_at.isoformat(),
            'is_reachable': self.is_reachable,
            'response_time_ms': self.response_time_ms,
            'status_code': self.status_code,
            'error_message': self.error_message,
            'has_ssl': self.has_ssl,
            'ssl_valid': self.ssl_valid,
            'tech_stack': self.tech_stack,
            'cms_detected': self.cms_detected,
            'framework_detected': self.framework_detected,
            'mobile_friendly': self.mobile_friendly,
            'has_viewport_meta': self.has_viewport_meta,
            'page_size_kb': self.page_size_kb,
            'has_contact_form': self.has_contact_form,
            'has_phone_number': self.has_phone_number,
            'has_email': self.has_email,
            'has_online_booking': self.has_online_booking,
            'has_live_chat': self.has_live_chat,
            'has_google_maps': self.has_google_maps,
            'social_links': self.social_links,
            'has_meta_description': self.has_meta_description,
            'has_title': self.has_title,
            'title_text': self.title_text,
            'website_score': self.website_score,
            'opportunity_score': self.opportunity_score,
        }


class WebsiteAnalyzer:
    """
    Analyzes business websites to extract intelligence.

    Features:
    - Technology stack detection (WordPress, Shopify, etc.)
    - SSL/security analysis
    - Mobile-friendliness detection
    - Contact form detection
    - Social media link extraction
    - SEO basic analysis
    - Performance metrics
    """

    def __init__(self, db, config: Optional[Dict] = None):
        """
        Initialize website analyzer.

        Args:
            db: DatabaseManager instance
            config: Optional configuration dict
        """
        self.db = db
        self.config = config or {}

        self.timeout = self.config.get('timeout', 10)
        self.max_workers = self.config.get('max_workers', 5)
        self.user_agent = self.config.get('user_agent',
            'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36')

        self._init_analysis_table()

    def _init_analysis_table(self):
        """Create website_analysis table if not exists."""
        self.db.conn.execute("""
            CREATE TABLE IF NOT EXISTS website_analysis (
                dedup_hash TEXT PRIMARY KEY,
                url TEXT NOT NULL,
                analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_reachable INTEGER DEFAULT 0,
                response_time_ms INTEGER,
                status_code INTEGER,
                error_message TEXT,
                has_ssl INTEGER DEFAULT 0,
                ssl_valid INTEGER DEFAULT 0,
                tech_stack TEXT,
                cms_detected TEXT,
                mobile_friendly INTEGER DEFAULT 0,
                has_viewport_meta INTEGER DEFAULT 0,
                page_size_kb REAL,
                has_contact_form INTEGER DEFAULT 0,
                has_phone_number INTEGER DEFAULT 0,
                has_email INTEGER DEFAULT 0,
                has_online_booking INTEGER DEFAULT 0,
                has_live_chat INTEGER DEFAULT 0,
                has_google_maps INTEGER DEFAULT 0,
                social_links TEXT,
                has_meta_description INTEGER DEFAULT 0,
                has_title INTEGER DEFAULT 0,
                title_text TEXT,
                website_score INTEGER DEFAULT 0,
                opportunity_score INTEGER DEFAULT 0
            )
        """)

        self.db.conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_website_score
            ON website_analysis(website_score)
        """)
        self.db.conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_opportunity_score
            ON website_analysis(opportunity_score)
        """)

        self.db.conn.commit()
        logger.info("Website analysis table initialized")

    def _check_ssl(self, hostname: str) -> Tuple[bool, bool, Optional[datetime]]:
        """
        Check SSL certificate status.

        Returns:
            (has_ssl, ssl_valid, expiry_date)
        """
        try:
            context = ssl.create_default_context()
            with socket.create_connection((hostname, 443), timeout=5) as sock:
                with context.wrap_socket(sock, server_hostname=hostname) as ssock:
                    cert = ssock.getpeercert()
                    expiry_str = cert.get('notAfter')
                    if expiry_str:
                        expiry = datetime.strptime(expiry_str, '%b %d %H:%M:%S %Y %Z')
                        return True, expiry > datetime.now(), expiry
                    return True, True, None
        except ssl.SSLError:
            return True, False, None  # Has SSL but invalid
        except Exception:
            return False, False, None

    def _detect_technologies(self, html: str) -> Tuple[List[str], Optional[str], Optional[str]]:
        """
        Detect technologies used in the website.

        Returns:
            (tech_stack, cms_detected, framework_detected)
        """
        tech_stack = []
        cms_detected = None
        framework_detected = None

        # CMS detection (mutually exclusive, first match wins)
        cms_platforms = ['wordpress', 'shopify', 'wix', 'squarespace', 'webflow', 'godaddy', 'weebly']

        for tech, patterns in TECH_PATTERNS.items():
            for pattern in patterns:
                if re.search(pattern, html, re.IGNORECASE):
                    tech_stack.append(tech)
                    if tech in cms_platforms and not cms_detected:
                        cms_detected = tech
                    elif tech in ['react', 'angular', 'vue'] and not framework_detected:
                        framework_detected = tech
                    break

        return list(set(tech_stack)), cms_detected, framework_detected

    def _extract_social_links(self, html: str) -> Dict[str, str]:
        """Extract social media links from HTML."""
        social_links = {}

        for platform, pattern in SOCIAL_PATTERNS.items():
            match = re.search(pattern, html, re.IGNORECASE)
            if match:
                social_links[platform] = match.group(0)

        return social_links

    def _check_features(self, html: str) -> Dict[str, bool]:
        """Check for various website features."""
        html_lower = html.lower()

        return {
            'has_contact_form': bool(re.search(
                r'<form[^>]*(?:contact|inquiry|message|email)',
                html_lower
            ) or '<form' in html_lower and 'submit' in html_lower),

            'has_phone_number': bool(re.search(
                r'(?:\+1|tel:|\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4})',
                html
            )),

            'has_email': bool(re.search(
                r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}',
                html
            )),

            'has_online_booking': bool(re.search(
                r'book(?:ing)?|schedule|appointment|reserve|calendly|acuity',
                html_lower
            )),

            'has_live_chat': bool(re.search(
                r'livechat|intercom|drift|zendesk|tawk\.to|crisp|hubspot',
                html_lower
            )),

            'has_google_maps': bool(re.search(
                r'maps\.google\.com|google\.com/maps|maps\.googleapis\.com',
                html
            )),

            'has_viewport_meta': bool(re.search(
                r'<meta[^>]*name=["\']viewport["\']',
                html_lower
            )),

            'mobile_friendly': bool(re.search(
                r'<meta[^>]*name=["\']viewport["\'][^>]*width=device-width',
                html_lower
            )) or bool(re.search(r'@media\s*\([^)]*max-width', html_lower)),
        }

    def _extract_seo_info(self, html: str) -> Dict[str, Any]:
        """Extract SEO-related information."""
        seo = {
            'has_title': False,
            'title_text': None,
            'has_meta_description': False,
            'meta_description': None,
        }

        # Title
        title_match = re.search(r'<title[^>]*>([^<]+)</title>', html, re.IGNORECASE)
        if title_match:
            seo['has_title'] = True
            seo['title_text'] = title_match.group(1).strip()[:200]

        # Meta description
        desc_match = re.search(
            r'<meta[^>]*name=["\']description["\'][^>]*content=["\']([^"\']+)["\']',
            html, re.IGNORECASE
        )
        if desc_match:
            seo['has_meta_description'] = True
            seo['meta_description'] = desc_match.group(1).strip()[:300]

        return seo

    def _calculate_scores(self, analysis: WebsiteAnalysis) -> Tuple[int, int]:
        """
        Calculate website score and opportunity score.

        Returns:
            (website_score, opportunity_score)
        """
        website_score = 0
        opportunity_score = 0

        # Website score: How good is the website? (0-100)
        if analysis.is_reachable:
            website_score += 20

            if analysis.has_ssl and analysis.ssl_valid:
                website_score += 15
            elif analysis.has_ssl:
                website_score += 5

            if analysis.mobile_friendly:
                website_score += 15

            if analysis.response_time_ms < 3000:
                website_score += 10
            elif analysis.response_time_ms < 5000:
                website_score += 5

            if analysis.has_contact_form:
                website_score += 10

            if analysis.has_meta_description and analysis.has_title:
                website_score += 10

            if analysis.has_online_booking:
                website_score += 10

            if len(analysis.social_links) >= 2:
                website_score += 5

            if analysis.cms_detected in ['shopify', 'squarespace', 'webflow']:
                website_score += 5  # Modern platform

        # Opportunity score: How good a lead for web dev? (0-100)
        # Higher = better opportunity

        if not analysis.is_reachable:
            opportunity_score += 40  # No website = great opportunity

        else:
            # Poor website = opportunity
            if not analysis.has_ssl:
                opportunity_score += 20
            elif not analysis.ssl_valid:
                opportunity_score += 15

            if not analysis.mobile_friendly:
                opportunity_score += 25

            if analysis.response_time_ms > 5000:
                opportunity_score += 15
            elif analysis.response_time_ms > 3000:
                opportunity_score += 10

            if not analysis.has_contact_form:
                opportunity_score += 10

            if not analysis.has_online_booking:
                opportunity_score += 10

            if analysis.cms_detected in ['wix', 'godaddy', 'weebly']:
                opportunity_score += 10  # Upgrade opportunity

            if not analysis.has_meta_description:
                opportunity_score += 5

            if len(analysis.social_links) == 0:
                opportunity_score += 5

        return min(100, website_score), min(100, opportunity_score)

    def analyze_website(self, url: str) -> WebsiteAnalysis:
        """
        Analyze a single website.

        Args:
            url: Website URL to analyze

        Returns:
            WebsiteAnalysis with all extracted data
        """
        analysis = WebsiteAnalysis(url=url)

        if not url:
            analysis.error_message = "No URL provided"
            analysis.opportunity_score = 100  # No website = hot lead
            return analysis

        # Normalize URL
        if not url.startswith(('http://', 'https://')):
            url = 'https://' + url

        try:
            parsed = urlparse(url)
            hostname = parsed.hostname

            # Check SSL
            if hostname:
                has_ssl, ssl_valid, ssl_expiry = self._check_ssl(hostname)
                analysis.has_ssl = has_ssl
                analysis.ssl_valid = ssl_valid
                analysis.ssl_expiry = ssl_expiry

            # Fetch page
            start_time = time.time()
            response = requests.get(
                url,
                timeout=self.timeout,
                headers={'User-Agent': self.user_agent},
                allow_redirects=True
            )
            analysis.response_time_ms = int((time.time() - start_time) * 1000)
            analysis.status_code = response.status_code
            analysis.is_reachable = response.status_code == 200
            analysis.page_size_kb = len(response.content) / 1024

            if analysis.is_reachable:
                html = response.text

                # Detect technologies
                tech_stack, cms, framework = self._detect_technologies(html)
                analysis.tech_stack = tech_stack
                analysis.cms_detected = cms
                analysis.framework_detected = framework

                # Check features
                features = self._check_features(html)
                analysis.has_contact_form = features['has_contact_form']
                analysis.has_phone_number = features['has_phone_number']
                analysis.has_email = features['has_email']
                analysis.has_online_booking = features['has_online_booking']
                analysis.has_live_chat = features['has_live_chat']
                analysis.has_google_maps = features['has_google_maps']
                analysis.has_viewport_meta = features['has_viewport_meta']
                analysis.mobile_friendly = features['mobile_friendly']

                # Extract social links
                analysis.social_links = self._extract_social_links(html)

                # Extract SEO info
                seo = self._extract_seo_info(html)
                analysis.has_title = seo['has_title']
                analysis.title_text = seo['title_text']
                analysis.has_meta_description = seo['has_meta_description']
                analysis.meta_description = seo['meta_description']

        except Timeout:
            analysis.error_message = "Connection timeout"
            analysis.response_time_ms = self.timeout * 1000

        except SSLError as e:
            analysis.error_message = f"SSL error: {str(e)[:50]}"
            analysis.has_ssl = True
            analysis.ssl_valid = False

        except RequestException as e:
            analysis.error_message = f"Request error: {str(e)[:50]}"

        except Exception as e:
            analysis.error_message = f"Error: {str(e)[:50]}"
            logger.error(f"Error analyzing {url}: {e}")

        # Calculate scores
        analysis.website_score, analysis.opportunity_score = self._calculate_scores(analysis)

        return analysis

    def analyze_table(self, table_name: str,
                      limit: Optional[int] = None,
                      skip_analyzed: bool = True,
                      on_progress: Optional[callable] = None) -> List[WebsiteAnalysis]:
        """
        Analyze all websites in a table.

        Args:
            table_name: Table to analyze
            limit: Optional max businesses to analyze
            skip_analyzed: Skip businesses already analyzed
            on_progress: Optional callback(current, total, name) for progress updates

        Returns:
            List of WebsiteAnalysis results
        """
        # Get businesses with websites
        query = f"""
            SELECT dedup_hash, website_url, name
            FROM "{table_name}"
            WHERE website_url IS NOT NULL AND website_url != ''
        """

        if skip_analyzed:
            query += """
                AND dedup_hash NOT IN (SELECT dedup_hash FROM website_analysis)
            """

        if limit:
            query += f" LIMIT {limit}"

        cursor = self.db.conn.execute(query)
        businesses = cursor.fetchall()

        total = len(businesses)
        logger.info(f"Analyzing {total} websites from {table_name}")

        if total == 0:
            return []

        results = []
        completed = 0

        # Use thread pool for parallel analysis
        with ThreadPoolExecutor(max_workers=self.max_workers) as executor:
            futures = {}
            for dedup_hash, url, name in businesses:
                future = executor.submit(self.analyze_website, url)
                futures[future] = (dedup_hash, url, name)

            for future in as_completed(futures):
                dedup_hash, url, name = futures[future]
                completed += 1

                try:
                    analysis = future.result()
                    self._save_analysis(dedup_hash, analysis)
                    results.append(analysis)
                    logger.debug(f"Analyzed {name}: score={analysis.website_score}")
                except Exception as e:
                    logger.error(f"Failed to analyze {url}: {e}")

                # Report progress
                if on_progress:
                    on_progress(completed, total, name or url[:30])

        return results

    def _save_analysis(self, dedup_hash: str, analysis: WebsiteAnalysis):
        """Save analysis results to database."""
        import json

        try:
            self.db.conn.execute("""
                INSERT OR REPLACE INTO website_analysis
                (dedup_hash, url, analyzed_at, is_reachable, response_time_ms,
                 status_code, error_message, has_ssl, ssl_valid, tech_stack,
                 cms_detected, mobile_friendly, has_viewport_meta, page_size_kb,
                 has_contact_form, has_phone_number, has_email, has_online_booking,
                 has_live_chat, has_google_maps, social_links, has_meta_description,
                 has_title, title_text, website_score, opportunity_score)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                dedup_hash,
                analysis.url,
                analysis.analyzed_at.isoformat(),
                1 if analysis.is_reachable else 0,
                analysis.response_time_ms,
                analysis.status_code,
                analysis.error_message,
                1 if analysis.has_ssl else 0,
                1 if analysis.ssl_valid else 0,
                json.dumps(analysis.tech_stack),
                analysis.cms_detected,
                1 if analysis.mobile_friendly else 0,
                1 if analysis.has_viewport_meta else 0,
                analysis.page_size_kb,
                1 if analysis.has_contact_form else 0,
                1 if analysis.has_phone_number else 0,
                1 if analysis.has_email else 0,
                1 if analysis.has_online_booking else 0,
                1 if analysis.has_live_chat else 0,
                1 if analysis.has_google_maps else 0,
                json.dumps(analysis.social_links),
                1 if analysis.has_meta_description else 0,
                1 if analysis.has_title else 0,
                analysis.title_text,
                analysis.website_score,
                analysis.opportunity_score
            ))
            self.db.conn.commit()

        except Exception as e:
            logger.error(f"Failed to save analysis: {e}")

    def get_analysis(self, dedup_hash: str) -> Optional[Dict]:
        """Get saved analysis for a business."""
        cursor = self.db.conn.execute("""
            SELECT * FROM website_analysis WHERE dedup_hash = ?
        """, (dedup_hash,))

        row = cursor.fetchone()
        if row:
            columns = [desc[0] for desc in cursor.description]
            return dict(zip(columns, row))
        return None

    def get_opportunities(self, min_score: int = 50,
                          limit: int = 100) -> List[Dict]:
        """
        Get businesses with high opportunity scores.

        Args:
            min_score: Minimum opportunity score
            limit: Max results

        Returns:
            List of businesses with high opportunity scores
        """
        cursor = self.db.conn.execute("""
            SELECT dedup_hash, url, website_score, opportunity_score,
                   has_ssl, mobile_friendly, cms_detected, error_message
            FROM website_analysis
            WHERE opportunity_score >= ?
            ORDER BY opportunity_score DESC
            LIMIT ?
        """, (min_score, limit))

        return [
            {
                'dedup_hash': row[0],
                'url': row[1],
                'website_score': row[2],
                'opportunity_score': row[3],
                'has_ssl': bool(row[4]),
                'mobile_friendly': bool(row[5]),
                'cms_detected': row[6],
                'error_message': row[7],
            }
            for row in cursor.fetchall()
        ]

    def get_analysis_stats(self) -> Dict[str, Any]:
        """Get statistics about analyzed websites."""
        cursor = self.db.conn.execute("""
            SELECT
                COUNT(*) as total_analyzed,
                SUM(CASE WHEN is_reachable = 1 THEN 1 ELSE 0 END) as reachable,
                SUM(CASE WHEN has_ssl = 1 AND ssl_valid = 1 THEN 1 ELSE 0 END) as ssl_valid,
                SUM(CASE WHEN mobile_friendly = 1 THEN 1 ELSE 0 END) as mobile_friendly,
                AVG(website_score) as avg_website_score,
                AVG(opportunity_score) as avg_opportunity_score,
                SUM(CASE WHEN opportunity_score >= 70 THEN 1 ELSE 0 END) as hot_opportunities
            FROM website_analysis
        """)

        row = cursor.fetchone()
        return {
            'total_analyzed': row[0] or 0,
            'reachable': row[1] or 0,
            'ssl_valid': row[2] or 0,
            'mobile_friendly': row[3] or 0,
            'avg_website_score': round(row[4] or 0, 1),
            'avg_opportunity_score': round(row[5] or 0, 1),
            'hot_opportunities': row[6] or 0,
        }


def create_website_analyzer(db, config_dict: Optional[Dict] = None) -> WebsiteAnalyzer:
    """Factory function to create WebsiteAnalyzer from config."""
    return WebsiteAnalyzer(db, config_dict)