import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from datetime import datetime, date
import re
import os

# ============================================================
# Bước 1: Đọc file Goc và Lam Sach để hiểu mapping cột
# ============================================================
# File Goc.xlsx (tuần 26): 18 cột A-R
# Row 9: Header chính
#   A=Stt | B=Nhà máy | C=mã số màu phát triển/KH | D=Mã số màu theo NM | E=Khách hàng
#   F=Ngày nhận mẫu | G=Ngày hoàn thành | H=Loại mẫu (Mới) | I=Loại mẫu (Nhân) | J=Mẫu bộ (tên SP)
#   K=số lượng | L=Kỹ thuật làm mẫu | M=Trạng thái (Đang làm) | N=Trạng thái (Đã hoàn thành)
#   O=Trạng thái (KH đã ký) | P=Mẫu có đơn hàng | Q=Đã lưu về Cty | R=Ghi chú
# Row 10: Sub-header: H=Mới | I=Nhân | J=Mẫu bộ (tên SP) | M=Đang làm | N=Đã hoàn thành | O=KH đã ký

# File tuần 27: 19 cột A-S (thêm cột S: ghi chú mẫu hết hạn)
# Row 9: Header chính (giống Goc + thêm cột S)
# Row 10: Sub-header (giống Goc)
# Dữ liệu bắt đầu từ Row 12

# File sach (14 cột): Stt, Nhà máy, Mã PT/KH, Mã NM, Khách hàng, Ngày nhận, Ngày HT, Loại mẫu, SP, Số lượng, Kỹ thuật, Trạng thái, Có ĐH, Ghi chú

# Mapping từ file nguồn (19 cột, rows 12-124) sang file sạch (14 cột):
# A(1)  -> Stt
# B(2)  -> Nhà máy
# C(3)  -> Mã PT/KH
# D(4)  -> Mã NM
# E(5)  -> Khách hàng
# F(6)  -> Ngày nhận
# G(7)  -> Ngày HT
# H(8)  + I(9) + J(10) -> Loại mẫu (H=X→Mới, I=X→Nhân, J có value→Bộ)
# J(10) -> SP (tên bộ)
# K(11) -> Số lượng
# L(12) -> Kỹ thuật
# M(13) + N(14) + O(15) -> Trạng thái (M=X→DK, N=X→HT, O=X→ĐL)
# P(16) -> Có ĐH (X)
# R(18) -> Ghi chú

# ============================================================
# Bước 2: Đọc file tuần 27
# ============================================================
input_path = r"C:\antigra-DS\CLAW_LAM_VIEC\Bao cao - Hanh chinh\Bao cao CSKH\Mau_bao_cao_lam_mau_tuan_27_29-06_05-07_2026.xlsx"

wb_in = openpyxl.load_workbook(input_path)
ws_in = wb_in.active

print(f"File tuần 27: {ws_in.max_row} dòng, {ws_in.max_column} cột")

# ============================================================
# Bước 3: Làm sạch
# ============================================================

def parse_date(val):
    """Chuyển đổi giá trị ngày tháng về DD/MM/YYYY"""
    if val is None:
        return None
    if isinstance(val, datetime):
        return val.strftime("%d/%m/%Y")
    if isinstance(val, date):
        return val.strftime("%d/%m/%Y")
    if isinstance(val, (int, float)):
        # Có thể là serial date của Excel
        try:
            from datetime import datetime as dt
            base = dt(1899, 12, 30)
            d = base + __import__('datetime').timedelta(days=int(val))
            return d.strftime("%d/%m/%Y")
        except:
            return str(int(val))
    s = str(val).strip()
    # Đã là DD/MM/YYYY
    if re.match(r'^\d{2}/\d{2}/\d{4}$', s):
        return s
    # YYYY-MM-DD HH:MM:SS
    m = re.match(r'^(\d{4})-(\d{2})-(\d{2})', s)
    if m:
        return f"{m.group(3)}/{m.group(2)}/{m.group(1)}"
    return s

