# This script calculates the incidence of charges under Application B.

# First run all other SQLs for the various options involved.

# Hard-coded assumptions include that:
# - revenue requirement is $855M per year
# - non-connection post-FTR LCE is $50M per year, revenue requirement is $855M per year
# - LCE offsets revenue requirements for some charges by 4.85%
# - BPE-HAY and OTB-HAY are commissioned after mid-2016, with the revenue requirement for OTB-HAY being approx. $3M per year
# - Norske Skog pays nil interconnection charge under status quo arrangements - but:
# - Under application B, floor on residual charge is $5.7/MWh (applied to gross load).

# Note that (for historical reasons) the options are listed here in a different order from how they are shown in the consultation paper.

USE tpm2;

CREATE TABLE application_B (option_no INT, charge VARCHAR(20), component VARCHAR(100), category VARCHAR(10), customer VARCHAR(30), charge_m DOUBLE);

# Option 1 - 'Base + LRMC'

DELETE FROM application_B WHERE option_no=1;

# Deeper connection charges - post 2016 investments
# Assume LCE offsets charges by 4.85% for these investments
INSERT application_B
SELECT 1, 'Deep connection', 'Deep connection', 'load', customer, SUM(total_charge_m)*(1-0.0485)
FROM deep_connection_charges_application_b 
WHERE generation='N'
GROUP BY customer
  UNION ALL
SELECT 1, 'Deep connection', 'Deep connection', 'NI gen', customer, SUM(total_charge_m)*(1-0.0485)
FROM deep_connection_charges_application_b 
WHERE generation='Y' AND island='N'
GROUP BY customer
  UNION ALL
SELECT 1, 'Deep connection', 'Deep connection', 'SI gen', customer, SUM(total_charge_m)*(1-0.0485)
FROM deep_connection_charges_application_b 
WHERE generation='Y' AND island='S'
GROUP BY customer
;

# LRMC (MIC) charges. No LCE offset is applicable
INSERT application_B
SELECT 1, 'MIC', investment, (CASE WHEN category='SI gen' OR category='NI gen' THEN category ELSE 'load' END), customer, LRMC_charge_m 
FROM LRMC_charge_on_congestion_allocation_by_customer;

# AoB charges for post-2016 investments
# HARD-CODED - LCE is 4.85% of revenue requirement
INSERT application_B
SELECT 1, 'AoB', CONCAT(investment," - ",charge_group), (CASE WHEN category='SI gen' OR category='NI gen' THEN category ELSE 'load' END), customer, charge_m*(1-0.0485) 
FROM aob_capacity_charges_by_customer
WHERE investment='BPE-HAY' OR investment='OTB-HAY';

# Status quo HVDC charge - less an assumed $3M for OTB-HAY which was covered under the HVDC charge for application A, but is covered under AoB and/or SPD for application B
INSERT application_B
SELECT 1, "HVDC", "HVDC", 'SI gen', party, charge_m*127/130
FROM hvdc_charge;
INSERT application_B
SELECT 1, "HVDC", "Rebate", 'SI gen', party, -rebate_m*127/130
FROM hvdc_lce_rebate;

# How much residual charge is to be recovered?
# HARD-CODED - non-connection post-FTR LCE is $50M per year, revenue requirement is $855M per year
CREATE TABLE combination_appB_working_1a AS
SELECT 3*(855-50)-SUM(charge_m) AS residual_m
FROM application_B
WHERE option_no=1;

# Status quo residual charge
CREATE TABLE rcpd_allocation AS
SELECT a.customer, SUM(a.charge_m) / b.sum_charge_m AS proportion
FROM rcpd_charges_by_customer AS a
INNER JOIN (SELECT SUM(charge_m) AS sum_charge_m FROM rcpd_charges_by_customer WHERE customer<>'Other') AS b
WHERE customer<>'Other'
GROUP BY a.customer;

INSERT application_B
SELECT 1, 'Residual','Load', 'load', customer, residual_m*proportion
FROM rcpd_allocation AS a
INNER JOIN combination_appB_working_1a
WHERE (customer<>'Other');

# HARD-CODED - Norske Skog pays nil interconnection charge under status quo arrangements
INSERT application_B
SELECT 1, 'Residual','Load', 'load', 'Norske Skog', 0;

