yuezi-saas/docs/02-database-design.md

宫中有喜 — 数据库设计

一、设计原则

1. 门店隔离：所有业务表均包含 store_id 字段，查询自动过滤
2. 软删除：关键业务表使用 deleted_at 软删除
3. 审计字段：所有表包含 created_by、updated_by、created_at、updated_at
4. 枚举用 tinyint：状态字段用 tinyint + 代码层枚举类，不用 enum 类型
5. 金额用 decimal(12,2)：财务金额统一精度
6. 索引策略：外键自动索引 + 常用查询组合索引

二、核心表清单（按模块）

2.1 系统基础（RBAC + 门店）

-- 区域
CREATE TABLE regions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '区域名称',
    sort INT DEFAULT 0,
    status TINYINT DEFAULT 1 COMMENT '1启用 0停用',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='区域表';

-- 门店
CREATE TABLE stores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    region_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL COMMENT '门店名称',
    code VARCHAR(20) UNIQUE COMMENT '门店编码',
    address VARCHAR(255),
    phone VARCHAR(20),
    logo VARCHAR(255),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_region (region_id)
) COMMENT='门店表';

-- 部门
CREATE TABLE departments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED DEFAULT 0,
    name VARCHAR(50) NOT NULL,
    sort INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store (store_id)
) COMMENT='部门表';

-- 职务
CREATE TABLE positions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    sort INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='职务表';

-- 用户（员工）
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    department_id BIGINT UNSIGNED NULL,
    position_id BIGINT UNSIGNED NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL COMMENT '真实姓名',
    phone VARCHAR(20),
    email VARCHAR(100),
    avatar VARCHAR(255),
    status TINYINT DEFAULT 0 COMMENT '0待审核 1正常 2禁用',
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    UNIQUE INDEX idx_username (username),
    INDEX idx_store (store_id),
    INDEX idx_phone (phone)
) COMMENT='用户表';

-- 角色
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NULL COMMENT 'NULL=全局角色',
    name VARCHAR(50) NOT NULL,
    code VARCHAR(50) NOT NULL COMMENT '角色编码',
    description VARCHAR(255),
    is_system TINYINT DEFAULT 0 COMMENT '系统内置不可删',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE INDEX idx_code (code)
) COMMENT='角色表';

-- 权限
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED DEFAULT 0,
    name VARCHAR(100) NOT NULL COMMENT '权限名称',
    code VARCHAR(100) NOT NULL COMMENT '权限编码 如 crm.lead.create',
    type TINYINT NOT NULL COMMENT '1菜单 2按钮 3接口',
    path VARCHAR(255) COMMENT '前端路由/API路径',
    icon VARCHAR(50),
    sort INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE INDEX idx_code (code)
) COMMENT='权限表';

-- 角色-权限关联
CREATE TABLE role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id)
) COMMENT='角色权限关联表';

-- 用户-角色关联
CREATE TABLE user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id)
) COMMENT='用户角色关联表';

-- 操作日志
CREATE TABLE operation_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED,
    user_id BIGINT UNSIGNED,
    module VARCHAR(50) COMMENT '模块名',
    action VARCHAR(50) COMMENT '动作',
    target_type VARCHAR(50) COMMENT '操作对象类型',
    target_id BIGINT UNSIGNED COMMENT '操作对象ID',
    before_data JSON COMMENT '变更前',
    after_data JSON COMMENT '变更后',
    ip VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP NULL,
    INDEX idx_store_user (store_id, user_id),
    INDEX idx_created (created_at)
) COMMENT='操作日志表';

2.2 CRM 模块

-- 渠道来源
CREATE TABLE channels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    type TINYINT COMMENT '1线上 2线下 3转介绍',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='渠道来源表';

-- 线索
CREATE TABLE leads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED,
    name VARCHAR(50) NOT NULL COMMENT '客户姓名',
    phone VARCHAR(20) NOT NULL,
    wechat VARCHAR(50),
    expected_date DATE COMMENT '预产期',
    source VARCHAR(50) COMMENT '具体来源',
    status TINYINT DEFAULT 1 COMMENT '1新建 2跟进中 3已转化 4无效',
    owner_id BIGINT UNSIGNED COMMENT '负责销售',
    remark TEXT,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_status (store_id, status),
    INDEX idx_owner (owner_id),
    INDEX idx_phone (phone)
) COMMENT='线索表';

