from fastapi import FastAPI, HTTPException, UploadFile, File, Query
from fastapi.middleware.cors import CORSMiddleware
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse
from pydantic import BaseModel
from db import get_connection
from security import hash_password, verify_password, create_token
from datetime import datetime, timedelta
from typing import Optional, List
import pandas as pd
import io
import os
import random

app = FastAPI()

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # В продакшене укажите конкретные домены
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

app.mount("/static", StaticFiles(directory="static"), name="static")

# Главная страница франчайзера
@app.get("/main")
async def franchisee_page():
    return FileResponse("static/index.html")

class UserIn(BaseModel):
    email: str
    password: str

class VendingMachineCreate(BaseModel):
    name: str
    serial_number: int
    inventory_number: int
    model: str
    manufacturer: str
    country: str
    resource_hours: int
    type: str
    company_id: int
    modem_id: Optional[int] = None
    address: str
    installation_date: str

class VendingMachineUpdate(BaseModel):
    name: Optional[str] = None
    serial_number: Optional[int] = None
    inventory_number: Optional[int] = None
    model: Optional[str] = None
    manufacturer: Optional[str] = None
    country: Optional[str] = None
    resource_hours: Optional[int] = None
    type: Optional[str] = None
    company_id: Optional[int] = None
    modem_id: Optional[int] = None
    address: Optional[str] = None
    installation_date: Optional[str] = None

class CalendarEventCreate(BaseModel):
    vending_machine_id: int
    service_type: str  # "Плановое ТО", "ТО по ресурсу", etc
    reason: Optional[str] = None
    days_ahead: int = 5  # На сколько дней вперед создать событие (по умолчанию 5)

class EmergencyRequest(BaseModel):
    vending_machine_id: int
    description: str = 'Аварийная поломка - требуется срочный ремонт'


@app.post("/register")
def register(user: UserIn):
    conn = get_connection()
    with conn.cursor() as cur:

        cur.execute("SELECT id FROM users WHERE email=%s", (user.email,))
        if cur.fetchone():
            raise HTTPException(400, "Пользователь уже существует")

        hashed = hash_password(user.password)

        cur.execute(
            "INSERT INTO users (email, password_hash) VALUES (%s,%s)",
            (user.email, hashed)
        )
        conn.commit()

    conn.close()
    return {"status": "ok"}

@app.post("/login")
def login(user: UserIn):
    conn = get_connection()

    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, name, role, password_hash FROM users WHERE email=%s",
            (user.email,)
        )
        db_user = cur.fetchone()

    conn.close()

    if not db_user:
        raise HTTPException(401, "Неверный логин или пароль")

    if not verify_password(user.password, db_user["password_hash"]):
        raise HTTPException(401, "Неверный логин или пароль")

    token = create_token(db_user["id"])

    return {
        "access_token": token,
        "name": db_user["name"] or "Пользователь",
        "role": db_user["role"] or "user"
    }


# НОВЫЙ ENDPOINT - добавьте его сюда
@app.get("/network-efficiency")
def get_network_efficiency():
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'working' THEN 1 ELSE 0 END) as working
                FROM vending_maintenance
            """)
            
            result = cur.fetchone()
            
            if result["total"] == 0:
                efficiency = 0
            else:
                efficiency = round((result["working"] / result["total"]) * 100, 2)
            
            return {
                "efficiency": efficiency,
                "total_machines": result["total"],
                "working_machines": result["working"]
            }
    except Exception as e:
        raise HTTPException(500, f"Ошибка базы данных: {str(e)}")
    finally:
        conn.close()


@app.get("/network-status")
def get_network_status():
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'working' THEN 1 ELSE 0 END) as working,
                    SUM(CASE WHEN status = 'broken' THEN 1 ELSE 0 END) as broken,
                    SUM(CASE WHEN status = 'maintenance' THEN 1 ELSE 0 END) as maintenance
                FROM vending_maintenance
            """)
            
            result = cur.fetchone()
            
            total = result["total"]
            working = result["working"] or 0
            broken = result["broken"] or 0
            maintenance = result["maintenance"] or 0
            
            if total == 0:
                efficiency = 0
            else:
                efficiency = round((working / total) * 100, 2)
            
            return {
                "efficiency": efficiency,
                "total_machines": total,
                "working_machines": working,
                "broken_machines": broken,
                "maintenance_machines": maintenance
            }
    finally:
        conn.close()

@app.get("/sales-dynamics")
def get_sales_dynamics(by_amount: bool = True):
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Получаем продажи за последние 10 дней
            if by_amount:
                # По сумме
                cur.execute("""
                    SELECT 
                        income_date as date,
                        SUM(amount) as value
                    FROM incomes
                    WHERE income_date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
                    GROUP BY income_date
                    ORDER BY income_date
                    LIMIT 10
                """)
            else:
                # По количеству транзакций
                cur.execute("""
                    SELECT 
                        income_date as date,
                        COUNT(*) as value
                    FROM incomes
                    WHERE income_date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
                    GROUP BY income_date
                    ORDER BY income_date
                    LIMIT 10
                """)
            
            results = cur.fetchall()
            
            sales = []
            for row in results:
                sales.append({
                    "date": row["date"].strftime("%Y-%m-%d"),
                    "value": float(row["value"])
                })
            
            return {"sales": sales}
    
    finally:
        conn.close()

