drop table moc_payments_view ;

create table moc_payments_view as
select party_id, i.id invoice_id, total_invoice_amount totalfee,
max(case when item = 'Government Fee'  then total_price end) as govfee,
max(case when item = 'Service Fee'  then total_price end) as servicefee,
date(pay.payment_date) paymentdate, d.name servicelocation, i.status paymentstatus, 1 as count
from invoice i
join invoice_item it on it.invoice_id = i.id
left join invoice_payment ip on ip.invoice_id = i.id
left join payment pay on ip.payment_id = pay.id
left join district d on pay.district_id = d.id
group by i.id;

ALTER TABLE `moc_payments_view` ADD INDEX `party_id_idx` (`party_id`);

drop table moc_business_view ;

create table moc_business_view as
select p.id mb_party_id, c.id, c.name customername, status, tin, concat_ws('/', p.phone_number_1, p.phone_number_2) mobileno,
bc.name businessclass, i.name industry, ct.name organizationtype, a.name licenseissuingagency, lt.name moclicensetype,
date(c.issued_date) licenseregistrationdate, date(c.expiry_date) licenseexpirationdate, d.name as businessdistrict
from party p
join address ad on p.address_id = ad.id
left join district d on d.id = ad.district_id
join company c on c.id = p.partyable_id
join business_class bc on bc.id = c.business_class_id
join industry i on i.id = c.industry_id
join company_type ct on ct.id = c.company_type_id
left join agency a on a.id = c.agency_id
join license_type lt on lt.id = c.license_type_id
and p.partyable_type = 'Company';

ALTER TABLE `moc_business_view` ADD INDEX `party_id_idx` (`mb_party_id`);

drop table moc_report_view;

create table moc_report_view
select * from
moc_business_view mb
join moc_payments_view mp  on mb.mb_party_id = mp.party_id
where licenseregistrationdate is not null;
