### "Calculate beneficiaries pay charges.sql"

# Convert 'raw allocations' into beneficiaries-pay transmission charges.
# This script is for 4 month analyses only (it does not process 3 year runs)

# Before you run this script,
# (a) you should run "Prepare raw allocations.sql"
# (b) you should have prepared SQL tables of load shares at GXP, including 'test.reconciliation_allocations_for_spd_method_long' and 'test.reconciliation_allocations_by_network_long'
#     - but these load share tables are commercially sensitive and may not be distributed
# (c) directory "C:\TPM\Inputs\" should contain:
# - "annual revenue requirements.csv" (annual amounts to be recovered by approach, variant, and component)
# - "offer node to participant.csv" (a table mapping each generation node to a generator)
# - "injecting load node to participant.csv" (a table mapping each demand node that is sometimes a net injector to a generator)
# - "node to network.csv" (a table mapping each node to the _primary_ network or direct-connect customer it serves)
# - "prefix to region.csv" (a table mapping each node to a region of NZ)
# - "prefix to rcpd region.csv" (a table mapping each node to the four existing transmission pricing regions)
# - "days per month.csv" (providing the number of days in each calendar month studied)

# The output tables in SQL will be: 
# - vspd.charges (gross method, load charges on purchasers)
# - vspd.net_charges (net method, load charges on purchasers)
# - vspd.net_charges_unfloored (net method with no floor, load charges on purchasers)
# - vspd.charges_by_network (gross method, load charges on networks)
# - vspd.monthly_aggregated_charges                           }
#   vspd.monthly_aggregated_charges_on_net_benefit            } Monthly aggregated versions of the above
#   vspd.monthly_aggregated_charges_on_net_benefit_unfloored  }
#   vspd.monthly_aggregated_charges_by_network                }

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

/* Load various input tables */

CREATE TABLE vspd.annual_revenue_requirements  (approach VARCHAR(10), variant VARCHAR(25), component VARCHAR(20), annual_revenue_requirement DOUBLE); 
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\annual revenue requirements.csv'
INTO TABLE vspd.annual_revenue_requirements
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE TABLE vspd.offer_node_to_participant (generator VARCHAR(20), offer_code VARCHAR(14));
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\offer node to participant.csv'
INTO TABLE vspd.offer_node_to_participant
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON vspd.offer_node_to_participant (offer_code);

CREATE TABLE vspd.injecting_load_node_to_participant (generator VARCHAR(20), injecting_load_node VARCHAR(7));
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\injecting load node to participant.csv'
INTO TABLE vspd.injecting_load_node_to_participant
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON vspd.injecting_load_node_to_participant (injecting_load_node);

CREATE TABLE vspd.node_to_network (node_7character VARCHAR(7), primary_network_id VARCHAR(20));
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\node to network.csv'
INTO TABLE vspd.node_to_network
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON vspd.node_to_network (node_7character);

CREATE TABLE vspd.prefix_to_region (prefix VARCHAR(3), region VARCHAR(30), island VARCHAR(1));
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\prefix to region.csv'
INTO TABLE vspd.prefix_to_region
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON vspd.prefix_to_region (prefix);

CREATE TABLE vspd.days_per_month (record_year INT, record_month INT, n_days INT);
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\days per month.csv'
INTO TABLE vspd.days_per_month
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON vspd.days_per_month (record_year, record_month);

CREATE TABLE vspd.prefix_to_rcpd_region (prefix VARCHAR(3), rcpd_region VARCHAR(3));
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\prefix to rcpd region.csv'
INTO TABLE vspd.prefix_to_rcpd_region
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON vspd.prefix_to_rcpd_region (prefix);

