from flask import Flask, render_template, send_from_directory, request, redirect, url_for, flash, session
import sqlite3
import os
from datetime import datetime

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

# ========== ПОДКЛЮЧЕНИЕ К БАЗЕ ДАННЫХ ==========
def get_db_connection():
    conn = sqlite3.connect('materials.db')
    conn.row_factory = sqlite3.Row
    return conn

# ========== ФУНКЦИИ РАСЧЕТА СКИДОК И СТОИМОСТИ ==========

def calculate_supplier_discount(supplier_id, year):
    """Расчет накопительной скидки поставщика за год (по сумме заказов)"""
    conn = get_db_connection()
    
    # Получаем все заказы поставщика за указанный год
    orders = conn.execute('''
        SELECT total_amount FROM Orders 
        WHERE client_id = ? AND strftime('%Y', order_date) = ?
    ''', (supplier_id, str(year))).fetchall()
    
    conn.close()
    
    total_year = sum(order['total_amount'] for order in orders)
    
    if total_year >= 500000:
        return 20
    elif total_year >= 300000:
        return 15
    elif total_year >= 150000:
        return 10
    return 0

def calculate_order_total_with_transport(items):
    """Расчет общей суммы заказа с транспортными расходами (10%)"""
    total = 0
    for item in items:
        total += item['quantity'] * item['price'] * 1.1
    return round(total, 2)

# ========== ГЛАВНАЯ СТРАНИЦА (ПОСТАВЩИКИ) ==========

@app.route('/')
def index():
    # Получаем параметры фильтрации из URL
    search_query = request.args.get('search', '')
    client_type = request.args.get('type', 'all')
    min_rating = request.args.get('rating', '0')
    sort_by = request.args.get('sort', 'name_asc')
    
    conn = get_db_connection()
    
    try:
        # Получаем общее количество всех клиентов (без фильтров)
        total_count = conn.execute('SELECT COUNT(*) as count FROM Clients').fetchone()['count']
        
        # Базовый SQL запрос для клиентов
        sql = '''
            SELECT c.*, ct.type_name as contractor_type_name,
                   COALESCE(SUM(o.total_amount), 0) as total_amount,
                   COUNT(DISTINCT o.id) as orders_count
            FROM Clients c 
            LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
            LEFT JOIN Orders o ON c.id = o.client_id
            WHERE 1=1
        '''
        params = []
        
        # Поиск по названию или ИНН
        if search_query:
            sql += " AND (c.name LIKE ? OR c.inn LIKE ?)"
            params.extend([f'%{search_query}%', f'%{search_query}%'])
        
        # Фильтр по типу клиента
        if client_type != 'all':
            sql += " AND ct.type_name = ?"
            params.append(client_type)
        
        # Фильтр по минимальному рейтингу
        if min_rating != '0':
            sql += " AND c.rating >= ?"
            params.append(float(min_rating))
        
        # Группировка (обязательно после WHERE)
        sql += " GROUP BY c.id"
        
        # Сортировка
        if sort_by == 'name_asc':
            sql += " ORDER BY c.name ASC"
        elif sort_by == 'name_desc':
            sql += " ORDER BY c.name DESC"
        elif sort_by == 'rating_desc':
            sql += " ORDER BY c.rating DESC"
        elif sort_by == 'rating_asc':
            sql += " ORDER BY c.rating ASC"
        elif sort_by == 'discount_desc':
            sql += " ORDER BY total_amount DESC"
        elif sort_by == 'discount_asc':
            sql += " ORDER BY total_amount ASC"
        else:
            sql += " ORDER BY c.name ASC"
        
        # Выполняем запрос
        clients = conn.execute(sql, params).fetchall()
        
        # Добавляем информацию о скидке на основе суммы
        clients_with_discount = []
        for client in clients:
            total_amount = client['total_amount'] or 0
            
            # Рассчитываем текущую скидку на основе истории заказов
            current_discount = calculate_supplier_discount(client['id'], datetime.now().year)
            
            client_dict = dict(client)
            client_dict['total_amount'] = total_amount
            client_dict['total_sales'] = total_amount
            client_dict['orders_count'] = client['orders_count'] or 0
            client_dict['discount_percent'] = current_discount
            client_dict['discount'] = current_discount
            client_dict['discount_text'] = f'{current_discount}%'
            
            clients_with_discount.append(client_dict)
        
        conn.close()
        
        return render_template('index.html', 
                             partners=clients_with_discount, 
                             total_count=total_count)
    
    except sqlite3.OperationalError as e:
        conn.close()
        return render_template('index.html', 
                             partners=[], 
                             total_count=0,
                             error_message=f"Ошибка базы данных: {str(e)}")

