132 lines
6.3 KiB
PL/PgSQL
Executable File
132 lines
6.3 KiB
PL/PgSQL
Executable File
-- Миграция для автоматизации воронки развития
|
||
|
||
-- Таблица истории переходов
|
||
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();
|