from flask import Flask, render_template, request, redirect, url_for, flash
import sqlite3
import re
from datetime import datetime
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
DATABASE_PATH = BASE_DIR / "jewelry.db"

app = Flask(__name__)
app.config["SECRET_KEY"] = "jewelry-secret"

def get_db():
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    if DATABASE_PATH.exists():
        return
    conn = get_db()
    
    # Таблица металлов
    conn.execute("""
        CREATE TABLE IF NOT EXISTS metal_types (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            purity INTEGER NOT NULL,
            price_per_gram REAL NOT NULL
        )
    """)
    
    # Таблица товаров
    conn.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            metal_type_id INTEGER NOT NULL,
            sku TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL,
            weight_grams REAL NOT NULL,
            complexity_factor REAL NOT NULL,
            calculated_price REAL NOT NULL,
            description TEXT
        )
    """)
    
    # Таблица партнеров
    conn.execute("""
        CREATE TABLE IF NOT EXISTS partners (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            company_name TEXT NOT NULL,
            director_name TEXT NOT NULL,
            email TEXT NOT NULL,
            phone TEXT NOT NULL,
            legal_address TEXT NOT NULL,
            inn TEXT NOT NULL UNIQUE,
            total_sales REAL NOT NULL DEFAULT 0
        )
    """)
    
    # Таблица заявок
    conn.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            partner_id INTEGER NOT NULL,
            order_number TEXT NOT NULL UNIQUE,
            order_date TEXT NOT NULL,
            discount_percent REAL NOT NULL,
            subtotal_amount REAL NOT NULL,
            total_amount REAL NOT NULL,
            notes TEXT
        )
    """)
    
    # Таблица позиций заявок
    conn.execute("""
        CREATE TABLE IF NOT EXISTS order_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity REAL NOT NULL,
            price_at_moment REAL NOT NULL,
            line_total REAL NOT NULL
        )
    """)
    
    # Заполняем демо-данными
    conn.execute("INSERT OR IGNORE INTO metal_types (name, purity, price_per_gram) VALUES ('Золото', 585, 3500)")
    conn.execute("INSERT OR IGNORE INTO metal_types (name, purity, price_per_gram) VALUES ('Золото', 750, 4500)")
    conn.execute("INSERT OR IGNORE INTO metal_types (name, purity, price_per_gram) VALUES ('Серебро', 925, 85)")
    conn.execute("INSERT OR IGNORE INTO metal_types (name, purity, price_per_gram) VALUES ('Платина', 950, 2800)")
    
    conn.execute("INSERT OR IGNORE INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description) VALUES (1, 'RNG-001', 'Кольцо Классическое', 3.5, 1.2, 8600.70, 'Классическое обручальное кольцо')")
    conn.execute("INSERT OR IGNORE INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description) VALUES (1, 'EAR-001', 'Серьги Капля', 2.8, 1.5, 8600.70, 'Элегантные серьги-капли')")
    conn.execute("INSERT OR IGNORE INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description) VALUES (2, 'CHN-001', 'Цепь Плетение', 12.0, 1.0, 40500.00, 'Массивная золотая цепь')")
    conn.execute("INSERT OR IGNORE INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description) VALUES (3, 'BRC-001', 'Браслет Нежный', 6.5, 1.3, 663.91, 'Серебряный браслет с фианитами')")
    conn.execute("INSERT OR IGNORE INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description) VALUES (3, 'EAR-002', 'Серьги Цветок', 3.0, 1.2, 283.05, 'Серебряные серьги-цветы')")
    conn.execute("INSERT OR IGNORE INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description) VALUES (4, 'RNG-002', 'Кольцо Престиж', 5.5, 2.0, 29260.00, 'Платиновое кольцо с бриллиантом')")
    
    conn.execute("INSERT OR IGNORE INTO partners (company_name, director_name, email, phone, legal_address, inn, total_sales) VALUES ('Ювелирный Дом Блеск', 'Иван Петров', 'blisk@example.com', '+7 495 123-45-67', 'Москва, ул. Цветная, д.1', '770123456789', 158200)")
    conn.execute("INSERT OR IGNORE INTO partners (company_name, director_name, email, phone, legal_address, inn, total_sales) VALUES ('ИП Сидорова А.А.', 'Анна Сидорова', 'sidorova@example.com', '+7 812 765-43-21', 'СПб, Невский пр., д.10', '781234567890', 0)")
    conn.execute("INSERT OR IGNORE INTO partners (company_name, director_name, email, phone, legal_address, inn, total_sales) VALUES ('Магазин Золотой Лотос', 'Сергей Иванов', 'lotus@example.com', '+7 383 555-11-22', 'Новосибирск, ул. Ленина, д.5', '540123456789', 0)")
    conn.execute("INSERT OR IGNORE INTO partners (company_name, director_name, email, phone, legal_address, inn, total_sales) VALUES ('Ювелирная Лавка', 'Михаил Козлов', 'lavka@example.com', '+7 495 888-99-00', 'Москва, ул. Тверская, д.10', '500123456789', 650000)")
    
    conn.execute("INSERT OR IGNORE INTO orders (partner_id, order_number, order_date, discount_percent, subtotal_amount, total_amount, notes) VALUES (1, 'ORD-001', '2026-03-20', 5, 24653.40, 23420.73, 'Срочная поставка')")
    conn.execute("INSERT OR IGNORE INTO orders (partner_id, order_number, order_date, discount_percent, subtotal_amount, total_amount, notes) VALUES (2, 'ORD-002', '2026-03-15', 0, 40500.00, 40500.00, '')")
    conn.execute("INSERT OR IGNORE INTO orders (partner_id, order_number, order_date, discount_percent, subtotal_amount, total_amount, notes) VALUES (4, 'ORD-003', '2026-03-10', 5, 8600.70, 8170.67, '')")
    
    conn.execute("INSERT OR IGNORE INTO order_items (order_id, product_id, quantity, price_at_moment, line_total) VALUES (1, 1, 2, 8600.70, 17201.40)")
    conn.execute("INSERT OR IGNORE INTO order_items (order_id, product_id, quantity, price_at_moment, line_total) VALUES (1, 2, 1, 8600.70, 8600.70)")
    conn.execute("INSERT OR IGNORE INTO order_items (order_id, product_id, quantity, price_at_moment, line_total) VALUES (2, 3, 1, 40500.00, 40500.00)")
    conn.execute("INSERT OR IGNORE INTO order_items (order_id, product_id, quantity, price_at_moment, line_total) VALUES (3, 5, 2, 283.05, 566.10)")
    
    conn.commit()
    conn.close()
    print("База данных создана с демо-данными!")

