# Built-in modules
import json
import random
import string
import calendar
from datetime import datetime, timedelta
from io import BytesIO
from collections import Counter, defaultdict
from functools import wraps

# Third-party modules
import bcrypt
import psycopg2
import openpyxl
from openpyxl.styles import NamedStyle

# Django modules
from django.db import connection
from django.http import HttpResponse, HttpResponseForbidden, JsonResponse
from django.shortcuts import render, redirect
from django.views.decorators.csrf import csrf_protect

# UNTUK LOGIN LOGOUT
@csrf_protect
def login(request):
    if request.method == 'POST':
        uname = request.POST.get('uname')
        password = request.POST.get('password').encode('utf-8')  # harus bytes

        user = None
        role = None

        with connection.cursor() as cursor:
            # Cek di tabel karyawan
            cursor.execute("SELECT nama_karyawan, jabatan, password FROM public.karyawan WHERE login_web = %s", [uname])
            row = cursor.fetchone()
            if row and bcrypt.checkpw(password, row[2].encode('utf-8')):
                user = row[0]
                role = row[1]

            # if not user:
            #     cursor.execute("SELECT user_name, jabatan, password, wok_id FROM public.team_leader WHERE login_web = %s", [uname])
            #     row = cursor.fetchone()
            #     if row and bcrypt.checkpw(password, row[2].encode('utf-8')):
            #         user = row[0]
            #         role = row[1]
            #         request.session['tl_name'] = user  # Simpan nama TL untuk filter otomatis
            #         request.session['wok_id_tl'] = row[3]  # Simpanwok_id

            # # Jika belum ditemukan, cek di sales_force
            # if not user:
            #     cursor.execute("SELECT user_name, jabatan, password, wok_id FROM public.sales_force WHERE login_web = %s", [uname])
            #     row = cursor.fetchone()
            #     if row and bcrypt.checkpw(password, row[2].encode('utf-8')):
            #         user = row[0]
            #         role = row[1]
            #         request.session['sf_name'] = user  # Simpan nama TL untuk filter otomatis
            #         request.session['wok_id_sf'] = row[3]  # Simpanwok_id

            # # Cek di mitra
            # if not user:
            #     cursor.execute("SELECT mitra_name, password, wok_id FROM public.mitra WHERE login_web = %s", [uname])
            #     row = cursor.fetchone()
            #     if row and bcrypt.checkpw(password, row[1].encode('utf-8')):
            #         user = row[0]
            #         role = 'Mitra'
            #         request.session["wok_mitra"] = row[2]

        if user:
            request.session['uname'] = uname
            request.session['user'] = user
            request.session['role'] = role
            return redirect('/datadigital/')  # Sesuaikan dengan tujuan
        else:
            return render(request, 'adminchannel/login.html', {'error': 'Username atau password salah'})

    return render(request, 'adminchannel/login.html')

def login_required(view_func):
    def wrapper(request, *args, **kwargs):
        if 'uname' not in request.session:
            return redirect('')
        return view_func(request, *args, **kwargs)
    return wrapper

def role_allowed(*allowed_roles):
    def decorator(view_func):
        @wraps(view_func)
        def wrapper(request, *args, **kwargs):
            if 'role' not in request.session:
                return redirect('')
            if request.session['role'] not in allowed_roles:
                return HttpResponseForbidden("Anda tidak memiliki izin untuk mengakses halaman ini.")
            return view_func(request, *args, **kwargs)
        return wrapper
    return decorator

def role_not_allowed(*not_allowed_roles):
    def decorator(view_func):
        @wraps(view_func)
        def wrapper(request, *args, **kwargs):
            if 'role' not in request.session:
                return redirect('')
            if request.session['role'] in not_allowed_roles:
                return HttpResponseForbidden("Anda tidak memiliki izin untuk mengakses halaman ini.")
            return view_func(request, *args, **kwargs)
        return wrapper
    return decorator

def user_logout(request):
    request.session.flush()  # Hapus semua session user
    return redirect('login')  # Kembali ke halaman login

# #UNUTK DATA PS DIGITAL
# def datadigital(request):
#     return render(request,"adminchannel/datadigital.html")

