--
-- packages/ams/sql/postgresql/ams-package-create.sql
--
-- @author Matthew Geddert openacs@geddert.com
-- @creation-date 2004-09-07
-- @cvs-id $Id: ams-package-create.sql,v 1.3 2004/10/22 01:16:16 matthewg Exp $
--
--


------ Attributes
--------------------------------------------------------------------



select define_function_args('ams_attribute__new','ams_attribute_id,object_type,attribute_name,pretty_name,pretty_plural,default_value,description,widget_name,deprecated_p;f,creation_date,creation_user,creation_ip,context_id');

create or replace function ams_attribute__new (integer,varchar,varchar,varchar,varchar,varchar,text,varchar,boolean,timestamptz,integer,varchar,integer)
returns integer as '
declare
        p_ams_attribute_id   alias for $1; -- the AMS Attribute ID
        p_object_type        alias for $2;
        p_attribute_name     alias for $3;
        p_pretty_name        alias for $4; 
        p_pretty_plural      alias for $5; 
        p_default_value      alias for $6; 
        p_description        alias for $7; 
        p_widget_name        alias for $8; 
        p_deprecated_p       alias for $9; 
        p_creation_date      alias for $10;
        p_creation_user      alias for $11;
        p_creation_ip        alias for $12;
        p_context_id         alias for $13;
        v_attribute_id       integer;
        v_acs_datatype       varchar;
        v_ams_attribute_id   integer;
begin

        v_acs_datatype := acs_datatype from ams_widgets where widget_name = p_widget_name;

        v_attribute_id := acs_attribute__create_attribute (
                p_object_type,
                p_attribute_name,
                v_acs_datatype,
                p_pretty_name,
                p_pretty_plural,
                null,               -- p_table_name
                null,               -- p_column_name
                p_default_value,
                ''0'',              -- p_min_n_values
                ''1'',              -- p_max_n_values
                null,               -- p_sort_order
                ''type_specific'',  -- p_storage
                ''f''               -- p_static_p
        );

        if p_description is not null then
                PERFORM acs_attribute__add_description (
                                p_object_type,
                                p_attribute_name,
                                ''ams_attribute_description'',
                                p_description
                );
        end if;

        v_ams_attribute_id := acs_object__new (
                p_ams_attribute_id,
                ''ams_attribute'',
                p_creation_date,
                p_creation_user,
                P_creation_ip,
                p_context_id
        );

        insert into ams_attributes
                (ams_attribute_id,attribute_id,widget_name,deprecated_p)
        values
                (v_ams_attribute_id,v_attribute_id,p_widget_name,p_deprecated_p);

        return v_ams_attribute_id;
end;' language 'plpgsql';



create or replace function ams_attribute__name (integer)
returns varchar as '
declare
        p_ams_attribute_id  alias for $1;
        v_name          varchar;
begin
        v_name := acs_attributes.attribute_name 
             from acs_attributes, ams_attributes
            where ams_attributes.ams_attribute_id = p_ams_attribute_id
              and ams_attributes.attribute_id = acs_attributes.attribute_id;

        return v_name;
end;' language 'plpgsql';

create or replace function ams_attribute__pretty_name (integer)
returns varchar as '
declare
        p_ams_attribute_id  alias for $1;
        v_name          varchar;
begin
        v_name := acs_attributes.pretty_name 
             from acs_attributes, ams_attributes
            where ams_attributes.ams_attribute_id = p_ams_attribute_id
              and ams_attributes.attribute_id = acs_attributes.attribute_id;

        return v_name;
end;' language 'plpgsql';

create or replace function ams_attribute__delete (integer)
returns integer as '
declare
        p_ams_attribute_id      alias for $1;
        v_object_type       varchar;
        v_attribute_name    varchar;
