import datetime
from django.http import JsonResponse
from django.shortcuts import render, redirect
from django.conf import settings
from django.contrib import messages
from django.views.decorators.csrf import csrf_exempt
from .models import user, appointment,location,company,department,safety_training,appointment_reject,safety_question,auto_email,website_setting,gate_pass_template,roles
from django.contrib.auth.hashers import check_password, make_password
from .context_processors import my_constants
from django.db import connection
from django.shortcuts import get_object_or_404
from django.urls import reverse
import os
from PIL import Image
import io
from visitor_management import settings
from django.utils.html import strip_tags
from django.core.mail import EmailMultiAlternatives, send_mail
from django.template.loader import render_to_string
from django.utils.safestring import mark_safe
from django.template import engines
from django.contrib.staticfiles.storage import staticfiles_storage
import json
import time
import qrcode
import base64
from io import BytesIO
from django.core import signing
import random
import string

# Create your views here.
def home(request):
    return redirect('gate_keeper')


def date_time():
    now = datetime.datetime.now()
    date_time =now.strftime("%y-%m-%d %H:%M:%S")
    return date_time
    
def generate_vms_string():
    now = datetime.datetime.now()
    vms_string = now.strftime("VMS%d%m%y%H%M%S")
    return vms_string

def generate_unique_request_id():
    while True:
        # Website setting mathi company initialization get karo
        setting = website_setting.objects.first()

        if setting and setting.company_initialization:
            company_code = setting.company_initialization
        else:
            company_code = "VMS"

        # 5 digit random number
        random_number = ''.join(random.choices(string.digits, k=5))

        # Final request id
        request_id = f"{company_code}{random_number}"

        # Database ma check karo same request_id chhe ke nai
        exists = appointment.objects.filter(request_id=request_id).exists()

        # Jo same na hoy to return karo
        if not exists:
            return request_id