/* 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 half-hourly, daily, monthly and 4-monthly basis.
# Calculate the scaling factors to use (when positive raw allocations > revenue to be received).

CREATE TABLE vspd.temp_benefits_halfhourly_load AS
SELECT approach, variant, component, record_date, trading_period, 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, trading_period;

CREATE TABLE vspd.temp_benefits_halfhourly_gen AS
SELECT approach, variant, component, record_date, trading_period, 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, trading_period;

CREATE INDEX idx ON vspd.temp_benefits_halfhourly_load (approach, variant, component, record_date, trading_period);
CREATE INDEX idx ON vspd.temp_benefits_halfhourly_gen  (approach, variant, component, record_date, trading_period);

CREATE TABLE vspd.temp_benefits_halfhourly AS
SELECT a.*, b.gross_gen_benefit, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor_gross_halfhourly,
 1e6*annual_revenue_requirement/(365*48) AS halfhourly_revenue_requirement
FROM vspd.temp_benefits_halfhourly_load AS a
LEFT JOIN vspd.temp_benefits_halfhourly_gen AS b
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.record_date=b.record_date AND a.trading_period=b.trading_period
LEFT JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component;

UPDATE vspd.temp_benefits_halfhourly SET gross_gen_benefit=0 WHERE gross_gen_benefit IS NULL;
UPDATE vspd.temp_benefits_halfhourly SET scaling_factor_gross_halfhourly=(halfhourly_revenue_requirement / (gross_gen_benefit+gross_load_benefit)) WHERE (gross_gen_benefit+gross_load_benefit<>0) AND gross_gen_benefit+gross_load_benefit >= halfhourly_revenue_requirement;

CREATE TABLE vspd.temp_benefits_daily_load 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 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 (approach, variant, component, record_date);
CREATE INDEX idx ON vspd.temp_benefits_daily_gen  (approach, variant, component, record_date);

CREATE TABLE vspd.temp_benefits_daily 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 AS a
LEFT JOIN vspd.temp_benefits_daily_gen 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;

UPDATE vspd.temp_benefits_daily SET gross_gen_benefit=0 WHERE gross_gen_benefit IS NULL;
UPDATE vspd.temp_benefits_daily 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 TABLE vspd.temp_benefits_monthly AS
SELECT approach, variant, component, DATE_FORMAT(record_date,'%Y-%m') AS yearmonth, 
 SUM(gross_load_benefit) AS gross_load_benefit,  
 SUM(gross_gen_benefit) AS gross_gen_benefit,  
 daily_revenue_requirement/daily_revenue_requirement AS scaling_factor_gross_monthly,
 n_days*MAX(daily_revenue_requirement) AS monthly_revenue_requirement
FROM vspd.temp_benefits_daily AS a
INNER JOIN vspd.days_per_month AS b
ON YEAR(a.record_date)=b.record_year AND MONTH(a.record_date)=b.record_month
GROUP BY approach, variant, component, YEAR(record_date), MONTH(record_date);

UPDATE vspd.temp_benefits_monthly SET scaling_factor_gross_monthly=(monthly_revenue_requirement / (gross_gen_benefit+gross_load_benefit)) WHERE (gross_gen_benefit+gross_load_benefit<>0) AND gross_gen_benefit+gross_load_benefit >= monthly_revenue_requirement;

CREATE TABLE vspd.temp_benefits_4monthly AS
SELECT approach, variant, component, 
 SUM(gross_load_benefit) AS gross_load_benefit,
 SUM(gross_gen_benefit) AS gross_gen_benefit,
 monthly_revenue_requirement/monthly_revenue_requirement AS scaling_factor_gross_4monthly,
 SUM(monthly_revenue_requirement) AS 4monthly_revenue_requirement
FROM vspd.temp_benefits_monthly
GROUP BY approach, variant, component;

UPDATE vspd.temp_benefits_4monthly SET scaling_factor_gross_4monthly=(4monthly_revenue_requirement / (gross_gen_benefit+gross_load_benefit)) WHERE (gross_gen_benefit+gross_load_benefit<>0) AND gross_gen_benefit+gross_load_benefit >= 4monthly_revenue_requirement;

CREATE INDEX idx ON vspd.temp_benefits_halfhourly (approach, variant, component, record_date, trading_period);
CREATE INDEX idx ON vspd.temp_benefits_daily (approach, variant, component, record_date);
CREATE INDEX idx ON vspd.temp_benefits_monthly (approach, variant, component, yearmonth);
CREATE INDEX idx ON vspd.temp_benefits_4monthly (approach, variant, component);

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

CREATE TABLE vspd.temp_scaling_factors AS
SELECT b.approach, b.variant, b.component, b.record_date, a.trading_period,
  MAX(a.scaling_factor_gross_halfhourly) AS scaling_factor_gross_halfhourly,
  MAX(b.scaling_factor_gross_daily) AS scaling_factor_gross_daily,
  MAX(c.scaling_factor_gross_monthly) AS scaling_factor_gross_monthly,
  MAX(d.scaling_factor_gross_4monthly) AS scaling_factor_gross_4monthly
 FROM vspd.temp_benefits_halfhourly AS a
INNER JOIN vspd.temp_benefits_daily AS b
INNER JOIN vspd.temp_benefits_monthly AS c
INNER JOIN vspd.temp_benefits_4monthly AS d
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.record_date=b.record_date
 AND DATE_FORMAT(b.record_date,'%Y-%m')=c.yearmonth AND b.approach=c.approach AND b.variant=c.variant AND b.component=c.component
 AND b.approach=d.approach AND b.variant=d.variant AND b.component=d.component
GROUP BY b.approach, b.variant, b.component, b.record_date, a.trading_period; 

CREATE INDEX idx ON vspd.temp_scaling_factors(approach,variant,component,record_date,trading_period); 
 
CREATE TABLE vspd.charges AS
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.generator AS party, "offered_gen  " AS charge_type,
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_halfhourly AS charge_on_gross_benefit_with_halfhourly_capping, 
   (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, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_monthly AS charge_on_gross_benefit_with_monthly_capping, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_4monthly AS charge_on_gross_benefit_with_4monthly_capping
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.temp_scaling_factors AS b
  INNER JOIN vspd.offer_node_to_participant AS e
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.trading_period=b.trading_period
   AND a.node=e.offer_code)
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.generator AS party, "negative_load" AS charge_type,
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_halfhourly AS charge_on_gross_benefit_with_halfhourly_capping, 
   (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, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_monthly AS charge_on_gross_benefit_with_monthly_capping, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_4monthly AS charge_on_gross_benefit_with_4monthly_capping
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.temp_scaling_factors AS b
  INNER JOIN vspd.injecting_load_node_to_participant AS e
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.trading_period=b.trading_period
   AND a.node=e.injecting_load_node)
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.purchaser AS party, "load         " AS charge_type,
   e.proportion*(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_halfhourly AS charge_on_gross_benefit_with_halfhourly_capping, 
   e.proportion*(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, 
   e.proportion*(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_monthly AS charge_on_gross_benefit_with_monthly_capping, 
   e.proportion*(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_4monthly AS charge_on_gross_benefit_with_4monthly_capping
  FROM vspd.tpm_raw_allocations_load AS a
  INNER JOIN vspd.temp_scaling_factors AS b
  INNER JOIN test.reconciliation_allocations_for_spd_method_long AS e
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.trading_period=b.trading_period
   AND a.record_date=e.trading_date AND a.trading_period=e.trading_period AND a.node=e.poc)
;

# Aggregate to the monthly level

CREATE TABLE vspd.monthly_aggregated_charges AS
SELECT approach, variant, component, DATE_FORMAT(record_date,'%Y-%m') AS yearmonth, node, party, charge_type, region, island,
 SUM(charge_on_gross_benefit_with_halfhourly_capping)/1e6 AS charge_on_gross_benefit_with_halfhourly_capping,
 SUM(charge_on_gross_benefit_with_daily_capping)/1e6 AS charge_on_gross_benefit_with_daily_capping,
 SUM(charge_on_gross_benefit_with_monthly_capping)/1e6 AS charge_on_gross_benefit_with_monthly_capping,
 SUM(charge_on_gross_benefit_with_4monthly_capping)/1e6 AS charge_on_gross_benefit_with_4monthly_capping
FROM vspd.charges AS a
LEFT JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY approach, variant, component, DATE_FORMAT(record_date,'%Y-%m'), node, party, charge_type, region, island;

/* Alternately, use net (not gross) method. Charges on mass-market load still paid by retailers rather than networks */

