"""
Inventory Management Routes
Parts and part variants with stock tracking
"""
import logging
from flask import Blueprint, request, jsonify
from app.models.database import Database
from app.middleware.auth_local import require_admin, optional_auth

inventory_bp = Blueprint('inventory', __name__, url_prefix='/api/inventory')

# ============================================================================
# PARTS
# ============================================================================

@inventory_bp.route('/parts', methods=['GET'])
@optional_auth
def get_parts():
    """Get all parts with optional filtering"""
    service_type = request.args.get('service_type')
    category = request.args.get('category')

    query = "SELECT * FROM parts WHERE active = true"
    params = []

    if service_type:
        query += " AND service_type = %s"
        params.append(service_type)

    if category:
        query += " AND category = %s"
        params.append(category)

    query += " ORDER BY service_type, name"

    try:
        parts = Database.execute_query(query, params if params else None)
        return jsonify(parts), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


def _build_variant_filter_clause_for_reviews(args):
    """Reuse the same filters used by the variants listing for review counts."""
    part_id = args.get('part_id')
    search = args.get('search')
    service_type = args.get('serviceType')
    grade = args.get('grade')
    stock_status = args.get('stockStatus')

    where_conditions = "WHERE pv.active = true AND p.active = true"
    params = []

    # Only include rows that have been reviewed (both reviewer and timestamp present)
    where_conditions += " AND pv.price_reviewed_by IS NOT NULL AND pv.price_reviewed_by <> '' AND pv.price_reviewed_at IS NOT NULL"

    if part_id:
        where_conditions += " AND pv.part_id = %s"
        params.append(part_id)

    if search:
        where_conditions += """ AND (
            pv.sku ILIKE %s OR
            p.name ILIKE %s OR
            p.part_number ILIKE %s OR
            p.description ILIKE %s
        )"""
        search_param = f"%{search}%"
        params.extend([search_param, search_param, search_param, search_param])

    if service_type:
        where_conditions += " AND p.service_type = %s"
        params.append(service_type)

    if grade:
        where_conditions += " AND pv.grade = %s"
        params.append(grade)

    if stock_status:
        if stock_status == 'in_stock':
            where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) > 0"
        elif stock_status == 'low_stock':
            where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) > 0 AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold"
        elif stock_status == 'out_of_stock':
            where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) = 0"

    return where_conditions, params


@inventory_bp.route('/review-stats', methods=['GET'])
@optional_auth
def get_review_stats():
    """Return counts of reviewed variants by reviewer (blue, green, purple) across all pages.
    Always returns full counts regardless of filters/search."""

    # Always show full counts - only filter for active and reviewed items
    where_conditions = "WHERE pv.active = true AND p.active = true AND pv.price_reviewed_by IS NOT NULL AND pv.price_reviewed_by <> '' AND pv.price_reviewed_at IS NOT NULL"

    # Normalize reviewer slug server-side to capture variants reviewed by the aliases
    query = f"""
        SELECT
            COALESCE(SUM(CASE WHEN slug LIKE '%%blue-parrot%%' THEN 1 ELSE 0 END), 0) AS blue,
            COALESCE(SUM(CASE WHEN slug LIKE '%%green-ghost%%' OR slug LIKE '%%green-goblin%%' OR slug LIKE '%%jeksaballa%%' THEN 1 ELSE 0 END), 0) AS green,
            COALESCE(SUM(CASE WHEN slug LIKE '%%purple-dino%%' THEN 1 ELSE 0 END), 0) AS purple
        FROM (
            SELECT lower(regexp_replace(pv.price_reviewed_by, '[\\s_]+', '-', 'g')) AS slug
            FROM part_variants pv
            JOIN parts p ON pv.part_id = p.id
            {where_conditions}
        ) reviewer_slugs;
    """

    try:
        result = Database.execute_query(query, None, fetch_one=True)
        return jsonify({
            'blue': result.get('blue', 0) if result else 0,
            'green': result.get('green', 0) if result else 0,
            'purple': result.get('purple', 0) if result else 0
        }), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/parts', methods=['POST'])
