"""
gen_demo_data.py - 데모/검증용 합성 배치 데이터 생성
표준 배합 + 랜덤 편차를 주고, 편차에 따라 물성/적합여부를 규칙으로 생성.
실제 운영에서는 과거 제조 이력으로 대체.
"""
import random
import datetime as dt
from config import get_conn

random.seed(7)
PRODUCT = "PRD001"
N_BATCH = 220


def gen():
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT material_code, std_ratio, tolerance_pct "
                "FROM cq_formula WHERE product_code=%s AND formula_ver='V1'",
                (PRODUCT,))
            formula = cur.fetchall()

            for i in range(1, N_BATCH + 1):
                batch_no = f"B{dt.date.today():%Y%m%d}{i:04d}"
                batch_size = 10000.0
                rpm = random.choice([60, 80, 100, 120])
                mins = random.choice([20, 30, 40])
                temp = round(random.uniform(40, 70), 1)

                cur.execute("""
                    INSERT INTO cq_batch
                      (batch_no, product_code, formula_ver, batch_size,
                       mix_rpm, mix_minutes, mix_temp, status)
                    VALUES (%s,%s,'V1',%s,%s,%s,%s,'INSPECTED')
                    ON DUPLICATE KEY UPDATE batch_size=VALUES(batch_size)
                """, (batch_no, PRODUCT, batch_size, rpm, mins, temp))

                dev_thicken = 0.0   # 점증제 편차 누적 (경도 영향)
                dev_neutral = 0.0   # 중화제 편차 (pH 영향)
                seq = 0
                for f in formula:
                    seq += 1
                    std_qty = batch_size * float(f["std_ratio"]) / 100.0
                    # 대부분 정상, 가끔 큰 편차
                    if random.random() < 0.30:
                        dev = random.uniform(-12, 12)
                    else:
                        dev = random.uniform(-1.5, 1.5)
                    actual = std_qty * (1 + dev / 100.0)
                    cur.execute("""
                        INSERT INTO cq_weighing
                          (batch_no, material_code, std_qty, actual_qty,
                           deviation_pct, weigh_seq)
                        VALUES (%s,%s,%s,%s,%s,%s)
                    """, (batch_no, f["material_code"], round(std_qty, 3),
                          round(actual, 3), round(dev, 3), seq))
                    if f["material_code"] == "M004":  # 카보머(점증제)
                        dev_thicken = dev
                    if f["material_code"] == "M005":  # TEA(중화제)
                        dev_neutral = dev

                # ---- 규칙 기반 물성 생성 (편차 → 물성 영향) ----
                ph = 6.2 + dev_neutral * 0.08 - dev_thicken * 0.02 \
                    + random.uniform(-0.1, 0.1)
                hardness = 8.0 + dev_thicken * 1.2 + random.uniform(-0.8, 0.8)
                sg = 1.02 + random.uniform(-0.008, 0.008)
                content = 100.0 - abs(dev_thicken) * 0.5 + random.uniform(-1.5, 1.5)
                visc = 2000 + dev_thicken * 120 + random.uniform(-150, 150)

                # 적합 판정 (관리기준)
                ok = (5.5 <= ph <= 7.0) and (0 <= hardness <= 20) \
                    and (95 <= content <= 105) and (1000 <= visc <= 3000)
                flag = "PASS" if ok else ("REWORK" if random.random() < 0.6 else "FAIL")

                cur.execute("""
                    INSERT INTO cq_quality
                      (batch_no, ph, hardness, specific_grav, content_pct,
                       viscosity, result_flag)
                    VALUES (%s,%s,%s,%s,%s,%s,%s)
                    ON DUPLICATE KEY UPDATE ph=VALUES(ph)
                """, (batch_no, round(ph, 2), round(hardness, 2),
                      round(sg, 4), round(content, 3), round(visc, 2), flag))
        conn.commit()
        print(f"{N_BATCH} 배치 생성 완료")
    finally:
        conn.close()


if __name__ == "__main__":
    gen()