---

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.worksheet.datavalidation import DataValidation

# Инициализация книги Excel
wb = openpyxl.Workbook()

# Определение стилей
bold_font = Font(bold=True)
gray_fill = PatternFill(start_color="E0E0E0", end_color="E0E0E0", fill_type="solid")
yellow_fill = PatternFill(start_color="FFFF99", end_color="FFFF99", fill_type="solid")
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")

# === ЛИСТ 1: "Данные" ===
ws1 = wb.active
ws1.title = "Данные"
ws1["A1"] = "Исходные данные коммерческих предложений"
ws1["A1"].font = Font(bold=True, size=12)

ws1.cell(row=3, column=1, value="Параметр").font = bold_font
ws1.cell(row=3, column=1).fill = gray_fill
for col_idx, name in enumerate(["Контрагент 1", "Контрагент 2", "Контрагент 3"], 2):
    c = ws1.cell(row=3, column=col_idx, value=name)
    c.font = bold_font
    c.fill = gray_fill

# Тестовые данные с новыми условиями оплаты (например, 70% и 30%)
data_rows = [
    (4, "Цена, руб. (без НДС)", [1000000, 1100000, 950000], "#,##0.00"),
    (5, "Сумма НДС, руб.", [200000, 220000, 190000], "#,##0.00"),
    (7, "Цена с НДС, руб.", ["=B4+B5", "=C4+C5", "=D4+D5"], "#,##0.00"),
    (8, "Количество единиц", [10, 10, 10], "#,##0"),
    (9, "Цена за ед. с НДС, руб.", ["=B7/B8", "=C7/C8", "=D7/D8"], "#,##0.00"),
    (10, "Способ оплаты", ["Предоплата 100%", "Аванс 70%", "Постоплата 70%"], None),
    (11, "Размер аванса, %", [100, 70, 30], "#,##0.0"),
    (12, "Срок выполнения, дней", [30, 45, 60], "#,##0"),
    (13, "Срок оплаты аванса, дней", [0, 5, 0], "#,##0"),
]

for row_num, label, values, fmt in data_rows:
    ws1.cell(row=row_num, column=1, value=label).font = bold_font
    for col_idx, val in enumerate(values, 2):
        cell = ws1.cell(row=row_num, column=col_idx, value=val)
        if fmt:
            cell.number_format = fmt
        if row_num == 10:
            cell.fill = yellow_fill

# Выпадающий список со всеми вариантами оплаты
payment_options = "Предоплата 100%,Аванс 70%,Аванс 50%,Аванс 30%,Постоплата 70%,Постоплата 30%,Постоплата 100%"
dv = DataValidation(
    type="list", 
    formula1=f'"{payment_options}"', 
    allow_blank=True
)
ws1.add_data_validation(dv)
dv.add("B10:D10")

ws1.column_dimensions["A"].width = 42
for col in ["B", "C", "D", "E", "F", "G"]: 
    ws1.column_dimensions[col].width = 18

# === ЛИСТ 2: "Расчёт PVP" ===
ws2 = wb.create_sheet("Расчёт PVP")
ws2["A1"] = "Ставка дисконтирования, % годовых"
ws2["A1"].font = bold_font
ws2["B1"] = 15
ws2["B1"].fill = yellow_fill
ws2["B1"].number_format = "0.0"

for col, h in enumerate(["Параметр", "Контрагент 1", "Контрагент 2", "Контрагент 3"], 1):
    c = ws2.cell(row=3, column=col, value=h)
    c.font = bold_font
    c.fill = gray_fill

links = {
    4: ("Цена с НДС, руб.", "7", "#,##0.00"), 
    5: ("Способ оплаты", "10", None),
    6: ("Срок выполнения, дней", "12", "#,##0"), 
    7: ("Срок оплаты аванса, дней", "13", "#,##0"),
    8: ("Размер аванса, %", "11", "#,##0.0")
}

for r, (label, src, fmt) in links.items():
    ws2.cell(row=r, column=1, value=label).font = bold_font
    for col in ["B", "C", "D"]:
        c = ws2[f"{col}{r}"]
        c.value = f"=Данные!{col}{src}"
        if fmt: 
            c.number_format = fmt

ws2["A10"] = "ПОТОКИ ПЛАТЕЖЕЙ И ПРИВЕДЁННАЯ ЦЕНА (PVP)"
ws2["A10"].font = Font(color="FF0000", bold=True, size=12)

payment_rows = {
    11: "Платёж 1 — размер, руб.", 
    12: "Платёж 1 — срок, дней",
    13: "Платёж 1 — PVP, руб.", 
    14: "Платёж 2 — размер, руб.",
    15: "Платёж 2 — срок, дней", 
    16: "Платёж 2 — PVP, руб.",
    17: "ИТОГО PVP, руб."
}

for r, label in payment_rows.items():
    ws2.cell(row=r, column=1, value=label).font = bold_font if r == 17 else Font()

