### "Prepare all raw allocations.sql"

# Postprocess vSPD results for the SPD charge, and the interconnector part of the 'Zonal 3' charge.
#  Load results into SQL and convert them into 'raw allocations'.
# Also calculate 'raw allocations' for the GIT-based charge.
# Also calculate 'raw allocations' for the non-interconnector parts of the 'Zonal 3' charge.

# Most of the results produced by this script cover a four-month period only,
#  but 'raw allocations' are also calculated for a 3-year period for selected investments, under the SPD and 'Zonal 3' charges.

# Before you run this script, directory "C:\TPM\Inputs\" should contain:
# - Subdirectories "\from vSPD" and "\from vSPD\3 years" containing various "_NodeResults_TP.csv" files produced by vSPD 
# - "Table of runs.csv" - which lists the above runs and organises them into factual/counterfactual pairs 
# - Results of preparatory calculations for GIT-based method - "Base case nodal charges.csv"
# - Results of preparatory calculations for non-interconnector parts of the Zonal 3 method - "Zonal 3 base case non asset specific charging rate.csv", "Zonal 3 base case within region charging rates.csv"

# The resulting 'raw allocations' tables in SQL will be: 'vspd.tpm_raw_allocations_gen', 'vspd.tpm_raw_allocations_load'

/* Load vSPD output into SQL */

CREATE TABLE vspd.tpm_nodal_results (
  `runtype` VARCHAR(15) NOT NULL,
  `record_date` DATE NOT NULL,
  `tp` INT NOT NULL,
  `node` VARCHAR(13) NOT NULL,
  `generation` FLOAT DEFAULT NULL,
  `demand` FLOAT DEFAULT NULL,
  `price` FLOAT DEFAULT NULL,
  `deficit` FLOAT DEFAULT NULL,
  `net_benefit` FLOAT DEFAULT NULL,
  PRIMARY KEY (`runtype`,`record_date`,`tp`,`node`)
); 

CREATE TABLE vspd.load_tpm_nodal_results (raw_date VARCHAR(20), node VARCHAR(13), generation DOUBLE, demand DOUBLE, price DOUBLE, junk2 DOUBLE, junk3 DOUBLE, deficit DOUBLE, junk5 DOUBLE, net_benefit DOUBLE, junk6 DOUBLE);

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\base7_1000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'base7_1000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\base7_5000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'base7_5000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\base7_IR_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'base7_IR',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\base7_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'base7',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\CNI_UNI_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'CNI_UNI',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\CSI_USI_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'CSI_USI',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\LNI_CNI_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'LNI_CNI',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\LSI_CSI_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'LSI_CSI',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\mono_1000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'mono_1000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\mono_5000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'mono_5000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monobidz1_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monobidz1',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monobidz2_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monobidz2',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monobidz3_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monobidz3',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monobidz4_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monobidz4',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monopole_IR_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monopole_IR',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monopole_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monopole',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\monopole1000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'monopole1000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\nobidz1_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'nobidz1',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\nobidz2_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'nobidz2',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\nobidz3_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'nobidz3',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noBPEHAY_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noBPEHAY',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noLSI_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noLSI',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noLSIRel_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noLSIRel',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noNAaN_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noNAaN',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noNIGU_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noNIGU',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\nopole_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'nopole',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\nopole1000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'nopole1000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noUSI_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noUSI',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noWRK_1000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noWRK_1000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noWRK_5000_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noWRK_5000',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\noWRK_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'noWRK',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\WRKbidz1_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'WRKbidz1',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\WRKbidz2_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'WRKbidz2',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\WRKbidz3_NodeResults_TP.csv'
  INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
