import mercantile
from typing import List, Tuple
from .utils import skip_tiles, tiles_to_latlon
from .models import TilePoint, JobCreateRequest
import requests
import json
from datetime import datetime
from pymongo import MongoClient
from src.database import init_db,engine
from src.config import mongo_settings, app_settings,stadia_settings, n8n_settings
import uuid
import random
import string
from src.enrichment.service import EnrichmentService
from src.enrichment.models import EnrichedLead
from src.outreach.email_generator import EmailBatchGenerator
from src.emailCampaigns.service import WoodpeckerService
from src.emailCampaigns.models import CampaignCreateRequest, CampaignSettings, CampaignStep, FollowupEmail, EmailBody, EmailVersion
from src.database import init_db, engine, SessionLocal
import pandas as pd
class TileGenerator:
    def __init__(self):
        self.mongo_client = MongoClient(mongo_settings.uri)
        self.db = self.mongo_client[mongo_settings.db]
        self.collection = self.db[mongo_settings.collection]
        self.enrichment_service = EnrichmentService()
        self.email_generator = EmailBatchGenerator()
        self.woodpecker_service = WoodpeckerService()
        # Stadia Settings
        self.api_key = stadia_settings.map_api_key
        init_db()

    def generate_tiles(self, city: str, country: str, zoom: int) -> List[Tuple[int,int,int]]:
        # 1. Geocode via Stadia Maps
        geocode_url = f"https://api.stadiamaps.com/geocoding/v1/search?text={city}&api_key={self.api_key}&size=1&boundary.country={country}"
        try:
            resp = requests.get(geocode_url)
            resp.raise_for_status()
            data = resp.json()
            
            if not data.get("features"):
                raise ValueError(f"City not found: {city}")
            
            # 2. Get Bounding Box [min_lon, min_lat, max_lon, max_lat]
            bbox = data["features"][0].get("bbox")
            if not bbox:
                lon, lat = data["features"][0]["geometry"]["coordinates"]
                bbox = [lon - 0.01, lat - 0.01, lon + 0.01, lat + 0.01]
            
            # 3. Generate tiles using mercantile (standard XYZ)
            tiles = list(mercantile.tiles(bbox[0], bbox[1], bbox[2], bbox[3], [13]))
            return [(t.x, t.y, t.z) for t in tiles]
            
        except Exception as e:
            raise ValueError(f"Failed to generate tiles for '{city}': {e}")

    def generate_tile_points(self, city: str, country: str, zoom: int = 15, step: int = 3) -> Tuple[int, int, List[TilePoint]]:
        tiles = self.generate_tiles(city, country, zoom)
        skipped = skip_tiles(tiles, step)
        points = tiles_to_latlon(skipped)
        return len(tiles), len(skipped), points

    async def create_jobs_from_tiles(self, request: JobCreateRequest, user_id: str) -> dict:
        _, _, points = self.generate_tile_points(request.city, request.country, request.zoom, request.step)
        run_id = str(uuid.uuid4())
        campaign_name = request.campaign_name or (
            "campaign_" + ''.join(random.choices(string.ascii_uppercase + string.digits, k=10))
        )        
        
        woodpecker_campaign_id = None
        try:
            active_mailbox = await self.woodpecker_service.get_active_mailbox(user_id)
            if active_mailbox:
                wp_request = CampaignCreateRequest(
                    name=campaign_name,
                    email_account_ids=[active_mailbox.id],
                    settings=CampaignSettings(
                        timezone="Europe/Warsaw",
                        prospect_timezone=True,
                        daily_enroll=100
                    ),
                    steps=CampaignStep(
                        type="START",
                        followup=FollowupEmail(
                            type="EMAIL",
                            delivery_time={
                                "MONDAY": [{"from": "09:00", "to": "18:00"}],
                                "TUESDAY": [{"from": "09:00", "to": "18:00"}],
                                "WEDNESDAY": [{"from": "09:00", "to": "18:00"}],
                                "THURSDAY": [{"from": "09:00", "to": "18:00"}],
                                "FRIDAY": [{"from": "09:00", "to": "18:00"}]
                            },
                            body=EmailBody(
                                versions=[
                                    EmailVersion(
                                        subject="{{SNIPPET_2}}",
                                        message="{{SNIPPET_1}}"
                                    )
                                ]
                            )
                        )
                    )
                )
                wp_campaign = await self.woodpecker_service.create_campaign(user_id, wp_request)
                woodpecker_campaign_id = wp_campaign.id
                print(f"Created Woodpecker campaign {woodpecker_campaign_id} for run {run_id}")
            else:
                print(f"No active Woodpecker mailbox found for user {user_id}. Skipping automatic campaign creation.")
        except Exception as e:
            print(f"Failed to create Woodpecker campaign for run {run_id}: {e}")

        run_doc = {
            "run_id": run_id,
            "user_id": user_id,
            "campaign_name": campaign_name,
            "woodpecker_campaign_id": woodpecker_campaign_id,
            "city": request.city,
            "keywords": request.keywords,
            "created_at": datetime.now(),
            "job_ids": [],
            "status": "started"
        }
        self.collection.insert_one(run_doc)
        
        # Insert default AI prompt for the newly created run
        prompt_doc = {
            "user_id": user_id,
            "run_id": run_id,
            "system_instruction": self.email_generator.system_instruction,
            "user_instruction": self.email_generator.user_instruction,
            "rag_context": self.email_generator.rag_context,
            "is_universal": False,
            "created_at": datetime.now(),
            "updated_at": datetime.now()
        }
        self.db["ai_prompts"].insert_one(prompt_doc)
        
        job_ids = []
        scraper_url = app_settings.scraper_url
        
        for point in points:
            payload = {
                "name": f"{campaign_name} - {point.lat}, {point.lon}",
                "keywords": request.keywords,
                "lang": "en",
                "zoom": 15,
                "lat": str(point.lat),
                "lon": str(point.lon),
                "fast_mode": False,
                "depth": 10,
                "email": True,
                "max_time": request.max_time,
                "proxies": request.proxies
            }
            
            try:
                response = requests.post(scraper_url, json=payload)
                response.raise_for_status()
                job_id = response.json().get("id")
                if job_id:
                    job_ids.append(job_id)
                    self.collection.update_one({"run_id": run_id}, {"$push": {"job_ids": job_id}})
            except Exception as e:
                print(f"Failed to create job for point {point.lat}, {point.lon}: {e}")
        
        self.collection.update_one({"run_id": run_id}, {"$set": {"status": "completed", "updated_at": datetime.now()}})
        return {"run_id": run_id, "campaign_name": campaign_name, "job_ids": job_ids}

    def get_all_runs(self, user_id: str) -> List[dict]:
        runs = list(self.collection.find({"user_id": user_id}).sort("created_at", -1))
        for run in runs:
            run["_id"] = str(run["_id"])
        return runs

    def get_run_by_id(self, run_id: str, user_id: str) -> dict:
        run = self.collection.find_one({"run_id": run_id, "user_id": user_id})
        if run:
            run["_id"] = str(run["_id"])
        return run


    def sync_completed_jobs(self):
        # Find runs that have job_ids but are not fully synced
        runs = self.collection.find({
            "job_ids": {"$exists": True, "$not": {"$size": 0}},
            "$expr": {"$lt": [{"$size": {"$ifNull": ["$synced_job_ids", []]}}, {"$size": "$job_ids"}]}
        })

        scraper_url = app_settings.scraper_url
        webhook_url = n8n_settings.webhook_url

        for run in runs:
            hit_webhook = False
            run_id = run["run_id"]
            campaign_name = run["campaign_name"]
            job_ids = run["job_ids"]
            synced_job_ids = run.get("synced_job_ids", [])
            
            # Initialize Stats for this specific Run
            run_stats = {
                "run_id": run_id,
                "total_completed_jobs": 0,
                "extracted_leads": 0,
                "leads_with_email": 0,
                "leads_with_phone": 0,
                "leads_with_email_and_phone": 0,
                "leads_with_single_email": 0,
                "leads_with_single_phone": 0,
            }

            for job_id in job_ids:
                if job_id in synced_job_ids:
                    # If already synced, we should still count it towards total if you want 
                    # stats for the WHOLE run. If not, remove this logic.
                    run_stats["total_completed_jobs"] += 1
                    continue

                try:
                    status_url = f"{scraper_url}/{job_id}"
                    response = requests.get(status_url)
                    if response.json().get("Status") == "ok":
                        download_url = f"{scraper_url}/{job_id}/download"
                        df = pd.read_csv(download_url)                        
                        # Basic Clean
                        for col in df.select_dtypes(include=['uint64']).columns:
                            df[col] = df[col].astype('int64')

                        # df.to_sql('job_results', engine, if_exists='append', index=False)

                        # Process each lead and aggregate stats
                        for _, row in df.iterrows():
                            lead_data = row.to_dict()
                            lead_data['run_id'] = run_id
                            lead_data['user_id'] = run.get('user_id', '')
                            lead_data['job_id'] = job_id
                            lead_data['campaign_name'] = campaign_name
                            # Enrich the lead
                            result = self.enrichment_service.process_lead(lead_data)                            
                            if result["existing_lead"] == False:
                                run_stats["extracted_leads"] += 1
                                enriched = result["data"]
                                has_email = bool(enriched.email)
                                # Email generation is now handled by a separate background job after approval
                                # if has_email:
                                #     self.email_generator.generate_emails_for_lead(enriched)
                                    
                                has_phone = bool(enriched.whatsapp_number)
                                has_extra_phones = len(enriched.additional_phone_numbers) > 0

                                # Increment Stats
                                if has_email: run_stats["leads_with_email"] += 1
                                if has_phone: run_stats["leads_with_phone"] += 1
                                if has_email and has_phone: run_stats["leads_with_email_and_phone"] += 1
                                
                                # Logic for "Single" (strictly one)
                                if has_email: # Based on your model, email is a single field
                                    run_stats["leads_with_single_email"] += 1
                                if has_phone and not has_extra_phones:
                                    run_stats["leads_with_single_phone"] += 1

                        # Update DB tracking
                        self.collection.update_one(
                            {"run_id": run_id},
                            {"$push": {"synced_job_ids": job_id}}
                        )

                        try:
                            requests.delete(status_url)
                        except Exception as e:
                            print(f"Error deleting job {job_id}: {e}")

                        hit_webhook = True
                        run_stats["total_completed_jobs"] += 1

                except Exception as e:
                    print(f"Error processing job {job_id}: {e}")
            
            # Moved inside the run loop to fix UnboundLocalError and ensure per-run notifications
            if hit_webhook:
                try:
                    requests.post(webhook_url, json=run_stats, timeout=10)
                    print(f"Webhook sent for run {run_id}")
                except Exception as e:
                    print(f"Failed to hit webhook: {e}")

                    
    def update_lead_status(self, lead_id: int, status: str, user_id: str):
        with SessionLocal() as db:
            lead = db.query(EnrichedLead).filter(EnrichedLead.id == lead_id, EnrichedLead.user_id == user_id).first()
            if not lead:
                return None
            lead.email_generation_status = status
            lead.updated_at = datetime.utcnow()
            db.commit()
            db.refresh(lead)
            return lead

    def bulk_update_lead_status(self, lead_ids: List[int], status: str, user_id: str):
        with SessionLocal() as db:
            updated_count = db.query(EnrichedLead).filter(
                EnrichedLead.id.in_(lead_ids), 
                EnrichedLead.user_id == user_id
            ).update({EnrichedLead.email_generation_status: status, EnrichedLead.updated_at: datetime.utcnow()}, synchronize_session=False)
            db.commit()
            return updated_count

    def update_run_status(self, run_id: str, status: str, user_id: str):
        with SessionLocal() as db:
            updated_count = db.query(EnrichedLead).filter(
                EnrichedLead.run_id == run_id, 
                EnrichedLead.user_id == user_id
            ).update({EnrichedLead.email_generation_status: status, EnrichedLead.updated_at: datetime.utcnow()}, synchronize_session=False)
            db.commit()
            return updated_count