# Place a floor of $5.7/MWh (applied to gross load) on the residual charge
CREATE TABLE residual_charging_rates AS
SELECT a.customer, 1000*gwh*net_to_gross AS mwh, charge_m, a.charge_m*1e6/(1000*gwh*net_to_gross) AS charging_rate
FROM application_B AS a
INNER JOIN load_customer_volumes AS b
ON a.customer=b.customer
INNER JOIN load_customer_net_to_gross_approximate_conversion AS d
ON a.customer=d.customer
WHERE option_no=1 AND charge='Residual';

INSERT application_B
SELECT 1, 'Residual', 'Adjustment', 'load', customer, mwh*(5.7-charging_rate)/1e6  
FROM residual_charging_rates
WHERE charging_rate<5.7;

INSERT application_B
SELECT 1, 'Residual', 'Adjustment', 'load', c.customer, -b.to_return*c.charge_m/d.sum_charge_m
FROM residual_charging_rates AS c
INNER JOIN (SELECT SUM(charge_m) AS to_return FROM application_B WHERE option_no=1 AND component='Adjustment') AS b
INNER JOIN (SELECT SUM(charge_m) AS sum_charge_m FROM residual_charging_rates WHERE charging_rate>=5.7) AS d
WHERE c.charging_rate>=5.7
;


# Option 2 - 'Base Option'

DELETE FROM application_B WHERE option_no=2;

# Deep connection - note LCE has already been subtracted
INSERT application_B
SELECT 2, charge, component, category, customer, charge_m
FROM application_B WHERE option_no=1 AND charge='Deep connection';

# HARD-CODED - LCE is 4.85% of non-deeper-connection revenue requirement
INSERT application_B
SELECT 2, 'AoB', CONCAT(investment," - ",charge_group), (CASE WHEN category='SI gen' OR category='NI gen' THEN category ELSE 'load' END), customer, charge_m*(1-0.0485) 
FROM aob_capacity_charges_by_customer
WHERE investment='BPE-HAY' OR investment='OTB-HAY';

# Status quo HVDC charge - less an assumed $3M for OTB-HAY which was covered under the HVDC charge for application A, but is covered under AoB and/or SPD for application B
INSERT application_B
SELECT 2, "HVDC", "HVDC", 'SI gen', party, charge_m*127/130
FROM hvdc_charge;
INSERT application_B
SELECT 2, "HVDC", "Rebate", 'SI gen', party, -rebate_m*127/130
FROM hvdc_lce_rebate;

# How much residual charge is to be recovered?
# HARD-CODED - non-connection post-FTR LCE is $50M per year, revenue requirement is $855M per year
CREATE TABLE combination_appB_working_1a AS
SELECT 3*(855-50)-SUM(charge_m) AS residual_m
FROM application_B
WHERE option_no=2;

# Status quo residual charge
INSERT application_B
SELECT 2, 'Residual','Load', 'load', customer, residual_m*proportion
FROM rcpd_allocation AS a
INNER JOIN combination_appB_working_1a
WHERE (customer<>'Other');

# HARD-CODED - Norske Skog pays nil interconnection charge under status quo arrangements
INSERT application_B
SELECT 2, 'Residual','Load', 'load', 'Norske Skog', 0;

# Place a floor of $5.7/MWh (applied to gross load) on the residual charge
CREATE TABLE residual_charging_rates AS
SELECT a.customer, 1000*gwh*net_to_gross AS mwh, charge_m, a.charge_m*1e6/(1000*gwh*net_to_gross) AS charging_rate
FROM application_B AS a
INNER JOIN load_customer_volumes AS b
ON a.customer=b.customer
INNER JOIN load_customer_net_to_gross_approximate_conversion AS d
ON a.customer=d.customer
WHERE option_no=2 AND charge='Residual';

INSERT application_B
SELECT 2, 'Residual', 'Adjustment', 'load', customer, mwh*(5.7-charging_rate)/1e6  
FROM residual_charging_rates
WHERE charging_rate<5.7;