# First calculate total net benefits by party, for various periods. 
# Negative net benefits over an entire period will not translate into negative charges (but what the period is, is still to be determined).
# Once negatives have been zeroed, capping will be applied on the same time period (so that the charges do not exceed the revenue requirement).

CREATE TABLE vspd.temp_net_benefits_by_party AS
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.generator AS party, "offered_gen  " AS charge_type, a.net_benefit
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.offer_node_to_participant AS e
  ON a.node=e.offer_code
  WHERE a.variant<>'3 years')
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.generator AS party, "negative_load" AS charge_type, a.net_benefit
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.injecting_load_node_to_participant AS e
  ON a.node=e.injecting_load_node
  WHERE a.variant<>'3 years')
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.purchaser AS party, "load         " AS charge_type, a.net_benefit*e.proportion AS net_benefit
  FROM vspd.tpm_raw_allocations_load AS a
  INNER JOIN test.reconciliation_allocations_for_spd_method_long AS e
  ON a.record_date=e.trading_date AND a.trading_period=e.trading_period AND a.node=e.poc
  WHERE a.variant<>'3 years')
;

CREATE TABLE vspd.temp_daily_net_benefits_by_party AS
SELECT approach, variant, component, DATE_FORMAT(record_date,'%Y-%m') AS yearmonth, record_date, party, SUM(net_benefit) AS net_benefit, 0000000000.00 AS floored_net_benefit
FROM vspd.temp_net_benefits_by_party
GROUP BY approach, variant, component, DATE_FORMAT(record_date,'%Y-%m'), record_date, party;

