import sqlite3
import time
from contextlib import contextmanager
from datetime import datetime

DATABASE = 'partners.db'


def get_db_connection():
    """Создает подключение к базе данных с правильными настройками"""
    conn = sqlite3.connect(DATABASE, timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn


@contextmanager
def get_db():
    """Контекстный менеджер для безопасной работы с БД"""
    conn = None
    try:
        conn = get_db_connection()
        yield conn
        if conn:
            conn.commit()
    except Exception as e:
        if conn:
            conn.rollback()
        raise e
    finally:
        if conn:
            conn.close()


def create_tables():
    """Создает все таблицы в базе данных"""
    with get_db() as conn:
        cursor = conn.cursor()

        cursor.executescript('''
        DROP TABLE IF EXISTS order_items;
        DROP TABLE IF EXISTS orders;
        DROP TABLE IF EXISTS products;
        DROP TABLE IF EXISTS partners;
        DROP TABLE IF EXISTS product_types;

        CREATE TABLE product_types (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type_name VARCHAR(100) NOT NULL UNIQUE,
            description TEXT
        );

        CREATE TABLE partners (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(255) NOT NULL,
            director VARCHAR(255),
            email VARCHAR(255) NOT NULL,
            phone VARCHAR(50),
            legal_address TEXT,
            inn VARCHAR(20) UNIQUE
        );

        CREATE TABLE products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_type_id INTEGER NOT NULL,
            product_name VARCHAR(255) NOT NULL,
            article VARCHAR(100) UNIQUE NOT NULL,
            min_partner_price DECIMAL(15,2) NOT NULL CHECK (min_partner_price > 0),
            FOREIGN KEY (product_type_id) REFERENCES product_types(id) ON DELETE RESTRICT
        );

        CREATE TABLE orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            partner_id INTEGER NOT NULL,
            order_date DATE NOT NULL,
            notes TEXT,
            discount_percent INTEGER DEFAULT 0,
            FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE RESTRICT
        );

        CREATE TABLE order_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL CHECK (quantity > 0),
            price_at_order DECIMAL(15,2) NOT NULL CHECK (price_at_order > 0),
            FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
        );
        ''')

        print("✅ Таблицы успешно созданы")


def calculate_discount(total_amount):
    """Рассчитывает скидку на основе общей суммы закупок за год"""
    if total_amount >= 300000:
        return 50
    elif total_amount >= 200000:
        return 30
    elif total_amount >= 150000:
        return 15
    else:
        return 0


def get_discount_description(discount_percent):
    """Возвращает описание скидки"""
    if discount_percent == 50:
        return '50% (300 000+ руб.)'
    elif discount_percent == 30:
        return '30% (200 000 - 299 999 руб.)'
    elif discount_percent == 15:
        return '15% (150 000 - 199 999 руб.)'
    else:
        return '0% (до 150 000 руб.)'


def calculate_order_total(order_id):
    """Рассчитывает общую стоимость заказа с учетом скидки и добавочной стоимости 10%"""
    try:
        with get_db() as conn:
            order_info = conn.execute('''
                SELECT o.*, p.id as partner_id, p.name as partner_name
                FROM orders o
                JOIN partners p ON o.partner_id = p.id
                WHERE o.id = ?
            ''', (order_id,)).fetchone()

            if not order_info:
                return None

            items = conn.execute('''
                SELECT oi.*, p.product_name
                FROM order_items oi
                JOIN products p ON oi.product_id = p.id
                WHERE oi.order_id = ?
            ''', (order_id,)).fetchall()

            subtotal_without_markup = 0
            items_list = []

            for item in items:
                # Базовая стоимость = количество × цена
                base_item_subtotal = item['quantity'] * item['price_at_order']
                # Добавляем 10% добавочной стоимости
                item_subtotal = base_item_subtotal * 1.1
                subtotal_without_markup += base_item_subtotal
                items_list.append({
                    'id': item['id'],
                    'product_name': item['product_name'],
                    'quantity': item['quantity'],
                    'price': item['price_at_order'],
                    'base_subtotal': round(base_item_subtotal, 2),
                    'subtotal_with_markup': round(item_subtotal, 2)
                })

            # Общая стоимость с добавочной стоимостью (без скидки)
            subtotal_with_markup = subtotal_without_markup * 1.1

            discount_percent = order_info['discount_percent'] if order_info['discount_percent'] else 0
            discount_desc = get_discount_description(discount_percent)

            discount_amount = subtotal_with_markup * discount_percent / 100
            total = subtotal_with_markup - discount_amount

            return {
                'order_id': order_id,
                'partner_id': order_info['partner_id'],
                'partner_name': order_info['partner_name'],
                'order_date': order_info['order_date'],
                'notes': order_info['notes'] if order_info['notes'] else '',
                'items': items_list,
                'items_count': len(items_list),
                'subtotal_without_markup': round(subtotal_without_markup, 2),
                'subtotal_with_markup': round(subtotal_with_markup, 2),
                'discount_percent': discount_percent,
                'discount_desc': discount_desc,
                'discount_amount': round(discount_amount, 2),
                'total': round(total, 2)
            }
    except Exception as e:
        print(f"Ошибка при расчете стоимости заказа {order_id}: {e}")
        return None


def calculate_discount_for_new_order(partner_id, order_date, current_order_amount_without_markup,
                                     exclude_order_id=None):
    """Рассчитывает скидку для нового заказа на основе предыдущих заказов в этом году"""
    try:
        with get_db() as conn:
            year = order_date[:4] if order_date else str(datetime.now().year)

            query = '''
                SELECT id
                FROM orders
                WHERE partner_id = ? AND strftime('%Y', order_date) = ?
            '''
            params = [partner_id, year]

            if exclude_order_id:
                query += ' AND id != ?'
                params.append(exclude_order_id)

            previous_orders = conn.execute(query, params).fetchall()

            total_previous_without_markup = 0
            for prev_order in previous_orders:
                order_total = conn.execute('''
                    SELECT COALESCE(SUM(quantity * price_at_order), 0) as total
                    FROM order_items WHERE order_id = ?
                ''', (prev_order['id'],)).fetchone()['total']
                total_previous_without_markup += order_total

            total_with_current_without_markup = total_previous_without_markup + current_order_amount_without_markup

            return calculate_discount(total_with_current_without_markup)
    except Exception as e:
        print(f"Ошибка при расчете скидки: {e}")
        return 0