# utils.py

from django.db import connection
from .models import Record, WebsiteXPath
from django.db import IntegrityError, transaction
from django.contrib import messages
from django.http import JsonResponse



def extract_phone_numbers(text):
    import re
    # Define regex patterns for phone numbers
    patterns = [
        r'\(\d{3}\) \d{3}-\d{4}',
        r'\(\d{3}\) \d{3}\.\d{4}',
        r'\d{3}-\d{3}-\d{4}',
        r'\d{3}\.\d{3}\.\d{4}',
        r'\d{3} \d{3} \d{4}',
        r'\+?\d{1,4}[-.\s]?\(?\d{1,4}\)?[-.\s]?\d{1,4}[-.\s]?\d{1,9}',
        r'\+?\d{1,3} \d{1,4} \d{1,4} \d{4,10}'
    ]
    
    phone_numbers = []
    for pattern in patterns:
        phone_numbers.extend(re.findall(pattern, text))
    
    return phone_numbers

def extract_email(text):
    import re
    # Define regex pattern for email addresses
    email_pattern = r'[\w\.-]+@[\w\.-]+\.\w+'
    return re.findall(email_pattern, text)


def get_record_not_in_website_xpath_rawsql(start_id='', end_id=''):
    with connection.cursor() as cursor:
        # Base query
        query = """
            SELECT r.id,r.city, r.state_id, r.website
            FROM records r
            LEFT JOIN spidersweb_websitexpath wx
            ON CONVERT(r.city USING utf8mb4) COLLATE utf8mb4_unicode_ci = CONVERT(wx.city USING utf8mb4) COLLATE utf8mb4_unicode_ci
            AND CONVERT(r.state_id USING utf8mb4) COLLATE utf8mb4_unicode_ci = CONVERT(wx.state_id USING utf8mb4) COLLATE utf8mb4_unicode_ci
            WHERE wx.city IS NULL AND wx.state_id IS NULL 
            AND r.website IS NOT NULL
        """
        
        # Add conditional filters
        params = []
        if start_id:
            query += " AND r.id >= %s"
            params.append(start_id)
        if end_id:
            query += " AND r.id <= %s"
            params.append(end_id)
        
        # Add limit clause
        query += " LIMIT 1;"
        
        cursor.execute(query, params)
        row = cursor.fetchone()
        if row:
            column_names = [desc[0] for desc in cursor.description]
            result = dict(zip(column_names, row))
        else:
            result = None
    return result


def store_records_in_db(request):
    form_data = request.session.get('form_data')
    scraped_records = request.session.get('scraped_records')

    if form_data and scraped_records:
        filtered_scraped_records = {k: v for k, v in scraped_records.items() if v}

        try:
            with transaction.atomic():

                record = Record.objects.filter(city=form_data['city'], state_id=form_data['state_id']).first()

                if record:
                    # Check if the website has changed
                    if form_data['website'] != record.website:
                        # Update the website if it has changed
                        record.website = form_data['website']
                    
                    # Update other fields
                    for field, value in filtered_scraped_records.items():
                        # Check if the field exists in the model and if the value is not None
                        if hasattr(record, field) and value is not None:
                            setattr(record, field, value)
                    
                    # Save the updated record
                    record.save()

                    filtered_form_data = {k: v for k, v in form_data.items() if v and k not in ['city', 'state_id', 'website']}
                    
                    filtered_form_data['record_id'] = record.id

                    WebsiteXPath.objects.update_or_create(
                        city=form_data['city'],
                        state_id=form_data['state_id'],
                        website=form_data['website'],
                        defaults=filtered_form_data
                    )
                    
                return True

        except IntegrityError as e:
            print(f"IntegrityError: {e}")
            return False

    return False


def check_website_url(request):
    if request.method == 'POST' and request.headers.get('x-requested-with') == 'XMLHttpRequest':
        form_data = request.POST
        city = form_data.get('city')
        state_id = form_data.get('state_id')
        new_website_url = form_data.get('website')
        
        record = Record.objects.filter(city=city, state_id=state_id).first()
        
        if record:
            old_website_url = record.website
            if old_website_url != new_website_url:
                return JsonResponse({
                    'website_diff': True,
                    'old_website_url': old_website_url,
                    'new_website_url': new_website_url
                })
        
        return JsonResponse({'website_diff': False})