#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Les Vertes Prairies - Site E-commerce de Miel
Serveur Flask avec SQLite
"""

import os
import uuid
import secrets
from datetime import datetime
from functools import wraps

from flask import Flask, render_template, request, jsonify, redirect, url_for, session, send_from_directory
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager, UserMixin, login_user, logout_user, login_required, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from dotenv import load_dotenv

# Charger la configuration
load_dotenv()

# Configuration de l'application
app = Flask(__name__, static_folder='public', static_url_path='')
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', secrets.token_hex(32))

# Configuration base de données avec chemin absolu
basedir = os.path.abspath(os.path.dirname(__file__))
db_path = os.path.join(basedir, 'data', 'miel.db')
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', f'sqlite:///{db_path}')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['UPLOAD_FOLDER'] = os.path.join(basedir, os.getenv('UPLOAD_FOLDER', 'uploads'))
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB max

# Créer les dossiers nécessaires
os.makedirs(os.path.join(basedir, 'data'), exist_ok=True)
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

# Initialisation des extensions
db = SQLAlchemy(app)
login_manager = LoginManager(app)
login_manager.login_view = 'compte'

# ============================================================================
# MODÈLES DE BASE DE DONNÉES
# ============================================================================

class Setting(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(100), unique=True, nullable=False)
    value = db.Column(db.Text, nullable=False)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow)

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    first_name = db.Column(db.String(100), nullable=False)
    last_name = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(20))
    address = db.Column(db.String(255))
    city = db.Column(db.String(100))
    postal_code = db.Column(db.String(10))
    is_admin = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    orders = db.relationship('Order', backref='user', lazy=True)

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    
    products = db.relationship('Product', backref='category', lazy=True)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Float, nullable=False)
    weight = db.Column(db.String(50))
    stock = db.Column(db.Integer, default=0)
    image = db.Column(db.String(255))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    is_active = db.Column(db.Boolean, default=True)
    is_featured = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class CartItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.String(100), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    quantity = db.Column(db.Integer, default=1)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    product = db.relationship('Product')

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    order_number = db.Column(db.String(50), unique=True, nullable=False)
    status = db.Column(db.String(20), default='pending')
    total = db.Column(db.Float, nullable=False)
    shipping_first_name = db.Column(db.String(100))
    shipping_last_name = db.Column(db.String(100))
    shipping_email = db.Column(db.String(120))
    shipping_phone = db.Column(db.String(20))
    shipping_address = db.Column(db.String(255))
    shipping_city = db.Column(db.String(100))
    shipping_postal_code = db.Column(db.String(10))
    payment_method = db.Column(db.String(50))
    notes = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    items = db.relationship('OrderItem', backref='order', lazy=True)

class OrderItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    product_name = db.Column(db.String(200), nullable=False)
    product_price = db.Column(db.Float, nullable=False)
    quantity = db.Column(db.Integer, nullable=False)

# ============================================================================
# HELPERS
# ============================================================================

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

def get_session_id():
    if current_user.is_authenticated:
        return f"user_{current_user.id}"
    if 'cart_session' not in session:
        session['cart_session'] = str(uuid.uuid4())
    return session['cart_session']

def get_setting(key, default=None):
    setting = Setting.query.filter_by(key=key).first()
    return setting.value if setting else default

def set_setting(key, value):
    setting = Setting.query.filter_by(key=key).first()
    if setting:
        setting.value = value
        setting.updated_at = datetime.utcnow()
    else:
        setting = Setting(key=key, value=value)
        db.session.add(setting)
    db.session.commit()

def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated or not current_user.is_admin:
            return jsonify({'error': 'Accès refusé'}), 403
        return f(*args, **kwargs)
    return decorated_function

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in {'png', 'jpg', 'jpeg', 'gif', 'webp'}

# ============================================================================
# ROUTES - PAGES
# ============================================================================

@app.route('/')
def index():
    return send_from_directory('public', 'index.html')

@app.route('/boutique')
def boutique():
    return send_from_directory('public', 'boutique.html')

@app.route('/panier')
def panier():
    return send_from_directory('public', 'panier.html')

@app.route('/checkout')
def checkout():
    return send_from_directory('public', 'checkout.html')

@app.route('/compte')
def compte():
    return send_from_directory('public', 'compte.html')

@app.route('/admin')
def admin():
    return send_from_directory('public', 'admin.html')

@app.route('/sonotherapie')
def sonotherapie():
    return send_from_directory('public', 'sonotherapie.html')

@app.route('/gite')
def gite():
    return send_from_directory('public', 'gite.html')

@app.route('/uploads/<path:filename>')
def uploaded_file(filename):
    return send_from_directory(app.config['UPLOAD_FOLDER'], filename)

# ============================================================================
# API - AUTHENTIFICATION
# ============================================================================

@app.route('/api/auth/register', methods=['POST'])
def register():
    data = request.json
    
    if not all([data.get('email'), data.get('password'), data.get('firstName'), data.get('lastName')]):
        return jsonify({'error': 'Tous les champs obligatoires doivent être remplis'}), 400
    
    if User.query.filter_by(email=data['email']).first():
        return jsonify({'error': 'Cet email est déjà utilisé'}), 400
    
    user = User(
        email=data['email'],
        password=generate_password_hash(data['password'], method='pbkdf2:sha256'),
        first_name=data['firstName'],
        last_name=data['lastName'],
        phone=data.get('phone')
    )
    db.session.add(user)
    db.session.commit()
    
    login_user(user)
    
    return jsonify({
        'success': True,
        'user': {
            'id': user.id,
            'email': user.email,
            'firstName': user.first_name,
            'lastName': user.last_name,
            'isAdmin': user.is_admin
        }
    })

@app.route('/api/auth/login', methods=['POST'])
def login():
    data = request.json
    
    user = User.query.filter_by(email=data.get('email')).first()
    
    if not user or not check_password_hash(user.password, data.get('password', '')):
        return jsonify({'error': 'Email ou mot de passe incorrect'}), 401
    
    login_user(user)
    
    return jsonify({
        'success': True,
        'user': {
            'id': user.id,
            'email': user.email,
            'firstName': user.first_name,
            'lastName': user.last_name,
            'isAdmin': user.is_admin
        }
    })

@app.route('/api/auth/logout', methods=['POST'])
def logout():
    logout_user()
    return jsonify({'success': True})

@app.route('/api/auth/me')
def get_current_user():
    if not current_user.is_authenticated:
        return jsonify({'loggedIn': False})
    
    return jsonify({
        'loggedIn': True,
        'user': {
            'id': current_user.id,
            'email': current_user.email,
            'firstName': current_user.first_name,
            'lastName': current_user.last_name,
            'phone': current_user.phone,
            'address': current_user.address,
            'city': current_user.city,
            'postalCode': current_user.postal_code,
            'isAdmin': current_user.is_admin
        }
    })

@app.route('/api/auth/profile', methods=['PUT'])
@login_required
def update_profile():
    data = request.json
    
    current_user.first_name = data.get('firstName', current_user.first_name)
    current_user.last_name = data.get('lastName', current_user.last_name)
    current_user.phone = data.get('phone', current_user.phone)
    current_user.address = data.get('address', current_user.address)
    current_user.city = data.get('city', current_user.city)
    current_user.postal_code = data.get('postalCode', current_user.postal_code)
    
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/auth/password', methods=['PUT'])
@login_required
def change_password():
    data = request.json
    
    if not check_password_hash(current_user.password, data.get('currentPassword', '')):
        return jsonify({'error': 'Mot de passe actuel incorrect'}), 400
    
    current_user.password = generate_password_hash(data['newPassword'], method='pbkdf2:sha256')
    db.session.commit()
    
    return jsonify({'success': True})

# ============================================================================
# API - PRODUITS
# ============================================================================

@app.route('/api/products')
def get_products():
    query = Product.query.filter_by(is_active=True)
    
    category = request.args.get('category')
    if category:
        query = query.filter_by(category_id=category)
    
    featured = request.args.get('featured')
    if featured == 'true':
        query = query.filter_by(is_featured=True)
    
    products = query.order_by(Product.is_featured.desc(), Product.created_at.desc()).all()
    
    return jsonify([{
        'id': p.id,
        'name': p.name,
        'description': p.description,
        'price': p.price,
        'weight': p.weight,
        'stock': p.stock,
        'image': p.image,
        'category_id': p.category_id,
        'category_name': p.category.name if p.category else None,
        'is_featured': p.is_featured
    } for p in products])

@app.route('/api/products/categories')
def get_categories():
    categories = Category.query.all()
    return jsonify([{'id': c.id, 'name': c.name, 'description': c.description} for c in categories])

@app.route('/api/products/<int:product_id>')
def get_product(product_id):
    product = Product.query.get_or_404(product_id)
    return jsonify({
        'id': product.id,
        'name': product.name,
        'description': product.description,
        'price': product.price,
        'weight': product.weight,
        'stock': product.stock,
        'image': product.image,
        'category_id': product.category_id,
        'category_name': product.category.name if product.category else None,
        'is_active': product.is_active,
        'is_featured': product.is_featured
    })

# ============================================================================
# API - PANIER
# ============================================================================

@app.route('/api/cart')
def get_cart():
    session_id = get_session_id()
    items = CartItem.query.filter_by(session_id=session_id).all()
    
    cart_items = []
    total = 0
    count = 0
    
    for item in items:
        if item.product:
            cart_items.append({
                'id': item.id,
                'quantity': item.quantity,
                'product_id': item.product.id,
                'name': item.product.name,
                'price': item.product.price,
                'image': item.product.image,
                'stock': item.product.stock,
                'weight': item.product.weight
            })
            total += item.product.price * item.quantity
            count += item.quantity
    
    return jsonify({'items': cart_items, 'total': total, 'count': count})

@app.route('/api/cart/add', methods=['POST'])
def add_to_cart():
    data = request.json
    product_id = data.get('productId')
    quantity = data.get('quantity', 1)
    
    # Vérifier si la boutique est ouverte
    if get_setting('shop_open', 'true') == 'false':
        return jsonify({'error': 'La boutique est actuellement fermée'}), 400
    
    product = Product.query.filter_by(id=product_id, is_active=True).first()
    if not product:
        return jsonify({'error': 'Produit non trouvé'}), 404
    
    if product.stock < quantity:
        return jsonify({'error': 'Stock insuffisant'}), 400
    
    session_id = get_session_id()
    
    existing = CartItem.query.filter_by(session_id=session_id, product_id=product_id).first()
    
    if existing:
        new_qty = existing.quantity + quantity
        if new_qty > product.stock:
            return jsonify({'error': 'Stock insuffisant'}), 400
        existing.quantity = new_qty
    else:
        cart_item = CartItem(
            session_id=session_id,
            user_id=current_user.id if current_user.is_authenticated else None,
            product_id=product_id,
            quantity=quantity
        )
        db.session.add(cart_item)
    
    db.session.commit()
    return jsonify({'success': True, 'message': 'Produit ajouté au panier'})

@app.route('/api/cart/update/<int:item_id>', methods=['PUT'])
def update_cart_item(item_id):
    data = request.json
    quantity = data.get('quantity', 1)
    session_id = get_session_id()
    
    item = CartItem.query.filter_by(id=item_id, session_id=session_id).first()
    if not item:
        return jsonify({'error': 'Article non trouvé'}), 404
    
    if quantity <= 0:
        db.session.delete(item)
    else:
        if quantity > item.product.stock:
            return jsonify({'error': 'Stock insuffisant'}), 400
        item.quantity = quantity
    
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/cart/remove/<int:item_id>', methods=['DELETE'])
def remove_from_cart(item_id):
    session_id = get_session_id()
    item = CartItem.query.filter_by(id=item_id, session_id=session_id).first()
    
    if not item:
        return jsonify({'error': 'Article non trouvé'}), 404
    
    db.session.delete(item)
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/cart/clear', methods=['DELETE'])
def clear_cart():
    session_id = get_session_id()
    CartItem.query.filter_by(session_id=session_id).delete()
    db.session.commit()
    return jsonify({'success': True})

# ============================================================================
# API - COMMANDES
# ============================================================================

@app.route('/api/orders/create', methods=['POST'])
def create_order():
    # Vérifier si la boutique est ouverte
    if get_setting('shop_open', 'true') == 'false':
        return jsonify({'error': 'La boutique est actuellement fermée'}), 400
    
    data = request.json
    session_id = get_session_id()
    
    # Validation
    required = ['firstName', 'lastName', 'email', 'address', 'city', 'postalCode']
    if not all(data.get(f) for f in required):
        return jsonify({'error': 'Informations de livraison incomplètes'}), 400
    
    # Récupérer le panier
    cart_items = CartItem.query.filter_by(session_id=session_id).all()
    if not cart_items:
        return jsonify({'error': 'Le panier est vide'}), 400
    
    # Vérifier les stocks
    total = 0
    for item in cart_items:
        if item.quantity > item.product.stock:
            return jsonify({'error': f'Stock insuffisant pour {item.product.name}'}), 400
        total += item.product.price * item.quantity
    
    # Générer numéro de commande
    order_number = f"VP-{datetime.now().strftime('%y%m%d')}-{uuid.uuid4().hex[:6].upper()}"
    
    # Créer la commande
    order = Order(
        user_id=current_user.id if current_user.is_authenticated else None,
        order_number=order_number,
        total=total,
        shipping_first_name=data['firstName'],
        shipping_last_name=data['lastName'],
        shipping_email=data['email'],
        shipping_phone=data.get('phone'),
        shipping_address=data['address'],
        shipping_city=data['city'],
        shipping_postal_code=data['postalCode'],
        payment_method=data.get('paymentMethod', 'card'),
        notes=data.get('notes')
    )
    db.session.add(order)
    db.session.flush()
    
    # Ajouter les articles et mettre à jour les stocks
    for item in cart_items:
        order_item = OrderItem(
            order_id=order.id,
            product_id=item.product.id,
            product_name=item.product.name,
            product_price=item.product.price,
            quantity=item.quantity
        )
        db.session.add(order_item)
        item.product.stock -= item.quantity
    
    # Vider le panier
    CartItem.query.filter_by(session_id=session_id).delete()
    
    db.session.commit()
    
    return jsonify({
        'success': True,
        'orderNumber': order_number,
        'orderId': order.id,
        'total': total,
        'message': 'Commande créée avec succès'
    })

@app.route('/api/orders/my-orders')
@login_required
def get_my_orders():
    orders = Order.query.filter_by(user_id=current_user.id).order_by(Order.created_at.desc()).all()
    return jsonify([{
        'id': o.id,
        'order_number': o.order_number,
        'status': o.status,
        'total': o.total,
        'created_at': o.created_at.isoformat()
    } for o in orders])

@app.route('/api/orders/<order_number>')
def get_order(order_number):
    order = Order.query.filter_by(order_number=order_number).first_or_404()
    
    # Vérifier les permissions
    if current_user.is_authenticated:
        if not current_user.is_admin and order.user_id != current_user.id:
            return jsonify({'error': 'Accès refusé'}), 403
    
    return jsonify({
        'id': order.id,
        'order_number': order.order_number,
        'status': order.status,
        'total': order.total,
        'shipping_first_name': order.shipping_first_name,
        'shipping_last_name': order.shipping_last_name,
        'shipping_email': order.shipping_email,
        'created_at': order.created_at.isoformat(),
        'items': [{
            'product_name': i.product_name,
            'product_price': i.product_price,
            'quantity': i.quantity
        } for i in order.items]
    })

# ============================================================================
# API - PARAMÈTRES
# ============================================================================

@app.route('/api/settings')
def get_all_settings():
    settings = Setting.query.all()
    return jsonify({s.key: s.value for s in settings})

@app.route('/api/settings/<key>')
def get_setting_value(key):
    value = get_setting(key)
    if value is None:
        return jsonify({'error': 'Paramètre non trouvé'}), 404
    return jsonify({'value': value})

# ============================================================================
# API - ADMINISTRATION
# ============================================================================

@app.route('/api/admin/stats')
@login_required
@admin_required
def admin_stats():
    total_orders = Order.query.count()
    total_revenue = db.session.query(db.func.sum(Order.total)).filter(Order.status != 'cancelled').scalar() or 0
    total_customers = User.query.filter_by(is_admin=False).count()
    total_products = Product.query.count()
    pending_orders = Order.query.filter_by(status='pending').count()
    low_stock = Product.query.filter(Product.stock < 10, Product.is_active == True).count()
    
    recent_orders = Order.query.order_by(Order.created_at.desc()).limit(5).all()
    
    return jsonify({
        'totalOrders': total_orders,
        'totalRevenue': total_revenue,
        'totalCustomers': total_customers,
        'totalProducts': total_products,
        'pendingOrders': pending_orders,
        'lowStock': low_stock,
        'recentOrders': [{
            'order_number': o.order_number,
            'total': o.total,
            'status': o.status,
            'created_at': o.created_at.isoformat(),
            'shipping_first_name': o.shipping_first_name,
            'shipping_last_name': o.shipping_last_name
        } for o in recent_orders]
    })

@app.route('/api/admin/products')
@login_required
@admin_required
def admin_products():
    products = Product.query.order_by(Product.created_at.desc()).all()
    return jsonify([{
        'id': p.id,
        'name': p.name,
        'description': p.description,
        'price': p.price,
        'weight': p.weight,
        'stock': p.stock,
        'image': p.image,
        'category_id': p.category_id,
        'category_name': p.category.name if p.category else None,
        'is_active': p.is_active,
        'is_featured': p.is_featured
    } for p in products])

@app.route('/api/admin/products', methods=['POST'])
@login_required
@admin_required
def create_product():
    name = request.form.get('name')
    description = request.form.get('description')
    price = float(request.form.get('price', 0))
    weight = request.form.get('weight')
    stock = int(request.form.get('stock', 0))
    category_id = request.form.get('categoryId') or None
    is_active = request.form.get('isActive') == 'true'
    is_featured = request.form.get('isFeatured') == 'true'
    
    image = None
    if 'image' in request.files:
        file = request.files['image']
        if file and allowed_file(file.filename):
            filename = secure_filename(f"{uuid.uuid4().hex}_{file.filename}")
            file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            image = f"/uploads/{filename}"
    
    product = Product(
        name=name,
        description=description,
        price=price,
        weight=weight,
        stock=stock,
        category_id=category_id,
        is_active=is_active,
        is_featured=is_featured,
        image=image
    )
    db.session.add(product)
    db.session.commit()
    
    return jsonify({'success': True, 'id': product.id})

@app.route('/api/admin/products/<int:product_id>', methods=['PUT'])
@login_required
@admin_required
def update_product(product_id):
    product = Product.query.get_or_404(product_id)
    
    product.name = request.form.get('name', product.name)
    product.description = request.form.get('description', product.description)
    product.price = float(request.form.get('price', product.price))
    product.weight = request.form.get('weight', product.weight)
    product.stock = int(request.form.get('stock', product.stock))
    product.category_id = request.form.get('categoryId') or product.category_id
    product.is_active = request.form.get('isActive') == 'true'
    product.is_featured = request.form.get('isFeatured') == 'true'
    
    if 'image' in request.files:
        file = request.files['image']
        if file and allowed_file(file.filename):
            filename = secure_filename(f"{uuid.uuid4().hex}_{file.filename}")
            file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
            product.image = f"/uploads/{filename}"
    
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/admin/products/<int:product_id>', methods=['DELETE'])
@login_required
@admin_required
def delete_product(product_id):
    product = Product.query.get_or_404(product_id)
    db.session.delete(product)
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/admin/orders')
@login_required
@admin_required
def admin_orders():
    orders = Order.query.order_by(Order.created_at.desc()).all()
    return jsonify([{
        'id': o.id,
        'order_number': o.order_number,
        'user_email': o.user.email if o.user else None,
        'status': o.status,
        'total': o.total,
        'shipping_first_name': o.shipping_first_name,
        'shipping_last_name': o.shipping_last_name,
        'shipping_email': o.shipping_email,
        'created_at': o.created_at.isoformat()
    } for o in orders])

@app.route('/api/admin/orders/<int:order_id>')
@login_required
@admin_required
def admin_order_detail(order_id):
    order = Order.query.get_or_404(order_id)
    return jsonify({
        'id': order.id,
        'order_number': order.order_number,
        'status': order.status,
        'total': order.total,
        'shipping_first_name': order.shipping_first_name,
        'shipping_last_name': order.shipping_last_name,
        'shipping_email': order.shipping_email,
        'shipping_phone': order.shipping_phone,
        'shipping_address': order.shipping_address,
        'shipping_city': order.shipping_city,
        'shipping_postal_code': order.shipping_postal_code,
        'notes': order.notes,
        'created_at': order.created_at.isoformat(),
        'items': [{
            'product_name': i.product_name,
            'product_price': i.product_price,
            'quantity': i.quantity
        } for i in order.items]
    })

@app.route('/api/admin/orders/<int:order_id>/status', methods=['PUT'])
@login_required
@admin_required
def update_order_status(order_id):
    order = Order.query.get_or_404(order_id)
    data = request.json
    
    valid_statuses = ['pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled']
    if data.get('status') not in valid_statuses:
        return jsonify({'error': 'Statut invalide'}), 400
    
    order.status = data['status']
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/admin/customers')
@login_required
@admin_required
def admin_customers():
    customers = User.query.filter_by(is_admin=False).order_by(User.created_at.desc()).all()
    
    result = []
    for c in customers:
        order_count = Order.query.filter_by(user_id=c.id).count()
        total_spent = db.session.query(db.func.sum(Order.total)).filter_by(user_id=c.id).scalar() or 0
        result.append({
            'id': c.id,
            'email': c.email,
            'first_name': c.first_name,
            'last_name': c.last_name,
            'phone': c.phone,
            'city': c.city,
            'created_at': c.created_at.isoformat(),
            'order_count': order_count,
            'total_spent': total_spent
        })
    
    return jsonify(result)

@app.route('/api/admin/categories')
@login_required
@admin_required
def admin_categories():
    categories = Category.query.all()
    return jsonify([{'id': c.id, 'name': c.name, 'description': c.description} for c in categories])

@app.route('/api/admin/categories', methods=['POST'])
@login_required
@admin_required
def create_category():
    data = request.json
    category = Category(name=data.get('name'), description=data.get('description'))
    db.session.add(category)
    db.session.commit()
    return jsonify({'success': True, 'id': category.id})

@app.route('/api/admin/categories/<int:category_id>', methods=['DELETE'])
@login_required
@admin_required
def delete_category(category_id):
    category = Category.query.get_or_404(category_id)
    db.session.delete(category)
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/admin/settings')
@login_required
@admin_required
def admin_settings():
    settings = Setting.query.all()
    return jsonify([{'key': s.key, 'value': s.value} for s in settings])

@app.route('/api/admin/settings/<key>', methods=['PUT'])
@login_required
@admin_required
def update_setting(key):
    data = request.json
    set_setting(key, data.get('value', ''))
    return jsonify({'success': True})

# ============================================================================
# INITIALISATION DE LA BASE DE DONNÉES
# ============================================================================

def init_db():
    """Initialise la base de données avec les données par défaut."""
    
    # Paramètres par défaut
    defaults = {
        'shop_open': 'true',
        'shop_closed_message': 'Notre miellerie est actuellement fermée pour la saison. Revenez nous voir au printemps !',
        'shop_name': 'Les Vertes Prairies',
        'shop_tagline': 'Miel artisanal de nos ruches'
    }
    
    for key, value in defaults.items():
        if not Setting.query.filter_by(key=key).first():
            db.session.add(Setting(key=key, value=value))
    
    # Admin par défaut
    if not User.query.filter_by(is_admin=True).first():
        admin = User(
            email='admin@vertesprairies.fr',
            password=generate_password_hash('admin123', method='pbkdf2:sha256'),
            first_name='Admin',
            last_name='Miellerie',
            is_admin=True
        )
        db.session.add(admin)
        print('👤 Compte admin créé: admin@vertesprairies.fr / admin123')
    
    # Catégories par défaut
    if Category.query.count() == 0:
        categories = [
            Category(name='Miels', description='Nos différentes variétés de miel'),
            Category(name='Coffrets', description='Coffrets cadeaux et assortiments'),
            Category(name='Produits de la ruche', description='Pollen, propolis, cire et autres trésors')
        ]
        db.session.add_all(categories)
        db.session.flush()
        
        # Produits de démonstration
        products = [
            Product(
                name='Miel de Fleurs Sauvages',
                description='Un miel doux et parfumé, récolté dans les prairies fleuries de notre région. Notes florales délicates avec une texture crémeuse.',
                price=12.50, weight='500g', stock=50, category_id=1, is_featured=True, image='/images/miel-fleurs.jpg'
            ),
            Product(
                name="Miel d'Acacia",
                description='Miel très clair et liquide, au goût délicat et subtil. Idéal pour sucrer vos boissons sans en altérer le goût.',
                price=14.00, weight='500g', stock=35, category_id=1, is_featured=True, image='/images/miel-acacia.jpg'
            ),
            Product(
                name='Miel de Tilleul',
                description='Miel ambré aux notes mentholées caractéristiques. Reconnu pour ses propriétés apaisantes, parfait pour les tisanes du soir.',
                price=13.50, weight='500g', stock=40, category_id=1, is_featured=True, image='/images/miel-tilleul.jpg'
            ),
            Product(
                name='Miel de Châtaignier',
                description='Miel corsé et puissant, légèrement amer. Un caractère affirmé pour les amateurs de saveurs intenses.',
                price=15.00, weight='500g', stock=25, category_id=1, is_featured=False, image='/images/miel-chataignier.jpg'
            ),
            Product(
                name='Coffret Découverte',
                description='Assortiment de 4 petits pots (125g chacun) pour découvrir nos différentes variétés de miel.',
                price=28.00, weight='4x125g', stock=20, category_id=2, is_featured=True, image='/images/coffret-decouverte.jpg'
            ),
            Product(
                name='Pollen Frais',
                description='Pollen récolté avec soin, séché naturellement. Riche en protéines et vitamines, à saupoudrer sur vos salades ou yaourts.',
                price=18.00, weight='250g', stock=15, category_id=3, is_featured=False, image='/images/pollen.jpg'
            ),
            Product(
                name='Propolis Pure',
                description='Propolis brute récoltée dans nos ruches. Connue pour ses propriétés antibactériennes naturelles.',
                price=22.00, weight='30g', stock=30, category_id=3, is_featured=False, image='/images/propolis.jpg'
            )
        ]
        db.session.add_all(products)
    
    db.session.commit()
    print('🗄️  Base de données initialisée')

# ============================================================================
# POINT D'ENTRÉE
# ============================================================================

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
        init_db()
    
    port = int(os.getenv('PORT', 9100))
    debug = os.getenv('DEBUG', 'false').lower() == 'true'
    
    print(f'🍯 Serveur Les Vertes Prairies démarré sur http://localhost:{port}')
    app.run(host='0.0.0.0', port=port, debug=debug)