#This first section is calculating how benefits at each POC should be split between purchasers, and between networks,
# based on reconcilliation data during the period.

#Unfortunately, reconciliation data is confidential, so it is not possible to recreate the steps below
#However, the code is included as it is the best way to explain the method used to make the calculations.

#There are two distinct shares calculated. One is the share per network, and the other is 
# share per purchaser. The code common to both is done first, then the purchaser steps, then the network steps.

#First create mappings from old POCs that do not exist in the new network to the POCs where the demand has moved. 
CREATE TABLE test.poc_demand_mappings (new_poc VARCHAR(15), old_pocs VARCHAR(15)); 

INSERT INTO test.poc_demand_mappings 
VALUES ('ISL0661','ADD0111'),
('ISL0661','ADD0661'),
('ISL0661','BRY0111'),
('TUI1101','GIS0501'),
('MPE1101','KEN0331'),
('ISL0661','MLN0661'),
('ISL0661','MLN0664'),
('STK0331','MOT0111'),
('STK0331','MPI0661'),
('HWB0331','PAL0331'),
('ISL0661','SPN0331'),
('ISL0661','SPN0661'),
('TUI1101','TUI0111'),
('TUI1101','WRA0111'),
('WTK0111','WTK0331');
	
ALTER TABLE test.poc_demand_mappings ADD PRIMARY KEY (old_pocs); 
	
#Calculate total demand at each POC 
CREATE TABLE test.total_per_poc AS
SELECT trading_date, trading_period, IFNULL(c.new_poc,a.poc) AS poc, SUM(rm_demand) AS poc_demand
FROM static.rm_demand a
LEFT JOIN test.poc_demand_mappings c
ON a.poc = c.old_pocs
WHERE trading_Date BETWEEN '2014-01-01' AND '2014-12-31'
#This is because this is now split out
GROUP BY trading_date, trading_period, IFNULL(c.new_poc,a.poc);

ALTER TABLE test.total_per_poc ADD PRIMARY KEY (trading_date, trading_period, poc);

########################
### purchaser section ###
########################

#mapping tables for the purchaser results	

CREATE TABLE test.purchaser_mappings (purchaser_id VARCHAR(4), purchaser VARCHAR(31)); 

#This is the default mapping of purchaser code (as listed in reconciliation data) to purchaser name (as used in results)
INSERT INTO test.purchaser_mappings
VALUES ('CTCT','Contact'),
('GENE','Genesis'),
('GEOL','Genesis'),
('GENH','Genesis'),
('EDNZ','Trustpower'),
('TRUS','Trustpower'),
('PSNZ','Meridian'),
('MERI','Meridian'),
('NZAB','Pacific Aluminium'),
('NZAP','Pacific Aluminium'),
('NZSP','NZ_Steel'),
('EZYN','MRP'),
('GBUG','MRP'),
('MEEN','MRP'),
('MRPL','MRP'),
('SKOG','Norske Skog'),
('TODD','Todd'),
('BOPE','Todd'),
('AGCL','Todd'),
('PUNZ','Pulse'),
('KING','King Country energy'),
('SIMP','Simply Energy'),
('SELS','Simply Energy'),
('OPHL','Other'),
('HNET','Other'),
('CSNL','Other'),
('PRME','Other'),
('SHPK','Other'),
('NZWF','Other'),
('WNSL','Other'),
('PLEL','Other'),
('KPOW','Other'),
('TPNZ','Other'),
('FLCK','Other'),
('NAPJ','Other'),
('TUAR','Other'),
('ECOT','Error'),
('ELKI','Error'),
('EMHT','Error'),
('NZSC','Error'),
('NZSD','Error'),
('SELX','Error'),
('TASP','Error'),
('WNSP','Error');
 
ALTER TABLE test.purchaser_mappings ADD PRIMARY KEY (purchaser_id);

#At some POCs, the default purchaser is replaced by a new purchaser (typically large user)
CREATE TABLE test.purchaser_poc_exceptions (poc	VARCHAR(15), purchaser	VARCHAR(31));