@require_admin
def create_part():
    """Create a new part"""
    data = request.get_json()

    try:
        result = Database.execute_query("""
            INSERT INTO parts (part_number, name, service_type, category, description, manufacturer_part_number)
            VALUES (%s, %s, %s, %s, %s, %s)
            RETURNING *
        """, (
            data.get('part_number'),
            data.get('name'),
            data.get('service_type'),
            data.get('category'),
            data.get('description'),
            data.get('manufacturer_part_number')
        ), fetch_one=True)

        return jsonify(result), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/parts/<int:part_id>', methods=['GET'])
@optional_auth
def get_part(part_id):
    """Get a specific part by ID"""
    try:
        part = Database.execute_query(
            "SELECT * FROM parts WHERE id = %s AND active = true",
            (part_id,),
            fetch_one=True
        )

        if not part:
            return jsonify({'error': 'Part not found'}), 404

        return jsonify(part), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# ============================================================================
# PART VARIANTS (SKUs with inventory)
# ============================================================================

@inventory_bp.route('/variants', methods=['GET'])
@optional_auth
def get_variants():
    """Get part variants with inventory levels and device compatibility"""
    part_id = request.args.get('part_id')
    low_stock = request.args.get('low_stock')
    in_stock = request.args.get('in_stock')
    search = request.args.get('search')
    service_type = request.args.get('serviceType')
    grade = request.args.get('grade')
    stock_status = request.args.get('stockStatus')
    manufacturer = request.args.get('manufacturer')
    unreviewed_only = request.args.get('unreviewed_only') == 'true'

    # Pagination parameters (support both pageSize and page_size for backwards compatibility)
    page = int(request.args.get('page', 1))
    page_size = request.args.get('pageSize') or request.args.get('page_size') or 50
    try:
        page_size = int(page_size)
    except ValueError:
        page_size = 50
    offset = (page - 1) * page_size

    # Build WHERE clause for filters
    where_conditions = "WHERE pv.active = true AND p.active = true"
    params = []
    count_params = []

    if part_id:
        where_conditions += " AND pv.part_id = %s"
        params.append(part_id)
        count_params.append(part_id)

    if search:
        # Search in SKU, part name, part number, description
        where_conditions += """ AND (
            pv.sku ILIKE %s OR
            p.name ILIKE %s OR
            p.part_number ILIKE %s OR
            p.description ILIKE %s
        )"""
        search_param = f"%{search}%"
        params.extend([search_param, search_param, search_param, search_param])
        count_params.extend([search_param, search_param, search_param, search_param])

    if service_type:
        where_conditions += " AND p.service_type = %s"
        params.append(service_type)
        count_params.append(service_type)

    if grade:
        where_conditions += " AND pv.grade = %s"
        params.append(grade)
        count_params.append(grade)

    if stock_status:
        # Map stock_status to appropriate condition
        if stock_status == 'in_stock':
            where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) > 0"
        elif stock_status == 'low_stock':
            where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) > 0 AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold"
        elif stock_status == 'out_of_stock':
            where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) = 0"

    if manufacturer:
        # Filter by manufacturer - join through compatibility tables
        where_conditions += """ AND EXISTS (
            SELECT 1 FROM part_compatibility pc
            JOIN devices d ON pc.device_id = d.id
            JOIN device_families df ON d.family_id = df.id
            JOIN manufacturers m ON df.manufacturer_id = m.id
            WHERE pc.part_id = p.id AND m.name ILIKE %s
        )"""
        params.append(f"%{manufacturer}%")
        count_params.append(f"%{manufacturer}%")

    # TEMP: filter to only unreviewed rows (no reviewer or timestamp)
    if unreviewed_only:
        where_conditions += " AND (pv.price_reviewed_by IS NULL OR pv.price_reviewed_at IS NULL)"

    if low_stock == 'true':
        where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold"

    if in_stock == 'true':
        where_conditions += " AND (pv.quantity_on_hand - pv.quantity_reserved) > 0"

    # Get total count first
    count_query = f"""
        SELECT COUNT(*)
        FROM part_variants pv
        JOIN parts p ON pv.part_id = p.id
        {where_conditions}
    """

    try:
        count_result = Database.execute_query(count_query, count_params if count_params else None)
        total = count_result[0]['count'] if count_result else 0

        # Calculate pagination metadata
        page_count = (total + page_size - 1) // page_size  # Ceiling division
        has_more = page < page_count

        # Main query with pagination
        query = f"""
            WITH part_devices AS (
                SELECT
                    pc.part_id,
                    d.id as device_id,
                    d.model_name,
                    d.model_number,
                    d.release_date,
                    df.id as family_id,
                    df.name as family_name,
                    m.id as manufacturer_id,
                    m.name as manufacturer_name,
                    dt.id as device_type_id,
                    dt.name as device_type_name
                FROM part_compatibility pc
                JOIN devices d ON d.id = pc.device_id
                JOIN device_families df ON df.id = d.family_id
                JOIN manufacturers m ON m.id = df.manufacturer_id
                JOIN device_types dt ON dt.id = df.device_type_id
                WHERE pc.device_id IS NOT NULL
            ),
            part_families_agg AS (
                SELECT
                    part_id,
                    family_id,
                    family_name,
                    manufacturer_id,
                    manufacturer_name,
                    device_type_id,
                    device_type_name,
                    json_agg(
                        json_build_object(
                            'device_id', device_id,
                            'model_name', model_name,
                            'model_number', model_number,
                            'release_date', release_date
                        ) ORDER BY model_name
                    ) as devices
                FROM part_devices
                GROUP BY part_id, family_id, family_name, manufacturer_id, manufacturer_name, device_type_id, device_type_name
            ),
            part_compatibility_json AS (
                SELECT
                    part_id,
                    json_agg(
                        json_build_object(
                            'family_id', family_id,
                            'family_name', family_name,
                            'manufacturer_id', manufacturer_id,
                            'manufacturer_name', manufacturer_name,
                            'device_type_id', device_type_id,
                            'device_type_name', device_type_name,
                            'devices', devices
                        ) ORDER BY manufacturer_name, family_name
                    ) as compatibility
                FROM part_families_agg
                GROUP BY part_id
            )
            SELECT
                pv.id,
                pv.part_id,
                pv.sku,
                pv.color,
                pv.grade,
                pv.cost,
                pv.quantity_on_hand,
                pv.quantity_reserved,
                (pv.quantity_on_hand - pv.quantity_reserved) as quantity_available,
                pv.reorder_threshold,
                pv.reorder_quantity,
                pv.notes,
                pv.supplier_id,
                pv.active,
                pv.created_at,
                pv.updated_at,
                pv.price_reviewed_by,
                pv.price_reviewed_at,
                p.name as part_name,
                p.service_type,
                p.category,
                p.part_number,
                p.description,
                COALESCE(pcj.compatibility, '[]'::json) as compatibility
            FROM part_variants pv
            JOIN parts p ON pv.part_id = p.id
            LEFT JOIN part_compatibility_json pcj ON pcj.part_id = p.id
            {where_conditions}
            ORDER BY p.name, pv.grade, pv.color
            LIMIT %s OFFSET %s
        """

        # Add pagination params
        params.extend([page_size, offset])

        variants = Database.execute_query(query, params)

        # Return paginated response
        return jsonify({
            'items': variants,
            'total': total,
            'page': page,
            'pageSize': page_size,
            'pageCount': page_count,
            'hasMore': has_more
        }), 200

    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/variants', methods=['POST'])
