<commit_before>/*
eGo Data Processing result data versioning
Copy a version of mvies from model_draft to OEP schema

__copyright__ 	= "Europa-Universität Flensburg"
__license__ 	= "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__ 	= "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__ 	= "wolfbunke"
*/

-- Create MView with full dataset per scenario and conventional power plants for schema supply 

-- MView for Status Quo
DROP MATERIALIZED VIEW IF EXISTS  supply.ego_dp_conv_powerplant_sq_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_conv_powerplant_sq_mview AS
    SELECT *
    FROM supply.ego_dp_conv_powerplant
    WHERE scenario = 'Status Quo'
    AND (shutdown IS NULL or shutdown >= 2015)
    AND capacity > 0 
    AND preversion = 'v0.3.0'
    AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_conv_powerplant_sq_mview
    OWNER TO oeuser; 

-- MView for NEP 2035
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_conv_powerplant_nep2035_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_conv_powerplant_nep2035_mview AS
    SELECT *
    FROM  supply.ego_dp_conv_powerplant
    WHERE scenario = 'NEP 2035'
    AND   capacity > 0 
    AND   fuel not in ('hydro', 'run_of_river', 'reservoir')
    AND   (shutdown IS NULL or shutdown >= 2034)
    AND   preversion = 'v0.3.0'
    AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_conv_powerplant_nep2035_mview
    OWNER TO oeuser;

-- MView for eGo 100
DROP MATERIALIZED VIEW IF EXISTS  supply.ego_dp_conv_powerplant_ego100_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_conv_powerplant_ego100_mview AS
	SELECT 
	  version,
          preversion,
	  gid,
	  bnetza_id,
	  company,
	  name,
	  postcode,
	  city,
	  street,
	  state,
	  block,
	  commissioned_original,
	  commissioned,
	  retrofit,
	  shutdown,
	  status,
	  fuel,
	  technology,
	  type,
	  eeg,
	  chp,
	  capacity,
	  capacity_uba,
	  chp_capacity_uba,
	  efficiency_data,
	  efficiency_estimate,
	  network_node,
	  voltage,
	  network_operator,
	  name_uba,
	  lat,
	  lon,
	  'pumed storage for eGo 100'::text as comment,
	  geom,
	  voltage_level,
	  subst_id,
	  otg_id,
	  un_id,
	  la_id,
	  'eGo 100'::text as scenario,
	  'constantly'::text as flag,
	  nuts,
	  w_id
	FROM supply.ego_dp_conv_powerplant
	WHERE scenario in('NEP 2035')
	AND fuel = 'pumped_storage'
	AND capacity > 0
	AND (shutdown IS NULL or shutdown >= 2049)
	AND preversion = 'v0.3.0'
	AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_conv_powerplant_ego100_mview
    OWNER TO oeuser;

-- scenario log (project,version,io,schema_name,table_name,script_name,comment)
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_conv_powerplant_sq_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_conv_powerplant_nep2035_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_conv_powerplant_ego100_mview','ego_dp_versioning_mviews.sql','versioning');

-- Create View with full dataset per scenario and renewables power plants

-- MView for Status Quo  
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_res_powerplant_sq_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_res_powerplant_sq_mview AS
    SELECT *
    FROM supply.ego_dp_res_powerplant
    WHERE scenario =  'Status Quo'
    AND electrical_capacity > 0
    AND preversion = 'v0.3.0'
    AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_res_powerplant_sq_mview
    OWNER TO oeuser;

-- MView for NEP 2035
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_res_powerplant_nep2035_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_res_powerplant_nep2035_mview AS
	SELECT
	sub.*
	FROM  ( 
		SELECT DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
			WHERE id not in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE version = 'v0.4.0'
			Group BY id
			HAVING count(*) > 1
			Order by id)
		 AND scenario = 'Status Quo'
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
		 ) as sub
	UNION 
	SELECT
	sub2.*
	FROM  ( 
		SELECT  DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
		WHERE id in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE scenario in ('NEP 2035')
			AND version = 'v0.4.0'
			Group BY id
			Order by id)
		 AND scenario in ('NEP 2035')
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
	) sub2
	Order by id;

ALTER MATERIALIZED VIEW supply.ego_dp_res_powerplant_nep2035_mview
    OWNER TO oeuser;
    
