94 lines
4.8 KiB
SQL
Executable File
94 lines
4.8 KiB
SQL
Executable File
-- Миграция: добавление полей для отслеживания производительности и просрочек
|
||
-- Выполнить: psql -d mkd_control_center -f migrate_add_performance_tracking.sql
|
||
|
||
-- Добавляем поля в таблицу applications, если их ещё нет
|
||
DO $$
|
||
BEGIN
|
||
-- Добавляем building_id, если его нет
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'applications' AND column_name = 'building_id'
|
||
) THEN
|
||
ALTER TABLE applications ADD COLUMN building_id VARCHAR(50) REFERENCES buildings(id) ON DELETE SET NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_applications_building ON applications(building_id);
|
||
END IF;
|
||
|
||
-- Добавляем employee_id, если его нет
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'applications' AND column_name = 'employee_id'
|
||
) THEN
|
||
ALTER TABLE applications ADD COLUMN employee_id VARCHAR(50) REFERENCES employees(id) ON DELETE SET NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_applications_employee ON applications(employee_id);
|
||
END IF;
|
||
|
||
-- Добавляем is_overdue, если его нет
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'applications' AND column_name = 'is_overdue'
|
||
) THEN
|
||
ALTER TABLE applications ADD COLUMN is_overdue BOOLEAN NOT NULL DEFAULT FALSE;
|
||
CREATE INDEX IF NOT EXISTS idx_applications_overdue ON applications(is_overdue);
|
||
END IF;
|
||
|
||
-- Добавляем updated_at, если его нет
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'applications' AND column_name = 'updated_at'
|
||
) THEN
|
||
ALTER TABLE applications ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
|
||
END IF;
|
||
|
||
-- Создаём индексы, если их ещё нет
|
||
CREATE INDEX IF NOT EXISTS idx_applications_performer ON applications(performer_name);
|
||
CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status);
|
||
CREATE INDEX IF NOT EXISTS idx_applications_deadline ON applications(deadline_at);
|
||
END $$;
|
||
|
||
-- Создаём таблицы для статистики производительности, если их ещё нет
|
||
CREATE TABLE IF NOT EXISTS employee_performance_stats (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
employee_name TEXT NOT NULL,
|
||
period_start DATE NOT NULL,
|
||
period_end DATE NOT NULL,
|
||
total_assigned INTEGER NOT NULL DEFAULT 0,
|
||
total_completed INTEGER NOT NULL DEFAULT 0,
|
||
total_overdue INTEGER NOT NULL DEFAULT 0,
|
||
total_in_progress INTEGER NOT NULL DEFAULT 0,
|
||
total_deferred INTEGER NOT NULL DEFAULT 0,
|
||
completion_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
||
overdue_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
||
performance_score NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
||
district_id VARCHAR(50) REFERENCES districts(id) ON DELETE SET NULL,
|
||
calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
UNIQUE(employee_name, period_start, period_end)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_performance_stats_employee ON employee_performance_stats(employee_name);
|
||
CREATE INDEX IF NOT EXISTS idx_performance_stats_district ON employee_performance_stats(district_id);
|
||
CREATE INDEX IF NOT EXISTS idx_performance_stats_period ON employee_performance_stats(period_start, period_end);
|
||
CREATE INDEX IF NOT EXISTS idx_performance_stats_score ON employee_performance_stats(performance_score DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS district_performance_stats (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
district_id VARCHAR(50) NOT NULL REFERENCES districts(id) ON DELETE CASCADE,
|
||
period_start DATE NOT NULL,
|
||
period_end DATE NOT NULL,
|
||
total_applications INTEGER NOT NULL DEFAULT 0,
|
||
total_completed INTEGER NOT NULL DEFAULT 0,
|
||
total_overdue INTEGER NOT NULL DEFAULT 0,
|
||
total_in_progress INTEGER NOT NULL DEFAULT 0,
|
||
completion_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
||
overdue_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
||
average_score NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
||
calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
UNIQUE(district_id, period_start, period_end)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_district_stats_district ON district_performance_stats(district_id);
|
||
CREATE INDEX IF NOT EXISTS idx_district_stats_period ON district_performance_stats(period_start, period_end);
|
||
|
||
RAISE NOTICE 'Миграция завершена: добавлены поля для отслеживания производительности';
|