# This script loads input tables that are used in the calculation of the SPD charge.

USE tpm2;

CREATE TABLE spd_table_of_runs (investment VARCHAR(30),factual_runtype VARCHAR(20),counterfactual_runtype VARCHAR(20));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\SPD\\Postprocessing\\table_of_runs.csv'
INTO TABLE spd_table_of_runs
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE TABLE spd_amounts_to_recover_pre_lce (investment VARCHAR(30),amount_m_per_year DOUBLE);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\SPD\\Postprocessing\\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;

CREATE INDEX idx ON spd_amounts_to_recover_pre_lce (investment);

CREATE TABLE spd_node_pooling (node VARCHAR(20), pooled VARCHAR(20));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\SPD\\Postprocessing\\node_pooling.csv'
INTO TABLE spd_node_pooling
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON spd_node_pooling (node);

# some checks

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 investment FROM spd_amounts_to_recover_pre_lce WHERE NOT (investment IN (SELECT DISTINCT investment FROM spd_table_of_runs)); # should be none
SELECT investment FROM spd_table_of_runs WHERE NOT (investment IN (SELECT DISTINCT investment FROM spd_table_of_runs)); # should be none
SELECT node FROM spd_node_pooling WHERE NOT (node IN (SELECT DISTINCT node FROM o_raw_node_ir)); # should be none 