@login_required
def managedigital(request):
    # Ambil parameter dari request
    filter_area = request.GET.get('filter-area')
    filter_area = filter_area.strip() if filter_area else None

    filter_bulan = request.GET.get('filter_bulan')
    filter_bulan = int(filter_bulan.strip()) if filter_bulan else None
    # filter_bulan = int(filter_bulan.strip()) if filter_bulan else datetime.now().month

    filter_agent = request.GET.get('filter-agent')
    filter_agent = filter_agent.strip() if filter_agent else None

    filter_tahun = request.GET.get('filter_tahun')
    filter_tahun = int(filter_tahun.strip()) if filter_tahun else None
    # filter_tahun = int(filter_tahun.strip()) if filter_tahun else datetime.now().year

    filter_branch = request.GET.get('filter-branch')
    filter_branch = filter_branch.strip() if filter_branch else None

    filter_status_order = request.GET.get('filter-status-order')
    filter_status_order = filter_status_order.strip() if filter_status_order else None

    filter_status_case = request.GET.get('filter-status-case')
    filter_status_case = filter_status_case.strip() if filter_status_case else None

    filter_channel = request.GET.get('filter-channel')
    filter_channel = filter_channel.strip() if filter_channel else None

    search_track_order = request.GET.get('search-track-order')
    search_track_order = search_track_order.strip() if search_track_order else None

    # Query utama
    query = """
        SELECT ps.ps_id, 
               ps.tanggal_created_at,
               ps.tanggal_updated_at,
               sf.nama_sales, 
               ag.nama_agent,
               ag.channel,
               ps.track_id,
               ps.kontak_pelanggan,
               br.nama_branch,
               ar.nama_area,
               ps.status_order,
               ps.kendala_fallout,
               ps.keterangan,
               ps.status_case,
               ps.nama_paket,
               ps.arpu
        FROM data_ps ps
        JOIN sales sf ON ps.sf_id = sf.sf_id
        JOIN agent ag ON ps.agent_id = ag.agent_id
        JOIN branch br ON ps.branch_id = br.branch_id
        JOIN area ar ON ps.area_id = ar.area_id
        WHERE 1=1
    """

    params = []

    # Filter dinamis
    if filter_area:
        query += " AND LOWER(ar.nama_area) ILIKE %s"
        params.append(f"%{filter_area.lower()}%")
    if filter_agent:
        query += " AND LOWER(ag.nama_agent) ILIKE %s"
        params.append(f"%{filter_agent.lower()}%")
    if filter_branch:
        query += " AND LOWER(br.nama_branch) ILIKE %s"
        params.append(f"%{filter_branch.lower()}%")
    if filter_bulan:
        query += " AND EXTRACT(MONTH FROM ps.tanggal_created_at) = %s"
        params.append(filter_bulan)
    if filter_tahun:
        query += " AND EXTRACT(YEAR FROM ps.tanggal_created_at) = %s"
        params.append(filter_tahun)
    if filter_status_order:
        query += " AND ps.status_order = %s"
        params.append(filter_status_order)
    if filter_status_case:
        query += " AND ps.status_case = %s"
        params.append(filter_status_case)
    if filter_channel:
        query += " AND LOWER(ag.channel) ILIKE %s"
        params.append(f"%{filter_channel.lower()}%")
    if search_track_order:
        query += " AND ps.track_id = %s"
        params.append(search_track_order)

    # Urutkan berdasarkan tanggal terbaru
    query += " ORDER BY ps.tanggal_created_at DESC"

    with connection.cursor() as cursor:
        # Eksekusi query utama
        cursor.execute(query, params)
        columns = [col[0] for col in cursor.description]
        data = [dict(zip(columns, row)) for row in cursor.fetchall()]

        # Ambil area
        cursor.execute("""
            SELECT DISTINCT ar.nama_area
            FROM area ar
            JOIN data_ps ps ON ps.area_id = ar.area_id
            ORDER BY ar.nama_area
        """)
        area_list = [row[0] for row in cursor.fetchall()]

        # Ambil channel dari agent
        cursor.execute("""
            SELECT DISTINCT ag.channel
            FROM agent ag
            WHERE ag.channel IS NOT NULL
            ORDER BY ag.channel
        """)
        channel_list = [row[0] for row in cursor.fetchall()]

    # Context
    context = {
        'data_ps': data,
        'bulan_list': [
            ('1', 'Januari'), ('2', 'Februari'), ('3', 'Maret'), ('4', 'April'),
            ('5', 'Mei'), ('6', 'Juni'), ('7', 'Juli'), ('8', 'Agustus'),
            ('9', 'September'), ('10', 'Oktober'), ('11', 'November'), ('12', 'Desember'),
        ],
        'area_list': area_list,
        'channel_list': channel_list,
        'status_case_list': ['Open', 'Close'],
    }

    return render(request, "adminchannel/managedigital.html", context)

@login_required
def datadigital(request):
    filters = {
        "agent": request.GET.get("agent"),
        "bulan": request.GET.get("bulan") or datetime.now().month,
        "tahun": request.GET.get("tahun") or datetime.now().year,
        "channel": request.GET.get("channel"),
        "branch": request.GET.get("branch"),
        "area": request.GET.get("area"),
        "status_order": request.GET.get("status_order"),
        "status_case": request.GET.get("status_case"),
    }

    query = """
        SELECT 
            ps.ps_id, 
            ps.tanggal_created_at,
            ps.tanggal_updated_at,
            sf.nama_sales, 
            ag.nama_agent,
            ag.channel,
            ps.track_id,
            ps.kontak_pelanggan,
            br.nama_branch,
            ar.nama_area,
            ps.status_order,
            ps.status_case,
            ps.kendala_fallout,
            ps.keterangan,
            ps.nama_paket,
            ps.arpu
        FROM data_ps ps
        JOIN sales sf ON ps.sf_id = sf.sf_id
        JOIN agent ag ON ps.agent_id = ag.agent_id
        JOIN branch br ON ps.branch_id = br.branch_id
        JOIN area ar ON ps.area_id = ar.area_id
        WHERE 1=1
    """

    params = []

    if filters["bulan"]:
        query += " AND EXTRACT(MONTH FROM ps.tanggal_created_at) = %s"
        params.append(int(filters["bulan"]))
    if filters["tahun"]:
        query += " AND EXTRACT(YEAR FROM ps.tanggal_created_at) = %s"
        params.append(int(filters["tahun"]))
    if filters["agent"]:
        query += " AND ag.nama_agent ILIKE %s"
        params.append(f"%{filters['agent']}%")
    if filters["channel"]:
        query += " AND ag.channel ILIKE %s"
        params.append(f"%{filters['channel']}%")
    if filters["branch"]:
        query += " AND br.nama_branch ILIKE %s"
        params.append(f"%{filters['branch']}%")
    if filters["area"]:
        query += " AND ar.nama_area ILIKE %s"
        params.append(f"%{filters['area']}%")
    if filters["status_order"]:
        query += " AND ps.status_order ILIKE %s"
        params.append(f"%{filters['status_order']}%")
    if filters["status_case"]:
        query += " AND ps.status_case ILIKE %s"
        params.append(f"%{filters['status_case']}%")

    query += " ORDER BY ps.tanggal_created_at DESC"

    with connection.cursor() as cursor:
        cursor.execute(query, params)
        rows = cursor.fetchall()

        data_ps = [
            dict(zip([
                "ps_id", "tanggal_created_at", "tanggal_updated_at", "nama_sales", "nama_agent",
                "channel", "track_id", "kontak_pelanggan", "nama_branch", "nama_area",
                "status_order", "status_case", "kendala_fallout", "keterangan",
                "nama_paket", "arpu"
            ], row))
            for row in rows
        ]

        cursor.execute("SELECT DISTINCT ag.nama_agent FROM agent ag WHERE ag.nama_agent IS NOT NULL ORDER BY ag.nama_agent")
        agent_list = [row[0] for row in cursor.fetchall()]

        cursor.execute("SELECT DISTINCT ag.channel FROM agent ag WHERE ag.channel IS NOT NULL ORDER BY ag.channel")
        channel_list = [row[0] for row in cursor.fetchall()]

        cursor.execute("SELECT DISTINCT ar.nama_area FROM area ar JOIN data_ps ps ON ps.area_id = ar.area_id WHERE ar.nama_area IS NOT NULL ORDER BY ar.nama_area")
        area_list = [row[0] for row in cursor.fetchall()]

        cursor.execute("SELECT DISTINCT ps.status_order FROM data_ps ps WHERE ps.status_order IS NOT NULL ORDER BY ps.status_order")
        status_list = [row[0] for row in cursor.fetchall()]

    area_sales = sorted(Counter(row['nama_area'] for row in data_ps if row['nama_area']).items(), key=lambda x: x[1], reverse=True)
    agen_sales = sorted(Counter(row['nama_agent'] for row in data_ps if row['nama_agent']).items(), key=lambda x: x[1], reverse=True)
    status_sold = sorted(Counter(row['status_order'] for row in data_ps if row['status_order']).items(), key=lambda x: x[1], reverse=True)

    data_ps_completed = [row for row in data_ps if (row.get('status_case') or '').upper() == 'CLOSE']
    data_ps_not_completed = [row for row in data_ps if (row.get('status_case') or '').upper() == 'OPEN']

    paket_terjual = sorted(
        Counter(row['nama_paket'] for row in data_ps if (row['status_order'] or '').upper() == 'COMPLETED' and row['nama_paket']).items(),
        key=lambda x: x[1],
        reverse=True
    )

    return render(request, 'adminchannel/datadigital.html', {
        'data_ps_completed': data_ps_completed,
        'data_ps_not_completed': data_ps_not_completed,
        'selected_area': filters["area"],
        'selected_agent': filters["agent"],
        'selected_branch': filters["branch"],
        'selected_bulan': filters["bulan"],
        'selected_status_order': filters["status_order"],
        'selected_status_case': filters["status_case"],
        'agent_list': agent_list,
        'channel_list': channel_list,
        'area_list': area_list,
        'status_list': status_list,
        'status_sold': status_sold,
        'agen_sales': agen_sales,
        'area_sales': area_sales,
        'paket_terjual': paket_terjual,
        'filters': {k: v or '' for k, v in filters.items()},
    })
    
