# This script calculates LRMC charges. 

# Run 'load_LRMC_inputs.sql' first.

# The script is complex - in large part because of the adaptations to the method used to deal with the limited time frame of the modelled scenario.
# If we had a 2016 year in the scenario, we would calculate charges by comparing 2016 peak with 2019 peak
# But we don't. And 2017 is not of much use for load, because (based on 2011) it is very atypical.
# So for load, we generate an artificial 2016 peak, which is 97% of the 2018 peak. And we compare that with the 2019 peak.
# While for generation, we generate an artificial 2016 peak, which is equal to the 2017 peak. And we compare that with the 2019 peak.

# 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.
# 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;

# First identify the timing of maximum congestion, into (or out of, as the case may be) each region of interest

CREATE TABLE LRMC_working_1 AS
SELECT LRMC_region, generation_enabling, record_date, tp, 
 SUM(CASE WHEN load_or_generation='load' AND net_demand>0 THEN net_demand ELSE 0 END) AS demand, 
 SUM(CASE WHEN load_or_generation='load' THEN net_demand WHEN load_or_generation='generation' THEN -generation ELSE 0 END) AS net_demand,
 SUM(CASE WHEN load_or_generation='load' AND net_demand<0 THEN -net_demand WHEN load_or_generation='generation' THEN generation ELSE 0 END) AS generation,
 (CASE WHEN tp<=14 OR tp>=43 THEN "W " WHEN MONTH(record_date)<=2 OR MONTH(record_date)=12 OR (MONTH(record_date)=3 AND DAY(record_date)<=14) THEN "SU" WHEN (MONTH(record_date)>=6 AND MONTH(record_date)<=9) OR (MONTH(record_date)=5 AND DAY(record_date)>=10) OR (MONTH(record_date)=10 AND DAY(record_date)<20) THEN "W " ELSE "SH" END) AS season
FROM o_raw_node_ir AS a
INNER JOIN LRMC_region_definitions AS b
ON a.node=b.node
WHERE runtype='baseTPM2'
GROUP BY LRMC_region, generation_enabling, record_date, tp;

CREATE TABLE LRMC_working_2 AS
SELECT LRMC_region, generation_enabling, YEAR(record_date) AS record_year, 
 MAX(net_demand/(CASE WHEN season="W " THEN 1 WHEN season="SU" THEN 0.9 WHEN season="SH" THEN 0.95 END)) AS max_congestion_in, 
 MIN(net_demand/(CASE WHEN season="W " THEN 1 WHEN season="SU" THEN 0.9 WHEN season="SH" THEN 0.95 END)) AS max_congestion_out
FROM LRMC_working_1
GROUP BY LRMC_region, generation_enabling, YEAR(record_date);

CREATE TABLE LRMC_working_3 AS
SELECT a.LRMC_region, record_year, record_date, tp 
FROM LRMC_working_1 AS a
INNER JOIN LRMC_working_2 AS b
ON a.LRMC_region=b.LRMC_region AND YEAR(a.record_date)=b.record_year 
 AND ((a.generation_enabling='F' AND net_demand/(CASE WHEN season="W " THEN 1 WHEN season="SU" THEN 0.9 WHEN season="SH" THEN 0.95 END)=b.max_congestion_in) OR 
      (a.generation_enabling='T' AND net_demand/(CASE WHEN season="W " THEN 1 WHEN season="SU" THEN 0.9 WHEN season="SH" THEN 0.95 END)=b.max_congestion_out));

# The following block of code identifies each customer's contribution to peak congestion.
# The code has eight branches - for the start and end of the study period, for demand and generation, for export investments and import investments.

# Assume that in import-constrained areas, 2016 peak (demand - generation) is 97% of 2018 figure
# Assume that in export-constrained areas, 2016 peak (generation - demand) is same as 2017 figure
# Note further that in the o_raw_node_ir table (containing vspd output):
# - 2011 means 2017
# - 2012 means 2018
# - 2013 means 2019.

