from datetime import datetime
import pytz
import mysql.connector
from pymcprotocol import Type3E
import traceback

# === Configuration ===
MYSQL_CONFIG = {
    "host": "localhost",
    "user": "hydronun_ml",
    "password": "Hydron112-",
    "database": "hydronun_cnc"
}

PLC_IP = "193.227.116.170"
PLC_PORT = 8080

REGISTER_MAP = {
    "czas_frezarka_sn": [101, 102, 103, 104, 105, 106],
    "czas_frezarka_n":  [141, 142, 143, 144, 145, 146],
    "czas_tokarka_sn":  [201, 202, 203, 204, 205, 206],
    "czas_tokarka_n":   [241, 242, 243, 244, 245, 246],
    "czas_frezarka_pom":[301, 302, 303, 304, 305, 306],
    "wrz_tokarka_sn":  [211, 212, 213, 214, 215, 216],
    "wrz_tokarka_n":   [251, 252, 253, 254, 255, 256],
}

def get_timestamps():
    print("⏱ Getting timestamps...")
    cet = pytz.timezone("Europe/Paris")
    now_utc = datetime.now(pytz.utc)
    now_cet = now_utc.astimezone(cet)
    print(f"🕓 Local (CET): {now_cet}, UTC: {now_utc}")
    return now_cet, now_utc

def read_today_machine_values(today_index, max_attempts=5):
    attempt = 1
    while attempt <= max_attempts:
        print(f"🔄 Attempt {attempt} to read registers (with fresh PLC connection)...")
        values = {}
        total = 0

        plc = Type3E()
        try:
            plc.connect(PLC_IP, PLC_PORT)
            print("✅ Connected to PLC")
        except Exception as e:
            print(f"❌ Failed to connect to PLC on attempt {attempt}: {e}")
            attempt += 1
            continue

        for key, addresses in REGISTER_MAP.items():
            reg_addr = addresses[today_index]
            try:
                val = plc.batchread_wordunits(f"D{reg_addr}", 1)[0]
                values[key] = val
                total += val
                print(f"   - {key}: D{reg_addr} = {val}")
            except Exception as e:
                print(f"❌ Error reading {key} at D{reg_addr}: {e}")
                break  # if any read fails, discard this attempt and retry
        else:
            if total > 0:
                return values
            else:
                print("⚠️ All read values are 0 — retrying...")
        attempt += 1

    print("❌ All attempts failed or returned 0. Skipping update.")
    return None

def write_snapshot_to_db(timestamp_local, timestamp_utc, values):
    print("🗄 Connecting to MySQL database...")
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cur = conn.cursor()
        print("✅ Connected to MySQL")
    except Exception as e:
        print("❌ MySQL connection failed:", e)
        raise

    row = (
        timestamp_local.strftime("%Y-%m-%d %H:%M:%S"),
        timestamp_utc.strftime("%Y-%m-%d %H:%M:%S"),
        values["czas_frezarka_sn"],
        values["czas_frezarka_n"],
        values["czas_tokarka_sn"],
        values["czas_tokarka_n"],
        values["czas_frezarka_pom"],
        values["wrz_tokarka_sn"],
        values["wrz_tokarka_n"]
        )

    print(f"📤 Writing to DB:\n    Timestamps: {row[0]} / {row[1]}\n    Values: {row[2:]}")
    
    try:
        cur.execute("""
            INSERT INTO machine_times_h
            (timestamp_local, timestamp_utc, czas_frezarka_sn, czas_frezarka_n, czas_tokarka_sn, czas_tokarka_n, czas_frezarka_pom, wrz_tokarka_sn, wrz_tokarka_n)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                czas_frezarka_sn = VALUES(czas_frezarka_sn),
                czas_frezarka_n  = VALUES(czas_frezarka_n),
                czas_tokarka_sn  = VALUES(czas_tokarka_sn),
                czas_tokarka_n   = VALUES(czas_tokarka_n),               
                czas_frezarka_pom   = VALUES(czas_frezarka_pom),
                wrz_tokarka_sn   = VALUES(wrz_tokarka_sn), 
                wrz_tokarka_n   = VALUES(wrz_tokarka_n) ;
        """, row)
        conn.commit()
        print("✅ Snapshot saved to DB")
    except Exception as e:
        print("❌ Failed to insert/update database:", e)
        raise
    finally:
        cur.close()
        conn.close()

if __name__ == "__main__":
    print("🚀 Script started")
    try:
        now_cet, now_utc = get_timestamps()
        weekday_index = now_cet.weekday()  # 0=Monday, 6=Sunday
        print(f"📆 Weekday index: {weekday_index}")

        if weekday_index > 5:
            print("ℹ️ Sunday — no register assigned.")
        else:
            values = read_today_machine_values(weekday_index)

            if values is None:
                print("⚠️ No usable data collected after retries — skipping DB update.")
            else:
                write_snapshot_to_db(now_cet, now_utc, values)

    except Exception as e:
        print("❌ Uncaught Exception:")
        traceback.print_exc()
    print("🏁 Script finished")