# Load deeper connection charges for investments that are subject to the deeper connection charge under both Applications A and B, 
#  which are calculated in 'Simplified_deeper_connection_working_-_assets_covered_in_both_Applications_A_and_B.xlsx'.

# Note we do not load rentals for these investments, as we do for assets that are only subject to Application A. We will approximate them later

USE tpm2;

DROP TABLE IF EXISTS deep_connection_charges_application_b;
CREATE TABLE deep_connection_charges_application_b (investment VARCHAR(30),customer VARCHAR(30),island CHAR(1),generation CHAR(1),total_charge_m FLOAT);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\deeper_connection\\Deeper_connection_charges_-_Applications_A_and_B.csv'
INTO TABLE deep_connection_charges_application_b
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON deep_connection_charges_application_b (customer);

# For consistency with the remainder of the analysis:
UPDATE deep_connection_charges_application_b SET customer='Pacific Aluminium' WHERE customer='NZAS';

# checks

SELECT customer FROM deep_connection_charges_application_b WHERE NOT (customer IN (SELECT customer FROM transmission_customers)); # only Pioneer
SELECT DISTINCT island FROM deep_connection_charges_application_b; # should be S and N
SELECT DISTINCT generation FROM deep_connection_charges_application_b; # should be Y and N

