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

warehouse_bp = Blueprint('warehouse', __name__)

# Create Warehouse
@warehouse_bp.route('/warehouses', methods=['POST'])
@jwt_required()
@role_required('admin')
def create_warehouse():
    """Create a new warehouse"""
    try:
        data = request.get_json()
        
        # Validate required fields
        required_fields = ['warehouse_name', 'city', 'country', 'contact', 'email']
        for field in required_fields:
            if field not in data or not data[field]:
                return jsonify({'error': f'{field} is required'}), 400
        
        # Validate email format
        email = data['email'].strip()
        if '@' not in email:
            return jsonify({'error': 'Invalid email format'}), 400
        
        connection = get_db_connection()
        cursor = connection.cursor()
        
        # Check if warehouse name already exists
        cursor.execute(
            "SELECT id FROM warehouses WHERE warehouse_name = %s",
            (data['warehouse_name'].strip(),)
        )
        if cursor.fetchone():
            cursor.close()
            connection.close()
            return jsonify({'error': 'Warehouse name already exists'}), 409
        
        # Insert new warehouse
        query = """
            INSERT INTO warehouses 
            (warehouse_name, city, country, contact, email, is_active) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """
        values = (
            data['warehouse_name'].strip(),
            data['city'].strip(),
            data['country'].strip(),
            data['contact'].strip(),
            email,
            data.get('is_active', 1)
        )
        
        cursor.execute(query, values)
        connection.commit()
        
        warehouse_id = cursor.lastrowid
        
        cursor.close()
        connection.close()
        
        return jsonify({
            'message': 'Warehouse created successfully',
            'warehouse_id': warehouse_id
        }), 201
        
    except mysql.connector.Error as err:
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500


