---

with params as (

select '1970-01-01'::date at time zone 'gmt+03' as start_date

, '2026-03-31'::date at time zone 'gmt+03' as end_date

)

select u.id

, case u.type

when 'LEGAL' then lp.title

when 'ENTREPRENEUR' then concat_ws(' ', pd.last_name, pd.first_name, pd.second_name)

when 'INDIVIDUAL' then concat_ws(' ', pd.last_name, pd.first_name, pd.second_name)

end as title

, case

when u.registered_at is not null then 'Включен в реестр ИС'

else 'Не включен в реестр ИС'

end as in_registry_status

, case

when p.deleted_at is not null then 'Пользовательское соглашение расторгнуто'

when u.is_blocked then 'Заблокирован'

when not u.is_identified then 'Не идентифицирован'

when u.is_identified then 'Идентифицирован'

end as current_status

, null as block_date

, null as unblock_date

--, u.created_at

, u.sent_for_approval_at

, u.registered_at

, case u.type

when 'LEGAL' then 'ЮЛ'

when 'ENTREPRENEUR' then 'ФЛ'

when 'INDIVIDUAL' then 'ФЛ'

end as type_name

, case u.type

when 'LEGAL' then lp.inn

when 'ENTREPRENEUR' then pd.inn

when 'INDIVIDUAL' then pd.inn

end as inn

, 'Нет' as qualified

, null as qualified_date

, null as disqualified_date

, case

when u.type = 'LEGAL' and p.legal_type = 'resident' then 'Резидент'

--when u.type in ('INDIVIDUAL', 'ENTREPRENEUR') and pd.citizenship = 'ru' then 'Резидент'

when u.type in ('INDIVIDUAL', 'ENTREPRENEUR') and pd.citizenship = 'RU' then 'Резидент'

else 'Нерезидент'

end as resident_status

, p.report_okato

from params, users u

inner join profile p on p.user_id = u.id and p.type = u.type

left join individual_profile ip on ip.id = p.id

left join entrepreneur_profile ep on ep.id = p.id

left join legal_profile lp on lp.id = p.id

left join personal_data pd on pd.id = coalesce(ip.personal_data_id, ep.personal_data_id)

where u.type in ('LEGAL', 'ENTREPRENEUR', 'INDIVIDUAL')

and p.status = 'APPROVED'

and u.registered_at <= params.end_date

and exists(

select 1

from profile_status_history hist

where hist.user_id = u.id

and hist.dt_start_status >= params.start_date

--and hist.dt_start_status < params.end_date

and hist.dt_start_status <= params.end_date or hist.dt_start_status is null

)