begin
        select acs_attributes.attribute_name, acs_attributes.object_type
          into v_object_type, v_attribute_name
          from acs_attributes, ams_attributes
         where ams_attributes.attribute_id = acs_attributes.attribute_id;

        delete from ams_attribute_values where ams_attribute_id = p_ams_attribute_id;

        PERFORM acs_object__delete (
                        p_ams_attribute_id
                );

        PERFORM acs_attribute__drop_description (
                        v_object_type,
                        v_attribute_name,
                        ''ams_attribute_description''
                );

        PERFORM acs_attribute__drop_attribute (
                        v_object_type,
                        v_attribute_name
                );

        return 0;
end;' language 'plpgsql';






------ Objects
--------------------------------------------------------------------


create or replace function ams_object_revision__root_folder (integer)
returns integer as '
declare
        p_package_id            alias for $1;
        v_folder_id             integer;
        v_count                 integer;
        v_folder_name           varchar;
begin
        v_count := count(*) from cr_folders where package_id = p_package_id;

        if v_count = 0 then

                v_folder_name := package_key || ''_'' || p_package_id from apm_packages
                           where package_id = p_package_id;

                -- create a new root folder
                v_folder_id := content_folder__new (
                        v_folder_name,                  -- name
                        ''AMS Objects'',                -- label
                        ''AMS Object Repository'',      -- description
                        null,                           -- parent_id
                        p_package_id,                   -- parent_id
                        null,                           -- folder_id
                        null,                           -- creation_date
                        null,                           -- creation_user
                        null                            -- creation_ip
                );

                -- register folder content types
                PERFORM content_folder__register_content_type (
                        v_folder_id,                    -- folder_id
                        ''ams_object_revision'',        -- content_type
                        ''f''                           -- include_subtypes
                );

                -- there is no facility in the API for adding in the package_id,
                -- so we have to do it ourselves

                update cr_folders 
                   set package_id = p_package_id 
                 where folder_id = v_folder_id;

        else
                v_folder_id := folder_id from cr_folders where package_id = p_package_id;
        end if;

        return v_folder_id;

end; ' language 'plpgsql';



-- select define_function_args('ams_object_id','object_id,package_id,creation_date;now(),creation_user,creation_ip');
-- get the ams_object_id, and none exists create a new content item

create or replace function ams_object__new (integer,integer,timestamptz,integer,varchar)
returns integer as '
declare
        p_object_id          alias for $1;
        p_package_id         alias for $2;
        p_creation_date      alias for $3;
        p_creation_user      alias for $4;
        p_creation_ip        alias for $5;
        v_ams_object_id      integer;
        v_count              integer;
begin
        v_count := count(*) from ams_objects where object_id = p_object_id;

        if v_count = 0 then

                -- create a new item
                v_ams_object_id := content_item__new (
                        p_object_id::varchar,                   -- name
                        ams_object_revision__root_folder(p_package_id),  -- parent_id
                        null,                                   -- item_id
                        null,                                   -- locale
                        p_creation_date,                        -- creation_date
                        p_creation_user,                        -- creation_user
                        p_object_id,                            -- context_id
                        p_creation_ip,                          -- creation_ip
                        ''content_item'',                       -- item_subtype
                        ''ams_object_revision'',                -- content_type
                        null,                                   -- title
                        null,                                   -- description
                        null,                                   -- mime_type
                        null,                                   -- nls_language
                        null                                    -- data
                );

                insert into ams_objects
                (ams_object_id,object_id)
                values
                (v_ams_object_id,p_object_id);

        else
                v_ams_object_id := ams_object_id(p_object_id);
        end if;

        return v_ams_object_id;
end;' language 'plpgsql';



create or replace function ams_object_id (integer)
returns integer as '
declare
        p_object_id          alias for $1;
        v_ams_object_id      integer;
begin
        return ams_object_id from ams_objects where object_id = p_object_id;
end;' language 'plpgsql';



select define_function_args('ams_object_revision__new','object_id,package_id,creation_date;now(),creation_user,creation_ip');

create or replace function ams_object_revision__new (integer,integer,timestamptz,integer,varchar)
returns integer as '
declare
        p_object_id          alias for $1;
        p_package_id         alias for $2;
        p_creation_date      alias for $3;
        p_creation_user      alias for $4;
        p_creation_ip        alias for $5;
        v_ams_object_id           integer;
        v_ams_object_revision_id  integer;