INSERT INTO test.purchaser_poc_exceptions
VALUES ('LFD1101','Fonterra'),
('LFD1102','Fonterra'),
('MNG1101','Pacific Steel'),
('ASY0111','Daiken MDF'),
('TWI2201','Pacific Aluminium'),
('TNG0111','Winstones'),
('BPE0551','Kiwirail'),
('HAM0551','Kiwirail'),
('TMN0551','Kiwirail'),
('TNG0551','Kiwirail'),
('KIN0111','CHH'),
('KIN0112','CHH'),
('KIN0113','CHH');

ALTER TABLE test.purchaser_poc_exceptions ADD PRIMARY KEY (poc);

CREATE TABLE test.purchaser_network_exceptions (network_id	VARCHAR(15), purchaser	VARCHAR(31));

#Likewise, some networks are mapped to a specific purchaser that differs from the default
INSERT INTO test.purchaser_network_exceptions
VALUES ('NZST','NZ Steel'),
('METH','Methanex'),
('RAYN','Rayonier'),
('PANP','PanPac');

ALTER TABLE test.purchaser_network_exceptions ADD PRIMARY KEY (network_id);

#and a special one for MDN
CREATE TABLE test.purchaser_refinery_exceptions (trader VARCHAR(4), poc	VARCHAR(7), purchaser	VARCHAR(31));

#Likewise, some networks are mapped to a specific purchaser that differs from the default
INSERT INTO test.purchaser_refinery_exceptions
VALUES ('TODD','BRB0331','Refinery');

ALTER TABLE test.purchaser_refinery_exceptions ADD PRIMARY KEY (trader,poc);

#This is the total of demand split by POC and purchaser
#It inner joins to the default mapping, and then attempts to join to the 2 exception tables.
#If there is a mapping to the exception tables, then it uses the values there, otherwise it uses the defaults
CREATE TABLE test.total_per_purchaser_and_poc AS
SELECT trading_date, trading_period, 
	IFNULL(f.purchaser,IFNULL(d.purchaser,IFNULL(c.purchaser,b.purchaser))) AS purchaser, 
	IFNULL(e.new_poc,a.poc) AS poc, SUM(rm_demand) AS purchaser_poc_demand
FROM static.rm_demand a
INNER JOIN test.purchaser_mappings b
ON a.trader = b.purchaser_id
LEFT JOIN test.purchaser_poc_exceptions c
ON a.poc = c.poc
LEFT JOIN test.purchaser_network_exceptions d
ON a.network_participant = d.network_id
LEFT JOIN test.purchaser_refinery_exceptions f
ON a.trader = f.trader
AND a.poc = f.poc
LEFT JOIN test.poc_demand_mappings e
ON a.poc = e.old_pocs
WHERE trading_Date BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY trading_date, trading_period, IFNULL(f.purchaser,IFNULL(d.purchaser,IFNULL(c.purchaser,b.purchaser))), IFNULL(e.new_poc,a.poc);

ALTER TABLE test.total_per_purchaser_and_poc ADD PRIMARY KEY (trading_date, trading_period, poc, purchaser);

CREATE TABLE test.reconciliation_purchaser_dw AS
SELECT a.trading_date AS record_Date, a.trading_period AS tp, purchaser, a.poc, purchaser_poc_demand/poc_demand AS pshare
FROM test.total_per_purchaser_and_poc a
INNER JOIN test.total_per_poc b
ON a.trading_Date = b.trading_date
AND a.trading_period = b.trading_period
AND a.poc = b.poc;

SELECT DISTINCT poc FROM test.reconciliation_purchaser_dw;

ALTER TABLE test.reconciliation_purchaser_dw ADD PRIMARY KEY (record_Date, tp, poc, purchaser);

#######################
### Network Section ###
#######################

#mapping tables for the NETWORK results	

CREATE TABLE test.network_mappings (network_id VARCHAR(4), network_name VARCHAR(31)); 

