metro/app.py - master - Flash_A/SubjectTemplate

metro/app.py
996 строк45 KB
FlashAnton
Добавлены материалы от студентов
22 янв 2026, 21:16
22 янв 2026, 21:16511daf0
from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, Response
import codecs
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime, timedelta
import json
import os
import tempfile
import time
import uuid
import traceback


from config import Config

app = Flask(__name__)
app.config.from_object(Config)

db = SQLAlchemy(app)

# Перемещенные импорты для избежания циклических зависимостей
from db_graph_loader import load_database_graph_data, DatabaseGraphError
from db_table_generator import get_schedule_table_data
from excel_graph_loader import load_excel_graph_data, ExcelGraphError
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

# Модели базы данных
class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(120), nullable=False)
    role = db.Column(db.String(20), nullable=False)  # 'scheduler' или 'driver'
    name = db.Column(db.String(100), nullable=False)
    schedules = db.relationship('Schedule', backref='creator', lazy=True, foreign_keys='Schedule.created_by')
    assignments = db.relationship('Assignment', backref='driver', lazy=True, foreign_keys='Assignment.driver_id')

class Schedule(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    created_by = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    schedule_data = db.Column(db.JSON, nullable=False)  # JSON данные расписания
    status = db.Column(db.String(20), default='draft')  # 'draft', 'active', 'completed'
    assignments = db.relationship('Assignment', backref='schedule', lazy=True, cascade="all, delete-orphan")

class Assignment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    schedule_id = db.Column(db.Integer, db.ForeignKey('schedule.id', ondelete='CASCADE'), nullable=False)
    driver_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False)
    direction_id = db.Column(db.Integer, db.ForeignKey('direction.id'), nullable=True)
    shift_start = db.Column(db.DateTime, nullable=False)
    shift_end = db.Column(db.DateTime, nullable=False)
    route = db.Column(db.String(50), nullable=False)
    shift_type = db.Column(db.String(20), nullable=False)  # 'main' или 'substitute'
    direction = db.relationship('Direction', backref='assignments')