-- 线索跟进记录
CREATE TABLE lead_follows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    type TINYINT COMMENT '1电话 2微信 3到店 4其他',
    content TEXT,
    next_follow_at TIMESTAMP NULL COMMENT '下次跟进时间',
    created_at TIMESTAMP NULL,
    INDEX idx_lead (lead_id)
) COMMENT='线索跟进记录';

-- 客户
CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    lead_id BIGINT UNSIGNED COMMENT '来源线索',
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    id_card VARCHAR(20),
    wechat VARCHAR(50),
    birthday DATE,
    expected_date DATE COMMENT '预产期',
    actual_date DATE COMMENT '实际生产日期',
    baby_count TINYINT DEFAULT 1,
    tags JSON COMMENT '标签数组',
    status TINYINT DEFAULT 1 COMMENT '1潜在 2签约 3在住 4离店 5无效',
    owner_id BIGINT UNSIGNED COMMENT '负责人',
    remark TEXT,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_store_status (store_id, status),
    INDEX idx_phone (phone)
) COMMENT='客户表';

-- 客户家属
CREATE TABLE customer_families (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50),
    phone VARCHAR(20),
    relation VARCHAR(20) COMMENT '关系：丈夫/母亲/婆婆等',
    is_emergency TINYINT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_customer (customer_id)
) COMMENT='客户家属表';

-- 合同
CREATE TABLE contracts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    contract_no VARCHAR(50) UNIQUE COMMENT '合同编号',
    package_id BIGINT UNSIGNED COMMENT '套餐ID',
    package_name VARCHAR(100),
    total_amount DECIMAL(12,2) NOT NULL COMMENT '合同总额',
    discount_amount DECIMAL(12,2) DEFAULT 0 COMMENT '优惠金额',
    actual_amount DECIMAL(12,2) NOT NULL COMMENT '实收金额',
    days INT COMMENT '入住天数',
    check_in_date DATE,
    check_out_date DATE,
    status TINYINT DEFAULT 0 COMMENT '0待审 1通过 2无效 3已退',
    sign_type TINYINT COMMENT '1线下 2网签',
    audit_user_id BIGINT UNSIGNED,
    audit_at TIMESTAMP NULL,
    audit_remark VARCHAR(255),
    remark TEXT,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store (store_id),
    INDEX idx_customer (customer_id)
) COMMENT='合同表';

-- 问卷模板
CREATE TABLE questionnaire_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    questions JSON COMMENT '题目定义JSON',
    type TINYINT COMMENT '1入住前 2入住中 3离店 4回访',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='问卷模板表';

-- 问卷回收
CREATE TABLE questionnaire_answers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    answers JSON,
    score DECIMAL(5,2),
    status TINYINT DEFAULT 0 COMMENT '0未完成 1已提交',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_template (template_id),
    INDEX idx_customer (customer_id)
) COMMENT='问卷回收表';

-- 投诉
CREATE TABLE complaints (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    type TINYINT COMMENT '1服务 2膳食 3环境 4其他',
    content TEXT NOT NULL,
    images JSON,
    status TINYINT DEFAULT 0 COMMENT '0待处理 1处理中 2已解决 3已关闭',
    handler_id BIGINT UNSIGNED,
    handle_result TEXT,
    handle_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_status (store_id, status)
) COMMENT='投诉表';

2.3 房务模块

-- 房型
CREATE TABLE room_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(12,2) COMMENT '日单价',
    description TEXT,
    images JSON,
    facilities JSON COMMENT '设施列表',
    sort INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='房型表';

-- 房间
CREATE TABLE rooms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    room_type_id BIGINT UNSIGNED NOT NULL,
    floor VARCHAR(10) COMMENT '楼层',
    number VARCHAR(20) NOT NULL COMMENT '房号',
    status TINYINT DEFAULT 1 COMMENT '1空房 2已预定 3入住 4维修 5清洁',
    sort INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE INDEX idx_store_number (store_id, number)
) COMMENT='房间表';

