create or replace procedure "SEND_MILESTONE_REMINDER"
is
begin
  for c1 in (select m.id
             ,      p.project_lead
             ,      p.name project
             ,      m.name milestone
             ,      to_char(m.due_date, 'DD Month YYYY') due_date
             from eba_demo_projects p
             ,    eba_demo_proj_milestones m
             where p.id = m.project_id
             and   trunc(m.due_date) >= sysdate - 14 -- Due Date within the next 14 days
            ) loop
    for c2 in (select full_name
               ,      email
               from eba_demo_proj_team_members tm
               where tm.id = c1.project_lead
               and   tm.email is not null
              ) loop
      apex_mail.send (
        p_to                 => c2.email,
        p_template_static_id => 'MILESTONES',
        p_placeholders       => '{' ||
        '    "APPLICATION_LINK":' || apex_json.stringify( apex_util.prepare_url(p_url => 'f?p=:'||v('APP_ID')||':1:' )) ||
        '   ,"DUE_DATE":'         || apex_json.stringify( c1.due_date ) ||
        '   ,"INVITEE":'          || apex_json.stringify( c2.full_name ) ||
        '   ,"MILESTONE":'        || apex_json.stringify( c1.milestone ) ||
        '   ,"PROJECT":'          || apex_json.stringify( c1.project ) ||
        '}' );
    end loop;
  end loop;
end;