<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dora.mapper.AdminUserMapper">

    <resultMap id="AdminUserResultMap" type="com.dora.entity.AdminUser">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password_hash" property="passwordHash"/>
        <result column="role" property="role"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <!-- 插入管理员用户 -->
    <insert id="insert" parameterType="com.dora.entity.AdminUser" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO admin_user (
            username, password_hash, role, create_time, update_time, is_deleted
        ) VALUES (
            #{username}, #{passwordHash}, #{role}, #{createTime}, #{updateTime}, #{isDeleted}
        )
    </insert>

    <!-- 根据ID查询管理员用户 -->
    <select id="selectById" resultMap="AdminUserResultMap">
        SELECT * FROM admin_user 
        WHERE id = #{id} AND is_deleted = 0
    </select>

    <!-- 根据用户名查询管理员用户 -->
    <select id="selectByUsername" resultMap="AdminUserResultMap">
        SELECT * FROM admin_user 
        WHERE username = #{username} AND is_deleted = 0
        LIMIT 1
    </select>

    <!-- 检查用户名是否存在 -->
    <select id="existsByUsername" resultType="boolean">
        SELECT COUNT(*) > 0 FROM admin_user 
        WHERE username = #{username} AND is_deleted = 0
    </select>

    <!-- 检查数据库中是否存在任何管理员账号 -->
    <select id="hasAnyAdmin" resultType="boolean">
        SELECT COUNT(*) > 0 FROM admin_user 
        WHERE is_deleted = 0
    </select>

    <!-- 更新管理员用户 -->
    <update id="updateById" parameterType="com.dora.entity.AdminUser">
        UPDATE admin_user SET
            username = #{username},
            password_hash = #{passwordHash},
            role = #{role},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 更新密码 -->
    <update id="updatePassword">
        UPDATE admin_user SET
            password_hash = #{passwordHash},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

        <!-- 查询所有管理员用户 -->
    <select id="selectAll" resultMap="AdminUserResultMap">
        SELECT * FROM admin_user 
        WHERE is_deleted = 0
        ORDER BY create_time DESC
    </select>

    <!-- 按关键词分页查询员工 -->
    <select id="selectByKeywordPaged" resultMap="AdminUserResultMap">
        SELECT * FROM admin_user 
        WHERE is_deleted = 0
        <if test="keyword != null and keyword != ''">
            AND (
                real_name LIKE CONCAT('%', #{keyword}, '%') OR
                username LIKE CONCAT('%', #{keyword}, '%') OR
                email LIKE CONCAT('%', #{keyword}, '%') OR
                phone LIKE CONCAT('%', #{keyword}, '%')
            )
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="department != null and department != ''">
            AND department = #{department}
        </if>
        ORDER BY create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 按关键词统计员工数量 -->
    <select id="countByKeyword" resultType="long">
        SELECT COUNT(*) FROM admin_user 
        WHERE is_deleted = 0
        <if test="keyword != null and keyword != ''">
            AND (
                real_name LIKE CONCAT('%', #{keyword}, '%') OR
                username LIKE CONCAT('%', #{keyword}, '%') OR
                email LIKE CONCAT('%', #{keyword}, '%') OR
                phone LIKE CONCAT('%', #{keyword}, '%')
            )
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="department != null and department != ''">
            AND department = #{department}
        </if>
    </select>

    <!-- 按条件分页查询员工 -->
    <select id="selectByConditionPaged" resultMap="AdminUserResultMap">
        SELECT * FROM admin_user 
        WHERE is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="department != null and department != ''">
            AND department = #{department}
        </if>
        ORDER BY create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 按条件统计员工数量 -->
    <select id="countByCondition" resultType="long">
        SELECT COUNT(*) FROM admin_user 
        WHERE is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="department != null and department != ''">
            AND department = #{department}
        </if>
    </select>

    <!-- 根据角色查询管理员用户 -->
    <select id="selectByRole" resultMap="AdminUserResultMap">
        SELECT * FROM admin_user 
        WHERE role = #{role} AND is_deleted = 0 
        ORDER BY create_time DESC
    </select>

    <!-- 统计管理员用户数量 -->
    <select id="countAll" resultType="long">
        SELECT COUNT(*) FROM admin_user 
        WHERE is_deleted = 0
    </select>

    <!-- 统计指定角色的用户数量 -->
    <select id="countByRole" resultType="long">
        SELECT COUNT(*) FROM admin_user 
        WHERE role = #{role} AND is_deleted = 0
    </select>

    <!-- 删除管理员用户（逻辑删除） -->
    <update id="deleteById">
        UPDATE admin_user SET 
            is_deleted = 1,
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- ====== 仪表盘统计相关 ====== -->

    <!-- 统计总用户数 -->
    <select id="countTotalUsers" resultType="long">
        SELECT COUNT(*) FROM user 
        WHERE is_deleted = 0
    </select>

    <!-- 统计今日新增用户数 -->
    <select id="countTodayNewUsers" resultType="long">
        SELECT COUNT(*) FROM user 
        WHERE DATE(create_time) = DATE(#{today}) AND is_deleted = 0
    </select>

    <!-- 统计活跃用户数（最近7天有活动的用户） -->
    <select id="countActiveUsers" resultType="long">
        SELECT COUNT(DISTINCT user_id) FROM user_balance_log 
        WHERE create_time >= #{sevenDaysAgo}
    </select>

    <!-- 统计付费用户数（有购买记录的用户） -->
    <select id="countPaidUsers" resultType="long">
        SELECT COUNT(DISTINCT user_id) FROM `order` 
        WHERE status = 1 AND is_deleted = 0
    </select>

    <!-- 统计总工作流数 -->
    <select id="countTotalWorkflows" resultType="long">
        SELECT COUNT(*) FROM workflow 
        WHERE is_deleted = 0
    </select>

    <!-- 统计今日新增工作流数 -->
    <select id="countTodayNewWorkflows" resultType="long">
        SELECT COUNT(*) FROM workflow 
        WHERE DATE(create_time) = DATE(#{today}) AND is_deleted = 0
    </select>

    <!-- 统计总视频数 -->
    <select id="countTotalVideos" resultType="long">
        SELECT COUNT(*) FROM video 
        WHERE is_deleted = 0
    </select>

    <!-- 统计今日新增视频数 -->
    <select id="countTodayNewVideos" resultType="long">
        SELECT COUNT(*) FROM video 
        WHERE DATE(create_time) = DATE(#{today}) AND is_deleted = 0
    </select>

    <!-- 统计待审核内容数 -->
    <select id="countPendingAuditContent" resultType="long">
        SELECT 
            (SELECT COUNT(*) FROM workflow WHERE audit_status = 0 AND is_deleted = 0) +
            (SELECT COUNT(*) FROM video WHERE audit_status = 0 AND is_deleted = 0)
    </select>

</mapper> 