@require_admin
def create_variant():
    """Create a new part variant (SKU)"""
    data = request.get_json()

    try:
        result = Database.execute_query("""
            INSERT INTO part_variants
            (part_id, sku, color, grade, cost, quantity_on_hand, reorder_threshold, reorder_quantity, supplier_id)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            RETURNING *
        """, (
            data.get('part_id'),
            data.get('sku'),
            data.get('color'),
            data.get('grade'),
            data.get('cost'),
            data.get('quantity_on_hand', 0),
            data.get('reorder_threshold', 0),
            data.get('reorder_quantity', 0),
            data.get('supplier_id')
        ), fetch_one=True)

        return jsonify(result), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/variants/<int:variant_id>', methods=['GET'])
@optional_auth
def get_variant(variant_id):
    """Get a specific part variant by ID"""
    try:
        variant = Database.execute_query("""
            SELECT
                pv.*,
                (pv.quantity_on_hand - pv.quantity_reserved) as quantity_available,
                p.name as part_name,
                p.service_type,
                p.part_number
            FROM part_variants pv
            JOIN parts p ON pv.part_id = p.id
            WHERE pv.id = %s AND pv.active = true
        """, (variant_id,), fetch_one=True)

        if not variant:
            return jsonify({'error': 'Part variant not found'}), 404

        return jsonify(variant), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/variants/<int:variant_id>', methods=['PUT'])
