from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from db.db import get_db_connection
from config.auth import role_required
import mysql.connector
from datetime import datetime

expenses_bp = Blueprint('expenses', __name__)


@expenses_bp.route('/add_expense', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def add_expense():
    data = request.json
    title = data.get('title')
    category = data.get('category')
    amount = data.get('amount')
    details = data.get('details')
    warehouse_id = data.get('warehouse_id')
    store_id = data.get('store_id')
    
    # Get the current user ID from JWT
    current_user_id = get_jwt_identity()

    if not all([title, category, amount]):
        return jsonify({'status': 'fail', 'message': 'Missing required fields'}), 400

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        sql = """INSERT INTO expenses 
                 (title, category, amount, details, warehouse_id, store_id, created_by) 
                 VALUES (%s, %s, %s, %s, %s, %s, %s)"""
        cursor.execute(sql, (title, category, amount, details, warehouse_id, store_id, current_user_id))
        conn.commit()
        return jsonify({'status': 'success', 'expense_id': cursor.lastrowid}), 201
    except mysql.connector.Error as err:
        print(f"Database error: {err}")
        conn.rollback()
        return jsonify({'status': 'fail', 'message': str(err)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/get_expenses', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_expenses():
    # Optional query parameters for filtering
    warehouse_id = request.args.get('warehouse_id', type=int)
    store_id = request.args.get('store_id', type=int)
    category = request.args.get('category')
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # FIXED: Use warehouse_name and store_name instead of name
        sql = """SELECT e.id, e.title, e.category, e.amount, e.details, e.date, 
                        e.warehouse_id, e.store_id, e.created_by,
                        w.warehouse_name, s.store_name,
                        u.name as created_by_username
                 FROM expenses e
                 LEFT JOIN warehouses w ON e.warehouse_id = w.id
                 LEFT JOIN stores s ON e.store_id = s.id
                 LEFT JOIN users u ON e.created_by = u.id
                 WHERE 1=1"""
        
        params = []
        
        if warehouse_id:
            sql += " AND e.warehouse_id = %s"
            params.append(warehouse_id)
        
        if store_id:
            sql += " AND e.store_id = %s"
            params.append(store_id)
            
        if category:
            sql += " AND e.category = %s"
            params.append(category)
        
        sql += " ORDER BY e.date DESC"
        
        cursor.execute(sql, params)
        expenses = cursor.fetchall()
        
        # Convert datetime objects to strings for JSON serialization
        for expense in expenses:
            if expense.get('date'):
                expense['date'] = expense['date'].isoformat()
        
        return jsonify({'status': 'success', 'data': expenses}), 200
    except Exception as e:
        print(f"Error fetching expenses: {e}")
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/update_expense/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'cashier')
def update_expense(id):
    data = request.json
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # Check if expense exists
        cursor.execute("SELECT id FROM expenses WHERE id = %s", (id,))
        if cursor.fetchone() is None:
            return jsonify({'status': 'fail', 'message': 'Expense not found'}), 404
        
        sql = """UPDATE expenses 
                 SET title=%s, category=%s, amount=%s, details=%s, 
                     warehouse_id=%s, store_id=%s
                 WHERE id=%s"""
        cursor.execute(sql, (
            data.get('title'),
            data.get('category'),
            data.get('amount'),
            data.get('details'),
            data.get('warehouse_id'),
            data.get('store_id'),
            id
        ))
        conn.commit()
        return jsonify({'status': 'success', 'message': 'Expense updated successfully'}), 200
    except Exception as e:
        print(f"Error updating expense id={id}: {e}")
        conn.rollback()
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/delete_expense/<int:expense_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_expense(expense_id):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT id FROM expenses WHERE id = %s", (expense_id,))
        if cursor.fetchone() is None:
            return jsonify({'status': 'fail', 'message': 'Expense not found'}), 404

        cursor.execute("DELETE FROM expenses WHERE id = %s", (expense_id,))
        conn.commit()

        return jsonify({'status': 'success', 'message': 'Expense deleted successfully'}), 200

    except Exception as e:
        print(f"Error deleting expense: {e}")
        conn.rollback()
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/get_expense/<int:expense_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_expense(expense_id):
    """Get a single expense by ID"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # FIXED: Use warehouse_name and store_name
        sql = """SELECT e.*, 
                        w.warehouse_name, 
                        s.store_name,
                        u.name as created_by_username
                 FROM expenses e
                 LEFT JOIN warehouses w ON e.warehouse_id = w.id
                 LEFT JOIN stores s ON e.store_id = s.id
                 LEFT JOIN users u ON e.created_by = u.id
                 WHERE e.id = %s"""
        cursor.execute(sql, (expense_id,))
        expense = cursor.fetchone()
        
        if not expense:
            return jsonify({'status': 'fail', 'message': 'Expense not found'}), 404
        
        if expense.get('date'):
            expense['date'] = expense['date'].isoformat()
            
        return jsonify({'status': 'success', 'data': expense}), 200
    except Exception as e:
        print(f"Error fetching expense: {e}")
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/get_expense_summary', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_expense_summary():
    """Get expense summary statistics"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        sql = """SELECT 
                    COUNT(*) as total_count,
                    COALESCE(SUM(amount), 0) as total_amount,
                    category,
                    COUNT(*) as category_count,
                    COALESCE(SUM(amount), 0) as category_total
                 FROM expenses
                 GROUP BY category
                 ORDER BY category_total DESC"""
        cursor.execute(sql)
        summary = cursor.fetchall()
        
        # Convert Decimal to float for JSON serialization
        for item in summary:
            if item.get('total_amount'):
                item['total_amount'] = float(item['total_amount'])
            if item.get('category_total'):
                item['category_total'] = float(item['category_total'])
        
        return jsonify({'status': 'success', 'data': summary}), 200
    except Exception as e:
        print(f"Error fetching summary: {e}")
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/get_warehouses', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_warehouses():
    """Get all active warehouses for dropdown"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT id, warehouse_name, city, is_active 
            FROM warehouses 
            WHERE is_active = 1 
            ORDER BY warehouse_name
        """)
        warehouses = cursor.fetchall()
        return jsonify({'status': 'success', 'data': warehouses}), 200
    except Exception as e:
        print(f"Error fetching warehouses: {e}")
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


@expenses_bp.route('/get_stores', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def get_stores():
    """Get all active stores for dropdown"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT id, store_name, is_active 
            FROM stores 
            WHERE is_active = 1 
            ORDER BY store_name
        """)
        stores = cursor.fetchall()
        return jsonify({'status': 'success', 'data': stores}), 200
    except Exception as e:
        print(f"Error fetching stores: {e}")
        return jsonify({'status': 'fail', 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()