pragma foreign_keys = ON;

begin;

/*********Drop / delete existing tables*********/
delete from jsharmony.note;
delete from jsharmony.cust_user_role;
delete from jsharmony.cust_user;
delete from jsharmony.code where code_name='sample_code';
delete from jsharmony.code where code_name='sale_sts';
delete from jsharmony.code where code_name='cust_sts';
delete from jsharmony.code where code_name='cust_flag_type';
delete from jsharmony.sys_menu_role where menu_id in (300,30001);
delete from jsharmony.cust_menu_role where menu_id in (280001);
delete from jsharmony.menu where menu_id in (300,30001,280001);

drop view if exists v_sale;
drop view if exists v_cust_contact;
drop view if exists v_cust;


/*********soundex*********/
create table soundex (
  soundex_id integer primary key autoincrement not null,
  table_name text not null,
  field_name text not null,
  table_id text not null,
  soundex_word text not null,
  soundex_val text not null
);

/*********code_cust_sts*********/
create_code('code_cust_sts');
INSERT INTO jsharmony_code (code_name, code_desc) VALUES ('cust_sts', 'Customer Status');
insert into code_cust_sts(code_seq,code_val,code_txt,code_code,code_end_dt) values (1,'DEACTIVE','Deactivated','A','2017-11-19');
insert into code_cust_sts(code_seq,code_val,code_txt,code_code) values (2,'ACTIVE','Active','A');
insert into code_cust_sts(code_seq,code_val,code_txt,code_code) values (3,'INACTIVE','Inactive','A');
insert into code_cust_sts(code_seq,code_val,code_txt,code_code) values (4,'CREDITH','Credit Hold','A');
insert into code_cust_sts(code_seq,code_val,code_txt,code_code) values (6,'CLOSED','Closed','C');
insert into code_cust_sts(code_seq,code_val,code_txt,code_code) values (5,'HOLD','Hold','C');

/*********code_cust_flag_type*********/
create_code('code_cust_flag_type');
INSERT INTO jsharmony_code (code_name, code_desc) VALUES ('cust_flag_type', 'Customer Flag Type');
insert into code_cust_flag_type(code_seq,code_val,code_txt) values (1,'PREF','Preferred');
insert into code_cust_flag_type(code_seq,code_val,code_txt) values (2,'CRDTRISK','Credit Risk');
insert into code_cust_flag_type(code_seq,code_val,code_txt) values (3,'PAPER','Paper-only');
insert into code_cust_flag_type(code_seq,code_val,code_txt,code_end_dt) values (4,'DEDIREP','Dedicated Rep','2017-11-19' );

/*********code_order_sts*********/
create_code('code_sale_sts');
INSERT INTO jsharmony_code (code_name, code_desc) VALUES ('sale_sts', 'Sales Order Status');
insert into code_sale_sts(code_seq,code_val,code_txt) values (1,'PENDING','Pending');
insert into code_sale_sts(code_seq,code_val,code_txt) values (2,'SHIPPED','Shipped');
insert into code_sale_sts(code_seq,code_val,code_txt) values (3,'COMPLETED','Completed');
insert into code_sale_sts(code_seq,code_val,code_txt) values (4,'CANCELED','Canceled');

/*********sample_code*********/
INSERT INTO jsharmony_code (code_name, code_desc) VALUES ('sample_code', 'Sample Code');

/*********cust*********/
create table cust (
  cust_id integer primary key autoincrement not null,
  cust_sts text not null default 'ACTIVE',
  cust_name text not null unique,
  cust_email text,
  cust_ein blob,
  cust_einhash blob,
  cust_doc_ext text,
  cust_doc_size integer,
  cust_doc_uptstmp text,
  cust_doc_upuser text,
  cust_desc text,
  cust_portal_welcome_txt text,
  cust_start_dt text,
  cust_overdue_amt text,
  cust_parent_id integer null,
  cust_entry_user text,
  cust_entry_tstmp text,
  cust_update_dt text,
  foreign key (cust_sts) references code_cust_sts(code_val),
  foreign key (cust_parent_id) references cust(cust_id)
);
create trigger cust_after_insert after insert on cust
begin
  update cust set 
    cust_entry_user  = (select context from jsharmony_meta limit 1),
    cust_entry_tstmp = datetime('now','localtime')
    where rowid = new.rowid\;
  delete from soundex where table_name='cust' and table_id=NEW.cust_id\;
  update jsharmony_meta set jsexec = '{ "function": "soundex", "source": "(select cust_name from cust where cust_id='||NEW.cust_id||')", "dest": "insert into soundex(table_name,field_name,table_id,soundex_word,soundex_val) values(''cust'',''cust_name'','||NEW.cust_id||',(select cust_name from cust where cust_id='||NEW.cust_id||'),%%%SOUNDEX%%%)" }'\;