# ========== МАРШРУТЫ ДЛЯ КЛИЕНТОВ ==========

@app.route('/add_client', methods=['GET', 'POST'])
def add_client():
    if request.method == 'POST':
        try:
            name = request.form['name']
            director = request.form.get('director', '')
            email = request.form['email']
            phone = request.form.get('phone', '')
            address = request.form.get('address', '')
            inn = request.form.get('inn', '')
            contractor_type_id = request.form['contractor_type_id']
            rating = request.form.get('rating', 0.0)

            conn = get_db_connection()
            
            # Проверяем уникальность ИНН
            if inn:
                existing = conn.execute('SELECT id FROM Clients WHERE inn = ?', (inn,)).fetchone()
                if existing:
                    flash('Клиент с таким ИНН уже существует', 'error')
                    conn.close()
                    return redirect(url_for('add_client'))

            conn.execute('''
                INSERT INTO Clients (name, director, email, phone, address, inn, contractor_type_id, rating)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (name, director, email, phone, address, inn, contractor_type_id, rating))

            conn.commit()
            conn.close()
            flash('Клиент успешно добавлен!', 'success')
            return redirect(url_for('index'))

        except Exception as e:
            flash(f'Ошибка: {str(e)}', 'error')
            return redirect(url_for('add_client'))

    # GET запрос
    conn = get_db_connection()
    contractor_types = conn.execute('SELECT * FROM ContractorType ORDER BY type_name').fetchall()
    conn.close()
    return render_template('add_client.html', contractor_types=contractor_types)

@app.route('/edit_client/<int:client_id>', methods=['GET', 'POST'])
def edit_client(client_id):
    conn = get_db_connection()

    if request.method == 'POST':
        try:
            name = request.form['name']
            director = request.form.get('director', '')
            email = request.form['email']
            phone = request.form.get('phone', '')
            address = request.form.get('address', '')
            inn = request.form.get('inn', '')
            contractor_type_id = request.form['contractor_type_id']
            rating = request.form.get('rating', 0.0)

            # Проверяем уникальность ИНН
            if inn:
                existing = conn.execute(
                    'SELECT id FROM Clients WHERE inn = ? AND id != ?',
                    (inn, client_id)
                ).fetchone()
                if existing:
                    flash('Клиент с таким ИНН уже существует', 'error')
                    conn.close()
                    return redirect(url_for('edit_client', client_id=client_id))

            conn.execute('''
                UPDATE Clients 
                SET name = ?, director = ?, email = ?, phone = ?, 
                    address = ?, inn = ?, contractor_type_id = ?, rating = ?
                WHERE id = ?
            ''', (name, director, email, phone, address, inn, contractor_type_id, rating, client_id))

            conn.commit()
            conn.close()
            flash('Данные обновлены!', 'success')
            return redirect(url_for('index'))

        except Exception as e:
            flash(f'Ошибка: {str(e)}', 'error')
            conn.close()
            return redirect(url_for('edit_client', client_id=client_id))

    # GET запрос
    client = conn.execute('''
        SELECT c.*, ct.type_name as contractor_type_name
        FROM Clients c 
        LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
        WHERE c.id = ?
    ''', (client_id,)).fetchone()

    contractor_types = conn.execute('SELECT * FROM ContractorType ORDER BY type_name').fetchall()
    conn.close()

    if not client:
        flash('Клиент не найден', 'error')
        return redirect(url_for('index'))

    return render_template('edit_client.html', client=client, contractor_types=contractor_types)

# ========== МАРШРУТЫ ДЛЯ ЗАКАЗОВ ==========

@app.route('/order_history/<int:client_id>')
def order_history(client_id):
    conn = get_db_connection()

    client = conn.execute('''
        SELECT c.*, ct.type_name as contractor_type_name
        FROM Clients c 
        LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
        WHERE c.id = ?
    ''', (client_id,)).fetchone()

    if not client:
        conn.close()
        flash('Клиент не найден', 'error')
        return redirect(url_for('index'))

    # Получаем все заказы
    orders = conn.execute('''
        SELECT o.*
        FROM Orders o
        WHERE o.client_id = ?
        ORDER BY o.order_date DESC, o.id DESC
    ''', (client_id,)).fetchall()
    
    orders_with_items = []
    
    for order in orders:
        order_dict = dict(order)
        
        # Получаем позиции для заказа
        items = conn.execute('''
            SELECT oi.*, cat.product_name, cat.article, 
                   cat2.category_name, m.material_name
            FROM OrderItems oi
            JOIN Catalog cat ON oi.product_id = cat.id
            LEFT JOIN Categories cat2 ON cat.category_id = cat2.id
            LEFT JOIN Materials m ON cat.material_id = m.id
            WHERE oi.order_id = ?
        ''', (order['id'],)).fetchall()
        
        # Рассчитываем базовую стоимость (сумма quantity * price_at_moment)
        base_amount = 0
        items_list = []
        
        for item in items:
            item_dict = dict(item)
            # Рассчитываем стоимость позиции
            item_total = item_dict['quantity'] * item_dict['price_at_moment']
            item_dict['item_total'] = item_total
            base_amount += item_total
            items_list.append(item_dict)
        
        # Транспортные расходы (10% от базовой стоимости)
        shipping_cost = base_amount * 0.1
        
        # Получаем скидку клиента
        try:
            if order_dict['order_date']:
                order_year = datetime.strptime(order_dict['order_date'], '%Y-%m-%d').year
            else:
                order_year = datetime.now().year
        except:
            order_year = datetime.now().year
            
        discount = calculate_supplier_discount(client_id, order_year)
        
        # Рассчитываем итоговую стоимость с учётом скидки
        total_with_shipping = base_amount + shipping_cost
        total_amount_calculated = total_with_shipping * (1 - discount / 100) if discount > 0 else total_with_shipping
        
        # Добавляем рассчитанные поля в словарь заказа
        order_dict['base_amount'] = base_amount
        order_dict['shipping_cost'] = shipping_cost
        order_dict['discount'] = discount
        order_dict['total_amount_calculated'] = total_amount_calculated
        order_dict['display_total'] = order_dict.get('total_amount', total_amount_calculated)
        order_dict['order_items'] = items_list
        order_dict['items_count'] = len(items_list)
        
        orders_with_items.append(order_dict)

    # Считаем общую статистику
    total_stats = conn.execute('''
        SELECT 
            COUNT(DISTINCT o.id) as total_orders,
            SUM(o.total_amount) as total_amount,
            SUM(CASE WHEN o.status = 'Выполнен' THEN 1 ELSE 0 END) as completed_orders,
            COALESCE(SUM(oi.quantity), 0) as total_quantity
        FROM Orders o
        LEFT JOIN OrderItems oi ON o.id = oi.order_id
        WHERE o.client_id = ?
    ''', (client_id,)).fetchone()

    conn.close()

    return render_template('order_history.html',
                           client=client,
                           orders=orders_with_items,
                           total_stats=total_stats)

@app.route('/update_order_status/<int:order_id>', methods=['POST'])
def update_order_status(order_id):
    """Обновление статуса заказа"""
    try:
        new_status = request.form['status']
        
        conn = get_db_connection()
        conn.execute('UPDATE Orders SET status = ? WHERE id = ?', (new_status, order_id))
        conn.commit()
        conn.close()
        
        flash(f'Статус заказа обновлен на "{new_status}"', 'success')
    except Exception as e:
        flash(f'Ошибка при обновлении статуса: {str(e)}', 'error')
    
    return redirect(request.referrer or url_for('orders_list'))

@app.route('/add_order/<int:client_id>', methods=['GET', 'POST'])
def add_order(client_id):
    if request.method == 'POST':
        try:
            order_date = request.form['order_date']
            status = request.form.get('status', 'Новый')
            
            conn = get_db_connection()
            
            # Проверяем наличие таблицы OrderItems
            cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OrderItems'")
            has_orderitems = cursor.fetchone() is not None
            
            if has_orderitems:
                # Новая структура
                cursor = conn.execute('''
                    INSERT INTO Orders (client_id, order_date, status, total_amount)
                    VALUES (?, ?, ?, 0)
                ''', (client_id, order_date, status))
                
                order_id = cursor.lastrowid
                
                # Получаем позиции из формы
                product_ids = request.form.getlist('product_id[]')
                quantities = request.form.getlist('quantity[]')
                
                total = 0
                
                for i in range(len(product_ids)):
                    if product_ids[i] and quantities[i]:
                        product_id = int(product_ids[i])
                        quantity = int(quantities[i])
                        
                        # Получаем цену товара
                        product = conn.execute('SELECT min_price FROM Catalog WHERE id = ?', (product_id,)).fetchone()
                        
                        if product:
                            price = product['min_price']
                            
                            # Добавляем позицию
                            conn.execute('''
                                INSERT INTO OrderItems (order_id, product_id, quantity, price_at_moment)
                                VALUES (?, ?, ?, ?)
                            ''', (order_id, product_id, quantity, price))
                            
                            # Считаем сумму с транспортными расходами
                            total += quantity * price * 1.1
                
                # Рассчитываем скидку
                year = datetime.strptime(order_date, '%Y-%m-%d').year
                discount = calculate_supplier_discount(client_id, year)
                
                # Применяем скидку
                total_with_discount = total * (1 - discount/100)
                
                # Обновляем сумму заказа
                conn.execute('UPDATE Orders SET total_amount = ? WHERE id = ?', (total_with_discount, order_id))
                
                discount_msg = f' Применена накопительная скидка {discount}% (экономия {total * discount/100:.2f} руб.)' if discount > 0 else ''
                
            else:
                # Старая структура (для совместимости)
                product_id = request.form.get('product_id')
                quantity = int(request.form.get('quantity', 1))
                
                product = conn.execute('SELECT min_price FROM Catalog WHERE id = ?', (product_id,)).fetchone()
                
                if not product:
                    flash('Товар не найден', 'error')
                    return redirect(url_for('add_order', client_id=client_id))
                
                base_amount = product['min_price'] * quantity
                total_amount = base_amount * 1.1  # с транспортом
                
                cursor = conn.execute('''
                    INSERT INTO Orders (client_id, product_id, quantity, order_date, total_amount, status)
                    VALUES (?, ?, ?, ?, ?, ?)
                ''', (client_id, product_id, quantity, order_date, total_amount, status))
                
                order_id = cursor.lastrowid
                discount_msg = ''
            
            conn.commit()
            conn.close()
            
            flash(f'Заказ успешно добавлен!{discount_msg}', 'success')
            return redirect(url_for('order_history', client_id=client_id))

        except Exception as e:
            flash(f'Ошибка при добавлении заказа: {str(e)}', 'error')
            return redirect(url_for('add_order', client_id=client_id))

    # GET запрос
    conn = get_db_connection()

    client = conn.execute('''
        SELECT c.*, ct.type_name as contractor_type_name
        FROM Clients c 
        LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
        WHERE c.id = ?
    ''', (client_id,)).fetchone()

    products = conn.execute('''
        SELECT cat.*, c.category_name, m.material_name
        FROM Catalog cat
        LEFT JOIN Categories c ON cat.category_id = c.id
        LEFT JOIN Materials m ON cat.material_id = m.id
        ORDER BY c.category_name, cat.product_name
    ''').fetchall()

    conn.close()

    if not client:
        flash('Клиент не найден', 'error')
        return redirect(url_for('index'))

    today = datetime.now().strftime('%Y-%m-%d')

    return render_template('add_order.html', client=client, products=products, today=today)

@app.route('/orders_list')
def orders_list():
    """Список всех заказов"""
    conn = get_db_connection()
    
    # Получаем всех клиентов для выпадающего списка
    clients = conn.execute('''
        SELECT id, name FROM Clients ORDER BY name
    ''').fetchall()
    
    # Проверяем наличие таблицы OrderItems
    cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OrderItems'")
    has_orderitems = cursor.fetchone() is not None
    
    if has_orderitems:
        # Новая структура
        orders = conn.execute('''
            SELECT 
                o.id,
                o.client_id,
                o.order_date,
                o.total_amount,
                o.status,
                c.name as client_name,
                c.inn as client_inn,
                COUNT(oi.id) as items_count,
                SUM(oi.quantity) as total_quantity,
                GROUP_CONCAT(cat.product_name, ', ') as products_list
            FROM Orders o
            LEFT JOIN Clients c ON o.client_id = c.id
            LEFT JOIN OrderItems oi ON o.id = oi.order_id
            LEFT JOIN Catalog cat ON oi.product_id = cat.id
            GROUP BY o.id
            ORDER BY o.order_date DESC, o.id DESC
            LIMIT 100
        ''').fetchall()
        
        # Считаем общую статистику
        total_stats = conn.execute('''
            SELECT 
                COUNT(*) as total_orders,
                SUM(o.total_amount) as total_amount,
                SUM(CASE WHEN o.status = 'Выполнен' THEN 1 ELSE 0 END) as completed_orders
            FROM Orders o
        ''').fetchone()
        
    else:
        # Старая структура (с product_id и quantity в Orders)
        orders = conn.execute('''
            SELECT 
                o.id,
                o.client_id,
                o.order_date,
                o.total_amount,
                o.status,
                o.quantity,
                c.name as client_name,
                c.inn as client_inn,
                cat.product_name,
                cat.article,
                cat2.category_name
            FROM Orders o
            LEFT JOIN Clients c ON o.client_id = c.id
            LEFT JOIN Catalog cat ON o.product_id = cat.id
            LEFT JOIN Categories cat2 ON cat.category_id = cat2.id
            ORDER BY o.order_date DESC, o.id DESC
            LIMIT 100
        ''').fetchall()
        
        # Считаем общую статистику
        total_stats = conn.execute('''
            SELECT 
                COUNT(*) as total_orders,
                SUM(quantity) as total_quantity,
                SUM(total_amount) as total_amount,
                SUM(CASE WHEN status = 'Выполнен' THEN 1 ELSE 0 END) as completed_orders
            FROM Orders
        ''').fetchone()
    
    conn.close()
    
    return render_template('orders_list.html', orders=orders, total_stats=total_stats, clients=clients, has_orderitems=has_orderitems)

# ========== МАРШРУТ ДЛЯ КАТАЛОГА ==========

@app.route('/catalog')
def catalog():
    conn = get_db_connection()
    
    # Получаем список всех категорий для фильтра
    categories = conn.execute('SELECT * FROM Categories ORDER BY category_name').fetchall()
    
    # Получаем список всех поставщиков для фильтра
    suppliers = conn.execute('SELECT id, name FROM Clients ORDER BY name').fetchall()
    
    # Получаем параметры фильтрации из URL
    search_query = request.args.get('search', '')
    category_id = request.args.get('category', 'all')
    supplier_id = request.args.get('supplier', 'all')
    sort_by = request.args.get('sort', 'name_asc')
    
    # Базовый SQL запрос с добавлением поставщика
    sql = '''
        SELECT cat.*, c.category_name, m.material_name, m.color,
               cl.name as supplier_name, cl.id as supplier_id
        FROM Catalog cat
        LEFT JOIN Categories c ON cat.category_id = c.id
        LEFT JOIN Materials m ON cat.material_id = m.id
        LEFT JOIN Clients cl ON m.supplier = cl.name
        WHERE 1=1
    '''
    params = []
    
    # Поиск по названию или артикулу
    if search_query:
        sql += " AND (cat.product_name LIKE ? OR cat.article LIKE ?)"
        params.extend([f'%{search_query}%', f'%{search_query}%'])
    
    # Фильтр по категории
    if category_id != 'all':
        sql += " AND cat.category_id = ?"
        params.append(category_id)
    
    # Фильтр по поставщику
    if supplier_id != 'all':
        sql += " AND cl.id = ?"
        params.append(supplier_id)
    
    # Сортировка
    if sort_by == 'name_asc':
        sql += " ORDER BY cat.product_name ASC"
    elif sort_by == 'name_desc':
        sql += " ORDER BY cat.product_name DESC"
    elif sort_by == 'price_asc':
        sql += " ORDER BY cat.min_price ASC"
    elif sort_by == 'price_desc':
        sql += " ORDER BY cat.min_price DESC"
    elif sort_by == 'stock_desc':
        sql += " ORDER BY cat.quantity_in_stock DESC"
    else:
        sql += " ORDER BY cat.product_name ASC"
    
    # Выполняем запрос
    products = conn.execute(sql, params).fetchall()
    
    # Получаем количество товаров в корзине для бейджа
    cart_count = 0
    if 'cart' in session:
        cart_count = len(session['cart'])
    
    conn.close()
    
    return render_template('catalog.html', 
                         products=products, 
                         categories=categories,
                         suppliers=suppliers,
                         cart_count=cart_count)

# ========== МАРШРУТЫ ДЛЯ УПРАВЛЕНИЯ ТОВАРАМИ ==========

@app.route('/add_product', methods=['GET', 'POST'])
def add_product():
    """Добавление нового товара"""
    if request.method == 'POST':
        try:
            product_name = request.form['product_name']
            article = request.form['article']
            category_id = request.form['category_id']
            material_id = request.form.get('material_id') or None
            description = request.form.get('description', '')
            min_price = float(request.form['min_price'])
            quantity_in_stock = int(request.form.get('quantity_in_stock', 0))

            conn = get_db_connection()
            
            # Проверяем уникальность артикула
            existing = conn.execute('SELECT id FROM Catalog WHERE article = ?', (article,)).fetchone()
            if existing:
                flash('Товар с таким артикулом уже существует', 'error')
                conn.close()
                return redirect(url_for('add_product'))

            conn.execute('''
                INSERT INTO Catalog (product_name, article, category_id, material_id, description, min_price, quantity_in_stock)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (product_name, article, category_id, material_id, description, min_price, quantity_in_stock))

            conn.commit()
            conn.close()
            
            flash('Товар успешно добавлен!', 'success')
            return redirect(url_for('catalog'))

        except Exception as e:
            flash(f'Ошибка: {str(e)}', 'error')
            return redirect(url_for('add_product'))

    # GET запрос
    conn = get_db_connection()
    categories = conn.execute('SELECT * FROM Categories ORDER BY category_name').fetchall()
    materials = conn.execute('SELECT * FROM Materials ORDER BY material_name').fetchall()
    conn.close()
    
    return render_template('add_product.html', categories=categories, materials=materials)

