 	'DE',
 	cntr_id
 FROM model_draft.ego_grid_hv_electrical_neighbours_bus 
-WHERE central_bus = FALSE AND current_type = 'AC';
-	
+WHERE central_bus = FALSE AND current_type = 'AC' AND cntr_id != 'DE' ;
 
 UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
-SET cables = 6;
-
-		
-INSERT INTO model_draft.ego_grid_hv_electrical_neighbours_line (line_id, cntr_id_1, cntr_id_2, v_nom, cables)
+SET bus0 = (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus b WHERE a.cntr_id_1 = b.cntr_id AND a.v_nom = b.v_nom AND central_bus = TRUE);
+
+	
+--- Insert lines between electrical neigbours in respect to entso-e	
+INSERT INTO model_draft.ego_grid_hv_electrical_neighbours_line (line_id, cntr_id_1, cntr_id_2, v_nom, cables, s_nom)
 SELECT nextval('model_draft.ego_grid_hv_electrical_neighbours_line_id'), 
 	country1 , 
 	country2, 
-	voltage,
-	3*circiuts 
+	(CASE 	WHEN voltage = 110 THEN 110
+		WHEN voltage IN (150, 132, 220) THEN 220
+		WHEN voltage IN (300, 380) THEN 380
+		END),
+	3*circiuts,
+	(CASE 	WHEN voltage = 110 THEN circiuts * 260
+		WHEN voltage IN (150, 132, 220) THEN circiuts * 520
+		WHEN voltage IN (300, 380) THEN circiuts * 1790
+		END)
 FROM model_draft.ego_grid_pp_entsoe_line 
-WHERE country1 != country2 AND country1 IN (SELECT cntr_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus) AND country2 IN (SELECT cntr_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus) AND dc = false AND under_construction = false;
-
-
+WHERE country1 != country2 AND country1 IN (SELECT cntr_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus) AND country2 IN (SELECT cntr_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus) AND dc = false AND under_construction = false AND country1 != 'DE' AND country2 != 'DE';
+
+	
+--- Set cables and s_nom of lines in foregin countrys to values of border crossing lines
+UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
+SET	cables = (SELECT  sum(b.cables) FROM model_draft.ego_grid_pf_hv_line b  WHERE b.scn_name = 'Status Quo' AND b.bus0 = a.bus1 
+	AND b.bus0 NOT IN (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE central_bus = TRUE)),
+	s_nom = (SELECT  sum(b.s_nom) FROM model_draft.ego_grid_pf_hv_line b WHERE b.scn_name = 'Status Quo' AND b.bus0 = a.bus1
+	AND b.bus0 NOT IN (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE central_bus = TRUE))
+WHERE a.cntr_id_2 = 'DE';
+
+UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
+SET	cables = (SELECT  sum(b.cables) FROM model_draft.ego_grid_pf_hv_line b  WHERE b.scn_name = 'Status Quo' AND b.bus1 = a.bus1 
+	AND (SELECT DISTINCT ON (geom) geom FROM model_draft.ego_grid_pf_hv_bus WHERE bus_id = b.bus0) NOT IN (SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE central_bus = TRUE)),
+	s_nom = (SELECT  sum(b.s_nom) FROM model_draft.ego_grid_pf_hv_line b WHERE b.scn_name = 'Status Quo' AND b.bus1 = a.bus1
+	AND (SELECT DISTINCT ON (geom) geom FROM model_draft.ego_grid_pf_hv_bus WHERE bus_id = b.bus0) NOT IN (SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE central_bus = TRUE))
+WHERE a.cntr_id_2 = 'DE' AND cables is null ;
+
+--- Set bus0 to central bus of country 1			
+UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
+SET bus0 = (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus b WHERE a.cntr_id_1 = b.cntr_id AND a.v_nom = b.v_nom AND central_bus = TRUE);
+
+--- Lines between electrical neighours: Set bus1 to central bus of country 2
+UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
+SET bus1 = (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus b WHERE a.cntr_id_2 = b.cntr_id AND a.v_nom = b.v_nom AND central_bus = TRUE) 
+WHERE a.cntr_id_2 != 'DE';
+
+--- Set topo, frequency, length and geom
 UPDATE model_draft.ego_grid_hv_electrical_neighbours_line