end;
create trigger cust_after_update after update on cust
begin
  delete from soundex where table_name='cust' and table_id=OLD.cust_id\;
  update jsharmony_meta set jsexec = '{ "function": "soundex", "source": "(select cust_name from cust where cust_id='||NEW.cust_id||')", "dest": "insert into soundex(table_name,field_name,table_id,soundex_word,soundex_val) values(''cust'',''cust_name'','||NEW.cust_id||',(select cust_name from cust where cust_id='||NEW.cust_id||'),%%%SOUNDEX%%%)" }'\;
end;
insert into cust(cust_id,cust_sts,cust_name,cust_desc,cust_start_dt,cust_overdue_amt,cust_email,cust_portal_welcome_txt,cust_doc_ext,cust_doc_size,cust_doc_uptstmp,cust_doc_upuser) values (1,'DEACTIVE','ACME Industries','Industrial Fixtures','2019-05-01','1234567.12','user@example.com','<b>Sample</b> HTML<br/><img src="data:image/png\;base64,[encoded data, 5120 characters]">','.jpg',14434,datetime('now', 'localtime'),'system');
insert into cust(cust_id,cust_sts,cust_name,cust_desc,cust_start_dt,cust_overdue_amt,cust_parent_id) values (2,'ACTIVE','Creative Engineering','','2019-05-01','0',1);
insert into cust(cust_id,cust_sts,cust_name,cust_desc,cust_start_dt,cust_overdue_amt,cust_doc_ext,cust_doc_size,cust_doc_uptstmp,cust_doc_upuser) values (3,'ACTIVE','Coffee Brothers','',null,'90.9','.jpg',521480,'2019-05-01','system');

/*********cust_ext*********/
create table cust_ext (
  cust_id integer primary key not null,
  cust_ext_desc text null,
  foreign key (cust_id) references cust(cust_id)
);
insert into cust_ext(cust_id,cust_ext_desc) values (1,'Extended Description');


/*********cust_addr*********/
create table cust_addr (
  cust_addr_id integer primary key autoincrement not null,
  cust_id integer not null,
  cust_addr_line1 text null,
  cust_addr_line2 text null,
  cust_addr_city text not null,
  cust_addr_state text not null,
  cust_addr_zip text null,
  cust_addr_country text not null,
  cust_addr_type text null,
  foreign key (cust_id) references cust(cust_id),
  foreign key (cust_addr_country) references jsharmony_code_country(code_val),
  foreign key (cust_addr_country, cust_addr_state) references jsharmony_code2_country_state(code_val1, code_val2)
);
insert into cust_addr(cust_id,cust_addr_line1,cust_addr_city,cust_addr_state,cust_addr_zip,cust_addr_country,cust_addr_type) values (1,'123 Test St','Chicago','IL','60103','USA','BILLING');
insert into cust_addr(cust_id,cust_addr_line1,cust_addr_city,cust_addr_state,cust_addr_zip,cust_addr_country,cust_addr_type) values (1,'234 Main St','Chicago','IL','60103','USA','SHIPPING');
insert into cust_addr(cust_id,cust_addr_line1,cust_addr_city,cust_addr_state,cust_addr_zip,cust_addr_country,cust_addr_type) values (2,'111 S State St','Chicago','IL','60103','USA','BILLING');

/*********cust_contact*********/
create table cust_contact (
  cust_contact_id integer primary key autoincrement not null,
  cust_id integer not null,
  cust_contact_name text not null,
  cust_contact_title text,
  cust_contact_phone text,
  cust_contact_email text,
  cust_addr_id_shipping integer null,
  cust_addr_id_billing integer null,
  unique(cust_id,cust_contact_name),
  foreign key (cust_id) references cust(cust_id),
  foreign key (cust_addr_id_shipping) references cust_addr(cust_addr_id),
  foreign key (cust_addr_id_billing) references cust_addr(cust_addr_id)
);
insert into cust_contact(cust_id,cust_contact_name,cust_contact_title) values (1,'John Smith','General Manager');
insert into cust_contact(cust_id,cust_contact_name,cust_contact_title) values (1,'Jessie Blane','CFO');
insert into cust_contact(cust_id,cust_contact_name,cust_contact_title) values (2,'Jason Gant','Engineering Manager');