begin

        -- get the ams_object_id and create the content item if necessary
        v_ams_object_id := ams_object__new (
                p_object_id,
                p_package_id,
                p_creation_date,
                p_creation_user,
                p_creation_ip
        );                

        v_ams_object_revision_id := content_revision__new (
                null,                   -- title
                null,                   -- description
                now(),                  -- publish_date
                null,                   -- mime_type
                null,                   -- nls_language
                null,                   -- data
                v_ams_object_id,        -- item_id
                null,                   -- revision_id
                p_creation_date,        -- creation_date
                p_creation_user,        -- creation_user
                p_creation_ip           -- creation_ip
        );

        PERFORM content_item__set_live_revision (v_ams_object_revision_id);

        insert into ams_object_revisions
        (ams_object_revision_id)
        values
        (v_ams_object_revision_id);

        return v_ams_object_revision_id;
end;' language 'plpgsql';



------ Options                                        
--------------------------------------------------------------------

create or replace function ams_option__new (integer,varchar,integer)
returns integer as '
declare
        p_ams_attribute_id      alias for $1;
        p_option                alias for $2;
        p_sort_order            alias for $3;
        v_option_id             integer;
begin

        v_option_id := nextval(''ams_options_seq'');

        insert into ams_options
        (option_id,ams_attribute_id,option,sort_order)
        values
        (v_option_id,p_ams_attribute_id,p_option,p_sort_order);

        return v_option_id;
end;' language 'plpgsql';


create or replace function ams_option__delete(integer)
returns integer as '
declare
        p_option_id             alias for $1;
begin
        delete from ams_options where object_id = p_option_id;

        return 0;
end;' language 'plpgsql';

create or replace function ams_option__map (integer,integer)
returns integer as '
declare
        p_option_map_id         alias for $1;
        p_option_id             alias for $2;
        v_option_map_id         integer;
begin

        if count(*) from ams_option_map where option_map_id = p_option_map_id = ''0'' then

                v_option_map_id := nextval(''ams_option_map_id_seq'');
                insert into ams_option_map_ids(option_map_id) value (v_option_map_id);
       
        else 
                v_option_map_id := p_option_map_id;

        end if;

        insert into ams_option_map
        (option_map_id,option_id)
        values
        (v_option_map_id,p_option_id);

        return v_option_map_id;
end;' language 'plpgsql';





------ Attribute Values
--------------------------------------------------------------------

-- Unlike the ams_attribute_value__save proc below this one,
-- ams_attribute_value__new will save null entries (i.e. when
-- no value was given for an attribute). this will chew up
-- database space (with non-value rows). but it can be called
-- upon by content repository managed objects to store attribute
-- values with that objects revision_id (as opposed to the ams
-- managed revision_id). This is useful when permissions are not
-- set to hide certain attributes from users. If the attributes
-- for an object are restrict based on permissions the ams_object
-- container is preferred since it is made to deal with the
-- retrieval and display of this more complex form of content
-- revision. Note, this proc does not mark previous revisions as
-- superseeded, so if another objects revisions are used you must
-- make sure that the attribute has not already been entered for
-- this particular revision.



create or replace function ams_attribute_value__new (integer,integer,integer,integer,integer,timestamptz,text,varchar)
returns integer as '
declare
        p_revision_id                alias for $1;
        p_ams_attribute_id           alias for $2;
        p_option_map_id              alias for $3;
        p_address_id                 alias for $4;
        p_number_id                  alias for $5;
        p_time                       alias for $6;
        p_value                      alias for $7;
        p_value_mime_type            alias for $8;
begin
        insert into ams_attribute_values
        (revision_id,ams_attribute_id,option_map_id,address_id,number_id,time,value,value_mime_type)
        values
        (p_revision_id,p_ams_attribute_id,p_option_map_id,p_address_id,p_number_id,p_time,p_value,p_value_mime_type);

        return 0;
end;' language 'plpgsql';



