"""
Management Command: Populate Database from ML Dataset

This command reads the Final__data.csv file, runs ML predictions on each record,
creates user accounts with login credentials, and stores all data in the database.

Usage:
    python manage.py populate_from_csv [--limit N] [--export-excel] [--skip-ml]

Options:
    --limit N       Limit processing to N records (for testing)
    --export-excel  Export all data to Excel after population
    --skip-ml       Skip ML service calls (niche/popularity marked unavailable, no fabricated data)
"""

import csv
import os
import re
import secrets
import string
from datetime import datetime
from decimal import Decimal
from typing import Dict, Optional, List

from django.core.management.base import BaseCommand, CommandError
from django.db import transaction
from django.contrib.auth import get_user_model

from influencers.models import InfluencerProfile
from core.services import MLClient
from core.utils.pricing import calculate_estimated_price

User = get_user_model()


class Command(BaseCommand):
    help = 'Populate database from ML dataset CSV file and generate login credentials'

    def add_arguments(self, parser):
        parser.add_argument(
            '--limit',
            type=int,
            default=None,
            help='Limit the number of records to process (for testing)'
        )
        parser.add_argument(
            '--export-excel',
            action='store_true',
            help='Export all data to Excel after population'
        )
        parser.add_argument(
            '--skip-ml',
            action='store_true',
            help='Skip ML service; niche/popularity marked Unavailable (no fabricated data)'
        )
        parser.add_argument(
            '--csv-path',
            type=str,
            default=None,
            help='Custom path to CSV file'
        )

    def sanitize_text(self, text: str) -> str:
        """
        Remove 4-byte UTF-8 characters (emojis) that MySQL utf8 doesn't support.
        MySQL's utf8 encoding only supports up to 3-byte characters.
        Emojis and some special characters require utf8mb4.
        """
        if not text:
            return ''
        # Remove characters outside the BMP (Basic Multilingual Plane)
        # These are 4-byte UTF-8 characters (codepoints > 0xFFFF)
        return ''.join(char for char in text if ord(char) < 0x10000)

    def generate_password(self, length=12) -> str:
        """Generate a secure random password."""
        chars = string.ascii_letters + string.digits
        return ''.join(secrets.choice(chars) for _ in range(length))

    def generate_email(self, username: str) -> str:
        """Generate a unique email for the user."""
        return f"{username.lower().replace('.', '_')}@sync-influencer.local"

    def parse_float(self, value, default=0.0) -> float:
        """Safely parse a float value."""
        try:
            if value is None or value == '' or str(value).lower() == 'nan':
                return default
            return float(value)
        except (ValueError, TypeError):
            return default

    def parse_bool(self, value) -> bool:
        """Safely parse a boolean value."""
        if isinstance(value, bool):
            return value
        if isinstance(value, str):
            return value.lower() in ('true', '1', 'yes')
        return bool(value)

    def get_unavailable_ml_prediction(self) -> Dict:
        """No fabricated niche/popularity when ML is skipped or returns nothing."""
        explanation = (
            "Niche and popularity predictions are not available because the ML service was not used "
            "(--skip-ml), did not respond, or returned no result."
        )
        return {
            'niche_probabilities': {},
            'popularity_probabilities': {},
            'processed_data': {
                'top_niche': 'Unavailable',
                'top_popularity': 'Unavailable',
            },
            'explanation': explanation,
        }

    def handle(self, *args, **options):
        limit = options['limit']
        export_excel = options['export_excel']
        skip_ml = options['skip_ml']
        csv_path = options['csv_path']

        # Default CSV path
        if not csv_path:
            base_dir = os.path.dirname(os.path.dirname(os.path.dirname(
                os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
            )))
            csv_path = os.path.join(
                base_dir,
                'ml_service', 'evaluation', 'influencer', 'Final__data.csv'
            )

        self.stdout.write(f"Reading CSV from: {csv_path}")

        if not os.path.exists(csv_path):
            raise CommandError(f"CSV file not found: {csv_path}")

        # Initialize ML client
        ml_client = MLClient()
        ml_available = not skip_ml and ml_client.health_check()

        if skip_ml:
            self.stdout.write(self.style.WARNING(
                "Skipping ML service: niche/popularity will be marked Unavailable (no fabricated data)."
            ))
        elif not ml_available:
            self.stdout.write(self.style.WARNING(
                "ML service not available: niche/popularity will be marked Unavailable. "
                "Start the ML service for real predictions."
            ))

        # Track created credentials
        credentials = []
        processed = 0
        skipped = 0
        errors = 0

        # Read CSV
        with open(csv_path, 'r', encoding='utf-8', errors='replace') as f:
            reader = csv.DictReader(f)

            for row in reader:
                # Check limit
                if limit and processed >= limit:
                    break

                username = row.get('username', '').strip()

                # Skip empty usernames
                if not username:
                    skipped += 1
                    continue

                # Skip if user already exists
                if User.objects.filter(username=username).exists():
                    self.stdout.write(f"  Skipping {username} (already exists)")
                    skipped += 1
                    continue

                try:
                    with transaction.atomic():
                        # Parse data
                        followers = int(self.parse_float(row.get('followers', 0)))
                        following = int(self.parse_float(row.get('following', 0)))
                        posts_count = int(self.parse_float(row.get('posts_count', 0)))
                        verified = self.parse_bool(row.get('verified', False))
                        avg_likes = self.parse_float(row.get('avg_likes', 0))
                        avg_comments = self.parse_float(row.get('avg_comments', 0))
                        avg_video_views = self.parse_float(row.get('avg_video_views', 0))
                        engagement_rate = self.parse_float(row.get('engagement_rate_%', 0))

                        # Sanitize text fields to remove emojis (MySQL utf8 doesn't support 4-byte chars)
                        biography = self.sanitize_text(row.get('biography', '') or '')
                        full_name = self.sanitize_text(row.get('full_name', '') or username)
                        recent_captions = self.sanitize_text(row.get('recent_caption', '') or '')
                        recent_hashtags = self.sanitize_text(row.get('recent_hashtags', '') or '')

                        # Skip records with 0 followers (invalid data)
                        if followers <= 0:
                            skipped += 1
                            continue

                        # Generate credentials
                        password = self.generate_password()
                        email = self.generate_email(username)

                        # Sanitize names for user creation
                        first_name = self.sanitize_text(full_name.split()[0]) if full_name else ''
                        last_name = self.sanitize_text(' '.join(full_name.split()[1:])) if full_name and len(full_name.split()) > 1 else ''

                        # Create user
                        user = User.objects.create_user(
                            username=username,
                            email=email,
                            password=password,
                            first_name=first_name[:30],  # Django User first_name max_length is 30
                            last_name=last_name[:150],   # Django User last_name max_length is 150
                            role='INFLUENCER'
                        )

                        # Get ML prediction
                        if ml_available:
                            prediction = ml_client.predict_influencer(
                                biography=biography,
                                recent_captions=recent_captions,
                                recent_hashtags=recent_hashtags,
                                followers=followers,
                                following=following,
                                posts_count=posts_count,
                                verified=verified,
                                avg_likes=avg_likes,
                                avg_comments=avg_comments,
                                avg_video_views=avg_video_views,
                                engagement_rate=engagement_rate
                            )
                            if not prediction:
                                prediction = self.get_unavailable_ml_prediction()
                        else:
                            prediction = self.get_unavailable_ml_prediction()

                        # Extract predictions
                        niche_probs = prediction.get('niche_probabilities', {})
                        popularity_probs = prediction.get('popularity_probabilities', {})
                        processed_data = prediction.get('processed_data', {})
                        top_niche = processed_data.get('top_niche', 'Unavailable')
                        top_popularity = processed_data.get('top_popularity', 'Unavailable')
                        ml_explanation = prediction.get('explanation')

                        # Calculate pricing
                        pricing = calculate_estimated_price(
                            followers=followers,
                            engagement_rate=engagement_rate,
                            verified=verified,
                            avg_likes=avg_likes,
                            avg_comments=avg_comments,
                            llm_predicted_tier=top_popularity
                        )

                        # Create influencer profile
                        profile = InfluencerProfile.objects.create(
                            user=user,
                            instagram_handle=username,
                            verification_status=verified,
                            followers_count=followers,
                            following_count=following,
                            media_count=posts_count,
                            biography=biography[:500] if biography else '',
                            full_name=full_name[:100] if full_name else '',
                            # Engagement metrics (FloatField)
                            avg_likes=avg_likes,
                            avg_comments=avg_comments,
                            ave_video_views=avg_video_views,
                            engagement_rate_percent=engagement_rate,
                            # AI Predictions
                            ai_predicted_niche=top_niche,
                            ai_predicted_popularity=top_popularity,
                            niche_probabilities=niche_probs,
                            popularity_probabilities=popularity_probs,
                            niche_classification_explanation=ml_explanation,
                            # Pricing (DecimalField)
                            estimated_price_min=Decimal(str(round(pricing['estimated_min'], 2))),
                            estimated_price_max=Decimal(str(round(pricing['estimated_max'], 2))),
                            estimated_price_avg=Decimal(str(round(pricing['estimated_avg'], 2))),
                            # Status
                            data_processing_status='COMPLETED'
                        )

                        # Store credentials
                        credentials.append({
                            'username': username,
                            'email': email,
                            'password': password,
                            'user_id': user.id,
                            'profile_id': profile.id,
                            'followers': followers,
                            'engagement_rate': engagement_rate,
                            'niche': top_niche,
                            'popularity': top_popularity,
                            'price_min': pricing['estimated_min'],
                            'price_max': pricing['estimated_max']
                        })

                        processed += 1
                        if processed % 50 == 0:
                            self.stdout.write(f"  Processed {processed} records...")

                except Exception as e:
                    errors += 1
                    self.stdout.write(self.style.ERROR(f"  Error processing {username}: {e}"))

        # Summary
        self.stdout.write("\n" + "=" * 50)
        self.stdout.write(self.style.SUCCESS(f"Database population complete!"))
        self.stdout.write(f"  Processed: {processed}")
        self.stdout.write(f"  Skipped: {skipped}")
        self.stdout.write(f"  Errors: {errors}")

        # Export to Excel if requested
        if export_excel and credentials:
            self._export_to_excel(credentials)

        # Always save credentials to a secure file
        self._save_credentials(credentials)

    def _save_credentials(self, credentials: List[Dict]):
        """Save credentials to a JSON file for reference."""
        import json
        base_dir = os.path.dirname(os.path.dirname(os.path.dirname(
            os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        )))
        output_path = os.path.join(base_dir, 'generated_credentials.json')

        with open(output_path, 'w') as f:
            json.dump(credentials, f, indent=2)

        self.stdout.write(f"\nCredentials saved to: {output_path}")

    def _export_to_excel(self, credentials: List[Dict]):
        """Export all data to an Excel file."""
        try:
            import openpyxl
            from openpyxl.styles import Font, PatternFill, Alignment
        except ImportError:
            self.stdout.write(self.style.WARNING(
                "openpyxl not installed. Install with: pip install openpyxl"
            ))
            return

        base_dir = os.path.dirname(os.path.dirname(os.path.dirname(
            os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        )))
        output_path = os.path.join(base_dir, 'verification_dataset.xlsx')

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "Influencer Data"

        # Headers
        headers = [
            'User ID', 'Profile ID', 'Username', 'Email', 'Password',
            'Followers', 'Engagement Rate (%)', 'Predicted Niche',
            'Predicted Popularity', 'Price Min ($)', 'Price Max ($)'
        ]

        # Header styling
        header_font = Font(bold=True, color='FFFFFF')
        header_fill = PatternFill(start_color='EC4899', end_color='EC4899', fill_type='solid')

        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal='center')

        # Data rows
        for row, cred in enumerate(credentials, 2):
            ws.cell(row=row, column=1, value=cred['user_id'])
            ws.cell(row=row, column=2, value=cred['profile_id'])
            ws.cell(row=row, column=3, value=cred['username'])
            ws.cell(row=row, column=4, value=cred['email'])
            ws.cell(row=row, column=5, value=cred['password'])
            ws.cell(row=row, column=6, value=cred['followers'])
            ws.cell(row=row, column=7, value=round(cred['engagement_rate'], 2))
            ws.cell(row=row, column=8, value=cred['niche'])
            ws.cell(row=row, column=9, value=cred['popularity'])
            ws.cell(row=row, column=10, value=round(cred['price_min'], 2))
            ws.cell(row=row, column=11, value=round(cred['price_max'], 2))

        # Auto-adjust column widths
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            ws.column_dimensions[column].width = min(max_length + 2, 30)

        wb.save(output_path)
        self.stdout.write(self.style.SUCCESS(f"\nExcel file exported to: {output_path}"))