@app.get("/summary")
def get_summary():
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Денег в ТА (сумма всех доходов за текущий месяц)
            cur.execute("""
                SELECT COALESCE(SUM(amount), 0) as money_in_machines
                FROM incomes
                WHERE MONTH(income_date) = MONTH(CURDATE())
                AND YEAR(income_date) = YEAR(CURDATE())
            """)
            money_result = cur.fetchone()
            money_in_machines = float(money_result["money_in_machines"])
            
            # Инкассации входящие (последняя неделя)
            cur.execute("""
                SELECT COALESCE(SUM(amount), 0) as collection_incoming
                FROM incomes
                WHERE income_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            """)
            collection_result = cur.fetchone()
            collection_incoming = float(collection_result["collection_incoming"])
            
            # Выручка сегодня
            cur.execute("""
                SELECT COALESCE(SUM(amount), 0) as revenue_today
                FROM incomes
                WHERE income_date = CURDATE()
            """)
            today_result = cur.fetchone()
            revenue_today = float(today_result["revenue_today"])
            
            # Выручка вчера
            cur.execute("""
                SELECT COALESCE(SUM(amount), 0) as revenue_yesterday
                FROM incomes
                WHERE income_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
            """)
            yesterday_result = cur.fetchone()
            revenue_yesterday = float(yesterday_result["revenue_yesterday"])
            
            # Техобслуживание плановое
            cur.execute("""
                SELECT COALESCE(SUM(duration_hours), 0) as planned_hours
                FROM services
                WHERE type = 'Плановое ТО'
                AND MONTH(service_date) = MONTH(CURDATE())
            """)
            planned_result = cur.fetchone()
            maintenance_planned = int(planned_result["planned_hours"])
            
            # Техобслуживание внеплановое
            cur.execute("""
                SELECT COALESCE(SUM(duration_hours), 0) as unplanned_hours
                FROM services
                WHERE type IN ('Ремонт', 'Капитальный ремонт', 'Замена детали')
                AND MONTH(service_date) = MONTH(CURDATE())
            """)
            unplanned_result = cur.fetchone()
            maintenance_unplanned = int(unplanned_result["unplanned_hours"])
            
            # Обращений ТП сегодня (можно использовать количество поломок)
            cur.execute("""
                SELECT COUNT(*) as support_requests
                FROM services
                WHERE service_date = CURDATE()
            """)
            support_result = cur.fetchone()
            support_requests_today = int(support_result["support_requests"])
            
            return {
                "money_in_machines": money_in_machines,
                "collection_incoming": collection_incoming,
                "revenue_today": revenue_today,
                "revenue_yesterday": revenue_yesterday,
                "maintenance_planned_hours": maintenance_planned,
                "maintenance_unplanned_hours": maintenance_unplanned,
                "support_requests_today": support_requests_today
            }
    
    finally:
        conn.close()

@app.get("/vending-machines")
def get_vending_machines(
    page: int = Query(1, ge=1),
    page_size: int = Query(10, ge=1, le=100),
    search: Optional[str] = None,
    company_id: Optional[int] = None
):
    """Получить список торговых автоматов с пагинацией и фильтрацией"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Базовый запрос
            where_clauses = []
            params = []
            
            if search:
                where_clauses.append("(vm.name LIKE %s OR vm.address LIKE %s)")
                params.extend([f"%{search}%", f"%{search}%"])
            
            if company_id:
                where_clauses.append("vm.company_id = %s")
                params.append(company_id)
            
            where_sql = " WHERE " + " AND ".join(where_clauses) if where_clauses else ""
            
            # Получаем общее количество
            count_query = f"""
                SELECT COUNT(*) as total
                FROM vending_machines vm
                {where_sql}
            """
            cur.execute(count_query, params)
            total = cur.fetchone()["total"]
            
            # Получаем данные с пагинацией
            offset = (page - 1) * page_size
            data_query = f"""
                SELECT 
                    vm.id,
                    vm.name,
                    vm.serial_number,
                    vm.inventory_number,
                    vm.model,
                    vm.manufacturer,
                    vm.country,
                    vm.resource_hours,
                    vm.type,
                    vm.company_id,
                    c.name as company_name,
                    vm.modem_id,
                    IFNULL(m.serial_number, '-1') as modem_serial,
                    vm.address,
                    vm.installation_date
                FROM vending_machines vm
                LEFT JOIN companies c ON vm.company_id = c.id
                LEFT JOIN modems m ON vm.modem_id = m.id
                {where_sql}
                ORDER BY vm.id
                LIMIT %s OFFSET %s
            """
            cur.execute(data_query, params + [page_size, offset])
            machines = cur.fetchall()
            
            return {
                "total": total,
                "page": page,
                "page_size": page_size,
                "total_pages": (total + page_size - 1) // page_size,
                "data": machines
            }
    
    finally:
        conn.close()


@app.get("/vending-machines/{machine_id}")
def get_vending_machine(machine_id: int):
    """Получить информацию об одном торговом автомате"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    vm.*,
                    c.name as company_name,
                    IFNULL(m.serial_number, '-1') as modem_serial
                FROM vending_machines vm
                LEFT JOIN companies c ON vm.company_id = c.id
                LEFT JOIN modems m ON vm.modem_id = m.id
                WHERE vm.id = %s
            """, (machine_id,))
            
            machine = cur.fetchone()
            
            if not machine:
                raise HTTPException(404, "Торговый автомат не найден")
            
            return machine
    
    finally:
        conn.close()


@app.post("/vending-machines")
def create_vending_machine(machine: VendingMachineCreate):
    """Создать новый торговый автомат"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO vending_machines 
                (name, serial_number, inventory_number, model, manufacturer, country, 
                 resource_hours, type, company_id, modem_id, address, installation_date)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                machine.name, machine.serial_number, machine.inventory_number,
                machine.model, machine.manufacturer, machine.country,
                machine.resource_hours, machine.type, machine.company_id,
                machine.modem_id, machine.address, machine.installation_date
            ))
            conn.commit()
            
            return {"id": cur.lastrowid, "status": "created"}
    
    except Exception as e:
        conn.rollback()
        raise HTTPException(500, f"Ошибка создания: {str(e)}")
    
    finally:
        conn.close()


@app.put("/vending-machines/{machine_id}")
def update_vending_machine(machine_id: int, machine: VendingMachineUpdate):
    """Обновить торговый автомат"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Формируем SET часть запроса только для переданных полей
            update_fields = []
            params = []
            
            if machine.name is not None:
                update_fields.append("name = %s")
                params.append(machine.name)
            if machine.serial_number is not None:
                update_fields.append("serial_number = %s")
                params.append(machine.serial_number)
            if machine.inventory_number is not None:
                update_fields.append("inventory_number = %s")
                params.append(machine.inventory_number)
            if machine.model is not None:
                update_fields.append("model = %s")
                params.append(machine.model)
            if machine.manufacturer is not None:
                update_fields.append("manufacturer = %s")
                params.append(machine.manufacturer)
            if machine.country is not None:
                update_fields.append("country = %s")
                params.append(machine.country)
            if machine.resource_hours is not None:
                update_fields.append("resource_hours = %s")
                params.append(machine.resource_hours)
            if machine.type is not None:
                update_fields.append("type = %s")
                params.append(machine.type)
            if machine.company_id is not None:
                update_fields.append("company_id = %s")
                params.append(machine.company_id)
            if machine.modem_id is not None:
                update_fields.append("modem_id = %s")
                params.append(machine.modem_id)
            if machine.address is not None:
                update_fields.append("address = %s")
                params.append(machine.address)
            if machine.installation_date is not None:
                update_fields.append("installation_date = %s")
                params.append(machine.installation_date)
            
            if not update_fields:
                raise HTTPException(400, "Нет полей для обновления")
            
            params.append(machine_id)
            
            query = f"""
                UPDATE vending_machines 
                SET {', '.join(update_fields)}
                WHERE id = %s
            """
            
            cur.execute(query, params)
            conn.commit()
            
            if cur.rowcount == 0:
                raise HTTPException(404, "Торговый автомат не найден")
            
            return {"status": "updated"}
    
    except HTTPException:
        raise
    except Exception as e:
        conn.rollback()
        raise HTTPException(500, f"Ошибка обновления: {str(e)}")
    
    finally:
        conn.close()


@app.delete("/vending-machines/{machine_id}")
def delete_vending_machine(machine_id: int):
    """Удалить торговый автомат"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("DELETE FROM vending_machines WHERE id = %s", (machine_id,))
            conn.commit()
            
            if cur.rowcount == 0:
                raise HTTPException(404, "Торговый автомат не найден")
            
            return {"status": "deleted"}
    
    except HTTPException:
        raise
    except Exception as e:
        conn.rollback()
        raise HTTPException(500, f"Ошибка удаления: {str(e)}")
    
    finally:
        conn.close()