def normalize_kythuat(name):
    """Chuẩn hóa tên kỹ thuật: viết hoa đầu mỗi từ"""
    if name is None:
        return None
    s = str(name).strip()
    if not s:
        return None
    # Tách theo dấu phẩy, dấu + 
    parts = re.split(r'[,+]+', s)
    normalized_parts = []
    for part in parts:
        part = part.strip()
        if not part:
            continue
        # Xóa khoảng trắng thừa
        part = re.sub(r'\s+', ' ', part)
        # Xóa nội dung trong ngoặc đơn (ghi chú kỹ thuật)
        part_clean = re.sub(r'\(.*?\)', '', part).strip()
        if not part_clean:
            # Giữ lại nguyên nếu chỉ có ngoặc
            normalized_parts.append(part.strip())
            continue
        
        # Xóa text như "kt cắm tại nha máy làm", "kt căm tai nha máy làm"
        part_clean = re.sub(r'kt\s+c[ăa]m\s+t[aii].*$', '', part_clean, flags=re.IGNORECASE).strip()
        if not part_clean:
            continue
        
        words = part_clean.split()
        normalized_words = []
        for w in words:
            if w.upper() == w and len(w) > 1:
                # Từ viết hoa toàn bộ: giữ nguyên
                normalized_words.append(w)
            else:
                # Viết hoa chữ cái đầu
                normalized_words.append(w[0].upper() + w[1:] if len(w) > 1 else w.upper())
        normalized_parts.append(' '.join(normalized_words))
    
    if not normalized_parts:
        return None
    return ' , '.join(normalized_parts)

# Mapping các biến thể tên kỹ thuật (từ Skill.txt và dữ liệu thực tế)
KYTHUAT_MAP = {
    'trần dũng': 'Trần Dũng',
    'trần dung': 'Trần Dũng',
    'trần thanh dung': 'Trần Thanh Dũng',
    'nguyễn văn tuân': 'Nguyễn Văn Tuân',
    'nguyễn văn tuấn': 'Nguyễn Văn Tuấn',
    'nguyên hưu lợi': 'Nguyễn Hữu Lợi',
    'nguyên hữu lợi': 'Nguyễn Hữu Lợi',
    'nguyễn hưu lợi': 'Nguyễn Hữu Lợi',
    'nguyễn hữu lợi': 'Nguyễn Hữu Lợi',
    'nguyên hữu lợi ( kt cắm tại nha máy làm)': 'Nguyễn Hữu Lợi',
    'nguyễn hưu lợi ( kt căm tai nha máy làm)': 'Nguyễn Hữu Lợi',
    'nông đưc trường': 'Nông Đức Trường',
    'nông đức trường': 'Nông Đức Trường',
    'đỗ quang thuỷ': 'Đỗ Quang Thủy',
    'đỗ quang thủy': 'Đỗ Quang Thủy',
    'đõ quang thuỷ': 'Đỗ Quang Thủy',
    'nguyễn văn tùng': 'Nguyễn Văn Tùng',
    'đoàn thanh nhân': 'Đoàn Thanh Nhân',
    'dương tấn hiền': 'Dương Tấn Hiền',
    'dươg tấn hiền': 'Dương Tấn Hiền',
    'trịnh hữu hải': 'Trịnh Hữu Hải',
    'phan văn hiếu': 'Phan Văn Hiếu',
    'lê văn hoa': 'Lê Văn Hoa',
    'bùi văn tuấn': 'Bùi Văn Tuấn',
    'phạm thanh hoàn': 'Phạm Thanh Hoàn',
    'phan văn hiếu': 'Phan Văn Hiếu',
    'lê xuân tương': 'Lê Xuân Tương',
    'lê xuân tuong': 'Lê Xuân Tương',
    'lê xuân tuong': 'Lê Xuân Tương',
    'hồ tiến sĩ': 'Hồ Tiến Sĩ',
    'ngọc dương': 'Ngọc Dương',
    'đỗ quang thủy': 'Đỗ Quang Thủy',
    'trần văn thưởng': 'Trần Văn Thưởng',
    'nguyễn văn hoan': 'Nguyễn Văn Hoan',
    'hoàng phong': 'Hoàng Phong',
    'hữu nhựt': 'Hữu Nhựt',
    'xuân tương': 'Xuân Tương',
    'lê xuân tương': 'Lê Xuân Tương',
    'thanh hoàn': 'Thanh Hoàn',
    'phú vĩnh': 'Phú Vĩnh',
    'văn lịnh': 'Văn Lịnh',
    'hữu nghị': 'Hữu Nghị',
    'đông (sơn nước)': 'Đông (Sơn Nước)',
    'đông , thưởng': 'Đông , Thưởng',
    'thưởng': 'Thưởng',
}

def apply_kythuat_map(name):
    """Áp dụng mapping cho tên kỹ thuật đã chuẩn hóa"""
    if name is None:
        return None
    name_lower = name.lower().strip()
    if name_lower in KYTHUAT_MAP:
        return KYTHUAT_MAP[name_lower]
    return name

