app.py - master - woodoo/web_complex_manager

app.py
2 637 строк101 KB
woodoo
upload files
25 ноя 2025, 13:35
25 ноя 2025, 13:35d517a88
# app.py
from flask import Flask, render_template, request, jsonify, send_file, session, redirect, url_for
from flask_cors import CORS
import mysql.connector
from mysql.connector import Error
from datetime import datetime
import os
import json
from decimal import Decimal
import io
import pandas as pd
import re
import bcrypt

class JSONEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.strftime('%d.%m.%Y')
        elif isinstance(obj, Decimal):
            return float(obj)
        elif isinstance(obj, bytes):
            return obj.decode('utf-8', errors='ignore')
        return super().default(obj)

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key-change-in-production'
app.json_encoder = JSONEncoder
CORS(app)

# Подключение к существующей БД
def get_db_connection():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='complex_manager',
            user='root',
            password='rus1111LAN',
            auth_plugin='mysql_native_password'
        )
        return connection
    except Error as e:
        print(f"Database connection error: {e}")
        return None

def parse_date(date_input):
    """Преобразует дату из различных форматов в YYYY-MM-DD"""
    if not date_input:
        return None
    
    # Если это pandas Timestamp или datetime объект
    if hasattr(date_input, 'strftime'):
        return date_input.strftime('%Y-%m-%d')
    
    date_str = str(date_input).strip()
    
    # Пропускаем некорректные даты
    if date_str in ['0000-00-00', '00.00.0000', '']:
        return None
    
    # Если дата уже в формате YYYY-MM-DD
    if re.match(r'^\d{4}-\d{2}-\d{2}$', date_str):
        return date_str
    
    # Если дата в формате YYYY-MM-DD HH:MM:SS
    if re.match(r'^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$', date_str):
        return date_str.split(' ')[0]
    
    # Пробуем разные форматы
    formats = [
        '%d.%m.%Y',        # 08.09.2025
        '%d/%m/%Y',        # 08/09/2025
        '%d-%m-%Y',        # 08-09-2025
        '%Y.%m.%d',        # 2025.09.08
        '%Y/%m/%d',        # 2025/09/08
        '%Y-%m-%d',        # 2025-09-08
    ]
    
    for fmt in formats:
        try:
            parsed_date = datetime.strptime(date_str, fmt)
            return parsed_date.strftime('%Y-%m-%d')
        except ValueError:
            continue
    
    print(f"Не удалось распарсить дату: {date_input} (тип: {type(date_input)})")
    return None

# ==================== АВТОРИЗАЦИЯ ====================

@app.route('/')
def index():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    return render_template('index.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.json.get('username')
        password = request.json.get('password')
        
        print(f"Login attempt: {username}")
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE username = %s AND is_active = TRUE", (username,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if user:
            # Простая проверка пароля (пока без bcrypt)
            if user['password_hash'] == password:
                session['user_id'] = user['id']
                session['username'] = user['username']
                session['role'] = user['role']
                return jsonify({'status': 'success', 'message': 'Login successful', 'role': user['role']})
        
        return jsonify({'error': 'Invalid username or password'}), 401
    
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))

@app.route('/api/current-user')
def get_current_user():
    if 'user_id' not in session:
        return jsonify({'error': 'Not authenticated'}), 401
    
    return jsonify({
        'id': session['user_id'],
        'username': session['username'],
        'role': session['role']
    })

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

@app.route('/api/users')
def get_users():
    if 'user_id' not in session or session.get('role') != 'admin':
        return jsonify({'error': 'Unauthorized'}), 403
    
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT id, username, role, created_at, is_active FROM users ORDER BY id")
        users = cursor.fetchall()
        
        # Форматируем даты
        for user in users:
            if user['created_at']:
                user['created_at'] = user['created_at'].strftime('%d.%m.%Y %H:%M')
        
        cursor.close()
        conn.close()
        
        return jsonify(users)
        
    except Exception as e:
        print(f"Error getting users: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/users', methods=['POST'])
def create_user():
    if 'user_id' not in session or session.get('role') != 'admin':
        return jsonify({'error': 'Unauthorized'}), 403
    
    try:
        data = request.json
        username = data.get('username')
        password = data.get('password')
        role = data.get('role', 'user')
        
        if not username or not password:
            return jsonify({'error': 'Username and password are required'}), 400
        
        # Хешируем пароль
        password_hash = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        try:
            cursor.execute(
                "INSERT INTO users (username, password_hash, role) VALUES (%s, %s, %s)",
                (username, password_hash, role)
            )
            conn.commit()
            user_id = cursor.lastrowid
            cursor.close()
            conn.close()
            
            return jsonify({'status': 'success', 'id': user_id, 'message': 'User created successfully'})
        except mysql.connector.IntegrityError:
            return jsonify({'error': 'Username already exists'}), 400
        
    except Exception as e:
        print(f"Error creating user: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/users/<int:user_id>', methods=['PUT'])
def update_user(user_id):
    if 'user_id' not in session or session.get('role') != 'admin':
        return jsonify({'error': 'Unauthorized'}), 403
    
    try:
        data = request.json
        username = data.get('username')
        password = data.get('password')
        role = data.get('role')
        is_active = data.get('is_active')
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        update_fields = []
        params = []
        
        if username:
            update_fields.append("username = %s")
            params.append(username)
        
        if password:
            password_hash = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
            update_fields.append("password_hash = %s")
            params.append(password_hash)
        
        if role:
            update_fields.append("role = %s")
            params.append(role)
        
        if is_active is not None:
            update_fields.append("is_active = %s")
            params.append(is_active)
        
        if not update_fields:
            return jsonify({'error': 'No fields to update'}), 400
        
        params.append(user_id)
        
        query = f"UPDATE users SET {', '.join(update_fields)} WHERE id = %s"
        cursor.execute(query, params)
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'User updated successfully'})
        
    except Exception as e:
        print(f"Error updating user: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    if 'user_id' not in session or session.get('role') != 'admin':
        return jsonify({'error': 'Unauthorized'}), 403
    
    # Не позволяем удалить самого себя
    if user_id == session['user_id']:
        return jsonify({'error': 'Cannot delete your own account'}), 400
    
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'User deleted successfully'})
        
    except Exception as e:
        print(f"Error deleting user: {e}")
        return jsonify({'error': str(e)}), 500

# ==================== ФУНКЦИИ ПРОВЕРКИ АВТОРИЗАЦИИ ====================

def check_auth():
    """Проверка авторизации для API"""
    if 'user_id' not in session:
        return jsonify({'error': 'Authentication required'}), 401
    return None

def check_admin():
    """Проверка прав администратора"""
    auth_error = check_auth()
    if auth_error:
        return auth_error
    if session.get('role') != 'admin':
        return jsonify({'error': 'Admin access required'}), 403
    return None

# ==================== ОСНОВНЫЕ API (С ПРОВЕРКОЙ АВТОРИЗАЦИИ) ====================

@app.route('/api/complexes')
def get_complexes():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    filter_type = request.args.get('filter', 'active')
    search = request.args.get('search', '')
    
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Проверяем наличие столбца is_archived
        cursor.execute("SHOW COLUMNS FROM complexes LIKE 'is_archived'")
        has_archive_column = cursor.fetchone() is not None
        
        query = "SELECT id, number, location, is_archived FROM complexes WHERE 1=1"
        params = []
        
        if has_archive_column:
            if filter_type == 'active':
                query += " AND is_archived = FALSE"
            elif filter_type == 'archived':
                query += " AND is_archived = TRUE"
        
        if search:
            query += " AND (number LIKE %s OR location LIKE %s)"
            params.extend([f"%{search}%", f"%{search}%"])
        
        query += " ORDER BY id"
        
        cursor.execute(query, params)
        complexes = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify(complexes)
        
    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>')
