-- Automation Features for Discovery Strategies
-- 017_automation_features.sql

-- Add automation columns to discovery_strategies
ALTER TABLE discovery_strategies 
ADD COLUMN IF NOT EXISTS automation_enabled BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS schedule_cron VARCHAR(100),
ADD COLUMN IF NOT EXISTS auto_trigger_conditions JSONB, -- {condition_type, threshold, metric}
ADD COLUMN IF NOT EXISTS policy_driven BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS policy_ids UUID[],
ADD COLUMN IF NOT EXISTS real_time_monitoring BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS next_run_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS last_run_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS run_count INT DEFAULT 0,
ADD COLUMN IF NOT EXISTS automation_config JSONB; -- {notifications, retry_policy, failure_threshold}

-- Automation Rules Table
CREATE TABLE IF NOT EXISTS automation_rules (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	strategy_id UUID REFERENCES discovery_strategies(id) ON DELETE CASCADE,
	rule_type VARCHAR(50) NOT NULL, -- 'schedule', 'condition', 'policy', 'event'
	rule_config JSONB NOT NULL, -- Rule-specific configuration
	enabled BOOLEAN DEFAULT TRUE,
	priority INT DEFAULT 0,
	created_at TIMESTAMPTZ DEFAULT NOW(),
	updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Automation Execution Log
CREATE TABLE IF NOT EXISTS automation_executions (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	strategy_id UUID REFERENCES discovery_strategies(id) ON DELETE CASCADE,
	rule_id UUID REFERENCES automation_rules(id) ON DELETE SET NULL,
	trigger_type VARCHAR(50) NOT NULL, -- 'scheduled', 'condition', 'policy', 'manual', 'event'
	status VARCHAR(50) NOT NULL, -- 'success', 'failed', 'running', 'cancelled'
	process_id UUID REFERENCES data_discovery_processes(id) ON DELETE SET NULL,
	triggered_at TIMESTAMPTZ DEFAULT NOW(),
	completed_at TIMESTAMPTZ,
	error_message TEXT,
	execution_details JSONB
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_discovery_strategies_automation ON discovery_strategies(automation_enabled, next_run_at);
CREATE INDEX IF NOT EXISTS idx_automation_rules_strategy ON automation_rules(strategy_id, enabled);
CREATE INDEX IF NOT EXISTS idx_automation_executions_strategy ON automation_executions(strategy_id, triggered_at DESC);
CREATE INDEX IF NOT EXISTS idx_automation_executions_status ON automation_executions(status, triggered_at DESC);

