CREATE TABLE ProductFiles (FileId INT NOT NULL AUTO_INCREMENT, ProductId INT NOT NULL, Name VARCHAR(255) NOT NULL, Version VARCHAR(100) NOT NULL, FilePath VARCHAR(255) NOT NULL, RealPath VARCHAR(255) NOT NULL, Size INT NOT NULL, Status tinyint(4) NOT NULL default '0', IsPrimary tinyint(4) NOT NULL default '0', Priority smallint(6) NOT NULL default '0', AddedOn int(11) NOT NULL default '0', AddedById int(11) NOT NULL default '0', MIMEType varchar(255) NOT NULL default '', PRIMARY KEY (FileId));
CREATE TABLE UserFileAccess (FileAccessId INT NOT NULL AUTO_INCREMENT, ProductId INT NOT NULL, PortalUserId INT NOT NULL, PRIMARY KEY (FileAccessId));
CREATE TABLE UserDownloads (DownloadId int(11) NOT NULL auto_increment, PortalUserId int(11) NOT NULL default '0', Username varchar(255) NOT NULL default '', ProductId int(11) NOT NULL default '0', ProductName varchar(255) NOT NULL default '', FileId int(11) NOT NULL default '0', Filename varchar(255) NOT NULL default '', IPAddress varchar(100) NOT NULL default '', StartedOn int(11) NOT NULL default '0', EndedOn int(11) NOT NULL default '0', PRIMARY KEY (DownloadId));

INSERT INTO ConfigurationAdmin VALUES ('Comm_AffiliateStorageMethod', 'la_Text_Affiliates', 'la_prompt_affiliate_storage_method', 'radio', NULL, '1=lu_per_session,2=lu_in_cookie_permanent', 18, 1);
INSERT INTO ConfigurationValues VALUES ('Comm_AffiliateStorageMethod', '1', 'In-Commerce', 'in-commerce:general');
INSERT INTO ConfigurationAdmin VALUES ('Comm_AffiliateCookieDuration', 'la_Text_Affiliates', 'la_prompt_affiliate_cookie_duration', 'text', NULL, '', 19, 1);
INSERT INTO ConfigurationValues VALUES ('Comm_AffiliateCookieDuration', '2628000', 'In-Commerce', 'in-commerce:general');
INSERT INTO ConfigurationAdmin VALUES ('Comm_RegisterAsAffiliate', 'la_Text_Affiliates', 'la_prompt_register_as_affiliate', 'checkbox', NULL, '', 20, 1);
INSERT INTO ConfigurationValues VALUES ('Comm_RegisterAsAffiliate', '0', 'In-Commerce', 'in-commerce:general');

ALTER TABLE Orders ADD AffiliateId INT UNSIGNED NOT NULL;

ALTER TABLE Orders ADD VisitId INT UNSIGNED NOT NULL, ADD AffiliateCommission DECIMAL(10, 4) NOT NULL;

ALTER TABLE OrderItems ADD FlatPrice DECIMAL(10, 4) DEFAULT '0' NOT NULL AFTER QuantityReserved;
ALTER TABLE Orders ADD DiscountTotal DECIMAL(10, 4) DEFAULT '0' NOT NULL AFTER CouponDiscount;

ALTER TABLE Products ADD PackageContent MEDIUMTEXT NOT NULL;

