---

from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, send_file
from config import Config
from models import db, Employee, ServiceNote, SupplementDetail, CombinationDetail, SubstitutionDetail, AllowanceDetail
from datetime import datetime
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side
from io import BytesIO

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


# ==================== ВСПОМОГАТЕЛЬНЫЕ ФУНКЦИИ ДЛЯ EXCEL ====================

def style_excel_cell(cell, font_bold=False, font_size=11, alignment='center', border=True):
    if border:
        thin_border = Border(
            left=Side(style='thin'), right=Side(style='thin'),
            top=Side(style='thin'), bottom=Side(style='thin')
        )
        cell.border = thin_border
    cell.font = Font(size=font_size, bold=font_bold)
    if alignment == 'center':
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    elif alignment == 'left':
        cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)


# ==================== ГЛАВНАЯ СТРАНИЦА ====================

@app.route('/')
def index():
    return render_template('index.html')


@app.route('/api/notes')
def api_notes():
    page = request.args.get('page', 1, type=int)
    per_page = 15
    payment_type = request.args.get('type', '')
    
    query = ServiceNote.query
    if payment_type:
        query = query.filter(ServiceNote.payment_code == payment_type)
    
    notes = query.order_by(ServiceNote.created_at.desc()).paginate(page=page, per_page=per_page)
    
    items = []
    for note in notes.items:
        employee_name = '-'
        amount = '-'
        start_date = ''
        end_date = ''
        
        if note.payment_code in ['1106', '1006']:
            detail = SupplementDetail.query.filter_by(service_note_id=note.id).first()
            if detail and detail.employee:
                employee_name = detail.employee.full_name
                amount = f"{detail.amount} {'%' if detail.is_percent else 'руб'}"
                start_date = detail.start_date.strftime('%d.%m.%Y') if detail.start_date else ''
                end_date = detail.end_date.strftime('%d.%m.%Y') if detail.end_date else ''
        elif note.payment_code in ['1003', '1021', '1001']:
            detail = CombinationDetail.query.filter_by(service_note_id=note.id).first()
            if detail and detail.employee:
                employee_name = detail.employee.full_name
                amount = f"{detail.extra_pay} руб" if detail.extra_pay else '-'
                start_date = detail.start_date.strftime('%d.%m.%Y') if detail.start_date else ''
                end_date = detail.end_date.strftime('%d.%m.%Y') if detail.end_date else ''
        elif note.payment_code == '1019':
            detail = SubstitutionDetail.query.filter_by(service_note_id=note.id).first()
            if detail and detail.substitute:
                employee_name = detail.substitute.full_name
                amount = f"{detail.extra_pay_percent}%" if detail.extra_pay_percent else '-'
                start_date = detail.start_date.strftime('%d.%m.%Y') if detail.start_date else ''
                end_date = detail.end_date.strftime('%d.%m.%Y') if detail.end_date else ''
        elif note.payment_code == '9999':
            detail = AllowanceDetail.query.filter_by(service_note_id=note.id).first()
            if detail and detail.employee:
                employee_name = detail.employee.full_name
                amount = f"{detail.amount} {detail.unit or ''}"
                start_date = detail.start_date.strftime('%d.%m.%Y') if detail.start_date else ''
                end_date = detail.end_date.strftime('%d.%m.%Y') if detail.end_date else ''
        
        items.append({
            'id': note.id,
            'doc_number': note.doc_number or '-',
            'doc_date': note.doc_date.strftime('%d.%m.%Y') if note.doc_date else '-',
            'payment_code': note.payment_code or '-',
            'employee_name': employee_name,
            'amount': str(amount),
            'start_date': start_date,
            'end_date': end_date
        })
    
    return jsonify({
        'items': items,
        'total': notes.total,
        'page': page,
        'pages': notes.pages,
        'per_page': per_page
    })


# ==================== API ДЛЯ СОТРУДНИКОВ ====================