# ==================== ФУНКЦИИ ВАЛИДАЦИИ ====================

def validate_inn(inn):
    """Проверка ИНН (10 или 12 цифр)"""
    if not inn or not inn.strip():
        return False, "ИНН не может быть пустым"
    inn = inn.strip()
    if not inn.isdigit():
        return False, "ИНН должен содержать только цифры"
    if len(inn) not in [10, 12]:
        return False, "ИНН должен содержать 10 или 12 цифр"
    return True, ""

def validate_phone(phone):
    """Проверка телефона (должен содержать минимум 10 цифр)"""
    if not phone or not phone.strip():
        return False, "Телефон не может быть пустым"
    
    digits = re.sub(r'\D', '', phone)
    
    if len(digits) < 10:
        return False, f"Слишком мало цифр в номере телефона (найдено {len(digits)}, требуется минимум 10)"
    if len(digits) > 15:
        return False, f"Слишком много цифр в номере телефона (найдено {len(digits)}, максимум 15)"
    
    return True, ""

def validate_email(email):
    """Проверка email"""
    if not email or not email.strip():
        return False, "Email не может быть пустым"
    email = email.strip()
    if '@' not in email or '.' not in email:
        return False, "Введите корректный email (пример: name@domain.ru)"
    if email.count('@') != 1:
        return False, "Email должен содержать один символ @"
    local, domain = email.split('@')
    if not local or not domain:
        return False, "Email должен содержать имя и домен"
    if '.' not in domain:
        return False, "Домен должен содержать точку"
    return True, ""

