-- customer-service-create.sql
--
-- @author Dekka Corp.
-- @ported from OpenACS ecommerce package
-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
-- @cvs-id
--

--------- customer service --------------------

-- create sequence eccs_issue_id_seq;
-- create view eccs_issue_id_sequence as select nextval('eccs_issue_id_seq') as nextval;
-- create sequence eccs_action_id_seq;
-- create view eccs_action_id_sequence as select nextval('eccs_action_id_seq') as nextval;
-- create sequence eccs_interaction_id_seq;
-- create view eccs_interaction_id_sequence as select nextval('eccs_interaction_id_seq') as nextval;
-- create sequence eccs_user_ident_id_seq;
-- create view eccs_user_ident_id_sequence as select nextval('eccs_user_ident_id_seq') as nextval;
-- 
-- -- this contains the bits of info a cs rep uses to identify
-- -- a user
-- -- often user_id is not known and the customer service rep
-- -- will have to get other info in order to identify the user
-- create table eccs_user_identification (
--         user_identification_id  integer not null primary key,
--         date_added      	timestamptz,
--         user_id         	integer references users,
--         email           	varchar(100),
--         first_names     	varchar(100),
--         last_name       	varchar(100),
--         -- this is varchar(80) in community-core.sql, so I'll be consistent
--         postal_code     	varchar(80),
--         other_id_info   	varchar(2000)
-- );
-- 
-- -- should index everything because this all columns may potentially
-- -- be searched through every time a new interaction is recorded
-- create index eccs_user_ident_by_user_id on eccs_user_identification(user_id);
-- create index eccs_user_ident_by_email on eccs_user_identification(email);
-- create index eccs_user_ident_by_first_names on eccs_user_identification(first_names);
-- create index eccs_user_ident_by_last_name on eccs_user_identification(last_name);
-- create index eccs_user_ident_by_postal_code on eccs_user_identification(postal_code);
-- 
-- 
-- -- puts date_added into eccs_user_identification if it's missing
-- create function eccs_user_identificate_date_tr ()
-- returns trigger as '
-- begin
--         IF new.date_added is null THEN
--                 new.date_added := now();
--         END IF;
-- 	return new;
-- end;' language 'plpgsql';
-- 
-- create trigger eccs_user_identificate_date_tr
-- after insert on eccs_user_identification
-- for each row execute procedure eccs_user_identificate_date_tr ();
-- 
-- 
-- create table eccs_customer_serv_interactions (
--         interaction_id          integer not null primary key,
--         customer_service_rep    integer references users,
--         user_identification_id  integer not null 
-- 				    references eccs_user_identification,
--         interaction_date        timestamptz,
--         interaction_originator  varchar(20) not null, -- e.g. customer, customer-service-rep, automatic
--         interaction_type        varchar(30) not null, -- e.g. email, phone_call
--         -- will be filled in if the customer-originated interaction is
--         -- an email
--         interaction_headers     varchar(4000)
-- );
-- 
-- create index eccs_csin_by_user_ident_id on eccs_customer_serv_interactions(user_identification_id);
-- 
-- -- gilbertw - used the code in OpenACS 3.2.5 as a reference
-- create function eccs_cs_interaction_inserts ()
-- returns trigger as '
-- begin
--  IF new.interaction_date is null THEN 
--     new.interaction_date := now();
--  END IF;
--  return new;
-- end;' language 'plpgsql';
-- 
-- create trigger eccs_cs_interaction_inserts
-- after insert on eccs_customer_serv_interactions
-- for each row execute procedure eccs_cs_interaction_inserts ();
-- 
-- create view eccs_customer_service_reps
-- as
-- select * from cc_users 
-- where user_id in (select customer_service_rep 
-- 	from eccs_customer_serv_interactions)
--    or user_id in (select issued_by from eccs_gift_certificates_issued);
-- 
-- create table eccs_customer_service_issues (
--         issue_id        integer not null primary key,
--         user_identification_id  integer not null references eccs_user_identification,
--         -- may be null if this issue isn't associated with an order
--         order_id        integer references eccs_orders,
--         -- may be null if this issue isn't associated with a gift certificate
--         gift_certificate_id     integer references eccs_gift_certificates,
--         open_date       timestamptz not null,
--         close_date      timestamptz,
--         -- customer service reps who closed the issue
--         closed_by       integer references users,
--         -- we never really delete issues
--         deleted_p       boolean default 'f'
-- );
-- 
-- create index eccs_csi_by_user_ident_id on eccs_customer_service_issues(user_identification_id);
-- create index eccs_csi_by_open_date on eccs_customer_service_issues(open_date);
-- 
-- -- because an issue can have more than one issue_type
-- create table eccs_cs_issue_type_map (
--         issue_id        integer not null references eccs_customer_service_issues,
--         issue_type      varchar(40) not null -- e.g. billing, web site
-- );
-- 
-- create index eccs_csitm_by_issue_id on eccs_cs_issue_type_map(issue_id);
-- create index eccs_csitm_by_issue_type on eccs_cs_issue_type_map(issue_type);
-- 
-- -- gilbertw - used code OpenACS 3.2.5 as a reference
-- -- removed INSERTING
-- create function eccs_cs_issue_inserts ()
-- returns trigger as '
-- begin
--  IF new.open_date is null THEN 
--     new.open_date := now();
--  END IF;
--  return new;
-- end;' language 'plpgsql';
-- 
-- create trigger eccs_cs_issue_inserts
-- after insert on eccs_customer_service_issues
-- for each row execute procedure eccs_cs_issue_inserts ();
-- 
-- create table eccs_customer_service_actions (
--         action_id               integer not null primary key,
--         issue_id                integer not null references eccs_customer_service_issues,
--         interaction_id          integer not null references eccs_customer_serv_interactions,
--         action_details          varchar(4000),
--         follow_up_required      varchar(4000)
-- );
--         
-- create index eccs_csa_by_issue on eccs_customer_service_actions(issue_id);
-- 
-- create table eccs_cs_action_info_used_map (
--         action_id               integer not null references eccs_customer_service_actions,
--         info_used               varchar(100) not null
-- );
-- 
-- create index eccs_csaium_by_action_id on eccs_cs_action_info_used_map(action_id);
-- create index eccs_csaium_by_info_used on eccs_cs_action_info_used_map(info_used);
-- 
-- -- this table contains picklist choices for the customer service data
-- -- entry people
-- 
-- create sequence eccs_picklist_item_id_seq;
-- create view eccs_picklist_item_id_sequence as select nextval('eccs_picklist_item_id_seq') as nextval;
-- 
-- create table eccs_picklist_items (
--         picklist_item_id        integer not null primary key,
--         -- pretty, human-readable
--         picklist_item           varchar(100),
--         -- which picklist this item is in
--         picklist_name           varchar(100),
--         sort_key                numeric,
--         last_modified           timestamptz not null,
--         last_modifying_user     integer not null references users,
--         modified_ip_address     varchar(20) not null
-- );
-- 
-- create table eccs_picklist_items_audit (
--         picklist_item_id        integer,
--         picklist_item           varchar(100),
--         picklist_name           varchar(100),
--         sort_key                numeric,
--         last_modified           timestamptz,
--         last_modifying_user     integer,
--         modified_ip_address     varchar(20),
--         delete_p                boolean default 'f'
-- );
-- 
-- create function eccs_picklist_items_audit_tr ()
-- returns trigger as '
-- begin
--         insert into eccs_picklist_items_audit (
--         picklist_item_id, picklist_item,
--         picklist_name, sort_key,
--         last_modified,
--         last_modifying_user, modified_ip_address
--         ) values (
--         old.picklist_item_id, old.picklist_item,
--         old.picklist_name, old.sort_key,
--         old.last_modified,
--         old.last_modifying_user, old.modified_ip_address
--         );
-- 	return new;
-- end;' language 'plpgsql';
-- 
-- create trigger eccs_picklist_items_audit_tr
-- after update or delete on eccs_picklist_items
-- for each row execute procedure eccs_picklist_items_audit_tr ();
-- 
-- -- Canned responses for customer support
-- create sequence eccs_canned_response_id_seq;
-- create view eccs_canned_response_id_sequence as select nextval('eccs_canned_response_id_seq') as nextval;
-- 
-- create table eccs_canned_responses (
--         response_id     integer not null primary key,
--         one_line        varchar(100) not null,
--         response_text   varchar(4000) not null
-- );
-- 
-- -----------------------------------------------
-- 
-- -- templates 1-6 are pre-defined (see the insert statements in 
-- -- ecommerce-defaults.sql)
-- -- the wording of each can be changed at [eccs_url_concat [eccs_url] /admin]/email-templates/
-- create sequence eccs_email_template_id_seq start 7;
-- create view eccs_email_template_id_sequence as select nextval('eccs_email_template_id_seq') as nextval;
-- 
-- create table eccs_email_templates (
--         email_template_id       integer not null primary key,
--         title                   varchar(100),
--         subject                 varchar(200),
--         message                 varchar(4000),
--         -- this lists the variable names that customer service can
--         -- use in this particular email -- for their info only
--         variables               varchar(1000),
--         -- for informational purposes only, when the email is
--         -- sent
--         when_sent               varchar(1000),
--         -- for customer service issues, this is a tcl list of all
--         -- the issue_types that should be inserted into 
--         -- eccs_cs_issue_type_map for the issue that will be created
--         -- when the message is sent
--         issue_type_list         varchar(100),
--         last_modified           timestamptz not null,
--         last_modifying_user     integer not null references users,
--         modified_ip_address     varchar(20) not null
-- );
-- 
-- create table eccs_email_templates_audit (
--         email_template_id       integer,
--         title                   varchar(100),
--         subject                 varchar(200),
--         message                 varchar(4000),
--         variables               varchar(1000),
--         when_sent               varchar(1000),
--         issue_type_list         varchar(100),
--         last_modified           timestamptz,
--         last_modifying_user     integer,
--         modified_ip_address     varchar(20),
--         delete_p                boolean default 'f' 
-- );
-- 
-- create function eccs_email_templates_audit_tr ()
-- returns trigger as '
-- begin
--         insert into eccs_email_templates_audit (
--         email_template_id, title,
--         subject, message,
--         variables, when_sent,
--         issue_type_list,
--         last_modified,
--         last_modifying_user, modified_ip_address
--         ) values (
--         old.email_template_id, old.title,
--         old.subject, old.message,
--         old.variables, old.when_sent,
--         old.issue_type_list,
--         old.last_modified,
--         old.last_modifying_user, old.modified_ip_address
--         );
-- 	return new;
-- end;' language 'plpgsql';
-- 
-- create trigger eccs_email_templates_audit_tr
-- after update or delete on eccs_email_templates
-- for each row execute procedure eccs_email_templates_audit_tr ();
-- 
-- -- 6 default templates are predefined ecommerce-defaults.
-- -- The templates are
-- -- used in procedures which send out the email, so the template_ids
-- -- shouldn t be changed, although the text can be edited at
-- -- [eccs_url_concat [eccs_url] /admin]/email-templates/
-- --
-- -- email_template_id    used for
-- -- -----------------    ---------
-- --      1               new order
-- --      2               order shipped
-- --      3               delayed credit denied
-- --      4               new gift certificate order
-- --      5               gift certificate recipient
-- --      6               gift certificate order failure
-- 
-- 
-- -- users can sign up for mailing lists based on category, subcategory,
-- -- or subsubcategory (the appropriate level of categorization on which
-- -- to base mailing lists depends on how the site administrator has
-- -- set up their system)
-- -- when the user is signed up for a subsubcategory list, the subcategory_id
-- -- and category_id are also filled in (which makes it easier to refer
-- -- to the mailing list later).
-- -- cat stands for categorization
-- 
-- -- this table needs to be integrated with contacts and categories package and maybe spam users package
-- 
-- create table eccs_cat_mailing_lists (
--         user_id                 integer not null references users,
--         category_id             integer references eccs_categories,
--         subcategory_id          integer references eccs_subcategories,
--         subsubcategory_id       integer references eccs_subsubcategories
-- );
-- 
-- create index eccs_cat_mailing_list_idx on eccs_cat_mailing_lists(user_id);
-- create index eccs_cat_mailing_list_idx2 on eccs_cat_mailing_lists(category_id);
-- create index eccs_cat_mailing_list_idx3 on eccs_cat_mailing_lists(subcategory_id);
-- create index eccs_cat_mailing_list_idx4 on eccs_cat_mailing_lists(subsubcategory_id);
-- 
-- 
-- 
-- create sequence eccs_spam_id_seq;
-- create view eccs_spam_id_sequence as select nextval('eccs_spam_id_seq') as nextval;
-- 
-- -- this table needs to be integrated with contacts package
-- create table eccs_spam_log (
--         spam_id                 integer not null primary key,
--         spam_date               timestamptz,
--         spam_text               varchar(4000),
--         -- the following are all criteria used in choosing the users to be spammed
--         mailing_list_category_id        integer references eccs_categories,
--         mailing_list_subcategory_id     integer references eccs_subcategories,
--         mailing_list_subsubcategory_id  integer references eccs_subsubcategories,
--         user_class_id                   integer references eccs_user_classes,
--         product_id                      integer references eccs_products,
--         last_visit_start_date           timestamptz,
--         last_visit_end_date             timestamp
-- );
-- 
-- create index eccs_spam_log_by_cat_mail_idx on eccs_spam_log (mailing_list_category_id);
-- create index eccs_spam_log_by_cat_mail_idx2 on eccs_spam_log (mailing_list_subcategory_id);
-- create index eccs_spam_log_by_cat_mail_idx3 on eccs_spam_log (mailing_list_subsubcategory_id);
-- create index eccs_spam_log_by_user_cls_idx on eccs_spam_log (user_class_id);
-- create index eccs_spam_log_by_product_idx on eccs_spam_log (product_id);
-- 
-- 
-- 
-- 
-- 
