import datetime

from django.shortcuts import render, HttpResponse, redirect
from django.conf import settings
# Create your views here.
from django.contrib import messages
from visitors_app.models import user,appointment,visitors_log,gate_pass_no,company,location,website_setting,gate_pass_template
from django.contrib.auth.hashers import check_password ,make_password
from django.contrib.auth import authenticate, login
from django.core.files.images import get_image_dimensions
from django.http import JsonResponse
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.shortcuts import render
from django.template.loader import render_to_string
from datetime import  datetime,timedelta
import requests
from django.template import engines

from gate_keeper_app.checkin_checkout_whatsapps_send_notification import send_whatsapp_notification
from requests.auth import HTTPBasicAuth
def gate_keeper_visitor_verification(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_visitor_verification.html',{'username': username})

def gate_keeper_visitor_verification_page_ajax(request):
    constants = my_constants(request)
    username = request.session.get('gate_keeper')
    if not username:
        return redirect('gate_keeper')
    database_name = constants['database_name']
    
    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('user_data', {})
    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 (visitors.first_name LIKE %s OR "
            "visitors.last_name LIKE %s OR "
            "employees.first_name LIKE %s OR "
            "employees.last_name LIKE %s OR "
            "appointment.id LIKE %s OR "
            "appointment.date LIKE %s OR "
            "appointment.time LIKE %s OR "
            "appointment.purpose LIKE %s OR "
            "appointment.status LIKE %s OR "
            "visitors.uni_id LIKE %s OR "
            "appointment.visitors_type LIKE %s OR "
            "visitors.mobile LIKE %s)"
        )
        search_params = ['%' + search_value + '%'] * 12
    else:
        search_params = []

    # First SQL query to get the count of appointments
    sql_query = f"""
        SELECT
            COUNT(*) AS appointment_count
        FROM
            {database_name}.appointment
        INNER JOIN
            {database_name}.users AS visitors ON visitors.id = appointment.visitors_id
        INNER JOIN
            {database_name}.users AS employees ON employees.id = appointment.employee_id
        INNER JOIN 
            {database_name}.users AS created_by ON created_by.id = appointment.created_by
        WHERE
            visitors.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 
            appointment.*, 
            visitors.first_name AS visitors_name, 
            visitors.last_name AS visitors_last_name, 
            visitors.uni_id AS visitors_uni_id,
            visitors.image AS visitors_image,
            visitors.mobile AS visitors_mobile,
            employees.first_name AS employee_name,
            employees.last_name AS employee_last_name,
            employees.uni_id AS employee_uni_id,
            appointment.check_in_time AS start_time,
            appointment.check_out_time AS stop_time,
            created_by.first_name AS created_by_first_name,
            created_by.last_name AS created_by_last_name,
            employees.designation_id AS employees_designation_id,
            designation.allow_check_in AS employees_allow_check_in

        FROM 
            {database_name}.appointment
        INNER JOIN 
            {database_name}.users AS visitors ON visitors.id = appointment.visitors_id
        INNER JOIN 
            {database_name}.users AS employees ON employees.id = appointment.employee_id
        INNER JOIN 
            {database_name}.users AS created_by ON created_by.id = appointment.created_by
            
        LEFT JOIN 
            {database_name}.designation AS designation ON designation.id = employees.designation_id
        
    
        WHERE
            visitors.location_id = %s
            
            {search}
        ORDER BY 
            appointment.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_start_time(request, id):
    username = request.session.get('gate_keeper')
    if not username:
        return redirect('gate_keeper')
    username_id = username.get('id')
    if request.method == 'POST':
        try:
            Appointment = appointment.objects.get(id=id)
            if Appointment.check_in_time is not None:

                Appointment.status = 'check in'
                Appointment.check_in_time = date_time()
                
                Appointment.save()
                visitor_id = Appointment.visitors_id
                employee_id = Appointment.employee_id
                check_employee_useres = user.objects.get(id=employee_id)
                check_gate_user = user.objects.get(id=username_id)
                
                company_id = check_gate_user.company_id
                check_company = company.objects.get(id=company_id)
                company_name = check_company.company_name
                
                location_id = check_gate_user.location_id
                check_location = location.objects.get(id=location_id)
                company_location = check_location.location_name
                check_visitor_user = user.objects.get(id=visitor_id)

                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:
                    if check_visitor_user and check_visitor_user.mobile:
                        send_whatsapp_notification(
                            request,
                            check_visitor_user.mobile,
                            "message_1_visitor_welcome_message_upon_checkin",
                            check_employee_useres.first_name,
                            check_visitor_user.first_name,
                            company_name,
                            company_location,
                            website_settings.company_domain,
                            check_in_date,
                            check_in_time
                        )

                    if check_employee_useres and check_employee_useres.mobile:
                        send_whatsapp_notification(
                            request,
                            to_mobile = check_employee_useres.mobile,
                            template_name = "message_2_notification_to_employee_visitor_checkin",
                            employee_name = check_employee_useres.first_name,
                            visitor_name = check_visitor_user.first_name,
                            company_name = company_name,
                            company_location = company_location,
                            company_domain = website_settings.company_domain,
                            check_in_date = check_in_date,
                            check_in_time = check_in_time,
                            Purpose_of_Visit = Appointment.purpose
                        )
                return JsonResponse({'status': 'success'})
            print('yes...no......')
            return JsonResponse({'status': 'error', 'message': 'Start time already set'})
        except Appointment.DoesNotExist:
            return JsonResponse({'status': 'error', 'message': 'Appointment not found'})
    return JsonResponse({'status': 'error', 'message': 'Invalid request'})





def gate_keeper_stop_time(request, id):
    constants = my_constants(request)
    username = request.session.get('gate_keeper')
    if not username:
        return redirect('gate_keeper')
    username_id = username.get('id')
    if request.method == 'POST':
        try:
            appointmentes = appointment.objects.get(id=id)
            if appointment.check_in_time and appointment.check_out_time is not None:
                appointmentes.status = 'check out'
                appointmentes.check_out_time = date_time()
                appointmentes.save()
                visitor_id = appointmentes.visitors_id 
                employee_id = appointmentes.employee_id
                check_employee_useres = user.objects.get(id=employee_id)
                check_employee_user = user.objects.get(id=username_id)
                company_id = check_employee_user.company_id
                check_company = company.objects.get(id=company_id)
                company_name = check_company.company_name
                
                location_id = check_employee_user.location_id
                check_location = location.objects.get(id=location_id)
                company_location = check_location.location_name
                check_visitor_user = user.objects.get(id=visitor_id)

                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:
                    if check_visitor_user and check_visitor_user.mobile:
                        send_whatsapp_notification(
                            request,
                            check_visitor_user.mobile,
                            "message_3_visitor_farewell_message_upon_checkout",
                            check_employee_useres.first_name,  # employee_name
                            check_visitor_user.first_name,   # visitor_name
                            company_name,                    # company_name
                            company_location,                # company_location
                            website_settings.company_domain,         # company_domain
                            check_in_date,                    # check_in_date
                            check_in_time 
                            
                        )

                    if check_employee_useres and check_employee_useres.mobile:
                        send_whatsapp_notification(
                            request,
                            to_mobile =check_employee_useres.mobile,
                            template_name = "message_4_notification_to_employee_visitor_checkout",
                            employee_name = check_employee_useres.first_name,
                            visitor_name =check_visitor_user.first_name,
                            company_location = company_location,                        
                            check_in_date= check_in_date,
                            check_in_time = check_in_time,
                            company_name =company_name,
                            company_domain = website_settings.company_domain
                            )
                return JsonResponse({'status': 'success'})
            return JsonResponse({'status': 'error', 'message': 'Cannot stop time'})
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': f'An error occurred: {(e)}'})        
    
    return JsonResponse({'status': 'success'})




# def gate_keeper_print_gate_pass(request, id):
#     constants = my_constants(request)
#     username = request.session.get('gate_keeper')
#     if not username:
#         return redirect('gate_keeper')
#     get_pass_images = constants['GET_PASS_IMAGE']
#     database_name = constants['database_name']
#     appointmentes = get_object_or_404(appointment, id=id)
    
        
#     latest_gate_pass = gate_pass_no.objects.filter(appointment_id=id).first()
#     if latest_gate_pass:
#         latest_gate_pass.updated_at = date_time()
#         latest_gate_pass.save()
#     else:
#         latest_gate_pass = gate_pass_no.objects.order_by('-gate_pass_number').first()

#         gate_pass_nos = latest_gate_pass.gate_pass_number + 1 if latest_gate_pass else 1
#         # gate_pass_nos = str(gate_pass_nos).zfill(4)
#         new_gate_pass = gate_pass_no(
#             appointment_id=id,
#             gate_pass_number=gate_pass_nos,
#             created_at=date_time()

#         )
#         new_gate_pass.save()
#     if latest_gate_pass:
#         # Format the existing gate pass number
#         formatted_gate_pass_number = str(latest_gate_pass.gate_pass_number).zfill(4)
#     else:
#         # If there was no previous gate pass, and we created a new one
#         formatted_gate_pass_number = str(gate_pass_nos).zfill(4)
#     check_in_time = datetime.now().replace(microsecond=0)

#     formatted_check_in_time = check_in_time.strftime('%y-%m-%d %H:%M:%S')
    
#     if appointment.check_in_time:
#         pass
#     else:

#         appointmentes.check_in_time = formatted_check_in_time

#         appointmentes.save()
        
#     sql_query = f"""
#       SELECT 
#             appointment.*, 
#             visitors.first_name AS visitors_name,
#             visitors.last_name AS visitors_last_name, 
#             visitors.uni_id AS visitors_uni_id,
#             employees.first_name AS employee_name,
#             employees.last_name AS employee_last_name,
#             visitors.mobile AS visitors_mobile,
#             visitors.email AS visitors_email,
#             visitors.address AS visitors_address,
#             visitors.image AS visitors_image,
#             company.company_name AS company_name, 
#             company.address_1 AS address_1,
#             department.department_name AS department_name,
#             location.location_name AS location_name,
#             location.address AS location_address,
#             gate_pass.gate_pass_number AS gate_pass_number
            
#         FROM 
#             {database_name}.appointment
#         INNER JOIN 
#             {database_name}.users AS visitors ON visitors.id = appointment.visitors_id
#         INNER JOIN 
#             {database_name}.users AS employees ON employees.id = appointment.employee_id
#         INNER JOIN
#             {database_name}.company AS company ON company.id = employees.company_id  -- Join with company table
        
#         INNER JOIN
#             {database_name}.department AS department ON department.id = employees.department_id  -- Join with company table
            
#         INNER JOIN
#             {database_name}.location AS location ON location.id = employees.location_id  -- Join with company table
#         INNER JOIN 
#             {database_name}.gate_pass_no AS gate_pass ON gate_pass.appointment_id = appointment.id
#         WHERE
#             appointment.id = %s;
#     """
#     with connection.cursor() as cursor:
#         cursor.execute(sql_query,[id])
#         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]
        
#     context = {
#         'appointment': all_coupon_dtl,
#         'get_pass_images':get_pass_images,
#         'formatted_gate_pass_number':formatted_gate_pass_number
#     }
#     # Render the HTML content for the modal
#     if request.headers.get('x-requested-with') == 'XMLHttpRequest':

#         # If the request is AJAX, render just the part of the template needed
#         html = render_to_string('dashboard/gate_keeper_dashboard/gate_keeper_print_gate_pass.html', context, request=request)
#         return JsonResponse({'html': html})
#     # For non-AJAX requests (e.g., direct URL access)
#     return render(request, 'dashboard/gate_keeper_dashboard/gate_keeper_print_gate_pass.html', context)


def gate_keeper_print_gate_pass(request, id):
    constants = my_constants(request)
    username = request.session.get('gate_keeper')
    if not username:
        return redirect('gate_keeper')

    get_pass_images = constants['GET_PASS_IMAGE']  # ✅ e.g., 'images/logo.png'
    database_name = constants['database_name']
    Default_image = constants['DEFAULT_IMAGE']
    appointmentes = get_object_or_404(appointment, id=id)

    # Gate Pass Number Generation
    latest_gate_pass = gate_pass_no.objects.filter(appointment_id=id).first()
    if latest_gate_pass:
        latest_gate_pass.updated_at = date_time()
        latest_gate_pass.save()
    else:
        latest_gate_pass = gate_pass_no.objects.order_by('-gate_pass_number').first()
        gate_pass_nos = latest_gate_pass.gate_pass_number + 1 if latest_gate_pass else 1
        new_gate_pass = gate_pass_no(
            appointment_id=id,
            gate_pass_number=gate_pass_nos,
            created_at=date_time()
        )
        new_gate_pass.save()
        latest_gate_pass = new_gate_pass

    formatted_gate_pass_number = str(latest_gate_pass.gate_pass_number).zfill(4)

    # Set check-in time if not already set
    check_in_time = datetime.now().replace(microsecond=0)
    formatted_check_in_time = check_in_time.strftime('%y-%m-%d %H:%M:%S')
    if not appointmentes.check_in_time:
        appointmentes.check_in_time = formatted_check_in_time
        appointmentes.save()

    # Query appointment-related info
    sql_query = f"""
       SELECT 
            appointment.*, 
            visitors.first_name AS visitors_name,
            visitors.last_name AS visitors_last_name, 
            visitors.uni_id AS visitors_uni_id,
            employees.first_name AS employee_name,
            employees.last_name AS employee_last_name,
            visitors.mobile AS visitors_mobile,
            visitors.email AS visitors_email,
            visitors.address AS visitors_address,
            visitors.image AS visitors_image,
            company.company_name AS company_name, 
            company.address_1 AS address_1,
            department.department_name AS department_name,
            location.location_name AS location_name,
            location.address AS location_address,
            gate_pass.gate_pass_number AS gate_pass_number,
            location.gate_pass_template_id AS gate_pass_template_id
        FROM 
            {database_name}.appointment
        INNER JOIN 
            {database_name}.users AS visitors ON visitors.id = appointment.visitors_id
        INNER JOIN 
            {database_name}.users AS employees ON employees.id = appointment.employee_id
        INNER JOIN
            {database_name}.company AS company ON company.id = employees.company_id
        INNER JOIN
            {database_name}.department AS department ON department.id = employees.department_id
        INNER JOIN
            {database_name}.location AS location ON location.id = employees.location_id
        INNER JOIN 
            {database_name}.gate_pass_no AS gate_pass ON gate_pass.appointment_id = appointment.id
        WHERE
            appointment.id = %s;
    """
    with connection.cursor() as cursor:
        cursor.execute(sql_query, [id])
        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]

    # ✅ Get gate_pass_template_id from the data
    gate_pass_template_id = all_coupon_dtl[0].get("gate_pass_template_id") if all_coupon_dtl else None

    # ✅ Fetch gate_pass_template HTML from the gate_pass_template table
    gate_pass_template_html = ""
    if gate_pass_template_id:
        with connection.cursor() as cursor:
            cursor.execute(
                f"SELECT gate_pass_template FROM {database_name}.gate_pass_template WHERE id = %s",
                [gate_pass_template_id]
            )
            template_result = cursor.fetchone()
            if template_result:
                gate_pass_template_html = template_result[0]
    # ✅ Render gate_pass_template_html using Django template engine
    rendered_gate_pass_template = ""

    if gate_pass_template_html:
        django_engine = engines['django']
        full_template_string = "{% load static %}" + gate_pass_template_html
        template_context = {
            'formatted_gate_pass_number': formatted_gate_pass_number,
            'check_in_time': appointmentes.check_in_time,
            'check_out_time': appointmentes.check_out_time,
            'visitors_type': appointmentes.visitors_type,
            'visitors_name': f"{all_coupon_dtl[0].get('visitors_name', '')} {all_coupon_dtl[0].get('visitors_last_name', '')}".strip(),
            'visitors_last_name': all_coupon_dtl[0].get('visitors_last_name', ''),
            'visitors_image':request.build_absolute_uri(f"/user/{all_coupon_dtl[0].get('visitors_image', '')}") if all_coupon_dtl[0].get('visitors_image') else Default_image,
            'visitors_address': all_coupon_dtl[0].get('visitors_address', ''),
            'visitors_mobile': all_coupon_dtl[0].get('visitors_mobile', ''),
            'employee_name':f"{all_coupon_dtl[0].get('employee_name', '')} {all_coupon_dtl[0].get('employee_last_name', '')}".strip(),
            'company_name': all_coupon_dtl[0].get('company_name', ''),
            'department_name': all_coupon_dtl[0].get('department_name', ''),
            'location_name': all_coupon_dtl[0].get('location_name', ''),
            'location_address':all_coupon_dtl[0].get('location_address', ''),
            'appointment': all_coupon_dtl,
            'GET_PASS_IMAGE': get_pass_images  # ✅ This is used in {% static GET_PASS_IMAGE %}
        }
        rendered_gate_pass_template = django_engine.from_string(full_template_string).render(template_context)


    # ✅ Final context for outer template
    context = {
        'appointment': all_coupon_dtl,
        'get_pass_images': get_pass_images,
        'formatted_gate_pass_number': formatted_gate_pass_number,
        'gate_pass_template_html': rendered_gate_pass_template,
    }

    if request.headers.get('x-requested-with') == 'XMLHttpRequest':
        html = render_to_string('dashboard/gate_keeper_dashboard/gate_keeper_print_gate_pass.html', context,
                                request=request)
        return JsonResponse({'html': html})

    return render(request, 'dashboard/gate_keeper_dashboard/gate_keeper_print_gate_pass.html', context)