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

app = Flask(__name__)
app.secret_key = 'super_secret_key_for_exam'
DB_PATH = 'shoe_shop.db'

# --- РАБОТА С БД ---
def get_db_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    # Если базы нет, создаем её из файла database.sql
    if not os.path.exists(DB_PATH):
        conn = get_db_connection()
        with open('database.sql', 'r', encoding='utf-8') as f:
            conn.executescript(f.read())
        
        # Наполняем базу тестовыми данными (Импорт данных)
        # Роли
        conn.execute("INSERT INTO roles (name) VALUES ('admin'), ('manager'), ('user')")
        
        # Пользователи (Логины без пароля)
        conn.execute("INSERT INTO users (login, last_name, first_name, middle_name, role_id) VALUES ('admin', 'Админов', 'Админ', 'Админович', 1)")
        conn.execute("INSERT INTO users (login, last_name, first_name, middle_name, role_id) VALUES ('manager', 'Менеджеров', 'Менеджер', 'Менеджерович', 2)")
        conn.execute("INSERT INTO users (login, last_name, first_name, middle_name, role_id) VALUES ('user', 'Петров', 'Петр', 'Петрович', 3)")

        # Категории и подкатегории
        conn.execute("INSERT INTO categories (name) VALUES ('Мужская'), ('Женская')")
        conn.execute("INSERT INTO subcategories (category_id, name) VALUES (1, 'Кроссовки'), (1, 'Ботинки'), (2, 'Кроссовки')")
        conn.execute("INSERT INTO manufacturers (name) VALUES ('Nike'), ('Adidas')")
        
        # Товары
        conn.execute("""INSERT INTO products (article, name, subcategory_id, manufacturer_id, description, composition, price, image_path) 
                        VALUES ('A001', 'Air Max', 1, 1, 'Спортивные', 'Кожа', 10000, '')""")
        conn.execute("""INSERT INTO products (article, name, subcategory_id, manufacturer_id, description, composition, price, image_path) 
                        VALUES ('A002', 'Superstar', 3, 2, 'Классика', 'Текстиль', 8000, '')""")
        
        # Размеры и остатки (для проверки "мало/много")
        conn.execute("INSERT INTO sizes (size_value) VALUES (40), (41), (42)")
        conn.execute("INSERT INTO product_stock (product_id, size_id, quantity) VALUES (1, 1, 1), (1, 2, 2), (2, 1, 10), (2, 2, 15)")
        
        conn.commit()
        conn.close()

