# Load deeper connection charges and rentals for investments that are subject to the deeper connection charge under Application A only, 
#  which are calculated in 'Deeper_connection_working_-_assets_covered_in_Application_A_only.xlsx'.

USE tpm2;

DROP TABLE IF EXISTS deep_connection_charges;
CREATE TABLE deep_connection_charges (customer VARCHAR(30),charge_deep_connection_m_ni FLOAT,charge_deep_connection_m_si FLOAT);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\deeper_connection\\Deeper_connection_charges_-_Application_A_only.csv'
INTO TABLE deep_connection_charges
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON deep_connection_charges (customer);

DROP TABLE IF EXISTS deep_connection_rentals;
CREATE TABLE deep_connection_rentals (customer VARCHAR(30), rental_m FLOAT);
LOAD DATA LOCAL INFILE 'c:\\mydir\\TPM\\deeper_connection\\Deeper_connection_rentals_-_Application_A_only.csv'
INTO TABLE deep_connection_rentals
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE INDEX idx ON deep_connection_rentals (customer);

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

# checks

SELECT customer FROM deep_connection_charges WHERE NOT (customer IN (SELECT customer FROM transmission_customers)); # only Pioneer
SELECT customer FROM deep_connection_rentals WHERE NOT (customer IN (SELECT customer FROM transmission_customers)); # only Pioneer
SELECT customer FROM transmission_customers WHERE NOT (customer IN (SELECT customer FROM deep_connection_charges)); # should be none
SELECT customer FROM transmission_customers WHERE NOT (customer IN (SELECT customer FROM deep_connection_rentals)); # should be none
