-- ====================================================================
-- УНИВЕРСАЛЕН CLEANUP СКРИПТ С FK CONSTRAINT FIX
-- Версия: FINAL - Работи без грешки
-- Автор: GitHub Copilot 
-- Дата: 16.11.2025
-- ====================================================================
-- УПОТРЕБА: 
-- 1. Замени "dd.mm.yyyy" с желаната дата
-- 2. Конвертирай в Windows-1251 encoding
-- 3. Стартирай със: isql -u SYSDBA -p PASSWORD database.fdb -i script.sql
-- ====================================================================
-- ПРИМЕРИ:
-- "31.12.2020" - изтрива всички данни до края на 2020
-- "31.12.2021" - изтрива всички данни до края на 2021  
-- "31.12.2023" - изтрива всички данни до края на 2023
-- ====================================================================

SET ECHO ON;
SET AUTODDL ON;
SET BAIL ON;

-- Задаване на датата за cleanup (ПРОМЕНИ ТАЗИ ДАТА!)
SELECT RDB$SET_CONTEXT('USER_SESSION', 'CLEANUP_DATE', 'dd.mm.yyyy') FROM RDB$DATABASE;

-- Показване на информацията за cleanup
SELECT 'CLEANUP ДО ДАТА: ' || RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS CLEANUP_INFO FROM RDB$DATABASE;

-- Статистика преди cleanup
SELECT 'ПРЕДИ CLEANUP - ОБЩО РЕЗЕРВАЦИИ' AS INFO, COUNT(*) AS COUNT_VALUE FROM RESERV_DETAIL;

SELECT 'ПРЕДИ CLEANUP - ЗА ИЗТРИВАНЕ' AS INFO, COUNT(*) AS COUNT_VALUE 
FROM RESERV_DETAIL 
WHERE FROM_DATE <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE);

-- ====================================================================
-- СТЪПКА 1: FK CONSTRAINT FIX - NULLIFY ID_TITULAR
-- Решава проблема с FK_RD_TITULAR constraint
-- ====================================================================

UPDATE RESERV_DETAIL 
SET ID_TITULAR = NULL
WHERE FROM_DATE <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE)
  AND ID_TITULAR IS NOT NULL;

SELECT 'FK FIX - NULLIFIED ROWS' AS INFO, COUNT(*) AS COUNT_VALUE
FROM RESERV_DETAIL 
WHERE FROM_DATE <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE)
  AND ID_TITULAR IS NULL;

COMMIT;

-- ====================================================================
-- СТЪПКА 2: ИЗТРИВАНЕ НА ACCOUNT_POST ЗАПИСИ
-- ====================================================================

DELETE FROM ACCOUNT_POST 
WHERE DATE_POST <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE)
  AND ID_BASE_DOC IS NULL;

SELECT 'ACCOUNT_POST - ИЗТРИТИ' AS INFO, 'НЕИЗВЕСТЕН БРОЙ' AS COUNT_VALUE FROM RDB$DATABASE;

COMMIT;

-- ====================================================================
-- СТЪПКА 3: ИЗТРИВАНЕ НА RESERV_DETAIL ЗАПИСИ 
-- Основната операция - изтриване на резервациите
-- ====================================================================

DELETE FROM RESERV_DETAIL 
WHERE FROM_DATE <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE);

SELECT 'RESERV_DETAIL - ИЗТРИТИ' AS INFO, 'ВСИЧКИ ДО ДАТАТА' AS COUNT_VALUE FROM RDB$DATABASE;

COMMIT;

-- ====================================================================
-- ФИНАЛНА СТАТИСТИКА И ПРОВЕРКА
-- ====================================================================

SELECT 'СЛЕД CLEANUP - ОБЩО РЕЗЕРВАЦИИ' AS INFO, COUNT(*) AS COUNT_VALUE FROM RESERV_DETAIL;

SELECT 'ПРОВЕРКА - ОСТАНАЛИ СТАРИ' AS INFO, COUNT(*) AS COUNT_VALUE 
FROM RESERV_DETAIL 
WHERE FROM_DATE <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE);

SELECT 'ОБЩО GUEST ЗАПИСИ' AS INFO, COUNT(*) AS COUNT_VALUE FROM GUEST;

SELECT 'ОБЩО ACCOUNT_POST ЗАПИСИ' AS INFO, COUNT(*) AS COUNT_VALUE FROM ACCOUNT_POST;

-- Финален статус
SELECT CASE 
    WHEN (SELECT COUNT(*) FROM RESERV_DETAIL WHERE FROM_DATE <= CAST(RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE') AS DATE)) = 0 
    THEN 'CLEANUP ЗАВЪРШЕН УСПЕШНО! ДО ' || RDB$GET_CONTEXT('USER_SESSION', 'CLEANUP_DATE')
    ELSE 'ВНИМАНИЕ: ОСТАНАЛИ СА СТАРИ ЗАПИСИ!'
END AS FINAL_STATUS
FROM RDB$DATABASE;

SELECT '' AS EMPTY_LINE FROM RDB$DATABASE;
SELECT '=== ПРЕПОРЪКИ ===' AS RECOMMENDATIONS FROM RDB$DATABASE;
SELECT '1. Стартирай: gfix -sweep database.fdb' AS RECOMMENDATION_1 FROM RDB$DATABASE;
SELECT '2. Направи backup: gbak -backup database.fdb backup.gbk' AS RECOMMENDATION_2 FROM RDB$DATABASE;
SELECT '3. Проверка: gstat -header database.fdb' AS RECOMMENDATION_3 FROM RDB$DATABASE;

SELECT '' AS EMPTY_LINE FROM RDB$DATABASE;
SELECT 'CLEANUP SCRIPT ЗАВЪРШЕН!' AS FINAL_MESSAGE FROM RDB$DATABASE;

EXIT;