from flask import Flask, render_template, request, redirect, url_for, session, jsonify
from db_handler import authenticate_user, read_sheet, append_row, get_device_by_imei, update_cell, get_stats
from datetime import datetime, timedelta
import os

app = Flask(__name__)
app.secret_key = 'orbit_super_secret_key' # Change in production

@app.route('/')
def index():
    if 'user' not in session:
        return redirect(url_for('login'))
    
    stats = get_stats()
    stats['today'] = datetime.now().strftime('%A, %d %B %Y')
    
    from db_handler import get_stock_matrix
    stock_matrix = get_stock_matrix()
    
    return render_template('dashboard.html', stats=stats, matrix=stock_matrix, page='dashboard')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        user = authenticate_user(username, password)
        if user:
            session['user'] = user
            return redirect(url_for('index'))
        else:
            return render_template('login.html', error="Invalid Credentials")
            
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.pop('user', None)
    return redirect(url_for('login'))

@app.route('/inventory', methods=['GET'])
def inventory():
    if 'user' not in session: return redirect(url_for('login'))
    
    # Use the new enriched function
    from db_handler import get_enriched_inventory, read_sheet
    devices = get_enriched_inventory()
    
    # Get installers list for the transfer dropdown
    settings_list = read_sheet('Settings')
    settings_dict = {item['Key']: item['Value'] for item in settings_list}
    
    raw_installers = settings_dict.get('Installers', '')
    installer_names = [name.strip() for name in raw_installers.split(',') if name.strip()]
    
    raw_models = settings_dict.get('Device Models', '')
    model_names = [name.strip() for name in raw_models.split(',') if name.strip()]
    
    raw_sales = settings_dict.get('Sales Persons', '')
    sales_persons = [name.strip() for name in raw_sales.split(',') if name.strip()]
    
    raw_purchasers = settings_dict.get('Purchasers', 'ITR, Maruti, Orbit, Other')
    purchasers = [name.strip() for name in raw_purchasers.split(',') if name.strip()]
    
    stock_devices = [d for d in devices if d.get('Status') == 'Stock']
    
    return render_template('inventory.html', 
                         devices=devices, 
                         stock_devices=stock_devices,
                         installers=installer_names, 
                         sales_persons=sales_persons,
                         models=model_names, 
                         purchasers=purchasers,
                         page='inventory')

@app.route('/api/add_device', methods=['POST'])
def add_device():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    imei = data.get('imei')
    
    if get_device_by_imei(imei):
        return jsonify({'error': 'Duplicate IMEI'}), 400
        
    holder = data.get('holder')
    if not holder or str(holder).strip() == '':
        holder = 'Office'

    new_device = {
        'IMEI': imei,
        'Model': data.get('model'),
        'Status': 'Stock',
        'Added Date': datetime.now().strftime('%Y-%m-%d'),
        'Holder': holder,
        'Purchased From': data.get('purchased_from')
    }
    
    if append_row('Devices', new_device):
        return jsonify({'success': True})
    return jsonify({'error': 'Database Error'}), 500

@app.route('/api/batch_add_devices', methods=['POST'])
def batch_add_devices_api():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    imeis = data.get('imeis', [])
    model = data.get('model')
    purchased_from = data.get('purchased_from')
    holder = data.get('holder')
    
    if not holder or str(holder).strip() == '':
        holder = 'Office'
        
    if not imeis:
        return jsonify({'error': 'No IMEIs provided'}), 400
        
    devices_to_add = []
    current_date = datetime.now().strftime('%Y-%m-%d')
    
    for imei in imeis:
        devices_to_add.append({
            'IMEI': imei,
            'Model': model,
            'Status': 'Stock',
            'Added Date': current_date,
            'Holder': holder,
            'Purchased From': purchased_from
        })
        
    from db_handler import batch_add_devices
    success, added, skipped = batch_add_devices(devices_to_add)
    
    if success:
        return jsonify({'success': True, 'added': added, 'skipped': skipped})
    return jsonify({'error': 'Database Error'}), 500

@app.route('/api/delete_device', methods=['POST'])
def delete_device_api():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    imei = data.get('imei')
    password = data.get('password')
    
    # Password verification
    if str(password) != '29041983':
        return jsonify({'error': 'Invalid Password'}), 403
        
    from db_handler import delete_device
    if delete_device(imei):
        return jsonify({'success': True})
    
    return jsonify({'error': 'Delete Failed'}), 500

@app.route('/api/update_device', methods=['POST'])
def edit_device():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    original_imei = data.get('original_imei')
    new_imei = data.get('new_imei')
    model = data.get('model')

    holder = data.get('holder')
    purchased_from = data.get('purchased_from')
    
    from db_handler import update_device
    if update_device(original_imei, new_imei=new_imei, model=model, holder=holder, purchased_from=purchased_from):
        return jsonify({'success': True})
    
    return jsonify({'error': 'Update Failed'}), 500