@require_admin
def update_variant(variant_id):
    """Update a part variant and optionally its parent part"""
    data = request.get_json()

    try:
        # First, check if the variant exists and get its part_id
        variant = Database.execute_query(
            "SELECT id, part_id FROM part_variants WHERE id = %s",
            (variant_id,),
            fetch_one=True
        )

        if not variant:
            return jsonify({'error': 'Part variant not found'}), 404

        part_id = variant['part_id']

        # Handle part-level fields (description, part_name)
        part_fields = []
        part_params = []

        if 'description' in data:
            part_fields.append("description = %s")
            part_params.append(data['description'])

        if 'part_name' in data:
            part_fields.append("name = %s")
            part_params.append(data['part_name'])

        # Update the parent part if needed
        if part_fields:
            part_params.append(part_id)
            part_query = f"""
                UPDATE parts
                SET {', '.join(part_fields)}
                WHERE id = %s
            """
            Database.execute_query(part_query, tuple(part_params))

        # Build dynamic update query for variant fields
        fields = []
        params = []

        if 'color' in data:
            fields.append("color = %s")
            params.append(data['color'])

        if 'cost' in data:
            fields.append("cost = %s")
            params.append(data['cost'])

            # =====================================================================
            # TEMP: Track manual price reviews for temporary pricing audit
            # REMOVAL: Remove this block when running TEMP_cleanup_price_review.sql
            # =====================================================================
            fields.append("price_reviewed_by = %s")
            params.append(request.user_email)  # From JWT token via auth middleware
            fields.append("price_reviewed_at = CURRENT_TIMESTAMP")
            # =====================================================================

        if 'quantity_on_hand' in data:
            fields.append("quantity_on_hand = %s")
            params.append(data['quantity_on_hand'])

        if 'reorder_threshold' in data:
            fields.append("reorder_threshold = %s")
            params.append(data['reorder_threshold'])

        if 'reorder_quantity' in data:
            fields.append("reorder_quantity = %s")
            params.append(data['reorder_quantity'])

        if 'notes' in data:
            fields.append("notes = %s")
            params.append(data['notes'])

        if 'active' in data:
            fields.append("active = %s")
            params.append(data['active'])

        # If there are variant fields to update, update them
        if fields:
            params.append(variant_id)
            query = f"""
                UPDATE part_variants pv
                SET {', '.join(fields)}
                WHERE pv.id = %s
                RETURNING
                    pv.*,
                    (SELECT name FROM parts WHERE id = pv.part_id) as part_name,
                    (SELECT service_type FROM parts WHERE id = pv.part_id) as service_type,
                    (SELECT category FROM parts WHERE id = pv.part_id) as category,
                    (SELECT part_number FROM parts WHERE id = pv.part_id) as part_number,
                    (SELECT description FROM parts WHERE id = pv.part_id) as description
            """
            result = Database.execute_query(query, tuple(params), fetch_one=True)
        else:
            # No variant fields to update, just return the variant with updated part data
            query = """
                SELECT
                    pv.*,
                    p.name as part_name,
                    p.service_type,
                    p.category,
                    p.part_number,
                    p.description
                FROM part_variants pv
                JOIN parts p ON p.id = pv.part_id
                WHERE pv.id = %s
            """
            result = Database.execute_query(query, (variant_id,), fetch_one=True)

        if not result:
            return jsonify({'error': 'Part variant not found'}), 404

        return jsonify(result), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/variants/<int:variant_id>/adjust', methods=['POST'])
