-- =============================================================
-- CRM 招投标管理模块表结构（17 张）
--
-- 模块说明：CRM 系统下的「招投标管理」子模块
--   - 我要招标（甲方）：6 张表
--   - 我要投标（乙方）：11 张表
-- 设计原则：
--   1. 命名统一以 yz_backend_crm_bidding_ 为前缀
--   2. 软删除：delete_time
--   3. 多租户：tenant_id
--   4. 与现有 CRM 模块（business/contract/project）字段风格一致
--   5. 中标/定标后只存 ID 引用（contract_id、project_id），不冗余数据
--
-- 状态机：
--   招标：0草稿 -> 1公告中 -> 2报名中 -> 3投标中 -> 4开标中 -> 5评标中 -> 6已定标 -> 7已归档
--   投标：0草稿 -> 1立项审批中 -> 2投标中 -> 3已开标 -> (4中标 | 5落标) -> (6已转合同 | 7已归档)
-- =============================================================

SET NAMES utf8mb4;

-- ============================================================
-- 第一部分：我要招标（6 张）
-- ============================================================

-- ---------------- 1. 招标项目主表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_tender` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`              VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '租户ID',
  `tender_no`              VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '招标编号',
  `title`                  VARCHAR(200) NOT NULL DEFAULT '' COMMENT '招标标题/项目名称',
  `tender_type`            VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '招标类型: 1公开招标 2邀请招标 3竞争性谈判 4询比 5单一来源',
  `status`                 TINYINT      NOT NULL DEFAULT 0 COMMENT '0草稿 1公告中 2报名中 3投标中 4开标中 5评标中 6已定标 7已归档',
  `customer_id`            BIGINT UNSIGNED DEFAULT NULL COMMENT '招标方客户ID（我方客户库）',
  `customer_name`          VARCHAR(200) NOT NULL DEFAULT '' COMMENT '招标方名称',
  `publish_org`            VARCHAR(200) NOT NULL DEFAULT '' COMMENT '发布单位',
  `budget_amount`          DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '预算金额',
  `bid_deposit`            DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '保证金',
  `publish_date`           DATE         DEFAULT NULL COMMENT '发布日期',
  `register_start_date`    DATETIME     DEFAULT NULL COMMENT '报名开始',
  `register_end_date`      DATETIME     DEFAULT NULL COMMENT '报名截止',
  `bid_start_date`         DATETIME     DEFAULT NULL COMMENT '投标开始',
  `bid_end_date`           DATETIME     DEFAULT NULL COMMENT '投标截止',
  `open_bid_date`          DATETIME     DEFAULT NULL COMMENT '开标时间',
  `contact_person`         VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '联系人',
  `contact_phone`          VARCHAR(20)  NOT NULL DEFAULT '' COMMENT '联系电话',
  `contact_email`          VARCHAR(100) NOT NULL DEFAULT '' COMMENT '联系邮箱',
  `bid_address`            VARCHAR(255) NOT NULL DEFAULT '' COMMENT '投标地址',
  `open_address`           VARCHAR(255) NOT NULL DEFAULT '' COMMENT '开标地址',
  `description`            TEXT         COMMENT '项目描述',
  `requirement`            TEXT         COMMENT '投标要求',
  `evaluation_method`      VARCHAR(50)  NOT NULL DEFAULT '1' COMMENT '评标方法: 1综合评估 2最低价 3性价比',
  `winner_supplier_id`     BIGINT UNSIGNED DEFAULT NULL COMMENT '中标供应商ID',
  `winner_supplier_name`   VARCHAR(200) NOT NULL DEFAULT '' COMMENT '中标供应商名称',
  `winner_amount`          DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '中标金额',
  `winner_date`            DATETIME     DEFAULT NULL COMMENT '定标时间',
  `contract_id`            BIGINT UNSIGNED DEFAULT NULL COMMENT '转合同ID',
  `project_id`             BIGINT UNSIGNED DEFAULT NULL COMMENT '关联项目ID',
  `owner_user_id`          VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '负责人ID',
  `owner_user_name`        VARCHAR(128) NOT NULL DEFAULT '' COMMENT '负责人姓名',
  `department_id`          BIGINT UNSIGNED DEFAULT 0 COMMENT '部门ID',
  `locked`                 TINYINT      NOT NULL DEFAULT 0 COMMENT '0否 1是（定标后锁定）',
  `remark`                 TEXT         COMMENT '备注',
  `create_user_id`         VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`       VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`            DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`            DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_status` (`tenant_id`, `status`),
  KEY `idx_tender_no` (`tenant_id`, `tender_no`),
  KEY `idx_owner` (`tenant_id`, `owner_user_id`),
  KEY `idx_customer` (`tenant_id`, `customer_id`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-招标项目主表';

-- ---------------- 2. 招标文件表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_tender_file` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `tender_id`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '招标项目ID',
  `file_type`           VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1招标文件 2补充文件 3答疑文件 4其他',
  `file_name`           VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文件名称',
  `file_url`            VARCHAR(500) NOT NULL DEFAULT '' COMMENT '文件URL',
  `file_size`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '文件大小（字节）',
  `file_ext`            VARCHAR(20)  NOT NULL DEFAULT '' COMMENT '扩展名',
  `upload_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `upload_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `remark`              VARCHAR(500) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_tender` (`tenant_id`, `tender_id`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-招标文件';

-- ---------------- 3. 招标公告表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_tender_announce` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `tender_id`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '招标项目ID',
  `announce_type`       VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1招标公告 2变更公告 3中标公告 4终止公告',
  `title`               VARCHAR(255) NOT NULL DEFAULT '' COMMENT '公告标题',
  `content`             LONGTEXT     COMMENT '公告内容（富文本）',
  `publish_status`      TINYINT      NOT NULL DEFAULT 0 COMMENT '0草稿 1已发布',
  `publish_time`        DATETIME     DEFAULT NULL COMMENT '发布时间',
  `view_count`          INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览次数',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_tender` (`tenant_id`, `tender_id`),
  KEY `idx_announce_type` (`tenant_id`, `announce_type`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-招标公告';

-- ---------------- 4. 投标方/供应商表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_tender_supplier` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `tender_id`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '招标项目ID',
  `supplier_id`         BIGINT UNSIGNED DEFAULT NULL COMMENT '供应商ID（ERP/CRM共享）',
  `supplier_name`       VARCHAR(200) NOT NULL DEFAULT '' COMMENT '投标方/供应商名称',
  `register_status`     TINYINT      NOT NULL DEFAULT 1 COMMENT '1待审核 2已通过 3已拒绝 4已退标',
  `register_time`       DATETIME     DEFAULT NULL COMMENT '报名时间',
  `bid_amount`          DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '报价金额',
  `bid_files`           TEXT         COMMENT '投标文件JSON（多文件）',
  `contact_person`      VARCHAR(50)  NOT NULL DEFAULT '',
  `contact_phone`       VARCHAR(20)  NOT NULL DEFAULT '',
  `is_winner`           TINYINT      NOT NULL DEFAULT 0 COMMENT '0否 1是中标方',
  `ranking`             INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '排名',
  `remark`              VARCHAR(500) NOT NULL DEFAULT '',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_tender` (`tenant_id`, `tender_id`),
  KEY `idx_supplier` (`tenant_id`, `supplier_id`),
  KEY `idx_register_status` (`tenant_id`, `register_status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标方/供应商';

-- ---------------- 5. 开标评标表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_tender_evaluation` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `tender_id`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '招标项目ID',
  `supplier_id`         BIGINT UNSIGNED DEFAULT NULL COMMENT '被评标供应商ID',
  `supplier_name`       VARCHAR(200) NOT NULL DEFAULT '' COMMENT '供应商名称',
  `technical_score`     DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '技术分',
  `business_score`      DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '商务分',
  `price_score`         DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '价格分',
  `service_score`       DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '服务分',
  `total_score`         DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '总分',
  `weight_config`       VARCHAR(255) NOT NULL DEFAULT '' COMMENT '权重配置JSON',
  `ranking`             INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '排名',
  `evaluation_user_id`  VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '评标人ID',
  `evaluation_user_name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '评标人姓名',
  `evaluation_time`     DATETIME     DEFAULT NULL COMMENT '评标时间',
  `comment`             TEXT         COMMENT '评语',
  `result`              TINYINT      NOT NULL DEFAULT 0 COMMENT '0待评 1合格 2不合格 3废标',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_tender` (`tenant_id`, `tender_id`),
  KEY `idx_supplier` (`tenant_id`, `supplier_id`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-开标评标';

-- ---------------- 6. 定标结果表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_tender_result` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `tender_id`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '招标项目ID',
  `result_type`         TINYINT      NOT NULL DEFAULT 1 COMMENT '1定标 2流标 3废标',
  `winner_supplier_id`  BIGINT UNSIGNED DEFAULT NULL COMMENT '中标供应商ID',
  `winner_supplier_name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '中标供应商名称',
  `winner_amount`       DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '中标金额',
  `result_date`         DATETIME     DEFAULT NULL COMMENT '定标日期',
  `announcement`        TEXT         COMMENT '定标公告',
  `contract_id`         BIGINT UNSIGNED DEFAULT NULL COMMENT '转合同ID',
  `project_id`          BIGINT UNSIGNED DEFAULT NULL COMMENT '关联项目ID',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_tender` (`tenant_id`, `tender_id`),
  KEY `idx_result_type` (`tenant_id`, `result_type`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-定标结果';


-- ============================================================
-- 第二部分：我要投标（11 张）
-- ============================================================

-- ---------------- 7. 投标项目主表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`              VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_no`                 VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '投标编号',
  `title`                  VARCHAR(200) NOT NULL DEFAULT '' COMMENT '投标标题/项目名称',
  `bid_type`               VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '投标类型: 1公开 2邀请 3竞争性谈判 4询比 5单一来源',
  `status`                 TINYINT      NOT NULL DEFAULT 0 COMMENT '0草稿 1立项审批中 2投标中 3已开标 4中标 5落标 6已转合同 7已归档',
  `source_type`            TINYINT      NOT NULL DEFAULT 0 COMMENT '0独立 1商机 2项目',
  `source_id`              BIGINT UNSIGNED DEFAULT NULL COMMENT '来源ID（商机/项目）',
  `business_id`            BIGINT UNSIGNED DEFAULT NULL COMMENT '关联商机ID',
  `business_name`          VARCHAR(200) NOT NULL DEFAULT '' COMMENT '商机名称',
  `project_id`             BIGINT UNSIGNED DEFAULT NULL COMMENT '关联项目ID（中转）',
  `project_name`           VARCHAR(200) NOT NULL DEFAULT '' COMMENT '项目名称',
  `tender_id`              BIGINT UNSIGNED DEFAULT NULL COMMENT '关联招标项目ID（如果从招标信息创建）',
  `tender_no`              VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '招标编号',
  `customer_id`            BIGINT UNSIGNED DEFAULT NULL COMMENT '招标方ID',
  `customer_name`          VARCHAR(200) NOT NULL DEFAULT '' COMMENT '招标方名称',
  `publish_org`            VARCHAR(200) NOT NULL DEFAULT '' COMMENT '发布单位',
  `bid_amount`             DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '投标报价',
  `bid_deposit`            DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '保证金',
  `publish_date`           DATE         DEFAULT NULL COMMENT '招标发布日期',
  `register_end_date`      DATETIME     DEFAULT NULL COMMENT '报名截止',
  `bid_end_date`           DATETIME     DEFAULT NULL COMMENT '投标截止',
  `open_bid_date`          DATETIME     DEFAULT NULL COMMENT '开标时间',
  `contact_person`         VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '联系人',
  `contact_phone`          VARCHAR(20)  NOT NULL DEFAULT '',
  `bid_address`            VARCHAR(255) NOT NULL DEFAULT '' COMMENT '投标地址',
  `open_address`           VARCHAR(255) NOT NULL DEFAULT '' COMMENT '开标地址',
  `win_status`             TINYINT      NOT NULL DEFAULT 0 COMMENT '0未开标 1中标 2未中标 3废标 4流标',
  `win_date`               DATETIME     DEFAULT NULL COMMENT '中标日期',
  `our_ranking`            INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '我方排名',
  `total_score`            DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '我方得分',
  `competitor_count`       INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '竞争对手数量',
  `contract_id`            BIGINT UNSIGNED DEFAULT NULL COMMENT '转合同ID',
  `new_project_id`         BIGINT UNSIGNED DEFAULT NULL COMMENT '中标后新建项目ID',
  `owner_user_id`          VARCHAR(64)  NOT NULL DEFAULT '',
  `owner_user_name`        VARCHAR(128) NOT NULL DEFAULT '',
  `department_id`          BIGINT UNSIGNED DEFAULT 0 COMMENT '部门ID',
  `description`            TEXT         COMMENT '项目描述',
  `remark`                 TEXT         COMMENT '备注',
  `locked`                 TINYINT      NOT NULL DEFAULT 0 COMMENT '0否 1是',
  `create_user_id`         VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`       VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`            DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`            DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_status` (`tenant_id`, `status`),
  KEY `idx_bid_no` (`tenant_id`, `bid_no`),
  KEY `idx_owner` (`tenant_id`, `owner_user_id`),
  KEY `idx_business` (`tenant_id`, `business_id`),
  KEY `idx_project` (`tenant_id`, `project_id`),
  KEY `idx_tender` (`tenant_id`, `tender_id`),
  KEY `idx_customer` (`tenant_id`, `customer_id`),
  KEY `idx_win_status` (`tenant_id`, `win_status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标项目主表';

-- ---------------- 8. 投标文件表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_file` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `file_type`           VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1投标文件 2技术方案 3商务方案 4报价单 5保证金 7其他',
  `file_name`           VARCHAR(255) NOT NULL DEFAULT '',
  `file_url`            VARCHAR(500) NOT NULL DEFAULT '',
  `file_size`           BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `file_ext`            VARCHAR(20)  NOT NULL DEFAULT '',
  `version`             VARCHAR(20)  NOT NULL DEFAULT '1.0' COMMENT '版本号',
  `upload_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `upload_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `remark`              VARCHAR(500) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标文件';

-- ---------------- 9. 投标立项审批表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_approval` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `approval_type`       VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1立项审批 2报价审批 3合同审批',
  `approval_status`     TINYINT      NOT NULL DEFAULT 0 COMMENT '0待审批 1已通过 2已拒绝 3已撤回',
  `approval_flow_id`    BIGINT UNSIGNED DEFAULT NULL COMMENT '审批流ID（复用现有引擎）',
  `current_user_id`     VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '当前审批人',
  `current_user_name`   VARCHAR(128) NOT NULL DEFAULT '',
  `approval_users`      TEXT         COMMENT '审批人列表JSON',
  `submit_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `submit_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `submit_time`         DATETIME     DEFAULT NULL,
  `complete_time`       DATETIME     DEFAULT NULL,
  `comment`             TEXT         COMMENT '审批意见',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_flow` (`approval_flow_id`),
  KEY `idx_status` (`tenant_id`, `approval_status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标立项审批';

-- ---------------- 10. 投标任务表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_task` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `task_name`           VARCHAR(100) NOT NULL DEFAULT '' COMMENT '任务名称',
  `task_type`           VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1商务 2技术 3报价 4法务 5财务 6其他',
  `content`             TEXT         COMMENT '任务内容',
  `owner_user_id`       VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '负责人',
  `owner_user_name`     VARCHAR(128) NOT NULL DEFAULT '',
  `start_date`          DATE         DEFAULT NULL,
  `end_date`            DATE         DEFAULT NULL,
  `complete_time`       DATETIME     DEFAULT NULL,
  `status`              TINYINT      NOT NULL DEFAULT 0 COMMENT '0未开始 1进行中 2已完成 3已逾期 4已取消',
  `progress`            TINYINT      NOT NULL DEFAULT 0 COMMENT '进度 0-100',
  `files`               TEXT         COMMENT '附件JSON',
  `remark`              VARCHAR(500) NOT NULL DEFAULT '',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_owner` (`tenant_id`, `owner_user_id`),
  KEY `idx_status` (`tenant_id`, `status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标任务';

-- ---------------- 11. 标书模板表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_template` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `template_name`       VARCHAR(100) NOT NULL DEFAULT '' COMMENT '模板名称',
  `template_type`       VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1技术标 2商务标 3综合标',
  `industry`            VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '适用行业',
  `description`         VARCHAR(500) NOT NULL DEFAULT '',
  `content`             LONGTEXT     COMMENT '模板内容',
  `file_url`            VARCHAR(500) NOT NULL DEFAULT '' COMMENT '模板文件URL',
  `use_count`            INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用次数',
  `status`              TINYINT      NOT NULL DEFAULT 1 COMMENT '1启用 0禁用',
  `owner_user_id`       VARCHAR(64)  NOT NULL DEFAULT '',
  `owner_user_name`     VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_type` (`tenant_id`, `template_type`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-标书模板';

-- ---------------- 12. 标书版本表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_document` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `template_id`         BIGINT UNSIGNED DEFAULT NULL COMMENT '基于模板',
  `version`             VARCHAR(20)  NOT NULL DEFAULT 'v1.0' COMMENT '版本号',
  `title`               VARCHAR(200) NOT NULL DEFAULT '' COMMENT '版本标题',
  `content`             LONGTEXT     COMMENT '标书内容',
  `file_url`            VARCHAR(500) NOT NULL DEFAULT '' COMMENT '标书文件URL',
  `status`              TINYINT      NOT NULL DEFAULT 1 COMMENT '1编辑中 2待审核 3已定稿 4已提交',
  `editor_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `editor_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `review_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `review_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `review_comment`      TEXT         COMMENT '审核意见',
  `review_time`         DATETIME     DEFAULT NULL,
  `submit_time`         DATETIME     DEFAULT NULL,
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_template` (`template_id`),
  KEY `idx_status` (`tenant_id`, `status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-标书版本';

-- ---------------- 13. 资质文件库 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_qualification` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `qualification_name`  VARCHAR(100) NOT NULL DEFAULT '' COMMENT '资质名称',
  `qualification_type`  VARCHAR(20)  NOT NULL DEFAULT '1' COMMENT '1营业执照 2资质证书 3业绩证明 4认证证书 5其他',
  `qualification_no`    VARCHAR(100) NOT NULL DEFAULT '' COMMENT '证书编号',
  `issuing_authority`   VARCHAR(200) NOT NULL DEFAULT '' COMMENT '颁发机构',
  `issue_date`          DATE         DEFAULT NULL COMMENT '颁发日期',
  `expire_date`         DATE         DEFAULT NULL COMMENT '到期日期',
  `alert_days`          INT UNSIGNED NOT NULL DEFAULT 30 COMMENT '提前提醒天数',
  `file_url`            VARCHAR(500) NOT NULL DEFAULT '' COMMENT '文件URL',
  `file_name`           VARCHAR(255) NOT NULL DEFAULT '',
  `file_size`           BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `description`         TEXT         COMMENT '备注',
  `status`              TINYINT      NOT NULL DEFAULT 1 COMMENT '1有效 2即将过期 0失效',
  `owner_user_id`       VARCHAR(64)  NOT NULL DEFAULT '',
  `owner_user_name`     VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_type` (`tenant_id`, `qualification_type`),
  KEY `idx_expire` (`tenant_id`, `expire_date`),
  KEY `idx_status` (`tenant_id`, `status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-资质文件库';

-- ---------------- 14. 报价表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_quote` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `quote_name`          VARCHAR(100) NOT NULL DEFAULT '' COMMENT '报价单名称',
  `total_amount`        DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '总报价',
  `currency`            VARCHAR(10)  NOT NULL DEFAULT 'CNY' COMMENT '币种',
  `tax_rate`            DECIMAL(6,4) NOT NULL DEFAULT 0 COMMENT '税率',
  `valid_date`          DATE         DEFAULT NULL COMMENT '报价有效期',
  `products`            LONGTEXT     COMMENT '产品清单JSON',
  `cost_analysis`       TEXT         COMMENT '成本分析',
  `profit_rate`          DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '利润率%',
  `status`              TINYINT      NOT NULL DEFAULT 1 COMMENT '1草稿 2待审批 3已审批 4已使用',
  `approval_flow_id`    BIGINT UNSIGNED DEFAULT NULL,
  `submit_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `submit_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `submit_time`         DATETIME     DEFAULT NULL,
  `approval_time`       DATETIME     DEFAULT NULL,
  `remark`              TEXT         COMMENT '备注',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_status` (`tenant_id`, `status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-报价表';

-- ---------------- 15. 竞争对手报价表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_competitor` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `competitor_name`     VARCHAR(200) NOT NULL DEFAULT '' COMMENT '竞争对手名称',
  `competitor_type`     TINYINT      NOT NULL DEFAULT 1 COMMENT '1同类厂商 2关联企业 3其他',
  `quote_amount`        DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT '报价',
  `advantages`          TEXT         COMMENT '优势',
  `weaknesses`          TEXT         COMMENT '劣势',
  `source`              VARCHAR(200) NOT NULL DEFAULT '' COMMENT '信息来源',
  `remark`              VARCHAR(500) NOT NULL DEFAULT '',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-竞争对手报价';

-- ---------------- 16. 投标结果表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_result` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `result_type`         TINYINT      NOT NULL DEFAULT 0 COMMENT '1中标 2未中标 3废标 4流标',
  `announce_date`       DATETIME     DEFAULT NULL COMMENT '公告日期',
  `announce_url`        VARCHAR(500) NOT NULL DEFAULT '' COMMENT '公告URL',
  `total_score`         DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT '我方得分',
  `our_ranking`         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '我方排名',
  `competitor_count`    INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '竞争对手数量',
  `reason`              TEXT         COMMENT '原因分析',
  `lessons`             TEXT         COMMENT '经验教训',
  `next_step`           VARCHAR(255) NOT NULL DEFAULT '' COMMENT '下一步行动',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_unique_bid` (`tenant_id`, `bid_id`),
  KEY `idx_result_type` (`tenant_id`, `result_type`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标结果';

-- ---------------- 17. 投标费用表 ----------------
CREATE TABLE IF NOT EXISTS `yz_backend_crm_bidding_bid_expense` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`           VARCHAR(64)  NOT NULL DEFAULT '',
  `bid_id`              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '投标项目ID',
  `expense_type`        TINYINT      NOT NULL DEFAULT 1 COMMENT '1标书费 2保证金 3差旅费 4咨询费 5代理费 6其他',
  `expense_name`        VARCHAR(100) NOT NULL DEFAULT '' COMMENT '费用名称',
  `amount`              DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '金额',
  `currency`            VARCHAR(10)  NOT NULL DEFAULT 'CNY',
  `payment_status`      TINYINT      NOT NULL DEFAULT 0 COMMENT '0未付 1已付 2已退还 3已抵扣',
  `payment_date`        DATETIME     DEFAULT NULL COMMENT '支付日期',
  `payment_user_id`     VARCHAR(64)  NOT NULL DEFAULT '',
  `payment_user_name`   VARCHAR(128) NOT NULL DEFAULT '',
  `voucher_url`         VARCHAR(500) NOT NULL DEFAULT '' COMMENT '凭证URL',
  `description`         TEXT         COMMENT '说明',
  `create_user_id`      VARCHAR(64)  NOT NULL DEFAULT '',
  `create_user_name`    VARCHAR(128) NOT NULL DEFAULT '',
  `create_time`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`         DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `delete_time`         DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_bid` (`tenant_id`, `bid_id`),
  KEY `idx_payment_status` (`tenant_id`, `payment_status`),
  KEY `idx_delete_time` (`delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-投标费用';


-- ============================================================
-- 菜单配置（投标管理）
-- ============================================================
-- 招标方菜单：path = 'apps/crm/bidding/tender/index.vue'
-- 投标方菜单：path = 'apps/crm/bidding/bid/index.vue'
-- 注意：菜单通常通过平台端 UI 配置，详见 docs/bidding_menus.sql