# Get All Warehouses
@warehouse_bp.route('/warehouses', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier') 
def get_all_warehouses():
    """Get all warehouses with optional filters"""
    try:
        # Get query parameters
        is_active = request.args.get('is_active', type=int)
        search = request.args.get('search', '').strip()
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        
        # Build query with filters
        query = "SELECT * FROM warehouses WHERE 1=1"
        params = []
        
        if is_active is not None:
            query += " AND is_active = %s"
            params.append(is_active)
        
        if search:
            query += " AND (warehouse_name LIKE %s OR city LIKE %s OR country LIKE %s)"
            search_pattern = f"%{search}%"
            params.extend([search_pattern, search_pattern, search_pattern])
        
        # Get total count
        count_query = query.replace("SELECT *", "SELECT COUNT(*) as total")
        cursor.execute(count_query, params)
        total_count = cursor.fetchone()['total']
        
        # Add pagination
        query += " ORDER BY created_at DESC LIMIT %s OFFSET %s"
        params.extend([per_page, (page - 1) * per_page])
        
        cursor.execute(query, params)
        warehouses = cursor.fetchall()
        
        # Format datetime objects
        for warehouse in warehouses:
            if warehouse.get('created_at'):
                warehouse['created_at'] = warehouse['created_at'].isoformat()
            if warehouse.get('updated_at'):
                warehouse['updated_at'] = warehouse['updated_at'].isoformat()
        
        cursor.close()
        connection.close()
        
        return jsonify({
            'warehouses': warehouses,
            'total': total_count,
            'page': page,
            'per_page': per_page,
            'total_pages': (total_count + per_page - 1) // per_page
        }), 200
        
    except mysql.connector.Error as err:
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500


# Get Single Warehouse
@warehouse_bp.route('/warehouses/<int:warehouse_id>', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_warehouse(warehouse_id):
    """Get a single warehouse by ID"""
    try:
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        
        cursor.execute("SELECT * FROM warehouses WHERE id = %s", (warehouse_id,))
        warehouse = cursor.fetchone()
        
        if not warehouse:
            cursor.close()
            connection.close()
            return jsonify({'error': 'Warehouse not found'}), 404
        
        # Format datetime objects
        if warehouse.get('created_at'):
            warehouse['created_at'] = warehouse['created_at'].isoformat()
        if warehouse.get('updated_at'):
            warehouse['updated_at'] = warehouse['updated_at'].isoformat()
        
        cursor.close()
        connection.close()
        
        return jsonify(warehouse), 200
        
    except mysql.connector.Error as err:
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500


# Update Warehouse
@warehouse_bp.route('/warehouses/<int:warehouse_id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def update_warehouse(warehouse_id):
    """Update an existing warehouse"""
    try:
        data = request.get_json()
        
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        
        # Check if warehouse exists
        cursor.execute("SELECT id FROM warehouses WHERE id = %s", (warehouse_id,))
        if not cursor.fetchone():
            cursor.close()
            connection.close()
            return jsonify({'error': 'Warehouse not found'}), 404
        
        # Build dynamic update query
        update_fields = []
        values = []
        
        allowed_fields = ['warehouse_name', 'city', 'country', 'contact', 'email', 'is_active']
        
        for field in allowed_fields:
            if field in data:
                if field == 'email' and data[field] and '@' not in data[field]:
                    cursor.close()
                    connection.close()
                    return jsonify({'error': 'Invalid email format'}), 400
                
                update_fields.append(f"{field} = %s")
                values.append(data[field].strip() if isinstance(data[field], str) else data[field])
        
        if not update_fields:
            cursor.close()
            connection.close()
            return jsonify({'error': 'No fields to update'}), 400
        
        # Check for duplicate warehouse name if updating name
        if 'warehouse_name' in data:
            cursor.execute(
                "SELECT id FROM warehouses WHERE warehouse_name = %s AND id != %s",
                (data['warehouse_name'].strip(), warehouse_id)
            )
            if cursor.fetchone():
                cursor.close()
                connection.close()
                return jsonify({'error': 'Warehouse name already exists'}), 409
        
        # Execute update
        query = f"UPDATE warehouses SET {', '.join(update_fields)} WHERE id = %s"
        values.append(warehouse_id)
        
        cursor.execute(query, values)
        connection.commit()
        
        cursor.close()
        connection.close()
        
        return jsonify({'message': 'Warehouse updated successfully'}), 200
        
    except mysql.connector.Error as err:
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500


# # Soft Delete Warehouse (Current - keeps in database)
# @warehouse_bp.route('/warehouses/<int:warehouse_id>', methods=['DELETE'])
# @jwt_required()
# @role_required('admin')
# def delete_warehouse(warehouse_id):
#     """Permanently delete a warehouse from the database"""
#     try:
#         connection = get_db_connection()
#         cursor = connection.cursor(dictionary=True)
        
#         # Check if warehouse exists
#         cursor.execute("SELECT warehouse_name FROM warehouses WHERE id = %s", (warehouse_id,))
#         warehouse = cursor.fetchone()
        
#         if not warehouse:
#             cursor.close()
#             connection.close()
#             return jsonify({'error': 'Warehouse not found'}), 404
        
#         # Check if warehouse has associated stock
#         cursor.execute(
#             "SELECT COUNT(*) as count FROM warehouse_stock WHERE warehouse_id = %s",
#             (warehouse_id,)
#         )
#         stock_count = cursor.fetchone()['count']
        
#         if stock_count > 0:
#             cursor.close()
#             connection.close()
#             return jsonify({
#                 'error': f'Cannot delete warehouse. It has {stock_count} stock records. Please transfer or remove stock first.'
#             }), 400
        
#         # Check for other dependencies (purchases, sales, transfers, etc.)
#         dependencies = []
        
#         # Check purchase orders
#         cursor.execute(
#             "SELECT COUNT(*) as count FROM purchase_orders WHERE warehouse_id = %s",
#             (warehouse_id,)
#         )
#         if cursor.fetchone()['count'] > 0:
#             dependencies.append('purchase orders')
        
#         # Check GRN
#         cursor.execute(
#             "SELECT COUNT(*) as count FROM grn WHERE warehouse_id = %s",
#             (warehouse_id,)
#         )
#         if cursor.fetchone()['count'] > 0:
#             dependencies.append('GRN records')
        
#         # Check sales
#         cursor.execute(
#             "SELECT COUNT(*) as count FROM invoice_sale WHERE warehouse_id = %s",
#             (warehouse_id,)
#         )
#         if cursor.fetchone()['count'] > 0:
#             dependencies.append('sales')
        
#         # Check stock transfers
#         cursor.execute(
#             "SELECT COUNT(*) as count FROM stock_transfers WHERE from_warehouse_id = %s OR to_warehouse_id = %s",
#             (warehouse_id, warehouse_id)
#         )
#         if cursor.fetchone()['count'] > 0:
#             dependencies.append('stock transfers')
        
#         if dependencies:
#             cursor.close()
#             connection.close()
#             return jsonify({
#                 'error': f'Cannot delete warehouse. It has associated {", ".join(dependencies)}.'
#             }), 400
        
#         # Permanent delete - completely remove from database
#         cursor.execute("DELETE FROM warehouses WHERE id = %s", (warehouse_id,))
#         connection.commit()
        
#         cursor.close()
#         connection.close()
        
#         return jsonify({
#             'message': f'Warehouse "{warehouse["warehouse_name"]}" has been permanently deleted'
#         }), 200
        
#     except mysql.connector.Error as err:
#         print(f"❌ Database error: {str(err)}")
#         import traceback
#         traceback.print_exc()
#         return jsonify({'error': f'Database error: {str(err)}'}), 500
#     except Exception as e:
#         print(f"❌ Server error: {str(e)}")
#         import traceback
#         traceback.print_exc()
#         return jsonify({'error': f'Server error: {str(e)}'}), 500


# Toggle Status (Activate/Deactivate - keeps in database)
@warehouse_bp.route('/warehouses/<int:warehouse_id>/toggle-status', methods=['PATCH'])
@jwt_required()
@role_required('admin')
def toggle_warehouse_status(warehouse_id):
    """Toggle warehouse active status (soft delete alternative)"""
    try:
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        
        # Get current status
        cursor.execute("SELECT warehouse_name, is_active FROM warehouses WHERE id = %s", (warehouse_id,))
        warehouse = cursor.fetchone()
        
        if not warehouse:
            cursor.close()
            connection.close()
            return jsonify({'error': 'Warehouse not found'}), 404
        
        # Toggle status
        new_status = 0 if warehouse['is_active'] == 1 else 1
        cursor.execute(
            "UPDATE warehouses SET is_active = %s WHERE id = %s",
            (new_status, warehouse_id)
        )
        connection.commit()
        
        cursor.close()
        connection.close()
        
        status_text = 'activated' if new_status == 1 else 'deactivated'
        return jsonify({
            'message': f'Warehouse "{warehouse["warehouse_name"]}" {status_text} successfully',
            'is_active': new_status
        }), 200
        
    except mysql.connector.Error as err:
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500

# Permanent Delete Warehouse
@warehouse_bp.route('/warehouses/<int:warehouse_id>/permanent', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def permanent_delete_warehouse(warehouse_id):
    """Permanently delete a warehouse from database"""
    try:
        connection = get_db_connection()
        cursor = connection.cursor()
        
        # Check if warehouse exists
        cursor.execute("SELECT id FROM warehouses WHERE id = %s", (warehouse_id,))
        if not cursor.fetchone():
            cursor.close()
            connection.close()
            return jsonify({'error': 'Warehouse not found'}), 404
        
        # Permanent delete
        cursor.execute("DELETE FROM warehouses WHERE id = %s", (warehouse_id,))
        connection.commit()
        
        cursor.close()
        connection.close()
        
        return jsonify({'message': 'Warehouse permanently deleted'}), 200
        
    except mysql.connector.Error as err:
        return jsonify({'error': f'Database error: {str(err)}'}), 500
    except Exception as e:
        return jsonify({'error': f'Server error: {str(e)}'}), 500


# Activate/Deactivate Warehouse
# @warehouse_bp.route('/warehouses/<int:warehouse_id>/toggle-status', methods=['PATCH'])
# @jwt_required()
# @role_required('admin')
# def toggle_warehouse_status(warehouse_id):
#     """Toggle warehouse active status"""
#     try:
#         connection = get_db_connection()
#         cursor = connection.cursor(dictionary=True)
        
#         # Get current status
#         cursor.execute("SELECT is_active FROM warehouses WHERE id = %s", (warehouse_id,))
#         warehouse = cursor.fetchone()
        
#         if not warehouse:
#             cursor.close()
#             connection.close()
#             return jsonify({'error': 'Warehouse not found'}), 404
        
#         # Toggle status
#         new_status = 0 if warehouse['is_active'] == 1 else 1
#         cursor.execute(
#             "UPDATE warehouses SET is_active = %s WHERE id = %s",
#             (new_status, warehouse_id)
#         )
#         connection.commit()
        
#         cursor.close()
#         connection.close()
        
#         status_text = 'activated' if new_status == 1 else 'deactivated'
#         return jsonify({
#             'message': f'Warehouse {status_text} successfully',
#             'is_active': new_status
#         }), 200
        
#     except mysql.connector.Error as err:
#         return jsonify({'error': f'Database error: {str(err)}'}), 500
#     except Exception as e:
#         return jsonify({'error': f'Server error: {str(e)}'}), 500
    
    
    
@warehouse_bp.route('/warehouses/<int:warehouse_id>/products', methods=['GET'])
@jwt_required()
@role_required('admin')
def get_warehouse_products(warehouse_id):
    """Get all products in a specific warehouse with their quantities"""
    connection = None
    cursor = None
    
    try:
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        
        # Get store_id from query parameters
        store_id = request.args.get('store_id')
        
        if not store_id:
            return jsonify({'error': 'store_id is required'}), 400
        
        # Check if warehouse exists
        cursor.execute("SELECT id, warehouse_name FROM warehouses WHERE id = %s", (warehouse_id,))
        warehouse = cursor.fetchone()
        
        if not warehouse:
            return jsonify({'error': 'Warehouse not found'}), 404
        
        # Get SINGLE products with stock in this warehouse
        # ✅ CORRECTED: base_units.base_unit (not unit_name)
        single_products_query = """
            SELECT 
                p.id as product_id,
                NULL as variation_id,
                p.product_name,
                p.product_image,
                p.sku,
                p.product_type,
                SUM(ws.quantity) as quantity,
                bu.base_unit as base_unit_name,
                su.unit_name as sale_unit_name,
                su.unit_short as sale_unit_short
            FROM warehouse_stock ws
            INNER JOIN products p ON ws.product_id = p.id
            LEFT JOIN base_units bu ON p.base_unit_id = bu.id
            LEFT JOIN units su ON p.sale_unit_id = su.id
            WHERE ws.warehouse_id = %s 
                AND ws.store_id = %s
                AND p.product_type = 'single'
                AND ws.variation_id IS NULL
            GROUP BY p.id, p.product_name, p.product_image, p.sku, p.product_type,
                     bu.base_unit, su.unit_name, su.unit_short
            HAVING quantity > 0
        """
        
        # Get VARIATION products with stock in this warehouse
        # ✅ CORRECTED: base_units.base_unit (not unit_name)
        variation_products_query = """
            SELECT 
                p.id as product_id,
                pv.id as variation_id,
                CONCAT(p.product_name, ' (', pv.variation_name, ' - ', pv.variation_type, ')') as product_name,
                p.product_image,
                pv.variation_sku as sku,
                p.product_type,
                SUM(ws.quantity) as quantity,
                bu.base_unit as base_unit_name,
                su.unit_name as sale_unit_name,
                su.unit_short as sale_unit_short
            FROM warehouse_stock ws
            INNER JOIN products p ON ws.product_id = p.id
            INNER JOIN product_variations pv ON ws.variation_id = pv.id
            LEFT JOIN base_units bu ON p.base_unit_id = bu.id
            LEFT JOIN units su ON p.sale_unit_id = su.id
            WHERE ws.warehouse_id = %s 
                AND ws.store_id = %s
                AND p.product_type = 'variable'
                AND ws.variation_id IS NOT NULL
            GROUP BY p.id, pv.id, p.product_name, pv.variation_name, pv.variation_type,
                     p.product_image, pv.variation_sku, p.product_type,
                     bu.base_unit, su.unit_name, su.unit_short
            HAVING quantity > 0
        """
        
        # Execute both queries
        cursor.execute(single_products_query, (warehouse_id, store_id))
        single_products = cursor.fetchall()
        
        cursor.execute(variation_products_query, (warehouse_id, store_id))
        variation_products = cursor.fetchall()
        
        # Combine and format the results
        all_products = single_products + variation_products
        
        formatted_products = []
        for product in all_products:
            formatted_products.append({
                'id': product['product_id'],
                'variation_id': product['variation_id'],
                'product_name': product['product_name'],
                'product_image': product['product_image'],
                'sku': product['sku'],
                'product_type': product['product_type'],
                'quantity': float(product['quantity']) if product['quantity'] else 0.0,
                'unit_name': product['sale_unit_name'] or product['base_unit_name'] or 'unit',
                'unit_short_name': product['sale_unit_short'] or product['sale_unit_name'] or product['base_unit_name'] or 'unit'
            })
        
        # Sort by product name
        formatted_products.sort(key=lambda x: x['product_name'])
        
        return jsonify({
            'warehouse': warehouse,
            'products': formatted_products,
            'total_products': len(formatted_products)
        }), 200
        
    except mysql.connector.Error as err:
        print(f"❌ Database error: {str(err)}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500
        
    except Exception as e:
        print(f"❌ Server error: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': f'Server error: {str(e)}'}), 500
        
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()