-- 预定/入住记录
CREATE TABLE reservations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    contract_id BIGINT UNSIGNED,
    room_id BIGINT UNSIGNED NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    actual_check_in TIMESTAMP NULL,
    actual_check_out TIMESTAMP NULL,
    status TINYINT DEFAULT 0 COMMENT '0预定 1已入住 2已退房 3已取消',
    remark TEXT,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_room_date (room_id, check_in_date, check_out_date),
    INDEX idx_customer (customer_id)
) COMMENT='预定/入住记录表';

-- 客户外出记录
CREATE TABLE customer_outings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    out_at TIMESTAMP NOT NULL,
    expected_back_at TIMESTAMP,
    actual_back_at TIMESTAMP NULL,
    reason TEXT,
    risk_note TEXT COMMENT '风险提醒说明',
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='客户外出记录';

-- 呼叫记录
CREATE TABLE call_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    room_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED,
    type TINYINT COMMENT '1护理 2清洁 3送水 4其他',
    status TINYINT DEFAULT 0 COMMENT '0待处理 1处理中 2已完成',
    handler_id BIGINT UNSIGNED,
    handle_at TIMESTAMP NULL,
    remark VARCHAR(255),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_status (store_id, status)
) COMMENT='呼叫记录表';

-- 场地
CREATE TABLE venues (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL COMMENT 'SPA房/瑜伽房/活动室',
    capacity INT,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='场地表';

-- 场地预约
CREATE TABLE venue_bookings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    venue_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED,
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    purpose VARCHAR(100),
    status TINYINT DEFAULT 0 COMMENT '0预约 1使用中 2已结束 3已取消',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_venue_date (venue_id, booking_date)
) COMMENT='场地预约表';

2.4 护理模块

-- 护理档案
CREATE TABLE care_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    reservation_id BIGINT UNSIGNED,
    type TINYINT COMMENT '1妈妈 2宝宝',
    baby_name VARCHAR(50),
    baby_gender TINYINT COMMENT '1男 2女',
    baby_birthday DATETIME,
    birth_weight DECIMAL(5,2),
    birth_method TINYINT COMMENT '1顺产 2剖宫产',
    allergies TEXT COMMENT '过敏信息',
    medical_history TEXT COMMENT '既往病史',
    risk_level TINYINT DEFAULT 0 COMMENT '0正常 1低风险 2高风险',
    assessment JSON COMMENT '入所评估结果',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_customer (customer_id)
) COMMENT='护理档案表';

-- 护理计划
CREATE TABLE care_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    care_profile_id BIGINT UNSIGNED NOT NULL,
    plan_date DATE NOT NULL,
    stage VARCHAR(50) COMMENT '阶段：产后第X天',
    items JSON COMMENT '计划项目列表',
    nurse_id BIGINT UNSIGNED COMMENT '责任护士',
    status TINYINT DEFAULT 0 COMMENT '0待执行 1执行中 2已完成',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_profile_date (care_profile_id, plan_date)
) COMMENT='护理计划表';

-- 护理记录
CREATE TABLE care_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    care_profile_id BIGINT UNSIGNED NOT NULL,
    care_plan_id BIGINT UNSIGNED,
    type TINYINT COMMENT '1妈妈护理 2宝宝护理',
    items JSON COMMENT '执行的护理项目与结果',
    remark TEXT,
    images JSON COMMENT '护理照片',
    nurse_id BIGINT UNSIGNED NOT NULL,
    recorded_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_profile (care_profile_id),
    INDEX idx_date (recorded_at)
) COMMENT='护理记录表';

-- 护理异常
CREATE TABLE care_exceptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    care_profile_id BIGINT UNSIGNED NOT NULL,
    reporter_id BIGINT UNSIGNED NOT NULL,
    level TINYINT COMMENT '1一般 2重要 3紧急',
    description TEXT NOT NULL,
    images JSON,
    status TINYINT DEFAULT 0 COMMENT '0上报 1处理中 2已解决',
    handler_id BIGINT UNSIGNED,
    handle_result TEXT,
    handle_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_profile (care_profile_id)
) COMMENT='护理异常表';