@app.route('/api/sell_device', methods=['POST'])
def sell_device():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    imei = data.get('imei')
    
    # Check if device is available
    device = get_device_by_imei(imei)
    if not device or device['Status'] != 'Stock':
        return jsonify({'error': 'Device not in stock or not found'}), 400

    # Calculate Warranty
    sale_date = datetime.now()
    warranty_months = 12 # Default
    warranty_end = sale_date + timedelta(days=365)
    
    sale_record = {
        'IMEI': imei,
        'Vehicle No': data.get('vehicle_no', '').upper(),
        'SIM No': data.get('sim_no'),
        'Sale Date': sale_date.strftime('%Y-%m-%d'),
        'Warranty Start': sale_date.strftime('%Y-%m-%d'),
        'Warranty End': warranty_end.strftime('%Y-%m-%d'),
        # Payment Status removed
        'Docket No': data.get('docket_no'),
        'Sales Person': data.get('sales_person'),
        'Installer': data.get('installer')
    }
    
    # Record Sale
    if append_row('Sales', sale_record):
        # Update Device Status
        update_cell('Devices', 'IMEI', imei, 'Status', 'Sold')
        return jsonify({'success': True})
        
    return jsonify({'error': 'Database Error'}), 500

@app.route('/personnel')
def personnel():
    if 'user' not in session: return redirect(url_for('login'))
    people = read_sheet('Users')
    return render_template('personnel.html', people=people, page='personnel')

@app.route('/settings')
def settings():
    if 'user' not in session: return redirect(url_for('login'))
    settings_list = read_sheet('Settings')
    # Convert list of dicts to a single dict for easy access in template
    settings_dict = {item['Key']: item['Value'] for item in settings_list}
    return render_template('settings.html', settings=settings_dict, page='settings')

@app.route('/add_person', methods=['POST'])
def add_person():
    if 'user' not in session: return redirect(url_for('login'))
    
    new_user = {
        'Name': request.form.get('name'),
        'Username': request.form.get('username'),
        'Password': request.form.get('password'),
        'Role': request.form.get('role')
    }
    
    append_row('Users', new_user)
    return redirect(url_for('personnel'))

@app.route('/update_settings', methods=['POST'])
def update_settings():
    if 'user' not in session: return redirect(url_for('login'))
    
    for key, value in request.form.items():
        update_cell('Settings', 'Key', key, 'Value', value)
        
    return redirect(url_for('settings'))


@app.route('/installers')
def installers():
    if 'user' not in session: return redirect(url_for('login'))
    
    settings_list = read_sheet('Settings')
    settings_dict = {item['Key']: item['Value'] for item in settings_list}
    
    # Parse installers list
    raw_installers = settings_dict.get('Installers', '')
    installer_names = [name.strip() for name in raw_installers.split(',') if name.strip()]
    
    # Get stats for the first installer selected or default
    selected_installer = request.args.get('name', installer_names[0] if installer_names else None)
    
    stats = {}
    stock_with_installer = []
    
    if selected_installer:
        from db_handler import get_installer_stats, get_enriched_inventory, get_replacements
        stats = get_installer_stats(selected_installer)
        
        # Filter inventory for this installer
        all_inventory = get_enriched_inventory()
        stock_with_installer = [d for d in all_inventory if d.get('Holder') == selected_installer and d.get('Status') == 'Stock']
        
        # Get Replacements done by this installer
        all_replacements = get_replacements()
        installer_replacements = [r for r in all_replacements if r.get('Installed By') == selected_installer]
        # Sort by Date descending
        installer_replacements.sort(key=lambda x: x.get('Transferred Date', '1900-01-01'), reverse=True)

    return render_template('installers.html', 
                         installers=installer_names, 
                         selected_installer=selected_installer, 
                         stats=stats,
                         stock=stock_with_installer,
                         replacements=installer_replacements if selected_installer else [],
                         page='installers')

@app.route('/api/transfer_stock', methods=['POST'])
def transfer_stock_api():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    imeis = data.get('imeis', []) # List of IMEIs
    installer = data.get('installer')
    
    date = data.get('date')
    
    from db_handler import transfer_stock
    success, count = transfer_stock(imeis, installer, date=date)
    
    if success:
        return jsonify({'success': True, 'count': count})
    return jsonify({'error': 'Transfer Failed'}), 500

