# -*- coding: utf-8 -*-
"""Tạo CSDL trung tâm BTChem (SQLite) + nạp dữ liệu tuần 34.
Chạy: python build_db_btchem.py
Kết quả: C:/Claw 2/CSDL/btchem.db
"""
import sqlite3, os, re, glob
from openpyxl import load_workbook
from docx import Document

DB = r'C:/Claw 2/CSDL/btchem.db'
BASE = r'C:/Claw 2'

os.makedirs(os.path.dirname(DB), exist_ok=True)

# ============ TẠO SCHEMA ============
schema = """
CREATE TABLE IF NOT EXISTS nha_may (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ten TEXT UNIQUE NOT NULL,
    khu_vuc TEXT, nhom TEXT, ghi_chu TEXT,
    updated_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS bao_cao_sx (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tuan TEXT NOT NULL,
    nha_may_id INTEGER REFERENCES nha_may(id),
    cont REAL, chi_tiet TEXT, mau_moi TEXT, tinh_trang TEXT,
    tinh_hinh TEXT, canh_tranh TEXT,
    FOREIGN KEY (nha_may_id) REFERENCES nha_may(id)
);
CREATE TABLE IF NOT EXISTS phai_vien_kt (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ten TEXT UNIQUE NOT NULL, cum TEXT
);
CREATE TABLE IF NOT EXISTS bao_cao_cskh (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tuan TEXT NOT NULL, ngay TEXT, nha_may_id INTEGER,
    nguoi_bc TEXT, canh_tranh TEXT, kh_chu_luc TEXT, noi_dung TEXT,
    FOREIGN KEY (nha_may_id) REFERENCES nha_may(id)
);
CREATE TABLE IF NOT EXISTS su_co (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ngay TEXT, nha_may_id INTEGER, mau TEXT, mo_ta TEXT,
    ket_qua TEXT, xu_ly TEXT, nguoi_bc TEXT, nguon TEXT,
    FOREIGN KEY (nha_may_id) REFERENCES nha_may(id)
);
CREATE TABLE IF NOT EXISTS mau_lam (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tuan TEXT NOT NULL, nha_may_id INTEGER, ma_mau TEXT,
    khach_hang TEXT, loai TEXT, san_pham TEXT, so_luong TEXT,
    ky_thuat TEXT, trang_thai TEXT, co_don_hang TEXT,
    FOREIGN KEY (nha_may_id) REFERENCES nha_may(id)
);
CREATE TABLE IF NOT EXISTS nguyen_lieu (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ten TEXT NOT NULL, nhom TEXT, don_vi TEXT
);
CREATE TABLE IF NOT EXISTS gia_nguyen_lieu (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nguyen_lieu_id INTEGER, ngay TEXT, loai TEXT, gia_vnd REAL, ghi_chu TEXT,
    FOREIGN KEY (nguyen_lieu_id) REFERENCES nguyen_lieu(id)
);
"""
con = sqlite3.connect(DB)
con.executescript(schema)
print('✅ Schema đã tạo')

# ============ 1. NẠP NHÀ MÁY + BÁO CÁO SX T34 ============
SRC_SX = os.path.join(BASE, 'Bao cao - Hanh chinh/Bao-cao-sx/LAMSACH/KETQUA/BANG_BC_SX_TUAN_34_DA-LAM-SACH.xlsx')
def clean(v):
    if v is None: return ''
    return re.sub(r'\s+', ' ', str(v)).strip()

wb = load_workbook(SRC_SX, data_only=True)
ws = wb['T39']
cur_kv, cur_nhom = '', ''
n_nm = 0; n_sx = 0
for row in ws.iter_rows(min_row=4, values_only=True):
    tt = clean(row[0]); nm = clean(row[1]); kv = clean(row[2]); qm = clean(row[4])
    if kv and not nm and not tt:
        cur_kv = kv; continue
    if qm and not nm:
        cur_nhom = qm; continue
    if nm:
        con.execute("INSERT OR IGNORE INTO nha_may (ten, khu_vuc, nhom) VALUES (?,?,?)",
                    (nm, cur_kv, cur_nhom))
        mid = con.execute("SELECT id FROM nha_may WHERE ten=?", (nm,)).fetchone()[0]
        try: cont = float(clean(row[3]).replace(',', '.'))
        except: cont = None
        con.execute("""INSERT INTO bao_cao_sx (tuan, nha_may_id, cont, chi_tiet, mau_moi, tinh_trang, tinh_hinh, canh_tranh)
                       VALUES (?,?,?,?,?,?,?,?)""",
                    ('T34', mid, cont, clean(row[5]), clean(row[6]), clean(row[7]), clean(row[8]), clean(row[9])))
        n_nm += 1; n_sx += 1
print(f'✅ Nhà máy: {n_nm} | Báo cáo SX T34: {n_sx}')