create or replace function ams_attribute_value__superseed (integer,integer,integer)
returns integer as '
declare
        p_revision_id                alias for $1;
        p_ams_attribute_id           alias for $2;
        p_ams_object_id              alias for $3;
begin

        update ams_attribute_values
           set superseed_ams_attribute_id = p_revision_id
         where ams_attribute_id = p_ams_attribute_id
           and superseed_revision_id is null
           and revision_id in ( select revision_id
                                  from cr_revisions
                                 where item_id = p_ams_object_id );


        return 0;
end;' language 'plpgsql';



create or replace function ams_attribute_value__save (integer,integer,integer,integer,integer,timestamptz,text,varchar)
returns integer as '
declare
        p_revision_id                alias for $1;
        p_ams_attribute_id           alias for $2;
        p_option_map_id              alias for $3;
        p_address_id                 alias for $4;
        p_number_id                  alias for $5;
        p_time                       alias for $6;
        p_value                      alias for $7;
        p_value_mime_type            alias for $8;
        v_ams_object_id              integer;
        v_count                      integer;
        v_option_map_id              integer;
        v_address_id                 integer;
        v_number_id                  integer;
        v_time                       timestamptz;
        v_value                      text;
        v_value_mime_type            varchar;
        v_insert_new_p               boolean;
        v_duplicate_p                boolean;
begin

        v_ams_object_id := item_id from cr_revisions where revision_id = p_revision_id;

        v_count := count(*) from ams_attribute_values 
                           where superseed_revision_id is null
                             and revision_id in ( select revision_id
                                                    from cr_revisions
                                                   where item_id = v_ams_object_id );

        if v_count > 0 then
                select option_map_id,
                       address_id,
                       number_id,
                       time, 
                       value,
                       value_mime_type
                  into v_option_map_id,
                       v_address_id,
                       v_number_id,
                       v_time,
                       v_value,
                       v_value_mime_type
                  from ams_attribute_values
                 where ams_attribute_id = p_ams_attribute_id
                   and revision_id in ( select revision_id
                                          from cr_items
                                         where item_id = v_ams_object_id )
                   and superseed_revision_id is not null;

                if v_option_map_id != p_option_map_id
                        or v_address_id != p_address_id
                        or v_number_id != p_number_id
                        or v_time != p_time
                        or v_value != p_value
                then
                        PERFORM ams_attribute_value__superseed (
                                        p_revision_id,
                                        p_ams_attribute_id,
                                        v_ams_object_id
                        );
                        
                        v_duplicate_p := ''f'';
                else
                        v_duplicate_p := ''t'';

                end if;
        else
                v_duplicate_p := ''f'';
        end if;

        
        if not v_duplicate_p then
                -- we know that this is not duplicate

                if p_option_map_id is not null
                   or p_address_id is not null
                   or p_number_id is not null
                   or p_time is not null
                   or p_value is not null
                then
                        -- there is a not null value to this attribute
                        PERFORM ams_attribute_value__new (
                                        p_revision_id,
                                        p_ams_attribute_id,
                                        p_option_map_id,
                                        p_address_id,
                                        p_number_id,
                                        p_time,
                                        p_value,
                                        p_value_mime_type
                        );
                end if;

        end if;


        return 0;
end;' language 'plpgsql';



------ Groups
--------------------------------------------------------------------



select define_function_args('ams_list__new','list_id,package_key,object_type,list_name,pretty_name,description,description_mime_type,creation_date,creation_user,creation_ip,context_id');

create or replace function ams_list__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,integer,varchar,integer)
returns integer as '
declare
        p_list_id               alias for $1;
        p_package_key           alias for $2;
        p_object_type           alias for $3;
        p_list_name             alias for $4;
        p_pretty_name           alias for $5;
        p_description           alias for $6;
        p_description_mime_type alias for $7;
        p_creation_date         alias for $8;
        p_creation_user         alias for $9;
        p_creation_ip           alias for $10;
        p_context_id            alias for $11;
        v_list_id               integer;
