app/routes.py

from flask import render_template, jsonify, request
from app.map_py import create_map
from app.map_py_heatmap import create_heatmap_interactive
from app.table_summary import table_summary
from app.forecast import forecast
from app import app
import subprocess
import sys
import os
import pandas as pd
from collections import Counter
import warnings
warnings.filterwarnings("ignore")


@app.route('/')
def home():
    return render_template('index.html')


@app.route('/map')
def map_view():
    """Display interactive heatmap with choropleth (click to see case details)"""
    # Get filter parameters
    filter_year = request.args.get('year', 'all')
    filter_crime = request.args.get('crime', 'all')
    filter_city = request.args.get('city', 'all')
    
    map_html = create_heatmap_interactive(filter_year=filter_year, filter_crime=filter_crime, filter_city=filter_city)
    return render_template('map.html', map_html=map_html)


@app.route('/map-folium')
def map_folium_view():
    """Old Folium polygon map (backup)"""
    map_html = create_map()
    return render_template('map.html', map_html=map_html)


@app.route('/heatmap')
def heatmap_view():
    """Display GeoPandas-generated heatmap"""
    return render_template('heatmap.html')


@app.route('/generate-heatmap', methods=['POST'])
def generate_heatmap():
    """API endpoint to regenerate heatmap"""
    try:
        # Get the project root directory
        project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        script_path = os.path.join(project_root, 'scripts', 'generate_heatmap_geopandas.py')
        
        # Run the script
        result = subprocess.run(
            [sys.executable, script_path],
            cwd=project_root,
            capture_output=True,
            text=True,
            timeout=60
        )
        
        if result.returncode == 0:
            return jsonify({
                'success': True,
                'message': 'Heatmap generated successfully',
                'image_url': '/static/img/heatmap_jatim.png'
            })
        else:
            return jsonify({
                'success': False,
                'error': result.stderr or 'Unknown error'
            }), 500
            
    except subprocess.TimeoutExpired:
        return jsonify({
            'success': False,
            'error': 'Script timeout (lebih dari 60 detik)'
        }), 500
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@app.route('/api/statistics')
def get_statistics():
    """API endpoint untuk mendapatkan data statistik dari CSV"""
    # Get filter parameters from query string
    filter_year = request.args.get('year', 'all')
    filter_crime = request.args.get('crime', 'all')
    filter_city = request.args.get('city', 'all')
    
    # Load cleaned_data.csv (preferred) or fallback to meta folder
    cleaned_csv_path = 'cleaned_data.csv'
    print(os.getcwd())
    
    if os.path.exists(cleaned_csv_path):
        # Use cleaned_data.csv (single file, faster)
        print(f"Loading data from {cleaned_csv_path}")
        data = pd.read_csv(cleaned_csv_path, on_bad_lines='skip')
        data_lower = data.map(lambda x: x.lower().strip() if isinstance(x, str) and x.strip() != "" else x)
        
        # Parse year from tahun column
        if 'tahun' in data_lower.columns:
            data_lower['tahun_putusan'] = data_lower['tahun'].astype('Int64')
        
        # Count unique PNs
        exist_pn = data_lower['lembaga_peradilan'].nunique()
    
    # # Apply filters
    filtered_data = data_lower.copy()
    filtered = False
    
    # Filter by year
    if filter_year != 'all':
        try:
            year_val = int(filter_year)
            filtered_data = filtered_data[filtered_data['tahun_putusan'] == year_val]
            filtered = True
        except:
            pass
    
    # Filter by crime type
    if filter_crime != 'all':
        filtered_data = filtered_data[filtered_data['kata_kunci'] == filter_crime.lower()]
        filtered = True
    
    # Filter by city/kabupaten
    if filter_city != 'all':
        filtered_data = filtered_data[
            filtered_data['lembaga_peradilan'].str.contains(filter_city.lower(), case=False, na=False)
        ]
        filtered = True

    city_names = (
        data_lower['lembaga_peradilan']
        .str.replace(r'^pn\s+', '', regex=True)
        .str.strip()
        .str.title()
        .unique()        # ambil unik
    )

    # sort hasil unik
    city_names = sorted(city_names)

    city_options = [
        {'value': city.lower(), 'label': city}
        for city in city_names
    ]


    if (filtered):
        data_lower = filtered_data

    #mengambil untuk option dropdown
    # Crime types + count (sudah OK)
    all_crimes = data_lower['kata_kunci'].value_counts()
    crime_types = [
        {'value': crime, 'label': crime.title(), 'count': int(count)}
        for crime, count in all_crimes.items()
    ]

    # Years + count
    all_years = data_lower['tahun_putusan'].dropna().astype(int).value_counts().sort_index(ascending=False)
    year_options = [
        {'value': str(year), 'label': str(year), 'count': int(count)}
        for year, count in all_years.items()
    ]


    # Pastikan kolom tanggal ada
    if 'tanggal' in data_lower.columns:
        try:
            data_lower['tanggal'] = pd.to_datetime(
                data_lower['tanggal'], errors='coerce'
            )
        except:
            pass


    # Extract month
    data_lower['bulan_putusan'] = data_lower['tanggal'].dt.month
    data_lower['tahun_putusan'] = data_lower['tanggal'].dt.year

    # hitung jumlah per (tahun, bulan)
    grouped = (
        data_lower
        .groupby(['tahun_putusan', 'bulan_putusan'])
        .size()
        .reset_index(name='count')
    )

    # generate struktur lengkap: setiap tahun punya 12 bulan
    tahun_list = sorted(grouped['tahun_putusan'].unique())

    forecast_data = []

    for tahun in tahun_list:
        for bulan in range(1, 12+1):
            row = grouped[
                (grouped['tahun_putusan'] == tahun) &
                (grouped['bulan_putusan'] == bulan)
            ]
            
            jumlah = int(row['count'].iloc[0]) if not row.empty else 0

            forecast_data.append({
                'tahun': tahun,
                'bulan': bulan,
                'count': jumlah
            })

    forecast_result = forecast(forecast_data)

    # Monthly count (filtered data)
    monthly_counts_raw = (
        data_lower['bulan_putusan']
        .dropna()
        .astype(int)
        .value_counts()
        .to_dict()
    )

    # Buat full 1–12 (meskipun 0)
    monthly_data = [
        {'month': m, 'count': int(monthly_counts_raw.get(m, 0))}
        for m in range(1, 13)
    ]


    tabel = table_summary(data_lower)

    # Ganti NaN dengan 0 atau null
    tabel = tabel.fillna(0)

    # Atau kalau mau null:
    # tabel = tabel.where(pd.notnull(tabel), None)

    # Pastikan semua keys lowercase tanpa spasi ganda
    tabel.columns = (
        tabel.columns
            .str.strip()
            .str.replace(" ", "_")
            .str.lower()
    )

    return jsonify({
        'total_cases': len(filtered_data),
        'total_pn': exist_pn,
        'seasonal_data': monthly_data,
        'kasus_percentage': tabel.to_dict(orient="records"),   # <-- ini
        'crime_types': crime_types,
        'year_options': year_options,
        'city_options': city_options,
        'forecast_result': forecast_result,
        'filter_active': filter_year != 'all' or filter_crime != 'all' or filter_city != 'all'
    })