@app.route('/api/employees')
def get_employees():
    employees = Employee.query.all()
    return jsonify([{
        'id': e.id,
        'tab_no': e.tab_no or '',
        'last_name': e.last_name or '',
        'first_name': e.first_name or '',
        'middle_name': e.middle_name or '',
        'department': e.department or '',
        'position': e.position or '',
        'position_code': e.position_code or ''
    } for e in employees])


@app.route('/api/employees/search')
def search_employees():
    query = request.args.get('q', '')
    if len(query) < 2:
        return jsonify([])
    
    employees = Employee.query.filter(
        (Employee.last_name.ilike(f'%{query}%')) |
        (Employee.first_name.ilike(f'%{query}%')) |
        (Employee.tab_no.ilike(f'%{query}%'))
    ).limit(20).all()
    
    return jsonify([{
        'id': e.id,
        'tab_no': e.tab_no,
        'full_name': e.full_name,
        'department': e.department,
        'position': e.position
    } for e in employees])


@app.route('/employees')
def employees_list():
    return render_template('employees.html')


# ==================== ПРОСМОТР СЛУЖЕБНОЙ ЗАПИСКИ ====================

@app.route('/note/<int:note_id>')
def view_note(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    
    details = None
    if note.payment_code in ['1106', '1006']:
        details = SupplementDetail.query.filter_by(service_note_id=note_id).all()
    elif note.payment_code in ['1003', '1021', '1001']:
        details = CombinationDetail.query.filter_by(service_note_id=note_id).all()
    elif note.payment_code == '1019':
        details = SubstitutionDetail.query.filter_by(service_note_id=note_id).first()
    elif note.payment_code == '9999':
        details = AllowanceDetail.query.filter_by(service_note_id=note_id).all()
    
    return render_template('view_note.html', note=note, details=details)


# ==================== СТРАНИЦЫ ФОРМ ====================

@app.route('/note/new/<payment_code>')
def new_note(payment_code):
    return render_template(f'note_form_{payment_code}.html', payment_code=payment_code)


@app.route('/note/supplement', methods=['POST'])
def create_supplement_note():
    try:
        doc_number = request.form.get('doc_number')
        doc_date = datetime.strptime(request.form.get('doc_date'), '%Y-%m-%d').date()
        payment_code = request.form.get('payment_code')
        base_reason = request.form.get('base_reason')
        
        note = ServiceNote(
            doc_number=doc_number,
            doc_date=doc_date,
            payment_code=payment_code,
            base_reason=base_reason
        )
        db.session.add(note)
        db.session.flush()
        
        employee_ids = request.form.getlist('employee_id')
        start_dates = request.form.getlist('start_date')
        end_dates = request.form.getlist('end_date')
        amounts = request.form.getlist('amount')
        is_percents = request.form.getlist('is_percent')
        work_contents = request.form.getlist('work_content')
        
        for i in range(len(employee_ids)):
            if employee_ids[i]:
                detail = SupplementDetail(
                    service_note_id=note.id,
                    employee_id=int(employee_ids[i]),
                    start_date=datetime.strptime(start_dates[i], '%Y-%m-%d').date(),
                    end_date=datetime.strptime(end_dates[i], '%Y-%m-%d').date(),
                    amount=float(amounts[i]) if amounts[i] else 0,
                    is_percent=(is_percents[i] == 'on' if i < len(is_percents) else False),
                    work_content=work_contents[i] if i < len(work_contents) else ''
                )
                db.session.add(detail)
        
        db.session.commit()
        flash('Служебная записка создана!', 'success')
        return redirect(url_for('index'))
    except Exception as e:
        db.session.rollback()
        flash(f'Ошибка: {str(e)}', 'error')
        return redirect(request.referrer)


@app.route('/note/combination', methods=['POST'])
def create_combination_note():
    try:
        doc_number = request.form.get('doc_number')
        doc_date = datetime.strptime(request.form.get('doc_date'), '%Y-%m-%d').date()
        payment_code = request.form.get('payment_code')
        base_reason = request.form.get('base_reason')
        
        note = ServiceNote(
            doc_number=doc_number,
            doc_date=doc_date,
            payment_code=payment_code,
            base_reason=base_reason
        )
        db.session.add(note)
        db.session.flush()
        
        employee_ids = request.form.getlist('employee_id')
        combined_positions = request.form.getlist('combined_position')
        position_identifiers = request.form.getlist('position_identifier')
        departments = request.form.getlist('department')
        start_dates = request.form.getlist('start_date')
        end_dates = request.form.getlist('end_date')
        extra_pays = request.form.getlist('extra_pay')
        reasons = request.form.getlist('reason')
        
        for i in range(len(employee_ids)):
            if employee_ids[i]:
                detail = CombinationDetail(
                    service_note_id=note.id,
                    employee_id=int(employee_ids[i]),
                    combined_position=combined_positions[i] if i < len(combined_positions) else '',
                    position_identifier=position_identifiers[i] if i < len(position_identifiers) else '',
                    department=departments[i] if i < len(departments) else '',
                    start_date=datetime.strptime(start_dates[i], '%Y-%m-%d').date(),
                    end_date=datetime.strptime(end_dates[i], '%Y-%m-%d').date(),
                    extra_pay=float(extra_pays[i]) if extra_pays[i] else 0,
                    payment_code=payment_code,
                    reason=reasons[i] if i < len(reasons) else ''
                )
                db.session.add(detail)
        
        db.session.commit()
        flash('Служебная записка о совмещении создана!', 'success')
        return redirect(url_for('index'))
    except Exception as e:
        db.session.rollback()
        flash(f'Ошибка: {str(e)}', 'error')
        return redirect(request.referrer)


@app.route('/note/substitution', methods=['POST'])
def create_substitution_note():
    try:
        doc_number = request.form.get('doc_number')
        doc_date = datetime.strptime(request.form.get('doc_date'), '%Y-%m-%d').date()
        payment_code = request.form.get('payment_code')
        base_reason = request.form.get('base_reason')
        
        note = ServiceNote(
            doc_number=doc_number,
            doc_date=doc_date,
            payment_code=payment_code,
            base_reason=base_reason
        )
        db.session.add(note)
        db.session.flush()
        
        substitute_ids = request.form.getlist('substitute_employee_id')
        absent_ids = request.form.getlist('absent_employee_id')
        start_dates = request.form.getlist('start_date')
        end_dates = request.form.getlist('end_date')
        extra_pays = request.form.getlist('extra_pay_percent')
        duties_list = request.form.getlist('duties')
        absence_reasons = request.form.getlist('absence_reason')
        
        for i in range(len(substitute_ids)):
            if substitute_ids[i] and absent_ids[i]:
                detail = SubstitutionDetail(
                    service_note_id=note.id,
                    substitute_employee_id=int(substitute_ids[i]),
                    absent_employee_id=int(absent_ids[i]),
                    start_date=datetime.strptime(start_dates[i], '%Y-%m-%d').date(),
                    end_date=datetime.strptime(end_dates[i], '%Y-%m-%d').date(),
                    extra_pay_percent=float(extra_pays[i]) if extra_pays[i] else 20,
                    payment_code=payment_code,
                    duties=duties_list[i] if i < len(duties_list) else '',
                    absence_reason=absence_reasons[i] if i < len(absence_reasons) else ''
                )
                db.session.add(detail)
        
        db.session.commit()
        flash('Заявка на исполнение обязанностей создана!', 'success')
        return redirect(url_for('index'))
    except Exception as e:
        db.session.rollback()
        flash(f'Ошибка: {str(e)}', 'error')
        return redirect(request.referrer)


@app.route('/note/allowance', methods=['POST'])
def create_allowance_note():
    try:
        doc_number = request.form.get('doc_number')
        doc_date = datetime.strptime(request.form.get('doc_date'), '%Y-%m-%d').date()
        payment_code = request.form.get('payment_code')
        base_reason = request.form.get('base_reason')
        
        note = ServiceNote(
            doc_number=doc_number,
            doc_date=doc_date,
            payment_code=payment_code,
            base_reason=base_reason
        )
        db.session.add(note)
        db.session.flush()
        
        employee_ids = request.form.getlist('employee_id')
        allowance_names = request.form.getlist('allowance_name')
        units = request.form.getlist('unit')
        amounts = request.form.getlist('amount')
        statuses = request.form.getlist('status')
        start_dates = request.form.getlist('start_date')
        end_dates = request.form.getlist('end_date')
        
        for i in range(len(employee_ids)):
            if employee_ids[i]:
                detail = AllowanceDetail(
                    service_note_id=note.id,
                    employee_id=int(employee_ids[i]),
                    allowance_name=allowance_names[i] if i < len(allowance_names) else '',
                    payment_code=request.form.get('allowance_payment_code', ''),
                    unit=units[i] if i < len(units) else '',
                    amount=float(amounts[i]) if amounts[i] else 0,
                    status=statuses[i] if i < len(statuses) else 'установление',
                    start_date=datetime.strptime(start_dates[i], '%Y-%m-%d').date(),
                    end_date=datetime.strptime(end_dates[i], '%Y-%m-%d').date() if end_dates[i] else None
                )
                db.session.add(detail)
        
        db.session.commit()
        flash('Служебная записка об установлении/снятии доплаты создана!', 'success')
        return redirect(url_for('index'))
    except Exception as e:
        db.session.rollback()
        flash(f'Ошибка: {str(e)}', 'error')
        return redirect(request.referrer)


# ==================== ЭКСПОРТ В EXCEL ====================

@app.route('/export/1106/<int:note_id>')
def export_1106(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    details = SupplementDetail.query.filter_by(service_note_id=note_id).all()
    
    wb = Workbook()
    ws = wb.active
    ws.title = "СЗ"
    
    ws.merge_cells('A1:O1')
    ws['A1'] = 'Утверждена приказом предприятия'
    style_excel_cell(ws['A1'], font_bold=True, alignment='center')
    
    ws.merge_cells('A2:O2')
    ws['A2'] = 'ФОРМА В-9.7'
    style_excel_cell(ws['A2'], font_bold=True, alignment='center')
    
    headers = ['№ п/п', 'Таб. номер работника', 'ФИО работника', 'Структурное подразделение',
               'Должность/профессия', 'Период/дата с', 'Период/дата по', 'Доплата', 'Код вида оплаты',
               'Основание (содержание работ)', 'Вид дополнительной работы', 'Код МВЗ', 'Заказ',
               'СПП-элемент', 'С расширением зон обслуживания согласен']
    
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=4, column=col, value=header)
        style_excel_cell(cell, font_bold=True, alignment='center')
    
    for idx, detail in enumerate(details, 1):
        emp = detail.employee
        row = 4 + idx
        ws.cell(row=row, column=1, value=idx)
        ws.cell(row=row, column=2, value=emp.tab_no if emp else '')
        ws.cell(row=row, column=3, value=emp.full_name if emp else '')
        ws.cell(row=row, column=4, value=emp.department if emp else '')
        ws.cell(row=row, column=5, value=emp.position if emp else '')
        ws.cell(row=row, column=6, value=detail.start_date.strftime('%d.%m.%Y') if detail.start_date else '')
        ws.cell(row=row, column=7, value=detail.end_date.strftime('%d.%m.%Y') if detail.end_date else '')
        ws.cell(row=row, column=8, value=f"{detail.amount} {'%' if detail.is_percent else 'руб'}")
        ws.cell(row=row, column=9, value=note.payment_code)
        ws.cell(row=row, column=10, value=detail.work_content or '')
        ws.cell(row=row, column=11, value=detail.work_type or '')
    
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    return send_file(output, download_name=f'СЗ_В-9.7_{note.doc_number}.xlsx', as_attachment=True)


@app.route('/export/1003/<int:note_id>')
def export_1003(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    details = CombinationDetail.query.filter_by(service_note_id=note_id).all()
    
    wb = Workbook()
    ws = wb.active
    ws.title = "СЗ"
    
    ws.merge_cells('A1:Q1')
    ws['A1'] = 'Утверждена приказом предприятия'
    style_excel_cell(ws['A1'], font_bold=True, alignment='center')
    
    ws.merge_cells('A2:Q2')
    ws['A2'] = 'ФОРМА В-9.4'
    style_excel_cell(ws['A2'], font_bold=True, alignment='center')
    
    ws.merge_cells('A3:Q3')
    ws['A3'] = f'№ приказа: {note.doc_number or ""} | Дата: {note.doc_date.strftime("%d.%m.%Y") if note.doc_date else ""}'
    style_excel_cell(ws['A3'], alignment='center')
    
    headers = ['№ п/п', 'Таб. номер', 'ФИО работника', 'Совмещаемая должность', 'Идентификатор должности',
               'Структурное подразделение', 'Период с', 'Период по', 'Размер доплаты', 'Код вида оплаты',
               'Код МВЗ', 'Заказ', 'СПП-элемент', 'Основание', 'Перечень обязанностей', 'Подпись', 'Дата экзамена']
    
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=5, column=col, value=header)
        style_excel_cell(cell, font_bold=True, alignment='center')
    
    for idx, detail in enumerate(details, 1):
        emp = detail.employee
        row = 5 + idx
        ws.cell(row=row, column=1, value=idx)
        ws.cell(row=row, column=2, value=emp.tab_no if emp else '')
        ws.cell(row=row, column=3, value=emp.full_name if emp else '')
        ws.cell(row=row, column=4, value=detail.combined_position or '')
        ws.cell(row=row, column=5, value=detail.position_identifier or '')
        ws.cell(row=row, column=6, value=detail.department or '')
        ws.cell(row=row, column=7, value=detail.start_date.strftime('%d.%m.%Y') if detail.start_date else '')
        ws.cell(row=row, column=8, value=detail.end_date.strftime('%d.%m.%Y') if detail.end_date else '')
        ws.cell(row=row, column=9, value=f"{detail.extra_pay} руб" if detail.extra_pay else '')
        ws.cell(row=row, column=10, value=detail.payment_code or '')
        ws.cell(row=row, column=14, value=detail.reason or '')
        ws.cell(row=row, column=15, value=detail.duties or '')
    
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    return send_file(output, download_name=f'СЗ_В-9.4_{note.doc_number}.xlsx', as_attachment=True)


@app.route('/export/1019/<int:note_id>')
def export_1019(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    detail = SubstitutionDetail.query.filter_by(service_note_id=note_id).first()
    
    wb = Workbook()
    ws = wb.active
    ws.title = "Заявка"
    
    ws.merge_cells('A1:S1')
    ws['A1'] = 'Утверждена приказом предприятия ФОРМА В-9.10'
    style_excel_cell(ws['A1'], font_bold=True, alignment='center')
    
    ws.merge_cells('A3:C3')
    ws['A3'] = f'Заявка № {note.doc_number} от {note.doc_date.strftime("%d.%m.%Y") if note.doc_date else ""}'
    style_excel_cell(ws['A3'], font_bold=True, alignment='left')
    
    ws.merge_cells('A5:B5')
    ws['A5'] = 'В связи с:'
    ws.merge_cells('C5:S5')
    ws['C5'] = detail.absence_reason if detail else ''
    
    if detail:
        ws.merge_cells('A7:B7')
        ws['A7'] = 'Отсутствующий работник:'
        ws.merge_cells('C7:S7')
        ws['C7'] = f"{detail.absent.full_name if detail.absent else ''} (ТН: {detail.absent.tab_no if detail.absent else ''})"
        
        ws.merge_cells('A9:B9')
        ws['A9'] = 'Должность, подразделение:'
        ws.merge_cells('C9:S9')
        ws['C9'] = f"{detail.absent.position if detail.absent else ''}, {detail.absent.department if detail.absent else ''}"
        
        ws.merge_cells('A11:B11')
        ws['A11'] = 'Замещающий работник:'
        ws.merge_cells('C11:S11')
        ws['C11'] = f"{detail.substitute.full_name if detail.substitute else ''} (ТН: {detail.substitute.tab_no if detail.substitute else ''})"
        
        ws.merge_cells('A13:B13')
        ws['A13'] = 'Должность, подразделение:'
        ws.merge_cells('C13:S13')
        ws['C13'] = f"{detail.substitute.position if detail.substitute else ''}, {detail.substitute.department if detail.substitute else ''}"
        
        ws.merge_cells('A15:B15')
        ws['A15'] = 'Период замещения:'
        ws.merge_cells('C15:S15')
        ws['C15'] = f"с {detail.start_date.strftime('%d.%m.%Y') if detail.start_date else ''} по {detail.end_date.strftime('%d.%m.%Y') if detail.end_date else ''}"
        
        ws.merge_cells('A17:B17')
        ws['A17'] = 'Доплата:'
        ws.merge_cells('C17:S17')
        ws['C17'] = f"{detail.extra_pay_percent}% от оклада отсутствующего работника"
        
        ws.merge_cells('A19:B19')
        ws['A19'] = 'Обязанности:'
        ws.merge_cells('C19:S19')
        ws['C19'] = detail.duties or 'в соответствии с должностной инструкцией'
    
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    return send_file(output, download_name=f'Заявка_В-9.10_{note.doc_number}.xlsx', as_attachment=True)


@app.route('/export/9999/<int:note_id>')
def export_9999(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    details = AllowanceDetail.query.filter_by(service_note_id=note_id).all()
    
    wb = Workbook()
    ws = wb.active
    ws.title = "СЗ"
    
    ws.merge_cells('A1:S1')
    ws['A1'] = 'Утверждена приказом предприятия'
    style_excel_cell(ws['A1'], font_bold=True, alignment='center')
    
    ws.merge_cells('A2:S2')
    ws['A2'] = 'ФОРМА № В-17.1'
    style_excel_cell(ws['A2'], font_bold=True, alignment='center')
    
    headers = ['№ п/п', 'Табельный номер', 'ФИО работника', 'Наименование надбавки/доплаты',
               'Код ВО', 'Единица измерения', 'Размер', 'Статус', 'Период с', 'Период по',
               'Ознакомлен', 'Код МВЗ', 'Заказ', 'СПП-элемент']
    
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=4, column=col, value=header)
        style_excel_cell(cell, font_bold=True, alignment='center')
    
    for idx, detail in enumerate(details, 1):
        emp = detail.employee
        row = 4 + idx
        ws.cell(row=row, column=1, value=idx)
        ws.cell(row=row, column=2, value=emp.tab_no if emp else '')
        ws.cell(row=row, column=3, value=emp.full_name if emp else '')
        ws.cell(row=row, column=4, value=detail.allowance_name or '')
        ws.cell(row=row, column=5, value=detail.payment_code or '')
        ws.cell(row=row, column=6, value=detail.unit or '')
        ws.cell(row=row, column=7, value=detail.amount)
        ws.cell(row=row, column=8, value=detail.status or 'установление')
        ws.cell(row=row, column=9, value=detail.start_date.strftime('%d.%m.%Y') if detail.start_date else '')
        ws.cell(row=row, column=10, value=detail.end_date.strftime('%d.%m.%Y') if detail.end_date else '')
    
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    return send_file(output, download_name=f'СЗ_В-17.1_{note.doc_number}.xlsx', as_attachment=True)


# ==================== ЗАПУСК ====================

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=True, host='0.0.0.0', port=5000)