from django.shortcuts import render, redirect
from django.contrib import messages
from visitors_app.models import location, company, department
from visitors_app.context_processors import my_constants
from django.shortcuts import get_object_or_404
from django.http import JsonResponse
from visitors_app.views import date_time
from django.db import connection
from django.views.decorators.csrf import csrf_exempt
from django.db import transaction
import csv
import io
import base64
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
from visitors_app.check_subscription import check_subscription


@check_subscription
def admin_department_add(request):
    username = request.session.get('admin')
    if not username:
        return redirect('admin')
    locationes = location.objects.filter(status=1)
    companyes = company.objects.filter(status=1)
    if request.method == 'POST':
        department_name = request.POST['department_name']
        department_code = request.POST['department_code']
        company_id = request.POST['company_id']
        location_id = request.POST['location_id']
        department_color_code = request.POST['department_color_code']
        created_at = date_time()
        departmente = department(department_name=department_name, department_code=department_code,department_color_code=department_color_code,
                                 company_id=company_id, location_id=location_id, created_at=created_at)
        departmente.save()
        messages.success(request, "Department successfully created.", extra_tags="success")
        return redirect('admin_department')

    return render(request, 'dashboard/admin_dashboard/admin_department/admin_department_add.html',
                  {'locationes': locationes, 'companyes': companyes,'function_name': 'Admin','username':username})


@check_subscription
def admin_department(request):
    username = request.session.get('admin')
    if not username:
        return redirect('admin')

    return render(request, 'dashboard/admin_dashboard/admin_department/admin_department.html',{'function_name': 'Admin','username':username})

