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

# Run 'load_HVDC_lookups.sql' first.

# Hard-coded assumptions include that:
# - there is $135M p.a. to be recovered
# - the HVDC LCE rebate is $5M per year
# - Cobb is now embedded and does not pay HVDC charges

# Note that, according to the definitions we are using, some of the payers are TP customers (e.g. Alpine Energy), 
#  some are participants (e.g. Pioneer), and some are both (e.g. Meridian)

# *** The calculation approach here is approximate and includes several simplifying assumptions. 
# *** Do not rely on it to form conclusions about the HVDC charges you will pay.
# *** Contact Transpower if you have any questions about the HVDC charges you will pay.
            
# In particular, there appears to be some problem in our calculation of Trustpower's HAMI on the West Coast. 
#  We discard the values produced by our calculation and replace them with a flat 2 MW injection for DOB, GYM and HKK combined.
# There may also be a problem in our calculation of HAMI at e.g. Waipori, but if so, we have not fixed it.
# We also allow for Clyde's peak injection to be capped at 395 for the purpose of calculating the HVDC charge, and Benmore's at 465
#  (e.g. through the exceptional operating circumstances mechanism).

# There is one step of working below that involves spreadsheeting.

USE tpm2;

CREATE TABLE hvdc_working_1 AS
SELECT a.record_date, a.tp, b.generator, SUM(a.generation-a.net_demand) AS net_injection
FROM o_raw_node_ir AS a
INNER JOIN hvdc_node_to_generator AS b
ON a.node=b.node
WHERE runtype='baseTPM2'
GROUP BY a.record_date, a.tp, b.generator;

CREATE INDEX idx ON hvdc_working_1 (record_date, tp, generator);

UPDATE hvdc_working_1 SET net_injection=0 WHERE net_injection<0; 

# Hard-coded bug-fixes, see comments at top of this file
UPDATE hvdc_working_1 SET net_injection=2 WHERE generator='GYM';
UPDATE hvdc_working_1 SET net_injection=0 WHERE generator='DOB';
UPDATE hvdc_working_1 SET net_injection=0 WHERE generator='HKK';
UPDATE hvdc_working_1 SET net_injection=465 WHERE generator='BEN' AND net_injection>465;
UPDATE hvdc_working_1 SET net_injection=395 WHERE generator='CYD' AND net_injection>395;

# THE NEXT STEP INVOLVES SPREADSHEETING
# Paste the following data into a spreadsheet and find the average of the 36 greatest entries in each column.
# Put the results into 'HAMI.csv'
SELECT record_date, tp,
SUM(CASE WHEN generator='ABY' THEN net_injection ELSE 0 END) AS ABY,
SUM(CASE WHEN generator='AVI' THEN net_injection ELSE 0 END) AS AVI,
SUM(CASE WHEN generator='BEN' THEN net_injection ELSE 0 END) AS BEN,
SUM(CASE WHEN generator='BRR' THEN net_injection ELSE 0 END) AS BRR,
SUM(CASE WHEN generator='COB' THEN net_injection ELSE 0 END) AS COB,
SUM(CASE WHEN generator='COL' THEN net_injection ELSE 0 END) AS COL,
SUM(CASE WHEN generator='CYD' THEN net_injection ELSE 0 END) AS CYD,
SUM(CASE WHEN generator='DOB' THEN net_injection ELSE 0 END) AS DOB,
SUM(CASE WHEN generator='GYM' THEN net_injection ELSE 0 END) AS GYM,
SUM(CASE WHEN generator='HBK' THEN net_injection ELSE 0 END) AS HBK,
SUM(CASE WHEN generator='HKK' THEN net_injection ELSE 0 END) AS HKK,
SUM(CASE WHEN generator='MAN' THEN net_injection ELSE 0 END) AS MAN,
SUM(CASE WHEN generator='NMA' THEN net_injection ELSE 0 END) AS NMA,
SUM(CASE WHEN generator='OHA' THEN net_injection ELSE 0 END) AS OHA,
SUM(CASE WHEN generator='OHB' THEN net_injection ELSE 0 END) AS OHB,
SUM(CASE WHEN generator='OHC' THEN net_injection ELSE 0 END) AS OHC,
SUM(CASE WHEN generator='PAE' THEN net_injection ELSE 0 END) AS PAE,
SUM(CASE WHEN generator='RMG' THEN net_injection ELSE 0 END) AS RMG,
SUM(CASE WHEN generator='ROX' THEN net_injection ELSE 0 END) AS ROX,
SUM(CASE WHEN generator='TKA' THEN net_injection ELSE 0 END) AS TKA,
SUM(CASE WHEN generator='TKB' THEN net_injection ELSE 0 END) AS TKB,
SUM(CASE WHEN generator='WPI' THEN net_injection ELSE 0 END) AS WPI,
SUM(CASE WHEN generator='WTK' THEN net_injection ELSE 0 END) AS WTK
FROM hvdc_working_1
GROUP BY record_date, tp;

# Proceed once 'HAMI.csv' has been created

CREATE TABLE hvdc_hami_by_generator (generator VARCHAR(30), hami DOUBLE);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\Status_quo_HVDC\\HAMI.csv'
INTO TABLE hvdc_hami_by_generator
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON hvdc_hami_by_generator (generator);

# checks
SELECT generator FROM hvdc_hami_by_generator WHERE NOT (generator IN (SELECT generator FROM hvdc_node_to_generator)); # should be none

# HARD CODED - $135M P.A. TO BE RECOVERED
# HARD CODED - COBB DOES NOT PAY
CREATE TABLE hvdc_charge AS
SELECT c.party, SUM(135*3*a.hami/b.sum_of_hami) AS charge_m
FROM hvdc_hami_by_generator AS a
INNER JOIN (SELECT SUM(hami) AS sum_of_hami FROM hvdc_hami_by_generator WHERE generator<>'COB') AS b
INNER JOIN hvdc_generator_to_party AS c
ON a.generator=c.generator
WHERE a.generator<>'COB'
GROUP BY c.party;
 
# HARD CODED - $135M P.A. TO BE RECOVERED AND $5M P.A. OF POST-FTR HVDC LCE TO BE REBATED
CREATE TABLE hvdc_lce_rebate AS
SELECT party, (5/135)*charge_m AS rebate_m
FROM hvdc_charge;

