import json
import requests
import pymysql
from datetime import datetime, timedelta
import time

def get_db_conn():
    with open('/root/mes_v2/config.json', 'r', encoding='utf-8') as f:
        config = json.load(f)
    db_info = config['mes_db']
    return pymysql.connect(
        host=db_info['host'], port=db_info['port'],
        user=db_info['user'], password=db_info['password'],
        database=db_info['database'], charset='utf8mb4'
    )

def run_history_sync(start_str, end_str):
    BASE_URL = "http://139.150.80.62"
    API_PATH = "/manufacture/cutting/period/external/"
    
    current_date = datetime.strptime(start_str, "%Y-%m-%d")
    final_end = datetime.strptime(end_str, "%Y-%m-%d")

    print(f"🚀 {start_str} ~ {end_str} 수집 시작 (HMI 테이블 & No 자동생성 적용)")

    while current_date <= final_end:
        if current_date.weekday() >= 5: # 토, 일 건너뛰기
            current_date += timedelta(days=1)
            continue

        date_str = current_date.strftime('%Y-%m-%d')
        url = f"{BASE_URL}{API_PATH}?start={date_str}&end={date_str}"
        
        print(f"📅 [{date_str}] 호출 중...", end=" ", flush=True)

        try:
            res = requests.get(url, timeout=120)
            res.raise_for_status()
            data_json = res.json()
            
            api_data = data_json.get('data', [])
            msg = data_json.get('msg', '')

            if not api_data or msg == "NotExists":
                print("⚪ 데이터 없음")
            else:
                conn = get_db_conn()
                with conn.cursor() as cursor:
                    for item in api_data:
                        # 1. 부모 테이블 저장
                        cursor.execute("""
                            INSERT INTO erp_cutting_master (id, num, company, status, job_cnt, idntf, unit_num_list)
                            VALUES (%s, %s, %s, %s, %s, %s, %s)
                            ON DUPLICATE KEY UPDATE status=VALUES(status), idntf=VALUES(idntf)
                        """, (item['id'], item['num'], item['company'], item['status'], item['job_cnt'], item['idntf'], item['unit_num_list']))

                        # 2. 자식 및 HMI 테이블 저장
                        for job in item.get('job_list', []):
                            # 소수점 제거 정수화
                            meter_int = int(float(job.get('meter', 0)))
                            bon_int = int(float(job.get('bon', 0)))
                            total_length_int = int(float(job.get('total_length', 0)))

                            # [erp_cutting_list] 삽입 (idntf 포함)
                            cursor.execute("""
                                INSERT INTO erp_cutting_list (
                                    id, parent_id, master_num, status, job_cnt, idntf, num, pjt_name, 
                                    meter, bon, total_length, swaging, drum, bb, structure_dt
                                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                                ON DUPLICATE KEY UPDATE 
                                    status=VALUES(status), idntf=VALUES(idntf), num=VALUES(num),
                                    meter=VALUES(meter), total_length=VALUES(total_length)
                            """, (job['id'], item['id'], item['num'], item['status'], item['job_cnt'], 
                                  item['idntf'], job['num'], job['pjt_name'], meter_int, bon_int, 
                                  total_length_int, 1 if job['swaging'] else 0, 1 if job['drum'] else 0, 
                                  1 if job['bb'] else 0, job['structure_dt']))

                            # [hmi_erp_cutting] 삽입 (no 자동생성, job_id로 중복방지)
                            cursor.execute("""
                                INSERT INTO hmi_erp_cutting (
                                    job_id, parent_id, master_num, num, pjt_name, idntf, 
                                    meter, bon, total_length, swaging, drum, bb, structure_dt
                                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                                ON DUPLICATE KEY UPDATE 
                                    num=VALUES(num), idntf=VALUES(idntf), pjt_name=VALUES(pjt_name),
                                    meter=VALUES(meter), total_length=VALUES(total_length)
                            """, (job['id'], item['id'], item['num'], job['num'], job['pjt_name'], item['idntf'],
                                  meter_int, bon_int, total_length_int, 1 if job['swaging'] else 0, 
                                  1 if job['drum'] else 0, 1 if job['bb'] else 0, job['structure_dt']))
                conn.commit()
                conn.close()
                print(f"✅ 완료 ({len(api_data)}건)")

        except Exception as e:
            print(f"❌ 에러: {e}")

        current_date += timedelta(days=1)
        # 응답 즉시 다음으로 진행 (미세 지연)
        time.sleep(0.1)

if __name__ == "__main__":
    # 5년치 수집 기간 설정
    run_history_sync("2021-05-05", "2026-05-05")