PNG %k25u25%fgd5n! /home/mkuwqnjx/palaknaturals.com/wp-content/plugins/echo-rewards/includes/Ecre_Tables.php
<?php
/**
* File: Ecre_Tables.php
*
* Database table creation and management for ECRE referral system.
* This class handles only table creation and structure management,
* following WordPress coding standards and best practices.
*
* @package ECRE
* @since 1.0.0
*/
namespace ECRE;
if ( ! class_exists( 'ECRE\Ecre_Tables' ) ) {
/**
* Handles database table creation and structure management
* for the ECRE referral system.
*
* @since 1.0.0
*/
class Ecre_Tables {
/**
* Current database version
*
* @var string
*/
const DB_VERSION = '1.1.0';
/**
* Constructor
*/
public function __construct() {
// Prevent multiple hook registrations.
static $hooks_added = false;
if ( ! $hooks_added ) {
// Create tables immediately when class is instantiated.
$this->ensure_tables_exist();
// Check and create tables on admin_init for existing installations.
add_action( 'admin_init', array( $this, 'check_and_create_tables' ) );
$hooks_added = true;
}
// Hook into plugin activation for new installations.
register_activation_hook( ECRE_FILE, array( $this, 'create_tables_on_activation' ) );
}
/**
* Create all referral system tables
*
* @return void
*/
public function create_all_tables() {
$this->create_ecre_referrers_table();
$this->create_ecre_referrals_orders_table();
$this->create_ecre_rewards_orders_table();
$this->create_ecre_reward_coupons_table();
$this->create_ecre_referrars_dashboard_summary_table();
$this->create_ecre_user_reward_settings_table();
update_option( 'ecre_db_version', self::DB_VERSION );
}
/**
* Create the ecre_referrars_dashboard_summary table
* Initialize with zeros only - no expensive data fetching
*/
public function create_ecre_referrars_dashboard_summary_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'ecre_referrars_dashboard_summary';
$sql = "CREATE TABLE $table_name (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-- Dashboard summary statistics
total_referrers int(11) DEFAULT 0,
total_referrals int(11) DEFAULT 0,
total_referral_sales decimal(15,2) DEFAULT 0.00,
total_rewards_issued int(11) DEFAULT 0,
total_discount_given decimal(15,2) DEFAULT 0.00,
-- Additional dashboard metrics
active_referrers int(11) DEFAULT 0,
average_conversion_rate decimal(5,2) DEFAULT 0.00,
-- Period tracking
summary_period varchar(20) DEFAULT 'all_time',
-- Timestamps
last_updated datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_summary_period (summary_period),
KEY idx_last_updated (last_updated)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
if ( $this->table_exists( $table_name ) ) {
// Just insert a zero record - no data fetching.
$this->create_empty_dashboard_summary_record();
}
}
/**
* Create empty dashboard summary record with zeros
* No expensive queries, just insert zeros
*/
private function create_empty_dashboard_summary_record() {
global $wpdb;
// Check if record already exists.
$exists = $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(*) FROM `{$wpdb->prefix}ecre_referrars_dashboard_summary` WHERE summary_period = %s",
'all_time'
)
);
if ( 0 === (int) $exists ) {
// Insert zero-initialized record.
$wpdb->insert(
$wpdb->prefix . 'ecre_referrars_dashboard_summary',
array(
'total_referrers' => 0,
'total_referrals' => 0,
'total_referral_sales' => 0.00,
'total_rewards_issued' => 0,
'total_discount_given' => 0.00,
'active_referrers' => 0,
'average_conversion_rate' => 0.00,
'summary_period' => 'all_time',
),
array( '%d', '%d', '%f', '%d', '%f', '%d', '%f', '%s' )
);
}
}
/**
* Create the ecre_referrers table
*
* This table stores referrer summary data with pre-calculated totals
* to avoid expensive SUM operations on queries.
*
* @return void
*/
public function create_ecre_referrers_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'ecre_referrers';
$sql = "CREATE TABLE $table_name (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id bigint(20) UNSIGNED NOT NULL,
-- Referral coupon information
referral_coupon_code varchar(100) DEFAULT NULL,
referral_coupon_id bigint(20) UNSIGNED DEFAULT NULL,
-- Pre-calculated totals (updated with +/- operations)
total_referrals int(11) DEFAULT 0,
total_sales decimal(10,2) DEFAULT 0.00,
total_rewards_issued int(11) DEFAULT 0,
total_discount decimal(10,2) DEFAULT 0.00,
conversion_rate decimal(5,2) DEFAULT 0.00,
-- Flags
custom_rewards tinyint(1) DEFAULT 0,
custom_referrals tinyint(1) DEFAULT 0,
-- Date tracking
first_referral_date datetime DEFAULT NULL,
last_referral_date datetime DEFAULT NULL,
-- Timestamps
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY user_id (user_id),
KEY idx_referral_coupon_code (referral_coupon_code),
KEY idx_referral_coupon_id (referral_coupon_id),
KEY idx_total_sales (total_sales),
KEY idx_total_referrals (total_referrals),
KEY idx_updated_at (updated_at)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* Create the ecre_referrals_orders table
*
* This table stores individual referral order records
* linking referrers to their referred orders.
*
* @return void
*/
public function create_ecre_referrals_orders_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'ecre_referrals_orders';
$sql = "CREATE TABLE $table_name (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-- Referrer information
referrer_user_id bigint(20) UNSIGNED NOT NULL,
-- Order/transaction information
order_id bigint(20) UNSIGNED NOT NULL,
referred_user_id bigint(20) UNSIGNED DEFAULT NULL, -- NULL if guest checkout
coupon_code varchar(100) NOT NULL,
coupon_id bigint(20) UNSIGNED DEFAULT NULL,
-- Financial data
order_value decimal(10,2) DEFAULT 0.00,
discount_amount decimal(10,2) DEFAULT 0.00,
-- Customer information
firstname varchar(100) DEFAULT '',
lastname varchar(100) DEFAULT '',
fullname varchar(255) DEFAULT '',
customer_email varchar(255) NOT NULL,
-- Status and dates
referral_status varchar(50) DEFAULT 'pending',
order_date datetime DEFAULT NULL,
-- Timestamps
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY unique_order_referrer (order_id, referrer_user_id),
KEY idx_referrer_user_id (referrer_user_id),
KEY idx_order_id (order_id),
KEY idx_coupon_code (coupon_code),
KEY idx_referral_status (referral_status),
KEY idx_order_date (order_date)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* Create the ecre_rewards_orders table
*
* This table stores records when reward coupons are actually used in orders,
* linking back to the original referral that generated the reward.
*
* @return void
*/
public function create_ecre_rewards_orders_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'ecre_rewards_orders';
$sql = "CREATE TABLE $table_name (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-- Reward coupon information
reward_coupon_id bigint(20) UNSIGNED NOT NULL,
reward_coupon_code varchar(100) NOT NULL,
-- Order where reward was used
order_id bigint(20) UNSIGNED NOT NULL,
user_id bigint(20) UNSIGNED DEFAULT NULL, -- User who used the reward (may be different from referrer)
-- Link back to original referral
referral_user_id bigint(20) UNSIGNED NOT NULL,
referral_coupon_id bigint(20) UNSIGNED DEFAULT NULL,
referral_coupon_code varchar(100) DEFAULT NULL,
-- Financial impact
reward_discount_amount decimal(10,2) DEFAULT 0.00,
order_total decimal(10,2) DEFAULT 0.00,
order_subtotal decimal(10,2) DEFAULT 0.00,
-- Customer information (who used the reward)
customer_email varchar(255) DEFAULT '',
firstname varchar(100) DEFAULT '',
lastname varchar(100) DEFAULT '',
fullname varchar(255) DEFAULT '',
-- Usage details
usage_date datetime DEFAULT NULL,
reward_status varchar(50) DEFAULT 'pending',
-- Tracking fields
reward_type varchar(50) DEFAULT 'referral_reward', -- For future expansion
notes text DEFAULT NULL,
-- Timestamps
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY unique_reward_order (reward_coupon_id, order_id),
KEY idx_reward_coupon_code (reward_coupon_code),
KEY idx_order_id (order_id),
KEY idx_user_id (user_id),
KEY idx_referral_user_id (referral_user_id),
KEY idx_referral_coupon_id (referral_coupon_id),
KEY idx_referral_coupon_code (referral_coupon_code),
KEY idx_usage_date (usage_date),
KEY idx_reward_status (reward_status),
KEY idx_reward_type (reward_type),
KEY idx_customer_email (customer_email),
KEY idx_fullname (fullname),
KEY idx_created_at (created_at)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* Create the ecre_reward_coupons table
*
* This table stores all generated reward coupons
* for tracking and management purposes.
*
* @return void
*/
public function create_ecre_reward_coupons_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'ecre_reward_coupons';
$sql = "CREATE TABLE $table_name (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-- Referrer information
referrer_user_id bigint(20) UNSIGNED NOT NULL,
order_id bigint(20) UNSIGNED NOT NULL,
-- Coupon details
coupon_code varchar(100) NOT NULL,
coupon_id bigint(20) UNSIGNED DEFAULT NULL,
discount_type varchar(20) NOT NULL DEFAULT 'fixed_cart',
discount_amount decimal(10,2) NOT NULL DEFAULT 0.00,
-- Usage information
usage_limit int(11) NOT NULL DEFAULT 1,
usage_count int(11) NOT NULL DEFAULT 0,
-- Status and expiry
status varchar(20) NOT NULL DEFAULT 'active',
expires_at datetime DEFAULT NULL,
-- Product/category restrictions (separate columns for better control)
include_products text DEFAULT NULL,
exclude_products text DEFAULT NULL,
include_product_categories text DEFAULT NULL,
exclude_product_categories text DEFAULT NULL,
-- Timestamps
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY coupon_code (coupon_code),
KEY idx_referrer_user_id (referrer_user_id),
KEY idx_order_id (order_id),
KEY idx_coupon_id (coupon_id),
KEY idx_status (status),
KEY idx_created_at (created_at),
KEY idx_expires_at (expires_at)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* Create the ecre_user_reward_settings table
*
* This table stores user-specific reward settings that override global defaults
*
* @return void
*/
public function create_ecre_user_reward_settings_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'ecre_user_reward_settings';
$sql = "CREATE TABLE $table_name (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-- User identification
user_id bigint(20) UNSIGNED NOT NULL,
-- Setting details
setting_key varchar(100) NOT NULL,
setting_value text DEFAULT NULL,
data_type varchar(20) DEFAULT 'string',
-- Metadata
is_active tinyint(1) DEFAULT 1,
-- Timestamps
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY unique_user_setting (user_id, setting_key),
KEY idx_user_id (user_id),
KEY idx_setting_key (setting_key),
KEY idx_is_active (is_active)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
/**
* Ensure tables exist - called on init
*
* @return void
*/
public function ensure_tables_exist() {
$this->check_and_create_tables();
}
/**
* Create tables on plugin activation
*
* @return void
*/
public function create_tables_on_activation() {
$this->create_all_tables();
}
/**
* Check if tables exist and create them if they don't
*
* @return void
*/
public function check_and_create_tables() {
// Check if we've already run this check recently to avoid running on every admin page load.
$last_check = get_option( 'ecre_tables_last_check', 0 );
$current_time = time();
// Get table names.
$referrers_table = $this->get_referrers_table_name();
$referrals_table = $this->get_referrals_orders_table_name();
$reward_table = $this->get_rewards_orders_table_name();
$reward_coupons_table = $this->get_reward_coupons_table_name();
$dashboard_summary_table = $this->get_referrars_dashboard_summary_table_name();
$user_settings_table = $this->get_user_reward_settings_table_name();
// Check if tables exist.
$referrers_exists = $this->table_exists( $referrers_table );
$referrals_exists = $this->table_exists( $referrals_table );
$rewards_exists = $this->table_exists( $reward_table );
$reward_coupons_exists = $this->table_exists( $reward_coupons_table );
$dashboard_summary_exists = $this->table_exists( $dashboard_summary_table );
$user_settings_exists = $this->table_exists( $user_settings_table );
// Force check if any table is missing, otherwise use cache.
$force_check = ! $referrers_exists || ! $referrals_exists || ! $reward_coupons_exists || ! $rewards_exists || ! $dashboard_summary_exists || ! $user_settings_exists;
// Only check once per hour to avoid performance issues, unless tables are missing.
if ( ! $force_check && ( $current_time - $last_check ) < 3600 ) {
return;
}
// Create table if it doesn't exist.
if ( ! $user_settings_exists ) {
$this->create_ecre_user_reward_settings_table();
}
// Create tables if they don't exist.
if ( ! $referrers_exists ) {
$this->create_ecre_referrers_table();
}
if ( ! $referrals_exists ) {
$this->create_ecre_referrals_orders_table();
}
if ( ! $rewards_exists ) {
$this->create_ecre_rewards_orders_table();
}
if ( ! $reward_coupons_exists ) {
$this->create_ecre_reward_coupons_table();
}
// Add this block for the new dashboard summary table.
if ( ! $dashboard_summary_exists ) {
$this->create_ecre_referrars_dashboard_summary_table();
}
// Update the last check time.
update_option( 'ecre_tables_last_check', $current_time );
}
/**
* Force table creation and clear cache
*
* @return array Results of table creation
*/
public static function force_create_tables() {
// Clear the cache.
delete_option( 'ecre_tables_last_check' );
// Create instance to call instance methods.
$instance = new self();
// Force create tables.
$instance->create_all_tables();
// Check if tables were created successfully.
$results = array(
'referrers_table' => $instance->table_exists( $instance->get_referrers_table_name() ),
'referrals_orders_table' => $instance->table_exists( $instance->get_referrals_orders_table_name() ),
'reward_coupons_table' => $instance->table_exists( $instance->get_reward_coupons_table_name() ),
'rewards_orders_table' => $instance->table_exists( $instance->get_rewards_orders_table_name() ),
'referrars_dashboard_summary_table' => $instance->table_exists( $instance->get_referrars_dashboard_summary_table_name() ),
'user_reward_settings_summary_table' => $instance->table_exists( $instance->get_user_reward_settings_table_name() ),
);
return $results;
}
/**
* Clear table check cache to force immediate table check
*
* @return void
*/
public static function clear_table_cache() {
delete_option( 'ecre_tables_last_check' );
}
/**
* Get table status for debugging
*
* @return array Table existence status
*/
public function get_table_status() {
return array(
'referrers_table' => array(
'name' => $this->get_referrers_table_name(),
'exists' => $this->table_exists( $this->get_referrers_table_name() ),
),
'referrals_orders_table' => array(
'name' => $this->get_referrals_orders_table_name(),
'exists' => $this->table_exists( $this->get_referrals_orders_table_name() ),
),
'reward_coupons_table' => array(
'name' => $this->get_reward_coupons_table_name(),
'exists' => $this->table_exists( $this->get_reward_coupons_table_name() ),
),
'rewards_orders_table' => array(
'name' => $this->get_rewards_orders_table_name(),
'exists' => $this->table_exists( $this->get_rewards_orders_table_name() ),
),
'referrars_dashboard_summary_table' => array(
'name' => $this->get_referrars_dashboard_summary_table_name(),
'exists' => $this->table_exists( $this->get_referrars_dashboard_summary_table_name() ),
),
'user_reward_settings_summary_table' => array(
'name' => $this->get_user_reward_settings_table_name(),
'exists' => $this->table_exists( $this->get_user_reward_settings_table_name() ),
),
);
}
/**
* Helper method to get referrers table name
*
* @return string Table name
*/
public function get_referrers_table_name() {
global $wpdb;
return $wpdb->prefix . 'ecre_referrers';
}
/**
* Helper method to get referrars dashboard summary table name
*
* @return string Table name
*/
public function get_referrars_dashboard_summary_table_name() {
global $wpdb;
return $wpdb->prefix . 'ecre_referrars_dashboard_summary';
}
/**
* Helper method to get referrars dashboard summary table name
*
* @return string Table name
*/
public function get_user_reward_settings_table_name() {
global $wpdb;
return $wpdb->prefix . 'ecre_user_reward_settings';
}
/**
* Helper method to get rewards orders table name
*
* @return string Table name
*/
public function get_rewards_orders_table_name() {
global $wpdb;
return $wpdb->prefix . 'ecre_rewards_orders';
}
/**
* Helper method to get referrals orders table name
*
* @return string Table name
*/
public function get_referrals_orders_table_name() {
global $wpdb;
return $wpdb->prefix . 'ecre_referrals_orders';
}
/**
* Helper method to get reward coupons table name
*
* @return string Table name
*/
public function get_reward_coupons_table_name() {
global $wpdb;
return $wpdb->prefix . 'ecre_reward_coupons';
}
/**
* Check if a database table exists
*
* @param string $table_name Table name to check.
* @return bool True if table exists, false otherwise
*/
private function table_exists( $table_name ) {
global $wpdb;
$result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
return $result === $table_name;
}
}
}