# This script loads lookup tables used throughout the analysis

USE tpm2;

CREATE TABLE offer_node_to_participant (generator VARCHAR(20), offer_code VARCHAR(14));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\offer_node_to_participant.csv'
INTO TABLE offer_node_to_participant
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON offer_node_to_participant (offer_code);

CREATE TABLE injecting_load_node_to_participant (generator VARCHAR(20), injecting_load_node VARCHAR(7));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\injecting_load_node_to_participant.csv'
INTO TABLE injecting_load_node_to_participant
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON injecting_load_node_to_participant (injecting_load_node);

CREATE TABLE prefix_to_region (prefix VARCHAR(3), region VARCHAR(30), island VARCHAR(1));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\prefix_to_region.csv'
INTO TABLE prefix_to_region
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON prefix_to_region (prefix);

CREATE TABLE days_per_month (record_year INT, record_month INT, n_days INT);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\days_per_month.csv'
INTO TABLE days_per_month
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON days_per_month (record_year, record_month);

CREATE TABLE prefix_to_rcpd_region (prefix VARCHAR(3), rcpd_region VARCHAR(3));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\prefix_to_rcpd_region.csv'
INTO TABLE prefix_to_rcpd_region
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON prefix_to_rcpd_region (prefix);

CREATE TABLE network_to_island (network VARCHAR(30), island VARCHAR(1), half_island VARCHAR(3));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\network_to_island.csv'
INTO TABLE network_to_island
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON network_to_island (network);

CREATE TABLE market_participants (participant VARCHAR(30));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\market_participants.csv'
INTO TABLE market_participants
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON market_participants (participant);

CREATE TABLE transmission_customers (customer VARCHAR(30));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\transmission_customers.csv'
INTO TABLE transmission_customers
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON transmission_customers (customer);

CREATE TABLE load_nodes (node VARCHAR(15));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\list_of_load_nodes.csv'
INTO TABLE load_nodes
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON load_nodes (node);

CREATE TABLE gg_nodes (node VARCHAR(15));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\preprocessing\\list_of_gg_nodes.csv'
INTO TABLE gg_nodes
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON gg_nodes (node);

/* checks */

SELECT DISTINCT generator FROM offer_node_to_participant WHERE NOT (generator IN (SELECT participant FROM market_participants)); # should be none
SELECT DISTINCT generator FROM injecting_load_node_to_participant WHERE NOT (generator IN (SELECT participant FROM market_participants)); # should be none
SELECT DISTINCT rcpd_region FROM prefix_to_rcpd_region; # should be four
SELECT DISTINCT offer_code FROM offer_node_to_participant WHERE NOT (offer_code IN (SELECT DISTINCT node FROM gg_nodes)); # should be none
SELECT DISTINCT node FROM gg_nodes WHERE NOT (node IN (SELECT DISTINCT offer_code FROM offer_node_to_participant)); # should be none
SELECT DISTINCT injecting_load_node FROM injecting_load_node_to_participant WHERE NOT (injecting_load_node IN (SELECT DISTINCT node FROM load_nodes)); # should be none
SELECT DISTINCT node FROM gg_nodes WHERE NOT (SUBSTRING(node,1,3) IN (SELECT prefix FROM prefix_to_region)); # should be none
SELECT DISTINCT node FROM load_nodes WHERE NOT (SUBSTRING(node,1,3) IN (SELECT prefix FROM prefix_to_region)); # should be none
SELECT DISTINCT node FROM gg_nodes WHERE NOT (SUBSTRING(node,1,3) IN (SELECT prefix FROM prefix_to_rcpd_region)); # should be none
SELECT DISTINCT node FROM load_nodes WHERE NOT (SUBSTRING(node,1,3) IN (SELECT prefix FROM prefix_to_rcpd_region)); # should be none
SELECT generator FROM offer_node_to_participant WHERE NOT (generator IN (SELECT customer FROM transmission_customers)); # should be none
SELECT DISTINCT network FROM network_to_island WHERE NOT (network IN (SELECT customer FROM transmission_customers)); # should be none
SELECT DISTINCT island FROM network_to_island; # should only be N and S
SELECT DISTINCT half_island FROM network_to_island; # should be UNI, LNI, USI, LSI