def validate_address(address):
    """Проверка юридического адреса"""
    if not address or not address.strip():
        return False, "Юридический адрес не может быть пустым"
    if len(address.strip()) < 5:
        return False, "Адрес слишком короткий (минимум 5 символов)"
    return True, ""

def validate_company_name(name):
    """Проверка названия компании"""
    if not name or not name.strip():
        return False, "Название компании не может быть пустым"
    if len(name.strip()) < 2:
        return False, "Название компании слишком короткое (минимум 2 символа)"
    return True, ""

def validate_director_name(name):
    """Проверка ФИО директора"""
    if not name or not name.strip():
        return False, "ФИО директора не может быть пустым"
    if len(name.strip()) < 5:
        return False, "ФИО директора слишком короткое (минимум 5 символов)"
    if ' ' not in name.strip():
        return False, "Введите полное ФИО (фамилию и имя)"
    return True, ""

def validate_sku(sku):
    """Проверка артикула товара"""
    if not sku or not sku.strip():
        return False, "Артикул не может быть пустым"
    sku = sku.strip()
    if len(sku) < 3:
        return False, "Артикул слишком короткий (минимум 3 символа)"
    if len(sku) > 50:
        return False, "Артикул слишком длинный (максимум 50 символов)"
    if not re.match(r'^[A-Za-z0-9\-_]+$', sku):
        return False, "Артикул может содержать только буквы, цифры, дефис и подчеркивание"
    return True, ""

def validate_product_name(name):
    """Проверка названия товара"""
    if not name or not name.strip():
        return False, "Название товара не может быть пустым"
    if len(name.strip()) < 2:
        return False, "Название товара слишком короткое (минимум 2 символа)"
    return True, ""

def validate_weight(weight):
    """Проверка веса товара"""
    try:
        w = float(weight)
        if w <= 0:
            return False, "Вес должен быть больше 0"
        if w > 10000:
            return False, "Вес слишком большой (максимум 10000 г)"
        return True, ""
    except ValueError:
        return False, "Вес должен быть числом"

def validate_complexity(complexity):
    """Проверка коэффициента сложности"""
    try:
        c = float(complexity)
        if c < 1.0:
            return False, "Коэффициент сложности должен быть не меньше 1.0"
        if c > 5.0:
            return False, "Коэффициент сложности слишком большой (максимум 5.0)"
        return True, ""
    except ValueError:
        return False, "Коэффициент сложности должен быть числом"

def calculate_product_price(metal_price, weight, purity, complexity):
    return round(metal_price * weight * (purity / 1000) * complexity, 2)

def calculate_partner_discount(order_subtotal):
    """
    Расчет скидки на основе суммы текущей заявки
    - до 500 000 ₽ → 0%
    - от 500 000 до 1 000 000 ₽ → 5%
    - от 1 000 000 до 2 000 000 ₽ → 10%
    - свыше 2 000 000 ₽ → 15%
    """
    if order_subtotal >= 2000000:
        return 15.0
    if order_subtotal >= 1000000:
        return 10.0
    if order_subtotal >= 500000:
        return 5.0
    return 0.0

def get_discount_description(discount):
    """Возвращает описание диапазона скидки"""
    if discount == 15:
        return "свыше 2 000 000 ₽"
    if discount == 10:
        return "от 1 000 000 до 2 000 000 ₽"
    if discount == 5:
        return "от 500 000 до 1 000 000 ₽"
    return "до 500 000 ₽"

# ==================== ОСНОВНЫЕ МАРШРУТЫ ====================

