* Serving Flask app 'main'
 * Debug mode: on
{'date': '2024-11-09', 'number': '345', 'serial': 'Не получен', 'worker_name': 'ARM_4', 'timestamp': '2024-11-09 17:56:57', 'arma_type': 'RPA'}
Traceback (most recent call last):
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: active_user.last_seen

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Program Files\Python312\Lib\site-packages\flask\app.py", line 1498, in __call__
    return self.wsgi_app(environ, start_response)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\flask\app.py", line 1476, in wsgi_app
    response = self.handle_exception(e)
               ^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\flask\app.py", line 1473, in wsgi_app
    response = self.full_dispatch_request()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\flask\app.py", line 882, in full_dispatch_request
    rv = self.handle_user_exception(e)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\flask\app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\flask\app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\B-ZONE\Desktop\myprojectV2\main.py", line 229, in update_table
    active_user = ActiveUser.query.filter_by(username=worker_name, date=selected_date).first()
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\orm\query.py", line 2728, in first
    return self.limit(1)._iter().first()  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\orm\query.py", line 2827, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
                                                  ^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2362, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2247, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\orm\context.py", line 305, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\sql\elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "c:\Program Files\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: active_user.last_seen
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
PS C:\Users\B-ZONE\Desktop\myprojectV2>

[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
PS C:\Users\B-ZONE\Desktop\myprojectV2>



[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
PS C:\Users\B-ZONE\Desktop\myprojectV2>


[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
PS C:\Users\B-ZONE\Desktop\myprojectV2>

[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
PS C:\Users\B-ZONE\Desktop\myprojectV2>


[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
PS C:\Users\B-ZONE\Desktop\myprojectV2>
[SQL: SELECT active_user.id AS active_user_id, active_user.username AS active_user_username, active_user.date AS active_user_date, active_user.record_count AS active_user_record_count, active_user.last_seen AS active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
S active_user_last_seen
FROM active_user
WHERE active_user.username = ? AND active_user.date = ?
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
 LIMIT ? OFFSET ?]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]
[parameters: ('ARM_4', '2024-11-09', 1, 0)]




from flask import Flask, render_template, request, redirect, make_response, jsonify, send_file, Response
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import func
from datetime import datetime, timedelta, date
import pandas as pd
from io import BytesIO
import json
import queue
import logging
from sqlalchemy import cast, Integer


app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///table_data.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)



class TableRow(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.String(10), nullable=False)
    row_number = db.Column(db.String(10))
    timestamp = db.Column(db.String(50))
    number = db.Column(db.String(50))
    serial = db.Column(db.String(50))
    dl_number = db.Column(db.String(50))
    worker_name = db.Column(db.String(100))
    pack_time = db.Column(db.String(50))
    last_graf = db.Column(db.String(50))
    arma_type = db.Column(db.String(50))
    


class DLCounter(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.String(10), unique=True, nullable=False)
    counter = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return f'<DLCounter {self.date}: {self.counter}>'


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    login_history = db.relationship('LoginHistory', backref='user', lazy=True)


class LoginHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    login_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

class ActiveUser(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), nullable=False)
    date = db.Column(db.Date, nullable=False)
    record_count = db.Column(db.Integer, default=0)
    last_seen = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

    def __repr__(self):
        return f'<ActiveUser {self.username} on {self.date}>'

tables = {}
table = [['№', 'Время отправки', 'Номер ', 'Serial', 'Графа', 'Имя отправителя','Время упаковки' ,'Тип АРМА', 'last_graf', 'Действие']]
# Добавляем счетчик для номеров записей
row_counter = 1
# Создаем очередь для событий
update_queue = queue.Queue()



@app.route('/stream')
def stream():
    def event_stream():
        while True:
            try:
                # Ожидаем новое сообщение с таймаутом 20 секунд
                data = update_queue.get(timeout=20)
                yield f'data: {data}\n\n'
            except queue.Empty:
                # Отправляем keep-alive комментарий, чтобы соединение не разорвалось
                yield ': keep-alive\n\n'
    return Response(event_stream(), mimetype='text/event-stream')

@app.route('/ping', methods=['POST'])
def ping():
    data = request.json
    username = data.get('username')
    if not username:
        return jsonify({"status": "error", "message": "Username is required"}), 400

    today = datetime.now().date()
    active_user = ActiveUser.query.filter_by(username=username, date=today).first()
    if active_user:
        active_user.last_seen = datetime.utcnow()
    else:
        # Если пользователя нет в ActiveUser, создаем запись
        active_user = ActiveUser(username=username, date=today, record_count=0, last_seen=datetime.utcnow())
        db.session.add(active_user)
    db.session.commit()
    return jsonify({"status": "success"})

@app.route('/get_table_data')
def get_table_data():
    date = request.args.get('date')
    filter_arma = request.args.get('filter_arma')  # Новый параметр фильтра

    # Начинаем построение запроса
    query = TableRow.query.filter_by(date=date)
    
    # Если фильтр активирован, добавляем условие
    if filter_arma == 'RPA':
        query = query.filter_by(arma_type='RPA')
    
    rows = query.all()
    data = []
    numbers = {}
    serials = {}
    dl_numbers = {}  # Добавляем словарь для dl_numbers

    for row in rows:
        data.append({
            'id': row.id,  # Добавляем ID записи
            'row_number': row.row_number,
            'timestamp': row.timestamp,
            'number': row.number,
            'serial': row.serial,
            'dl_number': row.dl_number,
            'worker_name': row.worker_name,
            'pack_time': row.pack_time,
            'arma_type': row.arma_type,
            'last_graf': row.last_graf
        })

        # Собираем информацию о дубликатах
        if row.number:
            clean_number = ''.join(filter(str.isdigit, row.number))
            if clean_number in numbers:
                numbers[clean_number].append(len(data) - 1)  # Индекс строки
            else:
                numbers[clean_number] = [len(data) - 1]

        if row.serial:
            clean_serial = ''.join(filter(str.isdigit, row.serial))
            if clean_serial in serials:
                serials[clean_serial].append(len(data) - 1)  # Индекс строки
            else:
                serials[clean_serial] = [len(data) - 1]

        if row.dl_number:
            clean_dl_number = ''.join(filter(str.isdigit, row.dl_number))
            if clean_dl_number in dl_numbers:
                dl_numbers[clean_dl_number].append(len(data) - 1)
            else:
                dl_numbers[clean_dl_number] = [len(data) - 1]

    # Фильтруем только реальные дубликаты
    duplicate_numbers = {k: v for k, v in numbers.items() if len(v) > 1}
    duplicate_serials = {k: v for k, v in serials.items() if len(v) > 1}
    duplicate_dl_numbers = {k: v for k, v in dl_numbers.items() if len(v) > 1}

    return jsonify({
        "data": data,
        "duplicates": {
            "numbers": duplicate_numbers,
            "serials": duplicate_serials,
            "dl_numbers": duplicate_dl_numbers  # Добавляем dl_numbers
        }
    })

@app.route('/update_table', methods=['POST'])
def update_table():
    data = request.json
    print(data)
    date = data['date']
    worker_name = data['worker_name']
    arma_type = data['arma_type']
    
    # Получаем количество существующих записей для этой даты
    row_count = TableRow.query.filter_by(date=date).count() + 1
    
    # Создаём новую строку в таблице
    if not data.get('number') and not data.get('serial'):
        new_row = TableRow(
            date=date,
            row_number=str(row_count),
            timestamp=data['timestamp'],
            number='',  # Пустое значение для номера
            serial='',  # Пустое значение для серийного номера
            dl_number='',  # Пустое значение для графы
            worker_name=worker_name,
            arma_type=arma_type,
        )
    elif not data.get('number'):
        new_row = TableRow(
            date=date,
            row_number=str(row_count),
            timestamp=data['timestamp'],
            number='',  # Пустое значение для номера
            serial=data['serial'],
            dl_number='',  # Пустое значение для графы
            worker_name=worker_name,
            arma_type=arma_type,
        )
    else:
        new_row = TableRow(
            date=date,
            row_number=str(row_count),
            timestamp=data['timestamp'],
            number=data['number'],
            serial=data['serial'],
            dl_number=data.get('dl_number', ''),
            worker_name=worker_name,
            arma_type=arma_type,
        )
    
    # Сохраняем запись в базе данных
    db.session.add(new_row)
    
    # Обновляем или создаём запись в ActiveUser
    try:
        selected_date = datetime.strptime(date, '%Y-%m-%d').date()
    except ValueError:
        return jsonify({"status": "error", "message": "Неверный формат даты"}), 400

    active_user = ActiveUser.query.filter_by(username=worker_name, date=selected_date).first()
    if active_user:
        active_user.record_count += 1
    else:
        active_user = ActiveUser(username=worker_name, date=selected_date, record_count=1)
        db.session.add(active_user)
    
    db.session.commit()
    
    # Отправляем сообщение в очередь для обновления клиентов
    update_queue.put(json.dumps({"type": "update", "date": date}))
    
    return jsonify({"status": "success"})


@app.route('/update_graf', methods=['POST'])
def update_graf():
    try:
        data = request.json
        row_id = int(data.get('id'))
        new_value = data.get('new_value')
        date = data.get('date')
        
        if not row_id or not date:
            return jsonify({"status": "error", "message": "Missing 'id' or 'date' in request"}), 400
        
        # Находим запись по id и дате
        row = TableRow.query.filter_by(id=row_id, date=date).first()
        if row:
            row.dl_number = new_value
            db.session.commit()
            return jsonify({"status": "success"})
        else:
            return jsonify({"status": "error", "message": "Row not found"}), 404
    except (ValueError, TypeError) as e:
        return jsonify({"status": "error", "message": f"Invalid input: {str(e)}"}), 400
    except Exception as e:
        db.session.rollback()
        return jsonify({"status": "error", "message": str(e)}), 500



@app.route('/update_arma_type', methods=['POST'])
def update_arma_type():
    try:
        data = request.json
        date = data['date']
        serial = data['serial']
        arma_type = data['arma_type']
        
        row = TableRow.query.filter_by(date=date, serial=serial).first()
        if row:
            row.arma_type = arma_type
            db.session.commit()
            return jsonify({"status": "success"})
        else:
            return jsonify({"status": "error", "message": "Row not found"})
    except Exception as e:
        return jsonify({"status": "error", "message": str(e)})


@app.route('/update_number', methods=['POST'])
def update_number():
    try:
        data = request.json
        row_id = int(data['id'])  # Используем 'id' вместо 'row_index'
        new_value = data['new_value']
        date = data['date']
        
        # Находим запись по id и дате
        row = TableRow.query.filter_by(id=row_id, date=date).first()
        if row:
            row.number = new_value
            db.session.commit()
            return jsonify({"status": "success"})
        return jsonify({"status": "error", "message": "Row not found"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"status": "error", "message": str(e)})

@app.route('/pack_item', methods=['POST'])
def pack_item():
    try:
        data = request.json
        row_id = data['id']
        date = data['date']
        
        rows = TableRow.query.filter_by(id=row_id, date=date).all()
        if rows:
            row = rows[0]
            pack_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            row.pack_time = pack_time
            db.session.commit()
            return jsonify({"status": "success", "pack_time": pack_time})
        
        return jsonify({"status": "error", "message": "Строка не найдена"}), 404
    except Exception as e:
        print(f"Ошибка в pack_item: {str(e)}")
        return jsonify({"status": "error", "message": str(e)}), 500


@app.route('/set_initial_dl', methods=['POST'])
def set_initial_dl():
    try:
        data = request.json
        if not data:
            return jsonify({"status": "error", "message": "No data received"})

        date = data.get('date')
        initial_number = data.get('initial_number')

        if not date or not initial_number:
            return jsonify({
                "status": "error", 
                "message": "Missing required fields",
                "received": {"date": date, "initial_number": initial_number}
            })

        try:
            initial_number = int(initial_number)
        except ValueError:
            return jsonify({
                "status": "error",
                "message": "Initial number must be an integer"
            })

        print(f"Setting initial DL for date {date}: {initial_number}")  # Отладочный вывод

        dl_counter = DLCounter.query.filter_by(date=date).first()
        if dl_counter:
            dl_counter.counter = initial_number
            print(f"Updated existing counter for {date}")  # Отладочный вывод
        else:
            dl_counter = DLCounter(date=date, counter=initial_number)
            db.session.add(dl_counter)
            print(f"Created new counter for {date}")  # Отладочный вывод

        db.session.commit()
        return jsonify({
            "status": "success",
            "message": f"DL counter set to {initial_number} for date {date}"
        })

    except Exception as e:
        print(f"Error in set_initial_dl: {str(e)}")  # Отладочный вывод
        db.session.rollback()
        return jsonify({
            "status": "error",
            "message": str(e)
        })



    
@app.route('/generate_dl', methods=['POST'])
def generate_dl():
    try:
        data = request.json
        row_id = data['id']
        date = data['date']
        
        # Получаем или создаем счетчик DL для данной даты
        dl_counter = DLCounter.query.filter_by(date=date).first()
        if not dl_counter:
            return jsonify({"status": "error", "message": "DL counter не инициализирован"}), 400
        
        # Генерируем новый DL номер
        dl_number = f"DL{dl_counter.counter:06d}"
        
        # Находим и обновляем нужную запись по id
        row = TableRow.query.filter_by(id=row_id, date=date).first()
        if row:
            row.dl_number = dl_number
            dl_counter.counter += 1  # Увеличиваем счетчик
            db.session.commit()
            return jsonify({"status": "success", "dl_number": dl_number, "id": row_id})
        
        return jsonify({"status": "error", "message": "Строка не найдена"}), 404
    except Exception as e:
        return jsonify({"status": "error", "message": str(e)}), 500
    

@app.route('/get_statistics')
def get_statistics():
    try:
        date = request.args.get('date')
        current_time = datetime.now()
        hour_ago = current_time - timedelta(hours=1)
        
        # Общее количество упакованных записей
        total_records = TableRow.query.filter_by(date=date).filter(TableRow.pack_time.isnot(None)).count()
        
        # Количество записей за последний час
        last_hour_records = TableRow.query.filter_by(date=date)\
            .filter(TableRow.pack_time.isnot(None))\
            .filter(TableRow.pack_time >= hour_ago.strftime("%Y-%m-%d %H:%M:%S"))\
            .count()
        
        return jsonify({
            "total_records": total_records,
            "last_hour_records": last_hour_records
        })
    except Exception as e:
        return jsonify({
            "total_records": 0,
            "last_hour_records": 0,
            "error": str(e)
        })


@app.route('/export_excel')
def export_excel():
    try:
        date = request.args.get('date')
        sort_column = request.args.get('sort_column')
        sort_order = request.args.get('sort_order')
        
        if not date:
            return jsonify({"error": "Date parameter is required"}), 400
        
        # Получаем все записи для выбранной даты
        query = TableRow.query.filter_by(date=date)
        
        # Применяем сортировку, если указана
        if sort_column and sort_order:
            column_mapping = {
                '0': TableRow.row_number,
                '1': TableRow.timestamp,
                '2': TableRow.number,
                '3': TableRow.serial,
                '4': TableRow.dl_number,
                '5': TableRow.worker_name,
                '6': TableRow.pack_time,  # Время упаковки
                '7': TableRow.arma_type,  # Тип АРМА
                '8': TableRow.last_graf
            }
            if sort_column in column_mapping:
                column = column_mapping[sort_column]
                
                # Явно приводим столбец "Номер" к целому числу для корректной сортировки
                if sort_column == '2':
                    column = cast(column, Integer)
                
                if sort_order == 'desc':
                    query = query.order_by(column.desc())
                else:
                    query = query.order_by(column.asc())
            else:
                return jsonify({"error": "Invalid sort column"}), 400
        
        rows = query.all()
        
        # Создаем DataFrame
        data = []
        for row in rows:
            # Безопасное преобразование номера
            try:
                number = int(row.number) if row.number and row.number.strip() else None
            except ValueError:
                number = None
            
            data.append({
                '№': row.row_number,
                'Время отправки': row.timestamp,
                'Номер': number,
                'Serial': row.serial,
                'Графа': row.dl_number,
                'Имя отправителя': row.worker_name,
                'Время упаковки': row.pack_time,  # Время упаковки
                'Тип АРМА': row.arma_type,  # Тип АРМА
                'last_graf': row.last_graf
            })
        
        df = pd.DataFrame(data)
        
        # Создаем Excel файл
        output = BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            df.to_excel(writer, index=False, sheet_name='Sheet1')
            
            # Настраиваем форматирование
            workbook = writer.book
            worksheet = writer.sheets['Sheet1']
            
            # Автоматическая ширина столбцов
            for idx, col in enumerate(df.columns):
                series = df[col]
                max_len = max(
                    series.astype(str).map(len).max(),
                    len(str(col))
                ) + 2
                worksheet.set_column(idx, idx, max_len)
                
                # Форматирование числовых столбцов
                if col in ['Номер', 'Графа']:
                    num_format = workbook.add_format({'num_format': '0'})
                    worksheet.set_column(idx, idx, max_len, num_format)
        
        output.seek(0)
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=f'export_{date}.xlsx'
        )
    
    except Exception as e:
        print(f"Export error: {str(e)}")
        return jsonify({"error": str(e)}), 500



@app.route('/update_serial', methods=['POST'])
def update_serial():
    try:
        data = request.json
        row_index = int(data['row_index'])
        new_value = data['new_value']
        date = data['date']
        
        rows = TableRow.query.filter_by(date=date).all()
        if 0 <= row_index < len(rows):
            row = rows[row_index]
            row.serial = new_value
            db.session.commit()
            return jsonify({"status": "success"})
        return jsonify({"status": "error", "message": "Invalid row index"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"status": "error", "message": str(e)})
    

def initialize_table_for_date(date):
    # Проверяем, существуют ли уже записи для этой даты
    existing = TableRow.query.filter_by(date=date).first()
    if not existing:
        # Создаем новую запись только в случае отсутствия данных
        new_row = TableRow(
            date=date,
            row_number=str(1),
            timestamp=datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            number='',
            serial='',
            dl_number='',
            worker_name='',
            arma_type='',
            pack_time='',
            last_graf=''
        )
        db.session.add(new_row)
        db.session.commit()

@app.route('/update_serial_arma', methods=['POST'])
def update_serial_arma():
    try:
        data = request.json
        date = data['date']
        serial = data['serial']
        arma_type = data['arma_type']
        
        # Создаем новую запись в таблице
        new_row = TableRow(
            date=date,
            serial=serial,
            arma_type=arma_type,
            timestamp=datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        )
        
        db.session.add(new_row)
        db.session.commit()
        
        return jsonify({"status": "success"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"status": "error", "message": str(e)}), 500




@app.route('/register_user', methods=['POST'])
def register_user():
    try:
        data = request.get_json()
        worker_name = data.get('worker_name')
        
        if not worker_name:
            return jsonify({'status': 'error', 'message': 'Worker name is required'}), 400
        
        today = datetime.now().date()
        
        # Проверяем, существует ли запись для пользователя на сегодня
        active_user = ActiveUser.query.filter_by(username=worker_name, date=today).first()
        if not active_user:
            active_user = ActiveUser(username=worker_name, date=today)
            db.session.add(active_user)
        
        db.session.commit()
        
        return jsonify({'status': 'success'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'error', 'message': str(e)}), 500

@app.route('/get_active_users')
def get_active_users():
    today = datetime.now().date()
    active_users = ActiveUser.query.filter_by(date=today).all()

    # Определяем порог времени для статуса "Онлайн" (например, 2 минуты)
    threshold = datetime.utcnow() - timedelta(minutes=2)

    users_status = {}
    for user in active_users:
        status = "Online" if user.last_seen >= threshold else "Offline"
        users_status[user.username] = {
            "record_count": user.record_count,
            "status": status
        }

    return jsonify(users_status)


@app.route('/delete_row', methods=['POST'])
def delete_row():
    try:
        data = request.json
        row_id = data.get('id')
        
        if not row_id:
            return jsonify({'status': 'error', 'message': 'ID записи не предоставлен'}), 400
        
        row = TableRow.query.get(row_id)
        if not row:
            return jsonify({'status': 'error', 'message': 'Запись не найдена'}), 404
        
        db.session.delete(row)
        db.session.commit()
        
        # Отправляем сообщение в очередь для обновления клиентов
        update_queue.put(json.dumps({"type": "delete", "date": row.date, "id": row.id}))
        
        return jsonify({'status': 'success'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'error', 'message': str(e)}), 500

@app.route('/', methods=['GET', 'POST'])
def main():
    if request.method == 'POST':
        worker = request.form.get('worker')
        name = request.form.get('name')
        
        # Сохраняем информацию о входе
        user = User.query.filter_by(username=name).first()
        if not user:
            user = User(username=name)
            db.session.add(user)
        
        login_history = LoginHistory(user=user)
        db.session.add(login_history)
        db.session.commit()
        
        resp = make_response(redirect('/welcome'))
        resp.set_cookie('worker', worker)
        resp.set_cookie('name', name)
        return resp

    return render_template('main.html')

@app.route('/get_current_dl', methods=['GET'])
def get_current_dl():
    date = request.args.get('date')
    if not date:
        return jsonify({"status": "error", "message": "Дата обязательна"}), 400
    
    dl_counter = DLCounter.query.filter_by(date=date).first()
    if dl_counter:
        return jsonify({"status": "success", "current_dl": dl_counter.counter})
    else:
        return jsonify({"status": "error", "message": "Счетчик DL не инициализирован для этой даты"}), 404


@app.route('/welcome')
def welcome():
    worker = request.cookies.get('worker')
    name = request.cookies.get('name')
    
    if not worker or not name:
        return redirect('/')
    
    return render_template('welcome.html', name=name, worker=worker, table=table)



@app.route('/logout')
def logout():
    resp = make_response(redirect('/'))
    resp.delete_cookie('worker')
    resp.delete_cookie('name')
    return resp




if __name__ == '__main__':
    with app.app_context():
        db.create_all()

    logging.disable(logging.CRITICAL)
    app.run(host='192.168.0.234', port=80, debug=True)  #192.168.50.99
const currentUser = "{{ name }}";
function sendPing() {
    if (!username) return;

    $.ajax({
        url: '/ping',
        type: 'POST',
        contentType: 'application/json',
        data: JSON.stringify({ username: username }),
        success: function(response) {
            if (response.status !== 'success') {
                console.error('Ошибка при отправке пинга:', response.message);
            }
        },
        error: function(xhr, status, error) {
            console.error('Ошибка при отправке пинга:', error);
        }
    });
}

function updateActiveUsers() {
    $.ajax({
        url: '/get_active_users',
        type: 'GET',
        success: function(data) {
            const tbody = $('#activeUsersBody');
            const existingRows = tbody.children('tr');

            // Создаём объект для хранения текущих пользователей
            const currentUsers = {};

            // Заполняем объект текущими пользователями
            existingRows.each(function() {
                const username = $(this).children('td').first().text();
                currentUsers[username] = true;
            });

            // Обновляем или добавляем строки
            tbody.empty(); // Очищаем таблицу и пересоздаем
            Object.entries(data).forEach(([username, info]) => {
                const row = $('<tr>');
                
                // Имя пользователя
                row.append($('<td>').text(username));
                
                // Количество записей
                row.append($('<td>').text(info.record_count));
                
                // Статус пользователя с индикатором
                const statusTd = $('<td>');
                const statusIndicator = $('<span>')
                    .addClass('status-indicator')
                    .addClass(info.status === 'Online' ? 'status-online' : 'status-offline');
                statusTd.append(statusIndicator).append(info.status);
                row.append(statusTd);
                
                tbody.append(row);
            });
        },
        error: function(error) {
            console.error('Ошибка при получении активных пользователей:', error);
        }
    });
}

// Регулярный пинг и обновление активных пользователей
$(document).ready(function() {
    // Отправка пинга каждые 60 секунд
    setInterval(sendPing, 60000); // 60,000 мс = 60 секунд

    // Отправка пинга немедленно при загрузке страницы
    sendPing();

    // Обновление списка активных пользователей каждые 30 секунд
    setInterval(updateActiveUsers, 30000); // 30,000 мс = 30 секунд

    // Первоначальное обновление при загрузке страницы
    updateActiveUsers();
});