# This script is used to produce the inputs for the calculation of charges under four transitional approaches.
# It applies to the Base Option under Application A.

# First run Application_A.sql.

USE tpm;

CREATE TABLE combination_by_customer_for_transition (NEW BOOLEAN, junk INT, charge VARCHAR(20), component VARCHAR(100), category VARCHAR(10), customer VARCHAR(30), charge_m DOUBLE);

# Deep connection 
INSERT combination_by_customer_for_transition
SELECT FALSE, 1, 'Deep connection', 'Deep connection', 'load', a.customer, charge_deep_connection_m_ni+charge_deep_connection_m_si-rental_m
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE NOT (a.customer IN (SELECT generator FROM offer_node_to_participant))
  UNION ALL
SELECT FALSE, 1, 'Deep connection', 'Deep connection', 'NI gen', a.customer, charge_deep_connection_m_ni+charge_deep_connection_m_si-rental_m
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE (a.customer='MRP' OR a.customer='NAP JV' OR a.customer='Todd' OR a.customer='Mokai JV')
  UNION ALL
SELECT FALSE, 1, 'Deep connection', 'Deep connection', 'NI gen', a.customer, charge_deep_connection_m_ni-rental_m*charge_deep_connection_m_ni/(charge_deep_connection_m_ni+charge_deep_connection_m_si)
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE (a.customer IN (SELECT generator FROM offer_node_to_participant)) AND NOT (a.customer='MRP' OR a.customer='NAP JV' OR a.customer='Todd' OR a.customer='Mokai JV')
  UNION ALL
SELECT FALSE, 1, 'Deep connection', 'Deep connection', 'SI gen', a.customer, charge_deep_connection_m_si-rental_m*charge_deep_connection_m_si/(charge_deep_connection_m_ni+charge_deep_connection_m_si)
FROM deep_connection_charges AS a
INNER JOIN deep_connection_rentals AS b
ON a.customer=b.customer
WHERE (a.customer IN (SELECT generator FROM offer_node_to_participant)) AND NOT (a.customer='MRP' OR a.customer='NAP JV' OR a.customer='Todd' OR a.customer='Mokai JV');

# Deep connection - post 2016 investments
INSERT combination_by_customer_for_transition
SELECT TRUE, 1, 'Deep connection', 'Deep connection', 'load', customer, SUM(total_charge_m)*(1-0.0485)
FROM deep_connection_charges_application_b 
WHERE generation='N'
GROUP BY customer
  UNION ALL
SELECT TRUE, 1, 'Deep connection', 'Deep connection', 'NI gen', customer, SUM(total_charge_m)*(1-0.0485)
FROM deep_connection_charges_application_b 
WHERE generation='Y' AND island='N'
GROUP BY customer
  UNION ALL
SELECT TRUE, 1, 'Deep connection', 'Deep connection', 'SI gen', customer, SUM(total_charge_m)*(1-0.0485)
FROM deep_connection_charges_application_b 
WHERE generation='Y' AND island='S'
GROUP BY customer
;

# HARD-CODED - LCE is 4.85% of revenue requirement
INSERT combination_by_customer_for_transition
SELECT TRUE, 1, 'AoB', CONCAT(investment," - ",charge_group), (CASE WHEN category='SI gen' OR category='NI gen' THEN category ELSE 'load' END), customer, charge_m*(1-0.0485) 
FROM aob_capacity_charges_by_customer
WHERE investment='OTB-HAY' OR investment='BPE-HAY';

INSERT combination_by_customer_for_transition
SELECT FALSE, 1, 'AoB', CONCAT(investment," - ",charge_group), (CASE WHEN category='SI gen' OR category='NI gen' THEN category ELSE 'load' END), customer, charge_m*(1-0.0485) 
FROM aob_capacity_charges_by_customer
WHERE NOT(investment='OTB-HAY' OR investment='BPE-HAY');

# HARD-CODED - some economic investments are partly or wholly deeper connection
DELETE FROM combination_by_customer_for_transition WHERE charge='AoB' AND SUBSTRING(component,1,7)='BRK-SFD'; # not modelled
DELETE FROM combination_by_customer_for_transition WHERE charge='AoB' AND SUBSTRING(component,1,14)='LSI Renewables'; # is deeper connection
UPDATE combination_by_customer_for_transition SET charge_m = charge_m/2 WHERE charge='AoB' AND SUBSTRING(component,1,13)='Wairakei Ring'; # is half deeper connection

# Copy the results of these queries into the 'Source data' worksheet of 'Capping_mechanisms.xlsx', in this order:

SELECT NEW, charge, customer, SUM(charge_m)/3 AS charge_m_per_year
FROM combination_by_customer_for_transition
GROUP BY NEW, charge, customer;

SELECT purchaser AS customer, amd/sum_load_capacity AS breakdown
FROM amd AS a
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c
 UNION ALL
SELECT customer, nominal_mva/sum_load_capacity AS breakdown
FROM icp_charge_allocation_by_customer AS a
INNER JOIN (SELECT SUM(load_capacity) AS sum_load_capacity FROM (SELECT purchaser AS customer, amd AS load_capacity FROM amd UNION ALL SELECT customer, nominal_mva AS load_capacity FROM icp_charge_allocation_by_customer) AS d) AS c;

SELECT a.customer, a.charge_m/SUM(b.charge_m) AS breakdown
FROM rcpd_charges_by_customer AS a
INNER JOIN rcpd_charges_by_customer AS b
GROUP BY a.customer;

SELECT a.party AS customer, a.charge_m/SUM(b.charge_m) AS breakdown
FROM hvdc_charge AS a
INNER JOIN hvdc_charge AS b
GROUP BY a.party;

SELECT a.customer, a.gwh*b.net_to_gross/3
FROM load_customer_volumes AS a
INNER JOIN load_customer_net_to_gross_approximate_conversion AS b
ON a.customer=b.customer;

SELECT b.generator, SUM((CASE WHEN demand<0 THEN -demand ELSE 0 END)/6e3) AS average_GWh
FROM o_raw_node_ir AS a
INNER JOIN injecting_load_node_to_participant AS b
ON a.node=b.injecting_load_node
WHERE runtype='baseTPM2'
GROUP BY b.generator;

SELECT b.generator, SUM(generation/6e3) AS average_GWh
FROM o_raw_node_ir AS a
INNER JOIN offer_node_to_participant AS b
ON a.node=b.offer_code
WHERE runtype='baseTPM2'
GROUP BY b.generator;

SELECT customer, SUM(charge_m) 
FROM combination_by_customer
WHERE option_no=5
GROUP BY customer;

SELECT customer, SUM(charge_m)
FROM combination_appB_by_customer
WHERE option_no=2
GROUP BY customer;
