import pandas as pd
from openpyxl import load_workbook
import os
from datetime import datetime

DB_FILE = 'database.xlsx'

def read_sheet(sheet_name):
    """Reads a sheet from the excel file and returns a list of dictionaries."""
    try:
        df = pd.read_excel(DB_FILE, sheet_name=sheet_name, engine='openpyxl')
        # Convert all standard pandas Timestamp to string to avoid JSON serialization errors
        for col in df.columns:
            if pd.api.types.is_datetime64_any_dtype(df[col]):
                df[col] = df[col].astype(str).replace('NaT', '')
        # Also replace NaN with empty string/None for cleaner JSON
        df = df.fillna('')
        return df.to_dict(orient='records')
    except Exception as e:
        print(f"Error reading {sheet_name}: {e}")
        return []

def append_row(sheet_name, row_data):
    """Appends a single row (dict) to the specified sheet."""
    try:
        # Load workbook and sheet
        wb = load_workbook(DB_FILE)
        if sheet_name not in wb.sheetnames:
            print(f"Sheet {sheet_name} not found.")
            return False
        
        ws = wb[sheet_name]
        
        # Prepare row values based on header order
        headers = [cell.value for cell in ws[1]]
        values = [row_data.get(h, '') for h in headers]
        
        ws.append(values)
        wb.save(DB_FILE)
        return True
    except Exception as e:
        print(f"Error appending to {sheet_name}: {e}")
        return False

def batch_add_devices(devices_data):
    """Appends multiple devices to the Devices sheet. Checks for duplicates."""
    try:
        wb = load_workbook(DB_FILE)
        ws = wb['Devices']
        
        # Load existing IMEIs to check duplicates
        # Reading existing data without saving might be slow if large, but safer for consistency
        existing_imeis = set()
        imei_col_idx = None
        
        headers = [cell.value for cell in ws[1]]
        try:
            imei_col_idx = headers.index('IMEI')
        except ValueError:
            return False, 0, 0 # Error
            
        for row in ws.iter_rows(min_row=2, values_only=True):
            if row[imei_col_idx]:
                 existing_imeis.add(str(row[imei_col_idx]))
                 
        added_count = 0
        skipped_count = 0
        
        for device in devices_data:
            imei = str(device.get('IMEI'))
            if imei in existing_imeis:
                skipped_count += 1
                continue
                
            values = [device.get(h, '') for h in headers]
            ws.append(values)
            existing_imeis.add(imei)
            added_count += 1
            
        wb.save(DB_FILE)
        return True, added_count, skipped_count
        
    except Exception as e:
        print(f"Error in batch add: {e}")
        return False, 0, 0

def update_cell(sheet_name, unique_col, unique_val, target_col, new_val):
    """Updates a cell value based on a unique key."""
    try:
        wb = load_workbook(DB_FILE)
        ws = wb[sheet_name]
        
        headers = [cell.value for cell in ws[1]]
        try:
            key_idx = headers.index(unique_col)
            target_idx = headers.index(target_col)
        except ValueError:
            print("Column not found")
            return False

        for row in ws.iter_rows(min_row=2):
            if str(row[key_idx].value) == str(unique_val):
                row[target_idx].value = new_val
                wb.save(DB_FILE)
                return True
        return False
    except Exception as e:
        print(f"Error updating {sheet_name}: {e}")
        return False

def update_device(original_imei, new_imei=None, model=None, status=None, holder=None, purchased_from=None):
    """Updates device details. Can also update IMEI and Holder."""
    try:
        wb = load_workbook(DB_FILE)
        ws = wb['Devices']
        
        headers = [cell.value for cell in ws[1]]
        imei_idx = headers.index('IMEI')
        model_idx = headers.index('Model')
        # Price removed
        # price_idx = headers.index('Price') 
        status_idx = headers.index('Status')
        holder_idx = headers.index('Holder')
        purchased_from_idx = headers.index('Purchased From')

        for row in ws.iter_rows(min_row=2):
            if str(row[imei_idx].value) == str(original_imei):
                if new_imei: row[imei_idx].value = new_imei
                if model: row[model_idx].value = model
                # if price: row[price_idx].value = price
                if status: row[status_idx].value = status
                if holder: row[holder_idx].value = holder
                if purchased_from: row[purchased_from_idx].value = purchased_from
                wb.save(DB_FILE)
                return True
        return False
    except Exception as e:
        print(f"Error updating device {original_imei}: {e}")
        return False

def get_device_by_imei(imei):
    devices = read_sheet('Devices')
    for d in devices:
        if str(d.get('IMEI')) == str(imei):
            return d
    return None

