-- Migration: 003_create_triggers.sql
-- Database triggers for multi-agent coordination system
-- Author: Rivet (Ops)
-- Target: Supabase Database (rateright-growth)

BEGIN;

-- Function to automatically update updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update bot_tasks.updated_at on UPDATE
CREATE TRIGGER update_bot_tasks_updated_at 
    BEFORE UPDATE ON bot_tasks 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Function to log task status changes to audit log
CREATE OR REPLACE FUNCTION log_task_status_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Only log if status actually changed
    IF (OLD IS NULL OR OLD.status != NEW.status) THEN
        INSERT INTO bot_audit_log (bot_id, action, details)
        VALUES (
            COALESCE(NEW.assigned_to, NEW.bot_id),
            'task_status_change',
            jsonb_build_object(
                'task_id', NEW.id,
                'title', NEW.title,
                'old_status', COALESCE(OLD.status, 'new'),
                'new_status', NEW.status,
                'completed_at', NEW.completed_at
            )
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically audit task status changes
CREATE TRIGGER audit_task_status_changes
    AFTER INSERT OR UPDATE ON bot_tasks
    FOR EACH ROW
    EXECUTE FUNCTION log_task_status_change();

-- Function to automatically set completed_at when task status becomes 'completed'
CREATE OR REPLACE FUNCTION set_task_completed_at()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        NEW.completed_at = NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to set completed_at timestamp
CREATE TRIGGER set_completed_timestamp
    BEFORE UPDATE ON bot_tasks
    FOR EACH ROW
    EXECUTE FUNCTION set_task_completed_at();

-- Function to update bot heartbeat when status changes
CREATE OR REPLACE FUNCTION update_bot_heartbeat()
RETURNS TRIGGER AS $$
BEGIN
    -- Update heartbeat whenever a bot's status changes
    INSERT INTO bot_status (bot_id, last_heartbeat, status)
    VALUES (NEW.bot_id, NOW(), 'online')
    ON CONFLICT (bot_id)
    DO UPDATE SET 
        last_heartbeat = NOW(),
        status = CASE 
            WHEN bot_status.status = 'error' THEN bot_status.status 
            ELSE 'online' 
        END;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update bot heartbeat on task activity
CREATE TRIGGER update_heartbeat_on_task_activity
    AFTER INSERT OR UPDATE ON bot_tasks
    FOR EACH ROW
    EXECUTE FUNCTION update_bot_heartbeat();

COMMIT;