# Lấy dữ liệu từ rows 12 đến 124
data_rows = []
current_nha_may = None

for r in range(12, ws_in.max_row + 1):
    stt = ws_in.cell(r, 1).value  # A: Stt
    nha_may = ws_in.cell(r, 2).value  # B: Nhà máy
    ma_pt = ws_in.cell(r, 3).value  # C: Mã PT/KH
    ma_nm = ws_in.cell(r, 4).value  # D: Mã NM
    khach_hang = ws_in.cell(r, 5).value  # E: Khách hàng
    ngay_nhan = ws_in.cell(r, 6).value  # F: Ngày nhận
    ngay_ht = ws_in.cell(r, 7).value  # G: Ngày HT
    
    loai_moi = ws_in.cell(r, 8).value   # H: Mới
    loai_nhan = ws_in.cell(r, 9).value  # I: Nhân
    loai_bo = ws_in.cell(r, 10).value   # J: Mẫu bộ (tên SP)
    
    so_luong = ws_in.cell(r, 11).value  # K: Số lượng
    ky_thuat = ws_in.cell(r, 12).value  # L: Kỹ thuật làm mẫu
    
    trang_thai_dk = ws_in.cell(r, 13).value  # M: Đang làm
    trang_thai_ht = ws_in.cell(r, 14).value  # N: Đã hoàn thành
    trang_thai_dl = ws_in.cell(r, 15).value  # O: KH đã ký
    
    co_dh = ws_in.cell(r, 16).value  # P: Mẫu có đơn hàng
    ghi_chu = ws_in.cell(r, 18).value  # R: Ghi chú
    
    # Bỏ qua dòng trống hoàn toàn (không có dữ liệu)
    if (stt is None and nha_may is None and ma_pt is None and ma_nm is None and 
        khach_hang is None and ngay_nhan is None and ngay_ht is None and
        loai_moi is None and loai_nhan is None and loai_bo is None and
        so_luong is None and ky_thuat is None and ghi_chu is None):
        continue
    
    # Cập nhật nhà máy hiện tại
    if nha_may is not None and str(nha_may).strip():
        current_nha_may = str(nha_may).strip()
    
    # a) Gộp Loại mẫu
    loai_mau = None
    sp = None
    loai_moi_str = str(loai_moi).strip() if loai_moi is not None else ''
    loai_nhan_str = str(loai_nhan).strip() if loai_nhan is not None else ''
    loai_bo_str = str(loai_bo).strip() if loai_bo is not None else ''
    
    if loai_moi_str.upper() == 'X':
        loai_mau = 'Mới'
    elif loai_nhan_str.upper() == 'X':
        loai_mau = 'Nhân'
    elif loai_bo_str and loai_bo_str.upper() != 'X':
        loai_mau = 'Bộ'
        sp = loai_bo_str
    
    # b) Gộp Trạng thái
    trang_thai = None
    tt_dk_str = str(trang_thai_dk).strip() if trang_thai_dk is not None else ''
    tt_ht_str = str(trang_thai_ht).strip() if trang_thai_ht is not None else ''
    tt_dl_str = str(trang_thai_dl).strip() if trang_thai_dl is not None else ''
    
    if tt_dk_str.upper() == 'X':
        trang_thai = 'DK'
    elif tt_ht_str.upper() == 'X':
        trang_thai = 'HT'
    elif tt_dl_str.upper() == 'X':
        trang_thai = 'ĐL'
    
    # c) Chuẩn hóa tên kỹ thuật
    ky_thuat_clean = normalize_kythuat(ky_thuat) if ky_thuat else None
    ky_thuat_mapped = apply_kythuat_map(ky_thuat_clean) if ky_thuat_clean else ky_thuat_clean
    
    # e) Chuyển ngày tháng
    ngay_nhan_clean = parse_date(ngay_nhan)
    ngay_ht_clean = parse_date(ngay_ht)
    
    # d) Điền tên nhà máy cho dòng con
    nha_may_final = str(nha_may).strip() if nha_may is not None and str(nha_may).strip() else current_nha_may
    
    # Có ĐH
    co_dh_final = None
    if co_dh is not None and str(co_dh).strip().upper() == 'X':
        co_dh_final = 'X'
    
    # Stt
    stt_final = stt
    if stt is not None:
        try:
            stt_final = int(float(str(stt)))
        except:
            stt_final = stt
    
    # Mã PT/KH và Mã NM
    ma_pt_final = str(ma_pt).strip() if ma_pt is not None else None
    if ma_pt_final == 'None' or ma_pt_final == '':
        ma_pt_final = None
    
    ma_nm_final = str(ma_nm).strip() if ma_nm is not None else None
    if ma_nm_final == 'None' or ma_nm_final == '':
        ma_nm_final = None
    
    # Ghi chú
    ghi_chu_final = str(ghi_chu).strip() if ghi_chu is not None else None
    if ghi_chu_final == 'None' or ghi_chu_final == '':
        ghi_chu_final = None
    
    # Số lượng
    sl_final = so_luong
    if sl_final is not None and isinstance(sl_final, str):
        try:
            sl_final = int(float(sl_final.replace(',', '').strip()))
        except:
            pass
    
    data_rows.append([
        stt_final,           # Stt
        nha_may_final,       # Nhà máy
        ma_pt_final,         # Mã PT/KH
        ma_nm_final,         # Mã NM
        khach_hang,          # Khách hàng
        ngay_nhan_clean,     # Ngày nhận
        ngay_ht_clean,       # Ngày HT
        loai_mau,            # Loại mẫu
        sp,                  # SP
        sl_final,            # Số lượng
        ky_thuat_mapped,     # Kỹ thuật
        trang_thai,          # Trạng thái
        co_dh_final,         # Có ĐH
        ghi_chu_final        # Ghi chú
    ])
    
    if ky_thuat and ky_thuat_mapped:
        orig = str(ky_thuat).strip()
        mapped = str(ky_thuat_mapped).strip()
        if orig.lower() != mapped.lower():
            pass  # Will report below