-- MView for eGo 100
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_res_powerplant_ego100_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_res_powerplant_ego100_mview AS
	SELECT DISTINCT ON (id)
	sub.*
	FROM  ( 
		SELECT DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
			WHERE id not in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE version = 'v0.4.0'
			Group BY id
			HAVING count(*) > 1
			Order by id)
		 AND scenario = 'Status Quo'
		 AND preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 AND generation_type in ('solar','wind')
		 AND generation_subtype not in ('wind_offshore')
		 ORDER BY id	
		 ) as sub
	UNION 
	SELECT DISTINCT ON (id)
	sub2.*
	FROM  ( 
		SELECT  DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
		WHERE id in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE scenario in ('eGo 100')
			AND generation_type not in ('gas')
			AND version = 'v0.4.0'
			AND flag in ('commissioning', 'repowering')
			Group BY id
			Order by id)
		 AND scenario in ('eGo 100')
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
	) sub2
        UNION 
	SELECT DISTINCT ON (id)
	sub3.*
	FROM  ( 
		SELECT  DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
		WHERE id in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE scenario in ('NEP 2035')
			AND version = 'v0.4.0'
			AND generation_type not in ('biomass','gas','reservoir','run_of_river')
			AND flag in ('commissioning', 'repowering')
			AND generation_subtype not in ('wind_offshore')
			Group BY id
			Order by id)
		 AND scenario in ('NEP 2035')
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
	) sub3
	Order by id;

ALTER MATERIALIZED VIEW supply.ego_dp_res_powerplant_ego100_mview
    OWNER TO oeuser;

-- scenario log (project,version,io,schema_name,table_name,script_name,comment)
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_res_powerplant_sq_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_res_powerplant_nep2035_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_res_powerplant_ego100_mview','ego_dp_versioning_mviews.sql','versioning');
<commit_msg>remove w_id<commit_after>/*
eGo Data Processing result data versioning
Copy a version of mvies from model_draft to OEP schema

__copyright__ 	= "Europa-Universität Flensburg"
__license__ 	= "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__ 	= "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__ 	= "wolfbunke"
*/

-- Create MView with full dataset per scenario and conventional power plants for schema supply 

-- MView for Status Quo
DROP MATERIALIZED VIEW IF EXISTS  supply.ego_dp_conv_powerplant_sq_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_conv_powerplant_sq_mview AS
    SELECT *
    FROM supply.ego_dp_conv_powerplant
    WHERE scenario = 'Status Quo'
    AND (shutdown IS NULL or shutdown >= 2015)
    AND capacity > 0 
    AND preversion = 'v0.3.0'
    AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_conv_powerplant_sq_mview
    OWNER TO oeuser; 

-- MView for NEP 2035
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_conv_powerplant_nep2035_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_conv_powerplant_nep2035_mview AS
    SELECT *
    FROM  supply.ego_dp_conv_powerplant
    WHERE scenario = 'NEP 2035'
    AND   capacity > 0 
    AND   fuel not in ('hydro', 'run_of_river', 'reservoir')
    AND   (shutdown IS NULL or shutdown >= 2034)
    AND   preversion = 'v0.3.0'
    AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_conv_powerplant_nep2035_mview
    OWNER TO oeuser;

-- MView for eGo 100
DROP MATERIALIZED VIEW IF EXISTS  supply.ego_dp_conv_powerplant_ego100_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_conv_powerplant_ego100_mview AS
	SELECT 
	  version,
          preversion,
	  gid,
	  bnetza_id,
	  company,
	  name,
	  postcode,
	  city,
	  street,
	  state,
	  block,
	  commissioned_original,
	  commissioned,
	  retrofit,
	  shutdown,
	  status,
	  fuel,
	  technology,
	  type,
	  eeg,
	  chp,
	  capacity,
	  capacity_uba,
	  chp_capacity_uba,
	  efficiency_data,
	  efficiency_estimate,
	  network_node,
	  voltage,
	  network_operator,
	  name_uba,
	  lat,
	  lon,
	  'pumed storage for eGo 100'::text as comment,
	  geom,
	  voltage_level,
	  subst_id,
	  otg_id,
	  un_id,
	  la_id,
	  'eGo 100'::text as scenario,
	  'constantly'::text as flag,
	  nuts
	FROM supply.ego_dp_conv_powerplant
	WHERE scenario in('NEP 2035')
	AND fuel = 'pumped_storage'
	AND capacity > 0
	AND (shutdown IS NULL or shutdown >= 2049)
	AND preversion = 'v0.3.0'
	AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_conv_powerplant_ego100_mview
    OWNER TO oeuser;

