CREATE TABLE `plugin_reward_affiliate_id` (
`user_id` INT( 11 ) NOT NULL ,
`affiliate_id` VARCHAR( 32 ) NOT NULL ,
UNIQUE (
`user_id` ,
`affiliate_id`
)
) ENGINE = MYISAM;

CREATE TABLE `plugin_reward` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`reward_user_id` INT( 11 ) NOT NULL ,
`premium_order_id` INT( 11 ) NOT NULL ,
`reward_percent` INT( 5 ) NOT NULL ,
`reward_amount` FLOAT( 5, 2 ) NOT NULL ,
`reward_date` DATETIME NOT NULL ,
`status` ENUM( 'pending', 'cleared', 'cancelled' ) NOT NULL DEFAULT 'pending'
) ENGINE = MYISAM;

INSERT INTO `site_config` (`id`, `config_key`, `config_value`, `config_description`, `availableValues`, `config_type`, `config_group`) VALUES (NULL, 'next_check_for_rewards_aggregation', '0', 'System value. The next time collate the rewards data.', '', 'integer', 'System');

CREATE TABLE `plugin_reward_aggregated` (`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `reward_user_id` INT(11) NOT NULL, `period` DATETIME NOT NULL, `amount` FLOAT(7,2) NOT NULL, `description` VARCHAR(150) NOT NULL, `aggregated_date` DATETIME NOT NULL, `status` ENUM('available','cancelled','requested','payment_in_progress','paid') NOT NULL, `payment_date` DATETIME NOT NULL) ENGINE = MyISAM;

ALTER TABLE `plugin_reward_aggregated` ADD UNIQUE (
`reward_user_id` ,
`period`
);

CREATE TABLE `plugin_reward_withdraw_request` (`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `reward_user_id` INT(11) NOT NULL, `amount` FLOAT(7,2) NOT NULL, `requested_date` DATETIME NOT NULL, `status` ENUM('pending','paid','cancelled') NOT NULL, `payment_date` DATETIME NOT NULL, `payment_method` VARCHAR(100) NOT NULL DEFAULT 'paypal', `payment_notes` TEXT NOT NULL) ENGINE = MyISAM;

ALTER TABLE `plugin_reward_affiliate_id` ADD `paypal_email` VARCHAR( 255 ) NOT NULL;

ALTER TABLE `plugin_reward_aggregated` ADD `withdrawal_request_id` INT( 11 ) NOT NULL;

