use gemhV2;
set @interval=13;
set @marketId=1089;
drop table if exists gemhs;
#get all companies with their main market
create temporary table gemhs
select distinct gemhnumber,if(main=1,market,null) mainMarket from marketCompanyCpa where market=@marketId ;

#get the latest status change for all the companies for the dates we want
drop table if exists maxStatus;
create temporary table maxStatus
  select max(id) id,gemhnumber from StatusChange
  where date <=current_date - interval @`interval` month
group by gemhnumber;

create index maxStatus_gemh_index
	on maxStatus  (gemhnumber);

#from the companies we have from table gemhs get the actives on the dates we want
drop table if exists gemhStatus;
create temporary table gemhStatus
  select sc.*,g.mainMarket from gemhs g  join maxStatus sc1 on g.gemhnumber=sc1.gemhnumber join StatusChange sc on sc.id=sc1.id and ((date<=current_date - interval @`interval` month
  and currentStatus in ('Ενεργή','Προεγγραφή'))
  or(
    month(date)=month(current_date - interval @`interval` month) and  year(date)=year(current_date - interval @`interval` month)
  and currentStatus not in ('Ενεργή','Προεγγραφή')
  ));

drop table if exists fullData;
create temporary table fullData
  select m.*,gs.currentStatus,gs.date,gs.mainMarket
from gemhStatus gs join Main m on gs.gemhnumber=m.gemhnumber;

select * from fullData
;



#------- below are not tested------------------
select f.mainMarket,mcc.*,M.* from
              fullData f join marketCompanyCpa mcc on f.gemhnumber=mcc.gemhnumber
join Markets M on mcc.market = M.id
join Markets m2 on m2.id=@marketId and m2.type=M.type

group by gemhnumber,market

;

select
sum(if(currentStatus in ('Ενεργή','Προεγγραφή'),1,0))`count actives`
,sum(if(mainMarket=@marketId,1,0))
,sum(if(market=@marketId,1,0))
,count(*),f.gemhnumber
from
 fullData f join marketCompanyCpa mcc on f.gemhnumber=mcc.gemhnumber
join Markets M on mcc.market = M.id
join Markets m2 on m2.id=@marketId and m2.type=M.type
group by f.gemhnumber
;