from django.http import Http404,HttpResponse
from django.core.exceptions import PermissionDenied
from django.shortcuts import render,redirect
from django.db import models
from django.db.models.functions import ExtractYear, TruncDate, ExtractMonth

from webapp.models import (koleksi as m_koleksi, master as m_master)
from webapp.common import dump, create_thumbnail

from django.db.models import Q, Count

from django.http import HttpResponseRedirect
from django.shortcuts import render
from django.core.paginator import Paginator
from django.db.models import Q, Value, CharField, IntegerField
from django.db.models.functions import Cast
from django.db.models import CharField, TextField

from webapp.models import koleksi as m_koleksi, master as m_master
from webapp.common import dump, currency, tgl_indo

from django.http import JsonResponse, HttpResponse
from django.views.decorators.csrf import csrf_exempt
from django.views.decorators.http import require_POST
from django.core.files.storage import default_storage
from django.core.files.base import ContentFile
from docx import Document
import os
from PyPDF2 import PdfReader
from io import BytesIO
from django.http import HttpResponse, Http404
import tempfile
from PIL import Image
from pdf2image import convert_from_bytes
from pdf2docx import Converter
from pdf2image import convert_from_path
from django.core.files.base import ContentFile
from django.http import JsonResponse
import os
import uuid
from django.conf import settings
import fitz  # PyMuPDF
from django.core.files.storage import default_storage
from django.conf import settings
from django.utils.text import slugify
import uuid
from webapp.models.koleksi import ContentVersion, ContentVersionAttachment
from webapp.models.master import Users
from webapp.models.master import Skpddg


from webapp.utils import upload_to_drive  # 🔄 Perubahan: upload Google Drive
from django.contrib import messages

from django.views.decorators.http import require_GET

# 🔄 Perubahan: View untuk upload PDF ke Google Drive
# ✅ Import fungsi upload
def upload_pdf_to_drive(request):
    if request.method == 'POST':
        file = request.FILES.get('file')
        if file:
            try:
                link = upload_to_drive(file, file.name)
                return JsonResponse({
                    'status': 'success',
                    'file_name': file.name,  # ✅ nama asli file
                    'web_link': 'https://drive.google.com/drive/folders/19pH19iCJ8hNr9OF0TwyWwMiKeR-_iecS'        # ✅ link Google Drive
                })
            except Exception as e:
                return JsonResponse({'status': 'error', 'message': str(e)})
        else:
            return JsonResponse({'status': 'error', 'message': 'File tidak ditemukan.'})
    return JsonResponse({'status': 'error', 'message': 'Metode request harus POST'})

@csrf_exempt
@require_POST
def convert_to_images(request):
    if request.method == 'POST' and request.FILES.get('file'):
        pdf_file = request.FILES['file']
        unique_id = uuid.uuid4().hex[:8]
        base_name = slugify(os.path.splitext(pdf_file.name)[0])
        filename = f"{base_name}-{unique_id}.pdf"
        pdf_path = os.path.join(settings.MEDIA_ROOT, 'converted_pdfs', filename)

        os.makedirs(os.path.dirname(pdf_path), exist_ok=True)

        # Simpan PDF
        with default_storage.open(pdf_path, 'wb+') as destination:
            for chunk in pdf_file.chunks():
                destination.write(chunk)

        # Konversi PDF ke gambar
        doc = fitz.open(pdf_path)
        image_urls = []

        image_folder = os.path.join(settings.MEDIA_ROOT, 'converted_images', f"{base_name}-{unique_id}")
        os.makedirs(image_folder, exist_ok=True)

        for i in range(len(doc)):
            page = doc.load_page(i)
            pix = page.get_pixmap(matrix=fitz.Matrix(2, 2))  # kualitas tinggi
            image_filename = f"page_{i+1}.png"
            image_path = os.path.join(image_folder, image_filename)
            pix.save(image_path)

            # BENTUK URL GAMBAR YANG VALID
            image_url = f"{settings.MEDIA_URL}converted_images/{base_name}-{unique_id}/{image_filename}"
            image_urls.append(image_url)

        doc.close()
        return JsonResponse({'status': 'success', 'images': image_urls})

    return JsonResponse({'status': 'error', 'message': 'No PDF file provided'}, status=400)


