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

USE tpm2;

CREATE TABLE aob_amounts_to_recover (investment VARCHAR(30), amount_m_per_year DOUBLE);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\AoB\\amounts_to_recover_under_area_of_benefit_charge.csv'
INTO TABLE aob_amounts_to_recover
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON aob_amounts_to_recover (investment);

CREATE TABLE aob_breakdown (investment VARCHAR(30), charge_group VARCHAR(70), proportion_to_recover DOUBLE);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\AoB\\breakdown_of_area_of_benefit_charge.csv'
INTO TABLE aob_breakdown
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON aob_breakdown (investment, charge_group);

CREATE TABLE aob_charge_group_definitions (node VARCHAR(15), charge_group VARCHAR(70), offtakes_at_load_nodes VARCHAR(1),injections_at_load_nodes VARCHAR(1),injections_at_generation_nodes VARCHAR(1));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\AoB\\charge_group_definitions.csv'
INTO TABLE aob_charge_group_definitions
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON aob_charge_group_definitions (node, charge_group);

CREATE TABLE aob_dc_to_charge_group (participant VARCHAR(20), charge_group VARCHAR(70));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\AoB\\DC_to_charge_group.csv'
INTO TABLE aob_dc_to_charge_group
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON aob_dc_to_charge_group (participant, charge_group);

# The following table contains nominal distributor capacities based on ICP counts.
#
# The workings are provided in 'ICP_count_calculations_-_by_charge_group.xlsx' - 
#  which carries out the following steps for each 'charge group' (group of distributors of interest):
#   - takes active ICP count data as of 2014
#   - multiplies distributor ICP counts by the nominal capacities shown below, to get a total nominal capacity for all mass-market customers in the charge group.
# For example, one charge group is 'all South Island distributors'.
#
#  Metering category code:  1    2    3     4     5  Unmetered
#  Nominal capacity (kVA)  20  100  700	 1750  2500         20

CREATE TABLE aob_icps_by_charge_group_and_customer (customer VARCHAR(30), charge_group VARCHAR(70), nominal_mva DOUBLE);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\AoB\\ICP_counts_by_charge_group_and_customer.csv'
INTO TABLE aob_icps_by_charge_group_and_customer
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON aob_icps_by_charge_group_and_customer (customer, charge_group);

CREATE TABLE aob_gen_charge_group_to_island (charge_group VARCHAR(70), island CHAR(1));
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\AoB\\generation_charge_group_to_island.csv'
INTO TABLE aob_gen_charge_group_to_island
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON aob_gen_charge_group_to_island (charge_group);

/* checks */

SELECT DISTINCT investment FROM aob_amounts_to_recover WHERE NOT (investment IN (SELECT investment FROM aob_breakdown)); # should be none
SELECT DISTINCT investment FROM aob_breakdown WHERE NOT (investment IN (SELECT investment FROM aob_amounts_to_recover)); # should be none
SELECT investment, SUM(proportion_to_recover) FROM aob_breakdown GROUP BY investment; # should be 1 for each
SELECT DISTINCT charge_group FROM aob_breakdown WHERE NOT (charge_group IN (SELECT charge_group FROM aob_charge_group_definitions)); # should be none
SELECT DISTINCT charge_group FROM aob_charge_group_definitions WHERE NOT (charge_group IN (SELECT charge_group FROM aob_breakdown)); # should be none
SELECT DISTINCT offtakes_at_load_nodes FROM aob_charge_group_definitions; # should only be N and Y
SELECT DISTINCT injections_at_load_nodes FROM aob_charge_group_definitions; # should only be N and Y
SELECT DISTINCT injections_at_generation_nodes FROM aob_charge_group_definitions; # should only be N and Y
SELECT DISTINCT node FROM aob_charge_group_definitions WHERE NOT (node IN (SELECT node FROM gg_nodes)) AND NOT (node IN (SELECT node FROM load_nodes)); # should be none
SELECT DISTINCT charge_group FROM aob_dc_to_charge_group WHERE NOT (charge_group IN (SELECT charge_group FROM aob_charge_group_definitions)); # should be none
SELECT DISTINCT participant FROM aob_dc_to_charge_group WHERE NOT (participant IN (SELECT DISTINCT participant FROM market_participants)); # should be none
SELECT DISTINCT customer FROM aob_icps_by_charge_group_and_customer WHERE NOT (customer IN (SELECT DISTINCT customer FROM transmission_customers)); # should be none
SELECT DISTINCT charge_group FROM aob_icps_by_charge_group_and_customer WHERE NOT (charge_group IN (SELECT charge_group FROM aob_charge_group_definitions)); # should be none
SELECT DISTINCT charge_group FROM aob_gen_charge_group_to_island WHERE NOT (charge_group IN (SELECT charge_group FROM aob_charge_group_definitions)); # should be none
SELECT DISTINCT island FROM aob_gen_charge_group_to_island; # should only be 'N' and 'S'