class Station(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    order_index = db.Column(db.Integer, nullable=False)
    is_depot = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class Direction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    start_station_id = db.Column(db.Integer, db.ForeignKey('station.id', ondelete='CASCADE'), nullable=False)
    end_station_id = db.Column(db.Integer, db.ForeignKey('station.id', ondelete='CASCADE'), nullable=False)
    start_idx = db.Column(db.Integer, nullable=False)
    end_idx = db.Column(db.Integer, nullable=False)
    color = db.Column(db.String(20), default='#0a8e2a')
    code = db.Column(db.String(10))
    travel_minutes = db.Column(db.Float)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    start_station = db.relationship('Station', foreign_keys=[start_station_id], backref='directions_from')
    end_station = db.relationship('Station', foreign_keys=[end_station_id], backref='directions_to')

class HourInterval(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    label = db.Column(db.String(20), unique=True, nullable=False)  # Например: "5-6"
    start_hour = db.Column(db.Integer, nullable=False)
    end_hour = db.Column(db.Integer, nullable=False)
    order_index = db.Column(db.Integer, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class DirectionHourCount(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    direction_id = db.Column(db.Integer, db.ForeignKey('direction.id', ondelete='CASCADE'), nullable=False)
    hour_interval_id = db.Column(db.Integer, db.ForeignKey('hour_interval.id', ondelete='CASCADE'), nullable=False)
    train_count = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    direction = db.relationship('Direction', backref='hour_counts')
    hour_interval = db.relationship('HourInterval', backref='direction_counts')
    __table_args__ = (db.UniqueConstraint('direction_id', 'hour_interval_id'),)

class TrainRun(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    direction_id = db.Column(db.Integer, db.ForeignKey('direction.id', ondelete='CASCADE'), nullable=False)
    train_id = db.Column(db.String(50), nullable=False)
    departure_minutes = db.Column(db.Float, nullable=False)
    arrival_minutes = db.Column(db.Float, nullable=False)
    departure_time = db.Column(db.Time, nullable=False)
    arrival_time = db.Column(db.Time, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    direction = db.relationship('Direction', backref='runs')

@login_manager.user_loader
def load_user(user_id):
    return db.session.get(User, int(user_id))

# Алгоритмы составления расписаний
class ScheduleAlgorithm:
    def __init__(self):
        self.max_shift_hours = 4.5
        self.min_rest_hours = 3
        self.max_continuous_hours = 4.5
        self.min_weekly_rest_hours = 24
    
    def parse_schedule_data(self, schedule_data):
        """Парсинг данных ГО ЭПС и ПГД"""
        try:
            data = json.loads(schedule_data)
            return data.get('routes', []), data.get('drivers', [])
        except:
            return [], []
    
    def recursive_main_drivers(self, work_intervals, drivers):
        """Рекурсивный алгоритм для основных машинистов"""
        if not work_intervals:
            return []
        
        # Инициализация
        assignments = []
        driver_loads = {i: 0 for i in range(len(drivers))}
        
        def can_assign(interval, driver_idx, current_assignments):
            """Проверка возможности назначения интервала машинисту"""
            driver_assignments = [a for a in current_assignments if a['driver'] == driver_idx]
            
            # Проверка максимальной продолжительности смены
            if interval['duration'] > self.max_shift_hours:
                return False
            
            # Проверка минимального времени отдыха
            for assignment in driver_assignments:
                time_diff = abs((interval['start'] - assignment['end']).total_seconds() / 3600)
                if time_diff < self.min_rest_hours:
                    return False
            
            return True
        
        def backtrack(remaining_intervals, current_assignments, driver_loads):
            """Рекурсивный поиск оптимального распределения"""
            if not remaining_intervals:
                return current_assignments
            
            best_assignment = None
            best_score = float('inf')
            
            interval = remaining_intervals[0]
            
            # Попытка назначить существующему машинисту
            for driver_idx in range(len(drivers)):
                if can_assign(interval, driver_idx, current_assignments):
                    new_assignments = current_assignments + [{
                        'driver': driver_idx,
                        'interval': interval,
                        'start': interval['start'],
                        'end': interval['end']
                    }]
                    new_loads = driver_loads.copy()
                    new_loads[driver_idx] += interval['duration']
                    
                    result = backtrack(remaining_intervals[1:], new_assignments, new_loads)
                    if result:
                        # Вычисление неравномерности нагрузки
                        score = sum(load**2 for load in new_loads.values())
                        if score < best_score:
                            best_score = score
                            best_assignment = result
            
            return best_assignment
        
        # Работаем со строками, чтобы избежать проблем с JSON сериализацией
        result = []
        
        # Простое назначение: каждый маршрут назначаем первому доступному машинисту
        for i, interval in enumerate(work_intervals):
            if i < len(drivers):
                simple_assignment = {
                    'driver': i,
                    'interval': {
                        'start': interval['start'],
                        'end': interval['end'],
                        'duration': interval['duration'],
                        'route': interval['route']
                    },
                    'start': interval['start'],
                    'end': interval['end']
                }
                result.append(simple_assignment)
        
        return result
    
    def heuristic_substitute_drivers(self, main_assignments):
        """Эвристический алгоритм для подменных машинистов"""
        if not main_assignments:
            return []
        
        # Генерация интервалов отдыха (ОП)
        break_intervals = []
        for assignment in main_assignments:
            # Добавляем интервал отдыха после каждой смены
            break_start = datetime.fromisoformat(assignment['end'])
            break_end = break_start + timedelta(hours=self.min_rest_hours)
            break_intervals.append({
                'start': break_start.isoformat(),
                'end': break_end.isoformat(),
                'route': assignment['interval']['route']
            })
        
        # Сортировка по времени начала
        break_intervals.sort(key=lambda x: x['start'])
        
        # Графовая раскраска для минимизации количества подменных
        substitute_assignments = []
        used_substitutes = []
        
        for interval in break_intervals:
            # Поиск совместимого подменного машиниста
            assigned = False
            for sub_idx, sub_assignments in enumerate(used_substitutes):
                # Проверка совместимости (нет пересечений по маршрутам)
                compatible = True
                for existing in sub_assignments:
                    if (existing['route'] == interval['route'] and 
                        not (interval['end'] <= existing['start'] or interval['start'] >= existing['end'])):
                        compatible = False
                        break
                
                if compatible:
                    sub_assignments.append(interval)
                    substitute_assignments.append({
                        'driver': f'substitute_{sub_idx}',
                        'interval': interval,
                        'start': interval['start'],
                        'end': interval['end']
                    })
                    assigned = True
                    break
            
            if not assigned:
                # Создание нового подменного машиниста
                used_substitutes.append([interval])
                substitute_assignments.append({
                    'driver': f'substitute_{len(used_substitutes)-1}',
                    'interval': interval,
                    'start': interval['start'],
                    'end': interval['end']
                })
        
        return substitute_assignments

# Маршруты Flask
@app.route('/')
def index():
    if current_user.is_authenticated:
        if current_user.role == 'scheduler':
            return redirect(url_for('scheduler_dashboard'))
        else:
            return redirect(url_for('driver_dashboard'))
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        user = db.session.scalar(db.select(User).filter_by(username=username))
        
        if user and check_password_hash(user.password_hash, password):
            login_user(user)
            return redirect(url_for('index'))
        else:
            flash('Неверное имя пользователя или пароль')
    
    return render_template('login.html')

@app.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('login'))

@app.route('/scheduler')
@login_required
def scheduler_dashboard():
    if current_user.role != 'scheduler':
        flash('Доступ запрещен')
        return redirect(url_for('index'))
    
    schedules = db.session.scalars(db.select(Schedule).filter_by(created_by=current_user.id)).all()
    drivers = db.session.scalars(db.select(User).filter_by(role='driver')).all()
    
    return render_template('scheduler_dashboard.html', schedules=schedules, drivers=drivers)

@app.route('/driver')
@login_required
def driver_dashboard():
    if current_user.role != 'driver':
        flash('Доступ запрещен')
        return redirect(url_for('index'))
    
    assignments = db.session.scalars(db.select(Assignment).filter_by(driver_id=current_user.id)).all()
    return render_template('driver_dashboard.html', assignments=assignments)

@app.route('/create_schedule', methods=['POST'])
@login_required
def create_schedule():
    if current_user.role != 'scheduler':
        return jsonify({'error': 'Доступ запрещен'}), 403
    
    try:
        schedule_data = request.json
        algorithm = ScheduleAlgorithm()
        
        # Парсинг данных
        routes, drivers = algorithm.parse_schedule_data(json.dumps(schedule_data))
        
        # Генерация интервалов работы
        work_intervals = []
        for route in routes:
            start_time = datetime.fromisoformat(route['start_time'])
            end_time = datetime.fromisoformat(route['end_time'])
            duration = (end_time - start_time).total_seconds() / 3600
            
            work_intervals.append({
                'start': route['start_time'],
                'end': route['end_time'],
                'duration': duration,
                'route': route['name']
            })
        
        # Запуск алгоритмов
        main_assignments = algorithm.recursive_main_drivers(work_intervals, drivers)
        substitute_assignments = algorithm.heuristic_substitute_drivers(main_assignments)
        
        # Отладочная информация
        print(f"DEBUG: main_assignments count: {len(main_assignments)}")
        print(f"DEBUG: substitute_assignments count: {len(substitute_assignments)}")
        print(f"DEBUG: drivers count: {len(drivers)}")
        
        # Сохранение расписания
        schedule = Schedule(
            name=schedule_data.get('name', 'Новое расписание'),
            created_by=current_user.id,
            schedule_data={
                'main_assignments': main_assignments,
                'substitute_assignments': substitute_assignments,
                'routes': routes,
                'drivers': drivers,
                'original_data': schedule_data
            }
        )
        db.session.add(schedule)
        db.session.commit()
        
        # Создание назначений для машинистов
        print(f"DEBUG: Creating assignments for {len(main_assignments)} main assignments")
        for i, assignment in enumerate(main_assignments):
            if i < len(drivers):  # Убеждаемся, что у нас есть машинист для назначения
                driver_id = drivers[i]['id']
                start_time = datetime.fromisoformat(assignment['interval']['start'])
                end_time = datetime.fromisoformat(assignment['interval']['end'])
                
                print(f"DEBUG: Creating assignment for driver {driver_id}, route {assignment['interval']['route']}")
                
                assignment_record = Assignment(
                    schedule_id=schedule.id,
                    driver_id=driver_id,
                    shift_start=start_time,
                    shift_end=end_time,
                    route=assignment['interval']['route'],
                    shift_type='main'
                )
                db.session.add(assignment_record)
        
        # Создание назначений для подменных машинистов
        for assignment in substitute_assignments:
            # Для подменных используем первого доступного машиниста
            if drivers:
                driver_id = drivers[0]['id']  # Можно улучшить логику выбора
                start_time = datetime.fromisoformat(assignment['interval']['start'])
                end_time = datetime.fromisoformat(assignment['interval']['end'])
                
                assignment_record = Assignment(
                    schedule_id=schedule.id,
                    driver_id=driver_id,
                    shift_start=start_time,
                    shift_end=end_time,
                    route=assignment['interval']['route'],
                    shift_type='substitute'
                )
                db.session.add(assignment_record)
        
        # Обновление статуса расписания на "active" после создания назначений
        schedule.status = 'active'
        
        db.session.commit()
        
        return jsonify({'success': True, 'schedule_id': schedule.id})
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/assign_schedule', methods=['POST'])
@login_required
def assign_schedule():
    if current_user.role != 'scheduler':
        return jsonify({'error': 'Доступ запрещен'}), 403
    
    try:
        schedule_id = request.json['schedule_id']
        assignments_data = request.json['assignments']
        
        # Удаление старых назначений
        db.session.execute(db.delete(Assignment).where(Assignment.schedule_id == schedule_id))
        
        # Создание новых назначений
        for assignment_data in assignments_data:
            assignment = Assignment(
                schedule_id=schedule_id,
                driver_id=assignment_data['driver_id'],
                shift_start=datetime.fromisoformat(assignment_data['start']),
                shift_end=datetime.fromisoformat(assignment_data['end']),
                route=assignment_data['route'],
                shift_type=assignment_data['type']
            )
            db.session.add(assignment)
        
        # Обновление статуса расписания на "active"
        schedule = db.session.get(Schedule, schedule_id)
        if schedule:
            schedule.status = 'active'
        
        db.session.commit()
        return jsonify({'success': True})
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/update_schedule_status', methods=['POST'])
@login_required
def update_schedule_status():
    if current_user.role != 'scheduler':
        return jsonify({'error': 'Доступ запрещен'}), 403
    
    try:
        schedule_id = request.json['schedule_id']
        new_status = request.json['status']
        
        schedule = db.session.get(Schedule, schedule_id)
        if not schedule:
            return jsonify({'error': 'Расписание не найдено'}), 404
        
        # Проверка прав доступа
        if schedule.created_by != current_user.id:
            return jsonify({'error': 'У вас нет прав для изменения этого расписания'}), 403
        
        # Валидация статуса
        if new_status not in ['draft', 'active', 'completed']:
            return jsonify({'error': 'Недопустимый статус'}), 400
        
        schedule.status = new_status
        db.session.commit()
        
        return jsonify({'success': True, 'status': new_status})
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/view_schedule/<int:schedule_id>')
@login_required
def view_schedule(schedule_id):
    if current_user.role != 'scheduler':
        flash('Доступ запрещен')
        return redirect(url_for('index'))
    
    schedule = db.get_or_404(Schedule, schedule_id)
    
    # Проверка прав доступа
    if schedule.created_by != current_user.id:
        flash('У вас нет прав для просмотра этого расписания')
        return redirect(url_for('scheduler_dashboard'))
    
    # Парсинг данных расписания
    schedule_data = schedule.schedule_data or {}
    main_assignments = schedule_data.get('main_assignments', [])
    substitute_assignments = schedule_data.get('substitute_assignments', [])
    routes = schedule_data.get('routes', [])
    drivers = schedule_data.get('drivers', [])
    
    # Получение назначений из базы данных
    assignments = db.session.scalars(db.select(Assignment).filter_by(schedule_id=schedule_id)).all()
    
    return render_template('view_schedule.html', 
                         schedule=schedule,
                         main_assignments=main_assignments,
                         substitute_assignments=substitute_assignments,
                         routes=routes,
                         drivers=drivers,
                         assignments=assignments,
                         User=User)

@app.route('/schedule_graph/<int:schedule_id>')
@login_required
def schedule_graph(schedule_id):
    if current_user.role != 'scheduler':
        flash('Доступ запрещен')
        return redirect(url_for('index'))
    
    schedule = db.get_or_404(Schedule, schedule_id)
    
    if schedule.created_by != current_user.id:
        flash('У вас нет прав для просмотра этого графика')
        return redirect(url_for('scheduler_dashboard'))
    
    schedule_data = schedule.schedule_data or {}
    
    routes = schedule_data.get('routes', [])
    stored_main = schedule_data.get('main_assignments', [])
    stored_substitute = schedule_data.get('substitute_assignments', [])
    stored_drivers = schedule_data.get('drivers', [])
    
    assignments = db.session.scalars(db.select(Assignment).filter_by(schedule_id=schedule_id)).all()
    
    driver_names_by_id = {}
    driver_ids = {assignment.driver_id for assignment in assignments}
    if driver_ids:
        driver_names_by_id = {
            user.id: user.name
            for user in db.session.scalars(db.select(User).filter(User.id.in_(driver_ids))).all()
        }
    
    graph_assignments = []
    unique_driver_keys = set()
    
    def add_graph_assignment(driver_id, driver_name, start, end, route, shift_type):
        if not start or not end:
            return
        label = driver_name or (f'ID {driver_id}' if driver_id else 'Неизвестный машинист')
        graph_assignments.append({
            'driver_id': driver_id,
            'driver_name': label,
            'start': start,
            'end': end,
            'route': route or 'Без маршрута',
            'type': shift_type
        })
        unique_key = driver_id if driver_id is not None else label
        unique_driver_keys.add(unique_key)
    
    for assignment in assignments:
        add_graph_assignment(
            assignment.driver_id,
            driver_names_by_id.get(assignment.driver_id),
            assignment.shift_start.isoformat(),
            assignment.shift_end.isoformat(),
            assignment.route,
            assignment.shift_type
        )
    
    assignments_source = 'database' if graph_assignments else 'calculated'
    
    if not graph_assignments:
        drivers_by_index = {idx: driver for idx, driver in enumerate(stored_drivers)}
        
        for entry in stored_main:
            interval = entry.get('interval', {})
            start = interval.get('start') or entry.get('start')
            end = interval.get('end') or entry.get('end')
            driver_idx = entry.get('driver')
            driver_meta = drivers_by_index.get(driver_idx, {})
            driver_id = driver_meta.get('id')
            driver_name = driver_meta.get('name') or (
                f"Машинист {driver_idx + 1}" if driver_idx is not None else 'Машинист'
            )
            route = interval.get('route')
            add_graph_assignment(driver_id, driver_name, start, end, route, 'main')
        
        for entry in stored_substitute:
            interval = entry.get('interval', {})
            start = interval.get('start') or entry.get('start')
            end = interval.get('end') or entry.get('end')
            driver_label = entry.get('driver')
            if isinstance(driver_label, str) and driver_label.startswith('substitute_'):
                try:
                    substitute_index = int(driver_label.split('_')[-1]) + 1
                except ValueError:
                    substitute_index = None
                substitute_name = f'Подменный {substitute_index}' if substitute_index else 'Подменный'
            elif isinstance(driver_label, str):
                substitute_name = driver_label
            else:
                substitute_name = 'Подменный'
            route = interval.get('route')
            add_graph_assignment(None, substitute_name, start, end, route, 'substitute')
    
    graph_data = {
        'routes': routes,
        'assignments': graph_assignments
    }
    
    unique_driver_count = len(unique_driver_keys)
    
    return render_template('schedule_graph.html',
                           schedule=schedule,
                           graph_data=graph_data,
                           unique_driver_count=unique_driver_count,
                           assignments_source=assignments_source)

@app.route('/api/excel_graph_data')
@login_required
def api_excel_graph_data():
    if current_user.role != 'scheduler':
        return jsonify({'error': 'Доступ запрещен'}), 403
    try:
        data = load_database_graph_data()
        return jsonify(data)
    except DatabaseGraphError as exc:
        return jsonify({'error': str(exc)}), 500

@app.route('/schedule/<int:schedule_id>/train_movement')
@login_required
def train_movement(schedule_id):
    schedule = db.get_or_404(Schedule, schedule_id)
    return render_template('train_movement.html', schedule=schedule)

@app.route('/api/train_movement/<int:schedule_id>')
@login_required
def get_train_movement_data(schedule_id):
    import csv
    import os
    
    # Define stations in order from first to last
    stations = [
        'Саларьево', 'Румянцево', 'Тропарёво', 'Юго-Западная',
        'Проспект Вернадского', 'Университет', 'Воробьёвы горы', 'Спортивная',
        'Фрунзенская', 'Парк культуры', 'Кропоткинская', 'Библиотека им. Ленина',
        'Охотный ряд', 'Лубянка', 'Чистые пруды', 'Красные ворота',
        'Комсомольская', 'Красносельская', 'Сокольники', 'Преображенская площадь',
        'Черкизовская', 'Бульвар Рокоссовского'
    ]
    
    # In a real application, you would get the correct CSV path based on the schedule_id
    csv_path = os.path.join(os.path.dirname(__file__), 'schedule_1 (2).csv')
    
    try:
        trains = []
        
        with codecs.open(csv_path, 'r', encoding='utf-8') as file:
            reader = csv.DictReader(file, delimiter=',')
            
            # Process each row in the CSV
            for i, row in enumerate(reader):
                try:
                    # Extract data from CSV row
                    start_time = datetime.strptime(row['Время начала'], '%H:%M').time()
                    end_time = datetime.strptime(row['Время окончания'], '%H:%M').time()
                    shift_type = row.get('Тип смены', '').strip()

                    # Базовая дата для расчётов
                    today = datetime.today().date()
                    start_dt = datetime.combine(today, start_time)
                    end_dt = datetime.combine(today, end_time)

                    # Поддержка перехода через полночь
                    if end_dt <= start_dt:
                        end_dt += timedelta(days=1)

                    total_minutes = (end_dt - start_dt).total_seconds() / 60.0
                    if total_minutes <= 0:
                        continue

                    num_stations = len(stations)
                    if num_stations == 0:
                        continue

                    # Направление поезда: основные и подменные в разные стороны,
                    # чтобы на графике получались перекрещивающиеся диагонали
                    if shift_type.lower().startswith('подмен'):
                        station_indexes = list(range(num_stations - 1, -1, -1))
                    else:
                        station_indexes = list(range(num_stations))

                    path = []
                    last_index = len(station_indexes) - 1
                    for pos, station_idx in enumerate(station_indexes):
                        if last_index > 0:
                            fraction = pos / last_index
                        else:
                            fraction = 0
                        point_dt = start_dt + timedelta(minutes=total_minutes * fraction)
                        path.append({
                            'time': point_dt.strftime('%H:%M'),
                            'station': stations[station_idx]
                        })

                    if not path:
                        continue

                    trains.append({
                        'id': f'П{i + 1}',
                        'color': 'rgba(0, 160, 0, 1)',
                        'path': path
                    })

                except Exception as e:
                    print(f"Error processing row {i+1}: {e}")
                    continue

        if not trains:
            raise FileNotFoundError('No train data')

        return jsonify({
            'status': 'success',
            'stations': stations,
            'trains': trains
        })
        
    except FileNotFoundError:
        # Fallback to sample data if file not found
        return jsonify({
            'status': 'error',
            'message': 'CSV file not found, using sample data',
            'stations': stations,
            'trains': [
                {
                    'id': 'П1',
                    'color': 'rgba(75, 192, 192, 1)',
                    'path': [
                        {'time': '05:30', 'station': 'Бульвар Рокоссовского'},
                        {'time': '05:45', 'station': 'Комсомольская'},
                        {'time': '06:00', 'station': 'Библиотека им. Ленина'},
                        {'time': '06:15', 'station': 'Воробьёвы горы'},
                        {'time': '06:30', 'station': 'Саларьево'}
                    ]
                },
                {
                    'id': 'П2',
                    'color': 'rgba(255, 99, 132, 1)',
                    'path': [
                        {'time': '06:00', 'station': 'Бульвар Рокоссовского'},
                        {'time': '06:15', 'station': 'Комсомольская'},
                        {'time': '06:30', 'station': 'Библиотека им. Ленина'},
                        {'time': '06:45', 'station': 'Воробьёвы горы'},
                        {'time': '07:00', 'station': 'Саларьево'}
                    ]
                }
            ]
        })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

# --- Маршрут для запуска миграции данных из Excel ---
@app.route('/upload-excel', methods=['POST'])
@login_required
def upload_excel():
    if 'excel_file' not in request.files:
        return jsonify({'error': 'Файл не найден'}), 400
    file = request.files['excel_file']
    if file and file.filename.endswith('.xlsx'):
        # Генерируем безопасное имя файла и сохраняем его во временной директории
        filename = f"{uuid.uuid4()}.xlsx"
        filepath = os.path.join(tempfile.gettempdir(), filename)
        file.save(filepath)
        return jsonify({'filename': filename})
    return jsonify({'error': 'Неверный файл'}), 400

@app.route('/run-migration') # Изменено на GET
@login_required
def run_migration():
    if current_user.role != 'scheduler':
        return Response("Доступ запрещен", status=403)

    filename = request.args.get('filename')
    if not filename:
        return Response("Имя файла не предоставлено", status=400)

    filepath = os.path.join(tempfile.gettempdir(), filename)
    if not os.path.exists(filepath):
        return Response("Файл миграции не найден на сервере", status=404)

    def generate_logs():
        with app.app_context():
            def send_log(message):
                yield f"data: {message}\n\n"
                # Этот пустой комментарий заставляет буфер сбрасываться
                yield ": \n\n"
                time.sleep(0.1) # Небольшая задержка для надежности

            try:
                yield from send_log("Начинаем миграцию...")
                
                yield from send_log("Чтение файла с сервера...")
                excel_data = load_excel_graph_data(file_source=filepath)
                yield from send_log("Данные из Excel загружены.")

                yield from send_log("Проверка и создание таблиц, если не существуют...")
                # db.drop_all() # Отключено удаление таблиц
                db.create_all()
                yield from send_log("Таблицы проверены.")

                yield from send_log("Миграция станций...")
                existing_stations = {s.name for s in db.session.scalars(db.select(Station)).all()}
                stations_to_add = []
                for idx, station_name in enumerate(excel_data.get('stations', [])):
                    if station_name not in existing_stations:
                        stations_to_add.append(Station(name=station_name, order_index=idx, is_depot=station_name.startswith('депо')))
                if stations_to_add:
                    db.session.add_all(stations_to_add)
                    db.session.commit()
                    yield from send_log(f"Добавлено {len(stations_to_add)} новых станций.")
                else:
                    yield from send_log("Новых станций не найдено, все уже существуют.")

                yield from send_log("Миграция часовых интервалов...")
                hour_labels = excel_data.get('hours', [])
                existing_hour_labels = {hi.label for hi in db.session.scalars(db.select(HourInterval)).all()}
                intervals_to_add = []
                for idx, label in enumerate(hour_labels):
                    if label not in existing_hour_labels:
                        parts = label.split('-')
                        start_hour, end_hour = (int(parts[0]), int(parts[1])) if len(parts) == 2 else (idx + 5, idx + 6)
                        intervals_to_add.append(HourInterval(label=label, start_hour=start_hour, end_hour=end_hour, order_index=idx))
                if intervals_to_add:
                    db.session.add_all(intervals_to_add)
                    db.session.commit()
                    yield from send_log(f"Добавлено {len(intervals_to_add)} новых часовых интервалов.")
                else:
                    yield from send_log("Новых часовых интервалов не найдено, все уже существуют.")

                yield from send_log("Обновление/добавление направлений и рейсов...")
                stations_map = {s.name: s for s in db.session.scalars(db.select(Station)).all()}
                hour_intervals_map = {hi.label: hi for hi in db.session.scalars(db.select(HourInterval)).all()}
                
                directions_data = excel_data.get('directions', [])
                directions_processed = 0
                hour_counts_count = 0
                runs_count = 0
                
                yield from send_log(f"Найдено {len(directions_data)} направлений в Excel. Начинаем обработку...")

                for dir_data in directions_data:
                    start_station = stations_map.get(dir_data['start_station'])
                    end_station = stations_map.get(dir_data['end_station'])
                    if not start_station or not end_station:
                        yield from send_log(f"[ПРЕДУПРЕЖДЕНИЕ] Пропускаем направление '{dir_data['name']}', т.к. не найдены станции '{dir_data['start_station']}' или '{dir_data['end_station']}'.")
                        continue

                    # Ищем существующее направление по имени
                    direction = db.session.scalar(db.select(Direction).filter_by(name=dir_data['name']))
                    
                    if direction:
                        # Если найдено, удаляем старые данные
                        yield from send_log(f"Обновляем существующее направление: '{direction.name}'. Удаляем старые рейсы...")
                        db.session.query(TrainRun).filter_by(direction_id=direction.id).delete()
                        db.session.query(DirectionHourCount).filter_by(direction_id=direction.id).delete()
                    else:
                        # Если не найдено, создаем новое
                        yield from send_log(f"Создаем новое направление: '{dir_data['name']}'.")
                        direction = Direction(name=dir_data['name'], start_station_id=start_station.id, end_station_id=end_station.id, start_idx=dir_data['start_idx'], end_idx=dir_data['end_idx'], color=dir_data.get('color', '#0a8e2a'), code=dir_data.get('code', ''))
                    
                    # Обновляем/устанавливаем данные
                    direction.travel_minutes = dir_data.get('travel_minutes', 51.0)
                    db.session.add(direction)
                    db.session.flush()  # Получаем/подтверждаем ID для связей
                    directions_processed += 1

                    # Добавляем новые данные
                    for hour_idx, count in enumerate(dir_data.get('hour_counts', [])):
                        if count > 0 and hour_idx < len(hour_labels):
                            hour_interval = hour_intervals_map.get(hour_labels[hour_idx])
                            if hour_interval:
                                db.session.add(DirectionHourCount(direction_id=direction.id, hour_interval_id=hour_interval.id, train_count=count))
                                hour_counts_count += 1

                    for run_data in dir_data.get('runs', []) :
                        try:
                            departure_time = datetime.strptime(run_data['departure_time'], '%H:%M').time()
                            arrival_time = datetime.strptime(run_data['arrival_time'], '%H:%M').time()
                            db.session.add(TrainRun(direction_id=direction.id, train_id=run_data['train_id'], departure_minutes=run_data['departure_minutes'], arrival_minutes=run_data['arrival_minutes'], departure_time=departure_time, arrival_time=arrival_time))
                            runs_count += 1
                        except ValueError:
                                yield from send_log(f"[ПРЕДУПРЕЖДЕНИЕ] Неверный формат времени для рейса {run_data['train_id']}. Пропускаем.")
                                continue
                
                yield from send_log(f"Обработано. Добавляем/обновляем в базе: {directions_processed} направлений, {hour_counts_count} записей о кол-ве поездов, {runs_count} рейсов.")
                db.session.commit()
                yield from send_log(f"Данные успешно сохранены в БД.")
                yield from send_log("DONE: Миграция успешно завершена!")

            except Exception as e:
                db.session.rollback()
                error_details = traceback.format_exc()
                yield from send_log(f"ERROR: Произошла критическая ошибка: {str(e)}")
                yield from send_log(f"StackTrace: {error_details}")

    return Response(generate_logs(), mimetype='text/event-stream')


@app.route('/schedule_table')
@login_required
def schedule_table():
    if current_user.role != 'scheduler':
        flash('Доступ запрещен')
        return redirect(url_for('index'))
    
    data = get_schedule_table_data()
    
    if 'error' in data:
        flash(data['error'])
        return render_template('schedule_table.html', headers=[], rows=[])
        
    return render_template('schedule_table.html', headers=data['headers'], rows=data['rows'])

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
        
        # Создание тестовых пользователей
        if not User.query.first():
            scheduler = User(
                username='scheduler',
                password_hash=generate_password_hash('password'),
                role='scheduler',
                name='Нарядчик'
            )
            driver1 = User(
                username='driver1',
                password_hash=generate_password_hash('password'),
                role='driver',
                name='Иванов И.И.'
            )
            driver2 = User(
                username='driver2',
                password_hash=generate_password_hash('password'),
                role='driver',
                name='Петров П.П.'
            )
            
            db.session.add(scheduler)
            db.session.add(driver1)
            db.session.add(driver2)
            db.session.commit()
    
    app.run(debug=True, host='0.0.0.0', port=5000)

# </final_file_content>

# IMPORTANT: For any future changes to this file, use the final_file_content shown above as your reference. This content reflects the current state of the file, including any auto-formatting (e.g., if you used single quotes but the formatter converted them to double quotes). Always base your SEARCH/REPLACE operations on this final version to ensure accuracy.



# New problems detected after saving the file:
# app.py
# - [Pylance Error] Line 898: Unexpected indentation
# - [Pylance Error] Line 901: Expected expression
# - [Pylance Error] Line 902: Unexpected indentation<environment_details>
# # Visual Studio Code Visible Files
# app.py

# # Visual Studio Code Open Tabs
# templates/schedule_graph.html
# config.py
# app.py
# debug_loader.py
# excel_graph_loader.py
# ../../../AppData/Roaming/Code/User/settings.json

# # Current Time
# 12/5/2025, 8:19:10 PM (Europe/Moscow, UTC+3:00)

# # Context Window Usage
# 110,411 / 1,048.576K tokens used (11%)

# # Current Mode
# ACT MODE
# </environment_details>