# -*- coding: utf-8 -*-
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, numbers
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.page import PageMargins

wb = Workbook()
ws = wb.active
ws.title = "IQC Report"

# ── Styles ──────────────────────────────────────────────
thin = Side(style='thin')
border_all = Border(left=thin, right=thin, top=thin, bottom=thin)
border_bottom = Border(bottom=thin)

font_title = Font(name='Calibri', size=14, bold=True)
font_section = Font(name='Calibri', size=12, bold=True)
font_header = Font(name='Calibri', size=10, bold=True)
font_data = Font(name='Calibri', size=10)
font_small = Font(name='Calibri', size=9)
font_company = Font(name='Calibri', size=12, bold=True)
font_italic = Font(name='Calibri', size=9, italic=True)

align_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
align_left = Alignment(horizontal='left', vertical='center', wrap_text=True)
align_right = Alignment(horizontal='right', vertical='center', wrap_text=True)

fill_section = PatternFill(start_color='B4C6E7', end_color='B4C6E7', fill_type='solid')  # light blue
fill_header = PatternFill(start_color='D6DCE4', end_color='D6DCE4', fill_type='solid')  # light gray
fill_light = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
fill_yellow = PatternFill(start_color='FFFFCC', end_color='FFFFCC', fill_type='solid')

# ── Column widths (A=1 .. R=18) ─────────────────────────
col_widths = {
    'A': 5,    # STT / No
    'B': 18,   # Label col 1
    'C': 20,   # Value col 1
    'D': 18,   # Label col 2
    'E': 20,   # Value col 2
    'F': 10,   # Sample 1
    'G': 10,   # Sample 2
    'H': 10,   # Sample 3
    'I': 10,   # Sample 4
    'J': 10,   # Sample 5
    'K': 10,   # Sample 6
    'L': 10,   # Sample 7
    'M': 10,   # Sample 8
    'N': 10,   # Min
    'O': 10,   # Max
    'P': 12,   # Determinant
    'Q': 5,
    'R': 5,
}
for col_letter, w in col_widths.items():
    ws.column_dimensions[col_letter].width = w


def set_border(ws, row_start, row_end, col_start, col_end):
    for r in range(row_start, row_end + 1):
        for c in range(col_start, col_end + 1):
            ws.cell(row=r, column=c).border = border_all


def merge_and_write(ws, row, col, end_row, end_col, value, font=font_data, alignment=align_center, fill=None):
    cell = ws.cell(row=row, column=col, value=value)
    cell.font = font
    cell.alignment = alignment
    if fill:
        cell.fill = fill
    if end_row > row or end_col > col:
        ws.merge_cells(start_row=row, start_column=col, end_row=end_row, end_column=end_col)


# ═══════════════════════════════════════════════════════════
# HEADER  (rows 1-4)
# ═══════════════════════════════════════════════════════════

# Logo area
merge_and_write(ws, 1, 1, 3, 2, "[LOGO]", font_header, align_center)

# Company name center
merge_and_write(ws, 1, 3, 1, 13,
    "CÔNG TY CỔ PHẦN MECSU", font_company, align_center)
merge_and_write(ws, 2, 3, 2, 13,
    "MECSU JOINT STOCK COMPANY", Font(name='Calibri', size=11, bold=True), align_center)
merge_and_write(ws, 3, 3, 3, 13,
    "Địa chỉ / Address: ................................................", font_small, align_center)

# Doc No right
merge_and_write(ws, 1, 14, 1, 16, "Mã tài liệu / Doc No:", font_header, align_right)
merge_and_write(ws, 2, 14, 2, 16, "MECSU-QC-F-___", font_data, align_right)
merge_and_write(ws, 3, 14, 3, 16, "Rev: 01", font_data, align_right)

# Main title row 4
merge_and_write(ws, 4, 1, 4, 16,
    "PHIẾU KIỂM TRA CHẤT LƯỢNG ĐẦU VÀO / INCOMING MATERIAL QUALITY INSPECTION REPORT",
    font_title, align_center, fill_section)
set_border(ws, 4, 4, 1, 16)

# Blank row 5
ws.row_dimensions[5].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 1 - PRODUCT INFORMATION (rows 6-19)
# ═══════════════════════════════════════════════════════════
r = 6
merge_and_write(ws, r, 1, r, 16,
    "1. THÔNG TIN SẢN PHẨM / PRODUCT INFORMATION", font_section, align_left, fill_section)
set_border(ws, r, r, 1, 16)

