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

# 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%
# - some economic investments are not covered by the AoB or SPD charges because they are partly or wholly deeper connection.

# 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_A (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_A WHERE option_no=1;

# Deeper connection charges less LCE - pre 2016 investments
INSERT application_A
SELECT 1, 'Deep connection', 'Deep connection', 'load', a.customer, charge_deep_connection_m_ni+charge_deep_connection_m_si-rental_m
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE NOT (a.customer IN (SELECT generator FROM offer_node_to_participant))
  UNION ALL
SELECT 1, 'Deep connection', 'Deep connection', 'NI gen', a.customer, charge_deep_connection_m_ni+charge_deep_connection_m_si-rental_m
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE (a.customer='MRP' OR a.customer='NAP JV' OR a.customer='Todd' OR a.customer='Mokai JV')
  UNION ALL
SELECT 1, 'Deep connection', 'Deep connection', 'NI gen', a.customer, charge_deep_connection_m_ni-rental_m*charge_deep_connection_m_ni/(charge_deep_connection_m_ni+charge_deep_connection_m_si)
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE (a.customer IN (SELECT generator FROM offer_node_to_participant)) AND NOT (a.customer='MRP' OR a.customer='NAP JV' OR a.customer='Todd' OR a.customer='Mokai JV')
  UNION ALL
SELECT 1, 'Deep connection', 'Deep connection', 'SI gen', a.customer, charge_deep_connection_m_si-rental_m*charge_deep_connection_m_si/(charge_deep_connection_m_ni+charge_deep_connection_m_si)
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE (a.customer IN (SELECT generator FROM offer_node_to_participant)) AND NOT (a.customer='MRP' OR a.customer='NAP JV' OR a.customer='Todd' OR a.customer='Mokai JV');

# Deeper connection charges - post 2016 investments
# Assume LCE offsets charges by 4.85% for these investments
INSERT application_A
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_A
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
# HARD-CODED - LCE is 4.85% of revenue requirement
INSERT application_A
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;

# HARD-CODED - some economic investments are partly or wholly deeper connection
DELETE FROM application_A WHERE option_no=1 AND charge='AoB' AND SUBSTRING(component,1,14)='LSI Renewables'; # is deeper connection
UPDATE application_A SET charge_m = charge_m/2 WHERE option_no=1 AND charge='AoB' AND SUBSTRING(component,1,13)='Wairakei Ring'; # is half deeper connection

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

# Capacity-based residual charge
INSERT application_A
SELECT 1, 'Residual','Load - by AMD', 'load', purchaser, residual_m*amd/sum_load_capacity
FROM amd AS a
INNER JOIN combination_working_1a AS b
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;

INSERT application_A
SELECT 1, 'Residual','Load - by ICPs', 'load', customer, residual_m*nominal_mva/sum_load_capacity
FROM icp_charge_allocation_by_customer AS a
INNER JOIN combination_working_1a AS b
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;


# Option 2 - 'Base Option'

DELETE FROM application_A WHERE option_no=2;

# Deeper connection - same as option 1
INSERT application_A
SELECT 2, charge, component, category, customer, charge_m
FROM application_A WHERE option_no=1 AND charge='Deep connection';

# AoB charges
# HARD-CODED - LCE is 4.85% of revenue requirement
INSERT application_A
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;

# HARD-CODED - some economic investments are partly or wholly deeper connection
DELETE FROM application_A WHERE option_no=2 AND charge='AoB' AND SUBSTRING(component,1,14)='LSI Renewables'; # is deeper connection
UPDATE application_A SET charge_m = charge_m/2 WHERE option_no=2 AND charge='AoB' AND SUBSTRING(component,1,13)='Wairakei Ring'; # is half deeper connection

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

# Capacity-based residual charge
INSERT application_A
SELECT 2, 'Residual','Load - by AMD', 'load', purchaser, residual_m*amd/sum_load_capacity
FROM amd AS a
INNER JOIN combination_working_1a AS b
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;

INSERT application_A
SELECT 2, 'Residual','Load - by ICPs', 'load', customer, residual_m*nominal_mva/sum_load_capacity
FROM icp_charge_allocation_by_customer AS a
INNER JOIN combination_working_1a AS b
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;


# Option 3 - 'Base + SPD'

DELETE FROM application_A WHERE option_no=3;

# Deeper connection - same as option 1
INSERT application_A
SELECT 3, charge, component, category, customer, charge_m
FROM application_A WHERE option_no=1 AND charge='Deep connection';

# SPD charge - note LCE has already been allowed for
# HARD-CODED - Only some investments are eligible
INSERT application_A
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='Pole 3' OR 
 investment='Pole 2' OR 
 investment='LSI Renewables' OR 
 investment='Wairakei Ring' OR 
 investment='BPE-HAY' OR 
 investment='OTB-HAY' OR 