@csrf_exempt
@require_POST
def convert_to_word(request):
    file = request.FILES.get('file')
    if not file or not file.name.endswith('.pdf'):
        return HttpResponse("File tidak valid. Harap unggah file PDF.", status=400)

    # Simpan file PDF ke file sementara
    with tempfile.NamedTemporaryFile(delete=False, suffix=".pdf") as tmp_pdf:
        tmp_pdf.write(file.read())
        tmp_pdf_path = tmp_pdf.name

    # Konversi ke DOCX
    tmp_docx_path = tmp_pdf_path.replace('.pdf', '.docx')
    cv = Converter(tmp_pdf_path)
    cv.convert(tmp_docx_path, start=0, end=None)
    cv.close()

    # Kirim file Word sebagai respons unduhan
    with open(tmp_docx_path, 'rb') as docx_file:
        response = HttpResponse(docx_file.read(), content_type='application/vnd.openxmlformats-officedocument.wordprocessingml.document')
        response['Content-Disposition'] = f'attachment; filename="{os.path.basename(tmp_docx_path)}"'

    # Bersihkan file sementara
    os.remove(tmp_pdf_path)
    os.remove(tmp_docx_path)

    return response

def home(request):
	return redirect('dash')
# 

# --- Tambahkan di suatu tempat di webapp/views/main.py, di atas fungsi dash ---

def safe_decode(text):
    """Membersihkan string dari byte non-UTF-8 (misalnya 0x96)"""
    if isinstance(text, str):
        try:
            # Coba encode ke cp1252 (di mana 0x96 valid), lalu decode ke UTF-8.
            # 'errors='ignore'' akan menghilangkan byte yang bermasalah.
            return text.encode('cp1252', errors='ignore').decode('utf-8', errors='ignore')
        except Exception:
            return text
    return text

# -----------------------------------------------------------------------------

