import datetime

from django.shortcuts import render, HttpResponse, redirect
from django.conf import settings
# Create your views here.
from django.contrib import messages
from .models import user,appointment
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 .views import date_time
from django.shortcuts import get_object_or_404
from .context_processors import my_constants

def visitors_appointment(request):
    username = request.session.get('visitors')
    
    if not username :
        return redirect('visitors')
     
    return render(request,'dashboard/visitors_dashboard/appointment.html',{'username': username,'function_name': 'Visitors'})

def appointment_page_ajax(request):
    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')
    start = request.POST.get('start', 0)
    length = request.POST.get('length', 10)
    search_value = request.POST.get('search[value]', '')
    search = ''
    if search_value != '':
        # search = f'WHERE visitors.first_name LIKE "%{search_value}%" or employees.first_name LIKE "%{search_value}%" or appointment.id LIKE "%{search_value}%" or appointment.date LIKE "%{search_value}%" or appointment.time LIKE "%{search_value}%" or appointment.purpose LIKE "%{search_value}%"'
        search = f'AND (visitors.first_name LIKE "%{search_value}%" OR employees.first_name LIKE "%{search_value}%" OR appointment.id LIKE "%{search_value}%" OR appointment.date LIKE "%{search_value}%" OR appointment.time LIKE "%{search_value}%" OR appointment.purpose LIKE "%{search_value}%")'
    # 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
            WHERE 1=1 and appointment.visitors_id = {user_id} {search};
"""

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

    # Check if database_all_data is not empty before accessing its elements
    if database_all_data:
        recordsTotal = database_all_data[0][0]
    else:
        recordsTotal = 0

    # Second SQL query to get detailed appointment data
    sql_query = f"""
    SELECT 
        appointment.*, 
        visitors.first_name AS visitors_name, 
        employees.first_name AS employee_name
    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
    {search} AND appointment.visitors_id = {user_id}
    ORDER BY 
        appointment.id DESC
    LIMIT {length} OFFSET {start};
"""
    # Execute the second SQL query
    with connection.cursor() as cursor:
        cursor.execute(sql_query)
        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})