--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements. See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership. The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
--

/* Leaving as out-of-box (but non-core reports) for now
could be removed later if people think too many non-generic
reports are being added
used in quipo TEVI dashboard (landing pages) */

DELETE FROM `stretchy_report` where report_name = 'FieldAgentStats';
DELETE FROM `stretchy_report` where report_name = 'FieldAgentPrograms';
DELETE FROM `stretchy_report` where report_name = 'ProgramDetails';
DELETE FROM `stretchy_report` where report_name = 'ChildrenStaffList';
DELETE FROM `stretchy_report` where report_name = 'CoordinatorStats';
DELETE FROM `stretchy_report` where report_name = 'BranchManagerStats';
DELETE FROM `stretchy_report` where report_name = 'ProgramDirectorStats';
DELETE FROM `stretchy_report` where report_name = 'ProgramStats';

DELETE FROM `m_permission` where entity_name = 'FieldAgentStats';
DELETE FROM `m_permission` where entity_name = 'FieldAgentPrograms';
DELETE FROM `m_permission` where entity_name = 'ProgramDetails';
DELETE FROM `m_permission` where entity_name = 'ChildrenStaffList';
DELETE FROM `m_permission` where entity_name = 'CoordinatorStats';
DELETE FROM `m_permission` where entity_name = 'BranchManagerStats';
DELETE FROM `m_permission` where entity_name = 'ProgramDirectorStats';
DELETE FROM `m_permission` where entity_name = 'ProgramStats';


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('FieldAgentStats', 'Table', 'Quipo', false, false, 'Field Agent Statistics',

"
select ifnull(cur.display_symbol, l.currency_code) as Currency,
/*This query will return more than one entry if more than one currency is used */
count(distinct(c.id)) as activeClients, count(*) as activeLoans,
sum(l.principal_disbursed_derived) as disbursedAmount,
sum(l.principal_outstanding_derived) as loanOutstandingAmount,
round((sum(l.principal_outstanding_derived) * 100) /  sum(l.principal_disbursed_derived),2) as loanOutstandingPC,
'???' as loanOverPaymentAmount,
sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) as portfolioAtRisk,

