from visitors_app.context_processors import my_constants
from django.shortcuts import  redirect,render
from django.http import JsonResponse
from django.db import connection
from datetime import datetime
from visitors_app.models import department
from visitors_app.check_subscription import check_subscription

@check_subscription
def admin_report(request):
    constants = my_constants(request)
    database_name = constants['database_name']
    user_id = constants['admin_data']['id']
    username = request.session.get('admin')

    if not username:
        return redirect('admin')

    if request.method == 'POST':
        date_1 = request.POST.get('date')
        department_id = request.POST.get('department')
        visitor_type = request.POST.get('visitor_type')

        start = int(request.POST.get('start', 0))
        length = int(request.POST.get('length', 10))
        search_value = request.POST.get('search[value]', '')

        search = ''
        search_args = []
        if search_value:
            search = '''
                AND (
                    v.first_name LIKE %s OR 
                    v.last_name LIKE %s OR 
                    v.email LIKE %s OR
                    v.uni_id LIKE %s OR
                    v.mobile LIKE %s OR
                    a.visitors_type LIKE %s OR
                    e.first_name LIKE %s
                )
            '''
            search_args.extend([f'%{search_value}%'] * 7)

        # Adding filters for date, department, and visitor type
        date_filter = ''
        department_filter = ''
        visitor_type_filter = ''
        filter_args = []

        if date_1:
            start_date_str, end_date_str = date_1.split(" - ")
            date_format = "%m/%d/%Y"
            start_date = datetime.strptime(start_date_str, date_format)
            end_date = datetime.strptime(end_date_str, date_format)
            output_format = "%y-%m-%d"
            start_date_formatted = start_date.strftime(output_format)
            end_date_formatted = end_date.strftime(output_format)
            start_date_formatt = f"{start_date_formatted} 00:00:00"
            end_date_formatt = f"{end_date_formatted} 23:59:59"
            date_filter = 'AND a.created_at >= %s AND a.created_at <= %s'
            filter_args.extend([start_date_formatt, end_date_formatt])

        if department_id:
            department_filter = 'AND v.department_id = %s'
            filter_args.append(department_id)

        if visitor_type:
            visitor_type_filter = 'AND a.visitors_type = %s'
            filter_args.append(visitor_type)

        # SQL query to get the count of appointments
        sql_query_count = f"""
            SELECT
                COUNT(*)
            FROM
                {database_name}.appointment AS a
            INNER JOIN
                {database_name}.users AS v
            ON
                v.id = a.visitors_id
            INNER JOIN
                {database_name}.users AS e
            ON
                e.id = a.employee_id
            INNER JOIN
                {database_name}.department AS d  
            ON
                d.id = v.department_id  
            INNER JOIN
                {database_name}.location AS l 
            ON
                l.id = v.location_id 
            INNER JOIN
                {database_name}.company AS c 
            ON
                c.id = v.company_id 
            WHERE
                1=1
                {date_filter}
                {department_filter}
                {visitor_type_filter}
                {search}
        """
        count_params = filter_args + search_args
        with connection.cursor() as cursor:
            cursor.execute(sql_query_count, count_params)
            recordsTotal = cursor.fetchone()[0]

        # SQL query to get detailed user data
        sql_query_data = f"""
            SELECT 
                a.*, 
                v.first_name AS visitors_first_name,
                v.last_name AS visitors_last_name,
                v.uni_id AS visitors_uni_id,
                e.first_name AS employee_first_name,
                v.email AS visitors_email,
                v.mobile AS visitors_mobile,
                v.image AS visitors_image,
                d.department_name AS department_name,  
                l.location_name AS location_name,
                c.company_name AS company_name
            FROM 
                {database_name}.appointment AS a
            INNER JOIN 
                {database_name}.users AS v
            ON 
                v.id = a.visitors_id
            INNER JOIN
                {database_name}.users AS e
            ON
                e.id = a.employee_id
            INNER JOIN
                {database_name}.department AS d  
            ON
                d.id = v.department_id  
            INNER JOIN
                {database_name}.location AS l 
            ON
                l.id = v.location_id 
            INNER JOIN
                {database_name}.company AS c 
            ON
                c.id = v.company_id  
            WHERE 
                1=1
                {date_filter}
                {department_filter}
                {visitor_type_filter}
                {search}
            ORDER BY
                a.id DESC
            LIMIT %s OFFSET %s
        """
        data_params = filter_args + search_args + [length, start]
        with connection.cursor() as cursor:
            cursor.execute(sql_query_data, data_params)
            database_all_data = cursor.fetchall()
            columns = [col[0] for col in cursor.description]
            all_user_dtl = [dict(zip(columns, row)) for row in database_all_data]

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

    departments = department.objects.all()
    return render(request, 'dashboard/admin_dashboard/admin_report/admin_report.html', {
        'departments': departments,
        'function_name': 'Admin',
        'username': username
    })