---

from flask import Flask, render_template, request, send_file
import psycopg2
import traceback
import openpyxl
from io import BytesIO
import os

app = Flask(__name__)

# -------------------- Настройки подключения --------------------
DB_HOST = "172.16.2.38"
DB_PORT = 5432
DB_NAME = "dev_intern"          # !!! замените на имя вашей БД
DB_USER = "dev_intern"
DB_PASSWORD = "Cneltynbr33"

# Путь к Excel-шаблону (положите файл BLANKS6.xlsx в корень проекта)
EXCEL_TEMPLATE = "BLANKS6.xlsx"

def get_db_connection():
    """Создаёт и возвращает соединение с PostgreSQL."""
    return psycopg2.connect(
        host=DB_HOST,
        port=DB_PORT,
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )

# -------------------- Главная страница --------------------
@app.route("/")
def index():
    search_tabnum = request.args.get("tabnum", "").strip()
    search_fam = request.args.get("fam", "").strip()

    try:
        conn = get_db_connection()
        cur = conn.cursor()
        query = "SELECT * FROM rabotnik WHERE 1=1"
        params = []
        if search_tabnum:
            query += " AND Tabnum = %s"
            params.append(search_tabnum)
        if search_fam:
            query += " AND fam ILIKE %s"
            params.append(f"%{search_fam}%")
        query += " ORDER BY fam, imja"

        cur.execute(query, params)
        rows = cur.fetchall()
        columns = [desc[0] for desc in cur.description]
        cur.close()
        conn.close()

        return render_template("index.html",
                               rows=rows, columns=columns,
                               search_tabnum=search_tabnum,
                               search_fam=search_fam)
    except Exception as e:
        return f"<h2>Ошибка загрузки</h2><pre>{traceback.format_exc()}</pre>"

# -------------------- Карточка работника + его дети --------------------
@app.route("/employee/<tabnum>")
def employee_detail(tabnum):
    try:
        conn = get_db_connection()
        cur = conn.cursor()
        # Данные работника
        cur.execute("""
            SELECT fam, imja, otch, Tabnum, nam_dol, nam_podr
            FROM rabotnik WHERE Tabnum = %s
        """, (tabnum,))
        worker = cur.fetchone()
        if not worker:
            return "<h2>Работник не найден</h2><a href='/'>Назад</a>"

        # Дети (таблица deti)
        cur.execute("""
            SELECT id, tabnum, step_rod, fam_rod, imja_rod, otch_rod, pol_rod, drog_rod
            FROM deti
            WHERE tabnum = %s
            ORDER BY id
        """, (tabnum,))
        children_rows = cur.fetchall()
        children_cols = [desc[0] for desc in cur.description]
        cur.close()
        conn.close()

        return render_template("employee.html",
                               worker=worker,
                               children_rows=children_rows,
                               children_cols=children_cols)
    except Exception as e:
        return f"<h2>Ошибка загрузки данных</h2><pre>{traceback.format_exc()}</pre>"

# -------------------- Экспорт в Excel --------------------
@app.route("/employee/<tabnum>/export")
def export_excel(tabnum):
    # 1. Получаем ФИО работника
    try:
        conn = get_db_connection()
        cur = conn.cursor()
        cur.execute("SELECT fam, imja, otch FROM rabotnik WHERE Tabnum = %s", (tabnum,))
        row = cur.fetchone()
        cur.close()
        conn.close()
        if not row:
            return "Работник не найден", 404
        fam, imja, otch = row
    except Exception as e:
        return f"Ошибка получения данных: {e}", 500

    # 2. Проверяем наличие шаблона
    if not os.path.exists(EXCEL_TEMPLATE):
        return f"Файл шаблона {EXCEL_TEMPLATE} не найден на сервере", 500

    # 3. Открываем шаблон, заполняем ячейки
    try:
        wb = openpyxl.load_workbook(EXCEL_TEMPLATE)
        ws = wb.active

        # Заполняем ФИО в строках 15, 16, 17 (диапазоны D-DQ)
        ws['D15'] = fam
        ws['D16'] = imja
        ws['D17'] = otch

        # Сохраняем в память
        output = BytesIO()
        wb.save(output)
        output.seek(0)
    except Exception as e:
        return f"Ошибка обработки Excel: {e}", 500

    # 4. Отправляем файл на скачивание
    filename = f"BLANKS6_{tabnum}.xlsx"
    return send_file(
        output,
        as_attachment=True,
        download_name=filename,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )

# -------------------- Запуск --------------------
if __name__ == "__main__":
    app.run(debug=True, host="0.0.0.0", port=5000)