def delete_device(imei):
    """Deletes a device by IMEI. Also removes any associated Sales records."""
    try:
        wb = load_workbook(DB_FILE)
        
        # 1. Delete from Devices
        ws_dev = wb['Devices']
        headers_dev = [cell.value for cell in ws_dev[1]]
        try:
            imei_idx_dev = headers_dev.index('IMEI')
        except ValueError:
            return False
            
        row_to_delete_dev = None
        for i, row in enumerate(ws_dev.iter_rows(min_row=2), start=2):
            if str(row[imei_idx_dev].value) == str(imei):
                row_to_delete_dev = i
                break
                
        # 2. Delete from Sales (if exists)
        # Sales can have multiple entries for same IMEI if returned/resold? Usually unique.
        # Iterate backwards to safely delete multiple rows if needed (though unlikely)
        sales_deleted = False
        if 'Sales' in wb.sheetnames:
            ws_sales = wb['Sales']
            headers_sales = [cell.value for cell in ws_sales[1]]
            try:
                imei_idx_sales = headers_sales.index('IMEI')
                rows_to_delete_sales = []
                for i, row in enumerate(ws_sales.iter_rows(min_row=2), start=2):
                    if str(row[imei_idx_sales].value) == str(imei):
                        rows_to_delete_sales.append(i)
                
                # Delete strictly matching rows, starting from bottom to avoid shifting issues
                for r in sorted(rows_to_delete_sales, reverse=True):
                    ws_sales.delete_rows(r)
                    sales_deleted = True
            except ValueError:
                pass # IMEI col missing in Sales? Matches not found.

        if row_to_delete_dev:
            ws_dev.delete_rows(row_to_delete_dev)
            wb.save(DB_FILE)
            return True
            
        # If we only found it in Sales but not Devices (edge case?), save anyway
        if sales_deleted:
             wb.save(DB_FILE)
             return True
             
        return False
    except Exception as e:
        print(f"Error deleting device {imei}: {e}")
        return False


def authenticate_user(username, password):
    users = read_sheet('Users')
    for u in users:
        if str(u.get('Username')) == username and str(u.get('Password')) == password:
            return u
    return None

def get_stats():
    devices = read_sheet('Devices')
    sales = read_sheet('Sales')
    
    # Create a set of valid IMEIs from the Devices sheet
    valid_imeis = set(str(d.get('IMEI')) for d in devices)
    
    total_stock = sum(1 for d in devices if d.get('Status') == 'Stock')
    
    # Only count sales for devices that still exist in the database
    valid_sales = [s for s in sales if str(s.get('IMEI')) in valid_imeis]
    total_sold = len(valid_sales)
    
    # Reverting to total inventory count per user feedback (includes Stock, Sold, Installed held in DB)
    total_devices = len(devices)
    
    return {
        'stock': total_stock,
        'sold': total_sold,
        'total': total_devices
    }

def get_enriched_inventory():
    """Returns inventory joined with sales data."""
    try:
        devices = pd.read_excel(DB_FILE, sheet_name='Devices', engine='openpyxl')
        sales = pd.read_excel(DB_FILE, sheet_name='Sales', engine='openpyxl')
        
        # Convert dates to strings for JSON safety
        for df in [devices, sales]:
            for col in df.columns:
                if pd.api.types.is_datetime64_any_dtype(df[col]):
                    df[col] = df[col].astype(str).replace('NaT', '')
            df.fillna('', inplace=True)
            
        # Force IMEI to string to ensure merge works (handle int vs object mismatch)
        if 'IMEI' in devices.columns:
            devices['IMEI'] = devices['IMEI'].astype(str).str.strip()
        if 'IMEI' in sales.columns:
            sales['IMEI'] = sales['IMEI'].astype(str).str.strip()

        # Merge
        # Added 'SIM No', 'Sales Person', 'Installer' to the merge
        merged = pd.merge(devices, sales[['IMEI', 'Sale Date', 'Warranty End', 'SIM No', 'Sales Person', 'Installer']], on='IMEI', how='left')
        merged.fillna('', inplace=True)
        
        return merged.to_dict(orient='records')
    except Exception as e:
        print(f"Error getting enriched inventory: {e}")
        return []

