from flask import Flask, render_template, request, redirect, url_for, flash, session, jsonify, make_response, send_file
import sqlite3
import hashlib
from datetime import datetime
import os
import io
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from reportlab.lib import colors
from reportlab.lib.pagesizes import letter, A4
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch

app = Flask(__name__)
app.secret_key = 'siripikan_secret_key_2025'

# Database initialization
def init_db():
    conn = sqlite3.connect('siripikan.db')
    cursor = conn.cursor()
    
    # Users table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL,
            bidang TEXT,
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Master Data Tables
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS programs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            code TEXT UNIQUE,
            tahun_anggaran INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS kegiatan (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            program_id INTEGER,
            name TEXT NOT NULL,
            code TEXT,
            FOREIGN KEY (program_id) REFERENCES programs (id)
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS sub_kegiatan (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kegiatan_id INTEGER,
            name TEXT NOT NULL,
            code TEXT,
            FOREIGN KEY (kegiatan_id) REFERENCES kegiatan (id)
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS pekerjaan (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sub_kegiatan_id INTEGER,
            name TEXT NOT NULL,
            code TEXT,
            FOREIGN KEY (sub_kegiatan_id) REFERENCES sub_kegiatan (id)
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS sumber_dana (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            code TEXT UNIQUE
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS lokasi (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kecamatan TEXT NOT NULL,
            desa TEXT NOT NULL
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS pihak_ketiga (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            alamat TEXT,
            kontak TEXT
        )
    ''')
    
    # Realisasi table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS realisasi (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            program_id INTEGER,
            kegiatan_id INTEGER,
            sub_kegiatan_id INTEGER,
            pekerjaan_id INTEGER,
            lokasi_id INTEGER,
            sumber_dana_id INTEGER,
            pihak_ketiga_id INTEGER,
            bidang TEXT,
            target_fisik REAL,
            realisasi_fisik REAL,
            pagu_anggaran REAL,
            realisasi_keuangan REAL,
            nomor_kontrak TEXT,
            tanggal_kontrak DATE,
            waktu_mulai DATE,
            waktu_selesai DATE,
            nilai_kontrak REAL,
            status_kontrak TEXT DEFAULT 'Aktif',
            permasalahan TEXT,
            upaya_penyelesaian TEXT,
            keterangan TEXT,
            dokumentasi_url TEXT,
            bulan INTEGER,
            tahun INTEGER,
            created_by INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (program_id) REFERENCES programs (id),
            FOREIGN KEY (kegiatan_id) REFERENCES kegiatan (id),
            FOREIGN KEY (sub_kegiatan_id) REFERENCES sub_kegiatan (id),
            FOREIGN KEY (pekerjaan_id) REFERENCES pekerjaan (id),
            FOREIGN KEY (lokasi_id) REFERENCES lokasi (id),
            FOREIGN KEY (sumber_dana_id) REFERENCES sumber_dana (id),
            FOREIGN KEY (pihak_ketiga_id) REFERENCES pihak_ketiga (id),
            FOREIGN KEY (created_by) REFERENCES users (id)
        )
    ''')
    
    # Activity log table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS activity_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            action TEXT NOT NULL,
            table_name TEXT,
            record_id INTEGER,
            description TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
    ''')
    
    # Create default super admin (tetap diperlukan untuk login pertama kali)
    hashed_password = hashlib.md5('admin123'.encode()).hexdigest()
    cursor.execute('''
        INSERT OR IGNORE INTO users (username, password, role, bidang)
        VALUES (?, ?, ?, ?)
    ''', ('admin', hashed_password, 'super_admin', None))
    
    conn.commit()
    conn.close()

def hash_password(password):
    return hashlib.md5(password.encode()).hexdigest()

def get_db_connection():
    conn = sqlite3.connect('siripikan.db')
    conn.row_factory = sqlite3.Row
    return conn

def log_activity(user_id, action, table_name=None, record_id=None, description=None):
    conn = get_db_connection()
    conn.execute('''
        INSERT INTO activity_logs (user_id, action, table_name, record_id, description)
        VALUES (?, ?, ?, ?, ?)
    ''', (user_id, action, table_name, record_id, description))
    conn.commit()
    conn.close()

def login_required(f):
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    decorated_function.__name__ = f.__name__
    return decorated_function

def admin_required(f):
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session or session.get('role') != 'super_admin':
            flash('Akses ditolak. Hanya Super Admin yang bisa mengakses halaman ini.', 'error')
            return redirect(url_for('dashboard'))
        return f(*args, **kwargs)
    decorated_function.__name__ = f.__name__
    return decorated_function

def format_currency(amount):
    """Format currency to Indonesian Rupiah"""
    if amount is None:
        return "Rp 0"
    return f"Rp {amount:,.0f}".replace(',', '.')

def get_month_name(month_num):
    """Get Indonesian month name"""
    months = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
              'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember']
    return months[month_num] if 1 <= month_num <= 12 else '-'

def get_report_data(filters=None):
    """Get report data based on filters"""
    conn = get_db_connection()
    
    # Build query based on role
    if session['role'] == 'super_admin':
        base_query = '''
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE 1=1
        '''
        params = []
    else:
        base_query = '''
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE r.bidang = ?
        '''
        params = [session['bidang']]
    
    # Apply filters
    if filters:
        if filters.get('program_id'):
            base_query += ' AND r.program_id = ?'
            params.append(filters['program_id'])
        
        if filters.get('sumber_dana_id'):
            base_query += ' AND r.sumber_dana_id = ?'
            params.append(filters['sumber_dana_id'])
        
        if filters.get('bulan'):
            base_query += ' AND r.bulan = ?'
            params.append(filters['bulan'])
        
        if filters.get('tahun'):
            base_query += ' AND r.tahun = ?'
            params.append(filters['tahun'])
    
    # Main query
    main_query = f'''
        SELECT r.*, p.name as program_name, k.name as kegiatan_name, 
               sk.name as sub_kegiatan_name, pek.name as pekerjaan_name, l.kecamatan, l.desa,
               sd.name as sumber_dana_name, pk.name as pihak_ketiga_name
        {base_query}
        ORDER BY r.created_at DESC
    '''
    
    try:
        report_data = conn.execute(main_query, params).fetchall()
        conn.close()
        return report_data
    except Exception as e:
        print(f"Database error: {e}")
        conn.close()
        return []

@app.route('/')
def index():
    if 'user_id' in session:
        return redirect(url_for('dashboard'))
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        hashed_password = hash_password(password)
        
        conn = get_db_connection()
        user = conn.execute(
            'SELECT * FROM users WHERE username = ? AND password = ? AND is_active = 1',
            (username, hashed_password)
        ).fetchone()
        conn.close()
        
        if user:
            session['user_id'] = user['id']
            session['username'] = user['username']
            session['role'] = user['role']
            session['bidang'] = user['bidang']
            
            log_activity(user['id'], 'LOGIN', description=f'User {username} logged in')
            flash(f'Selamat datang, {username}!', 'success')
            return redirect(url_for('dashboard'))
        else:
            flash('Username atau password salah!', 'error')
    
    return render_template('login.html')

@app.route('/logout')
def logout():
    if 'user_id' in session:
        log_activity(session['user_id'], 'LOGOUT', description=f'User {session["username"]} logged out')
    session.clear()
    flash('Anda telah logout.', 'success')
    return redirect(url_for('login'))

@app.route('/dashboard')
@login_required
def dashboard():
    conn = get_db_connection()
    
    # Get dashboard statistics
    stats = {}
    
    try:
        # Total pagu and realisasi
        if session['role'] == 'super_admin':
            total_stats = conn.execute('''
                SELECT 
                    COALESCE(SUM(pagu_anggaran), 0) as total_pagu,
                    COALESCE(SUM(realisasi_keuangan), 0) as total_realisasi,
                    COALESCE(AVG(realisasi_fisik), 0) as avg_fisik,
                    COALESCE(AVG(CASE WHEN pagu_anggaran > 0 THEN (realisasi_keuangan * 100.0 / pagu_anggaran) END), 0) as avg_keuangan
                FROM realisasi WHERE tahun = ?
            ''', (datetime.now().year,)).fetchone()
        else:
            total_stats = conn.execute('''
                SELECT 
                    COALESCE(SUM(pagu_anggaran), 0) as total_pagu,
                    COALESCE(SUM(realisasi_keuangan), 0) as total_realisasi,
                    COALESCE(AVG(realisasi_fisik), 0) as avg_fisik,
                    COALESCE(AVG(CASE WHEN pagu_anggaran > 0 THEN (realisasi_keuangan * 100.0 / pagu_anggaran) END), 0) as avg_keuangan
                FROM realisasi WHERE tahun = ? AND bidang = ?
            ''', (datetime.now().year, session['bidang'])).fetchone()
        
        if total_stats:
            stats['total_pagu'] = total_stats['total_pagu'] or 0
            stats['total_realisasi'] = total_stats['total_realisasi'] or 0
            stats['avg_fisik'] = round(total_stats['avg_fisik'] or 0, 2)
            stats['avg_keuangan'] = round(total_stats['avg_keuangan'] or 0, 2)
            stats['sisa_anggaran'] = stats['total_pagu'] - stats['total_realisasi']
        else:
            stats = {
                'total_pagu': 0,
                'total_realisasi': 0,
                'avg_fisik': 0,
                'avg_keuangan': 0,
                'sisa_anggaran': 0
            }
        
        # Monthly data for chart
        if session['role'] == 'super_admin':
            monthly_data_rows = conn.execute('''
                SELECT 
                    bulan,
                    SUM(realisasi_keuangan) as realisasi_keuangan,
                    AVG(realisasi_fisik) as realisasi_fisik
                FROM realisasi 
                WHERE tahun = ?
                GROUP BY bulan
                ORDER BY bulan
            ''', (datetime.now().year,)).fetchall()
        else:
            monthly_data_rows = conn.execute('''
                SELECT 
                    bulan,
                    SUM(realisasi_keuangan) as realisasi_keuangan,
                    AVG(realisasi_fisik) as realisasi_fisik
                FROM realisasi 
                WHERE tahun = ? AND bidang = ?
                GROUP BY bulan
                ORDER BY bulan
            ''', (datetime.now().year, session['bidang'])).fetchall()
        
        # --- PERBAIKAN DIMULAI DI SINI ---
        # Convert list of Row objects to a list of dictionaries to make it JSON serializable
        monthly_data = [dict(row) for row in monthly_data_rows]
        # --- PERBAIKAN SELESAI ---

    except Exception as e:
        print(f"Error loading dashboard data: {e}")
        stats = {
            'total_pagu': 0,
            'total_realisasi': 0,
            'avg_fisik': 0,
            'avg_keuangan': 0,
            'sisa_anggaran': 0
        }
        monthly_data = []
    
    conn.close()
    
    return render_template('dashboard.html', stats=stats, monthly_data=monthly_data)

@app.route('/users')
@admin_required
def users():
    conn = get_db_connection()
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    per_page = 10
    offset = (page - 1) * per_page
    
    query = 'SELECT * FROM users WHERE username LIKE ?'
    count_query = 'SELECT COUNT(*) as count FROM users WHERE username LIKE ?'
    search_param = f'%{search}%'
    
    try:
        total_count_result = conn.execute(count_query, (search_param,)).fetchone()
        total_count = total_count_result['count'] if total_count_result else 0
        
        users = conn.execute(f'{query} ORDER BY created_at DESC LIMIT ? OFFSET ?', 
                           (search_param, per_page, offset)).fetchall()
    except Exception as e:
        print(f"Error loading users: {e}")
        total_count = 0
        users = []
    
    conn.close()
    
    total_pages = (total_count + per_page - 1) // per_page if total_count > 0 else 1
    
    return render_template('users.html', users=users, page=page, total_pages=total_pages, search=search)

@app.route('/add_user', methods=['POST'])
@admin_required
def add_user():
    username = request.form['username']
    password = request.form['password']
    role = request.form['role']
    bidang = request.form.get('bidang', '')
    
    hashed_password = hash_password(password)
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO users (username, password, role, bidang)
            VALUES (?, ?, ?, ?)
        ''', (username, hashed_password, role, bidang if bidang else None))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'users', description=f'Created user {username}')
        flash('User berhasil ditambahkan!', 'success')
    except sqlite3.IntegrityError:
        flash('Username sudah ada!', 'error')
    
    return redirect(url_for('users'))

@app.route('/get_user/<int:user_id>')
@admin_required
def get_user(user_id):
    conn = get_db_connection()
    user = conn.execute('SELECT id, username, role, bidang FROM users WHERE id = ?', (user_id,)).fetchone()
    conn.close()
    if user:
        return jsonify(dict(user))
    return jsonify({'error': 'User not found'}), 404

@app.route('/edit_user/<int:user_id>', methods=['POST'])
@admin_required
def edit_user(user_id):
    password = request.form.get('password')
    role = request.form['role']
    bidang = request.form.get('bidang', '')

    try:
        conn = get_db_connection()
        if password:
            hashed_password = hash_password(password)
            conn.execute('UPDATE users SET password = ?, role = ?, bidang = ? WHERE id = ?',
                         (hashed_password, role, bidang if bidang else None, user_id))
        else:
            conn.execute('UPDATE users SET role = ?, bidang = ? WHERE id = ?',
                         (role, bidang if bidang else None, user_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'users', user_id, f'Updated user ID {user_id}')
        flash('User berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect(url_for('users'))


@app.route('/delete_user/<int:user_id>')
@admin_required
def delete_user(user_id):
    conn = get_db_connection()
    user = conn.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
    
    if user:
        if user['username'] == 'admin':
            flash('Tidak dapat menghapus user admin utama!', 'error')
        else:
            conn.execute('DELETE FROM users WHERE id = ?', (user_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'users', user_id, f'Deleted user {user["username"]}')
            flash(f'User {user["username"]} berhasil dihapus!', 'success')
    
    conn.close()
    return redirect('/users')

@app.route('/add_program', methods=['POST'])
@admin_required
def add_program():
    name = request.form['program_name']
    code = request.form.get('program_code', '')
    tahun_anggaran = request.form.get('tahun_anggaran', '')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO programs (name, code, tahun_anggaran)
            VALUES (?, ?, ?)
        ''', (name, code if code else None, tahun_anggaran if tahun_anggaran else None))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'programs', description=f'Created program {name}')
        flash('Program berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data')

@app.route('/edit_program/<int:program_id>', methods=['POST'])
@admin_required
def edit_program(program_id):
    name = request.form['program_name']
    code = request.form.get('program_code', '')
    tahun_anggaran = request.form.get('tahun_anggaran', '')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            UPDATE programs SET name = ?, code = ?, tahun_anggaran = ?
            WHERE id = ?
        ''', (name, code if code else None, tahun_anggaran if tahun_anggaran else None, program_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'programs', program_id, f'Updated program {name}')
        flash('Program berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data')

@app.route('/delete_program/<int:program_id>')
@admin_required
def delete_program(program_id):
    conn = get_db_connection()
    
    # Check if program is used in realisasi
    usage_check = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE program_id = ?', (program_id,)).fetchone()
    
    if usage_check['count'] > 0:
        flash('Program tidak dapat dihapus karena masih digunakan dalam data realisasi!', 'error')
    else:
        program = conn.execute('SELECT * FROM programs WHERE id = ?', (program_id,)).fetchone()
        if program:
            # Delete related items first
            conn.execute('DELETE FROM pekerjaan WHERE sub_kegiatan_id IN (SELECT id FROM sub_kegiatan WHERE kegiatan_id IN (SELECT id FROM kegiatan WHERE program_id = ?))', (program_id,))
            conn.execute('DELETE FROM sub_kegiatan WHERE kegiatan_id IN (SELECT id FROM kegiatan WHERE program_id = ?)', (program_id,))
            conn.execute('DELETE FROM kegiatan WHERE program_id = ?', (program_id,))
            conn.execute('DELETE FROM programs WHERE id = ?', (program_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'programs', program_id, f'Deleted program {program["name"]}')
            flash(f'Program {program["name"]} berhasil dihapus!', 'success')
    
    conn.close()
    return redirect('/master_data')

@app.route('/add_kegiatan', methods=['POST'])
@admin_required
def add_kegiatan():
    program_id = request.form.get('program_id')
    name = request.form.get('kegiatan_name')
    code = request.form.get('kegiatan_code', '')

    if not program_id or not name:
        flash('Program dan Nama Kegiatan harus diisi!', 'error')
        return redirect('/master_data')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO kegiatan (program_id, name, code)
            VALUES (?, ?, ?)
        ''', (program_id, name, code if code else None))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'kegiatan', description=f'Created kegiatan {name}')
        flash('Kegiatan berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data')

@app.route('/edit_kegiatan/<int:kegiatan_id>', methods=['POST'])
@admin_required
def edit_kegiatan(kegiatan_id):
    program_id = request.form['program_id']
    name = request.form['kegiatan_name']
    code = request.form.get('kegiatan_code', '')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            UPDATE kegiatan SET program_id = ?, name = ?, code = ?
            WHERE id = ?
        ''', (program_id, name, code if code else None, kegiatan_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'kegiatan', kegiatan_id, f'Updated kegiatan {name}')
        flash('Kegiatan berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data#kegiatan')

@app.route('/delete_kegiatan/<int:kegiatan_id>')
@admin_required
def delete_kegiatan(kegiatan_id):
    conn = get_db_connection()
    
    # Check if kegiatan is used in realisasi or has sub_kegiatan
    usage_realisasi = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE kegiatan_id = ?', (kegiatan_id,)).fetchone()
    usage_sub = conn.execute('SELECT COUNT(*) as count FROM sub_kegiatan WHERE kegiatan_id = ?', (kegiatan_id,)).fetchone()

    if usage_realisasi['count'] > 0 or usage_sub['count'] > 0:
        flash('Kegiatan tidak dapat dihapus karena masih digunakan atau memiliki sub kegiatan!', 'error')
    else:
        kegiatan = conn.execute('SELECT * FROM kegiatan WHERE id = ?', (kegiatan_id,)).fetchone()
        if kegiatan:
            conn.execute('DELETE FROM pekerjaan WHERE sub_kegiatan_id IN (SELECT id FROM sub_kegiatan WHERE kegiatan_id = ?)', (kegiatan_id,))
            conn.execute('DELETE FROM sub_kegiatan WHERE kegiatan_id = ?', (kegiatan_id,))
            conn.execute('DELETE FROM kegiatan WHERE id = ?', (kegiatan_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'kegiatan', kegiatan_id, f'Deleted kegiatan {kegiatan["name"]}')
            flash(f'Kegiatan {kegiatan["name"]} berhasil dihapus!', 'success')
            
    conn.close()
    return redirect('/master_data')

@app.route('/add_sub_kegiatan', methods=['POST'])
@admin_required
def add_sub_kegiatan():
    kegiatan_id = request.form.get('kegiatan_id')
    name = request.form.get('sub_kegiatan_name')
    code = request.form.get('sub_kegiatan_code', '')

    if not kegiatan_id or not name:
        flash('Kegiatan dan Nama Sub Kegiatan harus diisi!', 'error')
        return redirect('/master_data')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO sub_kegiatan (kegiatan_id, name, code)
            VALUES (?, ?, ?)
        ''', (kegiatan_id, name, code if code else None))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'sub_kegiatan', description=f'Created sub_kegiatan {name}')
        flash('Sub Kegiatan berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data')

@app.route('/edit_sub_kegiatan/<int:sub_kegiatan_id>', methods=['POST'])
@admin_required
def edit_sub_kegiatan(sub_kegiatan_id):
    kegiatan_id = request.form['kegiatan_id']
    name = request.form['sub_kegiatan_name']
    code = request.form.get('sub_kegiatan_code', '')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            UPDATE sub_kegiatan SET kegiatan_id = ?, name = ?, code = ?
            WHERE id = ?
        ''', (kegiatan_id, name, code if code else None, sub_kegiatan_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'sub_kegiatan', sub_kegiatan_id, f'Updated sub kegiatan {name}')
        flash('Sub Kegiatan berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data#sub-kegiatan')


@app.route('/delete_sub_kegiatan/<int:sub_kegiatan_id>')
@admin_required
def delete_sub_kegiatan(sub_kegiatan_id):
    conn = get_db_connection()
    
    # Check if sub_kegiatan is used in realisasi or has pekerjaan
    usage_check = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE sub_kegiatan_id = ?', (sub_kegiatan_id,)).fetchone()
    usage_pekerjaan = conn.execute('SELECT COUNT(*) as count FROM pekerjaan WHERE sub_kegiatan_id = ?', (sub_kegiatan_id,)).fetchone()

    if usage_check['count'] > 0 or usage_pekerjaan['count'] > 0:
        flash('Sub Kegiatan tidak dapat dihapus karena masih digunakan atau memiliki pekerjaan!', 'error')
    else:
        sub_kegiatan = conn.execute('SELECT * FROM sub_kegiatan WHERE id = ?', (sub_kegiatan_id,)).fetchone()
        if sub_kegiatan:
            conn.execute('DELETE FROM sub_kegiatan WHERE id = ?', (sub_kegiatan_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'sub_kegiatan', sub_kegiatan_id, f'Deleted sub_kegiatan {sub_kegiatan["name"]}')
            flash(f'Sub Kegiatan {sub_kegiatan["name"]} berhasil dihapus!', 'success')
            
    conn.close()
    return redirect('/master_data')

@app.route('/add_pekerjaan', methods=['POST'])
@admin_required
def add_pekerjaan():
    sub_kegiatan_id = request.form.get('sub_kegiatan_id_for_pekerjaan')
    name = request.form.get('pekerjaan_name')
    code = request.form.get('pekerjaan_code', '')

    if not sub_kegiatan_id or not name:
        flash('Sub Kegiatan dan Nama Pekerjaan harus diisi!', 'error')
        return redirect('/master_data#pekerjaan')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO pekerjaan (sub_kegiatan_id, name, code)
            VALUES (?, ?, ?)
        ''', (sub_kegiatan_id, name, code if code else None))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'pekerjaan', description=f'Created pekerjaan {name}')
        flash('Pekerjaan berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data#pekerjaan')

@app.route('/edit_pekerjaan/<int:pekerjaan_id>', methods=['POST'])
@admin_required
def edit_pekerjaan(pekerjaan_id):
    sub_kegiatan_id = request.form['sub_kegiatan_id_for_pekerjaan']
    name = request.form['pekerjaan_name']
    code = request.form.get('pekerjaan_code', '')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            UPDATE pekerjaan SET sub_kegiatan_id = ?, name = ?, code = ?
            WHERE id = ?
        ''', (sub_kegiatan_id, name, code if code else None, pekerjaan_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'pekerjaan', pekerjaan_id, f'Updated pekerjaan {name}')
        flash('Pekerjaan berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data#pekerjaan')


@app.route('/delete_pekerjaan/<int:pekerjaan_id>')
@admin_required
def delete_pekerjaan(pekerjaan_id):
    conn = get_db_connection()
    
    usage_check = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE pekerjaan_id = ?', (pekerjaan_id,)).fetchone()

    if usage_check['count'] > 0:
        flash('Pekerjaan tidak dapat dihapus karena masih digunakan dalam data realisasi!', 'error')
    else:
        pekerjaan = conn.execute('SELECT * FROM pekerjaan WHERE id = ?', (pekerjaan_id,)).fetchone()
        if pekerjaan:
            conn.execute('DELETE FROM pekerjaan WHERE id = ?', (pekerjaan_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'pekerjaan', pekerjaan_id, f'Deleted pekerjaan {pekerjaan["name"]}')
            flash(f'Pekerjaan {pekerjaan["name"]} berhasil dihapus!', 'success')
            
    conn.close()
    return redirect('/master_data#pekerjaan')


@app.route('/add_sumber_dana', methods=['POST'])
@admin_required
def add_sumber_dana():
    name = request.form['sumber_dana_name']
    code = request.form['sumber_dana_code']
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO sumber_dana (name, code)
            VALUES (?, ?)
        ''', (name, code))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'sumber_dana', description=f'Created sumber dana {name}')
        flash('Sumber dana berhasil ditambahkan!', 'success')
    except sqlite3.IntegrityError:
        flash('Kode sumber dana sudah ada!', 'error')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data')

@app.route('/edit_sumber_dana/<int:sumber_dana_id>', methods=['POST'])
@admin_required
def edit_sumber_dana(sumber_dana_id):
    name = request.form['sumber_dana_name']
    code = request.form['sumber_dana_code']
    
    try:
        conn = get_db_connection()
        conn.execute('UPDATE sumber_dana SET name = ?, code = ? WHERE id = ?', (name, code, sumber_dana_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'sumber_dana', sumber_dana_id, f'Updated sumber dana {name}')
        flash('Sumber dana berhasil diperbarui!', 'success')
    except sqlite3.IntegrityError:
        flash('Kode sumber dana sudah ada!', 'error')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data#sumber-dana')

@app.route('/delete_sumber_dana/<int:sumber_dana_id>')
@admin_required
def delete_sumber_dana(sumber_dana_id):
    conn = get_db_connection()
    
    # Check if sumber dana is used in realisasi
    usage_check = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE sumber_dana_id = ?', (sumber_dana_id,)).fetchone()
    
    if usage_check['count'] > 0:
        flash('Sumber dana tidak dapat dihapus karena masih digunakan dalam data realisasi!', 'error')
    else:
        sumber_dana = conn.execute('SELECT * FROM sumber_dana WHERE id = ?', (sumber_dana_id,)).fetchone()
        if sumber_dana:
            conn.execute('DELETE FROM sumber_dana WHERE id = ?', (sumber_dana_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'sumber_dana', sumber_dana_id, f'Deleted sumber dana {sumber_dana["name"]}')
            flash(f'Sumber dana {sumber_dana["name"]} berhasil dihapus!', 'success')
    
    conn.close()
    return redirect('/master_data')

@app.route('/add_lokasi', methods=['POST'])
@admin_required
def add_lokasi():
    kecamatan = request.form['kecamatan']
    desa = request.form['desa']
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO lokasi (kecamatan, desa)
            VALUES (?, ?)
        ''', (kecamatan, desa))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'lokasi', description=f'Created lokasi {kecamatan} - {desa}')
        flash('Lokasi berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data')

@app.route('/edit_lokasi/<int:lokasi_id>', methods=['POST'])
@admin_required
def edit_lokasi(lokasi_id):
    kecamatan = request.form['kecamatan']
    desa = request.form['desa']
    
    try:
        conn = get_db_connection()
        conn.execute('UPDATE lokasi SET kecamatan = ?, desa = ? WHERE id = ?', (kecamatan, desa, lokasi_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'lokasi', lokasi_id, f'Updated lokasi {kecamatan} - {desa}')
        flash('Lokasi berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data#lokasi')


@app.route('/delete_lokasi/<int:lokasi_id>')
@admin_required
def delete_lokasi(lokasi_id):
    conn = get_db_connection()
    
    # Check if lokasi is used in realisasi
    usage_check = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE lokasi_id = ?', (lokasi_id,)).fetchone()
    
    if usage_check['count'] > 0:
        flash('Lokasi tidak dapat dihapus karena masih digunakan dalam data realisasi!', 'error')
    else:
        lokasi = conn.execute('SELECT * FROM lokasi WHERE id = ?', (lokasi_id,)).fetchone()
        if lokasi:
            conn.execute('DELETE FROM lokasi WHERE id = ?', (lokasi_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'lokasi', lokasi_id, f'Deleted lokasi {lokasi["kecamatan"]} - {lokasi["desa"]}')
            flash(f'Lokasi {lokasi["kecamatan"]} - {lokasi["desa"]} berhasil dihapus!', 'success')
    
    conn.close()
    return redirect('/master_data')

@app.route('/add_pihak_ketiga', methods=['POST'])
@admin_required
def add_pihak_ketiga():
    name = request.form['pihak_ketiga_name']
    alamat = request.form.get('alamat', '')
    kontak = request.form.get('kontak', '')
    
    try:
        conn = get_db_connection()
        conn.execute('''
            INSERT INTO pihak_ketiga (name, alamat, kontak)
            VALUES (?, ?, ?)
        ''', (name, alamat if alamat else None, kontak if kontak else None))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'pihak_ketiga', description=f'Created pihak ketiga {name}')
        flash('Pihak ketiga berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect('/master_data')

@app.route('/edit_pihak_ketiga/<int:pihak_ketiga_id>', methods=['POST'])
@admin_required
def edit_pihak_ketiga(pihak_ketiga_id):
    name = request.form['pihak_ketiga_name']
    alamat = request.form.get('alamat', '')
    kontak = request.form.get('kontak', '')
    
    try:
        conn = get_db_connection()
        conn.execute('UPDATE pihak_ketiga SET name = ?, alamat = ?, kontak = ? WHERE id = ?', 
                     (name, alamat if alamat else None, kontak if kontak else None, pihak_ketiga_id))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'pihak_ketiga', pihak_ketiga_id, f'Updated pihak ketiga {name}')
        flash('Pihak ketiga berhasil diperbarui!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect('/master_data#pihak-ketiga')


@app.route('/delete_pihak_ketiga/<int:pihak_ketiga_id>')
@admin_required
def delete_pihak_ketiga(pihak_ketiga_id):
    conn = get_db_connection()
    
    # Check if pihak ketiga is used in realisasi
    usage_check = conn.execute('SELECT COUNT(*) as count FROM realisasi WHERE pihak_ketiga_id = ?', (pihak_ketiga_id,)).fetchone()
    
    if usage_check['count'] > 0:
        flash('Pihak ketiga tidak dapat dihapus karena masih digunakan dalam data realisasi!', 'error')
    else:
        pihak_ketiga = conn.execute('SELECT * FROM pihak_ketiga WHERE id = ?', (pihak_ketiga_id,)).fetchone()
        if pihak_ketiga:
            conn.execute('DELETE FROM pihak_ketiga WHERE id = ?', (pihak_ketiga_id,))
            conn.commit()
            
            log_activity(session['user_id'], 'DELETE', 'pihak_ketiga', pihak_ketiga_id, f'Deleted pihak ketiga {pihak_ketiga["name"]}')
            flash(f'Pihak ketiga {pihak_ketiga["name"]} berhasil dihapus!', 'success')
    
    conn.close()
    return redirect('/master_data')

@app.route('/delete_realisasi/<int:realisasi_id>')
@login_required
def delete_realisasi(realisasi_id):
    conn = get_db_connection()
    
    # Check if user has permission to delete this record
    if session['role'] == 'super_admin':
        realisasi = conn.execute('SELECT * FROM realisasi WHERE id = ?', (realisasi_id,)).fetchone()
    else:
        realisasi = conn.execute('SELECT * FROM realisasi WHERE id = ? AND bidang = ?', (realisasi_id, session['bidang'])).fetchone()
    
    if realisasi:
        conn.execute('DELETE FROM realisasi WHERE id = ?', (realisasi_id,))
        conn.commit()
        
        log_activity(session['user_id'], 'DELETE', 'realisasi', realisasi_id, 'Deleted realisasi data')
        flash('Data realisasi berhasil dihapus!', 'success')
    else:
        flash('Data tidak ditemukan atau Anda tidak memiliki akses untuk menghapus data ini!', 'error')
    
    conn.close()
    return redirect('/realisasi')

@app.route('/toggle_user/<int:user_id>')
@admin_required
def toggle_user(user_id):
    conn = get_db_connection()
    user = conn.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
    
    if user:
        new_status = 0 if user['is_active'] else 1
        conn.execute('UPDATE users SET is_active = ? WHERE id = ?', (new_status, user_id))
        conn.commit()
        
        status_text = 'diaktifkan' if new_status else 'dinonaktifkan'
        log_activity(session['user_id'], 'UPDATE', 'users', user_id, f'User {user["username"]} {status_text}')
        flash(f'User {user["username"]} berhasil {status_text}!', 'success')
    
    conn.close()
    return redirect(url_for('users'))

# Generic getter for master data modals
@app.route('/get_master_data/<string:table_name>/<int:record_id>')
@admin_required
def get_master_data(table_name, record_id):
    conn = get_db_connection()
    # Basic validation to prevent arbitrary table access
    allowed_tables = ['programs', 'kegiatan', 'sub_kegiatan', 'pekerjaan', 'sumber_dana', 'lokasi', 'pihak_ketiga']
    if table_name not in allowed_tables:
        return jsonify({'error': 'Invalid master data type'}), 400

    # For tables with dependencies, fetch the parent id
    if table_name == 'kegiatan':
        query = 'SELECT k.*, p.id as program_id FROM kegiatan k JOIN programs p ON k.program_id = p.id WHERE k.id = ?'
    elif table_name == 'sub_kegiatan':
        query = 'SELECT sk.*, k.program_id FROM sub_kegiatan sk JOIN kegiatan k ON sk.kegiatan_id = k.id WHERE sk.id = ?'
    elif table_name == 'pekerjaan':
        query = 'SELECT p.*, sk.kegiatan_id, k.program_id FROM pekerjaan p JOIN sub_kegiatan sk ON p.sub_kegiatan_id = sk.id JOIN kegiatan k ON sk.kegiatan_id = k.id WHERE p.id = ?'
    else:
        query = f'SELECT * FROM {table_name} WHERE id = ?'
        
    record = conn.execute(query, (record_id,)).fetchone()
    conn.close()
    if record:
        return jsonify(dict(record))
    return jsonify({'error': 'Record not found'}), 404

@app.route('/master_data')
@admin_required
def master_data():
    conn = get_db_connection()
    
    try:
        programs = conn.execute('SELECT * FROM programs ORDER BY created_at DESC').fetchall()
        kegiatan = conn.execute('''
            SELECT k.*, p.name as program_name 
            FROM kegiatan k JOIN programs p ON k.program_id = p.id 
            ORDER BY p.name, k.name
        ''').fetchall()
        sub_kegiatan = conn.execute('''
            SELECT sk.*, k.name as kegiatan_name 
            FROM sub_kegiatan sk JOIN kegiatan k ON sk.kegiatan_id = k.id 
            ORDER BY k.name, sk.name
        ''').fetchall()
        pekerjaan = conn.execute('''
            SELECT p.*, sk.name as sub_kegiatan_name, k.name as kegiatan_name, prog.name as program_name
            FROM pekerjaan p 
            JOIN sub_kegiatan sk ON p.sub_kegiatan_id = sk.id
            JOIN kegiatan k ON sk.kegiatan_id = k.id
            JOIN programs prog ON k.program_id = prog.id
            ORDER BY prog.name, k.name, sk.name, p.name
        ''').fetchall()
        sumber_dana = conn.execute('SELECT * FROM sumber_dana ORDER BY name').fetchall()
        lokasi = conn.execute('SELECT * FROM lokasi ORDER BY kecamatan, desa').fetchall()
        pihak_ketiga = conn.execute('SELECT * FROM pihak_ketiga ORDER BY name').fetchall()
    except Exception as e:
        print(f"Error loading master data: {e}")
        programs, kegiatan, sub_kegiatan, pekerjaan, sumber_dana, lokasi, pihak_ketiga = [], [], [], [], [], [], []
    
    conn.close()
    
    return render_template('master_data.html', 
                         programs=programs,
                         kegiatan=kegiatan,
                         sub_kegiatan=sub_kegiatan,
                         pekerjaan=pekerjaan,
                         sumber_dana=sumber_dana, 
                         lokasi=lokasi, 
                         pihak_ketiga=pihak_ketiga)

@app.route('/realisasi')
@login_required
def realisasi():
    conn = get_db_connection()
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    per_page = 10
    offset = (page - 1) * per_page
    
    # Build query based on role
    if session['role'] == 'super_admin':
        base_query = '''
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE (p.name LIKE ? OR k.name LIKE ? OR sk.name LIKE ? OR pek.name LIKE ? OR ? = '')
        '''
        search_params = [f'%{search}%', f'%{search}%', f'%{search}%', f'%{search}%', search]
    else:
        base_query = '''
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE r.bidang = ? AND (p.name LIKE ? OR k.name LIKE ? OR sk.name LIKE ? OR pek.name LIKE ? OR ? = '')
        '''
        search_params = [session['bidang'], f'%{search}%', f'%{search}%', f'%{search}%', f'%{search}%', search]
    
    # Count query
    count_query = f'SELECT COUNT(*) as count {base_query}'
    
    # Main query
    main_query = f'''
        SELECT r.*, p.name as program_name, k.name as kegiatan_name, 
               sk.name as sub_kegiatan_name, pek.name as pekerjaan_name, l.kecamatan, l.desa,
               sd.name as sumber_dana_name, pk.name as pihak_ketiga_name
        {base_query}
        ORDER BY r.created_at DESC LIMIT ? OFFSET ?
    '''
    
    try:
        total_count_result = conn.execute(count_query, search_params).fetchone()
        total_count = total_count_result['count'] if total_count_result else 0
        
        realisasi_rows = conn.execute(main_query, search_params + [per_page, offset]).fetchall()
        # --- PERBAIKAN DI SINI ---
        realisasi_data = [dict(row) for row in realisasi_rows]

    except Exception as e:
        print(f"Database error: {e}")
        total_count = 0
        realisasi_data = []
    
    # Get master data for form
    try:
        programs = conn.execute('SELECT * FROM programs ORDER BY name').fetchall()
        sumber_dana = conn.execute('SELECT * FROM sumber_dana ORDER BY name').fetchall()
        lokasi = conn.execute('SELECT * FROM lokasi ORDER BY kecamatan, desa').fetchall()
        pihak_ketiga = conn.execute('SELECT * FROM pihak_ketiga ORDER BY name').fetchall()
    except Exception as e:
        print(f"Master data error: {e}")
        programs = []
        sumber_dana = []
        lokasi = []
        pihak_ketiga = []
    
    conn.close()
    
    total_pages = (total_count + per_page - 1) // per_page if total_count > 0 else 1
    
    return render_template('realisasi.html', 
                         realisasi_data=realisasi_data, 
                         programs=programs,
                         sumber_dana=sumber_dana,
                         lokasi=lokasi,
                         pihak_ketiga=pihak_ketiga,
                         page=page, 
                         total_pages=total_pages, 
                         search=search)

@app.route('/add_realisasi', methods=['POST'])
@login_required
def add_realisasi():
    try:
        conn = get_db_connection()
        
        # Get form data
        data = {
            'program_id': request.form.get('program_id'),
            'kegiatan_id': request.form.get('kegiatan_id'),
            'sub_kegiatan_id': request.form.get('sub_kegiatan_id'),
            'pekerjaan_id': request.form.get('pekerjaan_id') or None,
            'lokasi_id': request.form.get('lokasi_id'),
            'sumber_dana_id': request.form.get('sumber_dana_id'),
            'pihak_ketiga_id': request.form.get('pihak_ketiga_id') or None,
            'bidang': session['bidang'] if session['role'] != 'super_admin' else request.form.get('bidang'),
            'target_fisik': float(request.form.get('target_fisik', 0)),
            'realisasi_fisik': float(request.form.get('realisasi_fisik', 0)),
            'pagu_anggaran': float(request.form.get('pagu_anggaran', 0)),
            'realisasi_keuangan': float(request.form.get('realisasi_keuangan', 0)),
            'nomor_kontrak': request.form.get('nomor_kontrak'),
            'tanggal_kontrak': request.form.get('tanggal_kontrak') or None,
            'waktu_mulai': request.form.get('waktu_mulai') or None,
            'waktu_selesai': request.form.get('waktu_selesai') or None,
            'nilai_kontrak': float(request.form.get('nilai_kontrak', 0)),
            'status_kontrak': request.form.get('status_kontrak', 'Aktif'),
            'permasalahan': request.form.get('permasalahan'),
            'upaya_penyelesaian': request.form.get('upaya_penyelesaian'),
            'keterangan': request.form.get('keterangan'),
            'dokumentasi_url': request.form.get('dokumentasi_url') or None,
            'bulan': int(request.form.get('bulan')),
            'tahun': int(request.form.get('tahun')),
            'created_by': session['user_id']
        }
        
        conn.execute('''
            INSERT INTO realisasi (
                program_id, kegiatan_id, sub_kegiatan_id, pekerjaan_id, lokasi_id, 
                sumber_dana_id, pihak_ketiga_id, bidang, target_fisik, 
                realisasi_fisik, pagu_anggaran, realisasi_keuangan,
                nomor_kontrak, tanggal_kontrak, waktu_mulai, waktu_selesai,
                nilai_kontrak, status_kontrak, permasalahan, upaya_penyelesaian,
                keterangan, dokumentasi_url, bulan, tahun, created_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', tuple(data.values()))
        
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'CREATE', 'realisasi', description='Added new realisasi data')
        flash('Data realisasi berhasil ditambahkan!', 'success')
        
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('realisasi'))

@app.route('/get_realisasi/<int:realisasi_id>')
@login_required
def get_realisasi(realisasi_id):
    conn = get_db_connection()
    # Also fetch the grand-parent id for cascading dropdowns
    query = '''
        SELECT r.*, sk.kegiatan_id, k.program_id
        FROM realisasi r
        LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
        LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
        WHERE r.id = ?
    '''
    realisasi = conn.execute(query, (realisasi_id,)).fetchone()
    conn.close()
    if realisasi:
        return jsonify(dict(realisasi))
    return jsonify({'error': 'Realisasi data not found'}), 404

@app.route('/edit_realisasi/<int:realisasi_id>', methods=['POST'])
@login_required
def edit_realisasi(realisasi_id):
    try:
        conn = get_db_connection()
        
        data = {
            'program_id': request.form.get('program_id'),
            'kegiatan_id': request.form.get('kegiatan_id'),
            'sub_kegiatan_id': request.form.get('sub_kegiatan_id'),
            'pekerjaan_id': request.form.get('pekerjaan_id') or None,
            'lokasi_id': request.form.get('lokasi_id'),
            'sumber_dana_id': request.form.get('sumber_dana_id'),
            'pihak_ketiga_id': request.form.get('pihak_ketiga_id') or None,
            'bidang': session['bidang'] if session['role'] != 'super_admin' else request.form.get('bidang'),
            'target_fisik': float(request.form.get('target_fisik', 0)),
            'realisasi_fisik': float(request.form.get('realisasi_fisik', 0)),
            'pagu_anggaran': float(request.form.get('pagu_anggaran', 0)),
            'realisasi_keuangan': float(request.form.get('realisasi_keuangan', 0)),
            'nomor_kontrak': request.form.get('nomor_kontrak'),
            'tanggal_kontrak': request.form.get('tanggal_kontrak') or None,
            'waktu_mulai': request.form.get('waktu_mulai') or None,
            'waktu_selesai': request.form.get('waktu_selesai') or None,
            'nilai_kontrak': float(request.form.get('nilai_kontrak', 0)),
            'status_kontrak': request.form.get('status_kontrak', 'Aktif'),
            'permasalahan': request.form.get('permasalahan'),
            'upaya_penyelesaian': request.form.get('upaya_penyelesaian'),
            'keterangan': request.form.get('keterangan'),
            'dokumentasi_url': request.form.get('dokumentasi_url') or None,
            'bulan': int(request.form.get('bulan')),
            'tahun': int(request.form.get('tahun')),
            'updated_at': datetime.now()
        }
        
        # Check permission before update
        if session['role'] != 'super_admin':
            realisasi_check = conn.execute('SELECT id FROM realisasi WHERE id = ? AND bidang = ?', 
                                           (realisasi_id, session['bidang'])).fetchone()
            if not realisasi_check:
                flash('Anda tidak memiliki izin untuk mengedit data ini.', 'error')
                return redirect(url_for('realisasi'))
        
        query = '''
            UPDATE realisasi SET
            program_id=?, kegiatan_id=?, sub_kegiatan_id=?, pekerjaan_id=?, lokasi_id=?,
            sumber_dana_id=?, pihak_ketiga_id=?, bidang=?, target_fisik=?,
            realisasi_fisik=?, pagu_anggaran=?, realisasi_keuangan=?,
            nomor_kontrak=?, tanggal_kontrak=?, waktu_mulai=?, waktu_selesai=?,
            nilai_kontrak=?, status_kontrak=?, permasalahan=?, upaya_penyelesaian=?,
            keterangan=?, dokumentasi_url=?, bulan=?, tahun=?, updated_at=?
            WHERE id=?
        '''
        params = list(data.values()) + [realisasi_id]
        conn.execute(query, tuple(params))
        conn.commit()
        conn.close()
        
        log_activity(session['user_id'], 'UPDATE', 'realisasi', realisasi_id, 'Updated realisasi data')
        flash('Data realisasi berhasil diperbarui!', 'success')
        
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
        
    return redirect(url_for('realisasi'))

@app.route('/get_kegiatan/<int:program_id>')
@login_required
def get_kegiatan(program_id):
    conn = get_db_connection()
    kegiatan = conn.execute('SELECT * FROM kegiatan WHERE program_id = ? ORDER BY name', (program_id,)).fetchall()
    conn.close()
    
    return jsonify([{'id': k['id'], 'name': k['name']} for k in kegiatan])

@app.route('/get_sub_kegiatan/<int:kegiatan_id>')
@login_required
def get_sub_kegiatan(kegiatan_id):
    conn = get_db_connection()
    sub_kegiatan = conn.execute('SELECT * FROM sub_kegiatan WHERE kegiatan_id = ? ORDER BY name', (kegiatan_id,)).fetchall()
    conn.close()
    
    return jsonify([{'id': sk['id'], 'name': sk['name']} for sk in sub_kegiatan])

@app.route('/get_pekerjaan/<int:sub_kegiatan_id>')
@login_required
def get_pekerjaan(sub_kegiatan_id):
    conn = get_db_connection()
    pekerjaan = conn.execute('SELECT * FROM pekerjaan WHERE sub_kegiatan_id = ? ORDER BY name', (sub_kegiatan_id,)).fetchall()
    conn.close()
    
    return jsonify([{'id': p['id'], 'name': p['name']} for p in pekerjaan])


@app.route('/reports')
@login_required  
def reports():
    conn = get_db_connection()
    
    # Get filter options
    try:
        programs = conn.execute('SELECT * FROM programs ORDER BY name').fetchall()
        sumber_dana = conn.execute('SELECT * FROM sumber_dana ORDER BY name').fetchall()
    except Exception as e:
        print(f"Error loading filter options: {e}")
        programs = []
        sumber_dana = []
    
    # Get current filters
    program_filter = request.args.get('program_id', '')
    sumber_dana_filter = request.args.get('sumber_dana_id', '')
    bulan_filter = request.args.get('bulan', '')
    tahun_filter = request.args.get('tahun', str(datetime.now().year))
    
    # Build query
    where_conditions = ['r.tahun = ?']
    params = [tahun_filter]
    
    if session['role'] != 'super_admin':
        where_conditions.append('r.bidang = ?')
        params.append(session['bidang'])
    
    if program_filter:
        where_conditions.append('r.program_id = ?')
        params.append(program_filter)
    
    if sumber_dana_filter:
        where_conditions.append('r.sumber_dana_id = ?')
        params.append(sumber_dana_filter)
    
    if bulan_filter:
        where_conditions.append('r.bulan = ?')
        params.append(bulan_filter)
    
    where_clause = ' AND '.join(where_conditions) if where_conditions else '1=1'
    
    try:
        report_data = conn.execute(f'''
            SELECT r.*, p.name as program_name, k.name as kegiatan_name, 
                   sk.name as sub_kegiatan_name, pek.name as pekerjaan_name, l.kecamatan, l.desa,
                   sd.name as sumber_dana_name, pk.name as pihak_ketiga_name
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE {where_clause}
            ORDER BY r.bulan, r.created_at
        ''', params).fetchall()
    except Exception as e:
        print(f"Error loading report data: {e}")
        report_data = []
    
    # Calculate summary
    if report_data:
        summary = {
            'total_pagu': sum(row['pagu_anggaran'] or 0 for row in report_data),
            'total_realisasi': sum(row['realisasi_keuangan'] or 0 for row in report_data),
            'avg_fisik': sum(row['realisasi_fisik'] or 0 for row in report_data) / len(report_data) if report_data else 0,
            'count': len(report_data)
        }
    else:
        summary = {
            'total_pagu': 0,
            'total_realisasi': 0,
            'avg_fisik': 0,
            'count': 0
        }
    
    conn.close()
    
    return render_template('reports.html', 
                         report_data=report_data,
                         summary=summary,
                         programs=programs,
                         sumber_dana=sumber_dana,
                         filters={
                             'program_id': program_filter,
                             'sumber_dana_id': sumber_dana_filter,
                             'bulan': bulan_filter,
                             'tahun': tahun_filter
                         })

@app.route('/reports/quarterly')
@login_required
def quarterly_report():
    conn = get_db_connection()
    
    # Get quarter and year from parameters
    quarter = request.args.get('quarter', type=int)
    year = request.args.get('year', datetime.now().year, type=int)
    
    # If no quarter specified, determine current quarter
    if not quarter:
        current_month = datetime.now().month
        if current_month <= 3:
            quarter = 1
        elif current_month <= 6:
            quarter = 2
        elif current_month <= 9:
            quarter = 3
        else:
            quarter = 4
    
    # Define month ranges for quarters
    quarter_months = {
        1: [1, 2, 3],    # Q1: Jan-Mar
        2: [4, 5, 6],    # Q2: Apr-Jun
        3: [7, 8, 9],    # Q3: Jul-Sep
        4: [10, 11, 12]  # Q4: Oct-Dec
    }
    
    quarter_names = {
        1: "Triwulan I (Januari - Maret)",
        2: "Triwulan II (April - Juni)", 
        3: "Triwulan III (Juli - September)",
        4: "Triwulan IV (Oktober - Desember)"
    }
    
    months = quarter_months.get(quarter, [1, 2, 3])
    quarter_name = quarter_names.get(quarter, "Triwulan I")
    
    try:
        # Build query based on role
        base_conditions = ['r.tahun = ?']
        params = [year]
        
        if session['role'] != 'super_admin':
            base_conditions.append('r.bidang = ?')
            params.append(session['bidang'])
        
        # Add month conditions for quarter
        month_conditions = ' OR '.join(['r.bulan = ?' for _ in months])
        base_conditions.append(f'({month_conditions})')
        params.extend(months)
        
        where_clause = ' AND '.join(base_conditions)
        
        quarterly_data = conn.execute(f'''
            SELECT r.*, p.name as program_name, k.name as kegiatan_name, 
                   sk.name as sub_kegiatan_name, pek.name as pekerjaan_name, l.kecamatan, l.desa,
                   sd.name as sumber_dana_name, pk.name as pihak_ketiga_name
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE {where_clause}
            ORDER BY r.bulan, r.created_at
        ''', params).fetchall()
        
        # Get monthly breakdown
        monthly_breakdown = {}
        for month in months:
            # Create params for this specific month's query
            month_params = [year]
            if session['role'] != 'super_admin':
                month_params.append(session['bidang'])
            month_params.append(month)

            month_where_conditions = ['tahun = ?']
            if session['role'] != 'super_admin':
                month_where_conditions.append('bidang = ?')
            month_where_conditions.append('bulan = ?')
            month_where = ' AND '.join(month_where_conditions)

            month_data = conn.execute(f'''
                SELECT 
                    COUNT(*) as count,
                    COALESCE(SUM(pagu_anggaran), 0) as total_pagu,
                    COALESCE(SUM(realisasi_keuangan), 0) as total_realisasi,
                    COALESCE(AVG(realisasi_fisik), 0) as avg_fisik
                FROM realisasi
                WHERE {month_where}
            ''', month_params).fetchone()
            
            month_names = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
                          'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember']
            
            monthly_breakdown[month] = {
                'name': month_names[month],
                'count': month_data['count'] if month_data else 0,
                'total_pagu': month_data['total_pagu'] if month_data else 0,
                'total_realisasi': month_data['total_realisasi'] if month_data else 0,
                'avg_fisik': round(month_data['avg_fisik'] if month_data else 0, 2)
            }
        
        # Calculate quarterly summary
        if quarterly_data:
            summary = {
                'total_pagu': sum(row['pagu_anggaran'] or 0 for row in quarterly_data),
                'total_realisasi': sum(row['realisasi_keuangan'] or 0 for row in quarterly_data),
                'avg_fisik': sum(row['realisasi_fisik'] or 0 for row in quarterly_data) / len(quarterly_data) if quarterly_data else 0,
                'count': len(quarterly_data),
                'quarter': quarter,
                'quarter_name': quarter_name,
                'year': year
            }
            
            # Calculate percentage
            if summary['total_pagu'] > 0:
                summary['percentage_keuangan'] = (summary['total_realisasi'] / summary['total_pagu']) * 100
            else:
                summary['percentage_keuangan'] = 0
                
        else:
            summary = {
                'total_pagu': 0,
                'total_realisasi': 0,
                'avg_fisik': 0,
                'percentage_keuangan': 0,
                'count': 0,
                'quarter': quarter,
                'quarter_name': quarter_name,
                'year': year
            }
        
        # Get bidang breakdown (for super admin)
        bidang_breakdown = {}
        if session['role'] == 'super_admin':
            bidang_list = ['sekretariat', 'tangkap', 'budidaya', 'pengolahan']
            for bidang in bidang_list:
                bidang_params = [year, bidang] + months
                bidang_where = 'tahun = ? AND bidang = ? AND (' + ' OR '.join(['bulan = ?' for _ in months]) + ')'
                
                bidang_data = conn.execute(f'''
                    SELECT 
                        COUNT(*) as count,
                        COALESCE(SUM(pagu_anggaran), 0) as total_pagu,
                        COALESCE(SUM(realisasi_keuangan), 0) as total_realisasi,
                        COALESCE(AVG(realisasi_fisik), 0) as avg_fisik
                    FROM realisasi
                    WHERE {bidang_where}
                ''', bidang_params).fetchone()
                
                bidang_breakdown[bidang] = {
                    'name': bidang.title(),
                    'count': bidang_data['count'] if bidang_data else 0,
                    'total_pagu': bidang_data['total_pagu'] if bidang_data else 0,
                    'total_realisasi': bidang_data['total_realisasi'] if bidang_data else 0,
                    'avg_fisik': round(bidang_data['avg_fisik'] if bidang_data else 0, 2)
                }
        
    except Exception as e:
        print(f"Error loading quarterly report: {e}")
        quarterly_data = []
        monthly_breakdown = {}
        bidang_breakdown = {}
        summary = {
            'total_pagu': 0,
            'total_realisasi': 0,
            'avg_fisik': 0,
            'percentage_keuangan': 0,
            'count': 0,
            'quarter': quarter,
            'quarter_name': quarter_name,
            'year': year
        }
    
    conn.close()
    
    return render_template('quarterly_report.html',
                         quarterly_data=quarterly_data,
                         summary=summary,
                         monthly_breakdown=monthly_breakdown,
                         bidang_breakdown=bidang_breakdown,
                         months=months)

# Export Excel Routes
@app.route('/export/excel')
@login_required
def export_excel():
    """Export regular reports to Excel"""
    try:
        # Get filters from request
        filters = {
            'program_id': request.args.get('program_id'),
            'sumber_dana_id': request.args.get('sumber_dana_id'),
            'bulan': request.args.get('bulan'),
            'tahun': request.args.get('tahun', str(datetime.now().year))
        }
        
        # Remove empty filters
        filters = {k: v for k, v in filters.items() if v}
        
        # Get data
        report_data = get_report_data(filters)
        
        # Create workbook
        wb = Workbook()
        ws = wb.active
        ws.title = "Laporan Realisasi"
        
        # Header styling
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="0284C7", end_color="0284C7", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # Title
        ws.merge_cells('A1:J2')
        title_cell = ws['A1']
        title_cell.value = "LAPORAN REALISASI FISIK DAN KEUANGAN"
        title_cell.font = Font(size=16, bold=True)
        title_cell.alignment = Alignment(horizontal="center", vertical="center")
        
        # Subtitle with filters
        subtitle_row = 3
        subtitle_text = "SIRIPIKAN - Sistem Informasi Realisasi Fisik dan Keuangan"
        if session['role'] != 'super_admin':
            subtitle_text += f" - Bidang {session['bidang'].title()}"
        if filters.get('tahun'):
            subtitle_text += f" - Tahun {filters['tahun']}"
        if filters.get('bulan'):
            subtitle_text += f" - {get_month_name(int(filters['bulan']))}"
            
        ws.merge_cells(f'A{subtitle_row}:J{subtitle_row}')
        subtitle_cell = ws[f'A{subtitle_row}']
        subtitle_cell.value = subtitle_text
        subtitle_cell.font = Font(size=12)
        subtitle_cell.alignment = Alignment(horizontal="center")
        
        # Generated date
        ws.merge_cells(f'A{subtitle_row+1}:J{subtitle_row+1}')
        date_cell = ws[f'A{subtitle_row+1}']
        date_cell.value = f"Digenerate pada: {datetime.now().strftime('%d %B %Y, %H:%M WIB')}"
        date_cell.font = Font(size=10, italic=True)
        date_cell.alignment = Alignment(horizontal="center")
        
        # Headers
        header_row = subtitle_row + 3
        headers = [
            "No", "Program", "Kegiatan", "Sub Kegiatan", "Lokasi", 
            "Pagu Anggaran", "Realisasi Keuangan", "% Keuangan", 
            "Realisasi Fisik (%)", "Status"
        ]
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=header_row, column=col)
            cell.value = header
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = border
        
        # Data rows
        data_start_row = header_row + 1
        total_pagu = 0
        total_realisasi = 0
        
        for idx, row in enumerate(report_data, 1):
            ws.cell(row=data_start_row + idx - 1, column=1, value=idx)
            ws.cell(row=data_start_row + idx - 1, column=2, value=row['program_name'] or '-')
            ws.cell(row=data_start_row + idx - 1, column=3, value=row['kegiatan_name'] or '-')
            ws.cell(row=data_start_row + idx - 1, column=4, value=row['sub_kegiatan_name'] or '-')
            
            lokasi = f"{row['kecamatan'] or ''}"
            if row['desa']:
                lokasi += f", {row['desa']}"
            ws.cell(row=data_start_row + idx - 1, column=5, value=lokasi or '-')
            
            pagu = row['pagu_anggaran'] or 0
            realisasi = row['realisasi_keuangan'] or 0
            percentage = (realisasi / pagu * 100) if pagu > 0 else 0
            
            ws.cell(row=data_start_row + idx - 1, column=6, value=pagu)
            ws.cell(row=data_start_row + idx - 1, column=7, value=realisasi)
            ws.cell(row=data_start_row + idx - 1, column=8, value=f"{percentage:.1f}%")
            ws.cell(row=data_start_row + idx - 1, column=9, value=f"{row['realisasi_fisik'] or 0:.1f}%")
            ws.cell(row=data_start_row + idx - 1, column=10, value=row['status_kontrak'] or '-')
            
            total_pagu += pagu
            total_realisasi += realisasi
        
        # Total row
        if report_data:
            total_row = data_start_row + len(report_data)
            ws.cell(row=total_row, column=1, value="TOTAL")
            ws.cell(row=total_row, column=6, value=total_pagu)
            ws.cell(row=total_row, column=7, value=total_realisasi)
            ws.cell(row=total_row, column=8, value=f"{(total_realisasi/total_pagu*100) if total_pagu > 0 else 0:.1f}%")
            
            # Style total row
            for col in range(1, 11):
                cell = ws.cell(row=total_row, column=col)
                cell.font = Font(bold=True)
                cell.fill = PatternFill(start_color="F3F4F6", end_color="F3F4F6", fill_type="solid")
        
        # Apply borders to all data
        for row in range(header_row, data_start_row + len(report_data) + 1):
            for col in range(1, 11):
                ws.cell(row=row, column=col).border = border
        
        # Adjust column widths
        column_widths = [5, 25, 25, 25, 20, 15, 15, 12, 15, 12]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(col)].width = width
        
        # Create file in memory
        output = io.BytesIO()
        wb.save(output)
        output.seek(0)
        
        # Generate filename
        filename = f"Laporan_Realisasi_{filters.get('tahun', datetime.now().year)}"
        if filters.get('bulan'):
            filename += f"_{get_month_name(int(filters['bulan']))}"
        filename += f"_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        
        # Create response
        response = make_response(output.read())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
        
        log_activity(session['user_id'], 'EXPORT', 'reports', description=f'Exported Excel report: {filename}')
        
        return response
        
    except Exception as e:
        print(f"Export Excel error: {e}")
        flash(f'Error saat export Excel: {str(e)}', 'error')
        return redirect(url_for('reports'))

@app.route('/export/quarterly/excel')
@login_required
def export_quarterly_excel():
    """Export quarterly reports to Excel"""
    try:
        quarter = request.args.get('quarter', type=int) or 1
        year = request.args.get('year', datetime.now().year, type=int)
        
        # Get quarterly data
        quarter_months = {
            1: [1, 2, 3], 2: [4, 5, 6], 3: [7, 8, 9], 4: [10, 11, 12]
        }
        quarter_names = {
            1: "Triwulan I (Januari - Maret)",
            2: "Triwulan II (April - Juni)", 
            3: "Triwulan III (Juli - September)",
            4: "Triwulan IV (Oktober - Desember)"
        }
        
        months = quarter_months.get(quarter, [1, 2, 3])
        quarter_name = quarter_names.get(quarter, "Triwulan I")
        
        conn = get_db_connection()
        
        # Build query
        base_conditions = ['r.tahun = ?']
        params = [year]
        
        if session['role'] != 'super_admin':
            base_conditions.append('r.bidang = ?')
            params.append(session['bidang'])
        
        month_conditions = ' OR '.join(['r.bulan = ?' for _ in months])
        base_conditions.append(f'({month_conditions})')
        params.extend(months)
        
        where_clause = ' AND '.join(base_conditions)
        
        quarterly_data = conn.execute(f'''
            SELECT r.*, p.name as program_name, k.name as kegiatan_name, 
                   sk.name as sub_kegiatan_name, pek.name as pekerjaan_name, l.kecamatan, l.desa,
                   sd.name as sumber_dana_name, pk.name as pihak_ketiga_name
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE {where_clause}
            ORDER BY r.bulan, r.created_at
        ''', params).fetchall()
        
        conn.close()
        
        # Create workbook
        wb = Workbook()
        ws = wb.active
        ws.title = f"Laporan {quarter_name}"
        
        # Styling
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="0284C7", end_color="0284C7", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        border = Border(
            left=Side(style='thin'), right=Side(style='thin'),
            top=Side(style='thin'), bottom=Side(style='thin')
        )
        
        # Title
        ws.merge_cells('A1:K2')
        title_cell = ws['A1']
        title_cell.value = f"LAPORAN {quarter_name.upper()} {year}"
        title_cell.font = Font(size=16, bold=True)
        title_cell.alignment = Alignment(horizontal="center", vertical="center")
        
        # Subtitle
        subtitle_text = "SIRIPIKAN - Sistem Informasi Realisasi Fisik dan Keuangan"
        if session['role'] != 'super_admin':
            subtitle_text += f" - Bidang {session['bidang'].title()}"
            
        ws.merge_cells('A3:K3')
        subtitle_cell = ws['A3']
        subtitle_cell.value = subtitle_text
        subtitle_cell.font = Font(size=12)
        subtitle_cell.alignment = Alignment(horizontal="center")
        
        # Generated date
        ws.merge_cells('A4:K4')
        date_cell = ws['A4']
        date_cell.value = f"Digenerate pada: {datetime.now().strftime('%d %B %Y, %H:%M WIB')}"
        date_cell.font = Font(size=10, italic=True)
        date_cell.alignment = Alignment(horizontal="center")
        
        # Headers
        headers = [
            "No", "Bulan", "Program", "Kegiatan", "Sub Kegiatan", "Lokasi", 
            "Pagu Anggaran", "Realisasi Keuangan", "% Keuangan", 
            "Realisasi Fisik (%)", "Status"
        ]
        
        header_row = 6
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=header_row, column=col)
            cell.value = header
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = border
        
        # Data rows
        data_start_row = header_row + 1
        total_pagu = 0
        total_realisasi = 0
        month_names = ['', 'Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun',
                      'Jul', 'Agu', 'Sep', 'Okt', 'Nov', 'Des']
        
        for idx, row in enumerate(quarterly_data, 1):
            ws.cell(row=data_start_row + idx - 1, column=1, value=idx)
            ws.cell(row=data_start_row + idx - 1, column=2, value=month_names[row['bulan']] if row['bulan'] else '-')
            ws.cell(row=data_start_row + idx - 1, column=3, value=row['program_name'] or '-')
            ws.cell(row=data_start_row + idx - 1, column=4, value=row['kegiatan_name'] or '-')
            ws.cell(row=data_start_row + idx - 1, column=5, value=row['sub_kegiatan_name'] or '-')
            
            lokasi = f"{row['kecamatan'] or ''}"
            if row['desa']:
                lokasi += f", {row['desa']}"
            ws.cell(row=data_start_row + idx - 1, column=6, value=lokasi or '-')
            
            pagu = row['pagu_anggaran'] or 0
            realisasi = row['realisasi_keuangan'] or 0
            percentage = (realisasi / pagu * 100) if pagu > 0 else 0
            
            ws.cell(row=data_start_row + idx - 1, column=7, value=pagu)
            ws.cell(row=data_start_row + idx - 1, column=8, value=realisasi)
            ws.cell(row=data_start_row + idx - 1, column=9, value=f"{percentage:.1f}%")
            ws.cell(row=data_start_row + idx - 1, column=10, value=f"{row['realisasi_fisik'] or 0:.1f}%")
            ws.cell(row=data_start_row + idx - 1, column=11, value=row['status_kontrak'] or '-')
            
            total_pagu += pagu
            total_realisasi += realisasi
        
        # Total row
        if quarterly_data:
            total_row = data_start_row + len(quarterly_data)
            ws.cell(row=total_row, column=1, value="TOTAL")
            ws.cell(row=total_row, column=7, value=total_pagu)
            ws.cell(row=total_row, column=8, value=total_realisasi)
            ws.cell(row=total_row, column=9, value=f"{(total_realisasi/total_pagu*100) if total_pagu > 0 else 0:.1f}%")
            
            # Style total row
            for col in range(1, 12):
                cell = ws.cell(row=total_row, column=col)
                cell.font = Font(bold=True)
                cell.fill = PatternFill(start_color="F3F4F6", end_color="F3F4F6", fill_type="solid")
        
        # Apply borders
        for row in range(header_row, data_start_row + len(quarterly_data) + 1):
            for col in range(1, 12):
                ws.cell(row=row, column=col).border = border
        
        # Adjust column widths
        column_widths = [5, 8, 20, 20, 20, 15, 15, 15, 12, 15, 10]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(col)].width = width
        
        # Create file in memory
        output = io.BytesIO()
        wb.save(output)
        output.seek(0)
        
        # Generate filename
        filename = f"Laporan_{quarter_name.replace(' ', '_')}_{year}_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        
        # Create response
        response = make_response(output.read())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
        
        log_activity(session['user_id'], 'EXPORT', 'quarterly_reports', description=f'Exported quarterly Excel: {filename}')
        
        return response
        
    except Exception as e:
        print(f"Export quarterly Excel error: {e}")
        flash(f'Error saat export Excel: {str(e)}', 'error')
        return redirect(url_for('quarterly_report'))

# Export PDF Routes
@app.route('/export/pdf')
@login_required
def export_pdf():
    """Export regular reports to PDF"""
    try:
        # Get filters from request
        filters = {
            'program_id': request.args.get('program_id'),
            'sumber_dana_id': request.args.get('sumber_dana_id'),
            'bulan': request.args.get('bulan'),
            'tahun': request.args.get('tahun', str(datetime.now().year))
        }
        
        # Remove empty filters
        filters = {k: v for k, v in filters.items() if v}
        
        # Get data
        report_data = get_report_data(filters)
        
        # Create PDF in memory
        output = io.BytesIO()
        doc = SimpleDocTemplate(output, pagesize=A4)
        elements = []
        
        # Styles
        styles = getSampleStyleSheet()
        title_style = ParagraphStyle(
            'TitleStyle',
            parent=styles['Heading1'],
            fontSize=16,
            spaceAfter=30,
            alignment=1  # Center
        )
        
        subtitle_style = ParagraphStyle(
            'SubtitleStyle',
            parent=styles['Normal'],
            fontSize=12,
            spaceAfter=20,
            alignment=1  # Center
        )
        
        # Title
        title = "LAPORAN REALISASI FISIK DAN KEUANGAN"
        elements.append(Paragraph(title, title_style))
        
        # Subtitle
        subtitle_text = "SIRIPIKAN - Sistem Informasi Realisasi Fisik dan Keuangan"
        if session['role'] != 'super_admin':
            subtitle_text += f"<br/>Bidang {session['bidang'].title()}"
        if filters.get('tahun'):
            subtitle_text += f"<br/>Tahun {filters['tahun']}"
        if filters.get('bulan'):
            subtitle_text += f" - {get_month_name(int(filters['bulan']))}"
        
        elements.append(Paragraph(subtitle_text, subtitle_style))
        
        # Generated date
        date_text = f"Digenerate pada: {datetime.now().strftime('%d %B %Y, %H:%M WIB')}"
        elements.append(Paragraph(date_text, styles['Normal']))
        elements.append(Spacer(1, 20))
        
        # Prepare table data
        table_data = [
            ["No", "Program", "Kegiatan", "Lokasi", "Pagu", "Realisasi", "%", "Fisik", "Status"]
        ]
        
        total_pagu = 0
        total_realisasi = 0
        
        for idx, row in enumerate(report_data, 1):
            lokasi = f"{row['kecamatan'] or ''}"
            if row['desa']:
                lokasi += f", {row['desa']}"
            
            pagu = row['pagu_anggaran'] or 0
            realisasi = row['realisasi_keuangan'] or 0
            percentage = (realisasi / pagu * 100) if pagu > 0 else 0
            
            table_data.append([
                str(idx),
                (row['program_name'] or '-')[:20],
                (row['kegiatan_name'] or '-')[:20],
                lokasi[:15],
                format_currency(pagu),
                format_currency(realisasi),
                f"{percentage:.1f}%",
                f"{row['realisasi_fisik'] or 0:.1f}%",
                (row['status_kontrak'] or '-')[:8]
            ])
            
            total_pagu += pagu
            total_realisasi += realisasi
        
        # Add total row
        if report_data:
            table_data.append([
                "TOTAL", "", "", "",
                format_currency(total_pagu),
                format_currency(total_realisasi),
                f"{(total_realisasi/total_pagu*100) if total_pagu > 0 else 0:.1f}%",
                "", ""
            ])
        
        # Create table
        table = Table(table_data)
        table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#0284C7')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 10),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            ('BACKGROUND', (0, 1), (-1, -2), colors.beige),
            ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#F3F4F6')),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
        ]))
        
        elements.append(table)
        
        # Build PDF
        doc.build(elements)
        output.seek(0)
        
        # Generate filename
        filename = f"Laporan_Realisasi_{filters.get('tahun', datetime.now().year)}"
        if filters.get('bulan'):
            filename += f"_{get_month_name(int(filters['bulan']))}"
        filename += f"_{datetime.now().strftime('%Y%m%d_%H%M')}.pdf"
        
        # Create response
        response = make_response(output.read())
        response.headers['Content-Type'] = 'application/pdf'
        response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
        
        log_activity(session['user_id'], 'EXPORT', 'reports', description=f'Exported PDF report: {filename}')
        
        return response
        
    except Exception as e:
        print(f"Export PDF error: {e}")
        flash(f'Error saat export PDF: {str(e)}', 'error')
        return redirect(url_for('reports'))

@app.route('/export/quarterly/pdf')
@login_required
def export_quarterly_pdf():
    """Export quarterly reports to PDF"""
    try:
        quarter = request.args.get('quarter', type=int) or 1
        year = request.args.get('year', datetime.now().year, type=int)
        
        # Get quarterly data
        quarter_months = {
            1: [1, 2, 3], 2: [4, 5, 6], 3: [7, 8, 9], 4: [10, 11, 12]
        }
        quarter_names = {
            1: "Triwulan I (Januari - Maret)",
            2: "Triwulan II (April - Juni)", 
            3: "Triwulan III (Juli - September)",
            4: "Triwulan IV (Oktober - Desember)"
        }
        
        months = quarter_months.get(quarter, [1, 2, 3])
        quarter_name = quarter_names.get(quarter, "Triwulan I")
        
        conn = get_db_connection()
        
        # Build query
        base_conditions = ['r.tahun = ?']
        params = [year]
        
        if session['role'] != 'super_admin':
            base_conditions.append('r.bidang = ?')
            params.append(session['bidang'])
        
        month_conditions = ' OR '.join(['r.bulan = ?' for _ in months])
        base_conditions.append(f'({month_conditions})')
        params.extend(months)
        
        where_clause = ' AND '.join(base_conditions)
        
        quarterly_data = conn.execute(f'''
            SELECT r.*, p.name as program_name, k.name as kegiatan_name, 
                   sk.name as sub_kegiatan_name, pek.name as pekerjaan_name, l.kecamatan, l.desa,
                   sd.name as sumber_dana_name, pk.name as pihak_ketiga_name
            FROM realisasi r
            LEFT JOIN programs p ON r.program_id = p.id
            LEFT JOIN kegiatan k ON r.kegiatan_id = k.id
            LEFT JOIN sub_kegiatan sk ON r.sub_kegiatan_id = sk.id
            LEFT JOIN pekerjaan pek ON r.pekerjaan_id = pek.id
            LEFT JOIN lokasi l ON r.lokasi_id = l.id
            LEFT JOIN sumber_dana sd ON r.sumber_dana_id = sd.id
            LEFT JOIN pihak_ketiga pk ON r.pihak_ketiga_id = pk.id
            WHERE {where_clause}
            ORDER BY r.bulan, r.created_at
        ''', params).fetchall()
        
        conn.close()
        
        # Create PDF in memory
        output = io.BytesIO()
        doc = SimpleDocTemplate(output, pagesize=A4)
        elements = []
        
        # Styles
        styles = getSampleStyleSheet()
        title_style = ParagraphStyle(
            'TitleStyle',
            parent=styles['Heading1'],
            fontSize=16,
            spaceAfter=30,
            alignment=1  # Center
        )
        
        subtitle_style = ParagraphStyle(
            'SubtitleStyle',
            parent=styles['Normal'],
            fontSize=12,
            spaceAfter=20,
            alignment=1  # Center
        )
        
        # Title
        title = f"LAPORAN {quarter_name.upper()} {year}"
        elements.append(Paragraph(title, title_style))
        
        # Subtitle
        subtitle_text = "SIRIPIKAN - Sistem Informasi Realisasi Fisik dan Keuangan"
        if session['role'] != 'super_admin':
            subtitle_text += f"<br/>Bidang {session['bidang'].title()}"
        
        elements.append(Paragraph(subtitle_text, subtitle_style))
        
        # Generated date
        date_text = f"Digenerate pada: {datetime.now().strftime('%d %B %Y, %H:%M WIB')}"
        elements.append(Paragraph(date_text, styles['Normal']))
        elements.append(Spacer(1, 20))
        
        # Summary
        if quarterly_data:
            total_pagu = sum(row['pagu_anggaran'] or 0 for row in quarterly_data)
            total_realisasi = sum(row['realisasi_keuangan'] or 0 for row in quarterly_data)
            avg_fisik = sum(row['realisasi_fisik'] or 0 for row in quarterly_data) / len(quarterly_data)
            
            summary_text = f"""
            <b>RINGKASAN {quarter_name.upper()}</b><br/>
            Total Records: {len(quarterly_data)}<br/>
            Total Pagu: {format_currency(total_pagu)}<br/>
            Total Realisasi: {format_currency(total_realisasi)}<br/>
            Persentase Keuangan: {(total_realisasi/total_pagu*100) if total_pagu > 0 else 0:.1f}%<br/>
            Rata-rata Fisik: {avg_fisik:.1f}%
            """
            elements.append(Paragraph(summary_text, styles['Normal']))
            elements.append(Spacer(1, 20))
        
        # Prepare table data
        table_data = [
            ["No", "Bln", "Program", "Kegiatan", "Lokasi", "Pagu", "Realisasi", "%", "Fisik"]
        ]
        
        total_pagu = 0
        total_realisasi = 0
        month_names = ['', 'Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun',
                      'Jul', 'Agu', 'Sep', 'Okt', 'Nov', 'Des']
        
        for idx, row in enumerate(quarterly_data, 1):
            lokasi = f"{row['kecamatan'] or ''}"
            if row['desa']:
                lokasi += f", {row['desa']}"
            
            pagu = row['pagu_anggaran'] or 0
            realisasi = row['realisasi_keuangan'] or 0
            percentage = (realisasi / pagu * 100) if pagu > 0 else 0
            
            table_data.append([
                str(idx),
                month_names[row['bulan']] if row['bulan'] else '-',
                (row['program_name'] or '-')[:18],
                (row['kegiatan_name'] or '-')[:18],
                lokasi[:12],
                f"Rp {pagu/1000000:.1f}M" if pagu >= 1000000 else format_currency(pagu),
                f"Rp {realisasi/1000000:.1f}M" if realisasi >= 1000000 else format_currency(realisasi),
                f"{percentage:.1f}%",
                f"{row['realisasi_fisik'] or 0:.1f}%"
            ])
            
            total_pagu += pagu
            total_realisasi += realisasi
        
        # Add total row
        if quarterly_data:
            table_data.append([
                "TOTAL", "", "", "", "",
                f"Rp {total_pagu/1000000:.1f}M" if total_pagu >= 1000000 else format_currency(total_pagu),
                f"Rp {total_realisasi/1000000:.1f}M" if total_realisasi >= 1000000 else format_currency(total_realisasi),
                f"{(total_realisasi/total_pagu*100) if total_pagu > 0 else 0:.1f}%",
                ""
            ])
        
        # Create table
        table = Table(table_data)
        table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#0284C7')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 9),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            ('BACKGROUND', (0, 1), (-1, -2), colors.beige),
            ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#F3F4F6')),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('FONTSIZE', (0, 1), (-1, -1), 7),
        ]))
        
        elements.append(table)
        
        # Build PDF
        doc.build(elements)
        output.seek(0)
        
        # Generate filename
        filename = f"Laporan_{quarter_name.replace(' ', '_')}_{year}_{datetime.now().strftime('%Y%m%d_%H%M')}.pdf"
        
        # Create response
        response = make_response(output.read())
        response.headers['Content-Type'] = 'application/pdf'
        response.headers['Content-Disposition'] = f'attachment; filename="{filename}"'
        
        log_activity(session['user_id'], 'EXPORT', 'quarterly_reports', description=f'Exported quarterly PDF: {filename}')
        
        return response
        
    except Exception as e:
        print(f"Export quarterly PDF error: {e}")
        flash(f'Error saat export PDF: {str(e)}', 'error')
        return redirect(url_for('quarterly_report'))

if __name__ == '__main__':
    init_db()
    app.run(debug=True)