def dash(request):
	tahun_active = request.GET.get('ta', m_master.Ta._read_one(current='t')['tahun'])
	tahun_active = int(tahun_active)
	tahun = m_master.Ta.objects.filter(tahun__lte=tahun_active+2)[:5].values('tahun','current')

	ukuran_banyak_berkas = m_koleksi.Contents.objects.filter(tahun=tahun_active).count()
	banyak_berkas = m_koleksi.Contents.objects.filter(tahun=tahun_active).count()
	ukuran_berkas = m_koleksi.Attachments.objects.filter(pk_contents__tahun=tahun_active).aggregate(models.Sum('size'))
	ukuran_berkas = ukuran_berkas['size__sum']
     
	total_attachments = m_koleksi.Attachments.objects.filter(
		pk_contents__tahun=tahun_active
	).count()

	ukuran_total_attachments = m_koleksi.Attachments.objects.filter(
		pk_contents__tahun=tahun_active
	).aggregate(models.Sum('size'))['size__sum'] or 0
     
	attachments_monthly = m_koleksi.Attachments.objects.filter(
		pk_contents__tahun=tahun_active
	).annotate(
		bulan=ExtractMonth('created')
	).values('bulan').annotate(total=models.Count('*'))

	# sess_is_auth => True
	# _pk => 2
	# pk_roles => 71
	# usr => operator
	# pwd => 10470c3b4b1fed12c3baac014be15fac67c6e815
	# pk => 2
	# role => OPERATOR

	banyak_surat_masuk = m_koleksi.Surat.objects.filter(tanggal__year=tahun_active,_type='MASUK').count()
	ukuran_surat_masuk = m_koleksi.Surat.objects.filter(tanggal__year=tahun_active,_type='MASUK').aggregate(models.Sum('suratf__size'))
	ukuran_surat_masuk = ukuran_surat_masuk['suratf__size__sum']

	banyak_surat_keluar = m_koleksi.Surat.objects.filter(tanggal__year=tahun_active,_type='KELUAR').count()
	ukuran_surat_keluar = m_koleksi.Surat.objects.filter(tanggal__year=tahun_active,_type='KELUAR').aggregate(models.Sum('suratf__size'))
	ukuran_surat_keluar = ukuran_surat_keluar['suratf__size__sum']

	'''
	versi sql bukan orm:
	SELECT COUNT(*) banyak, EXTRACT(MONTH from tanggal) bulan FROM surat
	WHERE EXTRACT(YEAR from tanggal) = 2020 AND "type"='MASUK/KELUAR'
	GROUP BY  EXTRACT(MONTH from tanggal)
	'''

	
	berkasmasuk_monthly = m_koleksi.Surat.objects.filter(
		Q(_type='MASUK') | Q(_type='KELUAR'),
		tanggal__year=tahun_active
	).values('tanggal__month').annotate(banyak=models.Count('*'))

	ukuran_berkas_total = m_koleksi.Surat.objects.filter(
		Q(_type='MASUK') | Q(_type='KELUAR'),
		tanggal__year=tahun_active
	).aggregate(models.Sum('suratf__size'))['suratf__size__sum']

	# berkasmasuk_monthly =  m_koleksi.Surat.objects.filter(_type='MASUK', tanggal__year=tahun_active).values('tanggal__month').annotate(banyak=models.Count('*'));
	suratmasuk_monthly =  m_koleksi.Surat.objects.filter(_type='MASUK', tanggal__year=tahun_active).values('tanggal__month').annotate(banyak=models.Count('*'));
	suratkeluar_monthly = m_koleksi.Surat.objects.filter(_type='KELUAR',tanggal__year=tahun_active).values('tanggal__month').annotate(banyak=models.Count('*'));

	# dump(list(suratmasuk_monthly))
	# dump(suratkeluar_monthly)
	# return HttpResponse(None)
    
	# TAMBAHAN JUMLAH TOTAL (Berkas Surat & Disposisi)
	total_berkas_surat = m_koleksi.Surat.objects.filter(tanggal__year=tahun_active).count()
	total_disposisi = m_koleksi.Disposisi.objects.filter(tanggal__year=tahun_active).count()
    
	total_surat_masuk = m_koleksi.Surat.objects.filter(
		tanggal__year=tahun_active, _type='MASUK'
	).count()

	total_surat_keluar = m_koleksi.Surat.objects.filter(
		tanggal__year=tahun_active, _type='KELUAR'
	).count()


	# Tambahan: jumlah disposisi berdasarkan tahun surat
	disposisi_monthly = m_koleksi.Disposisi.objects.filter(tanggal__year=tahun_active).values('tanggal__month').annotate(banyak=models.Count('*'))
	ukuran_disposisi = m_koleksi.Disposisi.objects.filter(tanggal__year=tahun_active).count()

	from webapp.models.master import AuditLog

	total_log = AuditLog.objects.count()

	user_teraktif = (
		AuditLog.objects.values('user__usr')
		.annotate(total=Count('log_id'))
		.order_by('-total')
		.first()
	)

	aksi_terbanyak = (
		AuditLog.objects.values('action')
		.annotate(total=Count('log_id'))
		.order_by('-total')
		.first()
	)

	hari_terbanyak = (
		AuditLog.objects.annotate(tanggal=TruncDate('timestamp'))
		.values('tanggal')
		.annotate(total=Count('log_id'))
		.order_by('-total')
		.first()
	)
	contents_limit = m_koleksi.Contents.objects.all().order_by('-_pk')[:50]
	categories = m_koleksi.Categories.objects.all().order_by('sort')
	skpds = Skpddg.objects.all()
	cleaned_skpds = []
	for skpd in skpds.values('pk', 'urai'): 
    # Pastikan Anda menggunakan nama field yang benar, di sini 'urai' dan 'pk'
		skpd['urai'] = safe_decode(skpd.get('urai'))
		cleaned_skpds.append(skpd)


	return render(request,'dash/home.html',{
		'tahun': tahun,
		'tahun_active': tahun_active,
		
		'berkasmasuk_monthly': list(berkasmasuk_monthly),
		'suratmasuk_monthly' : list(suratmasuk_monthly),
		'suratkeluar_monthly': list(suratkeluar_monthly),
        'skpds': cleaned_skpds,

		'banyak_berkas': banyak_berkas,
		'ukuran_berkas': ukuran_berkas,
		'ukuran_berkas': ukuran_berkas_total,

		'banyak_surat_masuk' : banyak_surat_masuk,
		'ukuran_surat_masuk' : ukuran_surat_masuk,

		'banyak_surat_keluar': banyak_surat_keluar,
		'ukuran_surat_keluar': ukuran_surat_keluar,

		'disposisi_monthly': list(disposisi_monthly),
    	'ukuran_disposisi': ukuran_disposisi,
		'total_log': total_log,
		'user_teraktif': user_teraktif or {'user__usr': 'N/A', 'total': 0},
		'aksi_terbanyak': aksi_terbanyak or {'action': 'N/A', 'total': 0},
		'hari_terbanyak': hari_terbanyak or {'tanggal': None, 'total': 0},
          
		# Tambahan total
		'total_berkas_surat': total_berkas_surat,
		'total_disposisi': total_disposisi,
          
		# Tambahan baru:
		'total_surat_masuk': total_surat_masuk,
		'total_surat_keluar': total_surat_keluar,
          
		'total_attachments': total_attachments,
		'ukuran_total_attachments': ukuran_total_attachments,

		# bulanan:
		'attachments_monthly': list(attachments_monthly) if attachments_monthly else [],
        'contents_limit': contents_limit,
        'categories': categories,
	})
# 

def page403(request,exception=None):
	return render(request,'403.html')
# 

