-- ============================================================
-- 宠物服务管理系统 — 数据库初始化脚本
-- 数据库版本: MySQL 8.0
-- 数据库名: pet_service_managemen_system
-- 字符集: utf8mb4
-- ============================================================

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

USE pet_service_managemen_system;

-- ============================================================
-- 1. 用户表
-- ============================================================
CREATE TABLE IF NOT EXISTS user (
    id          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    account     VARCHAR(100) NOT NULL COMMENT '登录账号',
    username    VARCHAR(100) NOT NULL COMMENT '用户名',
    password    VARCHAR(255) NOT NULL COMMENT 'MD5 加密密码',
    email       VARCHAR(255) DEFAULT NULL COMMENT '邮箱',
    phone       VARCHAR(20)  DEFAULT NULL COMMENT '联系方式',
    avatar      VARCHAR(500) DEFAULT NULL COMMENT '头像 URL',
    role        VARCHAR(20)  DEFAULT 'user' NOT NULL COMMENT '角色: user/admin',
    enable      TINYINT(1)   DEFAULT 1 NOT NULL COMMENT '状态: 0禁用 1启用',
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_account (account)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ============================================================
-- 2. 宠物表
-- ============================================================
CREATE TABLE IF NOT EXISTS pet (
    id          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    user_id     BIGINT       NOT NULL COMMENT '所属用户 ID',
    name        VARCHAR(100) NOT NULL COMMENT '宠物名称',
    species     VARCHAR(50)  NOT NULL COMMENT '物种: DOG/CAT/OTHER',
    breed       VARCHAR(100) DEFAULT NULL COMMENT '品种',
    gender      VARCHAR(10)  DEFAULT NULL COMMENT '性别: MALE/FEMALE',
    birth_date  DATE         DEFAULT NULL COMMENT '出生日期',
    weight      DECIMAL(5,2) DEFAULT NULL COMMENT '体重(kg)',
    avatar      VARCHAR(500) DEFAULT NULL COMMENT '头像 URL',
    remark      TEXT         DEFAULT NULL COMMENT '备注',
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT '更新时间',
    PRIMARY KEY (id),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='宠物表';

-- ============================================================
-- 3. 疫苗记录表
-- ============================================================
CREATE TABLE IF NOT EXISTS vaccination_record (
    id           BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    pet_id       BIGINT       NOT NULL COMMENT '关联宠物 ID',
    vaccine_name VARCHAR(255) NOT NULL COMMENT '疫苗名称',
    vaccine_date DATE         NOT NULL COMMENT '接种日期',
    hospital     VARCHAR(255) DEFAULT NULL COMMENT '接种机构',
    doctor       VARCHAR(100) DEFAULT NULL COMMENT '接种兽医',
    remark       TEXT         DEFAULT NULL COMMENT '备注',
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    PRIMARY KEY (id),
    KEY idx_pet_id (pet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='疫苗记录表';

-- ============================================================
-- 4. 驱虫记录表
-- ============================================================
CREATE TABLE IF NOT EXISTS deworming_record (
    id             BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    pet_id         BIGINT       NOT NULL COMMENT '关联宠物 ID',
    deworming_name VARCHAR(255) NOT NULL COMMENT '驱虫名称',
    deworming_date DATE         NOT NULL COMMENT '驱虫日期',
    hospital       VARCHAR(255) DEFAULT NULL COMMENT '驱虫机构',
    doctor         VARCHAR(100) DEFAULT NULL COMMENT '驱虫兽医',
    remark         TEXT         DEFAULT NULL COMMENT '备注',
    created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    PRIMARY KEY (id),
    KEY idx_pet_id (pet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='驱虫记录表';

-- ============================================================
-- 5. 套餐表
-- ============================================================
CREATE TABLE IF NOT EXISTS package (
    id          BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键',
    name        VARCHAR(255)  NOT NULL COMMENT '套餐名称',
    type        VARCHAR(20)   NOT NULL COMMENT '类型: BOARDING/GROOMING',
    price       DECIMAL(10,2) NOT NULL COMMENT '价格',
    duration    INT           DEFAULT NULL COMMENT '寄养天数（仅寄养套餐）',
    description TEXT          DEFAULT NULL COMMENT '项目内容描述',
    is_active   TINYINT(1)    DEFAULT 1 NOT NULL COMMENT '是否启用: 0禁用 1启用',
    created_at  DATETIME      DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    updated_at  DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT '更新时间',
    PRIMARY KEY (id),
    KEY idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='套餐表';

-- ============================================================
-- 6. 寄养房型表
-- ============================================================
CREATE TABLE IF NOT EXISTS boarding_room (
    id            BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键',
    room_name     VARCHAR(100)  NOT NULL COMMENT '房型名称',
    room_type     VARCHAR(50)   DEFAULT NULL COMMENT '房型分类: STANDARD/VIP/DELUXE',
    capacity      INT           DEFAULT 1 NOT NULL COMMENT '可容纳宠物数',
    price_per_day DECIMAL(10,2) NOT NULL COMMENT '每晚价格',
    is_active     TINYINT(1)    DEFAULT 1 NOT NULL COMMENT '是否启用: 0禁用 1启用',
    description   TEXT          DEFAULT NULL COMMENT '房型描述',
    created_at    DATETIME      DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    updated_at    DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='寄养房型表';

-- ============================================================
-- 7. 房态表
-- ============================================================
CREATE TABLE IF NOT EXISTS boarding_room_status (
    id             BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    room_id        BIGINT       NOT NULL COMMENT '关联房型 ID',
    date           DATE         NOT NULL COMMENT '日期',
    status         VARCHAR(20)  DEFAULT 'AVAILABLE' NOT NULL COMMENT '房态: AVAILABLE/OCCUPIED/MAINTENANCE',
    appointment_id BIGINT       DEFAULT NULL COMMENT '关联预约订单 ID',
    created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_room_date (room_id, date),
    KEY idx_appointment_id (appointment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='房态表';

-- ============================================================
-- 8. 预约订单表
-- ============================================================
CREATE TABLE IF NOT EXISTS appointment (
    id             BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键',
    user_id        BIGINT        NOT NULL COMMENT '用户 ID',
    pet_id         BIGINT        NOT NULL COMMENT '宠物 ID',
    package_id     BIGINT        NOT NULL COMMENT '套餐 ID',
    room_id        BIGINT        DEFAULT NULL COMMENT '寄养房型 ID',
    type           VARCHAR(20)   NOT NULL COMMENT '类型: BOARDING/GROOMING',
    status         VARCHAR(30)   DEFAULT 'PENDING' NOT NULL COMMENT '状态: PENDING/CONFIRMED/CANCELLED/CANCEL_REQUESTED',
    start_date     DATE          DEFAULT NULL COMMENT '开始日期（寄养）',
    end_date       DATE          DEFAULT NULL COMMENT '结束日期（寄养）',
    total_price    DECIMAL(10,2) NOT NULL COMMENT '总价',
    deposit        DECIMAL(10,2) DEFAULT 0.00 NOT NULL COMMENT '定金金额',
    deposit_paid   TINYINT(1)    DEFAULT 0 NOT NULL COMMENT '是否支付定金: 0未支付 1已支付',
    cancel_reason  TEXT          DEFAULT NULL COMMENT '取消原因',
    reject_reason  VARCHAR(500)  DEFAULT NULL COMMENT '拒绝原因',
    remark         TEXT          DEFAULT NULL COMMENT '备注',
    created_at     DATETIME      DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    updated_at     DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT '更新时间',
    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_status (status),
    KEY idx_package_id (package_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预约订单表';

-- ============================================================
-- 9. 排班表
-- ============================================================
CREATE TABLE IF NOT EXISTS schedule (
    id            BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    employee_name VARCHAR(100) NOT NULL COMMENT '员工姓名',
    work_date     DATE         NOT NULL COMMENT '排班日期',
    time_slot     VARCHAR(20)  NOT NULL COMMENT '时段: MORNING/AFTERNOON/FULL_DAY',
    remark        TEXT         DEFAULT NULL COMMENT '备注',
    created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
    PRIMARY KEY (id),
    KEY idx_work_date (work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='排班表';

-- ============================================================
-- 初始数据
-- ============================================================

-- 默认管理员账号: admin / admin123
INSERT INTO user (account, username, password, role, enable) VALUES
    ('admin', '系统管理员', 'f0a2f3c9e1b8d4e7a6c5b0d9f8e7a6c5', 'admin', 1);

-- 测试套餐数据
INSERT INTO package (name, type, price, duration, description, is_active) VALUES
    ('标准寄养', 'BOARDING', 100.00, 1, '基础寄养服务，含每日喂食、遛宠、基础清洁', 1),
    ('豪华寄养', 'BOARDING', 200.00, 1, '豪华寄养服务，含独立空调房、每日零食、视频监控', 1),
    ('基础洗护', 'GROOMING', 80.00, NULL, '基础洗护服务，含洗澡、吹干、剪指甲', 1),
    ('全套洗护', 'GROOMING', 150.00, NULL, '全套洗护服务，含洗澡、美容修剪、SPA护理', 1);

-- 测试房型数据
INSERT INTO boarding_room (room_name, room_type, capacity, price_per_day, description, is_active) VALUES
    ('标准单间', 'STANDARD', 1, 100.00, '标准独立单间，适合中小型犬猫', 1),
    ('豪华套房', 'VIP', 1, 200.00, '豪华独立套房，含空调、摄像头、软垫', 1),
    ('标准双间', 'STANDARD', 2, 150.00, '标准双宠间，适合同一家庭的宠物', 1);