@app.route('/edit_product/<int:product_id>', methods=['GET', 'POST'])
def edit_product(product_id):
    """Редактирование товара"""
    conn = get_db_connection()

    if request.method == 'POST':
        try:
            product_name = request.form['product_name']
            article = request.form['article']
            category_id = request.form['category_id']
            material_id = request.form.get('material_id') or None
            description = request.form.get('description', '')
            min_price = float(request.form['min_price'])
            quantity_in_stock = int(request.form.get('quantity_in_stock', 0))

            # Проверяем уникальность артикула (исключая текущий товар)
            existing = conn.execute(
                'SELECT id FROM Catalog WHERE article = ? AND id != ?',
                (article, product_id)
            ).fetchone()
            if existing:
                flash('Товар с таким артикулом уже существует', 'error')
                conn.close()
                return redirect(url_for('edit_product', product_id=product_id))

            conn.execute('''
                UPDATE Catalog 
                SET product_name = ?, article = ?, category_id = ?, material_id = ?, 
                    description = ?, min_price = ?, quantity_in_stock = ?
                WHERE id = ?
            ''', (product_name, article, category_id, material_id, description, min_price, quantity_in_stock, product_id))

            conn.commit()
            conn.close()
            
            flash('Товар успешно обновлен!', 'success')
            return redirect(url_for('catalog'))

        except Exception as e:
            flash(f'Ошибка: {str(e)}', 'error')
            return redirect(url_for('edit_product', product_id=product_id))

    # GET запрос
    product = conn.execute('''
        SELECT cat.*, c.category_name, m.material_name
        FROM Catalog cat
        LEFT JOIN Categories c ON cat.category_id = c.id
        LEFT JOIN Materials m ON cat.material_id = m.id
        WHERE cat.id = ?
    ''', (product_id,)).fetchone()

    categories = conn.execute('SELECT * FROM Categories ORDER BY category_name').fetchall()
    materials = conn.execute('SELECT * FROM Materials ORDER BY material_name').fetchall()
    conn.close()

    if not product:
        flash('Товар не найден', 'error')
        return redirect(url_for('catalog'))

    return render_template('edit_product.html', product=product, categories=categories, materials=materials)