INSERT application_B
SELECT 2, 'Residual', 'Adjustment', 'load', c.customer, -b.to_return*c.charge_m/d.sum_charge_m
FROM residual_charging_rates AS c
INNER JOIN (SELECT SUM(charge_m) AS to_return FROM application_B WHERE option_no=2 AND component='Adjustment') AS b
INNER JOIN (SELECT SUM(charge_m) AS sum_charge_m FROM residual_charging_rates WHERE charging_rate>=5.7) AS d
WHERE c.charging_rate>=5.7
;


# Option 3 - 'Base + SPD'

DELETE FROM application_B WHERE option_no=3;

INSERT application_B
SELECT 3, charge, component, category, customer, charge_m
FROM application_B WHERE option_no=1 AND charge='Deep connection';

# SPD charge - note LCE has already been allowed for
INSERT application_B
SELECT 3, 'SPD', investment, (CASE WHEN island='S' AND category='generation' THEN 'SI gen' WHEN island='S' AND category='generation' THEN 'NI gen' ELSE 'load' END), customer, charge_with_monthly_capping_m
FROM spd_charges_by_customer
WHERE investment='BPE-HAY' OR investment='OTB-HAY';

# AoB charges on what is left after SPD charge
# HARD-CODED - LCE is 4.85% of non-deeper-connection revenue requirement
CREATE TABLE combination_appB_working_3a AS 
SELECT a.investment, 1-SUM(charge_m)/(amount_m_per_year*3*(1-0.0485)) AS aob_proportion
FROM aob_amounts_to_recover AS a
INNER JOIN (SELECT component AS investment, SUM(charge_m) AS charge_m FROM application_B WHERE option_no=3 AND charge='SPD' GROUP BY component) AS b
ON a.investment=b.investment
GROUP BY a.investment;
INSERT application_B
SELECT 3, 'AoB', CONCAT(a.investment," - ",charge_group), (CASE WHEN category='SI gen' OR category='NI gen' THEN category ELSE 'load' END), customer, charge_m*(1-0.0485)*aob_proportion 
FROM aob_capacity_charges_by_customer AS a
INNER JOIN combination_appB_working_3a AS b
ON a.investment=b.investment;

# Status quo HVDC charge - less an assumed $3M for OTB-HAY which was covered under the HVDC charge for application A, but is covered under AoB and/or SPD for application B
INSERT application_B
SELECT 3, "HVDC", "HVDC", 'SI gen', party, charge_m*127/130
FROM hvdc_charge;
INSERT application_B
SELECT 3, "HVDC", "Rebate", 'SI gen', party, -rebate_m*127/130
FROM hvdc_lce_rebate;

# How much residual charge is to be recovered?
# HARD-CODED - non-connection post-FTR LCE is $50M per year, revenue requirement is $855M per year
CREATE TABLE combination_appB_working_1a AS
SELECT 3*(855-50)-SUM(charge_m) AS residual_m
FROM application_B
WHERE option_no=3;

# Status quo residual charge
INSERT application_B
SELECT 3, 'Residual','Load', 'load', customer, residual_m*proportion
FROM rcpd_allocation AS a
INNER JOIN combination_appB_working_1a
WHERE (customer<>'Other');

# HARD-CODED - Norske Skog pays nil interconnection charge under status quo arrangements
INSERT application_B
SELECT 3, 'Residual','Load', 'load', 'Norske Skog', 0;

# Place a floor of $5.7/MWh (applied to gross load) on the residual charge
CREATE TABLE residual_charging_rates AS
SELECT a.customer, 1000*gwh*net_to_gross AS mwh, charge_m, a.charge_m*1e6/(1000*gwh*net_to_gross) AS charging_rate
FROM application_B AS a
INNER JOIN load_customer_volumes AS b
ON a.customer=b.customer
INNER JOIN load_customer_net_to_gross_approximate_conversion AS d
ON a.customer=d.customer
WHERE option_no=3 AND charge='Residual';

INSERT application_B
SELECT 3, 'Residual', 'Adjustment', 'load', customer, mwh*(5.7-charging_rate)/1e6  
FROM residual_charging_rates
WHERE charging_rate<5.7;

INSERT application_B
SELECT 3, 'Residual', 'Adjustment', 'load', c.customer, -b.to_return*c.charge_m/d.sum_charge_m
FROM residual_charging_rates AS c
INNER JOIN (SELECT SUM(charge_m) AS to_return FROM application_B WHERE option_no=3 AND component='Adjustment') AS b
INNER JOIN (SELECT SUM(charge_m) AS sum_charge_m FROM residual_charging_rates WHERE charging_rate>=5.7) AS d
WHERE c.charging_rate>=5.7
;