0);

# AoB charges on what is left after SPD charge
# HARD-CODED - LCE is 4.85% of revenue requirement
CREATE TABLE combination_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_A WHERE option_no=3 AND charge='SPD' GROUP BY component) AS b
ON a.investment=b.investment
GROUP BY a.investment;
INSERT application_A
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_working_3a AS b
ON a.investment=b.investment;

# HARD-CODED - some economic investments are partly or wholly deeper connection
DELETE FROM application_A WHERE option_no=3 AND charge='AoB' AND SUBSTRING(component,1,14)='LSI Renewables'; # is deeper connection
UPDATE application_A SET charge_m = charge_m/2 WHERE option_no=3 AND charge='AoB' AND SUBSTRING(component,1,13)='Wairakei Ring'; # is half deeper connection
DELETE FROM application_A WHERE option_no=3 AND charge='SPD' AND SUBSTRING(component,1,14)='LSI Renewables'; # is deeper connection
UPDATE application_A SET charge_m = charge_m/2 WHERE option_no=3 AND charge='SPD' AND SUBSTRING(component,1,13)='Wairakei Ring'; # is half deeper connection

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

# Capacity-based residual charge
INSERT application_A
SELECT 3, 'Residual','Load - by AMD', 'load', purchaser, residual_m*amd/sum_load_capacity
FROM amd AS a
INNER JOIN combination_working_1a AS b
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;

INSERT application_A
SELECT 3, 'Residual','Load - by ICPs', 'load', customer, residual_m*nominal_mva/sum_load_capacity
FROM icp_charge_allocation_by_customer AS a
INNER JOIN combination_working_1a AS b
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;


# Option 5 - Status quo

DELETE FROM application_A 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_A
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_A
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_A
SELECT 5, "HVDC", "HVDC", 'SI gen', party, charge_m
FROM hvdc_charge;

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


## A bit of tidying up

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

## Tests

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

## Key outputs

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

# Note 'Other ind' includes Rayonier and Daiken MDF. UNI includes Counties.
CREATE TABLE application_A_stacked_bar AS
SELECT option_no, 'Other ind    ' AS bar, charge, SUM(charge_m) AS charge_m FROM application_A 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_A WHERE customer='Pacific Aluminium' GROUP BY option_no, charge
UNION ALL
SELECT option_no, category, charge, SUM(charge_m) FROM application_A 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_A 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_A 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_A 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_A_stacked_bar GROUP BY option_no, bar, charge ORDER BY option_no, charge, bar;
SELECT option_no, bar, SUM(charge_m)/3 FROM application_A_stacked_bar GROUP BY option_no, bar;

# Average annual volumes, used in calculating $/MWh charges

SELECT (case when a.customer='Pacific Aluminium' then 'NZAS' else a.customer end) as customer, a.gwh*b.net_to_gross/3
FROM load_customer_volumes AS a
INNER JOIN load_customer_net_to_gross_approximate_conversion AS b
ON a.customer=b.customer;

SELECT b.generator, SUM((CASE WHEN demand<0 THEN -demand ELSE 0 END)/6e3) AS average_GWh
FROM o_raw_node_ir AS a
INNER JOIN injecting_load_node_to_participant AS b
ON a.node=b.injecting_load_node
WHERE runtype='baseTPM2'
GROUP BY b.generator;

SELECT b.generator, SUM(generation/6e3) AS average_GWh
FROM o_raw_node_ir AS a
INNER JOIN offer_node_to_participant AS b
ON a.node=b.offer_code
WHERE runtype='baseTPM2'
GROUP BY b.generator;

# Incidence of deeper connection charges

SELECT customer, SUM(charge_m)/3 FROM application_A WHERE option_no=2 AND charge='Deep connection' GROUP BY customer;

## Calculate generator pass-through

CREATE TABLE combination_working_2 AS
SELECT option_no, SUM(charge_m) AS gg_charge_m FROM application_A
WHERE category<>'load'
GROUP BY option_no;

CREATE TABLE combination_passthrough_by_customer AS
SELECT option_no, customer, gg_charge_m*gwh/sum_of_gwh AS full_passthrough
FROM load_customer_volumes AS a
INNER JOIN combination_working_2 AS b
INNER JOIN (SELECT SUM(gwh) AS sum_of_gwh FROM load_customer_volumes) AS c;

CREATE INDEX idx ON combination_passthrough_by_customer (option_no, customer);

SELECT option_no, 0 AS passthrough_0, 0.5*1000*gg_charge_m/sum_of_gwh AS passthrough_50, 1000*gg_charge_m/sum_of_gwh AS passthrough_100
FROM combination_working_2 AS b
INNER JOIN (SELECT SUM(gwh) AS sum_of_gwh FROM load_customer_volumes) AS c;

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