import openpyxl, os
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter

input_file = [f for f in os.listdir('.') if '26-9' in f and f.endswith('.xlsx')][0]
print(f"Reading {input_file}")
wb = openpyxl.load_workbook(input_file)
sheet_name = 'FILE GỐC' if 'FILE GỐC' in wb.sheetnames else wb.sheetnames[0]

# Xoa cac sheet phu
for sn in wb.sheetnames:
    if sn != sheet_name:
        del wb[sn]
        print(f"Deleted sheet: {sn}")

ws = wb[sheet_name]

# Find header row
header_row = 4
for r in range(1, 10):
    val = ws.cell(r, 2).value
    if val and "Mã khách hàng" in str(val):
        header_row = r
        break

print(f"Header row is {header_row}")

# Them (Khối lượng Kg) vao cac cot Thang 01..08, cot cuoi "Từ 1->26 Tháng 09 2026"
for c in range(4, 20):
    val = str(ws.cell(header_row, c).value or "").strip()
    if not val or val == "None":
        continue
    if "Tháng" in val and "09" not in val and "9" not in val and "26" not in val[0:2]: # avoid matching the '26' in 26/09
        ws.cell(header_row, c).value = f"{val} (Khối lượng Kg)"
    elif "09" in val or "9" in val:
        ws.cell(header_row, c).value = "Từ 1->26 Tháng 09 2026 (Khối lượng Kg)"

# Giu nguyen du lieu + dong "Tổng cộng", so nguyen, do rong cot
header_font = Font(name='Times New Roman', size=10, bold=True)
data_font = Font(name='Times New Roman', size=10)

for c in range(1, 14):
    cell = ws.cell(header_row, c)
    cell.font = header_font
    cell.alignment = Alignment(horizontal='center', wrap_text=True)

for r in range(header_row + 1, ws.max_row + 1):
    for c in range(1, 14):
        cell = ws.cell(r, c)
        cell.font = data_font
        if c >= 4:
            cell.alignment = Alignment(horizontal='right')
            if cell.value is not None:
                try:
                    cell.value = int(float(str(cell.value).replace(',', '')))
                except Exception as e:
                    pass

col_widths = [5, 14, 50, 14, 14, 14, 14, 14, 14, 14, 14, 18]
for ci, w in enumerate(col_widths, 1):
    ws.column_dimensions[get_column_letter(ci)].width = w

os.makedirs('File-lam-sach', exist_ok=True)
out_file = 'File-lam-sach/MUA_HANG_26T9-26_DA-LAM-SACH.xlsx'
wb.save(out_file)
print(f"Saved to {out_file}")

with open('NHAT_KY_agy_T9_26.md', 'a', encoding='utf-8') as f:
    f.write(f"- Bước 1: Đã làm sạch file {input_file}, xoá sheet phụ, sửa header, format int. Đã lưu 1 file {out_file}.\n")