@login_required
def upload_excel(request):
    if request.method == 'POST' and request.headers.get('x-requested-with') == 'XMLHttpRequest':
        excel_file = request.FILES.get('excel_file')

        if not excel_file:
            return JsonResponse({"status": "error", "message": "File tidak ditemukan"}, status=400)

        if not excel_file.name.endswith(('.xlsx', '.xls')):
            return JsonResponse({"status": "error", "message": "Format file harus .xlsx atau .xls"}, status=400)

        try:
            wb = openpyxl.load_workbook(excel_file)
            sheet = wb.active

            expected_columns = [
                "tanggal_created_at", "agent_id", "sf_id", "track_id", "kontak_pelanggan",
                "status_order", "branch_id", "area_id", "tanggal_updated_at",
                "kendala_fallout", "keterangan", "status_case", "nama_paket", "arpu"
            ]

            # Validasi header
            for i, column in enumerate(expected_columns, start=1):
                cell_value = str(sheet.cell(row=1, column=i).value).strip().lower()
                if cell_value != column.lower():
                    return JsonResponse({
                        "status": "error",
                        "message": f"Kolom ke-{i} harus '{column}', tapi ditemukan '{cell_value}'"
                    }, status=400)

            success_count = 0

            for row in sheet.iter_rows(min_row=2, values_only=True):
                try:
                    (
                        tanggal_created_at, agent_id, sf_id, track_id, kontak_pelanggan,
                        status_order, branch_id, area_id, tanggal_updated_at,
                        kendala_fallout, keterangan, status_case, nama_paket, arpu
                    ) = row

                    # Konversi tanggal jika perlu
                    if not isinstance(tanggal_created_at, datetime):
                        tanggal_created_at = datetime.strptime(str(tanggal_created_at), "%Y-%m-%d %H:%M:%S")

                    if not isinstance(tanggal_updated_at, datetime):
                        tanggal_updated_at = datetime.strptime(str(tanggal_updated_at), "%Y-%m-%d %H:%M:%S")

                    with connection.cursor() as cursor:
                        cursor.execute("""
                            INSERT INTO data_ps (
                                tanggal_created_at, agent_id, sf_id, track_id, kontak_pelanggan,
                                status_order, branch_id, area_id, tanggal_updated_at,
                                kendala_fallout, keterangan, status_case, nama_paket, arpu
                            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        """, (
                            tanggal_created_at, agent_id, sf_id, track_id, kontak_pelanggan,
                            status_order, branch_id, area_id, tanggal_updated_at,
                            kendala_fallout, keterangan, status_case, nama_paket, arpu
                        ))
                        success_count += 1

                except Exception as row_error:
                    return JsonResponse({
                        "status": "error",
                        "message": f"Kesalahan pada baris {success_count+2}: {str(row_error)}"
                    }, status=400)

            return JsonResponse({"status": "success", "message": f"{success_count} data berhasil diupload"})

        except Exception as e:
            return JsonResponse({"status": "error", "message": f"Gagal memproses file: {str(e)}"}, status=500)

    return JsonResponse({"status": "error", "message": "Invalid request"}, status=400)

@login_required
def delete_data_ps(request):
    if request.method != "POST":
        return JsonResponse({"success": False, "error": "Metode tidak diperbolehkan."}, status=405)

    try:
        body_unicode = request.body.decode('utf-8')
        data = json.loads(body_unicode)
        row_id = data.get("id")

        if not row_id:
            return JsonResponse({"success": False, "error": "ID tidak ditemukan dalam request."}, status=400)

        with connection.cursor() as cursor:
            cursor.execute("DELETE FROM data_ps WHERE ps_id = %s", [row_id])
            if cursor.rowcount == 0:
                return JsonResponse({"success": False, "error": "Data dengan ID tersebut tidak ditemukan."}, status=404)

        return JsonResponse({"success": True, "message": f"Data dengan ID {row_id} berhasil dihapus."})

    except json.JSONDecodeError:
        return JsonResponse({"success": False, "error": "Format JSON tidak valid."}, status=400)

    except Exception as e:
        return JsonResponse({"success": False, "error": f"Terjadi kesalahan server: {str(e)}"}, status=500)

@login_required
def edit_data_ps(request):
    if request.method == "POST":
        try:
            data = json.loads(request.body)
            row_id = data.get("id")
            if not row_id:
                return JsonResponse({"success": False, "error": "ID tidak ditemukan."}, status=400)

            # Buat daftar field yang boleh diedit
            allowed_fields = [
                "bulan", "agent_id", "nama_agent", "track_id", "channel",
                "kontak_pelanggan", "status_order", "branch", "area",
                "status_case", "kendala_fallout", "keterangan"
            ]

            fields_to_update = []
            values = []

            for field in allowed_fields:
                if field in data:
                    fields_to_update.append(f"{field} = %s")
                    values.append(data[field])

            if not fields_to_update:
                return JsonResponse({"success": False, "error": "Tidak ada data untuk diupdate."})

            values.append(row_id)
            query = f"UPDATE data_ps SET {', '.join(fields_to_update)} WHERE ps_id = %s"

            with connection.cursor() as cursor:
                cursor.execute(query, values)

            return JsonResponse({"success": True})

        except Exception as e:
            return JsonResponse({"success": False, "error": str(e)}, status=500)

    return JsonResponse({"success": False, "error": "Metode tidak diizinkan."}, status=405)

