CREATE TABLE tblaffilates (
    `id` INT NOT NULL AUTO_INCREMENT,
    `fname` VARCHAR(255) NOT NULL,
    `lname` VARCHAR(255) NULL,
    `code` VARCHAR(255) NOT NULL,
    `validity_in_days` INT NOT NULL,
    `coupon_id` INT NOT NULL,
    `commission` INT NOT NULL,
    `commission_type` ENUM('percent', 'fixed') NOT NULL,
    `is_active` TINYINT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`coupon_id`),
    UNIQUE (`code`)
);

CREATE TABLE tblaffiliate_account (
    `id` INT NOT NULL AUTO_INCREMENT,
    `affiliate_id` INT NOT NULL,
    `account_type` ENUM('bank', 'paypal', 'cashapp') NOT NULL,
    `account_info` JSON NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`affiliate_id`)
);

CREATE TABLE tblaffiliate_link_visits (
    `id` INT NOT NULL AUTO_INCREMENT,
    `affiliate_id` INT NOT NULL,
    `visited_on` DATETIME NOT NULL,
    `visit_info` JSON NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`affiliate_id`)
);

CREATE TABLE tblaffiliate_tracking (
    `id` INT NOT NULL AUTO_INCREMENT,
    `tenant_id` INT NOT NULL,
    `staff_id` INT NOT NULL,
    `affiliate_id` INT NOT NULL,
    `is_converted` TINYINT NOT NULL,
    `created_at` DATE NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`tenant_id`),
    INDEX (`staff_id`),
    INDEX (`affiliate_id`)
);

CREATE TABLE tblaffiliate_earning (
    `id` INT NOT NULL AUTO_INCREMENT,
    `affiliate_tracking_id` INT NOT NULL,
    `affiliate_id` INT NOT NULL,
    `tenant_id` INT NOT NULL,
    `staff_id` INT NOT NULL,
    `plan_id` INT NOT NULL,
    `plan_name` VARCHAR(255) NOT NULL,
    `plan_tenure` ENUM('monthly', 'yearly') NOT NULL,
    `plan_purchase_amount` DECIMAL(11, 2) NOT NULL,
    `commission_amount` DECIMAL(11, 2) NOT NULL,
    `status` ENUM('pending', 'paid', 'in_review', 'cancelled') NOT NULL,
    `created_at` DATE NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`affiliate_tracking_id`),
    INDEX (`affiliate_id`),
    INDEX (`tenant_id`),
    INDEX (`staff_id`),
    INDEX (`plan_id`)
);

CREATE TABLE tblaffiliate_payout (
    `id` INT NOT NULL AUTO_INCREMENT,
    `affiliate_id` INT NOT NULL,
    `affiliate_earning_ids` LONGTEXT NOT NULL,
    `affiliate_account_id` INT NOT NULL,
    `payout_method` ENUM('check', 'other') NOT NULL,
    `payout_proof` JSON NOT NULL,
    `payout_amount` DECIMAL(11, 2) NOT NULL,
    `payout_comment` TEXT NOT NULL,
    `status` ENUM('pending', 'paid', 'failed') NOT NULL,
    `payoutby_admin_id` INT NOT NULL,
    `payout_date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    INDEX (`affiliate_id`),
    INDEX (`payoutby_admin_id`),
    INDEX (`affiliate_account_id`)
);

-- payout proof image file configuration ---
INSERT INTO `tblapad_options`(
    `id`,
    `type`,
    `name`,
    `value`,
    `autoload`
)
VALUES(
    NULL,
    'affiliate_payout_file',
    'allowed_file_extention',
    '{\"image\":[\"jpeg\",\"jpg\",\"png\",\"gif\",\"webp\"],\"video\":[\"mp4\",\"mpeg\",\"3gp\",\"mov\",\"mts\",\"m2ts\",\"mxf\",\"webp\",\"avi\",\"mkv\",\"webm\"],\"audio\":[],\"document\":[\"pdf\",\"doc\",\"docx\",\"csv\",\"ppt\",\"pptx\",\"xls\",\"xlsx\",\"txt\"]}',
    '1'
),(
    NULL,
    'affiliate_payout_file',
    'allowed_file_count',
    '5',
    '1'
),(
    NULL,
    'affiliate_payout_file',
    'allowed_file_size',
    '16000',
    '1'
),(
    NULL,
    'affiliate_payout_file',
    'total_file_count',
    '10',
    '1'
);
-- payout proof image file configuration ---

-- added created_at and updated_at in affiliate table--
ALTER TABLE
    `tblaffilates` ADD `created_at` DATETIME NULL AFTER `is_active`,
    ADD `updated_at` DATETIME NULL AFTER `created_at`;

-- added created_at and updated_at in affiliate table--

-- updated commission field to decimal type in affiliate table--
ALTER TABLE `tblaffilates` CHANGE `commission` `commission` DECIMAL(15,2) NOT NULL DEFAULT '0.00';
-- updated commission field to decimal type in affiliate table--

-- Affiliate account type in affiliate table an earning status in affilaite earning table queries--

ALTER TABLE `tblaffiliate_account` ADD `is_primary` ENUM('0','1') NOT NULL DEFAULT '0' AFTER `account_info`;

ALTER TABLE `tblaffiliate_earning` ADD `status_comment` TEXT NULL AFTER `status`, ADD `status_updated_by_id` BIGINT NULL AFTER `status_comment`;

ALTER TABLE `tblaffiliate_earning` ADD `updated_at` DATE NULL AFTER `created_at`;

-- Affiliate account type in affiliate table an earning status in affilaite earning table queries--

-- Affiliate Table Date Column Changes To Date Time --
    ALTER TABLE `tblaffiliate_earning` CHANGE `created_at` `created_at` DATETIME NOT NULL, CHANGE `updated_at` `updated_at` DATETIME NULL DEFAULT NULL;

	ALTER TABLE `tblaffiliate_payout` CHANGE `payout_date` `payout_date` DATETIME NOT NULL;

	ALTER TABLE `tblaffiliate_tracking` CHANGE `created_at` `created_at` DATETIME NOT NULL;
-- Affiliate Table Date Column Changes To Date Time --
