/*****************************************************************
 * Lösche bereits existierende Views, Tabellen und Domänen.
 *****************************************************************/

DROP AGGREGATE IF EXISTS ARRAY_AGGREGATE_DISTINCT(ANYELEMENT)        CASCADE;
DROP AGGREGATE IF EXISTS STRING_AGGREGATE_DISTINCT(ANYELEMENT)       CASCADE;
DROP AGGREGATE IF EXISTS STRING_ARRAY_AGGREGATE_DISTINCT(ANYELEMENT) CASCADE;

DROP VIEW IF EXISTS vorfahren_1 CASCADE;
DROP VIEW IF EXISTS vorfahren_2 CASCADE;

DROP View IF EXISTS person CASCADE;
DROP VIEW IF EXISTS eltern CASCADE;

DROP TABLE IF EXISTS e_person   CASCADE;
DROP TABLE IF EXISTS generation CASCADE;

DROP TYPE IF EXISTS D_GESCHLECHT;

/*****************************************************************
 * Domänen
 *****************************************************************/

CREATE TYPE D_GESCHLECHT 
AS 
ENUM('Frau', 'Mann');

/*****************************************************************
 * Tabellen
 *****************************************************************/

-- Generationen:
--     g_id (PK): Generationsstude
--     g_bezeichnung:      Name der Generationstufe (geschlechtsneutral, plural)
--     g_bezeichnung_frau: Name der Generationstufe (weiblich, singular)
--     g_bezeichnung:      Name der Generationstufe (männlich, singular)
CREATE TABLE generation
(g_id               INTEGER NOT NULL,
 g_bezeichnung      VARCHAR NOT NULL,
 g_bezeichnung_frau VARCHAR NOT NULL,
 g_bezeichnung_mann VARCHAR NOT NULL,

 CONSTRAINT pk_generation
   PRIMARY KEY (g_id)
);

-- e_preson: 
--    p_id (PK) 
--    p_name 
--    p_geschlecht
--    p_mutter (FK -> e_person)
--    p_vater  (FK -> e_person)
--    ...        (beliebig viele weitere Informationen)
CREATE TABLE e_person
(p_id         INTEGER      NOT NULL,
 p_name       VARCHAR      NOT NULL,
 p_geschlecht D_GESCHLECHT NOT NULL,
 m_id         INTEGER,
 v_id         INTEGER,

 CONSTRAINT pk_person
   PRIMARY KEY (p_id),
   
 CONSTRAINT fk_preson_mutter
   FOREIGN KEY (m_id) REFERENCES e_person(p_id),

 CONSTRAINT fk_person_vater
   FOREIGN KEY (v_id)  REFERENCES e_person(p_id)
);

/*****************************************************************
 * Views
 *****************************************************************/

-- person: = e_person mit Namen und Geschlecht der Personen,
--           die in der jeweiligen Eltern-/Kind-Beziehung stehen.
--  p_id:         Identifikator einer Person
--  p_name:       Name der Person p_id
--  p_geschlecht: Geschlecht der Person p_id
--  m_id:         Identifikator der Mutter der Person p_id
--  m_name:       Name der Person e_id
--  v_id:         Identifikator des Vaters der Person p_id
--  v_name:       Name der Person e_id
CREATE VIEW person (p_id, p_name, p_geschlecht,
                    m_id, m_name, v_id, v_name
                   )
AS
SELECT p.p_id, p.p_name, p.p_geschlecht,
       m.p_id, m.p_name, v.p_id, v.p_name
FROM        e_person p 
       JOIN e_person m ON (p.m_id = m.p_id)
       JOIN e_person v ON (p.v_id = v.p_id)           
;

-- Inhalt der View "person"
SELECT   p_id, p_name, p_geschlecht,
         m_id, m_name, v_id, v_name
FROM     person
ORDER BY p_name, p_id, m_name, m_id, v_name, v_id
;

