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

# The analysis underlying these inputs is provided in 'Modelled_calculation_of_LRMC.xlsx'. 

USE tpm2;

CREATE TABLE LRMC_rates (investment VARCHAR(60), LRMC_region VARCHAR(50), rate_dollars_per_kw DOUBLE);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\LRMC\\LRMC_rates.csv'
INTO TABLE LRMC_rates
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON LRMC_rates (investment, LRMC_region);

CREATE TABLE LRMC_region_definitions (LRMC_region VARCHAR(50), node VARCHAR(15), load_or_generation VARCHAR(10), generation_enabling CHAR(1));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\LRMC\\LRMC_region_definitions.csv'
INTO TABLE LRMC_region_definitions
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON LRMC_region_definitions (LRMC_region);
CREATE INDEX idx2 ON LRMC_region_definitions (node);

# some checks

SELECT DISTINCT LRMC_region FROM LRMC_rates WHERE NOT (LRMC_region IN (SELECT DISTINCT LRMC_region FROM LRMC_region_definitions)); # should be none
SELECT DISTINCT LRMC_region FROM LRMC_region_definitions WHERE NOT (LRMC_region IN (SELECT DISTINCT LRMC_region FROM LRMC_rates)); # should be none
SELECT DISTINCT node FROM LRMC_region_definitions WHERE NOT (node IN (SELECT DISTINCT node FROM load_nodes)) AND NOT (node IN (SELECT DISTINCT node FROM gg_nodes)); # should be none
SELECT DISTINCT generation_enabling FROM LRMC_region_definitions; # should only be F or T
