# This script calculates status quo interconnection charges and LCE rebates. 
#  (LCE is a separate rebate, rather an offset against the amount to be recovered.)

# Run 'load_interconnection_lookups.sql' first.

# Hard-coded assumptions include that:
# - there is $720M p.a. to be recovered (just a placeholder - charges are scaled later so that they add up to the correct amount)
# - the interconnection LCE rebate is $45M per year
# - 32 MW (representing Cobb, now embedded) is subtracted from Network Tasman's RCPD quantity.

# *** The calculation approach here is approximate and includes several simplifying assumptions. 
# *** Do not rely on it to form conclusions about the interconnection charges you will pay.
# *** Contact Transpower if you have any questions about the interconnection charges you will pay.

# In particular:
# - all GLN charges are allocated to NZ Steel (it would be more correct to split out Counties Power's charges at GLN0332)
# - the (small) charges on grid-connected generators that sometimes offtake may not be correct in some cases.

# The script uses a dataset 'reconciliation_allocations_for_spd_method_3yr_long' that has not been published because it is commercially sensitive.
# Therefore, you will not be able to run this code in the form shown below.
# However, so that you can reproduce the remainder of the analysis, the key outputs of this script have been provided in .csv form and can be loaded by running the commented-out code below.

USE tpm2;

CREATE TABLE rcpd_working_0 AS
SELECT record_date, tp, rcpd_aggregation, SUBSTRING(rcpd_aggregation,1,3) AS prefix, SUM(net_demand-generation) AS net_load_mw
FROM o_raw_node_ir AS a
INNER JOIN rcpd_mapping AS b
ON a.node=b.node
WHERE a.runtype='baseTPM2'
GROUP BY record_date, tp, rcpd_aggregation
HAVING SUM(net_demand-generation)>0;

CREATE INDEX idx ON rcpd_working_0 (record_date, tp, rcpd_aggregation);

CREATE TABLE rcpd_working_1 AS
SELECT record_date, tp, b.rcpd_region, SUM(net_load_mw) AS load_mw
FROM rcpd_working_0 AS a
INNER JOIN prefix_to_rcpd_region AS b
ON a.prefix=b.prefix
GROUP BY record_date, tp, b.rcpd_region;

CREATE TABLE rcpd_working_2_2017 AS
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='UNI' AND YEAR(record_date)=2011 ORDER BY load_mw DESC LIMIT 12)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LNI' AND YEAR(record_date)=2011 ORDER BY load_mw DESC LIMIT 100)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='USI' AND YEAR(record_date)=2011 ORDER BY load_mw DESC LIMIT 12)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LSI' AND YEAR(record_date)=2011 ORDER BY load_mw DESC LIMIT 100)
;

CREATE TABLE rcpd_working_2_2018 AS
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='UNI' AND YEAR(record_date)=2012 ORDER BY load_mw DESC LIMIT 12)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LNI' AND YEAR(record_date)=2012 ORDER BY load_mw DESC LIMIT 100)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='USI' AND YEAR(record_date)=2012 ORDER BY load_mw DESC LIMIT 12)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LSI' AND YEAR(record_date)=2012 ORDER BY load_mw DESC LIMIT 100)
;

CREATE TABLE rcpd_working_2_2019 AS
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='UNI' AND YEAR(record_date)=2013 ORDER BY load_mw DESC LIMIT 12)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LNI' AND YEAR(record_date)=2013 ORDER BY load_mw DESC LIMIT 100)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='USI' AND YEAR(record_date)=2013 ORDER BY load_mw DESC LIMIT 12)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LSI' AND YEAR(record_date)=2013 ORDER BY load_mw DESC LIMIT 100)
;

CREATE TABLE rcpd_working_2 AS
SELECT *, 2017 AS record_year FROM rcpd_working_2_2017
 UNION ALL
SELECT *, 2018 AS record_year FROM rcpd_working_2_2018
 UNION ALL
SELECT *, 2019 AS record_year FROM rcpd_working_2_2019
;
 
CREATE INDEX idx ON rcpd_working_2 (record_date, tp, rcpd_region);