@app.route('/delete_product/<int:product_id>', methods=['POST'])
def delete_product(product_id):
    """Удаление товара"""
    try:
        conn = get_db_connection()
        
        # Проверяем наличие таблицы OrderItems
        cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OrderItems'")
        has_orderitems = cursor.fetchone() is not None
        
        if has_orderitems:
            # Новая структура
            items = conn.execute('SELECT COUNT(*) as count FROM OrderItems WHERE product_id = ?', (product_id,)).fetchone()
            if items['count'] > 0:
                flash('Нельзя удалить товар, который есть в заказах', 'error')
                conn.close()
                return redirect(url_for('catalog'))
        else:
            # Старая структура
            orders = conn.execute('SELECT COUNT(*) as count FROM Orders WHERE product_id = ?', (product_id,)).fetchone()
            if orders['count'] > 0:
                flash('Нельзя удалить товар, который есть в заказах', 'error')
                conn.close()
                return redirect(url_for('catalog'))
        
        conn.execute('DELETE FROM Catalog WHERE id = ?', (product_id,))
        conn.commit()
        conn.close()
        
        flash('Товар успешно удален', 'success')
    except Exception as e:
        flash(f'Ошибка при удалении: {str(e)}', 'error')
    
    return redirect(url_for('catalog'))

