--
--  Copyright (C) 2001, 2002 MIT
--
--  This file is part of dotLRN.
--
--  dotLRN is free software; you can redistribute it and/or modify it under the
--  terms of the GNU General Public License as published by the Free Software
--  Foundation; either version 2 of the License, or (at your option) any later
--  version.
--
--  dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
--  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
--  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
--  details.
--

--
-- create portals model
--
-- @author <a href="mailto:arjun@openforce.net">arjun@openforce.net</a>
-- @author <a href="mailto:yon@openforce.net">yon@openforce.net</a>
-- @creation-date 2001-10-01
-- @version $Id: portal-core-create.sql,v 1.12 2004/06/09 00:56:46 donb Exp $
--

create sequence portal_seq;

create table portal_datasources (
    datasource_id                   integer
                                    constraint portal_datasources_pk
                                    primary key,
    name                            varchar(200)
                                    constraint p_datasources_name_nn
                                    not null,
    pretty_name                     varchar(200)
                                    constraint p_datasources_pretty_name_nn
                                    not null,
    application                     varchar(100)
                                    constraint p_datasources_application_fk
                                    references apm_package_types (package_key)
                                    on delete cascade,
    owner                           varchar(100)
                                    constraint p_datasources_owner_fk
                                    references apm_package_types (package_key)
                                    on delete cascade
                                    constraint p_datasources_owner_nn
                                    not null,
    template                        varchar(200)
                                    constraint p_datasources_template_nn
                                    not null,
    admin_p                         char(1)
                                    default 't'
                                    constraint p_datasources_admin_p_nn
                                    not null
                                    constraint p_datasources_admin_p_ck
                                    check (admin_p in ('t', 'f')),
    shadeable_p                     char(1)
                                    default 't'
                                    constraint p_datasources_shadeable_p_nn
                                    not null
                                    constraint p_datasources_shadeable_p_ck
                                    check (shadeable_p in ('t', 'f')),
    hideable_p                      char(1)
                                    default 't'
                                    constraint p_datasources_hideable_p_nn
                                    not null
                                    constraint p_datasources_hideable_p_ck
                                    check (hideable_p in ('t', 'f')),
    description                     varchar(200)
);

-- indexes for referential integrity checking

create index p_datasources_application_idx on portal_datasources(application);
create index p_datasources_owner_idx on portal_datasources(owner);

comment on table portal_datasources is '
    a portal datasource is the package of code that generates the content      
    of a portal element. the foo-portlet packages create datasources.
';

comment on column portal_datasources.name is '
    The name of this portal datasource.  We can''t arbitrarily use portlet_key because many
    portlet packages will support at least two portlets, one user portlet and one admin
    portlet.
';

comment on column portal_datasources.pretty_name is '
    The default pretty name to use for an instance of this portlet.  This should be a
    message key for internationalized portlets.
';

comment on column portal_datasources.application is '
    The package key of the application that this portlet works with.  For instance the
    forums portlet works with the forums package.
';

comment on column portal_datasources.owner is '
    The package key of the package that implements this portlet.
';

comment on column portal_datasources.template is '
    The name of template that displays the portlet content.  Note this is not a full
    path, portlet templates go in the standard package template library directory.
';

comment on column portal_datasources.admin_p is '
    If true, this is a package admin datasource that should only be made available
    to admins.  This only determines how the portal package code chooses to handle
    a portal datasource when it creates portal pages and creates portlets.  An
    admin portlet is responsible for ensuring that a user actually has the proper
    privileges to admin its related application.  And applications that create their
    own portal page structure (.LRN, in particular) may or may not honor this flag.
';

comment on column portal_datasources.shadeable_p is '
    If true, this datasource implements the shade operator.  DRB: This is only
    being kept around for .LRN, shading is something the portal page manager
    should have absolute control over.
';

comment on column portal_datasources.hideable_p is '
    If true, this datasource implements the hide operator.  DRB: This is only
    being kept around for .LRN, shading is something the portal page manager
    should have absolute control over.
';

create table portal_datasource_parameters (
    datasource_id                   integer
                                    constraint p_ds_params_datasource_id_fk
                                    references portal_datasources (datasource_id)
                                    on delete cascade
                                    constraint p_ds_params_datasource_id_nn
                                    not null,
    key                             varchar(200)
                                    constraint p_ds_params_key_nn
                                    not null,
    value                           varchar(200),
    config_required_p               char(1)
                                    default 'f'
                                    constraint p_ds_params_cfg_req_p_nn
                                    not null
                                    constraint p_ds_params_cfg_req_p_ck
                                    check (config_required_p in ('t', 'f')),
    configured_p                    char(1)
                                    default 'f'
                                    constraint p_ds_params_cfg_p_nn
                                    not null
                                    constraint p_ds_params_cfg_p_ck
                                    check (configured_p in ('t', 'f')),
    constraint portal_datasource_params_pk
    primary key (datasource_id, key)

);