@app.post("/vending-machines/{machine_id}/detach-modem")
def detach_modem(machine_id: int):
    """Отвязать модем от торгового автомата"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                UPDATE vending_machines 
                SET modem_id = NULL
                WHERE id = %s
            """, (machine_id,))
            conn.commit()
            
            if cur.rowcount == 0:
                raise HTTPException(404, "Торговый автомат не найден")
            
            return {"status": "modem_detached", "message": "Модем успешно отвязан"}
    
    except HTTPException:
        raise
    except Exception as e:
        conn.rollback()
        raise HTTPException(500, f"Ошибка отвязки модема: {str(e)}")
    
    finally:
        conn.close()


# ============= ENDPOINTS ДЛЯ КОМПАНИЙ =============

@app.get("/companies")
def get_companies():
    """Получить список всех компаний"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM companies ORDER BY name")
            companies = cur.fetchall()
            return {"companies": companies}
    
    finally:
        conn.close()


# ============= ENDPOINTS ДЛЯ МОДЕМОВ =============

@app.get("/modems")
def get_modems():
    """Получить список всех модемов"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM modems ORDER BY serial_number")
            modems = cur.fetchall()
            return {"modems": modems}
    
    finally:
        conn.close()

@app.get("/manufacturers")
def get_manufacturers():
    """Получить список производителей"""
    manufacturers = [
        "Necta", "Bianchi", "Azkoyen", "Fas", "Jofemar", 
        "Saeco", "N&W", "Rheavendors", "Crane", "Royal"
    ]
    return {"manufacturers": manufacturers}

@app.get("/models")
def get_models():
    """Получить список моделей"""
    models = [
        "Любая", "Brio", "Kikko", "Samba", "Tango", 
        "Opera", "Concerto", "Maestro", "Festival", "Solista"
    ]
    return {"models": models}

@app.get("/working-modes")
def get_working_modes():
    """Получить список режимов работы"""
    modes = ["Стандартный", "Эконом", "Интенсивный", "Ночной"]
    return {"modes": modes}

@app.get("/timezones")
def get_timezones():
    """Получить список часовых поясов"""
    timezones = [
        "UTC - 12", "UTC - 11", "UTC - 10", "UTC - 9", "UTC - 8",
        "UTC - 7", "UTC - 6", "UTC - 5", "UTC - 4", "UTC - 3",
        "UTC - 2", "UTC - 1", "UTC + 0", "UTC + 1", "UTC + 2",
        "UTC + 3", "UTC + 4", "UTC + 5", "UTC + 6", "UTC + 7",
        "UTC + 8", "UTC + 9", "UTC + 10", "UTC + 11", "UTC + 12"
    ]
    return {"timezones": timezones}

@app.get("/payment-systems")
def get_payment_systems():
    """Получить список платежных систем"""
    systems = ["Монеторр", "Купюрорр", "Модуль б/н опл.", "QR-платежи"]
    return {"payment_systems": systems}

@app.get("/priorities")
def get_priorities():
    """Получить список приоритетов обслуживания"""
    priorities = ["Средний", "Низкий", "Высокий", "Критический"]
    return {"priorities": priorities}

