------ Type Declaration ------

-- Region_t
create or replace type Region_t as object
(
    COD	NUMBER(4,0),
    DESIGNATION	VARCHAR2(50 BYTE),
    NUT1 VARCHAR2(50 BYTE)
);

-- District_t
create or replace type District_t as object
(
    COD	NUMBER(4,0),
    DESIGNATION	VARCHAR2(50 BYTE),
    REGION ref Region_t
);

-- DistrictsRef_tab_t
create or replace type DistrictsRef_tab_t as table of ref District_t;

-- Municipality
create or replace type Municipality_t as object
(
    COD	NUMBER(4,0),
    DESIGNATION	VARCHAR2(50 BYTE),
    DISTRICT ref District_t,
    REGION ref Region_t
);

-- MunicipalitiesRef_tab_t
create or replace type MunicipalitiesRef_tab_t as table of ref Municipality_t;

-- RoomType
create or replace type RoomType_t as object
(
    ROOMTYPE NUMBER(4,0),
    DESCRIPTION VARCHAR2(50 BYTE)
);

-- Facility
create or replace type Facility_t as object
(
    ID number(4,0),
    NAME varchar2(80),
    CAPACITY number(8,0),
    ROOMTYPE ref RoomType_t,
    ADDRESS varchar2(80),
    MUNICIPALITY ref Municipality_t
);

-- FacilitiesRef_tab_t
create or replace type FacilitiesRef_tab_t as table of ref Facility_t;

-- Activity
create or replace type Activity_t as object
(
    REF varchar2(20),
    ACTIVITY varchar2(20)
);

-- Activities_tab_t
create or replace type Activities_tab_t as table of Activity_t;

-- Region Districts
alter type Region_t
add attribute (DISTRICTS DistrictsRef_tab_t) CASCADE;

-- Region Municipalities
alter type Region_t
add attribute (MUNICIPALITIES MunicipalitiesRef_tab_t) CASCADE;

-- District Municipalities
alter type District_t
add attribute (MUNICIPALITIES MunicipalitiesRef_tab_t) CASCADE;

-- Municipality Facilities
alter type Municipality_t
add attribute (FACILITIES FacilitiesRef_tab_t) CASCADE;

-- Facility Activities
alter type Facility_t
add attribute (ACTIVITIES Activities_tab_t) CASCADE;

------ Table Creation ------

-- Regions
create table Regions of Region_t
    nested table Districts store as Region_Districts,
    nested table Municipalities store as Region_Municipalities;

-- Districts
create table Districts of District_t
    nested table Municipalities store as District_Municipalities;

-- Municipalities
create table Municipalities of Municipality_t
    nested table Facilities store as Municipality_Facilities;

-- RoomTypes
create table RoomTypes of RoomType_t;

-- Facilities
create table Facilities of Facility_t
    nested table Activities store as Facility_Activities;

------ Insert Values ------

-- Regions TODO
delete from Regions;
insert into Regions (COD, DESIGNATION, NUT1)
select r.COD, r.DESIGNATION, r.NUT1
from gtd8.Regions r;

select *
from Regions;

-- Districts TODO
delete from Districts;
insert into Districts (COD, DESIGNATION, REGION)
select d.COD, d.DESIGNATION, (select ref(r) from Regions r where r.COD = d.REGION)
from gtd8.Districts d;

select *
from Districts;

-- Municipalities TODO
delete from Municipalities;
insert into Municipalities (COD, DESIGNATION, DISTRICT, REGION)
select m.COD, m.DESIGNATION, (select ref(d) from Districts d where d.COD = m.DISTRICT), (select ref(r) from Regions r where r.COD = m.REGION)
from gtd8.Municipalities m;

select count(*)
from Municipalities;

-- RoomTypes
delete from RoomTypes;
insert into RoomTypes (ROOMTYPE, DESCRIPTION)
select rt.ROOMTYPE, rt.DESCRIPTION
from gtd8.RoomTypes rt;

select *
from RoomTypes;

-- Facilities
delete from Facilities;
insert into Facilities (ID, NAME, CAPACITY, ROOMTYPE, ADDRESS, MUNICIPALITY, ACTIVITIES)
select f.ID, f.NAME, f.CAPACITY, (select ref(rt) from RoomTypes rt where rt.ROOMTYPE = f.ROOMTYPE), f.ADDRESS, (select ref(m) from Municipalities m where m.COD = f.MUNICIPALITY)
, Activities_tab_t(Activity_t(a.ref, a.Activity))
from gtd8.Facilities f
join gtd8.Uses u ON u.ID = f.ID
join gtd8.Activities a on a.REF = u.REF;

delete from Facilities;
insert into Facilities (ID, NAME, CAPACITY, ROOMTYPE, ADDRESS, MUNICIPALITY, ACTIVITIES)
select f.ID, f.NAME, f.CAPACITY, (select ref(rt) from RoomTypes rt where rt.ROOMTYPE = f.ROOMTYPE), f.ADDRESS, (select ref(m) from Municipalities m where m.COD = f.MUNICIPALITY)
, (select cast(collect(a)) as Activities_tab_t) from gtd8.activities a where m.cod = f.municipality.cod)
from gtd8.Facilities f
join gtd8.Uses u ON u.ID = f.ID
join gtd8.Activities a on a.REF = u.REF;

SELECT CAST(COLLECT(phone_numbers) AS phone_book_t)
   FROM customers;

select *
from Facilities;

------ Insert Nested References ------

update municipalities m
set facilities = (select cast(collect(ref(f)) as FacilitiesRef_tab_t) from Facilities f where m.cod = f.municipality.cod);