from django.shortcuts import render, redirect
from django.views import View
from django.contrib import messages
from django.contrib.auth.mixins import LoginRequiredMixin
from django.db.models import F, FloatField, Count
from django.db.models.functions import Cast, Round
from django.http import JsonResponse
import pandas as pd
from datetime import datetime
from hr_department.models import Team
from users.models import Csr_Partners, Partner_Organizations, Users
from .models import Deliverables, Program_Types, Progress, Projects, Programs
from country.models import States, Districts


class ProgramTypeView(LoginRequiredMixin, View):
    
    def post(self, request):
        program_type = Program_Types()
        program_type.program_type = request.POST['program_type']
        program_type.save()
        return redirect('userDepartmentView')
    

    def get(self, request):
        try:
            program_type = Program_Types.objects.get(id=request.GET['id'])
            program_type.delete()
            messages.success(request, "Program type deleted successfully.")
            return redirect('userDepartmentView')
        except Program_Types.DoesNotExist:
            messages.error(request, "Program type doesn't exists.")
            return redirect('userDepartmentView')


class ProjectView(LoginRequiredMixin, View):
    
    def post(self, request):
        project = Projects()
        project.program_type_id = request.POST['program_type']
        project.project = request.POST['project']
        project.save()
        return redirect('userDepartmentView')
    

    def get(self, request):
        try:
            project = Projects.objects.get(id=request.GET['id'])
            project.delete()
            messages.success(request, "Project deleted successfully.")
            return redirect('userDepartmentView')
        except Program_Types.DoesNotExist:
            messages.error(request, "Project doesn't exists.")
            return redirect('userDepartmentView')


class ProgramView(LoginRequiredMixin, View):

    def get(self, request):
        program_types = Program_Types.objects.all()
        states = States.objects.all()
        csr_partners = Csr_Partners.objects.all()
        partner_organizations = Partner_Organizations.objects.all()
        members = Team.objects.all()
        return render(request, "add-program.html", {"program_types": program_types, 'states' : states, 'csr_partners' : csr_partners, 'partner_organizations' : partner_organizations, 'members' : members})
    
    def post(self, request):
        program = Programs()
        
        program.program_type_id = request.POST.get('program_type')
        program.project_id = request.POST.get('project')
        program.state_id = request.POST.get('state')
        program.district_id = request.POST.get('district')
        program.csr_partner_id = request.POST.get('csr_partner')
        program.partner_id = request.POST.get('partner_organization')
        program.from_date = request.POST.get('from_date')
        program.team_id = request.POST.get('team')
        program.beneficiary_name = request.POST.get('beneficiary_name')
        program.age = request.POST.get('age')
        program.gender = request.POST.get('gender')
        program.caste = request.POST.get('caste')
        program.mobile = request.POST.get('mobile')
        program.religion = request.POST.get('religion')
        program.occupation = request.POST.get('occupation')
        program.family_income = request.POST.get('family_income')
        program.differently_abled = request.POST.get('differently_abled')
        program.category = request.POST.get('category')
        program.document_apply = ",".join(request.POST.getlist('document_apply'))
        program.scheme_apply = ",".join(request.POST.getlist('scheme_apply'))
        program.support = request.POST.get('support')
        program.describe_support = request.POST.get('describe_support')
        program.area = request.POST.get('area')
        program.session_type = request.POST.get('session_type')
        program.ngo = request.POST.get('ngo')
        program.organisation_representative_name = request.POST.get('organisation_representative_name')
        program.organisation_type = request.POST.get('organisation_type')
        program.working_period = request.POST.get('working_period')
        program.no_of_members = request.POST.get('no_of_members')
        program.working_area = request.POST.get('working_area')
        program.organisation_support = request.POST.get('organisation_support')
        program.save()
        messages.success(request, "Program added successfully.")
        return redirect('programs')


