-- ============================================================
-- 寄售平台 · 数据库初始化脚本（MySQL / MySQL 8.x 或 MariaDB 10.4+）
-- 宝塔部署：在「数据库」中创建数据库后，导入本文件即可。
-- 金额字段统一以「分」为单位的整数存储。
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 管理员表
CREATE TABLE IF NOT EXISTS `admins` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`   VARCHAR(64)  NOT NULL,
  `password`   VARCHAR(255) NOT NULL COMMENT 'password_hash 密文',
  `nickname`   VARCHAR(64)  NOT NULL DEFAULT '',
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='后台管理员';

-- 商户表（每个商户独立金额/余额，单位：分）
CREATE TABLE IF NOT EXISTS `merchants` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`   VARCHAR(64)  NOT NULL COMMENT '登录账号',
  `password`   VARCHAR(255) NOT NULL COMMENT 'password_hash 密文',
  `nickname`     VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '店铺/商户名称',
  `balance`      INT          NOT NULL DEFAULT 0 COMMENT '可用余额（分，已结算可提现）',
  `settle_hours` INT          NOT NULL DEFAULT 0 COMMENT '结算冻结时长（小时），0=付款后立即可结算',
  `epay_api_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '商户自有易支付 API 地址',
  `epay_pid`     VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '商户自有易支付 PID',
  `epay_key`     VARCHAR(255) NOT NULL DEFAULT '' COMMENT '商户自有易支付 KEY',
  `status`       TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='寄售商户';

-- 商品表
CREATE TABLE IF NOT EXISTS `products` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `merchant_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属商户ID，0=平台自营',
  `title`       VARCHAR(200) NOT NULL,
  `cover`       VARCHAR(500) NOT NULL DEFAULT '' COMMENT '封面图 URL',
  `description` TEXT NULL COMMENT '商品描述',
  `type`        ENUM('card','service','software') NOT NULL DEFAULT 'service' COMMENT 'card=卡密 service=服务 software=软件/文章',
  `download_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '软件/文章类型的下载地址',
  `use_spec`    TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '0=不启用规格直接售卖 1=启用规格',
  `base_price`  INT          NOT NULL DEFAULT 0 COMMENT '初始价格/金额（分），不启用规格时即售价',
  `stock`       INT          NOT NULL DEFAULT 0 COMMENT '不启用规格时的库存（仅服务类型可自定义；卡密类型否决，由卡密数量决定）',
  `status`      TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '1=上架 0=下架',
  `sort`        INT          NOT NULL DEFAULT 0,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_merchant` (`merchant_id`),
  KEY `idx_status` (`status`, `sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品';

-- 规格维度表
CREATE TABLE IF NOT EXISTS `product_specs` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` INT UNSIGNED NOT NULL,
  `name`       VARCHAR(100) NOT NULL,
  `mode`       ENUM('single','multi') NOT NULL DEFAULT 'single' COMMENT 'single=单选 multi=多选',
  `required`   TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '单选/多选是否必填',
  `sort`       INT          NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品规格维度';

-- 规格值表
CREATE TABLE IF NOT EXISTS `product_spec_values` (
  `id`      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `spec_id` INT UNSIGNED NOT NULL,
  `name`    VARCHAR(100) NOT NULL,
  `price`   INT          NOT NULL DEFAULT 0 COMMENT '单选价/多选附加价（分）',
  `sort`    INT          NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_spec` (`spec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品规格值';

-- SKU 表
CREATE TABLE IF NOT EXISTS `product_skus` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` INT UNSIGNED NOT NULL,
  `signature`  VARCHAR(255) NOT NULL DEFAULT '' COMMENT '必选单选值ID升序逗号拼接，如 11,23；无单选维度时为空串表示默认SKU',
  `name`       VARCHAR(255) NOT NULL DEFAULT '默认',
  `price`      INT          NOT NULL DEFAULT 0 COMMENT 'SKU 价格（分）',
  `stock`      INT          NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_signature` (`signature`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品 SKU';

-- 卡密表（卡密类型商品的库存 = 未售出卡密数量）
CREATE TABLE IF NOT EXISTS `product_cards` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` INT UNSIGNED NOT NULL,
  `card_no`    VARCHAR(255) NOT NULL COMMENT '卡密内容',
  `status`     TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '0=未售出 1=已售出',
  `order_id`   INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '售出所属订单ID，0=未售',
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product_status` (`product_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商品卡密';

-- 订单表
CREATE TABLE IF NOT EXISTS `orders` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no`    VARCHAR(40)  NOT NULL,
  `product_id`  INT UNSIGNED NOT NULL,
  `merchant_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品所属商户ID，用于分账',
  `sku_id`      INT UNSIGNED NULL DEFAULT NULL COMMENT '命中的 SKU（不启用规格或默认SKU时为默认SKU行）',
  `product_title` VARCHAR(200) NOT NULL DEFAULT '',
  `product_type` ENUM('card','service','software') NOT NULL DEFAULT 'service',
  `spec_snap`   TEXT NULL COMMENT '规格快照 JSON：{spec_name,mode,values:[{name,price}]}',
  `amount`      INT          NOT NULL DEFAULT 0 COMMENT '应付金额（分）',
  `fee`         INT          NOT NULL DEFAULT 0 COMMENT '平台支付费/手续费（分）',
  `merchant_amount` INT      NOT NULL DEFAULT 0 COMMENT '应结算给商户的金额（分）= amount - fee',
  `qty`         INT          NOT NULL DEFAULT 1,
  `buyer_name`  VARCHAR(100) NOT NULL DEFAULT '',
  `buyer_phone` VARCHAR(30)  NOT NULL DEFAULT '',
  `buyer_note`  VARCHAR(500) NOT NULL DEFAULT '',
  `status`      TINYINT      NOT NULL DEFAULT 0 COMMENT '0=待支付 1=已支付 2=已发货 3=已完成 4=已关闭',
  `pay_type`    VARCHAR(20)  NOT NULL DEFAULT 'epay',
  `pay_trade_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '易支付平台返回的 trade_no',
  `card_id`     INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '发货的卡密ID（卡密类型）',
  `card_no`     VARCHAR(255) NOT NULL DEFAULT '' COMMENT '发货卡密内容快照',
  `download_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '发货下载地址快照（软件/文章类型）',
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at`     DATETIME     NULL DEFAULT NULL,
  `settled`     TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '0=未结算（冻结） 1=已结算入商户余额',
  `settle_at`   DATETIME     NULL DEFAULT NULL COMMENT '可结算时间（paid_at + 商户冻结时长）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_product` (`product_id`),
  KEY `idx_merchant` (`merchant_id`),
  KEY `idx_status` (`status`),
  KEY `idx_settle` (`merchant_id`, `settled`, `settle_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单';

-- 平台配置表（键值对：如支付费率等）
CREATE TABLE IF NOT EXISTS `settings` (
  `k` VARCHAR(64) NOT NULL,
  `v` TEXT NOT NULL,
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='平台配置';

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='货源广场·对接关系';

SET FOREIGN_KEY_CHECKS = 1;

-- ============ 初始数据 ============
-- 默认管理员：admin / admin123（下方为 password_hash('admin123') 的密文，请在部署后修改）
INSERT INTO `admins` (`username`, `password`, `nickname`) VALUES
('admin', '$2y$12$p1btGkzcMB8N1ybzfj7hoe5vYE.hh.LBaFw.crgaII1kFsLECKHvC', '管理员')
ON DUPLICATE KEY UPDATE `username` = `username`;

-- 默认平台支付费率（百分比，4 表示商户结算时扣除 4% 支付费）
INSERT INTO `settings` (`k`, `v`) VALUES
('merchant_fee_rate',        '4'),
('platform_wx_enabled',      '1'),
('platform_alipay_enabled',  '1'),
('merchant_wx_enabled',      '1'),
('merchant_alipay_enabled',  '1'),
('fee_charge_mode',          '1'),
('operation_fund',           '0')
ON DUPLICATE KEY UPDATE `k` = `k`;

-- 示例商品
INSERT INTO `products` (`merchant_id`, `title`, `cover`, `description`, `type`, `download_url`, `use_spec`, `base_price`, `stock`, `status`, `sort`) VALUES
(0, '青云智能手表 Pro 2026', '', '轻薄旗舰智能手表，全天候健康监测。（服务类型，可自定义库存）', 'service', '', 1, 149900, 999, 1, 1),
(0, '示例商品 · 无规格直售', '', '不启用规格直接售卖的示例商品。', 'service', '', 0, 9900, 200, 1, 2)
ON DUPLICATE KEY UPDATE `title` = `title`;