@app.route("/")
def index():
    conn = get_db()
    
    orders = conn.execute("""
        SELECT o.*, p.company_name as partner_name, p.total_sales as partner_total_sales
        FROM orders o 
        JOIN partners p ON p.id = o.partner_id
        ORDER BY o.order_date DESC
    """).fetchall()
    
    partners = conn.execute("SELECT id, company_name FROM partners ORDER BY company_name").fetchall()
    
    partner_filter = request.args.get('partner_id', '')
    
    if partner_filter and partner_filter.isdigit():
        filtered_orders = [o for o in orders if o['partner_id'] == int(partner_filter)]
    else:
        filtered_orders = orders
    
    conn.close()
    return render_template("index.html", orders=filtered_orders, partners=partners, selected_partner=partner_filter)

# ==================== УПРАВЛЕНИЕ ТОВАРАМИ ====================

@app.route("/products")
def products():
    conn = get_db()
    products = conn.execute("""
        SELECT p.*, mt.name as metal_name, mt.purity, mt.price_per_gram
        FROM products p 
        JOIN metal_types mt ON mt.id = p.metal_type_id
        ORDER BY p.name
    """).fetchall()
    metal_types = conn.execute("SELECT * FROM metal_types").fetchall()
    conn.close()
    return render_template("products.html", products=products, metal_types=metal_types)