INSERT INTO vspd.tpm_nodal_results SELECT 'WRKbidz3',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\base2011a_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\base2011_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\base2012_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\base7_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\base2013_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
DELETE FROM vspd.load_tpm_nodal_results WHERE (SUBSTRING(raw_date,1,11)="03-APR-2011" OR SUBSTRING(raw_date,1,11)="01-APR-2012" OR SUBSTRING(raw_date,1,11)="07-APR-2013") AND (SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=5 OR SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=6); 
INSERT INTO vspd.tpm_nodal_results SELECT 'base7_3years',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\NIGU2011a_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\NIGU2011_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\NIGU2012_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\noNIGU_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\NIGU2013_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
DELETE FROM vspd.load_tpm_nodal_results WHERE (SUBSTRING(raw_date,1,11)="03-APR-2011" OR SUBSTRING(raw_date,1,11)="01-APR-2012" OR SUBSTRING(raw_date,1,11)="07-APR-2013") AND (SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=5 OR SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=6); 
INSERT INTO vspd.tpm_nodal_results SELECT 'noNIGU_3years',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\mono2011a_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\mono2011_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\Mono2012_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\monopole_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\Mono2013_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
DELETE FROM vspd.load_tpm_nodal_results WHERE (SUBSTRING(raw_date,1,11)="03-APR-2011" OR SUBSTRING(raw_date,1,11)="01-APR-2012" OR SUBSTRING(raw_date,1,11)="07-APR-2013") AND (SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=5 OR SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=6); 
INSERT INTO vspd.tpm_nodal_results SELECT 'monopole_3years',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\WRK2011_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\WRK2012_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\noWRK_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\WRK2013_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
DELETE FROM vspd.load_tpm_nodal_results WHERE (SUBSTRING(raw_date,1,11)="03-APR-2011" OR SUBSTRING(raw_date,1,11)="01-APR-2012" OR SUBSTRING(raw_date,1,11)="07-APR-2013") AND (SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=5 OR SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=6); 
INSERT INTO vspd.tpm_nodal_results SELECT 'noWRK_3years',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\CNI_UNI2011a_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\CNI_UNI2011b_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\CNI_UNI2012_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\CNI_UNI_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\CNI_UNI2013_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
DELETE FROM vspd.load_tpm_nodal_results WHERE (SUBSTRING(raw_date,1,11)="03-APR-2011" OR SUBSTRING(raw_date,1,11)="01-APR-2012" OR SUBSTRING(raw_date,1,11)="07-APR-2013") AND (SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=5 OR SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=6); 
INSERT INTO vspd.tpm_nodal_results SELECT 'CNI_UNI_3years',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.load_tpm_nodal_results;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\LSI_CSI2011a_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\LSI_CSI2011_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\LSI_CSI2012_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\LSI_CSI_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\from vSPD\\3 years\\LSI_CSI2013_NodeResults_TP.csv' INTO TABLE vspd.load_tpm_nodal_results COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
DELETE FROM vspd.load_tpm_nodal_results WHERE (SUBSTRING(raw_date,1,11)="03-APR-2011" OR SUBSTRING(raw_date,1,11)="01-APR-2012" OR SUBSTRING(raw_date,1,11)="07-APR-2013") AND (SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=5 OR SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1=6); 
INSERT INTO vspd.tpm_nodal_results SELECT 'LSI_CSI_3years',STR_TO_DATE(SUBSTRING(raw_date,1,11),'%d-%M-%Y'),SUBSTRING(raw_date,13,2)*2+SUBSTRING(raw_date,16,2)/30+1,node,generation,demand,price,deficit,net_benefit FROM vspd.load_tpm_nodal_results;

DELETE FROM vspd.tpm_nodal_results WHERE generation=0 AND demand=0;
DELETE FROM vspd.tpm_nodal_results WHERE record_date>='2013-10-01';


/* test the load worked - should be 34 runtypes with approx. 1.4M records and approx. 13.8 TWh of demand each,
                                 and 6 runtypes with approx. 11.5M records and approx. 110 TWh of demand each. */

SELECT runtype, COUNT(*), SUM(generation)/2e6, SUM(demand)/2e6, MIN(record_date), MAX(record_date) FROM vspd.tpm_nodal_results GROUP BY runtype;

/* load the list of runs - each has a factual and a counterfactual */

CREATE TABLE vspd.tpm_run_table (approach VARCHAR(10), variant VARCHAR(25), component VARCHAR(20), factual_runtype VARCHAR(15), counterfactual_runtype VARCHAR(15));
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\Table of runs.csv'
  INTO TABLE vspd.tpm_run_table COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

/* Where VoLL differs between factual and counterfactual, the difference in 'net benefit' as output by vSPD is no longer a meaningful representation
   of private benefit. Correct this problem... */
   
UPDATE vspd.tpm_nodal_results SET net_benefit=(3000-price)*(CASE WHEN deficit>demand THEN 0 ELSE demand-deficit END)/2
WHERE (runtype='nopole1000' OR runtype='monopole1000' OR runtype='CNI_UNI' OR runtype='LNI_CNI' OR runtype='CSI_USI' OR runtype='LSI_CSI' OR runtype='CNI_UNI_3years' OR runtype='LSI_CSI_3years') AND demand>0;

