from rest_framework.views import APIView
from rest_framework.response import Response
from visitors_app.models import appointment
from visitors_app.views import date_time
from django.db import connection, IntegrityError
from visitors_app.context_processors import my_constants
from django.core.mail import  EmailMultiAlternatives
from django.template.loader import render_to_string
from django.utils.html import strip_tags
from datetime import datetime


class visitore_listingView(APIView):
    def post(self, request):
        constants = my_constants(request)
        employee_id = request.data.get('employee_id', '')
        page = request.data.get('page', 1)
        type = request.data.get('type', '')
        search_term = request.data.get('search', '')
        page = int(page)

        database_name = constants['database_name']
        if not employee_id:
            return Response({"status": "false", "message": "Employee ID is required."})

        # Define the number of results per page
        results_per_page = 10

        base_image_url = constants['IMAGEPATH']
        placeholder_image_url = constants['DEFAULT_IMAGE']
        offset = (page - 1) * results_per_page

        with connection.cursor() as cursor:
            # Construct the base query
            base_query = f"""
                SELECT 
                    {database_name}.appointment.*, 
                    {database_name}.users.first_name as visitors_first_name,
                    {database_name}.users.email as visitors_email,
                    {database_name}.users.mobile as visitors_mobile,
                    {database_name}.users.image as visitors_image
                FROM 
                    {database_name}.appointment
                INNER JOIN 
                    {database_name}.users 
                ON 
                    {database_name}.users.id = {database_name}.appointment.visitors_id
                WHERE 
                    {database_name}.appointment.employee_id = %s
            """

            # Prepare the parameters for the main query
            params = [employee_id]

            # Handle search term if provided
            if search_term:
                base_query += " AND ("
                base_query += f"{database_name}.users.first_name LIKE %s OR "
                base_query += f"{database_name}.users.email LIKE %s OR "
                base_query += f"{database_name}.users.mobile LIKE %s OR "  # Fix applied here
                base_query += f"{database_name}.appointment.status LIKE %s"
                base_query += ")"
                params.extend([f"%{search_term}%", f"%{search_term}%", f"%{search_term}%", f"%{search_term}%"])

            # Handle different values for `type`
            if type == 'all':
                count_query = f"""
                    SELECT COUNT(*) 
                    FROM 
                        {database_name}.appointment 
                    INNER JOIN 
                        {database_name}.users 
                    ON 
                        {database_name}.users.id = {database_name}.appointment.visitors_id
                    WHERE 
                        {database_name}.appointment.employee_id = %s
                        AND {database_name}.appointment.status != 'pending'
                """
                if search_term:
                    count_query += " AND (users.first_name LIKE %s OR users.email LIKE %s OR users.mobile LIKE %s OR appointment.status LIKE %s)"  # Fix applied here
                
                query = base_query + f"""
                    AND {database_name}.appointment.status != 'pending'
                    ORDER BY {database_name}.appointment.id DESC
                    LIMIT %s OFFSET %s
                """
                params.append(results_per_page)
                params.append(offset)
            elif type == 'pending':
                count_query = f"""
                    SELECT COUNT(*)
                    FROM
                        {database_name}.appointment
                    INNER JOIN
                        {database_name}.users
                    ON
                        {database_name}.users.id = {database_name}.appointment.visitors_id
                    WHERE
                        {database_name}.appointment.employee_id = %s
                        AND {database_name}.appointment.status = 'pending'
                """
                if search_term:
                    count_query += " AND (users.first_name LIKE %s OR users.email LIKE %s OR users.mobile LIKE %s OR appointment.status LIKE %s)"  # Fix applied here
                
                query = base_query + f"""
                    AND {database_name}.appointment.status = 'pending'
                    LIMIT %s OFFSET %s
                """
                params.append(results_per_page)
                params.append(offset)
            else:
                return Response({"status": "false", "message": "Invalid type value. Allowed values are 'all' or 'pending'."})

            try:
                # Execute the count query
                if search_term:
                    cursor.execute(count_query, [employee_id] + [f"%{search_term}%"] * 4)
                else:
                    cursor.execute(count_query, [employee_id])
                    
                total_items_result = cursor.fetchone()
                total_items = total_items_result[0] if total_items_result else 0

                # Calculate total pages
                total_pages = (total_items // results_per_page) + (1 if total_items % results_per_page else 0)

                # Execute the main query with pagination
                cursor.execute(query, params)
                columns = [col[0] for col in cursor.description]
                results = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
                for result in results:
                    result['visitors_image'] = (
                        base_image_url + result['visitors_image']
                        if result.get('visitors_image') else placeholder_image_url
                    )

            except Exception as e:
                print(f"Error: {e}")
                return Response({"status": "false", "message": f"An error occurred: {e}"})

        pagination = {
            'current_page': page,
            'per_page_count': results_per_page,
            'total': total_items,
            'last_page': total_pages,
            'search_term': search_term
        }

        if results:
            return Response({
                "status": "true",
                "data": results,
                "pagination": pagination,
            })
        else:
            pagination['total'] = 0
            return Response({
                "status": "false",
                "message": "No data found.",
                "data": results,
                "pagination": pagination,
            })




class status_changesView(APIView):
    def post(self, request):
        constants = my_constants(request)
        database_name = constants['database_name']
        from_email = constants['From_Email']

        employee_id = request.data.get('appointment_id', '')
        status = request.data.get('status', '')
        reason = request.data.get('reason', '')
        date = request.data.get('date', '')
        time = request.data.get('time', '')

        # Input validation
        if not employee_id:
            return Response({"status": "false", "message": "Employee ID is required."})
        if status not in ['accepted', 'rejected']:
            return Response({"status": "false", "message": "Invalid status provided."})

        appointment_exist = appointment.objects.filter(id=employee_id).first()
        if not appointment_exist:
            return Response({"status": "false", "message": "Appointment not found."})

        if status == 'rejected' and (not reason or not date or not time):
            return Response({"status": "false", "message": "Reason, date, and time are required for rejected status."})

        try:
            with connection.cursor() as cursor:
                # Generate current timestamp for updated_at
                current_timestamp = datetime.now().strftime('%y-%m-%d %H:%M:%S')

                # Update the status in vms.appointment
                cursor.execute(f"""
                            UPDATE {database_name}.appointment
                            SET status = %s, employee_approval = %s, updated_at = %s
                            WHERE id = %s
                        """, [status, status, current_timestamp, employee_id])

                # If status is 'rejected', insert into appointment_reject
                if status == 'rejected':
                    cursor.execute(f"""
                                INSERT INTO {database_name}.appointment_reject (appointment_id, reason, date, time, created_at, updated_at)
                                VALUES (%s, %s, %s, %s, %s,%s)
                            """, [employee_id, reason, date, time, current_timestamp,current_timestamp])

                # Fetch appointment details for email
                cursor.execute(f"""
                            SELECT 
                                {database_name}.appointment.*,
                                visitors.first_name AS visitors_first_name,
                                visitors.email AS visitors_email,
                                visitors.mobile AS visitors_mobile,
                                visitors.image AS visitors_image,
                                employees.first_name AS employee_first_name,
                                employees.email AS employees_email,
                                employees.mobile AS employees_mobile,
                                employees.image AS employees_image
                            FROM 
                                {database_name}.appointment
                            INNER JOIN 
                                {database_name}.users AS visitors ON visitors.id = {database_name}.appointment.visitors_id
                            INNER JOIN 
                                {database_name}.users AS employees ON employees.id = {database_name}.appointment.employee_id
                            WHERE 
                                {database_name}.appointment.id = %s
                        """, [employee_id])

                database_all_data = cursor.fetchone()
                if not database_all_data:
                    return Response({"status": "false", "message": "Failed to retrieve appointment details."})

                columns = [col[0] for col in cursor.description]
                all_data = dict(zip(columns, database_all_data))

                # Send email if recipient_email exists
                recipient_email = all_data.get('visitors_email', '')
                if recipient_email:
                    context = {
                        'status': status,
                        'visitor_name': all_data.get('visitors_first_name', ''),
                        'employee_name': all_data.get('employee_first_name', ''),
                    }
                    if status == 'rejected':
                        context.update({'reason': reason, 'date': date, 'time': time})
                    subject = 'Appointment Accepted' if status == 'accepted' else 'Appointment Rejected'
                    template_name = 'api_templates/status_change_email.html'
                    message = render_to_string(template_name, context)
                    plain_message = strip_tags(message)

                    mail = EmailMultiAlternatives(subject, plain_message, from_email, [recipient_email])
                    mail.attach_alternative(message, "text/html")
                    mail.send()

                return Response({"status": "true", "message": "Status changed successfully."})

        except IntegrityError as e:
            return Response({"status": "false", "message": f"Database integrity error: {str(e)}"})
        except Exception as e:
            return Response({"status": "false", "message": f"An error occurred: {str(e)}"})
        







class visitore_detailView(APIView):
    def post(self, request):
        constants = my_constants(request)
        database_name = constants['database_name']
        appointment_id = request.data.get('appointment_id', '')
        
        if not appointment_id:
            return Response({"status": "false", "message": "Appointment ID is required."})

        try:
            appointment_id = int(appointment_id)
        except ValueError:
            return Response({"status": "false", "message": "Appointment ID must be a number."})
        
        base_image_url = constants['IMAGEPATH']
        # Define a placeholder image URL
        placeholder_image_url =  constants['DEFAULT_IMAGE']
        appointment_exist = appointment.objects.filter(id=appointment_id)
        if appointment_exist:
            with connection.cursor() as cursor:
                cursor.execute(f"""
                    SELECT    
                        {database_name}.appointment.*,
                        visitors.first_name AS visitor_first_name, 
                        visitors.email AS visitor_email, 
                        visitors.mobile AS visitor_mobile,
                        visitors.image as visitors_image,
                        employees.first_name AS employee_first_name, 
                        employees.email AS employee_email, 
                        employees.mobile AS employee_mobile
                        
                    FROM 
                        {database_name}.appointment 
                        INNER JOIN {database_name}.users AS visitors ON visitors.id = {database_name}.appointment.visitors_id
                        INNER JOIN {database_name}.users AS employees ON employees.id = {database_name}.appointment.employee_id
                    WHERE 
                        {database_name}.appointment.id = %s;
                    ;
                """, [appointment_id])

                columns = [col[0] for col in cursor.description]

                results = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
                for result in results:
                    if 'visitors_image' in result and result['visitors_image']:
                        # If the image URL is not empty or invalid, prepend the base URL
                        result['visitors_image'] = base_image_url + result['visitors_image']
                    else:
                        # Use the placeholder image URL if no image is provided
                        result['visitors_image'] = placeholder_image_url
                with connection.cursor() as cursor:
                    cursor.execute(f"""
                        SELECT  
                            {database_name}.appointment.*,
                            appointmentes.reason AS appointment_reason, 
                            appointmentes.date AS appointment_date, 
                            appointmentes.time AS appointment_time,
                            appointmentes.created_at AS  appointment_created_at, 
                            appointmentes.updated_at AS appointment_updated_at
                            
                        FROM 
                            {database_name}.appointment 
                            INNER JOIN {database_name}.appointment_reject AS appointmentes ON appointment_id = {database_name}.appointment.id
                        WHERE 
                            {database_name}.appointment.id = %s;
                        ;
                    """, [appointment_id])

                    columns = [col[0] for col in cursor.description]
                    appointment_reason = [
                        dict(zip(columns, row))
                        for row in cursor.fetchall()
                    ]
                
                return Response({
                        "status": "true",
                        "data": results,
                        "appointment_reason":appointment_reason
                        
                    })
        else:
            return Response({"status": "false", "message": "Appointment not found."})