print(f"\nTổng số dòng dữ liệu: {len(data_rows)}")

# ============================================================
# Thống kê
# ============================================================
loai_mau_count = {'Mới': 0, 'Nhân': 0, 'Bộ': 0, None: 0}
trang_thai_count = {'DK': 0, 'HT': 0, 'ĐL': 0, None: 0}
kythuat_changes = []

for row in data_rows:
    lm = row[7]
    tt = row[11]
    if lm in loai_mau_count:
        loai_mau_count[lm] += 1
    else:
        loai_mau_count[lm] = 1
    if tt in trang_thai_count:
        trang_thai_count[tt] += 1
    else:
        trang_thai_count[tt] = 1

# Thống kê chuẩn hóa kỹ thuật
# Đọc lại từ file gốc để so sánh
kythuat_orig_list = []
for r in range(12, ws_in.max_row + 1):
    val = ws_in.cell(r, 12).value
    if val is not None and str(val).strip():
        kythuat_orig_list.append(str(val).strip())

kythuat_norm_set = {}
for orig in kythuat_orig_list:
    norm = normalize_kythuat(orig)
    mapped = apply_kythuat_map(norm)
    if mapped and orig.strip().lower() != mapped.lower():
        kythuat_norm_set[orig.strip()] = mapped

print(f"\n=== THỐNG KÊ ===")
print(f"Tổng số dòng dữ liệu: {len(data_rows)}")
print(f"Loại mẫu - Mới: {loai_mau_count['Mới']}, Nhân: {loai_mau_count['Nhân']}, Bộ: {loai_mau_count['Bộ']}, Không rõ: {loai_mau_count[None]}")
print(f"Trạng thái - DK: {trang_thai_count['DK']}, HT: {trang_thai_count['HT']}, ĐL: {trang_thai_count['ĐL']}, Không rõ: {trang_thai_count[None]}")
print(f"\nĐã chuẩn hóa {len(kythuat_norm_set)} tên kỹ thuật:")
for orig, mapped in sorted(kythuat_norm_set.items()):
    print(f"  '{orig}' -> '{mapped}'")

# ============================================================
# Bước 4: Lưu file sạch
# ============================================================
output_dir = r"C:\antigra-DS\CLAW_LAM_VIEC\Bao cao - Hanh chinh\Bao cao lam mau\da lam sach"
os.makedirs(output_dir, exist_ok=True)

output_path = os.path.join(output_dir, "Tuan_27_29.06-05.07_DA-LAM-SACH.xlsx")

wb_out = openpyxl.Workbook()
ws_out = wb_out.active
ws_out.title = "Làm sạch"

# Header
headers = ["Stt", "Nhà máy", "Mã PT/KH", "Mã NM", "Khách hàng", "Ngày nhận", "Ngày HT", "Loại mẫu", "SP", "Số lượng", "Kỹ thuật", "Trạng thái", "Có ĐH", "Ghi chú"]

header_font = Font(bold=True, size=11)
header_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
header_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