/*********cust_flag*********/
create table cust_flag (
  cust_flag_id integer primary key autoincrement not null,
  cust_id integer not null,
  cust_flag_type text not null,
  unique(cust_id,cust_flag_type),
  foreign key (cust_id) references cust(cust_id),
  foreign key (cust_flag_type) references code_cust_flag_type(code_val)
);
insert into cust_flag(cust_id,cust_flag_type) values (1,'PREF');
insert into cust_flag(cust_id,cust_flag_type) values (1,'PAPER');
insert into cust_flag(cust_id,cust_flag_type) values (1,'DEDIREP');
insert into cust_flag(cust_id,cust_flag_type) values (2,'CRDTRISK');

/*********item*********/
create table item (
  item_id integer primary key autoincrement not null,
  cust_id integer not null,
  item_code text not null,
  item_name text not null,
  unique(cust_id,item_code),
  foreign key (cust_id) references cust(cust_id)
);
insert into item(cust_id,item_code,item_name) values (1,'A392','Maier C6');
insert into item(cust_id,item_code,item_name) values (1,'A214','Maier ML-26');
insert into item(cust_id,item_code,item_name) values (2,'515-231','Milwaukee Portable Drill Press');

/*********sale*********/
create table sale (
  sale_id integer primary key autoincrement not null,
  cust_id integer not null,
  sale_contact integer,
  sale_sts text not null,
  sale_po text,
  sale_dt text,
  sale_desc text,
  foreign key (cust_id) references cust(cust_id),
  foreign key (sale_contact) references cust_contact(cust_contact_id),
  foreign key (sale_sts) references code_sale_sts(code_val)
);

create trigger sale_after_insert after insert on sale
begin
  select case when (NEW.sale_contact is not null) and not exists(select * from cust_contact where cust_contact_id=NEW.sale_contact and cust_id=NEW.cust_id) then raise(FAIL,'Application Error - Sales Order Contact must be a child record of the Sales Order Customer ID') end\;
end;
create trigger sale_after_update after update on sale
begin
  select case when (NEW.sale_contact is not null) and not exists(select * from cust_contact where cust_contact_id=NEW.sale_contact and cust_id=NEW.cust_id) then raise(FAIL,'Application Error - Sales Order Contact must be a child record of the Sales Order Customer ID') end\;
end;

insert into sale(cust_id,sale_contact,sale_sts,sale_po,sale_dt) values (1,NULL,'PENDING',null,datetime('now','localtime'));
insert into sale(cust_id,sale_contact,sale_sts,sale_po,sale_dt) values (2,3,'COMPLETED',null,datetime('now','localtime'));
insert into sale(cust_id,sale_contact,sale_sts,sale_po,sale_dt) values (2,NULL,'CANCELED','123 Test St',datetime('now','localtime'));
insert into sale(cust_id,sale_contact,sale_sts,sale_po,sale_dt) values (2,NULL,'PENDING',null,datetime('now','localtime'));
insert into sale(cust_id,sale_contact,sale_sts,sale_po,sale_dt) values (2,NULL,'PENDING',null,datetime('now','localtime'));

/*********order_line*********/
create table sale_line (
  sale_line_id integer primary key autoincrement not null,
  sale_id integer not null,
  sale_line_desc text not null,
  sale_line_amt number,
  sale_line_qty number,
  sale_line_tot number,
  foreign key (sale_id) references sale(sale_id)
);
insert into sale_line(sale_id,sale_line_desc,sale_line_amt,sale_line_qty,sale_line_tot) values (1,'Item 1',129.12,2,258.24);
insert into sale_line(sale_id,sale_line_desc,sale_line_amt,sale_line_qty,sale_line_tot) values (2,'Item 1',49.42,1,49.42);
insert into sale_line(sale_id,sale_line_desc,sale_line_amt,sale_line_qty,sale_line_tot) values (3,'Item 1',23.11,1,23.11);
insert into sale_line(sale_id,sale_line_desc,sale_line_amt,sale_line_qty,sale_line_tot) values (3,'Item 2',5.14,3,15.42);
insert into sale_line(sale_id,sale_line_desc,sale_line_amt,sale_line_qty,sale_line_tot) values (4,'Item 1',3.93,1,3.93);

/*********v_cust*********/
create view v_cust as
  select cust_id,cust_sts,cust_name,code_cust_sts.code_txt as cust_sts_txt,cust_einhash
    from cust
    left outer join code_cust_sts on cust.cust_sts = code_cust_sts.code_val;

