nordabiz/gbp_audit_service.py

"""
GBP Audit Service for Norda Biznes Partner
=======================================

Google Business Profile completeness audit service with:
- Field-by-field completeness checking
- Weighted scoring algorithm
- AI-powered recommendations (via Gemini)
- Historical tracking

Inspired by Localo.com audit features.

Author: Maciej Pienczyn, InPi sp. z o.o.
Created: 2026-01-08
"""

import json
import logging
from dataclasses import dataclass, field
from datetime import datetime
from decimal import Decimal
from typing import Dict, List, Optional, Any

from sqlalchemy.orm import Session

from database import Company, GBPAudit, GBPReview, CompanyWebsiteAnalysis, SessionLocal, OAuthToken
from utils.data_quality import update_company_data_quality
import gemini_service

try:
    from google_places_service import GooglePlacesService
except ImportError:
    GooglePlacesService = None

# Configure logging
logger = logging.getLogger(__name__)


# Field weights for completeness scoring (total = 100)
FIELD_WEIGHTS = {
    'name': 10,           # Business name - essential
    'address': 10,        # Full address - essential for local SEO
    'phone': 8,           # Contact phone - important
    'website': 8,         # Business website - important
    'hours': 8,           # Opening hours - important for customers
    'categories': 10,     # Business categories - essential for discovery
    'photos': 15,         # Photos - high impact on engagement
    'description': 12,    # Business description - important for SEO
    'services': 10,       # Services list - important for discovery
    'reviews': 9,         # Review presence and rating - trust factor
}

# Photo requirements for optimal GBP profile
PHOTO_REQUIREMENTS = {
    'minimum': 3,         # Minimum photos for basic completeness
    'recommended': 10,    # Recommended for good profile
    'optimal': 25,        # Optimal for excellent profile
}

# Review thresholds
REVIEW_THRESHOLDS = {
    'minimum': 1,         # At least 1 review
    'good': 5,            # Good number of reviews
    'excellent': 20,      # Excellent review count
}

# Google Place Types to Polish translations
# Source: https://developers.google.com/maps/documentation/places/web-service/supported_types
GOOGLE_TYPES_PL = {
    # Automotive
    'car_dealer': 'Salon samochodowy',
    'car_rental': 'Wynajem samochodów',
    'car_repair': 'Warsztat samochodowy',
    'car_wash': 'Myjnia samochodowa',
    'electric_vehicle_charging_station': 'Stacja ładowania EV',
    'gas_station': 'Stacja paliw',
    'parking': 'Parking',
    'rest_stop': 'Miejsce odpoczynku',

    # Business
    'farm': 'Gospodarstwo rolne',

    # Culture
    'art_gallery': 'Galeria sztuki',
    'museum': 'Muzeum',
    'performing_arts_theater': 'Teatr',

    # Education
    'library': 'Biblioteka',
    'preschool': 'Przedszkole',
    'primary_school': 'Szkoła podstawowa',
    'school': 'Szkoła',
    'secondary_school': 'Szkoła średnia',
    'university': 'Uniwersytet',

    # Entertainment and Recreation
    'amusement_center': 'Centrum rozrywki',
    'amusement_park': 'Park rozrywki',
    'aquarium': 'Akwarium',
    'banquet_hall': 'Sala bankietowa',
    'bowling_alley': 'Kręgielnia',
    'casino': 'Kasyno',
    'community_center': 'Centrum społeczne',
    'convention_center': 'Centrum konferencyjne',
    'cultural_center': 'Centrum kultury',
    'dog_park': 'Park dla psów',
    'event_venue': 'Miejsce eventowe',
    'hiking_area': 'Szlak turystyczny',
    'historical_landmark': 'Zabytek',
    'marina': 'Przystań',
    'movie_rental': 'Wypożyczalnia filmów',
    'movie_theater': 'Kino',
    'national_park': 'Park narodowy',
    'night_club': 'Klub nocny',
    'park': 'Park',
    'tourist_attraction': 'Atrakcja turystyczna',
    'visitor_center': 'Centrum informacyjne',
    'wedding_venue': 'Sala weselna',
    'zoo': 'Zoo',

    # Finance
    'accounting': 'Biuro rachunkowe',
    'atm': 'Bankomat',
    'bank': 'Bank',

    # Food and Drink
    'american_restaurant': 'Restauracja amerykańska',
    'bakery': 'Piekarnia',
    'bar': 'Bar',
    'barbecue_restaurant': 'Restauracja BBQ',
    'brazilian_restaurant': 'Restauracja brazylijska',
    'breakfast_restaurant': 'Restauracja śniadaniowa',
    'brunch_restaurant': 'Restauracja brunchowa',
    'cafe': 'Kawiarnia',
    'chinese_restaurant': 'Restauracja chińska',
    'coffee_shop': 'Kawiarnia',
    'fast_food_restaurant': 'Fast food',
    'french_restaurant': 'Restauracja francuska',
    'greek_restaurant': 'Restauracja grecka',
    'hamburger_restaurant': 'Burgery',
    'ice_cream_shop': 'Lodziarnia',
    'indian_restaurant': 'Restauracja indyjska',
    'indonesian_restaurant': 'Restauracja indonezyjska',
    'italian_restaurant': 'Restauracja włoska',
    'japanese_restaurant': 'Restauracja japońska',
    'korean_restaurant': 'Restauracja koreańska',
    'lebanese_restaurant': 'Restauracja libańska',
    'meal_delivery': 'Dostawa jedzenia',
    'meal_takeaway': 'Jedzenie na wynos',
    'mediterranean_restaurant': 'Restauracja śródziemnomorska',
    'mexican_restaurant': 'Restauracja meksykańska',
    'middle_eastern_restaurant': 'Restauracja bliskowschodnia',
    'pizza_restaurant': 'Pizzeria',
    'ramen_restaurant': 'Ramen',
    'restaurant': 'Restauracja',
    'sandwich_shop': 'Kanapki',
    'seafood_restaurant': 'Owoce morza',
    'spanish_restaurant': 'Restauracja hiszpańska',
    'steak_house': 'Steakhouse',
    'sushi_restaurant': 'Sushi',
    'thai_restaurant': 'Restauracja tajska',
    'turkish_restaurant': 'Restauracja turecka',
    'vegan_restaurant': 'Restauracja wegańska',
    'vegetarian_restaurant': 'Restauracja wegetariańska',
    'vietnamese_restaurant': 'Restauracja wietnamska',

    # Government
    'city_hall': 'Ratusz',
    'courthouse': 'Sąd',
    'embassy': 'Ambasada',
    'fire_station': 'Straż pożarna',
    'local_government_office': 'Urząd',
    'police': 'Policja',
    'post_office': 'Poczta',

    # Health and Wellness
    'dental_clinic': 'Klinika stomatologiczna',
    'dentist': 'Dentysta',
    'doctor': 'Lekarz',
    'drugstore': 'Apteka',
    'hospital': 'Szpital',
    'medical_lab': 'Laboratorium medyczne',
    'pharmacy': 'Apteka',
    'physiotherapist': 'Fizjoterapeuta',
    'spa': 'Spa',

    # Lodging
    'bed_and_breakfast': 'Nocleg ze śniadaniem',
    'campground': 'Pole namiotowe',
    'camping_cabin': 'Domek kempingowy',
    'cottage': 'Domek letniskowy',
    'extended_stay_hotel': 'Hotel długoterminowy',
    'farmstay': 'Agroturystyka',
    'guest_house': 'Pensjonat',
    'hostel': 'Hostel',
    'hotel': 'Hotel',
    'lodging': 'Nocleg',
    'motel': 'Motel',
    'private_guest_room': 'Pokój gościnny',
    'resort_hotel': 'Hotel resortowy',
    'rv_park': 'Park dla kamperów',

    # Places of Worship
    'church': 'Kościół',
    'hindu_temple': 'Świątynia hinduska',
    'mosque': 'Meczet',
    'synagogue': 'Synagoga',

    # Services
    'barber_shop': 'Fryzjer męski',
    'beauty_salon': 'Salon kosmetyczny',
    'cemetery': 'Cmentarz',
    'child_care_agency': 'Opieka nad dziećmi',
    'consultant': 'Konsultant',
    'courier_service': 'Usługi kurierskie',
    'electrician': 'Elektryk',
    'florist': 'Kwiaciarnia',
    'funeral_home': 'Dom pogrzebowy',
    'hair_care': 'Fryzjer',
    'hair_salon': 'Salon fryzjerski',
    'insurance_agency': 'Agencja ubezpieczeniowa',
    'laundry': 'Pralnia',
    'lawyer': 'Prawnik',
    'locksmith': 'Ślusarz',
    'moving_company': 'Firma przeprowadzkowa',
    'painter': 'Malarz',
    'plumber': 'Hydraulik',
    'real_estate_agency': 'Agencja nieruchomości',
    'roofing_contractor': 'Dekarz',
    'storage': 'Magazyn',
    'tailor': 'Krawiec',
    'telecommunications_service_provider': 'Telekomunikacja',
    'travel_agency': 'Biuro podróży',
    'veterinary_care': 'Weterynarz',

    # Shopping
    'auto_parts_store': 'Sklep z częściami',
    'bicycle_store': 'Sklep rowerowy',
    'book_store': 'Księgarnia',
    'cell_phone_store': 'Sklep z telefonami',
    'clothing_store': 'Sklep odzieżowy',
    'convenience_store': 'Sklep spożywczy',
    'department_store': 'Dom towarowy',
    'discount_store': 'Sklep z przecenami',
    'electronics_store': 'Sklep elektroniczny',
    'furniture_store': 'Sklep meblowy',
    'gift_shop': 'Sklep z upominkami',
    'grocery_store': 'Sklep spożywczy',
    'hardware_store': 'Sklep z narzędziami',
    'home_goods_store': 'Sklep z artykułami domowymi',
    'home_improvement_store': 'Market budowlany',
    'jewelry_store': 'Jubiler',
    'liquor_store': 'Sklep monopolowy',
    'market': 'Targ',
    'pet_store': 'Sklep zoologiczny',
    'shoe_store': 'Sklep obuwniczy',
    'shopping_mall': 'Centrum handlowe',
    'sporting_goods_store': 'Sklep sportowy',
    'store': 'Sklep',
    'supermarket': 'Supermarket',
    'wholesaler': 'Hurtownia',

    # Sports
    'athletic_field': 'Boisko sportowe',
    'fitness_center': 'Siłownia',
    'golf_course': 'Pole golfowe',
    'gym': 'Siłownia',
    'playground': 'Plac zabaw',
    'ski_resort': 'Ośrodek narciarski',
    'sports_club': 'Klub sportowy',
    'sports_complex': 'Kompleks sportowy',
    'stadium': 'Stadion',
    'swimming_pool': 'Basen',

    # Transportation
    'airport': 'Lotnisko',
    'bus_station': 'Dworzec autobusowy',
    'bus_stop': 'Przystanek autobusowy',
    'ferry_terminal': 'Terminal promowy',
    'heliport': 'Lądowisko helikopterów',
    'light_rail_station': 'Stacja kolejki',
    'park_and_ride': 'Park & Ride',
    'subway_station': 'Stacja metra',
    'taxi_stand': 'Postój taksówek',
    'train_station': 'Dworzec kolejowy',
    'transit_depot': 'Zajezdnia',
    'transit_station': 'Stacja przesiadkowa',
    'truck_stop': 'Parking dla TIR-ów',

    # Generic types (often returned by Google)
    'establishment': 'Firma',
    'point_of_interest': 'Punkt zainteresowania',
    'general_contractor': 'Firma budowlana',
    'roofing_contractor': 'Dekarz',
    'hvac_contractor': 'Klimatyzacja i ogrzewanie',
    'plumber': 'Hydraulik',
    'electrician': 'Elektryk',
    'contractor': 'Wykonawca',
    'construction_company': 'Firma budowlana',
    'industrial_area': 'Strefa przemysłowa',
    'office': 'Biuro',
    'food': 'Gastronomia',
    'health': 'Zdrowie',
    'finance': 'Finanse',
    'political': 'Instytucja polityczna',
    'place_of_worship': 'Miejsce kultu',
    'natural_feature': 'Obiekt przyrodniczy',
    'locality': 'Miejscowość',
    'sublocality': 'Dzielnica',
    'neighborhood': 'Okolica',
    'premise': 'Lokal',
    'subpremise': 'Podlokal',
    'route': 'Trasa',
    'street_address': 'Adres',
    'floor': 'Piętro',
    'room': 'Pokój',
    'postal_code': 'Kod pocztowy',
    'country': 'Kraj',
    'administrative_area_level_1': 'Województwo',
    'administrative_area_level_2': 'Powiat',
}


