from flask import Blueprint, send_file, jsonify, current_app, request
from flask_jwt_extended import jwt_required
import os
import logging
import pandas as pd

# Configure logging
logger = logging.getLogger(__name__)

# Create blueprint for Excel routes
excel_bp = Blueprint('excel', __name__)

# Hardcoded Excel filename to download
EXCEL_FILENAME = 'rolecall.xlsx'  # Change this to your desired Excel filename


@excel_bp.route('/api/download-excel', methods=['GET'])
@jwt_required()
def download_excel():
    """Convert Excel file to JSON with headers as keys and rows as values"""
    try:
        uploads_folder = current_app.config['UPLOAD_FOLDER']
        file_path = os.path.join(uploads_folder, EXCEL_FILENAME)
        
        # Debug logging
        logger.info(f"Looking for Excel file at: {file_path}")
        logger.info(f"Uploads folder: {uploads_folder}")
        logger.info(f"File exists: {os.path.exists(file_path)}")
        
        if not os.path.exists(file_path):
            return jsonify({
                'error': 'File not found',
                'message': f'Excel file "{EXCEL_FILENAME}" not found at path: {file_path}',
                'uploads_folder': uploads_folder
            }), 404
        
        # Read Excel file using pandas
        try:
            df = pd.read_excel(file_path, engine='openpyxl')
            
            # Define the columns we want to extract
            required_columns = [
                "Payroll Number",
                "Employee Name", 
                "License Number",
                "License Code",
                "Start Date",
                "Expiry/Update  Date",
                "State"
            ]
            
            # Filter DataFrame to only include the required columns
            # Check which columns actually exist in the file
            available_columns = [col for col in required_columns if col in df.columns]
            missing_columns = [col for col in required_columns if col not in df.columns]
            
            if not available_columns:
                return jsonify({
                    'error': 'No required columns found',
                    'message': f'None of the required columns found in the Excel file. Available columns: {list(df.columns)}',
                    'required_columns': required_columns,
                    'available_columns': list(df.columns)
                }), 400
            
            # Filter the DataFrame to only include available required columns
            filtered_df = df[available_columns]
            
            # Convert date columns to readable format (YYYY-MM-DD)
            date_columns = ['Start Date', 'Expiry/Update  Date']
            for col in date_columns:
                if col in filtered_df.columns:
                    # Convert to datetime and format as YYYY-MM-DD
                    filtered_df[col] = pd.to_datetime(filtered_df[col], errors='coerce').dt.strftime('%Y-%m-%d')
            
            # Convert DataFrame to JSON format with headers as keys and rows as values
            data = filtered_df.to_dict('records')
            
            # Get column information
            total_rows = len(filtered_df)
            
            logger.info(f"Successfully read Excel file: {EXCEL_FILENAME}, {total_rows} rows, {len(available_columns)} columns")
            if missing_columns:
                logger.warning(f"Missing columns in Excel file: {missing_columns}")
            
            return jsonify({
                'success': True,
                'filename': EXCEL_FILENAME,
                'total_rows': total_rows,
                'data': data
            })
            
        except Exception as e:
            logger.error(f"Error reading Excel file {EXCEL_FILENAME}: {e}")
            return jsonify({
                'error': 'Excel reading failed',
                'message': f'Could not read Excel file "{EXCEL_FILENAME}": {str(e)}'
            }), 500
        
    except Exception as e:
        logger.error(f"Excel processing error: {e}")
        return jsonify({
            'error': 'Excel processing failed',
            'message': str(e)
        }), 500


