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

stores_bp = Blueprint('stores', __name__)

# -------------------------
#  CREATE STORE
# -------------------------
@stores_bp.route('/create_store', methods=['POST'])
@jwt_required()
@role_required('admin')
def create_store():
    """Create a new store"""
    data = request.get_json()
    store_name = data.get('store_name')
    email = data.get('email')
    contact = data.get('contact')
    address = data.get('address')

    if not store_name:
        return jsonify({"error": "store_name is required"}), 400

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # Check if store name already exists
        cursor.execute("SELECT id FROM stores WHERE store_name = %s", (store_name,))
        if cursor.fetchone():
            return jsonify({"error": "Store name already exists"}), 409

        cursor.execute("""
            INSERT INTO stores (store_name, email, contact, address, is_active)
            VALUES (%s, %s, %s, %s, %s)
        """, (store_name, email, contact, address, 1))

        conn.commit()
        store_id = cursor.lastrowid

        return jsonify({
            "message": "Store created successfully",
            "store_id": store_id
        }), 201

    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# -------------------------
#  EDIT STORE
# -------------------------
@stores_bp.route('/edit_store/<int:store_id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def edit_store(store_id):
    """Update store details"""
    data = request.get_json()
    store_name = data.get('store_name')
    email = data.get('email')
    contact = data.get('contact')
    address = data.get('address')
    is_active = data.get('is_active')

    if all(v is None for v in [store_name, email, contact, address, is_active]):
        return jsonify({"error": "No fields to update"}), 400

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # Check if store exists
        cursor.execute("SELECT * FROM stores WHERE id = %s", (store_id,))
        store = cursor.fetchone()
        if not store:
            return jsonify({"error": "Store not found"}), 404

        # Check for duplicate store name (if updating name)
        if store_name and store_name != store['store_name']:
            cursor.execute("SELECT id FROM stores WHERE store_name = %s AND id != %s", (store_name, store_id))
            if cursor.fetchone():
                return jsonify({"error": "Store name already exists"}), 409

        # Build dynamic update query
        update_fields = []
        values = []

        if store_name is not None:
            update_fields.append("store_name = %s")
            values.append(store_name)

        if email is not None:
            update_fields.append("email = %s")
            values.append(email)

        if contact is not None:
            update_fields.append("contact = %s")
            values.append(contact)

        if address is not None:
            update_fields.append("address = %s")
            values.append(address)

        if is_active is not None:
            update_fields.append("is_active = %s")
            values.append(1 if is_active else 0)

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

        values.append(store_id)

        query = f"UPDATE stores SET {', '.join(update_fields)}, updated_at = NOW() WHERE id = %s"
        cursor.execute(query, tuple(values))
        conn.commit()

        return jsonify({"message": "Store updated successfully"}), 200

    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# -------------------------
#  VIEW ALL STORES WITH USER COUNT
# -------------------------
@stores_bp.route('/all_stores', methods=['GET'])
@jwt_required()
@role_required('admin','cashier')
def all_stores():
    """Get all stores with cashier count"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # Get all stores with cashier count
        cursor.execute("""
            SELECT 
                s.id,
                s.store_name,
                s.email,
                s.contact,
                s.address,
                s.is_active,
                s.created_at,
                s.updated_at,
                COUNT(CASE WHEN u.role = 'cashier' THEN 1 END) as cashier_count
            FROM stores s
            LEFT JOIN users u ON u.store_id = s.id
            GROUP BY s.id, s.store_name, s.email, s.contact, s.address, s.is_active, s.created_at, s.updated_at
            ORDER BY s.id DESC
        """)
        stores = cursor.fetchall()
        
        # Convert datetime objects to string format
        for store in stores:
            if store.get('created_at'):
                store['created_at'] = store['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if store.get('updated_at'):
                store['updated_at'] = store['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify(stores), 200

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

    finally:
        cursor.close()
        conn.close()


# -------------------------
#  GET SINGLE STORE
# -------------------------
@stores_bp.route('/store/<int:store_id>', methods=['GET'])
@jwt_required()
def get_store(store_id):
    """Get a single store by ID"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT 
                s.*,
                COUNT(CASE WHEN u.role = 'cashier' THEN 1 END) as cashier_count
            FROM stores s
            LEFT JOIN users u ON u.store_id = s.id
            WHERE s.id = %s
            GROUP BY s.id
        """, (store_id,))
        
        store = cursor.fetchone()
        
        if not store:
            return jsonify({"error": "Store not found"}), 404
        
        # Convert datetime objects to string format
        if store.get('created_at'):
            store['created_at'] = store['created_at'].strftime('%Y-%m-%d %H:%M:%S')
        if store.get('updated_at'):
            store['updated_at'] = store['updated_at'].strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify(store), 200

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

    finally:
        cursor.close()
        conn.close()


# -------------------------
#  GET ACTIVE STORES ONLY
# -------------------------
@stores_bp.route('/active_stores', methods=['GET'])
@jwt_required()
def active_stores():
    """Get only active stores"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT 
                s.id,
                s.store_name,
                s.email,
                s.contact,
                s.address,
                s.is_active,
                COUNT(CASE WHEN u.role = 'cashier' THEN 1 END) as cashier_count
            FROM stores s
            LEFT JOIN users u ON u.store_id = s.id
            WHERE s.is_active = 1
            GROUP BY s.id, s.store_name, s.email, s.contact, s.address, s.is_active
            ORDER BY s.store_name ASC
        """)
        stores = cursor.fetchall()
        
        return jsonify(stores), 200

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

    finally:
        cursor.close()
        conn.close()


# -------------------------
#  DELETE STORE (FIXED - COMPLETE)
# -------------------------
@stores_bp.route('/delete_store/<int:store_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_store(store_id):
    """Delete a store with comprehensive checks"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # Check if store exists
        cursor.execute("SELECT * FROM stores WHERE id = %s", (store_id,))
        store = cursor.fetchone()
        if not store:
            return jsonify({"error": "Store not found"}), 404

        # ===========================
        # 1. Check Users
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM users WHERE store_id = %s", (store_id,))
        user_count = cursor.fetchone()['count']
        
        if user_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {user_count} associated user(s). Please reassign or delete users first."
            }), 409

        # ===========================
        # 2. Check Sales (invoice_sale)
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM invoice_sale WHERE store_id = %s", (store_id,))
        sales_count = cursor.fetchone()['count']
        
        if sales_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {sales_count} sales transactions."
            }), 409

        # ===========================
        # 3. Check Purchase Orders
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM purchase_orders WHERE store_id = %s", (store_id,))
        purchase_count = cursor.fetchone()['count']
        
        if purchase_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {purchase_count} purchase orders."
            }), 409

        # ===========================
        # 4. Check GRN
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM grn WHERE store_id = %s", (store_id,))
        grn_count = cursor.fetchone()['count']
        
        if grn_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {grn_count} GRN records."
            }), 409

        # ===========================
        # 5. Check Quotations
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM quotations WHERE store_id = %s", (store_id,))
        quotation_count = cursor.fetchone()['count']
        
        if quotation_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {quotation_count} quotations."
            }), 409

        # ===========================
        # 6. Check Expenses
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM expenses WHERE store_id = %s", (store_id,))
        expense_count = cursor.fetchone()['count']
        
        if expense_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {expense_count} expense records."
            }), 409

        # ===========================
        # 7. Check Warehouse Stock
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM warehouse_stock WHERE store_id = %s", (store_id,))
        stock_count = cursor.fetchone()['count']
        
        if stock_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {stock_count} warehouse stock entries."
            }), 409

        # ===========================
        # 8. Check Stock Adjustments
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM stock_adjustments WHERE store_id = %s", (store_id,))
        adjustment_count = cursor.fetchone()['count']
        
        if adjustment_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {adjustment_count} stock adjustments."
            }), 409

        # ===========================
        # 9. Check Stock Transfers
        # ===========================
        cursor.execute("""
            SELECT COUNT(*) as count 
            FROM stock_transfers 
            WHERE from_store_id = %s OR to_store_id = %s
        """, (store_id, store_id))
        transfer_count = cursor.fetchone()['count']
        
        if transfer_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {transfer_count} stock transfers."
            }), 409

        # ===========================
        # 10. Check Sale Returns
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM sale_return WHERE store_id = %s", (store_id,))
        sale_return_count = cursor.fetchone()['count']
        
        if sale_return_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {sale_return_count} sale returns."
            }), 409

        # ===========================
        # 11. Check Purchase Returns
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM purchase_returns WHERE store_id = %s", (store_id,))
        purchase_return_count = cursor.fetchone()['count']
        
        if purchase_return_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {purchase_return_count} purchase returns."
            }), 409

        # ===========================
        # 12. Check Register Logs
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM close_register_logs WHERE store_id = %s", (store_id,))
        register_count = cursor.fetchone()['count']
        
        if register_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {register_count} register log entries."
            }), 409

        # ===========================
        # 13. Check Barcode Generations
        # ===========================
        cursor.execute("SELECT COUNT(*) as count FROM barcode_generations WHERE store_id = %s", (store_id,))
        barcode_count = cursor.fetchone()['count']
        
        if barcode_count > 0:
            return jsonify({
                "error": f"Cannot delete store. It has {barcode_count} barcode generation records."
            }), 409

        # ===========================
        # If NO dependencies, DELETE
        # ===========================
        cursor.execute("DELETE FROM stores WHERE id = %s", (store_id,))
        conn.commit()

        return jsonify({"message": "Store deleted successfully"}), 200

    except Exception as e:
        conn.rollback()
        print(f"Error deleting store: {str(e)}")  # Debug log
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()

