import pandas as pd
import xlsxwriter

# फाइल का नाम
file_name = 'FAM_JAN_SEWA_KENDRA_ERP.xlsx'

# डाटाफ्रेम बनाना (Khali template)
df = pd.DataFrame(columns=[
    'DATE', 'CUSTOMER NAME', 'MOBILE', 'PURPOSE', 'AADHAAR', 
    'TXN TYPE', 'AMOUNT', 'EARNINGS', 'TOTAL', 
    'KOTAK BANK', 'CANARA BANK', 'NSDL BANK', 'AIRTEL MITRA', 
    'PHONEPE BIZ', 'PAYTM UPI', 'PAY TYPE'
])

# Excel Writer स्टार्ट करना
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Dashboard', startrow=7, index=False)

workbook  = writer.book
worksheet = writer.sheets['Dashboard']

# --- FORMATTING STYLES ---
header_format = workbook.add_format({
    'bold': True, 'text_wrap': True, 'valign': 'vcenter', 'align': 'center', 
    'fg_color': '#2C3E50', 'font_color': 'white', 'font_size': 20, 'border': 1
})
sub_header_format = workbook.add_format({
    'bold': True, 'align': 'center', 'fg_color': '#E74C3C', 'font_color': 'white', 'font_size': 11
})
table_header_format = workbook.add_format({
    'bold': True, 'align': 'center', 'bg_color': '#F1C40F', 'border': 1
})
sidebar_format = workbook.add_format({
    'bg_color': '#ECF0F1', 'bold': True, 'align': 'center'
})
money_fmt = workbook.add_format({'num_format': '₹ #,##0.00'})

# --- 1. HEADER SECTION (Top Banner) ---
worksheet.merge_range('A1:P3', 
    "FAM JAN SEWA KENDRA PVT. LTD.\nMANJUR MARKET VILLAGE HALDONI POST KULESARA GB NAGAR UP 201306", 
    header_format)

# --- 2. DASHBOARD / SIDEBAR EFFECT (Rows 4-6) ---
# यहाँ हम Balance दिखाएंगे (Excel Formulas के साथ)
worksheet.write('C5', "TOTAL CASH IN HAND:", sub_header_format)
worksheet.write_formula('D5', '=SUM(G9:G1000)+SUM(H9:H1000)', money_fmt) # Amount + Earn

worksheet.write('F5', "TOTAL EARNINGS:", sub_header_format)
worksheet.write_formula('G5', '=SUM(H9:H1000)', money_fmt)

worksheet.write('I5', "AIRTEL BALANCE:", sub_header_format)
worksheet.write_formula('J5', '=SUM(M9:M1000)', money_fmt)

# --- 3. TABLE HEADERS ---
# हेडर रो की हाइट बढ़ाना
worksheet.set_row(7, 30, table_header_format)

# कॉलम की चौड़ाई सेट करना (Layout)
worksheet.set_column('A:A', 12, sidebar_format) # DATE (Sidebar look)
worksheet.set_column('B:B', 20) # Name
worksheet.set_column('C:C', 15) # Mobile
worksheet.set_column('D:D', 25) # Purpose
worksheet.set_column('E:E', 15) # Aadhaar
worksheet.set_column('F:F', 15) # Type
worksheet.set_column('G:I', 12, money_fmt) # Money Columns
worksheet.set_column('J:O', 12, money_fmt) # Bank Columns
worksheet.set_column('P:P', 12) # Pay Type

# --- 4. DROPDOWNS & VALIDATION ---
# Transaction Type Dropdown
worksheet.data_validation('F9:F1000', {'validate': 'list',
                                       'source': ['Credit (+)', 'Debit (-)']})

# Pay Type Dropdown
worksheet.data_validation('P9:P1000', {'validate': 'list',
                                       'source': ['CASH', 'ONLINE']})

# --- 5. FREEZE PANES (Sidebar/Header fix) ---
# ऊपर की 8 लाइनें और पहला कॉलम (A) फिक्स रहेगा, ताकि Sidebar जैसा लगे
worksheet.freeze_panes(8, 1)

print(f"File '{file_name}' created successfully with Dashboard Layout.")
writer.close()