info_fields = [
    # (label_left, label_right)  — two-column layout
    ("Khách Hàng / Customer:", "Nhà Cung Cấp / Supplier Name:"),
    ("Packing No:", "Heat No:"),
    ("PO No:", "Lot No:"),
    ("Mã Hàng / Part Number:", "Tham Chiếu CO / CO Reference:"),
    ("Tên Hàng / Part Name (Mô Tả / Description):", "Tham Chiếu CQ / CQ Reference:"),
    ("Kích Thước / Size:", "Số Lượng / Quantity (Pcs):"),
    ("Vật Liệu / Material Type:", "Xuất Xứ / Origin:"),
    ("Cấp Bền / Grade:", "Ngày Kiểm Tra / Inspection Date:"),
    ("Xử Lý Bề Mặt / Surface Treatment:", "Người Kiểm Tra / Inspector:"),
    ("Ký Hiệu / Marking:", ""),
    ("Tiêu Chuẩn / Standard (Commodity):", ""),
]

for i, (left_lbl, right_lbl) in enumerate(info_fields):
    row = r + 1 + i
    ws.row_dimensions[row].height = 20
    # Left label
    merge_and_write(ws, row, 1, row, 4, left_lbl, font_header, align_left)
    # Left value
    merge_and_write(ws, row, 5, row, 8, "", font_data, align_left, fill_yellow)
    # Right label
    if right_lbl:
        merge_and_write(ws, row, 9, row, 12, right_lbl, font_header, align_left)
        merge_and_write(ws, row, 13, row, 16, "", font_data, align_left, fill_yellow)
    else:
        merge_and_write(ws, row, 9, row, 12, "", font_data, align_left)
        merge_and_write(ws, row, 13, row, 16, "", font_data, align_left)
    set_border(ws, row, row, 1, 16)

section1_end = r + 1 + len(info_fields) - 1  # row 17

# Blank row
ws.row_dimensions[section1_end + 1].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 2 - VISUAL INSPECTION (rows 20-28)
# ═══════════════════════════════════════════════════════════
r2 = section1_end + 2  # 19
merge_and_write(ws, r2, 1, r2, 16,
    "2. KIỂM TRA NGOẠI QUAN / VISUAL INSPECTION", font_section, align_left, fill_section)
set_border(ws, r2, r2, 1, 16)

# AQL info
r2a = r2 + 1
merge_and_write(ws, r2a, 1, r2a, 5, "Inspection Level (AQL): General Inspection Level GII", font_header, align_left)
merge_and_write(ws, r2a, 6, r2a, 10, "Rating / Select Level: Level 1.5", font_header, align_left)
merge_and_write(ws, r2a, 11, r2a, 16, "Số lượng mẫu / Number of checks:", font_header, align_left)
set_border(ws, r2a, r2a, 1, 16)

# Visual table header
r2h = r2a + 1
vis_headers = [
    (1, 1, "STT\nNo"),
    (2, 6, "Hạng Mục Kiểm Tra / Check Item"),
    (7, 8, "Số Mẫu\nSample Qty"),
    (9, 10, "Đạt\nYes"),
    (11, 12, "Không Đạt\nNot Achieved"),
    (13, 14, "Kết Quả\nResult"),
    (15, 16, "Mô Tả / Describe"),
]
ws.row_dimensions[r2h].height = 30
for (c1, c2, txt) in vis_headers:
    merge_and_write(ws, r2h, c1, r2h, c2, txt, font_header, align_center, fill_header)
set_border(ws, r2h, r2h, 1, 16)

# Visual rows
vis_items = [
    "Ngoại quan chung / General Appearance",
    "Ký hiệu / Marking",
    "Ren / Thread condition",
    "Độ dày lớp mạ / Plating Thickness\n(Min Spec:          Actual:          )",
    "Khuyết tật / Defects (nứt, móp, rỉ sét / crack, dent, rust)",
]
for i, item in enumerate(vis_items):
    row = r2h + 1 + i
    ws.row_dimensions[row].height = 22
    merge_and_write(ws, row, 1, row, 1, str(i+1), font_data, align_center)
    merge_and_write(ws, row, 2, row, 6, item, font_data, align_left)
    merge_and_write(ws, row, 7, row, 8, "", font_data, align_center)
    merge_and_write(ws, row, 9, row, 10, "", font_data, align_center)
    merge_and_write(ws, row, 11, row, 12, "", font_data, align_center)
    merge_and_write(ws, row, 13, row, 14, "", font_data, align_center)
    merge_and_write(ws, row, 15, row, 16, "", font_data, align_left)
    set_border(ws, row, row, 1, 16)

section2_end = r2h + len(vis_items)

