-- ============================================================
-- 寄售平台 · 货源广场 数据库增量补丁（phpMyAdmin 直接粘贴执行）
-- 对已有 orders 表补字段 + 新增 2 张货源广场表
-- 幂等：字段/表已存在会跳过，不会报错
-- ============================================================

SET NAMES utf8mb4;

-- ========== orders 表补货源广场相关字段 ==========

DROP PROCEDURE IF EXISTS _ensure_orders_supplier_id;
DELIMITER $$
CREATE PROCEDURE _ensure_orders_supplier_id()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='orders' AND COLUMN_NAME='supplier_id') THEN
        ALTER TABLE `orders` ADD COLUMN `supplier_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '货源广场供货商ID，0=非货源对接' AFTER `settle_at`;
    END IF;
END$$
DELIMITER ;
CALL _ensure_orders_supplier_id(); DROP PROCEDURE IF EXISTS _ensure_orders_supplier_id;

DROP PROCEDURE IF EXISTS _ensure_orders_supply_price;
DELIMITER $$
CREATE PROCEDURE _ensure_orders_supply_price()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='orders' AND COLUMN_NAME='supply_price') THEN
        ALTER TABLE `orders` ADD COLUMN `supply_price` INT NOT NULL DEFAULT 0 COMMENT '供货价快照（分）' AFTER `supplier_id`;
    END IF;
END$$
DELIMITER ;
CALL _ensure_orders_supply_price(); DROP PROCEDURE IF EXISTS _ensure_orders_supply_price;

DROP PROCEDURE IF EXISTS _ensure_orders_supplier_amount;
DELIMITER $$
CREATE PROCEDURE _ensure_orders_supplier_amount()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='orders' AND COLUMN_NAME='supplier_amount') THEN
        ALTER TABLE `orders` ADD COLUMN `supplier_amount` INT NOT NULL DEFAULT 0 COMMENT '供货商实得快照（分，扣平摊fee后）' AFTER `supply_price`;
    END IF;
END$$
DELIMITER ;
CALL _ensure_orders_supplier_amount(); DROP PROCEDURE IF EXISTS _ensure_orders_supplier_amount;

DROP PROCEDURE IF EXISTS _ensure_orders_supplier_settled;
DELIMITER $$
CREATE PROCEDURE _ensure_orders_supplier_settled()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='orders' AND COLUMN_NAME='supplier_settled') THEN
        ALTER TABLE `orders` ADD COLUMN `supplier_settled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '供货商是否已结算' AFTER `supplier_amount`;
    END IF;
END$$
DELIMITER ;
CALL _ensure_orders_supplier_settled(); DROP PROCEDURE IF EXISTS _ensure_orders_supplier_settled;

DROP PROCEDURE IF EXISTS _ensure_orders_supplier_settle_at;
DELIMITER $$
CREATE PROCEDURE _ensure_orders_supplier_settle_at()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='orders' AND COLUMN_NAME='supplier_settle_at') THEN
        ALTER TABLE `orders` ADD COLUMN `supplier_settle_at` DATETIME NULL DEFAULT NULL COMMENT '供货商可结算时间' AFTER `supplier_settled`;
    END IF;
END$$
DELIMITER ;
CALL _ensure_orders_supplier_settle_at(); DROP PROCEDURE IF EXISTS _ensure_orders_supplier_settle_at;

DROP PROCEDURE IF EXISTS _ensure_idx_supplier;
DELIMITER $$
CREATE PROCEDURE _ensure_idx_supplier()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='orders' AND INDEX_NAME='idx_supplier') THEN
        ALTER TABLE `orders` ADD KEY `idx_supplier` (`supplier_id`, `supplier_settled`, `supplier_settle_at`);
    END IF;
END$$
DELIMITER ;
CALL _ensure_idx_supplier(); DROP PROCEDURE IF EXISTS _ensure_idx_supplier;

-- ========== 货源广场两张新表 ==========

CREATE TABLE IF NOT EXISTS `marketplace_items` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id`     INT UNSIGNED NOT NULL COMMENT '供货商商户ID',
  `title`           VARCHAR(200) NOT NULL,
  `description`     TEXT NULL,
  `cover`           VARCHAR(500) NOT NULL DEFAULT '',
  `type`            ENUM('card','service','software') NOT NULL DEFAULT 'service',
  `supply_price`    INT          NOT NULL DEFAULT 0 COMMENT '供货价（分）',
  `suggested_price` INT          NOT NULL DEFAULT 0 COMMENT '建议零售价（分）',
  `source_product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '卡密类卡密源原始商品ID',
  `status`          TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '1=在售 0=下架',
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_supplier` (`supplier_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='货源广场·货源';

CREATE TABLE IF NOT EXISTS `marketplace_relations` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id`      INT UNSIGNED NOT NULL COMMENT '货源ID',
  `supplier_id`  INT UNSIGNED NOT NULL COMMENT '供货商商户ID',
  `merchant_id`  INT UNSIGNED NOT NULL COMMENT '接单方商户ID',
  `product_id`   INT UNSIGNED NOT NULL COMMENT '接单方商铺的商品ID',
  `supply_price` INT          NOT NULL DEFAULT 0 COMMENT '约定供货价快照（分）',
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_item_merchant` (`item_id`, `merchant_id`),
  KEY `idx_merchant` (`merchant_id`),
  KEY `idx_supplier` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='货源广场·对接关系';

SELECT '✅ 货源广场增量补丁执行完毕' AS result;