# HARD-CODED - Cobb subtracts 32 MW from USI
CREATE TABLE rcpd_working_3 AS
SELECT record_year, rcpd_region, SUM(CASE WHEN rcpd_region='USI' THEN load_mw-32 ELSE load_mw END)/COUNT(*) AS mean_mw_in_rcpd_periods
FROM rcpd_working_2
GROUP BY record_year, rcpd_region;

# HARD CODED - $720M P.A. TO BE RECOVERED
# IT IS HARD CODED AGAIN BELOW
CREATE TABLE rcpd_working_4 AS
SELECT record_year, 720e6/(1000*SUM(mean_mw_in_rcpd_periods)) AS rcpd_rate_dollars_per_kw
FROM rcpd_working_3
GROUP BY record_year;

CREATE INDEX idx ON rcpd_working_4 (record_year);

# HARD-CODED - Cobb subtracts 32 MW from STK
CREATE TABLE rcpd_working_5 AS
SELECT a.record_date, a.tp, a.rcpd_aggregation, b.rcpd_region, 
 SUM((CASE WHEN rcpd_aggregation='STK0331' THEN net_load_mw-32 ELSE net_load_mw END)*1000*rcpd_rate_dollars_per_kw/(1e6*(CASE WHEN b.rcpd_region='UNI' OR b.rcpd_region='USI' THEN 12 ELSE 100 END))) AS charge_m
FROM rcpd_working_0 AS a
INNER JOIN prefix_to_rcpd_region AS b
ON a.prefix=b.prefix
INNER JOIN rcpd_working_2 AS c
ON a.record_date=c.record_date AND a.tp=c.tp AND b.rcpd_region=c.rcpd_region
INNER JOIN rcpd_working_4 AS d
ON YEAR(a.record_date)=(d.record_year-6)
GROUP BY a.record_date, a.tp, a.rcpd_aggregation, b.rcpd_region;

CREATE INDEX idx ON rcpd_working_5 (record_date, tp, rcpd_aggregation);

CREATE TABLE rcpd_charges_by_customer AS
SELECT customer, island, SUM(charge_m) AS charge_m FROM
(SELECT b.purchaser AS customer, (CASE WHEN a.rcpd_region='UNI' OR a.rcpd_region='LNI' THEN 'N' ELSE 'S' END) AS island, SUM(a.charge_m * b.proportion) AS charge_m
 FROM rcpd_working_5 AS a
 INNER JOIN reconciliation_allocations_by_network_3yr_long AS b
 ON a.record_date=b.trading_date AND a.tp=b.trading_period AND a.rcpd_aggregation=b.poc
 GROUP BY b.purchaser, (CASE WHEN a.rcpd_region='UNI' OR a.rcpd_region='LNI' THEN 'N' ELSE 'S' END)
UNION ALL
 SELECT d.customer, (CASE WHEN c.rcpd_region='UNI' OR c.rcpd_region='LNI' THEN 'N' ELSE 'S' END) AS island, SUM(c.charge_m) AS charge_m
 FROM rcpd_working_5 AS c
 INNER JOIN rcpd_payers_for_aggregations AS d
 ON c.rcpd_aggregation=d.rcpd_aggregation
 GROUP BY d.customer, (CASE WHEN c.rcpd_region='UNI' OR c.rcpd_region='LNI' THEN 'N' ELSE 'S' END)
) AS e GROUP BY customer, island;

# HARD CODED - $720M P.A. TO BE RECOVERED
# HARD CODED - $45M P.A. OF POST-FTR INTERCONNECTION LCE TO BE REBATED
CREATE TABLE rcpd_rebates_by_customer AS
SELECT customer, island, charge_m*45/720 AS rebate_m
FROM rcpd_charges_by_customer;

