create schema matchning;
--drop schema matchning cascade;

/* create and update table matchning.categories_generic */
create table matchning.categories_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
)
-- drop table matchning.categories_generic

INSERT INTO matchning.categories_generic (namn_sv)
select q.name
from
(
select name, count(1) as number
from skfab.damage_categories c
group by 1
order by 2 desc 
) q
where number > 2 and name <> '-odefinierad-'

update matchning.categories_generic set namn_no = 'Uhell', namn_en ='Accident'
where namn_sv = 'Olyckshändelse'
update matchning.categories_generic set namn_no = 'Ansvar', namn_en ='Liability'
where namn_sv = 'Ansvar'
update matchning.categories_generic set namn_no = 'Egendom', namn_en ='Property'
where namn_sv = 'Egendom'
update matchning.categories_generic set namn_no = 'Med overlegg', namn_en ='Intent'
where namn_sv = 'Uppsåt'

--select * from matchning.categories_generic

/* create and update table matchning.categories_crossref */
create table matchning.categories_crossref
as
(
select damage_category_id, name from skfab.damage_categories dc 
)
alter table matchning.categories_crossref add column generic_category_id integer;

--Uppdatera först de kategorier som matchar med de generiska på svenska och norska
update matchning.categories_crossref set generic_category_id = id from categories_generic 
where matchning.categories_crossref.name in (categories_generic.namn_sv, categories_generic.namn_no)
-- Fortsätt sedan att uppdatera de kategorier som inte är matchade
update matchning.categories_crossref set generic_category_id = 3 --egendom
where matchning.categories_crossref.name in ('Byggfel, bristande underhåll', 'Byggfel/bristande underhåll', 'Byggfel/Bristande underhåll', 'Byggnadsfel' )
update matchning.categories_crossref set generic_category_id = 4 --Olyckshändelse
where matchning.categories_crossref.name in ('Olycka','Olycka (tillbud)' )
update matchning.categories_crossref set generic_category_id = 2 --uppsåt
where matchning.categories_crossref.name in ('SD Utemiljö','SK Skadegörelse','Fordonsskador','Oaktsamhet' )
update matchning.categories_crossref set generic_category_id = 1 --ansvar
where matchning.categories_crossref.name in ('.','-odefinierad-','Tillbud','Övrigt','Snöröjning','Väderpåverkan' )

select damage_category_id,name, g.namn_sv 
from matchning.categories_crossref
join categories_generic g on generic_category_id = g.id

/* create and update table matchning.categories_generic */
create table matchning.types_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
)
-- drop table matchning.types_generic

INSERT INTO matchning.types_generic (namn_sv)
select q.name
from
(
select distinct name, count(1) as number
from skfab.damage_types c
group by 1
order by 2 desc
) q
where number > 3 and name not in 
('-odefinierad-', 'Annet', 'Hærverk','Inventarier odyl','Ruteknusing',
'Tagging','Vann','Vattenskada','Övriga skador','Skadegörelse')

update matchning.types_generic set namn_no = 'Ansvar', namn_en ='Liability'
where namn_sv = 'Ansvar';
update matchning.types_generic set namn_no = 'Avløp', namn_en ='Drainage'
where namn_sv = 'Avlopp';
update matchning.types_generic set namn_no = 'Brann', namn_en ='Fire'
where namn_sv = 'Brand';
update matchning.types_generic set namn_no = 'Eiendom', namn_en ='Property'
where namn_sv = 'Egendom';
update matchning.types_generic set namn_no = 'Glassknuser', namn_en ='Glass breaker'
where namn_sv = 'Glaskross';
update matchning.types_generic set namn_no = 'Innbrudd', namn_en ='Burglary'
where namn_sv = 'Inbrott';
update matchning.types_generic set namn_no = 'Doodle', namn_en ='Doodle'
where namn_sv = 'Klotter';
update matchning.types_generic set namn_no = 'Maskin', namn_en ='Machine'
where namn_sv = 'Maskin';
update matchning.types_generic set namn_no = 'Skade', namn_en ='Damage'
where namn_sv = 'Skada';
update matchning.types_generic set namn_no = 'Tyveri', namn_en ='Theft'
where namn_sv = 'Stöld';
update matchning.types_generic set namn_no = 'Vann', namn_en ='Water'
where namn_sv = 'Vatten';
update matchning.types_generic set namn_no = 'Annen', namn_en ='Other'
where namn_sv = 'Övrigt';
update matchning.types_generic set namn_no = 'Personlig', namn_en ='Personal', namn_sv='Person'
where id = 8 ;
update matchning.types_generic set namn_no = 'Forbrytelse', namn_en ='Crime', namn_sv='Brottslihet'
where id = 5 ;