@require_admin
def adjust_inventory(variant_id):
    """Adjust inventory quantity (add or remove stock)"""
    data = request.get_json()

    adjustment = data.get('adjustment')
    reason = data.get('reason')
    notes = data.get('notes', '')

    if adjustment is None:
        return jsonify({'error': 'Adjustment value required'}), 400

    if not reason:
        return jsonify({'error': 'Reason required'}), 400

    valid_reasons = ['received', 'damaged', 'returned', 'correction', 'used']
    if reason not in valid_reasons:
        return jsonify({'error': f'Invalid reason. Must be one of: {", ".join(valid_reasons)}'}), 400

    try:
        # Update quantity
        result = Database.execute_query("""
            UPDATE part_variants
            SET quantity_on_hand = quantity_on_hand + %s
            WHERE id = %s AND active = true
            RETURNING *, (quantity_on_hand - quantity_reserved) as quantity_available
        """, (adjustment, variant_id), fetch_one=True)

        if not result:
            return jsonify({'error': 'Part variant not found'}), 404

        # TODO: Log the adjustment in an inventory_adjustments table for audit trail
        # For now, just return the updated variant

        return jsonify({
            'message': 'Inventory adjusted successfully',
            'new_quantity': result['quantity_on_hand'],
            'quantity_available': result['quantity_available'],
            'adjustment': adjustment,
            'reason': reason
        }), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/low-stock', methods=['GET'])
@require_admin
def get_low_stock():
    """Get all part variants below reorder threshold"""
    try:
        low_stock = Database.execute_query("""
            SELECT
                pv.*,
                (pv.quantity_on_hand - pv.quantity_reserved) as quantity_available,
                p.name as part_name,
                p.service_type,
                p.part_number
            FROM part_variants pv
            JOIN parts p ON pv.part_id = p.id
            WHERE pv.active = true
              AND p.active = true
              AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold
            ORDER BY (pv.quantity_on_hand - pv.quantity_reserved) ASC, p.name
        """)

        return jsonify(low_stock), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/stats', methods=['GET'])
@optional_auth
def get_inventory_stats():
    """Get inventory statistics dashboard"""
    try:
        stats = Database.execute_query("""
            SELECT
                COUNT(*) as total_skus,
                COALESCE(SUM(pv.cost * pv.quantity_on_hand), 0) as inventory_value,
                SUM(CASE WHEN (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold THEN 1 ELSE 0 END) as low_stock,
                SUM(CASE WHEN (pv.quantity_on_hand - pv.quantity_reserved) = 0 THEN 1 ELSE 0 END) as out_of_stock
            FROM part_variants pv
            JOIN parts p ON pv.part_id = p.id
            WHERE pv.active = true AND p.active = true
        """)

        if stats and len(stats) > 0:
            return jsonify(stats[0]), 200
        else:
            return jsonify({
                'total_skus': 0,
                'inventory_value': 0,
                'low_stock': 0,
                'out_of_stock': 0
            }), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# ============================================================================
# PART COMPATIBILITY
# ============================================================================

@inventory_bp.route('/parts/<int:part_id>/compatibility', methods=['GET'])
@optional_auth
def get_part_compatibility(part_id):
    """Get compatibility assignments for a part"""
    try:
        compatibility = Database.execute_query("""
            SELECT
                pc.id as compatibility_id,
                pc.part_id,
                d.id AS device_id,
                d.model_name,
                d.model_number,
                d.release_date,
                df.id AS family_id,
                df.name AS family_name,
                m.id AS manufacturer_id,
                m.name AS manufacturer_name,
                dt.id AS device_type_id,
                dt.name AS device_type_name
            FROM part_compatibility pc
            JOIN devices d ON d.id = pc.device_id
            JOIN device_families df ON df.id = d.family_id
            JOIN manufacturers m ON m.id = df.manufacturer_id
            JOIN device_types dt ON dt.id = df.device_type_id
            WHERE pc.part_id = %s
            ORDER BY m.name, df.name, d.model_name
        """, (part_id,))

        return jsonify(compatibility), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@inventory_bp.route('/parts/<int:part_id>/compatibility', methods=['POST'])