# Blank
ws.row_dimensions[section2_end + 1].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 3 - DIMENSIONAL INSPECTION (rows ~30-48)
# ═══════════════════════════════════════════════════════════
r3 = section2_end + 2
merge_and_write(ws, r3, 1, r3, 16,
    "3. KIỂM TRA KÍCH THƯỚC / DIMENSIONAL INSPECTION", font_section, align_left, fill_section)
set_border(ws, r3, r3, 1, 16)

# AQL info
r3a = r3 + 1
merge_and_write(ws, r3a, 1, r3a, 5, "Inspection Level (AQL): Special Inspection Level S-II", font_header, align_left)
merge_and_write(ws, r3a, 6, r3a, 10, "Rating / Select Level: Level 1.5", font_header, align_left)
merge_and_write(ws, r3a, 11, r3a, 16, "Số lượng mẫu / Number of checks:", font_header, align_left)
set_border(ws, r3a, r3a, 1, 16)

# Dimensional table header
r3h = r3a + 1
ws.row_dimensions[r3h].height = 30
dim_headers = [
    (1, 1, "STT\nNo"),
    (2, 3, "Danh Mục\nCharacteristics"),
    (4, 4, "Thông Số\nSpec"),
    (5, 5, "Dung Sai\nTolerance"),
    (6, 6, "Mẫu 1"),
    (7, 7, "Mẫu 2"),
    (8, 8, "Mẫu 3"),
    (9, 9, "Mẫu 4"),
    (10, 10, "Mẫu 5"),
    (11, 11, "Mẫu 6"),
    (12, 12, "Mẫu 7"),
    (13, 13, "Mẫu 8"),
    (14, 14, "Min\nResult"),
    (15, 15, "Max\nResult"),
    (16, 16, "Đánh Giá\nDeterminant"),
]
for (c1, c2, txt) in dim_headers:
    merge_and_write(ws, r3h, c1, r3h, c2, txt, font_header, align_center, fill_header)
set_border(ws, r3h, r3h, 1, 16)

# Dimensional rows - pre-filled labels
dim_items = [
    "Đường kính ren / Thread Diameter (d)",
    "Bước ren / Thread Pitch (P)",
    "Chiều dài / Length (L)",
    "Đường kính đầu / Head Diameter (dk)",
    "Chiều cao đầu / Head Height (k)",
    "Chiều rộng miệng / Width Across Flats (s)",
    "Đường kính chân / Body Diameter",
    "Chiều dài ren / Thread Length (b)",
    "Đường kính ngoài / OD (vòng đệm/washer)",
    "Đường kính trong / ID (vòng đệm/washer)",
    "",
    "",
    "",
]
for i, item in enumerate(dim_items):
    row = r3h + 1 + i
    ws.row_dimensions[row].height = 20
    merge_and_write(ws, row, 1, row, 1, str(i+1) if item else "", font_data, align_center)
    merge_and_write(ws, row, 2, row, 3, item, font_data, align_left)
    for c in range(4, 17):
        ws.cell(row=row, column=c).font = font_data
        ws.cell(row=row, column=c).alignment = align_center
    set_border(ws, row, row, 1, 16)

dim_data_end = r3h + len(dim_items)

# Thread gauge row
r_gauge = dim_data_end + 1
ws.row_dimensions[r_gauge].height = 22
merge_and_write(ws, r_gauge, 1, r_gauge, 1, "", font_data, align_center)
merge_and_write(ws, r_gauge, 2, r_gauge, 3, "Kiểm tra dưỡng ren / Thread Gauge Check", font_header, align_left)
merge_and_write(ws, r_gauge, 4, r_gauge, 5, "Go:", font_header, align_center)
merge_and_write(ws, r_gauge, 6, r_gauge, 8, "", font_data, align_center, fill_yellow)
merge_and_write(ws, r_gauge, 9, r_gauge, 10, "No Go:", font_header, align_center)
merge_and_write(ws, r_gauge, 11, r_gauge, 13, "", font_data, align_center, fill_yellow)
merge_and_write(ws, r_gauge, 14, r_gauge, 16, "", font_data, align_center)
set_border(ws, r_gauge, r_gauge, 1, 16)

section3_end = r_gauge

# Blank
ws.row_dimensions[section3_end + 1].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 4 - MECHANICAL PROPERTIES (rows ~50-58)
# ═══════════════════════════════════════════════════════════
r4 = section3_end + 2
merge_and_write(ws, r4, 1, r4, 16,
    "4. TÍNH CHẤT CƠ HỌC / MECHANICAL PROPERTIES", font_section, align_left, fill_section)
set_border(ws, r4, r4, 1, 16)