-- 健康指标记录
CREATE TABLE health_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    care_profile_id BIGINT UNSIGNED NOT NULL,
    metric_type VARCHAR(30) NOT NULL COMMENT 'temperature/weight/jaundice/blood_pressure',
    value DECIMAL(8,2) NOT NULL,
    unit VARCHAR(10),
    recorded_at TIMESTAMP NOT NULL,
    recorder_id BIGINT UNSIGNED,
    remark VARCHAR(255),
    created_at TIMESTAMP NULL,
    INDEX idx_profile_type (care_profile_id, metric_type),
    INDEX idx_recorded (recorded_at)
) COMMENT='健康指标记录表';

2.5 月子餐模块

-- 菜品库
CREATE TABLE dishes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(30) COMMENT '分类：汤/主食/配菜/甜品',
    description TEXT,
    image VARCHAR(255),
    ingredients JSON COMMENT '主要食材',
    contraindications JSON COMMENT '禁忌分类',
    price DECIMAL(8,2) DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store (store_id)
) COMMENT='菜品库';

-- 排餐模板
CREATE TABLE meal_plan_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    stage VARCHAR(30) COMMENT '适用阶段：产后1-7天/8-14天等',
    meals JSON COMMENT '每日餐次与菜品配置',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='排餐模板表';

-- 每日排餐
CREATE TABLE daily_meal_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    plan_date DATE NOT NULL,
    meal_type TINYINT NOT NULL COMMENT '1早餐 2午餐 3下午茶 4晚餐 5宵夜',
    dishes JSON COMMENT '菜品列表',
    special_note TEXT COMMENT '特殊要求',
    status TINYINT DEFAULT 0 COMMENT '0待备餐 1已备餐 2已送达 3未用',
    deliver_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_customer_date (customer_id, plan_date),
    INDEX idx_store_date (store_id, plan_date)
) COMMENT='每日排餐表';

-- 膳食评价
CREATE TABLE meal_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    daily_meal_plan_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    score TINYINT COMMENT '1-5星',
    content TEXT,
    created_at TIMESTAMP NULL,
    INDEX idx_plan (daily_meal_plan_id)
) COMMENT='膳食评价表';

2.6 服务与产康模块

-- 服务项目
CREATE TABLE service_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    category TINYINT COMMENT '1产康 2零售 3加餐 4其他',
    price DECIMAL(10,2) NOT NULL,
    duration INT COMMENT '服务时长(分钟)',
    description TEXT,
    image VARCHAR(255),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_cat (store_id, category)
) COMMENT='服务项目表';

-- 服务包/套餐
CREATE TABLE service_packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    items JSON COMMENT '包含项目与次数',
    price DECIMAL(10,2) NOT NULL,
    validity_days INT COMMENT '有效天数',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='服务包表';

-- 服务订单
CREATE TABLE service_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    order_no VARCHAR(50) UNIQUE,
    type TINYINT COMMENT '1单项 2服务包 3零售 4加餐',
    items JSON COMMENT '订单项明细',
    total_amount DECIMAL(12,2),
    discount_amount DECIMAL(12,2) DEFAULT 0,
    actual_amount DECIMAL(12,2),
    status TINYINT DEFAULT 0 COMMENT '0待付 1已付 2已完成 3已取消 4已退',
    pay_method TINYINT COMMENT '1微信 2支付宝 3现金 4挂账',
    paid_at TIMESTAMP NULL,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store (store_id),
    INDEX idx_customer (customer_id)
) COMMENT='服务订单表';

-- 服务执行记录
CREATE TABLE service_executions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_order_id BIGINT UNSIGNED NOT NULL,
    service_item_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    technician_id BIGINT UNSIGNED COMMENT '技师/产康师',
    scheduled_at TIMESTAMP COMMENT '预约时间',
    started_at TIMESTAMP NULL,
    ended_at TIMESTAMP NULL,
    status TINYINT DEFAULT 0 COMMENT '0待执行 1执行中 2已完成 3已取消',
    remark TEXT,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_order (service_order_id),
    INDEX idx_technician (technician_id)
) COMMENT='服务执行记录表';