# Interconnector deprival runs use varying VoLL in different places
UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-150)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE (runtype='CNI_UNI' OR runtype='CNI_UNI_3years') AND demand>0 AND (
node='ALB0331' OR node='ALB1101' OR node='ALB2201' OR node='BHL2201' OR node='BHL2202' OR node='BOB0331' OR node='BOB1101' OR node='BRB0331' OR 
node='BRB2201' OR node='DRY2201' OR node='DRY2202' OR node='GLN0331' OR node='GLN0332' OR node='GLN0332 GLN0' OR node='GLN2201' OR node='HEN0331' OR 
node='HEN1101' OR node='HEN2201' OR node='HEP0331' OR node='HEP1101' OR node='HOB2201' OR node='HPI2201' OR node='HPI2202' OR node='KEN0331' OR 
node='KEN1101' OR node='KEN1102' OR node='KOE1101' OR node='MDN1101' OR node='MDN2201' OR node='MER0331' OR node='MNG0331' OR node='MNG1101' OR 
node='MPE1101' OR node='MTO0331' OR node='MTO1101' OR node='MTO1102' OR node='NGA0331' OR node='OTA0221' OR node='OTA1101' OR node='OTA1101 OTG0' OR 
node='OTA1102' OR node='OTA1103' OR node='OTA1104' OR node='OTA1105' OR node='OTA1106' OR node='OTA1107' OR node='OTA1108' OR node='OTA1109' OR 
node='OTA110A' OR node='OTA2201' OR node='OTA2202' OR node='OTA2202 OTC0' OR node='OTA2203' OR node='PAK0331' OR node='PAK2201' OR node='PEN0221' OR 
node='PEN0222' OR node='PEN0251' OR node='PEN0331' OR node='PEN1101' OR node='PEN2201' OR node='ROS0221' OR node='ROS1101' OR node='SVL0331' OR 
node='SVL2201' OR node='SVL2202' OR node='SWN0251' OR node='SWN2201' OR node='SWN2201 SWN0' OR node='SWN2201 SWN5' OR node='TAK0331' OR node='TAK2201' OR 
node='TAK2202' OR node='WEL0331' OR node='WEL1101' OR node='WEL1102' OR node='WIR0331' OR node='WIR1101' OR node='WIR1102' OR node='WRD0331' OR 
node='WRD2201' OR node='WRU2201');

UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-300)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE runtype='LNI_CNI' AND demand>0 AND (
node='BRK0331' OR node='BRK2201' OR node='BRK2202' OR node='RPO2201' OR node='RPO2201 RPO0' OR node='TKU0331' OR node='TKU2201' OR node='TKU2201 TKU0' OR 
node='TNG0111' OR node='TNG0551' OR node='TNG2201');

UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-1000)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE runtype='LNI_CNI' AND demand>0 AND (
node='BPE0331' OR node='BPE0331 TWF0' OR node='BPE0551' OR node='BPE1101' OR node='BPE2201' OR node='CPK0111' OR node='CPK0331' OR node='CPK1101' OR 
node='CPK1102' OR node='CPK1103' OR node='DVK0111' OR node='DVK1101' OR node='DVK1102' OR node='GFD0331' OR node='GFD1101' OR node='GFD1102' OR 
node='GYT0331' OR node='GYT1101' OR node='GYT1102' OR node='HAY0111' OR node='HAY0331' OR node='HAY1101' OR node='HAY2201' OR node='HAY3502' OR 
node='HAY3503' OR node='KWA0111' OR node='KWA1101' OR node='KWA1102' OR node='LTN0331' OR node='LTN0331 TWF0' OR node='LTN2201' OR node='LTN2202' OR 
node='MGM0331' OR node='MGM1101' OR node='MHO0331' OR node='MHO0331 MHO0' OR node='MHO1101' OR node='MHO1102' OR node='MHT1102' OR node='MHT1103' OR 
node='MLG0111' OR node='MLG0331' OR node='MLG1101' OR node='MLG1102' OR node='MST0331' OR node='MST1101' OR node='PNI0331' OR node='PNI1101' OR 
node='PNI1102' OR node='PRM0331' OR node='PRM1101' OR node='PRM1102' OR node='TKR0331' OR node='TKR1101' OR node='TWC2201' OR node='TWC2201 NZW0' OR 
node='TWC2201 TWF0' OR node='TWT2201' OR node='UHT0331' OR node='UHT1101' OR node='WDV0111' OR node='WDV1101' OR node='WDV1101 TAP0' OR node='WDV1102' OR 
node='WIL0331' OR node='WIL1101' OR node='WIL2201' OR node='WPW0331' OR node='WPW1101' OR node='WPW1102' OR node='WWD1102' OR node='WWD1102 WWD0' OR 
node='WWD1103' OR node='WWD1103 WWD0');

UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-150)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE runtype='CSI_USI' AND demand>0 AND (
node='ADD0111' OR node='ADD0661' OR node='APS0111' OR node='APS0661' OR node='ARG1101' OR node='ARG1101 BRR0' OR node='ASY0111' OR node='ASY0661' OR 
node='ATU1101' OR node='BLN0331' OR node='BLN1101' OR node='BRY0111' OR node='BRY0661' OR node='BRY2201' OR node='CLH0111' OR node='CLH0661' OR 
node='COB0661' OR node='COB0661 COB0' OR node='COL0111' OR node='COL0661' OR node='COL0661 COL0' OR node='CUL0331' OR node='CUL0661' OR 
node='CUL2202' OR node='CUL2203' OR node='DOB0331' OR node='DOB0661' OR node='DOB1101' OR node='DOB1102' OR node='GYM0661' OR node='HKK0661' OR 
node='HOR0331' OR node='HOR0661' OR node='IGH1101' OR node='ISL0331' OR node='ISL0661' OR node='ISL2201' OR node='KAI0111' OR node='KAI0661' OR 
node='KBT0661' OR node='KBT0662' OR node='KBY0661' OR node='KBY0662' OR node='KIK0111' OR node='KIK1101' OR node='KIK2201' OR node='KUM0661' OR 
node='KUM0661 KUM0' OR node='MCH0111' OR node='MCH1101' OR node='MLN0661' OR node='MLN0664' OR node='MLT0661' OR node='MLT0664' OR node='MOT0111' OR 
node='MOT0661' OR node='MOT0662' OR node='MPI0661' OR node='ORO1101' OR node='ORO1102' OR node='OTI0111' OR node='OTI0661' OR node='RFC1101' OR 
node='RFN1101' OR node='RFN1102' OR node='SBK0331' OR node='SBK0661' OR node='SPN0331' OR node='SPN0661' OR node='SPN0662' OR node='STK0331' OR 
node='STK0661' OR node='STK1101' OR node='STK2201' OR node='UTK0661' OR node='WMG1101' OR node='WMG1102' OR node='WPR0331' OR node='WPR0661' OR 
node='WPR2202' OR node='WPR2203' OR node='WPT0111' OR node='WPT1101' OR node='WPT1102');

UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-300)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE runtype='CSI_USI' AND demand>0 AND (
node='ABY0111' OR node='ABY1101' OR node='ASB0331' OR node='ASB0661' OR node='ASB0661 HBK0' OR node='ASB2201' OR node='TIM0111' OR node='TIM1101' OR 
node='TIM2201' OR node='TIM2202' OR node='TKA0111' OR node='TKA0111 TKA1' OR node='TKA0331' OR node='TKA1101' OR node='TMK0331' OR node='TMK1101' OR 
node='TMK1102');

UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-300)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE (runtype='LSI_CSI' OR runtype='LSI_CSI_3years') AND demand>0 AND (
node='CML0331' OR node='CML1101' OR node='CML1102' OR node='CML2201' OR node='CML2202' OR node='CYD0331' OR node='CYD2201' OR node='CYD2201 CYD0' OR 
node='FKN0331' OR node='FKN1101' OR node='FKN1102' OR node='NSY0331' OR node='NSY0331 PAE0' OR node='NSY2201');