class EditProgramView(LoginRequiredMixin, View):

    def get(self, request, id):
        program = Programs.objects.get(id = id)
        program_types = Program_Types.objects.all()
        states = States.objects.all()
        csr_partners = Csr_Partners.objects.all()
        partner_organizations = Partner_Organizations.objects.all()
        members = Team.objects.all()
        return render(request, "edit-program.html", {"program_types": program_types, 'states' : states, 'csr_partners' : csr_partners, 'partner_organizations' : partner_organizations, 'members' : members, 'program' : program})
    

    def post(self, request, id):
        program = Programs.objects.get(id = id)
        program.program_type_id = request.POST.get('program_type')
        program.project_id = request.POST.get('project')
        program.state_id = request.POST.get('state')
        program.district_id = request.POST.get('district')
        program.csr_partner_id = request.POST.get('csr_partner')
        program.partner_id = request.POST.get('partner_organization')
        program.from_date = request.POST.get('from_date')
        program.team_id = request.POST.get('team')
        program.beneficiary_name = request.POST.get('beneficiary_name')
        program.age = request.POST.get('age')
        program.gender = request.POST.get('gender')
        program.caste = request.POST.get('caste')
        program.mobile = request.POST.get('mobile')
        program.religion = request.POST.get('religion')
        program.occupation = request.POST.get('occupation')
        program.family_income = request.POST.get('family_income')
        program.differently_abled = request.POST.get('differently_abled')
        program.category = request.POST.get('category')
        program.document_apply = ",".join(request.POST.getlist('document_apply'))
        program.scheme_apply = ",".join(request.POST.getlist('scheme_apply'))
        program.support = request.POST.get('support')
        program.describe_support = request.POST.get('describe_support')
        program.area = request.POST.get('area')
        program.session_type = request.POST.get('session_type')
        program.ngo = request.POST.get('ngo')
        program.organisation_representative_name = request.POST.get('organisation_representative_name')
        program.organisation_type = request.POST.get('organisation_type')
        program.working_period = request.POST.get('working_period')
        program.no_of_members = request.POST.get('no_of_members')
        program.working_area = request.POST.get('working_area')
        program.organisation_support = request.POST.get('organisation_support')
        program.save()
        messages.success(request, "Program added successfully.")
        return redirect('programs')


class DeleteProgramView(LoginRequiredMixin, View):

    def get(self, request, id):
        try:
            program = Programs.objects.get(id = id)
            program.delete()
            messages.success(request, "Program deleted successfully.")
            return redirect('programs')
        except Programs.DoesNotExist:
            messages.error(request, "Program doesn't exists.")
            return redirect('programs')


class ProgramsView(LoginRequiredMixin, View):

    def get(self, request):
        program_types = Program_Types.objects.all()
        programs = Programs.objects.all()

        if 'program_type' in request.GET and request.GET['program_type'] != "All":
            programs = programs.filter(program_type_id = request.GET['program_type'])
            program_type = request.GET['program_type']
        else:
            program_type = 'All'
        
        if 'project' in request.GET and request.GET['project'] != "All":
            programs = programs.filter(project_id = request.GET['project'])
            project = request.GET['project']
        else:
            project = 'All'

        if 'start_date' in request.GET and request.GET['start_date']:
            programs = programs.filter(created_at__date__gte = request.GET['start_date'])
            start_date = request.GET['start_date']
        else:
            start_date = ''

        if 'end_date' in request.GET and request.GET['end_date']:
            programs = programs.filter(created_at__date__lte = request.GET['end_date'])
            end_date = request.GET['end_date']
        else:
            end_date = ''
        
        if request.user.user_type_id == 2:
            programs = programs.filter(csr_partner__user__id = request.user.id)
        elif request.user.user_type_id == 3:
            programs = programs.filter(partner__user__id = request.user.id)

        return render(request, "programs.html", {'programs' : programs, 'program_types' : program_types, 'program_type' : program_type, 'project' : project, 'start_date' : start_date, 'end_date' : end_date})
    