@app.route("/products/add", methods=["GET", "POST"])
def product_add():
    conn = get_db()
    metal_types = conn.execute("SELECT * FROM metal_types").fetchall()
    
    if request.method == "POST":
        try:
            metal_type_id = request.form["metal_type_id"]
            sku = request.form["sku"].strip() if request.form["sku"] else ""
            name = request.form["name"].strip() if request.form["name"] else ""
            weight = request.form["weight_grams"]
            complexity = request.form["complexity_factor"]
            description = request.form.get("description", "").strip()
            
            errors = []
            
            sku_valid, sku_error = validate_sku(sku)
            if not sku_valid:
                errors.append(sku_error)
            
            name_valid, name_error = validate_product_name(name)
            if not name_valid:
                errors.append(name_error)
            
            weight_valid, weight_error = validate_weight(weight)
            if not weight_valid:
                errors.append(weight_error)
            
            complexity_valid, complexity_error = validate_complexity(complexity)
            if not complexity_valid:
                errors.append(complexity_error)
            
            if not metal_type_id:
                errors.append("Выберите тип металла")
            
            if errors:
                for error in errors:
                    flash(error, "error")
                conn.close()
                return render_template("product_form.html", product=None, metal_types=metal_types)
            
            metal = conn.execute("SELECT price_per_gram, purity FROM metal_types WHERE id = ?", (int(metal_type_id),)).fetchone()
            price = calculate_product_price(metal["price_per_gram"], float(weight), metal["purity"], float(complexity))
            
            conn.execute("""
                INSERT INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (int(metal_type_id), sku, name, float(weight), float(complexity), price, description))
            conn.commit()
            flash("Товар успешно добавлен!", "success")
            return redirect(url_for("products"))
        except sqlite3.IntegrityError:
            flash("Ошибка: артикул уже существует!", "error")
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    conn.close()
    return render_template("product_form.html", product=None, metal_types=metal_types)

@app.route("/products/<int:product_id>/edit", methods=["GET", "POST"])
def product_edit(product_id):
    conn = get_db()
    product = conn.execute("SELECT * FROM products WHERE id = ?", (product_id,)).fetchone()
    metal_types = conn.execute("SELECT * FROM metal_types").fetchall()
    
    if request.method == "POST":
        try:
            metal_type_id = request.form["metal_type_id"]
            sku = request.form["sku"].strip() if request.form["sku"] else ""
            name = request.form["name"].strip() if request.form["name"] else ""
            weight = request.form["weight_grams"]
            complexity = request.form["complexity_factor"]
            description = request.form.get("description", "").strip()
            
            errors = []
            
            sku_valid, sku_error = validate_sku(sku)
            if not sku_valid:
                errors.append(sku_error)
            
            name_valid, name_error = validate_product_name(name)
            if not name_valid:
                errors.append(name_error)
            
            weight_valid, weight_error = validate_weight(weight)
            if not weight_valid:
                errors.append(weight_error)
            
            complexity_valid, complexity_error = validate_complexity(complexity)
            if not complexity_valid:
                errors.append(complexity_error)
            
            if not metal_type_id:
                errors.append("Выберите тип металла")
            
            if errors:
                for error in errors:
                    flash(error, "error")
                conn.close()
                return render_template("product_form.html", product=product, metal_types=metal_types)
            
            metal = conn.execute("SELECT price_per_gram, purity FROM metal_types WHERE id = ?", (int(metal_type_id),)).fetchone()
            price = calculate_product_price(metal["price_per_gram"], float(weight), metal["purity"], float(complexity))
            
            conn.execute("""
                UPDATE products SET metal_type_id=?, sku=?, name=?, weight_grams=?, 
                complexity_factor=?, calculated_price=?, description=? WHERE id=?
            """, (int(metal_type_id), sku, name, float(weight), float(complexity), price, description, product_id))
            conn.commit()
            flash("Товар успешно обновлен!", "success")
            return redirect(url_for("products"))
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    conn.close()
    return render_template("product_form.html", product=product, metal_types=metal_types)

@app.route("/products/<int:product_id>/delete", methods=["POST"])
def product_delete(product_id):
    conn = get_db()
    try:
        conn.execute("DELETE FROM products WHERE id = ?", (product_id,))
        conn.commit()
        flash("Товар удален!", "success")
    except sqlite3.IntegrityError:
        flash("Нельзя удалить товар, который есть в заявках!", "error")
    conn.close()
    return redirect(url_for("products"))

# ==================== УПРАВЛЕНИЕ ПАРТНЕРАМИ ====================

@app.route("/partners")
def partners():
    conn = get_db()
    partners = conn.execute("SELECT * FROM partners ORDER BY company_name").fetchall()
    conn.close()
    return render_template("partners.html", partners=partners)

@app.route("/partners/add", methods=["GET", "POST"])
def partner_add():
    if request.method == "POST":
        try:
            company_name = request.form["company_name"].strip() if request.form["company_name"] else ""
            director_name = request.form["director_name"].strip() if request.form["director_name"] else ""
            email = request.form["email"].strip() if request.form["email"] else ""
            phone = request.form["phone"].strip() if request.form["phone"] else ""
            legal_address = request.form["legal_address"].strip() if request.form["legal_address"] else ""
            inn = request.form["inn"].strip() if request.form["inn"] else ""
            total_sales = float(request.form.get("total_sales", 0))
            
            errors = []
            
            company_valid, company_error = validate_company_name(company_name)
            if not company_valid:
                errors.append(company_error)
            
            director_valid, director_error = validate_director_name(director_name)
            if not director_valid:
                errors.append(director_error)
            
            email_valid, email_error = validate_email(email)
            if not email_valid:
                errors.append(email_error)
            
            phone_valid, phone_error = validate_phone(phone)
            if not phone_valid:
                errors.append(phone_error)
            
            address_valid, address_error = validate_address(legal_address)
            if not address_valid:
                errors.append(address_error)
            
            inn_valid, inn_error = validate_inn(inn)
            if not inn_valid:
                errors.append(inn_error)
            
            if errors:
                for error in errors:
                    flash(error, "error")
                return render_template("partner_form.html", partner=None)
            
            conn = get_db()
            conn.execute("""
                INSERT INTO partners (company_name, director_name, email, phone, legal_address, inn, total_sales)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (company_name, director_name, email, phone, legal_address, inn, total_sales))
            conn.commit()
            conn.close()
            flash("Партнер успешно добавлен!", "success")
            return redirect(url_for("partners"))
            
        except sqlite3.IntegrityError:
            flash("Ошибка: партнер с таким ИНН уже существует!", "error")
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    return render_template("partner_form.html", partner=None)