INSERT INTO test.network_mappings 
VALUES ('ALPE','Alpine Energy'),
('BUEL','Buller Electricity'),
('CKHK','Wellington Electricity'),
('COUP','Counties Power'),
('CTCT','Contact'),
('DUNE','Aurora Energy'),
('EASH','Electricity Ashburton'),
('EAST','Eastland Network'),
('ELEC','Electra'),
('GENE','Genesis'),
#two here
('HAWK','Unison'),
#('CHBP','Unison'),
('CHBP','Centralines'),
('HEDL','Horizon'),
#lots here
('KUPE','Other'),
('SHPK','Other'),
('SOLE','Other'),
('KING','Other'),
('KRGL','Other'),
('PTNP','Other'),
('NAPJ','Other'),
('TUAR','Other'),
('LINE','The Lines Company'),
('MARL','Marlborough Lines'),
('MERI','Meridian'),
('METH','Methanex'),
('MPOW','Mainpower'),
('MRPL','MRP'),
('NPOW','Northpower'),
('NZAS','Pacific Aluminium'),
('NZRN','Kiwirail'),
('NZST','NZ Steel'),
('ORON','Orion'),
('PANP','PanPac'),
('POCO','Powerco'),
('RAYN','Rayonier'),
('SCAN','Scanpower'),
('SKOG','Norske Skog'),
('TASM','Network Tasman'),
('TODD','Todd'),
('TOPE','Top Energy'),
#4 here
#('TPCO','PowerNet'),
('TPCO','The Power Company'),
#('ELIN','PowerNet'),
('ELIN','Electricity Invercargill'),
#('OTPO','PowerNet'),
('OTPO','OtagoNet JV'),
#('LLNW','PowerNet'),
('LLNW','Lakeland Network'),
#3 here
('TRUS','TrustPower'),
('TRPG','TrustPower'),
('BOPD','TrustPower'),
('UNET','Vector'),
('VECT','Vector'),
('WAIK','WEL'),
('WAIP','Waipa Power'),
('WATA','Network Waitaki'),
('WNST','Winstones'),
('WPOW','Westpower'),
#this is different from the previous one. With the way the query works, there needs to be an entry for every record
#in rm_demand. However, this should always be overriden by one of the exception tables
('CHHE','If this shows, Error')
;

ALTER TABLE test.network_mappings ADD PRIMARY KEY (network_id);

CREATE TABLE test.network_poc_exceptions (poc	VARCHAR(15), network_name	VARCHAR(31));

INSERT INTO test.network_poc_exceptions
VALUES ('ASY0111','Daiken MDF'),
	('KIN0111','CHH'),
	('KIN0112','CHH'),
	('KIN0113','CHH'),
	('LFD1101','Fonterra'),
	('LFD1102','Fonterra'),
	('MNG1101','Pacific Steel');

ALTER TABLE test.network_poc_exceptions ADD PRIMARY KEY (poc);

#Likewise, some networks are mapped to a specific purchaser that differs from the default

#and a special one for MDN
CREATE TABLE test.network_refinery_exceptions (trader VARCHAR(4), poc	VARCHAR(7), network	VARCHAR(31));

INSERT INTO test.network_refinery_exceptions
VALUES ('TODD','BRB0331','Refinery');

ALTER TABLE test.network_refinery_exceptions ADD PRIMARY KEY (trader,poc);


CREATE TABLE test.total_per_network_and_poc AS
SELECT trading_date, trading_period, IFNULL(e.network,IFNULL(d.network_name,b.network_name)) AS network_name, IFNULL(c.new_poc,a.poc) AS poc, SUM(rm_demand) AS network_poc_demand
FROM static.rm_demand a
INNER JOIN test.network_mappings b
ON a.network_participant = b.network_id
LEFT JOIN test.poc_demand_mappings c
ON a.poc = c.old_pocs
LEFT JOIN test.network_poc_exceptions d
ON a.poc = d.poc
LEFT JOIN test.network_refinery_exceptions e
ON a.poc = e.poc
AND a.trader = e.trader
WHERE trading_Date BETWEEN '2014-01-01' AND '2014-12-31'
#This is because this is now split out
GROUP BY trading_date, trading_period, IFNULL(e.network,IFNULL(d.network_name,b.network_name)), IFNULL(c.new_poc,a.poc);

ALTER TABLE test.total_per_network_and_poc ADD PRIMARY KEY (trading_date, trading_period, poc, network_name);

CREATE TABLE test.reconciliation_network_dw AS
SELECT a.trading_date AS record_Date, a.trading_period AS tp, a.network_name AS network, a.poc, network_poc_demand/poc_demand AS nshare
FROM test.total_per_network_and_poc a
INNER JOIN test.total_per_poc b
ON a.trading_Date = b.trading_date
AND a.trading_period = b.trading_period
AND a.poc = b.poc;

