# This script calculates area-of-benefit charges. 

# Run 'load_area_of_benefit_inputs.sql' first.

# Note that subsequent scripts:
# - adjust amounts to recover downwards, for investments whose costs are partly or wholly recovered through a different charge (such as the deeper connection charge or SPD charge).
# - adjust AoB charges downwards for LCE.

USE tpm2;

CREATE TABLE aob_working_1 AS
SELECT charge_group, generator, SUM(volume_mwh)/1000 AS generation_gwh FROM ( 
  SELECT a.charge_group, c.generator, "generation    " AS category, SUM(b.generation)/2 AS volume_mwh
  FROM aob_charge_group_definitions AS a
  INNER JOIN o_raw_node_ir AS b
  ON a.node=b.node
  INNER JOIN offer_node_to_participant AS c
  ON b.node=c.offer_code
  WHERE runtype='baseTPM2' AND a.injections_at_generation_nodes='Y' AND b.generation>0
  GROUP BY a.charge_group, c.generator
UNION ALL
  SELECT a.charge_group, c.generator, "load_injection" AS category, SUM(-b.net_demand)/2 AS volume_mwh
  FROM aob_charge_group_definitions AS a
  INNER JOIN o_raw_node_ir AS b
  ON a.node=b.node
  INNER JOIN injecting_load_node_to_participant AS c
  ON b.node=c.injecting_load_node
  WHERE runtype='baseTPM2' AND a.injections_at_load_nodes='Y' AND b.net_demand<0
  GROUP BY a.charge_group, c.generator
) AS d
GROUP BY charge_group, generator;  

CREATE TABLE aob_working_1b AS
SELECT charge_group, SUM(generation_gwh) AS generation_gwh 
FROM aob_working_1
GROUP BY charge_group;

CREATE TABLE aob_working_2 AS
SELECT charge_group, customer, category, SUM(capacity) AS capacity FROM
    (SELECT a.charge_group, participant AS customer, 'DC        ' AS category, SUM(amd) AS capacity
    FROM aob_dc_to_charge_group AS a
    INNER JOIN amd AS b
    ON a.participant=b.purchaser
    GROUP BY charge_group, a.participant
      UNION ALL
    SELECT charge_group, customer, 'MM        ' AS category, nominal_mva AS capacity
    FROM aob_icps_by_charge_group_and_customer
      ) AS c
GROUP BY charge_group, customer;

CREATE TABLE aob_working_2b AS
SELECT charge_group, SUM(capacity) AS capacity 
FROM aob_working_2
GROUP BY charge_group;

CREATE TABLE aob_capacity_charges_by_customer AS
SELECT a.investment, a.charge_group, category, customer, 3*amount_m_per_year*proportion_to_recover*b.capacity/c.capacity AS charge_m
FROM aob_breakdown AS a
INNER JOIN aob_working_2 AS b
ON a.charge_group=b.charge_group
INNER JOIN aob_working_2b AS c
ON a.charge_group=c.charge_group
INNER JOIN aob_amounts_to_recover AS d
ON a.investment=d.investment
 UNION ALL
SELECT a.investment, a.charge_group, CASE WHEN island='S' THEN 'SI gen' ELSE 'NI gen' END, generator, 3*amount_m_per_year*proportion_to_recover*b.generation_gwh/c.generation_gwh AS charge_m
FROM aob_breakdown AS a
INNER JOIN aob_working_1 AS b
ON a.charge_group=b.charge_group
INNER JOIN aob_working_1b AS c
ON a.charge_group=c.charge_group
INNER JOIN aob_amounts_to_recover AS d
ON a.investment=d.investment 
INNER JOIN aob_gen_charge_group_to_island AS e
ON a.charge_group=e.charge_group
;
