---

from flask import Flask, render_template, request, send_file
import psycopg2
import traceback
import os
import tempfile
import xlrd
import xlwt
from xlutils.copy import copy as xl_copy
from openpyxl import load_workbook

app = Flask(__name__)

# -------------------- Настройки БД --------------------
DB_HOST = "172.16.2.38"
DB_PORT = 5432
DB_NAME = "dev_intern"
DB_USER = "dev_intern"
DB_PASSWORD = "Cneltynbr33"

TEMPLATE_BLANK6 = "BLANKS6.XLS"
TEMPLATE_BLANK5 = "BLANK5_1.xlsx"

def get_db_connection():
    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 Tabnum, fam, imja, otch, nam_dol 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>/children")
def select_children(tabnum):
    try:
        conn = get_db_connection()
        cur = conn.cursor()
        cur.execute("SELECT fam, imja, otch, Tabnum, nam_dol FROM rabotnik WHERE Tabnum = %s", (tabnum,))
        worker = cur.fetchone()
        if not worker:
            return f"<h2>Работник с таб. № {tabnum} не найден</h2><a href='/'>Назад</a>"
        cur.execute("SELECT id, fam_rod, imja_rod, otch_rod, drog_rod, step_rod, pol_rod FROM deti WHERE tabnum = %s ORDER BY id", (tabnum,))
        children = cur.fetchall()
        cur.close()
        conn.close()
        return render_template("employee.html", worker=worker, children=children)
    except Exception as e:
        return f"<h2>Ошибка загрузки детей</h2><pre>{traceback.format_exc()}</pre>"

# -------------------- Единый экспорт --------------------
@app.route("/employee/<tabnum>/export")
def export_data(tabnum):
    form_type = request.args.get("form")        # 'blank6' или 'blank5'
    child_id = request.args.get("child_id")     # может быть None

    try:
        conn = get_db_connection()
        cur = conn.cursor()
        # Получаем данные работника
        cur.execute("SELECT fam, imja, otch FROM rabotnik WHERE Tabnum = %s", (tabnum,))
        worker = cur.fetchone()
        if not worker:
            cur.close()
            conn.close()
            return "Работник не найден"

        # Если выбран BLANK5 – нужен ребёнок
        if form_type == "blank5":
            if not child_id:
                cur.close()
                conn.close()
                return "Для формы BLANK5_1 необходимо выбрать ребёнка"
            cur.execute("SELECT fam_rod, imja_rod, otch_rod FROM deti WHERE id = %s AND tabnum = %s", (child_id, tabnum))
            child = cur.fetchone()
            if not child:
                cur.close()
                conn.close()
                return "Выбранный ребёнок не найден"
        else:
            child = None

        cur.close()
        conn.close()

        # -------------------- Экспорт в BLANKS6.XLS --------------------
        if form_type == "blank6":
            if not os.path.exists(TEMPLATE_BLANK6):
                return f"Файл шаблона {TEMPLATE_BLANK6} не найден."

            rb = xlrd.open_workbook(TEMPLATE_BLANK6, formatting_info=True)
            wb = xl_copy(rb)
            ws = wb.get_sheet(0)

            # Стиль
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.name = 'Times New Roman'
            font.height = 200
            style.font = font
            alignment = xlwt.Alignment()
            alignment.horz = xlwt.Alignment.HORZ_CENTER
            alignment.vert = xlwt.Alignment.VERT_CENTER
            style.alignment = alignment
            borders = xlwt.Borders()
            borders.left = xlwt.Borders.THIN
            borders.right = xlwt.Borders.THIN
            borders.top = xlwt.Borders.THIN
            borders.bottom = xlwt.Borders.THIN
            style.borders = borders

            rows_fio = [14, 15, 16]   # строки 15,16,17
            start_col = 3             # колонка D
            step = 3                  # группа из 3 столбцов
            fam, imja, otch = worker

            for i, ch in enumerate(fam.upper()):
                col = start_col + i * step
                if col < 256:
                    ws.write(rows_fio[0], col, ch, style)
            for i, ch in enumerate(imja.upper()):
                col = start_col + i * step
                if col < 256:
                    ws.write(rows_fio[1], col, ch, style)
            for i, ch in enumerate(otch.upper()):
                col = start_col + i * step
                if col < 256:
                    ws.write(rows_fio[2], col, ch, style)

            fd, tmp = tempfile.mkstemp(suffix=".xls")
            os.close(fd)
            wb.save(tmp)
            return send_file(tmp, as_attachment=True,
                             download_name=f"BLANKS6_{tabnum}.xls",
                             mimetype="application/vnd.ms-excel")

        # -------------------- Экспорт в BLANK5_1.xlsx (работник + ребёнок) --------------------
        elif form_type == "blank5":
            if not os.path.exists(TEMPLATE_BLANK5):
                return f"Файл шаблона {TEMPLATE_BLANK5} не найден."

            wb = load_workbook(TEMPLATE_BLANK5)
            ws = wb.active

            fam, imja, otch = worker
            fam_rod, imja_rod, otch_rod = child

            # Работник: строки 16,17,18, стартовая колонка F (индекс 6), шаг 5
            rows_worker = [16, 17, 18]
            start_col_worker = 6   # F
            step = 5
            for i, ch in enumerate(fam.upper()):
                col = start_col_worker + i * step
                ws.cell(row=rows_worker[0], column=col, value=ch)
            for i, ch in enumerate(imja.upper()):
                col = start_col_worker + i * step
                ws.cell(row=rows_worker[1], column=col, value=ch)
            for i, ch in enumerate(otch.upper()):
                col = start_col_worker + i * step
                ws.cell(row=rows_worker[2], column=col, value=ch)

            # Ребёнок: строки 33,36,37, стартовая колонка A (индекс 1), шаг 5
            rows_child = [33, 36, 37]
            start_col_child = 1    # A
            for i, ch in enumerate(fam_rod.upper()):
                col = start_col_child + i * step
                ws.cell(row=rows_child[0], column=col, value=ch)
            for i, ch in enumerate(imja_rod.upper()):
                col = start_col_child + i * step
                ws.cell(row=rows_child[1], column=col, value=ch)
            for i, ch in enumerate(otch_rod.upper()):
                col = start_col_child + i * step
                ws.cell(row=rows_child[2], column=col, value=ch)

            fd, tmp = tempfile.mkstemp(suffix=".xlsx")
            os.close(fd)
            wb.save(tmp)
            return send_file(tmp, as_attachment=True,
                             download_name=f"BLANK5_1_{tabnum}.xlsx",
                             mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

        else:
            return "Неверный тип формы"

    except Exception as e:
        return f"<h2>Ошибка экспорта</h2><pre>{traceback.format_exc()}</pre>"

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