# ============ 2. NẠP CSKH T34 (DOCX) ============
SRC_CSKH = os.path.join(BASE, 'Bao cao - Hanh chinh/Bao cao CSKH/Báo Cáo Tuần/17-23_08_2026/Bao_cao_CSKH_tong_hop_17-23_08_2026.docx')
try:
    doc = Document(SRC_CSKH)
    text = '\n'.join(p.text for p in doc.paragraphs)
    # Trích 6 nhà máy CSKH theo mẫu "N. Tên — ngày (Quang)"
    n_cskh = 0
    for line in text.split('\n'):
        m = re.match(r'(\d+)\.\s*(.+?)\s*—\s*(\d{2}/\d{2}/2026)\s*\((\w+)\)', line.strip())
        if m:
            ten_nm = m.group(2).strip()
            con.execute("INSERT OR IGNORE INTO nha_may (ten) VALUES (?)", (ten_nm,))
            mid = con.execute("SELECT id FROM nha_may WHERE ten=?", (ten_nm,)).fetchone()
            mid = mid[0] if mid else None
            con.execute("INSERT INTO bao_cao_cskh (tuan, ngay, nha_may_id, nguoi_bc) VALUES (?,?,?,?)",
                        ('T34', m.group(3), mid, m.group(4)))
            n_cskh += 1
    print(f'✅ CSKH T34: {n_cskh} báo cáo')
except Exception as e:
    print(f'⚠️ CSKH: {e}')

# ============ 3. NẠP PHÁI VIÊN KT ============
ktv = [('Nguyễn Văn Đen','Hạnh Phúc & Đăng Long'), ('Nguyễn Đăng Hiệp','Anh Khoa'),
       ('Lê Quang Khải','Minh Dương & Trường Thành'), ('Nguyễn Văn Linh','MeKong, Geuther & Demeyer'),
       ('Bùi Đức Nam','Hố Nai'), ('Nguyễn Duy Tài','Thiết Đan & Bảo Hưng'),
       ('Nguyễn Đức Thăng','Kim Phong & Kim Huy'), ('Nguyễn Minh Thoại','Tân Việt Tiến'),
       ('Lê Văn Hòa','Đất Mới, Liên Minh & An Cường')]
for ten, cum in ktv:
    con.execute("INSERT OR IGNORE INTO phai_vien_kt (ten, cum) VALUES (?,?)", (ten, cum))
print('✅ Phái viên KT: 9')

# ============ 4. NẠP MẪU LÀM T34 (XLSX gộp) ============
SRC_MAU = os.path.join(BASE, 'Bao cao - Hanh chinh/Bao cao lam mau/da lam sach/Tuan_34_17.08-22.08_DA-LAM-SACH.xlsx')
try:
    wb2 = load_workbook(SRC_MAU, data_only=True)
    ws2 = wb2[wb2.sheetnames[0]]
    n_mau = 0
    for row in ws2.iter_rows(min_row=2, values_only=True):
        nm = clean(row[1])
        if not nm: continue
        con.execute("INSERT OR IGNORE INTO nha_may (ten) VALUES (?)", (nm,))
        mid = con.execute("SELECT id FROM nha_may WHERE ten=?", (nm,)).fetchone()
        mid = mid[0] if mid else None
        con.execute("""INSERT INTO mau_lam (tuan, nha_may_id, ma_mau, khach_hang, loai, san_pham, so_luong, ky_thuat, trang_thai, co_don_hang)
                       VALUES (?,?,?,?,?,?,?,?,?,?)""",
                    ('T34', mid, clean(row[3]), clean(row[4]), clean(row[7]), clean(row[8]),
                     clean(row[9]), clean(row[10]), clean(row[11]), clean(row[12])))
        n_mau += 1
    print(f'✅ Mẫu làm T34: {n_mau}')
except Exception as e:
    print(f'⚠️ Mẫu: {e}')

# ============ 5. NẠP NGUYÊN LIỆU + GIÁ (bảng giá 25-08) ============
SRC_GIA = os.path.join(BASE, 'Nghien cuu/Tinh-Gia-Thanh/Bảng giá nguyên liệu (đến ngày 25-08-26) _gửi email.xlsx')
try:
    wb3 = load_workbook(SRC_GIA, data_only=True, read_only=True)
    ws3 = wb3[wb3.sheetnames[0]]
    n_nl = 0; n_gia = 0
    cur_nhom = ''
    for row in ws3.iter_rows(values_only=True):
        vals = [clean(c) for c in row[:9]]
        # Dòng nhóm
        if vals[0] and not any(vals[1:]):
            cur_nhom = vals[0]; continue
        ten = vals[0] or vals[1] or ''
        if ten and ('Tồn kho' in ten or 'Đặt hàng' in ten or 'Giá báo' in ten):
            continue
        if ten:
            con.execute("INSERT OR IGNORE INTO nguyen_lieu (ten, nhom) VALUES (?,?)", (ten, cur_nhom))
            n_nl += 1
    print(f'✅ Nguyên liệu: {n_nl}')
except Exception as e:
    print(f'⚠️ Giá: {e}')

con.commit()
con.close()
print(f'\n🎉 HOÀN TẤT: {DB}')