import logging
import json
import csv
import openpyxl
from django.db.models import Q
from django.http import JsonResponse
from django.shortcuts import render, redirect
from django.views.decorators.csrf import csrf_exempt
from django.contrib.auth import authenticate, login, logout
from django.contrib.auth.decorators import login_required
from django.contrib import messages
from openpyxl import Workbook
from django.http import HttpResponse
from datetime import datetime, timedelta, timezone as dt_timezone
from django.utils import timezone
from .models import Accreditation, Invite
from .utils import send_qrcode_email

logger = logging.getLogger(__name__)
TODAY = datetime.today().strftime('%Y-%m-%d')

WAT = dt_timezone(timedelta(hours=1))
ONSITE_THRESHOLD = datetime(2026, 6, 1, 7, 0, 0, tzinfo=WAT)

def index(request):
    return render(request, 'public/index.html')

def register(request):
    if request.method == 'POST':
        content_type = request.content_type or ''
        if 'application/json' in content_type:
            try:
                data = json.loads(request.body)
            except json.JSONDecodeError:
                data = request.POST
        else:
            data = request.POST

        name = data.get('fullname') or data.get('name')
        email = data.get('email')
        phone = data.get('phone')
        organisation = data.get('organisation')

        if not name or not email:
            return JsonResponse({'status': 400, 'message': 'All fields are required!'})

        try:
            guest = Invite.objects.create(
                name=name,
                email=email,
                phone=phone,
                organization=organisation,
            )

            # try:
            #     send_qrcode_email(guest)
            #     guest.is_sent = True
            #     guest.save()
            # except Exception as e:
            #     logger.error(f'Error sending confirmation email to {guest.email}: {str(e)}')

        except Exception as e:
            logger.error(f'Error creating guest: {str(e)}')
            return JsonResponse({'status': 400, 'message': 'Registration failed. Please try again.'})

        return JsonResponse({'status': 200, 'message': 'Registration completed successfully!', 'name': name})

    return JsonResponse({'status': 405, 'message': 'Method Not Allowed!'})

@login_required
def accreditation(request):
    return render(request, 'public/accreditation.html')

@login_required
def export_accredited_guests(request):
    accreditations = Accreditation.objects.filter(is_accredited=True).select_related('guest')

    wb = Workbook()
    ws = wb.active
    ws.title = "Accredited Guests"

    # Headers
    ws['A1'] = 'Name/Organization'
    ws['B1'] = 'Email Address' 
    ws['C1'] = 'Phone Number'
    ws['D1'] = 'Title/Organization'
    ws['E1'] = 'Representative'
    ws['F1'] = 'Accredited On'

    # Data
    for i, accred in enumerate(accreditations, start=2):
        ws[f'A{i}'] = accred.guest.name or ''
        ws[f'B{i}'] = accred.guest.email or ''
        ws[f'C{i}'] = accred.guest.phone or ''
        ws[f'D{i}'] = accred.guest.organization or ''
        ws[f'E{i}'] = accred.guest.representative or ''
        ws[f'F{i}'] = f'{accred.date} {accred.time}'

    # Create response
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=accredited_guests.xlsx'
    wb.save(response)

    return response

@login_required
def accredited_guest(request):
    guests = Accreditation.objects.filter(is_accredited=True).select_related('guest')
    invite_count = Invite.objects.filter(created_at__lt=ONSITE_THRESHOLD).count()
    onsite_count = Invite.objects.filter(created_at__gte=ONSITE_THRESHOLD).count()
    context = {
        'guests': guests.all(),
        'accredited_count': guests.count(),
        'invite_count': invite_count,
        'onsite_count': onsite_count,
    }
    return render(request, 'dashboard/accredited_guest.html', context=context)

# Search guests
def search_guests(request):
    """Search for guests by name or representative columns"""
    if request.method == 'GET':
        query = request.GET.get('q', '').strip()

        if len(query) >= 2:
            rsvp_results = Invite.objects.filter(
                Q(name__icontains=query) | Q(representative__icontains=query) | Q(organization__icontains=query)
            ).order_by('name')

            ticket_list = []

            # Get today's accreditations
            today = timezone.now().date()
            tomorrow = today + timedelta(days=1)
            rsvp_accreditations = {
                acc.guest_id: acc.time.strftime('%I:%M %p')
                for acc in Accreditation.objects.filter(
                    guest__in=rsvp_results,
                    date__gte=today,
                    date__lt=tomorrow,
                    is_accredited=True
                ).select_related('guest')
            }

            # Process Guest results
            for rsvp in rsvp_results:
                ticket_list.append({
                    'id': rsvp.id,
                    'name': rsvp.name,
                    'email': rsvp.email,
                    'organization': rsvp.organization or '',
                    'is_accredited': rsvp.id in rsvp_accreditations,
                    'accredit_time': rsvp_accreditations.get(rsvp.id, ''),
                })

            return JsonResponse({'tickets': ticket_list})
        else:
            return JsonResponse({'tickets': []})

    return JsonResponse({'status': 405, 'message': 'Method Not Allowed!'})