@login_required
def edit_data_ps(request):
    if request.method != "POST":
        return JsonResponse({"success": False, "error": "Metode tidak diizinkan."}, status=405)

    try:
        data = json.loads(request.body)
        row_id = data.get("id")

        if not row_id:
            return JsonResponse({"success": False, "error": "ID tidak ditemukan."}, status=400)

        # Daftar kolom yang diperbolehkan untuk diedit
        allowed_fields = [
            "tanggal_created_at", "tanggal_updated_at", "agent_id", "sf_id", "track_id", 
            "kontak_pelanggan", "status_order", "branch_id", "area_id", "kendala_fallout", 
            "keterangan", "status_case", "nama_paket", "arpu"
        ]

        fields_to_update = []
        values = []

        for field in allowed_fields:
            if field in data:
                fields_to_update.append(f"{field} = %s")
                values.append(data[field])

        if not fields_to_update:
            return JsonResponse({"success": False, "error": "Tidak ada data yang diberikan untuk diperbarui."}, status=400)

        # Tambahkan id ke values untuk klausa WHERE
        values.append(row_id)

        query = f"UPDATE data_ps SET {', '.join(fields_to_update)} WHERE ps_id = %s"

        with connection.cursor() as cursor:
            cursor.execute(query, values)

        return JsonResponse({"success": True, "message": f"Data dengan ID {row_id} berhasil diperbarui."})

    except json.JSONDecodeError:
        return JsonResponse({"success": False, "error": "Format JSON tidak valid."}, status=400)

    except Exception as e:
        return JsonResponse({"success": False, "error": f"Kesalahan server: {str(e)}"}, status=500)

@login_required
def kpi(request):
    bulan = request.GET.get('bulan', datetime.now().strftime('%m'))
    tahun = request.GET.get('tahun', datetime.now().strftime('%Y'))

    query = """
        SELECT 
            kategori,
            COALESCE(target, 0) AS target,
            real,
            ach,
            COALESCE(bobot, 0) AS bobot,
            COALESCE(nilai, 0) AS nila
        FROM 
            kpi_agency
        WHERE 
            bulan = %s AND tahun = %s
        ORDER BY 
            kategori
    """

    params = [bulan, tahun]

    with connection.cursor() as cursor:
        cursor.execute(query, params)
        rows = cursor.fetchall()

    kpi_data = []
    total_bobot = 0
    total_nilai = 0

    for row in rows:
        kategori, target, real, ach, bobot, nilai = row
        kpi_data.append({
            'kategori': kategori,
            'target': target,
            'real': real,
            'ach': ach,
            'bobot': bobot,
            'nilai': nilai,
        })
        total_bobot += bobot
        total_nilai += nilai

    context = {
        'kpi_data': kpi_data,
        'total_bobot': total_bobot,
        'total_nilai': total_nilai,
        'bulan': bulan,
        'tahun': tahun,
    }

    return render(request, 'adminchannel/kpi.html', context)


def manage_kpi(request):
    return render(request, 'adminchannel/manage_kpi.html')

@login_required
def list_kpi_agency(request):
    bulan = request.GET.get('filter_bulan') or datetime.now().strftime('%m')
    tahun = request.GET.get('filter_tahun') or datetime.now().strftime('%Y')

    kpi_data = []

    if bulan and tahun:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT 
                    kpi_id, kategori, 
                    target, real, ach, bobot, nilai
                FROM kpi_agency
                WHERE bulan = %s AND tahun = %s
                ORDER BY kategori
            """, [bulan, tahun])

            rows = cursor.fetchall()

            total_bobot = 0
            total_nilai = 0
            for row in rows:
                kpi_id, kategori, target, real, ach, bobot, nilai = row
                kpi_data.append({
                    'kpi_id': kpi_id,
                    'kategori': kategori,
                    'target': target,
                    'real': real,
                    'ach': ach,
                    'bobot': bobot,
                    'nilai': nilai,
                })

                total_bobot += bobot or 0
                total_nilai += nilai or 0

    return render(request, 'adminchannel/manage_kpi.html', {
        'selected_filter_bulan': bulan,
        'selected_filter_tahun': tahun,
        'kpi_data': kpi_data,
        'total_bobot': total_bobot if kpi_data else 0,
        'total_nilai': total_nilai if kpi_data else 0,
        'selected_bulan': bulan,
        'selected_tahun': tahun,
    })

@login_required
def input_kpi_agency(request):
    if request.method == 'POST':
        try:
            data = json.loads(request.body)

            bulan = data.get('bulan')
            tahun = data.get('tahun')
            kategori = data.get('kategori')

            target = float(data.get('target', 0))
            real = float(data.get('real', 0))
            ach = (real / target * 100) if target != 0 else 0
            ach = min(ach, 200)
            bobot = float(data.get('bobot', 0))
            nilai = (ach / 100) * bobot

            query = """
                INSERT INTO kpi_agency
                (bulan, tahun, kategori, target, real, ach, bobot, nilai)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """
            params = [bulan, tahun, kategori, target, real, ach, bobot, nilai]

            with connection.cursor() as cursor:
                cursor.execute(query, params)

            return JsonResponse({'success': True})
        except Exception as e:
            return JsonResponse({'success': False, 'error': str(e)})
    else:
        return JsonResponse({'success': False, 'error': 'Invalid method'})

@login_required
def update_kpi_agency(request):
    if request.method == 'POST':
        try:
            data = json.loads(request.body)

            kpi_id = data.get('kpi_id')
            target = float(data.get('target', 0))
            real = float(data.get('real', 0))
            ach = (real / target * 100) if target != 0 else 0
            ach = min(ach, 200)
            bobot = float(data.get('bobot', 0))
            nilai = (ach / 100) * bobot

            query = """
                UPDATE kpi_agency
                SET target = %s,
                    real = %s,
                    ach = %s,
                    bobot = %s,
                    nilai = %s
                WHERE kpi_id = %s
            """
            params = [target, real, ach, bobot, nilai, kpi_id]

            with connection.cursor() as cursor:
                cursor.execute(query, params)

            return JsonResponse({'success': True})
        except Exception as e:
            return JsonResponse({'success': False, 'error': str(e)})
    else:
        return JsonResponse({'success': False, 'error': 'Invalid method'})

@login_required
def delete_kpi_agency(request):
    if request.method == 'POST':
        try:
            data = json.loads(request.body)
            kpi_id = data.get('kpi_id')

            query = "DELETE FROM kpi_agency WHERE kpi_id = %s"

            with connection.cursor() as cursor:
                cursor.execute(query, [kpi_id])

            return JsonResponse({'success': True})
        except Exception as e:
            return JsonResponse({'success': False, 'error': str(e)})
    else:
        return JsonResponse({'success': False, 'error': 'Invalid method'})

@login_required
def dailyagent(request):
    selected_year = request.GET.get('year')
    selected_month = request.GET.get('month')

    if not selected_year:
        selected_year = str(datetime.now().year)
    if not selected_month:
        selected_month = f"{datetime.now().month:02d}"

    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT ps.pab_id, 
                   ag.agent_id, 
                   ag.nama_agent, 
                   ps.tahun, 
                   ps.bulan, 
                   ps.day_1, ps.day_2, ps.day_3, ps.day_4, ps.day_5,
                   ps.day_6, ps.day_7, ps.day_8, ps.day_9, ps.day_10,
                   ps.day_11, ps.day_12, ps.day_13, ps.day_14, ps.day_15,
                   ps.day_16, ps.day_17, ps.day_18, ps.day_19, ps.day_20,
                   ps.day_21, ps.day_22, ps.day_23, ps.day_24, ps.day_25,
                   ps.day_26, ps.day_27, ps.day_28, ps.day_29, ps.day_30, ps.day_31,
                   ps.total_pab
            FROM ps_agent_per_bulan ps
            JOIN agent ag ON ps.agent_id = ag.agent_id
            WHERE ps.tahun = %s AND ps.bulan = %s
        """, [selected_year, selected_month])
        
        rows = cursor.fetchall()

    # Pisahkan berdasarkan RC (SOBI) atau tidak (OUTLET)
    data_sobi = []
    data_outlet = []

    for row in rows:
        if row[1].startswith('RC'):
            data_sobi.append(row)
        else:
            data_outlet.append(row)

    # Hitung total harian (kolom 5–35 = day_1 s/d day_31)
    def hitung_total(data):
        return [sum(row[i] or 0 for row in data) for i in range(5, 36)]

    total_outlet = hitung_total(data_outlet)
    total_sobi = hitung_total(data_sobi)

    # Tambahkan perhitungan total seluruh PSB (kolom total_pab)
    total_psb_outlet = sum(row[36] for row in data_outlet)
    total_psb_sobi = sum(row[36] for row in data_sobi)


    context = {
        'selected_year': selected_year,
        'selected_month': selected_month,
        'data_sobi': data_sobi,
        'data_outlet': data_outlet,
        'total_sobi': total_sobi,
        'total_outlet': total_outlet,
        'total_psb_outlet': total_psb_outlet,
        'total_psb_sobi': total_psb_sobi,
    }

    return render(request, 'adminchannel/dailyagent.html', context)