# -------------------------
#  TOGGLE STORE STATUS
# -------------------------
@stores_bp.route('/toggle_store_status/<int:store_id>', methods=['PATCH'])
@jwt_required()
@role_required('admin')
def toggle_store_status(store_id):
    """Toggle store active/inactive status"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("SELECT is_active FROM stores WHERE id = %s", (store_id,))
        store = cursor.fetchone()
        
        if not store:
            return jsonify({"error": "Store not found"}), 404

        new_status = 0 if store['is_active'] else 1
        
        cursor.execute("""
            UPDATE stores 
            SET is_active = %s, updated_at = NOW() 
            WHERE id = %s
        """, (new_status, store_id))
        
        conn.commit()

        return jsonify({
            "message": "Store status updated successfully",
            "is_active": new_status
        }), 200

    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e)}), 500

    finally:
        cursor.close()
        conn.close()


# -------------------------
#  GET STORE STATISTICS
# -------------------------
@stores_bp.route('/store_statistics/<int:store_id>', methods=['GET'])
@jwt_required()
def store_statistics(store_id):
    """Get statistics for a specific store"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # Check if store exists
        cursor.execute("SELECT store_name FROM stores WHERE id = %s", (store_id,))
        store = cursor.fetchone()
        if not store:
            return jsonify({"error": "Store not found"}), 404

        stats = {"store_id": store_id, "store_name": store['store_name']}

        # Get cashier count
        cursor.execute("""
            SELECT COUNT(*) as count 
            FROM users 
            WHERE store_id = %s AND role = 'cashier'
        """, (store_id,))
        stats['cashier_count'] = cursor.fetchone()['count']

        # Get sales count (if sales table exists)
        try:
            cursor.execute("""
                SELECT COUNT(*) as count 
                FROM sales 
                WHERE store_id = %s
            """, (store_id,))
            stats['total_sales'] = cursor.fetchone()['count']
        except:
            stats['total_sales'] = 0

        # Get product count (if products table exists)
        try:
            cursor.execute("""
                SELECT COUNT(*) as count 
                FROM products 
                WHERE store_id = %s
            """, (store_id,))
            stats['total_products'] = cursor.fetchone()['count']
        except:
            stats['total_products'] = 0

        return jsonify(stats), 200

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

    finally:
        cursor.close()
        conn.close()