# As you do not have the 'reconciliation_allocations_by_network_3yr_long' table and hence cannot run the code above, 
#  use this commented-out code instead to have the same effect:
#
# DROP TABLE IF EXISTS rcpd_charges_by_customer;
# CREATE TABLE rcpd_charges_by_customer (
#  `customer` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
#  `island` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
#  `charge_m` double DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# insert into rcpd_charges_by_customer(`customer`,`island`,`charge_m`) values ('Alpine Energy','S',34.21350439338504),('Aurora Energy','S',66.47954580173729),('Buller Electricity','S',5.266399604920122),('CHH','N',14.112147406976232),('Contact','N',1.082086857216627),('Contact','S',0.005034647581814473),('Counties Power','N',22.85068872683899),('Daiken MDF','S',2.8570952847830133),('Eastland Network','N',17.943495317861075),('Electra','N',23.369580203443537),('Electricity Ashburton','S',12.913791363435434),('Fonterra','N',0.5705661932653171),('Genesis','N',0.07398845717104471),('Horizon','N',8.904149488378026),('Kiwirail','N',1.5191833793145622),('Mainpower','S',30.34037091265414),('Marlborough Lines','S',21.15278662770661),('Meridian','N',0.01780515322424593),('Meridian','S',0.31283530352064315),('Methanex','N',1.7707058877115487),('Network Tasman','S',36.7416354465512),('Network Waitaki','S',11.85311017470893),('Northpower','N',52.94149220057758),('NZ Steel','N',28.334033853407682),('Orion','S',217.36051178050693),('Other','N',4.754415303633039),('Other','S',0.015099118610291189),('Pacific Aluminium','S',202.3069987114566),('Pacific Steel','N',11.579718645846947),('PanPac','N',7.0240649291297705),('Powerco','N',237.8046749936429),('PowerNet','S',69.96127338625513),('Rayonier','S',2.3330982162313507),('Scanpower','N',5.191087658929686),('The Lines Company','N',12.884405631755286),('Top Energy','N',15.233498366118322),('Unison','N',108.11407895827483),('Vector','N',570.9717877283106),('Waipa Power','N',21.354095516287572),('WEL','N',68.95623109038996),('Wellington Electricity','N',188.91053397402763),('Westpower','S',7.6337786528014755),('Winstones','N',11.608399775637787);
#
# DROP TABLE IF EXISTS rcpd_rebates_by_customer;
# CREATE TABLE rcpd_rebates_by_customer (
#  `customer` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
#  `island` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
#  `rebate_m` double DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# insert into rcpd_rebates_by_customer(`customer`,`island`,`rebate_m`) values ('Alpine Energy','S',2.138344024586565),('Aurora Energy','S',4.1549716126085805),('Buller Electricity','S',0.3291499753075076),('CHH','N',0.8820092129360145),('Contact','N',0.06763042857603918),('Contact','S',0.00031466547386340457),('Counties Power','N',1.4281680454274368),('Daiken MDF','S',0.17856845529893836),('Eastland Network','N',1.1214684573663172),('Electra','N',1.460598762715221),('Electricity Ashburton','S',0.8071119602147147),('Fonterra','N',0.03566038707908232),('Genesis','N',0.004624278573190295),('Horizon','N',0.5565093430236266),('Kiwirail','N',0.09494896120716013),('Mainpower','S',1.8962731820408838),('Marlborough Lines','S',1.3220491642316632),('Meridian','N',0.0011128220765153707),('Meridian','S',0.019552206470040197),('Methanex','N',0.11066911798197179),('Network Tasman','S',2.29635221540945),('Network Waitaki','S',0.7408193859193081),('Northpower','N',3.308843262536098),('NZ Steel','N',1.7708771158379801),('Orion','S',13.585031986281683),('Other','N',0.29715095647706496),('Other','S',0.0009436949131431993),('Pacific Aluminium','S',12.64418741946604),('Pacific Steel','N',0.7237324153654342),('PanPac','N',0.4390040580706106),('Powerco','N',14.86279218710268),('PowerNet','S',4.372579586640946),('Rayonier','S',0.14581863851445942),('Scanpower','N',0.3244429786831054),('The Lines Company','N',0.8052753519847055),('Top Energy','N',0.952093647882395),('Unison','N',6.757129934892177),('Vector','N',35.68573673301941),('Waipa Power','N',1.3346309697679732),('WEL','N',4.309764443149373),('Wellington Electricity','N',11.806908373376729),('Westpower','S',0.4771111658000922),('Winstones','N',0.7255249859773616);