def get_all_wok():
    with connection.cursor() as cursor:
        cursor.execute("SELECT wok_id, nama_wok FROM wok ORDER BY nama_wok")
        return [{"wok_id": row[0], "nama_wok": row[1]} for row in cursor.fetchall()]

def dailysf(request):
    return render(request, 'adminchannel/dailysf.html')

@login_required
def managetl(request):
    if request.method == "POST":
        form_mode = request.POST.get("form_mode")
        tl_id = request.POST.get("tl_id")
        nama_tl = request.POST.get("nama_tl")
        wok_id = request.POST.get("wok_id")
        # login_web = generate_login_web(username, tl_id)

        try:
            with connection.cursor() as cursor:
                if form_mode == "create":
                    cursor.execute(''' 
                        INSERT INTO team_leader 
                        (tl_id, nama_tl, wok_id) 
                        VALUES (%s, %s, %s)
                    ''', [tl_id, nama_tl, wok_id])
                    message = "Team Leader berhasil ditambahkan."
                elif form_mode == "update":
                    cursor.execute('''
                        UPDATE team_leader SET nama_tl=%s, wok_id=%s,
                        WHERE tl_id=%s
                    ''', [nama_tl, wok_id, tl_id])
                    message = "Team Leader berhasil diperbarui."
                elif "_method" in request.POST and request.POST["_method"] == "DELETE":
                    # Menghapus team leader berdasarkan tl_id yang diberikan
                    cursor.execute('''DELETE FROM team_leader WHERE tl_id = %s''', [tl_id])
                    message = "Team Leader berhasil dihapus."
        except Exception as e:
            return render(request, "adminchannel/manage_tl.html", {
                "error": f"Terjadi kesalahan: {str(e)}",
                "tl_list": get_all_team_leaders(),
                "wok_list" : get_all_wok()
            })

        return render(request, "adminchannel/manage_tl.html", {
            "success": message,
            "tl_list": get_all_team_leaders(),
            "wok_list" : get_all_wok()
        })

    elif request.method == "GET":
        return render(request, "adminchannel/manage_tl.html", {
            "tl_list": get_all_team_leaders(),
            "wok_list" : get_all_wok()
        })

def get_all_team_leaders():
    with connection.cursor() as cursor:
        cursor.execute("SELECT tl.tl_id, tl.nama_tl, tl.wok_id, w.nama_wok FROM team_leader tl JOIN wok w ON tl.wok_id = w.wok_id")
        rows = cursor.fetchall()
        return [{"tl_id": row[0], "nama_tl": row[1], "wok_id": row[2], "nama_wok" : row[3]} for row in rows]

