
alter table jin_package change aweber_data aweber_data text not null default '';

#
# affiliate series

alter table jin_role add priv_master_affiliates_series int(1) not null default 0 after priv_master_affiliates_affprogs;

create table jin_affprog_series
(
  id            int(11) not null primary key,
  owner_id      int(11) not null,

  title         varchar(255) not null,

  key(owner_id)
);

create table jin_affprog_series_step
(
  id                    int(11) not null primary key,
  owner_id              int(11) not null,

  ctime                 datetime null,

  series_id             int(11) not null default 0,
  affprog_id            int(11) not null default 0,

  upgrade_type          varchar(20) not null default '',
  upgrade_amount        varchar(20) not null default '',
  upgrade_period_n      varchar(10) not null default '',
  upgrade_period_u      char(1) not null default 0,

  downgrade_type        varchar(20) not null default '',
  downgrade_amount      varchar(20) not null default '',
  downgrade_period_n    varchar(10) not null default '',
  downgrade_period_u    char(1) not null default 0,

  email_upgrade text    not null default '',
  email_downgrade text  not null default '',

  key(owner_id,series_id)
);

create table jin_affprog_series_history
(
  id                       int(11) not null primary key,
  owner_id                 int(11) not null,
  
  user_id                  int(11) not null,

  series_id                int(11) not null,

  otime                    datetime,
  affprog_id_before        int(11) not null,
  affprog_title_before     varchar(255) not null default '',
  affprog_id_after         int(11) not null,
  affprog_title_after      varchar(255) not null default '',

  comment                  varchar(255) not null default '',

  key(owner_id,user_id)
);

alter table jin_user_affprog add ctime datetime null after owner_id;

# unification of user selection in Mass mail and Special users

alter table jin_specusers add u_generated_min varchar(20) not null default '' after u_paid_max;
alter table jin_specusers add u_generated_max varchar(20) not null default '' after u_generated_min;
alter table jin_specusers add u_count_sales_from date null after u_generated_max;
alter table jin_specusers add u_count_sales_until date null after u_count_sales_from;

#
# multiple ext_scripts per role

alter table jin_role change ext_script ext_scripts varchar(255) not null default '';
update jin_role set ext_scripts='' where ext_scripts=0;

alter table jin_user_role change ext_script ext_scripts varchar(255) not null default '';
update jin_user_role set ext_scripts='' where ext_scripts=0;

#
# comment field in autoresponders and short urls

alter table jin_autoresponder add admin_notes text not null default '' after subject;
alter table jin_short_url add admin_notes text not null default '' after url;

#
# payment system restructuring

alter table jin_product drop pg_use_global_settings;

create table jin_pgate
(
  id                    int(11) not null primary key,
  owner_id              int(11) not null,

  type                  varchar(50) not null default '',
  
  title                 varchar(50) not null default '',
  settings              text not null default ''
);

alter table jin_role add priv_master_store_pgates int(1) not null default 0 after priv_supervisor;
update jin_role set priv_master_store_pgates=1 where priv_master_store_products=1;

alter table jin_product add pgates varchar(255) not null default '' after dlv_points;

alter table jin_product add pg_use_ext_recur varchar(255) not null default '' after pgates;
alter table jin_product add pg_use_int_recur varchar(255) not null default '' after pg_use_ext_recur;
alter table jin_product add pg_recur_period_l varchar(10) not null default '' after pg_use_int_recur;
alter table jin_product add pg_recur_period_u char(1) not null default '' after pg_recur_period_l;
alter table jin_product add pg_recur_numinst varchar(10) not null default '' after pg_recur_period_u;
alter table jin_product add pg_recur_trial_l varchar(10) not null default '' after pg_recur_numinst;
alter table jin_product add pg_recur_trial_u char(1) not null default '' after pg_recur_trial_l;
alter table jin_product add pg_recur_trial_amount varchar(15) not null default '' after pg_recur_trial_u;

## convert subscription data
## update jin_product set pg_use_ext_recur='',pg_use_int_recur=''

## first try to populate subscription settings with paypal subscripton data
update jin_product set pg_recur_period_l=pg_paypal_recur_length,pg_recur_period_u=lower(pg_paypal_recur_unit) where pg_recur_period_l='';
update jin_product set pg_recur_numinst=pg_paypal_recur_number where pg_recur_numinst='';
update jin_product set pg_recur_trial_l=pg_paypal_trial_length,pg_recur_trial_u=lower(pg_paypal_trial_unit) where pg_recur_trial_l='';
update jin_product set pg_recur_trial_amount=pg_paypal_trial_price where pg_recur_trial_amount='';

## then try to populate subscription settings with authnet subscripton data
update jin_product set pg_recur_period_l=pg_an_recur_length,pg_recur_period_u=substring(pg_an_recur_unit,1,1) where pg_recur_period_l='';
update jin_product set pg_recur_numinst=pg_an_recur_number where pg_recur_numinst='';
update jin_product set pg_recur_trial_l=pg_recur_period_l*pg_an_trial_number,pg_recur_trial_u=pg_recur_period_u where pg_recur_trial_l='';
update jin_product set pg_recur_trial_amount=pg_an_trial_amount where pg_recur_trial_amount='';

