Загрузка данных

-- Заголовок реестра УНВ
CREATE TABLE report.unv_registry (
   id              serial          PRIMARY KEY,
   registry_number varchar(50)     NOT NULL,
   registry_date   date            NOT NULL DEFAULT CURRENT_DATE,
   tax_period_year smallint        NOT NULL,
   sent_to_lkk_date date           NULL,
   sent_to_fns_date date           NULL,
   xml_document    bytea           NULL,
   fns_response    bytea           NULL,
   status          varchar(50)     NOT NULL DEFAULT 'FORMED',
   created_at      timestamp       NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at      timestamp       NOT NULL DEFAULT CURRENT_TIMESTAMP,

   CONSTRAINT unv_registry_status_check CHECK (
       status IN (
           'FORMED',        
           'SENT_LKK',      
           'ACCEPTED_LKK',  
           'SENT_FNS',      
           'ACCEPTED_FNS',  
           'FNS_RESPONSE',  
           'DELETED'        
       )
   )
);

COMMENT ON TABLE report.unv_registry IS 
   'Реестры на упрощённый налоговый вычет (УНВ). 
    Формируются ежегодно с 20 января за 3 предыдущих года.
    Путь в системе: Персонифицированный учет → Отчетность → ФНС → Реестр на УНВ';

COMMENT ON COLUMN report.unv_registry.id IS 
   'Внутренний идентификатор реестра (PK)';

COMMENT ON COLUMN report.unv_registry.registry_number IS 
   'Порядковый номер реестра. Отображается в журнале реестров';

COMMENT ON COLUMN report.unv_registry.registry_date IS 
   'Дата создания реестра. Формат отображения DD.MM.YYYY';

COMMENT ON COLUMN report.unv_registry.tax_period_year IS 
   'Налоговый период (год), за который собраны сведения.
    Принимает значения: текущий год - 1, 2 или 3.
    Например, если реестр формируется в 2028 году — значения 2025, 2026, 2027';

COMMENT ON COLUMN report.unv_registry.sent_to_lkk_date IS 
   'Дата отправки реестра в Личный кабинет клиента (ЛКК).
    Заполняется при нажатии кнопки "Отправить в ЛКК".
    NULL — реестр ещё не отправлен в ЛКК';

COMMENT ON COLUMN report.unv_registry.sent_to_fns_date IS 
   'Дата отправки реестра в ФНС через API ГНИВЦ.
    Заполняется при нажатии кнопки "Отправить в ФНС".
    NULL — реестр ещё не отправлен в ФНС';

COMMENT ON COLUMN report.unv_registry.xml_document IS 
   'Сформированный XML-файл для ФНС в бинарном виде.
    Для договоров ПДС — формат по Приложению 3 (КНД 1184070).
    Для договоров НПО — формат по Приложению 4 (КНД 1184069).
    Отображается в журнале как иконка-ссылка для скачивания';

COMMENT ON COLUMN report.unv_registry.fns_response IS 
   'Ответ ФНС в бинарном виде.
    Содержит файл с ошибками при наличии таковых.
    Отображается в журнале как ссылка на файл с ошибками';

COMMENT ON COLUMN report.unv_registry.status IS 
   'Текущий статус реестра. Возможные значения:
    FORMED        — Сформирован (после регламентной задачи);
    SENT_LKK      — Отправлен в ЛКК (после нажатия кнопки);
    ACCEPTED_LKK  — Принят в ЛКК (после обработки данных из ЛКК);
    SENT_FNS      — Отправлен в ФНС (после нажатия кнопки);
    ACCEPTED_FNS  — Принят в ФНС (после успешной обработки);
    FNS_RESPONSE  — Ответ ФНС (успешно или с ошибками);
    DELETED       — Удалён (доступно только для роли администратора)';

COMMENT ON COLUMN report.unv_registry.created_at IS 
   'Служебное поле. Дата и время создания записи';

COMMENT ON COLUMN report.unv_registry.updated_at IS 
   'Служебное поле. Дата и время последнего обновления записи';


-- Строки реестра (клиенты/договоры)
CREATE TABLE report.unv_registry_item (
   id                   serial         PRIMARY KEY,
   registry_id          int            NOT NULL,
   client_id            int            NOT NULL,
   snils                varchar(14)    NOT NULL,
   contract_id          int            NOT NULL,
   contract_type        varchar(3)     NOT NULL,
   tax_period_year      smallint       NOT NULL,
   contributions_amount numeric(15,2)  NOT NULL DEFAULT 0,
   has_fns_lk           smallint       NULL,
   last_sent_date       date           NULL,
   error_code           varchar(50)    NULL,
   error_description    text           NULL,
   error_status         varchar(20)    NULL,
   created_at           timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at           timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,

   CONSTRAINT fk_unv_registry
       FOREIGN KEY (registry_id)
       REFERENCES report.unv_registry(id),

   CONSTRAINT unv_item_contract_type_check CHECK (
       contract_type IN ('NPO', 'PDS')
   ),

   CONSTRAINT unv_item_has_fns_lk_check CHECK (
       has_fns_lk IN (0, 1, 99) OR has_fns_lk IS NULL
   ),

   CONSTRAINT unv_item_error_status_check CHECK (
       error_status IN ('NOT_FIXED', 'FIXED') OR error_status IS NULL
   )
);

COMMENT ON TABLE report.unv_registry_item IS 
   'Строки реестра УНВ — договоры клиентов, включённые в реестр.
    Одна запись = один договор (НПО или ПДС) за один налоговый период.
    Пример: 1 договор ПДС + 1 договор НПО за 3 года = 6 записей по клиенту';