@login_required
def download_team_leader(request):
    # Buat workbook dan worksheet
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Team Leaders"

    # Format text style
    text_style = NamedStyle(name="text_style", number_format="@")  # '@' = text
    if "text_style" not in wb.named_styles:
        wb.add_named_style(text_style)

    # Header
    ws.append(["Kode TL", "Nama Team Leader", "WOK"])

    with connection.cursor() as cursor:
        cursor.execute("SELECT tl_id, nama_tl, tl.wok_id, w.nama_wok FROM team_leader tl JOIN wok w ON tl.wok_id = w.wok_id")
        rows = cursor.fetchall()
        for tl_id, nama_tl, wok in rows:
            ws.append([tl_id, nama_tl, wok])

    # Set kolom A (tl_id) sebagai teks
    for row in ws.iter_rows(min_row=2, min_col=1, max_col=1):
        for cell in row:
            cell.style = text_style

    # Simpan ke memory buffer
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    # Buat response HTTP
    response = HttpResponse(
        output,
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response['Content-Disposition'] = 'attachment; filename="team_leader.xlsx"'
    return response

def get_all_sales_force():
    with connection.cursor() as cursor:
        cursor.execute("SELECT sf.sf_id, sf.nama_sales, sf.tl_id, tl.nama_tl FROM sales sf JOIN team_leader tl ON sf.tl_id = tl.tl_id")
        rows = cursor.fetchall()
        return [{"sf_id": row[0], "nama_sales": row[1], "tl_id": row[2], "nama_tl" : row[3]} for row in rows]

@login_required
def download_sales_force(request):
    # Buat workbook dan worksheet
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sales Force"

    # Format text style
    text_style = NamedStyle(name="text_style", number_format="@")  # '@' = text
    if "text_style" not in wb.named_styles:
        wb.add_named_style(text_style)

    # Header
    ws.append(["Kode SF", "Nama Sales", "Team Leader"])

    with connection.cursor() as cursor:
        cursor.execute("SELECT sf.sf_id, sf.nama_sales, tl.nama_tl FROM sales sf JOIN team_leader tl ON sf.tl_id = tl.tl_id ORDER BY nama_sales ASC")
        rows = cursor.fetchall()
        for sf_id, nama_sales, nama_tl in rows:
            ws.append([sf_id, nama_sales, nama_tl])

    # Set kolom A (sf_id) sebagai teks
    for row in ws.iter_rows(min_row=2, min_col=1, max_col=1):
        for cell in row:
            cell.style = text_style

    # Simpan ke memory buffer
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    # Buat response HTTP
    response = HttpResponse(
        output,
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response['Content-Disposition'] = 'attachment; filename="sales_force.xlsx"'
    return response

@login_required
def managesf(request):
    if request.method == "POST":
        form_mode = request.POST.get("form_mode")
        sf_id = request.POST.get("sf_id")
        nama_sales = request.POST.get("nama_sales")
        tl_id = request.POST.get("tl_id")
        # login_web = generate_login_web(username, tl_id)

        try:
            with connection.cursor() as cursor:
                if form_mode == "create":
                    cursor.execute(''' 
                        INSERT INTO sales 
                        (sf_id, nama_sales, tl_id) 
                        VALUES (%s, %s, %s)
                    ''', [sf_id, nama_sales, tl_id])
                    message = "Sales Force berhasil ditambahkan."
                elif form_mode == "update":
                    cursor.execute('''
                        UPDATE sales SET nama_sales=%s, tl_id=%s
                        WHERE sf_id=%s
                    ''', [nama_sales, tl_id, sf_id])
                    message = "Sales Force berhasil diperbarui."
                elif "_method" in request.POST and request.POST["_method"] == "DELETE":
                    # Menghapus Sales Force berdasarkan sf_id yang diberikan
                    cursor.execute('''DELETE FROM sales WHERE sf_id = %s''', [sf_id])
                    message = "Sales Force berhasil dihapus."
        except Exception as e:
            return render(request, "adminchannel/manage_sf.html", {
                "error": f"Terjadi kesalahan: {str(e)}",
                "tl_list": get_all_team_leaders(),
                "sf_list" : get_all_sales_force()
            })

        return render(request, "adminchannel/manage_sf.html", {
            "success": message,
            "tl_list": get_all_team_leaders(),
            "sf_list" : get_all_sales_force()
        })

    elif request.method == "GET":
        return render(request, "adminchannel/manage_sf.html", {
            "tl_list": get_all_team_leaders(),
            "sf_list" : get_all_sales_force()
        })

def get_all_branch():
    with connection.cursor() as cursor:
        cursor.execute("SELECT br.branch_id, br.nama_branch, br.area_id, ar.nama_area FROM branch br JOIN area ar ON br.area_id = ar.area_id")
        rows = cursor.fetchall()
        return [{"branch_id": row[0], "nama_branch": row[1], "area_id": row[2], "nama_area" : row[3]} for row in rows]

def get_all_paket():
    with connection.cursor() as cursor:
        cursor.execute("SELECT pkt.paket_id, pkt.nama_paket, pkt.branch_id, br.nama_branch, pkt.arpu, pkt.fee FROM paket pkt JOIN branch br ON pkt.branch_id = br.branch_id")
        rows = cursor.fetchall()
        return [{"paket_id": row[0], "nama_paket": row[1], "branch_id": row[2], "nama_branch" : row[3], "arpu" : row[4], "fee" :row[5] } for row in rows]

def generate_unique_paket_id(cursor):
    while True:
        # Buat random 4 karakter alfanumerik
        random_suffix = ''.join(random.choices(string.ascii_lowercase + string.digits, k=4))
        paket_id = f'paket_{random_suffix}'

        # Cek apakah paket_id ini sudah ada
        cursor.execute("SELECT 1 FROM paket WHERE paket_id = %s", [paket_id])
        if not cursor.fetchone():
            return paket_id

@login_required
def managepaket(request):
    if request.method == "POST":
        form_mode = request.POST.get("form_mode")
        paket_id = request.POST.get("paket_id")
        nama_paket = request.POST.get("nama_paket")
        branch_id = request.POST.get("branch_id")
        arpu = request.POST.get("arpu")
        fee = request.POST.get("fee")

        try:
            with connection.cursor() as cursor:
                if form_mode == "create":
                    paket_id = generate_unique_paket_id(cursor)

                    cursor.execute(''' 
                        INSERT INTO paket 
                        (paket_id, nama_paket, branch_id, arpu, fee) 
                        VALUES (%s, %s, %s, %s, %s)
                    ''', [paket_id, nama_paket, branch_id, arpu, fee])
                    message = "Paket berhasil ditambahkan."
                elif form_mode == "update" and paket_id:
                    cursor.execute('''
                        UPDATE paket SET nama_paket = %s, branch_id = %s, arpu = %s, fee = %s
                        WHERE paket_id = %s
                    ''', [nama_paket, branch_id, arpu, fee, paket_id])
                    message = "Paket berhasil diperbarui."
                elif form_mode == "delete" and paket_id:
                    cursor.execute('DELETE FROM paket WHERE paket_id = %s', [paket_id])
                    message = "Paket berhasil dihapus."
        
        except Exception as e:
            return render(request, "adminchannel/manage_paket.html", {
                "error": f"Terjadi kesalahan: {str(e)}",
                "paket_list": get_all_paket(),
                "branch_list" : get_all_branch()
            })

        return render(request, "adminchannel/manage_paket.html", {
            "success": message,
            "paket_list": get_all_paket(),
            "branch_list" : get_all_branch()
        })

    elif request.method == "GET":
        return render(request, "adminchannel/manage_paket.html", {
            "paket_list": get_all_paket(),
            "branch_list" : get_all_branch()
        })
    
def generate_unique_branch_id(cursor):
    while True:
        # Buat random 4 karakter alfanumerik
        random_suffix = ''.join(random.choices(string.ascii_lowercase + string.digits, k=4))
        branch_id = f'branch_{random_suffix}'

        # Cek apakah branch_id ini sudah ada
        cursor.execute("SELECT 1 FROM branch WHERE branch_id = %s", [branch_id])
        if not cursor.fetchone():
            return branch_id

def get_all_area():
    with connection.cursor() as cursor:
        cursor.execute("SELECT area_id, nama_area FROM area")
        rows = cursor.fetchall()
        return [{"area_id": row[0], "nama_area" : row[1]} for row in rows]

@login_required
def managebranch(request):
    if request.method == "POST":
        form_mode = request.POST.get("form_mode")
        branch_id = request.POST.get("branch_id")
        nama_branch = request.POST.get("nama_branch")
        area_id = request.POST.get("area_id")

        try:
            with connection.cursor() as cursor:
                if form_mode == "create":
                    branch_id = generate_unique_branch_id(cursor)

                    cursor.execute(''' 
                        INSERT INTO branch 
                        (branch_id, nama_branch, area_id) 
                        VALUES (%s, %s, %s)
                    ''', [branch_id, nama_branch, area_id])
                    message = "Branch berhasil ditambahkan."
                elif form_mode == "update" and branch_id:
                    cursor.execute('''
                        UPDATE branch SET nama_branch=%s, area_id=%s
                        WHERE branch_id=%s
                    ''', [nama_branch, area_id, branch_id])
                    message = "Branch berhasil diperbarui."
                elif form_mode == "delete" and branch_id:
                    cursor.execute('''DELETE FROM branch WHERE branch_id = %s''', [branch_id])
                    message = "Paket berhasil dihapus."

        except Exception as e:
            return render(request, "adminchannel/manage_branch.html", {
                "error": f"Terjadi kesalahan: {str(e)}",
                "branch_list": get_all_branch(),
                "area_list" : get_all_area()
            })

        return render(request, "adminchannel/manage_branch.html", {
            "success": message,
            "branch_list": get_all_branch(),
            "area_list" : get_all_area()
        })

    elif request.method == "GET":
        return render(request, "adminchannel/manage_branch.html", {
            "branch_list": get_all_branch(),
            "area_list" : get_all_area()
        })

@login_required
def download_branch(request):
    # Buat workbook dan worksheet
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Branch"

    # Format text style
    text_style = NamedStyle(name="text_style", number_format="@")  # '@' = text
    if "text_style" not in wb.named_styles:
        wb.add_named_style(text_style)

    # Header
    ws.append(["ID Branch", "Nama Branch", "Area"])

    with connection.cursor() as cursor:
        cursor.execute("SELECT br.branch_id, br.nama_branch, ar.nama_area FROM branch br JOIN area ar ON br.area_id = ar.area_id")
        rows = cursor.fetchall()
        for branch_id, nama_branch, nama_area in rows:
            ws.append([branch_id, nama_branch, nama_area])

    # Set kolom A (branch_id) sebagai teks
    for row in ws.iter_rows(min_row=2, min_col=1, max_col=1):
        for cell in row:
            cell.style = text_style

    # Simpan ke memory buffer
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    # Buat response HTTP
    response = HttpResponse(
        output,
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response['Content-Disposition'] = 'attachment; filename="branch.xlsx"'
    return response

def generate_unique_area_id(cursor):
    while True:
        # Buat random 4 karakter alfanumerik
        random_suffix = ''.join(random.choices(string.ascii_lowercase + string.digits, k=4))
        area_id = f'area_{random_suffix}'

        # Cek apakah area_id ini sudah ada
        cursor.execute("SELECT 1 FROM area WHERE area_id = %s", [area_id])
        if not cursor.fetchone():
            return area_id

@login_required
def managearea(request):
    if request.method == "POST":
        form_mode = request.POST.get("form_mode")
        area_id = request.POST.get("area_id")
        nama_area = request.POST.get("nama_area")

        try:
            with connection.cursor() as cursor:
                if form_mode == "create":
                    area_id = generate_unique_area_id(cursor)

                    cursor.execute(''' 
                        INSERT INTO branch 
                        (area_id, nama_area) 
                        VALUES (%s, %s)
                    ''', [area_id, nama_area])
                    message = "Branch berhasil ditambahkan."
                elif form_mode == "update" and area_id:
                    cursor.execute('''
                        UPDATE area SET nama_area=%s
                        WHERE area_id=%s
                    ''', [nama_area, area_id])
                    message = "Branch berhasil diperbarui."
                elif form_mode == "delete" and area_id:
                    cursor.execute('''DELETE FROM area WHERE area_id = %s''', [area_id])
                    message = "Paket berhasil dihapus."

        except Exception as e:
            return render(request, "adminchannel/manage_area.html", {
                "error": f"Terjadi kesalahan: {str(e)}",
                "area_list" : get_all_area()
            })

        return render(request, "adminchannel/manage_area.html", {
            "success": message,
            "area_list" : get_all_area()
        })

    elif request.method == "GET":
        return render(request, "adminchannel/manage_area.html", {
            "area_list" : get_all_area()
        })

@login_required
def download_area(request):
    # Buat workbook dan worksheet
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Area"

    # Format text style
    text_style = NamedStyle(name="text_style", number_format="@")  # '@' = text
    if "text_style" not in wb.named_styles:
        wb.add_named_style(text_style)

    # Header
    ws.append(["ID Area", "Nama Area"])

    with connection.cursor() as cursor:
        cursor.execute("SELECT area_id, nama_area FROM area")
        rows = cursor.fetchall()
        for area_id, nama_area in rows:
            ws.append([area_id, nama_area])

    # Set kolom A (branch_id) sebagai teks
    for row in ws.iter_rows(min_row=2, min_col=1, max_col=1):
        for cell in row:
            cell.style = text_style

    # Simpan ke memory buffer
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    # Buat response HTTP
    response = HttpResponse(
        output,
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response['Content-Disposition'] = 'attachment; filename="area.xlsx"'
    return response

@login_required
def get_psb_mom(request):
    tanggal_input = request.GET.get("tanggal")
    try:
        tanggal_obj = datetime.strptime(tanggal_input, "%Y-%m-%d") if tanggal_input else datetime.now()
    except ValueError:
        tanggal_obj = datetime.now()

    tanggal = tanggal_obj.day
    bulan_ini = tanggal_obj.month
    tahun_ini = tanggal_obj.year

    if bulan_ini == 1:
        bulan_lalu = 12
        tahun_lalu = tahun_ini - 1
    else:
        bulan_lalu = bulan_ini - 1
        tahun_lalu = tahun_ini

    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT ag.nama_agent, ag.agent_id, ag.channel,
                COALESCE(SUM(CASE WHEN p.tahun = %s AND p.bulan = %s THEN p.total_pab ELSE 0 END), 0) AS pab_ini,
                COALESCE(SUM(CASE WHEN p.tahun = %s AND p.bulan = %s THEN p.total_pab ELSE 0 END), 0) AS pab_lalu
            FROM ps_agent_per_bulan p
            JOIN agent ag ON ag.agent_id = p.agent_id
            GROUP BY ag.nama_agent, ag.agent_id, ag.channel
            ORDER BY ag.nama_agent ASC
        """, [tahun_ini, bulan_ini, tahun_lalu, bulan_lalu])
        rows = cursor.fetchall()

    hasil_data = []
    total_ini = 0
    total_lalu = 0

    # Total per channel
    total_ini_sobi = 0
    total_lalu_sobi = 0
    total_ini_outlet = 0
    total_lalu_outlet = 0

    for row in rows:
        nama, agent_id, channel, psb_ini, psb_lalu = row
        selisih = psb_ini - psb_lalu
        persentase = ((psb_ini - psb_lalu) / psb_lalu * 100) if psb_lalu != 0 else (100 if psb_ini > 0 else 0)

        hasil_data.append({
            "nama": nama,
            "id": agent_id,
            "channel": channel,
            "psb_ini": psb_ini,
            "psb_lalu": psb_lalu,
            "selisih": selisih,
            "mom": round(persentase, 2)
        })

        total_ini += psb_ini
        total_lalu += psb_lalu

        if channel.lower() == 'sobi':
            total_ini_sobi += psb_ini
            total_lalu_sobi += psb_lalu
        elif channel.lower() == 'outlet':
            total_ini_outlet += psb_ini
            total_lalu_outlet += psb_lalu

    total_selisih = total_ini - total_lalu
    total_mom = ((total_ini - total_lalu) / total_lalu * 100) if total_lalu != 0 else (100 if total_ini > 0 else 0)

    # MOM per channel
    total_selisih_sobi = total_ini_sobi - total_lalu_sobi
    total_mom_sobi = ((total_ini_sobi - total_lalu_sobi) / total_lalu_sobi * 100) if total_lalu_sobi != 0 else (100 if total_ini_sobi > 0 else 0)

    total_selisih_outlet = total_ini_outlet - total_lalu_outlet
    total_mom_outlet = ((total_ini_outlet - total_lalu_outlet) / total_lalu_outlet * 100) if total_lalu_outlet != 0 else (100 if total_ini_outlet > 0 else 0)

    context = {
        "data_mom": hasil_data,
        "total_ini": total_ini,
        "total_lalu": total_lalu,
        "total_selisih": total_selisih,
        "total_mom": round(total_mom, 2),
        "bulan_ini": calendar.month_name[bulan_ini],
        "bulan_lalu": calendar.month_name[bulan_lalu],
        "tahun_ini": tahun_ini,
        "tahun_lalu": tahun_lalu,
        "tanggal": tanggal,
        "tanggal_input": tanggal_obj.strftime('%Y-%m-%d'),

        # Total per channel
        "total_ini_sobi": total_ini_sobi,
        "total_lalu_sobi": total_lalu_sobi,
        "total_selisih_sobi": total_selisih_sobi,
        "total_mom_sobi": round(total_mom_sobi, 2),

        "total_ini_outlet": total_ini_outlet,
        "total_lalu_outlet": total_lalu_outlet,
        "total_selisih_outlet": total_selisih_outlet,
        "total_mom_outlet": round(total_mom_outlet, 2),
    }

    return render(request, 'adminchannel/rekap_mom.html', context)

@login_required
def data_ps_arpu(request):
    now = datetime.now()
    bulan = request.GET.get('bulan', now.strftime('%m'))
    tahun = request.GET.get('tahun', now.strftime('%Y'))
    bulan_num = int(bulan)
    tahun_num = int(tahun)


    with connection.cursor() as cursor:
        cursor.execute(f"""
            SELECT
                nama_paket,
                arpu,
                COUNT(*) as jumlah_ps,
                AVG(arpu) as avg_arpu
            FROM data_ps
            WHERE EXTRACT(MONTH FROM tanggal_created_at) = %s
              AND EXTRACT(YEAR FROM tanggal_created_at) = %s
            GROUP BY nama_paket, arpu
            ORDER BY nama_paket, arpu
        """, [bulan_num, tahun])

        data = cursor.fetchall()

    # Susun table_html secara manual tanpa loop
    table_html = ""
    paket_sebelumnya = ""
    total_ps = 0
    total_arpu = 0
    row_counter = 0

    for row in data:
        nama_paket, arpu, jumlah_ps, avg_arpu = row
        avg_arpu = round(avg_arpu or 0, 3)

        if nama_paket != paket_sebelumnya:
            if paket_sebelumnya != "":
                table_html += f"<tr class='table-info fw-bold'><td colspan='1'></td><td>{total_ps}</td><td>{round(total_arpu / total_ps, 3)}</td></tr>"
            table_html += f"<tr class='fw-bold'><td colspan='3'>{nama_paket}</td></tr>"
            total_ps = 0
            total_arpu = 0

        table_html += f"<tr><td>{arpu:,.0f}</td><td>{jumlah_ps}</td><td>{avg_arpu:,.0f}</td></tr>"
        total_ps += jumlah_ps
        total_arpu += arpu * jumlah_ps
        paket_sebelumnya = nama_paket
        row_counter += 1

    # Grand Total
    if total_ps > 0:
        table_html += f"<tr class='table-info fw-bold'><td colspan='1'></td><td>{total_ps}</td><td>{round(total_arpu / total_ps, 3)}</td></tr>"

    context = {
        'bulan': calendar.month_name[bulan_num],
        'bulan_num': bulan_num,
        'tahun': tahun,
        'table_html': table_html,
    }
    return render(request, 'adminchannel/data_arpu.html', context)





