--
-- packages/sdm/sql/download/download-create.sql
--
-- 
-- @author Vinod Kurup (vinod@kurup.com)
-- 
-- @cvs-id $Id: download-create.sql,v 1.10 2015/07/25 17:58:28 gustafn Exp $
--

-- Note cr_items has available:
--    parent_id
--    name
--    publish_status
-- Note cr_revisions has available:
--    title
--    description
--    publish_date
--    content

-- QUESTION:  How do we store information about vendors like their
--   URL, or a description

--
-- Download Repository Instances:
--   Each has a description and some help text.
--   parent_id is the package_id
--
create table download_repository (
       repository_id integer
                     constraint download_repository_id_fk
                     references cr_items (item_id) on delete cascade
                     constraint download_repository_id_pk primary key
);

select content_type__create_type (
	'cr_download_rep',
	'content_revision',
	'Download Repository',
	'Download Repositories',
	'download_repository',
	'repository_id',
	null
);

comment on table download_repository is '
This table stores the actual download repositories.  Each repository has a title
and description of the repository.  Meta information about what can be stored in
the repository is keyed to this table';

-- DESIGN QUESTION: We could make the archive type part of the metadata,
-- but then we couldn't very well conditionalize other metadata based on it.

-- Each download module will support certain archive types, we need to indicate
-- what those types are.
create sequence download_archive_type_seq;
create view download_archive_type_sequence as 
	   select nextval('download_archive_type_seq') as nextval;
create table download_archive_types (
    archive_type_id integer
                    constraint download_archive_types_pk 
					primary key,
    repository_id   integer
					constraint download_archive_rep_id_fk 
					references download_repository (repository_id),
    pretty_name     varchar(100) not null,
    description     varchar(500) not null
);

comment on table download_archive_types is '
 This table stores the types of archives that can be stored in a given download repository.
';

create sequence download_reasons_seq;
create view download_reasons_sequence as 
	   select nextval('download_reasons_seq') as nextval;
create table download_reasons (
    download_reason_id integer
                       constraint download_archive_reasons_pk 
					   primary key,
    repository_id	   integer
					   constraint download_reason_id_fk 
					   references download_repository (repository_id) ,
    reason			   varchar(500) not null
);

comment on table download_archive_types is '
 This table stores the types of archives that can be stored in a given download repository.
';


--
-- Meta Information for Each Archive in a Particular Repository
-- We must be able to support meta info per archive for things like:
--   owners (email addresses)
--   vendors
--   dependencies
-- This is basically survey simple.
--
create table download_archive_metadata (
    metadata_id      integer
                     constraint download_ma_pk 
					 primary key,
    repository_id    integer
					 constraint download_ma_rep_id_fk
                     references download_repository (repository_id),
    --if archive_type_id is null, applies to all archive types
    archive_type_id  integer 
                     constraint download_ma_type_fk 
					 references download_archive_types,
    sort_key         integer
                     constraint download_ma_sort_key_nn
                     not null,
    pretty_name      varchar(100) 
					 constraint download_ma_pretty_name_nn
					 not null,
    data_type        varchar(30)
                     constraint download_data_type_ck
                     check (data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'date', 'choice')),
    required_p       boolean,
    --linked_p indicates whether we should have links to show all
    --archives with a particular link
    linked_p         boolean,
    --is this field shown on the main page?                 
    mainpage_p       boolean,
    --is this field computed, or should we ask the user to enter it?
    computed_p       boolean
);

--When a piece of metadata has a fixed set of responses
create sequence download_md_choice_id_seq start 1;
create view download_md_choice_id_sequence as 
	   select nextval('download_md_choice_id_seq') as nextval;

create table download_metadata_choices (
	choice_id	integer 
				constraint download_mc_choice_id_nn 
				not null 
				constraint download_mc_choice_id_pk
				primary key,
	metadata_id	integer
				constraint download_mc_met_id_nn 
				not null 
				constraint download_mc_met_id_fk
				references download_archive_metadata,
	-- human readable 
	label		varchar(500) 
				constraint download_mc_label_nn
				not null,
	-- might be useful for averaging or whatever, generally null
	numeric_value	numeric,
	-- lower is earlier 
	sort_order	integer
);

comment on table download_archive_metadata is '
 This table stores information about all metadata stored for each archive in a given
 repository.';

--
-- The Archives Themselves
-- We need at least the following pieces of info:
--   archive_name (via cr_item.name)
--   repository_id (via cr_item.parent_id)
--   summary (via archive_desc_id.title)
--   description (via archive_desc_id.text)
--   description_type (i.e. archive_desc_id.mime_type for description)
--
create table download_archives (
    archive_id          integer
						constraint download_archives_a_id_fk 
                        references cr_items (item_id) on delete cascade 
                        constraint download_archives_a_id_pk 
						primary key,
    archive_type_id     integer 
                        constraint download_archives_at_id_fk 
						references download_archive_types,
-- we use another content_type to hold the content of the archive description, 
-- which we need in addition to version descriptions
    archive_desc_id     integer
                        constraint download_archives_ad_id_fk 
						references cr_revisions
);

