import json
import requests
import pymysql
from datetime import datetime

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 sync_job():
    # 1. 날짜 설정 및 URL 생성
    today = datetime.now().strftime('%Y-%m-%d')
    base_url = "http://139.150.80.62/manufacture/cutting/period/external/"
    query_string = f"?start={today}&end={today}"
    url = base_url + query_string

    try:
        # 2. API 데이터 호출
        res = requests.get(url, timeout=120)
        res.raise_for_status() # HTTP 에러 발생 시 예외 처리
        data_json = res.json()
        api_data = data_json.get('data', [])

        if api_data:
            conn = get_db_conn()
            try:
                with conn.cursor() as cursor:
                    for item in api_data:
                        # 3. 부모 테이블(erp_cutting_master) 갱신
                        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']))

                        for job in item['job_list']:
                            m, b, tl = int(float(job['meter'])), int(float(job['bon'])), int(float(job['total_length']))
                            
                            # 4. 자식 테이블(erp_cutting_list) 갱신
                            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'], m, b, tl, 1 if job['swaging'] else 0, 1 if job['drum'] else 0, 1 if job['bb'] else 0, job['structure_dt']))

                            # 5. HMI 테이블 갱신
                            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), meter=VALUES(meter), total_length=VALUES(total_length)
                            """, (job['id'], item['id'], item['num'], job['num'], job['pjt_name'], item['idntf'], m, b, tl, 1 if job['swaging'] else 0, 1 if job['drum'] else 0, 1 if job['bb'] else 0, job['structure_dt']))
                
                conn.commit()
            finally:
                conn.close()
            print(f"[{datetime.now()}] Sync Success: {len(api_data)} items processed.")
        else:
            print(f"[{datetime.now()}] Sync: No data found for today.")

    except Exception as e:
        print(f"[{datetime.now()}] Sync Error: {e}")

if __name__ == "__main__":
    sync_job()