UPDATE vspd.tpm_nodal_results SET net_benefit=net_benefit+(3000-1000)*(CASE WHEN deficit>demand THEN demand ELSE deficit END)/2 WHERE (runtype='LSI_CSI' OR runtype='LSI_CSI_3years') AND demand>0 AND (
node='BAL0331' OR node='BAL1101' OR node='BDE0111' OR node='BDE1101' OR node='BPC1101' OR node='BPD1101' OR node='BPT1101' OR node='BWK1101' OR node='BWK1101 WPI0' OR node='EDN0331' OR 
node='EDN1101' OR node='GNY1101' OR node='GOR0331' OR node='GOR1101' OR node='GOR1102' OR node='GOR2201' OR node='HWB0331' OR node='HWB0331 MAH0' OR 
node='HWB0331 WPI0' OR node='HWB0332' OR node='HWB1101' OR node='HWB2201' OR node='INV0331' OR node='INV1101' OR node='INV2201' OR node='INV2202' OR 
node='MAN2201' OR node='MAN2201 MAN0' OR node='NMA0331' OR node='NMA0331 WHL0' OR node='NMA2201' OR node='OAM0331' OR node='OAM1101' OR node='OAM1102' OR 
node='PAL0331' OR node='PAL1101' OR node='ROX1101' OR node='ROX1101 ROX0' OR node='ROX2201' OR node='ROX2201 ROX0' OR node='SDN0331' OR node='SDN2201' OR 
node='STU0111' OR node='STU1101' OR node='TMH2201' OR node='TWI2201');
   
/* Calculate raw allocations for SPD method, and for interconnector part of Zonal 3 method */

CREATE TABLE vspd.tpm_raw_allocations_gen AS
SELECT approach, variant, component, record_date, tp AS trading_period, node, ROUND(SUM(factual_net_benefit)-SUM(counterfactual_net_benefit),3) AS net_benefit
FROM (
 SELECT approach, variant, component, record_date, tp, node, net_benefit AS factual_net_benefit, 0 AS counterfactual_net_benefit
 FROM vspd.tpm_nodal_results AS a
 INNER JOIN vspd.tpm_run_table AS d
 ON a.runtype=d.factual_runtype
 WHERE a.generation>0 OR a.demand<0
UNION ALL
 SELECT approach, variant, component, record_date, tp, node, 0 AS factual_net_benefit, net_benefit AS counterfactual_net_benefit
 FROM vspd.tpm_nodal_results AS a
 INNER JOIN vspd.tpm_run_table AS d
 ON a.runtype=d.counterfactual_runtype
 WHERE a.generation>0 OR a.demand<0
) AS c
GROUP BY approach, variant, component, record_date, tp, node;

CREATE TABLE vspd.tpm_raw_allocations_load AS
SELECT approach, variant, component, record_date, tp AS trading_period, node, ROUND(SUM(factual_net_benefit)-SUM(counterfactual_net_benefit),3) AS net_benefit
FROM (
 SELECT approach, variant, component, record_date, tp, node, net_benefit AS factual_net_benefit, 0 AS counterfactual_net_benefit
 FROM vspd.tpm_nodal_results AS a
 INNER JOIN vspd.tpm_run_table AS d
 ON a.runtype=d.factual_runtype
 WHERE a.demand>0
UNION ALL
 SELECT approach, variant, component, record_date, tp, node, 0 AS factual_net_benefit, net_benefit AS counterfactual_net_benefit
 FROM vspd.tpm_nodal_results AS a
 INNER JOIN vspd.tpm_run_table AS d
 ON a.runtype=d.counterfactual_runtype
 WHERE a.demand>0
) AS c
GROUP BY approach, variant, component, record_date, tp, node;

DELETE FROM vspd.tpm_raw_allocations_gen WHERE net_benefit=0;
DELETE FROM vspd.tpm_raw_allocations_load WHERE net_benefit=0;

/* Prepare raw allocations for GIT-based charge */

CREATE TABLE vspd.temp_git_based_charges (node VARCHAR(7), charge DOUBLE);
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\Base case nodal charges.csv'
INTO TABLE vspd.temp_git_based_charges COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

CREATE TABLE vspd.temp_total_loads AS
SELECT record_date, tp, node, SUM(demand)/2 AS net_demand_mwh
FROM vspd.tpm_nodal_results WHERE runtype='base7' GROUP BY record_date, tp, node;

CREATE INDEX idx ON vspd.temp_git_based_charges (node);
CREATE INDEX idx ON vspd.temp_total_loads (node);

INSERT INTO vspd.tpm_raw_allocations_load 
SELECT 'GIT', '4 month base approach', 'All', a.record_date, a.tp, a.node, ROUND(a.net_demand_mwh * b.charge,2)
FROM vspd.temp_total_loads AS a
LEFT JOIN vspd.temp_git_based_charges AS b
ON a.node=b.node
WHERE a.net_demand_mwh>0 AND b.charge<>0;

