---

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 openpyxl.utils import range_boundaries
from openpyxl.cell.cell import MergedCell
from copy import copy
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 safe_write(ws, row, col, value):
    """Безопасная запись в ячейку. Если ячейка объединена, пишет в верхнюю левую ячейку диапазона."""
    cell = ws.cell(row=row, column=col)
    if isinstance(cell, MergedCell):
        for merged_range in ws.merged_cells.ranges:
            if cell.coordinate in merged_range:
                min_col, min_row, _, _ = range_boundaries(merged_range.coord)
                ws.cell(row=min_row, column=min_col).value = value
                return
    else:
        cell.value = value

def copy_cell_style(source_cell, target_cell):
    """Копирует стиль из одной ячейки в другую."""
    if source_cell.has_style:
        target_cell.font = copy(source_cell.font)
        target_cell.border = copy(source_cell.border)
        target_cell.fill = copy(source_cell.fill)
        target_cell.number_format = copy(source_cell.number_format)
        target_cell.protection = copy(source_cell.protection)
        target_cell.alignment = copy(source_cell.alignment)

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')
        
        # ИСПРАВЛЕНИЕ: Более надежный поиск кода ВО в форме
        detail_payment_code = request.form.get('allowance_payment_code') or request.form.get('payment_code') or ''
        
        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=detail_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['СЗ']
        
        safe_write(ws, 4, 3, f"№ {note.doc_number} от {format_date(note.doc_date)}")
        
        start_row = 22
        
        for idx, detail in enumerate(details):
            emp = detail.employee
            row = start_row + idx
            seq = idx + 1
            
            safe_write(ws, row, 1, seq)
            safe_write(ws, row, 2, emp.tab_no if emp else '')
            safe_write(ws, row, 3, emp.full_name if emp else '')
            safe_write(ws, row, 4, emp.department if emp else '')
            safe_write(ws, row, 5, emp.position if emp else '')
            safe_write(ws, row, 6, format_date(detail.start_date))
            safe_write(ws, row, 7, format_date(detail.end_date))
            safe_write(ws, row, 8, detail.amount)
            safe_write(ws, row, 9, note.payment_code)
            safe_write(ws, row, 10, detail.work_content or '')
            safe_write(ws, row, 11, detail.work_type or '')
            
            if idx > 0:
                for col in range(1, 12):
                    copy_cell_style(ws.cell(row=start_row, column=col), ws.cell(row=row, column=col))

        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['СЗ']
        
        safe_write(ws, 4, 5, note.doc_number or '') # E4
        safe_write(ws, 4, 6, format_date(note.doc_date)) # F4
        
        start_row = 20
        
        for idx, detail in enumerate(details):
            emp = detail.employee
            row = start_row + idx
            seq = idx + 1
            
            safe_write(ws, row, 1, seq)
            safe_write(ws, row, 2, emp.tab_no if emp else '')
            safe_write(ws, row, 3, emp.full_name if emp else '')
            safe_write(ws, row, 4, detail.combined_position or '')
            safe_write(ws, row, 5, detail.position_identifier or '')
            safe_write(ws, row, 6, detail.department or '')
            safe_write(ws, row, 7, format_date(detail.start_date))
            safe_write(ws, row, 8, format_date(detail.end_date))
            safe_write(ws, row, 9, f"{detail.extra_pay}%" if detail.extra_pay else '')
            safe_write(ws, row, 10, detail.payment_code or '')
            safe_write(ws, row, 14, detail.reason or '')
            safe_write(ws, row, 15, detail.duties or '')
            
            if idx > 0:
                for col in range(1, 17):
                    copy_cell_style(ws.cell(row=start_row, column=col), ws.cell(row=row, column=col))

        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['Заявка']
        
        safe_write(ws, 3, 15, note.doc_number or '') # O3
        safe_write(ws, 3, 16, format_date(note.doc_date)) # P3
        
        if detail:
            safe_write(ws, 4, 3, f"Заявка № {note.doc_number}") # C4
            safe_write(ws, 4, 8, format_date(note.doc_date)) # H4
            safe_write(ws, 6, 3, detail.absence_reason or '') # C6
            
            if detail.absent:
                safe_write(ws, 8, 1, detail.absent.full_name)
                safe_write(ws, 8, 9, detail.absent.tab_no)
                safe_write(ws, 10, 1, f"{detail.absent.position}, {detail.absent.department}")
                
            if detail.substitute:
                safe_write(ws, 14, 1, detail.substitute.full_name)
                safe_write(ws, 14, 9, detail.substitute.tab_no)
                safe_write(ws, 16, 1, f"{detail.substitute.position}, {detail.substitute.department}")
                
            safe_write(ws, 18, 2, format_date(detail.start_date))
            safe_write(ws, 18, 7, format_date(detail.end_date))

        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['СЗ']
        
        safe_write(ws, 2, 2, note.doc_number or '') # B2
        safe_write(ws, 2, 3, format_date(note.doc_date)) # C2
        
        start_row = 21
        
        for idx, detail in enumerate(details):
            emp = detail.employee
            row = start_row + idx
            seq = idx + 1
            
            safe_write(ws, row, 1, seq)
            safe_write(ws, row, 2, emp.tab_no if emp else '')
            safe_write(ws, row, 3, emp.full_name if emp else '')
            safe_write(ws, row, 4, detail.allowance_name or '')
            
            # ИСПРАВЛЕНИЕ: Берем код ВО строго из детали. Никаких fallback на 9999.
            safe_write(ws, row, 5, detail.payment_code or '') 
            
            safe_write(ws, row, 6, detail.unit or '')
            safe_write(ws, row, 7, detail.amount)
            safe_write(ws, row, 8, detail.status or 'установить')
            safe_write(ws, row, 9, format_date(detail.start_date))
            safe_write(ws, row, 10, format_date(detail.end_date))
            
            if idx > 0:
                for col in range(1, 11):
                    copy_cell_style(ws.cell(row=start_row, column=col), ws.cell(row=row, column=col))

        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)