import json
import os
from datetime import datetime
import paho.mqtt.client as mqtt
import pymysql

# 1. 외부 config.json 파일 로드
CONFIG_FILE = "config.json"

if not os.path.exists(CONFIG_FILE):
    print(f"❌ [에러] [{CONFIG_FILE}] 파일이 경로에 없습니다.")
    exit(1)

with open(CONFIG_FILE, "r", encoding="utf-8") as f:
    config_data = json.load(f)

mqtt_db_config = config_data.get("mqtt_db")
mqtt_broker_config = config_data.get("mqtt")

TARGET_DATABASE = mqtt_db_config.get('database', 'device_mqtt')

db_base_config = {
    'host': mqtt_db_config.get('host', '127.0.0.1'),
    'port': int(mqtt_db_config.get('port', 3307)),
    'user': mqtt_db_config.get('user', 'jinsungmes'),
    'password': mqtt_db_config.get('password', 'qetu1357@#'),
    'db': TARGET_DATABASE,
    'charset': 'utf8mb4'
}

START_SIGNAL_COLUMN = 'cycle_start'
END_SIGNAL_COLUMN = 'cycle_end'


def check_and_create_table_structure(table_name):
    """ 1054/1406 에러 예방 및 컬럼 스키마 완제품 개설 함수 """
    conn = pymysql.connect(**db_base_config)
    try:
        with conn.cursor() as cursor:
            create_table_sql = f"""
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                seq INT AUTO_INCREMENT PRIMARY KEY,
                column_name VARCHAR(50) NOT NULL,
                sub_name VARCHAR(50) DEFAULT NULL,
                master_num VARCHAR(50) DEFAULT NULL,
                work_id VARCHAR(50) DEFAULT NULL,
                cutting_bon INT DEFAULT NULL,
                cutting_total_bon INT DEFAULT NULL,
                cutting_length INT DEFAULT NULL,
                drag_duration INT DEFAULT 0,
                drag_count INT DEFAULT 0,
                wind_duration INT DEFAULT 0,
                wind_count INT DEFAULT 0,
                converted_value LONGTEXT DEFAULT NULL,
                db_insert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            );
            """
            cursor.execute(create_table_sql)
            
            # 구버전 테이블 자가 치유 레이어
            check_columns = ['drag_duration', 'drag_count', 'wind_duration', 'wind_count']
            for col in check_columns:
                try:
                    cursor.execute(f"SELECT `{col}` FROM `{table_name}` LIMIT 1;")
                except pymysql.InternalError as ie:
                    if ie.args == 1054:
                        cursor.execute(f"ALTER TABLE `{table_name}` ADD COLUMN `{col}` INT DEFAULT 0;")
        conn.commit()
    except Exception:
        pass
    finally:
        conn.close()