-	SET v_nom = (CASE v_nom WHEN 132 THEN 220
-				WHEN 150 THEN 220
-				WHEN 300 THEN 380
-				ELSE v_nom END);
-
-			
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
-	SET bus0 = (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus b WHERE a.cntr_id_1 = b.cntr_id AND a.v_nom = b.v_nom AND central_bus = TRUE);
-
-
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
-	SET bus1 = (CASE WHEN bus1 IS NULL THEN (SELECT bus_id FROM model_draft.ego_grid_hv_electrical_neighbours_bus b 
-		WHERE a.cntr_id_2 = b.cntr_id AND a.v_nom = b.v_nom AND central_bus = TRUE) ELSE bus1 END);
-  
+SET 	topo = (SELECT ST_MakeLine((SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus0), (SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus1))),
+	frequency = 50,
+	length = (SELECT  ST_Length((SELECT ST_MakeLine((SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus0), (SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus1))), true))/1000,
+	geom = (SELECT (ST_Multi(ST_MakeLine((SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus0), (SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus1)))));
+
+--- Set electrical parameters to standard-values of transmission lines
 UPDATE model_draft.ego_grid_hv_electrical_neighbours_line
-	SET 	topo = (SELECT ST_MakeLine((SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus0), (SELECT geom FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE bus_id = bus1))),
-		frequency = 50;
-
-
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line
-	SET length = (SELECT  ST_Length(topo, true))/1000;
-
-  
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line
-
-	SET geom  = (SELECT  ST_Multi(topo));
-
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line a
-	SET cables = (SELECT SUM(cables) FROM  model_draft.ego_grid_hv_electrical_neighbours_line b  WHERE a.geom = b.geom AND a.v_nom = b.v_nom) ;
-
-
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line 
-	SET cables = (CASE 	WHEN geom = ('0105000020E6100000010000000102000000020000008219DE771A512C40E266B10F27CB474032A946544D292A40422619390B214840') AND v_nom = 220 THEN 9
-				WHEN geom = ('0105000020E610000001000000010200000002000000CC34F5A862612040F35A1E0B1462474053EC681CEA172040FCA65599CEC64740') AND v_nom = 380 THEN 3
-				WHEN geom = ('0105000020E610000001000000010200000002000000CC34F5A862612040F35A1E0B146247406642DD51AD172040829D51A9C8C64740') AND v_nom = 380 THEN 9
-				ELSE cables
-			END);
-
-	
-DELETE FROM model_draft.ego_grid_hv_electrical_neighbours_line a USING model_draft.ego_grid_hv_electrical_neighbours_line b
-  WHERE a.geom = b.geom AND a.v_nom = b.v_nom AND a.ctid < b.ctid;
-
-
-UPDATE model_draft.ego_grid_hv_electrical_neighbours_line
-	SET 	x = 	(CASE 	WHEN v_nom = 110 THEN 0.31415 *1.2 / (cables/3) * length
+SET 	x = 	(CASE 	WHEN v_nom = 110 THEN 0.31415 *1.2 / (cables/3) * length
 			WHEN v_nom = 220 THEN 0.31415  /(cables/3) * length
 			WHEN v_nom = 380 THEN 0.31415 * 0.8 /(cables/3) * length
 			END),
 		
-		r = 	(CASE 	WHEN v_nom = 110 THEN 0.109 / (cables/3) * length
-				WHEN v_nom = 220 THEN 0.109 /(cables/3) * length
+	r = 	(CASE 	WHEN v_nom = 110 THEN 0.109 / (cables/3) * length
+			WHEN v_nom = 220 THEN 0.109 /(cables/3) * length
 			WHEN v_nom = 380 THEN 0.028 /(cables/3) * length
-			END),
-
-		s_nom = (CASE 	WHEN v_nom = 110 THEN 260 * (cables/3) 
-			WHEN v_nom = 220 THEN 520 * (cables/3) 
-			WHEN v_nom = 380 THEN 1790 *(cables/3) 
 			END);
 
-
 DELETE FROM model_draft.ego_grid_hv_electrical_neighbours_line WHERE cables IS NULL;
-DELETE FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE ((bus_id NOT IN (SELECT bus0 FROM model_draft.ego_grid_hv_electrical_neighbours_line)) AND (bus_id NOT IN (SELECT bus1 FROM model_draft.ego_grid_hv_electrical_neighbours_line)));
 
 			
 DROP TABLE IF EXISTS model_draft.ego_grid_hv_electrical_neighbours_transformer CASCADE;

);

ALTER TABLE model_draft.ego_grid_hv_electrical_neighbours_line
  OWNER TO oeuser;


INSERT INTO model_draft.ego_grid_hv_electrical_neighbours_line (line_id, bus1, v_nom, cntr_id_2, cntr_id_1)




DELETE FROM model_draft.ego_grid_hv_electrical_neighbours_bus WHERE geom IS NULL OR v_nom = 750;

UPDATE model_draft.ego_grid_hv_electrical_neighbours_bus
	SET v_nom = (CASE v_nom WHEN 132 THEN 220
