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
import traceback
from datetime import datetime

barcode_bp = Blueprint('barcode', __name__)


@barcode_bp.route('/save_barcode_generation', methods=['POST'])
@jwt_required()
@role_required('admin', 'user', 'manager')
def save_barcode_generation():
    """
    Save barcode generation history to database with location tracking
    
    Request body:
    {
        "products": [
            {
                "product_id": 1,
                "variation_id": 2 (optional),
                "sku": "SKU001",
                "product_name": "Product Name",
                "quantity": 10,
                "barcode_type": "CODE128"
            }
        ],
        "store_id": 1 (optional),
        "warehouse_id": 1 (optional)
    }
    """
    try:
        data = request.get_json()
        
        if not data or 'products' not in data:
            return jsonify({'error': 'No products provided'}), 400
        
        products = data['products']
        
        if not isinstance(products, list) or len(products) == 0:
            return jsonify({'error': 'Products must be a non-empty array'}), 400
        
        # Get location info (optional)
        store_id = data.get('store_id')
        warehouse_id = data.get('warehouse_id')
        
        # Get user ID from JWT token
        user_id = get_jwt_identity()
        
        # Convert user_id to int if it's a string
        try:
            user_id = int(user_id)
        except (ValueError, TypeError):
            print(f"❌ Warning: Could not convert user_id to int: {user_id}")
            return jsonify({'error': 'Invalid user ID format'}), 400
        
        # Convert location IDs to int if provided
        if store_id:
            try:
                store_id = int(store_id)
            except (ValueError, TypeError):
                print(f"⚠️ Warning: Invalid store_id: {store_id}")
                store_id = None
        
        if warehouse_id:
            try:
                warehouse_id = int(warehouse_id)
            except (ValueError, TypeError):
                print(f"⚠️ Warning: Invalid warehouse_id: {warehouse_id}")
                warehouse_id = None
        
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            conn.start_transaction()
            
            print(f"\n{'='*80}")
            print(f"💾 SAVING BARCODE GENERATION")
            print(f"{'='*80}")
            print(f"User ID: {user_id}")
            print(f"Store ID: {store_id}")
            print(f"Warehouse ID: {warehouse_id}")
            print(f"Products: {len(products)}")
            
            # Validate location IDs if provided
            if store_id:
                cursor.execute("SELECT id, store_name FROM stores WHERE id = %s", (store_id,))
                store = cursor.fetchone()
                if not store:
                    print(f"⚠️ Warning: Store ID {store_id} not found, setting to NULL")
                    store_id = None
                else:
                    print(f"✅ Store validated: {store['store_name']}")
            
            if warehouse_id:
                cursor.execute("SELECT id, warehouse_name FROM warehouses WHERE id = %s", (warehouse_id,))
                warehouse = cursor.fetchone()
                if not warehouse:
                    print(f"⚠️ Warning: Warehouse ID {warehouse_id} not found, setting to NULL")
                    warehouse_id = None
                else:
                    print(f"✅ Warehouse validated: {warehouse['warehouse_name']}")
            
            records_saved = 0
            failed_records = []
            
            for idx, product in enumerate(products):
                try:
                    # Validate required fields
                    if not product.get('product_id'):
                        failed_records.append({
                            'index': idx,
                            'reason': 'Missing product_id'
                        })
                        continue
                    
                    if not product.get('sku'):
                        failed_records.append({
                            'index': idx,
                            'reason': 'Missing SKU'
                        })
                        continue
                    
                    if not product.get('product_name'):
                        failed_records.append({
                            'index': idx,
                            'reason': 'Missing product_name'
                        })
                        continue
                    
                    # Extract values
                    product_id = int(product.get('product_id'))
                    variation_id = int(product.get('variation_id')) if product.get('variation_id') else None
                    sku = str(product.get('sku')).strip()
                    product_name = str(product.get('product_name')).strip()
                    quantity = int(product.get('quantity', 1))
                    barcode_type = str(product.get('barcode_type', 'CODE128'))
                    
                    # Validate quantity
                    if quantity < 1:
                        quantity = 1
                    
                    # Verify product exists
                    cursor.execute("""
                        SELECT id, product_name FROM products WHERE id = %s
                    """, (product_id,))
                    
                    product_check = cursor.fetchone()
                    if not product_check:
                        failed_records.append({
                            'index': idx,
                            'product_id': product_id,
                            'reason': 'Product not found in database'
                        })
                        continue
                    
                    # If variation_id provided, verify it exists
                    if variation_id:
                        cursor.execute("""
                            SELECT id, variation_name FROM product_variations 
                            WHERE id = %s AND product_id = %s
                        """, (variation_id, product_id))
                        
                        variation_check = cursor.fetchone()
                        if not variation_check:
                            failed_records.append({
                                'index': idx,
                                'variation_id': variation_id,
                                'reason': 'Variation not found in database'
                            })
                            continue
                        
                        print(f"  📦 Product {idx+1}: {product_check['product_name']} - {variation_check['variation_name']} (Qty: {quantity})")
                    else:
                        print(f"  📦 Product {idx+1}: {product_check['product_name']} (Qty: {quantity})")
                    
                    # Insert barcode generation record WITH LOCATION
                    cursor.execute("""
                        INSERT INTO barcode_generations (
                            product_id, variation_id, sku, product_name,
                            quantity, barcode_type, store_id, warehouse_id,
                            generated_by, generated_at
                        )
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                    """, (
                        product_id,
                        variation_id,
                        sku,
                        product_name,
                        quantity,
                        barcode_type,
                        store_id,
                        warehouse_id,
                        user_id
                    ))
                    
                    records_saved += 1
                    
                except Exception as e:
                    print(f"❌ Error processing product at index {idx}: {e}")
                    failed_records.append({
                        'index': idx,
                        'reason': str(e)
                    })
                    continue
            
            conn.commit()
            
            print(f"\n✅ Transaction committed successfully!")
            print(f"   Records saved: {records_saved}/{len(products)}")
            if failed_records:
                print(f"   Failed records: {len(failed_records)}")
            print(f"{'='*80}\n")
            
            response_data = {
                'success': True,
                'message': f'{records_saved} barcode generation(s) saved successfully',
                'records_saved': records_saved,
                'total_requested': len(products),
                'location': {
                    'store_id': store_id,
                    'warehouse_id': warehouse_id
                },
                'user_id': user_id
            }
            
            if failed_records:
                response_data['failed_records'] = failed_records
                response_data['failed_count'] = len(failed_records)
                response_data['message'] = f'{records_saved} of {len(products)} barcode generation(s) saved successfully'
            
            return jsonify(response_data), 201
            
        except mysql.connector.Error as err:
            conn.rollback()
            print(f"❌ Database Error in save_barcode_generation: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Database error: {str(err)}'}), 500
        
        except ValueError as err:
            conn.rollback()
            print(f"❌ Value Error in save_barcode_generation: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Invalid data format: {str(err)}'}), 400
        
        except Exception as e:
            conn.rollback()
            print(f"❌ Error in save_barcode_generation: {e}")
            traceback.print_exc()
            return jsonify({'error': f'Failed to save: {str(e)}'}), 500
        
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    except Exception as e:
        print(f"❌ Error processing request: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Request processing error: {str(e)}'}), 500


@barcode_bp.route('/barcode_history', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'user')
def get_barcode_history():
    """
    Get barcode generation history with pagination and filters
    
    Query parameters:
    - page: int (default 1)
    - limit: int (default 50, max 1000)
    - product_id: int (optional)
    - store_id: int (optional) - FILTER BY STORE
    - warehouse_id: int (optional)
    - date_from: YYYY-MM-DD (optional)
    - date_to: YYYY-MM-DD (optional)
    - user_id: int (optional)
    - search: string (optional) - searches product_name and SKU
    """
    try:
        # Get query parameters
        page = int(request.args.get('page', 1))
        limit = int(request.args.get('limit', 50))
        product_id = request.args.get('product_id')
        store_id = request.args.get('store_id')  # ✅ STORE FILTER
        warehouse_id = request.args.get('warehouse_id')
        date_from = request.args.get('date_from')
        date_to = request.args.get('date_to')
        user_id = request.args.get('user_id')
        search = request.args.get('search', '').strip()
        
        # Validate pagination
        if page < 1:
            page = 1
        if limit < 1 or limit > 1000:
            limit = 50
        
        offset = (page - 1) * limit
        
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            print(f"\n{'='*80}")
            print(f"🔍 BARCODE HISTORY REQUEST")
            print(f"{'='*80}")
            print(f"Page: {page}, Limit: {limit}")
            if store_id:
                print(f"✅ Store Filter: {store_id}")
            if warehouse_id:
                print(f"Warehouse Filter: {warehouse_id}")
            if search:
                print(f"Search: '{search}'")
            if date_from or date_to:
                print(f"Date Range: {date_from or 'Any'} to {date_to or 'Any'}")
            
            # Build WHERE clause
            where_clauses = []
            params = []
            
            if product_id:
                where_clauses.append("bg.product_id = %s")
                params.append(int(product_id))
            
            # ✅ STORE FILTER - MOST IMPORTANT
            if store_id:
                where_clauses.append("bg.store_id = %s")
                params.append(int(store_id))
            
            if warehouse_id:
                where_clauses.append("bg.warehouse_id = %s")
                params.append(int(warehouse_id))
            
            if date_from:
                where_clauses.append("DATE(bg.generated_at) >= %s")
                params.append(date_from)
            
            if date_to:
                where_clauses.append("DATE(bg.generated_at) <= %s")
                params.append(date_to)
            
            if user_id:
                where_clauses.append("bg.generated_by = %s")
                params.append(int(user_id))
            
            if search:
                where_clauses.append("(bg.product_name LIKE %s OR bg.sku LIKE %s)")
                search_param = f"%{search}%"
                params.extend([search_param, search_param])
            
            where_sql = "WHERE " + " AND ".join(where_clauses) if where_clauses else ""
            
            # Get total count
            count_query = f"""
                SELECT COUNT(*) as total
                FROM barcode_generations bg
                {where_sql}
            """
            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']
            
            print(f"Total Records Found: {total}")
            
            # Get paginated results with JOINs
            query_params = params + [limit, offset]
            data_query = f"""
                SELECT 
                    bg.id,
                    bg.product_id,
                    bg.variation_id,
                    bg.sku,
                    bg.product_name,
                    bg.quantity,
                    bg.barcode_type,
                    bg.store_id,
                    bg.warehouse_id,
                    bg.generated_by,
                    bg.generated_at,
                    p.product_name as full_product_name,
                    p.product_type,
                    pv.variation_name,
                    pv.variation_type,
                    s.store_name,
                    w.warehouse_name,
                    u.name as generated_by_name,
                    u.email as generated_by_email
                FROM barcode_generations bg
                LEFT JOIN products p ON bg.product_id = p.id
                LEFT JOIN product_variations pv ON bg.variation_id = pv.id
                LEFT JOIN stores s ON bg.store_id = s.id
                LEFT JOIN warehouses w ON bg.warehouse_id = w.id
                LEFT JOIN users u ON bg.generated_by = u.id
                {where_sql}
                ORDER BY bg.generated_at DESC
                LIMIT %s OFFSET %s
            """
            cursor.execute(data_query, query_params)
            records = cursor.fetchall()
            
            print(f"Records Returned: {len(records)}")
            print(f"{'='*80}\n")
            
            # Format datetime objects
            for record in records:
                if record['generated_at']:
                    record['generated_at'] = record['generated_at'].isoformat()
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit if total > 0 else 0
            
            return jsonify({
                'success': True,
                'data': records,
                'pagination': {
                    'total': total,
                    'page': page,
                    'limit': limit,
                    'pages': total_pages,
                    'has_next': page < total_pages,
                    'has_prev': page > 1
                },
                'filters': {
                    'store_id': int(store_id) if store_id else None,
                    'warehouse_id': int(warehouse_id) if warehouse_id else None,
                    'search': search if search else None,
                    'date_from': date_from,
                    'date_to': date_to
                }
            }), 200
            
        except mysql.connector.Error as err:
            print(f"❌ Database Error in get_barcode_history: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Database error: {str(err)}'}), 500
        
        except ValueError as err:
            print(f"❌ Value Error in get_barcode_history: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Invalid parameter: {str(err)}'}), 400
        
        except Exception as e:
            print(f"❌ Error in get_barcode_history: {e}")
            traceback.print_exc()
            return jsonify({'error': str(e)}), 500
        
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    except Exception as e:
        print(f"❌ Error processing request: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Request processing error: {str(e)}'}), 500


@barcode_bp.route('/update_barcode_generation/<int:id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'manager', 'user')
def update_barcode_generation(id):
    """
    Update barcode generation quantity
    
    Request body:
    {
        "quantity": 10
    }
    """
    try:
        data = request.get_json()
        
        if not data or 'quantity' not in data:
            return jsonify({'error': 'Quantity is required'}), 400
        
        quantity = int(data['quantity'])
        
        if quantity < 1:
            return jsonify({'error': 'Quantity must be at least 1'}), 400
        
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            print(f"\n{'='*80}")
            print(f"✏️ UPDATING BARCODE GENERATION")
            print(f"{'='*80}")
            print(f"Record ID: {id}")
            print(f"New Quantity: {quantity}")
            
            # Check if record exists
            cursor.execute("""
                SELECT id, product_name, quantity, store_id
                FROM barcode_generations 
                WHERE id = %s
            """, (id,))
            
            record = cursor.fetchone()
            
            if not record:
                print(f"❌ Record not found")
                print(f"{'='*80}\n")
                return jsonify({'error': 'Record not found'}), 404
            
            old_quantity = record['quantity']
            
            print(f"Product: {record['product_name']}")
            print(f"Old Quantity: {old_quantity} → New Quantity: {quantity}")
            
            # Update quantity
            cursor.execute("""
                UPDATE barcode_generations 
                SET quantity = %s 
                WHERE id = %s
            """, (quantity, id))
            
            conn.commit()
            
            print(f"✅ Record updated successfully")
            print(f"{'='*80}\n")
            
            return jsonify({
                'success': True,
                'message': 'Barcode generation updated successfully',
                'updated': {
                    'id': id,
                    'product_name': record['product_name'],
                    'old_quantity': old_quantity,
                    'new_quantity': quantity,
                    'store_id': record['store_id']
                }
            }), 200
            
        except mysql.connector.Error as err:
            conn.rollback()
            print(f"❌ Database Error in update_barcode_generation: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Database error: {str(err)}'}), 500
        
        except Exception as e:
            conn.rollback()
            print(f"❌ Error in update_barcode_generation: {e}")
            traceback.print_exc()
            return jsonify({'error': str(e)}), 500
        
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    except ValueError:
        return jsonify({'error': 'Invalid quantity format'}), 400
    
    except Exception as e:
        print(f"❌ Error processing request: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Request processing error: {str(e)}'}), 500


@barcode_bp.route('/delete_barcode_history/<int:id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_barcode_history(id):
    """
    Delete a barcode generation record by ID (admin only)
    """
    try:
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            print(f"\n{'='*80}")
            print(f"🗑️ DELETING BARCODE GENERATION")
            print(f"{'='*80}")
            print(f"Record ID: {id}")
            
            # Check if record exists
            cursor.execute("""
                SELECT id, product_name, quantity, store_id, warehouse_id
                FROM barcode_generations 
                WHERE id = %s
            """, (id,))
            
            record = cursor.fetchone()
            
            if not record:
                print(f"❌ Record not found")
                print(f"{'='*80}\n")
                return jsonify({'error': 'Record not found'}), 404
            
            print(f"Product: {record['product_name']}")
            print(f"Quantity: {record['quantity']}")
            print(f"Store ID: {record['store_id']}")
            
            # Delete the record
            cursor.execute("""
                DELETE FROM barcode_generations WHERE id = %s
            """, (id,))
            
            conn.commit()
            
            print(f"✅ Record deleted successfully")
            print(f"{'='*80}\n")
            
            return jsonify({
                'success': True,
                'message': 'Barcode generation record deleted successfully',
                'deleted': {
                    'id': id,
                    'product_name': record['product_name'],
                    'quantity': record['quantity'],
                    'store_id': record['store_id'],
                    'warehouse_id': record['warehouse_id']
                }
            }), 200
            
        except mysql.connector.Error as err:
            conn.rollback()
            print(f"❌ Database Error in delete_barcode_history: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Database error: {str(err)}'}), 500
        
        except Exception as e:
            conn.rollback()
            print(f"❌ Error in delete_barcode_history: {e}")
            traceback.print_exc()
            return jsonify({'error': str(e)}), 500
        
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    except Exception as e:
        print(f"❌ Error processing request: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Request processing error: {str(e)}'}), 500


@barcode_bp.route('/barcode_stats', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'user')
def get_barcode_stats():
    """
    Get barcode generation statistics
    
    Query parameters:
    - store_id: int (optional) - Filter by store
    - date_from: YYYY-MM-DD (optional)
    - date_to: YYYY-MM-DD (optional)
    """
    try:
        store_id = request.args.get('store_id')
        date_from = request.args.get('date_from')
        date_to = request.args.get('date_to')
        
        conn = get_db_connection()
        if conn is None:
            return jsonify({'error': 'Database connection failed'}), 500
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Build WHERE clause
            where_clauses = []
            params = []
            
            if store_id:
                where_clauses.append("store_id = %s")
                params.append(int(store_id))
            
            if date_from:
                where_clauses.append("DATE(generated_at) >= %s")
                params.append(date_from)
            
            if date_to:
                where_clauses.append("DATE(generated_at) <= %s")
                params.append(date_to)
            
            where_sql = "WHERE " + " AND ".join(where_clauses) if where_clauses else ""
            
            # Get statistics
            stats_query = f"""
                SELECT 
                    COUNT(*) as total_records,
                    SUM(quantity) as total_quantity,
                    COUNT(DISTINCT product_id) as unique_products,
                    COUNT(DISTINCT generated_by) as unique_users,
                    MIN(generated_at) as first_generation,
                    MAX(generated_at) as last_generation
                FROM barcode_generations
                {where_sql}
            """
            cursor.execute(stats_query, params)
            stats = cursor.fetchone()
            
            # Format datetime objects
            if stats['first_generation']:
                stats['first_generation'] = stats['first_generation'].isoformat()
            if stats['last_generation']:
                stats['last_generation'] = stats['last_generation'].isoformat()
            
            return jsonify({
                'success': True,
                'stats': stats,
                'filters': {
                    'store_id': int(store_id) if store_id else None,
                    'date_from': date_from,
                    'date_to': date_to
                }
            }), 200
            
        except mysql.connector.Error as err:
            print(f"❌ Database Error in get_barcode_stats: {err}")
            traceback.print_exc()
            return jsonify({'error': f'Database error: {str(err)}'}), 500
        
        except Exception as e:
            print(f"❌ Error in get_barcode_stats: {e}")
            traceback.print_exc()
            return jsonify({'error': str(e)}), 500
        
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    except Exception as e:
        print(f"❌ Error processing request: {e}")
        traceback.print_exc()
        return jsonify({'error': f'Request processing error: {str(e)}'}), 500