round((sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC,

count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) as clientsInDefault,
round((count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC,
(sum(l.principal_disbursed_derived) / count(*))  as averageLoanAmount
from m_staff fa
join m_office o on o.id = fa.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
join m_group pgm on pgm.staff_id = fa.id
join m_loan l on l.group_id = pgm.id and l.client_id is not null
left join m_currency cur on cur.code = l.currency_code
left join m_loan_arrears_aging laa on laa.loan_id = l.id
join m_client c on c.id = l.client_id
where fa.id = ${staffId}
and l.loan_status_id = 300
group  by l.currency_code
");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('FieldAgentPrograms', 'Table', 'Quipo', false, false, 'List of Field Agent Programs',

"
select pgm.id, pgm.display_name as `name`, sts.enum_message_property as status
 from m_group pgm
 join m_office o on o.id = pgm.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
 left join r_enum_value sts on sts.enum_name = 'status_enum' and sts.enum_id = pgm.status_enum
 where pgm.staff_id = ${staffId}
");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('ProgramDetails', 'Table', 'Quipo', false, false, 'List of Loans in a Program',

"
 select l.id as loanId, l.account_no as loanAccountNo, c.id as clientId, c.account_no as clientAccountNo,
 pgm.display_name as programName,

(select count(*)
from m_loan cy
where cy.group_id = pgm.id and cy.client_id =c.id
and cy.disbursedon_date <= l.disbursedon_date) as loanCycleNo,

c.display_name as clientDisplayName,
 ifnull(cur.display_symbol, l.currency_code) as Currency,
ifnull(l.principal_repaid_derived,0.0) as loanRepaidAmount,
ifnull(l.principal_outstanding_derived, 0.0) as loanOutstandingAmount,
 '???' as loanInAdvanceAmount,

ifnull(laa.principal_overdue_derived, 0.0) as loanInArrearsAmount,
if(ifnull(laa.principal_overdue_derived, 0.00) > 0, 'Yes', 'No') as inDefault,

if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)  as portfolioAtRisk

 from m_group pgm
 join m_office o on o.id = pgm.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
 join m_loan l on l.group_id = pgm.id and l.client_id is not null
 left join m_currency cur on cur.code = l.currency_code
 join m_client c on c.id = l.client_id
 left join m_loan_arrears_aging laa on laa.loan_id = l.id
 where pgm.id = ${programId}
 and l.loan_status_id = 300
order by c.display_name, l.account_no

");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('ChildrenStaffList', 'Table', 'Quipo', false, false, 'Get Next Level Down Staff',

"
 select s.id, s.display_name,
s.firstname, s.lastname, s.organisational_role_enum,
s.organisational_role_parent_staff_id,
sp.display_name as `organisational_role_parent_staff_display_name`
from m_staff s
join m_staff sp on s.organisational_role_parent_staff_id = sp.id
where s.organisational_role_parent_staff_id = ${staffId}
");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('CoordinatorStats', 'Table', 'Quipo', false, false, 'Coordinator Statistics',

"
select ifnull(cur.display_symbol, l.currency_code) as Currency,
/*This query will return more than one entry if more than one currency is used */
count(distinct(c.id)) as activeClients, count(*) as activeLoans,
sum(l.principal_disbursed_derived) as disbursedAmount,
sum(l.principal_outstanding_derived) as loanOutstandingAmount,
round((sum(l.principal_outstanding_derived) * 100) /  sum(l.principal_disbursed_derived),2) as loanOutstandingPC,
'???' as loanOverPaymentAmount,
sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) as portfolioAtRisk,

round((sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC,

count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) as clientsInDefault,
round((count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC,
(sum(l.principal_disbursed_derived) / count(*))  as averageLoanAmount
from m_staff coord
join m_staff fa on fa.organisational_role_parent_staff_id = coord.id
join m_office o on o.id = fa.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
join m_group pgm on pgm.staff_id = fa.id
join m_loan l on l.group_id = pgm.id and l.client_id is not null
left join m_currency cur on cur.code = l.currency_code
left join m_loan_arrears_aging laa on laa.loan_id = l.id
join m_client c on c.id = l.client_id
where coord.id = ${staffId}
and l.loan_status_id = 300
group  by l.currency_code
");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('BranchManagerStats', 'Table', 'Quipo', false, false, 'Branch Manager Statistics',

"
select ifnull(cur.display_symbol, l.currency_code) as Currency,
/*This query will return more than one entry if more than one currency is used */
count(distinct(c.id)) as activeClients, count(*) as activeLoans,
sum(l.principal_disbursed_derived) as disbursedAmount,
sum(l.principal_outstanding_derived) as loanOutstandingAmount,
round((sum(l.principal_outstanding_derived) * 100) /  sum(l.principal_disbursed_derived),2) as loanOutstandingPC,
'???' as loanOverPaymentAmount,
sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) as portfolioAtRisk,

round((sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC,

count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) as clientsInDefault,
round((count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC,
(sum(l.principal_disbursed_derived) / count(*))  as averageLoanAmount
from m_staff bm
join m_staff coord on coord.organisational_role_parent_staff_id = bm.id
join m_staff fa on fa.organisational_role_parent_staff_id = coord.id
join m_office o on o.id = fa.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
join m_group pgm on pgm.staff_id = fa.id
join m_loan l on l.group_id = pgm.id and l.client_id is not null
left join m_currency cur on cur.code = l.currency_code
left join m_loan_arrears_aging laa on laa.loan_id = l.id
join m_client c on c.id = l.client_id
where bm.id = ${staffId}
and l.loan_status_id = 300
group  by l.currency_code
");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('ProgramDirectorStats', 'Table', 'Quipo', false, false, 'Program DirectorStatistics',

"
select ifnull(cur.display_symbol, l.currency_code) as Currency,
/*This query will return more than one entry if more than one currency is used */
count(distinct(c.id)) as activeClients, count(*) as activeLoans,
sum(l.principal_disbursed_derived) as disbursedAmount,
sum(l.principal_outstanding_derived) as loanOutstandingAmount,
round((sum(l.principal_outstanding_derived) * 100) /  sum(l.principal_disbursed_derived),2) as loanOutstandingPC,
'???' as loanOverPaymentAmount,
sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) as portfolioAtRisk,

round((sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC,

count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) as clientsInDefault,
round((count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC,
(sum(l.principal_disbursed_derived) / count(*))  as averageLoanAmount
from m_staff pd
join m_staff bm on bm.organisational_role_parent_staff_id = pd.id
join m_staff coord on coord.organisational_role_parent_staff_id = bm.id
join m_staff fa on fa.organisational_role_parent_staff_id = coord.id
join m_office o on o.id = fa.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
join m_group pgm on pgm.staff_id = fa.id
join m_loan l on l.group_id = pgm.id and l.client_id is not null
left join m_currency cur on cur.code = l.currency_code
left join m_loan_arrears_aging laa on laa.loan_id = l.id
join m_client c on c.id = l.client_id
where pd.id = ${staffId}
and l.loan_status_id = 300
group  by l.currency_code
");


INSERT INTO `stretchy_report` (`report_name`, `report_type`, `report_category`, `core_report`, `use_report`, `description`, `report_sql`)
VALUES ('ProgramStats', 'Table', 'Quipo', false, false, 'Program Statistics',

"
select ifnull(cur.display_symbol, l.currency_code) as Currency,
/*This query will return more than one entry if more than one currency is used */
count(distinct(c.id)) as activeClients, count(*) as activeLoans,
sum(l.principal_disbursed_derived) as disbursedAmount,
sum(l.principal_outstanding_derived) as loanOutstandingAmount,
round((sum(l.principal_outstanding_derived) * 100) /  sum(l.principal_disbursed_derived),2) as loanOutstandingPC,
'???' as loanOverPaymentAmount,
sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) as portfolioAtRisk,

round((sum(
    if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
        l.principal_outstanding_derived,0)) * 100) / sum(l.principal_outstanding_derived), 2) as portfolioAtRiskPC,

count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) as clientsInDefault,
round((count(distinct(
        if(date_sub(curdate(), interval 28 day) > ifnull(laa.overdue_since_date_derived, curdate()),
            c.id,null))) * 100) / count(distinct(c.id)),2) as clientsInDefaultPC,
(sum(l.principal_disbursed_derived) / count(*))  as averageLoanAmount
from m_group pgm
join m_office o on o.id = pgm.office_id
            and o.hierarchy like concat('${currentUserHierarchy}', '%')
join m_loan l on l.group_id = pgm.id and l.client_id is not null
left join m_currency cur on cur.code = l.currency_code
left join m_loan_arrears_aging laa on laa.loan_id = l.id
join m_client c on c.id = l.client_id
where pgm.id = ${programId}
and l.loan_status_id = 300
group  by l.currency_code
");


INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_FieldAgentStats', 'FieldAgentStats', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_FieldAgentPrograms', 'FieldAgentPrograms', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_ProgramDetails', 'ProgramDetails', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_ChildrenStaffList', 'ChildrenStaffList', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_CoordinatorStats', 'CoordinatorStats', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_BranchManagerStats', 'BranchManagerStats', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_ProgramDirectorStats', 'ProgramDirectorStats', 'READ', 0);

INSERT INTO `m_permission` (`grouping`, `code`, `entity_name`, `action_name`, `can_maker_checker`)
VALUES ('report', 'READ_ProgramStats', 'ProgramStats', 'READ', 0);