@excel_bp.route('/api/file-data/<filename>', methods=['GET'])
@jwt_required()
def get_file_data(filename):
    """Get file data as JSON with headers as keys and rows as values"""
    try:
        # Get the project root directory (two levels up from this file)
        project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        results_folder = os.path.join(project_root, 'results')
        
        logger.info(f"Looking for file '{filename}' in results folder: {results_folder}")
        
        if not os.path.exists(results_folder):
            return jsonify({
                'error': 'Results folder not found',
                'message': f'Results folder not found at: {results_folder}'
            }), 404
        
        # Construct the full file path
        file_path = os.path.join(results_folder, filename)
        
        # Security check: ensure the file is within the results folder
        if not os.path.abspath(file_path).startswith(os.path.abspath(results_folder)):
            return jsonify({
                'error': 'Invalid file path',
                'message': 'File path is outside the results folder'
            }), 400
        
        if not os.path.exists(file_path):
            return jsonify({
                'error': 'File not found',
                'message': f'File "{filename}" not found in results folder'
            }), 404
        
        if not os.path.isfile(file_path):
            return jsonify({
                'error': 'Invalid file',
                'message': f'"{filename}" is not a file'
            }), 400
        
        logger.info(f"Found file: {file_path}")
        
        # Read the file based on extension
        file_ext = os.path.splitext(filename)[1].lower()
        
        try:
            if file_ext in ['.xlsx', '.xls']:
                df = pd.read_excel(file_path, engine='openpyxl')
            elif file_ext == '.csv':
                df = pd.read_csv(file_path)
            else:
                return jsonify({
                    'error': 'Unsupported file type',
                    'message': f'File type "{file_ext}" is not supported. Only .xlsx, .xls, and .csv files are supported.'
                }), 400
            
            # Convert DataFrame to JSON format with headers as keys
            # Each row becomes a dictionary with column names as keys
            data = df.to_dict('records')
            
            # Get column information
            # columns = df.columns.tolist()
            total_rows = len(df)
            
            # Extract scraper type from filename (e.g., "TAS_Results_job_..." -> "TAS")
            scraper_type = filename.split('_')[0] if '_' in filename else 'UNKNOWN'
            
            # logger.info(f"Successfully read file: {filename}, {total_rows} rows, {len(columns)} columns, scraper: {scraper_type}")
            
            # Delete the file after successfully reading it
            try:
                os.remove(file_path)
                logger.info(f"Successfully deleted file after reading: {filename}")
            except Exception as delete_error:
                logger.warning(f"Could not delete file {filename} after reading: {delete_error}")
            
            return jsonify({
                'success': True,
                'filename': filename,
                'scraper_type': scraper_type,
                'total_rows': total_rows,
                # 'columns': columns,
                'data': data,
                'file_deleted': True
            })
            
        except Exception as e:
            logger.error(f"Error reading file {filename}: {e}")
            return jsonify({
                'error': 'File reading failed',
                'message': f'Could not read file "{filename}": {str(e)}'
            }), 500
        
    except Exception as e:
        logger.error(f"Get file data error: {e}")
        return jsonify({
            'error': 'Get file data failed',
            'message': str(e)
        }), 500


@excel_bp.route('/api/list-results', methods=['GET'])
@jwt_required()
def list_results():
    """List all files in the results folder without downloading"""
    try:
        # Get the project root directory (two levels up from this file)
        project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        results_folder = os.path.join(project_root, 'results')
        
        logger.info(f"Looking for results folder at: {results_folder}")
        
        if not os.path.exists(results_folder):
            return jsonify({
                'error': 'Results folder not found',
                'message': f'Results folder not found at: {results_folder}',
                'files': []
            }), 404
        
        # Get all files in the results folder with details (excluding system files)
        result_files = []
        for file in os.listdir(results_folder):
            file_path = os.path.join(results_folder, file)
            if os.path.isfile(file_path) and not file.startswith('.'):
                file_stat = os.stat(file_path)
                result_files.append({
                    'filename': file,
                    'size': file_stat.st_size,
                    'modified': file_stat.st_mtime,
                    'path': file_path
                })
        
        # Sort by modification time (newest first)
        result_files.sort(key=lambda x: x['modified'], reverse=True)
        
        logger.info(f"Found {len(result_files)} files in results folder")
        
        return jsonify({
            'success': True,
            'count': len(result_files),
            'files': result_files,
            'results_folder': results_folder
        })
        
    except Exception as e:
        logger.error(f"List results error: {e}")
        return jsonify({
            'error': 'List results failed',
            'message': str(e)
        }), 500