def activity_log(request):
    """Return the last 20 accreditation events from the database."""
    if request.method != 'GET':
        return JsonResponse({'status': 405, 'message': 'Method not allowed'}, status=405)
    today = timezone.now().date()
    tomorrow = today + timedelta(days=1)
    events = []
    rows = Accreditation.objects.filter(
        date__gte=today,
        date__lt=tomorrow,
        is_accredited=True
    ).select_related('guest').order_by('-date', '-time')[:20]
    for a in rows:
        events.append({
            'name': a.guest.name if a.guest else 'Unknown',
            'organization': a.guest.organization if (a.guest and a.guest.organization) else '-',
            'time': a.time.strftime('%I:%M %p'),
            'ok': True,
        })
    return JsonResponse({'events': events})

# Check in
@csrf_exempt
def check_in(request):
    if request.method == 'POST':
        data       = request.POST.get('name')
        ticket_num = request.POST.get('unique_code')
        quantity_str = request.POST.get('quantity')

        # Accept either 'name' (text search) or 'unique_code' (QR scan)
        lookup_value = ticket_num or data

        if not lookup_value:
            return JsonResponse({'status': 400, 'message': 'Please provide guest name or QR code ID'})

        # Look up Guest by unique_code (from camera) or name (manual entry)
        if ticket_num:
            rsvp = Invite.objects.filter(unique_code__iexact=ticket_num).first()
        else:
            rsvp = Invite.objects.filter(name__iexact=data).first()

        if not rsvp:
            return JsonResponse({'status': 404, 'message': 'Guest not found'})

        # Handle Guest check-in
        if rsvp:
            today = timezone.now().date()
            tomorrow = today + timedelta(days=1)
            existing_accreditation = Accreditation.objects.filter(
                guest=rsvp,
                is_accredited=True,
                date__gte=today,
                date__lt=tomorrow
            ).first()

            if quantity_str:
                if existing_accreditation:
                    return JsonResponse({
                        'status': 400,
                        'message': 'This Guest has already been checked in today',
                        'is_already_accredited': True,
                        'rsvp_id': rsvp.id,
                        'accredit_time': existing_accreditation.time.strftime('%I:%M %p'),
                        'guest': {
                            'name': rsvp.name,
                            'email': rsvp.email,
                            'organization': rsvp.organization or '',
                            'rsvp_id': rsvp.id,
                        }
                    })

                Accreditation.objects.create(
                    guest=rsvp,
                    is_accredited=True
                )

                return JsonResponse({
                    'status': 200,
                    'message': 'Guest checked in successfully',
                    'is_already_accredited': False,
                    'guest': {
                        'name': rsvp.name,
                        'email': rsvp.email,
                        'organization': rsvp.organization or '',
                        'rsvp_id': rsvp.id,
                    }
                })
            else:
                ticket_info = {
                    'name':     rsvp.name,
                    'email':    rsvp.email,
                    'rsvp_id':  rsvp.id,
                    'current_date': timezone.now().strftime('%Y-%m-%d')
                }
                return JsonResponse({'status': 200, 'ticket': ticket_info})

    return JsonResponse({'status': 405, 'message': 'Method not allowed'})

def unaccredit(request):
    """Remove accreditation for a guest (revoke their check-in)."""
    if request.method != 'POST':
        return JsonResponse({'status': 405, 'message': 'Method not allowed'})

    rsvp_id = request.POST.get('rsvp_id')
    if not rsvp_id:
        return JsonResponse({'status': 400, 'message': 'Missing rsvp_id'})

    rsvp = Invite.objects.filter(id=rsvp_id).first()
    if not rsvp:
        return JsonResponse({'status': 404, 'message': 'Guest not found'})

    Accreditation.objects.filter(guest=rsvp, is_accredited=True).delete()

    return JsonResponse({'status': 200, 'message': rsvp.name + ' has been unaccredited.'})