CREATE TABLE LRMC_working_4 AS
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, a.net_demand*0.97 AS q, 2016 AS record_year
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='load' AND runtype='baseTPM2' AND record_year=2012 AND generation_enabling='F'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, -generation AS q, 2016 AS record_year
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='generation' AND generation>0 AND runtype='baseTPM2' AND record_year=2012 AND generation_enabling='F'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, a.net_demand AS q, 2019 AS record_year
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='load' AND runtype='baseTPM2' AND record_year=2013 AND generation_enabling='F'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, -a.generation AS q, 2019 AS record_year 
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='generation' AND generation>0 AND runtype='baseTPM2' AND record_year=2013 AND generation_enabling='F'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, -a.net_demand AS q, 2016 AS record_year
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='load' AND runtype='baseTPM2' AND record_year=2011 AND generation_enabling='T'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, generation AS q, 2016 AS record_year
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='generation' AND generation>0 AND runtype='baseTPM2' AND record_year=2011 AND generation_enabling='T'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, -a.net_demand AS q, 2019 AS record_year
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='load' AND runtype='baseTPM2' AND record_year=2013 AND generation_enabling='T'
UNION ALL
  SELECT b.LRMC_region, a.record_date, a.tp, a.node, a.generation AS q, 2019 AS record_year 
  FROM o_raw_node_ir AS a
  INNER JOIN LRMC_working_3 AS b
  ON a.record_date=b.record_date AND a.tp=b.tp
  INNER JOIN LRMC_region_definitions AS c
  ON b.LRMC_region=c.LRMC_region AND a.node=c.node
  WHERE load_or_generation='generation' AND generation>0 AND runtype='baseTPM2' AND record_year=2013 AND generation_enabling='T'
;

# Calculate increases in contribution to peak congestion, by customer
# The code has six branches, depending on whether the investment supports import or export, 
#  and whether the customer contributes to congestion via load offtake, load injection or generation.

CREATE TABLE LRMC_working_5_by_customer AS 
SELECT a.LRMC_region, a.record_date, a.tp, a.node, a.record_year, b.purchaser AS customer, 'load_offtake  ' AS category, a.q*b.proportion AS q
FROM LRMC_working_4 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.node=b.poc
INNER JOIN LRMC_region_definitions AS c
ON a.LRMC_region=c.LRMC_region AND a.node=c.node
WHERE q>0 AND generation_enabling='F'
  UNION ALL
SELECT a.LRMC_region, a.record_date, a.tp, a.node, a.record_year, b.generator AS customer, 'load_injection' AS category, a.q AS q
FROM LRMC_working_4 AS a
INNER JOIN injecting_load_node_to_participant AS b
ON a.node=b.injecting_load_node
INNER JOIN LRMC_region_definitions AS c
ON a.LRMC_region=c.LRMC_region AND a.node=c.node
WHERE q<0 AND generation_enabling='F'
  UNION ALL
SELECT a.LRMC_region, a.record_date, a.tp, a.node, a.record_year, b.generator AS customer, 'generation    ' AS category, a.q AS q
FROM LRMC_working_4 AS a
INNER JOIN offer_node_to_participant AS b
ON a.node=b.offer_code
INNER JOIN LRMC_region_definitions AS c
ON a.LRMC_region=c.LRMC_region AND a.node=c.node
WHERE q<0 AND generation_enabling='F'
  UNION ALL
SELECT a.LRMC_region, a.record_date, a.tp, a.node, a.record_year, b.purchaser AS customer, 'load_offtake  ' AS category, a.q*b.proportion AS q
FROM LRMC_working_4 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.node=b.poc
INNER JOIN LRMC_region_definitions AS c
ON a.LRMC_region=c.LRMC_region AND a.node=c.node
WHERE q<0 AND generation_enabling='T'
  UNION ALL
SELECT a.LRMC_region, a.record_date, a.tp, a.node, a.record_year, b.generator AS customer, 'load_injection' AS category, a.q AS q
FROM LRMC_working_4 AS a
INNER JOIN injecting_load_node_to_participant AS b
ON a.node=b.injecting_load_node
INNER JOIN LRMC_region_definitions AS c
ON a.LRMC_region=c.LRMC_region AND a.node=c.node
WHERE q>0 AND generation_enabling='T'
  UNION ALL
SELECT a.LRMC_region, a.record_date, a.tp, a.node, a.record_year, b.generator AS customer, 'generation    ' AS category, a.q AS q
FROM LRMC_working_4 AS a
INNER JOIN offer_node_to_participant AS b
ON a.node=b.offer_code
INNER JOIN LRMC_region_definitions AS c
ON a.LRMC_region=c.LRMC_region AND a.node=c.node
WHERE q>0 AND generation_enabling='T'
;

# Accordingly, calculate charges.

