# 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, transaction
from django.contrib import messages
from django.http import HttpResponse, HttpResponseForbidden, JsonResponse
from django.shortcuts import render, redirect
from django.core.paginator import Paginator
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 filter dari GET
    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_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_branch = request.GET.get('filter-branch')
    filter_branch = filter_branch.strip() if filter_branch else None
    
    filter_region = request.GET.get('filter-region')
    filter_region = filter_region.strip() if filter_region else None

    filter_status_order = request.GET.get('filter-status-order')
    filter_status_order = filter_status_order.strip() if filter_status_order 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_id,
            tanggal_ps,
            agent_id,
            agent_name,
            track_id,
            kontak_pelanggan,
            status_order,
            nama_paket,
            arpu,
            area,
            region,
            branch,
            wok,
            sto,
            jenis_transaksi,
            no_internet
        FROM data_ps
        WHERE 1=1
    """
    params = []

    # Filter dinamis
    if filter_area:
        query += " AND LOWER(area) ILIKE %s"
        params.append(f"%{filter_area.lower()}%")
    if filter_agent:
        query += " AND LOWER(agent_name) ILIKE %s"
        params.append(f"%{filter_agent.lower()}%")
    if filter_branch:
        query += " AND LOWER(branch) ILIKE %s"
        params.append(f"%{filter_branch.lower()}%")
    if filter_region:
        query += " AND LOWER(region) ILIKE %s"
        params.append(f"%{filter_region.lower()}%")
    if filter_bulan:
        query += " AND EXTRACT(MONTH FROM tanggal_ps) = %s"
        params.append(filter_bulan)
    if filter_tahun:
        query += " AND EXTRACT(YEAR FROM tanggal_ps) = %s"
        params.append(filter_tahun)
    if filter_status_order:
        query += " AND status_order = %s"
        params.append(filter_status_order)
    if search_track_order:
        query += " AND track_id = %s"
        params.append(search_track_order)

    # Urutkan berdasarkan tanggal terbaru
    query += " ORDER BY tanggal_ps DESC"

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

        # Ambil daftar area
        cursor.execute("SELECT DISTINCT area FROM data_ps WHERE area IS NOT NULL ORDER BY area")
        area_list = [row[0] for row in cursor.fetchall()]

        # Ambil daftar branch
        cursor.execute("SELECT DISTINCT branch FROM data_ps WHERE branch IS NOT NULL ORDER BY branch")
        branch_list = [row[0] for row in cursor.fetchall()]
        
        # Ambil daftar region
        cursor.execute("SELECT DISTINCT region FROM data_ps WHERE region IS NOT NULL ORDER BY region")
        region_list = [row[0] for row in cursor.fetchall()]

        # Ambil daftar agent
        cursor.execute("SELECT DISTINCT agent_name FROM data_ps WHERE agent_name IS NOT NULL ORDER BY agent_name")
        agent_list = [row[0] for row in cursor.fetchall()]

    # --- PAGINATION ---
    page_number = request.GET.get('page', 1)  # halaman aktif
    paginator = Paginator(data, 10)  # 10 item per halaman
    page_obj = paginator.get_page(page_number)

    # Context
    context = {
        'page_obj': page_obj,  # gunakan ini di template
        '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'),
        ],
        'list_tahun': ["2024", "2025", "2026", "2027"],
        'area_list': area_list,
        'branch_list': branch_list,
        'region_list': region_list,
        'agent_list': agent_list,
        'status_order_list': ['SUCCESS', 'CANCEL'],
        'area_selected': filter_area,
        'bulan_selected': str(filter_bulan) if filter_bulan else '',
        'tahun_selected': filter_tahun,
        'branch_selected': filter_branch,
        'region_selected': filter_region,
        'agent_selected': filter_agent,
        'status_order_selected': filter_status_order,
        'search_track_order': search_track_order,
    }

    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,
        "branch": request.GET.get("branch"),
        "region": request.GET.get("region"),
        "area": request.GET.get("area"),
        "status_order": request.GET.get("status_order"),
    }

    query = """
        SELECT 
            ps_id, 
            tanggal_ps,
            agent_id,
            agent_name,
            track_id,
            kontak_pelanggan,
            branch,
            area,
            status_order,
            nama_paket,
            arpu,
            jenis_transaksi,
            no_internet,
            region,
            wok,
            sto
        FROM data_ps
        WHERE 1=1
    """

    params = []

    if filters["bulan"]:
        query += " AND EXTRACT(MONTH FROM tanggal_ps) = %s"
        params.append(int(filters["bulan"]))
    if filters["tahun"]:
        query += " AND EXTRACT(YEAR FROM tanggal_ps) = %s"
        params.append(int(filters["tahun"]))
    if filters["agent"]:
        query += " AND LOWER(agent_name) ILIKE %s"
        params.append(f"%{filters['agent'].lower()}%")
    if filters["branch"]:
        query += " AND LOWER(branch) ILIKE %s"
        params.append(f"%{filters['branch'].lower()}%")
    if filters["region"]:
        query += " AND LOWER(region) ILIKE %s"
        params.append(f"%{filters['region'].lower()}%")
    if filters["area"]:
        query += " AND LOWER(area) ILIKE %s"
        params.append(f"%{filters['area'].lower()}%")
    if filters["status_order"]:
        query += " AND LOWER(status_order) ILIKE %s"
        params.append(f"%{filters['status_order'].lower()}%")

    # 'channel' dan 'status_case' sudah tidak tersedia → tidak digunakan
    query += " ORDER BY tanggal_ps DESC"

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

        data_ps = [
            dict(zip([
                "ps_id", "tanggal_ps", "agent_id", "agent_name", "track_id",
                "kontak_pelanggan", "branch", "area", "status_order",
                "nama_paket", "arpu", "jenis_transaksi", "no_internet",
                "region", "wok", "sto"
            ], row))
            for row in rows
        ]

        # DISTINCT lists
        cursor.execute("SELECT DISTINCT agent_name FROM data_ps WHERE agent_name IS NOT NULL ORDER BY agent_name")
        agent_list = [row[0] for row in cursor.fetchall()]

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

        cursor.execute("SELECT DISTINCT branch FROM data_ps WHERE branch IS NOT NULL ORDER BY branch")
        branch_list = [row[0] for row in cursor.fetchall()]
        
        cursor.execute("SELECT DISTINCT region FROM data_ps WHERE region IS NOT NULL ORDER BY region")
        region_list = [row[0] for row in cursor.fetchall()]

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

    # Analytics
    area_counter = Counter(row['area'] for row in data_ps if row.get('area'))
    region_counter = Counter(row['region'] for row in data_ps if row.get('region'))
    branch_counter = Counter(row['branch'] for row in data_ps if row.get('branch'))
    wok_counter = Counter(row['wok'] for row in data_ps if row.get('wok'))
    agen_counter = Counter(row['agent_name'] for row in data_ps if row.get('agent_name'))
    paket_counter = Counter(row['nama_paket'] for row in data_ps if row.get('nama_paket'))
    status_counter = Counter(row['status_order'] for row in data_ps if row.get('status_order'))

    # Ubah ke list terurut (nama, count) descending
    area_sales = sorted(area_counter.items(), key=lambda x: x[1], reverse=True)
    region_sales = sorted(region_counter.items(), key=lambda x: x[1], reverse=True)
    branch_sales = sorted(branch_counter.items(), key=lambda x: x[1], reverse=True)
    wok_sales = sorted(wok_counter.items(), key=lambda x: x[1], reverse=True)
    agen_sales = sorted(agen_counter.items(), key=lambda x: x[1], reverse=True)
    paket_sales = sorted(paket_counter.items(), key=lambda x: x[1], reverse=True)
    status_sales = sorted(status_counter.items(), key=lambda x: x[1], reverse=True)


    return render(request, 'adminchannel/datadigital.html', {
        'selected_area': filters["area"],
        'selected_region': filters["region"],
        'selected_agent': filters["agent"],
        'selected_branch': filters["branch"],
        'selected_bulan': filters["bulan"],
        'selected_status_order': filters["status_order"],
        'agent_list': agent_list,
        'channel_list': [],  # Tidak digunakan
        'area_list': area_list,
        'status_list': status_list,
        'agen_sales': agen_sales,        # sudah terdefinisi di atas
        'area_sales': area_sales,        # sudah terdefinisi di atas
        'region_sales': region_sales,
        'branch_sales': branch_sales,
        'wok_sales': wok_sales,
        'paket_sales': paket_sales,
        'status_sales': status_sales,
        '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_ps", "agent_id", "agent_name", "track_id", "kontak_pelanggan",
                "jenis_transaksi", "nama_paket", "arpu", "status_order", "no_internet",
                "area", "region", "branch", "wok", "sto"
            ]

            # 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

            with transaction.atomic():
                with connection.cursor() as cursor:
                    for row in sheet.iter_rows(min_row=2, values_only=True):
                        try:
                            (
                                tanggal_ps, agent_id, agent_name, track_id, kontak_pelanggan,
                                jenis_transaksi, nama_paket, arpu, status_order, no_internet,
                                area, region, branch, wok, sto
                            ) = row

                            # Validasi/konversi tanggal
                            if not isinstance(tanggal_ps, datetime):
                                tanggal_ps = datetime.strptime(str(tanggal_ps), "%Y-%m-%d")

                            cursor.execute("""
                                INSERT INTO data_ps (
                                    tanggal_ps, agent_id, agent_name, track_id, kontak_pelanggan,
                                    jenis_transaksi, nama_paket, arpu, status_order, no_internet,
                                    area, region, branch, wok, sto
                                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                            """, (
                                tanggal_ps, agent_id, agent_name, track_id, kontak_pelanggan,
                                jenis_transaksi, nama_paket, arpu, status_order, no_internet,
                                area, region, branch, wok, sto
                            ))
                            success_count += 1

                        except Exception as row_error:
                            raise Exception(f"Kesalahan pada baris {success_count+2}: {str(row_error)}")

            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)


def parse_date(value):
    """
    Fungsi bantu untuk parsing tanggal dari Excel.
    Menerima objek datetime atau string, lalu mengembalikan string 'YYYY-MM-DD'.
    """
    if isinstance(value, datetime):
        return value.strftime('%Y-%m-%d')
    elif isinstance(value, str):
        try:
            return datetime.strptime(value.strip(), '%Y-%m-%d').strftime('%Y-%m-%d')
        except ValueError:
            return None
    return None

@login_required
def upload_excel_data_digital(request):
    failed = []  # Baris yang gagal
    if request.method == 'POST' and request.FILES.get('file_excel'):
        file_excel = request.FILES['file_excel']
        wb = openpyxl.load_workbook(file_excel)
        sheet = wb.active

        headers = [cell.value for cell in sheet[1]]
        inserted, updated = 0, 0

        # Gunakan transaksi
        with transaction.atomic():
            for row_index, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
                try:
                    data = dict(zip(headers, row))
                    track_id = data.get('track_id')

                    if not track_id:
                        failed.append(f"Baris {row_index}: track_id kosong.")
                        continue

                    tanggal_ps = parse_date(data.get('tanggal_ps'))
                    if not tanggal_ps:
                        failed.append(f"Baris {row_index}: tanggal_ps tidak valid atau kosong.")
                        continue

                    arpu = int(data.get('arpu') or 0)

                    with connection.cursor() as cursor:
                        cursor.execute("SELECT COUNT(*) FROM data_ps WHERE track_id = %s", [track_id])
                        exists = cursor.fetchone()[0]

                        if exists:
                            # Update data
                            cursor.execute("""
                                UPDATE data_ps SET
                                    kontak_pelanggan = %s,
                                    jenis_transaksi = %s,
                                    nama_paket = %s,
                                    arpu = %s,
                                    status_order = %s,
                                    tanggal_ps = %s,
                                    agent_id = %s,
                                    agent_name = %s,
                                    no_internet = %s,
                                    area = %s,
                                    region = %s,
                                    branch = %s,
                                    wok = %s,
                                    sto = %s
                                WHERE track_id = %s
                            """, [
                                data.get('kontak_pelanggan'),
                                data.get('jenis_transaksi'),
                                data.get('nama_paket'),
                                arpu,
                                data.get('status_order'),
                                tanggal_ps,
                                data.get('agent_id'),
                                data.get('agent_name'),
                                data.get('no_internet'),
                                data.get('area'),
                                data.get('region'),
                                data.get('branch'),
                                data.get('wok'),
                                data.get('sto'),
                                track_id
                            ])
                            updated += 1
                        else:
                            # Insert data baru
                            cursor.execute("""
                                INSERT INTO data_ps (
                                    kontak_pelanggan, jenis_transaksi, nama_paket, arpu,
                                    status_order, tanggal_ps, agent_id, agent_name, track_id,
                                    no_internet, area, region, branch, wok, sto
                                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                            """, [
                                data.get('kontak_pelanggan'),
                                data.get('jenis_transaksi'),
                                data.get('nama_paket'),
                                arpu,
                                data.get('status_order'),
                                tanggal_ps,
                                data.get('agent_id'),
                                data.get('agent_name'),
                                track_id,
                                data.get('no_internet'),
                                data.get('area'),
                                data.get('region'),
                                data.get('branch'),
                                data.get('wok'),
                                data.get('sto')
                            ])
                            inserted += 1

                except Exception as e:
                    failed.append(f"Baris {row_index}: {str(e)}")
                    # Raise error untuk batalkan semua perubahan
                    raise

        messages.success(request, f"Upload selesai. {inserted} data baru, {updated} diperbarui, {len(failed)} gagal.")

    return render(request, 'adminchannel/upload_excel.html', {
        'failed_rows': failed
    })

@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)
        ps_id = data.get("id")

        if not ps_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", [ps_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 {ps_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":
        return JsonResponse({
            "success": False,
            "error": "Metode tidak diizinkan."
        }, status=405)

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

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

        # Field yang boleh diedit sesuai struktur tabel data_ps
        allowed_fields = [
            "kontak_pelanggan",
            "jenis_transaksi",
            "nama_paket",
            "arpu",
            "status_order",
            "tanggal_ps",
            "agent_id",
            "agent_name",
            "track_id",
            "no_internet",
            "area",
            "region",
            "branch",
            "wok",
            "sto"
        ]

        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."
            }, status=400)

        values.append(ps_id)

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

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

        return JsonResponse({
            "success": True,
            "message": f"Data dengan ID {ps_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"Terjadi kesalahan: {str(e)}"
        }, status=500)

@login_required
def edit_data_ps_lama(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 
                pab.pab_id,
                pab.agent_id,
                COALESCE(dp.agent_name, '-') AS agent_name,
                pab.tahun,
                pab.bulan,
                pab.day_1, pab.day_2, pab.day_3, pab.day_4, pab.day_5,
                pab.day_6, pab.day_7, pab.day_8, pab.day_9, pab.day_10,
                pab.day_11, pab.day_12, pab.day_13, pab.day_14, pab.day_15,
                pab.day_16, pab.day_17, pab.day_18, pab.day_19, pab.day_20,
                pab.day_21, pab.day_22, pab.day_23, pab.day_24, pab.day_25,
                pab.day_26, pab.day_27, pab.day_28, pab.day_29, pab.day_30, pab.day_31,
                pab.total_pab
            FROM ps_agent_per_bulan pab
            LEFT JOIN (
                SELECT DISTINCT agent_id, agent_name
                FROM data_ps
            ) dp ON dp.agent_id = pab.agent_id
            WHERE pab.tahun = %s AND pab.bulan = %s
        """, [selected_year, selected_month])
        
        rows = cursor.fetchall()

    # Bagi ke data Sobi (RC) dan Outlet (non-RC)
    data_sobi = []
    data_outlet = []

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

    # Hitung total harian untuk 31 hari (index 5 - 35)
    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)

    # Hitung total_pab (index 36)
    total_psb_outlet = sum(row[36] or 0 for row in data_outlet)
    total_psb_sobi = sum(row[36] or 0 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 
                dp.agent_id,
                COALESCE(MAX(dp.agent_name), '-') AS agent_name,
                COALESCE(COUNT(CASE WHEN EXTRACT(YEAR FROM dp.tanggal_ps) = %s 
                                    AND EXTRACT(MONTH FROM dp.tanggal_ps) = %s 
                                    THEN 1 END), 0) AS psb_ini,
                COALESCE(COUNT(CASE WHEN EXTRACT(YEAR FROM dp.tanggal_ps) = %s 
                                    AND EXTRACT(MONTH FROM dp.tanggal_ps) = %s 
                                    THEN 1 END), 0) AS psb_lalu
            FROM data_ps dp
            GROUP BY dp.agent_id
            ORDER BY agent_name ASC
        """, [
            tahun_ini, bulan_ini,
            tahun_lalu, bulan_lalu
        ])
        rows = cursor.fetchall()

    hasil_data = []
    total_ini = total_lalu = 0

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

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

        total_ini += psb_ini
        total_lalu += psb_lalu

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

    context = {
        "data_mom": hasil_data,
        "bulan_ini": calendar.month_name[bulan_ini],
        "bulan_lalu": calendar.month_name[bulan_lalu],
        "tanggal": tanggal,
        "tahun_ini": tahun_ini,
        "tahun_lalu": tahun_lalu,
        "tanggal_input": tanggal_input,
        "total_ini": total_ini,
        "total_lalu": total_lalu,
        "total_selisih": total_selisih,
        "total_mom": round(total_mom, 2),
        "today": datetime.today(),
    }

    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)