set statement_timeout to '5min';

-- pocet aktivnych firiem v ORSR
select source_register, count(*) from rpo.organizations 
 where source_register is not null and terminated_on is null and deleted_at is null 
 group by 1 
 order by 2 desc;

--- "Obchodný register"	336514

--- pocet stakeholderov a kolko k nim vieme priradit na zaklade zhodneho mena statutarov (ti maju RC)
--- tu su aj firmy a zahranicni ako stakeholderi
select
  (select count(*) from rpo.organization_statutory_entries m where 
    m.organization_id = o.id 
    and m.person_formatted_name = ose.person_formatted_name    
    and m.effective_to is null
  ),
  count(*)
  from rpo.organizations o
  join rpo.organization_stakeholder_entries ose ON ose.organization_id = o.id  
  --left join rpo.organization_stakeholder_entries ohe ON ohe.organization_id = o.id AND ohe.person_formatted_name = ose.person_formatted_name
  where
  ose.effective_to is null 
  and source_register = 'Obchodný register'
  and o.deleted_at is null
  and terminated_on is null  
  --and o.id = 1012550
  group by 1
---
---0	165478  - ziadna zhoda sa nenasla (tu su aj spolocnici ako sro, zahranicne osoby...)
---1	324036  - perfect match
---2	5355    - k 1 stakeholderovi su dvaja statutari (menovci, likvidatori)
---3	12      - k 1 stakeholderovi su 3 statutari
---4	1       - k 1 stakeholderovi su 4 statutari
---11	11      - k 1 stakeholderovi je 11 statutarov (bug u nas)
  

--- pocet orgs ktore maju spolocnika, ktoreho nevieme jednoznacne stotoznit podla mena
select count(*) from rpo.organizations o
where EXISTS (
   SELECT 1 FROM rpo.organization_stakeholder_entries ose 
    WHERE 
	ose.organization_id = o.id 
    and ose.effective_to is null 
    and (SELECT count(*) FROM rpo.organization_statutory_entries m WHERE m.organization_id = o.id and m.person_formatted_name = ose.person_formatted_name and m.effective_to is null) != 1
    LIMIT 1
    )
  and source_register = 'Obchodný register'
  and o.deleted_at is null
  and terminated_on is null  
--- 111781

--- pocet orgs ktore maju len spolocnikov, ktorych jednoznacne vieme stotoznit podla mena (vo firme je statutar co ma rovnake meno)
select count(*) from rpo.organizations o
where NOT EXISTS (
   SELECT 1 FROM rpo.organization_stakeholder_entries ose 
    WHERE 
	ose.organization_id = o.id 
    and ose.effective_to is null 
    and (SELECT count(*) FROM rpo.organization_statutory_entries m WHERE m.organization_id = o.id and m.person_formatted_name = ose.person_formatted_name and m.effective_to is null) != 1
    LIMIT 1
    )
  and source_register = 'Obchodný register'
  and o.deleted_at is null
  and terminated_on is null  
--- 224733