ALTER TABLE `plugin_reward` CHANGE `status` `status` ENUM( 'pending', 'cleared', 'cancelled', 'charged_back', 'refunded' ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'pending';


ALTER TABLE `plugin_reward_aggregated` ADD `reward_type` VARCHAR( 3 ) NOT NULL DEFAULT 'PPS';

CREATE TABLE IF NOT EXISTS `plugin_reward_country_list` (
  `iso_alpha2` varchar(2) DEFAULT NULL,
  `iso_alpha3` varchar(3) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `plugin_reward_country_list` (`iso_alpha2`, `iso_alpha3`, `name`) VALUES
('AD', 'AND', 'Andorra'),
('AE', 'ARE', 'United Arab Emirates'),
('AF', 'AFG', 'Afghanistan'),
('AG', 'ATG', 'Antigua and Barbuda'),
('AI', 'AIA', 'Anguilla'),
('AL', 'ALB', 'Albania'),
('AM', 'ARM', 'Armenia'),
('AN', 'ANT', 'Netherlands Antilles'),
('AO', 'AGO', 'Angola'),
('AQ', 'ATA', 'Antarctica'),
('AR', 'ARG', 'Argentina'),
('AS', 'ASM', 'American Samoa'),
('AT', 'AUT', 'Austria'),
('AU', 'AUS', 'Australia'),
('AW', 'ABW', 'Aruba'),
('AX', 'ALA', 'Aland Islands'),
('AZ', 'AZE', 'Azerbaijan'),
('BA', 'BIH', 'Bosnia and Herzegovina'),
('BB', 'BRB', 'Barbados'),
('BD', 'BGD', 'Bangladesh'),
('BE', 'BEL', 'Belgium'),
('BF', 'BFA', 'Burkina Faso'),
('BG', 'BGR', 'Bulgaria'),
('BH', 'BHR', 'Bahrain'),
('BI', 'BDI', 'Burundi'),
('BJ', 'BEN', 'Benin'),
('BL', 'BLM', 'Saint Barthélemy'),
('BM', 'BMU', 'Bermuda'),
('BN', 'BRN', 'Brunei'),
('BO', 'BOL', 'Bolivia'),
('BR', 'BRA', 'Brazil'),
('BS', 'BHS', 'Bahamas'),
('BT', 'BTN', 'Bhutan'),
('BV', 'BVT', 'Bouvet Island'),
('BW', 'BWA', 'Botswana'),
('BY', 'BLR', 'Belarus'),
('BZ', 'BLZ', 'Belize'),
('CA', 'CAN', 'Canada'),
('CC', 'CCK', 'Cocos Islands'),
('CD', 'COD', 'Democratic Republic of the Congo'),
('CF', 'CAF', 'Central African Republic'),
('CG', 'COG', 'Republic of the Congo'),
('CH', 'CHE', 'Switzerland'),
('CI', 'CIV', 'Ivory Coast'),
('CK', 'COK', 'Cook Islands'),
('CL', 'CHL', 'Chile'),
('CM', 'CMR', 'Cameroon'),
('CN', 'CHN', 'China'),
('CO', 'COL', 'Colombia'),
('CR', 'CRI', 'Costa Rica'),
('CU', 'CUB', 'Cuba'),
('CV', 'CPV', 'Cape Verde'),
('CX', 'CXR', 'Christmas Island'),
('CY', 'CYP', 'Cyprus'),
('CZ', 'CZE', 'Czech Republic'),
('DE', 'DEU', 'Germany'),
('DJ', 'DJI', 'Djibouti'),
('DK', 'DNK', 'Denmark'),
('DM', 'DMA', 'Dominica'),
('DO', 'DOM', 'Dominican Republic'),
('DZ', 'DZA', 'Algeria'),
('EC', 'ECU', 'Ecuador'),
('EE', 'EST', 'Estonia'),
('EG', 'EGY', 'Egypt'),
('EH', 'ESH', 'Western Sahara'),
('ER', 'ERI', 'Eritrea'),
('ES', 'ESP', 'Spain'),
('ET', 'ETH', 'Ethiopia'),
('FI', 'FIN', 'Finland'),
('FJ', 'FJI', 'Fiji'),
('FK', 'FLK', 'Falkland Islands'),
('FM', 'FSM', 'Micronesia'),
('FO', 'FRO', 'Faroe Islands'),
('FR', 'FRA', 'France'),
('GA', 'GAB', 'Gabon'),
('GB', 'GBR', 'United Kingdom'),
('GD', 'GRD', 'Grenada'),
('GE', 'GEO', 'Georgia'),
('GF', 'GUF', 'French Guiana'),
('GG', 'GGY', 'Guernsey'),
('GH', 'GHA', 'Ghana'),
('GI', 'GIB', 'Gibraltar'),
('GL', 'GRL', 'Greenland'),
('GM', 'GMB', 'Gambia'),
('GN', 'GIN', 'Guinea'),
('GP', 'GLP', 'Guadeloupe'),
('GQ', 'GNQ', 'Equatorial Guinea'),
('GR', 'GRC', 'Greece'),
('GS', 'SGS', 'South Georgia and the South Sandwich Islands'),
('GT', 'GTM', 'Guatemala'),
('GU', 'GUM', 'Guam'),
('GW', 'GNB', 'Guinea-Bissau'),
('GY', 'GUY', 'Guyana'),
('HK', 'HKG', 'Hong Kong'),
('HM', 'HMD', 'Heard Island and McDonald Islands'),
('HN', 'HND', 'Honduras'),
('HR', 'HRV', 'Croatia'),
('HT', 'HTI', 'Haiti'),
('HU', 'HUN', 'Hungary'),
('ID', 'IDN', 'Indonesia'),
('IE', 'IRL', 'Ireland'),
('IL', 'ISR', 'Israel'),
('IM', 'IMN', 'Isle of Man'),
('IN', 'IND', 'India'),
('IO', 'IOT', 'British Indian Ocean Territory'),
('IQ', 'IRQ', 'Iraq'),
('IR', 'IRN', 'Iran'),
('IS', 'ISL', 'Iceland'),
('IT', 'ITA', 'Italy'),
('JE', 'JEY', 'Jersey'),
('JM', 'JAM', 'Jamaica'),
('JO', 'JOR', 'Jordan'),
('JP', 'JPN', 'Japan'),
('KE', 'KEN', 'Kenya'),
('KG', 'KGZ', 'Kyrgyzstan'),
('KH', 'KHM', 'Cambodia'),
('KI', 'KIR', 'Kiribati'),
('KM', 'COM', 'Comoros'),
('KN', 'KNA', 'Saint Kitts and Nevis'),
('KP', 'PRK', 'North Korea'),
('KR', 'KOR', 'South Korea'),
('KW', 'KWT', 'Kuwait'),
('KY', 'CYM', 'Cayman Islands'),
('KZ', 'KAZ', 'Kazakhstan'),
('LA', 'LAO', 'Laos'),
('LB', 'LBN', 'Lebanon'),
('LC', 'LCA', 'Saint Lucia'),
('LI', 'LIE', 'Liechtenstein'),
('LK', 'LKA', 'Sri Lanka'),
('LR', 'LBR', 'Liberia'),
('LS', 'LSO', 'Lesotho'),
('LT', 'LTU', 'Lithuania'),
('LU', 'LUX', 'Luxembourg'),
('LV', 'LVA', 'Latvia'),
('LY', 'LBY', 'Libya'),
('MA', 'MAR', 'Morocco'),
('MC', 'MCO', 'Monaco'),
('MD', 'MDA', 'Moldova'),
('ME', 'MNE', 'Montenegro'),
('MF', 'MAF', 'Saint Martin'),
('MG', 'MDG', 'Madagascar'),
('MH', 'MHL', 'Marshall Islands'),
('MK', 'MKD', 'Macedonia'),
('ML', 'MLI', 'Mali'),
('MM', 'MMR', 'Myanmar'),
('MN', 'MNG', 'Mongolia'),
('MO', 'MAC', 'Macao'),
('MP', 'MNP', 'Northern Mariana Islands'),
('MQ', 'MTQ', 'Martinique'),
('MR', 'MRT', 'Mauritania'),
('MS', 'MSR', 'Montserrat'),
('MT', 'MLT', 'Malta'),
('MU', 'MUS', 'Mauritius'),
('MV', 'MDV', 'Maldives'),
('MW', 'MWI', 'Malawi'),
('MX', 'MEX', 'Mexico'),
('MY', 'MYS', 'Malaysia'),
('MZ', 'MOZ', 'Mozambique'),
('NA', 'NAM', 'Namibia'),
('NC', 'NCL', 'New Caledonia'),
('NE', 'NER', 'Niger'),
('NF', 'NFK', 'Norfolk Island'),
('NG', 'NGA', 'Nigeria'),
('NI', 'NIC', 'Nicaragua'),
('NL', 'NLD', 'Netherlands'),
('NO', 'NOR', 'Norway'),
('NP', 'NPL', 'Nepal'),
('NR', 'NRU', 'Nauru'),
('NU', 'NIU', 'Niue'),
('NZ', 'NZL', 'New Zealand'),
('OM', 'OMN', 'Oman'),
('PA', 'PAN', 'Panama'),
('PE', 'PER', 'Peru'),
('PF', 'PYF', 'French Polynesia'),
('PG', 'PNG', 'Papua New Guinea'),
('PH', 'PHL', 'Philippines'),
('PK', 'PAK', 'Pakistan'),
('PL', 'POL', 'Poland'),
('PM', 'SPM', 'Saint Pierre and Miquelon'),
('PN', 'PCN', 'Pitcairn'),
('PR', 'PRI', 'Puerto Rico'),
('PS', 'PSE', 'Palestinian Territory'),
('PT', 'PRT', 'Portugal'),
('PW', 'PLW', 'Palau'),
('PY', 'PRY', 'Paraguay'),
('QA', 'QAT', 'Qatar'),
('RE', 'REU', 'Reunion'),
('RO', 'ROU', 'Romania'),
('RS', 'SRB', 'Serbia'),
('RU', 'RUS', 'Russia'),
('RW', 'RWA', 'Rwanda'),
('SA', 'SAU', 'Saudi Arabia'),
('SB', 'SLB', 'Solomon Islands'),
('SC', 'SYC', 'Seychelles'),
('SD', 'SDN', 'Sudan'),
('SE', 'SWE', 'Sweden'),
('SG', 'SGP', 'Singapore'),
('SH', 'SHN', 'Saint Helena'),
('SI', 'SVN', 'Slovenia'),
('SJ', 'SJM', 'Svalbard and Jan Mayen'),
('SK', 'SVK', 'Slovakia'),
('SL', 'SLE', 'Sierra Leone'),
('SM', 'SMR', 'San Marino'),
('SN', 'SEN', 'Senegal'),
('SO', 'SOM', 'Somalia'),
('SR', 'SUR', 'Suriname'),
('ST', 'STP', 'Sao Tome and Principe'),
('SV', 'SLV', 'El Salvador'),
('SY', 'SYR', 'Syria'),
('SZ', 'SWZ', 'Swaziland'),
('TC', 'TCA', 'Turks and Caicos Islands'),
('TD', 'TCD', 'Chad'),
('TF', 'ATF', 'French Southern Territories'),
('TG', 'TGO', 'Togo'),
('TH', 'THA', 'Thailand'),
('TJ', 'TJK', 'Tajikistan'),
('TK', 'TKL', 'Tokelau'),
('TL', 'TLS', 'East Timor'),
('TM', 'TKM', 'Turkmenistan'),
('TN', 'TUN', 'Tunisia'),
('TO', 'TON', 'Tonga'),
('TR', 'TUR', 'Turkey'),
('TT', 'TTO', 'Trinidad and Tobago'),
('TV', 'TUV', 'Tuvalu'),
('TW', 'TWN', 'Taiwan'),
('TZ', 'TZA', 'Tanzania'),
('UA', 'UKR', 'Ukraine'),
('UG', 'UGA', 'Uganda'),
('UM', 'UMI', 'United States Minor Outlying Islands'),
('US', 'USA', 'United States'),
('UY', 'URY', 'Uruguay'),
('UZ', 'UZB', 'Uzbekistan'),
('VA', 'VAT', 'Vatican'),
('VC', 'VCT', 'Saint Vincent and the Grenadines'),
('VE', 'VEN', 'Venezuela'),
('VG', 'VGB', 'British Virgin Islands'),
('VI', 'VIR', 'U.S. Virgin Islands'),
('VN', 'VNM', 'Vietnam'),
('VU', 'VUT', 'Vanuatu'),
('WF', 'WLF', 'Wallis and Futuna'),
('WS', 'WSM', 'Samoa'),
('YE', 'YEM', 'Yemen'),
('YT', 'MYT', 'Mayotte'),
('ZA', 'ZAF', 'South Africa'),
('ZM', 'ZMB', 'Zambia'),
('ZW', 'ZWE', 'Zimbabwe'),
('CS', 'SCG', 'Serbia and Montenegro');

CREATE TABLE IF NOT EXISTS `plugin_reward_ppd_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reward_user_id` int(11) NOT NULL,
  `download_ip` varchar(15) COLLATE utf8_bin NOT NULL,
  `file_id` int(11) NOT NULL,
  `download_country_group_id` int(11) DEFAULT NULL,
  `download_date` datetime NOT NULL,
  `reward_amount` float(5,4) NOT NULL,
  `status` enum('pending','cleared','cancelled','aggregated') COLLATE utf8_bin NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`),
  KEY `reward_user_id` (`reward_user_id`),
  KEY `reward_amount` (`reward_amount`),
  KEY `status` (`status`),
  KEY `download_ip` (`download_ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `plugin_reward_ppd_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_label` varchar(100) COLLATE utf8_bin NOT NULL,
  `payout_rate` float(5,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8 ;

INSERT INTO `plugin_reward_ppd_group` (`id`, `group_label`, `payout_rate`) VALUES
(1, 'Group A', 3.50),
(2, 'Group B', 3.00),
(3, 'Group C', 2.50),
(4, 'Group D', 2.00),
(5, 'Group E', 1.50),
(6, 'Group F', 1.00),
(7, 'Group G', 0.50);

CREATE TABLE IF NOT EXISTS `plugin_reward_ppd_group_country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `country_code` varchar(3) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=124 ;

INSERT INTO `plugin_reward_ppd_group_country` (`id`, `group_id`, `country_code`) VALUES
(101, 1, 'AG'),
(108, 2, 'TT'),
(113, 3, 'KZ'),
(117, 4, 'ZW'),
(120, 5, 'MH'),
(123, 6, 'VC'),
(100, 1, 'AO'),
(99, 1, 'DZ'),
(107, 2, 'TO'),
(106, 2, 'TK'),
(105, 2, 'RE'),
(104, 2, 'QA'),
(112, 3, 'JO'),
(111, 3, 'JE'),
(110, 3, 'JM'),
(109, 3, 'CI'),
(116, 4, 'YE'),
(115, 4, 'VU'),
(114, 4, 'UZ'),
(119, 5, 'ML'),
(118, 5, 'JO'),
(122, 6, 'PM'),
(121, 6, 'MF'),
(102, 1, 'GB'),
(103, 1, 'US');