-- We need at least the following
--
--  approved_p
--  approved_date
--  approved_user
--  approved_comment 
--  archive_id (via cr_revision.cr_item)
--  version_name (via cr_revision.description)
--  file_name (via cr_revision.title)
--  file_type (via cr_revision.mime_type)
--  file_content (via cr_revision.content)
--  version_url (via metadata)
--  release_notes (via metadata)
--  release_date (via metadata)
--  vendor (via metadata)
--  owner (via metadata)

--  I added file_size, cuz the oracle version
--  keeps content in blobs and thus can easily
--  get file_size from the database
--     vinodk 2001-05-16
create table download_archive_revisions (
    revision_id         integer
						constraint download_ar_id_fk
                        references cr_revisions (revision_id) on delete cascade
                        constraint download_ar_id_pk 
						primary key,
    approved_p          boolean,
    approved_date       timestamptz,
    approved_user       integer 
                        constraint download_ar_usr_fk 
						references users,
    approved_comment    text,
	file_size			integer
						constraint download_ar_file_size_nn
						not null
);

-- Storage of the metadata per archive
-- Long skinny table.
create table download_revision_data (
    revision_id       integer
					  constraint download_revision_data_fk
                      references download_archive_revisions(revision_id),
    metadata_id       integer
					  constraint download_revision_metadata_fk
                      references download_archive_metadata(metadata_id),
    --The possible responses.
    choice_id		  integer
					  constraint download_revision_choice_fk
					  references download_metadata_choices (choice_id),
    boolean_answer	  boolean,
    clob_answer		  text,
    number_answer	  numeric,
    varchar_answer	  text,
    date_answer		  timestamptz
);


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Information about who has downloaded stuff
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

-- We want to collect statistics on downloads.
create sequence download_downloads_seq;
create view download_downloads_sequence as 
	   select nextval('download_downloads_seq') as nextval;
create table download_downloads (
    download_id   integer 
                  constraint download_downloads_id_pk 
				  primary key,
    user_id       integer
				  constraint download_downloads_user_fk 
				  references users 
                  on delete set null,
    revision_id   integer
				  constraint download_downloads_rev_fk 
				  references download_archive_revisions
                  on delete cascade,
    download_date timestamptz not null,
    download_hostname varchar(400),
    download_ip   varchar(40),
    user_agent    varchar(500),
    reason_id     integer
				  constraint download_downloads_reason_fl
				  references download_reasons(download_reason_id) 
				  on delete set null,
    reason        text
);

select content_type__create_type (
	  'cr_download_archive',
	  'content_revision',
	  'Download Archive',
	  'Download Archives',
	  'download_archives',
	  'archive_id',
	  null
);

select content_type__register_child_type(
      'cr_download_rep',
      'cr_download_archive',
	  'generic',
	  0,
	  null
);

select content_type__create_type (
	  'cr_download_archive_desc',
	  'content_revision',
	  'Download Archive Description',
	  'Download Archive Descriptions',
	  'download_archive_descs',
	  'archive_desc_id',
	  null
);

select content_type__register_child_type(
      'cr_download_rep',
      'cr_download_archive_desc',
	  'generic',
	  0,
	  null
);

create view download_repository_obj as
       select repository_id, 
                 o.object_id, o.object_type, o.title as obj_title, o.package_id as obj_package_id, o.context_id,
                 o.security_inherit_p, o.creation_user, o.creation_date, o.creation_ip, o.last_modified, o.modifying_user,
                 o.modifying_ip, 
	          i.parent_id, 
			  r.title, 
			  r.description, 
			  r.content as help_text
       from download_repository dr, acs_objects o, cr_items i, cr_revisions r
	   where dr.repository_id = o.object_id and 
			 i.item_id = o.object_id and
             r.revision_id = i.live_revision;

create view download_archives_obj as
       select cri.parent_id as repository_id, 
			  cri.name as archive_name, 
			  cri.latest_revision, 
			  cri.live_revision,
              da.archive_id, 
			  da.archive_type_id, 
			  da.archive_desc_id,
              crr.title as summary, 
			  crr.description as description, 
			  crr.mime_type as description_type,
              o.creation_user, 
			  o.creation_date, 
			  o.creation_ip 
       from download_archives da, cr_items cri, cr_revisions crr, acs_objects o
	   where da.archive_desc_id = crr.revision_id and 
                         da.archive_desc_id = o.object_id and
			 da.archive_id = cri.item_id;

create view download_arch_revisions_obj as
       select dar.*, 
                          o.object_id, o.object_type, o.title as obj_title, o.package_id as obj_package_id, o.context_id,
                          o.security_inherit_p, o.creation_user, o.creation_date, o.creation_ip, o.last_modified, o.modifying_user,
                          o.modifying_ip, 
			  r.item_id as archive_id, 
			  r.title as file_name, 
			  r.description as version_name, 
			  r.publish_date, 
			  r.mime_type, 
			  r.content
       from download_archive_revisions dar, acs_objects o, cr_revisions r
	   where dar.revision_id = o.object_id and 
			 dar.revision_id = r.revision_id;

create view download_downloads_repository as
       select dd.*,
			  (select repository_id 
			   from download_archives_obj da, cr_revisions r 
			   where dd.revision_id = r.revision_id and 
					 r.item_id = da.archive_id) as repository_id
       from download_downloads dd;

\i download-packages.sql