-- 服务评价
CREATE TABLE service_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_order_id BIGINT UNSIGNED,
    service_execution_id BIGINT UNSIGNED,
    customer_id BIGINT UNSIGNED NOT NULL,
    score TINYINT COMMENT '1-5星',
    content TEXT,
    created_at TIMESTAMP NULL,
    INDEX idx_order (service_order_id)
) COMMENT='服务评价表';

2.7 月嫂模块

-- 月嫂档案
CREATE TABLE nannies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    id_card VARCHAR(20),
    avatar VARCHAR(255),
    level TINYINT COMMENT '1初级 2中级 3高级 4金牌',
    skills JSON COMMENT '技能标签',
    experience_years INT,
    health_cert VARCHAR(255) COMMENT '健康证',
    cooperation_type TINYINT COMMENT '1自有 2合作 3兼职',
    base_salary DECIMAL(10,2),
    introduction TEXT,
    status TINYINT DEFAULT 1 COMMENT '1空闲 2服务中 3休假 4停用',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_status (store_id, status)
) COMMENT='月嫂档案表';

-- 月嫂订单
CREATE TABLE nanny_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    nanny_id BIGINT UNSIGNED,
    order_no VARCHAR(50) UNIQUE,
    service_type TINYINT COMMENT '1月嫂 2育儿嫂',
    start_date DATE,
    end_date DATE,
    days INT,
    price DECIMAL(12,2),
    status TINYINT DEFAULT 0 COMMENT '0待匹配 1待确认 2已签约 3服务中 4已完成 5已取消',
    match_candidates JSON COMMENT '匹配候选月嫂',
    remark TEXT,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store (store_id),
    INDEX idx_nanny (nanny_id)
) COMMENT='月嫂订单表';

-- 月嫂排班
CREATE TABLE nanny_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nanny_id BIGINT UNSIGNED NOT NULL,
    nanny_order_id BIGINT UNSIGNED,
    schedule_date DATE NOT NULL,
    type TINYINT COMMENT '1服务 2休息 3培训',
    remark VARCHAR(255),
    created_at TIMESTAMP NULL,
    INDEX idx_nanny_date (nanny_id, schedule_date)
) COMMENT='月嫂排班表';

-- 月嫂评价
CREATE TABLE nanny_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nanny_order_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    nanny_id BIGINT UNSIGNED NOT NULL,
    score TINYINT COMMENT '1-5',
    content TEXT,
    created_at TIMESTAMP NULL,
    INDEX idx_nanny (nanny_id)
) COMMENT='月嫂评价表';

2.8 进销存模块

-- 仓库
CREATE TABLE warehouses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    manager_id BIGINT UNSIGNED,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='仓库表';

-- 供应商
CREATE TABLE suppliers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    contact VARCHAR(50),
    phone VARCHAR(20),
    address VARCHAR(255),
    bank_info VARCHAR(255),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='供应商表';

-- 物资（SKU）
CREATE TABLE materials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) COMMENT 'SKU编码',
    category VARCHAR(50),
    unit VARCHAR(20) COMMENT '单位',
    spec VARCHAR(100) COMMENT '规格',
    min_stock INT DEFAULT 0 COMMENT '安全库存',
    shelf_life_days INT COMMENT '保质天数',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store (store_id),
    INDEX idx_code (code)
) COMMENT='物资表';

-- 库存
CREATE TABLE inventories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    material_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(12,2) DEFAULT 0,
    batch_no VARCHAR(50),
    expire_date DATE,
    updated_at TIMESTAMP NULL,
    UNIQUE INDEX idx_wh_mat (warehouse_id, material_id, batch_no)
) COMMENT='库存表';

-- 采购单
CREATE TABLE purchase_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    order_no VARCHAR(50) UNIQUE,
    supplier_id BIGINT UNSIGNED,
    total_amount DECIMAL(12,2),
    status TINYINT DEFAULT 0 COMMENT '0草稿 1待审批 2已审批 3已入库 4已取消',
    items JSON COMMENT '采购明细',
    audit_user_id BIGINT UNSIGNED,
    audit_at TIMESTAMP NULL,
    remark TEXT,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='采购单表';