# Mech header
r4h = r4 + 1
ws.row_dimensions[r4h].height = 28
mech_headers = [
    (1, 1, "STT\nNo"),
    (2, 5, "Đặc Tính / Characteristics"),
    (6, 8, "Tiêu Chuẩn / Standard"),
    (9, 11, "Thông Số / Specification"),
    (12, 14, "Kết Quả / Actual Result"),
    (15, 16, "Đánh Giá / Determinant"),
]
for (c1, c2, txt) in mech_headers:
    merge_and_write(ws, r4h, c1, r4h, c2, txt, font_header, align_center, fill_header)
set_border(ws, r4h, r4h, 1, 16)

mech_items = [
    "Độ Bền Kéo / Tensile Strength (N/mm²)",
    "Giới Hạn Chảy / Yield Strength (N/mm²)",
    "Độ Giãn Dài / Elongation (%)",
    "Độ Cứng / Hardness (HRC/HV/HB)",
    "Tải Trọng Thử / Proof Load (kN)",
    "Mô Men Xiết / Torque Test",
]
for i, item in enumerate(mech_items):
    row = r4h + 1 + i
    ws.row_dimensions[row].height = 20
    merge_and_write(ws, row, 1, row, 1, str(i+1), font_data, align_center)
    merge_and_write(ws, row, 2, row, 5, item, font_data, align_left)
    merge_and_write(ws, row, 6, row, 8, "", font_data, align_center)
    merge_and_write(ws, row, 9, row, 11, "", font_data, align_center)
    merge_and_write(ws, row, 12, row, 14, "", font_data, align_center, fill_yellow)
    merge_and_write(ws, row, 15, row, 16, "", font_data, align_center)
    set_border(ws, row, row, 1, 16)

section4_end = r4h + len(mech_items)

# Blank
ws.row_dimensions[section4_end + 1].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 5 - CHEMICAL COMPOSITION (rows ~60-65)
# ═══════════════════════════════════════════════════════════
r5 = section4_end + 2
merge_and_write(ws, r5, 1, r5, 16,
    "5. THÀNH PHẦN HÓA HỌC / CHEMICAL COMPOSITION", font_section, align_left, fill_section)
set_border(ws, r5, r5, 1, 16)

# Heat number row
r5a = r5 + 1
merge_and_write(ws, r5a, 1, r5a, 3, "Heat Number:", font_header, align_left)
merge_and_write(ws, r5a, 4, r5a, 8, "", font_data, align_left, fill_yellow)
merge_and_write(ws, r5a, 9, r5a, 11, "Vật Liệu / Material:", font_header, align_left)
merge_and_write(ws, r5a, 12, r5a, 16, "", font_data, align_left, fill_yellow)
set_border(ws, r5a, r5a, 1, 16)

# Chemical element headers
r5h = r5a + 1
ws.row_dimensions[r5h].height = 22
chem_headers = ["", "C", "Si", "Mn", "P", "S", "Ni", "Cr", "Mo", "Cu", "N", "B", "V", "Đánh Giá\nDeterminant"]
# Map to columns: col 1=label area, cols 2-13=elements, col 14-16=determinant
merge_and_write(ws, r5h, 1, r5h, 1, "", font_header, align_center, fill_header)
for i, el in enumerate(chem_headers[1:-1]):
    ws.cell(row=r5h, column=i+2, value=el).font = font_header
    ws.cell(row=r5h, column=i+2).alignment = align_center
    ws.cell(row=r5h, column=i+2).fill = fill_header
merge_and_write(ws, r5h, 14, r5h, 16, chem_headers[-1], font_header, align_center, fill_header)
set_border(ws, r5h, r5h, 1, 16)

# Spec row
r5s = r5h + 1
ws.row_dimensions[r5s].height = 20
merge_and_write(ws, r5s, 1, r5s, 1, "Spec", font_header, align_center)
for c in range(2, 14):
    ws.cell(row=r5s, column=c).font = font_data
    ws.cell(row=r5s, column=c).alignment = align_center
merge_and_write(ws, r5s, 14, r5s, 16, "", font_data, align_center)
set_border(ws, r5s, r5s, 1, 16)

# Actual row
r5r = r5s + 1
ws.row_dimensions[r5r].height = 20
merge_and_write(ws, r5r, 1, r5r, 1, "Actual", font_header, align_center)
for c in range(2, 14):
    ws.cell(row=r5r, column=c).font = font_data
    ws.cell(row=r5r, column=c).alignment = align_center
    ws.cell(row=r5r, column=c).fill = fill_yellow
merge_and_write(ws, r5r, 14, r5r, 16, "", font_data, align_center)
set_border(ws, r5r, r5r, 1, 16)

section5_end = r5r

