ylrz_saas_pharmacy/java/sql/V20260709_01__lobster_profile_stats_projection.sql

-- =====================================================
-- Profile Hub: stats cache table + summary projection columns (schema only)
-- Target: tenant DB (fs_tenant_*)
-- Data backfill: run apply-lobster-profile-migration-all-tenants.js
-- =====================================================

CREATE TABLE IF NOT EXISTS lobster_profile_stats (
  company_id         BIGINT       NOT NULL PRIMARY KEY COMMENT '0=tenant total, >0=company scope',
  total_profiles     BIGINT       NOT NULL DEFAULT 0,
  enriched_count     BIGINT       NOT NULL DEFAULT 0,
  interaction_sum    BIGINT       NOT NULL DEFAULT 0,
  avg_conversations  DECIMAL(10,2) NOT NULL DEFAULT 0,
  active_today       BIGINT       NOT NULL DEFAULT 0,
  refresh_time       DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='lobster profile stats cache';

SET @dbname = DATABASE();
SET @tablename = 'lobster_user_profile';

SET @columnname = 'latest_stage';
SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME=@tablename AND COLUMN_NAME=@columnname)>0,'SELECT 1','ALTER TABLE lobster_user_profile ADD COLUMN latest_stage VARCHAR(64) DEFAULT NULL COMMENT ''latest stage'' AFTER variable_snapshot'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @columnname = 'latest_customer_attitude';
SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME=@tablename AND COLUMN_NAME=@columnname)>0,'SELECT 1','ALTER TABLE lobster_user_profile ADD COLUMN latest_customer_attitude VARCHAR(32) DEFAULT NULL COMMENT ''latest attitude'' AFTER latest_stage'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @columnname = 'latest_summary_text';
SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME=@tablename AND COLUMN_NAME=@columnname)>0,'SELECT 1','ALTER TABLE lobster_user_profile ADD COLUMN latest_summary_text VARCHAR(512) DEFAULT NULL COMMENT ''latest summary snippet'' AFTER latest_customer_attitude'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @columnname = 'profile_aggregate_preview';
SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME=@tablename AND COLUMN_NAME=@columnname)>0,'SELECT 1','ALTER TABLE lobster_user_profile ADD COLUMN profile_aggregate_preview VARCHAR(420) DEFAULT NULL COMMENT ''list aggregate preview'' AFTER latest_summary_text'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @columnname = 'latest_summary_time';
SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME=@tablename AND COLUMN_NAME=@columnname)>0,'SELECT 1','ALTER TABLE lobster_user_profile ADD COLUMN latest_summary_time DATETIME DEFAULT NULL COMMENT ''latest summary time'' AFTER profile_aggregate_preview'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='lobster_user_profile' AND INDEX_NAME='idx_profile_latest_stage')>0,'SELECT 1','CREATE INDEX idx_profile_latest_stage ON lobster_user_profile (company_id, latest_stage)'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='lobster_user_profile' AND INDEX_NAME='idx_profile_latest_attitude')>0,'SELECT 1','CREATE INDEX idx_profile_latest_attitude ON lobster_user_profile (company_id, latest_customer_attitude)'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME='lobster_user_profile' AND INDEX_NAME='idx_profile_latest_summary_time')>0,'SELECT 1','CREATE INDEX idx_profile_latest_summary_time ON lobster_user_profile (company_id, latest_summary_time)'));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;