-- packages/resources-list/sql/postgresql/resources-list-table-create.sql
--
-- @author jade@safe4all.org
-- @creation-date 2003-08-26
--

-- resources



-- we create two tables to store resource information
-- the information that we keep revisions on is in the 
-- rl_resource_revisions table, the rest is in rl_resource

create sequence rl_resources_number_seq;

create table rl_resources (
        resource_item_id                integer
                                        constraint rl_resources_resource_id_fk
                                        references cr_items 
                                        on delete cascade
                                        constraint rl_resource_resource_id_pk
                                        primary key,
        resource_number                 integer,
        approved_p                      char(1) 
                                        constraint rl_resources_approved_ck
                                        check(approved_p in ('t','f'))
);


create table rl_resources_revisions (
        resource_revision_id            integer
                                        constraint rl_resource_revs_id_fk
                                        references cr_revisions 
                                        on delete cascade
                                        constraint rl_resource_revs_id_pk
                                        primary key
);

-- create the content type
select content_type__create_type (
   'rl_resource',                   -- content_type
   'content_revision',	            -- supertype	
   'Resource',                      -- pretty_name 
   'Resources',                     -- pretty_plural
   'rl_resources_revisions',        -- table_name (should this be rl_resource?)
   'resource_revision_id',          -- id_column 
   'rl_resource__name'              -- name_method
);


-- resource category_type

create sequence rl_resource_category_type_seq;

create table rl_resource_category_type (
        category_id                     integer
                                        constraint rl_resource_category_pk
                                        primary key,
        short_name                      varchar(100),
        description                     varchar(1000),
        -- lower numbers go first in the hierarchy
        ordering                        integer
);

comment on table rl_resource_category_type is '
  A type of category, for example Country, or State, or Who services provided to
';

insert into rl_resource_category_type (
        category_id, short_name, description, ordering) 
values (nextval('rl_resource_category_type_seq'),'Population', 'People who are served by this organization', '1');

insert into rl_resource_category_type (
        category_id, short_name, description, ordering) 
values (nextval('rl_resource_category_type_seq'),'Location', 'Location this service is available in', '3');


-- resource category

create sequence rl_resource_category_seq;

create table rl_resource_category (
        category_id                     integer
                                        constraint rl_resource_category_id_pk
                                        primary key,
        category_type                   integer
                                        constraint rl_resource_category_type_fk
                                        references rl_resource_category_type,
        short_name                      varchar(100),
        description                     varchar(1000)
);

comment on table rl_resource_category_type is '
  The actual category for the resource. For a category type of Country, this might be
  Canada, for example.
';

-- mapping

create table rl_resource_category_map (
        resource_item_id        integer
                                constraint rl_resource_category_map_fk
                                references rl_resources
                                constraint rl_resource_category_nn
                                not null, 
        category_id             integer
                                constraint rl_resource_category_fk
                                references rl_resource_category
                                constraint rl_resource_category_nn
                                not null
);

comment on table rl_resource_category_map is '
  Keeps track of which resources are within each category
';