def transfer_stock(imeis, installer_name, date=None):
    """Updates the Holder for a list of IMEIs."""
    try:
        if not date:
            date = datetime.now().strftime('%Y-%m-%d')
            
        wb = load_workbook(DB_FILE)
        ws = wb['Devices']
        headers = [cell.value for cell in ws[1]]
        imei_idx = headers.index('IMEI')
        holder_idx = headers.index('Holder')
        
        # Check if Stock Received Date exists, if not add it
        try:
            date_idx = headers.index('Stock Received Date')
        except ValueError:
            # If header is missing (though we added it via script), handle gracefully or add it
            ws.cell(row=1, column=len(headers)+1, value='Stock Received Date')
            date_idx = len(headers)
        
        updated_count = 0
        for row in ws.iter_rows(min_row=2):
            if str(row[imei_idx].value) in imeis:
                row[holder_idx].value = installer_name
                row[date_idx].value = date
                updated_count += 1
        
        wb.save(DB_FILE)
        return True, updated_count
    except Exception as e:
        print(f"Error transferring stock: {e}")
        return False, 0

def get_installer_stats(installer_name):
    """Get stats for a specific installer."""
    try:
        devices = pd.read_excel(DB_FILE, sheet_name='Devices', engine='openpyxl')
        sales = pd.read_excel(DB_FILE, sheet_name='Sales', engine='openpyxl')
        
        # Calculate Stock Held
        stock_held = len(devices[(devices['Holder'] == installer_name) & (devices['Status'] == 'Stock')])
        
        # Calculate Sales & Revenue
        installer_sales = sales[sales['Installer'] == installer_name]
        sold_count = len(installer_sales)
        
        # Payment Status removed
        
        # Revenue removed

        return {
            'stock_held': stock_held,
            'sold_count': sold_count
        }
    except Exception as e:
        print(f"Error getting installer stats: {e}")
        return {'stock_held': 0, 'sold_count': 0}

def get_stock_matrix():
    """Returns data for the stock matrix table."""
    try:
        devices = pd.read_excel(DB_FILE, sheet_name='Devices', engine='openpyxl')
        
        # Filter only 'Stock' status devices
        stock_devices = devices[devices['Status'] == 'Stock'].copy()
        
        # Ensure Holder is set (default to Office)
        stock_devices['Holder'] = stock_devices['Holder'].fillna('Office')
        # Also handle empty strings if any
        stock_devices.loc[stock_devices['Holder'].astype(str).str.strip() == '', 'Holder'] = 'Office'
        
        # Get all unique models (from Settings AND Stock)
        settings_list = read_sheet('Settings')
        settings_dict = {item['Key']: item['Value'] for item in settings_list}
        
        raw_models = settings_dict.get('Device Models', '')
        configured_models = [m.strip() for m in raw_models.split(',') if m.strip()]
        
        # Use ALL devices to find models, so if something is sold out it still shows up
        db_models = list(set(devices['Model'].dropna().unique()))
        
        # Combine distinct models
        candidate_models = list(set(configured_models + db_models))
        
        # 1. Total Counts per Model
        # We need to count first to know what to filter
        all_counts = {model: len(stock_devices[stock_devices['Model'] == model]) for model in candidate_models}
        
        # Filter: Keep model if it is in Settings OR has > 0 stock
        final_models = []
        for m in sorted(candidate_models):
            if m in configured_models or all_counts.get(m, 0) > 0:
                final_models.append(m)
                
        # 2. Office Counts
        office_stock = stock_devices[stock_devices['Holder'] == 'Office']
        office_counts = {model: len(office_stock[office_stock['Model'] == model]) for model in final_models}
        office_total = len(office_stock)
        
        # 3. Installer Counts
        matrix_rows = []
        
        # Get ALL installers from Settings
        settings_list = read_sheet('Settings')
        settings_dict = {item['Key']: item['Value'] for item in settings_list}
        raw_installers = settings_dict.get('Installers', '')
        configured_installers = [name.strip() for name in raw_installers.split(',') if name.strip()]
        
        # Also include any holders currently in DB
        db_holders = list(set(stock_devices['Holder'].dropna().unique()))
        
        # Combine and deduplicate, removing 'Office'
        all_holders = sorted(list(set(configured_installers + db_holders)))
        if 'Office' in all_holders: all_holders.remove('Office')
        
        for holder in all_holders:
            holder_stock = stock_devices[stock_devices['Holder'] == holder]
            counts = {model: len(holder_stock[holder_stock['Model'] == model]) for model in final_models}
            total_held = len(holder_stock)
            matrix_rows.append({'name': holder, 'counts': counts, 'total': total_held})
            
        return {
            'models': final_models,
            'total': all_counts,
            'office': {'counts': office_counts, 'total': office_total},
            'installers': matrix_rows
        }
    except Exception as e:
        print(f"Error generating stock matrix: {e}")
        return {'models': [], 'total': {}, 'office': {}, 'installers': []}

