-- 创建消息表
drop table lucene$message;
create table lucene$message(
    me_id number primary key,
    me_table_name varchar(64) not null,
    me_method_type varchar(6) not null,
    me_data_id number not null,
    me_data varchar(1024),
    me_priority number(2) default 0,
    me_retry_count number(1) default 0,
    me_create_time date not null
);

-- 创建消息历史表
drop table lucene$message_history;
create table lucene$message_history(
    mh_id number primary key,
    mh_dequeue_time date not null,
    me_id number not null,
    me_table_name varchar(64) not null,
    me_method_type varchar(6) not null,
    me_data_id number not null,
    me_data varchar(1024),
    me_priority number(2) default 0,
    me_create_time date not null
);

-- 创建序列
drop sequence lucene_message_seq;
create sequence lucene_message_seq
start with 1
increment by 1
maxvalue 9999999999999999999999999999;

drop sequence lucene_message_history_seq;
create sequence lucene_message_history_seq
start with 1
increment by 1
maxvalue 9999999999999999999999999999;

-- 创建存储过程 入队消息
create or replace procedure enqueue_lucene_message(v_table_name in varchar, v_method_type in varchar, v_data_id in number, v_data in varchar, v_priority in number) is
begin
	insert into lucene$message (me_id, me_table_name, me_method_type, me_data_id, me_data, me_priority, me_create_time) values(lucene_message_seq.nextval, v_table_name, v_method_type, v_data_id, v_data, v_priority, sysdate());
end enqueue_lucene_message;

-- 创建存储过程 出队消息
create or replace procedure dequeue_lucene_message(v_id in number) is
begin
	insert into lucene$message_history (mh_id, mh_dequeue_time, me_id, me_table_name, me_method_type, me_data_id, me_data, me_priority, me_create_time) select lucene_message_history_seq.nextval, sysdate(), me_id, me_table_name, me_method_type, me_data_id, me_data, me_priority, me_create_time from lucene$message where me_id = v_id;
	delete from lucene$message where me_id = v_id;
end dequeue_lucene_message;

-- ----------------------------
-- Triggers structure for table AC$US$DETAIL
-- ----------------------------
CREATE or replace trigger lucene_ac_us_detail
after insert or update of name, shortname, id, industry, adminname, profession, tags or delete on AC$US$DETAIL
for each row
declare
  v_table_name varchar(64) default 'PURC$CHANGES';
  v_method_type varchar(6) default 'insert';
  v_data_id number;
  v_data varchar(1024) default null;
  v_priority number(2) default 1;
begin
  if inserting then
    v_data_id := :new.id;
  elsif updating then
    v_data_id := :old.id;
  elsif deleting then
    v_data_id := :old.id;
  end if;
  enqueue_lucene_message(v_table_name, v_method_type, v_data_id, v_data, v_priority);
end;