CREATE TABLE AffiliatePayments (AffiliatePaymentId int(11) NOT NULL auto_increment, AffiliateId int(10) unsigned NOT NULL default '0', PaymentDate int(10) unsigned NOT NULL default '0', Amount decimal(10,2) NOT NULL default '0.00', Comment text NOT NULL, PaymentReference varchar(255) NOT NULL default '', PaymentTypeId int(11) NOT NULL default '0', PRIMARY KEY (AffiliatePaymentId) );
CREATE TABLE AffiliatePaymentTypes (PaymentTypeId int(11) NOT NULL auto_increment, Name varchar(255) NOT NULL default '', Description text NOT NULL, IsPrimary tinyint(4) NOT NULL default '0', Status tinyint(4) NOT NULL default '0', Priority tinyint(4) NOT NULL default '0', PRIMARY KEY (PaymentTypeId) );
CREATE TABLE AffiliatePlans (AffiliatePlanId int(11) NOT NULL auto_increment, Name varchar(255) NOT NULL default '', PlanType tinyint(3) unsigned NOT NULL default '0', ResetInterval int(10) unsigned NOT NULL default '0', PaymentType tinyint(4) NOT NULL default '0', MinPaymentAmount varchar(255) NOT NULL default '', Enabled tinyint(4) unsigned NOT NULL default '0', IsPrimary tinyint(4) NOT NULL default '0', PRIMARY KEY (AffiliatePlanId) );
CREATE TABLE AffiliatePlansBrackets (AffiliateBracketId int(11) NOT NULL auto_increment, AffiliatePlanId int(11) NOT NULL default '0', FromAmount decimal(10,4) NOT NULL default '0.0000', ToAmount decimal(10,4) NOT NULL default '0.0000', Percent decimal(3,2) NOT NULL default '0.00', PRIMARY KEY (AffiliateBracketId) );
CREATE TABLE AffiliatePlansItems (AffiliateItemId int(11) NOT NULL auto_increment, AffiliatePlanId int(11) NOT NULL default '0', ItemResourceId int(10) unsigned NOT NULL default '0', ItemType tinyint(4) NOT NULL default '0', PRIMARY KEY (AffiliateItemId) );
CREATE TABLE Affiliates (AffiliateId int(11) NOT NULL auto_increment, PortalUserId int(10) unsigned NOT NULL default '0', AffiliatePlanId int(10) unsigned NOT NULL default '0', AccumulatedAmount decimal(10,2) NOT NULL default '0.00', AmountToPay decimal(10,2) NOT NULL default '0.00', LastPaymentDate int(11) NOT NULL default '0', LastOrderDate int(11) NOT NULL default '0', Status tinyint(4) NOT NULL default '0', AffiliateCode varchar(30) NOT NULL default '', ItemsSold int(10) unsigned NOT NULL default '0', PaymentTypeId int(11) NOT NULL default '0', SSN varchar(100) NOT NULL default '', Comments text NOT NULL, PRIMARY KEY (AffiliateId), UNIQUE KEY PortalUserId (PortalUserId), UNIQUE KEY AffiliateCode (AffiliateCode), KEY LastOrderDate (LastOrderDate) );

INSERT INTO Events VALUES ('', 'AFFILIATE.REGISTER', 2, 0, 'In-Commerce', 'la_event_prod_affiliate.register', 0);
INSERT INTO Events VALUES ('', 'AFFILIATE.REGISTER', 2, 0, 'In-Commerce', 'la_event_prod_affiliate.register', 1);
INSERT INTO Events VALUES ('', 'AFFILIATE.PAYMENT', 1, 0, 'In-Commerce', 'la_event_prod_affiliate.payment', 0);
INSERT INTO Events VALUES ('', 'AFFILIATE.PAYMENT', 1, 0, 'In-Commerce', 'la_event_prod_affiliate.payment', 1);
INSERT INTO Events VALUES ('', 'AFFILIATE.REGISTRATION_APPROVED', 1, 0, 'In-Commerce', 'la_event_prod_affiliate.registration_approved', 0);
INSERT INTO Events VALUES ('', 'AFFILIATE.REGISTRATION_APPROVED', 0, 0, 'In-Commerce', 'la_event_prod_affiliate.registration_approved', 1);
INSERT INTO Events VALUES ('', 'AFFILIATE.REGISTRATION_DENIED', 1, 0, 'In-Commerce', 'la_event_prod_affiliate.registration_denied', 0);
INSERT INTO Events VALUES ('', 'AFFILIATE.REGISTRATION_DENIED', 0, 0, 'In-Commerce', 'la_event_prod_affiliate.registration_denied', 1);
INSERT INTO Events VALUES ('', 'AFFILIATE.PAYMENT_TYPE_CHANGED', 1, 0, 'In-Commerce', 'la_event_prod_affiliate.payment_type_changed', 0);
INSERT INTO Events VALUES ('', 'AFFILIATE.PAYMENT_TYPE_CHANGED', 1, 0, 'In-Commerce', 'la_event_prod_affiliate.payment_type_changed', 1);

INSERT INTO GatewayConfigFields (GWConfigFieldId, SystemFieldName, FieldName, ElementType, ValueList, GatewayId) VALUES (13, 'encapsulate_char', 'Encapsulate Char', 'text', '', 2);
INSERT INTO GatewayConfigValues (GWConfigFieldId, PaymentTypeId, Value) VALUES (13, 3, '|');

ALTER TABLE Orders ADD INDEX (VisitId);
ALTER TABLE Orders ADD INDEX (AffiliateId);

UPDATE Modules SET Version = '1.0.2' WHERE Name = 'In-Commerce';