import datetime
from datetime import  datetime
from django.shortcuts import  redirect
from django.contrib import messages
from visitors_app.models import user,appointment,location,company,website_setting
from django.db import connection
from visitors_app.views import date_time
from django.shortcuts import get_object_or_404
from visitors_app.context_processors import my_constants
from django.template.loader import render_to_string
from django.db.models import Q
from django.core.mail import  EmailMultiAlternatives
from django.utils.html import strip_tags
from django.http import JsonResponse
from django.shortcuts import render
from django.core import signing
import requests
from gate_keeper_app.whatsapps_send_notification import whatsapps_send_notifications




def gate_keeper_user(request):
    constants = my_constants(request)
    username = request.session.get('gate_keeper')
    if not username:
        return redirect('gate_keeper')
    return render(request, 'dashboard/gate_keeper_dashboard/gate_keeper_user/gate_keeper_user.html', {
        'username': username,'function_name': 'Gate Keeper'})


def gate_keeper_user_page_ajax(request):
    constants = my_constants(request)
    database_name = constants['database_name']
    username = request.session.get('gate_keeper')
    if not username:
        return redirect('gate_keeper')

    try:
        user_email = username.get('id')
        useres = user.objects.get(id=user_email)
        user_location = useres.location_id
    except user.DoesNotExist:
        user_location = None

    user_data = constants.get('gate_keeper_data', {})
    user_id = user_data.get('id')
    start = int(request.POST.get('start', 0))
    length = int(request.POST.get('length', 10))
    search_value = request.POST.get('search[value]', '')

    search = ''
    if search_value:
        search = (
            "AND (users.first_name LIKE %s OR "
            "users.last_name LIKE %s OR "
            "users.email LIKE %s OR "
            "users.mobile LIKE %s OR "
            "users.created_at LIKE %s OR "
            "users.updated_at LIKE %s OR "
            "users.uni_id LIKE %s)"
        )
        search_params = ['%' + search_value + '%'] * 7
    else:
        search_params = []

    # First SQL query to get the count of appointments
    sql_query = f"""
       SELECT
            COUNT(*) AS users
        FROM
            {database_name}.users AS users
        LEFT  JOIN  
            {database_name}.users AS created_by ON created_by.id = users.created_by
        WHERE
            users.type = 'visitors' AND users.location_id = %s
            {search}
    """
    with connection.cursor() as cursor:
        cursor.execute(sql_query, [user_location] + search_params)
        database_all_data = cursor.fetchall()
    recordsTotal = database_all_data[0][0] if database_all_data else 0

    # Second SQL query to get detailed appointment data
    sql_query = f"""
       SELECT 
            users.*, 
            created_by.first_name AS created_by_first_name,
            created_by.last_name AS created_by_last_name 
        FROM 
            {database_name}.users
        LEFT JOIN  
            {database_name}.users AS created_by ON created_by.id = users.created_by
        WHERE
            users.type = 'visitors' AND users.location_id = %s
            {search} 
        ORDER BY 
            users.id DESC
        LIMIT %s OFFSET %s;
    """

    with connection.cursor() as cursor:
        cursor.execute(sql_query, [user_location] + search_params + [length, start])
        database_all_data = cursor.fetchall()
        columns = [col[0] for col in cursor.description]
        all_coupon_dtl = [dict(zip(columns, row)) for row in database_all_data]

    return JsonResponse({"recordsTotal": recordsTotal, "recordsFiltered": recordsTotal, 'data': all_coupon_dtl})