-- 出入库单
CREATE TABLE stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    movement_no VARCHAR(50) UNIQUE,
    type TINYINT NOT NULL COMMENT '1入库 2出库 3调拨 4盘点',
    direction TINYINT COMMENT '1入 2出',
    items JSON COMMENT '物资明细',
    related_order VARCHAR(50) COMMENT '关联单号',
    status TINYINT DEFAULT 0 COMMENT '0草稿 1已确认',
    operator_id BIGINT UNSIGNED,
    remark TEXT,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_type (store_id, type)
) COMMENT='出入库单表';

2.9 财务模块

-- 资金账户
CREATE TABLE fund_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL COMMENT '账户名称',
    type TINYINT COMMENT '1银行 2微信 3支付宝 4现金',
    balance DECIMAL(14,2) DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='资金账户表';

-- 收支记录
CREATE TABLE financial_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    record_no VARCHAR(50) UNIQUE,
    direction TINYINT NOT NULL COMMENT '1收入 2支出',
    category VARCHAR(50) COMMENT '分类：套餐/产康/零售/押金/工资/采购...',
    amount DECIMAL(12,2) NOT NULL,
    customer_id BIGINT UNSIGNED,
    related_type VARCHAR(50) COMMENT '关联单据类型',
    related_id BIGINT UNSIGNED COMMENT '关联单据ID',
    pay_method TINYINT COMMENT '1微信 2支付宝 3现金 4转账 5POS',
    status TINYINT DEFAULT 1 COMMENT '1已确认 2待确认 3已冲正',
    remark TEXT,
    operator_id BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_dir (store_id, direction),
    INDEX idx_customer (customer_id),
    INDEX idx_date (created_at)
) COMMENT='收支记录表';

-- 客户账户
CREATE TABLE customer_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    store_id BIGINT UNSIGNED NOT NULL,
    type TINYINT COMMENT '1套餐账 2会员账 3押金账 4预存卡',
    total_amount DECIMAL(12,2) DEFAULT 0 COMMENT '总额',
    used_amount DECIMAL(12,2) DEFAULT 0 COMMENT '已用',
    balance DECIMAL(12,2) DEFAULT 0 COMMENT '余额',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_customer (customer_id)
) COMMENT='客户账户表';

-- 客户账户流水
CREATE TABLE customer_account_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_account_id BIGINT UNSIGNED NOT NULL,
    direction TINYINT COMMENT '1充值/存入 2消费/扣除 3退还',
    amount DECIMAL(12,2) NOT NULL,
    balance_after DECIMAL(12,2),
    related_type VARCHAR(50),
    related_id BIGINT UNSIGNED,
    remark VARCHAR(255),
    operator_id BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    INDEX idx_account (customer_account_id)
) COMMENT='客户账户流水表';

-- 服务卡/代金卡
CREATE TABLE voucher_cards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    card_no VARCHAR(50) UNIQUE,
    type TINYINT COMMENT '1服务卡 2代金卡',
    total_amount DECIMAL(10,2),
    balance DECIMAL(10,2),
    expire_date DATE,
    status TINYINT DEFAULT 1 COMMENT '1正常 2冻结 3已退 4已过期',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_customer (customer_id)
) COMMENT='服务卡/代金卡表';

2.10 人事薪资模块

-- 员工档案（扩展 users 表的人事信息）
CREATE TABLE employee_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    hire_date DATE,
    regular_date DATE COMMENT '转正日期',
    contract_start DATE,
    contract_end DATE,
    id_card VARCHAR(20),
    bank_card VARCHAR(30),
    bank_name VARCHAR(50),
    education VARCHAR(20),
    emergency_contact VARCHAR(50),
    emergency_phone VARCHAR(20),
    attachments JSON COMMENT '证件附件',
    remark TEXT,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='员工档案表';

-- 班次模板
CREATE TABLE shift_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    start_time TIME,
    end_time TIME,
    color VARCHAR(10) COMMENT '颜色标识',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='班次模板表';