@require_admin
def add_part_compatibility(part_id):
    """Assign a part to a specific device"""
    data = request.get_json()
    device_id = data.get('device_id')

    if not device_id:
        return jsonify({'error': 'device_id is required'}), 400

    try:
        part = Database.execute_query(
            "SELECT id FROM parts WHERE id = %s AND active = true",
            (part_id,),
            fetch_one=True
        )
        if not part:
            return jsonify({'error': 'Part not found'}), 404

        device = Database.execute_query(
            "SELECT id FROM devices WHERE id = %s AND active = true",
            (device_id,),
            fetch_one=True
        )
        if not device:
            return jsonify({'error': 'Device not found'}), 404

        existing = Database.execute_query(
            "SELECT id FROM part_compatibility WHERE part_id = %s AND device_id = %s",
            (part_id, device_id),
            fetch_one=True
        )
        if existing:
            return jsonify({'message': 'Compatibility already exists'}), 200

        result = Database.execute_query(
            """
            INSERT INTO part_compatibility (part_id, device_id)
            VALUES (%s, %s)
            RETURNING id, part_id, device_id
            """,
            (part_id, device_id),
            fetch_one=True
        )

        return jsonify(result), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@inventory_bp.route('/compatibility/<int:compatibility_id>', methods=['DELETE'])
@require_admin
def delete_compatibility(compatibility_id):
    """Delete a compatibility assignment"""
    try:
        # Check if compatibility exists
        existing = Database.execute_query(
            "SELECT id FROM part_compatibility WHERE id = %s",
            (compatibility_id,),
            fetch_one=True
        )

        if not existing:
            return jsonify({'error': 'Compatibility assignment not found'}), 404

        # Delete the compatibility
        Database.execute_query(
            "DELETE FROM part_compatibility WHERE id = %s",
            (compatibility_id,)
        )

        return jsonify({'message': 'Compatibility deleted successfully'}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@inventory_bp.route('/parts/recommended', methods=['GET'])
@optional_auth
def get_recommended_parts():
    """
    Get recommended parts for a device and service combination

    Query parameters:
    - device_id: Device ID
    - service_name: Service name (e.g., "Screen Repair")
    """
    try:
        device_id = request.args.get('device_id', type=int)
        service_name = request.args.get('service_name')

        if not device_id:
            return jsonify({'error': 'device_id is required'}), 400

        if not service_name:
            return jsonify({'error': 'service_name is required'}), 400

        # Get parts for this device and service combination
        # This uses the same logic as customer pricing
        parts = Database.execute_query("""
            SELECT DISTINCT
                pv.id,
                pv.sku,
                p.name as part_name,
                pv.quantity_on_hand as stock_level,
                pv.cost as price
            FROM part_variants pv
            JOIN parts p ON pv.part_id = p.id
            JOIN service_offer_parts sop ON pv.id = sop.part_variant_id
            JOIN service_offers so ON sop.service_offer_id = so.id
            JOIN service_variants sv ON so.service_variant_id = sv.id
            JOIN service_catalog sc ON sv.service_id = sc.id
            WHERE so.device_id = %s
            AND LOWER(sc.name) LIKE LOWER(%s)
            AND so.active = true
            ORDER BY p.name
        """, (device_id, f'%{service_name}%'))

        result = [dict(part) for part in parts] if parts else []

        return jsonify(result), 200

    except Exception as e:
        logging.error(f"Error fetching recommended parts: {str(e)}")
        return jsonify({'error': str(e)}), 500