#!/usr/bin/env python3
import json

with open('/home/mdares/.node-red/flows.json', 'r') as f:
    flows = json.load(f)

# SQL to create the table
create_table_sql = """CREATE TABLE IF NOT EXISTS anomaly_events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp BIGINT NOT NULL,
    work_order_id VARCHAR(50),
    anomaly_type VARCHAR(50) NOT NULL,
    severity ENUM('info', 'warning', 'critical') NOT NULL DEFAULT 'warning',
    title VARCHAR(200) NOT NULL,
    description TEXT,
    data_json TEXT,
    kpi_snapshot_json TEXT,
    status ENUM('active', 'acknowledged', 'resolved') DEFAULT 'active',
    acknowledged_at BIGINT,
    resolved_at BIGINT,
    auto_resolved BOOLEAN DEFAULT FALSE,
    cycle_count INT,
    occurrence_count INT DEFAULT 1,
    last_occurrence BIGINT,
    notes TEXT,
    INDEX idx_timestamp (timestamp),
    INDEX idx_work_order (work_order_id),
    INDEX idx_status (status),
    INDEX idx_type (anomaly_type),
    INDEX idx_severity (severity)
)"""

# Find the tab where we'll add the setup node (use the same tab as other nodes)
tab_id = None
for node in flows:
    if node.get('type') == 'tab':
        tab_id = node['id']
        break

if not tab_id:
    print("✗ Could not find a tab to add the node to")
    exit(1)

# Create an inject node to trigger the table creation
inject_node = {
    "id": "create_table_inject_temp",
    "type": "inject",
    "z": tab_id,
    "name": "CREATE anomaly_events table (run once)",
    "props": [{"p": "payload"}],
    "repeat": "",
    "crontab": "",
    "once": False,
    "onceDelay": 0.1,
    "topic": "",
    "payload": "",
    "payloadType": "date",
    "x": 250,
    "y": 900,
    "wires": [["create_table_function_temp"]]
}

# Create a function node with the SQL
function_node = {
    "id": "create_table_function_temp",
    "type": "function",
    "z": tab_id,
    "name": "Create Table SQL",
    "func": f"""// Create anomaly_events table
msg.topic = `{create_table_sql}`;
msg.payload = [];
return msg;""",
    "outputs": 1,
    "timeout": 0,
    "noerr": 0,
    "initialize": "",
    "finalize": "",
    "libs": [],
    "x": 500,
    "y": 900,
    "wires": [["create_table_mysql_temp"]]
}

# Create a MySQL node to execute the SQL
mysql_node = {
    "id": "create_table_mysql_temp",
    "type": "mysql",
    "z": tab_id,
    "mydb": "00d8ad2b0277f906",
    "name": "Execute Create Table",
    "x": 730,
    "y": 900,
    "wires": [["create_table_debug_temp"]]
}

# Create a debug node to show result
debug_node = {
    "id": "create_table_debug_temp",
    "type": "debug",
    "z": tab_id,
    "name": "Table Created",
    "active": True,
    "tosidebar": True,
    "console": False,
    "tostatus": False,
    "complete": "true",
    "targetType": "full",
    "statusVal": "",
    "statusType": "auto",
    "x": 960,
    "y": 900,
    "wires": []
}

# Add all nodes to flows
flows.extend([inject_node, function_node, mysql_node, debug_node])

with open('/home/mdares/.node-red/flows.json', 'w') as f:
    json.dump(flows, f, indent=4)

print("✓ Added CREATE TABLE nodes to flows.json")
print("  - Inject node: 'CREATE anomaly_events table (run once)'")
print("  - After Node-RED restarts, click this inject button ONCE to create the table")
print("  - Check debug panel to confirm table creation")
print("  - These temporary nodes can be deleted after use")