---

import os
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, load_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
TEMPLATE_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'excel_templates')

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

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)

def format_date(date_obj):
    return date_obj.strftime('%d.%m.%Y') if date_obj else ''

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

@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 = format_date(detail.start_date)
                end_date = format_date(detail.end_date)
        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 = format_date(detail.start_date)
                end_date = format_date(detail.end_date)
        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 = format_date(detail.start_date)
                end_date = format_date(detail.end_date)
        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 = format_date(detail.start_date)
                end_date = format_date(detail.end_date)
        
        items.append({
            'id': note.id,
            'doc_number': note.doc_number or '-',
            'doc_date': format_date(note.doc_date),
            '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):
    try:
        note = ServiceNote.query.get_or_404(note_id)
        details = SupplementDetail.query.filter_by(service_note_id=note_id).all()
        
        template_path = os.path.join(TEMPLATE_DIR, 'v-9.7_rzo.xlsx')
        wb = load_workbook(template_path)
        ws = wb['СЗ']  # Явно выбираем лист
        
        # Заполняем шапку
        ws['C4'] = f"№ {note.doc_number} от {format_date(note.doc_date)}"
        
        # Данные начинаются с 24 строки
        start_row = 24
        for idx, detail in enumerate(details):
            emp = detail.employee
            row = start_row + idx
            
            ws.cell(row=row, column=1, value=idx + 1) # № п/п
            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=format_date(detail.start_date)) # Период с
            ws.cell(row=row, column=7, value=format_date(detail.end_date)) # Период по
            ws.cell(row=row, column=8, value=detail.amount) # Доплата
            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)
    except Exception as e:
        print(f"!!! ОШИБКА ЭКСПОРТА 1106: {e}")
        return f"Ошибка при формировании Excel: {e}", 500


@app.route('/export/1003/<int:note_id>')
def export_1003(note_id):
    try:
        note = ServiceNote.query.get_or_404(note_id)
        details = CombinationDetail.query.filter_by(service_note_id=note_id).all()
        
        template_path = os.path.join(TEMPLATE_DIR, 'v-9.4_comb.xlsx')
        wb = load_workbook(template_path)
        ws = wb['СЗ']
        
        # Данные начинаются с 21 строки
        start_row = 21
        for idx, detail in enumerate(details):
            emp = detail.employee
            row = start_row + idx
            
            ws.cell(row=row, column=1, value=idx + 1)
            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=format_date(detail.start_date))
            ws.cell(row=row, column=8, value=format_date(detail.end_date))
            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)
    except Exception as e:
        print(f"!!! ОШИБКА ЭКСПОРТА 1003: {e}")
        return f"Ошибка при формировании Excel: {e}", 500


@app.route('/export/1019/<int:note_id>')
def export_1019(note_id):
    try:
        note = ServiceNote.query.get_or_404(note_id)
        detail = SubstitutionDetail.query.filter_by(service_note_id=note_id).first()
        
        template_path = os.path.join(TEMPLATE_DIR, 'v-9.10_subst.xlsx')
        wb = load_workbook(template_path)
        ws = wb['Заявка'] # В этом файле лист называется "Заявка"
        
        if detail:
            # Заполняем конкретные ячейки согласно шаблону В-9.10 (координаты из вашего примера)
            ws['C4'] = f"Заявка № {note.doc_number}"
            ws['H4'] = format_date(note.doc_date)
            ws['C6'] = detail.absence_reason or ''
            
            # Отсутствующий работник
            if detail.absent:
                ws['A8'] = detail.absent.full_name
                ws['I8'] = detail.absent.tab_no
                ws['A10'] = f"{detail.absent.position}, {detail.absent.department}"
                
            # Замещающий работник
            if detail.substitute:
                ws['A14'] = detail.substitute.full_name
                ws['I14'] = detail.substitute.tab_no
                ws['A16'] = f"{detail.substitute.position}, {detail.substitute.department}"
                
            ws['B18'] = format_date(detail.start_date)
            ws['G18'] = format_date(detail.end_date)
            ws['G29'] = detail.extra_pay_percent

        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(output, download_name=f'Заявка_В-9.10_{note.doc_number}.xlsx', as_attachment=True)
    except Exception as e:
        print(f"!!! ОШИБКА ЭКСПОРТА 1019: {e}")
        return f"Ошибка при формировании Excel: {e}", 500


@app.route('/export/9999/<int:note_id>')
def export_9999(note_id):
    try:
        note = ServiceNote.query.get_or_404(note_id)
        details = AllowanceDetail.query.filter_by(service_note_id=note_id).all()
        
        template_path = os.path.join(TEMPLATE_DIR, 'v-17.1_allow.xlsx')
        wb = load_workbook(template_path)
        ws = wb['СЗ']
        
        # Данные начинаются с 21 строки
        start_row = 21
        for idx, detail in enumerate(details):
            emp = detail.employee
            row = start_row + idx
            
            ws.cell(row=row, column=1, value=idx + 1)
            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=format_date(detail.start_date))
            ws.cell(row=row, column=10, value=format_date(detail.end_date))

        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(output, download_name=f'СЗ_В-17.1_{note.doc_number}.xlsx', as_attachment=True)
    except Exception as e:
        print(f"!!! ОШИБКА ЭКСПОРТА 9999: {e}")
        return f"Ошибка при формировании Excel: {e}", 500


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

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