from flask import Blueprint, request, redirect, session, flash, render_template_string
import sqlite3
from database import DB_NAME
from shared_ui import HTML_TEMPLATE

rekap_bp = Blueprint('rekap', __name__)

@rekap_bp.route('/rekapitulasi')
def rekapitulasi():
    if not session.get('logged_in'):
        return redirect('/login')
        
    search_query = request.args.get('q', '')
    desa_filter = request.args.get('desa', '')
    bulan_filter = request.args.get('bulan', '')
    tahun_filter = request.args.get('tahun', '')
        
    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    
    query = "SELECT * FROM sasaran_lengkap WHERE 1=1"
    params = []
    
    if search_query:
        query += " AND (nik_penerima LIKE ? OR nama_penerima LIKE ?)"
        params.extend(['%'+search_query+'%', '%'+search_query+'%'])
    if desa_filter:
        query += " AND desa_sasaran COLLATE NOCASE = ?"
        params.append(desa_filter)
    if bulan_filter:
        query += " AND bulan_lapor = ?"
        params.append(bulan_filter)
    if tahun_filter:
        query += " AND tahun_lapor = ?"
        params.append(tahun_filter)
        
    query += " ORDER BY id DESC"
    c.execute(query, params)
    data_sasaran = c.fetchall()
    conn.close()
    
    processed_data = []
    for r in data_sasaran:
        r_list = list(r)
        ttl = r_list[11] if r_list[11] else ""
        if ", " in ttl:
            parts = ttl.split(", ", 1)
            tempat = parts[0]
            tanggal = parts[1]
        else:
            tempat = ttl
            tanggal = ""
        r_list.extend([tempat, tanggal])
        processed_data.append(r_list)
    
    return render_template_string(HTML_TEMPLATE, data=processed_data, 
                                  search_query=search_query, desa_filter=desa_filter, 
                                  bulan_filter=bulan_filter, tahun_filter=tahun_filter, 
                                  active_page='rekapitulasi', page_title='Rekapitulasi Sasaran 1000 HPK dan B3')

@rekap_bp.route('/rekap_laporan_kader')
def rekap_laporan_kader():
    if not session.get('logged_in'):
        return redirect('/login')
        
    desa_filter = request.args.get('desa', '')
    bulan_filter = request.args.get('bulan', '')
    tahun_filter = request.args.get('tahun', '')

    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    
    # PERBAIKAN: Memisahkan Balita dan Baduta sesuai urutan tabel HTML (row[0] s/d row[7])
    query = """
        SELECT 
            wilayah_posyandu,                                                                 -- row[0]
            SUM(CASE WHEN kategori_penerima = 'Balita Non-PAUD' THEN 1 ELSE 0 END) as jml_balita,   -- row[1]
            SUM(CASE WHEN kategori_penerima = 'Baduta' THEN 1 ELSE 0 END) as jml_baduta,            -- row[2]
            SUM(CASE WHEN kategori_penerima = 'Ibu Hamil' THEN 1 ELSE 0 END) as jml_bumil,          -- row[3]
            SUM(CASE WHEN kategori_penerima = 'Ibu Menyusui' THEN 1 ELSE 0 END) as jml_busui,       -- row[4]
            COUNT(id) as total_sasaran,                                                       -- row[5]
            bulan_lapor,                                                                      -- row[6]
            tahun_lapor                                                                       -- row[7]
        FROM sasaran_lengkap
        WHERE 1=1
    """
    params = []

    if desa_filter:
        query += " AND desa_sasaran COLLATE NOCASE = ?"
        params.append(desa_filter)
    if bulan_filter:
        query += " AND bulan_lapor = ?"
        params.append(bulan_filter)
    if tahun_filter:
        query += " AND tahun_lapor = ?"
        params.append(tahun_filter)
        
    query += " GROUP BY wilayah_posyandu, bulan_lapor, tahun_lapor ORDER BY tahun_lapor DESC, bulan_lapor DESC"
    
    c.execute(query, params)
    data_kader = c.fetchall()
    conn.close()
    
    return render_template_string(HTML_TEMPLATE, data_kader=data_kader,
                                  desa_filter=desa_filter, bulan_filter=bulan_filter, 
                                  tahun_filter=tahun_filter, 
                                  active_page='rekap_laporan_kader', page_title='Rekapitulasi Hasil Input Kader')

@rekap_bp.route('/hapus_rekap_kader_item')
def hapus_rekap_kader_item():
    if not session.get('logged_in'):
        return redirect('/login')
        
    posyandu = request.args.get('posyandu')
    bulan = request.args.get('bulan')
    tahun = request.args.get('tahun')

    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    c.execute("DELETE FROM sasaran_lengkap WHERE wilayah_posyandu=? AND bulan_lapor=? AND tahun_lapor=?", (posyandu, bulan, tahun))
    conn.commit()
    conn.close()
    
    flash(f'Keseluruhan Data rekapitulasi sasaran untuk Posyandu {posyandu} berhasil dihapus!', 'success')
    return redirect('/rekap_laporan_kader')