@app.get("/monitor/vending-machines")
def monitor_vending_machines(
    status: Optional[str] = None,  # working, broken, maintenance
    connection_type: Optional[str] = None,  # online, offline
):
    """Получить мониторинг торговых автоматов с фильтрами"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Базовый запрос
            where_clauses = []
            params = []
            
            # Применяем фильтр по статусу из vending_maintenance
            if status:
                where_clauses.append("vm_maint.status = %s")
                params.append(status)
            
            where_sql = " AND " + " AND ".join(where_clauses) if where_clauses else ""
            
            query = f"""
                SELECT 
                    vm.id,
                    vm.name,
                    vm.model,
                    vm.manufacturer,
                    vm.address,
                    c.name as company_name,
                    m.serial_number as modem_serial,
                    vm_maint.status,
                    vm_maint.location
                FROM vending_machines vm
                LEFT JOIN companies c ON vm.company_id = c.id
                LEFT JOIN modems m ON vm.modem_id = m.id
                LEFT JOIN vending_maintenance vm_maint ON vm.id = vm_maint.id
                WHERE 1=1 {where_sql}
                ORDER BY vm.id
            """
            
            cur.execute(query, params)
            machines = cur.fetchall()
            
            # Генерируем данные для мониторинга
            monitor_data = []
            total_money = 0
            
            for machine in machines:
                # Эмулируем данные
                connection_status = random.choice(['online', 'offline'])
                
                # Применяем фильтр по типу подключения
                if connection_type and connection_status != connection_type:
                    continue
                
                signal_strength = random.randint(1, 5) if connection_status == 'online' else 0
                
                # Загрузка товаров
                coffee_level = random.randint(0, 100)
                sugar_level = random.randint(0, 100)
                milk_level = random.randint(0, 100)
                cups_count = random.randint(0, 300)
                
                # Денежные средства
                money_coins = random.randint(0, 50) * 10  # Монеты
                money_bills = random.randint(0, 200) * 50  # Купюры
                money_cashless = random.randint(0, 500) * 10  # Безнал
                money_total = money_coins + money_bills + money_cashless
                total_money += money_total
                
                # События
                last_event_time = datetime.now() - timedelta(minutes=random.randint(1, 1440))
                events = [
                    {"type": "sale", "time": last_event_time.strftime("%H:%M"), "count": random.randint(1, 10)},
                    {"type": "error", "time": last_event_time.strftime("%H:%M"), "count": random.randint(0, 3)}
                ]
                
                # Оборудование
                equipment = {
                    "coffee_maker": random.choice([True, False]),
                    "payment_terminal": True,
                    "coin_acceptor": True,
                    "bill_acceptor": random.choice([True, False]),
                    "cooling_system": random.choice([True, False])
                }
                
                # Информация/статусы
                statuses = []
                if coffee_level < 20:
                    statuses.append({"type": "warning", "text": "Низкий уровень кофе"})
                if cups_count < 50:
                    statuses.append({"type": "warning", "text": "Мало стаканов"})
                if money_total > 5000:
                    statuses.append({"type": "info", "text": "Требуется инкассация"})
                if connection_status == 'offline':
                    statuses.append({"type": "error", "text": "Нет связи"})
                
                monitor_data.append({
                    "id": machine["id"],
                    "name": machine["name"],
                    "model": machine["model"],
                    "manufacturer": machine["manufacturer"],
                    "address": machine["address"],
                    "company_name": machine["company_name"],
                    "modem": machine["modem_serial"],
                    "status": machine["status"],
                    "location": machine["location"],
                    "connection": {
                        "status": connection_status,
                        "signal_strength": signal_strength,
                        "last_online": (datetime.now() - timedelta(minutes=random.randint(1, 60))).strftime("%H:%M")
                    },
                    "loading": {
                        "coffee": coffee_level,
                        "sugar": sugar_level,
                        "milk": milk_level,
                        "cups": cups_count
                    },
                    "money": {
                        "coins": money_coins,
                        "bills": money_bills,
                        "cashless": money_cashless,
                        "total": money_total
                    },
                    "events": events,
                    "equipment": equipment,
                    "statuses": statuses
                })
            
            # Подсчет статистики
            working_count = len([m for m in monitor_data if m["status"] == "working"])
            broken_count = len([m for m in monitor_data if m["status"] == "broken"])
            maintenance_count = len([m for m in monitor_data if m["status"] == "maintenance"])
            
            return {
                "total": len(monitor_data),
                "working": working_count,
                "broken": broken_count,
                "maintenance": maintenance_count,
                "total_money": total_money,
                "machines": monitor_data
            }
    
    finally:
        conn.close()

@app.get("/notifications")
def get_notifications(since_minutes: int = 5):
    """Получить уведомления за последние N минут"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            notifications = []
            
            # Проверяем критические ошибки (автоматы сломаны)
            cur.execute("""
                SELECT id, location, status
                FROM vending_maintenance
                WHERE status = 'broken'
            """)
            broken_machines = cur.fetchall()
            
            for machine in broken_machines:
                notifications.append({
                    "type": "critical",
                    "title": "Критическая ошибка",
                    "message": f"Автомат '{machine['location']}' не работает!",
                    "vending_machine_id": machine['id'],
                    "timestamp": datetime.now().isoformat(),
                    "requires_confirmation": True
                })
            
            # Проверяем предупреждения (низкий запас товара)
            cur.execute("""
                SELECT product_id, name, quantity, min_stock
                FROM products
                WHERE quantity <= min_stock AND quantity > 0
            """)
            low_stock = cur.fetchall()
            
            for product in low_stock:
                notifications.append({
                    "type": "warning",
                    "title": "Низкий запас товара",
                    "message": f"{product['name']} - осталось {product['quantity']} шт.",
                    "vending_machine_id": 0,
                    "timestamp": datetime.now().isoformat(),
                    "requires_confirmation": False
                })
            
            # Проверяем товары, которые закончились
            cur.execute("""
                SELECT product_id, name
                FROM products
                WHERE quantity = 0
            """)
            out_of_stock = cur.fetchall()
            
            for product in out_of_stock:
                notifications.append({
                    "type": "critical",
                    "title": "Товар закончился",
                    "message": f"{product['name']} - 0 шт. Требуется пополнение!",
                    "vending_machine_id": 0,
                    "timestamp": datetime.now().isoformat(),
                    "requires_confirmation": True
                })
            
            # Проверяем автоматы на обслуживании
            cur.execute("""
                SELECT id, location
                FROM vending_maintenance
                WHERE status = 'maintenance'
            """)
            maintenance_machines = cur.fetchall()
            
            for machine in maintenance_machines:
                notifications.append({
                    "type": "warning",
                    "title": "На обслуживании",
                    "message": f"Автомат '{machine['location']}' на обслуживании",
                    "vending_machine_id": machine['id'],
                    "timestamp": datetime.now().isoformat(),
                    "requires_confirmation": False
                })
            
            # Информационные сообщения (недавние продажи)
            cur.execute("""
                SELECT COUNT(*) as sales_count, SUM(amount) as total_amount
                FROM incomes
                WHERE income_date = CURDATE()
            """)
            today_sales = cur.fetchone()
            
            if today_sales and today_sales['sales_count'] > 0:
                notifications.append({
                    "type": "info",
                    "title": "Продажи сегодня",
                    "message": f"Транзакций: {today_sales['sales_count']}, Сумма: {today_sales['total_amount']:.0f} ₽",
                    "vending_machine_id": 0,
                    "timestamp": datetime.now().isoformat(),
                    "requires_confirmation": False
                })
            
            return {"notifications": notifications}
    
    finally:
        conn.close()


@app.post("/notifications/log")
def log_notification(notification: dict):
    """Логирование уведомления на сервере"""
    # Здесь можно сохранить в БД для истории
    print(f"[NOTIFICATION] {notification}")
    return {"status": "logged"}

