from flask import Flask, render_template, request, redirect, url_for, flash, jsonify
from flask_sqlalchemy import SQLAlchemy
import os
import logging

# Configure logging
logging.basicConfig(filename='app.log', level=logging.ERROR, format='%(asctime)s %(levelname)s: %(message)s')

app = Flask(__name__)
app.secret_key = 'seedvault_secret_key'
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'inventory.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# --- Models ---

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    sku = db.Column(db.String(50), nullable=True)
    bulk_weight_g = db.Column(db.Float, default=0.0)
    weight_per_packet_g = db.Column(db.Float, nullable=False)
    low_stock_threshold_g = db.Column(db.Float, default=10.0)
    current_stock_g = db.Column(db.Float, default=0.0)
    product_type = db.Column(db.String(20), default='weight')
    current_stock_qty = db.Column(db.Integer, default=0)
    is_unit_based = db.Column(db.Boolean, default=False)

    def get_status(self):
        # For unit-based, we check qty. For weight-based, we check grams.
        stock_val = self.current_stock_qty if self.is_unit_based else self.current_stock_g
        threshold_val = 1.0 if self.is_unit_based else self.low_stock_threshold_g
        
        if stock_val <= 0:
            return 'OUT'
        if stock_val <= threshold_val:
            return 'LOW'
        return 'OK'

class SyncLog(db.Model):
    __tablename__ = 'sync_log'
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, default=db.func.current_timestamp)
    status = db.Column(db.String(20))  # 'success' or 'error'
    message = db.Column(db.Text)

# --- Routes ---

@app.route('/')
def index():
    search_query = request.args.get('search', '').strip()
    filter_type = request.args.get('filter', 'all')
    
    products = Product.query
    
    if search_query:
        products = products.filter(
            (Product.name.contains(search_query)) | 
            (Product.sku.contains(search_query))
        )
    
    if filter_type == 'action_required':
        # Filter for products that are LOW or OUT
        # Since get_status() is a Python method, we can't use it in a SQL filter directly.
        # We have to do it in Python or use the underlying logic in SQL.
        # Logic: (is_unit_based AND current_stock_qty <= threshold) OR (NOT is_unit_based AND current_stock_g <= threshold)
        # But threshold is different for unit vs weight.
        # Let's just fetch and filter in Python for simplicity, or use a more complex SQL query.
        # Given the small number of products, Python filtering is fine.
        pass

    products_list = products.all()
    
    if filter_type == 'action_required':
        products_list = [p for p in products_list if p.get_status() in ['LOW', 'OUT']]
    
    last_sync = SyncLog.query.order_by(SyncLog.timestamp.desc()).first()
    
    return render_template('index.html', 
                           products=products_list, 
                           search_query=search_query, 
                           filter_type=filter_type,
                           last_sync=last_sync)

@app.route('/add_product', methods=['POST'])
def add_product():
    name = request.form.get('name')
    sku = request.form.get('sku') or None
    is_unit_based = request.form.get('is_unit_based') == 'on'
    weight_per_packet = float(request.form.get('weight_per_packet')) if not is_unit_based else 1.0
    initial_stock = float(request.form.get('current_stock'))
    threshold = float(request.form.get('threshold'))

    stock_g = 0.0
    stock_qty = 0
    if is_unit_based:
        stock_qty = int(initial_stock)
    else:
        stock_g = initial_stock

    new_product = Product(
        name=name,
        sku=sku,
        bulk_weight_g=0.0,
        weight_per_packet_g=weight_per_packet,
        current_stock_g=stock_g,
        current_stock_qty=stock_qty,
        low_stock_threshold_g=threshold,
        is_unit_based=is_unit_based,
        product_type='quantity' if is_unit_based else 'weight'
    )
    
    try:
        db.session.add(new_product)
        db.session.commit()
        flash(f'Product "{name}" added successfully!', 'success')
    except Exception as e:
        db.session.rollback()
        flash(f'Error adding product: {str(e)}', 'danger')
    
    return redirect(url_for('index'))

@app.route('/edit/<int:product_id>', methods=['GET', 'POST'])
def edit_product(product_id):
    product = Product.query.get_or_404(product_id)
    
    if request.method == 'POST':
        try:
            product.name = request.form.get('name')
            product.sku = request.form.get('sku') or None
            product.is_unit_based = request.form.get('is_unit_based') == 'on'
            
            if product.is_unit_based:
                product.weight_per_packet_g = 1.0
            else:
                product.weight_per_packet_g = float(request.form.get('weight_per_packet'))
                
            initial_stock = float(request.form.get('current_stock'))
            product.low_stock_threshold_g = float(request.form.get('threshold'))
            
            if product.is_unit_based:
                product.current_stock_qty = int(initial_stock)
                product.current_stock_g = 0.0
                product.product_type = 'quantity'
            else:
                product.current_stock_g = initial_stock
                product.current_stock_qty = 0
                product.product_type = 'weight'
            
            db.session.commit()
            flash(f'Product "{product.name}" updated successfully!', 'success')
            return redirect(url_for('index'))
        except Exception as e:
            db.session.rollback()
            logging.error(f"Error updating product {product_id}: {str(e)}", exc_info=True)
            flash(f'Error updating product: {str(e)}', 'danger')
            
    return render_template('edit.html', product=product)

@app.route('/restock', methods=['POST'])
def restock():
    product_id = request.form.get('product_id')
    amount = float(request.form.get('amount'))
    
    product = Product.query.get(product_id)
    if product:
        if product.is_unit_based:
            # If unit based, amount is the number of units.
            product.current_stock_qty += int(amount)
        else:
            # If weighted, amount is packets, so add amount * weight.
            product.current_stock_g += (amount * product.weight_per_packet_g)
        db.session.commit()
        flash(f'Restocked {product.name}.', 'success')
    else:
        flash('Product not found.', 'danger')
        
    return redirect(url_for('index'))

@app.route('/sell', methods=['POST'])
def sell():
    product_id = request.form.get('product_id')
    packets = int(request.form.get('packets'))
    
    product = Product.query.get(product_id)
    if product:
        if product.is_unit_based:
            # If unit based, deduction is just the number of units.
            deduction = packets
            if product.current_stock_qty >= deduction:
                product.current_stock_qty -= deduction
                db.session.commit()
                flash(f'Sold {packets} packets of {product.name}.', 'success')
            else:
                flash(f'Insufficient stock for {product.name}!', 'danger')
        else:
            # If weighted, deduction is packets * weight.
            deduction = packets * product.weight_per_packet_g
            if product.current_stock_g >= deduction:
                product.current_stock_g -= deduction
                db.session.commit()
                flash(f'Sold {packets} packets of {product.name}.', 'success')
            else:
                flash(f'Insufficient bulk stock for {product.name}!', 'danger')
    else:
        flash('Product not found.', 'danger')
        
    return redirect(url_for('index'))

@app.route('/delete/<int:product_id>', methods=['POST'])
def delete_product(product_id):
    product = Product.query.get(product_id)
    if product:
        db.session.delete(product)
        db.session.commit()
        flash(f'Deleted {product.name}.', 'warning')
    return redirect(url_for('index'))

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(host='127.0.0.1', port=5001, debug=True)