BEGIN;

-- Creates a type to store the contact informations’ type.
CREATE TYPE contack_contact_information_type AS ENUM (
    'Home',
    'Work'
);

-- Create a type to store the Contact informations’ platform.
CREATE TYPE contack_contact_platform AS ENUM (
    'Email',
    'Tel',
    'Discord',
    'Matrix',
    'Skype',
    'Aim',
    'Jabber',
    'ICQ',
    'GroupWise',
    'GaduGadu',
    'Unknown'
);

-- Create a table to store contact information
CREATE TABLE contacts_contact_information (
    pid TEXT,
    pref SMALLINT NOT NULL,
    value TEXT NOT NULL,
    platform contack_contact_platform NOT NULL,
    typ CONTACK_CONTACT_INFORMATION_TYPE,
    uid TEXT NOT NULL,
    PRIMARY KEY (pid, uid)
);

-- Create a table to store most of the contact information
CREATE TABLE contacts (
    
    -- ╭──────╮
    -- │ UUID │
    -- ╰──────╯

    uid TEXT PRIMARY KEY,

    -- ╭───────────────────────────╮
    -- │ Identification Properties │
    -- ╰───────────────────────────╯

    -- Name fields.
    name_given TEXT,
    name_additional TEXT,
    name_family TEXT,
    name_prefixes TEXT,
    name_suffixes TEXT,
    
    -- Nickname
    nickname TEXT,
    
    -- Anniversary
    anniversary TIMESTAMP,
    
    -- Bday
    bday TIMESTAMP,

    -- Gender
    gender_gender TEXT,
    gender_sex TEXT,
    
    -- ┌──────────────────────────────────────────────────────────────────┐
    -- │ Photo                                                            │
    -- │                                                                  │
    -- │ With this, either photo_uri should be non-null, or photo_bin and │ 
    -- │ photo_mime should be non-null. Otherwise it will be assumed that │ 
    -- │ the value is null.                                               │
    -- └──────────────────────────────────────────────────────────────────┘
    photo_uri TEXT,
    photo_bin BYTEA,
    photo_mime TEXT,
    
    -- ╭───────────────────────────╮
    -- │ Organisational Properties │
    -- ╰───────────────────────────╯
    
    -- Title
    title TEXT,
    
    -- Role
    role TEXT,
    
    -- Org Properties.
    org_org TEXT,
    org_unit TEXT,
    org_office TEXT,
   
    -- ┌──────────────────────────────────────────────────────────────────┐
    -- │ Logo                                                             │
    -- │                                                                  │
    -- │ With this, either logo_uri should be non-null, or logo_bin and   │ 
    -- │ logo_mime should be non-null. Otherwise it will be assumed that  │ 
    -- │ the value is null.                                               │
    -- └──────────────────────────────────────────────────────────────────┘
    logo_uri TEXT,
    logo_bin BYTEA,
    logo_mime TEXT,
    
    -- ╭─────────────────────╮
    -- │ Address Properties  │
    -- ├─────────────────────┴──────────────────────────────────────────────┐
    -- │ When it comes to Geo, both values have to be present, or otherwise │
    -- │ it is assumed null.                                                │
    -- └────────────────────────────────────────────────────────────────────┘
    
    -- Home Address
    home_address_street TEXT,
    home_address_locality TEXT,
    home_address_region TEXT,
    home_address_code TEXT,
    home_address_country TEXT,
    home_address_geo_longitude DOUBLE PRECISION,
    home_address_geo_latitude DOUBLE PRECISION,
    
    -- Work Address
    work_address_street TEXT,
    work_address_locality TEXT,
    work_address_region TEXT,
    work_address_code TEXT,
    work_address_country TEXT,
    work_address_geo_longitude DOUBLE PRECISION,
    work_address_geo_latitude DOUBLE PRECISION
    
    -- ╭─────────────────────╮
    -- │ Contact Information │
    -- ├─────────────────────┴──────────────────────────────────────────────┐
    -- │ With Contact Information, due to constraints of SQL, they are      │
    -- │ stored in a separate table, `contacts_contact_information`.        │
    -- └────────────────────────────────────────────────────────────────────┘
);

COMMIT;