-- 排班
CREATE TABLE schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    shift_template_id BIGINT UNSIGNED,
    schedule_date DATE NOT NULL,
    remark VARCHAR(255),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_user_date (user_id, schedule_date),
    INDEX idx_store_date (store_id, schedule_date)
) COMMENT='排班表';

-- 业绩记录
CREATE TABLE performances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    month VARCHAR(7) COMMENT '2024-01',
    metric_type VARCHAR(50) COMMENT '指标类型',
    metric_value DECIMAL(12,2),
    remark VARCHAR(255),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_user_month (user_id, month)
) COMMENT='业绩记录表';

-- 提成方案
CREATE TABLE commission_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100),
    rules JSON COMMENT '提成规则',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='提成方案表';

-- 薪资记录
CREATE TABLE salary_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    month VARCHAR(7) NOT NULL,
    base_salary DECIMAL(10,2),
    commission DECIMAL(10,2) DEFAULT 0,
    bonus DECIMAL(10,2) DEFAULT 0,
    deduction DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2),
    status TINYINT DEFAULT 0 COMMENT '0待发 1已发',
    paid_at TIMESTAMP NULL,
    remark TEXT,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_user_month (user_id, month)
) COMMENT='薪资记录表';

2.11 办公协同模块

-- 审批流模板
CREATE TABLE approval_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) COMMENT '请假/报销/采购/优惠/退款...',
    flow JSON COMMENT '审批节点配置',
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='审批流模板表';

-- 审批单
CREATE TABLE approvals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED,
    approval_no VARCHAR(50) UNIQUE,
    type VARCHAR(50),
    title VARCHAR(200),
    content JSON COMMENT '表单数据',
    applicant_id BIGINT UNSIGNED NOT NULL,
    current_step INT DEFAULT 0,
    status TINYINT DEFAULT 0 COMMENT '0待审批 1审批中 2已通过 3已拒绝 4已撤回',
    related_type VARCHAR(50),
    related_id BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_store_status (store_id, status),
    INDEX idx_applicant (applicant_id)
) COMMENT='审批单表';

-- 审批记录
CREATE TABLE approval_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    approval_id BIGINT UNSIGNED NOT NULL,
    step INT,
    approver_id BIGINT UNSIGNED NOT NULL,
    action TINYINT COMMENT '1同意 2拒绝 3转审',
    opinion TEXT,
    created_at TIMESTAMP NULL,
    INDEX idx_approval (approval_id)
) COMMENT='审批记录表';

-- 通知/公告
CREATE TABLE notices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    type TINYINT COMMENT '1公告 2通知 3系统消息',
    target_type TINYINT COMMENT '1全员 2指定角色 3指定人',
    target_ids JSON,
    publish_at TIMESTAMP NULL,
    status TINYINT DEFAULT 0 COMMENT '0草稿 1已发布',
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='通知公告表';

-- 交班记录
CREATE TABLE shift_handovers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    department VARCHAR(50),
    handover_date DATE NOT NULL,
    from_user_id BIGINT UNSIGNED NOT NULL,
    to_user_id BIGINT UNSIGNED,
    content TEXT NOT NULL,
    items JSON COMMENT '交接事项清单',
    status TINYINT DEFAULT 0 COMMENT '0待接 1已接',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='交班记录表';

2.12 知识库模块

-- 知识分类
CREATE TABLE kb_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED DEFAULT 0,
    name VARCHAR(50) NOT NULL,
    target TINYINT COMMENT '1员工 2客户 3通用',
    sort INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='知识分类表';

-- 知识内容
CREATE TABLE kb_articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content LONGTEXT,
    media_type TINYINT COMMENT '1图文 2视频 3文档',
    media_url VARCHAR(500),
    cover VARCHAR(255),
    view_count INT DEFAULT 0,
    status TINYINT DEFAULT 0 COMMENT '0草稿 1已发布 2已下架',
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_category (category_id)
) COMMENT='知识内容表';

-- 学习任务
CREATE TABLE learning_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200),
    articles JSON COMMENT '关联文章/视频ID列表',
    target_roles JSON COMMENT '目标角色',
    deadline DATE,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='学习任务表';