ALTER TABLE test.reconciliation_network_dw ADD PRIMARY KEY (record_Date, tp, poc, network);

SELECT * FROM test.network_refinery_exceptions
WHERE poc = 'brb0331'
AND record_Date = '2014-06-01';
########################
### Load in run info ###
########################

USE tpm3;

CREATE TABLE spd_table_of_runs (investment VARCHAR(30),factual_runtype VARCHAR(20),counterfactual_runtype VARCHAR(20));
LOAD DATA LOCAL INFILE 'Y:\\Client Job Files\\Electricity Authority\\Transmission\\EAT004 - TPM options paper\\SPD charge\\table of runs.csv'
INTO TABLE spd_table_of_runs
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

#DW change
ALTER TABLE spd_table_of_runs ADD PRIMARY KEY (investment);

CREATE TABLE spd_node_pooling (node VARCHAR(20), pooled VARCHAR(20));
LOAD DATA LOCAL INFILE 'Y:\\Client Job Files\\Electricity Authority\\Transmission\\EAT004 - TPM options paper\\SPD charge\\node pooling.csv'
INTO TABLE spd_node_pooling
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

ALTER TABLE spd_node_pooling ADD PRIMARY KEY (node);
#CREATE INDEX idx ON spd_node_pooling (node);

CREATE TABLE spd_amounts_to_recover_pre_lce (investment VARCHAR(20), amount_pa FLOAT);
LOAD DATA LOCAL INFILE 'Y:\\Client Job Files\\Electricity Authority\\Transmission\\EAT004 - TPM options paper\\SPD charge\\amounts to recover under SPD charge - pre LCE.csv'
INTO TABLE spd_amounts_to_recover_pre_lce
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

ALTER TABLE spd_amounts_to_recover_pre_lce ADD PRIMARY KEY (investment);
#CREATE INDEX idx ON spd_node_pooling (node);

# some checks
SELECT DISTINCT runtype FROM tpm3.o_raw_node_ir;

SELECT * FROM spd_table_of_runs;
SELECT DISTINCT factual_runtype FROM spd_table_of_runs WHERE NOT (factual_runtype IN (SELECT DISTINCT runtype FROM o_raw_node_ir)); # should be none
SELECT DISTINCT counterfactual_runtype FROM spd_table_of_runs WHERE NOT (counterfactual_runtype IN (SELECT DISTINCT runtype FROM o_raw_node_ir)); # should be none
SELECT node FROM spd_node_pooling WHERE NOT (node IN (SELECT DISTINCT node FROM tpm3.o_raw_node_ir)); # should be none 

SELECT * 
FROM (SELECT DISTINCT node FROM tpm3.o_raw_node_ir WHERE runtype IN ('basetpm4','mono4')
	AND (generation > 0 OR demand <> 0)) a
LEFT JOIN spd_node_pooling b
ON a.node = b.node;

SELECT * 
FROM tpm3.o_raw_node_ir a
LEFT JOIN offer_node_to_participant b
ON a.node = b.offer_code
WHERE record_date = '2014-12-31'
AND runtype = 'basetpm4'
AND generation > 0
AND b.offer_code IS NULL
GROUP BY a.node; #should be none - limited to a single day for speed, and because grid does not change day to day.

SELECT SUM(difference_in_net_benefit)/1000000, SUM(difference_in_net_benefit_IR)/1000000 FROM spd_raw_allocations_working
;

################################################################
### Create the 'working' table that summarizes o_raw_node_ir ###
################################################################


USE tpm3;

#factual = base
# Allocations of benefits identified by SPD
CREATE TABLE spd_raw_allocations_working AS
SELECT a.investment, b.record_date, b.tp, d.pooled AS pooled_node,
 ROUND(SUM(b.net_benefit-IFNULL(c.net_benefit,0)),3) AS difference_in_net_benefit, 
 ROUND(SUM(b.net_benefit_IR-IFNULL(c.net_benefit_IR,0)),3) AS difference_in_net_benefit_IR,
 SUM(b.demand) AS net_demand, SUM(b.generation) AS generation
