-- ============================================================
-- 采购管理模块数据库脚本
-- ============================================================

CREATE DATABASE IF NOT EXISTS purchase_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE purchase_db;

-- -----------------------------------------
-- 供应商表
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS `supplier` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `supplier_code` VARCHAR(64) NOT NULL COMMENT '供应商编码',
  `name` VARCHAR(255) NOT NULL COMMENT '供应商名称',
  `contact` VARCHAR(100) COMMENT '联系人',
  `phone` VARCHAR(32) COMMENT '联系电话',
  `email` VARCHAR(128) COMMENT '电子邮箱',
  `address` VARCHAR(512) COMMENT '地址',
  `status` VARCHAR(32) NOT NULL DEFAULT 'active' COMMENT '状态: active/suspended/blacklisted',
  `tax_rate` DECIMAL(10,4) DEFAULT 0.1300 COMMENT '税率',
  `payment_method` VARCHAR(64) COMMENT '付款方式',
  `payment_period` INT COMMENT '付款周期(天)',
  `bank_name` VARCHAR(255) COMMENT '银行名称',
  `bank_account` VARCHAR(64) COMMENT '银行账号',
  `bank_branch` VARCHAR(255) COMMENT '开户行',
  `credit_code` VARCHAR(32) COMMENT '统一社会信用代码',
  `remark` VARCHAR(512) COMMENT '备注',
  `tenant_id` BIGINT UNSIGNED DEFAULT 0 COMMENT '租户ID',
  `create_by` BIGINT UNSIGNED COMMENT '创建人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` BIGINT UNSIGNED COMMENT '更新人',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT UNSIGNED DEFAULT 0 COMMENT '逻辑删除: 0=未删除, 1=已删除',
  UNIQUE KEY `uk_supplier_code` (`supplier_code`),
  KEY `idx_status` (`status`),
  KEY `idx_name` (`name`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商表';

-- -----------------------------------------
-- 采购订单表
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_order` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `order_no` VARCHAR(64) NOT NULL COMMENT '采购单号',
  `supplier_id` BIGINT UNSIGNED NOT NULL COMMENT '供应商ID',
  `supplier_name` VARCHAR(255) COMMENT '供应商名称',
  `warehouse_id` BIGINT UNSIGNED COMMENT '仓库ID',
  `warehouse_name` VARCHAR(255) COMMENT '仓库名称',
  `order_date` DATE COMMENT '订单日期',
  `expected_date` DATE COMMENT '预计到货日期',
  `status` VARCHAR(32) NOT NULL DEFAULT 'draft' COMMENT '状态: draft/pending/approved/partial_inbound/all_inbound/partial_return/completed/cancelled',
  `total_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '订单总金额(未税)',
  `tax_rate` DECIMAL(10,4) DEFAULT 0.1300 COMMENT '税率',
  `tax_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '税额',
  `total_with_tax` DECIMAL(18,2) DEFAULT 0.00 COMMENT '含税总金额',
  `inbound_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '已入库金额',
  `paid_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '已付款金额',
  `currency` VARCHAR(16) DEFAULT 'CNY' COMMENT '币种',
  `payment_method` VARCHAR(64) COMMENT '付款方式',
  `contact` VARCHAR(100) COMMENT '联系人',
  `phone` VARCHAR(32) COMMENT '联系电话',
  `delivery_address` VARCHAR(512) COMMENT '收货地址',
  `remark` VARCHAR(1024) COMMENT '备注',
  `approver_id` BIGINT UNSIGNED COMMENT '审批人',
  `approve_time` DATETIME COMMENT '审批时间',
  `approve_remark` VARCHAR(512) COMMENT '审批备注',
  `tenant_id` BIGINT UNSIGNED DEFAULT 0 COMMENT '租户ID',
  `create_by` BIGINT UNSIGNED COMMENT '创建人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` BIGINT UNSIGNED COMMENT '更新人',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT UNSIGNED DEFAULT 0 COMMENT '逻辑删除',
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_supplier` (`supplier_id`),
  KEY `idx_status` (`status`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购订单表';

-- -----------------------------------------
-- 采购订单明细表（JSON存储在purchase_order.items）
-- 也可单独建表存储，以下为示例结构
-- -----------------------------------------
-- 采购入库单表
CREATE TABLE IF NOT EXISTS `purchase_inbound` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `inbound_no` VARCHAR(64) NOT NULL COMMENT '入库单号',
  `purchase_order_id` BIGINT UNSIGNED NOT NULL COMMENT '采购单ID',
  `purchase_order_no` VARCHAR(64) NOT NULL COMMENT '采购单号',
  `supplier_id` BIGINT UNSIGNED NOT NULL COMMENT '供应商ID',
  `supplier_name` VARCHAR(255) COMMENT '供应商名称',
  `warehouse_id` BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
  `warehouse_name` VARCHAR(255) COMMENT '仓库名称',
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/partial/completed/cancelled',
  `total_quantity` INT DEFAULT 0 COMMENT '入库总数量',
  `accepted_quantity` INT DEFAULT 0 COMMENT '已验收数量',
  `defective_quantity` INT DEFAULT 0 COMMENT '残次品数量',
  `total_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '入库单总金额',
  `inbound_time` DATETIME COMMENT '实际入库时间',
  `expected_time` DATETIME COMMENT '期望到货时间',
  `remark` VARCHAR(1024) COMMENT '备注',
  `warehouse_inbound_no` VARCHAR(64) COMMENT '仓库入库单号',
  `operator_id` BIGINT UNSIGNED COMMENT '操作人ID',
  `operator_name` VARCHAR(100) COMMENT '操作人名称',
  `tenant_id` BIGINT UNSIGNED DEFAULT 0 COMMENT '租户ID',
  `create_by` BIGINT UNSIGNED COMMENT '创建人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` BIGINT UNSIGNED COMMENT '更新人',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT UNSIGNED DEFAULT 0 COMMENT '逻辑删除',
  UNIQUE KEY `uk_inbound_no` (`inbound_no`),
  KEY `idx_purchase_order` (`purchase_order_id`),
  KEY `idx_supplier` (`supplier_id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_status` (`status`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购入库单表';

-- -----------------------------------------
-- 采购退货单表
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_return` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `return_no` VARCHAR(64) NOT NULL COMMENT '退货单号',
  `purchase_order_id` BIGINT UNSIGNED NOT NULL COMMENT '采购单ID',
  `purchase_order_no` VARCHAR(64) NOT NULL COMMENT '采购单号',
  `supplier_id` BIGINT UNSIGNED NOT NULL COMMENT '供应商ID',
  `supplier_name` VARCHAR(255) COMMENT '供应商名称',
  `warehouse_id` BIGINT UNSIGNED NOT NULL COMMENT '仓库ID',
  `warehouse_name` VARCHAR(255) COMMENT '仓库名称',
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/partial/completed/cancelled',
  `total_quantity` INT DEFAULT 0 COMMENT '退货总数量',
  `total_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '退货总金额',
  `refunded_amount` DECIMAL(18,2) DEFAULT 0.00 COMMENT '已退款金额',
  `return_reason` VARCHAR(512) COMMENT '退货原因',
  `return_type` VARCHAR(32) COMMENT '退货类型: quality/wrong/other',
  `outbound_time` DATETIME COMMENT '出库时间',
  `remark` VARCHAR(1024) COMMENT '备注',
  `warehouse_outbound_no` VARCHAR(64) COMMENT '仓库出库单号',
  `operator_id` BIGINT UNSIGNED COMMENT '操作人ID',
  `operator_name` VARCHAR(100) COMMENT '操作人名称',
  `tenant_id` BIGINT UNSIGNED DEFAULT 0 COMMENT '租户ID',
  `create_by` BIGINT UNSIGNED COMMENT '创建人',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` BIGINT UNSIGNED COMMENT '更新人',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT UNSIGNED DEFAULT 0 COMMENT '逻辑删除',
  UNIQUE KEY `uk_return_no` (`return_no`),
  KEY `idx_purchase_order` (`purchase_order_id`),
  KEY `idx_supplier` (`supplier_id`),
  KEY `idx_status` (`status`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购退货单表';