qipai/database/migrations/2026062218_m05d_profit_sharing.up.sql

CREATE TABLE IF NOT EXISTS qipai_collection_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  platform_app_id BIGINT UNSIGNED NULL,
  store_id BIGINT UNSIGNED NULL,
  provider VARCHAR(32) NOT NULL DEFAULT 'WECHAT',
  scope_key VARCHAR(255) NOT NULL,
  merchant_id VARCHAR(64) NOT NULL,
  credential_ref VARCHAR(255) NOT NULL,
  authorization_status VARCHAR(32) NOT NULL DEFAULT 'UNAUTHORIZED',
  profit_sharing_enabled TINYINT(1) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_collection_account_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_collection_account_app
    FOREIGN KEY (platform_app_id) REFERENCES qipai_platform_apps(id),
  CONSTRAINT fk_qipai_collection_account_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  UNIQUE KEY uq_qipai_collection_account_scope
    (tenant_id, provider, scope_key),
  KEY idx_qipai_collection_account_resolution
    (tenant_id, provider, enabled, store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_profit_share_receivers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  collection_account_id BIGINT UNSIGNED NOT NULL,
  receiver_type VARCHAR(32) NOT NULL,
  receiver_hash CHAR(64) NOT NULL,
  receiver_masked VARCHAR(128) NOT NULL,
  receiver_credential_ref VARCHAR(255) NOT NULL,
  relation_type VARCHAR(32) NOT NULL,
  name VARCHAR(128) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  authorization_status VARCHAR(32) NOT NULL DEFAULT 'UNAUTHORIZED',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_profit_receiver_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_profit_receiver_account
    FOREIGN KEY (collection_account_id) REFERENCES qipai_collection_accounts(id),
  UNIQUE KEY uq_qipai_profit_receiver_hash
    (tenant_id, collection_account_id, receiver_hash),
  KEY idx_qipai_profit_receiver_enabled
    (tenant_id, collection_account_id, enabled, authorization_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_profit_share_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  collection_account_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NULL,
  receiver_id BIGINT UNSIGNED NOT NULL,
  scope_key VARCHAR(255) NOT NULL,
  percentage_bps SMALLINT UNSIGNED NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_profit_policy_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_profit_policy_account
    FOREIGN KEY (collection_account_id) REFERENCES qipai_collection_accounts(id),
  CONSTRAINT fk_qipai_profit_policy_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_profit_policy_receiver
    FOREIGN KEY (receiver_id) REFERENCES qipai_profit_share_receivers(id),
  UNIQUE KEY uq_qipai_profit_policy_receiver
    (tenant_id, collection_account_id, scope_key, receiver_id),
  KEY idx_qipai_profit_policy_resolution
    (tenant_id, collection_account_id, store_id, enabled),
  CONSTRAINT chk_qipai_profit_policy_bps
    CHECK (percentage_bps > 0 AND percentage_bps <= 10000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE qipai_profit_shares
  ADD COLUMN collection_account_id BIGINT UNSIGNED NULL AFTER order_id,
  ADD COLUMN receiver_id BIGINT UNSIGNED NULL AFTER collection_account_id,
  ADD COLUMN client_request_id VARCHAR(128) NULL AFTER share_no,
  ADD COLUMN batch_request_id VARCHAR(128) NULL AFTER client_request_id,
  ADD COLUMN percentage_bps SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER receiver_ref,
  ADD COLUMN failure_code VARCHAR(64) NOT NULL DEFAULT '' AFTER status,
  ADD COLUMN raw_response JSON NULL AFTER provider_share_id,
  ADD CONSTRAINT fk_qipai_profit_share_account
    FOREIGN KEY (collection_account_id) REFERENCES qipai_collection_accounts(id),
  ADD CONSTRAINT fk_qipai_profit_share_receiver
    FOREIGN KEY (receiver_id) REFERENCES qipai_profit_share_receivers(id),
  ADD UNIQUE KEY uq_qipai_profit_share_request (tenant_id, client_request_id),
  ADD KEY idx_qipai_profit_share_batch (tenant_id, batch_request_id),
  ADD UNIQUE KEY uq_qipai_profit_share_payment_receiver
    (tenant_id, payment_id, receiver_id);

INSERT IGNORE INTO qipai_schema_migrations (version, name)
VALUES ('2026062218', 'm05d_profit_sharing');