FROM spd_table_of_runs AS a
INNER JOIN o_raw_node_ir AS b
ON a.factual_runtype=b.runtype
INNER JOIN spd_node_pooling AS d
ON b.node=d.node
LEFT JOIN o_raw_node_ir AS c
ON a.counterfactual_runtype=c.runtype 
AND b.record_date=c.record_date 
AND b.tp=c.tp 
AND b.node=c.node
GROUP BY a.investment, b.record_date, b.tp, d.pooled;

ALTER TABLE spd_raw_allocations_working ADD PRIMARY KEY (investment, record_Date, tp, pooled_node);

#Change noNAaN case to account for load movements
#1
INSERT INTO spd_raw_allocations_working
SELECT a.investment, a.record_date, a.tp, 'PEN0331c', (a.difference_in_net_benefit+b.difference_in_net_benefit),
	0, a.net_demand+b.net_demand, 0
FROM tpm3.spd_raw_allocations_working a 
INNER JOIN tpm3.spd_raw_allocations_working b
ON a.record_Date = b.record_date
AND a.investment = b.investment
AND a.tp = b.tp
WHERE a.pooled_node = 'PEN0331'
AND b.pooled_node = 'HOB1101'
AND a.investment = 'noNAaN';

DELETE FROM spd_raw_allocations_working WHERE pooled_node IN ('PEN0331','HOB1101') AND investment = 'noNAaN';
UPDATE spd_raw_allocations_working SET pooled_node = 'PEN0331' WHERE pooled_node = 'PEN0331c' AND investment = 'noNAaN';

#2
INSERT INTO spd_raw_allocations_working
SELECT a.investment, a.record_date, a.tp, 'SVL0331c', (a.difference_in_net_benefit+b.difference_in_net_benefit),
	0, a.net_demand+b.net_demand, 0
FROM tpm3.spd_raw_allocations_working a 
INNER JOIN tpm3.spd_raw_allocations_working b
ON a.record_Date = b.record_date
AND a.investment = b.investment
AND a.tp = b.tp
WHERE a.pooled_node = 'SVL0331'
AND b.pooled_node = 'WRD0331'
AND a.investment = 'noNAaN';

DELETE FROM spd_raw_allocations_working WHERE pooled_node IN ('SVL0331','WRD0331') AND investment = 'noNAaN';
UPDATE spd_raw_allocations_working SET pooled_node = 'SVL0331' WHERE pooled_node = 'SVL0331c' AND investment = 'noNAaN';

#3
INSERT INTO spd_raw_allocations_working
SELECT a.investment, a.record_date, a.tp, 'ALB0331c', (a.difference_in_net_benefit+b.difference_in_net_benefit),
	0, a.net_demand+b.net_demand, 0
FROM tpm3.spd_raw_allocations_working a 
INNER JOIN tpm3.spd_raw_allocations_working b
ON a.record_Date = b.record_date
AND a.investment = b.investment
AND a.tp = b.tp
WHERE a.pooled_node = 'ALB0331'
AND b.pooled_node = 'ALB1101'
AND a.investment = 'noNAaN';

DELETE FROM spd_raw_allocations_working WHERE pooled_node IN ('ALB0331','ALB1101') AND investment = 'noNAaN';
UPDATE spd_raw_allocations_working SET pooled_node = 'ALB0331' WHERE pooled_node = 'ALB0331c' AND investment = 'noNAaN';

#NIGU
#4 PEN0331, HOB1101
INSERT INTO spd_raw_allocations_working
SELECT a.investment, a.record_date, a.tp, 'OTA2201c', (a.difference_in_net_benefit+b.difference_in_net_benefit),
	0, a.net_demand+b.net_demand, 0
FROM tpm3.spd_raw_allocations_working a 
INNER JOIN tpm3.spd_raw_allocations_working b
ON a.record_Date = b.record_date
AND a.investment = b.investment
AND a.tp = b.tp
WHERE a.pooled_node = 'OTA2201'
AND b.pooled_node = 'PAK0331'
AND a.investment IN ('noNIGU','noNIGU21');

DELETE FROM spd_raw_allocations_working WHERE pooled_node IN ('PAK0331','OTA2201') AND investment IN ('noNIGU','noNIGU21');
UPDATE spd_raw_allocations_working SET pooled_node = 'OTA2201' WHERE pooled_node = 'OTA2201c' AND investment IN ('noNIGU','noNIGU21');

