### "Data for Excel graphs.sql"

# Carry out SQL queries to produce the data used in "Beneficiaries pay outputs for release.xlsx".
# These data can be pasted into the spreadsheet, with minimal rearrangement.

# Before you run this script, you should have run 'Prepare raw allocations.sql', 'Calculate beneficiaries pay charges.sql' and 'Calculate beneficiaries pay charges at nodal level - 3 years.sql'.
# Also, the directory "C:\TPM\Inputs" should contain the lookup tables "prefix to location for 3 year analysis.csv" and "Variants - GIT-based charging rates.csv".

/* Fig 5 */

SELECT 'gross' AS charge_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges;

SELECT 'net' AS charge_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_net_benefit_with_daily_capping_and_flooring ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges_on_net_benefit;

SELECT 'net_unfloored' AS charge_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_net_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges_on_net_benefit_unfloored;

/* Fig 6 */

SELECT 'halfhourly' AS capping_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges;

SELECT 'daily' AS capping_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges;

SELECT 'monthly' AS capping_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges;

SELECT '4monthly' AS capping_type,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_gross_benefit_with_4monthly_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges;

SELECT component, annual_revenue_requirement
FROM vspd.annual_revenue_requirements
WHERE approach='SPD' AND variant='4 month base approach';

/* Figs 7 8 9 */

CREATE TABLE vspd.tp_charges AS
SELECT component, record_date, trading_period, 
 SUM(charge_on_gross_benefit_with_daily_capping) AS charge_with_daily_capping,
 SUM(charge_on_gross_benefit_with_monthly_capping) AS charge_with_monthly_capping
FROM vspd.charges
WHERE approach='SPD' AND variant='4 month base approach' 
GROUP BY component, record_date, trading_period;

SELECT charge_with_daily_capping 
FROM vspd.tp_charges
WHERE component='Wairakei Ring'
ORDER BY charge_with_daily_capping DESC
LIMIT 50;

SELECT charge_with_monthly_capping 
FROM vspd.tp_charges
WHERE component='Wairakei Ring'
ORDER BY charge_with_monthly_capping DESC
LIMIT 50;

SELECT charge_with_daily_capping 
FROM vspd.tp_charges
WHERE component='Pole 3'
ORDER BY charge_with_daily_capping DESC
LIMIT 50;

SELECT charge_with_monthly_capping 
FROM vspd.tp_charges
WHERE component='Pole 3'
ORDER BY charge_with_monthly_capping DESC
LIMIT 50;

SELECT charge_with_daily_capping 
FROM vspd.tp_charges
WHERE component='NIGU'
ORDER BY charge_with_daily_capping DESC
LIMIT 50;

SELECT charge_with_monthly_capping 
FROM vspd.tp_charges
WHERE component='NIGU'
ORDER BY charge_with_monthly_capping DESC
LIMIT 50;

/* Fig 11 */