def page404(request,exception=None):
	return render(request,'404.html')
# 

def generate_thumbnail(request):
	berkas = m_koleksi.Attachments.objects.all()
	for i in berkas:
		try:
			create_thumbnail(i.path)
		except Exception as e:
			pass
			
	return HttpResponse('Sukses')


@require_GET
def attachments_monthly_api(request):
    tahun_active = request.GET.get('tahun', None)
    if tahun_active is None:
        tahun_active = m_master.Ta._read_one(current='t')['tahun']

    data = (
        m_koleksi.Attachments.objects
        .filter(pk_contents__tahun=tahun_active)
        .annotate(bulan=ExtractMonth('created'))
        .values('bulan')
        .annotate(total=Count('*'))
    )

    return JsonResponse({'data': list(data)}, safe=False)


# ... (kode dash di atas)

from django.http import JsonResponse
from django.db import connection

def graph_skpd_view(request, pk):
    KONTRAK_ID = 22       # parent kategori Kontrak
    SP2D_ID = 30          # kategori SPP/SPM/SP2D

    result = []

    # ===============================
    # 1. Subkategori KONTRAK
    # ===============================
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, text
            FROM categories
            WHERE pk_parent = %s
            ORDER BY text
        """, [KONTRAK_ID])
        subcats = cursor.fetchall()

    for cat_id, cat_name in subcats:

        # Ambil file kategori
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT pk, judul
                FROM contents
                WHERE pk_categories = %s AND pk_skpd_dg = %s
            """, [cat_id, pk])
            files = cursor.fetchall()

        if not files:
            continue

        result.append({
            "id": cat_id,
            "name": cat_name,
            "children": [
                {"id": f[0], "title": f[1] or "(No Title)"}
                for f in files
            ]
        })

    # ===============================
    # 2. SP2D FILES
    # (diperlakukan sama seperti kategori biasa)
    # ===============================
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, judul
            FROM contents
            WHERE pk_categories = %s AND pk_skpd_dg = %s
        """, [SP2D_ID, pk])
        files_sp2d = cursor.fetchall()

    if files_sp2d:
        result.append({
            "id": SP2D_ID,
            "name": "SP2D",
            "children": [
                {"id": f[0], "title": f[1] or "(No Title)"}
                for f in files_sp2d
            ]
        })

    return JsonResponse(result, safe=False)



def get_files(pk_skpd, cat_id):
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, judul
            FROM contents
            WHERE pk_categories=%s AND pk_skpd_dg=%s
            ORDER BY pk DESC
            LIMIT 20
        """, [cat_id, pk_skpd])
        rows = cursor.fetchall()
    return [{"id": r[0], "title": r[1] or "(No Title)"} for r in rows]

def graph_category_view(request, pk):
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, judul
            FROM contents
            WHERE pk_categories = %s
            ORDER BY pk DESC
            LIMIT 100
        """, [pk])
        rows = cursor.fetchall()

    data = [{"pk": r[0], "judul": r[1]} for r in rows]
    return JsonResponse(data, safe=False)


def graph_content_view(request, pk):
    pk = int(pk)

    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, judul FROM contents WHERE pk=%s
        """, [pk])
        content = cursor.fetchone()

    if not content:
        return JsonResponse({'nodes': [], 'edges': []})

    nodes = [{
        "id": f"content-{content[0]}",
        "label": content[1] or "(No Title)",
        "group": "Content"
    }]
    edges = []

    # Attachments
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, name FROM attachments WHERE pk_contents=%s
        """, [pk])
        rows = cursor.fetchall()
        for r in rows:
            nodes.append({"id": f"att-{r[0]}", "label": r[1], "group": "Attachment"})
            edges.append({"from": f"content-{pk}", "to": f"att-{r[0]}"})

    # Versions
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT pk, judul FROM content_versions WHERE pk_contents=%s
        """, [pk])
        rows = cursor.fetchall()
        for r in rows:
            nodes.append({"id": f"ver-{r[0]}", "label": r[1], "group": "Version"})
            edges.append({"from": f"content-{pk}", "to": f"ver-{r[0]}"})

    # Version Attachments
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT va.pk, va.name, va.pk_version
            FROM content_version_attachments va
            JOIN content_versions cv ON cv.pk = va.pk_version
            WHERE cv.pk_contents=%s
        """, [pk])
        rows = cursor.fetchall()
        for r in rows:
            nodes.append({"id": f"vatt-{r[0]}", "label": r[1], "group": "VersionAttachment"})
            edges.append({"from": f"ver-{r[2]}", "to": f"vatt-{r[0]}"})

    return JsonResponse({"nodes": nodes, "edges": edges})