INSERT INTO vspd.tpm_raw_allocations_gen 
SELECT 'GIT', '4 month base approach', 'All', a.record_date, a.tp, a.node, ROUND(a.net_demand_mwh * b.charge,2) 
FROM vspd.temp_total_loads AS a
LEFT JOIN vspd.temp_git_based_charges AS b
ON a.node=b.node
WHERE a.net_demand_mwh<0 AND b.charge<>0;

/* Prepare raw allocations for non-interconnector parts of Zonal 3 method */

CREATE TABLE vspd.temp_zonal_3_within_region_charging_rates (node VARCHAR(13), charge DOUBLE);
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\Zonal 3 base case within region charging rates.csv'
INTO TABLE vspd.temp_zonal_3_within_region_charging_rates 
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

CREATE TABLE vspd.temp_zonal_3_residual_charging_rate (load_charge DOUBLE, gen_charge DOUBLE);
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\Zonal 3 base case non asset specific charging rate.csv'
INTO TABLE vspd.temp_zonal_3_residual_charging_rate
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

CREATE TABLE vspd.temp_total_gens AS
SELECT record_date, tp, node, SUM(generation)/2 AS generation_mwh
FROM vspd.tpm_nodal_results WHERE runtype='base7' GROUP BY record_date, tp, node;

CREATE INDEX idx ON vspd.temp_zonal_3_within_region_charging_rates (node);
CREATE INDEX idx ON vspd.temp_total_gens (node);

INSERT INTO vspd.tpm_raw_allocations_load 
SELECT 'Zonal 3', '4 month base approach', 'Within region', record_date, tp, node, ROUND(dollars,2) FROM
(SELECT record_date, tp, a.node, a.net_demand_mwh * (b.charge + c.load_charge) AS dollars
 FROM vspd.temp_total_loads AS a
 LEFT JOIN vspd.temp_zonal_3_within_region_charging_rates AS b
 ON a.node=b.node
 INNER JOIN vspd.temp_zonal_3_residual_charging_rate AS c
 WHERE net_demand_mwh>0) AS d
WHERE dollars IS NOT NULL AND dollars<>0;

INSERT INTO vspd.tpm_raw_allocations_gen 
SELECT 'Zonal 3', '4 month base approach', 'Within region', record_date, tp, node, ROUND(dollars,2) FROM
(SELECT a.record_date, a.tp, a.node, ROUND(a.generation_mwh * (b.charge + c.gen_charge),2) AS dollars
 FROM vspd.temp_total_gens AS a
 LEFT JOIN vspd.temp_zonal_3_within_region_charging_rates AS b
 ON a.node=b.node
 INNER JOIN vspd.temp_zonal_3_residual_charging_rate AS c
UNION ALL
 SELECT a.record_date, a.tp, a.node, ROUND(-a.net_demand_mwh * (b.charge + c.gen_charge),2) AS dollars
 FROM vspd.temp_total_loads AS a
 LEFT JOIN vspd.temp_zonal_3_within_region_charging_rates AS b
 ON a.node=b.node
 INNER JOIN vspd.temp_zonal_3_residual_charging_rate AS c
 WHERE net_demand_mwh<0 
) AS d 
WHERE dollars IS NOT NULL AND dollars<>0;

/* Fix an omission of two days in the 3 year factual case */

DELETE FROM vspd.tpm_raw_allocations_load WHERE record_date='2013-01-01' OR record_date='2013-01-02';
DELETE FROM vspd.tpm_raw_allocations_gen WHERE record_date='2013-01-01' OR record_date='2013-01-02';

/* testing - For 4 month runs, expect 33 rows - For 3 year runs, expect 5 rows */

SELECT approach, variant, component, SUM(net_benefit) FROM vspd.tpm_raw_allocations_load
GROUP BY approach, variant, component;
SELECT approach, variant, component, SUM(net_benefit) FROM vspd.tpm_raw_allocations_gen
GROUP BY approach, variant, component;

/* Create indices */

CREATE INDEX idx ON vspd.tpm_raw_allocations_gen  (approach, variant, component, record_date, node, trading_period);
CREATE INDEX idx ON vspd.tpm_raw_allocations_load (approach, variant, component, record_date, node, trading_period);

