# This script loads input data for the calculation of the RCPD charge.

# You must first run '/Lookups/load_lookup_tables.sql'.

USE tpm3;

CREATE TABLE rcpd_mapping (node VARCHAR(20), rcpd_aggregation VARCHAR(20));
LOAD DATA LOCAL INFILE 'Y:\\Client Job Files\\Electricity Authority\\Transmission\\EAT004 - TPM options paper\\Board paper in Sep 2015\\Revised_RCPD_charge\\mapping.csv'
INTO TABLE rcpd_mapping
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON rcpd_mapping (node);

CREATE TABLE rcpd_payers_for_aggregations (rcpd_aggregation VARCHAR(20), customer VARCHAR(30));
LOAD DATA LOCAL INFILE 'Y:\\Client Job Files\\Electricity Authority\\Transmission\\EAT004 - TPM options paper\\Board paper in Sep 2015\\Revised_RCPD_charge\\payers_for_aggregations.csv'
INTO TABLE rcpd_payers_for_aggregations
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON rcpd_payers_for_aggregations (rcpd_aggregation);

# checks

SELECT DISTINCT node FROM rcpd_mapping WHERE NOT node IN (SELECT node FROM load_nodes) AND NOT node IN (SELECT node FROM gg_nodes); # should be none
SELECT DISTINCT rcpd_aggregation FROM rcpd_payers_for_aggregations WHERE NOT rcpd_aggregation IN (SELECT rcpd_aggregation FROM rcpd_mapping); # should be none
SELECT DISTINCT rcpd_aggregation FROM rcpd_mapping WHERE NOT rcpd_aggregation IN (SELECT rcpd_aggregation FROM rcpd_payers_for_aggregations) AND NOT rcpd_aggregation IN (SELECT node FROM load_nodes) AND NOT rcpd_aggregation IN (SELECT node FROM gg_nodes); # should be none
SELECT DISTINCT customer FROM rcpd_payers_for_aggregations WHERE NOT (customer IN (SELECT DISTINCT customer FROM transmission_customers)); # should be none
SELECT DISTINCT node FROM load_nodes WHERE NOT (node IN (SELECT node FROM rcpd_mapping)); # may be some generation nodes, and nodes ending in e
