--
--  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.1.1.1 2002/10/25 21:29:17 yon 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,
    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)
);

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

create table portal_datasource_parameters (
    datasource_id                   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 '
    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 '
    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 '
    if true then a default value is provided for this parameter.
';

create table portal_layouts (
    layout_id                       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),
    resource_dir                    varchar(200)
);

comment on table portal_layouts '
    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 '
    the relative path to the layout template from the "portal/www/"
    directory without the ending ".adp" extension. Example: "layouts/simple2"
';

comment on column portal_layouts.resource_dir '
    XXX remove me
';

create table portal_layout_regions (
    layout_id                       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 '
    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 '
    an ordering of regions in a layout starting from 1
';

create table portal_themes (
    theme_id                        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 '
    decoration templates for portal elements
';

comment on column portal_themes.filename '
    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 '
    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                       constraint portal_portal_id_fk
                                    references acs_objects (object_id)
                                    constraint portals_pk
                                    primary key,
    name                            varchar(200)
                                    default 'Untitled'
                                    constraint portal_name_nn
                                    not null,
    template_id                     constraint template_id_fk
                                    references portals (portal_id),
    theme_id                        constraint portal_theme_id_fk
                                    references portal_themes (theme_id)
                                    constraint portal_theme_id_nn
                                    not null
);

comment on table portals '
    portals are containers of one or more portal pages.
';

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

comment on column portals.theme_id '
    portals one theme for all it's contained pages
';


create table portal_pages (
    page_id                         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                       constraint p_pages_portal_id_fk
                                    references portals (portal_id)
                                    constraint p_pages_portal_id_nn
                                    not null,
    layout_id                       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 p_pages_prtl_page_idx on portal_pages (portal_id, page_id);

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

comment on column portal_pages.sort_key '
    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                   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                         constraint p_elements_page_id_fk
                                    references portal_pages (page_id)
                                    on delete cascade
                                    not null,
    region                          varchar(20)
                                    constraint p_elements_region_nn
                                    not null,
    sort_key                        integer
                                    constraint p_elements_sort_key_nn
                                    not null
                                    constraint p_elements_sort_key_ck
                                    check (sort_key > 0),
    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 '
    the user-visible "box" on a portal page that displays the content of a datasource
';

comment on column portal_elements.sort_key '
    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 '
    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                      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_elem_key_idx on portal_element_parameters (element_id, key);

comment on table portal_element_parameters '
    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. 
';

comment on column portal_element_parameters.config_required_p '
';

comment on column portal_element_parameters.configured_p '
';