-- 学习记录
CREATE TABLE learning_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    learning_task_id BIGINT UNSIGNED,
    user_id BIGINT UNSIGNED NOT NULL,
    article_id BIGINT UNSIGNED NOT NULL,
    progress TINYINT DEFAULT 0 COMMENT '0-100',
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_user (user_id)
) COMMENT='学习记录表';

-- 考试
CREATE TABLE exams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200),
    questions JSON,
    pass_score INT DEFAULT 60,
    time_limit INT COMMENT '分钟',
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='考试表';

-- 考试记录
CREATE TABLE exam_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exam_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    answers JSON,
    score INT,
    is_pass TINYINT,
    started_at TIMESTAMP,
    submitted_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    INDEX idx_exam_user (exam_id, user_id)
) COMMENT='考试记录表';

2.13 微客宝专用表

-- 客户微信绑定
CREATE TABLE customer_wechat (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    open_id VARCHAR(64) NOT NULL,
    union_id VARCHAR(64),
    nickname VARCHAR(100),
    avatar VARCHAR(255),
    session_key VARCHAR(255),
    phone VARCHAR(20),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE INDEX idx_openid (open_id),
    INDEX idx_customer (customer_id)
) COMMENT='客户微信绑定表';

-- 优惠券定义
CREATE TABLE coupon_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    type TINYINT COMMENT '1满减 2折扣 3代金',
    value DECIMAL(10,2) COMMENT '面值/折扣值',
    min_amount DECIMAL(10,2) DEFAULT 0 COMMENT '使用门槛',
    applicable JSON COMMENT '适用范围',
    total_count INT COMMENT '总发行量',
    issued_count INT DEFAULT 0,
    start_date DATE,
    end_date DATE,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='优惠券模板表';

-- 客户优惠券
CREATE TABLE customer_coupons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(50) UNIQUE,
    status TINYINT DEFAULT 0 COMMENT '0未使用 1已使用 2已过期',
    used_at TIMESTAMP NULL,
    used_order_id BIGINT UNSIGNED,
    expire_date DATE,
    created_at TIMESTAMP NULL,
    INDEX idx_customer (customer_id)
) COMMENT='客户优惠券表';

-- 活动
CREATE TABLE activities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content LONGTEXT,
    cover VARCHAR(255),
    activity_date DATETIME,
    location VARCHAR(200),
    max_participants INT,
    current_participants INT DEFAULT 0,
    status TINYINT DEFAULT 0 COMMENT '0草稿 1报名中 2已满 3已结束',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='活动表';

-- 活动报名
CREATE TABLE activity_registrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    activity_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50),
    phone VARCHAR(20),
    expected_date DATE COMMENT '预产期',
    status TINYINT DEFAULT 0 COMMENT '0已报名 1已签到 2已取消',
    created_at TIMESTAMP NULL,
    INDEX idx_activity (activity_id)
) COMMENT='活动报名表';

-- 母婴圈动态
CREATE TABLE community_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200),
    content TEXT,
    images JSON,
    type TINYINT COMMENT '1官方 2活动花絮 3课程',
    view_count INT DEFAULT 0,
    like_count INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) COMMENT='母婴圈动态表';

三、ER 关系要点

stores 1──N rooms
stores 1──N users
stores 1──N customers (通过业务表关联)

customers 1──N contracts
customers 1──N reservations
customers 1──N care_profiles
customers 1──N service_orders
customers 1──N nanny_orders
customers 1──N customer_accounts
customers 1──1 customer_wechat

contracts 1──1 reservations
reservations N──1 rooms

care_profiles 1──N care_plans
care_profiles 1──N care_records
care_profiles 1──N health_metrics

users N──N roles (user_roles)
roles N──N permissions (role_permissions)

四、索引与性能策略

1. 门店隔离索引：所有业务表 store_id 为第一索引字段
2. 时间范围查询：日期字段加索引（plan_date、schedule_date、created_at）
3. 外键关联索引：customer_id、user_id、order_id 等
4. 读写分离预留：Laravel 配置支持读写分离
5. JSON 字段：用于灵活配置（标签、表单、明细），不做条件查询的场景