# ========== МАРШРУТЫ ДЛЯ КОРЗИНЫ ==========

@app.route('/cart')
def view_cart():
    """Просмотр корзины"""
    cart = session.get('cart', {})
    cart_items = []
    total_amount = 0
    
    conn = get_db_connection()
    
    for key, item_data in cart.items():
        product_id = item_data['product_id']
        quantity = item_data['quantity']
        supplier_id = item_data.get('supplier_id')
        
        product = conn.execute('''
            SELECT cat.*, c.category_name, m.material_name, cat.quantity_in_stock
            FROM Catalog cat
            LEFT JOIN Categories c ON cat.category_id = c.id
            LEFT JOIN Materials m ON cat.material_id = m.id
            WHERE cat.id = ?
        ''', (product_id,)).fetchone()
        
        if product:
            # Получаем информацию о поставщике
            supplier = None
            if supplier_id:
                supplier = conn.execute('SELECT name FROM Clients WHERE id = ?', (supplier_id,)).fetchone()
            
            cart_items.append({
                'key': key,
                'product_id': product_id,
                'supplier_id': supplier_id,
                'supplier_name': supplier['name'] if supplier else 'Не указан',
                'product_name': product['product_name'],
                'article': product['article'],
                'category_name': product['category_name'],
                'material_name': product['material_name'],
                'price': product['min_price'],
                'quantity': quantity,
                'in_stock': product['quantity_in_stock'] or 0
            })
            total_amount += product['min_price'] * quantity
    
    conn.close()
    
    return render_template('cart_new.html', cart_items=cart_items, total_amount=total_amount)

