-- ============================================
-- Supplier Service 数据库初始化脚本
-- ============================================

-- 供应商表
CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `supplier_code` VARCHAR(50) NOT NULL COMMENT '供应商编码' unique,
  `supplier_name` VARCHAR(200) NOT NULL COMMENT '供应商名称',
  `supplier_type` VARCHAR(50) COMMENT '供应商类型: manufacturer/trader/agent/individual',
  `industry` VARCHAR(100) COMMENT '所属行业',
  `business_license` VARCHAR(100) COMMENT '统一社会信用代码',
  `registered_capital` DECIMAL(15,2) COMMENT '注册资本',
  `establishment_date` VARCHAR(20) COMMENT '成立日期',
  `legal_person` VARCHAR(100) COMMENT '法定代表人',
  `phone` VARCHAR(50) COMMENT '联系电话',
  `email` VARCHAR(100) COMMENT '电子邮箱',
  `website` VARCHAR(200) COMMENT '官方网站',
  `province` VARCHAR(50) COMMENT '省份',
  `city` VARCHAR(50) COMMENT '城市',
  `district` VARCHAR(50) COMMENT '区县',
  `address` VARCHAR(500) COMMENT '详细地址',
  `main_business` TEXT COMMENT '主营业务',
  `status` VARCHAR(50) DEFAULT 'pending' COMMENT '合作状态: pending/active/inactive/blacklist',
  `grade` VARCHAR(10) DEFAULT 'C' COMMENT '供应商等级: A/B/C/D',
  `credit_score` INT DEFAULT 60 COMMENT '信用评分 (0-100)',
  `is_internal` INT DEFAULT 0 COMMENT '是否为内供: 0-外部 1-内部',
  `cooperation_start_date` VARCHAR(20) COMMENT '合作开始日期',
  `cooperation_end_date` VARCHAR(20) COMMENT '合作结束日期',
  `settlement_method` VARCHAR(50) COMMENT '结算方式: prepaid/postpaid/monthly',
  `payment_days` INT COMMENT '付款周期(天)',
  `minimum_order_quantity` DECIMAL(15,3) COMMENT '最低起订量',
  `remark` TEXT COMMENT '备注',
  `attachments` TEXT COMMENT '附件(JSON数组)',
  `audit_status` VARCHAR(50) DEFAULT 'pending' COMMENT '审核状态: pending/approved/rejected',
  `audit_comment` TEXT COMMENT '审核备注',
  `auditor_id` BIGINT COMMENT '审核人ID',
  `audit_time` DATETIME COMMENT '审核时间',
  `version` INT DEFAULT 0 COMMENT '乐观锁版本号',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  INDEX `idx_supplier_code` (`supplier_code`),
  INDEX `idx_supplier_name` (`supplier_name`),
  INDEX `idx_status` (`status`),
  INDEX `idx_grade` (`grade`),
  INDEX `idx_audit_status` (`audit_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商主表';

-- 供应商联系人表
CREATE TABLE IF NOT EXISTS `supplier_contacts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
  `contact_name` VARCHAR(100) NOT NULL COMMENT '联系人姓名',
  `gender` VARCHAR(10) COMMENT '性别: male/female',
  `department` VARCHAR(100) COMMENT '部门',
  `position` VARCHAR(100) COMMENT '职务',
  `mobile` VARCHAR(50) COMMENT '手机号码',
  `phone` VARCHAR(50) COMMENT '固定电话',
  `email` VARCHAR(100) COMMENT '电子邮箱',
  `qq` VARCHAR(50) COMMENT 'QQ号码',
  `wechat` VARCHAR(100) COMMENT '微信',
  `is_primary` INT DEFAULT 0 COMMENT '是否为主要联系人: 0-否 1-是',
  `is_emergency` INT DEFAULT 0 COMMENT '是否为紧急联系人: 0-否 1-是',
  `remark` TEXT COMMENT '备注',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  INDEX `idx_supplier_id` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商联系人表';

-- 供应商银行账户表
CREATE TABLE IF NOT EXISTS `supplier_bank_accounts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
  `account_name` VARCHAR(200) NOT NULL COMMENT '开户名称',
  `bank_name` VARCHAR(200) NOT NULL COMMENT '开户银行',
  `bank_account` VARCHAR(100) NOT NULL COMMENT '银行账号',
  `branch_name` VARCHAR(200) COMMENT '开户支行',
  `branch_code` VARCHAR(50) COMMENT '银行联行号',
  `province` VARCHAR(50) COMMENT '开户省份',
  `city` VARCHAR(50) COMMENT '开户城市',
  `account_type` VARCHAR(50) COMMENT '账户类型: basic/general/reserve',
  `is_default` INT DEFAULT 0 COMMENT '是否为默认账户: 0-否 1-是',
  `balance` DECIMAL(15,2) DEFAULT 0 COMMENT '账户余额',
  `reserved_phone` VARCHAR(50) COMMENT '银行预留手机号',
  `remark` TEXT COMMENT '备注',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  INDEX `idx_supplier_id` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商银行账户表';

-- 供应商评级表
CREATE TABLE IF NOT EXISTS `supplier_ratings` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
  `rating_period` VARCHAR(50) COMMENT '评价周期: monthly/quarterly/annual',
  `rating_year` INT COMMENT '评价年份',
  `rating_month` INT COMMENT '评价月份',
  `quality_score` DECIMAL(5,2) COMMENT '质量评分(0-100)',
  `delivery_score` DECIMAL(5,2) COMMENT '交期评分(0-100)',
  `price_score` DECIMAL(5,2) COMMENT '价格评分(0-100)',
  `service_score` DECIMAL(5,2) COMMENT '服务评分(0-100)',
  `overall_score` DECIMAL(5,2) COMMENT '综合评分(0-100)',
  `grade` VARCHAR(10) COMMENT '供应商等级: A/B/C/D',
  `evaluator_id` BIGINT COMMENT '评价人ID',
  `evaluator_name` VARCHAR(100) COMMENT '评价人姓名',
  `evaluation_time` DATETIME COMMENT '评价时间',
  `strengths` TEXT COMMENT '优点',
  `weaknesses` TEXT COMMENT '不足',
  `suggestions` TEXT COMMENT '改进建议',
  `remark` TEXT COMMENT '备注',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  INDEX `idx_supplier_id` (`supplier_id`),
  INDEX `idx_rating_year_month` (`rating_year`, `rating_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商评级表';

-- 采购询价表
CREATE TABLE IF NOT EXISTS `procurement_inquiries` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `inquiry_no` VARCHAR(50) NOT NULL COMMENT '询价单号' unique,
  `supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
  `supplier_name` VARCHAR(200) COMMENT '供应商名称',
  `inquiry_type` VARCHAR(50) COMMENT '询价类型: product/material/service',
  `product_id` BIGINT COMMENT '商品ID',
  `product_name` VARCHAR(200) COMMENT '商品名称',
  `product_spec` VARCHAR(200) COMMENT '商品规格',
  `unit` VARCHAR(20) COMMENT '商品单位',
  `quantity` DECIMAL(15,3) COMMENT '询价数量',
  `expected_delivery_date` VARCHAR(20) COMMENT '期望交货日期',
  `inquiry_status` VARCHAR(50) DEFAULT 'draft' COMMENT '询价状态: draft/sent/replied/compared/confirmed/cancelled',
  `inquiry_date` DATETIME COMMENT '询价日期',
  `quote_deadline` VARCHAR(20) COMMENT '报价截止日期',
  `quoted_price` DECIMAL(15,4) COMMENT '报价金额',
  `currency` VARCHAR(10) DEFAULT 'CNY' COMMENT '报价币种',
  `quote_valid_until` VARCHAR(20) COMMENT '报价有效期',
  `delivery_cycle` INT COMMENT '交货周期(天)',
  `quality_standard` VARCHAR(500) COMMENT '质量标准',
  `payment_terms` VARCHAR(200) COMMENT '付款方式',
  `remark` TEXT COMMENT '备注',
  `is_selected` INT DEFAULT 0 COMMENT '是否已选择: 0-否 1-是',
  `selection_reason` TEXT COMMENT '选择原因',
  `creator_id` BIGINT COMMENT '创建人ID',
  `creator_name` VARCHAR(100) COMMENT '创建人姓名',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` INT DEFAULT 0 COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  INDEX `idx_inquiry_no` (`inquiry_no`),
  INDEX `idx_supplier_id` (`supplier_id`),
  INDEX `idx_inquiry_status` (`inquiry_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购询价表';

-- 供应商操作日志表
CREATE TABLE IF NOT EXISTS `supplier_operation_logs` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `supplier_id` BIGINT NOT NULL COMMENT '供应商ID',
  `operator_id` BIGINT COMMENT '操作人ID',
  `operator_name` VARCHAR(100) COMMENT '操作人姓名',
  `operation_type` VARCHAR(100) COMMENT '操作类型',
  `operation_desc` VARCHAR(500) COMMENT '操作描述',
  `before_data` TEXT COMMENT '操作前数据(JSON)',
  `after_data` TEXT COMMENT '操作后数据(JSON)',
  `ip` VARCHAR(50) COMMENT 'IP地址',
  `user_agent` VARCHAR(500) COMMENT 'User-Agent',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  INDEX `idx_supplier_id` (`supplier_id`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商操作日志表';