-- scenario log (project,version,io,schema_name,table_name,script_name,comment)
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_conv_powerplant_sq_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_conv_powerplant_nep2035_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_conv_powerplant_ego100_mview','ego_dp_versioning_mviews.sql','versioning');

-- Create View with full dataset per scenario and renewables power plants

-- MView for Status Quo  
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_res_powerplant_sq_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_res_powerplant_sq_mview AS
    SELECT *
    FROM supply.ego_dp_res_powerplant
    WHERE scenario =  'Status Quo'
    AND electrical_capacity > 0
    AND preversion = 'v0.3.0'
    AND version = 'v0.4.0';

ALTER MATERIALIZED VIEW supply.ego_dp_res_powerplant_sq_mview
    OWNER TO oeuser;

-- MView for NEP 2035
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_res_powerplant_nep2035_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_res_powerplant_nep2035_mview AS
	SELECT
	sub.*
	FROM  ( 
		SELECT DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
			WHERE id not in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE version = 'v0.4.0'
			Group BY id
			HAVING count(*) > 1
			Order by id)
		 AND scenario = 'Status Quo'
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
		 ) as sub
	UNION 
	SELECT
	sub2.*
	FROM  ( 
		SELECT  DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
		WHERE id in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE scenario in ('NEP 2035')
			AND version = 'v0.4.0'
			Group BY id
			Order by id)
		 AND scenario in ('NEP 2035')
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
	) sub2
	Order by id;

ALTER MATERIALIZED VIEW supply.ego_dp_res_powerplant_nep2035_mview
    OWNER TO oeuser;
    
-- MView for eGo 100
DROP MATERIALIZED VIEW IF EXISTS supply.ego_dp_res_powerplant_ego100_mview CASCADE;
CREATE MATERIALIZED VIEW supply.ego_dp_res_powerplant_ego100_mview AS
	SELECT DISTINCT ON (id)
	sub.*
	FROM  ( 
		SELECT DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
			WHERE id not in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE version = 'v0.4.0'
			Group BY id
			HAVING count(*) > 1
			Order by id)
		 AND scenario = 'Status Quo'
		 AND preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 AND generation_type in ('solar','wind')
		 AND generation_subtype not in ('wind_offshore')
		 ORDER BY id	
		 ) as sub
	UNION 
	SELECT DISTINCT ON (id)
	sub2.*
	FROM  ( 
		SELECT  DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
		WHERE id in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE scenario in ('eGo 100')
			AND generation_type not in ('gas')
			AND version = 'v0.4.0'
			AND flag in ('commissioning', 'repowering')
			Group BY id
			Order by id)
		 AND scenario in ('eGo 100')
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
	) sub2
        UNION 
	SELECT DISTINCT ON (id)
	sub3.*
	FROM  ( 
		SELECT  DISTINCT ON (id)
		  *
		FROM
		  supply.ego_dp_res_powerplant
		WHERE id in (
			SELECT id
			FROM supply.ego_dp_res_powerplant
			WHERE scenario in ('NEP 2035')
			AND version = 'v0.4.0'
			AND generation_type not in ('biomass','gas','reservoir','run_of_river')
			AND flag in ('commissioning', 'repowering')
			AND generation_subtype not in ('wind_offshore')
			Group BY id
			Order by id)
		 AND scenario in ('NEP 2035')
		 And preversion = 'v0.3.0'
		 AND version = 'v0.4.0'
		 AND electrical_capacity > 0
		 ORDER BY id	
	) sub3
	Order by id;

ALTER MATERIALIZED VIEW supply.ego_dp_res_powerplant_ego100_mview
    OWNER TO oeuser;

-- scenario log (project,version,io,schema_name,table_name,script_name,comment)
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_res_powerplant_sq_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_res_powerplant_nep2035_mview','ego_dp_versioning_mviews.sql','versioning');
SELECT scenario_log('eGo_DP','v0.4.0','result','supply','ego_dp_res_powerplant_ego100_mview','ego_dp_versioning_mviews.sql','versioning');

{'fraction_non_alphanumeric': 0.08890303416083174, 'fraction_numerical': 0.031826861871419476, 'mean_word_length': 3.783491204330176, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 31, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '674073', 'n_tokens_mistral': 6025, 'n_tokens_neox': 5241, 'n_words': 1306}