@app.route('/api/export_inventory', methods=['GET'])
def export_inventory():
    if 'user' not in session: return redirect(url_for('login'))
    
    from db_handler import get_enriched_inventory
    import pandas as pd
    from io import BytesIO
    from flask import send_file
    
    devices = get_enriched_inventory()
    
    # Ensure we always return an Excel file, even if empty
    if not devices:
        df = pd.DataFrame(columns=['IMEI', 'Model', 'Status', 'Holder', 'Added Date', 'Purchased From'])
    else:
        df = pd.DataFrame(devices)
        # Reorder/filter columns if needed
        # Ensure dates are strings or datetime objects
    
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Inventory_Export')
        
    output.seek(0)
    
    timestamp = datetime.now().strftime('%Y%m%d_%H%M')
    filename = f"Inventory_Export_{timestamp}.xlsx"
    return send_file(output, download_name=filename, as_attachment=True, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

@app.route('/api/export_installer_summary', methods=['GET'])
def export_installer_summary():
    if 'user' not in session: return redirect(url_for('login'))
    
    installer_name = request.args.get('installer')
    if not installer_name:
        return "Installer name required", 400
        
    from db_handler import get_enriched_inventory
    import pandas as pd
    from io import BytesIO
    from flask import send_file
    
    # Get all inventory and filter
    all_devices = get_enriched_inventory()
    
    # Filter for devices currently formatted to this installer
    # The requirement is "total data of the device transfer to installer"
    # This usually means current stock. If they mean historical stock, we'd need a different tracking mechanism.
    # Assuming Current Stock for now based on "next to the installer" context (Management).
    
    # Filter: Holder == installer_name
    installer_stock = [d for d in all_devices if d.get('Holder') == installer_name]
    
    data_for_export = []
    for d in installer_stock:
        data_for_export.append({
            'IMEI': d.get('IMEI'),
            'Model': d.get('Model'),
            'Added Date (Inventory)': d.get('Added Date', ''), # Added Date
            'Transferred Date': d.get('Stock Received Date', ''), # Transferred/Received Date
            'Purchased From': d.get('Purchased From', ''),
            'Status': d.get('Status')
        })
        
    df = pd.DataFrame(data_for_export)
    if df.empty:
        df = pd.DataFrame(columns=['IMEI', 'Model', 'Added Date (Inventory)', 'Transferred Date', 'Purchased From', 'Status'])
        
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name=f'{installer_name}_Summary')
        
    output.seek(0)
    
    timestamp = datetime.now().strftime('%Y%m%d_%H%M')
    filename = f"{installer_name}_Stock_Summary_{timestamp}.xlsx"
    return send_file(output, download_name=filename, as_attachment=True, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

@app.route('/api/get_stock_by_holder', methods=['GET'])
def get_stock_by_holder():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    holder = request.args.get('holder')
    if not holder:
        return jsonify({'error': 'Holder parameter required'}), 400
        
    from db_handler import get_enriched_inventory
    all_inventory = get_enriched_inventory()
    
    # Filter for devices held by this holder that are in Stock
    # Note: 'Office' is the default if Holder is empty/None
    if holder == 'Office':
        stock = [d for d in all_inventory if (d.get('Holder') == 'Office' or not d.get('Holder')) and d.get('Status') == 'Stock']
    else:
        stock = [d for d in all_inventory if d.get('Holder') == holder and d.get('Status') == 'Stock']
        
    return jsonify({'stock': stock})

@app.route('/api/import_inventory', methods=['POST'])
def import_inventory():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    if 'file' not in request.files:
        return jsonify({'error': 'No file part'}), 400
        
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400
        
    if file and file.filename.endswith('.xlsx'):
        from db_handler import import_devices_from_excel
        # Save temporarily
        temp_path = os.path.join('static', 'temp_import.xlsx')
        file.save(temp_path)
        
        success, msg = import_devices_from_excel(temp_path)
        
        # Cleanup
        if os.path.exists(temp_path):
            os.remove(temp_path)
            
        if success:
            return jsonify({'success': True, 'message': msg})
        else:
            return jsonify({'error': msg}), 400
            
    return jsonify({'error': 'Invalid file format. Please upload .xlsx'}), 400

@app.route('/summary_report')
def summary_report():
    if 'user' not in session: return redirect(url_for('login'))
    
    from db_handler import get_enriched_inventory, read_sheet, get_replacements
    
    devices = get_enriched_inventory()
    
    # Filter for devices that are NOT held by Office (meaning they were transferred)
    # We also verify filters: check if Holder is in our installers list or just not Office
    transferred_devices = [d for d in devices if d.get('Holder') and d.get('Holder') != 'Office']
    
    # Sort by Transferred Date descending (if available)
    # Note: Stock Received Date might be empty for old data
    def get_date(d):
        date_str = d.get('Stock Received Date')
        if not date_str or str(date_str).strip() == '':
            return '1900-01-01' # Oldest
        return date_str
        
    transferred_devices.sort(key=get_date, reverse=True)
    
    total_transferred = len(transferred_devices)
    active_installers = len(set(d.get('Holder') for d in transferred_devices))
    latest_transfer_date = get_date(transferred_devices[0]) if transferred_devices else '-'
    if latest_transfer_date == '1900-01-01': latest_transfer_date = '-'
    
    replacements = get_replacements()
    # Sort replacements by Transferred Date (index 4) descending
    def get_rep_date(r):
        return r.get('Transferred Date', '1900-01-01')
    replacements.sort(key=get_rep_date, reverse=True)

    return render_template('summary_report.html', 
                         page='summary_report',
                         total_transferred=total_transferred,
                         active_installers=active_installers,
                         latest_transfer_date=latest_transfer_date,
                         recent_devices=transferred_devices[:10],
                         recent_replacements=replacements[:20]) # Show last 20 replacements

@app.route('/api/export_global_summary', methods=['GET'])
def export_global_summary():
    if 'user' not in session: return redirect(url_for('login'))
    
    from db_handler import get_enriched_inventory
    import pandas as pd
    from io import BytesIO
    from flask import send_file
    
    devices = get_enriched_inventory()
    
    # Filter: devices with installers (Holder != Office)
    transferred_devices = [d for d in devices if d.get('Holder') and d.get('Holder') != 'Office']
    
    data_for_export = []
    for d in transferred_devices:
        data_for_export.append({
            'IMEI': d.get('IMEI'),
            'Model': d.get('Model'),
            'Holder (Installer)': d.get('Holder'),
            'Added Date (Inventory)': d.get('Added Date', ''),
            'Transferred Date': d.get('Stock Received Date', ''),
            'Purchased From': d.get('Purchased From', ''),
            'Status': d.get('Status')
        })
        
    df = pd.DataFrame(data_for_export)
    
    if df.empty:
        df = pd.DataFrame(columns=['IMEI', 'Model', 'Holder (Installer)', 'Added Date (Inventory)', 'Transferred Date', 'Purchased From', 'Status'])
        
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Global_Installer_Summary')
        
    output.seek(0)
    
    timestamp = datetime.now().strftime('%Y%m%d_%H%M')
    filename = f"Global_Installer_Summary_{timestamp}.xlsx"
    return send_file(output, download_name=filename, as_attachment=True, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')


@app.route('/api/replace_device', methods=['POST'])
def replace_device_api():
    if 'user' not in session: return jsonify({'error': 'Unauthorized'}), 401
    
    data = request.json
    old_imei = data.get('old_imei')
    new_imei = data.get('new_imei')
    vehicle_no = data.get('vehicle_no', '').upper()
    installer = data.get('installer')
    date = data.get('date')
    price = data.get('price')
    
    if not all([old_imei, new_imei, vehicle_no, installer]):
        return jsonify({'error': 'Missing required fields'}), 400
        
    from db_handler import replace_device
    success, msg = replace_device(old_imei, new_imei, vehicle_no, installer, date, price)
    
    if success:
        return jsonify({'success': True})
    return jsonify({'error': msg}), 400

@app.route('/api/export_replacement_summary', methods=['GET'])
def export_replacement_summary():
    if 'user' not in session: return redirect(url_for('login'))
    
    from db_handler import get_replacements
    import pandas as pd
    from io import BytesIO
    from flask import send_file
    
    replacements = get_replacements()
    # Columns: Old IMEI, New IMEI, Vehicle No, Installed By, Transferred Date, Added New Device Date
    # Sort by Date
    replacements.sort(key=lambda x: x.get('Transferred Date', '1900-01-01'), reverse=True)
    
    df = pd.DataFrame(replacements)
    
    # Filter/Reorder columns if they exist
    cols = ['Old IMEI', 'New IMEI', 'Vehicle No', 'Installed By', 'Transferred Date', 'Added New Device Date', 'Price']
    # Only keep columns that are actually in the data (to avoid errors if empty)
    final_cols = [c for c in cols if c in df.columns]
    
    # If dataframe is empty or missing columns, force create them
    if df.empty:
        df = pd.DataFrame(columns=cols)
    else:
        # If any column is missing from data but we want it (e.g. Price might be missing in old data), add it
        for c in cols:
            if c not in df.columns:
                df[c] = ''
        df = df[cols]
        
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Replacements_Summary')
        
    output.seek(0)
    
    timestamp = datetime.now().strftime('%Y%m%d_%H%M')
    filename = f"Replacement_Summary_{timestamp}.xlsx"
    return send_file(output, download_name=filename, as_attachment=True, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

if __name__ == '__main__':
    app.run(debug=True, port=5000)