-- Classified Ads Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-10-08
-- @cvs-id $Id: attributes-create.sql,v 1.1 2003/07/16 04:52:31 rmello Exp $
--

-- create the table
create table ca_attributes (
    attribute_id        integer
                        constraint ca_attributes_fk
                        references acs_attributes
                        constraint ca_attributes_nn
                        not null
                        constraint cr_ca_attributes_pk
                        primary key,
    package_id          integer
                        constraint ca_attributes_package_id_fk
                        references acs_objects,
    sitewide_p          char(1)
                        default 'f'
                        constraint ca_attributes_sitewide_p_nn
                        not null
                        constraint ca_attributes_sitewide_p_ck
                        check (sitewide_p in ('t','f')),
                        constraint ca_attributes_un
                        unique(attribute_id,package_id)
);


create table ca_attribute_values (
    object_id           integer
                        constraint cav_object_id_fk
                        references acs_objects
                        constraint cav_object_id_nn
                        not null,
    attribute_id        integer
                        constraint ca_attribute_values_fk
                        references ca_attributes
                        constraint ca_attributes_values_nn
                        not null,
    attr_value          text,
                        constraint ca_attribute_values_un
                        unique(object_id,attribute_id)
);


create table ca_attribute_widgets (
    widget              varchar(100)
                        constraint constraint_ca_widgets_pk
                        primary key
);

create table ca_attribute_widget_values (
    attribute_id        integer
                        constraint ca_attribute_widget_values_pk
                        primary key
                        constraint ca_attribute_widget_values_fk
                        references acs_attributes,
    widget	            varchar(100)
                        constraint ca_awv_widget_fk
                        references ca_attribute_widgets
                        constraint ca_awv_widget_nn
                        not null,
    label               varchar(200),
    html_options        varchar(400),
    select_options      text,
    validate            text,
    optional_p          char(1)
                        default 'f'
                        constraint ca_awv_optional_p_nn
                        not null
                        constraint ca_awv_optional_p_ck
                        check (optional_p in ('t','f')),
    enabled_p           char(1)
                        default 't'
                        constraint ca_awv_enabled_p_nn
                        not null
                        constraint ca_awv_enabled_p_ck
                        check (enabled_p in ('t','f'))
);



create function inline_0 ()
returns integer as'
begin

    insert into ca_attribute_widgets (widget) values (''checkbox'');
    insert into ca_attribute_widgets (widget) values (''date'');
    insert into ca_attribute_widgets (widget) values (''hidden'');
    insert into ca_attribute_widgets (widget) values (''multiselect'');
    insert into ca_attribute_widgets (widget) values (''radio'');
    insert into ca_attribute_widgets (widget) values (''select'');
    insert into ca_attribute_widgets (widget) values (''text'');
    insert into ca_attribute_widgets (widget) values (''textarea'');

    return null;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();