def register(request, encrypted_appointment_id):
    redirect_url = reverse('register', args=[encrypted_appointment_id])
    constants = my_constants(request)
    database_name = constants['database_name']
    from_email = constants['From_Email']
    try:
        appointment_id = signing.loads(encrypted_appointment_id)
        appointment_id = int(appointment_id)
    except Exception as e:
        messages.error(request, 'Invalid appointment ID.')
        return redirect(redirect_url)

    appointmentes = get_object_or_404(appointment, id=appointment_id)
    if appointmentes.status == 'check out':
        message = 'Your Appointment is closed.'
        return render(request, 'registration_form/thank_you.html',
                      {'message': message, 'appointment_id': appointment_id})

    all_employee_id = appointmentes.employee_id
    all_visitor_id = appointmentes.visitors_id

    website_settings = website_setting.objects.first()
    is_id_card_required = website_settings.is_id_card_required
    if all_employee_id:
        sql_query = f'''
            SELECT * FROM {database_name}.users WHERE id = %s
        '''
        with connection.cursor() as cursor:
            cursor.execute(sql_query, [all_employee_id])
            user_data = cursor.fetchone()
            if user_data:
                columns = [col[0] for col in cursor.description]  # Get column names
                user_data_dict = dict(zip(columns, user_data))  # Convert to dictionary
                # Process the user_data_dict as needed
            else:
                messages.error(request, 'No user found for the given ID.')
    if all_visitor_id:
        sql_query_visitor = f'''
            SELECT * FROM {database_name}.users WHERE id = %s
        '''
        with connection.cursor() as cursor:
            cursor.execute(sql_query_visitor, [all_visitor_id])
            visitor_data = cursor.fetchone()
            if visitor_data:
                columns = [col[0] for col in cursor.description]  # Get column names
                user_visitor = dict(zip(columns, visitor_data))
            else:
                messages.error(request, 'No visitor found for the given visitor ID.')

    if request.method == 'POST':

        firstname = request.POST.get('Firstname', '')

        lastname = request.POST.get('Lastname', '')

        email = request.POST.get('Email', '')

        Address = request.POST.get('Address', '')

        mobiles = request.POST.get('mobile', '')

        gender = request.POST.get('gender', '')

        id_card = request.POST['base_id_card']

        vehicle_number = request.POST['vehicle_number']

        image = request.POST['user_image']

        if image:
            base64_data = image.split(",")[1]
            binary_data = base64.b64decode(base64_data)
            image = Image.open(io.BytesIO(binary_data))

            # Save the image to a directory
            save_directory = 'user/'
            if not os.path.exists(save_directory):
                os.makedirs(save_directory)
            filename = firstname.replace(' ', '_')
            file_date = generate_vms_string()
            new_file_name = file_date.replace('VMS', '')
            filename = f"{filename}_{new_file_name}.jpg"
            face_image_path = os.path.join(save_directory, filename)

            image.save(face_image_path)
        id_cards_filename = ''
        if id_card:
            if 'id_cards/' in id_card:
                id_cards_filename = os.path.basename(id_card)
            else:
                try:
                    if ',' in id_card:
                        base64_data = id_card.split(',')[1]
                    else:
                        raise ValueError("Invalid ID card format")

                    binary_data = base64.b64decode(base64_data)

                    img = Image.open(io.BytesIO(binary_data))

                    if img.mode in ('RGBA', 'LA'):
                        background = Image.new('RGB', img.size, (255, 255, 255))
                        background.paste(img, mask=img.split()[3])
                        img = background
                    else:
                        img = img.convert('RGB')

                    save_directory = os.path.join(settings.MEDIA_ROOT, 'id_cards')
                    os.makedirs(save_directory, exist_ok=True)

                    unique_name = generate_vms_string().replace('VMS', '')
                    clean_name = firstname.strip().replace(' ', '_')

                    id_cards_filename = f"idcard_{clean_name}_{unique_name}.jpg"

                    id_card_path = os.path.join(save_directory, id_cards_filename)

                    img.save(id_card_path, 'JPEG', quality=95)

                except Exception as e:
                    messages.error(
                        request,
                        f"Error processing ID Card: {str(e)}",
                        extra_tags='danger'
                    )
                    return redirect(redirect_url)
        if firstname == '' or mobiles == '' or lastname == '' or Address == '' or gender == '' or image == '':
            messages.error(request, 'All Filed......', extra_tags='danger')
            return redirect(redirect_url)

        if user.objects.filter(id=all_visitor_id).exists():
            visitor = user.objects.get(id=all_visitor_id)
            visitor.first_name = firstname
            visitor.last_name = lastname
            # visitor.email = email
            visitor.mobile = mobiles
            visitor.gender = gender
            visitor.address = Address
            visitor.id_card = id_cards_filename
            visitor.image = face_image_path
            visitor.updated_at = date_time()
            # visitor.id_card = visitor

            appointmentes.id_card = id_cards_filename
            appointmentes.vehicle_number = vehicle_number
            try:
                visitor.save()
                appointmentes.save()
                subject_visitor = 'Appointment Confirmation'
                context_visitor = {
                    'text': 'Your invitation form successfully Fillup by visitor.'
                }

                template_name_visitor = 'registration_form/thank_you.html'
                message_visitor = render_to_string(template_name_visitor, context_visitor)
                plain_message_visitor = strip_tags(message_visitor)
                recipient_list_visitor = [user_data_dict['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()
                message = 'Thank you for Registration.'
                return render(request, 'registration_form/thank_you.html',
                              {'thank_message': message, 'registered': 'false', 'appointment_id': appointment_id})
            except Exception as e:
                print(e)
                messages.error(request, 'Error while updating visitor.')
                return redirect(redirect_url)
        else:
            messages.error(request, 'Visitor not found for updating.')

    else:
        if all_visitor_id:
            visitor = user.objects.get(id=all_visitor_id)
            if visitor.first_name != "":  # Check if visitor is already registered
                message = 'You have been already Registered.'
                return render(request, 'registration_form/thank_you.html',
                              {'thank_message': message, 'registered': 'true', 'appointment_id': appointment_id,'is_id_card_required':is_id_card_required})

    return render(request, 'registration_form/main_file.html',
                  {'user_data_dict': user_data_dict, 'user_visitor': user_visitor,'is_id_card_required':is_id_card_required})



def visitors_self_safety_training(request):
    context = {}
    appointment_url = request.build_absolute_uri(f'/qr-appointment')
    qr = qrcode.QRCode(
        version=1,
        error_correction=qrcode.constants.ERROR_CORRECT_L,
        box_size=10,
        border=1,
    )
    qr.add_data(appointment_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()
    context['appointment_url'] = qr_code_base64
    if request.method == 'POST':
        request_id = request.POST.get('request_id')
        first_name = request.POST.get('first_name')
        last_name = request.POST.get('last_name')
        email = request.POST.get('email')
        phone = request.POST.get('phone')
        password = request.POST.get('password')
        if not request_id:
            messages.error(request, 'Appointment ID is required.', extra_tags='danger')
            return render(request, 'dashboard/visitors_dashboard/visitors_self_safety_training.html', context)

        appointment_instance = appointment.objects.filter(request_id=request_id).first()
        if not appointment_instance:
            messages.error(request, 'Request Id not found.', extra_tags='danger')
            return render(request, 'dashboard/visitors_dashboard/visitors_self_safety_training.html', context)

        visitor_id = appointment_instance.visitors_id
        visitor_instance = user.objects.filter(id=visitor_id).first()
        if not visitor_instance:
            messages.error(request, 'Visitor not found.', extra_tags='danger')
            return render(request, 'dashboard/visitors_dashboard/visitors_self_safety_training.html', context)

        # If visitor name exists, redirect to training
        if visitor_instance.first_name and visitor_instance.last_name:
            request.session['user_id'] = visitor_instance.id
            request.session['appointment_id'] = appointment_instance.id
            return redirect('visitors_safety_training')

        # If names are missing, ensure they are submitted to update
        if first_name and last_name:
            visitor_instance.first_name = first_name
            visitor_instance.last_name = last_name
            visitor_instance.email = email
            visitor_instance.phone = phone
            visitor_instance.password = make_password(password)
            visitor_instance.save()

            request.session['user_id'] = visitor_instance.id
            request.session['appointment_id'] = appointment_instance.id
            return redirect('visitors_safety_training')
        else:
            # Show name fields if missing
            context['show_name_fields'] = True
            context['request_id'] = request_id

            messages.warning(request, 'First Name, Last Name, Email, Phone, Password are required.', extra_tags='danger')
            return render(request, 'dashboard/visitors_dashboard/visitors_self_safety_training.html', context)

    return render(request, 'dashboard/visitors_dashboard/visitors_self_safety_training.html', context)


def update_safety_training_status(request):
    appointment_ides = request.session.get('appointment_id')
    if request.method == "POST":
        try:
            data = json.loads(request.body)
            user_id = data.get('user_id')

            useres = user.objects.get(id=user_id)
            useres.is_safety_training = True
            useres.save()
            formatted_gate_pass_number = None
            user_appointment = appointment.objects.filter(id=appointment_ides).first()
            if user_appointment.employee_approval == 'accepted':
                formatted_gate_pass_number= f"VMS{appointment_ides}"
            else:
                formatted_gate_pass_number = None
            return JsonResponse(
                {'message': 'Safety training completed successfully!', 'gate_pass_number': formatted_gate_pass_number})
        except Exception as e:
            return JsonResponse({'message': f"Error: {str(e)}"}, status=400)
    return JsonResponse({'message': 'Invalid request method'}, status=400)


@csrf_exempt
def submit_safety_questions(request):
    if request.method == "POST":
        try:
            data = json.loads(request.body)
            user_id = data.get("user_id")
            answers = data.get("answers", {})

            if not user_id or not answers:
                return JsonResponse({
                    "status": "error",
                    "message": "Missing user ID or answers"
                }, status=400)

            # Get all relevant questions
            questions = safety_question.objects.filter(id__in=answers.keys())

            total_questions = questions.count()
            correct_count = 0

            for question in questions:
                correct_option = question.correct_option
                submitted_answer = answers.get(str(question.id))
                if submitted_answer == correct_option:
                    correct_count += 1

            # Calculate percentage
            if total_questions == 0:
                return JsonResponse({
                    "status": "error",
                    "message": "No questions found to evaluate"
                }, status=400)

            score = (correct_count / total_questions) * 100

            return JsonResponse({
                "status": "success",
                "score": round(score, 2)
            })

        except json.JSONDecodeError:
            return JsonResponse({
                "status": "error",
                "message": "Invalid JSON data"
            }, status=400)

        except Exception as e:
            return JsonResponse({
                "status": "error",
                "message": f"An unexpected error occurred: {str(e)}"
            }, status=500)
    else:
        return JsonResponse({
            "status": "error",
            "message": "Invalid request method"
        }, status=405)


def visitors_appointment_reject(request):
    if request.method == 'POST':
        try:
            data = json.loads(request.body)  # Parse JSON data from the request body
            appointment_id = data.get('appointment_id')
            status = data.get('status')
            reason = data.get('reason')
            date = data.get('date')
            time = data.get('time')

            # Ensure you're getting the appointment object
            appointmentes = appointment.objects.filter(id=appointment_id).first()
            if appointmentes:
                appointmentes.status = status
                appointmentes.save()

                appointmentes_rejection = appointment_reject.objects.create(
                    appointment_id=appointment_id,
                    reason=reason,
                    date=date,
                    time=time,
                    created_at=date_time()
                )
                appointmentes_rejection.save()

                return JsonResponse({'status': 'true', 'message': 'Status updated successfully.'})

            else:
                return JsonResponse({'status': 'false', 'message': 'Appointment not found.'})

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

    return JsonResponse({'status': 'false', 'message': 'Invalid request method.'})

def visitors_safety_training(request):
    constants = my_constants(request)
    username = request.session.get('visitors')
    user_data = constants['user_data']
    user_ides = request.session.get('user_id')
    appointment_ides = request.session.get('appointment_id')
    useres = user.objects.filter(id=user_ides).first()

    # If no user is found, redirect to another page (optional).
    if not useres:
        return redirect('visitors')

    # Define `video` with a default value
    video = None
    questions = None
    employee_data = None
    location_data = None
    department_data = None
    company_data = None
    gate_pass_template_data = None
    user_appointment = appointment.objects.filter(id=appointment_ides).first()
    purpose = user_appointment.purpose if user_appointment else "N/A"

    employee_data = user.objects.filter(id=user_appointment.employee_id).first()

    location_data = location.objects.filter(id=employee_data.location_id).first()

    company_data = company.objects.filter(id=employee_data.company_id).first()

    department_data = department.objects.filter(id=employee_data.department_id).first()

    setting = website_setting.objects.first()
    is_safety_video_active = setting.is_safety_video_active if setting else False

    if user_appointment.employee_approval == 'accepted':
        formatted_gate_pass_number = f"VMS{appointment_ides}"
    else:
        formatted_gate_pass_number = None


    # ---------------------------
    # Appointment → Employee (user table)
    # ---------------------------
    if user_appointment and user_appointment.employee_id:
        employee_data = user.objects.filter(
            id=user_appointment.employee_id
        ).first()

    # ---------------------------
    # Employee → Location → Gate Pass Template
    # ---------------------------
    if employee_data and employee_data.location_id:
        location_data = location.objects.filter(
            id=employee_data.location_id
        ).first()

        # if location_data and location_data.gate_pass_template_id:
        #     gate_pass_template_data = gate_pass_template.objects.filter(
        #         id=location_data.gate_pass_template_id
        #     ).first()
        if location_data:
            # ✅ CASE 1: gate_pass_template_id == 0 → Use Default Gate Pass
            if location_data.gate_pass_template_id == 0:
                gate_pass_template_data = gate_pass_template.objects.filter(
                    gate_pass_name='Default Gate Pass'
                ).first()

            # ✅ CASE 2: gate_pass_template_id > 0 → Use assigned template
            elif location_data.gate_pass_template_id:
                gate_pass_template_data = gate_pass_template.objects.filter(
                    id=location_data.gate_pass_template_id
                ).first()
    # === BEST WAY: Pre-render the stored gate pass template with full Django features ===
    rendered_gate_pass = "<div class='text-center text-danger'><strong>No gate pass template assigned to this location.</strong></div>"

    if gate_pass_template_data and gate_pass_template_data.gate_pass_template:
        gate_pass_template_html = gate_pass_template_data.gate_pass_template

        # Add {% load static %} at the top so {% static '...' %} works
        full_template_string = "{% load static %}\n" + gate_pass_template_html

        # Context for rendering the gate pass template
        template_context = {
            'formatted_gate_pass_number': formatted_gate_pass_number or "N/A",
            'visitors_type': user_appointment.visitors_type or '',
            'visitors_name': f"{useres.first_name} {useres.last_name}".strip(),
            'visitors_address': getattr(useres, 'address', '') or '',
            'visitors_mobile': getattr(useres, 'mobile', '') or '',
            'visitors_image': (
                request.build_absolute_uri(f"/user/{useres.image.name.split('/')[-1]}")
                if useres.image
                else request.build_absolute_uri(staticfiles_storage.url('default_user.png'))
            ),
            'check_in_time': user_appointment.check_in_time or '',
            'check_out_time': user_appointment.check_out_time or '',
            'employee_name': (
                f"{employee_data.first_name} {employee_data.last_name}".strip()
                if employee_data else 'N/A'
            ),
            'company_name': company_data.company_name if company_data else 'N/A',
            'department_name': department_data.department_name if department_data else 'N/A',
            'location_name': location_data.location_name if location_data else 'N/A',
            'location_address': getattr(company_data, 'address_1', '') or '',
            'GET_PASS_IMAGE': constants.get('GET_PASS_IMAGE', ''),  # Change to your actual logo path
            'pass_type': 'visitor_pass',  # or determine dynamically
            'qr_code_base64': None,  # Add QR generation logic if needed
            'qr_expired': False,
            'appointment': [user_appointment],  # for appointment.0 access in template
            'device':user_appointment.devices or '',
            'visitor_company_name': user_appointment.visitor_company_name or '',
        }

        try:
            django_engine = engines['django']
            rendered_gate_pass = django_engine.from_string(full_template_string).render(template_context)
        except Exception as e:
            rendered_gate_pass = f"<div class='alert alert-danger'>Error rendering gate pass: {str(e)}</div>"

    # Mark as safe to prevent HTML escaping
    gate_pass_template_safe = mark_safe(rendered_gate_pass)
    # ✅ If safety video is OFF → directly show gate pass
    if not is_safety_video_active:
        return render(request, 'dashboard/visitors_dashboard/visitors_safety_training.html', {
            'last_video': None,
            'user_id': user_ides,
            'user': useres,
            'purpose': purpose,
            'visitor_name': useres.first_name,
            'all_data_user': user_appointment,
            'employee_data': employee_data,
            'location_data': location_data,
            'department_data': department_data,
            'company_data': company_data,
            'formatted_gate_pass_number': formatted_gate_pass_number,
            'skip_video': True,  # ✅ use in template JS to skip
            'gate_pass_template': gate_pass_template_safe,
            })

    # If the user has completed safety training
    if useres.is_safety_training:
        # last_video = safety_training.objects.filter(is_active=True).order_by('-id').first()
        # video = last_video.video_file if last_video else None

        return render(request, 'dashboard/visitors_dashboard/visitors_safety_training.html', {
            'last_video': video,
            'user_id': user_ides,
            'user': useres,
            'purpose': purpose,
            'visitor_name': useres.first_name,
            'all_data_user': user_appointment,
            'employee_data': employee_data,
            'location_data': location_data,
            'department_data': department_data,
            'company_data': company_data,
            'formatted_gate_pass_number': formatted_gate_pass_number,
            'gate_pass_template': gate_pass_template_safe,

        })
    else:
        # Handle the case where safety training is not completed
        last_video = safety_training.objects.filter(is_active=True).order_by('-id').first()
        video = last_video.video_file if last_video else None
        if last_video:
            questions = list(safety_question.objects.filter(training=last_video).values())
        return render(request, 'dashboard/visitors_dashboard/visitors_safety_training.html', {
            'last_video': video,
            'user_id': user_ides,
            'user': useres,
            'purpose': purpose,
            'visitor_name': useres.first_name,
            'all_data_user': user_appointment,
            'employee_data': employee_data,
            'location_data': location_data,
            'department_data': department_data,
            'company_data': company_data,
            'questions': questions,
            'formatted_gate_pass_number': formatted_gate_pass_number,
            'gate_pass_template':gate_pass_template_safe,
            # 'user_id': user_ides,
        })
        
        
def visitors(request):
    context = {'function_name': 'Visitors', 'ADMIN_STATIC_PATH': settings.ADMIN_STATIC_PATH}
    if request.method == 'POST':
        email = request.POST.get('email', '')
        password = request.POST.get('password', '')
        user_type = request.POST.get('user_type', '')
        if email == '' or password == '':
            messages.error(request, 'All fields are required.')
            return render(request, 'registration/login.html', context)
        abc = user.objects.filter(email=email, is_active=True, type="visitors").first()
        # abc = user.objects.filter(Q(type__icontains='visitors'), employee_code=employee_code,is_active=True).first()
        if abc is not None:
            if check_password(password, abc.password):
                user_name = abc.first_name
                vistior = {'user_email': email, 'user_password': password, 'user_name': user_name}
                request.session['visitors'] = vistior
                # request.session['user_type'] = user_type
                # return render(request, 'dashboard/visitors_dashboard/dashboard.html', {'username': user_name})
                return redirect(f'visitors_dashboard')
            else:
                messages.error(request, 'Invalid Email or Password.',extra_tags='danger')
        else:
            messages.error(request, 'Invalid Login.',extra_tags='danger')

        return render(request, 'registration/login.html', context)

    return render(request, 'registration/login.html', context)

def visitors_sign_up(request):
    context = {'function_name': 'Visitors', 'ADMIN_STATIC_PATH': settings.ADMIN_STATIC_PATH}
    constants = my_constants(request)
    from_email = constants['From_Email']

    if request.method == 'POST':
        firstname = request.POST.get('first_name', '')
        lastname = request.POST.get('last_name', '')
        email = request.POST.get('email', '')
        password = request.POST.get('Password', '')
        confirm_password = request.POST.get('Confirm_Password', '')

        # Validate input
        if password != confirm_password:
            messages.error(request, 'Password and Confirm Password do not match.')
            return render(request, 'dashboard/visitors_dashboard/sign_up.html', context)

        if user.objects.filter(email=email).exists():
            messages.error(request, 'Email already exists.')
            return render(request, 'dashboard/visitors_dashboard/sign_up.html', context)

        if not all([firstname, lastname, email, password, confirm_password]):
            messages.error(request, 'All fields are required.')
            return render(request, 'dashboard/visitors_dashboard/sign_up.html', context)

        # Hash the password
        password = make_password(password)

        # Save the user
        user_instance = user(
            first_name=firstname,
            last_name=lastname,
            email=email,
            password=password,
            is_active=0,
            employee_code=0,
            type='visitors'
        )
        user_instance.save()

        # Send email to visitor
        visitor_subject = "Thank You for Registration"
        visitor_message = f"""
        Dear {firstname} {lastname},

        Thank you for registering with us. Your registration is under review. 
        You will be able to log in once approved by the admin.

        Regards,
        Visitor Management System
        """
        send_mail(visitor_subject, visitor_message, from_email, [email])

        # Send email to admin
        admin_subject = "New Visitor Registration Approval Needed"
        admin_message = f"""
        A new visitor has registered:
        Id = {user_instance.id}
        Name: {firstname} {lastname}
        Email: {email}

        Please review and approve the registration.

        Regards,
        Visitor Management System
        """
        email_string = auto_email.objects.values_list('email', flat=True).first()
        to_email = [email.strip() for email in email_string.split(',')] if email_string else []
        send_mail(admin_subject, admin_message, from_email, to_email)

        # Show success message and stay on the page for 1 minute before redirect
        messages.success(request, 'User registered successfully.')
        return render(request, 'dashboard/visitors_dashboard/sign_up.html', {**context, 'redirect_url': '/visitors'})

    return render(request, 'dashboard/visitors_dashboard/sign_up.html', context)



def visitors_dashboard(request):
    # Call my_constants to get domain and user information
    constants = my_constants(request)

    username = request.session.get('visitors')
    if not username:
        return redirect('visitors')
    user_data = constants.get('user_data', {})
    database_name = constants['database_name']
    user_id = user_data.get('id')
    all_appointment = appointment.objects.filter(visitors_id=user_id).count()
    username['all_appointment'] = all_appointment

    now = datetime.datetime.now()
    date = now.strftime("%Y-%m-%d")
    time = now.strftime("%I:%M %p").lstrip('0')  # 12-hour format

    # SQL date and time filter
    date_filter = f"""
                AND(appointment.date > '{date}' OR (appointment.date = '{date}' AND appointment.time > '{time}'))

            """
    user_id_filter = f"AND appointment.visitors_id = {user_id}"
    # First SQL query to get the count of appointments
    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
                WHERE 1=1
                {date_filter}{user_id_filter};
            """

    with connection.cursor() as cursor:
        cursor.execute(sql_query_count)
        database_all_data = cursor.fetchall()

    Upcoming_appointment = database_all_data[0][0] if database_all_data else 0

    context = {

        'username': username,
        'DOMAIN_NAME': constants['DOMAIN_NAME'],
        'DOMAIN_ICON': constants['DOMAIN_ICON'],
        'user_data': constants['user_data'],
        'all_appointment': all_appointment,
        'Upcoming_appointment': Upcoming_appointment,
        'function_name': 'Visitors'
    }
    return render(request, 'dashboard/visitors_dashboard/dashboard.html', context)


def visitors_logout(request):
    if request.session.get('visitors'):
        del request.session['visitors']
    return redirect('visitors')


# def password_reset(request):
#     constants = my_constants(request)
#
#     context = {
#         "function_name": "Forgot Password",
#         "is_password_reset": True
#     }
#
#     from_email = constants['From_Email']
#
#     if request.method == 'POST':
#         email = request.POST.get('email', '').strip()
#
#         # 1️⃣ Email required validation
#         if not email:
#             messages.error(request, "Email is required!", extra_tags="danger")
#             return render(request, "registration/login.html", context)
#
#         try:
#             # 2️⃣ Check if user exists
#             user_obj = user.objects.filter(email=email,type__in=['admin', 'gate_keeper', 'employee']).first()
#             # 3️⃣ Type Check (Visitor Not Allowed)
#             if user_obj:
#                 # 3️⃣ Generate secure reset token
#                 token = signing.dumps({
#                     "email": user_obj.email,
#                     "timestamp": int(time.time())
#                 })
#                 reset_link = request.build_absolute_uri(
#                     reverse('reset-password', args=[token])
#                 )
#
#                 # 4️⃣ Email subject
#                 subject = "Reset Your Password - Senador VMS"
#                 context={'user': user_obj,
#                     'reset_link': reset_link}
#                 # 5️⃣ Render email template with context
#                 template_name = 'notification_email/forgot_password.html'
#                 message = render_to_string(template_name, context)
#
#                 plain_message = strip_tags(message)
#                 recipient_list = [user_obj.email]
#
#                 # 6️⃣ Send Email
#                 email_message = EmailMultiAlternatives(
#                     subject,
#                     plain_message,
#                     from_email,
#                     recipient_list
#                 )
#                 email_message.attach_alternative(message, "text/html")
#                 email_message.send()
#                 messages.success(request, "Password reset link sent to your email!", extra_tags="success")
#                 return redirect('home')
#             else:
#                 messages.error(request, "Email ID does not exist!", extra_tags="danger")
#
#         except user.DoesNotExist:
#             # 7️⃣ If email not found
#             messages.error(request, "No account found with this email!", extra_tags="danger")
#
#         except Exception as e:
#             print("Email Send Error:", e)
#             # messages.error(request, "Something went wrong while sending email!", extra_tags="danger")
#             messages.error(request, e, extra_tags="danger")
#
#     return render(request, "registration/login.html", context)




# ===================== FORGOT PASSWORD =====================
def password_reset(request):
    constants = my_constants(request)

    context = {
        "function_name": "Forgot Password",
        "is_password_reset": True
    }

    from_email = constants['From_Email']

    if request.method == 'POST':
        email = request.POST.get('email', '').strip()

        if not email:
            messages.error(request, "Email is required!", extra_tags="danger")
            return render(request, "registration/login.html", context)

        try:
            user_obj = user.objects.filter(
                email=email,
                type__in=['admin', 'gate_keeper', 'employee']
            ).first()

            if user_obj:
                # Generate token with timestamp
                token = signing.dumps({
                    "email": user_obj.email,
                    "timestamp": int(time.time())
                })

                reset_link = request.build_absolute_uri(
                    reverse('reset-password', args=[token])
                )

                subject = "Forgot Password"

                email_context = {
                    'user': user_obj,
                    'reset_link': reset_link
                }

                template_name = 'notification_email/forgot_password.html'
                message = render_to_string(template_name, email_context)
                plain_message = strip_tags(message)

                email_message = EmailMultiAlternatives(
                    subject,
                    plain_message,
                    from_email,
                    [user_obj.email]
                )
                email_message.attach_alternative(message, "text/html")
                email_message.send()

                messages.success(
                    request,
                    "A password reset link has been sent to your registered email address. Please note that the link will expire in 10 minutes.",
                    extra_tags="success"
                )
                return redirect('home')

            else:
                messages.error(request, "Email ID does not exist!", extra_tags="danger")

        except Exception as e:
            print("Email Send Error:", e)
            messages.error(request, "Something went wrong!", extra_tags="danger")

    return render(request, "registration/login.html", context)

# ===================== RESET PASSWORD =====================
def reset_password(request, token):
    try:
        # 10 minutes = 600 seconds
        data = signing.loads(token, max_age=600)

        email = data["email"]
        token_created_at = data["timestamp"]

    except signing.SignatureExpired:
        messages.error(
            request,
            "This link has expired. Please request a new password reset link.",
            extra_tags="danger"
        )
        return redirect("password_reset")

    except signing.BadSignature:
        messages.error(
            request,
            "Invalid or expired link!",
            extra_tags="danger"
        )
        return redirect("password_reset")

    except Exception:
        messages.error(
            request,
            "Something went wrong!",
            extra_tags="danger"
        )
        return redirect("password_reset")

    if request.method == "POST":
        employee_code = request.POST.get("employee_code", "").strip()
        password = request.POST.get("new_password", "").strip()
        confirm_password = request.POST.get("confirm_password", "").strip()

        if not employee_code or not password or not confirm_password:
            messages.error(request, "All fields are required!", extra_tags="danger")

        elif password != confirm_password:
            messages.error(request, "Passwords do not match!", extra_tags="danger")

        else:
            user_obj = user.objects.filter(
                email=email,
                employee_code=employee_code,
                type__in=["admin", "gate_keeper", "employee"]
            ).first()

            if not user_obj:
                messages.error(request, "Invalid Employee Code!", extra_tags="danger")
            else:
                user_obj.password = make_password(password)
                user_obj.save()

                messages.success(
                    request,
                    "Password reset successful! Please login.",
                    extra_tags="success"
                )
                return redirect("home")

    return render(request, "forgot_password/reset_password.html", {
        "token_created_at": token_created_at
    })