class ProgramsChartView(LoginRequiredMixin, View):

    def get(self, request):
        # Get counts grouped by state as array
        programs = Programs.objects.all()
       

        if 'program_type' in request.GET and request.GET['program_type'] != "All":
            programs = programs.filter(program_type_id = request.GET['program_type'])
            
        
        if 'project' in request.GET and request.GET['project'] != "All":
            programs = programs.filter(project_id = request.GET['project'])
           
        if 'start_date' in request.GET and request.GET['start_date']:
            programs = programs.filter(created_at__date__gte = request.GET['start_date'])
           

        if 'end_date' in request.GET and request.GET['end_date']:
            programs = programs.filter(created_at__date__lte = request.GET['end_date'])
            
        
        if request.user.user_type_id == 2:
            programs = programs.filter(csr_partner__user__id = request.user.id)
        elif request.user.user_type_id == 3:
            programs = programs.filter(partner__user__id = request.user.id)

        
        state_counts = (
            programs.values('state__state')
            .annotate(count=Count('id'))
            .order_by('state__state')
        )

        state_result = [
            {'name': item['state__state'] or 'Unknown', 'y': item['count']}
            for item in state_counts
        ]

        # Get counts grouped by district as array
        district_counts = (
            programs.values('district__district')
            .annotate(count=Count('id'))
            .order_by('district__district')
        )
        district_result = [
            {'name': item['district__district'] or 'Unknown', 'y': item['count']}
            for item in district_counts
        ]

        # Get counts grouped by project type as array
        project_type_counts = (
            programs.values('program_type__program_type')
            .annotate(count=Count('id'))
            .order_by('program_type__program_type')
        )
        project_type_result = [
            {'name': item['program_type__program_type'] or 'Unknown', 'y': item['count']}
            for item in project_type_counts
        ]

        # Get counts grouped by team as array
        team_counts = (
            programs.values('team__name')
            .annotate(count=Count('id'))
            .order_by('team__name')
        )
        team_result = [
            {'name': item['team__name'] or 'Unknown', 'y': item['count']}
            for item in team_counts
        ]

        # Get counts grouped by age as array
        age_counts = (
            programs.values('age')
            .annotate(count=Count('id'))
            .order_by('age')
        )
        age_result = [
            {'name': item['age'] or 'Unknown', 'y': item['count']}
            for item in age_counts
        ]

        # Get counts grouped by caste as array
        caste_counts = (
            programs.values('caste')
            .annotate(count=Count('id'))
            .order_by('caste')
        )
        caste_result = [
            {'name': item['caste'] or 'Unknown', 'y': item['count']}
            for item in caste_counts
        ]

        # Get counts grouped by religion as array
        religion_counts = (
            programs.values('religion')
            .annotate(count=Count('id'))
            .order_by('religion')
        )
        religion_result = [
            {'name': item['religion'] or 'Unknown', 'y': item['count']}
            for item in religion_counts
        ]

        # Get counts grouped by gender as array
        gender_counts = (
            programs.values('gender')
            .annotate(count=Count('id'))
            .order_by('gender')
        )
        gender_result = [
            {'name': item['gender'] or 'Unknown', 'y': item['count']}
            for item in gender_counts
        ]

        # Get counts grouped by occupation as array
        occupation_counts = (
            programs.values('occupation')
            .annotate(count=Count('id'))
            .order_by('occupation')
        )
        occupation_result = [
            {'name': item['occupation'] or 'Unknown', 'y': item['count']}
            for item in occupation_counts
        ]

        # Get counts grouped by family_income as array
        income_counts = (
            programs.values('family_income')
            .annotate(count=Count('id'))
            .order_by('family_income')
        )
        income_result = [
            {'name': item['family_income'] or 'Unknown', 'y': item['count']}
            for item in income_counts
        ]

        return JsonResponse({
            'by_state': state_result,
            'by_district': district_result,
            'by_project_type': project_type_result,
            'by_team': team_result,
            'by_age': age_result,
            'by_caste': caste_result,
            'by_religion': religion_result,
            'by_gender': gender_result,
            'by_occupation': occupation_result,
            'by_family_income': income_result
        }, safe=False)











class ProjectsView(LoginRequiredMixin, View):

    def get(self, request, id):
        projects = Projects.objects.filter(program_type_id = id)
        return render(request, "projects.html", {"projects": projects})
    

class DeliverablesView(LoginRequiredMixin, View):

    def get(self, request):
        projects = Projects.objects.all()
        
        deliverables = Deliverables.objects.all().annotate(
            completion=Round(
                Cast(F('achieved'), FloatField()) / Cast(F('target'), FloatField()) * 100, 2
            )
        )

        if 'project' in request.GET and request.GET['project'] != "All":
            deliverables = deliverables.filter(project_id = request.GET['project'])
            project = request.GET['project']
        else:
            project = 'All'
        # progress_tracker = Progress.objects.all()
        return render(request, "deliverables.html", {"deliverables" : deliverables, 'projects' : projects, 'project' : project})
    

    def post(self, request):
        try:
            excel_file = request.FILES["deliverables"]
            df = pd.read_excel(excel_file, engine='openpyxl')
            for idx, row in df.iterrows():
                deliverable = Deliverables()
                deliverable.project_id = request.POST.get('project_id')
                deliverable.key_indicator = row['Key Indicator']
                deliverable.target = row['Target']
                deliverable.achieved = row['Achieved']
                deliverable.remarks = row['Remarks']
                deliverable.save()
            messages.success(request, "Deliverables added successfully.")
            return redirect('DeliverablesView')
        except Exception as e:
            print(e)
            messages.error(request, "Something went wrong. Please try again later")
            return redirect('DeliverablesView')


