from flask import Flask, render_template, request, redirect, url_for, flash
import sqlite3
from datetime import datetime, timedelta

app = Flask(__name__)
app.secret_key = 'apteka_secret'

def get_db():
    conn = sqlite3.connect('pharmacy.db')
    conn.row_factory = sqlite3.Row
    return conn

def calculate_order_price(medicine_id, quantity):
    """Расчет стоимости заказа с учетом срока годности и сезона"""
    conn = get_db()
    medicine = conn.execute('''
        SELECT price, expiration_date, season_coeff FROM medicines WHERE id = ?
    ''', (medicine_id,)).fetchone()
    conn.close()
    
    if not medicine:
        return 0
    
    base = medicine['price'] * quantity
    
    date_str = medicine['expiration_date'][:10]
    exp_date = datetime.strptime(date_str, '%Y-%m-%d').date()
    days_left = (exp_date - datetime.now().date()).days
    
    if days_left < 0:
        exp_factor = 0.1
    elif days_left < 30:
        exp_factor = 0.7
    elif days_left < 90:
        exp_factor = 0.9
    else:
        exp_factor = 1.0
    
    season_factor = medicine['season_coeff']
    
    return round(base * exp_factor * season_factor, 2)

# ========== ГЛАВНАЯ ==========
@app.route('/')
def index():
    conn = get_db()
    medicines = conn.execute('''
        SELECT m.*, c.name as category_name, man.name as manufacturer_name
        FROM medicines m
        LEFT JOIN categories c ON m.category_id = c.id
        LEFT JOIN manufacturers man ON m.manufacturer_id = man.id
        ORDER BY m.name
    ''').fetchall()
    conn.close()
    return render_template('index.html', medicines=medicines, calculate=calculate_order_price)

# ========== ПРОИЗВОДИТЕЛИ ==========
@app.route('/manufacturers')
def manufacturers():
    conn = get_db()
    mans = conn.execute('SELECT * FROM manufacturers ORDER BY name').fetchall()
    conn.close()
    return render_template('manufacturers.html', manufacturers=mans)

@app.route('/add_manufacturer', methods=['GET', 'POST'])
def add_manufacturer():
    if request.method == 'POST':
        name = request.form['name']
        country = request.form.get('country', '')
        phone = request.form.get('phone', '')
        email = request.form.get('email', '')
        
        conn = get_db()
        conn.execute('''
            INSERT INTO manufacturers (name, country, phone, email)
            VALUES (?, ?, ?, ?)
        ''', (name, country, phone, email))
        conn.commit()
        conn.close()
        flash('Производитель добавлен!', 'success')
        return redirect(url_for('manufacturers'))
    
    return render_template('add_manufacturer.html')

@app.route('/edit_manufacturer/<int:id>', methods=['GET', 'POST'])
def edit_manufacturer(id):
    conn = get_db()
    if request.method == 'POST':
        name = request.form['name']
        country = request.form.get('country', '')
        phone = request.form.get('phone', '')
        email = request.form.get('email', '')
        
        conn.execute('''
            UPDATE manufacturers 
            SET name=?, country=?, phone=?, email=?
            WHERE id=?
        ''', (name, country, phone, email, id))
        conn.commit()
        conn.close()
        flash('Производитель обновлен!', 'success')
        return redirect(url_for('manufacturers'))
    
    man = conn.execute('SELECT * FROM manufacturers WHERE id = ?', (id,)).fetchone()
    conn.close()
    return render_template('edit_manufacturer.html', manufacturer=man)

@app.route('/delete_manufacturer/<int:id>', methods=['POST'])
def delete_manufacturer(id):
    conn = get_db()
    conn.execute('DELETE FROM manufacturers WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('Производитель удален!', 'success')
    return redirect(url_for('manufacturers'))

# ========== КАТЕГОРИИ ==========
@app.route('/categories')
def categories():
    conn = get_db()
    cats = conn.execute('SELECT * FROM categories ORDER BY name').fetchall()
    conn.close()
    return render_template('categories.html', categories=cats)

@app.route('/add_category', methods=['GET', 'POST'])
def add_category():
    if request.method == 'POST':
        name = request.form['name']
        description = request.form.get('description', '')
        
        conn = get_db()
        conn.execute('INSERT INTO categories (name, description) VALUES (?, ?)',
                    (name, description))
        conn.commit()
        conn.close()
        flash('Категория добавлена!', 'success')
        return redirect(url_for('categories'))
    
    return render_template('add_category.html')

@app.route('/edit_category/<int:id>', methods=['GET', 'POST'])
def edit_category(id):
    conn = get_db()
    if request.method == 'POST':
        name = request.form['name']
        description = request.form.get('description', '')
        
        conn.execute('UPDATE categories SET name=?, description=? WHERE id=?',
                    (name, description, id))
        conn.commit()
        conn.close()
        flash('Категория обновлена!', 'success')
        return redirect(url_for('categories'))
    
    category = conn.execute('SELECT * FROM categories WHERE id = ?', (id,)).fetchone()
    conn.close()
    return render_template('edit_category.html', category=category)

@app.route('/delete_category/<int:id>', methods=['POST'])
def delete_category(id):
    conn = get_db()
    conn.execute('DELETE FROM categories WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('Категория удалена!', 'success')
    return redirect(url_for('categories'))

# ========== ЛЕКАРСТВА ==========
@app.route('/add_medicine', methods=['GET', 'POST'])
def add_medicine():
    conn = get_db()
    if request.method == 'POST':
        name = request.form['name']
        category_id = request.form['category_id']
        manufacturer_id = request.form['manufacturer_id']
        price = request.form['price']
        expiration_date = request.form['expiration_date']
        stock_quantity = request.form['stock_quantity']
        season_coeff = request.form.get('season_coeff', 1.0)

        conn.execute('''
            INSERT INTO medicines (name, category_id, manufacturer_id, price, expiration_date, stock_quantity, season_coeff)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (name, category_id, manufacturer_id, price, expiration_date, stock_quantity, season_coeff))
        conn.commit()
        conn.close()
        flash('Лекарство добавлено!', 'success')
        return redirect(url_for('index'))

    categories = conn.execute('SELECT * FROM categories').fetchall()
    manufacturers = conn.execute('SELECT * FROM manufacturers').fetchall()
    conn.close()
    return render_template('add_medicine.html', categories=categories, manufacturers=manufacturers)

if __name__ == '__main__':
    app.run(debug=True)