<?php

if (!defined('ABSPATH')) { exit; }

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`          bigint          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};");
}