# Hard-coded - IR (dis)benefits are only modelled for Pole 2 and Pole 3, and not for grid-connected generation nodes
UPDATE spd_raw_allocations_working SET difference_in_net_benefit_IR=0 WHERE investment NOT IN ('Pole 2','Pole 3','pole 3 48','pole 2 48') OR pooled_node IN (SELECT offer_code FROM offer_node_to_participant);
DELETE FROM spd_raw_allocations_working WHERE difference_in_net_benefit = 0 AND difference_in_net_benefit_ir = 0;

# checking
/*
SELECT investment, COUNT(*) FROM spd_raw_allocations_working GROUP BY investment;
*/
# Hard-coded assumptions that:
# - Load and generation at the KIN pooled node is paid for by CHH 
# - Load and generation at the KAW pooled node is paid for by Norske Skog (guess they might end up oncharging to Todd, who owns KAW0112 ONU0, and MRP, who owns KAW1101)
# - Load and generation at the GLN pooled node is paid for by NZ Steel (guess they might end up oncharging for retail load at GLN0332)
# - No other pooled nodes

USE tpm3;

#collate the previous tables into one...

#negative load
#CHeck for any unassigned nodes
/*
select pooled_node, min(net_demand) from tpm3.spd_working_2 a left join tpm3.injecting_load_node_to_participant b
on a.pooled_node = b.injecting_load_node
where a.investment= 'pole 3'
and injecting_load_node is null
and net_demand < 0
group by pooled_node;
*/

#Ignore small ones (<10MW peak.)

#Work out benefits to negative load participants
CREATE TABLE spd_working_3a AS
SELECT a.investment, YEAR(a.record_date) AS record_year, a.pooled_node, b.generator,
	SUM(difference_in_net_benefit+difference_in_net_benefit_IR) AS net_charge_uncapped
FROM spd_raw_allocations_working AS a
INNER JOIN injecting_load_node_to_participant AS b
ON a.pooled_node=b.injecting_load_node
WHERE a.net_demand<0 
GROUP BY a.investment, YEAR(a.record_date), a.pooled_node, b.generator;

#check for missing nodes in offers
/*
select * from spd_working_2 a
left join offer_node_to_participant b
ON a.pooled_node=b.offer_code
where generation > 0
and b.offer_code is null
group by pooled_node;
*/
#GLN KAW and KIN are dealt with later

#Work out benefits to offer participants (generators)
CREATE TABLE spd_working_3b AS
SELECT a.investment, YEAR(a.record_date) AS record_year, a.pooled_node, b.generator,
	SUM(difference_in_net_benefit+difference_in_net_benefit_IR) AS net_charge_uncapped
FROM spd_raw_allocations_working AS a
INNER JOIN offer_node_to_participant AS b
ON a.pooled_node=b.offer_code
WHERE a.generation>0
GROUP BY a.investment, YEAR(a.record_date), a.pooled_node, b.generator;

#Work out benefits to networks
#c is network
CREATE TABLE spd_working_3c AS
SELECT a.investment, YEAR(a.record_date) AS record_year, a.pooled_node, b.network,
	SUM(difference_in_net_benefit+difference_in_net_benefit_IR)*nshare AS net_charge_uncapped
FROM spd_raw_allocations_working AS a
INNER JOIN test.reconciliation_network_dw AS b
ON a.pooled_node=b.poc AND a.record_date=b.record_Date AND a.tp=b.tp
WHERE a.net_demand>0 
GROUP BY a.investment, YEAR(a.record_date), a.pooled_node, b.network;

SELECT * FROM spd_working_3c
WHERE pooled_node = 'asb0661';
#Work out benefits to purchasers
#d is purchaser
CREATE TABLE spd_working_3d AS
SELECT a.investment, YEAR(a.record_date) AS record_year, a.pooled_node, b.purchaser,
	SUM(difference_in_net_benefit+difference_in_net_benefit_IR)*pshare AS net_charge_uncapped
FROM spd_raw_allocations_working AS a
INNER JOIN test.reconciliation_purchaser_dw AS b
ON a.pooled_node=b.poc AND a.record_date=b.record_Date AND a.tp=b.tp
WHERE a.net_demand>0  
GROUP BY a.investment, YEAR(a.record_date), a.pooled_node, b.purchaser;