@app.route('/add_to_cart/<int:product_id>', methods=['POST'])
def add_to_cart(product_id):
    """Добавление товара в корзину с выбором поставщика"""
    quantity = int(request.form.get('quantity', 1))
    supplier_id = request.form.get('supplier_id')
    
    if not supplier_id:
        flash('Пожалуйста, выберите поставщика', 'error')
        return redirect(url_for('catalog'))
    
    # Получаем информацию о товаре для проверки наличия
    conn = get_db_connection()
    product = conn.execute('SELECT quantity_in_stock FROM Catalog WHERE id = ?', (product_id,)).fetchone()
    conn.close()
    
    if not product or product['quantity_in_stock'] < quantity:
        flash('Недостаточно товара на складе', 'error')
        return redirect(url_for('catalog'))
    
    cart = session.get('cart', {})
    
    # Создаем уникальный ключ для комбинации товар + поставщик
    key = f"{product_id}_{supplier_id}"
    
    if key in cart:
        cart[key]['quantity'] += quantity
    else:
        cart[key] = {
            'product_id': product_id,
            'supplier_id': int(supplier_id),
            'quantity': quantity
        }
    
    session['cart'] = cart
    flash('Товар добавлен в корзину!', 'success')
    
    return redirect(url_for('catalog'))