def gate_keeper_user_new_appointment(request, id):
    constants = my_constants(request)

    username = request.session.get('gate_keeper')

    if not username:
        return redirect('gate_keeper')

    # Fetch the appointment based on id
    all_user = get_object_or_404(user, id=id)

    user_data = constants.get('gate_keeper_data', {})
    user_id = user_data.get('id')
    user_data_gate = user.objects.get(id=user_id)
    from_email = constants['From_Email']
    try:
        user_data_gate = user.objects.get(id=user_id)
        gatekeeper_location = user_data_gate.location_id  # Get gatekeeper's location
    except user.DoesNotExist:
        gatekeeper_location = None
    all_appointmentes = appointment.objects.filter(visitors_id=id)

    all_employee = user.objects.filter(Q(type__icontains='employee'), is_active=1, location_id=gatekeeper_location)

    if request.method == "POST":
        visitors_id = user_id
        employee_id = request.POST['employee']
        date = request.POST['date']
        time = request.POST['time']
        purpose = request.POST['Purpose']
        visitors_type = request.POST['visitors_type']
        detail = request.POST['detail']
        visitors_timing = request.POST['visitors_timing']
        visitor_company_name = request.POST['visitor_company_name']
        device = request.POST['devices']
        if employee_id == '' or employee_id == 'Choose employee' or date == '' or time == '' or purpose == '' or visitors_type == 'Choose visitors type' or visitors_type == '':
            messages.error(request, "All fields are required.", extra_tags="danger")
            return render(request,
                          'dashboard/gate_keeper_dashboard/gate_keeper_user/gate_keeper_user_appointment_add.html', {
                              'username': username, 'all_employee': all_employee,'all_appointment':all_appointmentes
                          })
        try:
            appointment_obj = appointment.objects.create(
                visitors_id=id,
                employee_id=employee_id,
                date=date,
                time=time,
                status='pending',  # Assuming 'scheduled' is the default status
                created_at=date_time(),
                purpose=purpose,
                visitors_type=visitors_type,
                detail=detail,
                created_by=user_id,
                visitors_timing=visitors_timing,
                employee_approval=None,
                visitor_company_name=visitor_company_name,
                devices=device


            )
            appointment_obj.request_id = f"{datetime.now().strftime('VMS%d%m%Y%H%M')}{appointment_obj.id}"
            appointment_obj.save(update_fields=['request_id'])
            # appointment_obj.save()

            visitor = user.objects.filter(id=id).first()

            employee = user.objects.filter(id=employee_id).first()

            if employee != '':
                try:
                    appointment_id = appointment_obj.id
                    encrypted_appointment_id = signing.dumps(appointment_id)
                    base_url = request.build_absolute_uri('/')[:-1]
                    subject_employee = 'New Visitor Appointment'
                    context_employee = {
                        'employee_data': employee,
                        'visitor_data': visitor,
                        'new_appointment': appointment_obj,
                        'accept_url': f"{base_url}/accept/{encrypted_appointment_id}",
                        'reject_url': f"{base_url}/reject/{encrypted_appointment_id}",
                    }

                    template_name_employee = 'notification_email/employee_notification_email.html'

                    message_employee = render_to_string(template_name_employee, context_employee)
                    plain_message_employee = strip_tags(message_employee)
                    recipient_list_employee = [employee.email]

                    mail_employee = EmailMultiAlternatives(subject_employee, plain_message_employee, from_email,
                                                           recipient_list_employee)
                    mail_employee.attach_alternative(message_employee, "text/html")
                    mail_employee.send()
                except Exception as e:
                    print('eamil_send_error', e)
                    messages.error(request, f"Error: {e}", extra_tags="danger")
            try:
                if employee.mobile != '' or visitor.mobile != '':
                    # visitor_name = new_user.first_name
                    visitor_name = visitor.first_name
                    if visitor_name == '':
                        visitor_name = '(visitor name)'
                    else:
                        visitor_name = visitor_name

                    visitor_mobile = visitor.mobile

                    if visitor_mobile == '':
                        visitor_mobile = None
                    else:
                        visitor_mobile = visitor_mobile
                    invitation_link = f"{base_url}/register/{encrypted_appointment_id}"
                    employee_mobile = employee.mobile
                    employee_name = employee.first_name
                    visitor_name = visitor_name if visitor_name else 'Default Visitor Name'
                    accept_url = f"{base_url}/accept/{encrypted_appointment_id}"
                    reject_url = f"{base_url}/reject/{encrypted_appointment_id}"
                    purpose_of_visit = appointment_obj.purpose

                    company_id = user_data_gate.company_id
                    check_company = company.objects.get(id=company_id)
                    company_name = check_company.company_name

                    location_id = user_data_gate.location_id
                    check_location = location.objects.get(id=location_id)
                    company_location = check_location.location_name

                    check_in_date = datetime.today().strftime("%d-%m-%Y")
                    check_in_time = datetime.now().strftime("%H:%M")
                    website_settings = website_setting.objects.first()
                    if website_settings.whatsapp_notification == 1:
                        company_website = website_settings.company_domain
                        whatsapps_send_notifications(request, employee_mobile, employee_name, visitor_name, accept_url,
                                                     reject_url, purpose_of_visit, company_name, company_location,
                                                     check_in_date, check_in_time, company_website, visitor_mobile,
                                                     invitation_link)
            except requests.exceptions.RequestException as e:
                messages.error(request, f"Error: {e}", extra_tags="danger")

            if visitor and visitor.email:
                try:
                    appointment_id = appointment_obj.id
                    encrypted_appointment_id = signing.dumps(appointment_id)
                    confirmation_link = f"{base_url}/register/{encrypted_appointment_id}"
                    subject_visitor = 'Appointment Confirmation'
                    context_visitor = {
                        'visitor_data': visitor,
                        'employee_data': employee,
                        'new_appointment': appointment_obj,
                        'confirmation_link': confirmation_link,
                        'is_link': False
                    }

                    template_name_visitor = 'dashboard/admin_dashboard/admin_notifitation/visitor_confirmation_email.html'
                    message_visitor = render_to_string(template_name_visitor, context_visitor)
                    plain_message_visitor = strip_tags(message_visitor)
                    recipient_list_visitor = [visitor.email]
                    mail_visitor = EmailMultiAlternatives(subject_visitor, plain_message_visitor, from_email,
                                                          recipient_list_visitor)
                    mail_visitor.attach_alternative(message_visitor, "text/html")
                    mail_visitor.send()

                except Exception as e:
                    print(f"Email to visitor error: {e}")

            messages.success(request, "Appointment successfully created.", extra_tags="success")
            return redirect('gate_keeper_visitor_verification')
        except Exception as e:
            messages.error(request, f"An error occurred: {e}", extra_tags="danger")

    return render(request, 'dashboard/gate_keeper_dashboard/gate_keeper_user/gate_keeper_user_appointment_add.html',
                  {'username': username, 'all_employee': all_employee, 'user_id': user_id, 'all_user': all_user,
                   'all_appointment': all_appointmentes,'function_name': 'Gate Keeper'})