/* create and update table matchning.categories_types
 * drop table matchning.types_crossref */
create table matchning.types_crossref
as
(
select damage_type_id, name from skfab.damage_types dc 
);
alter table matchning.types_crossref add column generic_type_id integer;
--Uppdatera de vanligaste typerna
update matchning.types_crossref  set generic_type_id = id from types_generic  
where matchning.types_crossref.name in (types_generic.namn_sv, types_generic.namn_no);
--Uppdarera de udda typerna
update matchning.types_crossref set generic_type_id = 1
where matchning.types_crossref.name in ('.','Övriga skador','Annet','Annan ansvarsskada','-odefinierad-','Annan','Storm','Tillbud' );
update matchning.types_crossref set generic_type_id = 2
where matchning.types_crossref.name in ('Brandtillbud');
update matchning.types_crossref set generic_type_id = 3 --Vatten
where matchning.types_crossref.name in ('Vattenskada','Fukt/mögel','Frysskada','Översvämning','Vattenskada (i egna fastigheter)' );
update matchning.types_crossref set generic_type_id = 4 --Glaskross
where matchning.types_crossref.name in ('Glas','Ruteknusing' );
update matchning.types_crossref set generic_type_id = 5 --brott
where matchning.types_crossref.name in ('Rån','Inbrottsskada','Ofredande','Hot','Inbrottsskada','Sabotage','Stöld','Innbrudd','Tyveri','Inbrott' );
update matchning.types_crossref set generic_type_id = 6 --Skada
where matchning.types_crossref.name in ('Hærverk','Skadegörelse','Annan skadegörelse','Sakskada' );
update matchning.types_crossref set generic_type_id = 7 --Maskin
where matchning.types_crossref.name in ('Motor','Fordon','Privata fordone','Maskiner','Tunga fordon','Maskinskada','Privata fordon','Fordonsskada',
'Kommunens tjänstebilar','Motor','Transports fordon','Transport','Plogskada','Vägskada','Plogskada','Transports fordon' );
update matchning.types_crossref set generic_type_id = 8 --Person
where matchning.types_crossref.name in ('Tandskada','Kroppskada','Patient','Nackskada','Person','Halkolycka','Personskada','Patientskada','Sårskada','Fallskada','Personskade','Sekundär drunkning','Olyckshändelse','Stroke' );
update matchning.types_crossref set generic_type_id = 9 --Ansvar
where matchning.types_crossref.name in ('Myndighetsskada','Förmögenhetsskada','Diskriminering','KÖV','Oaktsamhet','Produkt','Kärl',
'Skadeståndsansvar','Påkörning','Slarv','Okänd','Generellt ansvar','Explosion','Allrisk','IT-incident' );
update matchning.types_crossref set generic_type_id = 10 --klotter
where matchning.types_crossref.name in ('Tagging');
update matchning.types_crossref set generic_type_id = 11 --egendom
where matchning.types_crossref.name in ('Inventarier odyl','Mekanisk/elektrisk','Mögel/Fukt','Gatu-/Parkbelysning','Bristande underhåll',
'Byggfel','Entreprenad','Mögel, fukt','Skadad egendom','Snölast','Miljö/byggherreansvar','El','Praktik','LOU-skada','Mögel/fukt');
update matchning.types_crossref set generic_type_id = 12 --Avlopp / miljö
where matchning.types_crossref.name in ('VA-skada','Miljöskada','Nedebördsskada','Olja','Naturskade','VA-skada','VA','Miljöutredning');

select damage_type_id,name, g.namn_sv 
from matchning.types_crossref
join types_generic g on generic_type_id = g.id

create table matchning.operation_types_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
)
-- drop table matchning.operation_types_generic
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Skola/förskola','Skole/førskole','School/preschool') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Omsorg/stöd','Omsorg/støtte','Care/support') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Bygga/bo/miljö','Bygg/bo/miljø','Building/living/environment') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Trafik/gator','Trafikk/gater','Traffic/streets') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Uppleva/göra','Oppleve/gjøre','Experience/do') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Kommun/politik','Kommune/politikk','Municipality/politics') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Jobb/företagande','Jobb/entreprenørskap','Job/entrepreneurship') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Trygghet','Sikkerhet','Security') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Vatten/energi','Vann/energi','Water/energy') ;
--select * from operation_types_generic