comment on table portal_datasource_parameters is '
    maintains a set of default parameters for portal datsources. these
    are copied to a portal element when it is created.

    configuration semantics:

    config_required_p | configured_p      |
    ----------------- | ----------------- | ----------------------------------
            t         |         t         | static configuration is provided
                      |                   | for the portal elements. it can be
                      |                   | changed later.
    ----------------- | ----------------- | ----------------------------------
            t         |         f         | the portal element must be
                      |                   | configured when created.
    ----------------- | ----------------- | ----------------------------------
            f         |         t         | an optional default configuration
                      |                   | is provided.
    ----------------- | ----------------- | ----------------------------------
            f         |         f         | configuration is optional and no
                      |                   | default is provided.
';

comment on column portal_datasource_parameters.config_required_p is '
    This parameter requires configuration. If configured_p is true then no
    action is needed at portal element creation since the portal element
    can simply use the default value provided. If configured_p is false then
    the portal element must provide a value for this parameter at creation
    time.
';

comment on column portal_datasource_parameters.configured_p is '
    if true then a default value is provided for this parameter.
';

create table portal_layouts (
    layout_id                       integer
                                    constraint p_layouts_layout_id_fk
                                    references acs_objects (object_id)
                                    constraint portal_layouts_pk
                                    primary key,
    name                            varchar(200)
                                    constraint p_layouts_name_un
                                    unique
                                    constraint p_layouts_name_nn
                                    not null,
    description                     varchar(200),
    filename                        varchar(200)
);

comment on table portal_layouts is '
    a portal layout is a template that defines regions for portal 
    elements to "fill in". Examples of layouts are two column and
    three column
';

comment on column portal_layouts.filename is '
    the relative path to the layout template from the "portal/www/"
    directory without the ending ".adp" extension. Example: "layouts/simple2"
';

create table portal_layout_regions (
    layout_id                       integer
                                    constraint p_layout_regions_layout_id_fk
                                    references portal_layouts (layout_id)
                                    on delete cascade
                                    constraint p_layout_regions_layout_id_nn
                                    not null,
    region                          varchar(20)
                                    constraint p_layout_regions_region_nn
                                    not null,
    constraint portal_layout_regions_pk
    primary key (layout_id, region)
);

comment on table portal_layout_regions is '
    this table describes each of the regions in each of the layouts.
    for example a two column layout would have two entries in this
    table: region = 1 and region = 2. 
';

comment on column portal_layout_regions.region is '
    an ordering of regions in a layout starting from 1
';

create table portal_themes (
    theme_id                        integer
                                    constraint p_themes_theme_id_fk
                                    references acs_objects (object_id)
                                    constraint portal_themes_pk
                                    primary key,
    name                            varchar(200)
                                    constraint p_themes_name_un
                                    unique
                                    constraint p_themes_name_nn
                                    not null,
    description                     varchar(200),
    filename                        varchar(200),
    resource_dir                    varchar(200)
);

comment on table portal_themes is '
    decoration templates for portal elements
';

comment on column portal_themes.filename is '
    the relative path to the theme template from the "portal/www/"
    directory without the ending ".adp" extension. Example: "themes/deco-theme"
';

comment on column portal_themes.resource_dir is '
    the relative path to the theme''s resource directory from the "portal/www/"
    directory. The resource directory typically contains graphics files. 
    Example: "themes/deco-theme"
';

create table portals (
    portal_id                       integer
                                    constraint p_portal_id_fk
                                    references acs_objects (object_id)
                                    constraint portals_pk
                                    primary key,
    owner_id                        integer
                                    constraint p_owner_id_fk
                                    references acs_objects (object_id)
                                    constraint p_owner_id_nn
                                    not null,
    package_id                      integer
                                    constraint p_package_id_fk
                                    references apm_packages (package_id)
                                    constraint p_package_id_nn
                                    not null,
    name                            varchar(200)
                                    default 'Untitled'
                                    constraint p_name_nn
                                    not null,
    theme_id                        integer
                                    constraint p_theme_id_fk
                                    references portal_themes (theme_id)
                                    constraint p_theme_id_nn
                                    not null,
    template_id                     integer
                                    constraint p_template_id_fk
                                    references portals (portal_id)
);

create index portals_owner_id_idx on portals(owner_id);
create index portals_package_id_idx on portals(package_id);

comment on table portals is '
    portals are containers of one or more portal pages mapped to a party.
';

comment on column portals.package_id is '
    The portal instance that ownns this portal
';

comment on column portals.template_id is '
    A portal may have a template (another portal)
';

comment on column portals.theme_id is '
    Portals one theme for all its contained pages
';

create table portal_datasource_map (
    package_id                      integer
                                    constraint pdm_package_id_fk
                                    references apm_packages (package_id)
                                    constraint pdm_package_id_nn
                                    not null,
    datasource_id                   integer
                                    constraint pdm_datasource_id_fk
                                    references portal_datasources (datasource_id)
                                    constraint pdm_datasource_id_nn
                                    not null,
    constraint pdm_pk
    primary key (package_id, datasource_id)
);

