"""
MySQL Sync module for pushing scraped data to remote MySQL server via Laravel API.
Handles batch upserts with conflict resolution on item_id.
"""

import logging
import requests
from datetime import datetime, timezone
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry
from typing import List, Dict, Any

logger = logging.getLogger(__name__)


class MySQLSync:
    """Sync scraped contracts from SQLite to remote MySQL via Laravel API."""

    def __init__(self, api_url: str, api_token: str):
        """
        Initialize the sync client.

        Args:
            api_url: Base URL of the Laravel API (e.g., 'https://your-server.com')
            api_token: API token for authentication
        """
        self.api_url = api_url.rstrip('/')
        self.api_token = api_token
        self.batch_size = 100
        self.session = requests.Session()
        self.session.mount("https://", HTTPAdapter(max_retries=Retry(
            total=3, backoff_factor=1, status_forcelist=[429, 500, 502, 503, 504],
            allowed_methods=["POST"],
        )))

    def push_contracts(self, contracts: List[Dict[str, Any]]) -> Dict[str, int]:
        """
        Push contracts to the remote MySQL server via API.

        Args:
            contracts: List of contract dictionaries from SQLite

        Returns:
            Dict with 'inserted', 'updated', 'failed' counts
        """
        if not contracts:
            logger.info("No contracts to sync.")
            return {'inserted': 0, 'updated': 0, 'failed': 0}

        total_inserted = 0
        total_updated = 0
        total_failed = 0

        # Clean contract data before sending
        cleaned_contracts = []
        for c in contracts:
            cleaned = {
                'item_id': c.get('item_id', ''),
                'contract_number': c.get('contract_number', ''),
                'reference_number': c.get('reference_number', ''),
                'description': c.get('description', ''),
                'type_category': c.get('type_category', ''),
                'city': c.get('city', ''),
                'status': c.get('status', ''),
                'publication_date': c.get('publication_date', ''),
                'closing_date': c.get('closing_date', ''),
                'winning_bidder': c.get('winning_bidder', ''),
                'contract_price': c.get('contract_price', ''),
                'scraped_at': c.get('scraped_at', ''),
                'last_seen_at': c.get('last_seen_at') or datetime.now(timezone.utc).isoformat(),
                'detail_scraped_at': c.get('detail_scraped_at', ''),
                'source_url': c.get('source_url', ''),
            }

            # Include submissions if present
            submissions = c.get('submissions', [])
            if submissions:
                cleaned['submissions'] = []
                for s in submissions:
                    cleaned['submissions'].append({
                        'supplier_name': s.get('supplier_name', ''),
                        'supplier_address': s.get('supplier_address', ''),
                        'neq': s.get('neq', ''),
                        'contact': s.get('contact', ''),
                        'bid_amount': s.get('bid_amount', ''),
                        'is_winner': s.get('is_winner', False),
                        'contract_date': s.get('contract_date', ''),
                        'contract_publication_date': s.get('contract_publication_date', ''),
                        'contract_amount': s.get('contract_amount', ''),
                        'final_publication_date': s.get('final_publication_date', ''),
                        'final_end_date': s.get('final_end_date', ''),
                        'renewal_options_exercised': s.get('renewal_options_exercised', 0),
                        'additional_acquisition_options': s.get('additional_acquisition_options', 0),
                        'total_paid_amount': s.get('total_paid_amount', ''),
                        'supplementary_info': s.get('supplementary_info', ''),
                        'source_tab': s.get('source_tab', 'ouverture'),
                        'scraped_at': s.get('scraped_at', ''),
                    })

            cleaned_contracts.append(cleaned)

        # Process in batches
        for i in range(0, len(cleaned_contracts), self.batch_size):
            batch = cleaned_contracts[i:i + self.batch_size]
            batch_num = (i // self.batch_size) + 1
            total_batches = (len(cleaned_contracts) + self.batch_size - 1) // self.batch_size

            logger.info(f"Pushing batch {batch_num}/{total_batches} ({len(batch)} contracts)...")

            try:
                response = self.session.post(
                    f"{self.api_url}/api/contracts/sync",
                    json={'contracts': batch},
                    headers={
                        'Content-Type': 'application/json',
                        'Accept': 'application/json',
                        'X-API-Token': self.api_token,
                    },
                    timeout=120,
                )

                if response.status_code == 200:
                    result = response.json()
                    total_inserted += result.get('inserted', 0)
                    total_updated += result.get('updated', 0)
                    subs_ins = result.get('submissions_inserted', 0)
                    logger.info(
                        f"Batch {batch_num} success: {result.get('inserted')} inserted, "
                        f"{result.get('updated')} updated, {subs_ins} submissions"
                    )
                else:
                    total_failed += len(batch)
                    logger.error(f"Batch {batch_num} failed: HTTP {response.status_code}")
                    logger.error(f"Response: {response.text[:500]}")

            except requests.exceptions.Timeout:
                total_failed += len(batch)
                logger.error(f"Batch {batch_num} timed out after 120s")
            except requests.exceptions.ConnectionError as e:
                total_failed += len(batch)
                logger.error(f"Batch {batch_num} connection failed: {e}")
            except requests.exceptions.RequestException as e:
                total_failed += len(batch)
                logger.error(f"Batch {batch_num} request failed: {e}")

        logger.info(
            f"Sync completed: {total_inserted} inserted, {total_updated} updated, "
            f"{total_failed} failed"
        )

        return {
            'inserted': total_inserted,
            'updated': total_updated,
            'failed': total_failed,
        }