for col_idx, h in enumerate(headers, 1):
    cell = ws_out.cell(1, col_idx, h)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment
    cell.border = thin_border

# Ghi dữ liệu
for row_idx, row_data in enumerate(data_rows, 2):
    for col_idx, val in enumerate(row_data, 1):
        cell = ws_out.cell(row_idx, col_idx, val)
        cell.border = thin_border
        if col_idx == 1:  # Stt - căn giữa
            cell.alignment = Alignment(horizontal='center')
        elif col_idx == 10:  # Số lượng
            cell.alignment = Alignment(horizontal='center')

# Độ rộng cột
col_widths = [5, 18, 18, 20, 18, 14, 14, 10, 25, 8, 30, 10, 8, 40]
for i, w in enumerate(col_widths, 1):
    ws_out.column_dimensions[chr(64 + i) if i <= 14 else f'Col{i}'].width = w

# Sử dụng letter thay vì chr
from openpyxl.utils import get_column_letter
for i, w in enumerate(col_widths, 1):
    ws_out.column_dimensions[get_column_letter(i)].width = w

wb_out.save(output_path)
print(f"\nĐã lưu file: {output_path}")
print(f"Kích thước: {os.path.getsize(output_path)} bytes")

# ============================================================
# Bước 5: Ghi file MD báo cáo
# ============================================================
report_path = os.path.join(output_dir, "Bao_cao_lam_sach_Tuan27.md")
report_content = f"""# BÁO CÁO LÀM SẠCH DỮ LIỆU - TUẦN 27

## Thông tin chung
- **File gốc**: Mau_bao_cao_lam_mau_tuan_27_29-06_05-07_2026.xlsx
- **File đã làm sạch**: Tuan_27_29.06-05.07_DA-LAM-SACH.xlsx
- **Ngày xử lý**: {datetime.now().strftime('%d/%m/%Y')}

## Thống kê dữ liệu

| Chỉ tiêu | Số lượng |
|----------|----------|
| Tổng số dòng dữ liệu | {len(data_rows)} |
| Mẫu Mới | {loai_mau_count['Mới']} |
| Mẫu Nhân | {loai_mau_count['Nhân']} |
| Mẫu Bộ | {loai_mau_count['Bộ']} |
| Không rõ loại mẫu | {loai_mau_count[None]} |
| Trạng thái DK | {trang_thai_count['DK']} |
| Trạng thái HT | {trang_thai_count['HT']} |
| Trạng thái ĐL | {trang_thai_count['ĐL']} |
| Không rõ trạng thái | {trang_thai_count[None]} |

## Chuẩn hóa tên kỹ thuật

Đã chuẩn hóa **{len(kythuat_norm_set)}** tên kỹ thuật:

| Biến thể gốc | Tên chuẩn hóa |
|--------------|---------------|
"""

for orig, mapped in sorted(kythuat_norm_set.items()):
    report_content += f"| `{orig}` | `{mapped}` |\n"

report_content += """
## Các thao tác đã thực hiện

1. **Gộp cột Loại mẫu**: Từ cột H (Mới), I (Nhân), J (Mẫu bộ) thành 1 cột
   - H có X → Mới
   - I có X → Nhân  
   - J có tên sản phẩm (không phải X) → Bộ

2. **Gộp Trạng thái**: Từ cột M (Đang làm), N (Đã hoàn thành), O (KH đã ký) thành 1 cột
   - M có X → DK
   - N có X → HT
   - O có X → ĐL

3. **Chuẩn hóa tên kỹ thuật**: Viết hoa đầu mỗi từ, map các biến thể về 1 tên duy nhất
   - Ví dụ: "nguyên hưu lợi" → "Nguyễn Hữu Lợi", "trần dũng" → "Trần Dũng"

4. **Điền tên nhà máy**: Điền tên nhà máy cho các dòng con (stt trống) từ dòng cha

5. **Chuyển định dạng ngày tháng**: Đưa về format DD/MM/YYYY

## Cấu trúc file sạch (14 cột)

Stt, Nhà máy, Mã PT/KH, Mã NM, Khách hàng, Ngày nhận, Ngày HT, Loại mẫu, SP, Số lượng, Kỹ thuật, Trạng thái, Có ĐH, Ghi chú
"""

with open(report_path, 'w', encoding='utf-8') as f:
    f.write(report_content)

print(f"Đã lưu báo cáo: {report_path}")
print(f"\n{'='*60}")
print("HOÀN THÀNH!")
print(f"{'='*60}")