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


customer_bp = Blueprint('customer', __name__)


# ==========================================
# CREATE CUSTOMER
# ==========================================
@customer_bp.route('/create_customer', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def create_customer():
    """
    Create a new customer
    Required: name
    Optional: address, email, contact, dob, gender
    """
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data provided'}), 400
    except Exception as e:
        return jsonify({'error': 'Invalid JSON data', 'message': str(e)}), 400

    # Extract fields
    name = data.get('name', '').strip()
    address = data.get('address', '').strip()
    email = data.get('email', '').strip()
    contact = data.get('contact', '').strip()
    dob = data.get('dob')
    gender = data.get('gender', '').strip()

    # Validation - only name is required
    if not name:
        return jsonify({'error': 'Customer name is required'}), 400

    # Email validation (if provided)
    if email:
        import re
        email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
        if not re.match(email_pattern, email):
            return jsonify({'error': 'Invalid email format'}), 400

    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        # Check for duplicate (name + contact if both provided)
        if name and contact:
            cursor.execute(
                "SELECT id FROM customers WHERE name = %s AND contact = %s",
                (name, contact)
            )
            existing = cursor.fetchone()
            if existing:
                return jsonify({
                    'error': 'Customer already exists with this name and contact',
                    'duplicate': True
                }), 409

        # Insert customer
        sql = """
            INSERT INTO customers (name, address, email, contact, dob, gender, created_at)
            VALUES (%s, %s, %s, %s, %s, %s, NOW())
        """
        values = (
            name,
            address if address else None,
            email if email else None,
            contact if contact else None,
            dob if dob else None,
            gender if gender else None
        )

        cursor.execute(sql, values)
        conn.commit()

        customer_id = cursor.lastrowid

        print(f"✅ Customer created successfully: ID {customer_id}, Name: {name}")

        return jsonify({
            'success': True,
            'message': 'Customer created successfully',
            'customer_id': customer_id,
            'customer_name': name
        }), 201

    except mysql.connector.Error as err:
        print(f"❌ MySQL Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    finally:
        cursor.close()
        conn.close()


# ==========================================
# GET ALL CUSTOMERS
# ==========================================
@customer_bp.route('/get_customers', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_customers():
    """
    Get all customers with pagination
    """
    try:
        page = int(request.args.get('page', 1))
        per_page = int(request.args.get('per_page', 20))
        search = request.args.get('search', '').strip()
        
        offset = (page - 1) * per_page

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)

        # Build WHERE clause
        where_clause = ""
        params = []

        if search:
            where_clause = """
                WHERE name LIKE %s 
                OR contact LIKE %s 
                OR email LIKE %s
            """
            search_pattern = f"%{search}%"
            params = [search_pattern, search_pattern, search_pattern]

        # Get total count
        count_sql = f"SELECT COUNT(*) as total FROM customers {where_clause}"
        cursor.execute(count_sql, params)
        total = cursor.fetchone()['total']

        # Get customers
        sql = f"""
            SELECT 
                id,
                name,
                address,
                email,
                contact,
                dob,
                gender,
                created_at
            FROM customers
            {where_clause}
            ORDER BY created_at DESC
            LIMIT %s OFFSET %s
        """
        cursor.execute(sql, params + [per_page, offset])
        customers = cursor.fetchall()

        # Format dates
        for customer in customers:
            if customer['dob']:
                customer['dob'] = customer['dob'].isoformat()
            if customer['created_at']:
                customer['created_at'] = customer['created_at'].strftime('%Y-%m-%d %H:%M:%S')

        return jsonify({
            'success': True,
            'customers': customers,
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': total,
                'pages': (total + per_page - 1) // per_page
            }
        }), 200

    except Exception as e:
        print(f"❌ Error fetching customers: {e}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# ==========================================
# GET SINGLE CUSTOMER
# ==========================================
@customer_bp.route('/get_customer/<int:customer_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def get_customer(customer_id):
    """
    Get single customer by ID
    """
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        sql = """
            SELECT 
                id,
                name,
                address,
                email,
                contact,
                dob,
                gender,
                created_at
            FROM customers
            WHERE id = %s
        """
        cursor.execute(sql, (customer_id,))
        customer = cursor.fetchone()

        if not customer:
            return jsonify({'error': 'Customer not found'}), 404

        # Format dates
        if customer['dob']:
            customer['dob'] = customer['dob'].isoformat()
        if customer['created_at']:
            customer['created_at'] = customer['created_at'].strftime('%Y-%m-%d %H:%M:%S')

        return jsonify({
            'success': True,
            'customer': customer
        }), 200

    except Exception as e:
        print(f"❌ Error fetching customer: {e}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# ==========================================
# UPDATE CUSTOMER
# ==========================================
@customer_bp.route('/update_customer/<int:customer_id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'cashier')
def update_customer(customer_id):
    """
    Update customer details
    Required: name
    Optional: address, email, contact, dob, gender
    """
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data provided'}), 400
    except Exception as e:
        return jsonify({'error': 'Invalid JSON data', 'message': str(e)}), 400

    # Extract fields
    name = data.get('name', '').strip()
    address = data.get('address', '').strip()
    email = data.get('email', '').strip()
    contact = data.get('contact', '').strip()
    dob = data.get('dob')
    gender = data.get('gender', '').strip()

    # Validation
    if not name:
        return jsonify({'error': 'Customer name is required'}), 400

    # Email validation (if provided)
    if email:
        import re
        email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
        if not re.match(email_pattern, email):
            return jsonify({'error': 'Invalid email format'}), 400

    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        # Check if customer exists
        cursor.execute("SELECT id FROM customers WHERE id = %s", (customer_id,))
        if not cursor.fetchone():
            return jsonify({'error': 'Customer not found'}), 404

        # Update customer
        sql = """
            UPDATE customers
            SET name = %s,
                address = %s,
                email = %s,
                contact = %s,
                dob = %s,
                gender = %s
            WHERE id = %s
        """
        values = (
            name,
            address if address else None,
            email if email else None,
            contact if contact else None,
            dob if dob else None,
            gender if gender else None,
            customer_id
        )

        cursor.execute(sql, values)
        conn.commit()

        print(f"✅ Customer updated successfully: ID {customer_id}, Name: {name}")

        return jsonify({
            'success': True,
            'message': 'Customer updated successfully',
            'customer_id': customer_id,
            'customer_name': name
        }), 200

    except mysql.connector.Error as err:
        print(f"❌ MySQL Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    finally:
        cursor.close()
        conn.close()


# ==========================================
# DELETE CUSTOMER
# ==========================================
@customer_bp.route('/delete_customer/<int:customer_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_customer(customer_id):
    """
    Delete customer (admin only)
    """
    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        # Check if customer exists
        cursor.execute("SELECT name FROM customers WHERE id = %s", (customer_id,))
        customer = cursor.fetchone()
        
        if not customer:
            return jsonify({'error': 'Customer not found'}), 404

        customer_name = customer['name']

        # Delete customer
        cursor.execute("DELETE FROM customers WHERE id = %s", (customer_id,))
        conn.commit()

        print(f"✅ Customer deleted successfully: ID {customer_id}, Name: {customer_name}")

        return jsonify({
            'success': True,
            'message': 'Customer deleted successfully',
            'customer_id': customer_id,
            'customer_name': customer_name
        }), 200

    except mysql.connector.IntegrityError as err:
        print(f"❌ Integrity Error: {err}")
        return jsonify({
            'error': 'Cannot delete customer. Customer has related records (sales, etc.)'
        }), 400

    except mysql.connector.Error as err:
        print(f"❌ MySQL Error: {err}")
        traceback.print_exc()
        return jsonify({'error': f'Database error: {str(err)}'}), 500

    finally:
        cursor.close()
        conn.close()


# ==========================================
# SEARCH CUSTOMERS
# ==========================================
@customer_bp.route('/search_customers', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager', 'cashier')
def search_customers():
    """
    Search customers by name, contact, or email
    """
    query = request.args.get('query', '').strip()
    limit = min(int(request.args.get('limit', 20)), 50)

    if not query:
        return jsonify({'error': 'Search query is required'}), 400

    conn = get_db_connection()
    if not conn:
        return jsonify({'error': 'Database connection failed'}), 500

    cursor = conn.cursor(dictionary=True)

    try:
        search_pattern = f"%{query}%"

        sql = """
            SELECT
                id,
                name,
                address,
                email,
                contact,
                dob,
                gender,
                created_at
            FROM customers
            WHERE name LIKE %s
               OR contact LIKE %s
               OR email LIKE %s
            ORDER BY name ASC
            LIMIT %s
        """
        cursor.execute(sql, (search_pattern, search_pattern, search_pattern, limit))
        customers = cursor.fetchall()

        # Format dates
        for customer in customers:
            if customer['dob']:
                customer['dob'] = customer['dob'].isoformat()
            if customer['created_at']:
                customer['created_at'] = customer['created_at'].strftime('%Y-%m-%d %H:%M:%S')

        return jsonify({
            'success': True,
            'customers': customers,
            'count': len(customers)
        }), 200

    except Exception as e:
        print(f"❌ Error searching customers: {e}")
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# ==========================================
# CUSTOMER STATISTICS
# ==========================================
@customer_bp.route('/customers/count', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_customer_count():
    """Get total customer count"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM customers")
        (count,) = cursor.fetchone()
        return jsonify({'total_customers': count}), 200
    except Exception as e:
        traceback.print_exc()
        return jsonify({'error': f'Failed to get count: {str(e)}'}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@customer_bp.route('/customers/today-count', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_today_registered_count():
    """Get today's registered customer count"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM customers WHERE DATE(created_at) = CURDATE()")
        (count,) = cursor.fetchone()
        return jsonify({'today_registered': count}), 200
    except Exception as e:
        traceback.print_exc()
        return jsonify({'error': f'Failed to get today count: {str(e)}'}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


@customer_bp.route('/customers/monthly-count', methods=['GET'])
@jwt_required()
@role_required('admin', 'manager')
def get_monthly_registered_count():
    """Get this month's registered customer count"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT COUNT(*) 
            FROM customers 
            WHERE MONTH(created_at) = MONTH(CURDATE()) 
              AND YEAR(created_at) = YEAR(CURDATE())
        """)
        (count,) = cursor.fetchone()
        return jsonify({'monthly_registered': count}), 200
    except Exception as e:
        traceback.print_exc()
        return jsonify({'error': f'Failed to get monthly count: {str(e)}'}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            
            
            
            