CREATE TABLE tblstaff_referral_details (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    tenant_id bigint(20) unsigned NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tbltenants(id) ON UPDATE CASCADE,
    staff_id bigint(20) unsigned NOT NULL,
    FOREIGN KEY (staff_id) REFERENCES tblstaff(staffid) ON UPDATE CASCADE,
    referral_campaign_code varchar(50) NOT NULL,
    total_referrals INT(10) NOT NULL DEFAULT 0,
    valid_referrals INT(10) NOT NULL DEFAULT 0,
    tenants_already_counted MEDIUMTEXT NULL DEFAULT NULL, 
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tblstaff_referral_rewards_claimed (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    tenant_id bigint(20) unsigned NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tbltenants(id) ON UPDATE CASCADE,
    staff_id bigint(20) unsigned NOT NULL,
    FOREIGN KEY (staff_id) REFERENCES tblstaff(staffid) ON UPDATE CASCADE,
    reward_code varchar(50) NOT NULL,
    status varchar(50) NOT NULL,
    reward_selected varchar(100) NULL DEFAULT NULL,
    address MEDIUMTEXT NULL DEFAULT NULL,
    total_referrals INT(10) NOT NULL DEFAULT 0,
    valid_referrals INT(10) NOT NULL DEFAULT 0,
    tenants_already_counted MEDIUMTEXT NULL DEFAULT NULL, 
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tblapad_options` (`id`, `type`, `name`, `value`, `form_input_type`, `autoload`) VALUES (NULL, NULL, 'current_referral_campaign_code', 'labor_day_2024', 'text', '1');