def get_complex_details(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("SELECT * FROM complexes WHERE id = %s", (complex_id,))
        complex_data = cursor.fetchone()
        
        # Форматируем даты для JSON
        if complex_data:
            date_fields = ['commission_date', 'verification_date', 'verification_valid_until']
            for field in date_fields:
                if complex_data.get(field) and complex_data[field] != '0000-00-00':
                    if isinstance(complex_data[field], str):
                        try:
                            complex_data[field] = datetime.strptime(complex_data[field], '%Y-%m-%d').strftime('%d.%m.%Y')
                        except:
                            complex_data[field] = ''
                    else:
                        complex_data[field] = complex_data[field].strftime('%d.%m.%Y')
                else:
                    complex_data[field] = ''
        
        cursor.close()
        conn.close()
        
        return jsonify(complex_data if complex_data else {})
        
    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/maintenance')
def get_maintenance(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        maintenance_type = request.args.get('type', '')
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT id, maintenance_date, maintenance_type, description 
            FROM maintenance_schedule 
            WHERE complex_id = %s
        """
        params = [complex_id]
        
        if maintenance_type:
            query += " AND maintenance_type = %s"
            params.append(maintenance_type)
        
        query += " ORDER BY maintenance_date DESC"
        
        cursor.execute(query, params)
        
        maintenance_data = cursor.fetchall()
        
        # Форматируем даты
        for item in maintenance_data:
            if item['maintenance_date']:
                item['maintenance_date'] = item['maintenance_date'].strftime('%d.%m.%Y')
        
        cursor.close()
        conn.close()
        
        return jsonify(maintenance_data)
        
    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance/<int:maintenance_id>')
def get_maintenance_by_id(maintenance_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT id, complex_id, maintenance_date, maintenance_type, description 
            FROM maintenance_schedule 
            WHERE id = %s
        """, (maintenance_id,))
        
        maintenance_data = cursor.fetchone()
        
        # Форматируем дату
        if maintenance_data and maintenance_data['maintenance_date']:
            maintenance_data['maintenance_date'] = maintenance_data['maintenance_date'].strftime('%d.%m.%Y')
        
        cursor.close()
        conn.close()
        
        return jsonify(maintenance_data if maintenance_data else {})
        
    except Exception as e:
        print(f"Error getting maintenance: {e}")
        return jsonify({'error': str(e)}), 500

# API для экспорта обслуживания в Excel
@app.route('/api/complexes/<int:complex_id>/maintenance/export')
def export_maintenance(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        maintenance_type = request.args.get('type', '')
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Получаем данные комплекса
        cursor.execute("SELECT number, location FROM complexes WHERE id = %s", (complex_id,))
        complex = cursor.fetchone()
        
        if not complex:
            return jsonify({'error': 'Комплекс не найден'}), 404
        
        # Получаем данные обслуживания
        query = """
            SELECT maintenance_date, maintenance_type, description 
            FROM maintenance_schedule 
            WHERE complex_id = %s
        """
        params = [complex_id]
        
        if maintenance_type:
            query += " AND maintenance_type = %s"
            params.append(maintenance_type)
        
        query += " ORDER BY maintenance_date DESC"
        
        cursor.execute(query, params)
        maintenance_data = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        # Создаем DataFrame
        df = pd.DataFrame(maintenance_data)
        
        # Форматируем даты
        if not df.empty:
            df['maintenance_date'] = pd.to_datetime(df['maintenance_date']).dt.strftime('%d.%m.%Y')
        
        # Создаем Excel файл в памяти
        output = io.BytesIO()
        
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            # Основной лист с обслуживанием
            df.to_excel(writer, sheet_name='Обслуживание', index=False)
            
            # Получаем workbook и worksheet для форматирования
            workbook = writer.book
            worksheet = writer.sheets['Обслуживание']
            
            # Добавляем заголовок
            header_format = workbook.add_format({
                'bold': True,
                'text_wrap': True,
                'valign': 'top',
                'fg_color': '#D7E4BC',
                'border': 1
            })
            
            # Форматируем заголовки
            for col_num, value in enumerate(df.columns.values):
                worksheet.write(0, col_num, value, header_format)
            
            # Автоматически подбираем ширину колонок
            for i, col in enumerate(df.columns):
                max_len = max(df[col].astype(str).map(len).max(), len(col)) + 2
                worksheet.set_column(i, i, max_len)
        
        output.seek(0)
        
        # Формируем имя файла
        filename = f"Обслуживание_{complex['number']}_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        if maintenance_type:
            filename = f"Обслуживание_{complex['number']}_{maintenance_type}_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        print(f"Error exporting maintenance: {e}")
        return jsonify({'error': str(e)}), 500

# API для экспорта всех комплексов с обслуживанием
@app.route('/api/maintenance/export-all')
def export_all_maintenance():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        maintenance_type = request.args.get('type', '')
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Получаем все комплексы
        cursor.execute("""
            SELECT c.id, c.number, c.location, 
                   m.maintenance_date, m.maintenance_type, m.description
            FROM complexes c
            LEFT JOIN maintenance_schedule m ON c.id = m.complex_id
            WHERE c.is_archived = FALSE
        """)
        
        data = cursor.fetchall()
        cursor.close()
        conn.close()
        
        if not data:
            return jsonify({'error': 'Нет данных для экспорта'}), 404
        
        # Создаем DataFrame
        df = pd.DataFrame(data)
        
        # Фильтруем по типу обслуживания если указан
        if maintenance_type:
            df = df[df['maintenance_type'] == maintenance_type]
        
        # Форматируем даты
        if not df.empty and 'maintenance_date' in df.columns:
            df['maintenance_date'] = pd.to_datetime(df['maintenance_date']).dt.strftime('%d.%m.%Y')
        
        # Создаем Excel файл в памяти
        output = io.BytesIO()
        
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            # Основной лист с обслуживанием
            df.to_excel(writer, sheet_name='Обслуживание всех комплексов', index=False)
            
            # Получаем workbook и worksheet для форматирования
            workbook = writer.book
            worksheet = writer.sheets['Обслуживание всех комплексов']
            
            # Добавляем заголовок
            header_format = workbook.add_format({
                'bold': True,
                'text_wrap': True,
                'valign': 'top',
                'fg_color': '#D7E4BC',
                'border': 1
            })
            
            # Форматируем заголовки
            for col_num, value in enumerate(df.columns.values):
                worksheet.write(0, col_num, value, header_format)
            
            # Автоматически подбираем ширину колонок
            for i, col in enumerate(df.columns):
                max_len = max(df[col].astype(str).map(len).max(), len(col)) + 2
                worksheet.set_column(i, i, max_len)
        
        output.seek(0)
        
        # Формируем имя файла
        filename = f"Обслуживание_всех_комплексов_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        if maintenance_type:
            filename = f"Обслуживание_{maintenance_type}_все_комплексы_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        print(f"Error exporting all maintenance: {e}")
        return jsonify({'error': str(e)}), 500

# API для изображений
@app.route('/api/complexes/<int:complex_id>/images')
def get_images(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT id, image_name, uploaded_at as upload_date 
            FROM complex_images 
            WHERE complex_id = %s 
            ORDER BY uploaded_at DESC
        """, (complex_id,))
        
        images = cursor.fetchall()
        
        # Форматируем даты
        for image in images:
            if image['upload_date']:
                image['upload_date'] = image['upload_date'].strftime('%d.%m.%Y %H:%M')
        
        cursor.close()
        conn.close()
        
        return jsonify(images)
        
    except Exception as e:
        print(f"Error getting images: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/images', methods=['POST'])
def upload_image(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        print("Upload image request received")
        print(f"Files: {request.files}")
        print(f"Form: {request.form}")
        
        if 'image' not in request.files:
            print("No file part")
            return jsonify({'error': 'No file part'}), 400
        
        file = request.files['image']
        print(f"File: {file.filename}")
        
        if file.filename == '':
            print("No selected file")
            return jsonify({'error': 'No selected file'}), 400
        
        # Проверяем тип файла
        allowed_extensions = {'png', 'jpg', 'jpeg', 'gif', 'bmp'}
        file_extension = file.filename.rsplit('.', 1)[1].lower() if '.' in file.filename else ''
        
        print(f"File extension: {file_extension}")
        
        if file_extension not in allowed_extensions:
            print(f"Invalid file type: {file_extension}")
            return jsonify({'error': 'Invalid file type. Allowed: png, jpg, jpeg, gif, bmp'}), 400
        
        image_name = request.form.get('image_name') or file.filename
        image_data = file.read()
        
        print(f"Image name: {image_name}, size: {len(image_data)} bytes")
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT INTO complex_images (complex_id, image_name, image_data, image_type)
            VALUES (%s, %s, %s, %s)
        """, (complex_id, image_name, image_data, file_extension.upper()))
        
        conn.commit()
        image_id = cursor.lastrowid
        cursor.close()
        conn.close()
        
        print(f"Image uploaded successfully, ID: {image_id}")
        return jsonify({'status': 'success', 'id': image_id, 'message': 'Изображение загружено'})
        
    except Exception as e:
        print(f"Error uploading image: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

@app.route('/api/images/<int:image_id>')
def get_image(image_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        print(f"Getting image: {image_id}")
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("SELECT image_data, image_name FROM complex_images WHERE id = %s", (image_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if result:
            print(f"Image found: {result['image_name']}")
            
            # Определяем MIME тип по расширению файла
            file_extension = result['image_name'].rsplit('.', 1)[-1].lower()
            mime_types = {
                'jpg': 'image/jpeg',
                'jpeg': 'image/jpeg',
                'png': 'image/png',
                'gif': 'image/gif',
                'bmp': 'image/bmp'
            }
            mimetype = mime_types.get(file_extension, 'image/jpeg')
            
            return send_file(
                io.BytesIO(result['image_data']),
                mimetype=mimetype,
                as_attachment=False,
                download_name=result['image_name']
            )
        else:
            print("Image not found")
            return jsonify({'error': 'Image not found'}), 404
            
    except Exception as e:
        print(f"Error getting image: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

@app.route('/api/images/<int:image_id>', methods=['DELETE'])
def delete_image(image_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM complex_images WHERE id = %s", (image_id,))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'Изображение удалено'})
        
    except Exception as e:
        print(f"Error deleting image: {e}")
        return jsonify({'error': str(e)}), 500

# API для документов
@app.route('/api/complexes/<int:complex_id>/documents')
def get_documents(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT id, document_name, document_type, document_size, upload_date 
            FROM complex_documents 
            WHERE complex_id = %s 
            ORDER BY upload_date DESC
        """, (complex_id,))
        
        documents = cursor.fetchall()
        
        # Форматируем даты, размеры и переводим типы на русский
        type_translations = {
            'TECHNICAL': 'Техническая документация',
            'VERIFICATION': 'Поверка',
            'MAINTENANCE': 'Обслуживание',
            'OTHER': 'Другое'
        }
        
        for doc in documents:
            if doc['upload_date']:
                doc['upload_date'] = doc['upload_date'].strftime('%d.%m.%Y %H:%M')
            if doc['document_size']:
                doc['size_formatted'] = f"{doc['document_size'] // 1024} KB"
            if doc['document_type']:
                doc['document_type_ru'] = type_translations.get(doc['document_type'], doc['document_type'])
        
        cursor.close()
        conn.close()
        
        return jsonify(documents)
        
    except Exception as e:
        print(f"Error getting documents: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/documents', methods=['POST'])
def upload_document(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        print("Upload document request received")
        print(f"Files: {request.files}")
        
        if 'document' not in request.files:
            return jsonify({'error': 'No file part'}), 400
        
        file = request.files['document']
        print(f"File: {file.filename}")
        
        if file.filename == '':
            return jsonify({'error': 'No selected file'}), 400
        
        document_name = file.filename
        document_type = request.form.get('document_type', 'OTHER')
        document_data = file.read()
        document_size = len(document_data)
        
        print(f"Document: {document_name}, type: {document_type}, size: {document_size}")
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT INTO complex_documents (complex_id, document_name, document_type, document_size, document_data)
            VALUES (%s, %s, %s, %s, %s)
        """, (complex_id, document_name, document_type, document_size, document_data))
        
        conn.commit()
        document_id = cursor.lastrowid
        cursor.close()
        conn.close()
        
        print(f"Document uploaded successfully, ID: {document_id}")
        return jsonify({'status': 'success', 'id': document_id, 'message': 'Документ загружен'})
        
    except Exception as e:
        print(f"Error uploading document: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

@app.route('/api/documents/<int:document_id>')
def download_document(document_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("SELECT document_data, document_name FROM complex_documents WHERE id = %s", (document_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if result:
            return send_file(
                io.BytesIO(result['document_data']),
                as_attachment=True,
                download_name=result['document_name']
            )
        else:
            return jsonify({'error': 'Document not found'}), 404
            
    except Exception as e:
        print(f"Error downloading document: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/documents/<int:document_id>', methods=['DELETE'])
def delete_document(document_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM complex_documents WHERE id = %s", (document_id,))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'Документ удален'})
        
    except Exception as e:
        print(f"Error deleting document: {e}")
        return jsonify({'error': str(e)}), 500

# API для изображений калибровки
@app.route('/api/complexes/<int:complex_id>/calibration-images')
def get_calibration_images(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT id, image_name, uploaded_at as upload_date 
            FROM calibration_images 
            WHERE complex_id = %s 
            ORDER BY uploaded_at DESC
        """, (complex_id,))
        
        images = cursor.fetchall()
        
        # Форматируем даты
        for image in images:
            if image['upload_date']:
                image['upload_date'] = image['upload_date'].strftime('%d.%m.%Y %H:%M')
        
        cursor.close()
        conn.close()
        
        return jsonify(images)
        
    except Exception as e:
        print(f"Error getting calibration images: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/calibration-images', methods=['POST'])
def upload_calibration_image(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        if 'image' not in request.files:
            return jsonify({'error': 'No file part'}), 400
        
        file = request.files['image']
        
        if file.filename == '':
            return jsonify({'error': 'No selected file'}), 400
        
        # Проверяем тип файла
        allowed_extensions = {'png', 'jpg', 'jpeg', 'gif', 'bmp'}
        file_extension = file.filename.rsplit('.', 1)[1].lower() if '.' in file.filename else ''
        
        if file_extension not in allowed_extensions:
            return jsonify({'error': 'Invalid file type. Allowed: png, jpg, jpeg, gif, bmp'}), 400
        
        image_name = request.form.get('image_name') or file.filename
        image_data = file.read()
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT INTO calibration_images (complex_id, image_name, image_data, image_type)
            VALUES (%s, %s, %s, %s)
        """, (complex_id, image_name, image_data, file_extension.upper()))
        
        conn.commit()
        image_id = cursor.lastrowid
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'id': image_id, 'message': 'Изображение калибровки загружено'})
        
    except Exception as e:
        print(f"Error uploading calibration image: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/calibration-images/<int:image_id>')
def get_calibration_image(image_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("SELECT image_data, image_name FROM calibration_images WHERE id = %s", (image_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if result:
            # Определяем MIME тип по расширению файла
            file_extension = result['image_name'].rsplit('.', 1)[-1].lower()
            mime_types = {
                'jpg': 'image/jpeg',
                'jpeg': 'image/jpeg',
                'png': 'image/png',
                'gif': 'image/gif',
                'bmp': 'image/bmp'
            }
            mimetype = mime_types.get(file_extension, 'image/jpeg')
            
            return send_file(
                io.BytesIO(result['image_data']),
                mimetype=mimetype,
                as_attachment=False,
                download_name=result['image_name']
            )
        else:
            return jsonify({'error': 'Image not found'}), 404
            
    except Exception as e:
        print(f"Error getting calibration image: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/calibration-images/<int:image_id>', methods=['DELETE'])
def delete_calibration_image(image_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM calibration_images WHERE id = %s", (image_id,))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'Изображение калибровки удалено'})
        
    except Exception as e:
        print(f"Error deleting calibration image: {e}")
        return jsonify({'error': str(e)}), 500

# API для документов обслуживания
@app.route('/api/complexes/<int:complex_id>/maintenance-documents')
def get_maintenance_documents(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT id, document_name, document_type, document_size, upload_date 
            FROM maintenance_documents 
            WHERE complex_id = %s 
            ORDER BY upload_date DESC
        """, (complex_id,))
        
        documents = cursor.fetchall()
        
        # Форматируем даты, размеры и переводим типы на русский
        type_translations = {
            'TECHNICAL': 'Техническая документация',
            'VERIFICATION': 'Поверка',
            'MAINTENANCE': 'Обслуживание',
            'OTHER': 'Другое'
        }
        
        for doc in documents:
            if doc['upload_date']:
                doc['upload_date'] = doc['upload_date'].strftime('%d.%m.%Y %H:%M')
            if doc['document_size']:
                doc['size_formatted'] = f"{doc['document_size'] // 1024} KB"
            if doc['document_type']:
                doc['document_type_ru'] = type_translations.get(doc['document_type'], doc['document_type'])
        
        cursor.close()
        conn.close()
        
        return jsonify(documents)
        
    except Exception as e:
        print(f"Error getting maintenance documents: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/maintenance-documents', methods=['POST'])
def upload_maintenance_document(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        if 'documents[]' not in request.files:
            return jsonify({'error': 'No files provided'}), 400
        
        files = request.files.getlist('documents[]')
        work_date = request.form.get('work_date')
        
        if not files or files[0].filename == '':
            return jsonify({'error': 'No selected files'}), 400
        
        # Парсим дату работ если указана
        parsed_work_date = parse_date(work_date) if work_date else None
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        uploaded_files = []
        failed_files = []
        
        for file in files:
            if file.filename == '':
                continue
                
            try:
                document_name = file.filename
                document_type = 'MAINTENANCE'  # Фиксированный тип "Обслуживание"
                document_data = file.read()
                document_size = len(document_data)
                
                cursor.execute("""
                    INSERT INTO maintenance_documents 
                    (complex_id, document_name, document_type, document_size, document_data, work_date)
                    VALUES (%s, %s, %s, %s, %s, %s)
                """, (complex_id, document_name, document_type, document_size, document_data, parsed_work_date))
                
                uploaded_files.append(document_name)
                
            except Exception as e:
                print(f"Error uploading file {file.filename}: {e}")
                failed_files.append({'name': file.filename, 'error': str(e)})
        
        conn.commit()
        cursor.close()
        conn.close()
        
        result = {
            'status': 'success', 
            'message': f'Загружено {len(uploaded_files)} документов',
            'uploaded_files': uploaded_files
        }
        
        if failed_files:
            result['failed_files'] = failed_files
            result['message'] += f', не удалось загрузить {len(failed_files)}'
        
        return jsonify(result)
        
    except Exception as e:
        print(f"Error uploading maintenance documents: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance-documents/<int:document_id>')
def download_maintenance_document(document_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("SELECT document_data, document_name FROM maintenance_documents WHERE id = %s", (document_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if result:
            return send_file(
                io.BytesIO(result['document_data']),
                as_attachment=True,
                download_name=result['document_name']
            )
        else:
            return jsonify({'error': 'Document not found'}), 404
            
    except Exception as e:
        print(f"Error downloading maintenance document: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance-documents/<int:document_id>', methods=['DELETE'])
def delete_maintenance_document(document_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM maintenance_documents WHERE id = %s", (document_id,))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'Документ обслуживания удален'})
        
    except Exception as e:
        print(f"Error deleting maintenance document: {e}")
        return jsonify({'error': str(e)}), 500

# API для истекающих поверок - улучшенная версия
@app.route('/api/expiring-verifications')
def get_expiring_verifications():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Получаем все активные комплексы
        cursor.execute("""
            SELECT id, number, complex_name, location, verification_valid_until
            FROM complexes 
            WHERE (is_archived = FALSE OR is_archived IS NULL OR is_archived = 0)
        """)
        
        all_complexes = cursor.fetchall()
        
        # Фильтруем и обрабатываем даты
        expiring_complexes = []
        today = datetime.now().date()
        
        print(f"Processing {len(all_complexes)} active complexes")
        
        for complex in all_complexes:
            verification_date = complex['verification_valid_until']
            
            # Пропускаем пустые и некорректные даты
            if not verification_date or str(verification_date).strip() in ['0000-00-00', '']:
                continue
                
            try:
                valid_date = None
                date_str = str(verification_date).strip()
                
                # Пробуем разные форматы дат
                if re.match(r'^\d{4}-\d{2}-\d{2}$', date_str):
                    # Формат YYYY-MM-DD
                    valid_date = datetime.strptime(date_str, '%Y-%m-%d').date()
                elif re.match(r'^\d{2}\.\d{2}\.\d{4}$', date_str):
                    # Формат DD.MM.YYYY
                    valid_date = datetime.strptime(date_str, '%d.%m.%Y').date()
                elif re.match(r'^\d{1,2}\.\d{1,2}\.\d{4}$', date_str):
                    # Формат D.M.YYYY или DD.M.YYYY и т.д.
                    parts = date_str.split('.')
                    day = parts[0].zfill(2)
                    month = parts[1].zfill(2)
                    year = parts[2]
                    normalized_date = f"{day}.{month}.{year}"
                    valid_date = datetime.strptime(normalized_date, '%d.%m.%Y').date()
                elif hasattr(verification_date, 'date'):
                    # Если это datetime объект
                    valid_date = verification_date.date()
                else:
                    print(f"Unknown date format: {date_str} for complex {complex['id']}")
                    continue
                
                if not valid_date:
                    continue
                
                # Вычисляем разницу в днях
                days_left = (valid_date - today).days
                
                print(f"Complex {complex['number']}: {valid_date} - {days_left} days left")
                
                # Добавляем если поверка истекает в ближайшие 2 месяца или уже истекла
                if days_left <= 60:
                    complex_data = {
                        'id': complex['id'],
                        'number': complex['number'] or 'Без номера',
                        'complex_name': complex['complex_name'] or 'Не указано',
                        'location': complex['location'] or 'Не указан',
                        'verification_valid_until': valid_date.strftime('%d.%m.%Y'),
                        'days_left': days_left
                    }
                    expiring_complexes.append(complex_data)
                    
            except Exception as e:
                print(f"Error processing date '{verification_date}' for complex {complex['id']}: {e}")
                continue
        
        # Сортируем по дням до истечения
        expiring_complexes.sort(key=lambda x: x['days_left'])
        
        cursor.close()
        conn.close()
        
        print(f"Found {len(expiring_complexes)} complexes with expiring verifications")
        
        # Логируем найденные комплексы для отладки
        for comp in expiring_complexes:
            print(f"Expiring: {comp['number']} - {comp['days_left']} days - {comp['verification_valid_until']}")
        
        return jsonify(expiring_complexes)
        
    except Exception as e:
        print(f"Error getting expiring verifications: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

# API для проверки соединения с БД
@app.route('/api/debug/db-status')
def debug_db_status():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'status': 'error', 'message': 'Database connection failed'})
            
        cursor = conn.cursor(dictionary=True)
        
        # Проверяем структуру таблицы complexes
        cursor.execute("DESCRIBE complexes")
        columns = cursor.fetchall()
        
        # Проверяем данные о поверках
        cursor.execute("""
            SELECT COUNT(*) as total, 
                   SUM(CASE WHEN verification_valid_until IS NOT NULL AND verification_valid_until != '0000-00-00' THEN 1 ELSE 0 END) as with_dates,
                   SUM(CASE WHEN verification_valid_until IS NULL OR verification_valid_until = '0000-00-00' THEN 1 ELSE 0 END) as without_dates
            FROM complexes
        """)
        stats = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'status': 'success',
            'columns': columns,
            'stats': stats
        })
        
    except Exception as e:
        return jsonify({'status': 'error', 'message': str(e)})

@app.route('/api/complexes', methods=['POST'])
def save_complex():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        data = request.json
        print(f"Received complex data: {data}")
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Функция для очистки числовых полей
        def clean_numeric_value(value):
            if value is None or value == '':
                return None
            try:
                return int(value)
            except (ValueError, TypeError):
                return None
        
        # Функция для очистки строковых полей
        def clean_string_value(value):
            if value is None or value == '':
                return None
            return str(value).strip()
        
        # Подготавливаем данные согласно вашей структуре БД
        complex_data = {
            'number': clean_string_value(data.get('number')),
            'complex_name': clean_string_value(data.get('complex_name')),
			'full_name': clean_string_value(data.get('full_name')),
            'location': clean_string_value(data.get('location')),
            'ip_address': clean_string_value(data.get('ip_address')),
            'district': clean_string_value(data.get('district')),
            'complex_type': clean_string_value(data.get('complex_type')),
            'install_month': clean_string_value(data.get('install_month')),
            'install_year': clean_numeric_value(data.get('install_year')),
            'commission_date': parse_date(data.get('commission_date')),
            'verification_number': clean_string_value(data.get('verification_number')),
            'verification_date': parse_date(data.get('verification_date')),
            'verification_valid_until': parse_date(data.get('verification_valid_until')),
            # ДАТЧИКИ - берем из полей violation_sensors и overview_sensors
            'violation_sensors': clean_numeric_value(data.get('violation_sensors')),
            'overview_sensors': clean_numeric_value(data.get('overview_sensors')),
            'sim_card_number': clean_string_value(data.get('sim_card_number')),
            'alarm_number': clean_string_value(data.get('alarm_number')),
            'coordinates': clean_string_value(data.get('coordinates')),
            # Разрешенная скорость
            'allowed_speed': clean_numeric_value(data.get('allowed_speed')),
            'pdd_speed': clean_numeric_value(data.get('allowed_speed'))  # Для совместимости
        }
        
        print(f"Prepared complex data: {complex_data}")
        
        # Обработка enum полей нарушений ПДД (преобразуем boolean в 'Да'/'Нет')
        pdd_enum_fields = {
            'pdd_stop': data.get('pdd_stop'),
            'pdd_traffic_light': data.get('pdd_traffic_light'),
            'pdd_rails': data.get('pdd_rails'),
            'row_type': data.get('row_type'),
            'pdd_parking': data.get('pdd_parking'),
            'pdd_pedestrian': data.get('pdd_pedestrian'),
            'pdd_oncoming': data.get('pdd_oncoming'),
            'pdd_belt': data.get('pdd_belt'),
            'pdd_light': data.get('pdd_light')
        }
        
        for field, value in pdd_enum_fields.items():
            if value is not None:
                complex_data[field] = 'Да' if value else 'Нет'
        
        # Обработка поля flow
        if data.get('flow') is not None:
            complex_data['flow'] = 'Да' if data.get('flow') else 'Нет'
        
        # Удаляем None значения, но оставляем пустые строки для обязательных полей
        # Для числовых полей оставляем None, для строковых можно оставить пустую строку
        final_complex_data = {}
        for key, value in complex_data.items():
            if value is not None:
                final_complex_data[key] = value
            # Для обязательных полей, если они None, устанавливаем пустую строку
            elif key in ['number', 'location']:
                final_complex_data[key] = ''
        
        print(f"Final complex data for DB: {final_complex_data}")
        
        if data.get('id'):  # Обновление
            complex_id = data['id']
            set_clause = ", ".join([f"{key} = %s" for key in final_complex_data.keys()])
            values = list(final_complex_data.values()) + [complex_id]
            
            query = f"UPDATE complexes SET {set_clause} WHERE id = %s"
            print(f"Update query: {query}")
            print(f"Update values: {values}")
            
            cursor.execute(query, values)
            action = 'updated'
        else:  # Добавление
            columns = ", ".join(final_complex_data.keys())
            placeholders = ", ".join(["%s"] * len(final_complex_data))
            
            query = f"INSERT INTO complexes ({columns}) VALUES ({placeholders})"
            print(f"Insert query: {query}")
            print(f"Insert values: {list(final_complex_data.values())}")
            
            cursor.execute(query, list(final_complex_data.values()))
            complex_id = cursor.lastrowid
            action = 'created'
        
        conn.commit()
        cursor.close()
        conn.close()
        
        print(f"Complex {action} successfully with ID: {complex_id}")
        return jsonify({'status': 'success', 'id': complex_id, 'action': action})
        
    except Exception as e:
        print(f"Error saving complex: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500
		
@app.route('/api/debug/test-complex', methods=['POST'])
def debug_test_complex():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    """Тестовый endpoint для отладки создания комплекса"""
    try:
        data = request.json
        print("=== DEBUG COMPLEX CREATION ===")
        print(f"Received data: {data}")
        print(f"Data type: {type(data)}")
        print("=== END DEBUG ===")
        
        return jsonify({
            'status': 'debug_received', 
            'data_received': data,
            'message': 'Данные получены сервером'
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>', methods=['DELETE'])
def delete_complex(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        # Удаляем связанные данные (каскадное удаление)
        cursor.execute("DELETE FROM maintenance_schedule WHERE complex_id = %s", (complex_id,))
        cursor.execute("DELETE FROM complex_images WHERE complex_id = %s", (complex_id,))
        cursor.execute("DELETE FROM complex_documents WHERE complex_id = %s", (complex_id,))
        cursor.execute("DELETE FROM calibration_images WHERE complex_id = %s", (complex_id,))
        cursor.execute("DELETE FROM maintenance_documents WHERE complex_id = %s", (complex_id,))
        cursor.execute("DELETE FROM complexes WHERE id = %s", (complex_id,))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'Комплекс удален'})
        
    except Exception as e:
        print(f"Error deleting complex: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/archive', methods=['POST'])
def archive_complex(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        data = request.json
        archive = data.get('archive', True)
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("UPDATE complexes SET is_archived = %s, archived_date = %s WHERE id = %s", 
                     (archive, datetime.now() if archive else None, complex_id))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        action = "архивирован" if archive else "восстановлен из архива"
        return jsonify({'status': 'success', 'message': f'Комплекс {action}'})
        
    except Exception as e:
        print(f"Error archiving complex: {e}")
        return jsonify({'error': str(e)}), 500

# Maintenance CRUD operations
@app.route('/api/maintenance', methods=['POST'])
def add_maintenance():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        data = request.json
        print(f"Maintenance data received: {data}")
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        maintenance_date = parse_date(data.get('maintenance_date'))
        maintenance_type = data.get('maintenance_type', '')
        description = data.get('description', '')
        complex_id = data.get('complex_id')
        
        print(f"Parsed data - complex_id: {complex_id}, date: {maintenance_date}, type: {maintenance_type}")
        
        # Проверяем обязательные поля
        if not complex_id:
            return jsonify({'error': 'Complex ID is required'}), 400
        if not maintenance_date:
            return jsonify({'error': 'Maintenance date is required'}), 400
        if not maintenance_type:
            return jsonify({'error': 'Maintenance type is required'}), 400
        
        # Автозаполнение описания в зависимости от типа обслуживания
        if not description and maintenance_type != 'Внеплановое':
            maintenance_descriptions = {
                'ТО - еженедельное': 'Плановое еженедельное техническое обслуживание',
                'ТО1 - ежемесячное': 'Плановое ежемесячное техническое обслуживание',
                'ТО2 - ежеквартальное': 'Плановое ежеквартальное техническое обслуживание',
                'ТО3 - ежегодное': 'Плановое ежегодное техническое обслуживание',
                'Метрологическая поверка': 'Плановое метрологическое обслуживание и поверка оборудования'
            }
            description = maintenance_descriptions.get(maintenance_type, '')
        
        print(f"Final description: {description}")
        
        # Исправленный запрос с учетом поля parts_replaced
        cursor.execute("""
            INSERT INTO maintenance_schedule 
            (complex_id, maintenance_date, maintenance_type, description, parts_replaced)
            VALUES (%s, %s, %s, %s, %s)
        """, (complex_id, maintenance_date, maintenance_type, description, ''))
        
        conn.commit()
        maintenance_id = cursor.lastrowid
        cursor.close()
        conn.close()
        
        print(f"Maintenance record created successfully with ID: {maintenance_id}")
        return jsonify({'status': 'success', 'id': maintenance_id})
        
    except Exception as e:
        print(f"Error adding maintenance: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance/<int:maintenance_id>', methods=['PUT'])
def update_maintenance(maintenance_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        data = request.json
        print(f"Updating maintenance {maintenance_id} with data: {data}")
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        maintenance_date = parse_date(data.get('maintenance_date'))
        maintenance_type = data.get('maintenance_type', '')
        description = data.get('description', '')
        
        print(f"Parsed data - date: {maintenance_date}, type: {maintenance_type}")
        
        # Проверяем обязательные поля
        if not maintenance_date:
            return jsonify({'error': 'Maintenance date is required'}), 400
        if not maintenance_type:
            return jsonify({'error': 'Maintenance type is required'}), 400
        
        # Автозаполнение описания в зависимости от типа обслуживания
        if not description and maintenance_type != 'Внеплановое':
            maintenance_descriptions = {
                'ТО - еженедельное': 'Плановое еженедельное техническое обслуживание',
                'ТО1 - ежемесячное': 'Плановое ежемесячное техническое обслуживание',
                'ТО2 - ежеквартальное': 'Плановое ежеквартальное техническое обслуживание',
                'ТО3 - ежегодное': 'Плановое ежегодное техническое обслуживание',
                'Метрологическая поверка': 'Плановое метрологическое обслуживание и поверка оборудования'
            }
            description = maintenance_descriptions.get(maintenance_type, '')
        
        print(f"Final description: {description}")
        
        # Обновляем запись обслуживания
        cursor.execute("""
            UPDATE maintenance_schedule 
            SET maintenance_date = %s, maintenance_type = %s, description = %s
            WHERE id = %s
        """, (maintenance_date, maintenance_type, description, maintenance_id))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        print(f"Maintenance record {maintenance_id} updated successfully")
        return jsonify({'status': 'success', 'message': 'Обслуживание обновлено'})
        
    except Exception as e:
        print(f"Error updating maintenance: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance/bulk', methods=['POST'])
def bulk_add_maintenance():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    """Массовое добавление обслуживания из Excel файла"""
    try:
        print("Bulk maintenance upload started")
        
        if 'file' not in request.files:
            return jsonify({'error': 'No file provided'}), 400
        
        file = request.files['file']
        if file.filename == '':
            return jsonify({'error': 'No file selected'}), 400
        
        # Проверяем расширение файла
        if not file.filename.lower().endswith(('.xlsx', '.xls')):
            return jsonify({'error': 'File must be Excel format (.xlsx, .xls)'}), 400
        
        # Читаем Excel файл
        df = pd.read_excel(file)
        print(f"Excel file loaded with {len(df)} rows")
        print("Columns:", df.columns.tolist())
        print("First few rows:")
        print(df.head())
        
        # Проверяем структуру данных
        if len(df.columns) < 2:
            return jsonify({'error': 'Excel file must have at least 2 columns: номер комплекса и дата'}), 400
        
        results = {
            'total_processed': 0,
            'successful': 0,
            'failed': 0,
            'details': []
        }
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
        
        cursor = conn.cursor(dictionary=True)
        
        for index, row in df.iterrows():
            try:
                # Получаем данные из строки
                complex_number = str(row.iloc[0]).strip() if pd.notna(row.iloc[0]) else None
                date_value = row.iloc[1]
                maintenance_type_raw = str(row.iloc[2]).strip() if len(row) > 2 and pd.notna(row.iloc[2]) else None
                description_raw = str(row.iloc[3]).strip() if len(row) > 3 and pd.notna(row.iloc[3]) else None
                
                print(f"Processing row {index}: complex={complex_number}, date={date_value}, type={maintenance_type_raw}, desc={description_raw}")
                
                # Пропускаем пустые строки
                if not complex_number:
                    results['details'].append({
                        'row': index + 1,
                        'status': 'skipped',
                        'message': 'Пропущено: отсутствует номер комплекса'
                    })
                    continue
                
                # Нормализуем номер комплекса (убираем лишние пробелы)
                complex_number = re.sub(r'\s+', ' ', complex_number).strip()
                
                # Преобразуем дату
                maintenance_date = parse_date(date_value)
                if not maintenance_date:
                    results['details'].append({
                        'row': index + 1,
                        'complex_number': complex_number,
                        'status': 'error',
                        'message': f'Неверный формат даты: {date_value}'
                    })
                    results['failed'] += 1
                    continue
                
                # Определяем тип обслуживания и описание
                maintenance_type, description = parse_maintenance_type(maintenance_type_raw, description_raw)
                
                # Ищем комплекс по номеру
                cursor.execute("SELECT id FROM complexes WHERE number = %s", (complex_number,))
                complex_result = cursor.fetchone()
                
                if not complex_result:
                    results['details'].append({
                        'row': index + 1,
                        'complex_number': complex_number,
                        'status': 'error', 
                        'message': f'Комплекс не найден: {complex_number}'
                    })
                    results['failed'] += 1
                    continue
                
                complex_id = complex_result['id']
                
                # Для ВНЕПЛАНОВОГО обслуживания - НЕ проверяем дубликаты, разрешаем несколько записей на одну дату
                if maintenance_type != 'Внеплановое':
                    # Проверяем, нет ли уже такой записи (только для плановых ТО)
                    cursor.execute("""
                        SELECT id FROM maintenance_schedule 
                        WHERE complex_id = %s AND maintenance_date = %s AND maintenance_type = %s
                    """, (complex_id, maintenance_date, maintenance_type))
                    
                    existing_record = cursor.fetchone()
                    if existing_record:
                        results['details'].append({
                            'row': index + 1,
                            'complex_number': complex_number,
                            'status': 'skipped',
                            'message': f'Запись уже существует: {maintenance_type} на {maintenance_date}'
                        })
                        continue
                
                # Добавляем запись обслуживания
                cursor.execute("""
                    INSERT INTO maintenance_schedule 
                    (complex_id, maintenance_date, maintenance_type, description, parts_replaced)
                    VALUES (%s, %s, %s, %s, %s)
                """, (complex_id, maintenance_date, maintenance_type, description, ''))
                
                results['details'].append({
                    'row': index + 1,
                    'complex_number': complex_number,
                    'status': 'success',
                    'message': f'Добавлено: {maintenance_type} на {maintenance_date}'
                })
                results['successful'] += 1
                
            except Exception as e:
                print(f"Error processing row {index}: {e}")
                import traceback
                traceback.print_exc()
                
                results['details'].append({
                    'row': index + 1,
                    'complex_number': complex_number if 'complex_number' in locals() else 'Unknown',
                    'status': 'error',
                    'message': f'Ошибка обработки: {str(e)}'
                })
                results['failed'] += 1
        
        conn.commit()
        cursor.close()
        conn.close()
        
        results['total_processed'] = len(df)
        
        print(f"Bulk maintenance completed: {results['successful']} successful, {results['failed']} failed")
        
        return jsonify({
            'status': 'success',
            'results': results
        })
        
    except Exception as e:
        print(f"Error in bulk maintenance: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

def parse_maintenance_type(maintenance_type_raw, description_raw=None):
    """Парсит тип обслуживания и возвращает тип и описание"""
    if not maintenance_type_raw:
        return 'Внеплановое', description_raw or 'Внеплановое техническое обслуживание'
    
    maintenance_type_raw = maintenance_type_raw.upper().strip()
    
    # Сопоставление типов ТО
    if 'ТО3' in maintenance_type_raw or 'ТО 3' in maintenance_type_raw:
        return 'ТО3 - ежегодное', 'Плановое ежегодное техническое обслуживание'
    elif 'ТО2' in maintenance_type_raw or 'ТО 2' in maintenance_type_raw:
        return 'ТО2 - ежеквартальное', 'Плановое ежеквартальное техническое обслуживание'
    elif 'ТО1' in maintenance_type_raw or 'ТО 1' in maintenance_type_raw:
        return 'ТО1 - ежемесячное', 'Плановое ежемесячное техническое обслуживание'
    elif 'ТО' in maintenance_type_raw:
        return 'ТО - еженедельное', 'Плановое еженедельное техническое обслуживание'
    elif 'ПОВЕРК' in maintenance_type_raw or 'МЕТРОЛОГ' in maintenance_type_raw:
        return 'Метрологическая поверка', 'Плановое метрологическое обслуживание и поверка оборудования'
    elif 'ВНЕПЛАНОВ' in maintenance_type_raw:
        # Для внепланового используем описание из столбца D, если оно есть
        description = description_raw or 'Внеплановое техническое обслуживание'
        return 'Внеплановое', description
    else:
        # Для неизвестных типов используем как есть и берем описание из столбца D
        description = description_raw or f'Обслуживание: {maintenance_type_raw}'
        return maintenance_type_raw, description

@app.route('/api/maintenance/<int:maintenance_id>', methods=['DELETE'])
def delete_maintenance(maintenance_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM maintenance_schedule WHERE id = %s", (maintenance_id,))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success'})
        
    except Exception as e:
        print(f"Error deleting maintenance: {e}")
        return jsonify({'error': str(e)}), 500

# Тестовые endpoints
@app.route('/api/test')
def test_api():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    return jsonify({'message': 'API is working!'})

@app.route('/api/debug/complexes')
def debug_complexes():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    """Маршрут для отладки - показывает все данные complexes"""
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM complexes LIMIT 5")
        data = cursor.fetchall()
        cursor.close()
        conn.close()
        return jsonify(data)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# ==================== ДОКУМЕНТЫ ОБСЛУЖИВАНИЯ С ДАТАМИ ====================

@app.route('/api/complexes/<int:complex_id>/maintenance-documents-with-dates')
def get_maintenance_documents_with_dates(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Получаем документы с группировкой по датам
        cursor.execute("""
            SELECT 
                id,
                document_name,
                document_type,
                document_size,
                upload_date,
                DATE(upload_date) as document_date,
                work_date
            FROM maintenance_documents 
            WHERE complex_id = %s 
            ORDER BY work_date DESC, upload_date DESC
        """, (complex_id,))
        
        documents = cursor.fetchall()
        
        # Группируем документы по датам работ
        documents_by_date = {}
        for doc in documents:
            # Используем work_date если есть, иначе upload_date
            work_date = doc['work_date'] if doc['work_date'] else doc['document_date']
            date_key = work_date.strftime('%Y-%m-%d') if work_date else doc['document_date'].strftime('%Y-%m-%d')
            date_display = work_date.strftime('%d.%m.%Y') if work_date else doc['upload_date'].strftime('%d.%m.%Y')
            
            if date_key not in documents_by_date:
                documents_by_date[date_key] = {
                    'date_display': date_display,
                    'date_key': date_key,
                    'documents': []
                }
            
            # Форматируем данные документа
            if doc['upload_date']:
                doc['upload_date'] = doc['upload_date'].strftime('%d.%m.%Y %H:%M')
            if doc['document_size']:
                doc['size_formatted'] = f"{doc['document_size'] // 1024} KB"
            
            # Переводим типы на русский
            type_translations = {
                'TECHNICAL': 'Техническая документация',
                'VERIFICATION': 'Поверка',
                'MAINTENANCE': 'Обслуживание',
                'OTHER': 'Другое'
            }
            if doc['document_type']:
                doc['document_type_ru'] = type_translations.get(doc['document_type'], doc['document_type'])
            
            documents_by_date[date_key]['documents'].append(doc)
        
        # Преобразуем в список для JSON
        result = list(documents_by_date.values())
        
        cursor.close()
        conn.close()
        
        return jsonify(result)
        
    except Exception as e:
        print(f"Error getting maintenance documents with dates: {e}")
        return jsonify({'error': str(e)}), 500

# Добавьте столбец work_date в таблицу maintenance_documents если его нет
def add_work_date_column():
    try:
        conn = get_db_connection()
        if not conn:
            return
            
        cursor = conn.cursor()
        
        # Проверяем есть ли столбец work_date
        cursor.execute("""
            SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'maintenance_documents' AND COLUMN_NAME = 'work_date'
        """)
        
        if not cursor.fetchone():
            # Добавляем столбец
            cursor.execute("ALTER TABLE maintenance_documents ADD COLUMN work_date DATE")
            conn.commit()
            print("Added work_date column to maintenance_documents table")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"Error adding work_date column: {e}")

# Вызовите эту функцию при запуске приложения
add_work_date_column()

# ==================== ФОТО ОБСЛУЖИВАНИЯ ====================

@app.route('/api/complexes/<int:complex_id>/maintenance-photos')
def get_maintenance_photos(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT id, photo_name, work_date, upload_date 
            FROM maintenance_photos 
            WHERE complex_id = %s 
            ORDER BY work_date DESC, upload_date DESC
        """, (complex_id,))
        
        photos = cursor.fetchall()
        
        print(f"Found {len(photos)} maintenance photos for complex {complex_id}")
        
        # Форматируем даты
        for photo in photos:
            if photo['work_date']:
                photo['work_date'] = photo['work_date'].strftime('%d.%m.%Y')
            if photo['upload_date']:
                photo['upload_date'] = photo['upload_date'].strftime('%d.%m.%Y %H:%M')
        
        cursor.close()
        conn.close()
        
        return jsonify(photos)
        
    except Exception as e:
        print(f"Error getting maintenance photos: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/complexes/<int:complex_id>/maintenance-photos', methods=['POST'])
def upload_maintenance_photo(complex_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        if 'photo' not in request.files:
            return jsonify({'error': 'No file part'}), 400
        
        file = request.files['photo']
        
        if file.filename == '':
            return jsonify({'error': 'No selected file'}), 400
        
        # Проверяем тип файла
        allowed_extensions = {'png', 'jpg', 'jpeg', 'gif', 'bmp'}
        file_extension = file.filename.rsplit('.', 1)[1].lower() if '.' in file.filename else ''
        
        if file_extension not in allowed_extensions:
            return jsonify({'error': 'Invalid file type. Allowed: png, jpg, jpeg, gif, bmp'}), 400
        
        photo_name = request.form.get('photo_name') or file.filename
        work_date = request.form.get('work_date')
        photo_data = file.read()
        
        # Парсим дату работ если указана
        parsed_work_date = parse_date(work_date) if work_date else None
        
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT INTO maintenance_photos (complex_id, photo_name, photo_data, photo_type, work_date)
            VALUES (%s, %s, %s, %s, %s)
        """, (complex_id, photo_name, photo_data, file_extension.upper(), parsed_work_date))
        
        conn.commit()
        photo_id = cursor.lastrowid
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'id': photo_id, 'message': 'Фото обслуживания загружено'})
        
    except Exception as e:
        print(f"Error uploading maintenance photo: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance-photos/<int:photo_id>')
def get_maintenance_photo(photo_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        cursor.execute("SELECT photo_data, photo_name FROM maintenance_photos WHERE id = %s", (photo_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if result:
            # Определяем MIME тип по расширению файла
            file_extension = result['photo_name'].rsplit('.', 1)[-1].lower()
            mime_types = {
                'jpg': 'image/jpeg', 'jpeg': 'image/jpeg', 'png': 'image/png', 
                'gif': 'image/gif', 'bmp': 'image/bmp'
            }
            mimetype = mime_types.get(file_extension, 'image/jpeg')
            
            return send_file(
                io.BytesIO(result['photo_data']),
                mimetype=mimetype,
                as_attachment=False,
                download_name=result['photo_name']
            )
        else:
            return jsonify({'error': 'Photo not found'}), 404
            
    except Exception as e:
        print(f"Error getting maintenance photo: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/maintenance-photos/<int:photo_id>', methods=['DELETE'])
def delete_maintenance_photo(photo_id):
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor()
        cursor.execute("DELETE FROM maintenance_photos WHERE id = %s", (photo_id,))
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'status': 'success', 'message': 'Фото обслуживания удалено'})
        
    except Exception as e:
        print(f"Error deleting maintenance photo: {e}")
        return jsonify({'error': str(e)}), 500

# API для массовой загрузки фото обслуживания из сетевой папки
@app.route('/api/maintenance-photos/bulk-upload', methods=['POST'])
def bulk_upload_maintenance_photos():
    auth_error = check_auth()
    if auth_error:
        return auth_error
        
    admin_error = check_admin()
    if admin_error:
        return admin_error
        
    try:
        data = request.json
        selected_date = data.get('selected_date')
        
        if not selected_date:
            return jsonify({'error': 'Дата не указана'}), 400
        
        work_date = parse_date(selected_date)
        if not work_date:
            return jsonify({'error': 'Неверный формат даты'}), 400
        
        # Формируем путь к папке с фото
        date_obj = datetime.strptime(work_date, '%Y-%m-%d')
        year = date_obj.strftime('%Y')
        month = date_obj.strftime('%m')
        day = date_obj.strftime('%d')
        
        network_path = r"\\192.168.67.251\сеть\!!!МОНИТОРИНГ\Обслуживание\Фото"
        target_folder = os.path.join(network_path, year, month, day)
        
        print(f"Looking for photos in: {target_folder}")
        
        if not os.path.exists(target_folder):
            return jsonify({'error': f'Папка не найдена: {target_folder}'}), 404
        
        # Загружаем mapping комплексов
        complexes_mapping = load_complexes_mapping_from_excel()
        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        results = {
            'total_processed': 0,
            'successful': 0,
            'failed': 0,
            'details': []
        }
        
        # Обрабатываем все подпапки
        folder_count = 0
        for folder_name in os.listdir(target_folder):
            folder_path = os.path.join(target_folder, folder_name)
            
            if not os.path.isdir(folder_path):
                continue
                
            folder_count += 1
            print(f"Processing folder {folder_count}: {folder_name}")
            
            # Ищем комплекс по названию папки
            complex_number = find_complex_number(folder_name, complexes_mapping)

            if not complex_number:
                results['details'].append({
                    'folder': folder_name,
                    'status': 'error',
                    'message': 'Комплекс не найден в базе данных'
                })
                results['failed'] += 1
                continue

            # Получаем ID комплекса
            cursor.execute("SELECT id FROM complexes WHERE number = %s", (complex_number,))
            complex_result = cursor.fetchone()

            if not complex_result:
                # Пробуем найти по полному названию
                cursor.execute("SELECT id FROM complexes WHERE full_name = %s", (folder_name,))
                complex_result = cursor.fetchone()
    
                if not complex_result:
                    # Пробуем найти по частичному совпадению в полном названии
                    cursor.execute("SELECT id FROM complexes WHERE full_name LIKE %s", (f"%{folder_name}%",))
                    complex_result = cursor.fetchone()

            if not complex_result:
                results['details'].append({
                    'folder': folder_name,
                    'complex_number': complex_number,
                    'status': 'error',
                    'message': f'Комплекс с номером {complex_number} не найден в БД'
                })
                results['failed'] += 1
                continue
            
            complex_id = complex_result['id']
            
            # Обрабатываем фото в папке
            photos_uploaded = 0
            photo_files = [f for f in os.listdir(folder_path) if f.lower().endswith(('.png', '.jpg', '.jpeg', '.gif', '.bmp'))]
            
            if not photo_files:
                results['details'].append({
                    'folder': folder_name,
                    'complex_number': complex_number,
                    'status': 'warning',
                    'message': 'Фото не найдены в папке'
                })
                continue
            
            for filename in photo_files:
                try:
                    file_path = os.path.join(folder_path, filename)
                    
                    # Пропускаем пустые файлы
                    if os.path.getsize(file_path) == 0:
                        continue
                    
                    # Читаем файл
                    with open(file_path, 'rb') as f:
                        photo_data = f.read()
                    
                    file_extension = filename.rsplit('.', 1)[-1].lower() if '.' in filename else ''
                    photo_type = file_extension.upper() if file_extension else 'JPG'
                    
                    # Проверяем, не было ли уже загружено это фото
                    cursor.execute(
                        "SELECT id FROM maintenance_photos WHERE complex_id = %s AND photo_name = %s AND work_date = %s",
                        (complex_id, filename, work_date)
                    )
                    
                    if cursor.fetchone():
                        print(f"Photo already exists: {filename}")
                        continue
                    
                    # Сохраняем фото в таблицу maintenance_photos
                    cursor.execute("""
                        INSERT INTO maintenance_photos (complex_id, photo_name, photo_data, photo_type, work_date)
                        VALUES (%s, %s, %s, %s, %s)
                    """, (complex_id, filename, photo_data, photo_type, work_date))
                    
                    photos_uploaded += 1
                    results['successful'] += 1
                    
                except Exception as e:
                    print(f"Error processing photo {filename}: {e}")
                    results['details'].append({
                        'folder': folder_name,
                        'photo': filename,
                        'status': 'error',
                        'message': f'Ошибка обработки: {str(e)}'
                    })
                    results['failed'] += 1
            
            if photos_uploaded > 0:
                results['details'].append({
                    'folder': folder_name,
                    'complex_number': complex_number,
                    'status': 'success',
                    'message': f'Загружено {photos_uploaded} фото'
                })
            else:
                results['details'].append({
                    'folder': folder_name,
                    'complex_number': complex_number,
                    'status': 'warning',
                    'message': 'Все фото уже были загружены ранее'
                })
        
        conn.commit()
        cursor.close()
        conn.close()
        
        results['total_processed'] = folder_count
        
        print(f"Bulk upload completed: {results['successful']} successful, {results['failed']} failed")
        
        return jsonify({
            'status': 'success',
            'results': results,
            'target_folder': target_folder
        })
        
    except Exception as e:
        print(f"Error in bulk upload maintenance photos: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': str(e)}), 500

# Вспомогательные функции для работы с фото обслуживания
def load_complexes_mapping_from_excel():
    """Загружает mapping комплексов из Excel файла"""
    # Замените на путь к вашему Excel файлу с mapping
    excel_path = r"\\192.168.67.251\сеть\!!!МОНИТОРИНГ\Обслуживание\complexes_mapping.xlsx"
    
    mapping = {}
    try:
        if os.path.exists(excel_path):
            df = pd.read_excel(excel_path)
            for _, row in df.iterrows():
                folder_name = str(row['folder_name']).strip()
                complex_number = str(row['complex_number']).strip()
                mapping[folder_name] = complex_number
        else:
            print(f"Mapping file not found: {excel_path}")
    except Exception as e:
        print(f"Error loading mapping file: {e}")
    
    return mapping

def find_complex_number(folder_name, mapping):
    """Находит номер комплекса по названию папки"""
    # Прямое соответствие из mapping файла
    if folder_name in mapping:
        return mapping[folder_name]
    
    # Попробуем найти частичное соответствие в mapping
    for key, value in mapping.items():
        if key in folder_name or folder_name in key:
            return value
    
    # Если mapping не найден, ищем в базе данных по полному названию
    try:
        conn = get_db_connection()
        if conn:
            cursor = conn.cursor(dictionary=True)
            
            # Ищем по полному названию
            cursor.execute("SELECT number FROM complexes WHERE full_name = %s", (folder_name,))
            result = cursor.fetchone()
            
            # Если не нашли по полному названию, ищем по частичному совпадению
            if not result:
                cursor.execute("SELECT number FROM complexes WHERE full_name LIKE %s", (f"%{folder_name}%",))
                result = cursor.fetchone()
            
            cursor.close()
            conn.close()
            
            if result:
                return result['number']
    
    except Exception as e:
        print(f"Error searching complex in DB: {e}")
    
    # Последняя попытка - извлечь номер из названия
    import re
    numbers = re.findall(r'\d+', folder_name)
    if numbers:
        return numbers[0]
    
    return None

if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port=5000)

if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port=5000)