/*********v_cust_contact*********/
create view v_cust_contact as
  select cust_contact_id,cust_contact_name,cust_contact_title,cust_contact_phone,cust_contact_email,cust_contact.cust_id,cust_name
    from cust_contact
    inner join cust on cust.cust_id=cust_contact.cust_id;

/*********v_sale*********/
create view v_sale as
  select
    sale_id, 
    sale.cust_id, 
    cust_name,
    sale_sts,
    code_sale_sts.code_txt sale_sts_txt,
    sale_po,
    sale_dt,
    sale_contact,
    sale.rowid rowid,
    ifnull((select sum(sale_line_tot) from sale_line where sale_line.sale_id = sale.sale_id),0) sale_tot
    from sale
    left outer join cust on sale.cust_id = cust.cust_id
    left outer join code_sale_sts on code_sale_sts.code_val = sale_sts;

/********category*********/
create table category (
  category_id integer primary key autoincrement not null,
  category_parent_id integer,
  category_name text,
  foreign key (category_parent_id) references category(category_id)
);
insert into category(category_id, category_parent_id, category_name) values(1, null,'(Root)');
insert into category(category_id, category_parent_id, category_name) values(2, 1,'Home');
insert into category(category_id, category_parent_id, category_name) values(4, 2,'Cooking');
insert into category(category_id, category_parent_id, category_name) values(5, 2,'Design Ideas');
insert into category(category_id, category_parent_id, category_name) values(3, 5,'Bath');
insert into category(category_id, category_parent_id, category_name) values(6, 5,'Kitchen');
insert into category(category_id, category_parent_id, category_name) values(7, 5,'Inspiration Photos');
insert into category(category_id, category_parent_id, category_name) values(8, 4,'Baking');
insert into category(category_id, category_parent_id, category_name) values(9, 4,'Vegan');

/*********all_types*********/
create table all_types (
  x_primary integer primary key autoincrement not null,
  x_boolean integer,
  x_bigint integer,
  x_integer integer,
  x_smallint integer,
  x_tinyint integer,
  x_decimal real,
  x_float real,
  x_date text,
  x_datetime text,
  x_time text,
  x_varchar text,
  x_varcharmax text,
  x_char text,
  x_binary blob,
  x_encascii blob,
  x_hash blob
);
insert into all_types(
    x_boolean,
    x_bigint,x_integer,x_smallint,x_tinyint,x_decimal,x_float,
    x_date,x_datetime,x_time,
    x_varchar,x_varcharmax,x_char,
    x_binary) 
  values (
  '1',
  20999999999,
  199999999,
  30999,
  254,
  999999.123456789,
  2.12345678901234e50,
  '1999-09-09',
  '1999-09-09 09:59:59.987654',
  '16:32:12.987654',
  '▶▶▶▶▶abcdefgABCDEFG',
  '▶▶▶▶▶[encoded data, 5006 characters]', 
  '▶▶▶▶▶abcdefABCDEF',
  X'123456789A'
);
insert into all_types(
    x_boolean,
    x_bigint,x_integer,x_smallint,x_tinyint,x_decimal,x_float,
    x_date,x_datetime,x_time,
    x_varchar,x_varcharmax,x_char,
    x_binary) 
  values (
  '0',
  1,
  1,
  1,
  1,
  1,
  1,
  '1999-09-09',
  '1999-09-09 09:59:59.987654',
  '16:32:12.987654',
  'Test1',
  'Test2', 
  'Test3',
  X'001100'
);
insert into all_types(
    x_boolean,
    x_bigint,x_integer,x_smallint,x_tinyint,x_decimal,x_float,
    x_date,x_datetime,x_time,
    x_varchar,x_varcharmax,x_char,
    x_binary) 
  values (
  '0',
  2,
  2,
  2,
  2,
  2,
  2,
  '1999-09-09',
  '1999-09-09 09:59:59.987654',
  '16:32:12.987654',
  'Test1',
  'Test2', 
  'Test3',
  X'001100'
);

/*********all_controls*********/
create table all_controls (
  x_primary integer primary key autoincrement not null,
  x_label text,
  x_html text,
  x_textbox text,
  x_textzoom text,
  x_dropdown text,
  x_date text,
  x_textarea text,
  x_hidden text,
  x_checkbox integer,
  x_password text,
  x_error text,
  x_customtextbox text,
  x_customdropdown text
);