@dataclass
class FieldStatus:
    """Status of a single GBP field"""
    field_name: str
    status: str  # 'complete', 'partial', 'missing'
    value: Optional[Any] = None
    score: float = 0.0
    max_score: float = 0.0
    recommendation: Optional[str] = None
    details: Optional[Dict[str, Any]] = None  # Additional field-specific details


@dataclass
class AuditResult:
    """Complete GBP audit result"""
    company_id: int
    completeness_score: int
    fields: Dict[str, FieldStatus] = field(default_factory=dict)
    recommendations: List[Dict[str, Any]] = field(default_factory=list)
    photo_count: int = 0
    logo_present: bool = False
    cover_photo_present: bool = False
    review_count: int = 0
    average_rating: Optional[Decimal] = None
    google_place_id: Optional[str] = None
    google_maps_url: Optional[str] = None
    audit_errors: Optional[str] = None


class GBPAuditService:
    """Service for auditing Google Business Profile completeness"""

    def __init__(self, db: Session):
        """
        Initialize GBP Audit service.

        Args:
            db: SQLAlchemy database session
        """
        self.db = db

    def audit_company(self, company_id: int) -> AuditResult:
        """
        Run full GBP audit for a company.

        Args:
            company_id: ID of the company to audit

        Returns:
            AuditResult with completeness score and field details
        """
        company = self.db.query(Company).filter(Company.id == company_id).first()
        if not company:
            raise ValueError(f"Company with id {company_id} not found")

        # Get latest website analysis for Google Business data
        website_analysis = self.db.query(CompanyWebsiteAnalysis).filter(
            CompanyWebsiteAnalysis.company_id == company_id
        ).order_by(CompanyWebsiteAnalysis.analyzed_at.desc()).first()

        # Audit each field
        fields = {}
        total_score = 0.0
        recommendations = []

        # Name check (uses Google data)
        fields['name'] = self._check_name(company, website_analysis)
        total_score += fields['name'].score

        # Address check (uses Google data)
        fields['address'] = self._check_address(company, website_analysis)
        total_score += fields['address'].score

        # Phone check (uses Google data)
        fields['phone'] = self._check_phone(company, website_analysis)
        total_score += fields['phone'].score

        # Website check (uses Google data)
        fields['website'] = self._check_website(company, website_analysis)
        total_score += fields['website'].score

        # Hours check (uses Google data)
        fields['hours'] = self._check_hours(company, website_analysis)
        total_score += fields['hours'].score

        # Categories check (uses Google data - google_types)
        fields['categories'] = self._check_categories(company, website_analysis)
        total_score += fields['categories'].score

        # Photos check (uses Google data)
        fields['photos'] = self._check_photos(company, website_analysis)
        total_score += fields['photos'].score

        # Description check - Google API doesn't provide owner description
        fields['description'] = self._check_description(company)
        total_score += fields['description'].score

        # Services check
        fields['services'] = self._check_services(company)
        total_score += fields['services'].score

        # Reviews check (from website analysis)
        fields['reviews'] = self._check_reviews(company, website_analysis)
        total_score += fields['reviews'].score

        # Build recommendations from fields with issues
        for field_name, field_status in fields.items():
            if field_status.recommendation:
                priority = self._get_priority(field_status)
                recommendations.append({
                    'priority': priority,
                    'field': field_name,
                    'recommendation': field_status.recommendation,
                    'impact': FIELD_WEIGHTS.get(field_name, 0)
                })

        # Sort recommendations by priority and impact
        priority_order = {'high': 0, 'medium': 1, 'low': 2}
        recommendations.sort(key=lambda x: (priority_order.get(x['priority'], 3), -x['impact']))

        # Extract Google Business data from website analysis
        google_place_id = None
        google_maps_url = None
        review_count = 0
        average_rating = None

        if website_analysis:
            google_place_id = website_analysis.google_place_id
            google_maps_url = website_analysis.google_maps_url
            review_count = website_analysis.google_reviews_count or 0
            average_rating = website_analysis.google_rating

        # Create result
        result = AuditResult(
            company_id=company_id,
            completeness_score=round(total_score),
            fields=fields,
            recommendations=recommendations,
            photo_count=fields['photos'].value if isinstance(fields['photos'].value, int) else 0,
            logo_present=(fields['photos'].value or 0) >= 1 if isinstance(fields['photos'].value, int) else False,
            cover_photo_present=(fields['photos'].value or 0) >= 2 if isinstance(fields['photos'].value, int) else False,
            review_count=review_count,
            average_rating=average_rating,
            google_place_id=google_place_id,
            google_maps_url=google_maps_url
        )

        return result

    def save_audit(self, result: AuditResult, source: str = 'manual') -> GBPAudit:
        """
        Save audit result to database.

        Args:
            result: AuditResult to save
            source: Audit source ('manual', 'automated', 'api')

        Returns:
            Saved GBPAudit record
        """
        # Convert fields to JSON-serializable format
        fields_status = {}
        for name, field_status in result.fields.items():
            # Keep dict values as-is for JSON serialization (e.g., opening hours)
            # Convert other complex types to string
            if field_status.value is None:
                serialized_value = None
            elif isinstance(field_status.value, (dict, list)):
                serialized_value = field_status.value  # Keep as dict/list for JSON
            elif isinstance(field_status.value, (int, float, bool)):
                serialized_value = field_status.value  # Keep primitives as-is
            else:
                serialized_value = str(field_status.value)

            fields_status[name] = {
                'status': field_status.status,
                'value': serialized_value,
                'score': field_status.score,
                'max_score': field_status.max_score,
                'details': field_status.details
            }

        # Create audit record
        audit = GBPAudit(
            company_id=result.company_id,
            audit_date=datetime.now(),
            completeness_score=result.completeness_score,
            fields_status=fields_status,
            recommendations=result.recommendations,
            has_name=result.fields.get('name', FieldStatus('name', 'missing')).status == 'complete',
            has_address=result.fields.get('address', FieldStatus('address', 'missing')).status == 'complete',
            has_phone=result.fields.get('phone', FieldStatus('phone', 'missing')).status == 'complete',
            has_website=result.fields.get('website', FieldStatus('website', 'missing')).status == 'complete',
            has_hours=result.fields.get('hours', FieldStatus('hours', 'missing')).status == 'complete',
            has_categories=result.fields.get('categories', FieldStatus('categories', 'missing')).status == 'complete',
            has_photos=result.photo_count > 0,
            has_description=result.fields.get('description', FieldStatus('description', 'missing')).status == 'complete',
            has_services=result.fields.get('services', FieldStatus('services', 'missing')).status == 'complete',
            has_reviews=result.fields.get('reviews', FieldStatus('reviews', 'missing')).status in ['complete', 'partial'],
            photo_count=result.photo_count,
            logo_present=result.logo_present,
            cover_photo_present=result.cover_photo_present,
            review_count=result.review_count,
            average_rating=result.average_rating,
            google_place_id=result.google_place_id,
            google_maps_url=result.google_maps_url,
            audit_source=source,
            audit_version='1.0',
            audit_errors=result.audit_errors
        )

        self.db.add(audit)
        self.db.commit()
        self.db.refresh(audit)

        logger.info(f"GBP audit saved for company {result.company_id}: score={result.completeness_score}")
        return audit

    def get_latest_audit(self, company_id: int) -> Optional[GBPAudit]:
        """
        Get the most recent audit for a company.

        Args:
            company_id: Company ID

        Returns:
            Latest GBPAudit or None
        """
        return self.db.query(GBPAudit).filter(
            GBPAudit.company_id == company_id
        ).order_by(GBPAudit.audit_date.desc()).first()

    def get_audit_history(self, company_id: int, limit: int = 10) -> List[GBPAudit]:
        """
        Get audit history for a company.

        Args:
            company_id: Company ID
            limit: Maximum number of audits to return

        Returns:
            List of GBPAudit records ordered by date descending
        """
        return self.db.query(GBPAudit).filter(
            GBPAudit.company_id == company_id
        ).order_by(GBPAudit.audit_date.desc()).limit(limit).all()

    # === Field Check Methods ===

    def _check_name(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check business name completeness - uses Google data"""
        max_score = FIELD_WEIGHTS['name']

        # Use Google name if available, fallback to NordaBiz
        name = analysis.google_name if analysis and analysis.google_name else company.name

        if name and len(name.strip()) >= 3:
            return FieldStatus(
                field_name='name',
                status='complete',
                value=name,
                score=max_score,
                max_score=max_score
            )

        return FieldStatus(
            field_name='name',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj nazwę firmy do wizytówki Google. Nazwa powinna być oficjalną nazwą firmy.'
        )

    def _check_address(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check address completeness - uses Google data"""
        max_score = FIELD_WEIGHTS['address']

        # Use Google address if available
        google_address = analysis.google_address if analysis else None

        if google_address and len(google_address.strip()) >= 10:
            return FieldStatus(
                field_name='address',
                status='complete',
                value=google_address,
                score=max_score,
                max_score=max_score
            )

        # Fallback to NordaBiz data
        has_street = bool(company.address_street)
        has_city = bool(company.address_city)

        if has_city or has_street:
            partial_score = max_score * 0.5
            return FieldStatus(
                field_name='address',
                status='partial',
                value=company.address_city or company.address_street,
                score=partial_score,
                max_score=max_score,
                recommendation='Uzupełnij pełny adres firmy w Google Business Profile.'
            )

        return FieldStatus(
            field_name='address',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj adres firmy do wizytówki Google. Pełny adres jest kluczowy dla lokalnego SEO.'
        )

    def _check_phone(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check phone number presence - uses Google data"""
        max_score = FIELD_WEIGHTS['phone']

        # Use Google phone if available
        phone = analysis.google_phone if analysis and analysis.google_phone else company.phone

        if phone and len(phone.strip()) >= 9:
            return FieldStatus(
                field_name='phone',
                status='complete',
                value=phone,
                score=max_score,
                max_score=max_score
            )

        return FieldStatus(
            field_name='phone',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj numer telefonu do wizytówki Google. Klienci oczekują możliwości bezpośredniego kontaktu.'
        )

    def _check_website(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check website presence - uses Google data"""
        max_score = FIELD_WEIGHTS['website']

        # Use Google website if available
        website = analysis.google_website if analysis and analysis.google_website else company.website

        if website and website.strip().startswith(('http://', 'https://')):
            return FieldStatus(
                field_name='website',
                status='complete',
                value=website,
                score=max_score,
                max_score=max_score
            )

        if website:
            # Has website but might not be properly formatted
            return FieldStatus(
                field_name='website',
                status='partial',
                value=website,
                score=max_score * 0.7,
                max_score=max_score,
                recommendation='Upewnij się, że adres strony w Google Business Profile zawiera protokół (https://).'
            )

        return FieldStatus(
            field_name='website',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj stronę internetową do wizytówki Google. Link do strony zwiększa wiarygodność i ruch.'
        )

    def _check_hours(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check opening hours presence"""
        max_score = FIELD_WEIGHTS['hours']

        # Check if we have opening hours from Google Business Profile
        if analysis and analysis.google_opening_hours:
            return FieldStatus(
                field_name='hours',
                status='complete',
                value=analysis.google_opening_hours,
                score=max_score,
                max_score=max_score
            )

        return FieldStatus(
            field_name='hours',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj godziny otwarcia firmy. Klienci chcą wiedzieć, kiedy mogą Cię odwiedzić.'
        )

    def _check_categories(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check business category completeness - uses Google data"""
        max_score = FIELD_WEIGHTS['categories']

        # Use Google types if available
        google_types = analysis.google_types if analysis and analysis.google_types else None

        if google_types and len(google_types) > 0:
            # Translate Google types to Polish using GOOGLE_TYPES_PL dictionary
            # Fallback to formatted English if translation not available
            formatted_types = []
            for t in google_types[:3]:
                if t in GOOGLE_TYPES_PL:
                    formatted_types.append(GOOGLE_TYPES_PL[t])
                else:
                    # Fallback: remove underscores, title case
                    formatted_types.append(t.replace('_', ' ').title())

            return FieldStatus(
                field_name='categories',
                status='complete',
                value=', '.join(formatted_types),
                score=max_score,
                max_score=max_score
            )

        # Fallback to NordaBiz category
        if company.category_id and company.category:
            return FieldStatus(
                field_name='categories',
                status='partial',
                value=company.category.name if company.category else None,
                score=max_score * 0.5,
                max_score=max_score,
                recommendation='Dodaj kategorie w Google Business Profile. Kategorie z Google są ważniejsze dla SEO niż dane lokalne.'
            )

        return FieldStatus(
            field_name='categories',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Wybierz główną kategorię działalności w Google Business Profile. Kategoria pomaga klientom znaleźć Twoją firmę.'
        )

    def _check_photos(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """Check photo completeness"""
        max_score = FIELD_WEIGHTS['photos']

        # Get Google Business Profile photo count from website analysis
        photo_count = 0
        if analysis and analysis.google_photos_count:
            photo_count = analysis.google_photos_count

        if photo_count >= PHOTO_REQUIREMENTS['recommended']:
            return FieldStatus(
                field_name='photos',
                status='complete',
                value=photo_count,
                score=max_score,
                max_score=max_score
            )

        if photo_count >= PHOTO_REQUIREMENTS['minimum']:
            partial_score = max_score * (photo_count / PHOTO_REQUIREMENTS['recommended'])
            return FieldStatus(
                field_name='photos',
                status='partial',
                value=photo_count,
                score=min(partial_score, max_score * 0.7),
                max_score=max_score,
                recommendation=f'Dodaj więcej zdjęć firmy. Zalecane minimum to {PHOTO_REQUIREMENTS["recommended"]} zdjęć.'
            )

        return FieldStatus(
            field_name='photos',
            status='missing',
            value=photo_count,
            score=0,
            max_score=max_score,
            recommendation='Dodaj zdjęcia firmy (logo, wnętrze, zespół, produkty). Wizytówki ze zdjęciami mają 42% więcej zapytań o wskazówki dojazdu.'
        )

    def _check_description(self, company: Company) -> FieldStatus:
        """Check business description completeness"""
        max_score = FIELD_WEIGHTS['description']

        # Check short and full descriptions
        desc = company.description_full or company.description_short

        if desc and len(desc.strip()) >= 100:
            return FieldStatus(
                field_name='description',
                status='complete',
                value=desc[:100] + '...' if len(desc) > 100 else desc,
                score=max_score,
                max_score=max_score
            )

        if desc and len(desc.strip()) >= 30:
            return FieldStatus(
                field_name='description',
                status='partial',
                value=desc,
                score=max_score * 0.5,
                max_score=max_score,
                recommendation='Rozbuduj opis firmy. Dobry opis powinien mieć minimum 100-200 znaków i zawierać słowa kluczowe.'
            )

        return FieldStatus(
            field_name='description',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj szczegółowy opis firmy. Opisz czym się zajmujesz, jakie usługi oferujesz i co Cię wyróżnia.'
        )

    def _check_services(self, company: Company) -> FieldStatus:
        """Check services list completeness"""
        max_score = FIELD_WEIGHTS['services']

        # Check company services relationship
        service_count = 0
        if hasattr(company, 'services') and company.services:
            service_count = len(company.services)

        # Also check services_offered text field
        has_services_text = bool(company.services_offered and len(company.services_offered.strip()) > 10)

        if service_count >= 3 or has_services_text:
            return FieldStatus(
                field_name='services',
                status='complete',
                value=service_count if service_count else 'W opisie',
                score=max_score,
                max_score=max_score
            )

        if service_count >= 1:
            return FieldStatus(
                field_name='services',
                status='partial',
                value=service_count,
                score=max_score * 0.5,
                max_score=max_score,
                recommendation='Dodaj więcej usług do wizytówki. Zalecane jest minimum 3-5 głównych usług.'
            )

        return FieldStatus(
            field_name='services',
            status='missing',
            score=0,
            max_score=max_score,
            recommendation='Dodaj listę usług lub produktów. Pomaga to klientom zrozumieć Twoją ofertę.'
        )

    def _check_reviews(self, company: Company, analysis: Optional[CompanyWebsiteAnalysis]) -> FieldStatus:
        """
        Check reviews presence and quality.

        Scoring logic (max 9 points):
        =============================
        COMPLETE (9/9 pts):
          - 5+ opinii AND ocena >= 4.0

        PARTIAL (3-8/9 pts):
          - Bazowo: 3 pkt za pierwszą opinię
          - +1 pkt za każdą kolejną opinię (do 4 opinii = 6 pkt max)
          - +1 pkt bonus jeśli ocena >= 4.0 (do 7 pkt max)

          Przykłady:
          - 1 opinia, ocena 3.5 → 3/9 pkt
          - 1 opinia, ocena 5.0 → 4/9 pkt (3 + 1 bonus)
          - 3 opinie, ocena 4.2 → 6/9 pkt (3 + 2 + 1 bonus)
          - 4 opinie, ocena 4.5 → 7/9 pkt (3 + 3 + 1 bonus)

        MISSING (0/9 pts):
          - Brak opinii
        """
        max_score = FIELD_WEIGHTS['reviews']

        review_count = 0
        rating = None

        if analysis:
            review_count = analysis.google_reviews_count or 0
            rating = analysis.google_rating

        # COMPLETE: 5+ reviews with good rating (4.0+) → full 9/9 points
        if review_count >= REVIEW_THRESHOLDS['good'] and rating and float(rating) >= 4.0:
            return FieldStatus(
                field_name='reviews',
                status='complete',
                value=f'{review_count} opinii, ocena {rating}',
                score=max_score,
                max_score=max_score,
                details={
                    'review_count': review_count,
                    'rating': float(rating),
                    'base_score': 3,
                    'additional_score': min(review_count - 1, 4),
                    'rating_bonus': 1,
                    'breakdown': f'3 pkt (1. opinia) + {min(review_count - 1, 4)} pkt (kolejne) + 1 pkt (ocena) = {max_score}/{max_score}'
                }
            )

        # PARTIAL: 1-4 reviews → proportional scoring
        if review_count >= REVIEW_THRESHOLDS['minimum']:
            # Base: 3 pts for first review + 1 pt per additional review (max 6 pts for 4 reviews)
            base_score = 3
            additional_score = min(review_count - 1, 4)
            partial_score = base_score + additional_score

            # Bonus: +1 pt for good rating (>= 4.0)
            rating_bonus = 0
            if rating and float(rating) >= 4.0:
                rating_bonus = 1
                partial_score = min(partial_score + rating_bonus, max_score - 2)  # max 7 pts

            # Build breakdown string
            breakdown_parts = [f'3 pkt (1. opinia)']
            if additional_score > 0:
                breakdown_parts.append(f'{additional_score} pkt (kolejne)')
            if rating_bonus > 0:
                breakdown_parts.append(f'1 pkt (ocena)')
            breakdown = ' + '.join(breakdown_parts) + f' = {partial_score}/{max_score}'

            return FieldStatus(
                field_name='reviews',
                status='partial',
                value=f'{review_count} opinii' + (f', ocena {rating}' if rating else ''),
                score=partial_score,
                max_score=max_score,
                recommendation='Zachęcaj klientów do zostawiania opinii. Więcej pozytywnych recenzji zwiększa zaufanie.',
                details={
                    'review_count': review_count,
                    'rating': float(rating) if rating else None,
                    'base_score': base_score,
                    'additional_score': additional_score,
                    'rating_bonus': rating_bonus,
                    'breakdown': breakdown
                }
            )

        # MISSING: no reviews → 0/9 points
        return FieldStatus(
            field_name='reviews',
            status='missing',
            value=review_count,
            score=0,
            max_score=max_score,
            recommendation='Zbieraj opinie od klientów. Wizytówki z opiniami są bardziej wiarygodne i lepiej widoczne.',
            details={
                'review_count': 0,
                'rating': None,
                'breakdown': '0 pkt (brak opinii)'
            }
        )

    def _get_priority(self, field_status: FieldStatus) -> str:
        """Determine recommendation priority based on field importance and status"""
        weight = FIELD_WEIGHTS.get(field_status.field_name, 0)

        if field_status.status == 'missing':
            if weight >= 10:
                return 'high'
            elif weight >= 8:
                return 'medium'
            else:
                return 'low'
        elif field_status.status == 'partial':
            if weight >= 10:
                return 'medium'
            else:
                return 'low'

        return 'low'

    # === Enhanced Analysis Methods ===

    def analyze_reviews(self, company_id: int, place_data: Dict = None) -> Dict[str, Any]:
        """
        Analyze reviews for a company using Google Places data.

        Returns dict with:
        - reviews_with_response, reviews_without_response
        - review_response_rate
        - review_sentiment (positive/neutral/negative counts)
        - review_keywords (top words from reviews)
        - reviews_30d (recent review count)
        """
        result = {
            'reviews_with_response': 0,
            'reviews_without_response': 0,
            'review_response_rate': 0.0,
            'avg_review_response_days': None,
            'review_sentiment': {'positive': 0, 'neutral': 0, 'negative': 0},
            'reviews_30d': 0,
            'review_keywords': [],
        }

        if not place_data or 'reviews' not in place_data:
            return result

        reviews = place_data.get('reviews', [])
        if not reviews:
            return result

        # Analyze each review
        keywords_count = {}
        for review in reviews:
            rating = review.get('rating', 0)

            # Sentiment based on rating
            if rating >= 4:
                result['review_sentiment']['positive'] += 1
            elif rating == 3:
                result['review_sentiment']['neutral'] += 1
            else:
                result['review_sentiment']['negative'] += 1

            # Extract keywords from review text
            text = review.get('text', {})
            review_text = text.get('text', '') if isinstance(text, dict) else str(text)
            if review_text:
                # Simple keyword extraction - split and count common words
                words = review_text.lower().split()
                stop_words = {'i', 'w', 'na', 'do', 'z', 'się', 'jest', 'nie', 'to', 'że',
                             'o', 'jak', 'za', 'od', 'po', 'ale', 'co', 'tak', 'a', 'te',
                             'ze', 'dla', 'są', 'ten', 'ta', 'już', 'czy', 'tego', 'tej'}
                for word in words:
                    word = word.strip('.,!?;:"()[]')
                    if len(word) >= 4 and word not in stop_words:
                        keywords_count[word] = keywords_count.get(word, 0) + 1

        # Top 10 keywords
        sorted_keywords = sorted(keywords_count.items(), key=lambda x: x[1], reverse=True)
        result['review_keywords'] = [k for k, v in sorted_keywords[:10]]

        total = len(reviews)
        # BUG FIX: Check ownerResponse (not authorAttribution.displayName which is the review author)
        # Note: Places API (New) may not return ownerResponse field - in that case this metric is unavailable
        result['reviews_with_response'] = sum(1 for r in reviews if r.get('ownerResponse'))
        result['reviews_without_response'] = total - result['reviews_with_response']
        result['review_response_rate'] = round(result['reviews_with_response'] / total * 100, 1) if total > 0 else 0.0

        return result

    def analyze_review_sentiment_ai(self, reviews_data: list) -> dict:
        """Analyze review sentiment using Gemini AI.

        Args:
            reviews_data: List of review dicts with 'text', 'rating', 'author'

        Returns:
            Dict with AI-enhanced sentiment analysis:
            {
                'themes': [{'theme': str, 'sentiment': str, 'count': int}],
                'strengths': [str],  # What customers love
                'weaknesses': [str],  # What needs improvement
                'overall_sentiment': str,  # positive/mixed/negative
                'sentiment_score': float,  # -1.0 to 1.0
                'summary': str,  # 1-2 sentence summary
            }
        """
        # Filter reviews with text
        reviews_with_text = [r for r in reviews_data if r.get('text')]
        if not reviews_with_text:
            return None

        # Build prompt with review texts (max 10 reviews to stay within token limits)
        reviews_text = ""
        for i, r in enumerate(reviews_with_text[:10], 1):
            text = r.get('text', {})
            review_text = text.get('text', '') if isinstance(text, dict) else str(text)
            rating = r.get('rating', '?')
            reviews_text += f"\n{i}. [Ocena: {rating}/5] {review_text[:300]}"

        prompt = f"""Przeanalizuj poniższe opinie Google dla lokalnej firmy w Polsce.

OPINIE:{reviews_text}

Odpowiedz WYŁĄCZNIE poprawnym JSON-em (bez markdown, bez komentarzy):
{{
    "themes": [
        {{"theme": "nazwa tematu", "sentiment": "positive/negative/neutral", "count": N}}
    ],
    "strengths": ["co klienci chwalą - max 3 punkty"],
    "weaknesses": ["co wymaga poprawy - max 3 punkty"],
    "overall_sentiment": "positive/mixed/negative",
    "sentiment_score": 0.0,
    "summary": "1-2 zdania podsumowania po polsku"
}}

Gdzie sentiment_score: -1.0 (bardzo negatywny) do 1.0 (bardzo pozytywny).
Skup się na TREŚCI opinii, nie tylko na ocenach."""

        try:
            from gemini_service import generate_text
            import json

            response = generate_text(prompt, temperature=0.3)
            if not response:
                return None

            # Parse JSON response
            response = response.strip()
            if response.startswith('```'):
                response = response.split('\n', 1)[-1].rsplit('```', 1)[0]

            return json.loads(response)
        except Exception as e:
            logger.warning(f"AI sentiment analysis failed: {e}")
            return None

    def check_nap_consistency(self, company: Company,
                              website_analysis: 'CompanyWebsiteAnalysis' = None) -> Dict[str, Any]:
        """
        Check NAP (Name/Address/Phone) consistency between GBP and website.

        Returns dict with:
        - nap_consistent: bool
        - nap_issues: list of inconsistencies
        """
        result = {
            'nap_consistent': True,
            'nap_issues': [],
        }

        if not website_analysis:
            return result

        # Compare name
        gbp_name = website_analysis.google_name
        website_name = company.name
        if gbp_name and website_name:
            if gbp_name.lower().strip() != website_name.lower().strip():
                result['nap_consistent'] = False
                result['nap_issues'].append({
                    'field': 'name',
                    'gbp': gbp_name,
                    'website': website_name,
                    'severity': 'low'
                })

        # Compare phone
        gbp_phone = website_analysis.google_phone
        company_phone = company.phone
        if gbp_phone and company_phone:
            # Normalize phone numbers for comparison
            gbp_clean = ''.join(c for c in gbp_phone if c.isdigit())
            company_clean = ''.join(c for c in company_phone if c.isdigit())
            # Compare last 9 digits (ignore country code)
            if gbp_clean[-9:] != company_clean[-9:] if len(gbp_clean) >= 9 and len(company_clean) >= 9 else gbp_clean != company_clean:
                result['nap_consistent'] = False
                result['nap_issues'].append({
                    'field': 'phone',
                    'gbp': gbp_phone,
                    'website': company_phone,
                    'severity': 'medium'
                })

        # Compare address
        gbp_address = website_analysis.google_address
        company_address = f"{company.address_street or ''}, {company.address_city or ''}"
        if gbp_address and company.address_city:
            city_lower = company.address_city.lower()
            if city_lower not in gbp_address.lower():
                result['nap_consistent'] = False
                result['nap_issues'].append({
                    'field': 'address',
                    'gbp': gbp_address,
                    'website': company_address.strip(', '),
                    'severity': 'high'
                })

        return result

    def analyze_photo_categories(self, photos_data: List[Dict] = None) -> Dict[str, int]:
        """Categorize photos based on available metadata."""
        categories = {
            'total': 0,
            'owner': 0,
            'user': 0,
        }

        if not photos_data:
            return categories

        categories['total'] = len(photos_data)
        for photo in photos_data:
            attributions = photo.get('authorAttributions', [])
            is_owner = any('owner' in a.get('displayName', '').lower() or
                          'właściciel' in a.get('displayName', '').lower()
                          for a in attributions)
            if is_owner:
                categories['owner'] += 1
            else:
                categories['user'] += 1

        return categories

    def check_description_keywords(self, company: Company) -> Dict[str, Any]:
        """Check if business description contains relevant keywords."""
        result = {
            'description_keywords': [],
            'keyword_density_score': 0,
        }

        desc = company.description_full or company.description_short or ''
        if not desc:
            return result

        desc_lower = desc.lower()

        # Check for city name
        city = (company.address_city or '').lower()
        category_name = company.category.name.lower() if company.category else ''

        found_keywords = []

        # Check city name in description
        if city and city in desc_lower:
            found_keywords.append(city)

        # Check category-related terms
        if category_name and category_name in desc_lower:
            found_keywords.append(category_name)

        # General business keywords
        business_keywords = ['usługi', 'produkty', 'oferta', 'doświadczenie',
                           'profesjonalny', 'kontakt', 'zespół', 'specjalizacja']
        for kw in business_keywords:
            if kw in desc_lower:
                found_keywords.append(kw)

        result['description_keywords'] = found_keywords

        # Score: 0-100 based on keyword presence
        max_keywords = 5  # ideal number of keywords
        score = min(len(found_keywords) / max_keywords * 100, 100)
        result['keyword_density_score'] = int(score)

        return result

    def save_enhanced_audit(self, result: 'AuditResult', enhanced_data: Dict,
                           source: str = 'manual') -> 'GBPAudit':
        """Save audit with enhanced data (reviews, NAP, keywords, photos)."""
        # First save the standard audit
        audit = self.save_audit(result, source)

        # Then update with enhanced data
        if enhanced_data.get('reviews'):
            reviews = enhanced_data['reviews']
            audit.reviews_with_response = reviews.get('reviews_with_response', 0)
            audit.reviews_without_response = reviews.get('reviews_without_response', 0)
            audit.review_response_rate = reviews.get('review_response_rate', 0.0)
            audit.avg_review_response_days = reviews.get('avg_review_response_days')
            audit.review_sentiment = reviews.get('review_sentiment')
            audit.reviews_30d = reviews.get('reviews_30d', 0)
            audit.review_keywords = reviews.get('review_keywords')

        if enhanced_data.get('nap'):
            nap = enhanced_data['nap']
            audit.nap_consistent = nap.get('nap_consistent', True)
            audit.nap_issues = nap.get('nap_issues')

        if enhanced_data.get('keywords'):
            keywords = enhanced_data['keywords']
            audit.description_keywords = keywords.get('description_keywords')
            audit.keyword_density_score = keywords.get('keyword_density_score')

        if enhanced_data.get('photo_categories'):
            audit.photo_categories = enhanced_data['photo_categories']

        if enhanced_data.get('attributes'):
            audit.attributes = enhanced_data['attributes']

        if enhanced_data.get('hours'):
            hours = enhanced_data['hours']
            audit.has_special_hours = hours.get('has_special_hours', False)
            audit.special_hours = hours.get('special_hours')

        self.db.commit()
        self.db.refresh(audit)
        return audit

    def save_reviews(self, company_id: int, reviews_data: List[Dict]) -> int:
        """Save individual reviews to gbp_reviews table. Returns count saved."""
        saved = 0
        for review in reviews_data:
            review_id = review.get('name', '') or f"r_{review.get('author', 'anon')}_{review.get('time', '')}"

            existing = self.db.query(GBPReview).filter(
                GBPReview.company_id == company_id,
                GBPReview.google_review_id == review_id
            ).first()

            if not existing:
                gbp_review = GBPReview(
                    company_id=company_id,
                    google_review_id=review_id,
                    author_name=review.get('author', 'Anonim'),
                    rating=review.get('rating', 0),
                    text=review.get('text', ''),
                    publish_time=review.get('time'),
                    sentiment=self._classify_sentiment(review.get('rating', 0)),
                )
                self.db.add(gbp_review)
                saved += 1

        if saved:
            self.db.commit()
        return saved

    @staticmethod
    def _classify_sentiment(rating: int) -> str:
        """Classify review sentiment based on rating."""
        if rating >= 4:
            return 'positive'
        elif rating == 3:
            return 'neutral'
        else:
            return 'negative'

    # === AI-Powered Recommendations ===

    def generate_ai_recommendations(
        self,
        company: Company,
        result: AuditResult,
        user_id: Optional[int] = None
    ) -> List[Dict[str, Any]]:
        """
        Generate AI-powered recommendations using Gemini.

        Args:
            company: Company being audited
            result: AuditResult from the audit
            user_id: Optional user ID for cost tracking

        Returns:
            List of AI-generated recommendation dicts with keys:
            - priority: 'high', 'medium', 'low'
            - field: field name this applies to
            - recommendation: AI-generated recommendation text
            - action_steps: list of specific action steps
            - expected_impact: description of expected improvement
        """
        service = gemini_service.get_gemini_service()
        if not service:
            logger.warning("Gemini service not available - using static recommendations")
            return result.recommendations

        try:
            # Build context for AI
            prompt = self._build_ai_recommendation_prompt(company, result)

            # Call Gemini with cost tracking
            response_text = service.generate_text(
                prompt=prompt,
                feature='gbp_audit_ai',
                user_id=user_id,
                temperature=0.7,
                max_tokens=2000,
                model='3-pro'
            )

            # Parse AI response
            ai_recommendations = self._parse_ai_recommendations(response_text, result)

            logger.info(
                f"AI recommendations generated for company {company.id}: "
                f"{len(ai_recommendations)} recommendations"
            )

            return ai_recommendations

        except Exception as e:
            logger.error(f"AI recommendation generation failed: {e}")
            # Fall back to static recommendations
            return result.recommendations

    def _build_ai_recommendation_prompt(
        self,
        company: Company,
        result: AuditResult
    ) -> str:
        """
        Build prompt for Gemini to generate personalized recommendations.

        Args:
            company: Company being audited
            result: AuditResult with field statuses

        Returns:
            Formatted prompt string
        """
        # Build field status summary
        field_summary = []
        for field_name, field_status in result.fields.items():
            status_emoji = {
                'complete': '✅',
                'partial': '⚠️',
                'missing': '❌'
            }.get(field_status.status, '❓')

            field_summary.append(
                f"- {field_name}: {status_emoji} {field_status.status} "
                f"({field_status.score:.1f}/{field_status.max_score:.1f} pkt)"
            )

        # Get category info
        category_name = company.category.name if company.category else 'Nieznana'

        prompt = f"""Jesteś ekspertem od Google Business Profile (Wizytówki Google) i lokalnego SEO.

FIRMA: {company.name}
BRANŻA: {category_name}
MIASTO: {company.address_city or 'Nieznane'}
WYNIK AUDYTU: {result.completeness_score}/100

STATUS PÓL WIZYTÓWKI:
{chr(10).join(field_summary)}

LICZBA ZDJĘĆ: {result.photo_count}
LICZBA OPINII: {result.review_count}
OCENA: {result.average_rating or 'Brak'}

ZADANIE:
Wygeneruj 3-5 spersonalizowanych rekomendacji dla tej firmy, aby poprawić jej wizytówkę Google.

WYMAGANIA:
1. Każda rekomendacja powinna być konkretna i dostosowana do branży firmy
2. Skup się na polach z najniższymi wynikami
3. Podaj praktyczne kroki do wykonania
4. Używaj języka polskiego

ZWRÓĆ ODPOWIEDŹ W FORMACIE JSON (TYLKO JSON, BEZ MARKDOWN):
[
  {{
    "priority": "high|medium|low",
    "field": "nazwa_pola",
    "recommendation": "Krótki opis co poprawić",
    "action_steps": ["Krok 1", "Krok 2", "Krok 3"],
    "expected_impact": "Opis spodziewanej poprawy"
  }}
]

Priorytety:
- high: kluczowe pola (name, address, categories, description)
- medium: ważne pola (phone, website, photos, services)
- low: dodatkowe pola (hours, reviews)

Odpowiedź (TYLKO JSON):"""

        return prompt

    def _parse_ai_recommendations(
        self,
        response_text: str,
        fallback_result: AuditResult
    ) -> List[Dict[str, Any]]:
        """
        Parse AI response into structured recommendations.

        Args:
            response_text: Raw text from Gemini
            fallback_result: AuditResult to use for fallback

        Returns:
            List of recommendation dicts
        """
        try:
            # Clean up response - remove markdown code blocks if present
            cleaned = response_text.strip()
            if cleaned.startswith('```'):
                # Remove markdown code block markers
                lines = cleaned.split('\n')
                # Find JSON content between ``` markers
                json_lines = []
                in_json = False
                for line in lines:
                    if line.startswith('```') and not in_json:
                        in_json = True
                        continue
                    elif line.startswith('```') and in_json:
                        break
                    elif in_json:
                        json_lines.append(line)
                cleaned = '\n'.join(json_lines)

            # Parse JSON
            recommendations = json.loads(cleaned)

            # Validate and enhance recommendations
            valid_recommendations = []
            valid_priorities = {'high', 'medium', 'low'}
            valid_fields = set(FIELD_WEIGHTS.keys())

            for rec in recommendations:
                if not isinstance(rec, dict):
                    continue

                # Validate priority
                priority = rec.get('priority', 'medium')
                if priority not in valid_priorities:
                    priority = 'medium'

                # Validate field
                field = rec.get('field', 'general')
                if field not in valid_fields:
                    field = 'general'

                # Get impact score from field weights
                impact = FIELD_WEIGHTS.get(field, 5)

                valid_recommendations.append({
                    'priority': priority,
                    'field': field,
                    'recommendation': rec.get('recommendation', ''),
                    'action_steps': rec.get('action_steps', []),
                    'expected_impact': rec.get('expected_impact', ''),
                    'impact': impact,
                    'source': 'ai'
                })

            if valid_recommendations:
                # Sort by priority and impact
                priority_order = {'high': 0, 'medium': 1, 'low': 2}
                valid_recommendations.sort(
                    key=lambda x: (priority_order.get(x['priority'], 3), -x['impact'])
                )
                return valid_recommendations

        except json.JSONDecodeError as e:
            logger.warning(f"Failed to parse AI recommendations JSON: {e}")
        except Exception as e:
            logger.warning(f"Error processing AI recommendations: {e}")

        # Return fallback recommendations with source marker
        fallback = []
        for rec in fallback_result.recommendations:
            rec_copy = dict(rec)
            rec_copy['source'] = 'static'
            rec_copy['action_steps'] = []
            rec_copy['expected_impact'] = ''
            fallback.append(rec_copy)

        return fallback

    def audit_with_ai(
        self,
        company_id: int,
        user_id: Optional[int] = None
    ) -> AuditResult:
        """
        Run full GBP audit with AI-powered recommendations.

        Args:
            company_id: ID of the company to audit
            user_id: Optional user ID for cost tracking

        Returns:
            AuditResult with AI-enhanced recommendations
        """
        # Run standard audit
        result = self.audit_company(company_id)

        # Get company for AI context
        company = self.db.query(Company).filter(Company.id == company_id).first()
        if not company:
            return result

        # Generate AI recommendations
        ai_recommendations = self.generate_ai_recommendations(
            company=company,
            result=result,
            user_id=user_id
        )

        # Replace static recommendations with AI-generated ones
        result.recommendations = ai_recommendations

        return result


# === Convenience Functions ===

def audit_company(db: Session, company_id: int, save: bool = True) -> AuditResult:
    """
    Audit a company's GBP completeness.

    Args:
        db: Database session
        company_id: Company ID to audit
        save: Whether to save audit to database

    Returns:
        AuditResult with completeness score and recommendations
    """
    service = GBPAuditService(db)
    result = service.audit_company(company_id)

    if save:
        service.save_audit(result)

    return result


def get_company_audit(db: Session, company_id: int) -> Optional[GBPAudit]:
    """
    Get the latest audit for a company.

    Args:
        db: Database session
        company_id: Company ID

    Returns:
        Latest GBPAudit or None
    """
    service = GBPAuditService(db)
    return service.get_latest_audit(company_id)


def audit_company_with_ai(
    db: Session,
    company_id: int,
    save: bool = True,
    user_id: Optional[int] = None
) -> AuditResult:
    """
    Audit a company's GBP completeness with AI-powered recommendations.

    Args:
        db: Database session
        company_id: Company ID to audit
        save: Whether to save audit to database
        user_id: Optional user ID for cost tracking

    Returns:
        AuditResult with AI-enhanced recommendations
    """
    service = GBPAuditService(db)
    result = service.audit_with_ai(company_id, user_id=user_id)

    if save:
        service.save_audit(result, source='ai')

    return result


def batch_audit_companies(
    db: Session,
    company_ids: Optional[List[int]] = None,
    save: bool = True
) -> Dict[int, AuditResult]:
    """
    Audit multiple companies.

    Args:
        db: Database session
        company_ids: List of company IDs (None = all active companies)
        save: Whether to save audits to database

    Returns:
        Dict mapping company_id to AuditResult
    """
    service = GBPAuditService(db)

    # Get companies to audit
    if company_ids is None:
        companies = db.query(Company).filter(Company.status == 'active').all()
        company_ids = [c.id for c in companies]

    results = {}
    for company_id in company_ids:
        try:
            result = service.audit_company(company_id)
            if save:
                service.save_audit(result, source='automated')
            results[company_id] = result
        except Exception as e:
            logger.error(f"Failed to audit company {company_id}: {e}")

    return results


# === Google Places API Integration ===

def fetch_google_business_data(
    db: Session,
    company_id: int,
    force_refresh: bool = False
) -> Dict[str, Any]:
    """Fetch Google Business Profile data using Places API (New)."""
    import os
    from datetime import datetime, timedelta

    result = {
        'success': False,
        'steps': [],
        'data': {},
        'error': None
    }

    company = db.query(Company).filter(Company.id == company_id).first()
    if not company:
        result['error'] = f'Firma o ID {company_id} nie znaleziona'
        return result

    # Cache check (identical to current)
    if not force_refresh:
        existing = db.query(CompanyWebsiteAnalysis).filter(
            CompanyWebsiteAnalysis.company_id == company_id
        ).order_by(CompanyWebsiteAnalysis.analyzed_at.desc()).first()

        if existing and existing.analyzed_at:
            age = datetime.now() - existing.analyzed_at
            if age < timedelta(hours=24) and existing.google_place_id:
                result['success'] = True
                result['steps'].append({
                    'step': 'cache_check',
                    'status': 'skipped',
                    'message': f'Dane pobrano {age.seconds // 3600}h temu - używam cache'
                })
                result['data'] = {
                    'google_place_id': existing.google_place_id,
                    'google_rating': float(existing.google_rating) if existing.google_rating else None,
                    'google_reviews_count': existing.google_reviews_count,
                    'google_photos_count': existing.google_photos_count,
                    'google_opening_hours': existing.google_opening_hours,
                    'cached': True
                }
                return result

    # Initialize Places API service
    try:
        places_service = GooglePlacesService()
    except ValueError as e:
        result['error'] = str(e)
        result['steps'].append({
            'step': 'api_key_check',
            'status': 'error',
            'message': str(e)
        })
        return result

    result['steps'].append({
        'step': 'api_key_check',
        'status': 'complete',
        'message': 'Places API (New) skonfigurowany'
    })

    # Step 1: Find place — reuse stored place_id if available (safe, unique Google ID)
    existing_analysis = db.query(CompanyWebsiteAnalysis).filter(
        CompanyWebsiteAnalysis.company_id == company_id
    ).order_by(CompanyWebsiteAnalysis.analyzed_at.desc()).first()

    stored_place_id = existing_analysis.google_place_id if existing_analysis else None

    if stored_place_id:
        # Reuse verified place_id — skip name-matching search entirely
        place_id_for_details = stored_place_id
        google_name = existing_analysis.google_name or company.name
        google_address = existing_analysis.google_address or ''
        result['steps'].append({
            'step': 'find_place',
            'status': 'complete',
            'message': f'Używam zapisanego Place ID: {google_name}'
        })
        result['data']['google_place_id'] = place_id_for_details
        result['data']['google_name'] = google_name
        result['data']['google_address'] = google_address
    else:
        # No stored place_id — search Google Maps
        result['steps'].append({
            'step': 'find_place',
            'status': 'in_progress',
            'message': f'Szukam firmy "{company.name}" w Google Maps...'
        })

        city = company.address_city or 'Wejherowo'
        search_query = f'{company.name} {city}'

        # Use Wejherowo coordinates as location bias (most companies are local)
        location_bias = {'latitude': 54.6059, 'longitude': 18.2350, 'radius': 50000.0}

        place_result = places_service.search_place(search_query, location_bias=location_bias, company_name=company.name)

        if not place_result:
            result['steps'][-1]['status'] = 'warning'
            result['steps'][-1]['message'] = 'Nie znaleziono firmy w Google Maps'
            result['error'] = 'Firma nie ma profilu Google Business lub nazwa jest inna niż w Google'
            return result

        place_id = place_result.get('id', '')
        # Places API (New) returns id without 'places/' prefix in search, but needs it for details
        if not place_id.startswith('places/'):
            place_id_for_details = place_id
        else:
            place_id_for_details = place_id.replace('places/', '')

        google_name = place_result.get('displayName', {}).get('text', '')
        google_address = place_result.get('formattedAddress', '')

        result['steps'][-1]['status'] = 'complete'
        result['steps'][-1]['message'] = f'Znaleziono: {google_name}'
        result['data']['google_place_id'] = place_id_for_details
        result['data']['google_name'] = google_name
        result['data']['google_address'] = google_address

    # Step 2: Get full place details
    result['steps'].append({
        'step': 'get_details',
        'status': 'in_progress',
        'message': 'Pobieram szczegóły wizytówki (Places API New)...'
    })

    place_data = places_service.get_place_details(
        place_id_for_details,
        include_reviews=True,
        include_photos=True,
        include_attributes=True
    )

    if not place_data:
        result['steps'][-1]['status'] = 'warning'
        result['steps'][-1]['message'] = 'Nie udało się pobrać szczegółów'
        result['error'] = 'Błąd pobierania szczegółów z Places API (New)'
        return result

    # Extract all data from Places API (New)
    google_name = place_data.get('displayName', {}).get('text', google_name)
    google_address = place_data.get('formattedAddress', google_address)
    phone = place_data.get('nationalPhoneNumber') or place_data.get('internationalPhoneNumber')
    website = place_data.get('websiteUri')
    types = place_data.get('types', [])
    primary_type = place_data.get('primaryType', '')
    maps_url = place_data.get('googleMapsUri', '')
    rating = place_data.get('rating')
    reviews_count = place_data.get('userRatingCount')
    business_status = place_data.get('businessStatus', '')
    editorial_summary = place_data.get('editorialSummary', {}).get('text', '')
    price_level = place_data.get('priceLevel', '')
    maps_links = place_data.get('googleMapsLinks', {})
    current_hours = place_data.get('currentOpeningHours', {})
    open_now = current_hours.get('openNow')

    # Extract rich data using service methods
    reviews_data = places_service.extract_reviews_data(place_data)
    attributes = places_service.extract_attributes(place_data)
    hours_data = places_service.extract_hours(place_data)
    photos_meta = places_service.extract_photos_metadata(place_data)

    photos_count = photos_meta.get('total_count', 0)

    # Build opening hours dict (backward-compatible format)
    opening_hours = {}
    if hours_data.get('regular'):
        opening_hours = {
            'weekday_text': hours_data['regular'].get('weekday_descriptions', []),
            'open_now': hours_data['regular'].get('open_now'),
            'periods': hours_data['regular'].get('periods', [])
        }

    # Store in result data (backward-compatible fields)
    result['data'].update({
        'google_name': google_name,
        'google_address': google_address,
        'google_phone': phone,
        'google_website': website,
        'google_types': types,
        'google_maps_url': maps_url,
        'google_rating': rating,
        'google_reviews_count': reviews_count,
        'google_photos_count': photos_count,
        'google_opening_hours': opening_hours,
        'google_business_status': business_status,
        # NEW fields from Places API (New)
        'google_primary_type': primary_type,
        'google_editorial_summary': editorial_summary,
        'google_price_level': price_level,
        'google_attributes': attributes,
        'google_reviews_data': reviews_data,
        'google_photos_metadata': photos_meta,
        'google_has_special_hours': hours_data.get('has_special_hours', False),
        'google_maps_links': maps_links if maps_links else None,
        'google_open_now': open_now,
    })

    result['steps'][-1]['status'] = 'complete'
    details_msg = []
    if rating:
        details_msg.append(f'Ocena: {rating}')
    if reviews_count:
        details_msg.append(f'{reviews_count} opinii')
    if photos_count:
        details_msg.append(f'{photos_count} zdjęć')
    if attributes:
        details_msg.append(f'+{sum(len(v) for v in attributes.values() if isinstance(v, dict))} atrybutów')
    result['steps'][-1]['message'] = ', '.join(details_msg) if details_msg else 'Pobrano dane'

    # OAuth: Try GBP Management API for owner-specific data
    try:
        from oauth_service import OAuthService
        from gbp_management_service import GBPManagementService

        oauth = OAuthService()
        gbp_token = oauth.get_valid_token(db, company_id, 'google', 'gbp')
        if gbp_token:
            token_record = db.query(OAuthToken).filter(
                OAuthToken.company_id == company_id,
                OAuthToken.provider == 'google',
                OAuthToken.service == 'gbp',
                OAuthToken.is_active == True,
            ).first()
            location_name = None
            if token_record and token_record.metadata_json:
                location_name = token_record.metadata_json.get('location_name')

            if location_name:
                gbp_mgmt = GBPManagementService(gbp_token)
                reviews = gbp_mgmt.get_reviews(location_name)
                if reviews:
                    owner_responses = sum(1 for r in reviews if r.get('reviewReply'))
                    result['data']['google_owner_responses_count'] = owner_responses
                    result['data']['google_total_reviews_with_replies'] = len(reviews)
                    result['data']['google_review_response_rate'] = round(
                        owner_responses / len(reviews) * 100, 1
                    ) if reviews else 0

                posts = gbp_mgmt.get_local_posts(location_name)
                if posts:
                    result['data']['google_posts_count'] = len(posts)
                    result['data']['google_posts_data'] = posts[:10]

                logger.info(f"OAuth GBP enrichment: {len(reviews or [])} reviews, {len(posts or [])} posts for company {company_id}")
    except Exception as e:
        logger.warning(f"OAuth GBP enrichment failed for company {company_id}: {e}")

    # OAuth: Try GBP Performance API for visibility metrics
    try:
        from gbp_performance_service import GBPPerformanceService

        if gbp_token and location_name:
            perf_service = GBPPerformanceService(gbp_token)
            # Extract location ID from location_name (format: accounts/X/locations/Y)
            # Performance API uses locations/Y format
            parts = location_name.split('/')
            if len(parts) >= 4:
                perf_location = f"locations/{parts[3]}"
            else:
                perf_location = location_name

            perf_data = perf_service.get_all_performance_data(perf_location, days=30)
            if perf_data:
                result['data']['gbp_impressions_maps'] = perf_data.get('maps_impressions', 0)
                result['data']['gbp_impressions_search'] = perf_data.get('search_impressions', 0)
                result['data']['gbp_call_clicks'] = perf_data.get('call_clicks', 0)
                result['data']['gbp_website_clicks'] = perf_data.get('website_clicks', 0)
                result['data']['gbp_direction_requests'] = perf_data.get('direction_requests', 0)
                result['data']['gbp_conversations'] = perf_data.get('conversations', 0)
                result['data']['gbp_search_keywords'] = perf_data.get('search_keywords', [])
                result['data']['gbp_performance_period_days'] = perf_data.get('period_days', 30)
                logger.info(f"GBP Performance data collected for company {company_id}")
    except ImportError:
        pass
    except Exception as e:
        logger.warning(f"GBP Performance API failed for company {company_id}: {e}")

    # Step 3: Save to database
    result['steps'].append({
        'step': 'save_data',
        'status': 'in_progress',
        'message': 'Zapisuję dane w bazie...'
    })

    try:
        analysis = db.query(CompanyWebsiteAnalysis).filter(
            CompanyWebsiteAnalysis.company_id == company_id
        ).first()

        if not analysis:
            analysis = CompanyWebsiteAnalysis(
                company_id=company_id,
                website_url=company.website,
                analyzed_at=datetime.now()
            )
            db.add(analysis)

        # Update Google fields (same as before)
        analysis.google_place_id = place_id_for_details
        analysis.google_name = google_name
        analysis.google_address = google_address
        analysis.google_phone = phone
        analysis.google_website = website
        analysis.google_types = types if types else None
        analysis.google_maps_url = maps_url
        analysis.google_rating = rating
        analysis.google_reviews_count = reviews_count
        analysis.google_photos_count = photos_count
        analysis.google_opening_hours = opening_hours if opening_hours else None
        analysis.google_business_status = business_status
        analysis.analyzed_at = datetime.now()

        # NEW: Save additional Places API (New) data to JSONB fields if they exist
        # Use setattr with try/except for new columns that may not exist yet
        for attr, val in [
            ('google_primary_type', primary_type),
            ('google_editorial_summary', editorial_summary),
            ('google_price_level', price_level),
            ('google_attributes', attributes if attributes else None),
            ('google_reviews_data', reviews_data if reviews_data else None),
            ('google_photos_metadata', photos_meta if photos_meta else None),
            ('google_maps_links', maps_links if maps_links else None),
            ('google_open_now', open_now),
            ('gbp_impressions_maps', result['data'].get('gbp_impressions_maps')),
            ('gbp_impressions_search', result['data'].get('gbp_impressions_search')),
            ('gbp_call_clicks', result['data'].get('gbp_call_clicks')),
            ('gbp_website_clicks', result['data'].get('gbp_website_clicks')),
            ('gbp_direction_requests', result['data'].get('gbp_direction_requests')),
            ('gbp_conversations', result['data'].get('gbp_conversations')),
            ('gbp_search_keywords', result['data'].get('gbp_search_keywords')),
            ('gbp_performance_period_days', result['data'].get('gbp_performance_period_days')),
            ('google_owner_responses_count', result['data'].get('google_owner_responses_count')),
            ('google_review_response_rate', result['data'].get('google_review_response_rate')),
            ('google_posts_data', result['data'].get('google_posts_data')),
            ('google_posts_count', result['data'].get('google_posts_count')),
        ]:
            try:
                setattr(analysis, attr, val)
            except Exception:
                pass  # Column doesn't exist yet, skip

        db.commit()

        # Flow GBP phone/website to Company if Company fields are empty
        try:
            if analysis.google_phone and not company.phone:
                company.phone = analysis.google_phone
            if analysis.google_website and not company.website:
                company.website = analysis.google_website
            update_company_data_quality(company, db)
            db.commit()
        except Exception as flow_err:
            logger.warning(f"Failed to flow GBP data to Company {company_id}: {flow_err}")
            db.rollback()

        result['steps'][-1]['status'] = 'complete'
        result['steps'][-1]['message'] = 'Dane zapisane pomyślnie'
        result['success'] = True

    except Exception as e:
        db.rollback()
        result['steps'][-1]['status'] = 'error'
        result['steps'][-1]['message'] = f'Błąd zapisu: {str(e)}'
        result['error'] = f'Błąd zapisu do bazy danych: {str(e)}'
        return result

    logger.info(
        f"Google data fetched via Places API (New) for company {company_id}: "
        f"rating={rating}, reviews={reviews_count}, photos={photos_count}, "
        f"attributes={len(attributes)} categories"
    )

    return result


# === Main for Testing ===

if __name__ == '__main__':
    import sys

    # Test the service
    logging.basicConfig(level=logging.INFO)

    # Check for --ai flag to test AI recommendations
    use_ai = '--ai' in sys.argv

    db = SessionLocal()
    try:
        # Get first active company
        company = db.query(Company).filter(Company.status == 'active').first()
        if company:
            print(f"\nAuditing company: {company.name} (ID: {company.id})")
            print("-" * 50)

            if use_ai:
                print("\n[AI MODE] Generating AI-powered recommendations...")
                result = audit_company_with_ai(db, company.id, save=False)
            else:
                result = audit_company(db, company.id, save=False)

            print(f"\nCompleteness Score: {result.completeness_score}/100")
            print(f"\nField Status:")
            for name, field in result.fields.items():
                status_icon = {'complete': '✅', 'partial': '⚠️', 'missing': '❌'}.get(field.status, '?')
                print(f"  {status_icon} {name}: {field.status} ({field.score:.1f}/{field.max_score:.1f})")

            print(f"\nRecommendations ({len(result.recommendations)}):")
            for rec in result.recommendations[:5]:
                source = rec.get('source', 'static')
                source_label = '[AI]' if source == 'ai' else '[STATIC]'
                print(f"\n  {source_label} [{rec['priority'].upper()}] {rec['field']}:")
                print(f"    {rec['recommendation']}")

                # Print AI-specific fields if present
                if rec.get('action_steps'):
                    print("    Action steps:")
                    for step in rec['action_steps']:
                        print(f"      • {step}")

                if rec.get('expected_impact'):
                    print(f"    Expected impact: {rec['expected_impact']}")
        else:
            print("No active companies found")

        print("\n" + "-" * 50)
        print("Usage: python gbp_audit_service.py [--ai]")
        print("  --ai  Generate AI-powered recommendations using Gemini")

    finally:
        db.close()