# Универсальные формулы для любого % аванса
for col in ["B", "C", "D"]:
    # Платёж 1 (Авансовая часть / Предоплата)
    ws2[f"{col}11"] = f'=IF({col}5="Предоплата 100%",{col}4,IF({col}5="Постоплата 100%",0,{col}4*{col}8/100))'
    ws2[f"{col}12"] = f'=IF({col}5="Предоплата 100%",0,IF({col}5="Постоплата 100%",0,{col}7))'
    ws2[f"{col}13"] = f"={col}11/(1+{col}$1/100)^({col}12/365)"
    
    # Платёж 2 (Остаток / Постоплата)
    ws2[f"{col}14"] = f'=IF({col}5="Предоплата 100%",0,IF({col}5="Постоплата 100%",{col}4,{col}4*(1-{col}8/100)))'
    ws2[f"{col}15"] = f"={col}6"
    ws2[f"{col}16"] = f"={col}14/(1+{col}$1/100)^({col}15/365)"
    
    # Итого PVP
    ws2[f"{col}17"] = f"={col}13+{col}16"
    
    for r in (11, 13, 14, 16, 17): 
        ws2[f"{col}{r}"].number_format = "#,##0.00"
    for r in (12, 15): 
        ws2[f"{col}{r}"].number_format = "#,##0"
    ws2[f"{col}17"].font = bold_font

ws2.column_dimensions["A"].width = 38
for col in ["B", "C", "D"]: 
    ws2.column_dimensions[col].width = 18

# === ЛИСТ 3: "Сравнение" ===
ws3 = wb.create_sheet("Сравнение")
ws3.merge_cells("A1:K1")
ws3["A1"] = "СВОДНАЯ ТАБЛИЦА СРАВНЕНИЯ КП"
ws3["A1"].font = Font(bold=True, color="FF0000", size=14)
ws3.row_dimensions[1].height = 22

headers = [
    "№", "Контрагент", "Цена, руб.", "Цена с НДС", "Цена за ед.",
    "PVP, руб.", "PVP за ед.", "Срок, дней", "Способ оплаты", 
    "Δ к мин. цене, %", "Δ к мин. PVP, %"
]

for i, h in enumerate(headers, 1):
    c = ws3.cell(row=3, column=i, value=h)
    c.font = bold_font
    c.fill = gray_fill
    c.alignment = Alignment(horizontal="center", wrap_text=True)

for r, sc in zip([4, 5, 6], ["B", "C", "D"]):
    ws3.cell(row=r, column=1, value=r - 3)
    ws3[f"B{r}"] = f"=Данные!{sc}4"
    ws3[f"C{r}"] = f"=Данные!{sc}5"
    ws3[f"D{r}"] = f"=Данные!{sc}7"
    ws3[f"E{r}"] = f"=Данные!{sc}9"
    ws3[f"F{r}"] = f"='Расчёт PVP'!{sc}17"
    ws3[f"G{r}"] = f"=F{r}/Данные!{sc}8"
    ws3[f"H{r}"] = f"=Данные!{sc}12"
    ws3[f"I{r}"] = f"=Данные!{sc}10"
    ws3[f"J{r}"] = f"=(C{r}-MIN(C4:C6))/MIN(C4:C6)*100"
    ws3[f"K{r}"] = f"=(F{r}-MIN(F4:F6))/MIN(F4:F6)*100"
    
    for col in ["C", "D", "E", "F", "G"]: 
        ws3[f"{col}{r}"].number_format = "#,##0.00"
    ws3[f"H{r}"].number_format = "#,##0"
    ws3[f"J{r}"].number_format = "0.0"
    ws3[f"K{r}"].number_format = "0.0"

ws3["A8"] = "МИН. ЦЕНА"
ws3["A8"].font = bold_font
ws3["B8"] = "=MIN(C4:C6)"
ws3["C8"] = "=MIN(E4:E6)"
ws3["B8"].number_format = "#,##0.00"
ws3["C8"].number_format = "#,##0.00"
ws3["B8"].fill = green_fill
ws3["C8"].fill = green_fill

ws3["A9"] = "МИН. PVP"
ws3["A9"].font = bold_font
ws3["B9"] = "=MIN(F4:F6)"
ws3["C9"] = "=MIN(G4:G6)"
ws3["B9"].number_format = "#,##0.00"
ws3["C9"].number_format = "#,##0.00"
ws3["B9"].fill = green_fill
ws3["C9"].fill = green_fill

ws3["A10"] = "РЕКОМЕНДАЦИЯ"
ws3["A10"].font = bold_font
ws3.merge_cells("B10:K10")
ws3["B10"] = '="Контрагент "&MATCH(MIN(F4:F6),F4:F6,0)'
ws3["B10"].font = Font(bold=True, color="006100")
ws3["B10"].fill = green_fill

column_widths = {
    "A": 6, "B": 35, "C": 16, "D": 16, "E": 14, 
    "F": 16, "G": 14, "H": 12, "I": 20, "J": 16, "K": 16
}
for col, w in column_widths.items():
    ws3.column_dimensions[col].width = w

output_filename = "kp_comparison.xlsx"
wb.save(output_filename)
print(f"Файл успешно сохранен как: {output_filename}")