# --- БИЗНЕС-ЛОГИКА ---
def calculate_discount(product_id, base_price):
    """Скидка 25%, если на товар не было заказов за последний месяц (30 дней)"""
    conn = get_db_connection()
    one_month_ago = (datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d')
    has_recent_orders = conn.execute(
        "SELECT COUNT(*) FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE oi.product_id = ? AND o.order_date >= ?", 
        (product_id, one_month_ago)
    ).fetchone()[0]
    conn.close()
    return base_price * 0.75 if has_recent_orders == 0 else base_price

def get_user_role():
    """Получение роли текущего пользователя"""
    if 'user_id' not in session:
        return 'guest'
    conn = get_db_connection()
    role = conn.execute("SELECT r.name FROM roles r JOIN users u ON u.role_id = r.id WHERE u.id = ?", (session['user_id'],)).fetchone()
    conn.close()
    return role['name'] if role else 'guest'

# Передаем роль и имя во все шаблоны автоматически
@app.context_processor
def inject_globals():
    return dict(current_role=get_user_role(), current_user=session.get('user_name'))

# --- МАРШРУТЫ (АВТОРИЗАЦИЯ) ---

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        login_val = request.form.get('login')
        conn = get_db_connection()
        user = conn.execute("SELECT * FROM users WHERE login = ?", (login_val,)).fetchone()
        conn.close()
        
        if user:
            session['user_id'] = user['id']
            session['user_name'] = f"{user['last_name']} {user['first_name']}"
            flash(f'Вы вошли как {user["last_name"]}', 'message')
            return redirect(url_for('index'))
        else:
            flash('Пользователь с таким логином не найден', 'error')
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    flash('Вы вышли из системы', 'message')
    return redirect(url_for('index'))

# --- КАТАЛОГ ---

@app.route('/')
def index():
    conn = get_db_connection()
    
    # Фильтрация и поиск (доступна только не гостям)
    search = request.args.get('search', '')
    category_filter = request.args.get('category', '')
    
    query = """
        SELECT p.*, sc.name as subcategory_name, c.name as category_name, m.name as manufacturer_name,
               (SELECT SUM(quantity) FROM product_stock WHERE product_id = p.id) as total_qty
        FROM products p
        JOIN subcategories sc ON p.subcategory_id = sc.id
        JOIN categories c ON sc.category_id = c.id
        JOIN manufacturers m ON p.manufacturer_id = m.id
        WHERE 1=1
    """
    params = []
    
    # Гостю фильтры не применяем (по ТЗ)
    if get_user_role() != 'guest':
        if search:
            query += " AND (p.name LIKE ? OR p.description LIKE ?)"
            params.extend([f'%{search}%', f'%{search}%'])
        if category_filter:
            query += " AND c.id = ?"
            params.append(category_filter)
            
    products_raw = conn.execute(query, params).fetchall()
    categories = conn.execute("SELECT * FROM categories").fetchall()
    conn.close()
    
    products = []
    for p in products_raw:
        p_dict = dict(p)
        # Расчет цены со скидкой
        p_dict['final_price'] = round(calculate_discount(p['id'], p['price']), 2)
        # Проверка количества (мало/много)
        if p_dict['total_qty'] is None or p_dict['total_qty'] <= 5:
            p_dict['qty_text'] = 'мало'
            p_dict['highlight'] = False 
        else:
            p_dict['qty_text'] = 'много'
            p_dict['highlight'] = True 
        # Заглушка для картинки
        if not p_dict['image_path']:
            p_dict['image_path'] = 'picture.png'
        products.append(p_dict)

    return render_template('index.html', products=products, categories=categories, search=search, cat_filter=category_filter)

# --- КОРЗИНА И ЗАКАЗЫ ---

@app.route('/add_to_cart/<int:product_id>', methods=['POST'])
def add_to_cart(product_id):
    if get_user_role() == 'guest':
        flash('Войдите, чтобы оформлять заказы', 'error')
        return redirect(url_for('index'))
        
    size_id = request.form.get('size_id')
    qty = int(request.form.get('quantity', 1))
    
    if 'cart' not in session:
        session['cart'] = []
        
    cart = session['cart']
    found = False
    for item in cart:
        if item['product_id'] == product_id and item['size_id'] == int(size_id):
            item['quantity'] += qty
            found = True
            break
    if not found:
        cart.append({'product_id': product_id, 'size_id': int(size_id), 'quantity': qty})
    
    session['cart'] = cart
    flash('Товар добавлен в корзину', 'message')
    return redirect(url_for('index'))

@app.route('/cart')
def cart():
    if get_user_role() == 'guest':
        return redirect(url_for('login'))
    return render_template('cart.html', cart=session.get('cart', []))

@app.route('/checkout', methods=['POST'])
def checkout():
    if get_user_role() == 'guest' or not session.get('cart'):
        return redirect(url_for('index'))
        
    client_name = request.form.get('client_name')
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Создаем заказ
    cursor.execute("INSERT INTO orders (user_id, client_full_name, order_date) VALUES (?, ?, ?)", 
                   (session['user_id'], client_name, datetime.now().strftime('%Y-%m-%d')))
    order_id = cursor.lastrowid
    
    # Добавляем позиции
    for item in session['cart']:
        price = conn.execute("SELECT price FROM products WHERE id = ?", (item['product_id'],)).fetchone()['price']
        final_price = calculate_discount(item['product_id'], price)
        cursor.execute("INSERT INTO order_items (order_id, product_id, size_id, quantity, price_at_moment) VALUES (?, ?, ?, ?, ?)",
                       (order_id, item['product_id'], item['size_id'], item['quantity'], final_price))
        
    conn.commit()
    conn.close()
    session.pop('cart', None)
    flash('Заказ успешно оформлен!', 'message')
    return redirect(url_for('orders'))

@app.route('/orders')
def orders():
    # Просмотр заказов только для менеджера и админа
    if get_user_role() in ['guest', 'user']:
        flash('Доступ запрещен', 'error')
        return redirect(url_for('index'))
        
    conn = get_db_connection()
    orders_list = conn.execute("""
        SELECT o.id, o.order_date, o.client_full_name, u.last_name as manager_name
        FROM orders o 
        LEFT JOIN users u ON o.user_id = u.id
        ORDER BY o.order_date DESC
    """).fetchall()
    conn.close()
    return render_template('orders.html', orders=orders_list)

@app.route('/delete_order/<int:order_id>')
def delete_order(order_id):
    if get_user_role() not in ['admin', 'manager']:
        return redirect(url_for('index'))
    conn = get_db_connection()
    conn.execute("DELETE FROM orders WHERE id = ?", (order_id,))
    conn.commit()
    conn.close()
    flash('Заказ удален', 'message')
    return redirect(url_for('orders'))

# --- ЗАПУСК ---
if __name__ == '__main__':
    init_db() # Проверяем/создаем базу при запуске
    app.run(debug=True)