@app.post("/upload-machines")
async def upload_machines(data: dict):
    """
    Загрузка торговых автоматов из base64 encoded файла
    """
    try:
        # Получаем base64 строку и имя файла
        file_content_base64 = data.get('file_content')
        filename = data.get('filename', 'upload.csv')
        
        if not file_content_base64:
            raise HTTPException(400, "Не передано содержимое файла")
        
        # Декодируем base64
        import base64
        file_content = base64.b64decode(file_content_base64)
        
        # Проверка формата файла
        file_extension = os.path.splitext(filename)[1].lower()
        
        if file_extension not in ['.xlsx', '.csv']:
            raise HTTPException(400, "Неверный формат файла. Поддерживаются только .xlsx и .csv")
        
        # Читаем файл
        if file_extension == '.xlsx':
            df = pd.read_excel(io.BytesIO(file_content))
        else:
            df = pd.read_csv(io.BytesIO(file_content))
        
        # Обязательные поля (минимум 6)
        required_fields = ['serial_number', 'inventory_number', 'model', 'manufacturer', 
                          'country', 'type']
        
        errors = []
        
        # Проверка наличия обязательных полей
        missing_fields = [field for field in required_fields if field not in df.columns]
        if missing_fields:
            raise HTTPException(400, f"Отсутствуют обязательные поля: {', '.join(missing_fields)}")
        
        conn = get_connection()
        
        try:
            with conn.cursor() as cur:
                added_count = 0
                updated_count = 0
                
                for index, row in df.iterrows():
                    row_num = index + 2
                    
                    # Проверка корректности значений
                    if pd.isna(row['serial_number']) or pd.isna(row['inventory_number']):
                        errors.append({
                            "row": row_num,
                            "message": "Серийный номер и инвентарный номер обязательны"
                        })
                        continue
                    
                    serial_number = int(row['serial_number'])
                    inventory_number = int(row['inventory_number'])
                    
                    # Проверка на дубликаты в БД
                    cur.execute("""
                        SELECT id FROM vending_machines 
                        WHERE serial_number = %s OR inventory_number = %s
                    """, (serial_number, inventory_number))
                    
                    existing = cur.fetchone()
                    
                    if existing:
                        # Обновляем существующую запись
                        cur.execute("""
                            UPDATE vending_machines 
                            SET model = %s, manufacturer = %s, country = %s, 
                                type = %s, location = %s,
                                resource_hours = %s
                            WHERE id = %s
                        """, (
                            row['model'],
                            row['manufacturer'],
                            row['country'],
                            row['type'],
                            row.get('location', ''),
                            row.get('resource_hours', 10000),
                            existing['id']
                        ))
                        updated_count += 1
                    else:
                        # Вставляем новую запись
                        cur.execute("""
                            INSERT INTO vending_machines 
                            (serial_number, inventory_number, model, manufacturer, 
                             country, type, location, resource_hours, hours_worked,
                             last_service_date, next_service_date, service_interval_months,
                             equipment_status, franchisee_id)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        """, (
                            serial_number,
                            inventory_number,
                            row['model'],
                            row['manufacturer'],
                            row['country'],
                            row['type'],
                            row.get('location', ''),
                            row.get('resource_hours', 10000),
                            0,
                            datetime.now().date(),
                            datetime.now().date() + timedelta(days=180),
                            6,
                            'Работает',
                            1
                        ))
                        added_count += 1
                
                conn.commit()
                
                if errors:
                    return {
                        "status": "partial",
                        "added_count": added_count,
                        "updated_count": updated_count,
                        "errors": errors
                    }
                
                return {
                    "status": "success",
                    "added_count": added_count,
                    "updated_count": updated_count,
                    "errors": []
                }
        
        finally:
            conn.close()
    
    except Exception as e:
        raise HTTPException(500, f"Ошибка обработки файла: {str(e)}")

# Получение списка машин
@app.get("/machines")
def get_machines():
    """Получить список всех торговых автоматов"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    id, serial_number, inventory_number, model, manufacturer,
                    country, type, location, resource_hours, hours_worked,
                    last_service_date, next_service_date, service_interval_months,
                    equipment_status, franchisee_id
                FROM vending_machines
                ORDER BY id
            """)
            
            machines = cur.fetchall()
            
            # Преобразуем даты в строки
            for machine in machines:
                if machine.get('last_service_date'):
                    machine['last_service_date'] = machine['last_service_date'].isoformat()
                if machine.get('next_service_date'):
                    machine['next_service_date'] = machine['next_service_date'].isoformat()
            
            return machines
    
    finally:
        conn.close()


# Получение событий календаря
@app.get("/calendar-events")
def get_calendar_events():
    """
    Получить события календаря обслуживания
    Формирует события на основе:
    1. Планового ТО (интервал)
    2. ТО по ресурсу (80-90% от лимита)
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            events = []
            
            # Получаем все машины
            cur.execute("""
                SELECT 
                    vm.id, vm.model, vm.location, vm.resource_hours, 
                    vm.hours_worked, vm.next_service_date,
                    vm.last_service_date, vm.service_interval_months,
                    u.name as franchisee
                FROM vending_machines vm
                LEFT JOIN users u ON vm.franchisee_id = u.id
            """)
            
            machines = cur.fetchall()
            
            for machine in machines:
                # 1. Плановое ТО по интервалу
                if machine['next_service_date']:
                    events.append({
                        "id": machine['id'],
                        "vending_machine_id": machine['id'],
                        "machine_model": machine['model'],
                        "machine_location": machine['location'],
                        "franchisee": machine['franchisee'],
                        "service_date": machine['next_service_date'].isoformat(),
                        "service_type": "Плановое ТО",
                        "reason": f"Интервал {machine['service_interval_months']} мес."
                    })
                
                # 2. ТО по ресурсу (если достигнуто 80-90%)
                if machine['resource_hours'] and machine['hours_worked']:
                    usage_percent = (machine['hours_worked'] / machine['resource_hours']) * 100
                    
                    if usage_percent >= 80:
                        # Рассчитываем дату ТО (через 7 дней как предупреждение)
                        service_date = datetime.now() + timedelta(days=7)
                        
                        events.append({
                            "id": machine['id'] + 1000,  # Уникальный ID
                            "vending_machine_id": machine['id'],
                            "machine_model": machine['model'],
                            "machine_location": machine['location'],
                            "franchisee": machine['franchisee'],
                            "service_date": service_date.date().isoformat(),
                            "service_type": "ТО по ресурсу",
                            "reason": f"Наработка {usage_percent:.1f}% ({machine['hours_worked']}/{machine['resource_hours']} ч)"
                        })
            
            return events
    
    finally:
        conn.close()


# Создание события в календаре
@app.post("/calendar-events")
def create_calendar_event(event: CalendarEventCreate):
    """
    Создать новое событие в календаре обслуживания
    Автоматически устанавливает next_service_date на указанное количество дней вперед
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Проверяем, существует ли машина
            cur.execute(
                "SELECT id, model FROM vending_machines WHERE id = %s",
                (event.vending_machine_id,)
            )
            machine = cur.fetchone()
            
            if not machine:
                raise HTTPException(404, "Торговый автомат не найден")
            
            # Вычисляем дату события (N дней вперед)
            service_date = datetime.now() + timedelta(days=event.days_ahead)
            
            # Обновляем next_service_date в таблице vending_machines
            cur.execute(
                """
                UPDATE vending_machines 
                SET next_service_date = %s,
                    last_service_date = CURDATE()
                WHERE id = %s
                """,
                (service_date.date(), event.vending_machine_id)
            )
            
            conn.commit()
            
            return {
                "status": "success",
                "message": f"Событие создано на {service_date.date().isoformat()}",
                "vending_machine_id": event.vending_machine_id,
                "machine_model": machine['model'],
                "service_date": service_date.date().isoformat(),
                "service_type": event.service_type,
                "reason": event.reason or f"Запланировано на {event.days_ahead} дней"
            }
    
    except Exception as e:
        conn.rollback()
        raise HTTPException(500, f"Ошибка создания события: {str(e)}")
    
    finally:
        conn.close()


