import base64
import datetime
from io import BytesIO
import qrcode
from django.shortcuts import redirect
from django.template.loader import render_to_string
from datetime import  datetime
from gate_keeper_app.checkin_checkout_whatsapps_send_notification import send_whatsapp_notification
from visitors_app.models import user,appointment,visitors_log,company,location,website_setting
from django.http import JsonResponse
from django.db import connection
from django.shortcuts import get_object_or_404
from visitors_app.context_processors import my_constants
from django.shortcuts import render
from visitors_app.views import date_time
from gate_keeper_app.checkin_checkout_whatsapps_send_notification import send_whatsapp_notification
import requests
from django.template import engines
from django.utils import timezone

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,'function_name': 'Gate Keeper'})


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', {})
    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 = ''
    search_params = []
    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

    # Query to count records
    sql_query_count = 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_count, [user_location] + search_params)
        result = cursor.fetchone()
        recordsTotal = result[0] if result else 0

    # Query to fetch paginated appointment data
    sql_query_data = 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,
            employees.approval_require 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_data, [user_location] + search_params + [length, start])
        rows = cursor.fetchall()
        columns = [col[0] for col in cursor.description]
        all_coupon_dtl = [dict(zip(columns, row)) for row in rows]

    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()
                # visitors_loges = visitors_log.objects.create(appointment_id=id,start_time=date_time(),created_at=date_time())
                Appointment.save()
                visitor_id = Appointment.visitors_id
                employee_id = Appointment.employee_id

                check_employee_user = 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_user.first_name,
                            check_visitor_user.first_name,
                            company_name,
                            company_location,
                            website_settings.company_domain,
                            check_in_date,
                            check_in_time
                        )

                    if check_employee_user and check_employee_user.mobile:
                        send_whatsapp_notification(
                            request,
                            to_mobile=check_employee_user.mobile,
                            template_name="message_2_notification_to_employee_visitor_checkin",
                            employee_name=check_employee_user.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'})
            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_gate_user = user.objects.get(id=username_id)
#                 check_employee_user = user.objects.get(id=employee_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_3_visitor_farewell_message_upon_checkout",
#                             check_employee_user.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_user and check_employee_user.mobile:
#                         send_whatsapp_notification(
#                             request,
#                             to_mobile=check_employee_user.mobile,
#                             template_name="message_4_notification_to_employee_visitor_checkout",
#                             employee_name=check_employee_user.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_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:
            # ================= GET APPOINTMENT =================
            appointmentes = appointment.objects.get(id=id)

            # already checked out?
            if appointmentes.check_out_time:
                return JsonResponse({
                    'status': 'error',
                    'message': 'Already checked out'
                })

            # ================= READ DATA FROM AJAX =================
            time_type = request.POST.get('time_type')
            manual_datetime = request.POST.get('manual_datetime')
            if time_type == 'manual' and manual_datetime:
                manual_datetime_chnage= manual_datetime.split(" ")[0]
                today = datetime.now().date()
                if manual_datetime_chnage == str(today):

                    secoend_add = datetime.now().strftime("%S")
                else:
                    secoend_add = "00"
                dt = datetime.strptime(manual_datetime, "%Y-%m-%d %H:%M")

                # Format to desired short style
                checkout_time = dt.strftime("%y-%m-%d %H:%M")
                checkout_time = f"{checkout_time +':' +secoend_add}"
            else:
                checkout_time = datetime.now().strftime("%y-%m-%d %H:%M:%S")

            appointmentes.check_out_time = checkout_time
            appointmentes.status = 'check out'
            appointmentes.save()


            # ================= FETCH RELATED DATA =================
            visitor_id = appointmentes.visitors_id
            employee_id = appointmentes.employee_id

            check_gate_user = user.objects.get(id=username_id)
            check_employee_user = user.objects.get(id=employee_id)
            check_visitor_user = user.objects.get(id=visitor_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_in_date = checkout_time.split(" ")[0]
            check_in_time = checkout_time.split(" ")[1]

            website_settings = website_setting.objects.first()

            # ================= WHATSAPP NOTIFICATION =================
            if website_settings and 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_user.first_name,
                        check_visitor_user.first_name,
                        company_name,
                        company_location,
                        website_settings.company_domain,
                        check_in_date,
                        check_in_time
                    )

                if check_employee_user and check_employee_user.mobile:
                    send_whatsapp_notification(
                        request,
                        to_mobile=check_employee_user.mobile,
                        template_name="message_4_notification_to_employee_visitor_checkout",
                        employee_name=check_employee_user.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'})

        except appointment.DoesNotExist:
            return JsonResponse({
                'status': 'error',
                'message': 'Appointment not found'
            })

        except Exception as e:
            return JsonResponse({
                'status': 'error',
                'message': str(e)
            })

    return JsonResponse({
        'status': 'error',
        'message': 'Invalid request'
    })

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')
    pass_type = request.GET.get('pass_type')
    get_pass_images = constants['GET_PASS_IMAGE']
    database_name = constants['database_name']
    Default_image = constants['DEFAULT_IMAGE']
    appointmentes = get_object_or_404(appointment, id=id)
    check_in_time = datetime.now().replace(microsecond=0)

    # # Format the date to YY-MM-DD HH:MM:SS
    formatted_check_in_time = check_in_time.strftime('%y-%m-%d %H:%M:%S')

    if appointment.check_in_time:
        pass
    else:
        #     # Set the check-in time
        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,
            department.department_color_code AS department_color_code,
            location.location_name AS location_name,
            location.address AS location_address,
            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  -- 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
        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]
    # QR Code Expiry Logic
    qr_expired = False
    now = datetime.now()
    today_formatted = now.strftime('%Y-%m-%d')

    appointment_date = appointmentes.date


    appointment_checkout = appointmentes.check_out_time
    if appointment_date != today_formatted and appointment_checkout is not None:
        qr_expired = True
    # Generate QR code for the visitor details URL
    if not qr_expired:
        visitor_url = request.build_absolute_uri(f'/visitor_details/{id}/')
        qr = qrcode.QRCode(
            version=1,
            error_correction=qrcode.constants.ERROR_CORRECT_L,
            box_size=10,
            border=1,
        )
        qr.add_data(visitor_url)
        qr.make(fit=True)
        # Create QR code image
        img_buffer = BytesIO()
        qr_img = qr.make_image(fill_color="black", back_color="white")
        qr_img.save(img_buffer, format='PNG')
        qr_code_base64 = base64.b64encode(img_buffer.getvalue()).decode()

    else:
        qr_code_base64 = None

    # ✅ 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': f"VMS{id}",
            '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 %}
            'pass_type': pass_type,
            'qr_code_base64': qr_code_base64,
            'qr_expired': qr_expired,
        }
        rendered_gate_pass_template = django_engine.from_string(full_template_string).render(template_context)

    context = {
        'appointment': all_coupon_dtl,
        'get_pass_images': get_pass_images,
        'formatted_gate_pass_number':  f"VMS{id}",
        'gate_pass_template_html': rendered_gate_pass_template,
    }
    # 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)