# Blank
ws.row_dimensions[section5_end + 1].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 6 - NOTES & CONCLUSION (rows ~67-72)
# ═══════════════════════════════════════════════════════════
r6 = section5_end + 2
merge_and_write(ws, r6, 1, r6, 16,
    "6. GHI CHÚ & KẾT LUẬN / NOTES & CONCLUSION", font_section, align_left, fill_section)
set_border(ws, r6, r6, 1, 16)

# Remarks area
r6a = r6 + 1
merge_and_write(ws, r6a, 1, r6a, 2, "Ghi Chú / Remarks:", font_header, align_left)
merge_and_write(ws, r6a, 3, r6a + 1, 16, "", font_data, align_left, fill_yellow)
set_border(ws, r6a, r6a + 1, 1, 16)
ws.row_dimensions[r6a].height = 22
ws.row_dimensions[r6a + 1].height = 22
merge_and_write(ws, r6a + 1, 1, r6a + 1, 2, "", font_data, align_left)

# Note text
r6n = r6a + 2
ws.row_dimensions[r6n].height = 32
merge_and_write(ws, r6n, 1, r6n, 16,
    "Chất lượng vật liệu được chứng nhận theo CO/CQ của nhà sản xuất (nếu có). "
    "MECSU thực hiện kiểm tra kích thước, ngoại quan, lớp mạ và tính chất cơ học.\n"
    "Quality of material is certified per manufacturer's CO/CQ (if available). "
    "MECSU performs dimensional, visual, plating thickness and mechanical property inspections.",
    font_italic, Alignment(horizontal='left', vertical='center', wrap_text=True))
set_border(ws, r6n, r6n, 1, 16)

# Conclusion
r6c = r6n + 1
ws.row_dimensions[r6c].height = 28
merge_and_write(ws, r6c, 1, r6c, 3, "KẾT LUẬN / CONCLUSION:", font_header, align_left)
merge_and_write(ws, r6c, 4, r6c, 9,
    "\u2610 ĐẠT / QUALIFIED", Font(name='Calibri', size=11, bold=True), align_center)
merge_and_write(ws, r6c, 10, r6c, 16,
    "\u2610 KHÔNG ĐẠT / NOT QUALIFIED", Font(name='Calibri', size=11, bold=True, color='FF0000'), align_center)
set_border(ws, r6c, r6c, 1, 16)

section6_end = r6c

# Blank
ws.row_dimensions[section6_end + 1].height = 6

# ═══════════════════════════════════════════════════════════
# SECTION 7 - SIGNATURES (rows ~74-78)
# ═══════════════════════════════════════════════════════════
r7 = section6_end + 2
merge_and_write(ws, r7, 1, r7, 16,
    "7. XÁC NHẬN / SIGNATURES", font_section, align_left, fill_section)
set_border(ws, r7, r7, 1, 16)

# Three signature blocks
r7a = r7 + 1
ws.row_dimensions[r7a].height = 20

sig_blocks = [
    (1, 5, "Người Kiểm Tra / QC Inspector"),
    (6, 11, "Người Xác Nhận / QA Verification"),
    (12, 16, "Người Phê Duyệt / Approval"),
]
for (c1, c2, title) in sig_blocks:
    merge_and_write(ws, r7a, c1, r7a, c2, title, font_header, align_center, fill_header)

set_border(ws, r7a, r7a, 1, 16)

# Signature space
for row_off in range(1, 4):
    row = r7a + row_off
    ws.row_dimensions[row].height = 20
    for (c1, c2, _) in sig_blocks:
        merge_and_write(ws, row, c1, row, c2, "", font_data, align_center)
    set_border(ws, row, row, 1, 16)

# Date row
r7d = r7a + 4
ws.row_dimensions[r7d].height = 20
for (c1, c2, _) in sig_blocks:
    merge_and_write(ws, r7d, c1, r7d, c2, "Ngày / Date: ____/____/________", font_data, align_center)
set_border(ws, r7d, r7d, 1, 16)

# ═══════════════════════════════════════════════════════════
# Page setup - A4 Landscape
# ═══════════════════════════════════════════════════════════
ws.page_setup.orientation = 'landscape'
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_margins = PageMargins(left=0.4, right=0.4, top=0.4, bottom=0.4, header=0.2, footer=0.2)

# Print area
last_row = r7d
ws.print_area = f"A1:P{last_row}"

# ── Save ────────────────────────────────────────────────
filepath = r"d:\12\Mau_Kiem_Tra_Chat_Luong_Dau_Vao.xlsx"
wb.save(filepath)
print(f"File saved to: {filepath}")
print(f"Total rows used: {last_row}")