# Получение списка сотрудников
@app.get("/employees")
def get_employees():
    """Получить список сотрудников"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT id, name, email, phone, position, models_can_service
                FROM employees
                ORDER BY name
            """)
            
            return cur.fetchall()
    
    finally:
        conn.close()


# Получение заявок на обслуживание
@app.get("/service-requests")
def get_service_requests():
    """Получить заявки на обслуживание"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    sr.id, sr.vending_machine_id, sr.employee_id,
                    sr.status, sr.priority, sr.scheduled_date, sr.scheduled_time,
                    sr.service_duration_hours, sr.travel_time_hours,
                    sr.description, sr.created_at,
                    vm.model as machine_model,
                    vm.location as machine_location,
                    e.name as employee_name
                FROM service_requests sr
                LEFT JOIN vending_machines vm ON sr.vending_machine_id = vm.id
                LEFT JOIN employees e ON sr.employee_id = e.id
                WHERE sr.scheduled_date >= CURDATE() - INTERVAL 7 DAY
                ORDER BY sr.scheduled_date, sr.scheduled_time
            """)
            
            requests = cur.fetchall()
            
            # Преобразуем даты/время в строки
            for req in requests:
                if req.get('scheduled_date'):
                    req['scheduled_date'] = req['scheduled_date'].isoformat()
                if req.get('scheduled_time'):
                    # timedelta to string
                    total_seconds = int(req['scheduled_time'].total_seconds())
                    hours = total_seconds // 3600
                    minutes = (total_seconds % 3600) // 60
                    req['scheduled_time'] = f"{hours:02d}:{minutes:02d}:00"
                if req.get('created_at'):
                    req['created_at'] = req['created_at'].isoformat()
            
            return requests
    
    finally:
        conn.close()


# Обновление заявки (для drag-and-drop)
@app.put("/service-requests/{request_id}")
def update_service_request(request_id: int, updates: dict):
    """Обновить заявку на обслуживание (drag-and-drop)"""
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            # Формируем SQL запрос динамически
            update_fields = []
            values = []
            
            if 'employee_id' in updates:
                update_fields.append("employee_id = %s")
                values.append(updates['employee_id'])
            
            if 'scheduled_time' in updates:
                update_fields.append("scheduled_time = %s")
                values.append(updates['scheduled_time'])
            
            if 'scheduled_date' in updates:
                update_fields.append("scheduled_date = %s")
                values.append(updates['scheduled_date'])
            
            if not update_fields:
                raise HTTPException(400, "Нет полей для обновления")
            
            values.append(request_id)
            
            sql = f"""
                UPDATE service_requests 
                SET {', '.join(update_fields)}
                WHERE id = %s
            """
            
            cur.execute(sql, values)
            conn.commit()
            
            return {"status": "success", "updated_id": request_id}
    
    finally:
        conn.close()


# Подтверждение графика работ
@app.post("/confirm-schedule")
def confirm_schedule():
    """
    Подтверждение графика работ
    Обновляет статусы заявок на 'В работе'
    Обновляет статусы оборудования на 'В ремонте/на обслуживании'
    Сохраняет в историю статусов
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            updated_count = 0
            
            # Получаем все заявки со статусом "Новая" которые назначены
            cur.execute("""
                SELECT id, vending_machine_id, status
                FROM service_requests
                WHERE status = 'Новая' 
                AND employee_id IS NOT NULL
                AND scheduled_date IS NOT NULL
            """)
            
            requests = cur.fetchall()
            
            for req in requests:
                old_status = req['status']
                new_status = 'В работе'
                
                # Обновляем статус заявки
                cur.execute("""
                    UPDATE service_requests
                    SET status = %s
                    WHERE id = %s
                """, (new_status, req['id']))
                
                # Сохраняем в историю статусов заявки
                cur.execute("""
                    INSERT INTO status_history 
                    (entity_type, entity_id, old_status, new_status, changed_by)
                    VALUES ('request', %s, %s, %s, %s)
                """, (req['id'], old_status, new_status, 1))
                
                # Обновляем статус оборудования
                cur.execute("""
                    SELECT equipment_status FROM vending_machines
                    WHERE id = %s
                """, (req['vending_machine_id'],))
                
                machine = cur.fetchone()
                if machine:
                    old_equipment_status = machine['equipment_status']
                    new_equipment_status = 'В ремонте/на обслуживании'
                    
                    cur.execute("""
                        UPDATE vending_machines
                        SET equipment_status = %s
                        WHERE id = %s
                    """, (new_equipment_status, req['vending_machine_id']))
                    
                    # Сохраняем в историю статусов оборудования
                    cur.execute("""
                        INSERT INTO status_history 
                        (entity_type, entity_id, old_status, new_status, changed_by)
                        VALUES ('equipment', %s, %s, %s, %s)
                    """, (req['vending_machine_id'], old_equipment_status, new_equipment_status, 1))
                
                updated_count += 1
            
            conn.commit()
            
            return {
                "status": "success",
                "updated_count": updated_count
            }
    
    finally:
        conn.close()


# Автоматическое создание заявок (вызывается по расписанию или вручную)
@app.post("/generate-service-requests")
def generate_service_requests():
    """
    Автоматическое создание заявок на обслуживание
    Проверяет календарь и создает заявки со статусом "Новая"
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            created_count = 0
            today = datetime.now().date()
            
            # Находим все машины, которым требуется ТО сегодня
            cur.execute("""
                SELECT id, model, location, next_service_date
                FROM vending_machines
                WHERE next_service_date <= %s
                AND id NOT IN (
                    SELECT vending_machine_id 
                    FROM service_requests 
                    WHERE scheduled_date = %s
                )
            """, (today, today))
            
            machines = cur.fetchall()
            
            for machine in machines:
                # Создаем заявку
                cur.execute("""
                    INSERT INTO service_requests 
                    (vending_machine_id, status, priority, scheduled_date, description)
                    VALUES (%s, %s, %s, %s, %s)
                """, (
                    machine['id'],
                    'Новая',
                    'Обычная',
                    today,
                    f"Плановое ТО для {machine['model']}"
                ))
                
                created_count += 1
            
            conn.commit()
            
            return {
                "status": "success",
                "created_count": created_count
            }
    
    finally:
        conn.close()