# Option 5 - Status quo

DELETE FROM application_B WHERE option_no=5;

# RCPD charge and LCE rebate
# Hard-coded - CHH and NZ Steel pay in their capacity as loads, not as generators
INSERT application_B
SELECT 5, "RCPD", "RCPD", 
 (CASE WHEN customer IN (SELECT generator FROM offer_node_to_participant) AND customer<>'CHH' AND customer<>'NZ Steel' THEN (CASE WHEN island='N' THEN 'NI gen' ELSE 'SI gen' END) ELSE "load" END), 
 customer, charge_m
FROM rcpd_charges_by_customer;

INSERT application_B
SELECT 5, "RCPD", "Rebate", 
 (CASE WHEN customer IN (SELECT generator FROM offer_node_to_participant) AND customer<>'CHH' AND customer<>'NZ Steel' THEN (CASE WHEN island='N' THEN 'NI gen' ELSE 'SI gen' END) ELSE "load" END), 
 customer, -rebate_m
FROM rcpd_rebates_by_customer;

# HVDC charge and LCE rebate
INSERT application_B
SELECT 5, "HVDC", "HVDC", 'SI gen', party, charge_m
FROM hvdc_charge;

INSERT application_B
SELECT 5, "HVDC", "Rebate", 'SI gen', party, -rebate_m
FROM hvdc_lce_rebate;


## A bit of tidying up

DELETE FROM application_B WHERE charge_m=0;
UPDATE application_B SET category=TRIM(category);
UPDATE application_B set customer='NZAS' where customer='Pacific Aluminium';

## Tests

SELECT option_no, ROUND(SUM(charge_m)) FROM application_B GROUP BY option_no; # Should be 2415 for all

## Key outputs

SELECT option_no, charge, SUM(charge_m)/3 FROM application_B GROUP BY option_no, charge;
SELECT option_no, charge, component, SUM(charge_m)/3 FROM application_B GROUP BY option_no, charge, component ORDER BY option_no, charge, component;
SELECT option_no, customer, SUM(charge_m)/3 FROM application_B GROUP BY option_no, customer;

# Note 'Other ind' includes Rayonier and Daiken MDF. UNI includes Counties.
CREATE TABLE application_B_stacked_bar AS
SELECT option_no, 'Other ind    ' AS bar, charge, SUM(charge_m) AS charge_m FROM application_B WHERE category='load' AND customer<>'Pacific Aluminium' AND (customer IN (SELECT DISTINCT purchaser FROM amd)) GROUP BY option_no, charge
UNION ALL
SELECT option_no, 'Tiwai', charge, SUM(charge_m) FROM application_B WHERE customer='Pacific Aluminium' GROUP BY option_no, charge
UNION ALL
SELECT option_no, category, charge, SUM(charge_m) FROM application_B WHERE category='NI gen' OR category='SI gen' GROUP BY option_no, category, charge
UNION ALL
SELECT option_no, 'UNI MM load', charge, SUM(charge_m) FROM application_B WHERE category='load' AND customer IN (SELECT network FROM network_to_island WHERE half_island='UNI') GROUP BY option_no, charge
UNION ALL
SELECT option_no, 'LNI MM load', charge, SUM(charge_m) FROM application_B WHERE category='load' AND customer IN (SELECT network FROM network_to_island WHERE half_island='LNI') GROUP BY option_no, charge
UNION ALL
SELECT option_no, 'SI MM load', charge, SUM(charge_m) FROM application_B WHERE category='load' AND customer IN (SELECT network FROM network_to_island WHERE island='S') GROUP BY option_no, charge;

SELECT option_no, bar, charge, SUM(charge_m)/3 FROM application_B_stacked_bar GROUP BY option_no, bar, charge ORDER BY option_no, charge, bar;
SELECT option_no, bar, SUM(charge_m)/3 FROM application_B_stacked_bar GROUP BY option_no, bar;

# Copy the results of the above queries into 'Results_-_Application_B.xlsx', as directed in the spreadsheet.