SELECT variant, component,
SUM(CASE WHEN island='N' AND charge_type='load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS ni_load,
SUM(CASE WHEN island='N' AND charge_type<>'load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS ni_generation,
SUM(CASE WHEN island='S' AND charge_type='load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS si_load,
SUM(CASE WHEN island='S' AND charge_type<>'load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS si_generation
FROM (SELECT approach, variant, component, island, charge_type, charge_on_gross_benefit_with_daily_capping FROM vspd.monthly_aggregated_charges 
       UNION ALL 
      SELECT 'GIT' AS approach, variant, component,  island, charge_type, charge_on_gross_benefit_with_daily_capping FROM vspd.monthly_aggregated_charges WHERE approach='SPD' AND (component='Wairakei Ring' OR component='BPE-HAY' OR component='Pole 2' OR component='Pole 3' OR component='LSI Renewables')) AS a
WHERE approach='SPD' AND (component="Pole 2" OR component="Pole 3" OR component="Wairakei Ring")
 AND (variant='4 month base approach' OR variant='4 month bidz 1' OR variant='4 month bidz 2' OR variant='4 month bidz 3')
GROUP BY variant, component
ORDER BY component, variant;

SELECT variant, component,
SUM(CASE WHEN island='N' AND charge_type='load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS ni_load,
SUM(CASE WHEN island='N' AND charge_type<>'load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS ni_generation,
SUM(CASE WHEN island='S' AND charge_type='load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS si_load,
SUM(CASE WHEN island='S' AND charge_type<>'load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END) AS si_generation
FROM (SELECT approach, variant, component, island, charge_type, charge_on_gross_benefit_with_monthly_capping FROM vspd.monthly_aggregated_charges 
       UNION ALL 
      SELECT 'GIT' AS approach, variant, component,  island, charge_type, charge_on_gross_benefit_with_monthly_capping FROM vspd.monthly_aggregated_charges WHERE approach='SPD' AND (component='Wairakei Ring' OR component='BPE-HAY' OR component='Pole 2' OR component='Pole 3' OR component='LSI Renewables')) AS a
WHERE approach='SPD' AND (component="Pole 2" OR component="Pole 3" OR component="Wairakei Ring")
 AND (variant='4 month base approach' OR variant='4 month bidz 1' OR variant='4 month bidz 2' OR variant='4 month bidz 3')
GROUP BY variant, component
ORDER BY component, variant;

/* Fig 14 */

CREATE TABLE vspd.temp_bidz4_charges AS
SELECT SUBSTRING(node,1,3) AS prefix, component, charge_type, charge_on_gross_benefit_with_daily_capping, charge_on_gross_benefit_with_monthly_capping
FROM vspd.charges
WHERE approach='SPD' AND variant='4 month bidz 4' AND component='Pole 2';

CREATE INDEX idx ON vspd.temp_bidz4_charges (prefix);

CREATE TABLE vspd.temp_base_charges AS
SELECT SUBSTRING(node,1,3) AS prefix, component, charge_type, charge_on_gross_benefit_with_daily_capping, charge_on_gross_benefit_with_monthly_capping
FROM vspd.charges
WHERE approach='SPD' AND variant='4 month base approach' AND component='Pole 2';

CREATE INDEX idx ON vspd.temp_base_charges (prefix);

SELECT component,
 SUM(CASE WHEN island='N' AND charge_type='load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_ni_load,
 SUM(CASE WHEN island='N' AND charge_type<>'load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_ni_gen,
 SUM(CASE WHEN island='S' AND charge_type='load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_si_load,
 SUM(CASE WHEN island='S' AND charge_type<>'load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_si_gen
FROM vspd.temp_bidz4_charges AS a
INNER JOIN vspd.prefix_to_region AS b
ON a.prefix=b.prefix
GROUP BY component;

SELECT component,
 SUM(CASE WHEN island='N' AND charge_type='load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_ni_load,
 SUM(CASE WHEN island='N' AND charge_type<>'load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_ni_gen,
 SUM(CASE WHEN island='S' AND charge_type='load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_si_load,
 SUM(CASE WHEN island='S' AND charge_type<>'load' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_si_gen
FROM vspd.temp_base_charges AS a
INNER JOIN vspd.prefix_to_region AS b
ON a.prefix=b.prefix
GROUP BY component;

SELECT component,
 SUM(CASE WHEN island='N' AND charge_type='load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_ni_load,
 SUM(CASE WHEN island='N' AND charge_type<>'load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_ni_gen,
 SUM(CASE WHEN island='S' AND charge_type='load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_si_load,
 SUM(CASE WHEN island='S' AND charge_type<>'load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_si_gen
FROM vspd.temp_bidz4_charges AS a
INNER JOIN vspd.prefix_to_region AS b
ON a.prefix=b.prefix
GROUP BY component;

SELECT component,
 SUM(CASE WHEN island='N' AND charge_type='load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_ni_load,
 SUM(CASE WHEN island='N' AND charge_type<>'load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_ni_gen,
 SUM(CASE WHEN island='S' AND charge_type='load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_si_load,
 SUM(CASE WHEN island='S' AND charge_type<>'load' THEN charge_on_gross_benefit_with_monthly_capping ELSE 0 END)/1e6 AS charge_si_gen
FROM vspd.temp_base_charges AS a
INNER JOIN vspd.prefix_to_region AS b
ON a.prefix=b.prefix
GROUP BY component;

/* Figs 15 16 */

CREATE TABLE vspd.prefix_to_location_for_3_year_analysis (prefix VARCHAR(3), uni INT, ni INT, above_wairakei_ring INT, waikato_and_bop INT, lsi INT);
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\prefix to location for 3 year analysis.csv'
INTO TABLE vspd.prefix_to_location_for_3_year_analysis
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

CREATE INDEX idx ON vspd.prefix_to_location_for_3_year_analysis (prefix);

CREATE TABLE vspd.monthly_summaries_3year AS
SELECT YEAR(record_date) AS record_year, MONTH(record_date) AS record_month, node,
 SUM(CASE WHEN component='NIGU' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_spd_nigu,
 SUM(CASE WHEN component='Wairakei Ring' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_spd_wairakei_ring,
 SUM(CASE WHEN component='Pole 3' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_spd_pole_3,
 SUM(CASE WHEN component='CNI-UNI' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_zonal3_cni_uni,
 SUM(CASE WHEN component='LSI-CSI' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END)/1e6 AS charge_zonal3_lsi_csi
FROM vspd.charges_3years
GROUP BY YEAR(record_date), MONTH(record_date), node;

CREATE TABLE vspd.monthly_locational_summaries_3year AS
SELECT record_year, record_month,
 SUM(CASE WHEN uni=1 AND LENGTH(node)=7 THEN charge_spd_nigu ELSE 0 END) AS charge_spd_nigu_uni_load,
 SUM(CASE WHEN uni=0 OR LENGTH(node)>7 THEN charge_spd_nigu ELSE 0 END) AS charge_spd_nigu_not_uni_load,
 SUM(CASE WHEN above_wairakei_ring=1 AND LENGTH(node)=7 THEN charge_spd_wairakei_ring ELSE 0 END) AS charge_spd_wairakei_ring_uni_load,
 SUM(CASE WHEN waikato_and_bop=1 AND LENGTH(node)>7 THEN charge_spd_wairakei_ring ELSE 0 END) AS charge_spd_wairakei_ring_waikato_and_bop_gen,
 SUM(CASE WHEN above_wairakei_ring=0 AND waikato_and_bop=0 THEN charge_spd_wairakei_ring ELSE 0 END) AS charge_spd_wairakei_ring_other,
 SUM(CASE WHEN ni=1 AND LENGTH(node)=7 THEN charge_spd_pole_3 ELSE 0 END) AS charge_spd_pole_3_ni_load,
 SUM(CASE WHEN ni=1 AND LENGTH(node)>7 THEN charge_spd_pole_3 ELSE 0 END) AS charge_spd_pole_3_ni_gen,
 SUM(CASE WHEN ni=0 AND LENGTH(node)=7 THEN charge_spd_pole_3 ELSE 0 END) AS charge_spd_pole_3_si_load,
 SUM(CASE WHEN ni=0 AND LENGTH(node)>7 THEN charge_spd_pole_3 ELSE 0 END) AS charge_spd_pole_3_si_gen,
 SUM(CASE WHEN lsi=1 AND LENGTH(node)>7 THEN charge_zonal3_lsi_csi ELSE 0 END) AS charge_zonal3_lsi_csi_lsi_gen,
 SUM(CASE WHEN lsi=1 AND LENGTH(node)=7 THEN charge_zonal3_lsi_csi ELSE 0 END) AS charge_zonal3_lsi_csi_lsi_load,
 SUM(CASE WHEN lsi=0 AND LENGTH(node)>7 THEN charge_zonal3_lsi_csi ELSE 0 END) AS charge_zonal3_lsi_csi_other_gen,
 SUM(CASE WHEN lsi=0 AND LENGTH(node)=7 THEN charge_zonal3_lsi_csi ELSE 0 END) AS charge_zonal3_lsi_csi_other_load,
 SUM(CASE WHEN uni=1 AND LENGTH(node)=7 THEN charge_zonal3_cni_uni ELSE 0 END) AS charge_zonal3_cni_uni_uni_load,
 SUM(CASE WHEN uni=0 OR LENGTH(node)>7 THEN charge_zonal3_cni_uni ELSE 0 END) AS charge_zonal3_cni_uni_not_uni_load
FROM vspd.monthly_summaries_3year AS a
INNER JOIN vspd.prefix_to_location_for_3_year_analysis AS b
ON SUBSTRING(node,1,3)=b.prefix
INNER JOIN vspd.prefix_to_region AS c
ON SUBSTRING(node,1,3)=c.prefix
GROUP BY record_year, record_month
;

SELECT * FROM vspd.monthly_locational_summaries_3year
ORDER BY record_year, record_month;

/* Fig 17 */

SELECT party, 
SUM(CASE WHEN variant='4 month base approach' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS base, 
SUM(CASE WHEN variant='4 month IR charge' THEN charge_on_gross_benefit_with_halfhourly_capping ELSE 0 END) AS with_IR_charge 
FROM vspd.monthly_aggregated_charges
WHERE approach='SPD' AND component='Pole 3'
GROUP BY party;

/* Fig 19 */

SELECT 
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NAaN' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_naan,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='NIGU' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_nigu,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='UNI Reactive' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_uni_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Wairakei Ring' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_wairakei_ring,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='BPE-HAY' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_bpe_hay,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 2' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_pole_2,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='Pole 3' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_pole_3,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='USI Reactive' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_usi_reactive,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Renewables' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_renewables,
SUM(CASE WHEN approach='SPD' AND variant='4 month base approach' AND component='LSI Reliability' THEN charge_on_gross_benefit_with_daily_capping ELSE 0 END) AS spd_lsi_reliability
FROM vspd.monthly_aggregated_charges;

/* Figs 23 24 */

CREATE TABLE vspd.temp_data_for_more_variants AS
SELECT 'gross' AS gross_or_net, approach, component, yearmonth, node, party, charge_type, island, charge_on_gross_benefit_with_daily_capping AS charge_with_daily_capping
 FROM vspd.monthly_aggregated_charges 
 WHERE (approach='GIT' AND variant='4 month base approach') OR (approach='SPD' AND variant='4 month base approach')
UNION ALL
 SELECT 'net' AS gross_or_net, approach, component, yearmonth, node, party, charge_type, island, charge_on_net_benefit_with_daily_capping_and_flooring AS charge_with_daily_capping
 FROM vspd.monthly_aggregated_charges_on_net_benefit WHERE (approach='GIT' AND variant='4 month base approach') OR (approach='SPD' AND variant='4 month base approach');

SELECT gross_or_net, approach, charge_type, island, SUM(charge_with_daily_capping)
FROM vspd.temp_data_for_more_variants
GROUP BY gross_or_net, approach, charge_type, island;

SELECT gross_or_net, 'SPD economic' AS approach, charge_type, island, SUM(charge_with_daily_capping)
FROM vspd.temp_data_for_more_variants
WHERE approach='SPD' AND NOT (
component='LSI Reliability' OR 
component='NAaN' OR 
component='NIGU' OR 
component='USI Reactive'
) GROUP BY gross_or_net, approach, charge_type, island;

CREATE TABLE vspd.temp_data_for_more_variants_by_prefix AS 
SELECT gross_or_net, approach, component, yearmonth, SUBSTRING(node,1,3) AS prefix, party, charge_type, island, SUM(charge_with_daily_capping) AS charge_with_daily_capping
FROM vspd.temp_data_for_more_variants
GROUP BY gross_or_net, approach, component, yearmonth, SUBSTRING(node,1,3), party, charge_type, island;

CREATE TABLE vspd.temp_spd_sums_monthly AS
SELECT component, yearmonth, 
 SUM(CASE WHEN gross_or_net='gross' THEN charge_with_daily_capping ELSE 0 END) AS gross, 
 SUM(CASE WHEN gross_or_net='net' THEN charge_with_daily_capping ELSE 0 END) AS net
FROM vspd.temp_data_for_more_variants
WHERE approach='SPD'
GROUP BY component, yearmonth;

CREATE TABLE vspd.temp_demand_totals_monthly AS
SELECT YEAR(record_date) AS record_year, MONTH(record_date) AS record_month, node, SUM(demand)/2000 AS gwh, SUM(generation)/2000 AS generation_gwh
FROM vspd.tpm_nodal_results
WHERE runtype='base7'
GROUP BY YEAR(record_date), MONTH(record_date), node;

CREATE TABLE vspd.temp_demand_totals_by_prefix AS 
SELECT SUBSTRING(node,1,3) AS prefix, SUM(gwh)*1000 AS mwh, SUM(generation_gwh)*1000 AS generation_mwh 
FROM vspd.temp_demand_totals_monthly 
GROUP BY SUBSTRING(node,1,3);

CREATE TABLE vspd.temp_git_after_spd_charging_rates (node VARCHAR(13), gross_jul DOUBLE, gross_aug DOUBLE, gross_sep DOUBLE, gross_oct DOUBLE, gross_constant DOUBLE, net_jul DOUBLE, net_aug DOUBLE, net_sep DOUBLE, net_oct DOUBLE, net_constant DOUBLE);
LOAD DATA LOCAL INFILE 'C:\\TPM\\Inputs\\Variants - GIT-based charging rates.csv'
 INTO TABLE vspd.temp_git_after_spd_charging_rates COLUMNS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 2 LINES;

CREATE INDEX node ON vspd.temp_git_after_spd_charging_rates (node);

CREATE TABLE vspd.temp_git_after_gross_spd_monthly_charges_varying_rate AS
 SELECT record_year, record_month, a.node, gwh, gwh*gross_jul*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=7 AND a.node=b.node
UNION ALL
 SELECT record_year, record_month, a.node, gwh, gwh*gross_aug*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=8 AND a.node=b.node
UNION ALL
 SELECT record_year, record_month, a.node, gwh, gwh*gross_sep*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=9 AND a.node=b.node
UNION ALL
 SELECT record_year, record_month, a.node, gwh, gwh*gross_oct*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=10 AND a.node=b.node;

CREATE TABLE vspd.temp_git_after_gross_spd_monthly_charges_constant_rate AS
 SELECT record_year, record_month, a.node, gwh, gwh*gross_constant*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.node=b.node;
 
CREATE TABLE vspd.temp_git_after_net_spd_monthly_charges_varying_rate AS
 SELECT record_year, record_month, a.node, gwh, gwh*net_jul*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=7 AND a.node=b.node
UNION ALL
 SELECT record_year, record_month, a.node, gwh, gwh*net_aug*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=8 AND a.node=b.node
UNION ALL
 SELECT record_year, record_month, a.node, gwh, gwh*net_sep*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=9 AND a.node=b.node
UNION ALL
 SELECT record_year, record_month, a.node, gwh, gwh*net_oct*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.record_month=10 AND a.node=b.node;

CREATE TABLE vspd.temp_git_after_net_spd_monthly_charges_constant_rate AS
 SELECT record_year, record_month, a.node, gwh, gwh*net_constant*1000/1e6 AS git_charge
 FROM vspd.temp_demand_totals_monthly AS a 
 INNER JOIN vspd.temp_git_after_spd_charging_rates AS b
 WHERE a.node=b.node; 
 
SELECT b.island, SUM(git_charge) 
FROM vspd.temp_git_after_gross_spd_monthly_charges_varying_rate AS a
INNER JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY b.island;

SELECT b.island, SUM(git_charge) 
FROM vspd.temp_git_after_gross_spd_monthly_charges_constant_rate AS a
INNER JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY b.island;

SELECT b.island, SUM(git_charge) 
FROM vspd.temp_git_after_net_spd_monthly_charges_varying_rate AS a
INNER JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY b.island;

SELECT b.island, SUM(git_charge) 
FROM vspd.temp_git_after_net_spd_monthly_charges_constant_rate AS a
INNER JOIN vspd.prefix_to_region AS b
ON SUBSTRING(a.node,1,3)=b.prefix
GROUP BY b.island;

/* Figs 25 26 */

SELECT m.prefix, m.record_month, SUM(charge_with_daily_capping)*1e6/MAX(mwh) AS dollars_per_mwh_spd_charge_gross
FROM (SELECT *, SUBSTRING(yearmonth,6,2) AS record_month FROM test.data_for_more_variants_by_prefix) AS m
INNER JOIN (SELECT SUBSTRING(node,1,3) AS prefix, record_month, SUM(gwh)*1000 AS mwh, SUM(generation_gwh)*1000 AS generation_mwh 
FROM test.demand_totals_monthly GROUP BY SUBSTRING(node,1,3), record_month) AS n
ON m.prefix=n.prefix AND m.record_month=n.record_month
WHERE approach='SPD' AND gross_or_net='gross' AND charge_type='load' AND m.prefix='KEN'
GROUP BY m.prefix, m.record_month;

SELECT gross_jul, gross_aug, gross_sep, gross_oct, gross_constant
FROM vspd.temp_git_after_spd_charging_rates
WHERE node='KEN0331';