CREATE TABLE LRMC_charge_on_congestion_allocation_by_customer AS
SELECT a.investment, b.customer, 
(CASE WHEN c.island='N' AND b.category<>"load_offtake" THEN 'NI gen' WHEN c.island='S' AND b.category<>"load_offtake" THEN 'SI gen' ELSE b.category END) AS category,
rate_dollars_per_kw*1000*SUM(CASE WHEN record_year=2019 THEN q ELSE -q END)/1e6 AS LRMC_charge_m
FROM LRMC_rates AS a
INNER JOIN LRMC_working_5_by_customer AS b
ON a.LRMC_region=b.LRMC_region
LEFT JOIN prefix_to_region AS c
ON SUBSTRING(b.node,1,3)=c.prefix
GROUP BY a.investment, b.customer, 
(CASE WHEN c.island='N' AND b.category<>"load_offtake" THEN 'NI gen' WHEN c.island='S' AND b.category<>"load_offtake" THEN 'SI gen' ELSE b.category END);

# 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 LRMC_charge_on_congestion_allocation_by_customer;
# CREATE TABLE LRMC_charge_on_congestion_allocation_by_customer (
#  `investment` varchar(60) DEFAULT NULL,
#  `customer` varchar(22) CHARACTER SET utf8 DEFAULT NULL,
#  `category` varchar(14) CHARACTER SET utf8 DEFAULT NULL,
#  `LRMC_charge_m` double DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# insert into lrmc_charge_on_congestion_allocation_by_customer(`investment`,`customer`,`category`,`LRMC_charge_m`) values ('Lower North Island transmission reinforcement - for DS','CHH','load_offtake  ',-0.017696145648699636),('Lower North Island transmission reinforcement - for DS','CHH','NI gen',0.015318665090000001),('Lower North Island transmission reinforcement - for DS','Contact','load_offtake  ',0.002928985630121127),('Lower North Island transmission reinforcement - for DS','Contact','NI gen',1.10294388648),('Lower North Island transmission reinforcement - for DS','Counties Power','load_offtake  ',0.11850442607230574),('Lower North Island transmission reinforcement - for DS','Fonterra','load_offtake  ',0.002863366399129333),('Lower North Island transmission reinforcement - for DS','Genesis','NI gen',-5.59131275785),('Lower North Island transmission reinforcement - for DS','Horizon','load_offtake  ',0.19110495134917416),('Lower North Island transmission reinforcement - for DS','Kiwirail','load_offtake  ',-0.00854321955429374),('Lower North Island transmission reinforcement - for DS','Mokai JV','NI gen',0.1531866509),('Lower North Island transmission reinforcement - for DS','MRP','load_offtake  ',-0.006273145934585585),('Lower North Island transmission reinforcement - for DS','MRP','NI gen',3.69179828669),('Lower North Island transmission reinforcement - for DS','NAP JV','NI gen',-1.9914264617000002),('Lower North Island transmission reinforcement - for DS','Ngatamariki','NI gen',0),('Lower North Island transmission reinforcement - for DS','Norske Skog','load_offtake  ',0.8011392283784375),('Lower North Island transmission reinforcement - for DS','Northpower','load_offtake  ',-0.05781820846514585),('Lower North Island transmission reinforcement - for DS','NZ Steel','load_offtake  ',0.42167741164965433),('Lower North Island transmission reinforcement - for DS','NZ Steel','NI gen',0.10723065563),('Lower North Island transmission reinforcement - for DS','Other','load_offtake  ',-0.03308584018312452),('Lower North Island transmission reinforcement - for DS','Pacific Steel','load_offtake  ',0.09705049592519942),('Lower North Island transmission reinforcement - for DS','Powerco','load_offtake  ',-0.08788903717848921),('Lower North Island transmission reinforcement - for DS','The Lines Company','load_offtake  ',0.16340695676913577),('Lower North Island transmission reinforcement - for DS','Todd','NI gen',-0.482537950335),('Lower North Island transmission reinforcement - for DS','Top Energy','load_offtake  ',0.027646048831000667),('Lower North Island transmission reinforcement - for DS','TrustPower','load_offtake  ',0.00235907441107711),('Lower North Island transmission reinforcement - for DS','Trustpower','NI gen',0.56679060833),('Lower North Island transmission reinforcement - for DS','Unison','load_offtake  ',0.007460653121555539),('Lower North Island transmission reinforcement - for DS','Vector','load_offtake  ',2.3267468294573934),('Lower North Island transmission reinforcement - for DS','Waipa Power','load_offtake  ',-0.11782980078677345),('Lower North Island transmission reinforcement - for DS','WEL','load_offtake  ',0.3774405563188549),('Lower North Island transmission reinforcement - for SS','Alpine Energy','load_offtake  ',0.06700245914460132),('Lower North Island transmission reinforcement - for SS','Aurora Energy','load_offtake  ',-0.008451943187421077),('Lower North Island transmission reinforcement - for SS','Buller Electricity','load_offtake  ',-0.26090751702305437),('Lower North Island transmission reinforcement - for SS','Contact','load_offtake  ',-0.001444625632585645),('Lower North Island transmission reinforcement - for SS','Contact','SI gen',-2.2467375468999995),('Lower North Island transmission reinforcement - for SS','Electra','load_offtake  ',-0.031617731607418854),('Lower North Island transmission reinforcement - for SS','Genesis','SI gen',1.0212443395),('Lower North Island transmission reinforcement - for SS','Kiwirail','load_offtake  ',-0.03112752602653694),('Lower North Island transmission reinforcement - for SS','Marlborough Lines','load_offtake  ',-0.007965693381738432),('Lower North Island transmission reinforcement - for SS','Meridian','load_offtake  ',-0.009413655545297375),('Lower North Island transmission reinforcement - for SS','Meridian','NI gen',0.11564591719321814),('Lower North Island transmission reinforcement - for SS','Meridian','SI gen',14.09884096302221),('Lower North Island transmission reinforcement - for SS','Network Tasman','load_offtake  ',-0.3638489894925591),('Lower North Island transmission reinforcement - for SS','Network Waitaki','load_offtake  ',0.12578587230903018),('Lower North Island transmission reinforcement - for SS','Orion','load_offtake  ',-0.0017156909237608512),('Lower North Island transmission reinforcement - for SS','Other','load_offtake  ',-0.0022268641168093815),('Lower North Island transmission reinforcement - for SS','Pacific Aluminium','load_offtake  ',0.9830897932906041),('Lower North Island transmission reinforcement - for SS','Pioneer','SI gen',-0.15167521171789752),('Lower North Island transmission reinforcement - for SS','Powerco','load_offtake  ',0.21188780138820285),('Lower North Island transmission reinforcement - for SS','PowerNet','load_offtake  ',-0.8479487396657359),('Lower North Island transmission reinforcement - for SS','Rayonier','load_offtake  ',-0.039439904155643024),('Lower North Island transmission reinforcement - for SS','Scanpower','load_offtake  ',-0.0010212433655655024),('Lower North Island transmission reinforcement - for SS','Todd','NI gen',0.20424886789999996),('Lower North Island transmission reinforcement - for SS','TrustPower','load_offtake  ',0.005269620514248668),('Lower North Island transmission reinforcement - for SS','Trustpower','SI gen',-0.9876658245264333),('Lower North Island transmission reinforcement - for SS','Wellington Electricity','load_offtake  ',-0.91773093860594),('Lower North Island transmission reinforcement - for SS','Westpower','load_offtake  ',0.14264740487465288),('Upper South Island grid upgrade - stage 2','Buller Electricity','load_offtake  ',0.4999793952710953),('Upper South Island grid upgrade - stage 2','Daiken MDF','load_offtake  ',-0.5385776132901082),('Upper South Island grid upgrade - stage 2','Electricity Ashburton','load_offtake  ',0.8727900870500462),('Upper South Island grid upgrade - stage 2','Mainpower','load_offtake  ',-0.059248944254867694),('Upper South Island grid upgrade - stage 2','Marlborough Lines','load_offtake  ',1.0174773759865001),('Upper South Island grid upgrade - stage 2','Network Tasman','load_offtake  ',0.7343663365312693),('Upper South Island grid upgrade - stage 2','Orion','load_offtake  ',3.8008952689162947),('Upper South Island grid upgrade - stage 2','TrustPower','load_offtake  ',0.009024284680067348),('Upper South Island grid upgrade - stage 2','Trustpower','SI gen',0.64459177972),('Upper South Island grid upgrade - stage 2','Westpower','load_offtake  ',0.20941034282840545),('Upper South Island voltage stability','Buller Electricity','load_offtake  ',0.28742184726506437),('Upper South Island voltage stability','Daiken MDF','load_offtake  ',-0.30961070390414464),('Upper South Island voltage stability','Electricity Ashburton','load_offtake  ',0.5017385545629168),('Upper South Island voltage stability','Mainpower','load_offtake  ',-0.03406028561838095),('Upper South Island voltage stability','Marlborough Lines','load_offtake  ',0.5849145579246975),('Upper South Island voltage stability','Network Tasman','load_offtake  ',0.42216325514904235),('Upper South Island voltage stability','Orion','load_offtake  ',2.1850107220130917),('Upper South Island voltage stability','TrustPower','load_offtake  ',0.005187766931044025),('Upper South Island voltage stability','Trustpower','SI gen',0.37055479047999995),('Upper South Island voltage stability','Westpower','load_offtake  ',0.12038317606971663);