@check_subscription
def admin_department_ajax_page_ajax(request):
    constants = my_constants(request)
    username = request.session.get('admin')
    if not username:
        return redirect('admin')
    database_name = constants['database_name']
    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 ( department_name LIKE "%{search_value}%" OR department_code LIKE "%{search_value}%" OR location_name LIKE "%{search_value}%"  OR company_name LIKE "%{search_value}%")'
    # First SQL query to get the count of appointments

    sql_query = f"""
            SELECT COUNT(*) AS department_count
            FROM {database_name}.department
            INNER JOIN {database_name}.location ON {database_name}.department.location_id = {database_name}.location.id
            INNER JOIN {database_name}.company ON {database_name}.department.company_id = {database_name}.company.id
                where 1=1 {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 {database_name}.department.*,{database_name}.location.location_name,{database_name}.company.company_name
        from  {database_name}.department
        inner join {database_name}.location on {database_name}.department.location_id = {database_name}.location.id
        inner join {database_name}.company on {database_name}.department.company_id = {database_name}.company.id
        where 1=1 {search}
    	ORDER BY
    		department.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})

@check_subscription
def admin_department_delete(request,id):
    username = request.session.get('admin')
    if not username :
        return redirect('admin')
    all_department = get_object_or_404(department,id=id)
    all_department.delete()
    data = {
        'status': 1,
        'message': f'Department Deleted Successfully.',
    }
    messages.success(request, f"successfully Department Deleted!", extra_tags="success")
    return JsonResponse(data)


@check_subscription
def admin_department_edit(request, id):
    constants = my_constants(request)
    username = request.session.get('admin')
    if not username:
        return redirect('admin')

    user_data = constants.get('admin_data', {})
    user_id = user_data.get('id')
    all_department= get_object_or_404(department, id=id)
    all_location = location.objects.filter(status=1)
    all_company= company.objects.filter(status=1)
    # state = states.objects.filter(id=all_location.state_id).first()
    # citie = cities.objects.filter(id=all_location.city_id).first()
    if request.method == "POST":
        all_department.department_name = request.POST['department_name']
        all_department.department_code = request.POST['department_code']
        all_department.department_color_code = request.POST['department_color_code']
        all_department.company_id = request.POST['company_id']
        all_department.location_id = request.POST['location_id']
        all_department.updated_at = date_time()
        all_department.save()
        messages.success(request, "Department successfully updated.", extra_tags="success")
        return redirect('admin_department')
    return render(request, 'dashboard/admin_dashboard/admin_department/all_department_edit.html', {
        'all_department': all_department,
        'locationes': all_location,
        'companyes':all_company,
        'function_name': 'Admin',
        'username': username
    })
@csrf_exempt
def admin_department_csv_upload(request):
    constants = my_constants(request)
    username = request.session.get('admin')
    if not username:
        return redirect('admin')

    user_data = constants.get('admin_data', {})
    user_id = user_data.get('id')
    if not user_id:
        return JsonResponse({'status': 'error', 'message': 'Invalid user data'})

    if request.method == 'POST':
        failed_rows = []
        success_count = 0

        try:
            if 'csv_file' not in request.FILES:
                return JsonResponse({'status': 'error', 'message': 'No CSV file uploaded'})

            csv_file = request.FILES['csv_file']
            if not csv_file.name.endswith('.csv'):
                return JsonResponse({'status': 'error', 'message': 'File is not a CSV'})

            if csv_file.size > 5 * 1024 * 1024:
                return JsonResponse({'status': 'error', 'message': 'File size exceeds 5MB limit'})

            decoded_file = csv_file.read().decode('utf-8').splitlines()
            reader = csv.DictReader(decoded_file)
            fieldnames = reader.fieldnames + ['Error'] if reader.fieldnames else []

            required_columns = {'Department_Name', 'Department_Code', 'Company_Name', 'Location_Name'}
            if not required_columns.issubset(reader.fieldnames or []):
                missing = required_columns - set(reader.fieldnames or [])
                return JsonResponse({'status': 'error', 'message': f'Missing columns: {", ".join(missing)}'})

            with transaction.atomic():
                for row_num, row in enumerate(reader, start=2):
                    try:
                        row = {k: v.strip() for k, v in row.items()}

                        department_name = row['Department_Name']
                        department_code = row['Department_Code']
                        company_name = row['Company_Name']
                        location_name = row['Location_Name']

                        company_obj = company.objects.filter(company_name__iexact=company_name).first()
                        location_obj = location.objects.filter(location_name__iexact=location_name).first()

                        if not company_obj or not location_obj:
                            missing = []
                            if not company_obj:
                                missing.append('Invalid Company')
                            if not location_obj:
                                missing.append('Invalid Location')
                            row['Error'] = ', '.join(missing)
                            failed_rows.append(row)
                            continue

                        # Check for duplicate department code or name
                        errors = []
                        if department.objects.filter(department_code=department_code).exists():
                            errors.append('Duplicate Department Code')
                        if department.objects.filter(department_name__iexact=department_name).exists():
                            errors.append('Duplicate Department Name')

                        if errors:
                            row['Error'] = ', '.join(errors)
                            failed_rows.append(row)
                            continue

                        department.objects.create(
                            department_name=department_name,
                            department_code=department_code,
                            company_id=company_obj.id,
                            location_id=location_obj.id,
                            created_at=date_time()
                        )
                        success_count += 1

                    except Exception as e:
                        row['Error'] = f"Unexpected error: {str(e)}"
                        failed_rows.append(row)
                        continue

            if failed_rows:
                wb = Workbook()
                ws = wb.active
                ws.title = "Failed Uploads"

                ws.append(fieldnames)
                error_col_index = fieldnames.index('Error') + 1
                ws.cell(row=1, column=error_col_index).fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
                ws.cell(row=1, column=error_col_index).font = Font(color="FFFFFF")

                for row_data in failed_rows:
                    row_values = [row_data.get(field, '') for field in fieldnames]
                    ws.append(row_values)
                    cell = ws.cell(row=ws.max_row, column=error_col_index)
                    cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
                    cell.font = Font(color="FFFFFF")

                output = io.BytesIO()
                wb.save(output)
                output.seek(0)
                encoded_excel = base64.b64encode(output.getvalue()).decode('utf-8')

                return JsonResponse({
                    'status': 'success',
                    'message': f'{success_count} departments added successfully',
                    'count': success_count,
                    'failed_rows_excel': encoded_excel,
                    'failed_rows_filename': 'failed_departments.xlsx'
                })

            return JsonResponse({
                'status': 'success',
                'message': f'{success_count} departments added successfully',
                'count': success_count
            })

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

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