payment-platform/java/sql/002_service_provider.sql

-- ============================================
-- 支付宝多服务商架构 — DDL + 初始数据
-- 执行前请确认所在数据库为 payment_platform_prod
-- ============================================

-- 1. 创建服务商表
CREATE TABLE IF NOT EXISTS pay_service_provider (
    id                BIGSERIAL PRIMARY KEY,
    name              VARCHAR(64)  NOT NULL,
    scope_label       VARCHAR(64)  DEFAULT 'DOMESTIC',
    app_id            VARCHAR(64)  NOT NULL,
    app_private_key   TEXT         NOT NULL,
    alipay_public_key TEXT         NOT NULL,
    server_url        VARCHAR(256) DEFAULT 'https://openapi.alipay.com/gateway.do',
    sign_type         VARCHAR(16)  DEFAULT 'RSA2',
    format            VARCHAR(16)  DEFAULT 'JSON',
    charset           VARCHAR(16)  DEFAULT 'UTF-8',
    provider_status   VARCHAR(32)  DEFAULT 'ACTIVE',
    description       VARCHAR(512),
    status            VARCHAR(1)   DEFAULT '0',    -- MyBatis-Plus @TableLogic 软删除
    uuid              VARCHAR(64),
    created_time      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 插入当前服务商（密钥来自旧 application.yml）
INSERT INTO pay_service_provider (name, scope_label, app_id, app_private_key, alipay_public_key, server_url, sign_type, format, charset, provider_status, status)
VALUES (
    '国内服务商',
    'DOMESTIC',
    '2021005122654283',
    'MIIEogIBAAKCAQEAnhU7Ix3+klh7k+5+Vx10tmqS2MkWD3MIkB+GpwZm94SccEISoLjCatW56U1QJo7D9SP1rSwyJ3dtvbiEpje8f0D+iaHilX9tkdWZMnsI9XbQqmqLkjDPFdmII3sa4p9Mx1UUOtIGmPhKYmnR8jM85SEmf9ivuFOKZISny5LxDYam2o3czGKjRiLgb7oNs4LKf9isc1k4r/XeCEAhLPejFgilSiKWC8DQbiSG15DvOKrr0BxM1gb0YUngQr11diUSYx5LRXWjhUE7WkPaSGkLDVuZ+ZAHddH9DddPrfjoQpiq7ROJNTkxVuNo5Bqont5fvLnzIN9/9pR8bJVLNBDFlQIDAQABAoIBADbXhryYG0kKCVU41/vA7EycjHVIza5uafoV9dDcF7ym9N69DVlUv53wp56Yg8XcoX7aCtEZFA09EYVQDjTcATjkg2mcD89tdcWyJIOuy1zc62czr1f4Nt+Np/0nKByWxzwf9/SwCDnpaWTa8UrlG6sh5QlVUyDPWqOkodGuGJJoJoaC/yM2kXjovR/JbKvqevRRI/ZpqUU8OXU1MzBuYH3OEWmYjDFpydIJnEYRS9U4Ftgo3q8diRbTSb1rPR5cxyJh6ikoRNLnrWuEX/B2QW32Qag6vYrjq+LXtW7hzZYoNUAaEzf2c8WP9nrzw3IBvAF+sBBhiyH8wlxcSEndR8ECgYEA8Ka2sy4iTuLkf9Qxd22IRAxXgnkh7ITB71OM4OIU5dZULysAbnwTc/9B6URyuQ7o64VFQQHZtXLM/Ujt2l09RU2caUBkzRS495g6H53mZSY78k4F6nI9eEqTE0PPRiPZ/NrV1ZeUdOU4p1iaO6KxHJrMY5hCRMns5OX/jm4DfhsCgYEAqCpdfpTRqW03pMpM0MN3S43Z4xdthxeBDa0mGGo3CpQImj8WvVaAMiTNjnH/NBupXT/akn6lURQANo9IjR4EvoA8vgtYBYOrB+kchDRwbl4elAzdGYcUWixBnuKaz23yncOFXai0RwcnSZ57lF++fUlC778u2MUoLGk8JeozRg8CgYAJDSw4BxcxQmV3zWJi7JLAhHpSJP46qC+nMcxNtRM2Jd6au5JTlYUhyssO3A04mq/2E9gr/sbShVPifvi7SuCAF9A3QT3JXOssHOSqxcShr1N35KliTRO0z5FCmz1TlQug8BY053OlBe4glTuP3Kmuur/PSy3K6pFndqMrF0Y4WwKBgGbaopyNQT8zQdlUsm7tXsWjWSUAa0k5IwHOaYJ9VHTv3eMZuzrK8VW6FM+PAdxJHumd6H25YDua1BaKxIErXxN1M7G5Fnko8y6/cWWa/TlD78f1pjW63MPaVbhsOOut/7pmn+eNC9Z1lZ39hPBXLxdJ+9rPQEPtMokXRGacPAgZAoGATt/Rko7fkFST19JOiMorFyp26cZQ91ZLrPADRIZEN4H9ShZhHTeSBN32ZGUFYsOjLzM8g0Xui2w2NAQBrmeHCGNXrV+vFRX6+s5cccUtohtpjYK07mz4RbeScWL/oebNubDR1XsdiUKe+JQsHT5MMksKk/kmBbIfIJ3wDdeJLlk=',
    'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqUX9WqZPwh57HqR2RiAEYe8GWrXl8Zz9YC7dz7dnfGY1k/ma8/w18TC7txausbCCHCEWl52836+gdD1uQumCxPYPtkoWcZy8984kCE3whaV9O2PoaCd6Q3/Ww0WpRAvJDGDpa032t3vuTPSUbbU0N8iYfPua9a8z1JjbD4hDiIVMN797PllUDQEuIfNV+C06usGZxL01e/zBdMiIun1HhrVqHQ+p+GjHQXZ58kqP1EIGOg4/1HPib5i4umXULnVsCYZ0dvvdyrSiJxCnqMZZmtVSVm9rA+TYaiEoVf1RlchZPOKBFhF1gMGRsYh3LPL9cU4lbqt6DpZiLggsudD4NQIDAQAB',
    'https://openapi.alipay.com/gateway.do',
    'RSA2', 'JSON', 'UTF-8',
    'ACTIVE', '0'
);

-- 3. pay_enterprise 加字段
ALTER TABLE pay_enterprise ADD COLUMN IF NOT EXISTS service_provider_id BIGINT;
ALTER TABLE pay_enterprise ADD COLUMN IF NOT EXISTS scope_label VARCHAR(64);

-- 4. 旧企业全部绑定到第一个服务商
UPDATE pay_enterprise SET service_provider_id = 1, scope_label = 'DOMESTIC' WHERE service_provider_id IS NULL;