COMMENT ON COLUMN report.unv_registry_item.id IS 
   'Внутренний идентификатор строки реестра (PK)';

COMMENT ON COLUMN report.unv_registry_item.registry_id IS 
   'Ссылка на заголовок реестра (FK → report.unv_registry.id)';

COMMENT ON COLUMN report.unv_registry_item.client_id IS 
   'Ссылка на карточку физического лица в системе.
    Отображается в журнале как ФИО-ссылка на карточку ФЛ';

COMMENT ON COLUMN report.unv_registry_item.snils IS 
   'СНИЛС клиента. Берётся из блока "Персональные данные" карточки ФЛ.
    Обязателен для попадания в реестр — без СНИЛС договор не отбирается';

COMMENT ON COLUMN report.unv_registry_item.contract_id IS 
   'Ссылка на договор НПО или ПДС.
    Отображается в журнале как номер договора-ссылка';

COMMENT ON COLUMN report.unv_registry_item.contract_type IS 
   'Тип договора. Определяет формат XML и источник данных о взносах:
    PDS — Программа долгосрочных сбережений (мнемоника ПДС СВ, код PDS_DEPOSIT_ENROLLMENT);
    NPO — Негосударственное пенсионное обеспечение (мнемоника ИПС_ФЛ, код DEPOSIT_ENROLLMENT)';

COMMENT ON COLUMN report.unv_registry_item.tax_period_year IS 
   'Налоговый период (год) для данной строки реестра.
    Дублируется из заголовка для удобства запросов по конкретному договору';

COMMENT ON COLUMN report.unv_registry_item.contributions_amount IS 
   'Сумма личных взносов клиента за налоговый период.
    Учитываются только: эквайринг, СБП, оплата по реквизитам.
    НЕ учитываются: перевод ОПС в ПДС, бонусные рубли, возвраты,
    софинансирование от государства, инвестдоход, взносы юрлиц';

COMMENT ON COLUMN report.unv_registry_item.has_fns_lk IS 
   'Наличие личного кабинета ФНС у клиента. Результат запроса в API ГНИВЦ.
    Возможные значения:
    1    — есть ЛК ФНС (метод /gnivc/check_person_has_fns_lk вернул code=1);
    0    — нет ЛК ФНС (code=0), карточка в ЛКК получает статус 
           "Зарегистрируйтесь в личном кабинете налогоплательщика";
    99   — проверка не успешна (code=99), требуется повторный запрос;
    NULL — запрос в API ФНС ещё не выполнялся по данному клиенту';

COMMENT ON COLUMN report.unv_registry_item.last_sent_date IS 
   'Дата последней отправки XML-файла по данному договору в API ФНС.
    NULL — XML по этому договору в этом году ещё не отправлялся';

COMMENT ON COLUMN report.unv_registry_item.error_code IS 
   'Код ошибки от API ФНС. Возможные значения:
    01                        — физлицо не идентифицировано;
    02                        — есть дата смерти физлица;
    07                        — нет личного кабинета налогоплательщика;
    13                        — нет сведений о договоре НПО;
    14                        — нет сведений о договоре ПДС;
    15                        — нет сведений о Фонде;
    application.xsd.failed   — ошибка валидации XML;
    application.xsd.signature.failed — ошибка подписи КЭП';

COMMENT ON COLUMN report.unv_registry_item.error_description IS 
   'Расшифровка кода ошибки в человекочитаемом виде.
    Например: для кода 02 — "Есть дата смерти физ. лица"';

COMMENT ON COLUMN report.unv_registry_item.error_status IS 
   'Статус обработки ошибки администратором. Возможные значения:
    NOT_FIXED — ошибка получена от ФНС, не исправлена, повторная отправка не выполнена;
    FIXED     — ошибка исправлена администратором, система автоматически 
                повторит отправку XML в API ФНС;
    NULL      — ошибок не было';

COMMENT ON COLUMN report.unv_registry_item.created_at IS 
   'Служебное поле. Дата и время создания записи';

COMMENT ON COLUMN report.unv_registry_item.updated_at IS 
   'Служебное поле. Дата и время последнего обновления записи';


-- Индексы
CREATE INDEX idx_unv_registry_status 
   ON report.unv_registry(status);
COMMENT ON INDEX idx_unv_registry_status IS 
   'Фильтрация журнала реестров по статусу';

CREATE INDEX idx_unv_registry_tax_period 
   ON report.unv_registry(tax_period_year);
COMMENT ON INDEX idx_unv_registry_tax_period IS 
   'Фильтрация журнала реестров по налоговому периоду';

CREATE INDEX idx_unv_registry_item_registry_id 
   ON report.unv_registry_item(registry_id);
COMMENT ON INDEX idx_unv_registry_item_registry_id IS 
   'Быстрая выборка строк реестра при открытии детализации в журнале';

CREATE INDEX idx_unv_registry_item_contract 
   ON report.unv_registry_item(contract_id, tax_period_year);
COMMENT ON INDEX idx_unv_registry_item_contract IS 
   'Поиск записей по договору и году — используется при повторной отправке XML';

CREATE INDEX idx_unv_registry_item_snils 
   ON report.unv_registry_item(snils);
COMMENT ON INDEX idx_unv_registry_item_snils IS 
   'Поиск клиента по СНИЛС при проверке наличия ЛК ФНС';

CREATE INDEX idx_unv_registry_item_error_status
   ON report.unv_registry_item(error_status)
   WHERE error_status = 'NOT_FIXED';
COMMENT ON INDEX idx_unv_registry_item_error_status IS 
   'Быстрая выборка неисправленных ошибок для отправки уведомлений администратору';