-- eltern: = Eltern mit Namen und Geschlecht der Personen,
--           die in der jeweiligen Eltern-/Kind-Beziehung stehen.
--  p_id:         Identifikator einer Person
--  p_name:       Name der Person p_id
--  p_geschlecht: Geschlecht der Person p_id
--  e_id:         Identifikator eines Elters der Person p_id
--  e_name:       Name der Person e_id
--  e_geschlecht: Geschlecht der Person e_id
CREATE VIEW eltern (p_id, p_name, p_geschlecht,
                    e_id, e_name, e_geschlecht
                   )
AS
SELECT p.p_id, p.p_name, p.p_geschlecht,
       m.p_id, m.p_name, m.p_geschlecht
FROM   person p JOIN person m ON (p.m_id = m.p_id)
UNION
SELECT p.p_id, p.p_name, p.p_geschlecht,
       v.p_id, v.p_name, v.p_geschlecht
FROM   person p JOIN person v ON (p.v_id = v.p_id)        
;

-- Inhalt der View "eltern"
SELECT   p_id, p_name, p_geschlecht,
         e_id, e_name, e_geschlecht
FROM     eltern
ORDER BY p_name, p_id, e_name, e_id
;

/*****************************************************************
 * Rekursive Views
 *****************************************************************/

-- Vorfahren 1:
--  p_id: Identifikator einer Person
--  v_id: Identifikator eines Vorfahren der Person p_id 
--
-- Beziehung:  Eine Person p_id hat eine Person v_id als Vorfahre.
-- Berechnung: Durch Rekursion über die Beziehungstabelle Eltern.
CREATE RECURSIVE VIEW vorfahren_1 (p_id, v_id)
AS
( -- Die Eltern sind Vorfahren (nicht-rekrusiver Zweig).
  SELECT p_id, e_id FROM eltern

  UNION

  -- Die Vorfahren der Eltern sind auch Vorfahren (rekursiver Zweig).
  SELECT e.p_id, v.v_id
  FROM   eltern e, vorfahren_1 v
  WHERE  e.e_id = v.p_id
)
;

-- Inhalt der View "Vorfahren 1"
SELECT   p_id, v_id 
FROM     vorfahren_1
ORDER BY p_id, v_id
;


-- Vorfahren 2:
--  p_id:          Identifikator einer Person
--  p_name:        Name der Person p_id
--  p_geschlecht:  Geschlecht der Person p_id
--  v_id:          Identifikator eines Vorfahren der Person p_id
--  v_name:        Name der Person e_id
--  v_geschlecht:  Geschlecht der Person e_id
--  g_id:          Abstand der Generationen zwischen Person und Vorfahre
--  g_bezeichnung: Bezeichnung der Vorfahrengeneration: 
--                 Eltern, Großeltern ... (plural) 
--  g_bezeichnung_geschlecht: 
--                 Bezeichnung der Vorfahrengeneration 
--                 abhängig vom Geschlecht des Vorfahren:
--                 Mutter, Vater, Großmutter, Großvater ... (singular) 
--
-- Beziehung:  Eine Person p_id hat eine Person v_id als Vorfahre.
-- Berechnung: Durch Rekursion über die Beziehungstabelle Eltern.
CREATE RECURSIVE VIEW vorfahren_2 (p_id, p_name, p_geschlecht,
                                   v_id, v_name, v_geschlecht,
                                   g_id, g_bezeichnung, g_bezeichnung_geschlecht
                                  )
AS
(
  -- Die Eltern sind Vorfahren (nicht-rekrusiver Zweig).
  SELECT p_id, p_name, p_geschlecht,
         e_id, e_name, e_geschlecht,
         g.g_id, g.g_bezeichnung,
         -- Berechnung der geschlechtsspezifischen Bezeichnung der Generation
         CASE WHEN e_geschlecht='Frau' 
           THEN g.g_bezeichnung_frau
           ELSE g.g_bezeichnung_mann
         END
  FROM   eltern, generation g -- Kartesisches Produkt
  WHERE  g.g_id = 1             -- mit einer ein-elementigen Menge!
         -- Elterngeneration = eine Generation Abstand (g.g_id = 1)
  UNION

  -- Die Eltern der Vorfahren sind auch Vorfahren (rekursiver Zweig).
  SELECT v.p_id, v.p_name, v.p_geschlecht,
         e.e_id, e.e_name, e.e_geschlecht,
         g.g_id, g.g_bezeichnung,
         -- Berechnung der geschlechtsspezifischen Bezeichnung der Generation
         CASE WHEN e_geschlecht='Frau'
           THEN g.g_bezeichnung_frau
           ELSE g.g_bezeichnung_mann
         END
  FROM   vorfahren_2 v, eltern e, generation g
  WHERE  v.v_id = e.p_id AND g.g_id = v.g_id+1
         -- Elterngeneration = eine Generation mehr Abstand (g.g_id = v.g_id+1)
)
;