@app.route('/update_cart/<path:item_key>', methods=['POST'])
def update_cart(item_key):
    """Обновление количества товара в корзине"""
    quantity = int(request.form.get('quantity', 1))
    
    cart = session.get('cart', {})
    
    if item_key in cart:
        if quantity > 0:
            cart[item_key]['quantity'] = quantity
        else:
            cart.pop(item_key, None)
    
    session['cart'] = cart
    
    return redirect(url_for('view_cart'))

@app.route('/remove_from_cart/<path:item_key>', methods=['POST'])
def remove_from_cart(item_key):
    """Удаление товара из корзины"""
    cart = session.get('cart', {})
    cart.pop(item_key, None)
    session['cart'] = cart
    
    return redirect(url_for('view_cart'))

@app.route('/checkout', methods=['POST'])
def checkout():
    """Оформление заказа из корзины"""
    cart = session.get('cart', {})
    
    if not cart:
        flash('Корзина пуста', 'error')
        return redirect(url_for('catalog'))
    
    # Группируем товары по поставщикам для создания отдельных заказов
    orders_by_supplier = {}
    
    conn = get_db_connection()
    
    for key, item_data in cart.items():
        supplier_id = item_data['supplier_id']
        product_id = item_data['product_id']
        quantity = item_data['quantity']
        
        if supplier_id not in orders_by_supplier:
            orders_by_supplier[supplier_id] = []
        
        # Получаем цену товара
        product = conn.execute('SELECT min_price FROM Catalog WHERE id = ?', (product_id,)).fetchone()
        if product:
            orders_by_supplier[supplier_id].append({
                'product_id': product_id,
                'quantity': quantity,
                'price': product['min_price']
            })
    
    # Создаем заказы для каждого поставщика
    orders_created = 0
    today = datetime.now().strftime('%Y-%m-%d')
    
    for supplier_id, items in orders_by_supplier.items():
        # Считаем общую сумму с транспортом
        total = 0
        for item in items:
            total += item['quantity'] * item['price'] * 1.1
        
        # Рассчитываем скидку
        discount = calculate_supplier_discount(supplier_id, datetime.now().year)
        total_with_discount = total * (1 - discount/100)
        
        # Создаем заказ
        cursor = conn.execute('''
            INSERT INTO Orders (client_id, order_date, total_amount, status)
            VALUES (?, ?, ?, ?)
        ''', (supplier_id, today, total_with_discount, 'Новый'))
        
        order_id = cursor.lastrowid
        
        # Добавляем позиции
        for item in items:
            conn.execute('''
                INSERT INTO OrderItems (order_id, product_id, quantity, price_at_moment)
                VALUES (?, ?, ?, ?)
            ''', (order_id, item['product_id'], item['quantity'], item['price']))
        
        orders_created += 1
    
    conn.commit()
    conn.close()
    
    # Очищаем корзину
    session.pop('cart', None)
    
    flash(f'Заказ успешно оформлен! Создано {orders_created} заказов.', 'success')
    return redirect(url_for('orders_list'))

# ========== МАРШРУТ ДЛЯ МАТЕРИАЛОВ ==========

@app.route('/materials')
def materials():
    """Список всех материалов"""
    conn = get_db_connection()
    
    materials = conn.execute('''
        SELECT * FROM Materials 
        ORDER BY material_name
    ''').fetchall()
    
    conn.close()
    
    return render_template('materials.html', materials=materials)

# ========== СЛУЖЕБНЫЕ МАРШРУТЫ ==========

@app.route('/favicon.ico')
def favicon():
    return send_from_directory(os.path.join(app.root_path, 'static'),
                               'favicon.ico', mimetype='image/vnd.microsoft.icon')

@app.route('/static/<path:filename>')
def static_files(filename):
    return send_from_directory(app.root_path + '/static', filename)

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