def import_devices_from_excel(filepath):
    """Imports devices from an uploaded Excel file."""
    try:
        # Load the uploaded file
        df = pd.read_excel(filepath)
        
        # Check required columns
        required_cols = ['IMEI', 'Model', 'Purchased From']
        if not all(col in df.columns for col in required_cols):
             return False, "Missing columns. Required: IMEI, Model, Purchased From"

        # Check for duplicates in DB
        existing_imeis = set([str(d['IMEI']) for d in read_sheet('Devices')])
        
        new_devices_count = 0
        wb = load_workbook(DB_FILE)
        ws = wb['Devices']
        
        # We need headers to know where to write what
        headers = [cell.value for cell in ws[1]]
        
        for _, row in df.iterrows():
            imei = str(row['IMEI'])
            if imei not in existing_imeis:
                # Prepare row dict
                row_data = {
                    'IMEI': imei,
                    'Model': row['Model'],
                    'Added Date': datetime.now().strftime('%Y-%m-%d'),
                    'Status': 'Stock',
                    'Holder': 'Office',
                    'Purchased From': row.get('Purchased From', '')
                }
                
                # Append to sheet
                values = [row_data.get(h, '') for h in headers]
                ws.append(values)
                existing_imeis.add(imei) # Prevent dupes within the same file
                new_devices_count += 1
                
        wb.save(DB_FILE)
        return True, f"Imported {new_devices_count} new devices."
        
    except Exception as e:
        print(f"Import Error: {e}")
        return False, str(e)

def replace_device(old_imei, new_imei, vehicle_no, installer, date=None, price=None):
    """
    Replaces an old device with a new one.
    1. Update Old Device -> Status: Replaced
    2. Update New Device -> Status: Installed, Holder: Installer
    3. Log to 'Replacements' sheet
    """
    try:
        if not date:
            date = datetime.now().strftime('%Y-%m-%d')
        if not price:
            price = ''
            
        wb = load_workbook(DB_FILE)
        
        # 1. Update Old Device
        ws_dev = wb['Devices']
        headers = [cell.value for cell in ws_dev[1]]
        imei_idx = headers.index('IMEI')
        status_idx = headers.index('Status')
        
        old_device_found = False
        for row in ws_dev.iter_rows(min_row=2):
            if str(row[imei_idx].value) == str(old_imei):
                row[status_idx].value = 'Replaced'
                old_device_found = True
                break
        
        if not old_device_found:
            # Add the old device if not found
            # We don't have the model, so we'll set it to 'Unknown' or 'Legacy'
            # We need to map the headers to ensure we insert correctly
            new_row_data = {}
            for col_name in headers:
                new_row_data[col_name] = '' # Initialize
            
            # Set specific values
            # Using column indices might be safer if we map them
            # But appending a list is easier if we know the order. 
            # We should probably construct the list based on headers.
            
            row_values = []
            for h in headers:
                if h == 'IMEI': row_values.append(old_imei)
                elif h == 'Status': row_values.append('Replaced')
                elif h == 'Model': row_values.append('Legacy/Unknown')
                elif h == 'Added Date': row_values.append(date) # Replaced date as added date?
                else: row_values.append('')
            
            ws_dev.append(row_values)
            # We don't return here, we proceed to update New Device

        # 2. Update New Device
        new_device_added_date = ''
        holder_idx = headers.index('Holder')
        added_date_idx = headers.index('Added Date')
        
        new_device_found = False
        for row in ws_dev.iter_rows(min_row=2):
            if str(row[imei_idx].value) == str(new_imei):
                if row[status_idx].value != 'Stock':
                     return False, "New Device is not in Stock"
                
                row[status_idx].value = 'Installed'
                row[holder_idx].value = installer
                new_device_added_date = row[added_date_idx].value
                new_device_found = True
                break
                
        if not new_device_found:
             return False, "New Device not found"

        # 3. Log to Replacements Sheet
        if 'Replacements' not in wb.sheetnames:
            wb.create_sheet('Replacements')
            ws_rep = wb['Replacements']
            ws_rep.append(['Old IMEI', 'New IMEI', 'Vehicle No', 'Installed By', 'Transferred Date', 'Added New Device Date', 'Price'])
        
        ws_rep = wb['Replacements']
        # Check if Price header exists (for existing sheets)
        rep_headers = [cell.value for cell in ws_rep[1]]
        if 'Price' not in rep_headers:
            ws_rep.cell(row=1, column=len(rep_headers)+1, value='Price')
            
        ws_rep.append([old_imei, new_imei, vehicle_no, installer, date, new_device_added_date, price])
        
        wb.save(DB_FILE)
        return True, "Success"
        
    except Exception as e:
        print(f"Error replacing device: {e}")
        return False, str(e)

def get_replacements():
    """Returns all replacement records."""
    return read_sheet('Replacements')