CREATE TABLE IF NOT EXISTS trigger_manager (
    property_id VARCHAR(50) NOT NULL,
    execution_id UUID NOT NULL PRIMARY KEY,
    datetime_trigger TIMESTAMP,
    automation_id VARCHAR(255) NOT NULL,
    automation_name VARCHAR(255) NOT NULL,
    version INTEGER,
    status VARCHAR(50) NOT NULL CHECK (status IN ('pending', 'running', 'completed', 'failed')),
    path_to_json_flow VARCHAR(225) NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP
);

CREATE TABLE IF NOT EXISTS automation_context (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    property_id VARCHAR(50) NOT NULL,
    automation_id VARCHAR(255) NOT NULL PRIMARY KEY,
    automation_name VARCHAR(255) NOT NULL,
    current_version INTEGER,
    status VARCHAR(50) NOT NULL CHECK (status IN ('inactive', 'active', 'archived')) DEFAULT 'inactive',
    path_to_json_flow VARCHAR(225) NOT NULL
);

CREATE TABLE IF NOT EXISTS node_context (
    property_id VARCHAR(50) NOT NULL,
    automation_id VARCHAR(255) NOT NULL,
    execution_id UUID NOT NULL,
    user_pseudo_id UUID NOT NULL,
    node_id VARCHAR(255) NOT NULL,
    node_type VARCHAR(50) NOT NULL,
    node_name VARCHAR(255),
    input_data JSONB,
    output_data JSONB,
    status VARCHAR(50) NOT NULL CHECK (status IN ('completed', 'failed')),
    execution_time_ms INTEGER,
    error_message TEXT
);

CREATE TABLE IF NOT EXISTS user_pseudo_running_stage (
    property_id VARCHAR(50) NOT NULL,
    automation_id VARCHAR(255) NOT NULL,
    execution_id UUID NOT NULL,
    user_pseudo_id UUID NOT NULL,
    next_node_id VARCHAR(255) NOT NULL,
    next_node_type VARCHAR(50) NOT NULL,
    next_node_name VARCHAR(255),
    wait_until TEXT,
    input_data JSONB,
    status INTEGER DEFAULT 0,       -- 0=pending, 1=completed, 2=failed, 3=running
    is_moved INTEGER DEFAULT 0,     -- 0=pending, 1=completed
    execution_time_ms INTEGER DEFAULT 0,
    error_message TEXT
);

CREATE INDEX IF NOT EXISTS idx_trigger_manager ON trigger_manager(execution_id);
CREATE INDEX IF NOT EXISTS idx_automation_context ON automation_context(automation_id);
CREATE INDEX IF NOT EXISTS idx_node_context_automation_id_execution_id_user_pseudo_id ON node_context(automation_id,execution_id,user_pseudo_id);