Система архивации данных в PostgreSQL: автоматическое сохранение удаленных записей с помощью триггеров

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department_id INTEGER,
    position_title VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    termination_date DATE,
    termination_reason VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    
    -- Индексы для производительности
    INDEX idx_employees_active (is_active),
    INDEX idx_employees_department (department_id),
    INDEX idx_employees_salary (salary)
);

-- Тестовые данные для демонстрации
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id, position_title) VALUES
('Иван', 'Петров', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2020-03-15', 75000, 1, 'Разработчик'),
('Мария', 'Сидорова', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2019-07-22', 82000, 2, 'Менеджер проекта'),
('Алексей', 'Кузнецов', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2021-01-10', 68000, 1, 'Тестировщик'),
('Ольга', 'Васильева', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2018-11-05', 95000, 3, 'Руководитель отдела'),
('Дмитрий', 'Смирнов', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2022-06-30', 58000, 2, 'Аналитик'),
('Екатерина', 'Попова', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2023-09-12', 62000, 1, 'Разработчик'),
('Сергей', 'Новиков', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2017-12-01', 105000, 3, 'Директор департамента'),
('Анна', 'Морозова', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', '2020-08-18', 71000, 2, 'Менеджер по продукту');

-- Просмотр текущих данных
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    salary,
    department_id,
    position_title,
    is_active
FROM employees
ORDER BY employee_id;

-- Таблица архива с дополнительными метаданными
CREATE TABLE employees_archive (
    -- Технические поля архива
    archive_id BIGSERIAL PRIMARY KEY,
    archived_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    archived_by VARCHAR(100) NOT NULL DEFAULT CURRENT_USER,
    archive_reason VARCHAR(200),
    original_table VARCHAR(100) NOT NULL DEFAULT 'employees',
    operation_id UUID DEFAULT gen_random_uuid(),
    
    -- Данные из исходной таблицы (полная копия)
    employee_id INTEGER NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department_id INTEGER,
    position_title VARCHAR(100),
    is_active BOOLEAN,
    termination_date DATE,
    termination_reason VARCHAR(200),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    
    -- Дополнительные поля для аналитики
    years_of_service DECIMAL(5,2) GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM AGE(archived_at, hire_date)) + 
        EXTRACT(MONTH FROM AGE(archived_at, hire_date)) / 12.0
    ) STORED,
    salary_category VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN salary < 50000 THEN 'Низкая'
            WHEN salary BETWEEN 50000 AND 100000 THEN 'Средняя'
            WHEN salary > 100000 THEN 'Высокая'
            ELSE 'Не определена'
        END
    ) STORED,
    
    -- Индексы для эффективного поиска
    INDEX idx_archive_employee_id (employee_id),
    INDEX idx_archive_archived_at (archived_at DESC),
    INDEX idx_archive_department (department_id),
    INDEX idx_archive_salary (salary),
    INDEX idx_archive_archived_by (archived_by),
    INDEX idx_archive_reason (archive_reason),
    
    -- Ограничения для целостности данных
    CONSTRAINT chk_archive_salary_positive CHECK (salary > 0),
    CONSTRAINT chk_archive_dates CHECK (hire_date <= archived_at),
    
    -- Комментарии для документации
    CONSTRAINT employees_archive_pkey PRIMARY KEY (archive_id)
);

COMMENT ON TABLE employees_archive IS 'Архив удаленных записей из таблицы employees';
COMMENT ON COLUMN employees_archive.archive_id IS 'Уникальный идентификатор архивной записи';
COMMENT ON COLUMN employees_archive.archived_at IS 'Дата и время архивации записи';
COMMENT ON COLUMN employees_archive.archived_by IS 'Пользователь, выполнивший архивацию';
COMMENT ON COLUMN employees_archive.archive_reason IS 'Причина архивации (опционально)';
COMMENT ON COLUMN employees_archive.operation_id IS 'Уникальный идентификатор операции удаления';
COMMENT ON COLUMN employees_archive.years_of_service IS 'Стаж работы на момент архивации (вычисляемое поле)';
COMMENT ON COLUMN employees_archive.salary_category IS 'Категория зарплаты (вычисляемое поле)';

CREATE OR REPLACE FUNCTION archive_deleted_employee()
RETURNS TRIGGER 
LANGUAGE plpgsql
SECURITY DEFINER  -- Выполняется с правами создателя
AS $$
DECLARE
    v_archive_reason TEXT;
    v_context JSONB;
BEGIN
    -- Определяем причину архивации на основе контекста
    v_archive_reason := CASE
        WHEN OLD.is_active = FALSE AND OLD.termination_date IS NOT NULL THEN
            'Увольнение сотрудника: ' || COALESCE(OLD.termination_reason, 'Причина не указана')
        WHEN OLD.salary < 20000 THEN
            'Удаление сотрудника с низкой зарплатой'
        WHEN TG_NARGS > 0 THEN
            TG_ARGV[0]  -- Причина передана как аргумент триггера
        ELSE
            'Регулярная архивация данных'
    END;
    
    -- Собираем дополнительный контекст операции
    v_context := jsonb_build_object(
        'client_address', inet_client_addr(),
        'application_name', current_setting('application_name'),
        'transaction_id', txid_current(),
        'session_user', session_user,
        'current_database', current_database()
    );
    
    -- Вставляем данные в архив с дополнительной информацией
    INSERT INTO employees_archive (
        -- Метаданные архива
        archived_by,
        archive_reason,
        operation_id,
        
        -- Данные сотрудника
        employee_id,
        first_name,
        last_name,
        email,
        hire_date,
        salary,
        department_id,
        position_title,
        is_active,
        termination_date,
        termination_reason,
        created_at,
        updated_at,
        
        -- Дополнительное поле для контекста (можно хранить как JSON)
        archive_context
    ) VALUES (
        -- Метаданные (current_user уже установлен по умолчанию)
        COALESCE(current_user, 'system'),
        v_archive_reason,
        gen_random_uuid(),
        
        -- Данные из удаляемой записи
        OLD.employee_id,
        OLD.first_name,
        OLD.last_name,
        OLD.email,
        OLD.hire_date,
        OLD.salary,
        OLD.department_id,
        OLD.position_title,
        OLD.is_active,
        OLD.termination_date,
        OLD.termination_reason,
        OLD.created_at,
        OLD.updated_at,
        
        -- Контекст операции
        v_context
    );
    
    -- Логирование успешной архивации
    RAISE NOTICE 'Запись сотрудника % (%) успешно заархивирована. Причина: %',
        OLD.employee_id,
        OLD.first_name || ' ' || OLD.last_name,
        v_archive_reason;
    
    -- Возвращаем оригинальную запись для удаления
    RETURN OLD;
    
EXCEPTION
    WHEN OTHERS THEN
        -- В случае ошибки логируем и позволяем продолжить операцию
        RAISE WARNING 'Ошибка при архивации записи %: %', OLD.employee_id, SQLERRM;
        
        -- Можно также записать в таблицу ошибок
        INSERT INTO archive_errors (
            error_time,
            employee_id,
            error_message,
            operation_context
        ) VALUES (
            CURRENT_TIMESTAMP,
            OLD.employee_id,
            SQLERRM,
            v_context
        );
        
        -- Все равно разрешаем удаление, чтобы не блокировать операции
        RETURN OLD;
END;
$$;

-- Создадим таблицу для ошибок архивации
CREATE TABLE IF NOT EXISTS archive_errors (
    error_id BIGSERIAL PRIMARY KEY,
    error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    employee_id INTEGER,
    table_name VARCHAR(100),
    error_message TEXT,
    operation_context JSONB,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_at TIMESTAMP,
    resolved_by VARCHAR(100)
);

COMMENT ON FUNCTION archive_deleted_employee() IS 
'Функция для автоматической архивации удаленных записей из таблицы employees.
Сохраняет полную копию данных с метаданными об операции удаления.';

-- Создание триггера для автоматической архивации
CREATE TRIGGER trg_archive_on_delete
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION archive_deleted_employee();

-- Альтернативный вариант с возможностью передачи причины
CREATE OR REPLACE TRIGGER trg_archive_on_delete_with_reason
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION archive_deleted_employee('Удаление по запросу пользователя');

-- Проверка создания триггера
SELECT 
    tgname AS trigger_name,
    tgtype::integer & 4 > 0 AS "delete_trigger",
    tgenabled AS enabled,
    pg_get_triggerdef(oid) AS trigger_definition
FROM pg_trigger
WHERE tgrelid = 'employees'::regclass
  AND tgname LIKE '%archive%';

-- Просмотр данных перед удалением
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    salary,
    is_active
FROM employees
WHERE employee_id = 3;

-- Удаление сотрудника с ID 3
BEGIN;
    DELETE FROM employees WHERE employee_id = 3;
    SELECT 'Удален сотрудник: ' || first_name || ' ' || last_name 
    FROM employees_archive 
    WHERE employee_id = 3 
    ORDER BY archived_at DESC 
    LIMIT 1;
COMMIT;

-- Проверка архивной записи
SELECT 
    archive_id,
    employee_id,
    first_name || ' ' || last_name AS full_name,
    salary,
    archived_at,
    archived_by,
    archive_reason,
    years_of_service
FROM employees_archive
WHERE employee_id = 3
ORDER BY archived_at DESC;

-- Удаление неактивных сотрудников с низкой зарплатой
BEGIN;
    -- Сначала отмечаем их как неактивных (если еще не отмечены)
    UPDATE employees 
    SET is_active = FALSE,
        termination_date = CURRENT_DATE,
        termination_reason = 'Сокращение штата'
    WHERE salary < 60000 
      AND is_active = TRUE;
    
    -- Теперь удаляем с архивацией
    DELETE FROM employees 
    WHERE salary < 60000 
      AND is_active = FALSE
    RETURNING 
        employee_id,
        first_name || ' ' || last_name AS employee_name,
        salary;
COMMIT;

-- Статистика по архивным записям
SELECT 
    archive_reason,
    COUNT(*) AS records_count,
    AVG(salary) AS avg_salary,
    MIN(archived_at) AS first_archive,
    MAX(archived_at) AS last_archive
FROM employees_archive
WHERE archive_reason LIKE '%низкой зарплатой%'
   OR archive_reason LIKE '%сокращение%'
GROUP BY archive_reason
ORDER BY records_count DESC;

-- Временное изменение триггера для передачи причины
CREATE OR REPLACE FUNCTION archive_with_custom_reason()
RETURNS TRIGGER AS $$
BEGIN
    -- Используем TG_ARGV для получения причины из триггера
    PERFORM archive_deleted_employee(TG_ARGV[0]);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- Создание триггера с причиной
DROP TRIGGER IF EXISTS trg_custom_reason ON employees;

CREATE TRIGGER trg_custom_reason
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION archive_with_custom_reason('Реорганизация отдела продаж');

-- Удаление с кастомной причиной
DELETE FROM employees 
WHERE department_id = 2
  AND hire_date < '2022-01-01';

-- Проверка архивных записей с указанной причиной
SELECT 
    employee_id,
    first_name || ' ' || last_name AS name,
    department_id,
    archive_reason,
    archived_at
FROM employees_archive
WHERE archive_reason = 'Реорганизация отдела продаж'
ORDER BY archived_at DESC;

CREATE OR REPLACE FUNCTION restore_from_archive(
    p_archive_id BIGINT,
    p_restore_reason TEXT DEFAULT 'Восстановление по запросу пользователя'
)
RETURNS TABLE (
    restored_id INTEGER,
    employee_name TEXT,
    restore_status TEXT
) 
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_employee_record employees_archive%ROWTYPE;
    v_new_employee_id INTEGER;
BEGIN
    -- Получаем архивную запись
    SELECT * INTO v_employee_record
    FROM employees_archive
    WHERE archive_id = p_archive_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Архивная запись с ID % не найдена', p_archive_id;
    END IF;
    
    -- Проверяем, не существует ли уже сотрудник с таким email
    IF EXISTS (
        SELECT 1 
        FROM employees 
        WHERE email = v_employee_record.email
    ) THEN
        -- Обновляем email, чтобы избежать конфликта уникальности
        v_employee_record.email := 'restored_' || v_employee_record.employee_id || '_' || v_employee_record.email;
    END IF;
    
    -- Восстанавливаем запись в основную таблицу
    INSERT INTO employees (
        first_name,
        last_name,
        email,
        hire_date,
        salary,
        department_id,
        position_title,
        is_active,
        termination_date,
        termination_reason,
        created_at,
        updated_at
    ) VALUES (
        v_employee_record.first_name,
        v_employee_record.last_name,
        v_employee_record.email,
        v_employee_record.hire_date,
        v_employee_record.salary,
        v_employee_record.department_id,
        v_employee_record.position_title,
        COALESCE(v_employee_record.is_active, TRUE),
        v_employee_record.termination_date,
        v_employee_record.termination_reason,
        v_employee_record.created_at,
        CURRENT_TIMESTAMP
    )
    RETURNING employee_id INTO v_new_employee_id;
    
    -- Логируем операцию восстановления
    INSERT INTO restore_log (
        archive_id,
        original_employee_id,
        new_employee_id,
        restored_by,
        restore_reason,
        restored_at
    ) VALUES (
        p_archive_id,
        v_employee_record.employee_id,
        v_new_employee_id,
        CURRENT_USER,
        p_restore_reason,
        CURRENT_TIMESTAMP
    );
    
    -- Возвращаем результат
    RETURN QUERY
    SELECT 
        v_new_employee_id,
        v_employee_record.first_name || ' ' || v_employee_record.last_name,
        'Успешно восстановлен'::TEXT;
    
    -- Уведомление
    RAISE NOTICE 'Сотрудник % (%) восстановлен из архива. Новый ID: %',
        v_employee_record.first_name || ' ' || v_employee_record.last_name,
        v_employee_record.employee_id,
        v_new_employee_id;
        
EXCEPTION
    WHEN OTHERS THEN
        RETURN QUERY
        SELECT 
            NULL::INTEGER,
            NULL::TEXT,
            'Ошибка: ' || SQLERRM;
END;
$$;

-- Таблица для логов восстановления
CREATE TABLE restore_log (
    log_id BIGSERIAL PRIMARY KEY,
    archive_id BIGINT NOT NULL REFERENCES employees_archive(archive_id),
    original_employee_id INTEGER NOT NULL,
    new_employee_id INTEGER NOT NULL REFERENCES employees(employee_id),
    restored_by VARCHAR(100) DEFAULT CURRENT_USER,
    restore_reason TEXT,
    restored_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_restore_archive (archive_id),
    INDEX idx_restore_new_employee (new_employee_id)
);

-- Пример восстановления
SELECT * FROM restore_from_archive(1, 'Ошибочное удаление');

-- Статистика удалений по месяцам
SELECT 
    DATE_TRUNC('month', archived_at) AS month,
    COUNT(*) AS deletions_count,
    AVG(salary) AS avg_salary_deleted,
    SUM(salary) AS total_salary_deleted,
    AVG(years_of_service) AS avg_service_years,
    STRING_AGG(DISTINCT archived_by, ', ') AS users_performed_deletion
FROM employees_archive
WHERE archived_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', archived_at)
ORDER BY month DESC;

-- Аналитика по причинам удаления
SELECT 
    archive_reason,
    COUNT(*) AS total_deleted,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage,
    AVG(salary) AS avg_salary,
    MIN(archived_at) AS first_deletion,
    MAX(archived_at) AS last_deletion
FROM employees_archive
GROUP BY archive_reason
HAVING COUNT(*) > 0
ORDER BY total_deleted DESC;

-- Текучесть кадров по отделам
SELECT 
    department_id,
    COUNT(*) AS employees_deleted,
    AVG(years_of_service) AS avg_service_before_leaving,
    AVG(salary) AS avg_salary_before_leaving,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY years_of_service) AS median_service_years
FROM employees_archive
WHERE archive_reason LIKE '%увольнение%'
   OR archive_reason LIKE '%сокращение%'
GROUP BY department_id
ORDER BY employees_deleted DESC;

-- Создание партиционированной таблицы архива по месяцам
CREATE TABLE employees_archive_partitioned (
    LIKE employees_archive INCLUDING ALL
) PARTITION BY RANGE (archived_at);

-- Создание партиций на год вперед
CREATE TABLE employees_archive_2024_01 PARTITION OF employees_archive_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE employees_archive_2024_02 PARTITION OF employees_archive_partitioned
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Функция для автоматического создания партиций
CREATE OR REPLACE FUNCTION create_archive_partitions()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_month DATE;
    v_partition_name TEXT;
    v_sql TEXT;
BEGIN
    FOR i IN 0..11 LOOP
        v_month := DATE_TRUNC('month', CURRENT_DATE + (i || ' months')::INTERVAL);
        v_partition_name := 'employees_archive_' || 
                           EXTRACT(YEAR FROM v_month) || '_' || 
                           LPAD(EXTRACT(MONTH FROM v_month)::TEXT, 2, '0');
        
        -- Проверяем, существует ли уже партиция
        IF NOT EXISTS (
            SELECT 1 
            FROM pg_tables 
            WHERE tablename = v_partition_name
        ) THEN
            v_sql := format(
                'CREATE TABLE %I PARTITION OF employees_archive_partitioned
                FOR VALUES FROM (%L) TO (%L)',
                v_partition_name,
                v_month,
                v_month + INTERVAL '1 month'
            );
            
            EXECUTE v_sql;
            RAISE NOTICE 'Создана партиция: %', v_partition_name;
        END IF;
    END LOOP;
END;
$$;

-- Запуск создания партиций
SELECT create_archive_partitions();

-- Безопасное удаление архивных записей старше 7 лет
CREATE OR REPLACE PROCEDURE cleanup_old_archive_data(
    p_retention_years INTEGER DEFAULT 7
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_cutoff_date DATE;
    v_deleted_count INTEGER;
BEGIN
    v_cutoff_date := CURRENT_DATE - (p_retention_years || ' years')::INTERVAL;
    
    -- Сначала создаем резервную копию удаляемых данных
    CREATE TEMP TABLE old_archive_backup AS
    SELECT *
    FROM employees_archive
    WHERE archived_at < v_cutoff_date;
    
    -- Затем удаляем
    DELETE FROM employees_archive
    WHERE archived_at < v_cutoff_date;
    
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    
    -- Логируем операцию
    INSERT INTO cleanup_log (
        operation_date,
        table_name,
        cutoff_date,
        records_deleted,
        backup_created
    ) VALUES (
        CURRENT_TIMESTAMP,
        'employees_archive',
        v_cutoff_date,
        v_deleted_count,
        TRUE
    );
    
    RAISE NOTICE 'Удалено % архивных записей старше %', 
        v_deleted_count, v_cutoff_date;
        
    -- Можно сохранить временную таблицу в файл
    -- COPY old_archive_backup TO '/path/to/backup.csv' DELIMITER ',' CSV HEADER;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Ошибка при очистке архивных данных: %', SQLERRM;
        -- Автоматический откат транзакции
        ROLLBACK;
END;
$$;

-- Таблица для логов очистки
CREATE TABLE cleanup_log (
    log_id BIGSERIAL PRIMARY KEY,
    operation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    table_name VARCHAR(100),
    cutoff_date DATE,
    records_deleted INTEGER,
    backup_created BOOLEAN,
    performed_by VARCHAR(100) DEFAULT CURRENT_USER
);

-- Вызов процедуры очистки
CALL cleanup_old_archive_data(5);

-- Функция для получения архивных данных через API
CREATE OR REPLACE FUNCTION get_archive_data(
    p_employee_id INTEGER DEFAULT NULL,
    p_start_date TIMESTAMP DEFAULT NULL,
    p_end_date TIMESTAMP DEFAULT NULL,
    p_limit INTEGER DEFAULT 100,
    p_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
    archive_id BIGINT,
    employee_id INTEGER,
    full_name TEXT,
    email VARCHAR,
    salary DECIMAL,
    archived_at TIMESTAMP,
    archived_by VARCHAR,
    archive_reason TEXT,
    years_of_service DECIMAL
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.archive_id,
        a.employee_id,
        a.first_name || ' ' || a.last_name,
        a.email,
        a.salary,
        a.archived_at,
        a.archived_by,
        a.archive_reason,
        a.years_of_service
    FROM employees_archive a
    WHERE (p_employee_id IS NULL OR a.employee_id = p_employee_id)
      AND (p_start_date IS NULL OR a.archived_at >= p_start_date)
      AND (p_end_date IS NULL OR a.archived_at <= p_end_date)
    ORDER BY a.archived_at DESC
    LIMIT p_limit
    OFFSET p_offset;
END;
$$;

-- Функция для массового экспорта архива
CREATE OR REPLACE FUNCTION export_archive_to_csv(
    p_file_path TEXT,
    p_start_date TIMESTAMP DEFAULT NULL,
    p_end_date TIMESTAMP DEFAULT NULL
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER;
    v_sql TEXT;
BEGIN
    v_sql := format(
        'COPY (
            SELECT 
                archive_id,
                employee_id,
                first_name,
                last_name,
                email,
                salary,
                archived_at,
                archived_by,
                archive_reason
            FROM employees_archive
            WHERE (%L IS NULL OR archived_at >= %L)
              AND (%L IS NULL OR archived_at <= %L)
            ORDER BY archived_at
        ) TO %L WITH CSV HEADER',
        p_start_date, p_start_date,
        p_end_date, p_end_date,
        p_file_path
    );
    
    EXECUTE v_sql;
    
    -- Получаем количество экспортированных записей
    GET DIAGNOSTICS v_count = ROW_COUNT;
    
    RETURN v_count;
END;
$$;

-- Пример использования
SELECT * FROM get_archive_data(
    p_start_date => '2024-01-01',
    p_end_date => '2024-12-31',
    p_limit => 50
);

-- Экспорт данных за текущий месяц
SELECT export_archive_to_csv(
    '/tmp/archive_2024_01.csv',
    '2024-01-01',
    '2024-01-31'
);

-- Тестовая среда для проверки системы
CREATE TABLE test_employees (LIKE employees INCLUDING ALL);
CREATE TABLE test_employees_archive (LIKE employees_archive INCLUDING ALL);

-- Тестовый триггер
CREATE TRIGGER trg_test_archive
BEFORE DELETE ON test_employees
FOR EACH ROW
EXECUTE FUNCTION archive_deleted_employee();

-- Тестирование различных сценариев
BEGIN;
    INSERT INTO test_employees (...) VALUES (...);
    DELETE FROM test_employees WHERE ...;
    SELECT * FROM test_employees_archive;
ROLLBACK;

-- Регулярный мониторинг размера таблицы архива
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) - 
                   pg_relation_size(schemaname || '.' || tablename)) AS index_size,
    (SELECT COUNT(*) FROM employees_archive) AS row_count
FROM pg_tables
WHERE tablename = 'employees_archive';

-- Процедура для регулярного обслуживания
CREATE OR REPLACE PROCEDURE maintain_archive_system()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 1. Создание новых партиций
    PERFORM create_archive_partitions();
    
    -- 2. Очистка старых данных
    CALL cleanup_old_archive_data(7);
    
    -- 3. Перестроение индексов
    REINDEX TABLE employees_archive;
    
    -- 4. Анализ статистики
    ANALYZE employees_archive;
    
    -- 5. Логирование выполнения
    INSERT INTO maintenance_log (
        maintenance_type,
        performed_at,
        performed_by
    ) VALUES (
        'archive_system_maintenance',
        CURRENT_TIMESTAMP,
        CURRENT_USER
    );
    
    RAISE NOTICE 'Обслуживание системы архивации завершено';
END;
$$;

-- Настройка автоматического выполнения через pg_cron
-- (если расширение установлено)
-- SELECT cron.schedule('archive-maintenance', '0 2 * * 0', 'CALL maintain_archive_system()');