#Work out benefits to Direct connects
CREATE TABLE spd_working_3e AS
SELECT a.investment, YEAR(a.record_date) AS record_year, a.pooled_node, 
(CASE WHEN pooled_node='KIN' THEN 'CHH' 
	WHEN pooled_node='KAW' THEN 'Norske Skog' 
	WHEN pooled_node='GLN' THEN 'NZ Steel' 
	WHEN pooled_node = 'MDN' THEN 'Refinery' ELSE '' END) AS DC,
	SUM(difference_in_net_benefit) AS net_charge_uncapped 
FROM spd_raw_allocations_working AS a
WHERE (a.pooled_node IN ('KIN','KAW','GLN','brb0331'))
GROUP BY a.investment, YEAR(a.record_date), a.pooled_node;

#Group the '3' tables into one table
#4a is the network one
CREATE TABLE spd_working_4a AS
#insert into spd_working_4a 
SELECT investment, record_year, pooled_node, network, category,
IF(SUM(net_charge_uncapped)>0, SUM(net_charge_uncapped),0) AS net_charge_uncapped 
FROM (
  SELECT investment, record_year, pooled_node, generator AS network, 'load_injection' AS category, net_charge_uncapped FROM spd_working_3a
UNION ALL
  SELECT investment, record_year, pooled_node, generator AS network, 'generation' AS category, net_charge_uncapped FROM spd_working_3b
UNION ALL
  SELECT investment, record_year, pooled_node, network, 'load_offtake' AS category, net_charge_uncapped FROM spd_working_3c
UNION ALL
  SELECT investment, record_year, pooled_node, DC AS network, 'DC' AS category, net_charge_uncapped FROM spd_working_3e) AS a
GROUP BY investment, record_year, pooled_node, network, category;

#Group the '3' tables into one table
#4b is the purchaser one
CREATE TABLE spd_working_4b AS
SELECT investment, record_year, pooled_node, purchaser, category,
IF(SUM(net_charge_uncapped)>0, SUM(net_charge_uncapped),0) AS net_charge_uncapped
FROM (
  SELECT investment, record_year, pooled_node, generator AS purchaser, 'load_injection' AS category, net_charge_uncapped FROM spd_working_3a
UNION ALL
  SELECT investment, record_year, pooled_node, generator AS purchaser, 'generation' AS category, net_charge_uncapped FROM spd_working_3b
UNION ALL
  SELECT investment, record_year, pooled_node, purchaser, 'load_offtake' AS category, net_charge_uncapped FROM spd_working_3d
UNION ALL
  SELECT investment, record_year, pooled_node, DC AS purchaser, 'DC' AS category, net_charge_uncapped FROM spd_working_3e) AS a
GROUP BY investment, record_year, purchaser, category;

#Sum up the poc charges to island
CREATE TABLE spd_charges_by_network AS
SELECT investment, network, category, island,
SUM(net_charge_with_yearly_capping)/1e6 AS charge_with_yearly_capping_m,
SUM(net_charge_with_monthly_capping)/1e6 AS charge_with_monthly_capping_m,
SUM(net_charge_with_daily_capping)/1e6 AS charge_with_daily_capping_m,
SUM(net_charge_uncapped)/1e6 AS charge_uncapped
FROM spd_working_4a AS a
INNER JOIN prefix_to_region AS b
ON SUBSTRING(a.pooled_node,1,3)=b.prefix
GROUP BY investment, network, category, island;

#Sum up the poc charges to island
CREATE TABLE spd_charges_by_participant AS
SELECT investment, purchaser, category, island, 
SUM(net_charge_with_yearly_capping)/1e6 AS charge_with_yearly_capping_m,
SUM(net_charge_with_monthly_capping)/1e6 AS charge_with_monthly_capping_m,
SUM(net_charge_with_daily_capping)/1e6 AS charge_with_daily_capping_m,
SUM(net_charge_uncapped)/1e6 AS charge_uncapped
FROM spd_working_4b AS a
INNER JOIN prefix_to_region AS b
ON SUBSTRING(a.pooled_node,1,3)=b.prefix
GROUP BY investment, purchaser, category, island;

#The next section creates the heatmap outputs. Both for generation, and load. Generation is first:

SELECT a.investment, LEAST(1,a.amount_pa/(tot/1000000)) AS scalefactor, buscharge, node_level, sumg,
	node_level* LEAST(1,a.amount_pa/(tot/1000000)) AS scaled_charge,
	node_level* LEAST(1,a.amount_pa/(tot/1000000))/sumg AS perMW
FROM tpm3.`spd_amounts_to_recover_pre_lce` a
INNER JOIN (SELECT SUM(net_charge_uncapped) AS tot, investment
	FROM tpm3.spd_working_4a
	GROUP BY investment) b
ON a.investment = b.investment
INNER JOIN (SELECT SUM(net_charge_uncapped) AS node_level, investment, LEFT(pooled_node,3) AS buscharge
	FROM tpm3.spd_working_4a
	WHERE category = 'generation'
	GROUP BY investment, LEFT(pooled_node,3)) bx
ON a.investment = bx.investment
INNER JOIN (SELECT SUM(generation)/2 sumg, LEFT(pooled,3) AS busgen
	FROM o_raw_node_ir a INNER JOIN `spd_node_pooling` b
	ON a.node = b.node
	WHERE runtype = 'base3k'
	AND generation > 0
	GROUP BY LEFT(pooled,3)) c
ON busgen = buscharge;

SELECT buscharge, '13', SUM(node_level* LEAST(1,a.amount_pa/(tot/1000000))/sumg) AS perMW
FROM tpm3.`spd_amounts_to_recover_pre_lce` a
INNER JOIN (SELECT SUM(net_charge_uncapped) AS tot, investment
	FROM tpm3.spd_working_4a
	GROUP BY investment) b
ON a.investment = b.investment
INNER JOIN (SELECT SUM(net_charge_uncapped) AS node_level, investment, LEFT(pooled_node,3) AS buscharge
	FROM tpm3.spd_working_4a
	WHERE category = 'generation'
	GROUP BY investment, LEFT(pooled_node,3)) bx
ON a.investment = bx.investment
INNER JOIN (SELECT SUM(generation)/2 sumg, LEFT(pooled,3) AS busgen
	FROM o_raw_node_ir a INNER JOIN `spd_node_pooling` b
	ON a.node = b.node
	WHERE runtype = 'base3k'
	AND generation > 0
	GROUP BY LEFT(pooled,3)) c
ON busgen = buscharge
AND buscharge <> 'whi' 
GROUP BY buscharge;

#This maps load charges by network company, to POC for the heatmap script. 
# Heatoutx.csv is a csv file with 'unpivotted' values by network region and scenario
#Mapping values back to nodes for the heatmap
#Load in the data to table "heatmap_inputs"

CREATE TABLE test.heatmaps_inputs
	(network	VARCHAR(31),
	scenario	VARCHAR(127),
	val	FLOAT);
LOAD DATA LOCAL INFILE "heatout3.csv" INTO TABLE test.heatmaps_inputs
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

ALTER TABLE test.heatmaps_inputs ADD PRIMARY KEY (network, scenario);

#make mapping table from output
CREATE TABLE tpm3.heatmap_mapping_load
AS SELECT LEFT(poc,3) AS bus, b.network_name AS network
FROM (SELECT LEFT(poc,3) AS bus, MAX(network_poc_demand) AS maxer FROM test.total_per_network_and_poc a
	WHERE trading_Date = '2014-06-01'
	AND trading_period = 36
	GROUP BY LEFT(poc,3)) a
INNER JOIN test.total_per_network_and_poc b
ON bus = LEFT(poc,3)
AND a.maxer = b.network_poc_demand
WHERE trading_Date = '2014-06-01'
AND trading_period = 36;

#Query
SELECT bus, scenario, val
FROM test.heatmaps_inputs a
INNER JOIN tpm3.heatmap_mapping_load b
ON a.network = b.network
GROUP BY scenario, bus;

#net AMD
SELECT MAX(pshare), network, poc
FROM (SELECT a.record_Date, a.tp, network, poc, `nshare`*demand pshare FROM `reconciliation_network_dw` a
INNER JOIN tpm3.o_raw_node_ir b
ON a.record_Date = b.record_Date
AND a.tp = b.tp
AND poc = node) a
GROUP BY network, poc
