import pandas as pd
import xlsxwriter
from prediction_engine import TRAIN_TOWNS, TRAIN_PROP_TYPES

def generate_excel_template(filename="template_input.xlsx"):
    """
    Membuat template Excel dengan Validasi Data (Dropdown) untuk kolom 'Town' dan 'Property_Residential'.
    Memudahkan user agar tidak typo saat input data manual via Excel.
    """
    # Buat Pandas Excel writer menggunakan engine XlsxWriter
    writer = pd.ExcelWriter(filename, engine='xlsxwriter')

    # Buat dataframe dengan header saja
    df = pd.DataFrame(columns=['Town', 'Property_Residential', 'List Year', 'Assessed Value'])
    df.to_excel(writer, sheet_name='Sheet1', index=False)

    # Ambil objek workbook dan worksheet dari xlsxwriter
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']

    # --- KONFIGURASI DROPDOWN ---
    # List validasi Excel tidak boleh lebih dari 255 karakter jika diketik langsung.
    # Karena daftar kota sangat panjang, kita simpan di sheet tersembunyi ('Lists')
    # lalu kita referensikan range-nya.
    
    # Tambahkan sheet tersembunyi untuk referensi list
    worksheet_lists = workbook.add_worksheet('Lists')
    worksheet_lists.hide()
    
    # Tulis Daftar Kota (Towns) ke sheet tersembunyi
    worksheet_lists.write(0, 0, "Towns")
    for i, town in enumerate(sorted(TRAIN_TOWNS)):
        worksheet_lists.write(i + 1, 0, town)
        
    # Tulis Daftar Tipe Properti ke sheet tersembunyi
    worksheet_lists.write(0, 1, "Property Types")
    for i, prop in enumerate(sorted(TRAIN_PROP_TYPES)):
        worksheet_lists.write(i + 1, 1, prop)

    # Hitung range cell (Excel formula style: Sheet!A1:A100)
    town_len = len(TRAIN_TOWNS)
    prop_len = len(TRAIN_PROP_TYPES)
    
    town_range = f'=Lists!$A$2:$A${town_len+1}'
    prop_range = f'=Lists!$B$2:$B${prop_len+1}'

    # Terapkan Validasi Data ke Kolom 'Town' (Kolom A -> baris 2 s/d 1000)
    worksheet.data_validation('A2:A1000', {
        'validate': 'list',
        'source': town_range,
        'input_title': 'Select Town',
        'input_message': 'Please select a town from the list.',
        'error_title': 'Invalid Town',
        'error_message': 'Please select a valid town from the dropdown list.'
    })

    # Terapkan Validasi Data ke Kolom 'Property_Residential' (Kolom B)
    worksheet.data_validation('B2:B1000', {
        'validate': 'list',
        'source': prop_range,
        'input_title': 'Select Property Type',
        'input_message': 'Please select a property type from the list.'
    })
    
    # Validasi Tahun (Kolom C): Integer antara 2000 - 2030
    worksheet.data_validation('C2:C1000', {
        'validate': 'integer',
        'criteria': 'between',
        'minimum': 2000,
        'maximum': 2030,
        'input_title': 'Enter Year',
        'input_message': 'Enter a year between 2000 and 2030.'
    })

    # Validasi Assessed Value (Kolom D): Desimal > 0
    worksheet.data_validation('D2:D1000', {
        'validate': 'decimal',
        'criteria': '>',
        'value': 0,
        'input_title': 'Assessed Value',
        'input_message': 'Enter a value greater than 0.'
    })

    # Lebarkan kolom agar header terbaca jelas
    worksheet.set_column('A:A', 20)
    worksheet.set_column('B:B', 20)
    worksheet.set_column('C:C', 10)
    worksheet.set_column('D:D', 15)

    writer.close()
    return filename