---

with

params as (

select '1970-01-01'::date as start_date

, '2026-03-31'::date + interval '1' day as end_date

)

, balance_dfa as (

select asset_id

, sum(total_amount_dfa) as total_amount_dfa

from investor_balance_dfa

group

by asset_id

)

select to_char(to_timestamp(decision.issue_date), 'dd.mm.yyyy') as update_date

, to_char(to_timestamp(decision.redeem_date), 'dd.mm.yyyy') as redeem_date

, dfa.ticker_symbol

--, coalesce(tol.description, decision.type_of_law::text) as type_of_law

, decision.type_of_law

, coalesce(dac.digital_asset_category_description, decision.digital_asset_category) as digital_asset_category

, coalesce(g.guarantee_description, decision.guarantee) as guarantee

, decision.price_per_dfa * coalesce(bd.total_amount_dfa, 1) / 100.0 as price_dfa

, case dfa.status

when 'emitment_success' then decision.price_per_dfa * dfa.original_total_supply_invested / 100.0

else decision.price_per_dfa * coalesce(bd.total_amount_dfa, 1) / 100.0

end as current_price

, dfa.original_total_supply_invested

, decision.price_per_dfa / 100.0 as price_per_dfa

, case dfa.status

when 'draft' then 'Проект'

when 'waiting_for_links' then 'Публикация ссылок'

when 'rejected' then 'Отменен оператором ИС'

when 'collection_started' then 'Сбор заявок'

when 'emitment_success' then 'В обороте'

when 'redeemed' then 'Погашен'

when 'mature_preparation' then 'Подготовка к погашению'

when 'waiting_for_collection_started' then 'Создание смарт-контракта'

when 'mature' then 'В процессе погашения'

when 'mature_confirmed' then 'Собраны согласия владельцев'

when 'issue_stopped' then 'Приостановлен'

when 'issue_failure' then 'Не выполнены условия выпуска ЦФА'

when 'waiting_for_emission' then 'Эмиссия выпуска'

when 'not_redeemed' then 'Не погашен'

when 'revoked' then 'Отменено эмитентом'

when 'preliminary_check' then 'На предварительной проверке'

when 'issuer_confirmation' then 'На подтверждении у Эмитента'

when 'teller_approval' then 'На согласовании у Операциониста'

when 'lawyer_approval' then 'На согласовании у Юриста'

when 'spec_approval' then 'На согласовании у Специалиста ПОД/ФТ'

when 'waiting_for_holding' then 'Выпуск создается'

when 'waiting_for_mature' then 'Ожидает погашения'

when 'modification' then 'На доработке'

when 'waiting_for_emitment' then 'Выпускается'

end as status

from params

, release_dfas dfa

inner join release_decisions decision on decision.id = dfa.decision_id

left join type_of_law tol on tol.code = decision.type_of_law

left join digital_assets_categories dac on dac.digital_asset_category = decision.digital_asset_category

left join guarantees g on g.guarantee = decision.guarantee

left join balance_dfa bd on bd.asset_id = dfa.id

where decision.issue_date >= extract(epoch from params.start_date)

and decision.issue_date < extract(epoch from params.end_date)

--and decision.redeem_date > extract(epoch from params.end_date)

order

by dfa.ticker_symbol