-- Add soft-delete is_del to prc_price_set_product / prc_price_set_detail
SET NAMES utf8mb4;
SET @db := DATABASE();

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE prc_price_set_product ADD COLUMN is_del char(1) NOT NULL DEFAULT ''0'' COMMENT ''是否删除 0未删除 1已删除'' AFTER pef_id, ADD KEY idx_psp_is_del (is_del)',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'prc_price_set_product' AND COLUMN_NAME = 'is_del'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE prc_price_set_detail ADD COLUMN is_del char(1) NOT NULL DEFAULT ''0'' COMMENT ''是否删除 0未删除 1已删除'' AFTER expire_date, ADD KEY idx_psd_is_del (is_del)',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'prc_price_set_detail' AND COLUMN_NAME = 'is_del'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE prc_price_set_product SET is_del = '0' WHERE is_del IS NULL OR is_del = '';
UPDATE prc_price_set_detail SET is_del = '0' WHERE is_del IS NULL OR is_del = '';

-- Active-only unique: soft-deleted rows do not block re-insert of same product_code
SET @sql := (
  SELECT IF(COUNT(*) > 0,
    'ALTER TABLE prc_price_set_product DROP INDEX uk_psp_psv_code',
    'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'prc_price_set_product' AND INDEX_NAME = 'uk_psp_psv_code'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE prc_price_set_product ADD COLUMN active_product_code varchar(20) GENERATED ALWAYS AS (CASE WHEN IFNULL(is_del,''0'') = ''0'' THEN product_code ELSE NULL END) STORED COMMENT ''未删除时等于product_code，用于唯一约束''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'prc_price_set_product' AND COLUMN_NAME = 'active_product_code'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE prc_price_set_product ADD UNIQUE KEY uk_psp_psv_active_code (psv_id, active_product_code)',
    'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'prc_price_set_product' AND INDEX_NAME = 'uk_psp_psv_active_code'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;