create index pdm_datasource_idx on portal_datasource_map (datasource_id);

comment on table portal_datasource_map is '
    Maps datasources to portal instances.  This is used by the portal admin UI to track
    which datasources have had their applications mounted under the instance''s mount
    point.  Independent applications like .LRN that use the portal package as a service
    do not need to maintain this map.
';

comment on column portal_datasource_map.package_id is '
    The package id of the portal instance the datasource is mapped to.
';

comment on column portal_datasource_map.datasource_id is '
    The package id of the portal instance the datasource is mapped to.
';

create table portal_pages (
    page_id                         integer
                                    constraint p_pages_page_id_fk
                                    references acs_objects (object_id)
                                    constraint portal_pages_pk
                                    primary key,
    name                            varchar(200)
                                    default 'Untitled Page'
                                    constraint p_pages_name_nn
                                    not null,
    portal_id                       integer
                                    constraint p_pages_portal_id_fk
                                    references portals (portal_id)
                                    constraint p_pages_portal_id_nn
                                    not null,
    layout_id                       integer
                                    constraint p_pages_layout_id_fk
                                    references portal_layouts (layout_id)
                                    constraint p_pages_layout_id_nn
                                    not null,
    sort_key                        integer
                                    constraint p_pages_sort_key_nn
                                    not null,
    constraint p_pages_portal_id_sort_key_un
    unique (portal_id, sort_key)
);

create index portal_pages_prtl_page_idx on portal_pages (portal_id, page_id);

comment on table portal_pages is '
    portal pages are containers for portal elements. can be thought of
    as the "tabs" on a portal
';

comment on column portal_pages.sort_key is '
    an ordering of the pages contained in the same portal starting from
    0 for the first page and increasing in an gapless integer sequence
';

create table portal_elements (
    element_id                      integer
                                    constraint portal_elements_pk
                                    primary key,
    datasource_id                   integer
                                    constraint p_elements_datasource_id_fk
                                    references portal_datasources (datasource_id)
                                    on delete cascade
                                    constraint p_elements_datasource_id_nn
                                    not null,
    name                            varchar(200)
                                    constraint p_elements_name_nn
                                    not null,
    page_id                         integer
                                    constraint p_elements_page_id_fk
                                    references portal_pages
                                    on delete cascade
                                    constraint p_elements_page_id_nn
                                    not null,
    region                          varchar(20)
                                    constraint p_elements_region_nn
                                    not null,
    sort_key                        integer
                                    constraint p_elements_sort_key_nn
                                    not null,
    state                           varchar(6)
                                    default 'full'
                                    constraint p_elements_state_ck
                                    check (state in ('full', 'shaded', 'hidden', 'pinned')),
    shadeable_p                     char(1)
                                    default 't'
                                    constraint p_elements_shadeable_p_nn
                                    not null
                                    constraint p_elements_shadeable_p_ck
                                    check (shadeable_p in ('t', 'f')),
    hideable_p                      char(1)
                                    default 't'
                                    constraint p_elements_hideable_p_nn
                                    not null
                                    constraint p_elements_hideable_p_ck
                                    check (hideable_p in ('t', 'f')),
    constraint p_elements_page_id_name_un
    unique (page_id, name)
);

comment on table portal_elements is '
    the user-visible "box" on a portal page that displays the content of a datasource
';

comment on column portal_elements.sort_key is '
    an ordering of elements contained in the same region on a page starting from
    0 for the first element and increasing in an gapless integer sequence
';

comment on column portal_elements.state is '
    one of the set "full" (normal), "shaded" (title bar only), "hidden" (not shown), 
    or "pinned" (like full, but without state change links). portal themes impliment these
    different behaviors based on a given elements state
';

create table portal_element_parameters (
    parameter_id                    integer
                                    constraint portal_element_parameters_pk
                                    primary key,
    element_id                      integer
                                    constraint p_element_params_element_id_fk
                                    references portal_elements (element_id)
                                    on delete cascade
                                    constraint p_element_params_element_id_nn
                                    not null,
    key                             varchar(50)
                                    constraint p_element_params_key_nn
                                    not null,
    value                           varchar(200),
    config_required_p               char(1)
                                    default 'f'
                                    constraint p_element_params_cfg_req_p_nn
                                    not null
                                    constraint p_element_params_cfg_req_p_ck
                                    check (config_required_p in ('t', 'f')),
    configured_p                    char(1)
                                    default 'f'
                                    constraint p_element_params_cfg_p_nn
                                    not null
                                    constraint p_element_params_cfg_p_ck
                                    check (configured_p in ('t', 'f'))
);

create index p_element_params_element_key_idx on portal_element_parameters (element_id, key);

comment on table portal_element_parameters is '
    parameters on a per-element basis. the data structure is that of a multiset (aka bag)
    where multiple entries with the same key (for the same element) are allowed ?? XXXX. 
';