def on_message(client, userdata, message):
    connection = None
    cursor = None
    
    try:
        raw_topic = message.topic
        if isinstance(raw_topic, bytes):
            raw_topic = raw_topic.decode("utf-8")
        else:
            raw_topic = str(raw_topic)

        topic_parts = [t.strip() for t in raw_topic.split('/') if t.strip()]

        if len(topic_parts) != 2:
            return
        if topic_parts[0] in ['factory', 'sys', 'system', 'config']:
            return
        if topic_parts[1] not in [START_SIGNAL_COLUMN, END_SIGNAL_COLUMN, 'times']:
            return

        machine_id = str(topic_parts[0])     
        column_name = str(topic_parts[1])    

        target_table = f"{machine_id}_logs"
        check_and_create_table_structure(target_table)

        payload_str = message.payload.decode("utf-8").strip()
        json_data = json.loads(payload_str)

        connection = pymysql.connect(**db_base_config)
        cursor = connection.cursor()

        # ────────────────────────────────────────────────────────
        # [A] cycle_start 또는 cycle_end 패키지 데이터 처리
        # ────────────────────────────────────────────────────────
        if column_name in [START_SIGNAL_COLUMN, END_SIGNAL_COLUMN]:
            w_id = json_data.get('작업번호')
            u_id = json_data.get('사원번호')
            s_name = json_data.get('status')

            master_num = str(w_id if not isinstance(w_id, list) else w_id).strip() if w_id else None
            work_id = str(u_id if not isinstance(u_id, list) else u_id).strip() if u_id else None
            sub_name = str(s_name if not isinstance(s_name, list) else s_name).strip() if s_name else None

            try: cutting_bon = int(json_data.get('수량', 0))
            except: cutting_bon = 0
            try: cutting_total_bon = int(json_data.get('총수량', 0))
            except: cutting_total_bon = 0
            try: cutting_length = int(float(str(json_data.get('M', 0))))
            except: cutting_length = 0

            converted_value = "1사이클 시작" if column_name == START_SIGNAL_COLUMN else "1사이클 완료"
            log_title = "공정시작 🚀" if column_name == START_SIGNAL_COLUMN else "공정마감 🏁"

            sql = f"""
            INSERT INTO `{target_table}`
            (column_name, sub_name, master_num, work_id, cutting_bon, cutting_total_bon, cutting_length, converted_value)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (
                str(column_name), sub_name, master_num, work_id,
                cutting_bon, cutting_total_bon, cutting_length, converted_value
            ))

            print(f"\n[{log_title}] DB: {TARGET_DATABASE} | 설비: [{machine_id}] ➔ 테이블: [{target_table}]")
            print(f"            📥 작업: {master_num} | 수량: {cutting_bon} | 총수량: {cutting_total_bon}")

        # ────────────────────────────────────────────────────────
        # [B] 동작별 JSON 배열 파싱 후 각각 개별 컬럼으로 분류하여 적재 (필터 해제 완결판)
        # ────────────────────────────────────────────────────────
        else:
            drag_duration = 0
            drag_count = 0
            wind_duration = 0
            wind_count = 0

            if isinstance(json_data, list):
                for item in json_data:
                    action_name = item.get('action_name', '').strip()
                    
                    if action_name == "끌기":
                        drag_duration = int(item.get('duration_seconds', 0))
                        drag_count = int(item.get('action_count', 0))
                    elif action_name == "권취":
                        wind_duration = int(item.get('duration_seconds', 0))
                        wind_count = int(item.get('action_count', 0))

            converted_value = json.dumps(json_data, ensure_ascii=False)

            sql = f"""
            INSERT INTO `{target_table}` 
            (column_name, drag_duration, drag_count, wind_duration, wind_count, converted_value)
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (
                str(column_name), 
                drag_duration, drag_count, 
                wind_duration, wind_count, 
                str(converted_value)
            ))

            print(f"\n[⏱️ 동작타임 컬럼 분류 성공] DB: {TARGET_DATABASE} | 설비: [{machine_id}]")
            print(f"            📥 [끌기] 시간: {drag_duration}초, 횟수: {drag_count}회")
            print(f"            📥 [권취] 시간: {wind_duration}초, 횟수: {wind_count}회")

        connection.commit()

    except Exception as e:
        print(f"❌ [시스템 에러] 데이터 적재 실패: {e}")
        if connection and connection.open:
            connection.rollback()
    finally:
        if cursor: cursor.close()
        if connection: connection.close()


def on_connect(client, userdata, flags, rc):
    if rc == 0:
        print("✅ MQTT 브로커 연결 및 구독 세션 활성화")
        client.subscribe("+/#")
    else:
        print(f"❌ MQTT 브로커 연결 실패 (코드: {rc})")

mqtt_client = mqtt.Client()
mqtt_client.on_connect = on_connect
mqtt_client.on_message = on_message

broker_host = mqtt_broker_config.get("host", "localhost")
broker_port = mqtt_broker_config.get("port", 1883)

mqtt_client.connect(broker_host, broker_port, 60)

print("=========================================================================")
print(f" 🚀 [단일 DB: {TARGET_DATABASE} - PLC 전송값 100% 무조건 강제 인서트 버전] 가동")
print("=========================================================================")
mqtt_client.loop_forever()