# This script calculates RCPD charges in the simulated scenario.
# It uses the new version of the RCPD charge (ie post transition), with:
# - N=100 in all regions
# - summer trading periods excluded, except in the USI
#
# You must first run 'old_style_node_table.sql', 'load_RCPD_lookups.sql' and 'Lookups/load_lookup_tables.sql'.
#
# The table 'test.reconciliation_network_dw' used by this script is commercially sensitive and cannot be published.
#
# Hard-coded assumptions:
# - The amount to be recovered is $701M - $45M (for non-HVDC post-FTR LCE) = $656M
# - Cobb is now embedded generation 
# - 40% of charges at GLN are allocated to Counties. ('Our understanding is that RCPD charges for Glenbrook are allocated between NZ Steel and Counties based on actual revenue metering, and that in recent years, Counties have paid roughly 40% of RCPD charges for Glenbrook. ... The above breakdown of charges could be affected by various factors, including the move to N=100 in the Upper North Island.')
#
# One inaccuracy at least is present: the (small) charges on grid-connected generators that sometimes offtake may not be correct in some cases.

USE tpm3;

CREATE TABLE rcpd_working_0 AS
SELECT record_date, tp, rcpd_aggregation, SUBSTRING(rcpd_aggregation,1,3) AS prefix, rcpd_region, SUM(net_demand-generation) AS net_load_mw
FROM o_raw_node_ir_old_style AS a
INNER JOIN rcpd_mapping AS b
ON a.node=b.node
INNER JOIN prefix_to_rcpd_region AS c
ON SUBSTRING(rcpd_aggregation,1,3)=c.prefix
WHERE a.runtype='base3k' AND (rcpd_region='USI' OR (MONTH(record_date)>=4 AND MONTH(record_date)<10))
GROUP BY record_date, tp, rcpd_aggregation, rcpd_region
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, rcpd_region, SUM(net_load_mw) AS load_mw
FROM rcpd_working_0 AS a
GROUP BY record_date, tp, rcpd_region;

CREATE TABLE rcpd_working_2 AS
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='UNI' AND YEAR(record_date)=2014 ORDER BY load_mw DESC LIMIT 100)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LNI' AND YEAR(record_date)=2014 ORDER BY load_mw DESC LIMIT 100)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='USI' AND YEAR(record_date)=2014 ORDER BY load_mw DESC LIMIT 100)
 UNION ALL
(SELECT * FROM rcpd_working_1 WHERE rcpd_region='LSI' AND YEAR(record_date)=2014 ORDER BY load_mw DESC LIMIT 100)
;
 
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 2014 AS 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 rcpd_region;

# HARD CODED - $656 P.A. TO BE RECOVERED
CREATE TABLE rcpd_working_4 AS
SELECT record_year, 656e6/(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 assumption - 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*100)) 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)
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);

# HARD CODED - 40% of charges at GLN are allocated to Counties
CREATE TABLE rcpd_charges_by_customer_May_2016 AS
SELECT customer, island, SUM(charge_m) AS charge_less_rebate_m FROM
(SELECT b.network 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.nshare) AS charge_m
 FROM rcpd_working_5 AS a
 INNER JOIN test.reconciliation_network_dw AS b
 ON a.record_date=b.record_date AND a.tp=b.tp AND a.rcpd_aggregation=b.poc
 GROUP BY b.network, (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
 WHERE c.rcpd_aggregation<>'GLN'
 GROUP BY d.customer, (CASE WHEN c.rcpd_region='UNI' OR c.rcpd_region='LNI' THEN 'N' ELSE 'S' END)
UNION ALL
 SELECT "NZ Steel", 'N' AS island, 0.6*SUM(charge_m) AS charge_m
 FROM rcpd_working_5 
 WHERE rcpd_aggregation='GLN'
UNION ALL
 SELECT "Counties Power", 'N' AS island, 0.4*SUM(charge_m) AS charge_m
 FROM rcpd_working_5 
 WHERE rcpd_aggregation='GLN'
) AS e GROUP BY customer, island;
