-- Classified Ads Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-10-08
-- @cvs-id $Id: attributes-create.sql,v 1.7 2003/11/07 20:13:36 tilmanns 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,
    --
    -- RBM: We have keyword_id here so we can have per-category
    --      custom fields. Selling books requires ISBN, but motorcycles
    --      requires something else.
    --
    keyword_id          integer
                        constraint ca_attributes_keyword_id_nn
			not null
                        constraint ca_attributes_keyword_id_fk
                        references cr_keywords(keyword_id),
    constraint ca_attributes_un unique(attribute_id, keyword_id)
);

CREATE INDEX ca_attributes_keyword_id_idx 
ON ca_attributes( keyword_id );

--
-- RBM: Shouldn't attr_value be NOT NULL?
--
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
                        constraint ca_awv_attrbute_id_nn
                        not null,
    widget	            varchar(100)
                        constraint ca_awv_widget_fk
                        references ca_attribute_widgets
                        constraint ca_awv_widget_nn
                        not null,
    label               varchar(200),
    html_options        text,
    --
    -- RBM: extra fomr builder options like help, help_text, etc.
    -- We give the admin a nice UI to abstract this.
    --
    extra_options	text,
    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 (''currency'');
    insert into ca_attribute_widgets (widget) values (''richtext'');
    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();