<?php

if (!defined('ABSPATH')) { exit; }
require_once dirname( __DIR__, 2 ) . '/class-aieo-order-store.php'; 

if (!defined('AIEO_UV_POINTS_ALIGNED_OPTION')) {
    define('AIEO_UV_POINTS_ALIGNED_OPTION', 'aieo_uv_eshop_points_decimal');
}

function aieo_install_user_vitals_schema() {
    global $wpdb;
    $charset_collate = function_exists('aieo_get_charset_collate')
        ? aieo_get_charset_collate()
        : $wpdb->get_charset_collate();

    $tbl = $wpdb->prefix . 'aieo_user_vitals';
    
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$tbl} (
        `user_id`                       bigint unsigned NOT NULL,
        `soft1_customer_name`           varchar(255)    NULL DEFAULT NULL,
        `soft1_customer_id`             bigint          NULL DEFAULT NULL,
        `soft1_customer_ids`            varchar(255)    NULL DEFAULT NULL,
        `soft1_loyalty_id`              bigint          NULL DEFAULT NULL,
        `soft1_turnover`                decimal(14,2)   NULL DEFAULT NULL,
        `soft1_loyalty_points`          bigint          NULL DEFAULT NULL,
        `soft1_loyalty_points_total`    bigint          NULL DEFAULT NULL,
        `eshop_loyalty_points`          decimal(14,2)   NULL DEFAULT NULL,
        `soft1_customer_locations`      varchar(255)    NULL DEFAULT NULL,
        `soft1_latest_location`         varchar(191)    NULL DEFAULT NULL,
        `soft1_recorded_speciality`     varchar(191)    NULL DEFAULT NULL,
        `soft1_latest_transaction_date` datetime        NULL DEFAULT NULL,
        `soft1_first_transaction_date`  datetime        NULL DEFAULT NULL,
        `eshop_latest_transaction_date` datetime        NULL DEFAULT NULL,
        `migrated_at`                   datetime        NULL DEFAULT NULL,
        `updated_at`                    datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`user_id`),
        KEY `idx_customer_id` (`soft1_customer_id`),
        KEY `idx_loyalty_id` (`soft1_loyalty_id`),
        KEY `idx_speciality` (`soft1_recorded_speciality`),
        KEY `idx_latest_txn` (`soft1_latest_transaction_date`),
        KEY `idx_turnover` (`soft1_turnover`)
    ) {$charset_collate};");

    aieo_user_vitals_align_eshop_points_column();
    aieo_user_vitals_backfill_eshop_latest_txn_once();
}

function aieo_user_vitals_align_eshop_points_column() {
    global $wpdb;

    
    if (get_option(AIEO_UV_POINTS_ALIGNED_OPTION) === '1') {
        return;
    }

    
    
    if (!is_admin() && !(defined('WP_CLI') && WP_CLI) && !wp_doing_cron()) {
        return;
    }

    $tbl = $wpdb->prefix . 'aieo_user_vitals';

    $type = $wpdb->get_var($wpdb->prepare(
        "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
        $tbl,
        'eshop_loyalty_points'
    ));

    
    
    if ($type === null) {
        return;
    }

    if (strtolower((string) $type) !== 'decimal') {
        
        $wpdb->query("ALTER TABLE `{$tbl}` MODIFY COLUMN `eshop_loyalty_points` decimal(14,2) NULL DEFAULT NULL");
        aieo_user_vitals_backfill_eshop_points();
    }

    
    
    update_option(AIEO_UV_POINTS_ALIGNED_OPTION, '1', true);
}

function aieo_user_vitals_backfill_eshop_points() {
    global $wpdb;

    $tbl    = $wpdb->prefix . 'aieo_user_vitals';
    $ledger = $wpdb->prefix . 'aieo_dmm_loyalty_points';

    $has_ledger = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $ledger));
    if (!$has_ledger) {
        return 0;
    }

    
    $done = $wpdb->query(
        "INSERT INTO `{$tbl}` (`user_id`, `eshop_loyalty_points`)
         SELECT `user_id`, SUM(`delta`) FROM `{$ledger}`
         WHERE `user_id` > 0
         GROUP BY `user_id`
         ON DUPLICATE KEY UPDATE `eshop_loyalty_points` = VALUES(`eshop_loyalty_points`)"
    );

    return (int) $done;
}

function aieo_user_vitals_backfill_eshop_latest_txn_once() {
    if (get_option('aieo_uv_eshop_txn_backfilled') === '1') {
        return;
    }
    if (!is_admin() && !(defined('WP_CLI') && WP_CLI) && !wp_doing_cron()) {
        return;
    }
    $done = aieo_user_vitals_backfill_eshop_latest_txn();
    if ($done !== null) {
        update_option('aieo_uv_eshop_txn_backfilled', '1', true);
    }
}

function aieo_user_vitals_backfill_eshop_latest_txn() {
    global $wpdb;

    $tbl      = $wpdb->prefix . 'aieo_user_vitals';
    $statuses = class_exists('AIEO_DMM_User_Vitals')
        ? AIEO_DMM_User_Vitals::txn_statuses()
        : array('pending', 'processing', 'on-hold', 'completed'); 
    $in       = "'" . implode("','", array_map('esc_sql', array_map(static function ($s) {
        return 'wc-' . $s;
    }, $statuses))) . "'";

    $hpos_tbl = $wpdb->prefix . 'wc_orders';
    
    $has_hpos = AIEO_Order_Store::uses_hpos() && (bool) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $hpos_tbl));

    if ($has_hpos) {
        
        $done = $wpdb->query(
            "INSERT INTO `{$tbl}` (`user_id`, `eshop_latest_transaction_date`)
             SELECT o.customer_id, MAX(o.date_created_gmt)
               FROM `{$hpos_tbl}` o
              WHERE o.customer_id > 0
                AND o.type = 'shop_order'
                AND o.status IN ({$in})
              GROUP BY o.customer_id
             ON DUPLICATE KEY UPDATE `eshop_latest_transaction_date` = VALUES(`eshop_latest_transaction_date`)"
        );
        return (int) $done;
    }

    $has_posts = (bool) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->posts));
    if (!$has_posts) {
        return null;
    }
    
    
    $done = $wpdb->query(
        "INSERT INTO `{$tbl}` (`user_id`, `eshop_latest_transaction_date`)
         SELECT CAST(pm.meta_value AS UNSIGNED), MAX(p.post_date_gmt)
           FROM `{$wpdb->posts}` p
           JOIN `{$wpdb->postmeta}` pm ON pm.post_id = p.ID AND pm.meta_key = '_customer_user'
          WHERE p.post_type = 'shop_order'
            AND p.post_status IN ({$in})
            AND CAST(pm.meta_value AS UNSIGNED) > 0
          GROUP BY CAST(pm.meta_value AS UNSIGNED)
         ON DUPLICATE KEY UPDATE `eshop_latest_transaction_date` = VALUES(`eshop_latest_transaction_date`)"
    );
    return (int) $done;
}