WITH section_tui AS (
  -- Calculate section fees
  SELECT ssrfees_term_code,
         ssrfees_crn,
         ssrfees_rate_code,
         ssrfees_resd_code,
         ssrfees_atts_code,
         ssrfees_chrt_code,
         SUM(ssrfees_amount) AS per_credit_cost
    FROM ssrfees
   WHERE ssrfees_detl_code IN
         (SELECT tbbdetc_detail_code
            FROM tbbdetc
           WHERE tbbdetc_dcat_code = 'TUI')
   GROUP BY ssrfees_term_code, ssrfees_crn, ssrfees_rate_code, ssrfees_resd_code, ssrfees_atts_code, ssrfees_chrt_code
),
registrations AS (
  -- Inventory existing registrations
  SELECT sfrstcr_pidm,
         sfrstcr_term_code,
         sfrstcr_crn,
         sfrstcr_camp_code,
         sob2cmp_acad_inst,
         sfrstcr_bill_hr,
         SUM(per_credit_cost) AS per_credit_cost
    FROM sfrstcr a
    JOIN section_tui
      ON sfrstcr_term_code = ssrfees_term_code
     AND sfrstcr_crn = ssrfees_crn
    JOIN stvrsts
      ON sfrstcr_rsts_code = stvrsts_code
    JOIN sob2cmp
      ON sfrstcr_camp_code = sob2cmp_camp_code
    LEFT JOIN LATERAL
         (SELECT sgbstdn_rate_code
            FROM sgbstdn
           WHERE sgbstdn_pidm = sfrstcr_pidm
             AND sgbstdn_rate_code = ssrfees_rate_code
             AND sgbstdn_term_code_eff =
                 (SELECT MAX(sgbstdn_term_code_eff)
                    FROM sgbstdn
                   WHERE sgbstdn_pidm = sfrstcr_pidm
                     AND sgbstdn_term_code_eff <= sfrstcr_term_code))
      ON 1=1
    LEFT JOIN LATERAL
         (SELECT sgrsatt_atts_code
            FROM sgrsatt
           WHERE sgrsatt_pidm = sfrstcr_pidm
             AND sgrsatt_atts_code = ssrfees_atts_code
             AND sgrsatt_term_code_eff =
                 (SELECT MAX(sgrsatt_term_code_eff)
                    FROM sgrsatt
                   WHERE sgrsatt_pidm = sfrstcr_pidm
                     AND sgrsatt_term_code_eff <= sfrstcr_term_code))
      ON 1=1
    LEFT JOIN LATERAL
         (SELECT sgrchrt_chrt_code
            FROM sgrchrt
           WHERE sgrchrt_pidm = sfrstcr_pidm
             AND sgrchrt_chrt_code = ssrfees_chrt_code
             AND sgrchrt_term_code_eff =
                 (SELECT MAX(sgrchrt_term_code_eff)
                    FROM sgrchrt
                   WHERE sgrchrt_pidm = sfrstcr_pidm
                     AND sgrchrt_term_code_eff <= sfrstcr_term_code))
      ON 1=1
   WHERE sfrstcr_bill_hr > 0
     AND sfrstcr_pidm IN
         (SELECT sfrstcr_pidm
            FROM sfrstcr
           WHERE a.sfrstcr_pidm = sfrstcr_pidm
             AND a.sfrstcr_term_code = sfrstcr_term_code
             AND sfrstcr_camp_code IN
                 (SELECT sob2cmp_camp_code
                    FROM sob2cmp
                   WHERE sob2cmp_acad_inst = NVL(:section_mau_A_F_S, sob2cmp_acad_inst)))
     AND stvrsts_voice_type IN ('R','W')
     AND ssrfees_resd_code IS NULL
     AND (ssrfees_rate_code IS NULL
       OR ssrfees_rate_code = sgbstdn_rate_code)
     AND (ssrfees_atts_code IS NULL
       OR ssrfees_atts_code = sgrsatt_atts_code)
     AND (ssrfees_chrt_code IS NULL
       OR ssrfees_chrt_code = sgrchrt_chrt_code)
  GROUP BY sfrstcr_pidm, sfrstcr_term_code, sfrstcr_crn, sob2cmp_acad_inst, sfrstcr_camp_code, sfrstcr_bill_hr
),
tuiw_contracts AS (
  -- Inventory existing employee tuition waiver contracts
  SELECT *
    FROM (SELECT tbbcstu_stu_pidm,
                 tbbcstu_term_code,
                 tbbcstu_contract_pidm,
                 tbbcstu_max_student_amount,
                 tbbcstu_activity_date,
                 tbbcstu_user_id,
                 ROW_NUMBER() OVER (PARTITION BY tbbcstu_stu_pidm, tbbcstu_term_code
                                    ORDER BY CASE SUBSTR(spriden_id,1,5)
                                             WHEN 'AFS99' THEN 1 ELSE 2 END,
                                             tbbcstu_contract_number   DESC,
                                             tbbcstu_contract_priority ASC) AS rn
            FROM tbbcstu
            JOIN spriden
              ON tbbcstu_contract_pidm = spriden_pidm
           WHERE spriden_change_ind IS NULL
             AND SUBSTR(spriden_id,1,5) IN ('AAI99','ACC99','AFS99','AJSET','AKE00','AKO99',
                                            'AKP99','AKU99','AMS99','ANW99','APW99','ARB99',
                                            'ARC99','ASC00','ASCET'))
 WHERE rn = 1
),
acyr_remaining AS (
  -- For each student, sum prior term waived hours with contracts (each term capped at 8) 
  -- and then compute available waiver for the current term (max = 8, overall cap = 16)
  SELECT sfrstcr_pidm,
         total_prior,
         LEAST(16 - NVL(total_prior, 0), 8) AS available_waiver
    FROM (SELECT sfrstcr_pidm,
                 SUM(waived_in_term) AS total_prior
            FROM (SELECT sfrstcr_pidm,
                         sfrstcr_term_code,
                         LEAST(SUM(sfrstcr_bill_hr), 8) AS waived_in_term
                    FROM registrations, tuiw_contracts
                   WHERE sfrstcr_pidm = tbbcstu_stu_pidm
                     AND sfrstcr_term_code = tbbcstu_term_code
                     AND sfrstcr_term_code IN
                         (SELECT a.stvterm_code
                            FROM stvterm a,
                                 stvterm b
                           WHERE a.stvterm_acyr_code = b.stvterm_acyr_code
                             AND a.stvterm_code < b.stvterm_code
                             AND b.stvterm_code = :waiver_term_code)
                     AND sfrstcr_pidm IN
                         (SELECT sfrstcr_pidm
                            FROM sfrstcr
                           WHERE sfrstcr_term_code = :waiver_term_code)
                 GROUP BY sfrstcr_pidm, sfrstcr_term_code)
           GROUP BY sfrstcr_pidm)
),
ranked_regs AS (
  SELECT r.*,
         -- If no record in acyr_remaining then assume full 8 hours available.
         NVL(a.available_waiver, 8) AS available_waiver,
         SUM(sfrstcr_bill_hr) OVER (
             PARTITION BY r.sfrstcr_pidm
             ORDER BY per_credit_cost DESC, sfrstcr_crn
             ROWS UNBOUNDED PRECEDING
         ) AS running_total,
         NVL(
           SUM(sfrstcr_bill_hr) OVER (
             PARTITION BY r.sfrstcr_pidm
             ORDER BY per_credit_cost DESC, sfrstcr_crn
             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           ),
         0) AS prev_running_total,
         NVL(total_prior, '0') total_prior
    FROM registrations r
    LEFT JOIN acyr_remaining a
      ON r.sfrstcr_pidm = a.sfrstcr_pidm
   WHERE r.sfrstcr_term_code = :waiver_term_code
),
calculated AS (
  SELECT sfrstcr_pidm,
         sfrstcr_term_code,
         sfrstcr_crn,
         sfrstcr_camp_code,
         sob2cmp_acad_inst,
         sfrstcr_bill_hr,
         per_credit_cost,
         total_prior,
         available_waiver,
         running_total,
         prev_running_total,
         CASE 
           WHEN running_total <= available_waiver THEN sfrstcr_bill_hr
           WHEN prev_running_total < available_waiver THEN available_waiver - prev_running_total
           ELSE 0
         END AS waived_hours,
         CASE 
           WHEN running_total <= available_waiver THEN sfrstcr_bill_hr * per_credit_cost
           WHEN prev_running_total < available_waiver THEN (available_waiver - prev_running_total) * per_credit_cost
           ELSE 0
         END AS waiver_amount
    FROM ranked_regs
),
new_waiv_status AS (
  -- Some campuses share contract codes, so we group them here
  SELECT sfrstcr_pidm,
         sfrstcr_term_code,
         LISTAGG(DISTINCT sob2cmp_acad_inst, ',') AS class_insts,
         LISTAGG(DISTINCT sfrstcr_camp_code, ',') AS class_camps,
         TO_CHAR(total_prior, '90.0') AS total_prior,
         TO_CHAR(SUM(waived_hours), '90.0') AS adj_waived_hours,
         SUM(waiver_amount) AS adj_tuition_waiver
    FROM calculated
  GROUP BY sfrstcr_pidm, sfrstcr_term_code, total_prior
),
active_jobs AS (
  SELECT *
    FROM nbrjobs a, nbrbjob
   WHERE nbrjobs_status <> 'T'
     AND nbrbjob_contract_type = 'P'
     AND nbrjobs_pidm = nbrbjob_pidm
     AND nbrjobs_posn = nbrbjob_posn
     AND nbrjobs_suff = nbrbjob_suff
     AND nbrjobs_effective_date =
         (SELECT MAX(nbrjobs_effective_date)
            FROM nbrjobs
           WHERE a.nbrjobs_pidm = nbrbjob_pidm
             AND a.nbrjobs_posn = nbrjobs_posn
             AND a.nbrjobs_suff = nbrjobs_suff
             AND nbrjobs_effective_date <= 
                 (SELECT stvterm_start_date
                    FROM stvterm
                   WHERE stvterm_code = :waiver_term_code))
)
SELECT CASE WHEN adj_tuition_waiver = 0 THEN 'No waiver credits remain. Skip.'
            WHEN tbbcstu_contract_pidm IS NULL THEN 'Create waiver contract for $' || adj_tuition_waiver
            WHEN adj_tuition_waiver - NVL(tbbcstu_max_student_amount, 0) > 0 THEN 'Increase ' || c.spriden_id || ' amount to $' || adj_tuition_waiver
            WHEN adj_tuition_waiver = tbbcstu_max_student_amount THEN c.spriden_id || ' amount is correct: No action necessary'
            WHEN adj_tuition_waiver - NVL(tbbcstu_max_student_amount, 0) < 0 THEN 'Decrease ' || c.spriden_id || ' amount to $' || adj_tuition_waiver
       END "ACTION",
       e.spriden_id "ID",
       f_format_name(pebempl_pidm, 'FML') "Name",
       pebempl_empl_status "Empl",
       pebempl_ecls_code "Ecls",
       base_org "Base TKL ORG",
       sgbstdn_camp_code "SGASTDN Campus",
       class_insts "Section MAUs",
       class_camps "Section Campuses",
       sgbstdn_resd_code "Residency",
       sfrstcr_term_code "Enrollment Term",
       total_prior "All Prior Waived Hours (16/year)",
       adj_waived_hours "New Adjusted Waiver Hours (8/term)",
       '$' || adj_tuition_waiver "New Adjusted Tuition Benefit",
       DECODE(tbbcstu_max_student_amount, NULL, NULL, '$' || tbbcstu_max_student_amount) "Current Waiver Contract Amount",
       DECODE(tbbcstu_contract_pidm, NULL, NULL, c.spriden_id) "Contract ID",
       DECODE(tbbcstu_contract_pidm, NULL, 'Needs waiver created', f_format_name(tbbcstu_contract_pidm, 'FML')) "Waiver Contract",
       tbbcstu_term_code "Contract Term",
       tbbcstu_activity_date "Contract Date",
       tbbcstu_user_id "Contract User"
  FROM pebempl
  JOIN spriden e
    ON pebempl_pidm = e.spriden_pidm
  JOIN active_jobs
    ON pebempl_pidm = nbrjobs_pidm
  JOIN new_waiv_status
    ON pebempl_pidm = sfrstcr_pidm
  LEFT JOIN tuiw_contracts
    ON pebempl_pidm = tbbcstu_stu_pidm
   AND sfrstcr_term_code = tbbcstu_term_code
  LEFT JOIN spriden c
    ON c.spriden_pidm = tbbcstu_contract_pidm
  JOIN LATERAL
       (SELECT sgbstdn.*,
               REPLACE(gok2rpt.ua_f_hier_level(pebempl_orgn_code_home, SYSDATE, 1), 'TOT') AS base_org
          FROM sgbstdn
         WHERE sgbstdn_pidm = pebempl_pidm
           AND sgbstdn_term_code_eff =
               (SELECT MAX(sgbstdn_term_code_eff)
                  FROM sgbstdn
                 WHERE sgbstdn_pidm = pebempl_pidm
                   AND sgbstdn_term_code_eff <= :waiver_term_code))
    ON 1=1
 WHERE (SELECT stvterm_start_date
          FROM stvterm
         WHERE stvterm_code = :waiver_term_code) BETWEEN pebempl_current_hire_date AND NVL(pebempl_last_work_date, '01-JAN-2999')
   AND pebempl_ecls_code IN ('A9','AR','CR','EX','F9','FN','FR','NR','XR')
   AND base_org = NVL(:empl_org_UAA_UAF_UAS_SW, base_org)
   AND e.spriden_change_ind IS NULL
   AND c.spriden_change_ind IS NULL
ORDER BY 2;