@app.route("/partners/<int:partner_id>/edit", methods=["GET", "POST"])
def partner_edit(partner_id):
    conn = get_db()
    partner = conn.execute("SELECT * FROM partners WHERE id = ?", (partner_id,)).fetchone()
    
    if request.method == "POST":
        try:
            company_name = request.form["company_name"].strip() if request.form["company_name"] else ""
            director_name = request.form["director_name"].strip() if request.form["director_name"] else ""
            email = request.form["email"].strip() if request.form["email"] else ""
            phone = request.form["phone"].strip() if request.form["phone"] else ""
            legal_address = request.form["legal_address"].strip() if request.form["legal_address"] else ""
            inn = request.form["inn"].strip() if request.form["inn"] else ""
            total_sales = float(request.form.get("total_sales", 0))
            
            errors = []
            
            company_valid, company_error = validate_company_name(company_name)
            if not company_valid:
                errors.append(company_error)
            
            director_valid, director_error = validate_director_name(director_name)
            if not director_valid:
                errors.append(director_error)
            
            email_valid, email_error = validate_email(email)
            if not email_valid:
                errors.append(email_error)
            
            phone_valid, phone_error = validate_phone(phone)
            if not phone_valid:
                errors.append(phone_error)
            
            address_valid, address_error = validate_address(legal_address)
            if not address_valid:
                errors.append(address_error)
            
            inn_valid, inn_error = validate_inn(inn)
            if not inn_valid:
                errors.append(inn_error)
            
            if errors:
                for error in errors:
                    flash(error, "error")
                return render_template("partner_form.html", partner=partner)
            
            conn.execute("""
                UPDATE partners SET company_name=?, director_name=?, email=?, phone=?, 
                legal_address=?, inn=?, total_sales=? WHERE id=?
            """, (company_name, director_name, email, phone, legal_address, inn, total_sales, partner_id))
            conn.commit()
            flash("Партнер успешно обновлен!", "success")
            return redirect(url_for("partners"))
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    conn.close()
    return render_template("partner_form.html", partner=partner)

@app.route("/partners/<int:partner_id>/delete", methods=["POST"])
def partner_delete(partner_id):
    conn = get_db()
    try:
        conn.execute("DELETE FROM partners WHERE id = ?", (partner_id,))
        conn.commit()
        flash("Партнер удален!", "success")
    except sqlite3.IntegrityError:
        flash("Нельзя удалить партнера с существующими заявками!", "error")
    conn.close()
    return redirect(url_for("partners"))

# ==================== УПРАВЛЕНИЕ ЗАЯВКАМИ (С НОВОЙ СКИДКОЙ) ====================

