-- =============================================================================
-- DataGrip 手工迁移（2026-07-16）
-- 库名：chajia
-- 用法：仅在本机 DataGrip 执行即可改库；服务器上不要跑任何 migrate / 改库命令
-- =============================================================================

USE `chajia`;

-- 1) API Key 密文列（已存在报 Duplicate column → 忽略）
ALTER TABLE `service_api_key`
    ADD COLUMN `token_ciphertext` TEXT NULL;

-- 2) 客户承运商账密表（已存在报 Already exists → 忽略）
CREATE TABLE `customer_provider_credential` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id` VARCHAR(32) NOT NULL,
    `provider` VARCHAR(16) NOT NULL,
    `login_email` VARCHAR(255) NOT NULL,
    `password_ciphertext` TEXT NOT NULL,
    `updated_by` VARCHAR(32) NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`),
    UNIQUE INDEX `uq_customer_provider`(`customer_id`, `provider`),
    INDEX `idx_cpc_customer`(`customer_id`, `is_deleted`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 自检（可选，执行后跑）：
-- SHOW COLUMNS FROM `service_api_key` LIKE 'token_ciphertext';
-- SHOW TABLES LIKE 'customer_provider_credential';