begin

        v_list_id := acs_object__new (
                p_list_id,
                ''ams_list'',
                p_creation_date,
                p_creation_user,
                P_creation_ip,
                p_context_id
        );

        insert into ams_lists
        (list_id,package_key,object_type,list_name,pretty_name,description,description_mime_type)
        values
        (v_list_id,p_package_key,p_object_type,p_list_name,p_pretty_name,p_description,p_description_mime_type);

        return v_list_id;
end;' language 'plpgsql';

create or replace function ams_list__attribute_map (integer,integer,integer,boolean,varchar)
returns integer as '
declare
        p_list_id            alias for $1;
        p_ams_attribute_id   alias for $2;
        p_sort_order         alias for $3;
        p_required_p         alias for $4;
        p_section_heading    alias for $5;
        v_sort_order         integer;
begin

        if p_sort_order is null then
           v_sort_order := nextval(''ams_list_attribute_sort_order_seq'');
        else
           v_sort_order := p_sort_order;
        end if;

        delete from ams_list_attribute_map
         where ams_attribute_id = p_ams_attribute_id
           and list_id = p_list_id;

        insert into ams_list_attribute_map
                (list_id,ams_attribute_id,sort_order,required_p,section_heading)
        values
                (p_list_id,p_ams_attribute_id,v_sort_order,p_required_p,p_section_heading);

        return ''1'';
end;' language 'plpgsql';














------ Postal Address
--------------------------------------------------------------------

-- postal_type needs to be entered here at the end... this is a hack
-- CASE WHEN postal_type is not null THEN postal_type ELSE '''' END || ''}''
-- it needs to be consistently recast as an integer

create or replace function ams_attribute__postal_address_string (integer)
returns varchar as '
declare
        p_address_id    alias for $1;
        v_name          text;
begin

        if p_address_id is not null then
                v_name :=  ''{'' || 
                           CASE WHEN delivery_address is not null THEN delivery_address ELSE '''' END || ''} {'' || 
                           CASE WHEN municipality is not null THEN municipality ELSE '''' END || ''} {'' || 
                           CASE WHEN region is not null THEN region ELSE '''' END  || ''} {'' || 
                           CASE WHEN postal_code is not null THEN postal_code ELSE '''' END  || ''} {'' ||
                           CASE WHEN country_code is not null THEN country_code::varchar ELSE '''' END  || ''} {'' ||
                           CASE WHEN additional_text is not null THEN additional_text ELSE '''' END || ''} {}''
                      from postal_addresses
                     where address_id = p_address_id;
        else
                v_name := NULL;
        end if;

        return v_name;
end;' language 'plpgsql';



------ Telecom Number
--------------------------------------------------------------------


create or replace function ams_attribute__telecom_number_string (integer)
returns varchar as '
declare
        p_number_id     alias for $1;
        v_name          text;
begin

        if p_number_id is not null then
                v_name :=  ''{'' || 
                  CASE WHEN itu_id is not null THEN itu_id ELSE '''' END || ''} {'' || 
                  CASE WHEN national_number is not null THEN national_number ELSE '''' END || ''} {'' || 
                  CASE WHEN area_city_code is not null THEN area_city_code ELSE '''' END  || ''} {'' || 
                  CASE WHEN subscriber_number is not null THEN subscriber_number ELSE '''' END  || ''} {'' ||
                  CASE WHEN extension is not null THEN extension ELSE '''' END  || ''} {'' ||
                  CASE WHEN sms_enabled_p is not null THEN CASE WHEN sms_enabled_p THEN ''1'' ELSE ''0'' END ELSE '''' END || ''} {'' ||
                  CASE WHEN best_contact_time is not null THEN best_contact_time ELSE '''' END || ''} {'' ||
                  CASE WHEN location is not null THEN location ELSE '''' END || ''} {'' ||
                  CASE WHEN phone_type_id is not null THEN phone_type_id ELSE '''' END || ''}''
             from telecom_numbers
             where number_id = p_number_id;
        else
                v_name := NULL;
        end if;

        return v_name;
end;' language 'plpgsql';