-- Inhalt der View "Vorfahren 2"
SELECT   p_id, p_name, p_geschlecht,
         v_id, v_name, v_geschlecht,
         g_id, g_bezeichnung, g_bezeichnung_geschlecht
FROM     vorfahren_2
ORDER BY p_name, p_id, g_id, g_bezeichnung_geschlecht, v_name
;

/*****************************************************************
 * Listen-Aggregation
 *****************************************************************/

CREATE OR REPLACE FUNCTION ARRAY_APPEND_DISTINCT(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS 
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '{}'
   WHEN $1 IS NULL
       THEN ARRAY[$2]
   WHEN $2 IS NULL
       THEN $1
-- WHEN $2 = ANY($1) -- Duplikat-Elimination, kostet Zeit!
--     THEN $1
   ELSE
       ARRAY_APPEND($1,$2)
END;
$_SQL_$
LANGUAGE SQL IMMUTABLE;
-- http://www.postgresql.org/docs/current/interactive/sql-createaggregate.html
CREATE AGGREGATE ARRAY_AGGREGATE_DISTINCT(anyelement)
(
   SFUNC = ARRAY_APPEND_DISTINCT,
   STYPE = ANYARRAY
); 

/*****************************************************************
 * String-Listen-Aggregation
 *****************************************************************/

CREATE OR REPLACE FUNCTION STRING_APPEND_DISTINCT(VARCHAR, ANYELEMENT) RETURNS VARCHAR AS
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN ''
   WHEN $1 IS NULL
       THEN CAST($2 AS VARCHAR)
   WHEN $2 IS NULL
       THEN $1
   ELSE
       CAST($1 AS VARCHAR) || ', ' || CAST($2 AS VARCHAR)
END;
$_SQL_$
LANGUAGE SQL IMMUTABLE;

CREATE AGGREGATE STRING_AGGREGATE_DISTINCT(anyelement)
(
   SFUNC = STRING_APPEND_DISTINCT,
   STYPE = VARCHAR
);
 
 
CREATE OR REPLACE FUNCTION STRING_ARRAY_APPEND_DISTINCT(VARCHAR, ANYELEMENT) RETURNS VARCHAR AS
$_SQL_$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '[]'
   WHEN $1 IS NULL
       THEN '[' || CAST($2 AS VARCHAR) || ']'
   WHEN $2 IS NULL
       THEN $1
   ELSE
          SUBSTRING(CAST($1 AS VARCHAR) FROM 1 FOR CHAR_LENGTH(CAST($1 AS VARCHAR))-1)
       || ',' || CAST($2 AS VARCHAR) || ']'
END;
$_SQL_$
LANGUAGE SQL IMMUTABLE;

CREATE AGGREGATE STRING_ARRAY_AGGREGATE_DISTINCT(anyelement)
(
   SFUNC = STRING_ARRAY_APPEND_DISTINCT,
   STYPE = VARCHAR
);

/*****************************************************************
 * Beipielsdaten
 *****************************************************************/

-- http://www.ulf-neundorfer.de/v-bez.html
-- http://de.wikipedia.org/wiki/Generationsbezeichnungen
INSERT INTO 
generation(g_id, g_bezeichnung, g_bezeichnung_frau, g_bezeichnung_mann)
VALUES
(00, 'Selbst',               'Frau',                 'Mann'               ),
(01, 'Eltern',               'Mutter',               'Vater'              ),
(02, 'Großeltern',           'Großmutter',           'Großvater'          ),
(03, 'Urgroßeltern',         'Urgroßmutter',         'Urgroßvater'        ),
(04, 'Alteltern',            'Altmutter',            'Altvater'           ),
(05, 'Altgroßeltern',        'Altgroßmutter',        'Altgroßvater'       ),
(06, 'Alturgroßeltern',      'Alturgroßmutter',      'Alturgroßvater'     ),
(07, 'Obereltern',           'Obermutter',           'Obervater'          ),
(08, 'Obergroßeltern',       'Obergroßmutter',       'Obergroßvater'      ),
(09, 'Oberurgroßeltern',     'Oberurgroßmutter',     'Oberurgroßvater'    ),
(10, 'Stammeltern',          'Stammmutter',          'Stammvater'         ),
(11, 'Stammgroßeltern',      'Stammgroßmutter',      'Stammgroßvater'     ),
(12, 'Stammurgroßeltern',    'Stammurgroßmutter',    'Stammurgroßvater'   ),
(13, 'Ahneneltern',          'Ahnenmutter',          'Ahnenvater'         ),
(14, 'Ahnengroßeltern',      'Ahnengroßmutter',      'Ahnengroßvater'     ),
(15, 'Ahnenurgroßeltern',    'Ahnenurgroßmutter',    'Ahnenurgroßvater'   ),
(16, 'Urahneneltern',        'Urahnenmutter',        'Urahnenvater'       ),
(17, 'Urahnengroßeltern',    'Urahnengroßmutter',    'Urahnengroßvater'   ),
(18, 'Urahnenurgroßeltern',  'Urahnenurgroßmutter',  'Urahnenurgroßvater' ),
(19, 'Erzeltern',            'Erzmutter',            'Erzvater'           ),
(20, 'Erzgroßeltern',        'Erzgroßmutter',        'Erzgroßvater'       ),
(21, 'Erzurgroßeltern',      'Erzurgroßmutter',      'Erzurgroßvater'     ),
(22, 'Erzahneneltern',       'Erzahnenmutter',       'Erzahnenvater'      ),
(23, 'Erzahnengroßeltern',   'Erzahnengroßmutter',   'Erzahnengroßvater'  ),
(24, 'Erzahnenurgroßeltern', 'Erzahnenurgroßmutter', 'Erzahnenurgroßvater')
;

INSERT INTO e_person(p_id, p_name, p_geschlecht, m_id, v_id)
VALUES
--Obereltern-Generation
(01, 'Ba',  'Frau', NULL, NULL),
(02, 'Mat', 'Mann', NULL, NULL),

--Alturgroßeltern-Generation
(03, 'FrS', 'Mann',   01,   02),

--Altgroßeltern-Generation
(04, 'En',  'Frau', NULL, NULL),
(05, 'Lu',  'Mann', NULL, NULL),
(06, 'Fr',  'Mann', NULL,   03),

--Alteltern-Generation
(07, 'Th',  'Frau', NULL, NULL),
(08, 'Ni',  'Mann', NULL, NULL),
(09, 'Mar', 'Frau', NULL, NULL),
(10, 'Jo',  'Mann', NULL, NULL),

(11, 'Er',  'Frau', NULL, NULL),
(12, 'Ro',  'Mann',   04,   05),
(13, 'Cä',  'Frau', NULL, NULL),
(14, 'KaS', 'Mann', NULL,   06),

-- Urgroßeltern-Generation
(15, 'Fr',  'Frau',   07,   08),
(16, 'JaS', 'Mann',   09,   10),
(17, 'Il',  'Frau',   11,   12),
(18, 'Ka',  'Mann',   13,   14),

-- Großeltern-Generation
(19, 'Ma',  'Frau',   15,   16),
(20, 'Wo',  'Mann',   17,   18),

-- Eltern-Generation
(21, 'Si',  'Frau',   19,   20),
(22, 'So',  'Frau',   19,   20),
(23, 'Ve',  'Frau',   19,   20),

-- aktuelle Generation
(24, 'Ja',  'Mann' ,  21, NULL),
(25, 'Ol',  'Frau' ,  21, NULL),
(26, 'Li',  'Mann' ,  22, NULL)
;

/*****************************************************************
 * EOF
 *****************************************************************/