"""
Inventory Management Routes
Parts and part variants with stock tracking
"""
from flask import Blueprint, request, jsonify
from app.models.database import Database
from app.middleware.auth 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

@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')

    query = """
        WITH part_devices AS (
            -- Get all device-level compatibility
            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 (
            -- Aggregate devices by family for each part
            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 (
            -- Roll up families into JSON array per part
            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.supplier_id,
            pv.active,
            pv.created_at,
            pv.updated_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 pv.active = true AND p.active = true
    """
    params = []

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

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

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

    query += " ORDER BY p.name, pv.grade, pv.color"

    try:
        variants = Database.execute_query(query, params if params else None)
        return jsonify(variants), 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"""
    data = request.get_json()

    try:
        # Build dynamic update query
        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'])

        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 'active' in data:
            fields.append("active = %s")
            params.append(data['active'])

        if not fields:
            return jsonify({'error': 'No fields to update'}), 400

        params.append(variant_id)

        query = f"""
            UPDATE part_variants
            SET {', '.join(fields)}
            WHERE id = %s
            RETURNING *
        """

        result = Database.execute_query(query, tuple(params), 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


# ============================================================================
# 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