create table matchning.operation_types_crossref
as
(
select operation_type_id, name from skfab.operation_types dc 
);
alter table matchning.operation_types_crossref add column generic_operation_types_id integer;
--select * from matchning.operation_types_crossref

update matchning.operation_types_crossref set generic_operation_types_id = 1 --Skola/förskola
where matchning.operation_types_crossref.name in ('Grundskola/särskola','Gymnasieskola','Grundskola','Gymnasium','Grundskola','Högalid rektorsområde','Berga rektorsområde','Luossa rektorsområde',
'Triangel rektorsområde','Bolag rektorsområde','Raket rektorsområde','Lombolo rektorsområde','Tuolla/Jukkas rektorsområde','Vittangi rektorsområde','Karesuando rektorsområde','Elevhälsan',
'Förskola/barnomsorg','Gymnasieskola','Förskola','Särskola','Vuxenskola','Gymnasieskolan','Barnomsorg/förskola','Grundskola/särskola','Gymnasieskola/Komvux','Lokaler','Vuxenutbildningen',
'Skoler','Barnehager','Själevad skolområde','Grundskola/särskola 7-9','Grundskola/särskola F-6','Gymnasieskola/-särskola','Vuxenutbildning');

update matchning.operation_types_crossref set generic_operation_types_id = 2 --Omsorg/stöd
where matchning.operation_types_crossref.name in ('Särskilt boende','Omsorg Administrativ verksamhet m.m.','Måltid','Handikappomsorg','Måltidsverksamhet','Omsorg/äldreomsorg','Socialtjänst',
'Familjehem','Hemvård','LSS-boende','Socialt boende','Vårdboende','Äldreboende','Trygdeboliger','Socialförvaltningen','Äldreomsorg','Placeringsenheten','Stöd och omsorg','Social utredning',
'Socialt stöd','Individ- och familjeomsorg','Ungdomsavdelningen','Handikappomsorg','Äldreomsorg Hemtjänst','LSS verksamhet','IFO boenden','Ordinärt boende FO','Vård- och omsorgsboende,
 korttidsvistelse','LSS boende');

update matchning.operation_types_crossref set generic_operation_types_id = 3 --Bygga/bo/miljö
where matchning.operation_types_crossref.name in ('Lokal','Magasin','Kaj','Pitebo','Pireva','Bygg- och miljö','Ordinärt boende','Fysisk planering','Övikshem AB','Fastighetsförvaltning/kommunal byggnad',
'Fastighetsförvaltning','Kultur- och fritidsverksamhet','Drift och underhåll','Projektenhet','Stadsmiljö','Bostadsrätt','Fastighet','Garage','Hyreslägenhet','Lantmäteri','Saneringsfastigheter',
'Parkeringar','Parkeringshus','Hyreshus','Boendemoduler ','Fastighetsförvaltning','Hamnverksamhet','Renhållning/materialåtervinning','Sotning','Stadsplanering','Materialåtervinning/Deponi',
'Renhållning/Underhåll','Anläggningar','Mark och Exploatering','Boliger','Helsebygg','Administrasjonsbygg','Kulturbygg','Idrettsanlegg','Markaeiendommer','Avfall & Återvinning','MEX','Fastigheter');
update matchning.operation_types_crossref set generic_operation_types_id = 4 --Trafik/gator
where matchning.operation_types_crossref.name in ('Järnväg','Vägar','Farled','Öviks Hamn & Logistik','Renhållning','Tekniska verksamheter','Teknik och gator','Park och grönområden','Flygplats','Fordonsverkstad'
,'Gata/Park','Övik Airport AB','Trafik och Park','Trafik och Parker','Gata/park','Mark- och trafik avdelningen' );

update matchning.operation_types_crossref set generic_operation_types_id = 5 --Uppleva/göra
where matchning.operation_types_crossref.name in ('Serveringshytter','Kultur och Fritid','Kulturskolecentrum','Paradiset','kiruna kommunpartner','Fritidsavdelningen','Fritid och sport','Kultur','Bibliotek',
'Bad','Idrott och motion','Kulturliv','Skärgården','Kultur och fritidsverksamhet','Djur- och nöjespark','Kulturskola' );

update matchning.operation_types_crossref set generic_operation_types_id = 6 --Kommun/politik
where matchning.operation_types_crossref.name in ('Ansvarsskade','IT','-odefinierad-','IT-avdelningen','Städ','IFO','Ekonomi och upphandling','Service','Kommunledningsförvaltningen','Operativ','Personal',
'Administration','HR','Kommunikation','Myndighetsutövning','Upphandling' ,'Utställning','Annan/övrig','Verksamhet','Storkök','Utställningsverksamhet','Vaktmästeri och städ',
'Förvaltare','Administrativa stödtjänster','Annan/övrig','Offentlig utsmyckning','Daglig verksamhet','Övrigt','Ekonomi');

update matchning.operation_types_crossref set generic_operation_types_id = 7 --Jobb/företagande
where matchning.operation_types_crossref.name in ('Næringsbygg','Rodret i Önsköldsvik AB','Tjänstemannacentrum i Örnsköldsvik AB','Piteå Hamn','Pnf','Tillväxt','Kompetensförsörjning','Näringsliv och samhälle','Arbetsmarknad',
'Arbetsmarknadsutbildning/ åtgärd','Näringsliv','Arbetsmarknadsutbildning /åtgärd','Arbetsmarknad och integration','Kostenheten','Administration och innovation' );

update matchning.operation_types_crossref set generic_operation_types_id = 8 --Trygghet
where matchning.operation_types_crossref.name in ('Säkerhet','Förebyggande','Räddningstjänsten','Brandförsvaret','Räddningstjänst' );

update matchning.operation_types_crossref set generic_operation_types_id = 9 --Vatten/energi
where matchning.operation_types_crossref.name in ('Övik Energi AB','PiteEnergi','Pumpstation','VA-bolag','Energiförsörjning/Kraftvärme','Vatten- och avloppsverk','Energiförsörjning','Vatten och avlopp',
'Vatten- och avloppsnät','VA','Anläggningsavdelning' );
-- select * from matchning.operation_types_crossref

-- Uppdatera skfab.damage_categories från den tillfälliga matchning.categories_crossref
alter table skfab.damage_categories add column generic integer; 
-- alter table skfab.damage_categories drop column generic; 
update skfab.damage_categories set generic=generic_category_id from  matchning.categories_crossref
where  skfab.damage_categories.damage_category_id = matchning.categories_crossref.damage_category_id;

-- Uppdatera skfab.damage_categories från den tillfälliga matchning.categories_crossref
alter table skfab.damage_types add column generic integer;
-- alter table skfab.damage_types drop column generic;
update skfab.damage_types set generic=generic_type_id from  matchning.types_crossref
where  skfab.damage_types.damage_type_id = matchning.types_crossref.damage_type_id 

-- Uppdatera skfab.operation_types
alter table skfab.operation_types add column generic integer;
-- alter table skfab.operation_types drop column generic;
update skfab.operation_types set generic=matchning.operation_types_crossref.operation_type_id from  matchning.operation_types_crossref
where  skfab.operation_types.operation_type_id = matchning.operation_types_crossref.operation_type_id

-- flytta de generiska tabellern till schema skfab
create table skfab.categories_generic as table matchning.categories_generic;
create table skfab.types_generic as table matchning.types_generic;
create table skfab.operation_types_generic as table matchning.operation_types_generic;
-- drop table skfab.categories_generic
-- drop table skfab.types_generic
-- drop table skfab.operation_types_generic

--testa att relatione till generiska kategorier och typer finns
--Använd norska då kunen nummer är 100108
select d.customer_link,damage_id, 
damage_date,
category_link, 
dc."name", 
case when d.customer_link = 100108 then cg.namn_no else cg.namn_sv end ,
d.type_link ,
dt."name" ,
case when d.customer_link = 100108 then tg.namn_no else tg.namn_sv end ,
description
from skfab.damages d
join skfab.damage_categories dc on dc.damage_category_id = d.category_link 
join skfab.categories_generic cg on cg.id = dc.generic 
join skfab.damage_types dt on dt.damage_type_id = d.type_link
join skfab.types_generic tg on tg.id = dt.generic 

--Gruppera på generiska
--Generiska kategorier
select cg.namn_sv, count(*) as antal, sum(d.cost_def) as kostnad
from skfab.damages d
join skfab.damage_categories dc on dc.damage_category_id = d.category_link 
join skfab.categories_generic cg on cg.id = dc.generic 
group by 1
--Generiska typer
select tg.namn_sv, count(*) as antal, sum(d.cost_def) as kostnad
from skfab.damages d
join skfab.damage_types dt on dt.damage_type_id = d.type_link
join skfab.types_generic tg on tg.id = dt.generic 
group by 1

drop schema matchning cascade;