# Автоматическое назначение заявок на сотрудников
@app.post("/auto-assign-requests")
def auto_assign_requests(period: str = "day"):
    """
    Автоматическое назначение заявок на сотрудников
    period: "day" или "week"
    Учитывает:
    - Специфику работы (модели, которые может обслуживать)
    - Загруженность (не более 10 часов в день)
    - Равномерное распределение (не более 40 часов в неделю)
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            assigned_count = 0
            warnings = []
            
            # Получаем всех сотрудников
            cur.execute("SELECT id, name, models_can_service FROM employees")
            employees = cur.fetchall()
            
            # Период назначения
            if period == "day":
                start_date = datetime.now().date()
                end_date = start_date
            else:  # week
                start_date = datetime.now().date()
                end_date = start_date + timedelta(days=6)
            
            # Получаем неназначенные заявки
            cur.execute("""
                SELECT sr.id, sr.vending_machine_id, sr.scheduled_date,
                       sr.service_duration_hours, sr.travel_time_hours,
                       sr.priority, vm.model
                FROM service_requests sr
                JOIN vending_machines vm ON sr.vending_machine_id = vm.id
                WHERE sr.employee_id IS NULL
                AND sr.scheduled_date BETWEEN %s AND %s
                ORDER BY 
                    CASE WHEN sr.priority = 'Авария' THEN 0 ELSE 1 END,
                    sr.scheduled_date
            """, (start_date, end_date))
            
            requests = cur.fetchall()
            
            for request in requests:
                # Находим подходящих сотрудников
                suitable_employees = []
                
                for emp in employees:
                    # Проверяем, может ли сотрудник обслуживать эту модель
                    if emp['models_can_service']:
                        models = [m.strip() for m in emp['models_can_service'].split(',')]
                        if request['model'] in models:
                            suitable_employees.append(emp)
                
                if not suitable_employees:
                    warnings.append({
                        "request_id": request['id'],
                        "message": f"Нет сотрудников для обслуживания {request['model']}"
                    })
                    continue
                
                # Выбираем наименее загруженного сотрудника
                best_employee = None
                min_load = float('inf')
                
                for emp in suitable_employees:
                    # Считаем загрузку сотрудника на эту дату
                    cur.execute("""
                        SELECT COALESCE(SUM(service_duration_hours + travel_time_hours), 0) as total_hours
                        FROM service_requests
                        WHERE employee_id = %s AND scheduled_date = %s
                    """, (emp['id'], request['scheduled_date']))
                    
                    load = cur.fetchone()['total_hours']
                    
                    # Проверяем перегрузку
                    request_hours = (request['service_duration_hours'] or 1) + (request['travel_time_hours'] or 2)
                    
                    if load + request_hours <= 10:  # Не более 10 часов в день
                        if load < min_load:
                            min_load = load
                            best_employee = emp
                
                if best_employee:
                    # Назначаем заявку
                    cur.execute("""
                        UPDATE service_requests
                        SET employee_id = %s, scheduled_time = '09:00:00'
                        WHERE id = %s
                    """, (best_employee['id'], request['id']))
                    
                    assigned_count += 1
                else:
                    # Все сотрудники перегружены - переносим на следующий день
                    if request['priority'] != 'Авария':
                        new_date = request['scheduled_date'] + timedelta(days=1)
                        cur.execute("""
                            UPDATE service_requests
                            SET scheduled_date = %s
                            WHERE id = %s
                        """, (new_date, request['id']))
                        
                        warnings.append({
                            "request_id": request['id'],
                            "message": f"Заявка перенесена на {new_date} из-за перегрузки"
                        })
            
            conn.commit()
            
            return {
                "status": "success",
                "assigned_count": assigned_count,
                "warnings": warnings
            }
    
    finally:
        conn.close()

@app.post("/generate-daily-requests")
def generate_daily_requests():
    """
    Автоматическое создание заявок на обслуживание при смене дня
    Проверяет календарь и создает заявки со статусом "Новая"
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            created_count = 0
            today = datetime.now().date()
            
            # Находим все машины, которым требуется ТО сегодня или просрочено
            cur.execute("""
                SELECT vm.id, vm.model, vm.location, vm.next_service_date,
                       vm.resource_hours, vm.hours_worked
                FROM vending_machines vm
                WHERE (vm.next_service_date <= %s
                OR (vm.resource_hours > 0 AND vm.hours_worked >= vm.resource_hours * 0.8))
                AND vm.id NOT IN (
                    SELECT vending_machine_id 
                    FROM service_requests 
                    WHERE scheduled_date = %s
                    AND status IN ('Новая', 'В работе')
                )
            """, (today, today))
            
            machines = cur.fetchall()
            
            for machine in machines:
                # Определяем тип обслуживания
                if machine['resource_hours'] and machine['hours_worked'] >= machine['resource_hours'] * 0.8:
                    description = f"ТО по ресурсу ({machine['hours_worked']}/{machine['resource_hours']} ч)"
                    duration = 3  # часа
                else:
                    description = f"Плановое ТО для {machine['model']}"
                    duration = 1  # час
                
                # Создаем заявку
                cur.execute("""
                    INSERT INTO service_requests 
                    (vending_machine_id, status, priority, scheduled_date, 
                     service_duration_hours, travel_time_hours, description)
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """, (
                    machine['id'],
                    'Новая',
                    'Обычная',
                    today,
                    duration,
                    2,  # 1 час туда + 1 час обратно
                    description
                ))
                
                created_count += 1
            
            conn.commit()
            
            return {
                "status": "success",
                "created_count": created_count,
                "date": today.isoformat()
            }
    
    finally:
        conn.close()