UPDATE vspd.temp_daily_net_benefits_by_party SET floored_net_benefit=net_benefit WHERE net_benefit>0;
CREATE INDEX idx ON vspd.temp_daily_net_benefits_by_party (approach, variant, component, record_date, yearmonth, party);

CREATE TABLE vspd.temp_monthly_net_benefits_by_party AS
SELECT approach, variant, component, yearmonth, party, SUM(net_benefit) AS net_benefit, 0000000000.00 AS floored_net_benefit, n_days
FROM vspd.temp_daily_net_benefits_by_party AS a
INNER JOIN vspd.days_per_month AS b
ON YEAR(record_date)=record_year AND MONTH(record_date)=record_month
GROUP BY approach, variant, component, yearmonth, party;

UPDATE vspd.temp_monthly_net_benefits_by_party SET floored_net_benefit=net_benefit WHERE net_benefit>0;
CREATE INDEX idx ON vspd.temp_monthly_net_benefits_by_party (approach, variant, component, yearmonth, party);

CREATE TABLE vspd.temp_4monthly_net_benefits_by_party AS
SELECT approach, variant, component, party, SUM(net_benefit) AS net_benefit, 0000000000.00 AS floored_net_benefit, SUM(n_days) AS n_days
FROM vspd.temp_monthly_net_benefits_by_party
GROUP BY approach, variant, component, party;

UPDATE vspd.temp_4monthly_net_benefits_by_party SET floored_net_benefit=net_benefit WHERE net_benefit>0;
CREATE INDEX idx ON vspd.temp_4monthly_net_benefits_by_party (approach, variant, component, party);

CREATE TABLE vspd.temp_total_daily_net_benefits AS
SELECT a.approach, a.variant, a.component, yearmonth, record_date, 
 SUM(floored_net_benefit) AS total_floored_net_benefit, 1e6*annual_revenue_requirement/365 AS daily_revenue_requirement, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor
FROM vspd.temp_daily_net_benefits_by_party AS a
INNER JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component
GROUP BY a.approach, a.variant, a.component, yearmonth, record_date;

UPDATE vspd.temp_total_daily_net_benefits SET scaling_factor=daily_revenue_requirement/total_floored_net_benefit WHERE total_floored_net_benefit>daily_revenue_requirement;
CREATE INDEX idx ON vspd.temp_total_daily_net_benefits (approach, variant, component, yearmonth, record_date);

CREATE TABLE vspd.temp_total_monthly_net_benefits AS
SELECT a.approach, a.variant, a.component, yearmonth, 
 SUM(floored_net_benefit) AS total_floored_net_benefit, 1e6*annual_revenue_requirement*n_days/365 AS monthly_revenue_requirement, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor
FROM vspd.temp_monthly_net_benefits_by_party AS a
INNER JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component
GROUP BY a.approach, a.variant, a.component, yearmonth;

UPDATE vspd.temp_total_monthly_net_benefits SET scaling_factor=monthly_revenue_requirement/total_floored_net_benefit WHERE total_floored_net_benefit>monthly_revenue_requirement;
CREATE INDEX idx ON vspd.temp_total_monthly_net_benefits (approach, variant, component, yearmonth);

CREATE TABLE vspd.temp_total_4monthly_net_benefits AS
SELECT a.approach, a.variant, a.component, 
 SUM(floored_net_benefit) AS total_floored_net_benefit, 1e6*annual_revenue_requirement*n_days/365 AS 4monthly_revenue_requirement, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor
FROM vspd.temp_4monthly_net_benefits_by_party AS a
INNER JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component
GROUP BY a.approach, a.variant, a.component;

UPDATE vspd.temp_total_4monthly_net_benefits SET scaling_factor=4monthly_revenue_requirement/total_floored_net_benefit WHERE total_floored_net_benefit>4monthly_revenue_requirement;
CREATE INDEX idx ON vspd.temp_total_4monthly_net_benefits (approach, variant, component);

CREATE TABLE vspd.temp_net_scaling_factors AS
SELECT a.approach, a.variant, a.component, a.yearmonth, a.record_date, a.party,
 MAX(CASE WHEN a.floored_net_benefit=0 THEN 0 ELSE d.scaling_factor END) AS daily_scaling_factor,
 MAX(CASE WHEN b.floored_net_benefit=0 THEN 0 ELSE e.scaling_factor END) AS monthly_scaling_factor,
 MAX(CASE WHEN c.floored_net_benefit=0 THEN 0 ELSE f.scaling_factor END) AS 4monthly_scaling_factor
FROM vspd.temp_daily_net_benefits_by_party AS a
INNER JOIN vspd.temp_monthly_net_benefits_by_party AS b
INNER JOIN vspd.temp_4monthly_net_benefits_by_party AS c
INNER JOIN vspd.temp_total_daily_net_benefits AS d
INNER JOIN vspd.temp_total_monthly_net_benefits AS e
INNER JOIN vspd.temp_total_4monthly_net_benefits AS f
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.yearmonth=b.yearmonth AND a.party=b.party
AND a.approach=c.approach AND a.variant=c.variant AND a.component=c.component AND a.party=c.party
AND a.approach=d.approach AND a.variant=d.variant AND a.component=d.component AND a.record_date=d.record_date
AND a.approach=e.approach AND a.variant=e.variant AND a.component=e.component AND a.yearmonth=e.yearmonth
AND a.approach=f.approach AND a.variant=f.variant AND a.component=f.component
GROUP BY a.approach, a.variant, a.component, a.yearmonth, a.record_date, a.party;

CREATE INDEX idx ON vspd.temp_net_scaling_factors (approach, variant, component, record_date, party);
CREATE INDEX idx ON vspd.temp_net_benefits_by_party (approach, variant, component, record_date, party);

CREATE TABLE vspd.net_charges AS
SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, a.party, a.charge_type, 
 a.net_benefit*b.daily_scaling_factor AS charge_on_net_benefit_with_daily_capping_and_flooring,
 a.net_benefit*b.monthly_scaling_factor AS charge_on_net_benefit_with_monthly_capping_and_flooring,
 a.net_benefit*b.4monthly_scaling_factor AS charge_on_net_benefit_with_4monthly_capping_and_flooring
FROM vspd.temp_net_benefits_by_party AS a
LEFT JOIN vspd.temp_net_scaling_factors AS b
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.record_date=b.record_date AND a.party=b.party;

# Aggregate to the monthly level

CREATE TABLE vspd.monthly_aggregated_charges_on_net_benefit AS
SELECT approach, variant, component, DATE_FORMAT(record_date,'%Y-%m') AS yearmonth, node, party, charge_type, region, island,
 SUM(charge_on_net_benefit_with_daily_capping_and_flooring)/1e6 AS charge_on_net_benefit_with_daily_capping_and_flooring,
 SUM(charge_on_net_benefit_with_monthly_capping_and_flooring)/1e6 AS charge_on_net_benefit_with_monthly_capping_and_flooring,
 SUM(charge_on_net_benefit_with_4monthly_capping_and_flooring)/1e6 AS charge_on_net_benefit_with_4monthly_capping_and_flooring
FROM vspd.net_charges AS a
LEFT JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY approach, variant, component, DATE_FORMAT(record_date,'%Y-%m'), node, party, charge_type, region, island;

/* Alternatively, use the net benefit method with no flooring step. Charges on mass-market load still paid by retailers rather than distributors. */

CREATE TABLE vspd.temp_total_daily_net_benefits_unfloored AS
SELECT a.approach, a.variant, a.component, yearmonth, record_date, 
 SUM(net_benefit) AS total_net_benefit, 1e6*annual_revenue_requirement/365 AS daily_revenue_requirement, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor
FROM vspd.temp_daily_net_benefits_by_party AS a
INNER JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component
WHERE a.approach='SPD' AND a.variant='4 month base approach'
GROUP BY a.approach, a.variant, a.component, yearmonth, record_date;

UPDATE vspd.temp_total_daily_net_benefits_unfloored SET scaling_factor=daily_revenue_requirement/total_net_benefit WHERE total_net_benefit>daily_revenue_requirement;
CREATE INDEX idx ON vspd.temp_total_daily_net_benefits_unfloored (approach, variant, component, yearmonth, record_date);

CREATE TABLE vspd.temp_total_monthly_net_benefits_unfloored AS
SELECT a.approach, a.variant, a.component, yearmonth, 
 SUM(net_benefit) AS total_net_benefit, 1e6*annual_revenue_requirement*n_days/365 AS monthly_revenue_requirement, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor
FROM vspd.temp_monthly_net_benefits_by_party AS a
INNER JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component
WHERE a.approach='SPD' AND a.variant='4 month base approach'
GROUP BY a.approach, a.variant, a.component, yearmonth;

UPDATE vspd.temp_total_monthly_net_benefits_unfloored SET scaling_factor=monthly_revenue_requirement/total_net_benefit WHERE total_net_benefit>monthly_revenue_requirement;
CREATE INDEX idx ON vspd.temp_total_monthly_net_benefits_unfloored (approach, variant, component, yearmonth);

CREATE TABLE vspd.temp_total_4monthly_net_benefits_unfloored AS
SELECT a.approach, a.variant, a.component, 
 SUM(net_benefit) AS total_net_benefit, 1e6*annual_revenue_requirement*n_days/365 AS 4monthly_revenue_requirement, 
 annual_revenue_requirement/annual_revenue_requirement AS scaling_factor
FROM vspd.temp_4monthly_net_benefits_by_party AS a
INNER JOIN vspd.annual_revenue_requirements AS c
ON a.approach=c.approach AND a.variant=c.variant AND a.component=c.component
WHERE a.approach='SPD' AND a.variant='4 month base approach'
GROUP BY a.approach, a.variant, a.component;

UPDATE vspd.temp_total_4monthly_net_benefits_unfloored SET scaling_factor=4monthly_revenue_requirement/total_net_benefit WHERE total_net_benefit>4monthly_revenue_requirement;
CREATE INDEX idx ON vspd.temp_total_4monthly_net_benefits_unfloored (approach, variant, component);

CREATE TABLE vspd.temp_net_scaling_factors_unfloored AS
SELECT a.approach, a.variant, a.component, a.yearmonth, a.record_date, a.party,
 d.scaling_factor AS daily_scaling_factor,
 e.scaling_factor AS monthly_scaling_factor,
 f.scaling_factor AS 4monthly_scaling_factor
FROM vspd.temp_daily_net_benefits_by_party AS a
INNER JOIN vspd.temp_monthly_net_benefits_by_party AS b
INNER JOIN vspd.temp_4monthly_net_benefits_by_party AS c
INNER JOIN vspd.temp_total_daily_net_benefits_unfloored AS d
INNER JOIN vspd.temp_total_monthly_net_benefits_unfloored AS e
INNER JOIN vspd.temp_total_4monthly_net_benefits_unfloored AS f
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.yearmonth=b.yearmonth AND a.party=b.party
AND a.approach=c.approach AND a.variant=c.variant AND a.component=c.component AND a.party=c.party
AND a.approach=d.approach AND a.variant=d.variant AND a.component=d.component AND a.record_date=d.record_date
AND a.approach=e.approach AND a.variant=e.variant AND a.component=e.component AND a.yearmonth=e.yearmonth
AND a.approach=f.approach AND a.variant=f.variant AND a.component=f.component
GROUP BY a.approach, a.variant, a.component, a.yearmonth, a.record_date, a.party;

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

CREATE TABLE vspd.net_charges_unfloored AS
SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, a.party, a.charge_type, 
 a.net_benefit*b.daily_scaling_factor AS charge_on_net_benefit_with_daily_capping,
 a.net_benefit*b.monthly_scaling_factor AS charge_on_net_benefit_with_monthly_capping,
 a.net_benefit*b.4monthly_scaling_factor AS charge_on_net_benefit_with_4monthly_capping