create trigger all_controls_before_insert before insert on all_controls
begin
  select case when (NEW.x_error is not null) and (NEW.x_error = 'application_error') then raise(FAIL,'Application Error - Sample Application Error') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'application_warning') then raise(FAIL,'Application Warning - Sample Application Warning') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'execute_form') then raise(FAIL,'Execute Form - Sample Execute Form Message //<%=xmodel.namespace%>ModelDBValidation_ErrorPopup?action=update&x_primary=1&x_error_value=<%=xmodel.get("x_error")%>') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'db_error_standard') then raise(FAIL,'sample_db_error_standard') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'db_error_regex') then raise(FAIL,'sample_db_error_regex') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'unhandled') then raise(FAIL,'Sample unhandled database error') end\;
  select case when (NEW.x_error is not null) then raise(FAIL,'Application Error - Invalid value for x_error') end\;
end;

create trigger all_controls_before_update before update on all_controls
begin
  select case when (NEW.x_error is not null) and (NEW.x_error = 'application_error') then raise(FAIL,'Application Error - Sample Application Error') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'application_warning') then raise(FAIL,'Application Warning - Sample Application Warning') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'execute_form') then raise(FAIL,'Execute Form - Sample Execute Form Message //<%=xmodel.namespace%>ModelDBValidation_ErrorPopup?action=update&x_primary=<%=xmodel.get("x_primary")%>&x_error_value=<%=xmodel.get("x_error")%>') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'db_error_standard') then raise(FAIL,'sample_db_error_standard') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'db_error_regex') then raise(FAIL,'sample_db_error_regex') end\;
  select case when (NEW.x_error is not null) and (NEW.x_error = 'unhandled') then raise(FAIL,'Sample unhandled database error') end\;
  select case when (NEW.x_error is not null) then raise(FAIL,'Application Error - Invalid value for x_error') end\;
end;

insert into all_controls(x_label, x_html, x_textbox, x_textzoom, x_dropdown, x_date, x_textarea, x_hidden, x_checkbox, x_password, x_customtextbox, x_customdropdown)
values (
  'Test Label',
  '<b>Test</b> HTML',
  'Test Textbox',
  'Test Textzoom
Test Line 2
Test Line 3',
  'VALUE1',
  '2019-01-19',
  'Test Textarea
Test Line 2
Test Line 3
Test Line 4
Test Line 5',
  'Test Hidden Field',
  '1',
  'Test Password',
  'Custom Textbox',
  'ONE'
);
insert into all_controls(x_label, x_html, x_textbox, x_textzoom, x_dropdown, x_date, x_textarea, x_hidden, x_checkbox, x_password)
values (
  'Test Label',
  '<b>Test</b> HTML',
  'Textbox Second Row',
  'Test Textzoom
Test Line 2
Test Line 3',
  'VALUE1',
  '2019-01-19',
  'Test Textarea
Test Line 2
Test Line 3',
  'Test Hidden Field',
  '0',
  'Test Password'
);
insert into all_controls(x_label) values ('Third row');


/*********scope codes*********/
update jsharmony.code_doc_scope set code_code='cust' where code_val='C';
update jsharmony.code_note_scope set code_code='cust' where code_val='C';

/* menu */
insert into jsharmony.menu (menu_group, menu_id, menu_sts, menu_id_parent, menu_name, menu_seq, menu_desc, menu_desc_ext, menu_desc_ext2, menu_cmd, menu_image, menu_snotes, menu_subcmd) values ('S', 300, 'ACTIVE', 1, 'Customer', 300, 'Customers', null, null, 'Cust_Listing', null, null, null);
insert into jsharmony.menu (menu_group, menu_id, menu_sts, menu_id_parent, menu_name, menu_seq, menu_desc, menu_desc_ext, menu_desc_ext2, menu_cmd, menu_image, menu_snotes, menu_subcmd) values ('S', 30001, 'ACTIVE', 300, 'Customer/Cust_Listing', 30001, 'Customer Listing', null, null, 'Cust_Listing', null, null, null);
insert into jsharmony.sys_menu_role (menu_id, sys_role_name) values (300, '*');
insert into jsharmony.sys_menu_role (menu_id, sys_role_name) values (30001, '*');

update jsharmony.menu set menu_cmd='Client/Dashboard' where menu_id=200 and menu_cmd='jsHarmonyFactory/Client/Dashboard';
insert into jsharmony.menu (menu_group, menu_id, menu_sts, menu_id_parent, menu_name, menu_seq, menu_desc, menu_desc_ext, menu_desc_ext2, menu_cmd, menu_image, menu_snotes, menu_subcmd) values ('C', 280001, 'ACTIVE', 2800, 'Client/Admin/Settings', 280001, 'Settings', null, null, 'Client/Admin/Settings', null, null, null);
insert into jsharmony.cust_menu_role (menu_id, cust_role_name) values (280001, 'CSYSADMIN');


end;