global class CreateUpgradeDowngradeOpportunities Implements Schedulable
{
    global void execute(SchedulableContext sc)
    {
        CreateOpportunities();
    }
      
    public void CreateOpportunities()
    {
        //Variable & List Declarations
        
        Date Today = Date.Today();
        Date d1 = Today.addDays(-1);
		List<Opportunity> lstOpp = new List<Opportunity>();
		Opportunity objOpp;
		Id idRecTypeDowngrade = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('AMP Downgrade').getRecordTypeId();
		Id idRecTypeUpgrade = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('AMP Upgrade').getRecordTypeId();
		Map<Id, Account> mapAcc = new Map<Id, Account>();
		Id idAcc;
		String strStatus;
		Decimal amt;
		Set<Id> setIdAcc = new Set<Id>();
		List<AggregateResult> lstAR = new List<AggregateResult>();
		
		// collect sum by account
		for(AggregateResult objAR : [SELECT Zuora__Account__c , Zuora__Status__c, SUM(Zuora__MRR__C) 
									FROM Zuora__Subscription__c 
									WHERE Zuora__Status__c IN ('Cancelled', 'Active')
										AND Zuora__Account__c != NULL
										AND (Zuora__TermStartDate__c = YESTERDAY OR Zuora__TermEndDate__c = YESTERDAY)
									GROUP BY ROLLUP(Zuora__Account__c, Zuora__Status__c)])
		{
			lstAR.add(objAR);
			setIdAcc.add((Id)objAR.get('Zuora__Account__c'));
		} // End of Aggregate For Loop
		
		// collect account infos
		if(!setIdAcc.isEmpty())
		{
			mapAcc = new Map<Id, Account>([SELECT Id, Name, OwnerId FROM Account WHERE Id IN: setIdAcc]);
		}
		
		// create opps
		for(AggregateResult objAR : lstAR)
		{
			idAcc = (Id)objAR.get('Zuora__Account__c');
			strStatus = (String)objAR.get('Zuora__Status__c');
			amt = (Decimal)objAR.get('expr0');
			if(strStatus == 'Cancelled')
			{
				objOpp = new Opportunity();
				objOpp.RecordTypeId = idRecTypeDowngrade;
				objOpp.OwnerId = mapAcc.get(idAcc).OwnerId;
				objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
				objOpp.AccountId = mapAcc.get(idAcc).Id;
				objOpp.Opportunity_Source__c = 'Portal';
				objOpp.Type = 'Downgrade';
				objOpp.Amount = amt * -1;
				objOpp.CloseDate = d1;
				objOpp.StageName = 'Cancelled';    
				lstOpp.add(objOpp);    
			}
			else if(strStatus == 'Active')
			{
				objOpp = new Opportunity();
				objOpp.RecordTypeId = idRecTypeUpgrade;
				objOpp.OwnerId = mapAcc.get(idAcc).OwnerId;
				objOpp.Name = mapAcc.get(idAcc).Name + ' - AMP';
				objOpp.AccountId = mapAcc.get(idAcc).Id;
				objOpp.Opportunity_Source__c = 'Portal';
				objOpp.Type = 'Existing Business';
				objOpp.Amount = amt;
				objOpp.CloseDate = d1;
				objOpp.StageName = 'Closed Won';
                objOpp.Closed_Comments__c = 'AMP Portal Self Service Upgrade';
				lstOpp.add(objOpp);
			} 
		} // END of FOR Loop

		if(!lstOpp.isEmpty())
		{
			insert lstOpp;
		}
    } // End of CreateOpportunities Method 
} // End of Class

@isTest
public class CreateUpgradeDowngradeOpportunitiesTest {

    List<Zuora__Subscription__c> subs = new List<Zuora__Subscription__c>();
    
    
    private static testMethod void tm1(){
        
    	Date Today = Date.Today();
    	Date d1 = Today.addDays(-1);
        
        Account a = New Account();
        a.Name = 'Test Account';
        a.Status__c = 'Active';
        insert a;
        
        Zuora__Subscription__c s = new Zuora__Subscription__c();
        s.Zuora__Account__c = a.Id;
        s.Zuora__Status__c = 'Active';
        s.Zuora__MRR__c = 100;
        s.Zuora__TermStartDate__c = d1;
        insert s;
        
        CreateUpgradeDowngradeOpportunities sc= new CreateUpgradeDowngradeOpportunities();
		sc.execute(null);
        
    }
    
    
    
    
}