spa/database/spa_auxiliary_functions.sql

-- ============================================
-- SPA管理系统 - 辅助功能表SQL
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 收货地址表
DROP TABLE IF EXISTS `spa_customer_address`;
CREATE TABLE `spa_customer_address` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
  `receiver_name` varchar(100) NOT NULL COMMENT '收货人',
  `receiver_phone` varchar(20) NOT NULL COMMENT '联系电话',
  `province` varchar(50) NOT NULL COMMENT '省份',
  `city` varchar(50) NOT NULL COMMENT '城市',
  `district` varchar(50) NOT NULL COMMENT '区县',
  `address` varchar(500) NOT NULL COMMENT '详细地址',
  `is_default` tinyint DEFAULT 0 COMMENT '是否默认 0否 1是',
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表';

-- 2. 实名认证表
DROP TABLE IF EXISTS `spa_real_name_auth`;
CREATE TABLE `spa_real_name_auth` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `real_name` varchar(100) NOT NULL COMMENT '真实姓名',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号',
  `id_card_front` varchar(500) DEFAULT NULL COMMENT '身份证正面',
  `id_card_back` varchar(500) DEFAULT NULL COMMENT '身份证反面',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待审核 2已通过 3已拒绝',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `audit_remark` varchar(500) DEFAULT NULL COMMENT '审核备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user` (`user_type`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实名认证表';

-- 3. 库存商品表
DROP TABLE IF EXISTS `spa_inventory_product`;
CREATE TABLE `spa_inventory_product` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `product_no` varchar(50) NOT NULL COMMENT '商品编号',
  `product_name` varchar(100) NOT NULL COMMENT '商品名称',
  `category` varchar(50) DEFAULT NULL COMMENT '分类',
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `cost_price` decimal(10,2) DEFAULT 0.00 COMMENT '成本价',
  `sale_price` decimal(10,2) DEFAULT 0.00 COMMENT '销售价',
  `stock` int DEFAULT 0 COMMENT '库存数量',
  `min_stock` int DEFAULT 0 COMMENT '最低库存',
  `status` tinyint DEFAULT 1 COMMENT '状态 1正常 2停用',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_product_no` (`product_no`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存商品表';

-- 4. 库存变动记录表
DROP TABLE IF EXISTS `spa_inventory_log`;
CREATE TABLE `spa_inventory_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `product_id` bigint unsigned NOT NULL COMMENT '商品ID',
  `change_type` tinyint NOT NULL COMMENT '变动类型 1入库 2出库 3盘点 4调整',
  `change_quantity` int NOT NULL COMMENT '变动数量',
  `before_stock` int NOT NULL COMMENT '变动前库存',
  `after_stock` int NOT NULL COMMENT '变动后库存',
  `related_id` bigint unsigned DEFAULT NULL COMMENT '关联业务ID',
  `operator_id` bigint unsigned DEFAULT NULL COMMENT '操作人ID',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存变动记录表';

-- 5. 供应商表
DROP TABLE IF EXISTS `spa_supplier`;
CREATE TABLE `spa_supplier` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `supplier_no` varchar(50) NOT NULL COMMENT '供应商编号',
  `supplier_name` varchar(100) NOT NULL COMMENT '供应商名称',
  `contact_person` varchar(100) DEFAULT NULL COMMENT '联系人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `address` varchar(500) DEFAULT NULL COMMENT '地址',
  `status` tinyint DEFAULT 1 COMMENT '状态 1合作中 2已停用',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_supplier_no` (`supplier_no`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商表';

-- 6. 采购订单表
DROP TABLE IF EXISTS `spa_purchase_order`;
CREATE TABLE `spa_purchase_order` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `purchase_no` varchar(50) NOT NULL COMMENT '采购单号',
  `supplier_id` bigint unsigned NOT NULL COMMENT '供应商ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT '总金额',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待审核 2已审核 3已入库 4已取消',
  `purchaser_id` bigint unsigned DEFAULT NULL COMMENT '采购员ID',
  `audit_user_id` bigint unsigned DEFAULT NULL COMMENT '审核人ID',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `remark` text COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_purchase_no` (`purchase_no`),
  KEY `idx_supplier_id` (`supplier_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采购订单表';

-- 7. 采购明细表
DROP TABLE IF EXISTS `spa_purchase_item`;
CREATE TABLE `spa_purchase_item` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `purchase_id` bigint unsigned NOT NULL COMMENT '采购订单ID',
  `product_id` bigint unsigned NOT NULL COMMENT '商品ID',
  `product_name` varchar(100) NOT NULL COMMENT '商品名称',
  `quantity` int NOT NULL COMMENT '数量',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价',
  PRIMARY KEY (`id`),
  KEY `idx_purchase_id` (`purchase_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采购明细表';

-- 8. 设备信息表
DROP TABLE IF EXISTS `spa_equipment`;
CREATE TABLE `spa_equipment` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `equipment_no` varchar(50) NOT NULL COMMENT '设备编号',
  `equipment_name` varchar(100) NOT NULL COMMENT '设备名称',
  `store_id` bigint unsigned DEFAULT NULL COMMENT '所属店铺ID',
  `purchase_date` date DEFAULT NULL COMMENT '购买日期',
  `purchase_price` decimal(10,2) DEFAULT 0.00 COMMENT '购买价格',
  `status` tinyint DEFAULT 1 COMMENT '状态 1正常 2维修中 3已报废',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_equipment_no` (`equipment_no`),
  KEY `idx_store_id` (`store_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备信息表';

-- 9. 设备维护记录表
DROP TABLE IF EXISTS `spa_equipment_maintenance`;
CREATE TABLE `spa_equipment_maintenance` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `equipment_id` bigint unsigned NOT NULL COMMENT '设备ID',
  `maintenance_type` tinyint NOT NULL COMMENT '维护类型 1保养 2维修 3检查',
  `maintenance_date` date NOT NULL COMMENT '维护日期',
  `cost` decimal(10,2) DEFAULT 0.00 COMMENT '费用',
  `operator_id` bigint unsigned DEFAULT NULL COMMENT '操作人ID',
  `remark` text COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_equipment_id` (`equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备维护记录表';

-- 10. 培训课程表
DROP TABLE IF EXISTS `spa_training_course`;
CREATE TABLE `spa_training_course` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `course_name` varchar(100) NOT NULL COMMENT '课程名称',
  `course_type` tinyint DEFAULT 1 COMMENT '课程类型 1技能 2管理 3其他',
  `duration` int DEFAULT 0 COMMENT '课时（小时）',
  `instructor` varchar(100) DEFAULT NULL COMMENT '讲师',
  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
  PRIMARY KEY (`id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='培训课程表';

-- 11. 培训记录表
DROP TABLE IF EXISTS `spa_training_record`;
CREATE TABLE `spa_training_record` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `course_id` bigint unsigned NOT NULL COMMENT '课程ID',
  `employee_id` bigint unsigned NOT NULL COMMENT '员工ID',
  `training_date` date NOT NULL COMMENT '培训日期',
  `score` decimal(5,2) DEFAULT NULL COMMENT '成绩',
  `status` tinyint DEFAULT 1 COMMENT '状态 1已完成 2未完成',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_course_id` (`course_id`),
  KEY `idx_employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='培训记录表';

-- 12. 消息推送表
DROP TABLE IF EXISTS `spa_push_message`;
CREATE TABLE `spa_push_message` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `message_type` tinyint NOT NULL COMMENT '消息类型 1系统通知 2营销推送 3订单提醒',
  `title` varchar(200) NOT NULL COMMENT '标题',
  `content` text COMMENT '内容',
  `target_type` tinyint NOT NULL COMMENT '目标类型 1全部 2指定用户 3指定标签',
  `target_users` json DEFAULT NULL COMMENT '目标用户ID数组',
  `push_time` datetime DEFAULT NULL COMMENT '推送时间',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待推送 2已推送 3已取消',
  PRIMARY KEY (`id`),
  KEY `idx_push_time` (`push_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息推送表';

-- 13. 版本管理表
DROP TABLE IF EXISTS `spa_app_version`;
CREATE TABLE `spa_app_version` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `app_type` tinyint NOT NULL COMMENT 'APP类型 1用户端 2技师端',
  `platform` tinyint NOT NULL COMMENT '平台 1iOS 2Android',
  `version_no` varchar(20) NOT NULL COMMENT '版本号',
  `version_code` int NOT NULL COMMENT '版本代码',
  `download_url` varchar(500) DEFAULT NULL COMMENT '下载地址',
  `update_content` text COMMENT '更新内容',
  `is_force` tinyint DEFAULT 0 COMMENT '是否强制更新 0否 1是',
  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='版本管理表';

-- 14. 反馈建议表
DROP TABLE IF EXISTS `spa_feedback`;
CREATE TABLE `spa_feedback` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `feedback_type` tinyint NOT NULL COMMENT '反馈类型 1功能建议 2问题反馈 3投诉',
  `content` text NOT NULL COMMENT '反馈内容',
  `images` json DEFAULT NULL COMMENT '图片JSON',
  `contact_info` varchar(100) DEFAULT NULL COMMENT '联系方式',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待处理 2处理中 3已处理',
  `reply_content` text COMMENT '回复内容',
  `reply_time` datetime DEFAULT NULL COMMENT '回复时间',
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_type`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='反馈建议表';

-- 15. 预约记录表
DROP TABLE IF EXISTS `spa_appointment`;
CREATE TABLE `spa_appointment` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `appointment_no` varchar(50) NOT NULL COMMENT '预约编号',
  `customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
  `service_item_id` bigint unsigned NOT NULL COMMENT '服务项目ID',
  `technician_id` bigint unsigned DEFAULT NULL COMMENT '技师ID',
  `appointment_date` date NOT NULL COMMENT '预约日期',
  `appointment_time` time NOT NULL COMMENT '预约时间',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待确认 2已确认 3已完成 4已取消',
  `cancel_reason` varchar(500) DEFAULT NULL COMMENT '取消原因',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_appointment_no` (`appointment_no`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预约记录表';

-- 16. Banner广告表
DROP TABLE IF EXISTS `spa_banner`;
CREATE TABLE `spa_banner` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `title` varchar(100) NOT NULL COMMENT '标题',
  `image_url` varchar(500) NOT NULL COMMENT '图片URL',
  `link_type` tinyint DEFAULT 1 COMMENT '链接类型 1无 2内部页面 3外部链接',
  `link_url` varchar(500) DEFAULT NULL COMMENT '链接地址',
  `position` tinyint DEFAULT 1 COMMENT '位置 1首页 2分类页 3其他',
  `sort` int DEFAULT 0 COMMENT '排序',
  `start_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
  PRIMARY KEY (`id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Banner广告表';

SET FOREIGN_KEY_CHECKS = 1;