@login_required
def import_guest_list(request):
    if request.method == 'POST' and request.FILES.get('guest_list'):
        file = request.FILES['guest_list']
        records_count = 0

        try:
            filename = file.name.lower()
            
            # Detect file type and read accordingly
            if filename.endswith('.csv'):
                rows = _parse_csv_file(file)
                if not rows:
                    return JsonResponse({'message': 'Invalid CSV file or empty file.', 'status': 400})
                headers = rows[0]
                data_rows = rows[1:]
            elif filename.endswith(('.xlsx', '.xls')):
                wb = openpyxl.load_workbook(file)
                sheet = wb.active
                headers = list(sheet.iter_rows(min_row=1, max_row=1, values_only=True))[0]
                data_rows = [row for row in sheet.iter_rows(min_row=2, values_only=True) if any(cell is not None for cell in row)]
            else:
                return JsonResponse({'message': 'Unsupported file format. Please upload .xlsx, .xls, or .csv.', 'status': 400})

            # Normalize headers
            headers = [str(h).replace('\xa0', ' ').strip().lower() if h else '' for h in headers]
            normalized_headers = _normalize_headers(headers)

            # Detect column indices by name, falling back to positional index if not found
            name_idx = next((i for i, h in enumerate(normalized_headers) if h == 'name'), None)
            if name_idx is None:
                name_idx = 0

            org_idx = next((i for i, h in enumerate(normalized_headers) if h == 'organization'), None)
            if org_idx is None:
                org_idx = 1

            rep_idx = next((i for i, h in enumerate(normalized_headers) if h == 'representative'), None)
            if rep_idx is None:
                rep_idx = 2

            email_idx = next((i for i, h in enumerate(normalized_headers) if h == 'email'), None)
            if email_idx is None:
                email_idx = 3

            follow_up_org_idx = next((i for i, h in enumerate(normalized_headers) if h == 'follow_up_org'), None)
            if follow_up_org_idx is None:
                follow_up_org_idx = 4

            # Process data rows
            for row in data_rows:
                def get_cell_value(idx):
                    if idx is None or idx >= len(row):
                        return ''
                    val = row[idx]
                    return str(val).replace('\xa0', ' ').strip() if val is not None else ''

                name = get_cell_value(name_idx)
                organization = get_cell_value(org_idx)
                representative = get_cell_value(rep_idx)
                email = get_cell_value(email_idx)
                follow_up_org = get_cell_value(follow_up_org_idx)

                if not name:
                    continue

                # Truncate strings to prevent DB errors from too-long values
                name = name[:255]
                if organization:
                    organization = organization[:255]
                else:
                    organization = None

                if representative:
                    representative = representative[:100]
                else:
                    representative = None

                if email:
                    email = email[:254]
                else:
                    email = None

                if follow_up_org:
                    follow_up_org = follow_up_org[:20]
                else:
                    follow_up_org = None

                Invite.objects.create(
                    name=name,
                    organization=organization,
                    representative=representative,
                    email=email,
                    follow_up_org=follow_up_org
                )
                records_count += 1
                logger.info(f"<{name} inserted successfully>")

            return JsonResponse({'message': f'{records_count} records imported successfully.', 'status': 200})
        except Exception as e:
            logger.error(f"Error: {e}")
            return JsonResponse({'message': 'Something went wrong, try again.', 'status': 400})
    else:
        return JsonResponse({'message': 'Invalid request or file missing.', 'status': 400})

def _parse_csv_file(file):
    """Parse CSV file and return list of rows."""
    content = file.read().decode('utf-8-sig')
    reader = csv.reader(content.splitlines())
    return [row for row in reader]

def _normalize_headers(headers):
    """Normalize header names to standard format."""
    normalized = []
    for h in headers:
        if not h:
            normalized.append('')
            continue
        h_clean = str(h).lower().strip().replace('_', '').replace(' ', '')
        if h_clean in ['sn', 'sln', 'serial', 'no', 'no.', 'num', 'number']:
            normalized.append('s/n')
        elif h_clean in ['name', 'fullname', 'guestname']:
            normalized.append('name')
        elif h_clean in ['email', 'emailaddress', 'mail']:
            normalized.append('email')
        elif h_clean in ['organization', 'organisation', 'org']:
            normalized.append('organization')
        elif h_clean in ['representative', 'rep']:
            normalized.append('representative')
        elif h_clean in ['followuporg', 'followuporganisation', 'followuporganization']:
            normalized.append('follow_up_org')
        else:
            normalized.append(h_clean)
    return normalized

# Login
def login_view(request):
    if request.user.is_authenticated:
        return _postLoginRedirect(request.user)
    if request.method == 'POST':
        username = request.POST.get('username')
        password = request.POST.get('password')
        
        if username and password:
            user = authenticate(request, username=username, password=password)
            if user is not None:
                if user.is_staff:
                    login(request, user)
                    return _postLoginRedirect(user)
                else:
                    messages.error(request, "You do not have permission to access this dashboard.")
            else:
                messages.error(request, "Invalid credentials.")
        else:
            messages.error(request, "Invalid credentials.")

    return render(request, 'public/login.html')