## then try to populate subscription settings with linkpoint subscripton data
update jin_product set pg_recur_period_l=pg_lp_recur_length,pg_recur_period_u=lower(pg_lp_recur_unit) where pg_recur_period_l='';
update jin_product set pg_recur_numinst=pg_lp_recur_numinst where pg_recur_numinst='';

## then try to populate subscription settings with internal subscripton data
update jin_product set pg_recur_period_l=jvmsub_subscr_length,pg_recur_period_u=lower(jvmsub_subscr_unit) where pg_recur_period_l='';
update jin_product set pg_recur_numinst=jvmsub_subscr_numinst where pg_recur_numinst='';
update jin_product set pg_recur_trial_l=jvmsub_trial_length,pg_recur_trial_u=lower(jvmsub_trial_unit) where pg_recur_trial_l='';
update jin_product set pg_recur_trial_amount=jvmsub_trial_price where pg_recur_trial_amount='';


update jin_product set pg_use_ext_recur=concat(if(pg_paypal_recur=1,',paypal',''),if(pg_an_recur=1,',authnet',''),if(pg_lp_recur=1,',linkpoint','')) where pg_use_ext_recur='';

# potential problem here: %an% is triggered on "manual"
update jin_product set pg_use_int_recur=concat(if(jvmsub_use_with like '%2c%',',2checkout',''),if(jvmsub_use_with like '%mb%',',moneybookers',''),if(jvmsub_use_with like '%an%',',authnet',''),if(jvmsub_use_with like '%lp%',',linkpoint','')) where pg_use_int_recur='';


alter table jin_sale change pgate_code txn_pgate_type varchar(30) not null default '';
update jin_sale set txn_pgate_type='2checkout' where txn_pgate_type='2c';
update jin_sale set txn_pgate_type='clickbank' where txn_pgate_type='cb';
update jin_sale set txn_pgate_type='moneybookers' where txn_pgate_type='mb';
update jin_sale set txn_pgate_type='authnet' where txn_pgate_type='an';
update jin_sale set txn_pgate_type='linkpoint' where txn_pgate_type='lp';
alter table jin_sale change pgate_txn_id txn_code varchar(255) not null default '';
alter table jin_sale drop pgate_txn_data;

create table jin_pgtxn
(
  id                    int(11) not null primary key,
  owner_id              int(11) not null,
  
  txn_code              varchar(255) not null default '',
  processed             int(1) not null default 0,
  successful            int(1) not null default 0,

  pgate_id              int(11) not null default 0,
  pgate_type            varchar(30) not null default '',
  
  order_id              int(11) not null default 0,
  sale_id               int(11) not null default 0,

  key(owner_id,order_id,sale_id),
  key(owner_id,txn_code)
);

alter table jin_ipn_forward drop sale_id;
alter table jin_ipn_forward add pgtxn_id int(11) not null default 0 after owner_id;

alter table jin_product change pg_2c_pid pg_2checkout_pid varchar(50) not null default '' after pg_recur_trial_amount;
alter table jin_product change pg_cb_linknumber pg_clickbank_linknumber varchar(50) not null default '' after pg_2checkout_pid;

alter table jin_jvmsub change trial_length trial_l varchar(20) not null default '';
alter table jin_jvmsub change trial_unit trial_u char(1) not null default '';
alter table jin_jvmsub change trial_price trial_amount varchar(20) not null default '';
alter table jin_jvmsub change subscr_length period_l varchar(20) not null default '' after canceldate;
alter table jin_jvmsub change subscr_unit period_u varchar(20) not null default '' after period_l;
alter table jin_jvmsub change subscr_numinst numinst varchar(20) not null default '' after period_u;
alter table jin_jvmsub change subscr_price period_amount varchar(20) not null default '';

update jin_jvmsub set period_u=lower(period_u);
update jin_jvmsub set trial_u=lower(trial_u);

alter table jin_paylog drop pgate;
alter table jin_paylog add pgtxn_id int(11) not null default 0 after type;
create index pgtxn_id on jin_paylog(pgtxn_id);

alter table jin_package add pgates varchar(255) not null default '' after aweber_data;

#
# change states format

update jin_order set user_state=concat(if(find_in_set(user_state,'AB,BC,MB,NB,NF,NS,ON,PE,QC,SK'),'CA_','US_'),user_state) where length(user_state)=2;

update jin_user set state=concat(if(find_in_set(state,'AB,BC,MB,NB,NF,NS,ON,PE,QC,SK'),'CA_','US_'),state) where length(state)=2;

update jin_tax set state=concat(if(find_in_set(state,'AB,BC,MB,NB,NF,NS,ON,PE,QC,SK'),'CA_','US_'),state) where length(state)=2;


alter table jin_oto modify descr longtext not null default '';