class ProgressView(LoginRequiredMixin, View):

    def post(self, request):
        try:
            excel_file = request.FILES["progress"]
            df = pd.read_excel(excel_file, engine='openpyxl')
            for idx, row in df.iterrows():
                progress = Progress()
                progress.date = row['Date']
                progress.location = row['Location']
                progress.activity = row['Activity']
                progress.shg_covered = row['SHGs Covered']
                progress.members_enrolled = row['Members Enrolled']
                progress.schemes_facilitated = row['Schemes Facilitated']
                progress.legal_docs_processed = row['Legal Docs Processed']
                progress.save()
            messages.success(request, "Progress added successfully.")
            return redirect('programs')
        except Exception as e:
            print(e)
            messages.error(request, "Something went wrong. Please try again later")
            return redirect('programs')



class ProgramsImportView(LoginRequiredMixin, View):

    def post(self, request):
        try:
            excel_file = request.FILES["programs"]
            df = pd.read_excel(excel_file, engine='openpyxl')

            for idx, row in df.iterrows():
               
                try:
                    program_type = Program_Types.objects.get(program_type = row['Program Type'])
                except Exception as e:
                    program_type = None

                try:
                    project = Projects.objects.get(project = row['Project'])
                except Exception as e:
                    project = None

                try:
                    state = States.objects.get(state = row['State'])
                except Exception as e:
                    state = None

                try:
                    district = Districts.objects.get(district = row['District'])
                except Exception as e:
                    district = None

                try:
                    csr_partner = Csr_Partners.objects.get(company_name = row['Donor Organisation'])
                except Exception as e:
                    csr_partner = None

                try:
                    partner_organization = Partner_Organizations.objects.get(company_name = row['Support Partner'])
                except Exception as e:
                    partner_organization = None

                try:
                    team = Team.objects.get(name = row['Team Member'])
                except Exception as e:
                    team = None

                programs = Programs()

                if program_type is not None:
                    programs.program_type_id = program_type.id
                else:
                    programs.program_type_id = ""


                if project is not None:
                    programs.project_id = project.id
                else:
                    programs.project_id = ""


                if state is not None:
                    programs.state_id = state.id
                else:
                    programs.state_id = ""


                if district is not None:
                    programs.district_id = district.id
                else:
                    programs.district_id = ""


                if csr_partner is not None:
                    programs.csr_partner_id = csr_partner.id
                else:
                    programs.csr_partner_id = ""


                if partner_organization is not None:
                    programs.partner_id = partner_organization.id
                else:
                    programs.partner_id = ""


                if team is not None:
                    programs.team_id = team.id
                else:
                    programs.team_id = ""


                programs.from_date = datetime.strptime(row['From Date'], "%d/%m/%Y").strftime("%Y-%m-%d")
                programs.mobile = row['Mobile']
                programs.beneficiary_name = row['Beneficiary Name']
                programs.age = row['Age']
                programs.gender = row['Gender']
                programs.caste = row['Caste']
                programs.religion = row['Religion']
                programs.occupation = row['Occupation']
                programs.family_income = row['Family Income']

                # programs.document_apply = row['Document Apply']
                # programs.scheme_apply = row['Scheme Apply']
                # programs.support = row['Support']
                # programs.describe_support = row['Describe Support']
                # programs.area = row['Area']
                # programs.session_type = row['Session Type']
                # programs.ngo = row['NGO']
                # programs.organisation_representative_name = row['Organisation Representative Name']
                # programs.organisation_type = row['Organisation Type']
                # programs.working_period = row['Working Period']
                # programs.no_of_members = row['No Of Members']
                # programs.working_area = row['Working Area']
                # programs.organisation_support = row['Organisation Support']

                try:
                    Programs.objects.get(program_type_id = programs.program_type_id, project_id = programs.project_id, state_id = programs.state_id, district_id = programs.district_id, csr_partner_id = programs.csr_partner_id, partner_id = programs.partner_id, from_date = programs.from_date, team = programs.team_id, beneficiary_name = programs.beneficiary_name, age = programs.age, gender = programs.gender, caste = programs.caste, mobile = programs.mobile, religion = programs.religion, occupation = programs.occupation, family_income = programs.family_income)
                except Exception as e:
                    print(e)
                    programs.save()
               
            messages.success(request, "Deliverables added successfully.")
            return redirect('DeliverablesView')
        except Exception as e:
            print(e)
            messages.error(request, "Something went wrong. Please try again later")
            return redirect('DeliverablesView')




class DeleteDeliverableView(LoginRequiredMixin, View):

    def get(self, request, id):
        try:
            deliverable = Deliverables.objects.get(id = id)
            deliverable.delete()
            messages.success(request, "Deliverable deleted successfully.")
            return redirect('DeliverablesView')
        except Deliverables.DoesNotExist:
            messages.error(request, "Deliverable doesn't exists.")
            return redirect('DeliverablesView')