The Affiliate system needs a few new database tables:

1. Database
   1. `order_affiliates`
      • `id` int(10) not null, auto-increment
      • `client_id` unsigned int(10) not null, foreign key
      • `code` varchar(16) not null (the hashed `id` used as the affiliate ID in URLs)
      • `status` enum(active,inactive) default active not
      • `visits` unsigned int(10) not null default 0
      • `sales` unsigned int(10) not null default 0
      • `date_added` datetime not null
      • `date_updated` datetime null default null
      • PRIMARY KEY (`id`)
      • UNIQUE KEY (`client_id`)
      • UNIQUE KEY (`code`)
      • INDEX (`client_id`, `status`)
   2. `order_affiliate_settings`
      • `affiliate_id` int(10) not null, foreign key
      • `key` varchar(255) not null
      • `value` text not null
      • PRIMARY KEY(`affiliate_id`, `key`)
   3. `order_affiliate_company_settings`
      • `company_id` unsigned int(10) not null, foreign key
      • `key` varchar(255) not null
      • `value` text not null
      • PRIMARY KEY(`company_id`, `key`)
   4. `order_affiliate_referrals`
      • `id` unsigned int(10) not null, auto-increment
      • `affiliate_id` int(10) not null, foreign key
      • `order_id` unsigned int(10) not null, foreign key
      • `service_id` unsigned int(10) not null, foreign key
      • `package_id` unsigned int(10) not null, foreign key
      • `name` varchar(255) null default null
      • `status` enum('pending','mature','canceled') not null default 'pending'
      • `amount` decimal(19,4) not null, default 0.0000
      • `currency` char(3) not null default USD
      • `commission` decimal(19,4) not null, default 0.0000
      • `date_added` datetime not null
      • `date_updated` datetime null default null
      • PRIMARY KEY (`id`)
      • INDEX(`affiliate_id`, `date_added`)
      • INDEX(`order_id`)
      • INDEX(`service_id`)
      • INDEX(`package_id`)
      • INDEX(`status`)
   5. `order_affiliate_payouts`
      • `id` unsigned int(10) not null, auto-increment
      • `affiliate_id` int(10) not null, foreign key
      • `payment_method_id` unsigned int(10) null, default null, foreign key
      • `status` enum('pending','approved','declined')
      • `requested_amount` decimal(19,4) not null default 0.0000
      • `requested_currency` char(3) not null default USD
      • `paid_amount` decimal(19,4) null default null
      • `paid_currency` char(3) null default null
      • PRIMARY KEY(`id`)
      • INDEX(`affiliate_id`, `status`)
      • INDEX(`payment_method_id`)
   6. `order_affiliate_payment_methods`
      • `id` unsigned int(10) not null, auto-increment
      • `company_id` unsigned int(10) not null, foreign key
      • PRIMARY KEY (`id`)
      • INDEX (`company_id`)
   7. `order_affiliate_payment_method_names`
      • `payment_method_id` unsigned int(10) not null, foreign key
      • `lang` varchar(5) not null
      • `name` varchar(255) not null
      • PRIMARY KEY(`payment_method_id`, `lang`)
2. Initial values
   • Only the `order_affiliate_company_settings` will be populated by default. The `order_affiliate_settings` are set when an affiliate is created

     `order_affiliates` - blank
     `order_affiliate_settings` - users start with the following:
     	total_available - 0 (updates each time a referral order for an active client reaches maturity)
     	total_withdrawn - 0 (updates each time a successful payout occurs, stored in the "withdrawal_currency")
     	commission_type - (copy from company settings)
     	commission_amount - (copy from company settings)
     	order_frequency - (copy from company settings)
     	order_recurring - (copy from company settings)
     	maturity_days - (copy from company settings)
     	min_withdrawal_amount - (copy from company settings)
     	max_withdrawal_amount - (copy from company settings)
     	withdrawal_currency - (copy from company settings)
     `order_affiliate_company_settings` - when installed for the company, set the following:
             enabled - false (true/false)
     	cookie_tld - 180 (days)
     	commission_type - percentage (possible: "percentage" or "fixed")
     	commission_amount - 0 (the percentage or fixed amount value, e.g. "5" would be 5% or $5 depending on commission_type)
     	order_frequency - first (possible: "first" or "any" - whether referrals are paid only for a single "first" order or any number of orders)
     	order_recurring - false (possible: "true" or "false" - whether referrals will be paid for services ordered that renew)
     	maturity_days - 10
     	min_withdrawal_amount - 10
     	max_withdrawal_amount - 100
     	withdrawal_currency - USD
             signup_content - HTML content (default TBD)
     `order_affiliate_referrals` - blank
     `order_affiliate_payouts` - blank
     

   • Set initial values for any payment methods?
   • total_withdrawn and total_available are in the withdrawal_currency, but it's possible you could change it. Previous total_available/withdrawn would then be inaccurate because old values were for a different currency. WHAT TO DO ABOUT IT? - We could include a warning/confirmation modal with a message if trying to change the currency to mention this. If possible, they could also adjust the total_available/withdrawn on their own to reflect the correct values for the new currency.

Be sure this works for install, upgrade, and uninstall!