mkd/backend/migrate_pipeline_automation.sql

-- Миграция для автоматизации воронки развития

-- Таблица истории переходов
CREATE TABLE IF NOT EXISTS development_pipeline_history (
  id                BIGSERIAL PRIMARY KEY,
  pipeline_id       VARCHAR(50) NOT NULL REFERENCES development_pipeline(id) ON DELETE CASCADE,
  from_status       VARCHAR(20),
  to_status         VARCHAR(20) NOT NULL,
  reason            TEXT,
  triggered_by      VARCHAR(50) NOT NULL DEFAULT 'manual', -- 'auto' | 'manual' | user_id
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pipeline_history_pipeline ON development_pipeline_history(pipeline_id);
CREATE INDEX IF NOT EXISTS idx_pipeline_history_status ON development_pipeline_history(to_status);
CREATE INDEX IF NOT EXISTS idx_pipeline_history_date ON development_pipeline_history(created_at DESC);

-- Таблица правил автоматизации (для будущего расширения)
CREATE TABLE IF NOT EXISTS development_automation_rules (
  id                BIGSERIAL PRIMARY KEY,
  rule_name         VARCHAR(100) NOT NULL UNIQUE,
  from_status       VARCHAR(20),
  to_status         VARCHAR(20) NOT NULL,
  conditions        JSONB, -- условия для срабатывания
  enabled           BOOLEAN DEFAULT TRUE,
  priority          INTEGER DEFAULT 0,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_automation_rules_status ON development_automation_rules(from_status, to_status);
CREATE INDEX IF NOT EXISTS idx_automation_rules_enabled ON development_automation_rules(enabled);

-- Добавляем поле для отслеживания последнего автоматического обновления
ALTER TABLE development_pipeline 
ADD COLUMN IF NOT EXISTS last_auto_check TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_pipeline_last_check ON development_pipeline(last_auto_check);

-- Триггер для автоматического обновления статуса при создании ОСС
CREATE OR REPLACE FUNCTION auto_transition_to_voting()
RETURNS TRIGGER AS $$
BEGIN
  -- Если создано активное ОСС, переводим связанный pipeline объект в voting
  IF NEW.status = 'active' THEN
    UPDATE development_pipeline
    SET status = 'voting', updated_at = NOW()
    WHERE (building_id = NEW.building_id OR address = NEW.address)
      AND status = 'preparation';
    
    -- Логируем переход
    INSERT INTO development_pipeline_history (pipeline_id, from_status, to_status, reason, triggered_by)
    SELECT id, 'preparation', 'voting', 'Автоматический переход: создано активное ОСС', 'auto'
    FROM development_pipeline
    WHERE (building_id = NEW.building_id OR address = NEW.address)
      AND status = 'voting'
      AND id NOT IN (SELECT pipeline_id FROM development_pipeline_history WHERE to_status = 'voting' AND created_at > NOW() - INTERVAL '1 minute');
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_auto_transition_to_voting ON development_oss_sessions;
CREATE TRIGGER trigger_auto_transition_to_voting
  AFTER INSERT OR UPDATE ON development_oss_sessions
  FOR EACH ROW
  WHEN (NEW.status = 'active')
  EXECUTE FUNCTION auto_transition_to_voting();

-- Триггер для обработки завершения ОСС
CREATE OR REPLACE FUNCTION auto_handle_oss_completion()
RETURNS TRIGGER AS $$
DECLARE
  quorum_percent NUMERIC;
BEGIN
  -- Если ОСС завершено
  IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
    quorum_percent := (NEW.quorum_current / NULLIF(NEW.quorum_total, 0)) * 100;
    
    -- Если кворум > 50%, переводим в transfer
    IF quorum_percent > 50 THEN
      UPDATE development_pipeline
      SET status = 'transfer', updated_at = NOW()
      WHERE (building_id = NEW.building_id OR address = NEW.address)
        AND status = 'voting';
      
      -- Логируем переход
      INSERT INTO development_pipeline_history (pipeline_id, from_status, to_status, reason, triggered_by)
      SELECT id, 'voting', 'transfer', 
             format('Автоматический переход: ОСС завершено успешно (кворум %.1f%%)', quorum_percent), 
             'auto'
      FROM development_pipeline
      WHERE (building_id = NEW.building_id OR address = NEW.address)
        AND status = 'transfer'
        AND id NOT IN (SELECT pipeline_id FROM development_pipeline_history WHERE to_status = 'transfer' AND created_at > NOW() - INTERVAL '1 minute');
    ELSE
      -- ОСС провалено - снижаем probability
      UPDATE development_pipeline
      SET probability = GREATEST(0, probability - 20), updated_at = NOW()
      WHERE (building_id = NEW.building_id OR address = NEW.address)
        AND status = 'voting';
      
      -- Откатываем в preparation
      UPDATE development_pipeline
      SET status = 'preparation', updated_at = NOW()
      WHERE (building_id = NEW.building_id OR address = NEW.address)
        AND status = 'voting';
      
      -- Логируем откат
      INSERT INTO development_pipeline_history (pipeline_id, from_status, to_status, reason, triggered_by)
      SELECT id, 'voting', 'preparation', 
             format('Автоматический откат: ОСС провалено (кворум %.1f%%)', quorum_percent), 
             'auto'
      FROM development_pipeline
      WHERE (building_id = NEW.building_id OR address = NEW.address)
        AND status = 'preparation'
        AND id NOT IN (SELECT pipeline_id FROM development_pipeline_history WHERE to_status = 'preparation' AND created_at > NOW() - INTERVAL '1 minute');
    END IF;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_auto_handle_oss_completion ON development_oss_sessions;
CREATE TRIGGER trigger_auto_handle_oss_completion
  AFTER UPDATE ON development_oss_sessions
  FOR EACH ROW
  WHEN (NEW.status = 'completed' AND OLD.status != 'completed')
  EXECUTE FUNCTION auto_handle_oss_completion();