# Создание аварийной заявки с автоназначением
@app.post("/create-emergency-request")
async def create_emergency_request(data: EmergencyRequest):
    """
    Создание аварийной заявки с приоритетным назначением в тот же день
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            vending_machine_id = data.vending_machine_id
            description = data.description
            
            today = datetime.now().date()
            
            # Создаем заявку с приоритетом "Авария"
            cur.execute("""
                INSERT INTO service_requests 
                (vending_machine_id, status, priority, scheduled_date, 
                 service_duration_hours, travel_time_hours, description)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """, (
                vending_machine_id,
                'Новая',
                'Авария',
                today,
                2,  # Аварийный ремонт обычно дольше
                2,
                description
            ))
            
            request_id = cur.lastrowid
            
            # Получаем модель автомата
            cur.execute("""
                SELECT model FROM vending_machines WHERE id = %s
            """, (vending_machine_id,))
            
            machine = cur.fetchone()
            
            if not machine:
                raise HTTPException(404, "Автомат не найден")
            
            # Ищем подходящих сотрудников
            cur.execute("""
                SELECT id, name, models_can_service 
                FROM employees
            """)
            employees = cur.fetchall()
            
            suitable_employees = []
            for emp in employees:
                if emp['models_can_service']:
                    models = [m.strip() for m in emp['models_can_service'].split(',')]
                    if machine['model'] in models:
                        suitable_employees.append(emp)
            
            if not suitable_employees:
                conn.commit()
                return {
                    "status": "warning",
                    "request_id": request_id,
                    "message": f"Нет доступных сотрудников для обслуживания {machine['model']}"
                }
            
            # Выбираем наименее загруженного
            best_employee = None
            min_load = float('inf')
            
            for emp in suitable_employees:
                cur.execute("""
                    SELECT COALESCE(SUM(service_duration_hours + travel_time_hours), 0) as total_hours
                    FROM service_requests
                    WHERE employee_id = %s 
                    AND scheduled_date = %s
                    AND status != 'Завершена'
                """, (emp['id'], today))
                
                load = cur.fetchone()['total_hours']
                
                if load < min_load:
                    min_load = load
                    best_employee = emp
            
            # Если все перегружены (>10 часов), переносим их неаварийные задачи
            if min_load + 4 > 10:  # 2 часа работа + 2 часа дорога
                # Получаем неаварийные задачи этого сотрудника на сегодня
                cur.execute("""
                    SELECT id FROM service_requests
                    WHERE employee_id = %s 
                    AND scheduled_date = %s
                    AND priority != 'Авария'
                    AND status = 'Новая'
                    ORDER BY scheduled_time DESC
                    LIMIT 1
                """, (best_employee['id'], today))
                
                task_to_move = cur.fetchone()
                if task_to_move:
                    # Переносим на завтра
                    tomorrow = today + timedelta(days=1)
                    cur.execute("""
                        UPDATE service_requests
                        SET scheduled_date = %s
                        WHERE id = %s
                    """, (tomorrow, task_to_move['id']))
            
            # Назначаем аварийную заявку
            cur.execute("""
                UPDATE service_requests
                SET employee_id = %s, scheduled_time = '08:00:00'
                WHERE id = %s
            """, (best_employee['id'], request_id))
            
            conn.commit()
            
            return {
                "status": "success",
                "request_id": request_id,
                "assigned_employee": best_employee['name'],
                "message": f"Аварийная заявка назначена на {best_employee['name']}"
            }
    
    finally:
        conn.close()


# Получить статистику загруженности сотрудников
@app.get("/employee-workload")
def get_employee_workload(date: str = None):
    """
    Получение статистики загруженности сотрудников
    """
    conn = get_connection()
    
    try:
        if not date:
            date = datetime.now().date()
        else:
            date = datetime.strptime(date, '%Y-%m-%d').date()
        
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    e.id,
                    e.name,
                    COALESCE(SUM(
                        CASE WHEN sr.scheduled_date = %s 
                        THEN sr.service_duration_hours + sr.travel_time_hours 
                        ELSE 0 END
                    ), 0) as daily_hours,
                    COALESCE(SUM(
                        CASE WHEN sr.scheduled_date BETWEEN %s AND DATE_ADD(%s, INTERVAL 6 DAY)
                        THEN sr.service_duration_hours + sr.travel_time_hours 
                        ELSE 0 END
                    ), 0) as weekly_hours,
                    COUNT(CASE WHEN sr.scheduled_date = %s THEN 1 END) as daily_tasks,
                    COUNT(CASE WHEN sr.scheduled_date BETWEEN %s AND DATE_ADD(%s, INTERVAL 6 DAY) THEN 1 END) as weekly_tasks
                FROM employees e
                LEFT JOIN service_requests sr ON e.id = sr.employee_id 
                    AND sr.status != 'Завершена'
                GROUP BY e.id, e.name
                ORDER BY e.name
            """, (date, date, date, date, date, date))
            
            workload = cur.fetchall()
            
            # Добавляем статус перегрузки
            for emp in workload:
                emp['daily_overloaded'] = emp['daily_hours'] > 10
                emp['weekly_overloaded'] = emp['weekly_hours'] > 40
            
            return workload
    
    finally:
        conn.close()


# Получить историю статусов
@app.get("/status-history/{entity_type}/{entity_id}")
def get_status_history(entity_type: str, entity_id: int):
    """
    Получение истории изменения статусов
    entity_type: 'request' или 'equipment'
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT 
                    sh.id,
                    sh.entity_type,
                    sh.entity_id,
                    sh.old_status,
                    sh.new_status,
                    sh.changed_at,
                    u.name as changed_by_name
                FROM status_history sh
                LEFT JOIN users u ON sh.changed_by = u.id
                WHERE sh.entity_type = %s AND sh.entity_id = %s
                ORDER BY sh.changed_at DESC
            """, (entity_type, entity_id))
            
            history = cur.fetchall()
            
            # Преобразуем даты
            for item in history:
                if item.get('changed_at'):
                    item['changed_at'] = item['changed_at'].isoformat()
            
            return history
    
    finally:
        conn.close()


# Проверить доступность сотрудников для модели
@app.get("/check-employee-availability/{model}")
def check_employee_availability(model: str):
    """
    Проверка наличия сотрудников, которые могут обслуживать данную модель
    """
    conn = get_connection()
    
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT id, name, models_can_service 
                FROM employees
            """)
            employees = cur.fetchall()
            
            available = []
            for emp in employees:
                if emp['models_can_service']:
                    models = [m.strip() for m in emp['models_can_service'].split(',')]
                    if model in models:
                        available.append({
                            'id': emp['id'],
                            'name': emp['name']
                        })
            
            return {
                "model": model,
                "available_employees": available,
                "count": len(available),
                "has_employees": len(available) > 0
            }
    
    finally:
        conn.close()