### "Calculate beneficiaries pay charges at nodal level - 3 years.sql"

# Convert 'raw allocations' into beneficiaries-pay transmission charges.
#  Calculate charges at nodal level, but do not attempt to break them down below the nodal level.
# This script is for 3 year runs only (it does not process 4 month runs)

# Before you run this script,
# (a) you should run "Prepare raw allocations.sql"
# (b) you should run "Calculate beneficiaries pay charges.sql".

# The output table in SQL will be 'vspd.charges_3years'.

# The script takes a long time - run it overnight perhaps.

/* Proceed to calculate charges - using gross (not net) method, with charges on mass-market load paid by retailers rather than networks */

# Aggregate the raw allocations, on a daily basis.
# Calculate the scaling factors to use (when positive raw allocations > revenue to be received).

CREATE TABLE vspd.temp_benefits_daily_load_3years AS
SELECT approach, variant, component, record_date, SUM(CASE WHEN net_benefit>0 THEN net_benefit ELSE 0 END) AS gross_load_benefit
FROM vspd.tpm_raw_allocations_load
WHERE variant = '3 years'
GROUP BY approach, variant, component, record_date;

CREATE TABLE vspd.temp_benefits_daily_gen_3years AS
SELECT approach, variant, component, record_date, SUM(CASE WHEN net_benefit>0 THEN net_benefit ELSE 0 END) AS gross_gen_benefit
FROM vspd.tpm_raw_allocations_gen
WHERE variant = '3 years'
GROUP BY approach, variant, component, record_date;

CREATE INDEX idx ON vspd.temp_benefits_daily_load_3years (approach, variant, component, record_date);
CREATE INDEX idx ON vspd.temp_benefits_daily_gen_3years  (approach, variant, component, record_date);

CREATE TABLE vspd.temp_benefits_daily_3years AS
SELECT a.*, b.gross_gen_benefit, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor_gross_daily,
 1e6*annual_revenue_requirement/365 AS daily_revenue_requirement
FROM vspd.temp_benefits_daily_load_3years AS a
LEFT JOIN vspd.temp_benefits_daily_gen_3years AS b
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.record_date=b.record_date
LEFT JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component;

# Fix a known bug with the 3-year vSPD runs - GOR, BAL and BDE have incorrect data. 
# The error is regretted, but is not material given we are not specifically investigating the LSI Reliability upgrade in this context

DELETE FROM vspd.temp_benefits_daily_3years WHERE SUBSTRING(record_date,1,3)='GOR' AND approach='SPD';
DELETE FROM vspd.temp_benefits_daily_3years WHERE SUBSTRING(record_date,1,3)='BAL' AND approach='SPD';
DELETE FROM vspd.temp_benefits_daily_3years WHERE SUBSTRING(record_date,1,3)='BDE' AND approach='SPD';

UPDATE vspd.temp_benefits_daily_3years SET gross_gen_benefit=0 WHERE gross_gen_benefit IS NULL;
UPDATE vspd.temp_benefits_daily_3years SET scaling_factor_gross_daily=(daily_revenue_requirement / (gross_gen_benefit+gross_load_benefit)) WHERE (gross_gen_benefit+gross_load_benefit<>0) AND gross_gen_benefit+gross_load_benefit >= daily_revenue_requirement;

CREATE INDEX idx ON vspd.temp_benefits_daily_3years (approach, variant, component, record_date);

# Apply the scaling factors to the positive raw allocations to get charges.

CREATE TABLE vspd.charges_3years AS
  (SELECT a.approach, a.variant, a.component, a.record_date, a.node, "offered_gen  " AS charge_type,
   SUM(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_daily AS charge_on_gross_benefit_with_daily_capping
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.temp_benefits_daily_3years AS b
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component
  WHERE LENGTH(node)>7 AND a.variant='3 years'
  GROUP BY a.approach, a.variant, a.component, a.record_date, a.node)
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.node, "negative_load" AS charge_type,
   SUM(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_daily AS charge_on_gross_benefit_with_daily_capping
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.temp_benefits_daily_3years AS b
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component
  WHERE LENGTH(node)=7 AND a.variant='3 years'
  GROUP BY a.approach, a.variant, a.component, a.record_date, a.node)
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.node, "load         " AS charge_type,
   SUM(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_daily AS charge_on_gross_benefit_with_daily_capping  
  FROM vspd.tpm_raw_allocations_load AS a
  INNER JOIN vspd.temp_benefits_daily_3years AS b
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component
  WHERE a.variant='3 years'
  GROUP BY a.approach, a.variant, a.component, a.record_date, a.node)
;