@app.route("/orders/add", methods=["GET", "POST"])
def order_add():
    conn = get_db()
    partners = conn.execute("SELECT * FROM partners ORDER BY company_name").fetchall()
    products = conn.execute("SELECT * FROM products ORDER BY name").fetchall()
    
    if request.method == "POST":
        try:
            partner_id = int(request.form["partner_id"])
            order_number = request.form["order_number"].strip()
            notes = request.form.get("notes", "")
            
            if not order_number:
                flash("Номер заявки не может быть пустым!", "error")
                return render_template("order_form.html", order=None, partners=partners, products=products)
            
            product_ids = request.form.getlist("product_id[]")
            quantities = request.form.getlist("quantity[]")
            
            subtotal = 0.0
            items = []
            has_items = False
            
            for i in range(len(product_ids)):
                if product_ids[i] and quantities[i] and float(quantities[i]) > 0:
                    has_items = True
                    prod_id = int(product_ids[i])
                    qty = float(quantities[i])
                    prod = conn.execute("SELECT calculated_price FROM products WHERE id = ?", (prod_id,)).fetchone()
                    price = prod["calculated_price"]
                    line_total = qty * price
                    subtotal += line_total
                    items.append((prod_id, qty, price, line_total))
            
            if not has_items:
                flash("Добавьте хотя бы один товар в заявку!", "error")
                return render_template("order_form.html", order=None, partners=partners, products=products)
            
            # РАСЧЕТ СКИДКИ НА ОСНОВЕ СУММЫ ЗАЯВКИ
            discount = calculate_partner_discount(subtotal)
            total_amount = round(subtotal * (1 - discount / 100), 2)
            
            cursor = conn.execute("""
                INSERT INTO orders (partner_id, order_number, order_date, discount_percent, subtotal_amount, total_amount, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (partner_id, order_number, datetime.now().strftime("%Y-%m-%d"), discount, round(subtotal, 2), total_amount, notes))
            order_id = cursor.lastrowid
            
            for item in items:
                conn.execute("""
                    INSERT INTO order_items (order_id, product_id, quantity, price_at_moment, line_total)
                    VALUES (?, ?, ?, ?, ?)
                """, (order_id, item[0], item[1], item[2], item[3]))
            
            # Обновляем общую сумму продаж партнера (накопительная)
            conn.execute("UPDATE partners SET total_sales = total_sales + ? WHERE id = ?", (subtotal, partner_id))
            conn.commit()
            
            discount_range = get_discount_description(discount)
            flash(f"Заявка успешно создана! Скидка: {discount}% ({discount_range})", "success")
            return redirect(url_for("index"))
        except sqlite3.IntegrityError:
            flash("Ошибка: номер заявки уже существует!", "error")
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    conn.close()
    return render_template("order_form.html", order=None, partners=partners, products=products)

@app.route("/orders/<int:order_id>/edit", methods=["GET", "POST"])
def order_edit(order_id):
    conn = get_db()
    order = conn.execute("SELECT * FROM orders WHERE id = ?", (order_id,)).fetchone()
    order_items = conn.execute("SELECT * FROM order_items WHERE order_id = ?", (order_id,)).fetchall()
    partners = conn.execute("SELECT * FROM partners ORDER BY company_name").fetchall()
    products = conn.execute("SELECT * FROM products ORDER BY name").fetchall()
    
    if request.method == "POST":
        try:
            partner_id = int(request.form["partner_id"])
            order_number = request.form["order_number"].strip()
            notes = request.form.get("notes", "")
            
            product_ids = request.form.getlist("product_id[]")
            quantities = request.form.getlist("quantity[]")
            
            subtotal = 0.0
            items = []
            
            for i in range(len(product_ids)):
                if product_ids[i] and quantities[i] and float(quantities[i]) > 0:
                    prod_id = int(product_ids[i])
                    qty = float(quantities[i])
                    prod = conn.execute("SELECT calculated_price FROM products WHERE id = ?", (prod_id,)).fetchone()
                    price = prod["calculated_price"]
                    line_total = qty * price
                    subtotal += line_total
                    items.append((prod_id, qty, price, line_total))
            
            # РАСЧЕТ СКИДКИ НА ОСНОВЕ СУММЫ ЗАЯВКИ
            discount = calculate_partner_discount(subtotal)
            total_amount = round(subtotal * (1 - discount / 100), 2)
            
            # Возвращаем старую сумму продаж партнеру
            old_subtotal = order["subtotal_amount"]
            conn.execute("UPDATE partners SET total_sales = total_sales - ? WHERE id = ?", (old_subtotal, order["partner_id"]))
            
            conn.execute("""
                UPDATE orders SET partner_id=?, order_number=?, discount_percent=?, 
                subtotal_amount=?, total_amount=?, notes=? WHERE id=?
            """, (partner_id, order_number, discount, round(subtotal, 2), total_amount, notes, order_id))
            
            conn.execute("DELETE FROM order_items WHERE order_id = ?", (order_id,))
            for item in items:
                conn.execute("""
                    INSERT INTO order_items (order_id, product_id, quantity, price_at_moment, line_total)
                    VALUES (?, ?, ?, ?, ?)
                """, (order_id, item[0], item[1], item[2], item[3]))
            
            conn.execute("UPDATE partners SET total_sales = total_sales + ? WHERE id = ?", (subtotal, partner_id))
            conn.commit()
            
            discount_range = get_discount_description(discount)
            flash(f"Заявка успешно обновлена! Скидка: {discount}% ({discount_range})", "success")
            return redirect(url_for("index"))
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    conn.close()
    return render_template("order_form.html", order=order, order_items=order_items, partners=partners, products=products)

@app.route("/orders/<int:order_id>/delete", methods=["POST"])
def order_delete(order_id):
    conn = get_db()
    order = conn.execute("SELECT partner_id, subtotal_amount FROM orders WHERE id = ?", (order_id,)).fetchone()
    if order:
        conn.execute("UPDATE partners SET total_sales = total_sales - ? WHERE id = ?", (order["subtotal_amount"], order["partner_id"]))
    conn.execute("DELETE FROM orders WHERE id = ?", (order_id,))
    conn.commit()
    conn.close()
    flash("Заявка удалена!", "success")
    return redirect(url_for("index"))

# ==================== УПРАВЛЕНИЕ МЕТАЛЛАМИ ====================

@app.route("/metal_types")
def metal_types():
    conn = get_db()
    metals = conn.execute("SELECT * FROM metal_types").fetchall()
    conn.close()
    return render_template("metal_types.html", metals=metals)

@app.route("/metal_types/add", methods=["GET", "POST"])
def metal_type_add():
    if request.method == "POST":
        try:
            conn = get_db()
            name = request.form["name"].strip()
            purity = int(request.form["purity"])
            price = float(request.form["price_per_gram"])
            
            if not name:
                flash("Название металла не может быть пустым!", "error")
                return render_template("metal_form.html", metal=None)
            
            if purity <= 0 or purity > 1000:
                flash("Проба должна быть от 1 до 1000!", "error")
                return render_template("metal_form.html", metal=None)
            
            if price <= 0:
                flash("Цена должна быть больше 0!", "error")
                return render_template("metal_form.html", metal=None)
            
            conn.execute("INSERT INTO metal_types (name, purity, price_per_gram) VALUES (?, ?, ?)", 
                        (name, purity, price))
            conn.commit()
            conn.close()
            flash("Тип металла добавлен!", "success")
            return redirect(url_for("metal_types"))
        except sqlite3.IntegrityError:
            flash("Ошибка: такой металл уже существует!", "error")
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    return render_template("metal_form.html", metal=None)

@app.route("/metal_types/<int:metal_id>/edit", methods=["GET", "POST"])
def metal_type_edit(metal_id):
    conn = get_db()
    metal = conn.execute("SELECT * FROM metal_types WHERE id = ?", (metal_id,)).fetchone()
    
    if request.method == "POST":
        try:
            name = request.form["name"].strip()
            purity = int(request.form["purity"])
            price = float(request.form["price_per_gram"])
            
            conn.execute("UPDATE metal_types SET name=?, purity=?, price_per_gram=? WHERE id=?", 
                        (name, purity, price, metal_id))
            conn.commit()
            flash("Тип металла обновлен!", "success")
            return redirect(url_for("metal_types"))
        except Exception as e:
            flash(f"Ошибка: {e}", "error")
    
    conn.close()
    return render_template("metal_form.html", metal=metal)

@app.route("/metal_types/<int:metal_id>/delete", methods=["POST"])
def metal_type_delete(metal_id):
    conn = get_db()
    try:
        conn.execute("DELETE FROM metal_types WHERE id = ?", (metal_id,))
        conn.commit()
        flash("Тип металла удален!", "success")
    except sqlite3.IntegrityError:
        flash("Нельзя удалить металл, который используется в товарах!", "error")
    conn.close()
    return redirect(url_for("metal_types"))

if __name__ == "__main__":
    init_db()
    app.run(debug=True, port=5072)