FROM vspd.temp_net_benefits_by_party AS a
INNER JOIN vspd.temp_net_scaling_factors_unfloored AS b
ON a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.record_date=b.record_date AND a.party=b.party;

# Aggregate to the monthly level

CREATE TABLE vspd.monthly_aggregated_charges_on_net_benefit_unfloored AS
SELECT approach, variant, component, DATE_FORMAT(record_date,'%Y-%m') AS yearmonth, node, party, charge_type, region, island,
 SUM(charge_on_net_benefit_with_daily_capping)/1e6 AS charge_on_net_benefit_with_daily_capping,
 SUM(charge_on_net_benefit_with_monthly_capping)/1e6 AS charge_on_net_benefit_with_monthly_capping,
 SUM(charge_on_net_benefit_with_4monthly_capping)/1e6 AS charge_on_net_benefit_with_4monthly_capping
FROM vspd.net_charges_unfloored AS a
LEFT JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY approach, variant, component, DATE_FORMAT(record_date,'%Y-%m'), node, party, charge_type, region, island;

/* Now, back to the gross method - but with charges on mass-market load paid by networks rather than retailers */

CREATE TABLE vspd.charges_by_network AS
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.generator AS party, "offered_gen  " AS charge_type,
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_halfhourly AS charge_on_gross_benefit_with_halfhourly_capping, 
   (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, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_monthly AS charge_on_gross_benefit_with_monthly_capping, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_4monthly AS charge_on_gross_benefit_with_4monthly_capping
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.temp_scaling_factors AS b
  INNER JOIN vspd.offer_node_to_participant AS e
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.trading_period=b.trading_period
   AND a.node=e.offer_code)
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.generator AS party, "negative_load" AS charge_type,
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_halfhourly AS charge_on_gross_benefit_with_halfhourly_capping, 
   (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, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_monthly AS charge_on_gross_benefit_with_monthly_capping, 
   (CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_4monthly AS charge_on_gross_benefit_with_4monthly_capping
  FROM vspd.tpm_raw_allocations_gen AS a
  INNER JOIN vspd.temp_scaling_factors AS b
  INNER JOIN vspd.injecting_load_node_to_participant AS e
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.trading_period=b.trading_period
   AND a.node=e.injecting_load_node)
UNION ALL
  (SELECT a.approach, a.variant, a.component, a.record_date, a.trading_period, a.node, e.purchaser AS party, "load         " AS charge_type,
   e.proportion*(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_halfhourly AS charge_on_gross_benefit_with_halfhourly_capping, 
   e.proportion*(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, 
   e.proportion*(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_monthly AS charge_on_gross_benefit_with_monthly_capping, 
   e.proportion*(CASE WHEN a.net_benefit<0 THEN 0 ELSE a.net_benefit END)*b.scaling_factor_gross_4monthly AS charge_on_gross_benefit_with_4monthly_capping
  FROM vspd.tpm_raw_allocations_load AS a
  INNER JOIN vspd.temp_scaling_factors AS b
  INNER JOIN test.reconciliation_allocations_by_network_long AS e
  ON a.record_date=b.record_date AND a.approach=b.approach AND a.variant=b.variant AND a.component=b.component AND a.trading_period=b.trading_period
   AND a.record_date=e.trading_date AND a.trading_period=e.trading_period AND a.node=e.poc)
;

CREATE TABLE vspd.monthly_aggregated_charges_by_network AS
SELECT approach, variant, component, DATE_FORMAT(record_date,'%Y-%m') AS yearmonth, node, party, charge_type, region, island,
 SUM(charge_on_gross_benefit_with_halfhourly_capping)/1e6 AS charge_on_gross_benefit_with_halfhourly_capping,
 SUM(charge_on_gross_benefit_with_daily_capping)/1e6 AS charge_on_gross_benefit_with_daily_capping,
 SUM(charge_on_gross_benefit_with_monthly_capping)/1e6 AS charge_on_gross_benefit_with_monthly_capping,
 SUM(charge_on_gross_benefit_with_4monthly_capping)/1e6 AS charge_on_gross_benefit_with_4monthly_capping
FROM vspd.charges_by_network AS a
LEFT JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY approach, variant, component, DATE_FORMAT(record_date,'%Y-%m'), node, party, charge_type, region, island;