def _postLoginRedirect(user):
    if user.username.lower().endswith('@accredit.com'):
        return redirect('accreditation')
    return redirect('invite')

# Logout
@login_required
def logout_view(request):
    request.session.flush()
    logout(request)
    return redirect('login')

@login_required
def export_invites(request):
    guests = Invite.objects.filter(created_at__lt=ONSITE_THRESHOLD).order_by('created_at')

    wb = Workbook()
    ws = wb.active
    ws.title = "Guest List"

    # Headers
    ws['A1'] = 'Name/Organization'
    ws['B1'] = 'Email Address'
    ws['C1'] = 'Phone Number'
    ws['D1'] = 'Title/Organization'
    ws['E1'] = 'Representative'

    # Data
    for i, guest in enumerate(guests, start=2):
        ws[f'A{i}'] = guest.name
        ws[f'B{i}'] = guest.email or ''
        ws[f'C{i}'] = guest.phone or ''
        ws[f'D{i}'] = guest.organization or ''
        ws[f'E{i}'] = guest.representative or ''

    # Create response
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=Guests_List.xlsx'
    wb.save(response)

    return response

@login_required
def invite(request):
    guests = Invite.objects.filter(created_at__lt=ONSITE_THRESHOLD).order_by('-created_at')
    onsite_count = Invite.objects.filter(created_at__gte=ONSITE_THRESHOLD).count()
    context = {
        'guests': guests,
        'invite_count': guests.count(),
        'onsite_count': onsite_count,
        'accredited_count': Accreditation.objects.filter(is_accredited=True).count(),
    }
    return render(request, 'dashboard/invite.html', context=context)

@login_required
def onsite(request):
    guests = Invite.objects.filter(created_at__gte=ONSITE_THRESHOLD).order_by('-created_at')
    invite_count = Invite.objects.filter(created_at__lt=ONSITE_THRESHOLD).count()
    context = {
        'guests': guests,
        'invite_count': invite_count,
        'onsite_count': guests.count(),
        'accredited_count': Accreditation.objects.filter(is_accredited=True).count(),
    }
    return render(request, 'dashboard/onsite.html', context=context)

@login_required
def export_onsite(request):
    guests = Invite.objects.filter(created_at__gte=ONSITE_THRESHOLD).order_by('created_at')

    wb = Workbook()
    ws = wb.active
    ws.title = "Onsite Registrations"

    # Headers
    ws['A1'] = 'Guest Name'
    ws['B1'] = 'Email Address'
    ws['C1'] = 'Phone Number'
    ws['D1'] = 'Organization'
    ws['E1'] = 'Representative'

    # Data
    for i, guest in enumerate(guests, start=2):
        ws[f'A{i}'] = guest.name
        ws[f'B{i}'] = guest.email or ''
        ws[f'C{i}'] = guest.phone or ''
        ws[f'D{i}'] = guest.organization or ''
        ws[f'E{i}'] = guest.representative or ''

    # Create response
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=Onsite_Registrations.xlsx'
    wb.save(response)

    return response

@login_required
def send_invite(request):
    if request.method == 'POST':
        guest_id = request.POST.get('guest_id')
        if not guest_id:
            return JsonResponse({'status': 400, 'message': 'Guest ID is required.'})

        try:
            guest = Invite.objects.get(pk=guest_id)
        except Invite.DoesNotExist:
            return JsonResponse({'status': 404, 'message': 'Guest not found.'})

        try:
            send_qrcode_email(guest)
            guest.is_sent = True
            guest.save()
            return JsonResponse({'status': 200, 'message': f'Invite sent successfully to {guest.email}.'})
        except Exception as e:
            logger.error(f'Error sending invite to {guest.email}: {str(e)}')
            return JsonResponse({'status': 500, 'message': 'Failed to send invite.'})

    return JsonResponse({'status': 405, 'message': 'Method Not Allowed!'})

@login_required
def delete_guest(request):
    if request.method == 'POST':
        guest_id = request.POST.get('guest_id')
        if not guest_id:
            return JsonResponse({'status': 400, 'message': 'Guest ID is required.'})

        try:
            guest = Invite.objects.get(pk=guest_id)
        except Invite.DoesNotExist:
            return JsonResponse({'status': 404, 'message': 'Guest not found.'})

        name = guest.name
        guest.delete()
        return JsonResponse({'status': 200, 'message': f'{name} has been deleted.'})

    return JsonResponse({'status': 405, 'message': 'Method Not Allowed!'})